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'))
df
and the pd.Series s
s = pd.Series([10, 20, 30], list('abc'))
How do I fill in missing values of df with the corresponding values of s based on the index of s and the index of df
For example:
df.loc['c', 'x'] is NaNs.loc['c'] is 30expected result
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
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.
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)
