How to group by and apply aggregate function to datetime for a specified period in panda in Python?

user_15133717 0
Viewed 242

I have a data frame with 2 columns.

The 1st column is a timestamp of every minute. The 2nd column is a number.

All I want to do is to change the 1st column into timestamp of every 30 minutes, and the sum of the 30 numbers within that period from column 2.

example dataframe

Power is demonstrated for every minute and but I want to sum them up for every 30 minutes.

pandas numpy python
1 Answers

Using pandas/Series.resample

Series.resample can help you if set the timestamp as index ; then use series.resample('30T').sum()

Manual version

You can use cumsum over the serie you want to keep.

Then select only the index at every 30 positions (np.arange(0, len(df), 30).

Then iterate over the dataframe backward and substract at row n the sum found at row n-1 to keep only the value of the last 30 minutes. Iterating is not very efficient but since your dataset is 1M row, if you take 1 row every 30 rows, it should be fast (33,333 iterations).

df['cumsum'] = df["Power_kw"].cumsum()
df_30_min = df.iloc[np.arange(0, len(df), 30)].copy()

for i in range(len(df_30_min), 1, -1):
    df_30_min.iloc[i-1, df_30_min.columns.get_loc('B')] -= df_30_min.iloc[i-2, df_30_min.columns.get_loc('B')]
user_94539260
Related