Pandas ExcelWriter removing valid NaN values when they are needed in output spreadsheet

user_8808310 0
Viewed 399

I am using Pandas to load a json file and output it to Excel via the ExcelWriter. "NaN" is a valid value in the json and is getting stripped in the spreadsheet. How can I store the NaN value.

Here's the json input file (simple_json_test.json)

{"action_time":"2020-04-23T07:39:51.918Z","triggered_value":"NaN"}
{"action_time":"2020-04-23T07:39:51.918Z","triggered_value":"2"}
{"action_time":"2020-04-23T07:39:51.918Z","triggered_value":"1"}
{"action_time":"2020-04-23T07:39:51.918Z","triggered_value":"NaN"}

Here's the python code:

import pandas as pd
from datetime import datetime


with open('simple_json_test.json', 'r') as f:
    data = f.readlines()

data = map(lambda x: x.rstrip(), data)

data_json_str = "[" + ','.join(data) + "]"
df = pd.read_json(data_json_str)

# Write dataframe to excel
df['action_time'] = df['action_time'].dt.tz_localize(None)

# Write the dataframe to excel
writer = pd.ExcelWriter('jsonNaNExcelTest.xlsx', engine='xlsxwriter',datetime_format='yyy-mm-dd hh:mm:ss.000')
df.to_excel(writer, header=True, sheet_name='Pandas_Test',index=False)

# Widen the columns
worksheet = writer.sheets['Pandas_Test']
worksheet.set_column('A:B', 25)

writer.save()

Here's the output excel file:

enter image description here

Once that basic question is answer, i want to be able to specify which columns "NaN' is a valid value so save it to excel.

pandas.excelwriter pandas json
1 Answers

The default action for to_excel() is to convert NaN to the empty string ''. See the Pandas docs for to_excel() and the na_rep parameter.

You can specify an alternative like this:

df.to_excel(writer, header=True, sheet_name='Pandas_Test',
            index=False, na_rep='NaN')
user_102380
Related