pandas 在python中使用Pandas ExcelWriter时处理Nan
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35605042/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Handle Nan when using Pandas ExcelWriter in python
提问by Mike
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?
我将如何更改以下代码以将 NaN 值处理为我的数据框中的空单元格，因为我逐行遍历它并在 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'])
回答by jezrael
I think you can use fillna:
我认为你可以使用fillna：
df = df.fillna(0)
or:
或者：
df['A'] = df['A'].fillna(0)
But better is use to_excel:
但更好的是使用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)
If you want omit index and header, add parameters index=Falseand header=False:
如果要省略索引和标题，请添加参数index=False和header=False：
df1.to_excel(writer, sheet_name='PnL', na_rep=0, index=False, header=False)
回答by jmcnamara
I would recommend copying the dataframe, changing the NANs to spaces and then writing the data. Your original dataframe will still have the NANs.
我建议复制数据帧，将 NAN 更改为空格，然后写入数据。您的原始数据帧仍将具有 NAN。
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.
此外，作为一种解决方法，Xlsxwriter 支持编写映射到会产生错误代码的公式的 NAN。但是，这是一个构造函数选项，默认情况下不启用。请参阅文档。
If you have a version of Pandas >= 0.16 you should be able to turn on the option as follows:
如果您的 Pandas 版本 >= 0.16，您应该能够打开该选项，如下所示：
excel = pd.ExcelWriter(f_name,
engine='xlsxwriter',
options={'nan_inf_to_errors': True})
