Pandas Number Formatting

In many cases we need to apply different number formatting to different columns. One column might need to use currency format, while the other need to use percent format.

In this case, Pandas has Styler.format feature to do this (ref). We just need to define the formatting function for columns we want to apply on using dictionary where each key represents column name and the value represents formatting function.

Here is the example.

Let’s use this sample data.

Code

df = pd.read_csv("https://gist.githubusercontent.com/arifqodari/08692800e902401860c7ab4f94c2390c/raw/8e6e4e5c85ab091d6ac21801c2fe02fa6dd1fc77/sales_dummy.csv")
df

	date	total_sales	revenue	rate
0	2023-01-01	923	10400500	0.046723
1	2023-01-02	1104	11299050	0.050123
2	2023-01-03	987	10904500	0.040111

Next, we can define and apply the formatting function.

format_mapping = {
    "revenue": "{:,} IDR",
    "rate": "{:,.2%}"
}

df.style.format(format_mapping)

	date	total_sales	revenue	rate
0	2023-01-01	923	10,400,500 IDR	4.67%
1	2023-01-02	1104	11,299,050 IDR	5.01%
2	2023-01-03	987	10,904,500 IDR	4.01%

Neat!

The script above only apply formatting to certain columns defined in the format_mapping dictionary. Here is the explanation:

• revenue column needs thousand separator using comma (,)

• rate column need to be presented as percentage.

  Note that the original value is not in the percentage yet.