Pandas中Styler设置空值显示为空字符串后，使用format添加%符号时空值显示NAN%的问题求助

Hey there! Let's break down why your NaN values are showing up as NaN% instead of empty strings, and how to fix this without messing up your future subtraction operations.

Why This Happens #

When you use .format({'B':'{:}%','C':'{:}%'}), Pandas converts all values in those columns to strings first—including NaN values, which get turned into the string 'NaN'. Then it appends the % symbol to every string, resulting in NaN%. This overrides the set_na_rep('') setting because the formatting step runs after the NA representation is initially set.

The good news is we can fix this by controlling how NaN values are handled during formatting, without altering your original DataFrame (so your subtraction operations will still work!).

Solution: Custom Formatting Functions #

Instead of using a simple string format like '{:}%', create a custom function that checks if a value is NaN. If it is, return an empty string; otherwise, format it with the % symbol.

Step-by-Step Code Fix #

First, make sure you have pandas imported (you already do, but just to be clear):

import pandas as pd

Then, define a reusable formatting function for percentage columns:

def format_with_percent(x):
    # Check if the value is not NaN
    if pd.notna(x):
        return f"{x}%"
    # Return empty string for NaN
    return ""

Now update your styled DataFrame code to use this function instead of the string format:

df_styled = (
    df.style
    .apply(highlight_cells, axis=None)
    .format({'B': format_with_percent, 'C': format_with_percent})
    .set_table_styles(...)  # Keep your existing table styles here
)

Alternative: Inline Lambda Function #

If you prefer a more concise approach without defining a separate function, you can use inline lambda functions directly in the .format() method:

df_styled = (
    df.style
    .apply(highlight_cells, axis=None)
    .format({
        'B': lambda x: f"{x}%" if pd.notna(x) else "",
        'C': lambda x: f"{x}%" if pd.notna(x) else ""
    })
    .set_table_styles(...)
)

Why This Works #

This approach lets you explicitly handle NaN values during formatting:

• For valid numerical values, we append the % symbol as intended.
• For NaN values, we return an empty string instead of letting Pandas convert NaN to 'NaN' and add %.

Your original DataFrame remains untouched—NaN values are still present as numerical NaNs, so your subtraction operations will work perfectly fine.

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