Transform Excel Data into Stunning Pandas Reports with One Line of Code

Overview

Pandas is the most widely used tool for data processing among data scientists. Compared with Excel, Pandas can implement all Excel functions and is more convenient and concise.

Applying "conditional formatting" on a DataFrame helps highlight data and makes the presentation more attractive, which is covered for the first time here.

The left table shows a class's final exam scores. The goal is to transform it into the right‑hand styled table by completing the following tasks:

(1) Keep one decimal place for the "Mean" column values.

(2) Add a caption "Senior Year (5) Class Final Exam Scores".

(3) Hide the index column (the leftmost column).

(4) Hide specified columns that do not need to be displayed.

(5) Display a bar chart for the "Chinese" column based on its values.

(6) Highlight the maximum value in the "Mean" column.

(7) Apply a green colormap to the "Math" column values.

(8) Show all missing values in red.

One Line Code for All Operations

Similar to the "chaining rule" in Pyecharts, a single chained command can achieve all the above functionalities.

1. Read Data

import pandas as pd

df = pd.read_excel("特殊.xlsx")
df.index = list(range(df.shape[0]))
df

Result:

2. One Line Code

df.style.format('{:.1f}', subset='均值')\
    .set_caption('高三(5)班期末考试成绩')\
    .hide_index()\
    .hide_columns(['索引'])\
    .bar('语文', vmin=0)\
    .highlight_max('均值')\
    .background_gradient('Greens', subset='数学')\
    .highlight_null()

Result:

Usage Notes

This feature is part of pandas 0.17.1. The official documentation states that it is a new, actively developed feature that may undergo significant changes in future versions.

If you only need a specific functionality, you can call the corresponding Styler methods individually:

df.style.format()

df.style.set_caption()

df.style.hide_index()

df.style.hide_columns()

df.style.bar()

df.style.highlight_max()

df.style.background_gradient()

df.style.highlight_null()

Give it a try and see how quickly you can enhance your data presentations!