I am using Jupyter notebook. I have combined two DataFrames together, so in order to differentiate between the two I have created a new third column 'Color Code' where there are two values 'a' and 'b'. I have no problem iterating through the list of colors I have defined but I need to be able to color dark vs light depending on the unique columns and 'Color Code' column.
I have an MRE below. Apologies for the big example DataFrame, just need to be able to showcase what I am trying to accomplish.
df1 = pd.DataFrame({'x1':['a', 'a', 'a', 'a', 'a', 'a', 'b', 'b', 'b', 'b', 'b', 'b', 'c', 'c', 'c', 'c', 'c', 'c', 'c', 'c', 'd', 'd', 'd', 'd'],
'x2':['3','4','8','0','11','1','15','5','67','3','1','4','5','88','9','3','7','2','3','43','34','56','96','26'],
'Color Mode':['a','a','b','b','b','b','a','a','a','a','b','b','a','b','b','b','b','b','b','b','a','a','a','b']})
def Color_Unique(s):
df = s.copy()
color_map1 = {}
color_map2 = {}
x = pd.DataFrame({'Color Mode': ['a']})
y = pd.DataFrame({'Color Mode': ['b']})
Trade_Cusip_Combo_Key = df['x1'].unique()
if x.any(axis=None):
colors_to_use = ['background-color: #ADD8E6', 'background-color: #90ee90', 'background-color: #FFD580', 'background-color: #CBC3E3', 'background-color: #D3D3D3', 'background-color: #C4A484']
colors_cycle = cycle(colors_to_use)
for Trade_Cusip_Combo in Trade_Cusip_Combo_Key:
color_map1[Trade_Cusip_Combo] = next(colors_cycle)
for index, row in df.iterrows():
if row['x1'] in Trade_Cusip_Combo_Key:
Trade_Cusip_Combo = row['x1']
my_color = color_map1[Trade_Cusip_Combo]
df.loc[index,:] = my_color
else:
df.loc[index,:] = 'background-color: '
return df
elif y.any(axis=None):
colors_to_use = ['background-color: #ADD8E6', 'background-color: #90ee90', 'background-color: #FFD580', 'background-color: #CBC3E3', 'background-color: #D3D3D3', 'background-color: #C4A484']
colors_cycle = cycle(colors_to_use)
for Trade_Cusip_Combo in Trade_Cusip_Combo_Key:
color_map2[Trade_Cusip_Combo] = next(colors_cycle)
for index, row in df.iterrows():
if row['x1'] in Trade_Cusip_Combo_Key:
Trade_Cusip_Combo = row['x1']
my_color = color_map2[Trade_Cusip_Combo]
df.loc[index,:] = my_color
else:
df.loc[index,:] = 'background-color: '
return df
else:
print("boken")
df4 = df1.style.apply(Color_Unique, axis=None)
df4
This is what my code currently looks like when you run my above MRE.
This is what I need it to look like:
You will notice dark vs light colors alternate when column 'x1' values are the same but column 'Color Mode' values are either 'a' (dark color) or 'b' (lighter color). I need my function to be able to iterate through my defined dark and light colors based on column 'x1' and column ' Color Mode' as well as skip a line after each group is colored (blue to green to orange to purple).
new_df1 = pd.DataFrame({'x1':['axe', 'axe', 'axe', 'axe', 'axe', 'axe', 'bench', 'bench', 'bench',
'bench', 'bench', 'bench', 'crunch', 'crunch', 'crunch', 'crunch', 'crunch', 'crunch',
'crunch', 'crunch', 'deed', 'deed', 'deed', 'deed'],
'x2':['Bob','Bob','Bob','Bob','Bob','Bob','Leo','Leo','Leo','Leo',
'Leo','Leo','Jamie','Jamie','Jamie','Jamie','Jamie','Jamie','Jamie','Jamie',
'John','John','John','Luke'],
'Price':['3.00','3.00','3.00','3.00','3.00','3.00','15.00','80.00','15.00','15.00',
'15.00','15.00','4.00','4.00','4.00','68.00','4.00','4.00','39.00','4.00',
'5.00','5.00','5.00','27.00'],
'Color Mode':['a','a','b','b','b','b','a','a','a',
'a','b','b','a','b','b','b','b','b',
'b','b','a','a','a','b']})
new picture: New Picture
Before turning to the "styling" part of the exercise, let's use a function to insert blank rows into your df. We can do this as follows:
import pandas as pd
df1 = pd.DataFrame({'x1':['a', 'a', 'a', 'a', 'a', 'a', 'b', 'b', 'b',
'b', 'b', 'b', 'c', 'c', 'c', 'c', 'c', 'c',
'c', 'c', 'd', 'd', 'd', 'd'],
'x2':['3','4','8','0','11','1','15','5','67','3',
'1','4','5','88','9','3','7','2','3','43',
'34','56','96','26'],
'Color Mode':['a','a','b','b','b','b','a','a','a',
'a','b','b','a','b','b','b','b','b',
'b','b','a','a','a','b']})
def blank_rows(df, col_name):
insert_idx = [v[-1]+1 for v in df.groupby(col_name).groups.values()]
for i, idx in enumerate(insert_idx[:-1]):
temp = pd.DataFrame({k: '' for k in df.columns}, index=[idx+i])
df = pd.concat([df.iloc[:idx+i], temp, df.iloc[idx+i:]])\
.reset_index(drop=True)
return df
# add `col_name` (e.g. `x1`) to specify which groups need to be split up
df = blank_rows(df1, 'x1')
# N.B. the groups inside column `col_name` need to be sorted,
# and the index needs to be "regular" (i.e. `0,1,2...`).
# E.g. `df = blank_rows(df1, 'Color Mode')` would get you a nonsensical result;
# Instead you would want to use:
# `df = blank_rows(df1.sort_values('Color Mode').reset_index(drop=True),
# 'Color Mode')`
df
will now contain empty rows like below:
df.iloc[[5,6,7,12,13,14],:]
Now, let's apply a function to take care of the styles. E.g.:
from itertools import cycle
def color_unique(df_input):
df = df_input.copy()
colors = ['#8EA9DB', '#D9E1F2', # blues
'#A9D08E', '#E2EFDA', # greens
'#F4B084', '#FCE4D6', # reds
'#9751CB', '#DEC8EE'] # purples
colors_cycle = cycle(colors)
groups = df.groupby(['x1','Color Mode']).groups
for k, v in groups.items():
if '' in k:
df.iloc[v] = f'background-color: white;'
else:
df.iloc[v] = f'background-color: {next(colors_cycle)};'
return df
df_styled = df.style.apply(color_unique, axis=None)
df_styled
Result:
Explanation
cycle
from itertools
, the list does not need to be exhaustive. (N.B.: the above function is expecting that each group in x1
is always split in 2 subgroups based on Color Mode
. E.g. if you expect groups that only have a
, not b
, the above function would need to be adjusted!)df.groupby
to isolate the different groups. Make sure to add sort=False
to preserve the original order of the groups.Groupby.groups
property stores a dict
with "group name" (e.g. ('a', 'a')
) as keys and the associated index values as values (e.g. for ('a', 'a')
the value is Int64Index([0, 1], dtype='int64')
..iloc
and assign a color. However, we want to skip the group that has key (''),('')
, For that reason, we want to iterate over group.items()
and catch both the (k)ey
and (v)alue
. If the key (tuple) contains an empty string, we color the row "white", else we assign the next color from our list of colors.