pandas dataframe style numbers to currency

user_11542229 0
Viewed 290

I have a simple dataframe that contains Dates as columns and numbers as row values. I am attempting to add formatting to the numbers so that they display in currency format of $x.xx. I can't figure out what I am doing wrong as no error is produced and all the other formats I have are being applied except for the currency formatting. Below is a snippet of the code in question.

format_df = final_df.style.        apply(lambda row: ['background-color: yellow'
                         if row.name in team_totals_index else 'background-color:#FFCC99' for value in row], axis=1).\
        apply(lambda row: ['${:,.2f}' if row.name in team_totals_index else '' for value in row], axis=1).\
        apply(lambda row: ['font-weight: bold' if row.name in team_totals_index else '' for value in row], axis=1).\
        apply(lambda row: ['border-top-style:solid' if row.name in team_totals_index else '' for value in row], axis=1).\
        apply(lambda row: ['border-bottom-style:solid' if row.name in team_totals_index else '' for value in row], axis=1)

here's an image of the excel sheet it produces, the bold, border, and colors show but not the currency format

any insight on how I could fix this so that the currency format will reflect properly?

pandas dataframe formatting python
1 Answers

after looking through the official documentation I stumbled upon "number-format" which allowed me to add the currency format I was looking for.

here's an image from the docs showing what is acceptable when writing to excel

in case anyone is curious, I modified my code to the below:

    #style the dataframe totals in bold/border and highlight background colors
    format_df = final_df.style.        apply(lambda row: ['background-color: #FF9900'
                         if row.name in team_totals_index else 'background-color:#FFCC99' for value in row], axis=1).\
        apply(lambda row: ['number-format: $#,##0.00' if row.name in team_totals_index else 'number-format: $#,##0.00' for value in row], axis=1).\
        apply(lambda row: ['font-weight: bold' if row.name in team_totals_index else '' for value in row], axis=1).\
        apply(lambda row: ['border-top-style:solid' if row.name in team_totals_index else '' for value in row], axis=1).\
        apply(lambda row: ['border-bottom-style:solid' if row.name in team_totals_index else '' for value in row], axis=1)
    
    return format_df

This has yielded the results I was looking for.

excel sheet with the proper formatting

user_115422290
Related