In pandas subtract date from series of date to get series of number of days

user_11543027 0
Viewed 245

In my CSV file I have a column with date and time with the format 6/1/2019 12:00:00 AM. My requirement is to remove time from all rows, then row will have only date. After this I have to subtract all rows from base date 1/1/2019 so the row should have only number of days. Here for e.g if we subtract 6/1/2019 from 1/1/2019 the row will have the value 6.

I tried below code to remove time.

import pandas as pd
df = pd.read_csv('sample.csv', header = 0)
from datetime import datetime,date
df['date'] = pd.to_datetime(df['date']).dt.date

How to subtract date 1/1/2019 from each row in the column and get the days in number using pandas and python datetime library?

pandas datetime python
1 Answers

Remove times by Series.dt.floor from datetimes (convert them to 00:00:00) and subtract datetime, last convert output timedeltas to days by Series.dt.days:

df = pd.read_csv('sample.csv', header = 0, parse_dates=['date'])

df['days'] = df['date'].dt.floor('d').sub(pd.Timestamp('2019-01-01')).dt.days

Sample:

df = pd.DataFrame({'date': pd.date_range('2019-01-06 12:00:00', periods=10)})

df['days'] = df['date'].dt.floor('d').sub(pd.Timestamp('2019-01-01')).dt.days
print (df)
                 date  days
0 2019-01-06 12:00:00     5
1 2019-01-07 12:00:00     6
2 2019-01-08 12:00:00     7
3 2019-01-09 12:00:00     8
4 2019-01-10 12:00:00     9
5 2019-01-11 12:00:00    10
6 2019-01-12 12:00:00    11
7 2019-01-13 12:00:00    12
8 2019-01-14 12:00:00    13
9 2019-01-15 12:00:00    14
user_29010020
Related