python - 数字存储为文本 ExcelWriter Python

问题描述

我有一个名为 combine 的数据框，需要将其插入到 Excel 中。

writer = pd.ExcelWriter('YTD.xlsx', engine ='xlsxwriter', options={'strings_to_numbers':True})
workbook = writer.book
combine.to_excel(writer, sheet_name='Sheet1', startrow=1 , startcol=0, header=True, index=False, encoding='utf8') 
worksheet1 = writer.sheets['Sheet1']
num_fmt = workbook.add_format({'num_format': '#,##0.00_ ;[Red]-#,##0.00 '})
worksheet1.set_column('B:AJ', 15, num_fmt)
writer.save()

结果很有趣：

当我使用 options={'strings_to_numbers':True} 时，有些已经转换为数字，但有些没有；我猜'，'可能是问题所在；所以我首先尝试摆脱'，'：

for col in combine.columns[1:]:                  # UPDATE ONLY NUMERIC COLS 
    combine[col].replace(',','')   
    combine[col] = combine[col].astype(float)         # CONVERT TO FLOAT

但失败了：

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-16-ce75c2cc211a> in <module>
      1 for col in combine.columns[1:]:                  # UPDATE ONLY NUMERIC COLS
      2     #combine[col].replace(',','')
----> 3     combine[col] = combine[col].astype(float)         # CONVERT TO FLOAT

~\Anaconda3\lib\site-packages\pandas\core\generic.py in astype(self, dtype, copy, errors, **kwargs)
   5880             # else, only a single dtype is given
   5881             new_data = self._data.astype(
-> 5882                 dtype=dtype, copy=copy, errors=errors, **kwargs
   5883             )
   5884             return self._constructor(new_data).__finalize__(self)

~\Anaconda3\lib\site-packages\pandas\core\internals\managers.py in astype(self, dtype, **kwargs)
    579 
    580     def astype(self, dtype, **kwargs):
--> 581         return self.apply("astype", dtype=dtype, **kwargs)
    582 
    583     def convert(self, **kwargs):

~\Anaconda3\lib\site-packages\pandas\core\internals\managers.py in apply(self, f, axes, filter, do_integrity_check, consolidate, **kwargs)
    436                     kwargs[k] = obj.reindex(b_items, axis=axis, copy=align_copy)
    437 
--> 438             applied = getattr(b, f)(**kwargs)
    439             result_blocks = _extend_blocks(applied, result_blocks)
    440 

~\Anaconda3\lib\site-packages\pandas\core\internals\blocks.py in astype(self, dtype, copy, errors, values, **kwargs)
    557 
    558     def astype(self, dtype, copy=False, errors="raise", values=None, **kwargs):
--> 559         return self._astype(dtype, copy=copy, errors=errors, values=values, **kwargs)
    560 
    561     def _astype(self, dtype, copy=False, errors="raise", values=None, **kwargs):

~\Anaconda3\lib\site-packages\pandas\core\internals\blocks.py in _astype(self, dtype, copy, errors, values, **kwargs)
    641                     # _astype_nansafe works fine with 1-d only
    642                     vals1d = values.ravel()
--> 643                     values = astype_nansafe(vals1d, dtype, copy=True, **kwargs)
    644 
    645                 # TODO(extension)

~\Anaconda3\lib\site-packages\pandas\core\dtypes\cast.py in astype_nansafe(arr, dtype, copy, skipna)
    727     if copy or is_object_dtype(arr) or is_object_dtype(dtype):
    728         # Explicit copy, or required since NumPy can't view from / to object.
--> 729         return arr.astype(dtype, copy=True)
    730 
    731     return arr.view(dtype)

ValueError: could not convert string to float: '-181,849.78'

现在真的不知道，其他人可能有同样的问题，所以我在这里发布问题

标签： pythonexcelpandas

python - 数字存储为文本 ExcelWriter Python

问题描述

解决方案

推荐阅读