Write a multi-index pandas dataframe as a table

[stevenlis]

import seaborn as sns
df = sns.load_dataset('tips')
(
    df.groupby(['sex', 'smoker', 'time'])[['tip', 'size', 'total_bill']]
    .mean().round(1).reset_index()
    .pivot(index=['smoker', 'time'], columns=['sex'])
)

It looks like this in a Jupyter notebook environment:

I would like to write a table like this in a PDF:

(Please disregard the double/cell borders above. I am unsure how to control them in HTML. Single borders work fine for me)

I found an example here, but it doesn't work on a multi-index dataframe, which is common when generating summary statistics. I'm wondering if someone could help me with this.

Thanks in advance.

[gmischler]

Give them tables, and they will want singing, dancing, and jingling tables... 🌟🔔👯🎵🎆

The most banal obstacle to this is that our tables currently don't support rowspan.
Other than that, it seems largely to be a question of appropriate cell formatting.
The ability to have several heading lines has just been added with the latest release. Your example suggests that adding a vertical version of that (key columns?) might also be helpful. Coming up with an API that can combine the two (and their different formatting rules) without too much confusion will be an interesting challenge.

As usual in a situation like this: PRs welcome! 😉

[stevenlis]

A workaround:

import seaborn as sns
df = sns.load_dataset('tips')
df = (
    df.groupby(['sex', 'smoker', 'time'])[['tip', 'size', 'total_bill']]
    .mean().round(1).reset_index()
    .pivot(index=['smoker', 'time'], columns=['sex'])
).reset_index().astype('str')

levels = df.columns.nlevels
df.columns.names = [None]*levels

columns = [df.columns.get_level_values(l).tolist() for l in range(levels)]
rows = df.values.tolist()
data = columns + rows

with pdf.table(
    line_height=pdf.font_size * 2,
    text_align='RIGHT',
    width=160
) as table:
    for data_row in data:
        row = table.row()
        for datum in data_row:
            row.cell(datum)

[Lucas-C]

For the record, there has been a follow-up of this discussion in #970

[stevenlis]

Another workaround is using to_html and write_html:

from fpdf import FPDF
import seaborn as sns

pdf = FPDF()
pdf.set_font_size(16)
pdf.add_page()

df = sns.load_dataset('tips')
string_html = (
    df.groupby(['sex', 'smoker', 'time'])[['tip', 'size', 'total_bill']]
    .mean().round(1).reset_index()
    .pivot(index=['smoker', 'time'], columns=['sex'])
).reset_index().to_html()

pdf.write_html(string_html)
pdf.output('table-pdf.pdf')