Let's say I have a dataframe like the following;
df = pd.DataFrame({'Sample': ['A', 'B', 'C', 'D','E','F'],
'NFW': [8.16, 8.63, 9.25, 8.97, 7.5, 8.21],
'Qubit': [55, 100, 229, 30, 42, 33],
'Lane': ['1', '1', '2', '2', '3', '3']})
I want to colorize the background of all rows based on the values of the Lane column dynamically. Also, I'll write this dataframe to an excel file and I need to keep all style changes in there too.
IIUC, you can use a colormap (for example from matplotlib) and map it to each row using a Categorical and style.apply:
import matplotlib
cmap = matplotlib.cm.get_cmap('Pastel1')
colors = [matplotlib.colors.rgb2hex(cmap(i)) for i in range(cmap.N)]
# ['#fbb4ae', '#b3cde3', '#ccebc5', '#decbe4', '#fed9a6', '#ffffcc', '#e5d8bd', '#fddaec', '#f2f2f2']
def color(df, colors=colors):
# get unique values as category
s = df['Lane'].astype('category')
# map the colors and create CSS string
s = ('background-color: '
+s.cat.rename_categories(colors[:len(s.cat.categories)])
.astype(str)
)
# expand to DataFrame size
return pd.DataFrame(np.tile(s, (df.shape[1], 1)).T,
index=df.index, columns=df.columns)
df.style.apply(color, axis=None)
