[Python] Style a dataframe in notebook with indentation

I have a table, which I have read from a database. I want to display the table in a jupyter notebook, showing the elements of totals and subtotals indented. I want the end result to look like this



The data will come from the database in a format like this

from pandas import MultiIndex, DataFrame, Series
index = MultiIndex.from_tuples(
[
('total', 'subtotal', 'a'),
('total', 'subtotal', 'b'),
('total', 'subtotal', 'c'),
('total', 'subtotal', 'd'),
('total', 'foo', 'foo'),
('total', 'bar', 'bar')],
names=['module_1', 'module_2', 'module_3'])

musd = [
106.564338488296,
60.5686589737704,
311.695156173571,
-90.3794796906721,
29.6147863260859,
-49.0048344046974]

# This is how the data as it is loaded from the database
df = Series(musd, index=index, name='musd').to_frame()


I try to make a new dataframe from a dictionary where all subtotals and totals are added as separate rows, and the column names have the proper indentation.

# Template indicating how to sum and the indentation
template = DataFrame(
[
("a", "module_3", 4),
("b", "module_3", 4),
("c", "module_3", 4),
("d", "module_3", 4),
("subtotal", "module_2", 2),
("foo", "module_3", 2),
("bar", "module_3", 2),
("total", "module_1", 0)], columns=["name", "sum_over", "indent"])

# Create dataframe where names are indentated, using space, and with the proper sums
d = {f"{' '*indent}{name}": sum(df.query(f"{sum_over} == '{name}'")["musd"])
for name, sum_over, indent in template.values}

# Create dataframe from dictionary
df_formated = DataFrame(d, index=[0]).T.reset_index().set_axis(['', 'musd'], axis=1)

# I try to display the dataframe using the 'style' property of the dataframe
df_formated.style.format(precision=1)


Which produces this



There is a lot of stuff missing here, lines, bold and italics etc. I hope to figure that out later. For now my biggest issue is that the columns are not indented. I have tried to add the indentation at the end of the string as well. But that does not work

And for the record, I don't necessarily have to use ´dataframe.style´, any other solution is also welcome.

Continue reading...