Follow this tutorial to add a date and time column into a pandas DataFrame.
Step 1: Acquire your data
You most probably have already a pandas DataFrame. However if not, here’s some data that you can use for this example:
import pandas as pd
# create two columns
dates = pd.Series(pd.date_range(start='6/1/23', periods=5,freq='B'))
interviews = [185, 138, 137, 192, 137]
# construct DataFrame
dates = pd.Series(pd.date_range(start='6/1/23', periods=5,freq='B'))
interviews = [185, 138, 137, 192, 137]Step 2: Get the current datetime Timestamp as a column
You can easily get the current data and time Timestamp as a DataFrame column. THe following code adds as column named today_ts to our DataFrame:
today_ts = pd.Timestamp.today()
campaign['today_ts'] = today_tsAlternatively, you can use the assign DataFrame method:
campaign = campaign.assign(today_ts = today_ts)Step 3: Add a date column to your DataFrame
If you are interested to add just the date itself (without the time value) you can use the dt accessor for the relevant Series / Column:
campaign['today_date'] = pd.to_datetime(campaign['today_ts'].dt.date)Here’s our DataFrame:
campaign.head()| dates | interviews | today_ts | today_date | |
|---|---|---|---|---|
| 0 | 2023-06-01 | 185 | 2023-05-21 17:28:59.767052 | 2023-05-21 |
| 1 | 2023-06-02 | 138 | 2023-05-21 17:28:59.767052 | 2023-05-21 |
| 2 | 2023-06-05 | 137 | 2023-05-21 17:28:59.767052 | 2023-05-21 |
| 3 | 2023-06-06 | 192 | 2023-05-21 17:28:59.767052 | 2023-05-21 |
| 4 | 2023-06-07 | 137 | 2023-05-21 17:28:59.767052 | 2023-05-21 |
Step 4: Perform time calculations
Now that our data is complete we can easily perform some calculations, for example, subtracting times.
As an example, we can create a new column storing the calculated time difference between two days as following:
campaign['delta'] = campaign['dates'] - campaign['today_date']
campaign.head()This will render the following data:
| dates | interviews | today_ts | today_date | delta | |
|---|---|---|---|---|---|
| 0 | 2023-06-01 | 185 | 2023-05-21 17:28:59.767052 | 2023-05-21 | 11 days |
| 1 | 2023-06-02 | 138 | 2023-05-21 17:28:59.767052 | 2023-05-21 | 12 days |
| 2 | 2023-06-05 | 137 | 2023-05-21 17:28:59.767052 | 2023-05-21 | 15 days |
| 3 | 2023-06-06 | 192 | 2023-05-21 17:28:59.767052 | 2023-05-21 | 16 days |
| 4 | 2023-06-07 | 137 | 2023-05-21 17:28:59.767052 | 2023-05-21 | 17 days |
How to subtract dates and times in Python?
Here are a few related cases that i encounter in my daily work with Data:
- Difference between two dates
- Difference between a date and a timedelta object.
- Subtracting other timedeltas from date objects : years, months, hours, minutes, seconds.
Subtract days from a datetime object in Python
Let’s define two date objects that represents the hire and quitting date of a random employee.
import datetime
hire_date = datetime.date(2021,4, 7)
quit_date = datetime.date(2022,4, 24)Then let’s calculate the overall time worked:
print("The overall time that this employee worked in our company was:" + str(quit_date - hire_date).split(',')[0]+".")Here’s the result:
The overall time that this employee worked in our company was:382 days.Subtract timedeltas from datetimes
We’ll now define a datetime and timedelta representing the hire date and the number of days on the job for our fictitious employee:
import datetime
hire_date = datetime.date(2021,4, 7)
worked_days = datetime.timedelta(282)Let’s quickly calculate the quitting date of this employee:
print("The hire date of this employee worked in our company was: " + str(quit_date - worked_days)+".")Here’s the result:
The hire date of this employee worked in our company was: 2021-06-08.Note: Another way to accomplish this would have been using the dateutil.relativedelta method
print(quit_date - relativedelta(days = 282))
Subtract years from datetime
The dateutil.relativedelta utility allows to very easily subtract time from a date object.
from dateutil.relativedelta import relativedelta
graduation_date = quit_date - relativedelta(years = 2)
print("His graduation date was on: " + str(graduation_date))The result will be:
His graduation date was on: 2020-04-24Note: Don’t forget to import the utility into your Python program as otherwise you will receive the following NameError:
NameError: name 'relativedelta' is not defined
Subtract months from a Python date
As we just learnt, we can use the relativedelta utility to calculate time differences relative to a Python daytime / timestamp.
The following code helps to subtract 24 months from a datetime:
from dateutil.relativedelta import relativedelta
graduation_date = quit_date - relativedelta(months = 24)
print("His graduation date was on: " + str(graduation_date))
Find time differences in hours, minutes and seconds
In a similar fashion we can subtract any time units from our datetime:
- Seconds: graduation_date = quit_date – relativedelta(seconds=500000)
- Minutes:quit_date – relativedelta(minutes=45255)
FAQ
How to add a column containing date ranges in pandas DataFrame?
Use the following code to create a column containing a rand of dates starting today:
import datetime
today_date = datetime.now().date()
campaign['date_range_col'] = pd.date_range(start = today_date, periods = len(campaign), freq = 'D')Can i insert a column containing specific dates into a DataFrame?
Assuming that you have a Python list containing dates, that has the same length of your DataFrame, you can assign it as a new column:
campaign= campaign.assing(dates_col = pd.Series(dates_list))