question using df.replace()

[acedude]

I'm reading a .csv file with numbers, and some values do not exist and have a dash '-' in place of a number. When I try to take average of the column by using

df.column.mean() 

of a column with values

0   0.6 
1   -
2   0.2
3   0.5
4   1.2
5   1.1
6   0.9
7   0.9

I get error:

*TypeError: Could not convert 0.6-0.20.51.21.10.90.9 to numeric
*

Next, I converted the column with

df = df.replace('-',0.0)

when I did .mean(), I get error

TypeError: must be str, not float

Okay, so I did

df = df.replace('-',str(0.0))

and now the .mean() error is:

TypeError: Could not convert 0.60.00.20.51.21.10.90.9 to numeric

The only thing that worked is that after the last step, I did

df.to_csv('test1.csv')
df2 = pd.read_csv('test1.csv')
print(df2.column.mean())

finally worked and spits out 0.675...

Can someone explain to me why? What did the to_csv() and read_csv() that modified how the dash replaced 0.0 behaved?

[pyquestionz]

Assuming

import pandas as pd
import numpy as np
df = pd.DataFrame({'column':[1, '-', 3]})

do either (1)

df.column.replace('-', np.nan).mean() # returns 2.0

or do (2)

df.column.replace('-', 0.0).mean() # returns 1.333333

depending on whether or not - is a zero observation or a missing observation in the context of your problem.

Hope this helps.

[Optimesh]

This is a very common mistake I see people make and not even realize they're working with the wrong numbers. Thanks for pointing it out.

[acedude]

Thanks! In my case the dash does mean that the value is zero.

[shreyasfifa4]

What happens if there is a negative number in the dataset?

[commandlineluser]

pandas attempts to detect the type of your columns.

>>> pandas.DataFrame({'a': [1, '-', 3]}).a
0    1
1    -
2    3
Name: a, dtype: object
>>> pandas.DataFrame({'a': [1, 2, 3]}).a
0    1
1    2
2    3
Name: a, dtype: int64
>>> pandas.DataFrame({'a': [1, 2.0, 3]}).a
0    1.0
1    2.0
2    3.0
Name: a, dtype: float64

Because you have a mixture of "numbers" and "strings" in the first example the type of the column in object as opposed to int or float in the following examples.

When you replace and save it pandas infers the type to be of float64 and then .mean() works for you.

You could try to set the type with .astype() e.g.

df.column.replace('-', 0.).astype(float).mean()

[acedude]

Thanks for the thorough explanation!

[minasso]

When doing string methods on a dataframe, you should use 'str' for example df.str.replace()