Replace null values in a dataframe using another dataframe

user_15334219 0
Viewed 308

I'm trying to replace null values in dataframe d using dataframe f.

d and f are linked by EGI. In d, EGI is a column and is not unique. In f, EGI is unique and is this dataframe's index.

For each row in d, I need the values in that row to be 'masked' by the row in f with corresponding EGI.

Sample data:

d = pd.DataFrame({'EGI':['a1','b2','a1','d4'],'A': ['x', np.nan, 'z', 'e'], 'B': [pd.NaT, 6, 7, 9], 'C': [2, 1, None, 9], 'D': [2, None, np.nan, None]})

  EGI    A    B    C    D
0  a1    x  NaT  2.0  2.0
1  b2  NaN    6  1.0  NaN
2  a1    z    7  NaN  NaN
3  d4    e    9  9.0  NaN

f = pd.DataFrame({'B': [5, 8, 9], 'A': ['w', 'y', np.nan], 'D': [None, np.nan, 8], 'test': [5, 8, 9]}, index=['b2', 'a1', 'c3'])

    B    A    D  test
b2  5    w  NaN     5
a1  8    y  NaN     8
c3  9  NaN  8.0     9

Expected output:

  EGI    A    B    C    D
0  a1    x    8  2.0  2.0
1  b2    w    6  1.0  NaN
2  a1    z    7  NaN  NaN
3  d4    e    9  9.0  NaN

What I tried:

m = d.isnull()
m.index = d['EGI'].tolist()
m = m.drop(['EGI'], axis = 1)
d.mask(m, f)

   EGI    A    B   C   D
0  NaN  NaN  NaN NaN NaN
1  NaN  NaN  NaN NaN NaN
2  NaN  NaN  NaN NaN NaN
3  NaN  NaN  NaN NaN NaN
pandas mask dataframe python
1 Answers

If dataframes d and f have matching row indexes, we can just fillna:

d.fillna(f)

But in OP's example, the indexes do not match up, so we just need to align them first.

One-liner

Use set_index and reindex to align the indexes to EGI and then fillna:

d.set_index('EGI').fillna(f.reindex(d.EGI))

#    EGI  A    B    C    D
# 0   a1  x  8.0  2.0  2.0
# 1   b2  w    6  1.0  NaN
# 2   a1  z    7  NaN  NaN
# 3   d4  e    9  9.0  NaN

Step-by-step

  1. Use set_index to set d's index to EGI:

    d = d.set_index('EGI')

#        A    B    C    D
# EGI                    
# a1     x  NaT  2.0  2.0
# b2   NaN    6  1.0  NaN
# a1     z    7  NaN  NaN
# d4     e    9  9.0  NaN

  2. Use reindex to align f's index to d's index:

    f = f.reindex(d.index)

#        B    A   D  test
# EGI                    
# a1   8.0    y NaN   8.0
# b2   5.0    w NaN   5.0
# a1   8.0    y NaN   8.0
# d4   NaN  NaN NaN   NaN

  3. Use fillna to fill d's NaNs with f:

    d.fillna(f)

#    EGI  A    B    C    D
# 0   a1  x  8.0  2.0  2.0
# 1   b2  w    6  1.0  NaN
# 2   a1  z    7  NaN  NaN
# 3   d4  e    9  9.0  NaN

Note that the column indexes of d and f are not aligned and do not need to be. We only need to align the row indexes, and fillna will handle the rest.

user_131383640
Related