如何在Pandas多层索引DataFrame中显示带百分号的数值

问题需求 #

希望将多级列结构的DataFrame中，所有内层为%的子列数值以带%符号的百分比形式显示（如1.23显示为1.23%），同时保留现有条形图样式。

现有数据填充代码：

# Create multindex columns to get price and variation from the last trading day
col = pd.MultiIndex.from_product([['D0','D-1','D-2','D-3','W-1','W-2','W-3','M-1'],['€/MWh', '%']])

# general dataframe to show all the prices information easily 
df_pfm = pd.DataFrame(columns=col)
                                  
# List with all the codes for futures contract Refinitiv Eikon
RIC_POWER =['DEBMc1','DEBMc2','DEBMc3','DEBQc1','DEBQc2','DEBQc3','DEBYc1','DEBYc2','DEBYc3']         
RIC_GAS_TTF = ['TFMBMc1','TFMBMc2','TFMBMc3','TFMBQc1','TFMBQc2','TFMBQc3','TFMBYc1','TFMBYc2','TFMBYc3']
RIC_GAS_THE = ['PNCGMc1', 'PNCGMc2', 'PNCGMc3', 'PNCGQc1', 'PNCGQc2', 'PNCGQc3', 'PNCGYc1', 'PNCGYc2', 'PNCGYc3']
RIC_ETS = ['CFI2Z3', 'CFI2Z4', 'CFI2Z5']
RIC_OIL = ['LCOc1', 'LCOc2']

df_pfm.insert(0, "RIC", RIC_POWER + RIC_GAS_TTF + RIC_GAS_THE + RIC_ETS + RIC_OIL,True)

# Start date for data retrieving from Python
Sdate = str(date.today())

# End date for data retrieving from Python
Edate =str(date.today() - timedelta(days=30))

# Assign the prices to the respective contract and days
for i in df_pfm.index:                  # Columns
        
    ric_hist = ek.get_timeseries(df_pfm['RIC'][i],['Close'], start_date=Edate, end_date=Sdate)
    
    # Variable that will receive new indexes 
    index = []
    
    # converting the indexes from the data from eikon in order to read them
    for j in ric_hist.index:
        j = int(j.strftime('%Y%m%d'))
        index.append(j)
        
    ric_hist.index = index
    
    # Assigning the values
    D0 = int(date.today().strftime('%Y%m%d'))
    
    D1 = date.today() - BDay(1)
    D1 = int(D1.strftime('%Y%m%d'))
    
    D2 = date.today() - BDay(2)
    D2 = int(D2.strftime('%Y%m%d'))
    
    D3 = date.today() - BDay(3)
    D3 = int(D3.strftime('%Y%m%d'))
    
    W1 = date.today() - BDay(5)
    W1 = int(W1.strftime('%Y%m%d'))
    
    W2 = date.today() - BDay(10)
    W2 = int(W2.strftime('%Y%m%d'))
    
    W3 = date.today() - BDay(15)
    W3 = int(W3.strftime('%Y%m%d'))
    
    M1 = date.today() - BDay(20)
    M1 = int(M1.strftime('%Y%m%d'))
    
    last_price, x = ek.get_data(df_pfm['RIC'][i],'CF_LAST')
    
    df_pfm.iloc[i,2] = last_price.iloc[0][1]
    df_pfm.iloc[i,4] = ric_hist['CLOSE'].loc[D1]
    df_pfm.iloc[i,6] = ric_hist['CLOSE'].loc[D2]
    df_pfm.iloc[i,8] = ric_hist['CLOSE'].loc[D3]
    df_pfm.iloc[i,10] = ric_hist['CLOSE'].loc[W1]
    df_pfm.iloc[i,12] = ric_hist['CLOSE'].loc[W2]
    df_pfm.iloc[i,14] = ric_hist['CLOSE'].loc[W3]
    df_pfm.iloc[i,16] = ric_hist['CLOSE'].loc[M1]
    
    df_pfm.iloc[i,3] =  (((df_pfm.iloc[i,2] - df_pfm.iloc[i,4])/df_pfm.iloc[i,2])*100).round(2)
    df_pfm.iloc[i,5] =  (((df_pfm.iloc[i,4] - df_pfm.iloc[i,6])/df_pfm.iloc[i,4])*100).round(2)
    df_pfm.iloc[i,7] =  (((df_pfm.iloc[i,6] - df_pfm.iloc[i,8])/df_pfm.iloc[i,6])*100).round(2)
    df_pfm.iloc[i,9] =  (((df_pfm.iloc[i,8] - df_pfm.iloc[i,10])/df_pfm.iloc[i,8])*100).round(2)
    df_pfm.iloc[i,11] = (((df_pfm.iloc[i,10] - df_pfm.iloc[i,12])/df_pfm.iloc[i,10])*100).round(2)
    df_pfm.iloc[i,13] = (((df_pfm.iloc[i,12] - df_pfm.iloc[i,14])/df_pfm.iloc[i,12])*100).round(2)
    df_pfm.iloc[i,15] = (((df_pfm.iloc[i,14] - df_pfm.iloc[i,16])/df_pfm.iloc[i,14])*100).round(2)

当前样式设置代码：

mkt_view = df_pfm.style.bar(subset=[('D0','%'),('D-1','%'),('D-2','%'),('D-3','%'),('W-1','%'),('W-2','%'),('W-3','%')], 
                     color=['#d65f5f','#5fba7d']).format(precision=2)

解决方案 #

通过style.format()的字典参数，针对多级列的不同子列指定格式规则：

1. 对所有内层列名为%的列，使用"{:.2f}%"格式添加%符号；
2. 对价格列（内层列名为€/MWh）保留两位小数格式；
3. 保留原有条形图样式设置。

修改后的样式代码：

# 用字典推导式快速构建格式规则
format_rules = {
    col: "{:.2f}%" if col[1] == '%' else "{:.2f}" 
    for col in df_pfm.columns 
    if col[1] in ['%', '€/MWh']
}

# 应用样式和格式
mkt_view = df_pfm.style.bar(
    subset=[('D0','%'),('D-1','%'),('D-2','%'),('D-3','%'),('W-1','%'),('W-2','%'),('W-3','%')], 
    color=['#d65f5f','#5fba7d']
).format(format_rules)

这样设置后，所有%子列的数值会自动显示为带%符号的格式（如2.34显示为2.34%），同时价格列保持两位小数的数值显示。

内容的提问来源于stack exchange，提问作者user5484106