CODEWITHSUNDEEP
pythonpandasdataframenumpy
Max
Max

Reputation: 471

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

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!

Upvotes: 2

Views: 244

Answers (2)

Ynjxsjmh
Ynjxsjmh

Reputation: 30032

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

Upvotes: 2

Ch3steR
Ch3steR

Reputation: 20669

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

Upvotes: 1

Related Questions