{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"![](\"http://sydney.edu.au/images/content/about/logo-mono.jpg\")
\n",
"\n",
"Predictive Analytics (QBUS2820)
\n",
"Tutorial 7 (Working with Time Stamped Data)
\n",
"
\n",
"\n",
"This guide explains the basics of working with dates and times in Python and pandas. \n",
"\n",
"Dates and Time in Python
\n",
"Conversions between strings and datetime
\n",
"Date functionality in pandas
\n",
"Periods
\n",
"Reading time stamped data
\n",
"Time series subsetting
\n",
"Time series plot
\n",
"\n",
"This notebook assumes the following imports and settings."
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"%matplotlib inline\n",
"import pandas as pd\n",
"import numpy as np\n",
"import matplotlib.pyplot as plt\n",
"import seaborn as sns\n",
"sns.set_context('notebook') \n",
"sns.set_style('ticks')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"##Dates and Time in Python\n",
"\n",
"The [datetime](https://docs.python.org/3/library/datetime.html?highlight=datetime#module-datetime) module from the standard Python library provides the basic variable types and tools for date and time data. To get started, we retrieve the current date and time. "
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"datetime.datetime(2017, 9, 4, 14, 21, 10, 637334)"
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"from datetime import datetime\n",
"now=datetime.now()\n",
"now"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"2017-09-04 14:21:10.637334\n"
]
}
],
"source": [
"print(now)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The now variable that we created has a special data type which stores date and time down to microseconds."
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"datetime.datetime"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"type(now)"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"(2017, 9, 4)"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"now.year, now.month, now.day"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"An interesting feature of datetime objects is that we can perform operations with them."
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"datetime.timedelta(368, 64200)"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"delta=datetime(2020, 9, 15)-datetime(2019, 9, 12, 6, 10)\n",
"delta"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"(368, 64200)"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"delta.days, delta.seconds "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Fow example, if we want to shift a date 5 days ahead, we can use:"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"datetime.datetime(2020, 10, 6, 0, 0)"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"from datetime import timedelta\n",
"start=datetime(2020, 10, 1)\n",
"start+timedelta(5)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"As a note, the datetime module also has separate date and time objects. "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"##Conversions between strings and datetime\n",
"\n",
"Datetime objects (and the pandas counterparts) have the strftime method, which allows us to convert them to a string according to our desired format. Refer to this [page](http://strftime.org/) for the available formatting options. "
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'2020-10-01'"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"stamp=datetime(2020,10,1)\n",
"stamp.strftime('%Y-%m-%d')"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'01/Oct/20'"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"stamp.strftime('%d/%b/%y')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"To convert strings to datatime, we can use the parse function, which infers almost any intelligible date format. Here is an example. "
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"datetime.datetime(2020, 9, 15, 0, 0)"
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"from dateutil.parser import parse\n",
"parse('Sept 15th 2020')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We just need to be careful with the fact that it assumes a US date format, unless we specify otherwise. "
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"2020-01-10 00:00:00\n",
"2020-10-01 00:00:00\n"
]
}
],
"source": [
"print(parse('1/10/2020'))\n",
"print(parse('1/10/2020', dayfirst=True))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"##Date functionality in pandas\n",
"\n",
"When dealing with multiple dates, we turn to pandas. "
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"DatetimeIndex(['2020-10-10', '2020-10-11'], dtype='datetime64[ns]', freq=None)"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"dts=['10/10/2020','11/10/2020']\n",
"dates=pd.to_datetime(dts, dayfirst=True)\n",
"dates"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"In pandas, a set of dates has the DatetimeIndex type. Each element of a DatetimeIndex has the TimeStamp, which for practical purposes is equivalent to datetime; we can use the two interchangeably. "
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"Timestamp('2020-10-10 00:00:00')"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"dates[0]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The pandas [date_range](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.date_range.html) function allows us to generate a range of dates according to a specified frequency. "
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"DatetimeIndex(['2020-10-01', '2020-10-02', '2020-10-03', '2020-10-04',\n",
" '2020-10-05'],\n",
" dtype='datetime64[ns]', freq='D')\n"
]
}
],
"source": [
"dates=pd.date_range('1/Oct/2020', '5/Oct/2020')\n",
"print(dates)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"To set the frequency, we can set the date option. For example, for a business daily frequency:"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"DatetimeIndex(['2020-10-01', '2020-10-02', '2020-10-05', '2020-10-06',\n",
" '2020-10-07'],\n",
" dtype='datetime64[ns]', freq='B')\n"
]
}
],
"source": [
"dates=pd.date_range(start='1/Oct/2020', periods=5, freq='B')\n",
"print(dates)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Monthly frequency:"
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"DatetimeIndex(['2020-10-31', '2020-11-30', '2020-12-31', '2021-01-31',\n",
" '2021-02-28'],\n",
" dtype='datetime64[ns]', freq='M')\n"
]
}
],
"source": [
"dates=pd.date_range(start='1/Oct/2020', periods=5, freq='M')\n",
"print(dates)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The frequency option also accepts multiples:"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"DatetimeIndex(['2020-10-31', '2021-01-31', '2021-04-30', '2021-07-31',\n",
" '2021-10-31'],\n",
" dtype='datetime64[ns]', freq='3M')\n"
]
}
],
"source": [
"dates=pd.date_range(start='1/Oct/2020', periods=5, freq='3M')\n",
"print(dates)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Refer to the [pandas documentation](http://pandas.pydata.org/pandas-docs/stable/timeseries.html#offset-aliases) for a list of available frequencies. "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"##Periods\n",
"\n",
"A related object is the Period, which represents a timespan like months and quarters, rather a point in time as in TimeStamp. For example, the variable below represents the period between 1/10/2016 and 31/10/2016. "
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"Period('2020-10', 'M')"
]
},
"execution_count": 19,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"month=pd.Period('Oct-2020', freq='M')\n",
"month"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"As with datetime and TimeStamp objects, we can perform operations with a Period object. "
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"Period('2020-11', 'M')"
]
},
"execution_count": 20,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"month+1 # from above"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The counterpart of the DatetimeIndex is the PeriodIndex."
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"PeriodIndex(['2020Q1', '2020Q2', '2020Q3', '2020Q4'], dtype='period[Q-DEC]', freq='Q-DEC')"
]
},
"execution_count": 21,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"values=['2020Q1', '2020Q2', '2020Q3', '2020Q4']\n",
"index=pd.PeriodIndex(values, freq='Q')\n",
"index"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"To generate a period range:"
]
},
{
"cell_type": "code",
"execution_count": 22,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"PeriodIndex(['2020Q1', '2020Q2', '2020Q3', '2020Q4'], dtype='period[Q-DEC]', freq='Q-DEC')"
]
},
"execution_count": 22,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.period_range('2020Q1','2020Q4', freq='Q')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Finally, we can convert timestamps to periods as follows. "
]
},
{
"cell_type": "code",
"execution_count": 23,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"DatetimeIndex(['2020-10-31', '2020-11-30', '2020-12-31', '2021-01-31',\n",
" '2021-02-28'],\n",
" dtype='datetime64[ns]', freq='M')\n"
]
},
{
"data": {
"text/plain": [
"PeriodIndex(['2020-10', '2020-11', '2020-12', '2021-01', '2021-02'], dtype='period[M]', freq='M')"
]
},
"execution_count": 23,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"dates=pd.date_range(start='1/Oct/2020', periods=5, freq='M')\n",
"print(dates)\n",
"dates.to_period()"
]
},
{
"cell_type": "markdown",
"metadata": {
"collapsed": true
},
"source": [
"##Reading time stamped data\n",
"\n",
"Let us now work with data. For simplicity, our data will have only one column apart from the date. The same principles applies for working with data frames instead of a single series. \n",
"\n",
"The nswretail.csv file contains monthly retail turnover figures for the state of NSW. I downloaded the data from the [Australian Bureau of Statistics](http://www.abs.gov.au/ausstats/abs@.nsf/mf/8501.0) website. The ABS [explanatory notes](http://www.abs.gov.au/Ausstats/abs@.nsf/exnote/8501.0) define retail turnover as: \n",
"\n",
"\n",
"Retail sales; wholesale sales; takings from repairs, meals and hiring of goods (except for rent, leasing and hiring of land and buildings);\n",
"commissions from agency activity (e.g. commissions received from collecting dry cleaning, selling lottery tickets, etc.); and\n",
"the goods and services tax.\n",
"
\n",
"\n",
"To read the data, we follow the usual procedure. If you open the data file in a text editor, you will see that it has two columns: Month and Turnover. In a time series context, we want to make the date the index of the DataFrame by specifying Month as the index via the index_col option. We set the parse_dates options as True so that pandas can automatically recognise the dates column and convert them to TimeStamp objects. "
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Turnover | \n",
"
\n",
" \n",
" | Month | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" | 2017-02-01 | \n",
" 7298.9 | \n",
"
\n",
" \n",
" | 2017-03-01 | \n",
" 8085.8 | \n",
"
\n",
" \n",
" | 2017-04-01 | \n",
" 7883.7 | \n",
"
\n",
" \n",
" | 2017-05-01 | \n",
" 8132.0 | \n",
"
\n",
" \n",
" | 2017-06-01 | \n",
" 8130.1 | \n",
"
\n",
" \n",
"
\n",
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Turnover | \n",
"
\n",
" \n",
" | Month | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" | 2017-02 | \n",
" 7298.9 | \n",
"
\n",
" \n",
" | 2017-03 | \n",
" 8085.8 | \n",
"
\n",
" \n",
" | 2017-04 | \n",
" 7883.7 | \n",
"
\n",
" \n",
" | 2017-05 | \n",
" 8132.0 | \n",
"
\n",
" \n",
" | 2017-06 | \n",
" 8130.1 | \n",
"
\n",
" \n",
"
\n",
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Turnover | \n",
"
\n",
" \n",
" | Month | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" | 2017-02 | \n",
" 7298.9 | \n",
"
\n",
" \n",
" | 2017-03 | \n",
" 8085.8 | \n",
"
\n",
" \n",
" | 2017-04 | \n",
" 7883.7 | \n",
"
\n",
" \n",
" | 2017-05 | \n",
" 8132.0 | \n",
"
\n",
" \n",
" | 2017-06 | \n",
" 8130.1 | \n",
"
\n",
" \n",
"
\n",
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Turnover | \n",
"
\n",
" \n",
" | Month | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" | 2017-04 | \n",
" 7883.7 | \n",
"
\n",
" \n",
" | 2017-05 | \n",
" 8132.0 | \n",
"
\n",
" \n",
" | 2017-06 | \n",
" 8130.1 | \n",
"
\n",
" \n",
"
\n",
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Turnover | \n",
"
\n",
" \n",
" | Month | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" | 2017-02 | \n",
" 7298.9 | \n",
"
\n",
" \n",
" | 2017-03 | \n",
" 8085.8 | \n",
"
\n",
" \n",
" | 2017-04 | \n",
" 7883.7 | \n",
"
\n",
" \n",
" | 2017-05 | \n",
" 8132.0 | \n",
"
\n",
" \n",
" | 2017-06 | \n",
" 8130.1 | \n",
"
\n",
" \n",
"
\n",
"