I want to replace NA values with the mean of other column with the same year.

Note:
To replace NA values for Canada data, I want to use only the mean of Canada, not the mean from the whole dataset of course.

Here's a sample dataframe filled with random numbers. And some NA how i find them in my dataframe:

What's the easiest way with pandas to replace those NA with the mean values of the other years? And is it possible to write a code for which it is possible to go through every NA and replace them (Inhabitants, Area, Cats, Dogs at once)?

CodePudding user response：

Note Example is based on your additional data source from the comments

Replacing the NA-Values for multiple columns with mean() you can combine the following three methods:

• fillna() (Iterating per column axis should be 0, which is default value of fillna())
• groupby()
• transform()

Create data frame from your example:

df = pd.read_excel('https://happiness-report.s3.amazonaws.com/2021/DataPanelWHR2021C2.xls')

Call fillna() and iterate over all columns grouped by name of country:

df = df.fillna(df.groupby('Country name').transform('mean'))

Check your result for Canada:

df[df['Country name'] == 'Canada']

CodePudding user response：

This also works:

In [2]:

df = pd.read_excel('DataPanelWHR2021C2.xls')

In [3]:

# Check for number of null values in df
df.isnull().sum()

Out [3]:

Country name                          0
year                                  0
Life Ladder                           0
Log GDP per capita                   36
Social support                       13
Healthy life expectancy at birth     55
Freedom to make life choices         32
Generosity                           89
Perceptions of corruption           110
Positive affect                      22
Negative affect                      16
dtype: int64

SOLUTION

In [4]:

# Adds mean of column to any NULL values
df.fillna(df.mean(), inplace=True)

In [5]:

# 2nd check for number of null values
df.isnull().sum()

Out [5]: No more NULL values

Country name                        0
year                                0
Life Ladder                         0
Log GDP per capita                  0
Social support                      0
Healthy life expectancy at birth    0
Freedom to make life choices        0
Generosity                          0
Perceptions of corruption           0
Positive affect                     0
Negative affect                     0
dtype: int64