Tutorials/Python_Data_Engineering/5. Python for Data Engineering/5.1 Working with date & time.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working with datetime in Pandas DataFrame\n",
    "\n",
    "<img src=\"images/datetimememe.webp\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Convert strings to datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Default arguments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'date': ['3/10/2000', '3/11/2000', '3/12/2000'],\n",
    "                   'value': [2, 3, 4]})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3/10/2000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3/11/2000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3/12/2000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  value\n",
       "0  3/10/2000      2\n",
       "1  3/11/2000      3\n",
       "2  3/12/2000      4"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3 entries, 0 to 2\n",
      "Data columns (total 2 columns):\n",
      " #   Column  Non-Null Count  Dtype \n",
      "---  ------  --------------  ----- \n",
      " 0   date    3 non-null      object\n",
      " 1   value   3 non-null      int64 \n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 176.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['date'] = pd.to_datetime(df['date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3 entries, 0 to 2\n",
      "Data columns (total 2 columns):\n",
      " #   Column  Non-Null Count  Dtype         \n",
      "---  ------  --------------  -----         \n",
      " 0   date    3 non-null      datetime64[ns]\n",
      " 1   value   3 non-null      int64         \n",
      "dtypes: datetime64[ns](1), int64(1)\n",
      "memory usage: 176.0 bytes\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000-03-10</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2000-03-11</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2000-03-12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  value\n",
       "0 2000-03-10      2\n",
       "1 2000-03-11      3\n",
       "2 2000-03-12      4"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Day first format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'date': ['3/10/2000', '3/11/2000', '3/12/2000'],\n",
    "                   'value': [2, 3, 4]})\n",
    "df['date'] = pd.to_datetime(df['date'], dayfirst=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000-10-03</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2000-11-03</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2000-12-03</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  value\n",
       "0 2000-10-03      2\n",
       "1 2000-11-03      3\n",
       "2 2000-12-03      4"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Custom format \n",
    "\n",
    "Ref - https://pandas.pydata.org/docs/reference/api/pandas.to_datetime.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'date': ['2016-6-10 20:30:0', \n",
    "                            '2016-7-1 19:45:30', \n",
    "                            '2013-10-12 4:5:1'],\n",
    "                   'value': [2, 3, 4]})\n",
    "df['date'] = pd.to_datetime(df['date'], format=\"%Y-%d-%m %H:%M:%S\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-10-06 20:30:00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-01-07 19:45:30</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-12-10 04:05:01</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 date  value\n",
       "0 2016-10-06 20:30:00      2\n",
       "1 2016-01-07 19:45:30      3\n",
       "2 2013-12-10 04:05:01      4"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Speedup parsing with infer_datetime_format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3/11/2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3/12/2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3/13/2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3/11/2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3/12/2000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date\n",
       "0  3/11/2000\n",
       "1  3/12/2000\n",
       "2  3/13/2000\n",
       "3  3/11/2000\n",
       "4  3/12/2000"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({'date': ['3/11/2000', '3/12/2000', '3/13/2000'] * 1000 })\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "449 µs ± 8.62 µs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit pd.to_datetime(df['date'], infer_datetime_format=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "434 µs ± 12.2 µs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit pd.to_datetime(df['date'], infer_datetime_format=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Handle parsing error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'date': ['3/10/2000', 'a/11/2000', '3/12/2000'],\n",
    "                   'value': [2, 3, 4]})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3/10/2000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a/11/2000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3/12/2000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  value\n",
       "0  3/10/2000      2\n",
       "1  a/11/2000      3\n",
       "2  3/12/2000      4"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "ename": "ParserError",
     "evalue": "Unknown string format: a/11/2000",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "File \u001b[0;32m/opt/homebrew/lib/python3.10/site-packages/pandas/core/arrays/datetimes.py:2236\u001b[0m, in \u001b[0;36mobjects_to_datetime64ns\u001b[0;34m(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object, allow_mixed)\u001b[0m\n\u001b[1;32m   2235\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m-> 2236\u001b[0m     values, tz_parsed \u001b[38;5;241m=\u001b[39m \u001b[43mconversion\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdatetime_to_datetime64\u001b[49m\u001b[43m(\u001b[49m\u001b[43mdata\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mravel\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mK\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   2237\u001b[0m     \u001b[38;5;66;03m# If tzaware, these values represent unix timestamps, so we\u001b[39;00m\n\u001b[1;32m   2238\u001b[0m     \u001b[38;5;66;03m#  return them as i8 to distinguish from wall times\u001b[39;00m\n",
      "File \u001b[0;32m/opt/homebrew/lib/python3.10/site-packages/pandas/_libs/tslibs/conversion.pyx:360\u001b[0m, in \u001b[0;36mpandas._libs.tslibs.conversion.datetime_to_datetime64\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mTypeError\u001b[0m: Unrecognized value type: <class 'str'>",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mParserError\u001b[0m                               Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[22], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m df[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mdate\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mto_datetime\u001b[49m\u001b[43m(\u001b[49m\u001b[43mdf\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mdate\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m]\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m/opt/homebrew/lib/python3.10/site-packages/pandas/core/tools/datetimes.py:1051\u001b[0m, in \u001b[0;36mto_datetime\u001b[0;34m(arg, errors, dayfirst, yearfirst, utc, format, exact, unit, infer_datetime_format, origin, cache)\u001b[0m\n\u001b[1;32m   1049\u001b[0m         result \u001b[38;5;241m=\u001b[39m arg\u001b[38;5;241m.\u001b[39mmap(cache_array)\n\u001b[1;32m   1050\u001b[0m     \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m-> 1051\u001b[0m         values \u001b[38;5;241m=\u001b[39m \u001b[43mconvert_listlike\u001b[49m\u001b[43m(\u001b[49m\u001b[43marg\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_values\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43mformat\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1052\u001b[0m         result \u001b[38;5;241m=\u001b[39m arg\u001b[38;5;241m.\u001b[39m_constructor(values, index\u001b[38;5;241m=\u001b[39marg\u001b[38;5;241m.\u001b[39mindex, name\u001b[38;5;241m=\u001b[39marg\u001b[38;5;241m.\u001b[39mname)\n\u001b[1;32m   1053\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(arg, (ABCDataFrame, abc\u001b[38;5;241m.\u001b[39mMutableMapping)):\n",
      "File \u001b[0;32m/opt/homebrew/lib/python3.10/site-packages/pandas/core/tools/datetimes.py:402\u001b[0m, in \u001b[0;36m_convert_listlike_datetimes\u001b[0;34m(arg, format, name, tz, unit, errors, infer_datetime_format, dayfirst, yearfirst, exact)\u001b[0m\n\u001b[1;32m    400\u001b[0m \u001b[38;5;28;01massert\u001b[39;00m \u001b[38;5;28mformat\u001b[39m \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;129;01mor\u001b[39;00m infer_datetime_format\n\u001b[1;32m    401\u001b[0m utc \u001b[38;5;241m=\u001b[39m tz \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mutc\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m--> 402\u001b[0m result, tz_parsed \u001b[38;5;241m=\u001b[39m \u001b[43mobjects_to_datetime64ns\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m    403\u001b[0m \u001b[43m    \u001b[49m\u001b[43marg\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    404\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdayfirst\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdayfirst\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    405\u001b[0m \u001b[43m    \u001b[49m\u001b[43myearfirst\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43myearfirst\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    406\u001b[0m \u001b[43m    \u001b[49m\u001b[43mutc\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mutc\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    407\u001b[0m \u001b[43m    \u001b[49m\u001b[43merrors\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43merrors\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    408\u001b[0m \u001b[43m    \u001b[49m\u001b[43mrequire_iso8601\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mrequire_iso8601\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    409\u001b[0m \u001b[43m    \u001b[49m\u001b[43mallow_object\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mTrue\u001b[39;49;00m\u001b[43m,\u001b[49m\n\u001b[1;32m    410\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    412\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m tz_parsed \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m    413\u001b[0m     \u001b[38;5;66;03m# We can take a shortcut since the datetime64 numpy array\u001b[39;00m\n\u001b[1;32m    414\u001b[0m     \u001b[38;5;66;03m# is in UTC\u001b[39;00m\n\u001b[1;32m    415\u001b[0m     dta \u001b[38;5;241m=\u001b[39m DatetimeArray(result, dtype\u001b[38;5;241m=\u001b[39mtz_to_dtype(tz_parsed))\n",
      "File \u001b[0;32m/opt/homebrew/lib/python3.10/site-packages/pandas/core/arrays/datetimes.py:2242\u001b[0m, in \u001b[0;36mobjects_to_datetime64ns\u001b[0;34m(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object, allow_mixed)\u001b[0m\n\u001b[1;32m   2240\u001b[0m         \u001b[38;5;28;01mreturn\u001b[39;00m values\u001b[38;5;241m.\u001b[39mview(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mi8\u001b[39m\u001b[38;5;124m\"\u001b[39m), tz_parsed\n\u001b[1;32m   2241\u001b[0m     \u001b[38;5;28;01mexcept\u001b[39;00m (\u001b[38;5;167;01mValueError\u001b[39;00m, \u001b[38;5;167;01mTypeError\u001b[39;00m):\n\u001b[0;32m-> 2242\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m err\n\u001b[1;32m   2244\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m tz_parsed \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m   2245\u001b[0m     \u001b[38;5;66;03m# We can take a shortcut since the datetime64 numpy array\u001b[39;00m\n\u001b[1;32m   2246\u001b[0m     \u001b[38;5;66;03m#  is in UTC\u001b[39;00m\n\u001b[1;32m   2247\u001b[0m     \u001b[38;5;66;03m# Return i8 values to denote unix timestamps\u001b[39;00m\n\u001b[1;32m   2248\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m result\u001b[38;5;241m.\u001b[39mview(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mi8\u001b[39m\u001b[38;5;124m\"\u001b[39m), tz_parsed\n",
      "File \u001b[0;32m/opt/homebrew/lib/python3.10/site-packages/pandas/core/arrays/datetimes.py:2224\u001b[0m, in \u001b[0;36mobjects_to_datetime64ns\u001b[0;34m(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object, allow_mixed)\u001b[0m\n\u001b[1;32m   2222\u001b[0m order: Literal[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mF\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mC\u001b[39m\u001b[38;5;124m\"\u001b[39m] \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mF\u001b[39m\u001b[38;5;124m\"\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m flags\u001b[38;5;241m.\u001b[39mf_contiguous \u001b[38;5;28;01melse\u001b[39;00m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mC\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m   2223\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m-> 2224\u001b[0m     result, tz_parsed \u001b[38;5;241m=\u001b[39m \u001b[43mtslib\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43marray_to_datetime\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m   2225\u001b[0m \u001b[43m        \u001b[49m\u001b[43mdata\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mravel\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mK\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   2226\u001b[0m \u001b[43m        \u001b[49m\u001b[43merrors\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43merrors\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   2227\u001b[0m \u001b[43m        \u001b[49m\u001b[43mutc\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mutc\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   2228\u001b[0m \u001b[43m        \u001b[49m\u001b[43mdayfirst\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdayfirst\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   2229\u001b[0m \u001b[43m        \u001b[49m\u001b[43myearfirst\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43myearfirst\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   2230\u001b[0m \u001b[43m        \u001b[49m\u001b[43mrequire_iso8601\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mrequire_iso8601\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   2231\u001b[0m \u001b[43m        \u001b[49m\u001b[43mallow_mixed\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mallow_mixed\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m   2232\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   2233\u001b[0m     result \u001b[38;5;241m=\u001b[39m result\u001b[38;5;241m.\u001b[39mreshape(data\u001b[38;5;241m.\u001b[39mshape, order\u001b[38;5;241m=\u001b[39morder)\n\u001b[1;32m   2234\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m err:\n",
      "File \u001b[0;32m/opt/homebrew/lib/python3.10/site-packages/pandas/_libs/tslib.pyx:381\u001b[0m, in \u001b[0;36mpandas._libs.tslib.array_to_datetime\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32m/opt/homebrew/lib/python3.10/site-packages/pandas/_libs/tslib.pyx:613\u001b[0m, in \u001b[0;36mpandas._libs.tslib.array_to_datetime\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32m/opt/homebrew/lib/python3.10/site-packages/pandas/_libs/tslib.pyx:751\u001b[0m, in \u001b[0;36mpandas._libs.tslib._array_to_datetime_object\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32m/opt/homebrew/lib/python3.10/site-packages/pandas/_libs/tslib.pyx:742\u001b[0m, in \u001b[0;36mpandas._libs.tslib._array_to_datetime_object\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32m/opt/homebrew/lib/python3.10/site-packages/pandas/_libs/tslibs/parsing.pyx:281\u001b[0m, in \u001b[0;36mpandas._libs.tslibs.parsing.parse_datetime_string\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32m/opt/homebrew/lib/python3.10/site-packages/dateutil/parser/_parser.py:1368\u001b[0m, in \u001b[0;36mparse\u001b[0;34m(timestr, parserinfo, **kwargs)\u001b[0m\n\u001b[1;32m   1366\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m parser(parserinfo)\u001b[38;5;241m.\u001b[39mparse(timestr, \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mkwargs)\n\u001b[1;32m   1367\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m-> 1368\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mDEFAULTPARSER\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mparse\u001b[49m\u001b[43m(\u001b[49m\u001b[43mtimestr\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m/opt/homebrew/lib/python3.10/site-packages/dateutil/parser/_parser.py:643\u001b[0m, in \u001b[0;36mparser.parse\u001b[0;34m(self, timestr, default, ignoretz, tzinfos, **kwargs)\u001b[0m\n\u001b[1;32m    640\u001b[0m res, skipped_tokens \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_parse(timestr, \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mkwargs)\n\u001b[1;32m    642\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m res \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[0;32m--> 643\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m ParserError(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mUnknown string format: \u001b[39m\u001b[38;5;132;01m%s\u001b[39;00m\u001b[38;5;124m\"\u001b[39m, timestr)\n\u001b[1;32m    645\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mlen\u001b[39m(res) \u001b[38;5;241m==\u001b[39m \u001b[38;5;241m0\u001b[39m:\n\u001b[1;32m    646\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m ParserError(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mString does not contain a date: \u001b[39m\u001b[38;5;132;01m%s\u001b[39;00m\u001b[38;5;124m\"\u001b[39m, timestr)\n",
      "\u001b[0;31mParserError\u001b[0m: Unknown string format: a/11/2000"
     ]
    }
   ],
   "source": [
    "df['date'] = pd.to_datetime(df['date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3/10/2000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a/11/2000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3/12/2000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  value\n",
       "0  3/10/2000      2\n",
       "1  a/11/2000      3\n",
       "2  3/12/2000      4"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['date'] = pd.to_datetime(df['date'], errors='ignore')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000-03-10</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaT</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2000-03-12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  value\n",
       "0 2000-03-10      2\n",
       "1        NaT      3\n",
       "2 2000-03-12      4"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['date'] = pd.to_datetime(df['date'], errors='coerce')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Assemble a datetime from multiple columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'year': [2015, 2016],\n",
    "                   'month': [2, 3],\n",
    "                   'day': [4, 5]})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  day\n",
       "0  2015      2    4\n",
       "1  2016      3    5"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df['date'] = pd.to_datetime(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2015-02-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2016-03-05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  day       date\n",
       "0  2015      2    4 2015-02-04\n",
       "1  2016      3    5 2016-03-05"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Get year, month and day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'name': ['Tom', 'Andy', 'Lucas'],\n",
    "                 'DoB': ['08-05-1997', '04-28-1996', '12-16-1995']})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>DoB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>08-05-1997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andy</td>\n",
       "      <td>04-28-1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lucas</td>\n",
       "      <td>12-16-1995</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name         DoB\n",
       "0    Tom  08-05-1997\n",
       "1   Andy  04-28-1996\n",
       "2  Lucas  12-16-1995"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3 entries, 0 to 2\n",
      "Data columns (total 2 columns):\n",
      " #   Column  Non-Null Count  Dtype \n",
      "---  ------  --------------  ----- \n",
      " 0   name    3 non-null      object\n",
      " 1   DoB     3 non-null      object\n",
      "dtypes: object(2)\n",
      "memory usage: 176.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['DoB'] = pd.to_datetime(df['DoB'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3 entries, 0 to 2\n",
      "Data columns (total 2 columns):\n",
      " #   Column  Non-Null Count  Dtype         \n",
      "---  ------  --------------  -----         \n",
      " 0   name    3 non-null      object        \n",
      " 1   DoB     3 non-null      datetime64[ns]\n",
      "dtypes: datetime64[ns](1), object(1)\n",
      "memory usage: 176.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>DoB</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>1997-08-05</td>\n",
       "      <td>1997</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andy</td>\n",
       "      <td>1996-04-28</td>\n",
       "      <td>1996</td>\n",
       "      <td>4</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lucas</td>\n",
       "      <td>1995-12-16</td>\n",
       "      <td>1995</td>\n",
       "      <td>12</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name        DoB  year  month  day\n",
       "0    Tom 1997-08-05  1997      8    5\n",
       "1   Andy 1996-04-28  1996      4   28\n",
       "2  Lucas 1995-12-16  1995     12   16"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['year']= df['DoB'].dt.year\n",
    "df['month']= df['DoB'].dt.month\n",
    "df['day']= df['DoB'].dt.day\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Get the week of year, the day of week and leap year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/0n/nb074wzd4kl1nmdvkbdnpgym0000gn/T/ipykernel_11495/3969492849.py:1: FutureWarning: Series.dt.weekofyear and Series.dt.week have been deprecated. Please use Series.dt.isocalendar().week instead.\n",
      "  df['week_of_year'] = df['DoB'].dt.week\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>DoB</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>week_of_year</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>is_leap_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>1997-08-05</td>\n",
       "      <td>1997</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andy</td>\n",
       "      <td>1996-04-28</td>\n",
       "      <td>1996</td>\n",
       "      <td>4</td>\n",
       "      <td>28</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lucas</td>\n",
       "      <td>1995-12-16</td>\n",
       "      <td>1995</td>\n",
       "      <td>12</td>\n",
       "      <td>16</td>\n",
       "      <td>50</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name        DoB  year  month  day  week_of_year  day_of_week  is_leap_year\n",
       "0    Tom 1997-08-05  1997      8    5            32            1         False\n",
       "1   Andy 1996-04-28  1996      4   28            17            6          True\n",
       "2  Lucas 1995-12-16  1995     12   16            50            5         False"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['week_of_year'] = df['DoB'].dt.week\n",
    "df['day_of_week'] = df['DoB'].dt.dayofweek\n",
    "df['is_leap_year'] = df['DoB'].dt.is_leap_year\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1\n",
       "1    6\n",
       "2    5\n",
       "Name: DoB, dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['DoB'].dt.weekday"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>DoB</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>week_of_year</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>is_leap_year</th>\n",
       "      <th>day_of_week_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>1997-08-05</td>\n",
       "      <td>1997</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andy</td>\n",
       "      <td>1996-04-28</td>\n",
       "      <td>1996</td>\n",
       "      <td>4</td>\n",
       "      <td>28</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lucas</td>\n",
       "      <td>1995-12-16</td>\n",
       "      <td>1995</td>\n",
       "      <td>12</td>\n",
       "      <td>16</td>\n",
       "      <td>50</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name        DoB  year  month  day  week_of_year  day_of_week  \\\n",
       "0    Tom 1997-08-05  1997      8    5            32            1   \n",
       "1   Andy 1996-04-28  1996      4   28            17            6   \n",
       "2  Lucas 1995-12-16  1995     12   16            50            5   \n",
       "\n",
       "   is_leap_year day_of_week_name  \n",
       "0         False          Tuesday  \n",
       "1          True           Sunday  \n",
       "2         False         Saturday  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dw_mapping={\n",
    "    0: 'Monday', \n",
    "    1: 'Tuesday', \n",
    "    2: 'Wednesday', \n",
    "    3: 'Thursday', \n",
    "    4: 'Friday',\n",
    "    5: 'Saturday', \n",
    "    6: 'Sunday'\n",
    "} \n",
    "df['day_of_week_name']=df['DoB'].dt.weekday.map(dw_mapping)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Get the age from the date of birth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    26\n",
       "1    27\n",
       "2    28\n",
       "Name: DoB, dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.to_datetime('today').year - df['DoB'].dt.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1997\n",
       "1    1996\n",
       "2    1995\n",
       "Name: DoB, dtype: int64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>DoB</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>week_of_year</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>is_leap_year</th>\n",
       "      <th>day_of_week_name</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>1997-08-05</td>\n",
       "      <td>1997</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andy</td>\n",
       "      <td>1996-04-28</td>\n",
       "      <td>1996</td>\n",
       "      <td>4</td>\n",
       "      <td>28</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lucas</td>\n",
       "      <td>1995-12-16</td>\n",
       "      <td>1995</td>\n",
       "      <td>12</td>\n",
       "      <td>16</td>\n",
       "      <td>50</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name        DoB  year  month  day  week_of_year  day_of_week  \\\n",
       "0    Tom 1997-08-05  1997      8    5            32            1   \n",
       "1   Andy 1996-04-28  1996      4   28            17            6   \n",
       "2  Lucas 1995-12-16  1995     12   16            50            5   \n",
       "\n",
       "   is_leap_year day_of_week_name  age  \n",
       "0         False          Tuesday   26  \n",
       "1          True           Sunday   27  \n",
       "2         False         Saturday   28  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "today = pd.to_datetime('today')\n",
    "df['age'] = today.year - df['DoB'].dt.year\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>DoB</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>week_of_year</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>is_leap_year</th>\n",
       "      <th>day_of_week_name</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>1997-08-05</td>\n",
       "      <td>1997</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andy</td>\n",
       "      <td>1996-04-28</td>\n",
       "      <td>1996</td>\n",
       "      <td>4</td>\n",
       "      <td>28</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lucas</td>\n",
       "      <td>1995-12-16</td>\n",
       "      <td>1995</td>\n",
       "      <td>12</td>\n",
       "      <td>16</td>\n",
       "      <td>50</td>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name        DoB  year  month  day  week_of_year  day_of_week  \\\n",
       "0    Tom 1997-08-05  1997      8    5            32            1   \n",
       "1   Andy 1996-04-28  1996      4   28            17            6   \n",
       "2  Lucas 1995-12-16  1995     12   16            50            5   \n",
       "\n",
       "   is_leap_year day_of_week_name  age  \n",
       "0         False          Tuesday   25  \n",
       "1          True           Sunday   26  \n",
       "2         False         Saturday   27  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Year difference\n",
    "today = pd.to_datetime('today')\n",
    "diff_y = today.year - df['DoB'].dt.year\n",
    "\n",
    "\n",
    "# Haven't had birthday\n",
    "b_md = df['DoB'].apply(lambda x: (x.month,x.day) )\n",
    "no_birthday = b_md > (today.month,today.day)\n",
    "\n",
    "df['age'] = diff_y - no_birthday\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    True\n",
       "1    True\n",
       "2    True\n",
       "Name: DoB, dtype: bool"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "no_birthday"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. Improve performance by setting date column as the index "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1795144 entries, 0 to 1795143\n",
      "Data columns (total 3 columns):\n",
      " #   Column  Dtype         \n",
      "---  ------  -----         \n",
      " 0   date    datetime64[ns]\n",
      " 1   num     int64         \n",
      " 2   city    object        \n",
      "dtypes: datetime64[ns](1), int64(1), object(1)\n",
      "memory usage: 41.1+ MB\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv('data/city_sales.csv',parse_dates=['date'])\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.set_index(['date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2015-01-01 09:00:00</th>\n",
       "      <td>4</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-01-01 09:01:00</th>\n",
       "      <td>4</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-01-01 09:02:00</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-01-01 09:03:00</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-01-01 09:04:00</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-31 15:56:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-31 15:57:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-31 15:58:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-31 15:59:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-31 16:00:00</th>\n",
       "      <td>2</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1795144 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     num       city\n",
       "date                               \n",
       "2015-01-01 09:00:00    4     London\n",
       "2015-01-01 09:01:00    4     London\n",
       "2015-01-01 09:02:00    3     London\n",
       "2015-01-01 09:03:00    3     London\n",
       "2015-01-01 09:04:00    3     London\n",
       "...                  ...        ...\n",
       "2019-01-31 15:56:00    3  Cambridge\n",
       "2019-01-31 15:57:00    3  Cambridge\n",
       "2019-01-31 15:58:00    3  Cambridge\n",
       "2019-01-31 15:59:00    3  Cambridge\n",
       "2019-01-31 16:00:00    2  Cambridge\n",
       "\n",
       "[1795144 rows x 2 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. Select data with a specific year and perform aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01 09:00:00</th>\n",
       "      <td>2</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-01 09:01:00</th>\n",
       "      <td>1</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-01 09:02:00</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-01 09:03:00</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-01 09:04:00</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31 15:56:00</th>\n",
       "      <td>4</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31 15:57:00</th>\n",
       "      <td>2</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31 15:58:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31 15:59:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31 16:00:00</th>\n",
       "      <td>2</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>439524 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     num       city\n",
       "date                               \n",
       "2018-01-01 09:00:00    2     London\n",
       "2018-01-01 09:01:00    1     London\n",
       "2018-01-01 09:02:00    3     London\n",
       "2018-01-01 09:03:00    3     London\n",
       "2018-01-01 09:04:00    3     London\n",
       "...                  ...        ...\n",
       "2018-12-31 15:56:00    4  Cambridge\n",
       "2018-12-31 15:57:00    2  Cambridge\n",
       "2018-12-31 15:58:00    3  Cambridge\n",
       "2018-12-31 15:59:00    3  Cambridge\n",
       "2018-12-31 16:00:00    2  Cambridge\n",
       "\n",
       "[439524 rows x 2 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc['2018']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1231190"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc['2018','num'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/0n/nb074wzd4kl1nmdvkbdnpgym0000gn/T/ipykernel_11495/1368259670.py:1: FutureWarning: Indexing a DataFrame with a datetimelike index using a single string to slice the rows, like `frame[string]`, is deprecated and will be removed in a future version. Use `frame.loc[string]` instead.\n",
      "  df['2018'].groupby('city').sum()\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Cambridge</th>\n",
       "      <td>308428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Durham</th>\n",
       "      <td>307965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>London</th>\n",
       "      <td>307431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oxford</th>\n",
       "      <td>307366</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              num\n",
       "city             \n",
       "Cambridge  308428\n",
       "Durham     307965\n",
       "London     307431\n",
       "Oxford     307366"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['2018'].groupby('city').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8. Select data with a specific month or a specific day of the month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-05-01 09:00:00</th>\n",
       "      <td>1</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-01 09:01:00</th>\n",
       "      <td>4</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-01 09:02:00</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-01 09:03:00</th>\n",
       "      <td>2</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-01 09:04:00</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31 15:56:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31 15:57:00</th>\n",
       "      <td>4</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31 15:58:00</th>\n",
       "      <td>2</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31 15:59:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-31 16:00:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>38732 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     num       city\n",
       "date                               \n",
       "2018-05-01 09:00:00    1     London\n",
       "2018-05-01 09:01:00    4     London\n",
       "2018-05-01 09:02:00    3     London\n",
       "2018-05-01 09:03:00    2     London\n",
       "2018-05-01 09:04:00    3     London\n",
       "...                  ...        ...\n",
       "2018-05-31 15:56:00    3  Cambridge\n",
       "2018-05-31 15:57:00    4  Cambridge\n",
       "2018-05-31 15:58:00    2  Cambridge\n",
       "2018-05-31 15:59:00    3  Cambridge\n",
       "2018-05-31 16:00:00    3  Cambridge\n",
       "\n",
       "[38732 rows x 2 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc['2018-5']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-05-01 09:00:00</th>\n",
       "      <td>1</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-01 09:01:00</th>\n",
       "      <td>4</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-01 09:02:00</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-01 09:03:00</th>\n",
       "      <td>2</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-01 09:04:00</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-01 15:56:00</th>\n",
       "      <td>2</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-01 15:57:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-01 15:58:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-01 15:59:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-01 16:00:00</th>\n",
       "      <td>1</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1684 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     num       city\n",
       "date                               \n",
       "2018-05-01 09:00:00    1     London\n",
       "2018-05-01 09:01:00    4     London\n",
       "2018-05-01 09:02:00    3     London\n",
       "2018-05-01 09:03:00    2     London\n",
       "2018-05-01 09:04:00    3     London\n",
       "...                  ...        ...\n",
       "2018-05-01 15:56:00    2  Cambridge\n",
       "2018-05-01 15:57:00    3  Cambridge\n",
       "2018-05-01 15:58:00    3  Cambridge\n",
       "2018-05-01 15:59:00    3  Cambridge\n",
       "2018-05-01 16:00:00    1  Cambridge\n",
       "\n",
       "[1684 rows x 2 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc['2018-5-1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "cond = df.index.month==3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([False, False, False, ..., False, False, False])"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cond"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2015-03-02 09:00:00</th>\n",
       "      <td>1</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-02 09:01:00</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-02 09:02:00</th>\n",
       "      <td>2</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-02 09:03:00</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-02 09:04:00</th>\n",
       "      <td>1</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-30 15:56:00</th>\n",
       "      <td>4</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-30 15:57:00</th>\n",
       "      <td>1</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-30 15:58:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-30 15:59:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-30 16:00:00</th>\n",
       "      <td>4</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>151560 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     num       city\n",
       "date                               \n",
       "2015-03-02 09:00:00    1     London\n",
       "2015-03-02 09:01:00    3     London\n",
       "2015-03-02 09:02:00    2     London\n",
       "2015-03-02 09:03:00    3     London\n",
       "2015-03-02 09:04:00    1     London\n",
       "...                  ...        ...\n",
       "2018-03-30 15:56:00    4  Cambridge\n",
       "2018-03-30 15:57:00    1  Cambridge\n",
       "2018-03-30 15:58:00    3  Cambridge\n",
       "2018-03-30 15:59:00    3  Cambridge\n",
       "2018-03-30 16:00:00    4  Cambridge\n",
       "\n",
       "[151560 rows x 2 columns]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[cond]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2015-02-02 09:00:00</th>\n",
       "      <td>4</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-02-02 09:01:00</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-02-02 09:02:00</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-02-02 09:03:00</th>\n",
       "      <td>2</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-02-02 09:04:00</th>\n",
       "      <td>1</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28 15:56:00</th>\n",
       "      <td>4</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28 15:57:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28 15:58:00</th>\n",
       "      <td>4</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28 15:59:00</th>\n",
       "      <td>4</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-28 16:00:00</th>\n",
       "      <td>4</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>136404 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     num       city\n",
       "date                               \n",
       "2015-02-02 09:00:00    4     London\n",
       "2015-02-02 09:01:00    3     London\n",
       "2015-02-02 09:02:00    3     London\n",
       "2015-02-02 09:03:00    2     London\n",
       "2015-02-02 09:04:00    1     London\n",
       "...                  ...        ...\n",
       "2018-02-28 15:56:00    4  Cambridge\n",
       "2018-02-28 15:57:00    3  Cambridge\n",
       "2018-02-28 15:58:00    4  Cambridge\n",
       "2018-02-28 15:59:00    4  Cambridge\n",
       "2018-02-28 16:00:00    4  Cambridge\n",
       "\n",
       "[136404 rows x 2 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cond = df.index.month==2\n",
    "df[cond]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9. Select data between two dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/0n/nb074wzd4kl1nmdvkbdnpgym0000gn/T/ipykernel_11495/380412996.py:1: FutureWarning: Value based partial slicing on non-monotonic DatetimeIndexes with non-existing keys is deprecated and will raise a KeyError in a future Version.\n",
      "  df.loc['2016' : '2018']\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016-01-01 09:00:00</th>\n",
       "      <td>4</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-01 09:01:00</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-01 09:02:00</th>\n",
       "      <td>4</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-01 09:03:00</th>\n",
       "      <td>4</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-01-01 09:04:00</th>\n",
       "      <td>2</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31 15:56:00</th>\n",
       "      <td>4</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31 15:57:00</th>\n",
       "      <td>2</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31 15:58:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31 15:59:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31 16:00:00</th>\n",
       "      <td>2</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1316888 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     num       city\n",
       "date                               \n",
       "2016-01-01 09:00:00    4     London\n",
       "2016-01-01 09:01:00    3     London\n",
       "2016-01-01 09:02:00    4     London\n",
       "2016-01-01 09:03:00    4     London\n",
       "2016-01-01 09:04:00    2     London\n",
       "...                  ...        ...\n",
       "2018-12-31 15:56:00    4  Cambridge\n",
       "2018-12-31 15:57:00    2  Cambridge\n",
       "2018-12-31 15:58:00    3  Cambridge\n",
       "2018-12-31 15:59:00    3  Cambridge\n",
       "2018-12-31 16:00:00    2  Cambridge\n",
       "\n",
       "[1316888 rows x 2 columns]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc['2016' : '2018']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-05-02 10:00:00</th>\n",
       "      <td>2</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-02 10:01:00</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-02 10:02:00</th>\n",
       "      <td>4</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-02 10:03:00</th>\n",
       "      <td>4</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-02 10:04:00</th>\n",
       "      <td>4</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-02 11:55:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-02 11:56:00</th>\n",
       "      <td>3</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-02 11:57:00</th>\n",
       "      <td>4</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-02 11:58:00</th>\n",
       "      <td>4</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-02 11:59:00</th>\n",
       "      <td>1</td>\n",
       "      <td>Cambridge</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>480 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     num       city\n",
       "date                               \n",
       "2018-05-02 10:00:00    2     London\n",
       "2018-05-02 10:01:00    3     London\n",
       "2018-05-02 10:02:00    4     London\n",
       "2018-05-02 10:03:00    4     London\n",
       "2018-05-02 10:04:00    4     London\n",
       "...                  ...        ...\n",
       "2018-05-02 11:55:00    3  Cambridge\n",
       "2018-05-02 11:56:00    3  Cambridge\n",
       "2018-05-02 11:57:00    4  Cambridge\n",
       "2018-05-02 11:58:00    4  Cambridge\n",
       "2018-05-02 11:59:00    1  Cambridge\n",
       "\n",
       "[480 rows x 2 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc['2018-5-2 10' : '2018-5-2 11' ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc['2018-5-2 10:30' : '2018-5-2 10:45' ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.between_time('10:30','10:45')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['rolling_sum_backfilled'] = df['rolling_sum'].fillna(method='backfill')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}