How to exclude the total row (margins) from styling (subset) in a pandas pivot table

Question

I have a .pivot_table with margins = True.

I want to run .style.bar and .style.background_gradient on it but the problem is that margins (column totals) are also formatted and set to the maximum value so it looks non-descriptive.

I had a few ideas on how to solve this, however, none are working so far:

1. Tried somehow excluding last row (the margins / total columns) from .style using subset, but failed.
2. Saving the last row in a separate data frame. Removing the last row from the original data frame, applying .style and then concatenating both data frames, however, here I get an error that I cannot concatenate styled data frames.

Here is the code:

import pandas as pd
import numpy as np
import seaborn as sns

df = pd.DataFrame({"A": ["foo", "foo", "foo", "foo", "foo",
                      "bar", "bar", "bar", "bar"],
                "B": ["one", "one", "one", "two", "two",
                      "one", "one", "two", "two"],
                "C": ["small", "large", "large", "small",
                      "small", "large", "small", "small",
                     "large"],
                "D": [1, 2, 2, 3, 3, 4, 5, 6, 7]})

df = df.pivot_table(values='D', index=['A','B'], columns=['C'], aggfunc=np.sum, margins=True, fill_value = 0)

df = (df.style.background_gradient(subset = 'large', cmap = sns.light_palette('red', as_cmap = True))
      .background_gradient(subset = 'small', cmap = sns.light_palette('green', as_cmap = True)))
df

So the goal is to exclude the last row from formatting (All / Margins / Total column).

Crapshooter

Answer 1

You have to be a bit more explicit, but you can accomplish what you need with get_level_values and a pd.IndexSlice

---

u = df.index.get_level_values(0)

(df.style.background_gradient(
  subset = pd.IndexSlice[u[:-1], 'large'],
  cmap = sns.light_palette('red', as_cmap = True))
.background_gradient(
  subset = pd.IndexSlice[u[:-1], 'small'],
  cmap = sns.light_palette('green', as_cmap = True)))

Cilo

Exactly what I needed! Thank you very much. – Crapshooter

for all columns do like this: dft1.style.background_gradient(cmap='Blues', axis=0, subset=pd.IndexSlice[u[:-1], :]) – Ptolemaist