According to the Docs, you can use a Dataframe as the value parameter for .fillna()
http://pandas.pydata.org/pandas-docs/dev/generated/pandas.DataFrame.fillna.html
But does the dataframe need to be identical shape? If so, why does the first example give me the desired output?
Using this df:
mukey hzdept_r hzdepb_r sandtotal_r silttotal_r
425897 0 61
425897 61 152 5.3 44.7
425911 0 30 30.1 54.9
425911 30 74 17.7 49.8
425911 74 84
I can run:
df = pd.read_clipboard()
df1 = df.set_index('mukey')
df1.fillna(df.groupby('mukey').mean(),inplace=True)
and df1 results in the desired df:
hzdept_r hzdepb_r sandtotal_r silttotal_r
mukey
425897 0 61 5.3 44.70
425897 61 152 5.3 44.70
425911 0 30 30.1 54.90
425911 30 74 17.7 49.80
425911 74 84 23.9 52.35
However, when I try to run the same code on a larger df, it breaks with InvalidIndexError.
df = pd.read_csv('www004.csv')
df1 = df.set_index('mukey')
df1.fillna(df.groupby('mukey').mean(),inplace=True)
Error:
InvalidIndexError Traceback (most recent call last)
<ipython-input-126-a1038ea351c9> in <module>()
----> 1 df1.fillna(df.groupby('mukey').mean(),inplace=True)
/Users/liamfoley/anaconda/lib/python2.7/site-packages/pandas/core/generic.pyc in fillna(self, value, method, axis, inplace, limit, downcast)
2410 downcast=downcast)
2411 elif isinstance(value, DataFrame) and self.ndim == 2:
-> 2412 new_data = self.where(self.notnull(), value)
2413 else:
2414 raise ValueError("invalid fill value with a %s" % type(value))
/Users/liamfoley/anaconda/lib/python2.7/site-packages/pandas/core/generic.pyc in where(self, cond, other, inplace, axis, level, try_cast, raise_on_error)
3306 not all([other._get_axis(i).equals(ax)
3307 for i, ax in enumerate(self.axes)])):
-> 3308 raise InvalidIndexError
3309
3310 # slice me out of the other
InvalidIndexError:
I can get around that by creating a means_df that has identical shape.
import pandas as pd
df = pd.read_csv('www004.csv').set_index('mukey')
means = df.groupby(level=0).mean()
means_df = pd.merge(pd.DataFrame(df.index),means,
left_on='mukey',right_index=True,how='left').set_index('mukey')
df1 = df.fillna(means_df)
That gives me the desired result:
df.ix[426184]
hzdept_r hzdepb_r sandtotal_r silttotal_r claytotal_r om_r
mukey
426184 0 18 30.1 54.9 15 3.5
426184 18 46 58.2 17.8 24 NaN
426184 46 152 NaN NaN 5 NaN
df1.ix[426184]
hzdept_r hzdepb_r sandtotal_r silttotal_r claytotal_r om_r
mukey
426184 0 18 30.10 54.90 15 3.5
426184 18 46 58.20 17.80 24 3.5
426184 46 152 44.15 36.35 5 3.5
Related: Pandas fill missing values in dataframe from another dataframe
