Working with Dates and Time

It wasn’t too long ago in Module 4, where we briefly mentioned another column dtype called datetime64 and timedelta[ns].

Dates and times can be a bit tricky and require a specific data type so that analysis can be done correctly.

cycling.head()

	Date	Name	Type	Time	Distance	Comments
0	Sep-10-2019 17:13	Afternoon Ride	Ride	2084	12.62	Rain
1	Sep-11-2019 06:52	Morning Ride	Ride	2531	13.03	rain
2	Sep-11-2019 17:23	Afternoon Ride	Ride	1863	12.52	Wet road but nice whether
3	Sep-12-2019 07:06	Morning Ride	Ride	2192	12.84	Stopped for photo of sunrise
4	Sep-12-2019 17:28	Afternoon Ride	Ride	1891	12.48	Tired by the end of the week.

cycling.dtypes

Date         object
Name         object
Type         object
Time          int64
Distance    float64
Comments     object
dtype: object

Let’s take our cycling dataset as an example.

Our date column in our cycling dataframe currently has a dtype value of object.

cycling.sort_values('Date').head(15)

	Date	Name	Type	Time	Distance	Comments
21	Oct-01-2019 06:53	Morning Ride	Ride	2118	12.71	Rested after the weekend!
22	Oct-01-2019 17:15	Afternoon Ride	Ride	1732	NaN	Legs feeling strong!
23	Oct-02-2019 06:45	Morning Ride	Ride	2222	12.82	Beautiful morning! Feeling fit
...	...	...	...	...	...	...
0	Sep-10-2019 17:13	Afternoon Ride	Ride	2084	12.62	Rain
1	Sep-11-2019 06:52	Morning Ride	Ride	2531	13.03	rain
2	Sep-11-2019 17:23	Afternoon Ride	Ride	1863	12.52	Wet road but nice whether

15 rows × 6 columns

When we try to sort these values, it doesn’t recognize the day or month values and will sort them in some ascending order that is not temporal.

We can see that this sorted starts with October 1st, 2019, followed by September 10th, 2019, and then September 11th, 2019.

Python is purely sorting the rows by month and not taking the day into consideration.

cycling.head()

	Date	Name	Type	Time	Distance	Comments
0	Sep-10-2019 17:13	Afternoon Ride	Ride	2084	12.62	Rain
1	Sep-11-2019 06:52	Morning Ride	Ride	2531	13.03	rain
2	Sep-11-2019 17:23	Afternoon Ride	Ride	1863	12.52	Wet road but nice whether
3	Sep-12-2019 07:06	Morning Ride	Ride	2192	12.84	Stopped for photo of sunrise
4	Sep-12-2019 17:28	Afternoon Ride	Ride	1891	12.48	Tired by the end of the week.

dates = (cycling['Date'].str.split(' ', expand=True)
                           .rename(columns = {0:'Date',
                                              1:'Time'}))
dates.head()

	Date	Time
0	Sep-10-2019	17:13
1	Sep-11-2019	06:52
2	Sep-11-2019	17:23
3	Sep-12-2019	07:06
4	Sep-12-2019	17:28

We can try parsing dates ourselves, but that can be difficult and time-consuming.

To demonstrate this, let’s make an attempt at parsing the Date column in our cycling dataframe, which currently has an object dtype.

First, we would need to split the column separating the date and the time and rename the labels 0 and 1 to Date and Time respectively.

dates = (dates['Date'].str.split('-', expand=True).rename(columns = {0:'Month',
                                                                     1:'Day',
                                                                     2:'Year'}))
dates.head()

	Month	Day	Year
0	Sep	10	2019
1	Sep	11	2019
2	Sep	11	2019
3	Sep	12	2019
4	Sep	12	2019

Once again, we need to split the date column using str.split(), which we learned in Module 4, and separate it into columns for the year, month and day.

dates.iloc[0,1]

'10'

type(dates.iloc[0,1])

str

Currently, the values in dates are of type str, so we would not be able to sort them in a temporal manner.

cycling_dates = (cycling.assign(Year = dates['Year'].astype(int),
                                Month =  dates['Month'],
                                Day = dates['Day'].astype(int))
                                )
cycling_dates.head(3)

	Date	Name	Type	Time	...	Comments	Year	Month	Day
0	Sep-10-2019 17:13	Afternoon Ride	Ride	2084	...	Rain	2019	Sep	10
1	Sep-11-2019 06:52	Morning Ride	Ride	2531	...	rain	2019	Sep	11
2	Sep-11-2019 17:23	Afternoon Ride	Ride	1863	...	Wet road but nice whether	2019	Sep	11

3 rows × 9 columns

cycling_dates = cycling_dates.loc[:, ['Year', 'Month', 'Day', 'Name',
                                      'Type', 'Time', 'Distance', 'Comments']]
