Replace value in any column in pandas dataframe

Question

In the data frame below:

 T2MN   T2MX   RH2M DFP2M RAIN 
 6.96   9.32  84.27  5.57    - 
 6.31  10.46  -      5.63    - 
  -    10.66  79.38  3.63    - 
 0.79   4.45  94.24  1.85    - 
 1.45   3.99  91.71  1.17    - 

How to replace everything -with NaN. I don’t want to specify column names, since I don’t know which columns will be before-

Answer 1

If these are strings, then your floats are probably also strings.

Assuming your framework df, I will try

pd.to_numeric(df.stack(), 'coerce').unstack()

---

Deeper explanation

Pandas does not usually represent missing floats with '-'. Therefore, it '-'must be a string. Thus, dtypeany column with '-'in it should be 'object'. This makes it very likely that all the analyzed data left the floats as a string.

from io import StringIO
import pandas as pd

txt = """T2MN   T2MX   RH2M DFP2M RAIN 
 6.96   9.32  84.27  5.57    - 
 6.31  10.46  -      5.63    - 
  -    10.66  79.38  3.63    - 
 0.79   4.45  94.24  1.85    - 
 1.45   3.99  91.71  1.17    - """

df = pd.read_csv(StringIO(txt), delim_whitespace=True)
print(df)

   T2MN   T2MX   RH2M  DFP2M RAIN
0  6.96   9.32  84.27   5.57    -
1  6.31  10.46      -   5.63    -
2     -  10.66  79.38   3.63    -
3  0.79   4.45  94.24   1.85    -
4  1.45   3.99  91.71   1.17    -

dtypes?

print(df.dtypes)

T2MN      object
T2MX     float64
RH2M      object
DFP2M    float64
RAIN      object
dtype: object

?

print(type(df.iloc[0, 0]))

<class 'str'>

, '-' , float. pd.to_numeric errors='coerce', np.nan. pd.to_numeric pd.DataFrame, stack unstack.

pd.to_numeric(df.stack(), 'coerce').unstack()

   T2MN   T2MX   RH2M  DFP2M  RAIN
0  6.96   9.32  84.27   5.57   NaN
1  6.31  10.46    NaN   5.63   NaN
2   NaN  10.66  79.38   3.63   NaN
3  0.79   4.45  94.24   1.85   NaN
4  1.45   3.99  91.71   1.17   NaN

Answer 2

replace() :

In [10]: df.replace('-', 'NaN')
Out[10]: 
   T2MN   T2MX   RH2M  DFP2M RAIN
0  6.96   9.32  84.27   5.57  NaN
1  6.31  10.46    NaN   5.63  NaN
2   NaN  10.66  79.38   3.63  NaN
3  0.79   4.45  94.24   1.85  NaN
4  1.45   3.99  91.71   1.17  NaN

Answer 3

, numpy.nan NaN, , fillna/isnull/notnull pandas.Series/pandas.DataFrame

import pandas as pd

import numpy as np

df = pd.DataFrame([['-']*10]*10)

df = df.replace('-',np.nan)

Answer 4

, CSV/FWF... , '-' Pandas, NaN:

df = pd.read_csv(filename, na_values=['NaN', 'nan', '-'])

:

In [79]: df
Out[79]:
   T2MN   T2MX   RH2M  DFP2M  RAIN
0  6.96   9.32  84.27   5.57   NaN
1  6.31  10.46    NaN   5.63   NaN
2   NaN  10.66  79.38   3.63   NaN
3  0.79   4.45  94.24   1.85   NaN
4  1.45   3.99  91.71   1.17   NaN

In [80]: df.dtypes
Out[80]:
T2MN     float64
T2MX     float64
RH2M     float64
DFP2M    float64
RAIN     float64
dtype: object