Python - pandas export to csv or dat file removing None or numpy.nan

user_7292363 0
Viewed 7813

When exporting a dataframe df to a dat file, how to remove the None or numpy.nan in the file? I just need an empty value.

df.to_csv('test.dat')

I have tried:

df = df.fillna('')

or

df = df.replace(numpy.nan, '') and df = df.replace(None, '')

But I still see 'None' or 'nan' in the csv or dat file.

pandas null python
3 Answers

Answering here to comment on what the OP mentioned in his accepted answer in case others stumble upon this.

"It is clear that somehow pandas treat None and numpy.nan as string value here. Not sure why but this solution works."
This is not true, and what happened was that either the columns of the original df were set strings, or only the "None" and "Nan" values were set as strings. Otherwise, the first_answer would work with .replace(np.nan,''). 

df=pd.DataFrame({
    'na_string':[1,2,'nan'],
    'na':[1,2,np.nan],
    'none_string':[1,2,'None'],
    'none':[1,2,None],
    'na_float':[1.1,1.2,np.nan],
    'mixed':[1.1,1.2,'nan']})
df

enter image description here

df.dtypes
#na_string       object
#na             float64
#none_string     object
#none           float64
#na_float       float64
#mixed           object
#dtype: object

Now note that when the column is classified as object, all na and None are not identified as np.nan since they are strings.

enter image description here

And when you export them with df.to_csv(path+'out.csv', na_rep='', index=False), even with the na_rep, all strings are kept, but not the actual np.nan or other null values.

enter image description here

If you had replaced your strings by np.nan you could've gotten there too:

df.replace('None',np.nan, inplace=True)
df.replace('nan',np.nan, inplace=True)
df.to_csv(path+'out2.csv', na_rep='', index=False)

enter image description here

user_13798260
Related