Calculate dates between today and pandas dataframe-CodePudding

I am trying to subtract the date of today of a date set in a pandas dataframe.

Example data frame :
8 Emma Mike 1 2018/5/21 1654160303.597019
12 Emma Mike 3 2018/6/03 1654160303.597019
13 Emma Mike 1 2018/8/03 1654160303.597019
16 Emma Mike 2 2019/2/8 1654160303.597019
17 Emma Mike 2 2019/2/13 1654160303.597019
19 Emma Mike 2 2019/3/18 1654160303.597019

This is my code:

df_transactions['month'] = df_transactions['date'].str.split('/').str[1].astype(int) #select only one column of the dataframe
month = df_transactions['month'] #define value month
time_difference = pd.to_datetime(month) - pd.Timestamp.now().normalize() #calculate time difference
print(time_difference)

However, the results are as follows:
8 -19145 days 00:00:00.000000005
9 -19145 days 00:00:00.000000005
10 -19145 days 00:00:00.000000006
11 -19145 days 00:00:00.000000006
12 -19145 days 00:00:00.000000006
13 -19145 days 00:00:00.000000008
14 -19145 days 00:00:00.000000001

something is going wrong in the subtraction, no clue what. Thanks!

CodePudding user response：

I think need subtract today by original column converted to datetimes, if need days in numeric add Series.dt.days:

time_difference =  (pd.Timestamp.now().normalize() - 
                                pd.to_datetime(df_transactions['date'])).dt.days

CodePudding user response：

Fast make a list then try this code:-

def file_date_extract(key):
    match = re.search(r'\d{4}[/.-]\d{2}[/.-]\d{2}', key)
    date = datetime.strptime(match.group(), '%Y-%m-%d').date()     
    year = date.strftime("%Y")
    month = date.strftime("%m")
    day = date.strftime("%d")
    date = str(year) '/' str(month) '/' str(day)
    return date

date = file_data_extract('8 Emma Mike 1 2018/5/21 1654160303.597019')#list