0

I have a dataframe and I need to format one column to $xxx.xx. When I export to excel I need the numbers in float format but they are strings. How to export them as float?

df = pd.DataFrame(index=['index1', 'index2', 'index3'], 
          columns=["A", "B", "C"], 
          data=[[5,34, "ok"], [7,8,"fine"], ["3rd",100,np.nan]])
df['B'] = (df['B']).apply(lambda x: '${:,.2f}'.format(x))
df

enter image description here

The exported excel has B column in string format:

enter image description here

What I need:

enter image description here

DanZimmerman
  • 1,626
  • 6
  • 23
  • 45
  • Haha Psidom you got me again. I changed my name from AlexMeow to this. In excel you can easily select the numbers and go to the toolbar then select the "$". But they stay as number. – DanZimmerman Apr 05 '17 at 19:54
  • 1
    Is [this](http://stackoverflow.com/questions/41567847/how-to-format-specific-cells-in-excel-using-xlsx-package-in-python) what you want? – MaxU - stand with Ukraine Apr 05 '17 at 19:57
  • I do not want to do any manual work during the process so exporting to excel then add $ is not in consideration for now, unless there's no better solution. – DanZimmerman Apr 05 '17 at 20:00
  • Note: I am not a dataframe user. However, If I need more advanced Excel outputs, I use `xlsxwriter`. – RobertB Apr 05 '17 at 20:14

0 Answers0