pandas dataframe, if condition match and index next to each other: add value and delete the row used

Question

I want to drop queried dataframe - rows and replace it with new data when the index are next to each other.

basically adding value i-1 to i.

is this possible to be done ?

please see the example data below: if i have a data frame as above: i want to amend the dataframe as below


import pandas as pd 

training_data = pd.DataFrame()

training_data['a'] = [1,1,2,2,2,3,3,3,4,4,5,6,7]
training_data['b'] = [1,1,2,2,2,3,3,3,4,4,5,6,7]

training_data['c'] = [1,1,2,2,2,3,3,3,4,4,5,6,7]
training_data['condition'] = [True,True,False,False,True,True,False,False,True,False,False,False,True]


True_data = training_data[training_data['condition'] == True]


True_data:

index    a    b    c    condition   

0       1     1    1      True      

1       1     1    1      True 

4       2     2    2      True

5       3     3    3      True

8       4     4    4      True 

12      7     7    7      True 


desired output:

index    a    b    c    condition   


new     2     2    2      True 

new     5     5    5      True 

8       4     4    4      True

12      7     7    7      True

All added values have index next to each other however,8, 12 does not thus will not be added.

Thank you for all the help.

hey Bisal, can you add your data as text and any attempts you've made to solve this problem on your own? please read [mcve] — Umar.H, Nov 23 '20 at 14:24
@ScottBoston, sorry scott that's meant to be counted my bad. : i will edit it accordingly — bmaster69, Nov 23 '20 at 14:59

Scott Boston · Accepted Answer · 2020-11-23T15:08:21.437

1

Try:

grp = (~training_data['condition']).cumsum()
training_data.query('condition')\
             .groupby(grp)\
             .agg({'a':'sum','b':'sum','c':'sum','condition':'first'})

Output:

           a  b  c  condition
condition                    
0          2  2  2       True
2          5  5  5       True
4          8  8  8       True
6          7  7  7       True

Updated with new data:

training_data['grp'] =  (~training_data['condition']).cumsum()

training_data.query('condition').groupby('grp').agg({'a':'sum','b':'sum','c':'sum','condition':'first'})

Output:

     a  b  c  condition
grp                    
0    2  2  2       True
2    5  5  5       True
4    4  4  4       True
7    7  7  7       True

edited Nov 23 '20 at 15:08

answered Nov 23 '20 at 14:59

Scott Boston

147,308
15
139
187

i am getting list with column as condition :series(13,) – bmaster69 Nov 23 '20 at 15:05
Check the dtype of training_data['condition'] column is it a boolean and not a string? – Scott Boston Nov 23 '20 at 15:06
it does say dtype: bool when checked – bmaster69 Nov 23 '20 at 15:08
Sorry yes, its a bool – bmaster69 Nov 23 '20 at 15:08

pandas dataframe, if condition match and index next to each other: add value and delete the row used

1 Answers1