10

How would I change the following code to handle NaN values as just empty cells within my dataframe as I iterate through it line by line outputting values in column A?

excel = pd.ExcelWriter(f_name,engine='xlsxwriter')
wb = excel.book
ws = wb.add_worksheet('PnL')

for i in len(df):
  ws.write(0,i,df.iloc[i]['A'])
Mike
  • 197
  • 1
  • 2
  • 10

2 Answers2

9

I think you can use fillna:

df = df.fillna(0)

or:

df['A'] = df['A'].fillna(0)

But better is use to_excel:

import pandas as pd
import numpy as np

# Create a Pandas dataframe from the data.
df = pd.DataFrame({'A': [10, 20, 30, 20, 15, 30, 45, np.nan], 
                   'B': [10, 20, 30, 20, 15, 30, 45, np.nan]})
print df
    A   B
0  10  10
1  20  20
2  30  30
3  20  20
4  15  15
5  30  30
6  45  45
7 NaN NaN

#create subset, because cannot write Series to excel
df1 = df[['A']]

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('f_name.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object, instead NaN give 0
df1.to_excel(writer, sheet_name='PnL', na_rep=0)

# Save content
writer.save()

If you want omit index and header, add parameters index=False and header=False:

df1.to_excel(writer, sheet_name='PnL', na_rep=0, index=False, header=False)

excel

jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Could it be that `writer.save()` is missing? – ikku100 Mar 03 '23 at 08:15
  • @ikku100 - I think if use `df.to_excel` not necessary like here. – jezrael Mar 03 '23 at 08:17
  • It didn't work for me until I did. But perhaps when the writer is destroyed it saves it, which happens when you run a .py from start to finish and the kernel ends. When running in jupyter notebooks, not so much. – ikku100 Apr 25 '23 at 07:27
  • 1
    @ikku100 - You are right, missing `writer.save()`, added to answer. – jezrael Apr 25 '23 at 07:46
7

I would recommend copying the dataframe, changing the NANs to spaces and then writing the data. Your original dataframe will still have the NANs.

Also, as a workaround Xlsxwriter supports writing writing NANs mapped to formulas that yield an error code. However, this is a constructor option and isn't on by default. See the docs.

If you have a version of Pandas >= 0.16 you should be able to turn on the option as follows:

excel = pd.ExcelWriter(f_name,
                        engine='xlsxwriter',
                        options={'nan_inf_to_errors': True})
jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • If there is some nan value in the data you got this error: TypeError: NAN/INF not supported in write_number() without 'nan_inf_to_errors' Workbook() option. I use options={'nan_inf_to_errors': True}, and works pretty fine. – Beatriz Fonseca Sep 22 '16 at 16:07
  • Hi, how do I make it map nans to a #N/A value? – user147529 Sep 01 '20 at 14:27