Tables

Tables#

Introduction#

The humble table is an underloved and underappreciated element of communicating analysis. While it may not be as visually engaging as a vivid graph (and is far less good for a general audience), it has the advantage of being able to convey exact numerical information. It’s also an essential part of some analysis: for example, when writing economics papers, there is usually a “table 1” that contains descriptive statistics. (For more on best practice for tables, check out the advice provided by the UK government’s Analysis Function.)

Alternatives#

Unfortunately, Python is a bit weaker on tables than it ought to be though the situation has improved markedly with the release of the gt (great tables) package by Posit, who also made an R version of the same package. The Python version is in its early days but you can already make really good HTML tables: check here for the package website and they’ll surely add other output formats before long. If you want ease and flexibility, and you don’t mind that the end product is a HTML table (for instance, you’re just working in Jupyter notebooks or you’re creating a dashboard or website), gt is well worth a try. We won’t cover it here.

Another option, if you’re happy with image output formats, is matplotlib, the infinitely flexible image package. However, your tables have to be exported as image files rather than as machine-readable text-based files. If you want a graphic table that is more striking, matplotib is brilliant. I wouldn’t say it’s simple though, as it relies on content featured in Powerful Data Visualisation with Matplotlib and Common Plots, plus more besides. So, rather than attempt to cover it here, instead we’re just going to direct you to this excellent blog post on ‘the grammar of tables’ by Karina Bartolomé that walks through creating a table with matplotlib.

The rest of this chapter#

We’re going to spend the rest of the chapter looking at a tool than can export to a wider set of formats. Yes, this means we’re back to the Swiss Army Knife of data analysis, pandas. While pandas isn’t perfect for crafting tables, using it for them means you can benefit from its incredible number of output formats, some of which are machine readable.

pandas for tables#

Imports and setup#

As ever, we’ll start by importing some key packages and initialising any settings:

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

# Set seed for random numbers
seed_for_prng = 78557
prng = np.random.default_rng(
    seed_for_prng
)  # prng=probabilistic random number generator

We’ll use the penguins dataset to demonstrate the use of pandas in creating tables. These data come with the seaborn package, which you’ll need:

import seaborn as sns

pen = sns.load_dataset("penguins")
pen.head()

	species	island	bill_length_mm	bill_depth_mm	flipper_length_mm	body_mass_g	sex
0	Adelie	Torgersen	39.1	18.7	181.0	3750.0	Male
1	Adelie	Torgersen	39.5	17.4	186.0	3800.0	Female
2	Adelie	Torgersen	40.3	18.0	195.0	3250.0	Female
3	Adelie	Torgersen	NaN	NaN	NaN	NaN	NaN
4	Adelie	Torgersen	36.7	19.3	193.0	3450.0	Female

Preparing and creating your table in pandas#

There are a few operations that you’ll want to do again, and again, and again to create tables. A cross-tab is one such operation! A cross-tab is just a count of the number of elements split by two groupings. Rather than just display this using the pd.crosstab() function, we can add totals or percentages using the margins= and normalize= commands.

In the below, we’ll use margins and normalisation so that each row sums to 1, and the last row shows the probability mass over islands.

pd.crosstab(pen["species"], pen["island"], margins=True, normalize="index")

island	Biscoe	Dream	Torgersen
species
Adelie	0.289474	0.368421	0.342105
Chinstrap	0.000000	1.000000	0.000000
Gentoo	1.000000	0.000000	0.000000
All	0.488372	0.360465	0.151163

The neat thing about the cross-tabs that come out is that they are themselves pandas dataframes.

Of course, the usual pandas functions can be used to create any table you need:

pen_summary = (
    pen
    .groupby(["species", "island"])
    .agg(
        median_bill=("bill_length_mm", "median"),
        mean_bill=("bill_length_mm", "mean"),
        std_flipper=("flipper_length_mm", "std"),
    )
)
pen_summary

		median_bill	mean_bill	std_flipper
species	island
Adelie	Biscoe	38.70	38.975000	6.729247
	Dream	38.55	38.501786	6.585083
	Torgersen	38.90	38.950980	6.232238
Chinstrap	Dream	49.55	48.833824	7.131894
Gentoo	Biscoe	47.30	47.504878	6.484976

For reasons that will become apparent later, we’ll replace one of these values with a missing value (pd.NA).

pen_summary.iloc[2, 1] = pd.NA

