python - pandas to csv - preserving "$" as formatting

Question

I have a pandas data frame containing amounts with dollar signs. When I send this to a csv and open it in Excel I can see the amounts with the $'s, but the $'s are clearly not Excel formatting and when I actually click on a cell all I see is the number and no $. The $ signs are then completely lost when I import this file into something like Tableau.

Is there any way to address this easily in Python? (Note that only certain columns contain amounts with $ signs)

Any help is appreciate

question from:https://stackoverflow.com/questions/65600861/pandas-to-csv-preserving-as-formatting

Answer 1

Convert the type of the column to string.

df['columnname'].astype(str)

To demonstrate this, I make a dataframe where the money column is already strings and then export to csv. I use this code:

import pandas as pd
df = pd.DataFrame([[1,'$1.30'],[2,'$1.50'],[3,'$1.70']],columns=['id','money'])
df.to_csv('temp.csv')

The csv file looks like this in excel:

      id       money
0      1       $1.30 
1      2       $1.50 
2      3       $1.70 

The csv file looks like this in notepad:

,id,money
0,1,$1.30 
1,2,$1.50 
2,3,$1.70 

However, in excel, when I click on the cell that reads $1.30, in the wide box at the top of the screen it reads 1.3. This is excel doing its thing, but don't be fooled, the dollar sign is in the file and it's not just excel formatting. If you load it in Tableau and the $ disappears that is because tableau removed the dollar sign and you have to work on settings in tableau; changing your python code or the csv file wont fix this.

I then loaded this in tableau and it did remove the dollar signs.

Simply right click the axis and choose format. Then click numbers and click currency (standard)

Then hooray!