Pandas table beauty skills

Keywords: Python Data Mining pandas

Official account: Special House
Author: Peter
Editor: Peter

Hello, I'm Peter~

This article mainly introduces how to beautify the data of Pandas DataFrame. It is mainly realized through two methods in Pandas:

  1. Styler.applymap: returns a single string with CSS attribute value pairs element by element
  2. Styler.apply: returns Series or DataFrame with the same shape in the form of columns, tables and rows, where each value is a string with CSS attribute value pairs. When the method works, it is passed through the parameter axis. axis=0 means acting by column and axis=1 means acting by row.

Official website learning address:


Pandas serial

Pandas's articles have been serializing:

Analog data

import pandas as pd
import numpy as np

# Set random seeds to ensure the same results every time

# Analog data
df = pd.DataFrame({'A': np.linspace(1, 10, 10)})
# Merge two dataframes
df1 = pd.concat([df, pd.DataFrame(np.random.randn(10,4), columns=list("BCDE"))],axis=1)

Linspace (start, stop, set) in numpy: indicates that the data is retrieved according to the step size, including the head and tail data:

In order to see the effect of null values, three null values nan are specially set:

View and Hide Styles

First, view the style settings of the DataFrame:

We can use the render method to view the specific values of the data frame style: we find that the code is the default CSS style

Hide index

Code to hide index:

Hides the specified column

We found that the AC two columns are hidden, using the parameter subset parameter:

The following are examples of various display styles:

Case 1: positive and negative colors

Less than 0 shows red, greater than 0 shows blue

def color_change(val):
    color = 'red' if val < 0 else 'blue'
    return 'color: %s' % color  
# Use applymap and call the written function

The applymap method is for the entire DataFrame

Case 2: highlight data

You can highlight the maximum, minimum, and missing values. Write a simple highlight function:

def highlight_min(x):
    is_min = x == x.min()
    return ["background-color: yellow" if v else '' for v in is_min]

Example 3: use the default highlight function

Three built-in highlighting functions have been written in Pandas by default:

  • highlight_max(): Max
  • highlight_min(): Min
  • highlight_null(): null value

At the same time, we can also use the parameter axis to display rows or columns

1. Highlight Max

2. Highlight the minimum value for each column

3. Highlight null: the default is red

Of course, we can change the color by using the parameter null_color:

The parameter axis=1 indicates that the operation is performed in the direction of the row:

Case 4: chain call

Chained call refers to the simultaneous use of multiple functions (Methods) in the same style operation:

# color_change + highlight_max

If there are many methods, you can write them separately:

# color_change + highlight_max\

# Or: add a layer of parentheses outside to indicate that the whole code is a whole

Null value highlighting and changing color parameters are used at the same time to realize chain call:

Hide column properties and custom parameters at the same time:

Example 5: partial data beautification

Use the parameter subset to control the rows and columns. The passed in parameters can be:

  • Column label
  • List (numpy array)
  • Tuple (row_indexer, column_indexer)

Partial column properties

Specify the column properties we want to beautify through the parameter subset:

pd.IndexSlice construct list

Constructed by pd.IndexSlice

Example 6: formatted display of values (Styler.format)

Styler.format to control the formatted output of values, similar to string formatting in Python

Overall display

For example, we want all data to display only two decimal places:

Use a dictionary to format specific columns

In the form of a dictionary, the column attribute name is used as a key and the format description is used as a value for formatting:

Using lambda anonymous functions

Null value display

By parameter na_rep, replace the null value:

Replace with "null value":

Chained calls also use "-" instead:


Use emoji Emoji emoticons:

Example 7: built in styles

Built in function

The built-in null highlighting function is used here:

Thermal map making

We use seaborn library to realize:

import seaborn as sns

cm = sns.light_palette("blue", as_cmap=True)
s =

Continuous chromatography

Specify the chromatographic range through the parameters low and high:

set_properties usage

### set_properties usage**{"background":"yellow",  # Background color
                           "color":"red",  # typeface
                           "border-color":"black"})  # boundary

Example 8: personalized bar chart

Default mode

Operate by bar method:

Parameter align

There are 3 alignments:

  • Left: the minimum value starts from the left side of the cell
  • Zero: the zero value is in the center of the cell
  • mid: the center in the cell is at (max min) / 2

Example 9: style sharing

Suppose we create a style in a DataFrame and want to use it directly in another DataFrame, what should we do?

Let's create a style style1 first:

Apply style1 to style2:

Example 10: set_precision

Usually, the precision (decimal places) is specified for the data in the data frame:

Example 11: set title

Using the set_caption method

Missing value setting

The set_na_rep function is used:

    .format(None, na_rep="PASS", subset=["D"])

Example 13: comprehensive case

  .set_na_rep('-')  # Set null value
  .format({'C':'{:.4f}',  # Precision setting
           'E':'{:.2%}'},na_rep="-")  # Precision and null values are set at the same time
  .highlight_null("green")  # Null highlight
  .applymap(color_change,subset=['D'])  # Column D uses the color_change function
  .apply(highlight_min,subset=['C'])  # Column C uses highlight_min
  .background_gradient(cmap='cubehelix',subset=['B','D'])  # Background color settings
  .bar(subset=['E'], align='mid', color=['#60BCD4','#6A1B9A'])  # Histogram setting
  .set_caption("Title of Pandas Style")  # Title Setting
  .hide_index()  # Hide index
  .hide_columns(subset=['A']))  # Hide column A

Example 14: Ultimate Weapon

# Version requires at least pandas 1.2.0
style1 = [
    dict(selector="th", props=[("font-size", "125%"), 
                               ("text-align", "center"),
                               ("background-color", "#F0F3CF"),
    dict(selector="td", props=[("font-size", "105%"), 
                               ("text-align", "right"),
    dict(selector="caption", props=[("caption-side", "top"),
                                    ("text-align", "left"),

style2 = {
    'A': [dict(selector='td', props=[('text-align','center'),
    'B': [dict(selector='td', props=[('text-align','left'),
    'C': [dict(selector='td', props=[('text-decoration','underline'),
# Chain call various setting methods
  .set_na_rep('-')  # Global null value
  .format({'C':'{:.4f}','D':'{:.4f}','E':'{:.2%}'},na_rep="-")  # accuracy
  .highlight_null("gray")  # Highlight null
  .applymap(color_change,subset=['B']).highlight_max(subset=['A'])  # color_change function and maximum highlight
  .background_gradient(cmap='Pastel1',subset=['C','D'])  #  background
  .bar(subset=['E'], align='mid', color=['#90BCD4','#6A1B9A'])  # Columnar
  .set_caption("Advanced use of Pandas Style")  # title 
 	.hide_index()  # Hide index
  .hide_columns(subset=['E'])  # Hide column E  
  .set_table_styles(style1).set_table_styles(style2,overwrite=False)  # Style transfer
  .set_properties(**{'font-family': 'Microsoft Yahei','border-collapse': 'collapse',  # Property settings
                     'border-top': '1px solid black','border-bottom': '1px solid black'}))

Export to Excel

This is a function that is still in the development stage. It uses openyxl or xlswriter as the engine to export the DataFrame to Excel. The official website says:

Here is a simple case:

   .applymap(color_change)  # Positive and negative change color
   .apply(highlight_min)  # Highlight minimum
   .to_excel('styled.xlsx', engine='openpyxl'))

If we don't want index numbers, add index=False:

   .applymap(color_change)  # Positive and negative change color
   .apply(highlight_min)  # Highlight minimum
   .to_excel('styled.xlsx', engine='openpyxl', index=False))

Posted by john0117 on Tue, 30 Nov 2021 07:01:12 -0800