I have the reverse problem as described in Prevent pandas from interpreting 'NA' as NaN in a string.
I work with older English text data and want to write the word "nan" (i.e. Modern English 'non(e)') into an Excel file.
I want Excel to show this word as "nan" for a particular column. However, I don't want empty cells elsewhere in my dataframe be filled with any other Excel NaN-replacements.
Instead, what I get when I use df.to_excel() is an empty cell.
[Updated]
df["particular column"] = df["particular column"].fillna("nan")
df.toexcel("df.xlsx") # normal behavior already write as empty
[Old]
To Excel
df.toexcel("df.xlsx", na_rep="None") # or "nan"
From Excel
retain "None"
pd.read_excel("df.xlsx", na_filter=False)
or convert "None" values to NaN
pd.read_excel("df.xlsx"), na_values="None") # or "nan"
There is a fix to this, you can try and replace the nan with something the excel sheet would display as text instead.
df["word"] = df["word"].replace("nan", "nan_value")
df.to_excel("output.xlsx", index=False)
If you do that then excel will no longer think of it as an actual nan value but rather a string that it will display normally.
