Use None instead of np.nan for null values in pandas DataFrame

Question

I have a pandas DataFrame with mixed data types. I would like to replace all null values with None (instead of default np.nan). For some reason, this appears to be nearly impossible.

In reality my DataFrame is read in from a csv, but here is a simple DataFrame with mixed data types to illustrate my problem.

df = pd.DataFrame(index=[0], columns=range(5))
df.iloc[0] = [1, 'two', np.nan, 3, 4]

I can't do:

>>> df.fillna(None)
ValueError: must specify a fill method or value

nor:

>>> df[df.isnull()] = None
TypeError: Cannot do inplace boolean setting on mixed-types with a non np.nan value

nor:

>>> df.replace(np.nan, None)
TypeError: cannot replace [nan] with method pad on a DataFrame

I used to have a DataFrame with only string values, so I could do:

>>> df[df == ""] = None

which worked. But now that I have mixed datatypes, it's a no go.

For various reasons about my code, it would be helpful to be able to use None as my null value. Is there a way I can set the null values to None? Or do I just have to go back through my other code and make sure I'm using np.isnan or pd.isnull everywhere?

`df[df.isnull()] = None` - works fine for me (pandas 0.18.1) — MaxU - stand with Ukraine, Sep 01 '16 at 20:05

score 97 · Accepted Answer · answered Sep 01 '16 at 19:55

97

Use pd.DataFrame.where
Uses df value when condition is met, otherwise uses None

df.where(df.notnull(), None)

answered Sep 01 '16 at 19:55

piRSquared

285,575
57
475
624

8

Thanks! I tried so many different variations before seeing this. So far it's worked perfectly (I can't have numpy.nan's in my data because I'm copying some of it into SQL Alchemy objects and SQL Alchemy can't handle nan when writing to DB) – happyskeptic Oct 09 '17 at 08:47
7

lifesaver. This is perfect especially when trying to insert NaN values to SQL!!!! – addicted Jun 14 '19 at 13:52
1

What is the solution for a single column? – Martin Thøgersen Apr 27 '20 at 10:37
3

Single column: `df['A'].where(df['A].notnull(), None)` – Jorick Spitzen Jun 10 '21 at 14:23
8

Caution that this method sets the `dtype` for all columns to `object` – Jason Strimpel Aug 06 '21 at 06:10
1

Note you may need to manually cast to object (`astype(object)`). See [this answer](https://stackoverflow.com/a/70803926/4653485). – Jérôme Jul 01 '22 at 08:02
This is also very useful for writing from a dataframe to xlsxwriter (which requires Null, not NaN)! – Nick Jan 11 '23 at 15:49

score 6 · Answer 2 · answered Jan 21 '22 at 15:58

6

Expanding on the accpeted answer.. When you also need to catch NaN values within numeric dtype columns, you may need to change dtype to object first:

df.astype(object).where(df.notna(), None)

as per original reply by @BENNY

answered Jan 21 '22 at 15:58

KingOtto

840
5
18

Is it possible to only change the dtypes of columns originally with NaNs while maintaining the rest as before? – Vinicius Silva Jul 08 '22 at 22:54
Sure. you can change the dtype of only a single column: `df['foo'].astype('float')` – KingOtto Jul 12 '22 at 16:18

score 3 · Answer 3 · answered Oct 13 '22 at 16:08

3

Found this helpful for replacing NaN values with None: df.replace({pd.np.nan: None})

answered Oct 13 '22 at 16:08

apinanyogaratnam

628
7
14

1

Note that `pd.np.nan` is deprecated, but you can also just use `float("nan")` instead of importing numpy. – alexia Oct 14 '22 at 12:49

Use None instead of np.nan for null values in pandas DataFrame

3 Answers3

Linked