Title: Float to Currency retain format to add
Tags: python;excel;pandas
Question: I have a pandas dataframe with a lot of % and currency columns coming in from a sql table as float datatypes.
I'm doing the below to convert it to currency format.
I am appending this data to an excel spreadsheet with openpyxl.
while the output is showing currently with cost as $123, $100 etc, it possibly converts to string thus losing the ability to sum the values after generating the data.
How can I make the data display the $ before cost yet retain its ability to sum the values?
(df['Cost']).apply(lambda x: '${:,.2f}'.format(x))
The dataframe I am attempting to append to excel is very large and wide. About 480 columns and 30K rows - data is mixed. Strings, Currency, % and plain old float. Not sure if I can specify it by cell. So was hoping to handle it in the dataframe and output it. Also read that number formats are strings in openpyxl so that would not help in this case
Any other ideas/options for me?
Thanks in advance.
Comment: It sounds like you're completely misunderstanding "number formats" in Excel. Each cell has a value and a numeric format. These two attributes are pretty much independent. The value is what you use in calculations. In the case of numbers and dates, this is always a float. The numeric format controls how the cell looks, such as whether to use thousands separators or a currency symbol. The thing that specifies the numeric format is always a string.

Comment: *Where* do you need to retain the ability to perform arithmetic?  You've cited only Python and PANDAS in your tags, but your text suggests that you need that capability in Excel.

Comment: Very simply, if you need the capability in Excel, you need to manipulate the Excel format.  If you need the capability in Python/PANDAS, you have to maintain `float` type in the variables; *display* is just that: add it when you format the output, not while you're doing arithmetic.

Comment: What have you found in researching Excel cell formats, manipulated from Python?

Comment: Good response -- now edit that into your original posting.

Comment: Yes, in the excel output. I am writing the data frame to Excel using openpyxl with the converted values. Adding the Excel tag as well. Thanks!

Comment: i'm writing a very wide and large dataset to excel. About 480 columns and 30K rows - data is mixed. Strings, Currency, % and plain old float. Not sure if I can specify it by cell. So was hoping to handle it in the dataframe and output it. Also read that number formats are strings in openpyxl so that would not help in this case.



{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 0, 'CommentCount': 7, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2018:00:03/12/18', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<python><excel><pandas>', 'Title': 'Float to Currency retain format to add', 'ViewCount': 289, 'id': 49240707, 'user_AboutMe': None, 'user_AccountId': 12035199.0, 'user_DisplayName': 'Lucky', 'user_Reputation': 303.0, 'user_Views': 39.0, 'user_id': 8803985.0}}