Imputation missing values other than using Mean, Median in python

Question

I heard that Mean, Median isn't the best way to impute the missing values, why would that be?

In my scenario, I have data like this

Brand|Value
A|2,
A|NaN,
A|4,
B|8,
B|NaN,
B|10,
C|9,
C|11

if using mean imputation the data would be

Brand|Value
A|2,
A|7.3,
A|4,
B|8,
B|7.3,
B|10,
C|9,
C|11

which does make sense for brand B to be 7.3 but doesn't make sense if brand A 7.3 because the value of Brand A has its tendency somewhere around 2 and 8 is there any other way to fill the missing values based on the Brand?

This is an example of data with only 2 features, with 1 feature that may has pattern for missing values, what if there are like 20 features, and there would be more than one features that may have pattern to better define the missing values.

How to apply this in Python?

Answer 1

So if you want to impute some missing values, based on the group that they belong to (in your case A, B, ...), you can use the groupby method of a Pandas DataFrame. So make sure your data is in one of those first.

import pandas as pd
df = pd.DataFrame(your_data)              # read documentation to achieve this

Then, it is just a case of chaining a few steps together:

df["Value"] = df.groupby("Brand")["Value"].transform(lambda x: x.fillna(x.mean()))

• df.groupby simply groups the dataframe into sub-dataframes (groups), such that each group only contains one Brand
• transform() will apply a function to a dataframe - so to each of the individual groups created in groupby
• the nameless function (a lambda function) calls the DataFrame's fillna() method on each dataframe, using just the mean() to fill the gaps

You can simply substitute the mean() method for anything you like. You could also create a more complicated function, ifyou need it, and replace that lambda function. It would simply need to take a dataframe as input and return a dataframe with a comparable index.

Answer 2

What is the column I want to impute is not numerical?

Scenario as below:

There are 3 columns col1, col2 and col3 in a data frame. col1 is missing some rows. I want to fill these missing values with value of col1 from other row which have matching value of col2 and col3. How can I do this?

Example: How can I fill the NaN in row3:col1 with A, as row3:col2 and col3 matches row1:col2 and col3

xxxx col1 col2 col3 row1 A 1 11 row2 B 2 22 row3 NaN 1 11 row4 C 3 33