cycling_dates.head(3)

	Year	Month	Day	Name	Type	Time	Distance	Comments
0	2019	Sep	10	Afternoon Ride	Ride	2084	12.62	Rain
1	2019	Sep	11	Morning Ride	Ride	2531	13.03	rain
2	2019	Sep	11	Afternoon Ride	Ride	1863	12.52	Wet road but nice whether

We must convert the columns to integers values and add them to the cycling_dates dataframe.

We are then going to select and reorder the columns in the dataframe, so the new date columns are on the left side.

cycling_dates.sort_values(['Year', 'Month', 'Day'])

	Year	Month	Day	Name	Type	Time	Distance	Comments
21	2019	Oct	1	Morning Ride	Ride	2118	12.71	Rested after the weekend!
22	2019	Oct	1	Afternoon Ride	Ride	1732	NaN	Legs feeling strong!
23	2019	Oct	2	Morning Ride	Ride	2222	12.82	Beautiful morning! Feeling fit
...	...	...	...	...	...	...	...	...
18	2019	Sep	26	Afternoon Ride	Ride	1860	12.52	raining
19	2019	Sep	27	Morning Ride	Ride	2350	12.91	Detour around trucks at Jericho
20	2019	Sep	27	Afternoon Ride	Ride	1712	12.47	Tired by the end of the week

33 rows × 8 columns

Now we try to sort them, but how do we sort the month column?

It now incorrectly sorts the rows by listing the October rows before September.

It must be quite evident that we really don’t want to do it this way, right?

There are a lot of limitations, and we haven’t yet separated the time.

Calculating the time between dates now can also be extremely difficult.

(The differing number of days in months is a contributing factor.)

Thankfully we don’t have to do it this way.

Pandas have some built-in functions that will make our lives much easier.

By the end of this slide deck, we will be able to answer the question of what was Tom’s longest time between rides.

This is a question that without pandas, would have taken hours, instead of minutes.

Pandas parse_dates

cycling = pd.read_csv('data/cycling_data.csv')
cycling.head(3)

	Date	Name	Type	Time	Distance	Comments
0	Sep-10-2019 17:13	Afternoon Ride	Ride	2084	12.62	Rain
1	Sep-11-2019 06:52	Morning Ride	Ride	2531	13.03	rain
2	Sep-11-2019 17:23	Afternoon Ride	Ride	1863	12.52	Wet road but nice whether

cycling.dtypes

Date         object
Name         object
Type         object
Time          int64
Distance    float64
Comments     object
dtype: object

Remember How Pandas is built using the NumPy library?

Well in a similar way, Pandas datetime verbs are built using the built-in Python library; datetime .

We can parse our data at the same time as we read in our dataframe using the argument parse_dates.

Originally the Date column adopts a dtype of object when the data is read in.

cycling_dates = pd.read_csv('data/cycling_data.csv', parse_dates = ['Date'])
cycling_dates.head()

	Date	Name	Type	Time	Distance	Comments
0	2019-09-10 17:13:00	Afternoon Ride	Ride	2084	12.62	Rain
1	2019-09-11 06:52:00	Morning Ride	Ride	2531	13.03	rain
2	2019-09-11 17:23:00	Afternoon Ride	Ride	1863	12.52	Wet road but nice whether
3	2019-09-12 07:06:00	Morning Ride	Ride	2192	12.84	Stopped for photo of sunrise
4	2019-09-12 17:28:00	Afternoon Ride	Ride	1891	12.48	Tired by the end of the week.

cycling_dates.dtypes

Date        datetime64[ns]
Name                object
Type                object
Time                 int64
Distance           float64
Comments            object
dtype: object

Using the parse_datesargument with pd.read_csv(), transforms the column so that so that it now adopts a datetime64 dtype.

cycling_dates.sort_values('Date')

	Date	Name	Type	Time	Distance	Comments
0	2019-09-10 17:13:00	Afternoon Ride	Ride	2084	12.62	Rain
1	2019-09-11 06:52:00	Morning Ride	Ride	2531	13.03	rain
2	2019-09-11 17:23:00	Afternoon Ride	Ride	1863	12.52	Wet road but nice whether
...	...	...	...	...	...	...
30	2019-10-10 18:10:00	Afternoon Ride	Ride	1841	12.59	Feeling good after a holiday break!
31	2019-10-11 07:47:00	Morning Ride	Ride	2463	12.79	Stopped for photo of sunrise
32	2019-10-11 18:16:00	Afternoon Ride	Ride	1843	11.79	Bike feeling tight, needs an oil and pump

33 rows × 6 columns

Now that we have a datetime column which expresses when Tom began his journey, we can sort our dataframe in a temporal manner, properly now:

