CODEWITHSUNDEEP
pythonpython-3.xpandasdataframefillna
Kuk1a
Kuk1a

Reputation: 349

Pandas fillna multiple columns with values from corresponding columns without repeating for each

Let's say I have a DataFrame like this:

x = pd.DataFrame({'col1_x': [15, np.nan, 136, 93, 743, np.nan, np.nan, 91] ,                                        
                  'col2_x': [np.nan, np.nan, 51, 22, 38, np.nan, 72, np.nan], 
                  'col1_y': [10, 20, 30, 40, 50, 60, 70, 80],
                  'col2_y': [93, 24, 52, 246, 142, 53, 94, 2]})

And I want to fill the NaN values in col_x with the values in col_y respectively, I can do this:

x['col1_x'] = x['col1_x'].fillna(x['col1_y'])
x['col2_x'] = x['col2_x'].fillna(x['col2_y'])

print(x)

Which will yield:

   col1_x  col2_x  col1_y  col2_y
0    15.0    93.0      10      93
1    20.0    24.0      20      24
2   136.0    51.0      30      52
3    93.0    22.0      40     246
4   743.0    38.0      50     142
5    60.0    53.0      60      53
6    70.0    72.0      70      94
7    91.0     2.0      80       2

But requires to repeat the same function with different variables, now let's assume that I have a bigger DataFrame with much more columns, is it possible to do it without repeating?

Upvotes: 0

Views: 1708

Answers (3)

Rob Raymond
Rob Raymond

Reputation: 31166

  • you can use **kwargs to assign()
  • build up a dict with a comprehension to build **kwargs
import pandas as pd
import numpy as np
x = pd.DataFrame({'col1_x': [15, np.nan, 136, 93, 743, np.nan, np.nan, 91] ,                                        
                  'col2_x': [np.nan, np.nan, 51, 22, 38, np.nan, 72, np.nan], 
                  'col1_y': [10, 20, 30, 40, 50, 60, 70, 80],
                  'col2_y': [93, 24, 52, 246, 142, 53, 94, 2]})

x.assign(**{c:x[c].fillna(x[c.replace("_x","_y")]) for c in x.columns if "_x" in c})
col1_x col2_x col1_y col2_y
0 15 93 10 93
1 20 24 20 24
2 136 51 30 52
3 93 22 40 246
4 743 38 50 142
5 60 53 60 53
6 70 72 70 94
7 91 2 80 2

How does it work

# core - loop through columns that end with _x and generate it's pair column _y
{c:c.replace("_x","_y") 
 for c in x.columns if "_x" in c}

# now we have all the pairs of a columns let's do what we want - fillna()
{c:x[c].fillna(x[c.replace("_x","_y")]) for c in x.columns if "_x" in c}

# this dictionary matches this function.... https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.assign.html
# so final part is call the function with **kwargs
x.assign(**{c:x[c].fillna(x[c.replace("_x","_y")]) 
            for c in x.columns if "_x" in c})

Upvotes: 1

imxitiz
imxitiz

Reputation: 3987

Are you trying to make this type of function :

def fil(fill,fromm):
    fill.fillna(fromm,inplace=True)

fil(x['col1_x'],x['col1_y'])

Or if you are sure about dataframe(x) then this :

def fil(fill,fromm):
    x[fill].fillna(x[fromm],inplace=True)

fil('col1_x','col1_y')

For your code :

import pandas as pd
import numpy as np

x = pd.DataFrame({'col1_x': [15, np.nan, 136, 93, 743, np.nan, np.nan, 91] ,                                
                  'col2_x': [np.nan, np.nan, 51, 22, 38, np.nan, 72, np.nan], 
                  'col1_y': [10, 20, 30, 40, 50, 60, 70, 80],
                  'col2_y': [93, 24, 52, 246, 142, 53, 94, 2]})


def fil(fill,fromm):
    x[fill].fillna(x[fromm],inplace=True)

fil('col1_x','col1_y')
fil('col2_x','col2_y')

print(x)

"""
   col1_x  col2_x  col1_y  col2_y
0    15.0    93.0      10      93
1    20.0    24.0      20      24
2   136.0    51.0      30      52
3    93.0    22.0      40     246
4   743.0    38.0      50     142
5    60.0    53.0      60      53
6    70.0    72.0      70      94
7    91.0     2.0      80       2
"""

Additionally, if you have column name like col1_x,col2_x,col3_x.... same for y then you may automate it like this :

for i in range(1,3):
    fil(f'col{i}_x',f'col{i}_y')

Upvotes: 1

Tom Ron
Tom Ron

Reputation: 6181

You can use the following notation -

x.fillna({"col1_x": x["col1_y"], "col2_x": x["col2_y"]})

Assuming you can extract all the indices numbers you can do -

replace_dict = {f"col{item}_x":x[f"col{item}_y"] for item in indices}
x = x.fillna(replace_dict}

Upvotes: 2

Related Questions