Noobie Noobie - 3 months ago 37
Python Question

how to convert a (possibly negative) Pandas TimeDelta in minutes (float)?

I have a dataframe like this

df[['timestamp_utc','minute_ts','delta']].head()
Out[47]:
timestamp_utc minute_ts delta
0 2015-05-21 14:06:33.414 2015-05-21 12:06:00 -1 days +21:59:26.586000
1 2015-05-21 14:06:33.414 2015-05-21 12:07:00 -1 days +22:00:26.586000
2 2015-05-21 14:06:33.414 2015-05-21 12:08:00 -1 days +22:01:26.586000
3 2015-05-21 14:06:33.414 2015-05-21 12:09:00 -1 days +22:02:26.586000
4 2015-05-21 14:06:33.414 2015-05-21 12:10:00 -1 days +22:03:26.586000


Where
df['delta']=df.minute_ts-df.timestamp_utc


timestamp_utc datetime64[ns]
minute_ts datetime64[ns]
delta timedelta64[ns]


Problem is, I would like to get the number of (possibly negative) minutes between
timestamp_utc
and
minutes_ts
, disregarding the seconds component.

So for the first row I would like to get
-120
. Indeed,
2015-05-21 12:06:00
is 120 minutes before
2015-05-21 14:06:33.414
.

What is the most pandaesque way to do it?

Many thanks!

Answer

You can use:

df['a'] = df['delta'] / np.timedelta64(1, 'm')
print (df)
            timestamp_utc           minute_ts                    delta  \
0 2015-05-21 14:06:33.414 2015-05-21 12:06:00 -1 days +21:59:26.586000   
1 2015-05-21 14:06:33.414 2015-05-21 12:07:00 -1 days +22:00:26.586000   
2 2015-05-21 14:06:33.414 2015-05-21 12:08:00 -1 days +22:01:26.586000   
3 2015-05-21 14:06:33.414 2015-05-21 12:09:00 -1 days +22:02:26.586000   
4 2015-05-21 14:06:33.414 2015-05-21 12:10:00 -1 days +22:03:26.586000   

          a  
0 -120.5569  
1 -119.5569  
2 -118.5569  
3 -117.5569  
4 -116.5569  

And then convert float to int:

df['a'] = (df['delta'] / np.timedelta64(1, 'm')).astype(int)
print (df)
            timestamp_utc           minute_ts                    delta    a
0 2015-05-21 14:06:33.414 2015-05-21 12:06:00 -1 days +21:59:26.586000 -120
1 2015-05-21 14:06:33.414 2015-05-21 12:07:00 -1 days +22:00:26.586000 -119
2 2015-05-21 14:06:33.414 2015-05-21 12:08:00 -1 days +22:01:26.586000 -118
3 2015-05-21 14:06:33.414 2015-05-21 12:09:00 -1 days +22:02:26.586000 -117
4 2015-05-21 14:06:33.414 2015-05-21 12:10:00 -1 days +22:03:26.586000 -116