Python: resample dataframe and sum

Question

I have the following dataframe:

df=pd.DataFrame(index=[0,1])
df['timestamp'] = ['2022-01-01 20:10:00', '2022-01-01 20:50:00']
df['currency'] = ['USD', 'USD']
df['operation'] = ['deposit', 'deposit']
df['amount'] = [0.1, 0.4]
df:
            timestamp           currency    operation   amount
       0    2022-01-01 20:10:00 USD         deposit     0.1
       1    2022-01-01 20:50:00 USD         deposit     0.4

How can I resample the data on an hourly basis and sum the "amount" to get the following dataframe:

df:
         timestamp            currency  operation   amount
    0    2022-01-01 20:00:00  USD       deposit     0.5

Using .resample('H') eliminates the currency and operation columns. How can I do this so that Sum the "amount" column?

score 1 · Accepted Answer · answered Jan 27 '22 at 22:29

1

Doing with pd.Grouper and follow by agg

out = df.groupby(pd.Grouper(key='timestamp',freq='1h')).\
          agg(lambda x : x.sum() 
                         if x.dtypes == float 
                         else x.iloc[0]).reset_index()
Out[122]: 
            timestamp currency operation  amount
0 2022-01-01 20:00:00      USD   deposit     0.5

answered Jan 27 '22 at 22:29

BENY

317,841
20
164
234

For some reason I am getting TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'Index'. How did you get yours to work? – MathMan 99 Jan 27 '22 at 22:37
Nevermind, fixed it. – MathMan 99 Jan 27 '22 at 22:45

Python: resample dataframe and sum

1 Answers1