pandas_date_examples.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "742b6d36",
   "metadata": {},
   "source": [
    "# Working with Dates in Pandas\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d0b22616",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from random import randint\n",
    "from datetime import timedelta, date"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abf7ec9e",
   "metadata": {},
   "source": [
    "## Date Ranges \n",
    "- `pandas.date_range(start, end)` is the simplest use which produces a `DatetimeIndex` which can be converted to a Series object\n",
    "    - see [the official documentation](https://pandas.pydata.org/docs/reference/api/pandas.date_range.html)\n",
    "\n",
    "### Date Range Examples"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06497a20",
   "metadata": {},
   "source": [
    "#### Date Range from Date Strings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d49c52a1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2000-01-01', '2000-01-02', '2000-01-03'], dtype='datetime64[ns]', freq='D')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Daily Range\n",
    "dr = pd.date_range('2000-01-01', '2000-01-03')\n",
    "dr"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0051c01f",
   "metadata": {},
   "source": [
    "#### From Date Objects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4355fc96",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2000-01-01', '2000-01-02', '2000-01-03'], dtype='datetime64[ns]', freq='D')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Daily Range from Date types\n",
    "dr = pd.date_range(date(2000, 1, 1), date(2000, 1, 3))\n",
    "dr"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "250ce0d2",
   "metadata": {},
   "source": [
    "#### With a Monthly Frequency\n",
    "See [frequency string](https://pandas.pydata.org/pandas-docs/dev/user_guide/timeseries.html#timeseries-offset-aliases) documentation for more information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "425ead3f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2000-01-31', '2000-02-29', '2000-03-31'], dtype='datetime64[ns]', freq='M')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Month end Frequency\n",
    "dr = pd.date_range(start='2000-01-01', end='2000-04-01', freq='M')\n",
    "dr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f33acd31",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2000-01-31', '2000-02-29', '2000-03-31'], dtype='datetime64[ns]', freq='M')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Month end Frequency with a number of periods\n",
    "dr = pd.date_range(start='2000-01-01', periods=3, freq='M')\n",
    "dr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9f330fae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2000-01-01', '2000-02-01', '2000-03-01'], dtype='datetime64[ns]', freq='MS')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Month begin Frequency with a number of periods\n",
    "dr = pd.date_range(start='2000-01-01', periods=3, freq='MS')\n",
    "dr"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00c81a23",
   "metadata": {},
   "source": [
    "#### With a Weekly Frequency\n",
    "- Always note which day weeks start on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "adc296aa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2000-01-02', '2000-01-09', '2000-01-16'], dtype='datetime64[ns]', freq='W-SUN')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Weekly \n",
    "dr = pd.date_range(start='2000-01-01', periods=3, freq='W')\n",
    "dr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ca6ef452",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Sunday', 'Sunday', 'Sunday'], dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# DateTimeIndex objects have a day_name() method (locale specific)\n",
    "dr.day_name()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "59e1ab96",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2000-01-03', '2000-01-10', '2000-01-17'], dtype='datetime64[ns]', freq='W-MON')"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Weekly with a Monday start date\n",
    "dr = pd.date_range(start='2000-01-01', periods=3, freq='W-MON')\n",
    "dr"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "977cc73a",
   "metadata": {},
   "source": [
    "## Dates in Dataframe Columns\n",
    "Here, we create a simple dataframe for the purpose of demonstrating date methods on dataframe columns.\n",
    "\n",
    "The official documentation extensive examples for:\n",
    "- [datetime-like attributes](https://pandas.pydata.org/docs/reference/series.html#datetime-properties)\n",
    "- [datetime-like methods](https://pandas.pydata.org/docs/reference/series.html#datetime-methods)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "b4ddd1df",
   "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>event</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>2000-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>2000-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>2000-01-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>2000-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E</td>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>2000-01-09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  event start_date   end_date\n",
       "0     A 2000-01-01 2000-01-03\n",
       "1     B 2000-01-02 2000-01-03\n",
       "2     C 2000-01-03 2000-01-05\n",
       "3     D 2000-01-04 2000-01-08\n",
       "4     E 2000-01-05 2000-01-09"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# DatetimeIndex\n",
    "start_date = pd.date_range('2000-01-01', '2000-01-05')\n",
    "\n",
    "# Some random timedeltas (in days)\n",
    "days = pd.Series([timedelta(days=randint(0, 4)) for _ in range(5)])\n",
    "\n",
    "# Add the timedeltas to the index.  This produces a pd.Series\n",
    "end_date = start_date + days\n",
    "\n",
    "# Create a pd.DataFrame adding column names and a labeled 'event' column\n",
    "df = pd.DataFrame(\n",
    "    {\n",
    "        'event': list('ABCDE'),\n",
    "        'start_date': start_date,\n",
    "        'end_date': end_date,\n",
    "    }\n",
    ")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f155199a",
   "metadata": {},
   "source": [
    "### Add Day Name Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "383ecb55",
   "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>event</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>start_day</th>\n",
       "      <th>end_day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>Monday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>Monday</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>2000-01-08</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E</td>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>2000-01-09</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  event start_date   end_date  start_day    end_day\n",
       "0     A 2000-01-01 2000-01-03   Saturday     Monday\n",
       "1     B 2000-01-02 2000-01-03     Sunday     Monday\n",
       "2     C 2000-01-03 2000-01-05     Monday  Wednesday\n",
       "3     D 2000-01-04 2000-01-08    Tuesday   Saturday\n",
       "4     E 2000-01-05 2000-01-09  Wednesday     Sunday"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = (\n",
    "    df\n",
    "    .assign(start_day=df.start_date.dt.day_name())\n",
    "    .assign(end_day=df.end_date.dt.day_name())\n",
    ")\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "a03fd908",
   "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>event</th>\n",
       "      <th>start_day</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_day</th>\n",
       "      <th>end_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2000-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2000-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>2000-01-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2000-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2000-01-09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  event  start_day start_date    end_day   end_date\n",
       "0     A   Saturday 2000-01-01     Monday 2000-01-03\n",
       "1     B     Sunday 2000-01-02     Monday 2000-01-03\n",
       "2     C     Monday 2000-01-03  Wednesday 2000-01-05\n",
       "3     D    Tuesday 2000-01-04   Saturday 2000-01-08\n",
       "4     E  Wednesday 2000-01-05     Sunday 2000-01-09"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# re-order columns\n",
    "df2 = df2[['event', 'start_day', 'start_date',\n",
    "           'end_day', 'end_date',          \n",
    "]]\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87ff2246",
   "metadata": {},
   "source": [
    "### Add an event length column\n",
    "Determine the duration of an event in days and add it to the dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "b51acc5d",
   "metadata": {
    "scrolled": true
   },
   "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>event</th>\n",
       "      <th>start_day</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_day</th>\n",
       "      <th>end_date</th>\n",
       "      <th>event_duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2000-01-08</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2000-01-09</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  event  start_day start_date    end_day   end_date  event_duration\n",
       "0     A   Saturday 2000-01-01     Monday 2000-01-03               2\n",
       "1     B     Sunday 2000-01-02     Monday 2000-01-03               1\n",
       "2     C     Monday 2000-01-03  Wednesday 2000-01-05               2\n",
       "3     D    Tuesday 2000-01-04   Saturday 2000-01-08               4\n",
       "4     E  Wednesday 2000-01-05     Sunday 2000-01-09               4"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    df2\n",
    "    .assign(event_duration=(df2.end_date - df2.start_date).dt.days)\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "80ae43e6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5 entries, 0 to 4\n",
      "Data columns (total 6 columns):\n",
      " #   Column          Non-Null Count  Dtype         \n",
      "---  ------          --------------  -----         \n",
      " 0   event           5 non-null      object        \n",
      " 1   start_day       5 non-null      object        \n",
      " 2   start_date      5 non-null      datetime64[ns]\n",
      " 3   end_day         5 non-null      object        \n",
      " 4   end_date        5 non-null      datetime64[ns]\n",
      " 5   event_duration  5 non-null      int64         \n",
      "dtypes: datetime64[ns](2), int64(1), object(3)\n",
      "memory usage: 368.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "# Here it is again, but with info chained to the end to show dtypes\n",
    "(\n",
    "    df2\n",
    "    .assign(event_duration=(df2.end_date - df2.start_date).dt.days)\n",
    "    .info()\n",
    ")\n",
    "# Note that event_duration is an integer dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58c4fb4f",
   "metadata": {},
   "source": [
    "#### How it works\n",
    "- `df2.end_date` is a `pd.Series` with `dtype` `datetime`. \n",
    "- `df2.end_date - df2.start_date` is a `pd.Series` with `dtype` `timedelta`.\n",
    "- `timedelta` [attributes](https://pandas.pydata.org/docs/reference/series.html#timedelta-properties) provide access to the time difference in days. \n",
    "\n",
    "We can see the build up in the next few cells."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "a6ced75a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2 days\n",
       "1   1 days\n",
       "2   2 days\n",
       "3   4 days\n",
       "4   4 days\n",
       "dtype: timedelta64[ns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.end_date - df2.start_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "9ee549f4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2 days\n",
       "1   1 days\n",
       "2   2 days\n",
       "3   4 days\n",
       "4   4 days\n",
       "dtype: timedelta64[ns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "event_timedelta = df2.end_date - df2.start_date\n",
    "event_timedelta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "6cf30cd8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2\n",
       "1    1\n",
       "2    2\n",
       "3    4\n",
       "4    4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# use dt methods on Series with timedelta types.\n",
    "event_timedelta.dt.days"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4b7762f",
   "metadata": {},
   "source": [
    "### Combine all operations using Chained Methods\n",
    "On the original dataframe..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "88a240ac",
   "metadata": {
    "scrolled": true
   },
   "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>event</th>\n",
       "      <th>start_day</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_day</th>\n",
       "      <th>end_date</th>\n",
       "      <th>event_duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2000-01-08</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2000-01-09</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  event  start_day start_date    end_day   end_date  event_duration\n",
       "0     A   Saturday 2000-01-01     Monday 2000-01-03               2\n",
       "1     B     Sunday 2000-01-02     Monday 2000-01-03               1\n",
       "2     C     Monday 2000-01-03  Wednesday 2000-01-05               2\n",
       "3     D    Tuesday 2000-01-04   Saturday 2000-01-08               4\n",
       "4     E  Wednesday 2000-01-05     Sunday 2000-01-09               4"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = ['event', 'start_day', 'start_date',\n",
    "           'end_day', 'end_date', 'event_duration']\n",
    "\n",
    "(\n",
    "    df\n",
    "    .assign(start_day=df.start_date.dt.day_name())\n",
    "    .assign(end_day=df.end_date.dt.day_name())\n",
    "    .assign(event_duration=(df.end_date - df.start_date).dt.days)\n",
    "    .reindex(columns=columns) # Change the order of the columns\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53669751",
   "metadata": {},
   "source": [
    "## Reading and Writing Data to CSV Files\n",
    "This section focuses on a few tasks:\n",
    "- writing a dataframe to CSV file\n",
    "- reading a CSV file with dates\n",
    "- writing a subset of columns to a CSV file\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f29df628",
   "metadata": {},
   "source": [
    "### Writing a DataFrame to CSV\n",
    "Use the `to_csv()` method of a DataFrame, but be sure to pass `index=False` to prevent the integer index from being written to output. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "5714e5e5",
   "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>event</th>\n",
       "      <th>start_day</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_day</th>\n",
       "      <th>end_date</th>\n",
       "      <th>event_duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2000-01-08</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2000-01-09</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  event  start_day start_date    end_day   end_date  event_duration\n",
       "0     A   Saturday 2000-01-01     Monday 2000-01-03               2\n",
       "1     B     Sunday 2000-01-02     Monday 2000-01-03               1\n",
       "2     C     Monday 2000-01-03  Wednesday 2000-01-05               2\n",
       "3     D    Tuesday 2000-01-04   Saturday 2000-01-08               4\n",
       "4     E  Wednesday 2000-01-05     Sunday 2000-01-09               4"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Here's the DF we will work with\n",
    "columns = ['event', 'start_day', 'start_date',\n",
    "           'end_day', 'end_date', 'event_duration']\n",
    "\n",
    "df_out = (\n",
    "    df\n",
    "    .assign(start_day=df.start_date.dt.day_name())\n",
    "    .assign(end_day=df.end_date.dt.day_name())\n",
    "    .assign(event_duration=(df.end_date - df.start_date).dt.days)\n",
    "    .reindex(columns=columns) # Change the order of the columns\n",
    ")\n",
    "df_out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "3c2ca902",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write to CSV output.csv\n",
    "df_out.to_csv('output.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0537f64",
   "metadata": {},
   "source": [
    "### Inspect the result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "77117255",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "event,start_day,start_date,end_day,end_date,event_duration\r\n",
      "A,Saturday,2000-01-01,Monday,2000-01-03,2\r\n",
      "B,Sunday,2000-01-02,Monday,2000-01-03,1\r\n",
      "C,Monday,2000-01-03,Wednesday,2000-01-05,2\r\n",
      "D,Tuesday,2000-01-04,Saturday,2000-01-08,4\r\n",
      "E,Wednesday,2000-01-05,Sunday,2000-01-09,4\r\n"
     ]
    }
   ],
   "source": [
    "%cat output.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f351266",
   "metadata": {},
   "source": [
    "### Reading a CSV with Dates\n",
    "Be sure to pass the `parse_date` kwarg with a list of date columns.\n",
    "\n",
    "Here, the `output.csv` file from above is read into a DataFrame. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "2a9f63dc",
   "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>event</th>\n",
       "      <th>start_day</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_day</th>\n",
       "      <th>end_date</th>\n",
       "      <th>event_duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2000-01-08</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>E</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2000-01-09</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  event  start_day start_date    end_day   end_date  event_duration\n",
       "0     A   Saturday 2000-01-01     Monday 2000-01-03               2\n",
       "1     B     Sunday 2000-01-02     Monday 2000-01-03               1\n",
       "2     C     Monday 2000-01-03  Wednesday 2000-01-05               2\n",
       "3     D    Tuesday 2000-01-04   Saturday 2000-01-08               4\n",
       "4     E  Wednesday 2000-01-05     Sunday 2000-01-09               4"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read a CSV file with dates\n",
    "df_in = pd.read_csv('output.csv', parse_dates=['start_date', 'end_date'])\n",
    "df_in"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3bc152a1",
   "metadata": {},
   "source": [
    "We can see that the dates were parsed as date types by using `.info()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "7419353d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5 entries, 0 to 4\n",
      "Data columns (total 6 columns):\n",
      " #   Column          Non-Null Count  Dtype         \n",
      "---  ------          --------------  -----         \n",
      " 0   event           5 non-null      object        \n",
      " 1   start_day       5 non-null      object        \n",
      " 2   start_date      5 non-null      datetime64[ns]\n",
      " 3   end_day         5 non-null      object        \n",
      " 4   end_date        5 non-null      datetime64[ns]\n",
      " 5   event_duration  5 non-null      int64         \n",
      "dtypes: datetime64[ns](2), int64(1), object(3)\n",
      "memory usage: 368.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df_in.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c36f670c",
   "metadata": {},
   "source": [
    "### Writing a subset of columns to CSV\n",
    "What if we only wanted to write part of the dataframe out to CSV.  This can be done by indexing the dataframe columns with a `list` of column names. \n",
    "\n",
    "Here, we only want to write out the `event` and `event_duration` columns. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "006ff3c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# subset the DF and write to CSV\n",
    "columns_to_write = ['event', 'event_duration']\n",
    "\n",
    "df_out[columns_to_write].to_csv('output-subset.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "5a79a5fe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "event,event_duration\r\n",
      "A,2\r\n",
      "B,1\r\n",
      "C,2\r\n",
      "D,4\r\n",
      "E,4\r\n"
     ]
    }
   ],
   "source": [
    "%cat output-subset.csv"
   ]
  }
 ],
 "metadata": {
  "hide_input": false,
  "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.0"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}