Select and count specific values from pandas df

Question

I need some help with the following problem:

the data looks like

now I have to sum up dt until value reaches 50, always beginning from 0.

I have tried the following, but I am not sure if it is right,

df['value'].values[(df['value'].values > 0) & (df['value'].values < 50)] = 1    
df =  df.assign(counter_col_x = df.loc[df['value'].eq(1)].groupby(df['value'].ne(df['value'].shift()).cumsum()).ngroup())

Thanks for any hints!

Does the series always end at 50? Or there might be data after? — Quang Hoang
– Quang Hoang, Commented Feb 20, 2020 at 18:54
df.loc[df['value'].between(0,50, inclusive = False),'dt'].groupby(df['value'].ge(50).cumsum()).sum()? or size instead sum() — ansev
– ansev, Commented Feb 20, 2020 at 19:00
@Quang Hoang no it could also be any other value. Expected output is the sum from 0 to 50 and then start again from zero to 50. This should be a new column — Maik
– Maik, Commented Feb 20, 2020 at 19:10
df['value'].values[(df['value'].values > 0) & (df['value'].values < 50)] = 1 Won't that work without the .values ? — AMC
– AMC, Commented Feb 20, 2020 at 19:28

emiljoj · Accepted Answer · 2020-02-20 22:47:28Z

1

A simple way if you didn't have to restart several time within a column would have been the cumulating sum of a series. https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.cumsum.html

I hope you meant a cumulating sum of values until it reaches 50. I could only come up with a way of assigning the value of the Series in a loop. Hope that helps.

new_cum_sum = 0
list_of_values = []
for x, y in zip(test['dt'], test['value']):
    if y== 0:
        new_cum_sum = x
    elif y <= 50:
        new_cum_sum+=x
    list_of_values.append(new_cum_sum)

test['test_cum'] = list_of_values

answered Feb 20, 2020 at 22:47

emiljoj

4092 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select and count specific values from pandas df

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related