How to dynamically fill column values based on other column values?

user_14736736 0
Viewed 142

Imagine you have the following df:

d = {'taxrate#1': [0.21, np.nan, np.nan], 'taxrate#2': [0.0, np.nan, np.nan]}
colstbf = pd.DataFrame(data=d)
colstbf

    taxrate#1   taxrate#2
0   0.21          0.0
3   NaN           NaN
5   NaN           NaN

And another df:

d = {'btw-percentage#1': [0.21, 0.21, 0], 'btw-percentage#2': [0.06, np.nan, 0.21]}
colsfilling = pd.DataFrame(data=d)
colsfilling


btw-percentage#1    btw-percentage#2
0   0.21            0.06
1   0.21            NaN
2   0.00            0.21

I want to fill the df with other values of another df when the values in the df are NaN.

However, because the taxrate items may be large (10 or bigger depending on document) I want to do this dynamically.

So I've tried the following:

cols_to_check = []
cols_to_check = colstbf.filter(regex = 'taxrate').columns
colstbf['is_na'] = colstbf[cols_to_check].isnull().apply(lambda x: all(x), axis=1)

colstbf = np.where(df['is_na'] == True, colstbf, df['taxrate#1'])

AND:

colsfinal = colstbf.fillna(colsfilling)

However, the above is not working..

desired output:

    taxrate#1   taxrate#2
0   0.21          0.0
3   0.21          NaN
5   0.00          0.21


colstbf = df.filter(like='taxrate')
colsfilling = dfresult.filter(like='btw-percent').astype(float)
colsfilling = colsfilling/100
colsfilling.columns = colstbf.columns
colstbf.combine_first(colsfilling)
df = df.join(colstbf, lsuffix='_left')
df = df[df.columns.drop(list(df.filter(regex='_left')))]
df

Please help!

pandas dataframe numpy python
2 Answers

You can also use pandas.DataFrame.update() by setting overwrite argument.

overwrite bool, default True How to handle non-NA values for overlapping keys:

  • True: overwrite original DataFrame’s values with values from other.
  • False: only update values that are NA in the original DataFrame.
colstbf.update(colsfilling.rename(columns=dict(zip(colsfilling.columns, colstbf.columns))), overwrite=False)

print(colstbf)

   taxrate#1  taxrate#2
0       0.21       0.00
1       0.21        NaN
2       0.00       0.21
user_103151630

You can use df.combine_first here.

colsfilling.columns = colstbf.columns
colstbf.combine_first(colsfilling)

  taxrate#1  taxrate#2
0       0.21       0.00
1       0.21        NaN
2       0.00       0.21
user_124164530
Related