Is there any method to replace values with None
in Pandas in Python?
You can use df.replace('pre', 'post')
and can replace a value with another, but this can't be done if you want to replace with None
value, which if you try, you get a strange result.
So here's an example:
df = DataFrame(['-',3,2,5,1,-5,-1,'-',9])
df.replace('-', 0)
which returns a successful result.
But,
df.replace('-', None)
which returns a following result:
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
Why does such a strange result be returned?
Since I want to pour this data frame into MySQL database, I can't put NaN
values into any element in my data frame and instead want to put None
. Surely, you can first change '-'
to NaN
and then convert NaN
to None
, but I want to know why the dataframe acts in such a terrible way.
Tested on pandas 0.12.0 dev on Python 2.7 and OS X 10.8. Python is a pre-installed version on OS X and I installed pandas by using SciPy Superpack script, for your information.
df.replace('-', np.nan).astype("object")
This will ensure that you can use isnull()
later on your dataframe
I prefer the solution using replace
with a dict
because of its simplicity and elegance:
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.
where
is probably what you're looking for. So
data=data.where(data=='-', None)
From the panda docs:
where
[returns] an object of same shape as self and whose corresponding entries are from self where cond is True and otherwise are from other).
Setting null values can be done with np.nan
:
import numpy as np
df.replace('-', np.nan)
Advantage is that df.last_valid_index()
recognizes these as invalid.
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...
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.
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.
df = pd.DataFrame(['-',3,2,5,1,-5,-1,'-',9])
df = df.where(df!='-', None)
With Pandas version =1.0.0, I would use DataFrame.replace
or Series.replace
:
df.replace(old_val, pd.NA, inplace=True)
This is better for two reasons:
pd.NA
instead of None
or np.nan
.Using replace and assigning a new df:
import pandas as pd
df = pd.DataFrame(['-',3,2,5,1,-5,-1,'-',9])
dfnew = df.replace('-', 0)
print(dfnew)
(venv) D:\assets>py teste2.py
0
0 0
1 3
2 2
3 5
4 1
5 -5
Source: Stackoverflow.com