{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "# Build a working-day calendar in Python\n\nTurn a holiday table into **one row per date**, with weekend, public-holiday and working-day flags. The result can be joined to sales or operations data in Excel, Power BI or SQL.\n\nThis walkthrough uses the free [Polystash holiday sample](https://www.kaggle.com/datasets/polystash/public-holidays-by-country-and-region-2026-2028), covering 2026–2028. It runs with Python's standard library: no API key, package installation or network calls.\n\nWe'll build a 2027 calendar for **Bavaria, Germany**, compare it with the national-only selection, and export the result. I publish Polystash; the final section links to the optional paid full dataset and APIs.\n",
   "id": "polystash-00"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Load the sample\n\nOn Kaggle, add **Public Holidays by Country and Region 2026-2028** as an input. Outside Kaggle, download and unzip it into a folder named `public-holidays-by-country-and-region-2026-2028` beside this notebook.\n\nThe holiday table records observed dates. `countries.csv` supplies weekend definitions, so the calculation does not assume that every country has a Saturday–Sunday weekend.\n",
   "id": "polystash-01"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": 1,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Loaded 10,657 holiday rows; 224 countries and territories.\nSample years: 2026, 2027, 2028\n"
    }
   ],
   "source": "import calendar\nimport csv\nfrom collections import defaultdict\nfrom datetime import date, timedelta\nfrom pathlib import Path\nimport shutil\n\nlocal = Path(\"public-holidays-by-country-and-region-2026-2028\")\ncandidates = [local] if (local / \"holidays_sample.csv\").exists() else []\nif not candidates and Path(\"/kaggle/input\").exists():\n    candidates = sorted({p.parent for p in Path(\"/kaggle/input\").rglob(\"holidays_sample.csv\")\n                         if (p.parent / \"countries.csv\").exists()})\nif len(candidates) != 1:\n    raise ValueError(\"Add the Polystash holiday sample; expected exactly one matching input folder.\")\nDATA_DIR = candidates[0]\n\ndef read_csv(name):\n    with (DATA_DIR / name).open(encoding=\"utf-8-sig\", newline=\"\") as f:\n        return list(csv.DictReader(f))\n\nholidays = read_csv(\"holidays_sample.csv\")\ncountries = {row[\"code\"]: row for row in read_csv(\"countries.csv\")}\nregions = read_csv(\"regions.csv\")\nsample_years = sorted({int(row[\"year\"]) for row in holidays})\nprint(f\"Loaded {len(holidays):,} holiday rows; {len(countries)} countries and territories.\")\nprint(\"Sample years:\", \", \".join(map(str, sample_years)))\n",
   "id": "polystash-02"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Choose the calendar\n\nChange `COUNTRY`, `REGION` and `YEAR` below. Set `REGION = None` for **national entries only**. A region selection includes national holidays plus that region's entries.\n\nHere a working day means a date outside the country's listed weekends and outside the selected **public** holiday dates. Bank, school, optional and observance rows are not automatically treated as closures. This is a reporting convention, not a promise about an employer's opening hours. The source does not model every local exception or replacement working weekend; its country weekend definition is not a full history of workweek changes.\n",
   "id": "polystash-03"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": 2,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Germany / DE-BY / 2027\n365 dates; 253 working days under this selection.\nDates use observed holiday dates from the sample.\n"
    }
   ],
   "source": "COUNTRY = \"DE\"\nREGION = \"DE-BY\"\nYEAR = 2027\n\nweekday_numbers = {name: i for i, name in enumerate(calendar.day_name)}\n\ndef build_calendar(country, year, region=None):\n    country = country.upper()\n    region = region.upper() if region else None\n    if country not in countries:\n        raise ValueError(\"Unknown country code\")\n    if year not in sample_years:\n        raise ValueError(f\"Choose a sample year: {sample_years}\")\n    if region and not any(r[\"country\"] == country and r[\"region\"] == region for r in regions):\n        raise ValueError(\"Region is not listed for this country\")\n    info = countries[country]\n    if info[\"coverage\"] != \"full\":\n        raise ValueError(f\"{info['name']} is marked partial. Review the source gaps before adapting this example.\")\n    weekends = {weekday_numbers[day] for day in info[\"weekend_days\"].split(\";\") if day}\n    names_by_date = defaultdict(set)\n    for row in holidays:\n        if (row[\"country\"] == country and int(row[\"year\"]) == year\n                and (row[\"region\"] == \"\" or (region is not None and row[\"region\"] == region))\n                and \"public\" in row[\"types\"].split(\";\")):\n            names_by_date[row[\"date\"]].add(row[\"name_en\"])\n    result = []\n    day = date(year, 1, 1)\n    while day.year == year:\n        iso = day.isoformat()\n        weekend = day.weekday() in weekends\n        holiday = iso in names_by_date\n        result.append({\n            \"date\": iso, \"country\": country, \"region\": region or \"\",\n            \"weekday\": calendar.day_name[day.weekday()],\n            \"is_weekend\": int(weekend), \"is_public_holiday\": int(holiday),\n            \"is_working_day\": int(not weekend and not holiday),\n            \"holiday_names\": \"; \".join(sorted(names_by_date.get(iso, []))),\n        })\n        day += timedelta(days=1)\n    return result\n\nrows = build_calendar(COUNTRY, YEAR, REGION)\nprint(f\"{countries[COUNTRY]['name']} / {REGION or 'national entries only'} / {YEAR}\")\nprint(f\"{len(rows)} dates; {sum(r['is_working_day'] for r in rows)} working days under this selection.\")\nprint(\"Dates use observed holiday dates from the sample.\")\n",
   "id": "polystash-04"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## See why the region matters\n\nA common mistake is to count weekdays and subtract the number of holiday **rows**. That can subtract the same date twice, or subtract a holiday that already falls on a weekend. The function above groups holiday names by date and classifies each date once.\n",
   "id": "polystash-05"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": 3,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Calendar selection          Working days\nNational entries only                256\nDE-BY                                253\n\nAdditional weekday closures in the region selection:\n2027-01-06 Wednesday — Epiphany\n2027-05-27 Thursday — Corpus Christi\n2027-11-01 Monday — All Saints' Day\n"
    }
   ],
   "source": "national = build_calendar(COUNTRY, YEAR)\nprint(f\"{'Calendar selection':<27} {'Working days':>12}\")\nprint(f\"{'National entries only':<27} {sum(r['is_working_day'] for r in national):>12}\")\nprint(f\"{(REGION or 'National entries only'):<27} {sum(r['is_working_day'] for r in rows):>12}\")\nprint(\"\\nAdditional weekday closures in the region selection:\")\nextra = [r for r, n in zip(rows, national) if n['is_working_day'] and not r['is_working_day']]\nfor row in extra:\n    print(row['date'], row['weekday'], '—', row['holiday_names'])\nif not extra:\n    print(\"None for this selection.\")\n",
   "id": "polystash-06"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Summarize working days by month\n\nThese monthly counts can supply the denominator in a report such as orders per working day. Keep the underlying daily calendar so people can inspect which dates were excluded.\n",
   "id": "polystash-07"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": 4,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Month      Working days\n2027-01              19\n2027-02              20\n2027-03              21\n2027-04              22\n2027-05              18\n2027-06              22\n2027-07              22\n2027-08              22\n2027-09              22\n2027-10              21\n2027-11              21\n2027-12              23\nTOTAL               253\n"
    }
   ],
   "source": "monthly = defaultdict(int)\nfor row in rows:\n    monthly[row[\"date\"][:7]] += row[\"is_working_day\"]\nprint(f\"{'Month':<10} {'Working days':>12}\")\nfor month, count in sorted(monthly.items()):\n    print(f\"{month:<10} {count:>12}\")\nprint(f\"{'TOTAL':<10} {sum(monthly.values()):>12}\")\n",
   "id": "polystash-08"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Observed dates can cross a year boundary\n\nThe sample's `year` is the year of the **observed date**. In the US sample, New Year's Day falling on Saturday 1 January 2028 is recorded on Friday 31 December 2027. Filtering by the holiday's nominal year would lose that row.\n\nThis example prints what is in the sample; the data dictionary explains its date convention.\n",
   "id": "polystash-09"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": 5,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "2027-12-31 — New Year's Day — year column: 2027\n"
    }
   ],
   "source": "for row in holidays:\n    if row[\"country\"] == \"US\" and row[\"region\"] == \"\" and row[\"date\"] == \"2027-12-31\":\n        print(row[\"date\"], \"—\", row[\"name_en\"], \"— year column:\", row[\"year\"])\n",
   "id": "polystash-10"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Export a calendar you can join to a report\n\nThe CSV has one row per date for this country/region/year. Use `date`, `country` and `region` as the join keys if you combine several calendars. The three flags are integers (`0` or `1`); `holiday_names` preserves the reason for a holiday flag.\n\nKeep `ATTRIBUTION.md` with derived data you redistribute. The exported `METHOD.md` records this example's selection and counting convention.\n",
   "id": "polystash-11"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": 6,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Created: working_days_DE_DE-BY_2027.csv — 365 rows\nAlso created ATTRIBUTION.md and METHOD.md.\nOn Kaggle, find them in this notebook's Output files after saving a run.\n"
    }
   ],
   "source": "output_name = f\"working_days_{COUNTRY}_{REGION or 'national'}_{YEAR}.csv\"\nwith Path(output_name).open(\"w\", encoding=\"utf-8\", newline=\"\") as f:\n    writer = csv.DictWriter(f, fieldnames=list(rows[0]))\n    writer.writeheader()\n    writer.writerows(rows)\nshutil.copyfile(DATA_DIR / \"ATTRIBUTION.md\", \"ATTRIBUTION.md\")\nPath(\"METHOD.md\").write_text(\n    f\"# Working-day calendar\\n\\nCountry: {COUNTRY}. Region: {REGION or 'national entries only'}. Year: {YEAR}.\\n\\n\"\n    \"Derived from the Polystash 2026–2028 holiday sample. National public holidays plus the selected \"\n    \"region's public holidays are excluded, as are weekends from countries.csv. Each date is counted once. \"\n    \"The output is a reporting convention; employer schedules, local exceptions and replacement working weekends \"\n    \"are not modeled. Future source-generated calendars can change. Keep ATTRIBUTION.md with redistribution.\\n\\n\"\n    \"Source: https://www.kaggle.com/datasets/polystash/public-holidays-by-country-and-region-2026-2028\\n\",\n    encoding=\"utf-8\",\n)\nprint(\"Created:\", output_name, \"—\", len(rows), \"rows\")\nprint(\"Also created ATTRIBUTION.md and METHOD.md.\")\nprint(\"On Kaggle, find them in this notebook's Output files after saving a run.\")\n",
   "id": "polystash-12"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Use the sample, get the full pack, or call the API\n\n**For 2026–2028:** this free sample is enough to run and adapt the walkthrough.\n\n**For a longer date range or other formats:** I publish the [full Polystash holiday pack — $9 one-off](https://polystash.gumroad.com/l/public-holidays-business-days-2000-2060?utm_source=kaggle&utm_medium=notebook&utm_campaign=working_day_calendar). It covers 2000–2060 and includes CSV, Parquet, iCalendar files, country weekend definitions and regional reference tables. Source-generated future calendars can change; review the coverage notes for your country. Buying the pack pays for the prepared files, not exclusive access to the upstream open-source data.\n\n**For calculations inside an application:** the [Business Days API](https://api.polystash.com/?utm_source=kaggle&utm_medium=notebook&utm_campaign=working_day_calendar) adds or counts working days without loading a local file. If your task is securities settlement or payment value dates, see the separate Settlement API on the same site. [Compare the API plans](https://api.polystash.com/pricing) or [read the working examples](https://api.polystash.com/quickstart.md).\n\nHoliday data is sourced from Nager.Date and date-holidays. Consult the input's `ATTRIBUTION.md` and `DATA_DICTIONARY.md` for licences, observed dates, regional scope and coverage. A general public-holiday calendar is different from an exchange or payment-scheme calendar.\n",
   "id": "polystash-13"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}