Pandas DataFrame多列高效格式化方法问询

Great question—bulk formatting columns by their suffixes is totally doable and will save you tons of repetitive code. Here are two main approaches depending on whether you just want to format how the data displays (without changing the underlying values) or need to modify the actual data itself:

1. Display-Only Formatting (Recommended) #

This is the best option if you want to keep your numeric data intact for calculations but just want it to look nicer when displayed (like in Jupyter notebooks or exported reports). Pandas' Styler class makes this straightforward.

First, define a function that checks a column's suffix and returns the appropriate format string. Then pass this function to style.format():

import pandas as pd

# Sample data to test with
data = {
    'sales.pct': [0.123, 0.456, 0.789],
    'prod.cost': [1234.56, 789.01, 4567.89],
    'customer.count': [100, 250, 500],
    'regular_col': [1, 2, 3]
}
df = pd.DataFrame(data)

# Define your format rules
def format_column(col_name):
    if col_name.endswith('.pct'):
        return '{:.2%}'  # 2 decimal places as percentage
    elif col_name.endswith('.cost'):
        return '${:,.2f}'  # Currency with commas and 2 decimals
    elif col_name.endswith('.count'):
        return '{:,d}'  # Integer with commas
    else:
        return None  # Use default formatting for other columns

# Apply the formatting
styled_df = df.style.format(format_column)

# Display in Jupyter or save to HTML
styled_df

This gives you a styled object that renders beautifully in notebooks, and you can even add table styles (like alternating row colors) if you want. The original DataFrame's numeric values stay untouched, so you can still run calculations on them.

2. Modify Underlying Data (Use Sparingly) #

If you absolutely need the formatted strings stored in the DataFrame (though this isn't ideal for further math operations), you can target columns by suffix and apply the formatting directly:

# Create a mapping of suffixes to format strings
format_map = {
    '.pct': '{:.2%}',
    '.cost': '${:,.2f}',
    '.count': '{:,d}'
}

# Loop through each suffix and apply formatting
for suffix, fmt in format_map.items():
    target_cols = [col for col in df.columns if col.endswith(suffix)]
    df[target_cols] = df[target_cols].applymap(lambda x: fmt.format(x))

# Now df has string values in the formatted columns
print(df)

Just keep in mind that converting numeric columns to strings means you can't perform arithmetic operations on them anymore, so only use this if you're done with calculations and need the data in string format for export.

Both methods are scalable—just add more suffix-format pairs to the function or dictionary as needed!

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