This post explains how to work with date and time in pandas. Date and time are very common for a dataset to have. Based on the use case, the column should be transformed.

Creating a simple dataset

We are going to generate dates using pandas date_range function and then create a dataframe out of it. For this demo, we are going to have a single column dataframe.

import pandas as pd
df = pd.DataFrame(pd.date_range(start="2022-12-05", end="2022-12-06", freq='30S') , columns=["my_time"])

We are creating a daterange from 2022-12-05 to 2022-12-06 at the frequency of 30 seconds. We will get 2881 records in the dataset.

Extracting Year, Month, Day, Hour, Minutes, Seconds from the column

df['year'] = df['my_time'].dt.year
df['month'] = df['my_time'].dt.month
df['day'] = df['my_time'].dt.day
df['hour'] = df['my_time'].dt.hour
df['minute'] = df['my_time'].dt.minute
df['seconds'] = df['my_time'].dt.second

Extracting Day of the week

df['day_name'] = df['my_time'].dt.day_name()
df['day_of_week'] = df['my_time'].dt.dayofweek

Here day_name() will provide the name of the day while dayofweek will provide the number associated with the day. Here Monday is 0 while sunday is 6.

Detecting Weekend

df["Is Weekend"] = df['day_of_week'] > 4

Here we are using the column we derived in the earlier section. The condition df['day_of_week'] > 4 will return true only for values 5 and 6 which represent Saturday and Sunday respectively.

Extracting Name of the Month

df['month_name'] = df['my_time'].dt.month_name()

Extracting week of the year

df['week_of_year'] = df['my_time'].dt.isocalendar().week

Extracting quarter

df['quarter'] = df['my_time'].dt.quarter

Working with timezones

# Setting timezone for the series
df['my_time'] = df['my_time'].dt.tz_localize('Asia/Calcutta')
df['timezone_source'] = "Asia/Calcutta"

# Converting timezone info of the series
df['est'] = df['my_time'].dt.tz_convert('America/New_York')
df['timezone_dest'] = "America/New_York"

Next Steps

In this post, I have covered the most basic datetime transformation that happens using pandas. In most of the projects having datetime data, these transformation can be useful. These are some of the common transformations I have to perform as a data engineer.

In future, I will try to cover some more details associated with pandas date and time.

References

1. https://stackoverflow.com/questions/68149831/find-if-date-is-weekend-or-weekday-in-pandas-dataframe