The table we just saw, pen_summary, is not what you’d call publication quality. The numbers have lots of superfluous digits. The names are useful for when you’re doing analysis, but might not be so obvious to someone coming to this table for the first time. So let’s see what we can do to clean it up a bit.

First, those numbers. We can apply number rounding quickly using .round().

pen_summary.round(2)

		median_bill	mean_bill	std_flipper
species	island
Adelie	Biscoe	38.70	38.98	6.73
	Dream	38.55	38.50	6.59
	Torgersen	38.90	NaN	6.23
Chinstrap	Dream	49.55	48.83	7.13
Gentoo	Biscoe	47.30	47.50	6.48

This returns another dataframe. To change the names of the columns, you can just use one of the standard approaches:

pen_sum_clean = (
    pen_summary
    .rename(columns={"median_bill": "Median bill length (mm)", "mean_bill": "Mean bill length (mm)", "std_flipper": "Std. deviation of flipper length"})
)
pen_sum_clean

		Median bill length (mm)	Mean bill length (mm)	Std. deviation of flipper length
species	island
Adelie	Biscoe	38.70	38.975000	6.729247
	Dream	38.55	38.501786	6.585083
	Torgersen	38.90	NaN	6.232238
Chinstrap	Dream	49.55	48.833824	7.131894
Gentoo	Biscoe	47.30	47.504878	6.484976

One tip is to always have a dictionary up your sleeve that maps between the short names that are convenient for coding, and the longer names that you need to make outputs clear. Then, just before you do any exporting, you can always map the short names into the long names.

Styling a pandas dataframe#

As well as making direct modifications to a dataframe, you can apply styles. These are much more versatile ways to achieve styling of a table for some output formats, namely HTML and, although it isn’t perfect, for Latex too. (But this doesn’t work for markdown outputs, and markdown doesn’t support such rich formatting in any case.)

Behind the scenes, when a table is displayed on a webpage like the one you’re reading right now, HTML (the language most of the internet is in) is used. Styling is a way of modifying the default HTML for showing tables so that they look nicer or better.

In the example below, you can see some of the options that are available:

precision is like .round()
na_rep sets how missing values are rendered
thousands sets the separator between every thousand (for readability)
formatter gives fine-grained control over the formatting of individual columns

pen_styled = (
    pen_sum_clean.style
    .format(precision=3, na_rep='Value missing', thousands=",",
            formatter={
                "Mean bill length (mm)": "{:.1f}",
                "Std. deviation of flipper length (mm)": lambda x: "{:,.0f} um".format(x*1e3)
                }
            )
    .set_caption("This is the title")
)
pen_styled

This is the title
		Median bill length (mm)	Mean bill length (mm)	Std. deviation of flipper length
species	island
Adelie	Biscoe	38.700	39.0	6.729
	Dream	38.550	38.5	6.585
	Torgersen	38.900	Value missing	6.232
Chinstrap	Dream	49.550	48.8	7.132
Gentoo	Biscoe	47.300	47.5	6.485

If you need to add more labels to either the index or the column names, you can. It’s a bit fiddly, but you can.

pen_sum_extra_col_info = pen_sum_clean.copy() # create an independent copy
pen_sum_extra_col_info.columns = [["Lengths", "Lengths", "Stds"], pen_sum_clean.columns]

pen_sum_extra_col_info_styled = (
    pen_sum_extra_col_info
    .style
    .format(precision=3, na_rep='Value missing', thousands=",",
            formatter={
                "Mean bill length (mm)": "{:.1f}",
                "Std. deviation of flipper length (mm)": lambda x: "{:,.0f} um".format(x*1e3)
                }
            )
    .set_caption("This is the title")
    .set_table_styles(
        [{'selector': 'th', 'props': [('text-align', 'center')]}]
        )
)
pen_sum_extra_col_info_styled

This is the title
		Lengths		Stds
		Median bill length (mm)	Mean bill length (mm)	Std. deviation of flipper length
species	island
Adelie	Biscoe	38.700	38.975	6.729
	Dream	38.550	38.502	6.585
	Torgersen	38.900	Value missing	6.232
Chinstrap	Dream	49.550	48.834	7.132
Gentoo	Biscoe	47.300	47.505	6.485

Let’s see an example of exporting this to Latex too:

pen_sum_extra_col_info_styled.to_latex()

