notebooks/06-TimeSeries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1a136021",
   "metadata": {},
   "source": [
    "# Air Quality over Time - Working with dates and times\n",
    "\n",
    "For this module, we will be exploring the techniques for working with dates and times. \n",
    "\n",
    "You will learn about:\n",
    "\n",
    "* Parsing Dates\n",
    "* Cleaning up data\n",
    "* Fixing \"Numerical\" Strings\n",
    "* Making functions\n",
    "* Timezones\n",
    "* Resampling\n",
    "* Rolling operations\n",
    "* Plotting"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a958c9ed-fa5d-43ba-99f1-46e80fa1e700",
   "metadata": {},
   "source": [
    "# Air Quality Data\n",
    "\n",
    "https://archive.ics.uci.edu/ml/datasets/Air+Quality\n",
    "\n",
    "0. Date (DD/MM/YYYY)\n",
    "1. Time (HH.MM.SS)\n",
    "2. True hourly averaged concentration CO in mg/m^3 (reference analyzer)\n",
    "3. PT08.S1 (tin oxide) hourly averaged sensor response (nominally CO targeted)\n",
    "4. True hourly averaged overall Non Metanic HydroCarbons concentration in microg/m^3 (reference analyzer)\n",
    "5. True hourly averaged Benzene concentration in microg/m^3 (reference analyzer)\n",
    "6. PT08.S2 (titania) hourly averaged sensor response (nominally NMHC targeted)\n",
    "7. True hourly averaged NOx concentration in ppb (reference analyzer)\n",
    "8. PT08.S3 (tungsten oxide) hourly averaged sensor response (nominally NOx targeted)\n",
    "9. True hourly averaged NO2 concentration in microg/m^3 (reference analyzer)\n",
    "10. PT08.S4 (tungsten oxide) hourly averaged sensor response (nominally NO2 targeted)\n",
    "11. PT08.S5 (indium oxide) hourly averaged sensor response (nominally O3 targeted)\n",
    "12. Temperature in Â°C\n",
    "13. Relative Humidity (%)\n",
    "14. AH Absolute Humidity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "219fd926-e597-40c2-8e79-34f58c62e35c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Expect an error (for demonstration purposes)\n",
    "# ValueError: Found non-unique column index\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import zipfile\n",
    "with zipfile.ZipFile('data/AirQualityUCI.zip') as zip:\n",
    "  df = pd.read_csv(zip.open('AirQualityUCI.csv'), sep=';',\n",
    "                  dtype_backend='pyarrow', engine='pyarrow')\n",
    "  \n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17ea9660",
   "metadata": {
    "lines_to_next_cell": 0,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "with zipfile.ZipFile('data/AirQualityUCI.zip') as zip:\n",
    "  df = pd.read_csv(zip.open('AirQualityUCI.csv'), sep=';',\n",
    "                  dtype_backend='pyarrow', #engine='pyarrow',\n",
    "                  )\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b583ca08-991d-472f-85e3-e7b464437b8e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ca18afe",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbf9b2cc",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "704f9a7a-380a-40da-b7d1-3e74fd10baed",
   "metadata": {},
   "source": [
    "## Clean up Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1bd695e1-1d8c-4b79-8c9d-3719cbc70335",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "(df\n",
    " .isna()\n",
    " .sum(axis='columns')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc99fafa",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c8b43f0-4ab4-421f-af68-cc64db329d10",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(df\n",
    " .loc[df.isna().sum(axis='columns') < 17]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1d124b4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22f14314",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba7628d6",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "92241f9c",
   "metadata": {},
   "source": [
    "## Date Parsing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "355fa3b2",
   "metadata": {
    "lines_to_next_cell": 2,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(df\n",
    " .loc[df.isna().sum(axis='columns') < 17]\n",
    " .assign(date=lambda df_: (df_.Date + ' ' + \\\n",
    "                    df_.Time.replace(r'\\.', ':', regex=True)))\n",
    " .loc[:, ['Date', 'Time', 'date']]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e1190fa",
   "metadata": {
    "lines_to_next_cell": 2,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Expect an error (for demonstration purposes)\n",
    "# ValueError: time data \"13/03/2004 00:00:00\" doesn't match format \"%m/%d/%Y %H:%M:%S\":\n",
    "\n",
    "(df\n",
    " .loc[df.isna().sum(axis='columns') < 17]\n",
    " .assign(date=lambda df_: pd.to_datetime(df_.Date + ' ' + \\\n",
    "                    df_.Time.replace(r'\\.', ':', regex=True)))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "813929ee",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "(df\n",
    ".Date\n",
    ".value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1bc3f0a7",
   "metadata": {
    "lines_to_next_cell": 2,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(df\n",
    " .loc[df.isna().sum(axis='columns') < 17]\n",
    " .assign(date=lambda df_: pd.to_datetime(df_.Date + ' ' + \\\n",
    "                    df_.Time.replace(r'\\.', ':', regex=True),\n",
    "                                        dayfirst=True))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f1aa32a-b7a7-4249-9f1e-fba6a0fac27d",
   "metadata": {
    "lines_to_next_cell": 2,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Alternate\n",
    "(df\n",
    " .loc[df.isna().sum(axis='columns') < 17]\n",
    " .assign(date=lambda df_: pd.to_datetime(df_.Date + ' ' + \\\n",
    "                    df_.Time.replace(r'\\.', ':', regex=True),\n",
    "                    format='%d/%m/%Y %H:%M:%S'))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7038bd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "(df\n",
    " .loc[df.isna().sum(axis='columns') < 17]\n",
    " .assign(date=lambda df_: pd.to_datetime(df_.Date + ' ' + \\\n",
    "                    df_.Time.replace(r'\\.', ':', regex=True),\n",
    "                    format='%d/%m/%Y %H:%M:%S'))\n",
    " .dtypes\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "581f7bcd",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dea7037e",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "071cf195",
   "metadata": {},
   "source": [
    "## Rename Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86816bd5",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "(df\n",
    " .loc[df.isna().sum(axis='columns') < 17]\n",
    " .assign(date=lambda df_: pd.to_datetime(df_.Date + ' ' + \\\n",
    "                    df_.Time.replace(r'\\.', ':', regex=True),\n",
    "                    format='%d/%m/%Y %H:%M:%S'))\n",
    " .rename(columns={'CO(GT)': 'carbon_monoxide',\n",
    "                  'PT08.S1(CO)': 'tin_oxide',\n",
    "                  'NMHC(GT)': 'non_metallic_hydro_carb',\n",
    "                  'C6H6(GT)': 'benzene',\n",
    "                  'PT08.S2(NMHC)': 'titania',\n",
    "                  'NOx(GT)': 'nox',\n",
    "                  'PT08.S3(NOx)': 'tungsten_oxide_nox',\n",
    "                  'NO2(GT)': 'no2',\n",
    "                  'PT08.S4(NO2)':  'tungsten_oxide_no2',\n",
    "                  'PT08.S5(O3)': 'indium_oxide',\n",
    "                  'T': 'temp',\n",
    "                  'RH': 'rel_humid',\n",
    "                  'AH': 'abs_humid'})\n",
    " \n",
    " .columns\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9b38741-7624-4d38-9f01-f877bcbdf5fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Unnamed: 15'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e61c1478-920f-4fe8-9f1e-b8adafef0bd3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Unnamed: 16'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee73c7de",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(df\n",
    " .loc[df.isna().sum(axis='columns') < 17]\n",
    " .assign(date=lambda df_: pd.to_datetime(df_.Date + ' ' + \\\n",
    "                    df_.Time.replace(r'\\.', ':', regex=True),\n",
    "                    format='%d/%m/%Y %H:%M:%S'))\n",
    " .rename(columns={'CO(GT)': 'carbon_monoxide',\n",
    "                  'PT08.S1(CO)': 'tin_oxide',\n",
    "                  'NMHC(GT)': 'non_metallic_hydro_carb',\n",
    "                  'C6H6(GT)': 'benzene',\n",
    "                  'PT08.S2(NMHC)': 'titania',\n",
    "                  'NOx(GT)': 'nox',\n",
    "                  'PT08.S3(NOx)': 'tungsten_oxide_nox',\n",
    "                  'NO2(GT)': 'no2',\n",
    "                  'PT08.S4(NO2)':  'tungsten_oxide_no2',\n",
    "                  'PT08.S5(O3)': 'indium_oxide',\n",
    "                  'T': 'temp',\n",
    "                  'RH': 'rel_humid',\n",
    "                  'AH': 'abs_humid'})\n",
    " .loc[:, ['carbon_monoxide', 'tin_oxide',\n",
    "       'non_metallic_hydro_carb', 'benzene', 'titania', 'nox',\n",
    "       'tungsten_oxide_nox', 'no2', 'tungsten_oxide_no2', 'indium_oxide',\n",
    "       'temp', 'rel_humid', 'abs_humid', 'date']]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e784f1d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0760f83e",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "266b5009",
   "metadata": {},
   "source": [
    "## Fixing Numerical \"Strings\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "055b3ec6",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "(df\n",
    " .loc[df.isna().sum(axis='columns') < 17]\n",
    " .assign(date=lambda df_: pd.to_datetime(df_.Date + ' ' + \\\n",
    "                    df_.Time.replace(r'\\.', ':', regex=True),\n",
    "                    format='%d/%m/%Y %H:%M:%S'))\n",
    " .rename(columns={'CO(GT)': 'carbon_monoxide',\n",
    "                  'PT08.S1(CO)': 'tin_oxide',\n",
    "                  'NMHC(GT)': 'non_metallic_hydro_carb',\n",
    "                  'C6H6(GT)': 'benzene',\n",
    "                  'PT08.S2(NMHC)': 'titania',\n",
    "                  'NOx(GT)': 'nox',\n",
    "                  'PT08.S3(NOx)': 'tungsten_oxide_nox',\n",
    "                  'NO2(GT)': 'no2',\n",
    "                  'PT08.S4(NO2)':  'tungsten_oxide_no2',\n",
    "                  'PT08.S5(O3)': 'indium_oxide',\n",
    "                  'T': 'temp',\n",
    "                  'RH': 'rel_humid',\n",
    "                  'AH': 'abs_humid'})\n",
    " .loc[:, ['carbon_monoxide', 'tin_oxide',\n",
    "       'non_metallic_hydro_carb', 'benzene', 'titania', 'nox',\n",
    "       'tungsten_oxide_nox', 'no2', 'tungsten_oxide_no2', 'indium_oxide',\n",
    "       'temp', 'rel_humid', 'abs_humid', 'date']]\n",
    " .select_dtypes('string')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73488c10",
   "metadata": {
    "lines_to_next_cell": 2,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(df\n",
    " .loc[df.isna().sum(axis='columns') < 17]\n",
    " .assign(date=lambda df_: pd.to_datetime(df_.Date + ' ' + \\\n",
    "                    df_.Time.replace(r'\\.', ':', regex=True),\n",
    "                    format='%d/%m/%Y %H:%M:%S'))\n",
    " .rename(columns={'CO(GT)': 'carbon_monoxide',\n",
    "                  'PT08.S1(CO)': 'tin_oxide',\n",
    "                  'NMHC(GT)': 'non_metallic_hydro_carb',\n",
    "                  'C6H6(GT)': 'benzene',\n",
    "                  'PT08.S2(NMHC)': 'titania',\n",
    "                  'NOx(GT)': 'nox',\n",
    "                  'PT08.S3(NOx)': 'tungsten_oxide_nox',\n",
    "                  'NO2(GT)': 'no2',\n",
    "                  'PT08.S4(NO2)':  'tungsten_oxide_no2',\n",
    "                  'PT08.S5(O3)': 'indium_oxide',\n",
    "                  'T': 'temp',\n",
    "                  'RH': 'rel_humid',\n",
    "                  'AH': 'abs_humid'})\n",
    " .loc[:, ['carbon_monoxide', 'tin_oxide',\n",
    "       'non_metallic_hydro_carb', 'benzene', 'titania', 'nox',\n",
    "       'tungsten_oxide_nox', 'no2', 'tungsten_oxide_no2', 'indium_oxide',\n",
    "       'temp', 'rel_humid', 'abs_humid', 'date']]\n",
    " .pipe(lambda df_: df_.assign(**{col: df_[col].str.replace(',','.').astype('float[pyarrow]')\n",
    "                                for col in\n",
    "      ['carbon_monoxide', 'benzene', 'temp', 'rel_humid', 'abs_humid']}))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa52aa6f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(df\n",
    " .loc[df.isna().sum(axis='columns') < 17]\n",
    " .assign(date=lambda df_: pd.to_datetime(df_.Date + ' ' + \\\n",
    "                    df_.Time.replace(r'\\.', ':', regex=True),\n",
    "                    format='%d/%m/%Y %H:%M:%S'))\n",
    " .rename(columns={'CO(GT)': 'carbon_monoxide',\n",
    "                  'PT08.S1(CO)': 'tin_oxide',\n",
    "                  'NMHC(GT)': 'non_metallic_hydro_carb',\n",
    "                  'C6H6(GT)': 'benzene',\n",
    "                  'PT08.S2(NMHC)': 'titania',\n",
    "                  'NOx(GT)': 'nox',\n",
    "                  'PT08.S3(NOx)': 'tungsten_oxide_nox',\n",
    "                  'NO2(GT)': 'no2',\n",
    "                  'PT08.S4(NO2)':  'tungsten_oxide_no2',\n",
    "                  'PT08.S5(O3)': 'indium_oxide',\n",
    "                  'T': 'temp',\n",
    "                  'RH': 'rel_humid',\n",
    "                  'AH': 'abs_humid'})\n",
    " .loc[:, ['carbon_monoxide', 'tin_oxide',\n",
    "       'non_metallic_hydro_carb', 'benzene', 'titania', 'nox',\n",
    "       'tungsten_oxide_nox', 'no2', 'tungsten_oxide_no2', 'indium_oxide',\n",
    "       'temp', 'rel_humid', 'abs_humid', 'date']]\n",
    " .pipe(lambda df_: df_.assign(**{col: df_[col].str.replace(',','.').astype('float[pyarrow]')\n",
    "                                for col in\n",
    "      ['carbon_monoxide', 'benzene', 'temp', 'rel_humid', 'abs_humid']}))\n",
    " .dtypes\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29d7d7dc-37f5-4080-bbe1-0e1d43ae1830",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "2d500436-9546-4114-8942-fbcf4eae8fad",
   "metadata": {},
   "source": [
    "## Make a Function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "680e714b-7b3a-4f03-971d-c31566cc7d83",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import zipfile\n",
    "with zipfile.ZipFile('data/AirQualityUCI.zip') as zip:\n",
    "  df = pd.read_csv(zip.open('AirQualityUCI.csv'), sep=';',\n",
    "                  dtype_backend='pyarrow')\n",
    "  \n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3925be05",
   "metadata": {},
   "outputs": [],
   "source": [
    "def tweak_air_qual(df):\n",
    "  return (df\n",
    "     .loc[df.isna().sum(axis='columns') < 17]\n",
    "     .assign(date=lambda df_: pd.to_datetime(df_.Date + ' ' + \\\n",
    "                        df_.Time.replace(r'\\.', ':', regex=True),\n",
    "                        format='%d/%m/%Y %H:%M:%S'))\n",
    "     .rename(columns={'CO(GT)': 'carbon_monoxide',\n",
    "                      'PT08.S1(CO)': 'tin_oxide',\n",
    "                      'NMHC(GT)': 'non_metallic_hydro_carb',\n",
    "                      'C6H6(GT)': 'benzene',\n",
    "                      'PT08.S2(NMHC)': 'titania',\n",
    "                      'NOx(GT)': 'nox',\n",
    "                      'PT08.S3(NOx)': 'tungsten_oxide_nox',\n",
    "                      'NO2(GT)': 'no2',\n",
    "                      'PT08.S4(NO2)':  'tungsten_oxide_no2',\n",
    "                      'PT08.S5(O3)': 'indium_oxide',\n",
    "                      'T': 'temp',\n",
    "                      'RH': 'rel_humid',\n",
    "                      'AH': 'abs_humid'})\n",
    "     .loc[:, ['carbon_monoxide', 'tin_oxide',\n",
    "           'non_metallic_hydro_carb', 'benzene', 'titania', 'nox',\n",
    "           'tungsten_oxide_nox', 'no2', 'tungsten_oxide_no2', 'indium_oxide',\n",
    "           'temp', 'rel_humid', 'abs_humid', 'date']]\n",
    "     .pipe(lambda df_: df_.assign(**{col: df_[col].str.replace(',','.').astype('float[pyarrow]')\n",
    "                                    for col in\n",
    "          ['carbon_monoxide', 'benzene', 'temp', 'rel_humid', 'abs_humid']}))\n",
    "    )\n",
    "\n",
    "air_df = tweak_air_qual(df)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a3c9271-0d43-4b80-b3b3-ad3d1670bc9f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "air_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5487262",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "384f2541",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e82c7658",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d3e6d12",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "4f799235",
   "metadata": {},
   "source": [
    "## Convert to UTC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6887d663",
   "metadata": {},
   "outputs": [],
   "source": [
    "air_df.date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "63e41eed",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "air_df.date.dt.tz_localize(tz='UTC')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40a7425e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "air_df.date.convert_dtypes('timestamp[ns][pyarrow]').dt.tz_localize('UTC')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6ed9c51",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19b6c853",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9437689a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "6479645e",
   "metadata": {},
   "source": [
    "## Convert to Italian Time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c590e1be",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pytz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e026702",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "pytz.all_timezones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9bdf2dc2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "print([tz for tz in pytz.all_timezones if 'Europe' in tz])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ef78e40",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Expect an error (for demonstration purposes)\n",
    "# NonExistentTimeError: 2004-03-28 02:00:00\n",
    "\n",
    "air_df.date.dt.tz_localize('Europe/Rome')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a171565f",
   "metadata": {},
   "outputs": [],
   "source": [
    "air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6def4fd",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(air_df\n",
    " .date\n",
    " .convert_dtypes('timestamp[ns][pyarrow]')\n",
    " .dt.tz_localize('UTC')\n",
    " .dt.tz_convert('Europe/Rome')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a2f3443",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Note that CET works but isn't a timezone\n",
    "air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('CET')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa3744b4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "177584d5",
   "metadata": {},
   "source": [
    "## Plotting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e8c0f23",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc[:, ['carbon_monoxide', 'benzene']]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b249a015",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.convert_dtypes('timestamp[ns][pyarrow]').dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc[:, ['carbon_monoxide', 'benzene']]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62e2b5c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc[:, ['carbon_monoxide', 'benzene']]\n",
    " .plot(figsize=(8,3))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d64faad2",
   "metadata": {},
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04':'2004/05', ['carbon_monoxide', 'benzene']]\n",
    " .plot(figsize=(8,3))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b18c0022",
   "metadata": {},
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04':'2004/05', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .plot(figsize=(8,3)) \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1caa7ba9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# zoom in a little more\n",
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04/08':'2004/04/13', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .plot(figsize=(8,3)) \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "884f08b6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10433133",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "655634b4",
   "metadata": {},
   "source": [
    "## Missing Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37a5bb10",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04/08':'2004/04/13', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .plot(figsize=(8,3))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8868542d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Expect an error (for demonstration purposes)\n",
    "# TypeError: ExtensionArray.interpolate() missing 1 required keyword-only argument: 'fill_value'\n",
    "\n",
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04/08':'2004/04/13', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .interpolate()\n",
    " .plot(figsize=(8,3))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "949f8e5d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04/08':'2004/04/13', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .astype(float)\n",
    " .interpolate()\n",
    " .plot(figsize=(8,3))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "208a968f",
   "metadata": {},
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04/08':'2004/04/13', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .ffill()\n",
    " .plot(figsize=(8,3))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b376d79",
   "metadata": {},
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04/08':'2004/04/13', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .bfill()\n",
    " .plot(figsize=(8,3))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "828d7735",
   "metadata": {},
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04/08':'2004/04/13', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .fillna(22)\n",
    " .plot(figsize=(8,3))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d994b3b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04/08':'2004/04/13', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .pipe(lambda df_: df_.fillna(df_.mean()))\n",
    " .plot(figsize=(8,3))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f5a054e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "2667514e",
   "metadata": {},
   "source": [
    "## Resampling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5393117b",
   "metadata": {},
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04/08':'2004/04/13', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .astype(float)\n",
    " .interpolate()\n",
    " #.plot(figsize=(8,3))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dcd2294c",
   "metadata": {},
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04/08':'2004/04/13', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .astype(float)\n",
    " .interpolate()\n",
    " .resample('5h')\n",
    " .mean()\n",
    " .plot(figsize=(8,3))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a9ab306",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04/08':'2004/04/13', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .astype(float)\n",
    " .interpolate()\n",
    " .resample('2h37min')\n",
    " .mean()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90ff4ecf",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04/08':'2004/04/13', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .astype(float)\n",
    " .interpolate()\n",
    " .resample('2h37min')\n",
    " .mean()\n",
    " .plot(figsize=(8,3)) \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5af339ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04/08':'2004/05/13', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .astype(float)\n",
    " .interpolate()\n",
    " .resample('1d')\n",
    " .mean()\n",
    " .plot()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b08f504",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Note: You can also use pd.Grouper and .groupby\n",
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " #.set_index('date')\n",
    " #.loc['2004/04/08':'2004/05/13', ['carbon_monoxide', 'benzene']] \n",
    " .loc[:, ['date', 'carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    "# .astype(float)\n",
    " .astype({'carbon_monoxide': float, 'benzene': float})\n",
    " .interpolate()\n",
    "# #.resample('1d')\n",
    " .groupby(pd.Grouper(key='date', freq='1d'))\n",
    " .mean()\n",
    " .loc['2004/04/08':'2004/05/13']\n",
    " .plot()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc08938f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76e1a2c9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "5eecb106",
   "metadata": {},
   "source": [
    "## Rolling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "290e72d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04/08':'2005/05/13', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .astype(float)\n",
    " .interpolate()  #.interpolate works with Pandas 1.x\n",
    " .plot()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85670854",
   "metadata": {},
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04/08':'2005/05/13', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .astype(float)\n",
    " .interpolate()\n",
    " .resample('d')\n",
    " .mean()\n",
    " .interpolate()\n",
    " .plot()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29b336e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "(air_df\n",
    " .assign(date=air_df.date.dt.tz_localize(tz='UTC').dt.tz_convert('Europe/Rome'))\n",
    " .set_index('date')\n",
    " .loc['2004/04/08':'2005/05/13', ['carbon_monoxide', 'benzene']] \n",
    " .replace(-200, np.nan)\n",
    " .astype(float)\n",
    " .interpolate()\n",
    " .resample('d')\n",
    " .mean()\n",
    " .interpolate()\n",
    " .rolling(7)\n",
    " .mean()\n",
    " .plot()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3391dd99",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "489129d5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "4e71b5ea",
   "metadata": {},
   "source": [
    "## Fix the Function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1671a54e-5566-402e-ac8d-323a586ac464",
   "metadata": {},
   "outputs": [],
   "source": [
    "# fix the -200 values\n",
    "def tweak_air_qual(df):\n",
    "  return (df\n",
    "   .loc[df.isna().sum(axis='columns') < 17]\n",
    "   .assign(date=lambda df_: pd.to_datetime(df_.Date + ' ' + \\\n",
    "                      df_.Time.replace(r'\\.', ':', regex=True),\n",
    "                                          dayfirst=True))\n",
    "   .rename(columns={'CO(GT)': 'carbon_monoxide',\n",
    "                    'PT08.S1(CO)': 'tin_oxide',\n",
    "                    'NMHC(GT)': 'non_metallic_hydro_carb',\n",
    "                    'C6H6(GT)': 'benzene',\n",
    "                    'PT08.S2(NMHC)': 'titania',\n",
    "                    'NOx(GT)': 'nox',\n",
    "                    'PT08.S3(NOx)': 'tungsten_oxide_nox',\n",
    "                    'NO2(GT)': 'no2',\n",
    "                    'PT08.S4(NO2)':  'tungsten_oxide_no2',\n",
    "                    'PT08.S5(O3)': 'indium_oxide',\n",
    "                    'T': 'temp',\n",
    "                    'RH': 'rel_humid',\n",
    "                    'AH': 'abs_humid'})\n",
    "   .loc[:, ['carbon_monoxide', 'tin_oxide',\n",
    "         'non_metallic_hydro_carb', 'benzene', 'titania', 'nox',\n",
    "         'tungsten_oxide_nox', 'no2', 'tungsten_oxide_no2', 'indium_oxide',\n",
    "         'temp', 'rel_humid', 'abs_humid', 'date']]\n",
    "   .pipe(lambda df_: df_.assign(**{col: df_[col].str.replace(',','.').astype(float)\n",
    "                                  for col in\n",
    "        ['carbon_monoxide', 'benzene', 'temp', 'rel_humid', 'abs_humid']}))\n",
    "   .replace(-200, np.nan)\n",
    "  )\n",
    "\n",
    "with zipfile.ZipFile('data/AirQualityUCI.zip') as zip:\n",
    "  df = pd.read_csv(zip.open('AirQualityUCI.csv'), sep=';',\n",
    "                  dtype_backend='pyarrow', #engine='pyarrow',\n",
    "                  )\n",
    "\n",
    "air_df = tweak_air_qual(df)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0817713d",
   "metadata": {},
   "outputs": [],
   "source": [
    "air_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5bd91df",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "jupytext": {
   "formats": "ipynb,py:light"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.6"
  },
  "vscode": {
   "interpreter": {
    "hash": "2a01577db06f90dfcd8a13ffbfc05b48310cbd7ef042271883479819627b09e5"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}