sum of column using nan as break point with if statement-CodePudding

Sir,

example date column A header name column B header sold data in

             column A   clumn B
 header        Name       Sold
               ABC        NAN
               FP         200
               FP         300
               XYZ        NAN
               SC          50
               SC         100

sum between of column B replace abc value nan with sum value (200 300) and xyz nan value the sum of column b (50 100) thank you

CodePudding user response：

here is my way:

sample input:

import pandas as pd
import numpy as np
df = pd.DataFrame({'col1':['abc','c1','c2','xyz','c3','c4'], 'col2':[np.nan,100,200,np.nan,10,20]})


col1    col2
abc    NaN
c1     100.0
c2     200.0
xyz    NaN
c3     10.0
c4     20.0

create groups separated by NaN:

c = 0
for idx in df.index:
    if pd.isna(df.iloc[idx].col2):
        c =1
    df.loc[idx, 'group'] = c

fill NaN for each group:

for group in df.groupby('group'):
    df[df.group == group[0]] = df[df.group == group[0]].fillna(group[1].col2.sum())

output:

col1    col2    group
abc   300.0     1.0
c1    100.0     1.0
c2    200.0     1.0
xyz   30.0      2.0
c3    10.0      2.0
c4    20.0      2.0