CODEWITHSUNDEEP
pythonpandasdatetimetimetimedelta
ssmalik
ssmalik

Reputation: 25

How can i sum the time stamps of one day?

I am using Pandas.

I have the following dataset: dataset Image

This is one-year data containing details of trade exchanges happening per day. i want to sum the total minutes of one day for which trade is happening. For e.g if on 2009-04-01, trade starts at 9:30:50 and ends at 15:45:10, then total minutes are:

[(15-9)*60+(30+45]+(50+10)/60)

I am unable to do this by resampling function.

The dates are in DateTimeIndex format. I'm new to Pandas.

Upvotes: 0

Views: 320

Answers (2)

FObersteiner
FObersteiner

Reputation: 25544

you can groupby the date and use numpy's ptp to get the max-to-min difference of the timedeltas:

import pandas as pd
import numpy as np

# from the question I assume your df looks like
df = pd.DataFrame({'DATE': ["2020-04-01", "2020-04-01", "2020-04-02", "2020-04-02"],
                   'TIME': ["9:30:50", "15:45:10", "8:30:50", "15:45:10"]})

df['DATE'] = pd.to_datetime(df['DATE'])
df['TIME'] = pd.to_timedelta(df['TIME'])

totalTimes = df.groupby('DATE')['TIME'].agg(np.ptp)
# DATE
# 2020-04-01   0 days 06:14:20
# 2020-04-02   0 days 07:14:20
# Name: TIME, dtype: timedelta64[ns]

If you want e.g. the total minutes, you can use the .total_seconds() to calculate them, e.g.

totalTimes.loc['2020-04-01'].total_seconds()/60
# 374.3333333333333

Upvotes: 1

Patrick Lee
Patrick Lee

Reputation: 165

I think you'd better use datetime for this matter.

Since you have the starttime and endtime as string in the snapshot. You could do this:

import datetime

starttime = datetime.datetime.strptime('2009-04-01 9:30:50', '%Y-%m-%d %H:%M:%S')
endtime = datetime.datetime.strptime('2009-04-01 15:45:10', '%Y-%m-%d %H:%M:%S')
((endtime - starttime).seconds) / 60

Upvotes: 1

Related Questions