Apply Formatting to Each Column in Dataframe Using a Dict Mapping

Question

Problem Setup

import pandas as pd
df = pd.DataFrame(data={'Currency': {0: 111.23, 1: 321.23},
                   'Int': {0: 23, 1: 3},
                   'Rate': {0: 0.03030, 1: 0.09840}}
            )

Produces the following DataFrame

   Currency  Int    Rate
0    111.23   23  0.0303
1    321.23    3  0.0984

I want to apply very specific formatting to each column in the dataframe using a dict like the following:

format_mapping={'Currency': '${:,.2f}', 'Int': '{:,.0f}', 'Rate': '{:.2f}%'}

I know I can use applymap for multiple columns or apply on a single column:

#All columns
df = df.applymap('{:.2f}%'.format)
#Specific columns
df['Rate'] = df['Rate'].apply('{:.2f}%'.format)

Question

How can I iterate through each column in a dataframe and apply formatting using a dictionary where the dict key is the column and the value is the string formatting?

End result would look like this (ignore the fact that percent wasn't multiplied by 100 for now)

  Currency Int   Rate
0  $111.23  23  0.03%
1  $321.23   3  0.10%

Solution 1

The easiest way would be to iterate through the format_mapping dictionary and then apply on the column (denoted by the key) the formatting denoted by the value. Example -

for key, value in format_mapping.items():
    df[key] = df[key].apply(value.format)

Demo -

In [62]: df = pd.DataFrame(data={'Currency': {0: 111.23, 1: 321.23},
   ....:                    'Int': {0: 23, 1: 3},
   ....:                    'Rate': {0: 0.03030, 1: 0.09840}}
   ....:             )

In [63]:

In [63]: format_mapping={'Currency': '${:,.2f}', 'Int': '{:,.0f}', 'Rate': '{:.2f}%'}

In [64]: for key, value in format_mapping.items():
   ....:     df[key] = df[key].apply(value.format)
   ....:

In [65]: df
Out[65]:
  Currency Int   Rate
0  $111.23  23  0.03%
1  $321.23   3  0.10%

Solution 2

In 2021 (Pandas 1.2.3) you can use df.style.format():

import pandas as pd

df = pd.DataFrame(
    data={
        "Currency": {0: 111.23, 1: 321.23},
        "Int": {0: 23, 1: 3},
        "Rate": {0: 0.03030, 1: 0.09840},
    }
)
format_mapping = {"Currency": "${:,.2f}", "Int": "{:,.0f}", "Rate": "{:.2f}%"}

df.style.format(format_mapping)

More information: https://pandas.pydata.org/pandas-docs/stable/user_guide/style.html#Finer-Control:-Display-Values