Python Pandas+Streamlit下DataFrame数值格式化与条件着色问题求助

需求与矛盾 #

在Python/Pandas+Streamlit可视化开发中，需处理包含文本与字符串类型数值的DataFrame字典，实现两个核心需求：

• 将数值转为易读格式（如用1 M替代1,000,000）
• 条件着色（如P/E列数值<25时显示绿色文本）

但遇到两难困境：

• 若先将数值转为格式化字符串，后续无法基于数值进行条件着色判断
• 若先通过Pandas Styler添加条件着色，会出现TypeError: 'Styler' object is not subscriptable错误，且数值显示冗余（如20.040102030），无法转为易读格式

用户当前代码实现（存在errors='coerce'导致的着色失效问题）：

def apply_conditional_formatting(df, column_rules):
    styled_df = df.style
    # Example: Apply green text if P/E < 25
    if 'P/E' in df.columns:
        df['P/E'] = pd.to_numeric(df['P/E'], errors='coerce')
        styled_df = styled_df.map(
            lambda val: 'color: green' if isinstance(val, (int, float)) and val < 25 else '', 
            subset=['P/E']
        )
    if 'Market Cap' in df.columns:
        df['Market Cap'] = pd.to_numeric(df['Market Cap'], errors='coerce')
        styled_df = styled_df.map(
            lambda val: 'color: green' if isinstance(val, (int, float)) and val > 1000000 else '', 
            subset=['Market Cap']
        )
    # Extend the logic to other rules here...
    return styled_df

def apply_formatting_to_dataframes(dataframes, column_rules):
    # Apply conditional formatting to a dictionary of DataFrames.
    styled_dataframes = {}

    for section, df in dataframes.items():
        styled_dataframes[section] = apply_conditional_formatting(df, column_rules)

    return styled_dataframes

def convert_to_readable(num):
    if num is None or num == 'N/A':
        return 'N/A'
    if isinstance(num, str):
        try:
            num = float(num)  # Try converting strings to float
        except ValueError:
            return num

    if num >= 1_000_000_000 or num <= -1_000_000_000:
        return f'{num / 1_000_000_000:.2f}B'
    elif num >= 1_000_000 or num <= -1_000_000:
        return f'{num / 1_000_000:.2f}M'
    elif num >= 1_000 or num <= -1_000:
        return f'{num / 1_000:.2f}K'
    else:
        return f'{num:.2f}'

def convert_to_readable_dataframes(df):
    # Convert numeric values to a more readable format in the output df
    for column in df.columns:
        df[column] = df[column].apply(lambda x: convert_to_readable(x))
    return df

for section, df in financial_sections_dataframes.items():
    readable_df = convert_to_readable_dataframes(df)
    formatted_df = apply_conditional_formatting(readable_df, column_rules)

示例DataFrame：

example_df = pd.DataFrame({
    "Country": ["Netherlands", "France", "Luxembourg", "France"],
    "Market Cap": [142307622912, 1230484753, 12947592845, 987462847],
    "P/E": [33.66, 21.14, 22.87, 7.45],
    "Price": [131.28, 19.80, 22.76, 0.68],
    "Change": [-0.03, -0.02, -0.01, -0.01],
    "Volume": ["1091234", "326568", "629141", "400476"]
})

# Assuming this would be part of a larger dictionary
dataframes = {
    "Example Section": example_df
}

解决方案 #

核心思路：保留原始数值列用于条件判断，通过Pandas Styler同时实现数值格式化显示与条件着色，避免修改原始DataFrame的数值类型。

完整实现代码 #

import pandas as pd
import streamlit as st

def convert_to_readable(num):
    """用于Styler的数值格式化函数，处理单个数值"""
    if pd.isna(num):
        return 'N/A'
    if isinstance(num, str):
        try:
            num = float(num)
        except ValueError:
            return num
    
    abs_num = abs(num)
    if abs_num >= 1_000_000_000:
        return f'{num / 1_000_000_000:.2f}B'
    elif abs_num >= 1_000_000:
        return f'{num / 1_000_000:.2f}M'
    elif abs_num >= 1_000:
        return f'{num / 1_000:.2f}K'
    else:
        return f'{num:.2f}'

def apply_style(df):
    """整合数值格式化与条件着色的Styler处理函数"""
    # 先将字符串类型的数值列转换为数值类型（不修改原始df，用副本）
    styled_df = df.copy()
    numeric_cols = ['P/E', 'Market Cap', 'Price', 'Change', 'Volume']
    for col in numeric_cols:
        if col in styled_df.columns:
            styled_df[col] = pd.to_numeric(styled_df[col], errors='coerce')
    
    # 创建Styler对象
    styler = styled_df.style
    
    # 1. 应用数值格式化
    styler = styler.format(convert_to_readable)
    
    # 2. 应用条件着色
    if 'P/E' in styled_df.columns:
        styler = styler.applymap(
            lambda val: 'color: green' if isinstance(val, (int, float)) and val < 25 else '',
            subset=['P/E']
        )
    if 'Market Cap' in styled_df.columns:
        styler = styler.applymap(
            lambda val: 'color: green' if isinstance(val, (int, float)) and val > 1_000_000 else '',
            subset=['Market Cap']
        )
    # 可扩展其他列的着色规则
    
    return styler

def process_dataframes(dataframes):
    """处理DataFrame字典，返回带样式的Styler字典"""
    styled_dataframes = {}
    for section, df in dataframes.items():
        styled_dataframes[section] = apply_style(df)
    return styled_dataframes

# 测试示例
if __name__ == "__main__":
    example_df = pd.DataFrame({
        "Country": ["Netherlands", "France", "Luxembourg", "France"],
        "Market Cap": [142307622912, 1230484753, 12947592845, 987462847],
        "P/E": [33.66, 21.14, 22.87, 7.45],
        "Price": [131.28, 19.80, 22.76, 0.68],
        "Change": [-0.03, -0.02, -0.01, -0.01],
        "Volume": ["1091234", "326568", "629141", "400476"]
    })
    dataframes = {"Example Section": example_df}
    
    # 处理数据
    styled_dfs = process_dataframes(dataframes)
    
    # Streamlit渲染
    for section, styler in styled_dfs.items():
        st.subheader(section)
        st.dataframe(styler)

关键说明 #

1. 数值转换与分离：创建DataFrame副本并将字符串数值转为数值类型，保留原始数据的同时，为条件着色提供可判断的数值
2. Styler双处理：
   • 用styler.format()统一应用易读格式，仅改变显示内容，不修改底层数值
   • 用applymap()基于转换后的数值列实现条件着色，避免字符串判断的问题
3. Streamlit兼容：Pandas Styler对象可直接传入st.dataframe()渲染，无需额外转换

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