Change NaN to None in Pandas dataframe

Question

I try to replace Nan to None in pandas dataframe. It was working to use df.where(df.notnull(),None). Here is the thread for this method. Use None instead of np.nan for null values in pandas DataFrame

When I try to use the same method on another dataframe, it failed. The new dataframe is like below A NaN B C D E, the print out of the dataframe is like this:

     Unnamed: 1  Unnamed: 2 Unnamed: 3 Unnamed: 4 Unnamed: 5 Unnamed: 6  
0         A         NaN          B          C          D          E 
 

even when I use the working code run against the new dataframe, it failed. I just wondering is it is because in the excel, the cell format has to be certain type. Any suggestion on this?

Answer 1

This always works for me

df = df.replace({np.nan:None})

You can check this related question, Credit from here

Answer 2

The problem is that I did not follow the format. The format I used that cause the problem was

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

If I wrote the code like this, there is no problem

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

Answer 3

This is not as easy as it looks.

1.NaN is the value set for any cell that is empty when we are reading file using pandas.read_csv()

2.None is the value set for any cell that is NULL when we are reading file using pandas.read_sql() or readin from a database

import pandas as pd
import numpy as np

x=pd.DataFrame()
df=pd.read_csv('file.csv')
df=df.replace({np.NaN:None})
df['prog']=df['prog'].astype(str)
print(df)

if there is compatibility issue of datatype , which will be because on replacing np.NaN will make the column of dataframe as object type. so in this case first replace np.NaN with None and then choose the required datatype for the column

file.csv

column names : batch,prog,name

'prog' column is empty