Fill Multiple Missing Values With Series Based On Index Values

consider the pd.DataFrame df df = pd.DataFrame([ [np.nan, 1, np.nan], [2, np.nan, np.nan], [np.nan, np.nan, 3 ], ], list('abc'), list('xyz

Solution 1:

pandas handles this on a column basis with no issues. Suppose we had a different s

s = pd.Series([10, 20, 30], ['x', 'y', 'z'])

then we could

df.fillna(s)

      x     y     z
a  10.0   1.0  30.0
b   2.0  20.0  30.0
c  10.0  20.0   3.0

But that's not what you want. Using your s

s = pd.Series([10, 20, 30], ['a', 'b', 'c'])

then df.fillna(s) does nothing. But we know that it works for columns, so:

df.T.fillna(s).T

      x     y     z
a  10.0   1.0  10.0
b   2.0  20.0  20.0
c  30.0  30.0   3.0

Solution 2:

Another way:

def fillnull(col):
    col[col.isnull()] = s[col.isnull()]
    return col

df.apply(fillnull)

Note that it's less efficient than @Brian's way (9ms per loop versus 1.5ms per loop on my computer)

Solution 3:

Here's a NumPy approach -

mask = np.isnan(df.values)
df.values[mask] = s[s.index.searchsorted(df.index)].repeat(mask.sum(1))

Sample run -

In [143]: df
Out[143]: 
     x    y    z
a  NaN  1.0  NaN
b  2.0  NaN  NaN
d  4.0  NaN  7.0
c  NaN  NaN  3.0

In [144]: s
Out[144]: 
a    10
b    20
c    30
d    40
e    50
dtype: int64

In [145]: mask = np.isnan(df.values)
     ...: df.values[mask] = s[s.index.searchsorted(df.index)].repeat(mask.sum(1))
     ...: 

In [146]: df
Out[146]: 
      x     y     z
a  10.0   1.0  10.0
b   2.0  20.0  20.0
d   4.0  40.0   7.0
c  30.0  30.0   3.0

Please note that if the index values of s are not sorted, we need to use extra argument sorter with searchsorted.