6_dealing_with_dates.ipynb

{
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python",
      "version": "3.6.6",
      "mimetype": "text/x-python",
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "pygments_lexer": "ipython3",
      "nbconvert_exporter": "python",
      "file_extension": ".py"
    },
    "kaggle": {
      "accelerator": "none",
      "dataSources": [
        {
          "sourceId": 3136,
          "databundleVersionId": 26502,
          "sourceType": "competition"
        },
        {
          "sourceId": 291460,
          "sourceType": "datasetVersion",
          "datasetId": 121055
        }
      ],
      "dockerImageVersionId": 24140,
      "isInternetEnabled": false,
      "language": "python",
      "sourceType": "notebook",
      "isGpuEnabled": false
    },
    "colab": {
      "provenance": []
    }
  },
  "nbformat_minor": 0,
  "nbformat": 4,
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "# Dealing With Dates\n"
      ],
      "metadata": {
        "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5",
        "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
        "trusted": true,
        "id": "ELVk98DuIizF"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "In the last two lessons, we learned a variety of methods to text character and numeric data, but many data sets also contain dates that don't fit nicely into either category. Common date formats contain numbers and sometimes text as well to specify months and days. Getting dates into a friendly format and extracting features of dates like month and year into new variables can be useful preprocessing steps.\n",
        "\n",
        "For this lesson I've created and uploaded some dummy date data in a few different formats. Let's read in the date data:"
      ],
      "metadata": {
        "id": "Tpo350efIizJ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import numpy as np\n",
        "import pandas as pd"
      ],
      "metadata": {
        "execution": {
          "iopub.status.busy": "2024-08-23T13:23:57.495348Z",
          "iopub.execute_input": "2024-08-23T13:23:57.495666Z",
          "iopub.status.idle": "2024-08-23T13:23:57.499819Z",
          "shell.execute_reply.started": "2024-08-23T13:23:57.495618Z",
          "shell.execute_reply": "2024-08-23T13:23:57.498838Z"
        },
        "trusted": true,
        "id": "Ey9Lgqb7IizK"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "dates = pd.read_csv(\"../input/lesson-16-dates/dates_lesson_16.csv\")\n",
        "\n",
        "dates # Check the dates"
      ],
      "metadata": {
        "execution": {
          "iopub.status.busy": "2024-08-23T13:23:57.501234Z",
          "iopub.execute_input": "2024-08-23T13:23:57.501529Z",
          "iopub.status.idle": "2024-08-23T13:23:57.572391Z",
          "shell.execute_reply.started": "2024-08-23T13:23:57.501474Z",
          "shell.execute_reply": "2024-08-23T13:23:57.571152Z"
        },
        "trusted": true,
        "id": "z2qj4xEBIizL",
        "outputId": "8e8f459b-a485-4ca1-9ae0-34b530aa1183"
      },
      "execution_count": null,
      "outputs": [
        {
          "execution_count": 3,
          "output_type": "execute_result",
          "data": {
            "text/plain": "  month_day_year      ...       year_month_day\n0       04/22/96      ...           2007-06-22\n1       04/23/96      ...           2017-01-09\n2       05/14/96      ...           1998-04-12\n3       05/15/96      ...           2027-07-22\n4       05/16/01      ...           1945-11-15\n5       05/17/02      ...           1942-06-22\n6       05/18/03      ...           1887-06-13\n7       05/19/04      ...           1912-01-25\n8       05/20/05      ...           2007-06-22\n\n[9 rows x 4 columns]",
            "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>month_day_year</th>\n      <th>day_month_year</th>\n      <th>date_time</th>\n      <th>year_month_day</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>04/22/96</td>\n      <td>22-Apr-96</td>\n      <td>Tue Aug 11 09:50:35 1996</td>\n      <td>2007-06-22</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>04/23/96</td>\n      <td>23-Apr-96</td>\n      <td>Tue May 12 19:50:35 2016</td>\n      <td>2017-01-09</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>05/14/96</td>\n      <td>14-May-96</td>\n      <td>Mon Oct 14 09:50:35 2017</td>\n      <td>1998-04-12</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>05/15/96</td>\n      <td>15-May-96</td>\n      <td>Tue Jan 11 09:50:35 2018</td>\n      <td>2027-07-22</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>05/16/01</td>\n      <td>16-May-01</td>\n      <td>Fri Mar 11 07:30:36 2019</td>\n      <td>1945-11-15</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>05/17/02</td>\n      <td>17-May-02</td>\n      <td>Tue Aug 11 09:50:35 2020</td>\n      <td>1942-06-22</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>05/18/03</td>\n      <td>18-May-03</td>\n      <td>Wed Dec 21 09:50:35 2021</td>\n      <td>1887-06-13</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>05/19/04</td>\n      <td>19-May-04</td>\n      <td>Tue Jan 11 09:50:35 2022</td>\n      <td>1912-01-25</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>05/20/05</td>\n      <td>20-May-05</td>\n      <td>Sun Jul 10 19:40:25 2023</td>\n      <td>2007-06-22</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "*Note the date data is listed as \"lesson 16\" instead of 17 because I used the same data set for lesson 16 of my intro to R guide.*\n",
        "\n",
        "When you load data with Pandas, dates are typically loaded as strings by default. Let's check the type of data in each column:"
      ],
      "metadata": {
        "id": "-nGV3k14IizN"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "for col in dates:\n",
        "    print (type(dates[col][1]))"
      ],
      "metadata": {
        "execution": {
          "iopub.status.busy": "2024-08-23T13:25:11.511275Z",
          "iopub.execute_input": "2024-08-23T13:25:11.511611Z",
          "iopub.status.idle": "2024-08-23T13:25:11.516807Z",
          "shell.execute_reply.started": "2024-08-23T13:25:11.511546Z",
          "shell.execute_reply": "2024-08-23T13:25:11.515861Z"
        },
        "trusted": true,
        "id": "SUG9ACcbIizN",
        "outputId": "273459e0-69cb-4959-b652-69bbc64235c2"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": "<class 'str'>\n<class 'str'>\n<class 'str'>\n<class 'str'>\n",
          "output_type": "stream"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "The output confirms that all the date data is currently in string form. To work with dates, we need to convert them from strings into a data format built for processing dates. The pandas library comes with a Timestamp data object for storing and working with dates. You can instruct pandas to automatically convert a date column in your data into Timestamps when you read your data by adding the \"parse_dates\" argument to the data reading function with a list of column indices indicated the columns you wish to convert to Timestamps. Let's re-read the data with parse_dates turned on for each column:"
      ],
      "metadata": {
        "id": "m3dKjA9xIizO"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "dates = pd.read_csv(\"../input/lesson-16-dates/dates_lesson_16.csv\",\n",
        "                    parse_dates=[0,1,2,3]) # Convert cols to Timestamp"
      ],
      "metadata": {
        "execution": {
          "iopub.status.busy": "2024-08-23T13:26:23.889111Z",
          "iopub.execute_input": "2024-08-23T13:26:23.889529Z",
          "iopub.status.idle": "2024-08-23T13:26:23.911482Z",
          "shell.execute_reply.started": "2024-08-23T13:26:23.889471Z",
          "shell.execute_reply": "2024-08-23T13:26:23.910499Z"
        },
        "trusted": true,
        "id": "0o9Uz2x1IizO"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Now let's check the data types again:"
      ],
      "metadata": {
        "id": "xwt8P02qIizP"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "for col in dates:\n",
        "    print (type(dates[col][1]))"
      ],
      "metadata": {
        "execution": {
          "iopub.status.busy": "2024-08-23T13:26:29.513406Z",
          "iopub.execute_input": "2024-08-23T13:26:29.513742Z",
          "iopub.status.idle": "2024-08-23T13:26:29.519597Z",
          "shell.execute_reply.started": "2024-08-23T13:26:29.513675Z",
          "shell.execute_reply": "2024-08-23T13:26:29.518535Z"
        },
        "trusted": true,
        "id": "Ytii0Mt9IizP",
        "outputId": "0258f348-bcbc-47fc-88c6-015c933ee2b1"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": "<class 'pandas._libs.tslibs.timestamps.Timestamp'>\n<class 'pandas._libs.tslibs.timestamps.Timestamp'>\n<class 'pandas._libs.tslibs.timestamps.Timestamp'>\n<class 'pandas._libs.tslibs.timestamps.Timestamp'>\n",
          "output_type": "stream"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "The output shows that all 4 columns were successfully parsed and translated into Timestamps. The default date parser works on many common date formats. You can also convert date strings to Timestamps using the function pd.to_datetime().\n",
        "\n",
        "If you have oddly formatted date time objects, you might have to specify the exact format to get it to convert correctly into a Timestamp. For instance, consider a date format that gives date times of the form hour:minute:second year-day-month:"
      ],
      "metadata": {
        "id": "Rrt1vmH4IizP"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "odd_date = \"12:30:15 2015-29-11\""
      ],
      "metadata": {
        "execution": {
          "iopub.status.busy": "2024-08-23T13:27:27.243313Z",
          "iopub.execute_input": "2024-08-23T13:27:27.243637Z",
          "iopub.status.idle": "2024-08-23T13:27:27.248310Z",
          "shell.execute_reply.started": "2024-08-23T13:27:27.243589Z",
          "shell.execute_reply": "2024-08-23T13:27:27.247076Z"
        },
        "trusted": true,
        "id": "yVhnHGn5IizQ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "The default to_datetime parser will fail to convert this date because it expects dates in the form year-month-day. In cases like this, specify the date's format to convert it to Timestamp:"
      ],
      "metadata": {
        "id": "hy7KYaKmIizQ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "pd.to_datetime(odd_date,\n",
        "               format= \"%H:%M:%S %Y-%d-%m\")"
      ],
      "metadata": {
        "execution": {
          "iopub.status.busy": "2024-08-23T13:27:50.415983Z",
          "iopub.execute_input": "2024-08-23T13:27:50.416641Z",
          "iopub.status.idle": "2024-08-23T13:27:50.426575Z",
          "shell.execute_reply.started": "2024-08-23T13:27:50.416576Z",
          "shell.execute_reply": "2024-08-23T13:27:50.425415Z"
        },
        "trusted": true,
        "id": "RPLzsSoOIizQ",
        "outputId": "4e1fc096-70d3-482d-b697-9b1e803a5885"
      },
      "execution_count": null,
      "outputs": [
        {
          "execution_count": 10,
          "output_type": "execute_result",
          "data": {
            "text/plain": "Timestamp('2015-11-29 12:30:15')"
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "As seen above, date formatting uses special formatting codes for each part of the date. For instance, %H represents hours and %Y represents the four digit year. View a list of formatting codes here.\n",
        "\n",
        "Once you have your dates in the Timestamp format, you can extract a variety of properties like the year, month and day. Converting dates into several simpler features can make the data easier to analyze and use in predictive models. Access date properties from a Series of Timestamps with the syntax: Series.dt.property. To illustrate, let's extract some features from the first column of our date data and put them in a new DataFrame:"
      ],
      "metadata": {
        "id": "d4Nm5X96IizQ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "column_1 = dates.iloc[:,0]\n",
        "print(column_1)\n",
        "\n",
        "pd.DataFrame({\"year\": column_1.dt.year,\n",
        "              \"month\": column_1.dt.month,\n",
        "              \"day\": column_1.dt.day,\n",
        "              \"hour\": column_1.dt.hour,\n",
        "              \"dayofyear\": column_1.dt.dayofyear,\n",
        "              \"week\": column_1.dt.week,\n",
        "              \"weekofyear\": column_1.dt.weekofyear,\n",
        "              \"dayofweek\": column_1.dt.dayofweek,\n",
        "              \"weekday\": column_1.dt.weekday,\n",
        "              \"quarter\": column_1.dt.quarter,\n",
        "             })"
      ],
      "metadata": {
        "execution": {
          "iopub.status.busy": "2024-08-23T13:30:36.404362Z",
          "iopub.execute_input": "2024-08-23T13:30:36.404665Z",
          "iopub.status.idle": "2024-08-23T13:30:36.448260Z",
          "shell.execute_reply.started": "2024-08-23T13:30:36.404618Z",
          "shell.execute_reply": "2024-08-23T13:30:36.446659Z"
        },
        "trusted": true,
        "id": "oITXwpOOIizQ",
        "outputId": "2d9d583f-1c81-460d-f634-2589fbab9d96"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": "0   1996-04-22\n1   1996-04-23\n2   1996-05-14\n3   1996-05-15\n4   2001-05-16\n5   2002-05-17\n6   2003-05-18\n7   2004-05-19\n8   2005-05-20\nName: month_day_year, dtype: datetime64[ns]\n",
          "output_type": "stream"
        },
        {
          "execution_count": 12,
          "output_type": "execute_result",
          "data": {
            "text/plain": "   year  month  day  hour   ...     weekofyear  dayofweek  weekday  quarter\n0  1996      4   22     0   ...             17          0        0        2\n1  1996      4   23     0   ...             17          1        1        2\n2  1996      5   14     0   ...             20          1        1        2\n3  1996      5   15     0   ...             20          2        2        2\n4  2001      5   16     0   ...             20          2        2        2\n5  2002      5   17     0   ...             20          4        4        2\n6  2003      5   18     0   ...             20          6        6        2\n7  2004      5   19     0   ...             21          2        2        2\n8  2005      5   20     0   ...             20          4        4        2\n\n[9 rows x 10 columns]",
            "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>hour</th>\n      <th>dayofyear</th>\n      <th>week</th>\n      <th>weekofyear</th>\n      <th>dayofweek</th>\n      <th>weekday</th>\n      <th>quarter</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1996</td>\n      <td>4</td>\n      <td>22</td>\n      <td>0</td>\n      <td>113</td>\n      <td>17</td>\n      <td>17</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1996</td>\n      <td>4</td>\n      <td>23</td>\n      <td>0</td>\n      <td>114</td>\n      <td>17</td>\n      <td>17</td>\n      <td>1</td>\n      <td>1</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>1996</td>\n      <td>5</td>\n      <td>14</td>\n      <td>0</td>\n      <td>135</td>\n      <td>20</td>\n      <td>20</td>\n      <td>1</td>\n      <td>1</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>1996</td>\n      <td>5</td>\n      <td>15</td>\n      <td>0</td>\n      <td>136</td>\n      <td>20</td>\n      <td>20</td>\n      <td>2</td>\n      <td>2</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2001</td>\n      <td>5</td>\n      <td>16</td>\n      <td>0</td>\n      <td>136</td>\n      <td>20</td>\n      <td>20</td>\n      <td>2</td>\n      <td>2</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>2002</td>\n      <td>5</td>\n      <td>17</td>\n      <td>0</td>\n      <td>137</td>\n      <td>20</td>\n      <td>20</td>\n      <td>4</td>\n      <td>4</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>2003</td>\n      <td>5</td>\n      <td>18</td>\n      <td>0</td>\n      <td>138</td>\n      <td>20</td>\n      <td>20</td>\n      <td>6</td>\n      <td>6</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>2004</td>\n      <td>5</td>\n      <td>19</td>\n      <td>0</td>\n      <td>140</td>\n      <td>21</td>\n      <td>21</td>\n      <td>2</td>\n      <td>2</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>2005</td>\n      <td>5</td>\n      <td>20</td>\n      <td>0</td>\n      <td>140</td>\n      <td>20</td>\n      <td>20</td>\n      <td>4</td>\n      <td>4</td>\n      <td>2</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "In addition to extracting date features, you can use the subtraction operator on Timestamp objects to determine the amount of time between two different dates:"
      ],
      "metadata": {
        "id": "ncw-WcHQIizR"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "print(dates.iloc[1,0])\n",
        "print(dates.iloc[3,0])\n",
        "print(dates.iloc[3,0]-dates.iloc[1,0])"
      ],
      "metadata": {
        "execution": {
          "iopub.status.busy": "2024-08-23T13:31:44.734310Z",
          "iopub.execute_input": "2024-08-23T13:31:44.734623Z",
          "iopub.status.idle": "2024-08-23T13:31:44.740570Z",
          "shell.execute_reply.started": "2024-08-23T13:31:44.734577Z",
          "shell.execute_reply": "2024-08-23T13:31:44.739627Z"
        },
        "trusted": true,
        "id": "Cp6PwnNaIizR",
        "outputId": "54de4b29-508b-464c-8930-52259eda796c"
      },
      "execution_count": null,
      "outputs": [
        {
          "name": "stdout",
          "text": "1996-04-23 00:00:00\n1996-05-15 00:00:00\n22 days 00:00:00\n",
          "output_type": "stream"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Pandas includes a variety of more advanced date and time functionality beyond the basics covered in this lesson, particularly for dealing time series data (data consisting of many periodic measurements over time.). Read more about date and time functionality [here](http://pandas.pydata.org/pandas-docs/version/0.17.0/timeseries.html)."
      ],
      "metadata": {
        "id": "lEWQVE8AIizR"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Wrap Up"
      ],
      "metadata": {
        "id": "s0QAdT8RIizR"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Pandas makes it easy to convert date data into the Timestamp data format and extract basic date features like day of the year, month and day of week. Simple date features can be powerful predictors because data often exhibit cyclical patterns over different time scales.\n",
        "\n",
        "Cleaning and preprocessing numeric, character and date data is sometimes all you need to do before you start a project. In some cases, however, your data may be split across several tables such as different worksheets in an excel file or different tables in a database. In these cases, you might have combine two tables together before proceeding with your project. In the next lesson, we'll explore how to merge data sets."
      ],
      "metadata": {
        "id": "ictVsuMrIizS"
      }
    }
  ]
}