Skip to main content
 首页 » 编程设计

python之Pandas :基于另一列的数据透视表中的样式单元格

2024年11月24日13birdshome

我想根据该单元格的另一列突出显示数据透视表中的单元格。例如,我想根据“颜色”列突出显示“值”。

import pandas as pd 
 
data = { 'Week': [1,2,3,4,5,1,2,3,4,5], 
    'Color': ['Green','Red','Green','Yellow','Red','Green','Yellow','Red','Yellow','Red'], 
    'Part': ['A','A','A','A','A','B','B','B','B','B'], 
    'Value': [10, -20, 20, -20, -10, 10, -5, -8, -9, -10] 
    } 
 
df = pd.DataFrame(data) 
 
df_pivot = df.pivot_table(index='Part', columns='Week',values='Value') 

预期输出:

不幸的是,我无法在我的网络搜索中找到相关示例来帮助我。

请您参考如下方法:

利用内置的 pandas styling功能:

import pandas as pd 
 
# Initialize example dataframe 
data = { 
    'Week': [1, 2, 3, 4, 5, 1, 2, 3, 4, 5], 
    'Color': ['Green', 'Red', 'Green', 'Yellow', 'Red', 'Green', 'Yellow', 'Red', 'Yellow', 'Red'], 
    'Part': ['A', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'B'], 
    'Value': [10, -20, 20, -20, -10, 10, -5, -8, -9, -10] 
} 
df = pd.DataFrame(data) 
 
# Merge 'Color' and 'Value' columns into one single column 
df['Value'] = list(zip(df.Color, df.Value)) 
 
# Perform pivot operation 
df = df.pivot(index='Part', columns='Week', values='Value') 
 
# Split into two dataframes: a colors dataframe and a numerical values dataframe 
color_df = df.applymap(lambda x: x[0]) 
value_df = df.applymap(lambda x: x[1]) 
 
# Transform dataframe with colors into formatting commands 
color_df = color_df.applymap(lambda x: f'background-color: {x.lower()}') 
 
# Apply color styling to values dataframe 
styled_df = value_df.style.apply(lambda x: color_df, axis=None) 
styled_df.to_excel('output.xlsx')