title: Number stored as text ExcelWriter Python
body: <p>I have a datafram called combine which needs to be inserted into the excel. </p>
  
  <pre><code>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()
  </code></pre>
  
  <p>the result is interesting:
  <a href="https://i.stack.imgur.com/t3NeS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/t3NeS.png" alt="enter image description here"></a></p>
  
  <p>as I used options={'strings_to_numbers':True}, some are already transfered into numbers but some are not; I guess ',' may be the problem; so I tried to get rid of ',' first:</p>
  
  <pre><code>for col in combine.columns[1:]:                  # UPDATE ONLY NUMERIC COLS 
      combine[col].replace(',','')   
      combine[col] = combine[col].astype(float)         # CONVERT TO FLOAT  
  </code></pre>
  
  <p>but failed: </p>
  
  <pre><code>---------------------------------------------------------------------------
  ValueError                                Traceback (most recent call last)
  &lt;ipython-input-16-ce75c2cc211a&gt; in &lt;module&gt;
        1 for col in combine.columns[1:]:                  # UPDATE ONLY NUMERIC COLS
        2     #combine[col].replace(',','')
  ----&gt; 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(
  -&gt; 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):
  --&gt; 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 
  --&gt; 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):
  --&gt; 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()
  --&gt; 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.
  --&gt; 729         return arr.astype(dtype, copy=True)
      730 
      731     return arr.view(dtype)
  
  ValueError: could not convert string to float: '-181,849.78'
  </code></pre>
  
  <p>Really dont know now, others may have the same problem, so I post the problem here</p>