0

I am trying to identify hours between two dates. Date format is not consistent between two columns

The below code works when the date format is similar. How can I convert the UTC date format into normal date month year

df['timebetween'] = (pd.to_datetime(df['datecolA'],dayfirst = True) - pd.to_datetime(df['datecolB'],dayfirst = True))

df['timebetween']= df['timebetween']/np.timedelta64(1,'h')

my data looks like below and I am interested in column timebetween which can be achieved from the above code if both date columns had same format

datecolA                    datecolB                  timebetween 

29/06/2020 08:30:00   2018-12-02T11:32:00.000Z         x hours
29/06/2020 08:30:00   2018-12-04T14:00:00.000Z         y hours 
29/06/2020 08:30:00   2017-02-02T14:36:00.000Z         z hours 
29/06/2020 08:30:00   2017-02-02T14:36:00.000Z         n hours
EricA
  • 403
  • 2
  • 14

2 Answers2

0

I think you need to remove UTC from datecolB:

df['datecolB'] = df.datecolB.dt.tz_localize(None)

# or extract the time delta directly
df['timebetween'] = (df.datecolA - df.datecolB.dt.tz_localize(None))/np.timedelta64(1,'h')

Output:

             datecolA                  datecolB   timebetween
0 2020-06-29 08:30:00 2018-12-02 11:32:00+00:00  13796.966667
1 2020-06-29 08:30:00 2018-12-04 14:00:00+00:00  13746.500000
2 2020-06-29 08:30:00 2017-02-02 14:36:00+00:00  29825.900000
3 2020-06-29 08:30:00 2017-02-02 14:36:00+00:00  29825.900000
Quang Hoang
  • 146,074
  • 10
  • 56
  • 74
  • Thanks for the above I read the DateTime documentation and adding utc=true and changing both date format does the job – EricA Jun 29 '20 at 14:03
0

adding utc= true does the trick

df['timebetween'] = (pd.to_datetime(df['datecolA'],dayfirst = True,utc=True) - pd.to_datetime(df['datecolB'],dayfirst = True,utc=True))

df['timebetween']= df['timebetween']/np.timedelta64(1,'h')
EricA
  • 403
  • 2
  • 14
  • you shouldn't need `utc=True` for 'datecolB' - the 'Z' character (zulu time; UTC) is automatically parsed to UTC. You don't need `dayfirst = True` either since the string is ISO format thus correctly parsed anyway ;-) – FObersteiner Jun 30 '20 at 12:16