pd.read_csv('data/cycling_data_split_time.csv').head()

	Year	Month	Day	Clock	...	Type	Time	Distance	Comments
0	2019	Sep	10	17:13:04	...	Ride	2084	12.62	Rain
1	2019	Sep	11	06:52:18	...	Ride	2531	13.03	rain
2	2019	Sep	11	17:23:50	...	Ride	1863	12.52	Wet road but nice weather
3	2019	Sep	12	07:06:19	...	Ride	2192	12.84	Stopped for photo of sunrise
4	2019	Sep	12	17:28:05	...	Ride	1891	12.48	Tired by the end of the week

5 rows × 9 columns

(pd.read_csv('data/cycling_data_split_time.csv',
              parse_dates={'Date': ['Year', 'Month', 'Day', 'Clock']})
              .head())

	Date	Name	Type	Time	Distance	Comments
0	2019-09-10 17:13:04	Afternoon Ride	Ride	2084	12.62	Rain
1	2019-09-11 06:52:18	Morning Ride	Ride	2531	13.03	rain
2	2019-09-11 17:23:50	Afternoon Ride	Ride	1863	12.52	Wet road but nice weather
3	2019-09-12 07:06:19	Morning Ride	Ride	2192	12.84	Stopped for photo of sunrise
4	2019-09-12 17:28:05	Afternoon Ride	Ride	1891	12.48	Tired by the end of the week

As another example, our date data may be split between multiple columns.

We can combine the Year, Month, and Day columns to a single datetime column by using a dictionary within the parse_dates argument.

The dictionary key indicates the new column name, and the dictionary value is a list with the multiple date columns to combine.

cycling = pd.read_csv('data/cycling_data.csv')
cycling.head()

	Date	Name	Type	Time	Distance	Comments
0	Sep-10-2019 17:13	Afternoon Ride	Ride	2084	12.62	Rain
1	Sep-11-2019 06:52	Morning Ride	Ride	2531	13.03	rain
2	Sep-11-2019 17:23	Afternoon Ride	Ride	1863	12.52	Wet road but nice whether
3	Sep-12-2019 07:06	Morning Ride	Ride	2192	12.84	Stopped for photo of sunrise
4	Sep-12-2019 17:28	Afternoon Ride	Ride	1891	12.48	Tired by the end of the week.

cycling.dtypes

Date         object
Name         object
Type         object
Time          int64
Distance    float64
Comments     object
dtype: object

What if we need to convert a column into dtype datetime after reading in our data?

That’s not a problem! We have pd.to_datetime() to transform columns of an already existing dataframe.

Let’s use our original cycling dataframe where Date is still of dtype object.

new_cycling = cycling.assign(Date = pd.to_datetime(cycling['Date']))
new_cycling.head()

	Date	Name	Type	Time	Distance	Comments
0	2019-09-10 17:13:00	Afternoon Ride	Ride	2084	12.62	Rain
1	2019-09-11 06:52:00	Morning Ride	Ride	2531	13.03	rain
2	2019-09-11 17:23:00	Afternoon Ride	Ride	1863	12.52	Wet road but nice whether
3	2019-09-12 07:06:00	Morning Ride	Ride	2192	12.84	Stopped for photo of sunrise
4	2019-09-12 17:28:00	Afternoon Ride	Ride	1891	12.48	Tired by the end of the week.

new_cycling.dtypes

Date        datetime64[ns]
Name                object
Type                object
Time                 int64
Distance           float64
Comments            object
dtype: object

To convert Date to a datetime dtype, we use pd.to_datetime() and assign().

Now in the new_cycling dataframe, we see that the column Date is now of type datetime64[ns]

Ok, but what if I don’t want the full datetime value, and I want a column with only a portion of it, like the month or year?

Pandas datetime tools

• .dt.day_name() for the day of the week:

new_cycling['Date'].dt.day_name().head(3)

0      Tuesday
1    Wednesday
2    Wednesday
Name: Date, dtype: object

new_cycling.assign(weekday = new_cycling['Date'].dt.day_name()).head(3)

	Date	Name	Type	Time	Distance	Comments	weekday
0	2019-09-10 17:13:00	Afternoon Ride	Ride	2084	12.62	Rain	Tuesday
1	2019-09-11 06:52:00	Morning Ride	Ride	2531	13.03	rain	Wednesday
2	2019-09-11 17:23:00	Afternoon Ride	Ride	1863	12.52	Wet road but nice whether	Wednesday

No worries, we can add a new column to our dataframe in a similar as we did before, but now we can extract a portion of the datetime column by using one of the many pandas datetime tools.

Here are a couple of examples:

• .dt.day_name() for the day of the week, which we can pair this with .assign() to add this as a column in the dataframe.

new_cycling['Date'].dt.day.head()

