Farword fill based on other column with group by

user_9097114 0
Viewed 14

I am new to python and i have struct at forword fill. i have data frame (df_input) i need to forword fill d1 column up to the value in d2 column group by Name & type columns

import pandas as pd
data_input = {'Name':['Renault', 'Renault', 'Renault', 'Renault','Renault','Renault','Renault','Renault','Renault'],
        'type':['Duster', 'Duster', 'Duster', 'Duster','Duster','Triber','Triber','Triber','Triber'],
         'd1':['nan','10','nan','nan','nan','nan','20','nan','nan'],
         'd2':['nan','nan','nan','200','nan','nan','nan','nan','200']}  

df_input = pd.DataFrame(data_input)


data_out = {'Name':['Renault', 'Renault', 'Renault', 'Renault','Renault','Renault','Renault','Renault','Renault'],
        'type':['Duster', 'Duster', 'Duster', 'Duster','Duster','Triber','Triber','Triber','Triber'],
         'd1':['nan','10','nan','nan','nan','nan','20','nan','nan'],
         'd2':['nan','nan','nan','200','nan','nan','nan','nan','200'],
         'Out_col':['nan','10','10','10','nan','nan','20','20','20']} 

df_out = pd.DataFrame(data_out)

I have tried the following

df_out['Out_col']  = df_out.groupby(["Name","type"])["d1"].ffill()

Thanks in advance!

pandas numpy
1 Answers

Use:

#strings nans to NaNs missing values
df_input = df_input.replace('nan', np.nan)

You need replace missing values by backfilling values of column d2 with Series.mask:

s = df_input.groupby(["Name","type"])["d2"].bfill()
df_input['Out_col']  = df_input.groupby(["Name","type"])["d1"].ffill().mask(s.isna())
print (df_input)
      Name    type   d1   d2 Out_col
0  Renault  Duster  NaN  NaN     NaN
1  Renault  Duster   10  NaN      10
2  Renault  Duster  NaN  NaN      10
3  Renault  Duster  NaN  200      10
4  Renault  Duster  NaN  NaN     NaN
5  Renault  Triber  NaN  NaN     NaN
6  Renault  Triber   20  NaN      20
7  Renault  Triber  NaN  NaN      20
8  Renault  Triber  NaN  200      20
user_29010020
Related