使用groupby中位数填充NaN仅生效少量，求优化建议

Your issue happens because the way you're calculating and applying grouped medians doesn't align with the original DataFrame's structure. Let's break this down and implement the correct approach step by step.

Why Your Current Code Fails #

The line data.groupby(['product_id'], as_index=False).median() returns a summary DataFrame with one row per unique product_id—it doesn't match the original data's row count of 336666. When you pass this to fillna(), it can only fill rows where the summary table's index accidentally aligns with the original data's index, which explains why only a tiny number of NaNs get fixed.

Step 1: Correct Grouped Median Fill #

Use groupby().transform() instead—it broadcasts the grouped median value to every row in the original group, keeping the index perfectly aligned with your data:

# Calculate grouped medians, expanded to match the original data's shape
group_medians = data.groupby('product_id').transform('median')

# Fill NaNs using the group-specific medians
data = data.fillna(group_medians)

Step 2: Handle Remaining NaNs #

After the first step, you'll still have NaNs in cases where an entire product_id group has no valid values for a column (e.g., 4 groups where steering_gear_ratio is fully missing, or 519 rows of veh_reg_no with no values). Here's how to address this:

For Numeric Columns #

Fill remaining NaNs with the global median of the column:

# Select all numeric columns
numeric_cols = data.select_dtypes(include=['int64', 'float64']).columns

# Fill leftover NaNs with the column's overall median
data[numeric_cols] = data[numeric_cols].fillna(data[numeric_cols].median())

For Categorical/String Columns (like veh_reg_no) #

If veh_reg_no is a string/identifier column, fill missing values with a clear placeholder:

data['veh_reg_no'] = data['veh_reg_no'].fillna('Unknown')

Optional: Diagnose Full-Group NaNs #

To check which product_id groups have completely missing values for specific columns (helpful for targeted fixes):

# Calculate NaN counts per group and column
nan_per_group = data.groupby('product_id').apply(lambda x: x.isna().sum())

# Find groups where a column's NaN count equals the group's total row count (all values missing)
fully_missing_steering = nan_per_group[nan_per_group['steering_gear_ratio'] == data.groupby('product_id').size()]
print("Groups with fully missing steering_gear_ratio:\n", fully_missing_steering)

Step 3: Verify the Result #

Check the final NaN counts to confirm the fix:

print("Post-fix NaN counts per column:\n", data.isna().sum())

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