'\\begin{table}\n\\caption{This is the title}\n\\thcenter\n\\begin{tabular}{llrrr}\n &  & \\multicolumn{2}{r}{Lengths} & Stds \\\\\n &  & Median bill length (mm) & Mean bill length (mm) & Std. deviation of flipper length \\\\\nspecies & island &  &  &  \\\\\n\\multirow[c]{3}{*}{Adelie} & Biscoe & 38.700 & 38.975 & 6.729 \\\\\n & Dream & 38.550 & 38.502 & 6.585 \\\\\n & Torgersen & 38.900 & Value missing & 6.232 \\\\\nChinstrap & Dream & 49.550 & 48.834 & 7.132 \\\\\nGentoo & Biscoe & 47.300 & 47.505 & 6.485 \\\\\n\\end{tabular}\n\\end{table}\n'

You can read more about the style functionality over at the pandas Styler docs.

Another thing you might reasonably want to do is provide summary statistics for all columns, as the last column, or for all rows, as the last row. To do this for, say, the mean when not using the pd.crosstab function, you will need to insert a summary row into the dataframe object. To start with, you need to actually create the summary row—this is achieved with the .mean(axis=0) method to get the mean over rows. We cast this into being a dataframe using pd.DataFrame as otherwise it would just be a single column, or Series object. Then we need to give the columns a name that’s better than the default “0”, and we choose a multi-level (here, two level) column name recognising that the index of our original dataframe has two levels: species and island. We actually just want to put “summary” in once and we’ve arbitrarily chosen the first level for that. Note that multi-level indices and columns can get complicated but the essential trick to bear in mind is that you replace a list of strings with a list of tuples of strings, eg for the first column ["Summary:", ...] becomes [("Summary", "summary level two"), ...]. Putting this all together gives us a dataframe with an index that is the same as the columns of the original dataframe:

summary_row = pd.DataFrame(pen_sum_extra_col_info.mean(axis=0))
summary_row.columns = [("Summary:", "")] # note our index has two levels
summary_row

		(Summary:, )
Lengths	Median bill length (mm)	42.600000
Lengths	Mean bill length (mm)	43.453872
Stds	Std. deviation of flipper length	6.632687

Next we need to transpose the new summary row (so that its columns align with those in our original data frame) using .T, and concatenate the two dataframes together:

pd.concat([pen_sum_extra_col_info, summary_row.T], axis=0)

		Lengths		Stds
		Median bill length (mm)	Mean bill length (mm)	Std. deviation of flipper length
Adelie	Biscoe	38.70	38.975000	6.729247
	Dream	38.55	38.501786	6.585083
	Torgersen	38.90	NaN	6.232238
Chinstrap	Dream	49.55	48.833824	7.131894
Gentoo	Biscoe	47.30	47.504878	6.484976
Summary:		42.60	43.453872	6.632687

Once this is done, you can apply all the usual stylings.

Writing pandas tables to file#

Writing pandas tables to file is fairly straightforward: just use one of pandas many, many output functions. These typically begin with .to_ and then the output name. The most useful output formats will be:

to_html()
to_latex()
to_string()
to_markdown()

Add the filename you’d like to write to within the brackets following these method names. For example, to write a latex table it would be:

pen_styled.to_latex(Path("outputs/table_one.tex"))

These files can then be picked up by other documents. Note that, sometimes, when exporting to Latex, the code will have “escape” characters, for example extra backslashes. In some versions of pandas you can turn these off with an escape=False keyword argument.

It’s not perfect, but if you’re writing a table to latex and want footnotes and source notes, you can make use of the to_latex() method’s caption= keyword argument.

Limitations to pandas tables#

pandas tables have some limitations. It is not easy to include rich information such as images: while they can be included in the HTML rendition of a table on a webpage or in a Jupyter Notebook, it’s far harder to export this kind of information sensibly to an output file that isn’t HTML.

A more tricky general issue with them is that it can be hard to include all of the relevant information you’d expect in a table: they work extremely well for a typical table that is just rows and columns with equal-sized cells, but it you want to include, say, a long row (of a single cell) at the end for either footnotes or source notes, there isn’t an obvious way to do it. Similarly, it’s hard to make parts of a table such as the title, sub-title, and stubhead label work well in all cases.

To create a footnote or source note row, you could insert an extra row like above, but it’s a very unsatisfactory work-around as your notes can only be in one of the columns (not spread across all of them) and it will lead to you losing the data types in at least one of your original columns.

While we can eventually expect newcomer gt to make building tables that bit easier, for now we have to make do.