0    10
1    11
2    11
3    12
4    12
Name: Date, dtype: int32

new_cycling.assign(day = new_cycling['Date'].dt.day).head()

	Date	Name	Type	Time	Distance	Comments	day
0	2019-09-10 17:13:00	Afternoon Ride	Ride	2084	12.62	Rain	10
1	2019-09-11 06:52:00	Morning Ride	Ride	2531	13.03	rain	11
2	2019-09-11 17:23:00	Afternoon Ride	Ride	1863	12.52	Wet road but nice whether	11
3	2019-09-12 07:06:00	Morning Ride	Ride	2192	12.84	Stopped for photo of sunrise	12
4	2019-09-12 17:28:00	Afternoon Ride	Ride	1891	12.48	Tired by the end of the week.	12

• .dt.day for the day which we can again use with .assign() to add it to our dataframe.

Here are some of the most common useful datetime tools:

• .dt.year
• .dt.month
• .dt.month_name()
• .dt.day
• .dt.day_name()
• .dt.hour
• .dt.minute

For a full list, refer to the attributes and methods section of the Timestamp documentation.

There is some inconsistency with these verbs. You can see that some use parentheses () and some do not.

Using the .dt portion of these can only be used on a pandas Series. We can extract the day, month, year, hour, or minute from a single datetime value, using the same nouns but omitting the .dt.

Let’s see how that’s possible.

new_cycling.head()

	Date	Name	Type	Time	Distance	Comments
0	2019-09-10 17:13:00	Afternoon Ride	Ride	2084	12.62	Rain
1	2019-09-11 06:52:00	Morning Ride	Ride	2531	13.03	rain
2	2019-09-11 17:23:00	Afternoon Ride	Ride	1863	12.52	Wet road but nice whether
3	2019-09-12 07:06:00	Morning Ride	Ride	2192	12.84	Stopped for photo of sunrise
4	2019-09-12 17:28:00	Afternoon Ride	Ride	1891	12.48	Tired by the end of the week.

If I select the first example in row 1 of our new_cycling dataset, you’ll notice that it outputs something called a Timestamp.

timestamp_ex = new_cycling.loc[1,'Date']
timestamp_ex

Timestamp('2019-09-11 06:52:00')

If I select the first example in row 1 of our new_cycling dataset, you’ll notice that it outputs something called a Timestamp.

This is a pandas data type.

timestamp_ex

Timestamp('2019-09-11 06:52:00')

timestamp_ex.month_name()

'September'

timestamp_ex.day

11

timestamp_ex.hour

6

Timestamps show a snapshot of when an event has occurred. Timestamps are complete with both dates and times. If the date and time are not available in your original data, Python will fill in any temporal unknowns with default values (often with 00:00:00 for time, if only the date was provided).

To obtain the month name, day, or hour from the Timestamp, we can use the same nouns in the previous slide without .dt.

Here we get the .mount_name(), the .day and the .hour of a single value by using the same verbs as before but omitting the .dt.

.diff()

cycling_intervals = new_cycling['Date'].sort_values().diff()
cycling_intervals

0                NaT
1    0 days 13:39:00
2    0 days 10:31:00
           ...      
30   0 days 10:15:00
31   0 days 13:37:00
32   0 days 10:29:00
Name: Date, Length: 33, dtype: timedelta64[ns]

In our analysis, it might be important to know how frequent events occur and the time between them.

.diff() is a useful function for that.

This outputs a pandas Series with the time that occurs between rows. As you can see, there was a 10 hour and 22minute gap between Tom’s third and forth bike rides. Wow - that’s a long workday!”

Here, you’ll now notice a new dtype at the bottom of our new pandas Series named timedelta64.

timedelta

cycling_intervals[1]

Timedelta('0 days 13:39:00')

cycling_intervals[1].seconds

49140

sec_per_hour = 60 * 60
cycling_intervals[1].seconds / sec_per_hour

13.65

Unlike a Timestamp that represents a snapshot in time, timedelta represents a duration or an interval of time.

Here we can obtain the time between 2 trips.

Measurement can only be extracted from the timedelta object using either days, seconds, and microseconds verbs.

Here we obtain the number of seconds.

We can convert them into other units by using simple operations.

In this case, we convert it to hours by dividing it by the number of seconds in an hour.

cycling_intervals.max()

Timedelta('5 days 13:47:00')

cycling_intervals.min()

Timedelta('0 days 10:15:00')

interval_range = cycling_intervals.max() - cycling_intervals.min()
interval_range

Timedelta('5 days 03:32:00')

Timedelta objects have a lot of functionality.

We can use a summary statistic verbs with them.

For example, we can calculate the maximum amount of time between rides.

As well as the minimum.

We can also do a simple operation with them, like finding the range.

Let’s apply what we learned!