equivalent to having, lag or similar in pandas

user_6099022 0
Viewed 63

My aim is selecting the rows in which the sum of the column “value” is >1000, considering the cumulative value for each month. For instance client1 reaches that value in month 3, since the sum of value during the first 3 months is 1160, as shown in the target_data. My hints were about playing with group by, I was wondering about also using filter or shift, but I am stacked. In "target_data" I show the desidered result



raw_data = {'client': ['client1','client1','client1','client1','client2','client2','client2','client2','client2'],
'month': [1,2,3,4,1,2,3,4,5],
'value': [10,350,800,400,300,300,300,300,10]}

df = pd.DataFrame(raw_data, columns = ['client','month','value'])
df

target_data = {'client': ['client1','client2'],
'month': [3,4],
'value': [800,300],
'cumulative': [1160,1200]}


df2 = pd.DataFrame(target_data, columns = ['client','month','value','cumulative'])

df2


df_grouped = df.groupby(['client'])['value'].sum().reset_index(name='sum_value')


df_grouped.loc[df_grouped['sum_value'] > 1000]
pandas dataframe python
1 Answers

You can use groupby and cumsum here to create a Series of cumulative sums. Then use gt (greater than) and idxmax to get the first index where the condition is met, we'll use loc to filter to these indices and finally assign the cumulative sum back in with assign:

s_cum = df.groupby('client')['value'].cumsum() 
df.loc[s_cum.gt(1000).groupby(df['client']).idxmax()].assign(cumulative=s_cum)

[out]

    client  month  value  cumulative
2  client1      3    800        1160
7  client2      4    300        1200
user_102015800
Related