Menu
I have a pandas.DataFrame which contains numpy.nan floats. When using the Excel writer however, the fields where there should be numpy.nan floats are blank. I would expected at least a string representation instead of nothing.
Any thoughts on why this might be?
Writer code is as follows:
writer=pandas.ExcelWriter('output.xls')
frame.to_excel(writer,'tab name')
writer.save()
Where frame looks something like this (note the NaN on 2013-01-1):
Series ID Risk Bucket Contract PX Last Contract Value (Local) Currency X Contract Value (USD) Currency
2013-01-01 Future_ES EQ ES1 Index NaN NaN 1 NaN USD Curncy
2013-01-02 Future_ES EQ ES1 Index 1447.16 72362.5 1 72362.5 USD Curncy
2013-01-03 Future_ES EQ ES1 Index 1443.68 72187.5 1 72187.5 USD Curncy
2013-01-04 Future_ES EQ ES1 Index 1447.90 72400.0 1 72400.0 USD Curncy
But the Excel file has blanks (see attached image).
718
Export a Pandas DataFrame Into Excel File by Using the to_excel() Function. When we export a pandas DataFrame to an excel sheet using the dataframe. to_excel() function, it writes an object into the excel sheet directly. To implement this method, create a DataFrame and then specify the name of the excel file.
We can replace NaN values with 0 to get rid of NaN values. This is done by using fillna() function. This function will check the NaN values in the dataframe columns and fill the given value.
In order to check missing values in Pandas DataFrame, we use a function isnull() and notnull(). Both function help in checking whether a value is NaN or not. These function can also be used in Pandas Series in order to find null values in a series.
By using dropna() method you can drop rows with NaN (Not a Number) and None values from pandas DataFrame. Note that by default it returns the copy of the DataFrame after removing rows. If you wanted to remove from the existing DataFrame, you should use inplace=True .
From the documentation, you should set the option na_rep in to_excel with a string of your liking. E.g.:
frame.to_excel(writer,'tab name', na_rep='NA')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
