0

I have a csv file with multiple columns containing numeric values. However, some rows are missing a value in one or more columns. The missing value is denoted by 'x'.

How would I replace all the missing values ('x') with the average of all values in their column using Pandas?

df=  pd.read_csv('file.csv')
Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
lilek3
  • 27
  • 1
  • 4
  • Does this answer your question? [pandas DataFrame: replace nan values with average of columns](https://stackoverflow.com/questions/18689823/pandas-dataframe-replace-nan-values-with-average-of-columns) – Rodalm Nov 10 '21 at 23:53
  • Also [reading file with missing values in python pandas](https://stackoverflow.com/questions/12514590/reading-file-with-missing-values-in-python-pandas) – Rodalm Nov 10 '21 at 23:54

3 Answers3

1

The first thing to recognize is the columns that have 'x' in them are not integer datatypes. They are object datatypes.

df = pd.read_csv('file.csv')

df

    Col1    Col2
0   1   22
1   2   44
2   3   x
3   4   88
4   5   110
5   6   132
6   7   x
7   8   176
8   9   198
9   10  x

df.dtypes

Col1     int64
Col2    object
dtype: object

In order to get the mean of Col2, it needs to be converted to a numeric value.

df['Col2'] = pd.to_numeric(df['Col2'], errors='coerce').astype('Int64')

df.dtypes
Col1    int64
Col2    Int64
dtype: object

The df now looks like so:

df 

Col1    Col2
0   1   22
1   2   44
2   3   <NA>
3   4   88
4   5   110
5   6   132
6   7   <NA>
7   8   176
8   9   198
9   10  <NA>

Now we can use fillna() with df['Col2'].mean():

df['Col2'] = df['Col2'].fillna(df['Col2'].mean())

df
    Col1    Col2
0   1   22
1   2   44
2   3   110
3   4   88
4   5   110
5   6   132
6   7   110
7   8   176
8   9   198
9   10  110
cgapperi
  • 166
  • 1
  • 4
  • 1
    "The first thing to recognize is the columns that have 'x' in them are not integers. They are objects." Their dtypes are `object`. The actual columns aren't themselves integers, even if they contain only integers. – Acccumulation Nov 11 '21 at 00:47
0
avg = df[column].mean()
df[column] = df[column].apply(lambda v: avg if v=="x" else v)
Larry the Llama
  • 958
  • 3
  • 13
0

You're going to run into problems taking the mean of columns that have non-numeric values such as 'x'. You'll have to fix that first, such as with df = df.replace('x',np.nan) (this works only if x is the only non-numeric value) and then you can dodf = df.fillna(df.mean()). If you start out with integers, the columns with x in them will be converted to float (even the rows that weren't replaced), so if you don't want that, you'll have to do more work.

Acccumulation
  • 3,491
  • 1
  • 8
  • 12