# How do I replace NaN values using group by pivot_table in pandas DataFrame?

I am working on a machine learning practice problem, from https://datahack.analyticsvidhya.com/contest/practice-problem-big-mart-sales-iii/#ProblemStatement

I want to replace the null values in the column 'Item_Weight' and for that I am using the mean values given by a pivot_table where I calculated the mean of 'Item_Weight' and grouping the mean by column 'Item_Identifier' of the dataset.

item_weight_mean = ds.pivot_table(values='Item_Weight',columns='Item_Identifier')
loc2 = ds['Item_Weight'].isnull()
ds.loc[loc2, 'Item_Weight'] = ds.loc[loc2, 'Item_Identifier'].apply(lambda x: item_weight_mean[x])


I am getting an error for the same code.

(key)
-> 2902             indexer = self.columns.get_loc(key)
2903             if is_integer(indexer):
2904                 indexer = [indexer]

D:\Important Applications\anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
2895                 return self._engine.get_loc(casted_key)
2896             except KeyError as err:
-> 2897                 raise KeyError(key) from err
2898
2899         if tolerance is not None:

KeyError: 'FDN52'


How do I remove this error?

• I suggest adding some more output, for example what does the first 5 rows of item_weight_mean look like? – fswings May 15 at 19:48

Consider using df.fillna(). That has a variety of options to replace nans with values of choices. You can apply it on either the complete data frame or on specific columns (I.e Series).

• But using fillna(), I cannot repalce the values mean of column grouped by some other feature. Can this be done using fillna()? – Amandeep Singh May 10 at 14:58

The output of the pivot_table function is a dataframe, which you can confirm using the type command.

Secondly, the syntax item_weight_mean[x] is a way of indexing columns, whereas I suspect you want to index rows.

So the error message states that the key FDN52 Isn't a column I believe.