Search code examples
pythonpandasdataframepandas-styles

How to style groups of rows differently based on a column's value with Pandas Styler

What I am trying to accomplish:

I have the following dataframe, df:

data = {'person': {0: 'a',
  1: 'a',
  2: 'a',
  3: 'a',
  4: 'a',
  5: 'a',
  6: 'b',
  7: 'b',
  8: 'b',
  9: 'b',
  10: 'b',
  11: 'b',
  12: 'c',
  13: 'c',
  14: 'c',
  15: 'c',
  16: 'c',
  17: 'c'},
 'x': {0: 1,
  1: 1,
  2: 1,
  3: 1,
  4: 1,
  5: 1,
  6: 1,
  7: 1,
  8: 1,
  9: 1,
  10: 1,
  11: 1,
  12: 1,
  13: 1,
  14: 1,
  15: 1,
  16: 1,
  17: 1},
 'y': {0: 2,
  1: 2,
  2: 2,
  3: 2,
  4: 2,
  5: 2,
  6: 2,
  7: 2,
  8: 2,
  9: 2,
  10: 2,
  11: 2,
  12: 2,
  13: 2,
  14: 2,
  15: 2,
  16: 2,
  17: 2},
 'z': {0: 'foo',
  1: 'foo',
  2: 'foo',
  3: 'bar',
  4: 'bar',
  5: 'bar',
  6: 'foo',
  7: 'foo',
  8: 'foo',
  9: 'bar',
  10: 'bar',
  11: 'bar',
  12: 'foo',
  13: 'foo',
  14: 'foo',
  15: 'bar',
  16: 'bar',
  17: 'bar'}}

df = pd.DataFrame.from_dict(data, orient='columns')

And I want to style groups of rows differently (in different sets of alternating colors) based on the value of z FOR EACH VALUE OF person.

My desired output:

enter image description here

What I have tried:

Initially, I figured I could use a nested loop to break out each z for each person. I tried testing this out initially for just one person, like so:


COLORS = {
     'foo':['red','green'],
     'bar':['blue','yellow']
}

test = df.loc[df.person=='a'].copy()

sub_person = pd.DataFrame()

for val in test.z.unique():
     i_test = test.loc[test.z==val].copy()
     c1 = COLORS[val][0]
     c2 = COLORS[val][-1]
     css_alt_rows = f'background-color: {c1}; color: {c2};'

     i_test = (i_test.style.apply(lambda col: np.where(col.index % 2, css_alt_rows,None)))

     sub_person = pd.concat([sub_person,i_test])

I thought this was a clever solution to handle the different stylings individually, but I was met with the error:

TypeError: cannot concatenate object of type '<class 'pandas.io.formats.style.Styler'>'; only Series and DataFrame objs are valid

So, turns out this code cannot work as you cannot concat Styler objects.

Next, I tried a similar strategy by nesting the lambda function with another np.where() conditional:

COLORS = {
     'foo':['red','green'],
     'bar':['blue','yellow']
}

test = df.loc[df.person=='a'].copy()

for val in test.z.unique():

     c1 = COLORS[val][0]
     c2 = COLORS[val][-1]
     css_alt_rows = f'background-color: {c1}; color: {c2};'

     test = (test.style.apply(lambda col: np.where(np.where(col.index % 2, css_alt_rows,None),None)))

But I get the following error:

AttributeError: 'Styler' object has no attribute 'style'

Which makes sense as after the first iteration of the loop, test is a styler object, which results in test.style raising an error in the next iteration.

So, how would I go about applying these stylings for each z for each person?

Also, how could I add a bottom border in the last row of each person without being able to style groupings individually and concatenating them?

Note: Yes, the colors in colors won't match the image, which is fine.

Solution

  • Another possibility is to do a background_gradient with a custom ListedColormap :

    from operator import itemgetter
from matplotlib.colors import ListedColormap

colors = {"y": ["#fff2cc", "#ffe59a"], "b": ["#d0e1e2", "#a2c4c9"]}
cmap = ListedColormap(colors["y"] + colors["b"])
ng = df.groupby(["person", "z"], sort=False).ngroup()
gmap = ng.groupby(ng).cumcount() % 2 + ng % 2 * 2 # in a mozway

border_css = {"selector": "td", "props": "border-bottom: 3px solid black;"}

st = (
    df.style.background_gradient(gmap=gmap, cmap=cmap)
    .set_table_styles(
        {
            idx: [border_css]
            for idx in map(
                itemgetter(-1),
                df.groupby("person").indices.values(),
            )
        }, axis=1,
    ).set_properties(**{"text-align": "right", "width": "110px"})
     .hide(axis=0)
)

    enter image description here