Pandas中基于其他列均值替换指定列值的技术问题

Hey there! Let's work through these two data cleaning challenges together—they're super common, so I’ll break down each step clearly to fix your unmodified columns issue.

1. Replace Price=0 with Region's Average Price #

First, we need to calculate the correct average price per Region (excluding the 0 values, since those are the ones we want to replace), then map that average back to the rows where Price is 0.

Step-by-Step Code: #

import pandas as pd

# 1. Calculate average price per Region, excluding rows where Price=0
region_avg_price = df[df['Price'] != 0].groupby('Region')['Price'].mean()

# 2. Replace Price=0 values with the corresponding Region's average
# Use .loc to ensure we modify the original DataFrame (this is probably where you ran into issues!)
df.loc[df['Price'] == 0, 'Price'] = df.loc[df['Price'] == 0, 'Region'].map(region_avg_price)

# Optional: If some Regions have ALL Price=0, fill those NaNs with overall average Price
overall_avg_price = df[df['Price'] != 0]['Price'].mean()
df['Price'] = df['Price'].fillna(overall_avg_price)

Key Notes: #

• Always use .loc when modifying subsets of your DataFrame—Pandas can create copy slices otherwise, so your changes won't show up in the original data.
• Excluding Price=0 from the average calculation ensures you don't skew the mean with invalid values.

2. Replace Invalid Ratings & Update Rating Types #

For the Rating column, we first need to convert non-numeric values (NEW, -, Opening) to NaN, then fill those NaNs with the average rating for their Cuisine Type. Finally, we'll regenerate the Rating Types based on the cleaned Ratings.

Step-by-Step Code: #

# 1. Convert Rating to numeric (turns non-numeric values like NEW/-/Opening into NaN)
df['Rating'] = pd.to_numeric(df['Rating'], errors='coerce')

# 2. Calculate average rating per Cuisine Types, excluding NaN values
cuisine_avg_rating = df[df['Rating'].notna()].groupby('Cuisine Types')['Rating'].mean()

# 3. Fill NaN Ratings with the corresponding Cuisine Type's average
df['Rating'] = df['Rating'].fillna(df['Cuisine Types'].map(cuisine_avg_rating))

# 4. Optional: Fill any remaining NaNs (if a Cuisine Type has all invalid Ratings) with overall average
overall_avg_rating = df[df['Rating'].notna()]['Rating'].mean()
df['Rating'] = df['Rating'].fillna(overall_avg_rating)

# 5. Update Rating Types (adjust bins/labels to match your original logic!)
df['Rating Types'] = pd.cut(
    df['Rating'],
    bins=[0, 3.0, 4.0, 4.5, 5.0],
    labels=['Average', 'Good', 'Very Good', 'Excellent'],
    include_lowest=True
)

Key Notes: #

• Converting to numeric first makes it easy to identify and replace invalid values.
• Similar to the Price task, using .fillna() with a mapped group average ensures we use context-specific values instead of a global average.
• Adjust the bins and labels in pd.cut() to match whatever logic your original Rating Types used (e.g., maybe your thresholds are different!).

Why Your Previous Code Might Have Failed: #

• You didn't use .loc to modify the original DataFrame (Pandas was modifying a copy instead).
• You included invalid values (Price=0, non-numeric Ratings) when calculating group averages, leading to incorrect replacement values.
• You didn't convert the Rating column to numeric before trying to calculate averages.

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