Copying the styling results of one dataframe to another

Question

I have two dataframes of identical size, df_a and df_b. df_a only contains numbers and is styled using a colormap (either using xlsxwriter or pandas styling). df_b contains mixed values. Is it possible to copy the resulting style from one dataframe to the other such that the background color of df_b[i,j] equals the background color of df_a[i,j]?

In the example below the first element of df_b should be red, the second yellow, and so on.

Formatting code for df_a (based on the xlsxwriter example)

# Create a Pandas dataframe from some data.
df_a = pd.DataFrame({'col_a': [10, 20, 30, 20, 15, 30, 45]})
df_b = pd.DataFrame({'col_b': ['a', 'a', 'c', 'd', 'e', 'f', 'g']})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_conditional.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df_a.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Apply a conditional format to the cell range.
worksheet.conditional_format('B2:C8', {'type': '3_color_scale'})

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Answer 1

Let's use Pandas' style to format the data:

# This will color every column into 3-category color
def style(df):
    s = {}
    for c1, c2 in zip(df.columns, df_a.columns):
        s[c1] = pd.cut(df_a[c2], bins=3, 
                          labels=[f'background-color:{c}' for c in ['red','blue','green']])
    return pd.DataFrame(s)


writer = pd.ExcelWriter(output_file)
df_a.style.apply(style, axis=None).to_excel(writer, sheet_name='df_a', index=False)
df_b.style.apply(style, axis=None).to_excel(writer, sheet_name='df_b', index=False)
writer.save()

Answer 2

This solution expands the accepted solution to more than one column and uses a color map instead of a predefined limited set of colors.

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

np.random.seed(24)
df_a = pd.DataFrame(10*np.random.randn(10, 4), columns=list('ABCD'))
df_b= pd.DataFrame(np.random.randn(10, 4), columns=list('ABCD'))

cm = sns.color_palette("Spectral", as_cmap=True)


def style(df):
    data = df_a.values
    data_normalized = (data - np.min(data)) / (np.max(data) - np.min(data))
    bg_colors = cm(data_normalized)
    bg_colors = np.apply_along_axis(matplotlib.colors.to_hex, 2, bg_colors)
    s = pd.DataFrame(bg_colors, columns=df.columns)
    for col in s.columns:
        s[col] = s[col].apply(lambda c: f'background-color:{c}')
    return s


writer = pd.ExcelWriter('temp.xlsx')
df_a.style.apply(style, axis=None).to_excel(writer, sheet_name='df_a', index=False)
df_b.style.apply(style, axis=None).to_excel(writer, sheet_name='df_b', index=False)
writer.save()

This results in the following: