How do you change values to na in python?

Before proceeding with this post, it is important to understand the difference between NaN and None. One is a float type, the other is an object type. Pandas is better suited to working with scalar types as many methods on these types can be vectorised. Pandas does try to handle None and NaN consistently, but NumPy cannot.

My suggestion (and Andy's) is to stick with NaN.

But to answer your question...

pandas >= 0.18: Use na_values=['-'] argument with read_csv

If you loaded this data from CSV/Excel, I have good news for you. You can quash this at the root during data loading instead of having to write a fix with code as a subsequent step.

Most of the pd.read_* functions (such as read_csv and read_excel) accept a na_values attribute.

file.csv

A,B
-,1
3,-
2,-
5,3
1,-2
-5,4
-1,-1
-,0
9,0

Now, to convert the - characters into NaNs, do,

import pandas as pd
df = pd.read_csv('file.csv', na_values=['-'])
df

     A    B
0  NaN  1.0
1  3.0  NaN
2  2.0  NaN
3  5.0  3.0
4  1.0 -2.0
5 -5.0  4.0
6 -1.0 -1.0
7  NaN  0.0
8  9.0  0.0

And similar for other functions/file formats.

P.S.: On v0.24+, you can preserve integer type even if your column has NaNs (yes, talk about having the cake and eating it too). You can specify dtype='Int32'

df = pd.read_csv('file.csv', na_values=['-'], dtype='Int32')
df

     A    B
0  NaN    1
1    3  NaN
2    2  NaN
3    5    3
4    1   -2
5   -5    4
6   -1   -1
7  NaN    0
8    9    0

df.dtypes

A    Int32
B    Int32
dtype: object

The dtype is not a conventional int type... but rather, a Nullable Integer Type. There are other options.

Handling Numeric Data: pd.to_numeric with errors='coerce

If you're dealing with numeric data, a faster solution is to use pd.to_numeric with the errors='coerce' argument, which coerces invalid values (values that cannot be cast to numeric) to NaN.

pd.to_numeric(df['A'], errors='coerce')

0    NaN
1    3.0
2    2.0
3    5.0
4    1.0
5   -5.0
6   -1.0
7    NaN
8    9.0
Name: A, dtype: float64

To retain (nullable) integer dtype, use

pd.to_numeric(df['A'], errors='coerce').astype('Int32')

0    NaN
1      3
2      2
3      5
4      1
5     -5
6     -1
7    NaN
8      9
Name: A, dtype: Int32 

To coerce multiple columns, use apply:

df[['A', 'B']].apply(pd.to_numeric, errors='coerce').astype('Int32')

     A    B
0  NaN    1
1    3  NaN
2    2  NaN
3    5    3
4    1   -2
5   -5    4
6   -1   -1
7  NaN    0
8    9    0

...and assign the result back after.

More information can be found in this answer.

I'm learning pandas and I came across this problem.

[code]

df.replace('-', None)

[/code]

and this returns a pretty strange result

[code]

0
0   - // this isn't replaced
1   3
2   2
3   5
4   1
5  -5
6  -1
7  -1 // this is changed to `-1`...
8   9

[/code]

what exactly am I doing wrong?

Apr 3, 2018 in Python by
• 7,440 points • 83,682 views

4 answers to this question.

Actually in later versions of pandas this will give a TypeError:

df.replace('-', None)
TypeError: If "to_replace" and "value" are both None then regex must be a mapping

You can do it by passing either a list or a dictionary:

In [11]: df.replace('-', df.replace(['-'], [None]) # or .replace('-', {0: None})
Out[11]:
      0
0  None
1     3
2     2
3     5
4     1
5    -5
6    -1
7  None
8     9

But I recommend using NaNs rather than None:

In [12]: df.replace('-', np.nan)
Out[12]:
     0
0  NaN
1    3
2    2
3    5
4    1
5   -5
6   -1
7  NaN
8    9

I found the solution using replace with a dict the most simple and elegant solution:

df.replace({'-': None})

You can also have more replacements:

df.replace({'-': None, 'None': None})

And even for larger replacements, it is always obvious and clear what is replaced by what - which is way harder for long lists, in my opinion.

answered Oct 12, 2018 by findingbugs
• 4,780 points

Related Questions In Python

• All categories
• 
  Apache Kafka (84)
• 
  Apache Spark (596)
• 
  Azure (131)
• 
  Big Data Hadoop (1,907)
• 
  Blockchain (1,673)
• 
  C# (124)
• 
  C++ (268)
• 
  Career Counselling (1,060)
• 
  Cloud Computing (3,356)
• 
  Cyber Security & Ethical Hacking (145)
• 
  Data Analytics (1,266)
• 
  Database (853)
• 
  Data Science (75)
• 
  DevOps & Agile (3,500)
• 
  Digital Marketing (111)
• 
  Events & Trending Topics (28)
• 
  IoT (Internet of Things) (387)
• 
  Java (1,178)
• 
  Kotlin (3)
• 
  Linux Administration (384)
• 
  Machine Learning (337)
• 
  MicroStrategy (6)
• 
  PMP (423)
• 
  Power BI (516)
• 
  Python (3,154)
• 
  RPA (650)
• 
  SalesForce (92)
• 
  Selenium (1,569)
• 
  Software Testing (56)
• 
  Tableau (608)
• 
  Talend (73)
• 
  TypeSript (124)
• 
  Web Development (2,999)
• 
  Ask us Anything! (66)
• 
  Others (1,054)
• 
  Mobile Development (46)

Subscribe to our Newsletter, and get personalized recommendations.

Already have an account? Sign in.

How do I replace Na in Python?

How do you fill a NA value in Python?

How do I change NaN to NA in Python?

How do I fill a string with Na?