Btukfyl

I am looking for the best pythonic way to group the sum of values that follow each other from hour to hour. Example: I have the following Dataframe with the columns 'date' and'rainfall' and I have added an example of the desired result :

        date        rainfall    RE

    31/12/17 23:00  0.88    None

    01/01/18 00:00  0.38    1.26

    01/01/18 01:00  0       None

    01/01/18 02:00  0.22    0.22

    01/01/18 03:00  0       None

    01/01/18 04:00  0       None

    01/01/18 13:00  0       None

    01/01/18 14:00  0       None

    01/01/18 15:00  0.55    0.55

    01/01/18 16:00  0       None

    01/01/18 17:00  1.31    1.31

    01/01/18 18:00  0       None

    01/01/18 19:00  0.49    0.49

    01/01/18 20:00  0       None

    01/01/18 21:00  0       None

    01/01/18 22:00  0       None

    01/01/18 23:00  0       None

    02/01/18 00:00  0.7     None

    02/01/18 01:00  0.22    None

    02/01/18 02:00  0.61    None

    02/01/18 03:00  0.42    1.95

    02/01/18 04:00  0       None

    02/01/18 05:00  1.69    1.69

    02/01/18 06:00  0       None

    02/01/18 07:00  0       None

    02/01/18 08:00  0       None

I hope to be clear,

thanks a lot for your help,

Rémy

If I understand correctly, what you want is a rolling sum.
The following will output a Pandas Series of the rolling sums of 'rainfall' over 2 periods

df['rainfall'].rolling(2).sum()

Ofcourse, your DataFrame must stay sorted as you show in your example.

adding it as a part of your DataFrame would be:

df['rainfall_rolling_sum'] = df['rainfall'].rolling(2).sum()

EDIT1:

If you mean you just want to accumulate the rainfall use:

df['rainfall_cumsum'] = df['rainfall'].cumsum()

as the function cumsum() is accumulative sum

EDIT2:

rf_not_zero = df['rainfall'] != 0

df['rainfall_accum'] = df['rainfall'].cumsum()-df['rainfall'].cumsum().where(~df_not_zero).ffill().fillna(0).astype(int)

Okey it is not the best, but when you have big dataframes this could be a simple approach. (If it rains at the beginning or at the end, this will be ignored in this example. See Warning)

import pandas as pd

import numpy as np



# generate pseudo dataframe

rng = pd.date_range('1/1/2012', periods=20, freq='H')

rain = np.random.choice([0,0.5,1,2], size=20, p=[0.4,0.2,0.2,0.2])



df = pd.DataFrame()

df['data'] = rng

df['rain'] = rain



# convert rain to boolean

df['is_rain'] = df['rain'] > 0



# shift rain for one slot to recognice if the state has changed

data = list(df['is_rain'].values[0:-1])

shifted = [data[0]]

shifted.extend(data)

df['is_rain_next'] = shifted



# get start and endpoints of rain (the end has to be excluded! 

# it is always the next period, when it isn't raining anymore)

df['rain_start'] = df['is_rain'] < df['is_rain_next']

df['rain_end'] = df['is_rain'] > df['is_rain_next']



# this are the starts and ends you can use them for getting the groups from dataframe

df[df['rain_start']].index

df[df['rain_end']].index

Warning: The arrays can have different sizes, thats caused by the unknown state at the end and at the beginning. So check if the first element in rain_end smaller than the first element in rain_start than you can delete it. Also if the last element of rain_start is greater than the last element of rain_end (sound logical i think)