{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# AltDataEU: EU public procurement, last 12 months (real extract)\n",
    "\n",
    "This notebook loads a real extract of the `procurement_contracts` table (TED contract award notices) and shows three things: how to load it, how to aggregate it without mixing currencies, and how to find the rows that are linked to a listed security.\n",
    "\n",
    "* The file is a snapshot taken on 9 October 2026 and covers the previous 12 months of award dates. It is small on purpose: every row is a real notice, with a link to the source on ted.europa.eu.\n",
    "* Amounts are in the currency stated on the notice. Some notices state no value; those rows are kept with an empty amount.\n",
    "* `ticker`, `isin`, `lei` and `mapping_confidence` are filled only for winners that match an accepted entity-to-security mapping. Most rows are not mapped, and that is expected.\n",
    "* No prices are used here and nothing in this notebook is investment advice."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Load"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "url = \"https://www.altdataeu.com/samples/eu_procurement_contracts_12m_sample.csv\"\n",
    "df = pd.read_csv(url, parse_dates=[\"award_date\", \"ingested_at\"])\n",
    "\n",
    "print(f\"{len(df):,} contract awards, {df['award_date'].min().date()} to {df['award_date'].max().date()}\")\n",
    "df[[\"award_date\", \"buyer_name\", \"winner_name_raw\", \"awarded_amount\", \"awarded_currency\"]].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Aggregate by month, one currency at a time\n",
    "\n",
    "Summing amounts across currencies is meaningless, so group by currency as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with_value = df[df[\"awarded_amount\"].fillna(0) > 0]\n",
    "monthly = (\n",
    "    with_value\n",
    "    .assign(month=with_value[\"award_date\"].dt.to_period(\"M\"))\n",
    "    .groupby([\"month\", \"awarded_currency\"])[\"awarded_amount\"]\n",
    "    .agg(notices=\"count\", total=\"sum\")\n",
    ")\n",
    "print(f\"{(df['awarded_amount'].fillna(0) == 0).sum()} rows state no amount and are excluded from the totals\")\n",
    "monthly.tail(12)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Which winners are linked to a listed security?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mapped = df[df[\"ticker\"].notna() | df[\"isin\"].notna()]\n",
    "print(f\"{len(mapped)} of {len(df)} rows are linked to a mapped security\")\n",
    "mapped[[\"award_date\", \"winner_name_raw\", \"parent_company\", \"ticker\", \"isin\", \"lei\", \"mapping_confidence\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Provenance\n",
    "\n",
    "Every row has a `source_url` to the notice on TED and an `ingested_at` timestamp showing when AltDataEU first stored it. `award_date` is the date on the notice. Use `ingested_at`, not `award_date`, when you need to know what was knowable on a given day."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}