python - 将一个数据框的样式结果复制到另一个数据框

Question

我有两个相同大小的数据帧，df_a 和 df_b。 df_a 仅包含数字并使用颜色图设置样式(使用 xlsxwriter 或 pandas 样式)。 df_b 包含混合值。是否可以将结果样式从一个数据帧复制到另一个数据帧，使得 df_b[i,j] 的背景颜色等于 df_a[i,j]?

在下面的示例中，df_b 的第一个元素应该是红色，第二个元素应该是黄色，依此类推。

df_a 的格式化代码(基于 xlsxwriter 示例)

# 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

让我们使用 Pandas 的 style 来格式化数据:

# 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()