基于特定条件填充DataFrame中npv列空值的技术方案问询

Hey there! Looking at your DataFrame structure, I can see you've got missing values in the npv column, with clear grouping patterns by Site, technology, and consistent cell suffixes (A/B/C on your cells entries). Let's break down practical solutions based on common "specific conditions" you might want to use:

Scenario 1: Fill by Site + Technology + Cell Suffix (Matching Patterned Values) #

From your sample data, I notice that for Site T21079, the U and V prefixed cells under UMTS2100 have identical npv values for matching suffixes (e.g., U21079A and V21079A both have 6). If this pattern holds, you can extract the cell suffix and fill missing values using non-null entries from the same site, tech, and suffix group:

import pandas as pd

# Extract the last character (A/B/C) from the cells column as a temporary grouping key
df1['cell_suffix'] = df1['cells'].str[-1]

# Fill missing npv values with the first non-null value from the same Site+Technology+Suffix group
df1['npv'] = df1.groupby(['Site', 'technology', 'cell_suffix'])['npv'].transform(
    lambda x: x.fillna(x.dropna().iloc[0]) if not x.dropna().empty else x
)

# Clean up the temporary suffix column
df1.drop('cell_suffix', axis=1, inplace=True)

Scenario 2: Fill with Grouped Mean/Median (Statistical Imputation) #

If your missing values don't follow a strict suffix pattern but are part of a logical group (like same Site + Technology), using grouped statistical measures is a safe bet. This works well if values in the same group are correlated:

# Fill missing values with the mean of the Site+Technology group
df1['npv'] = df1.groupby(['Site', 'technology'])['npv'].transform(
    lambda x: x.fillna(x.mean())
)

# OR use median instead (more robust to outliers)
df1['npv'] = df1.groupby(['Site', 'technology'])['npv'].transform(
    lambda x: x.fillna(x.median())
)

Scenario 3: Fill with Site-Wide Statistical Values #

If you want to impute missing values using the overall average/median for the entire site (regardless of technology), adjust the grouping key:

# Fill with the mean npv value for the entire Site
df1['npv'] = df1.groupby('Site')['npv'].transform(
    lambda x: x.fillna(x.mean())
)

Scenario 4: Manual Rule-Based Filling #

If you have explicit rules for specific missing entries (e.g., "T21079's UMTS900 missing values should match G21079A's 76"), you can target those rows directly:

# Example 1: Fill T21079 UMTS900 missing values with 76
mask = (df1['Site'] == 'T21079') & (df1['technology'] == 'UMTS900') & df1['npv'].isna()
df1.loc[mask, 'npv'] = 76

# Example 2: Fill T42406's U-prefixed UMTS2100 cells with matching V-prefixed values
# First, get the V-cell values mapped by suffix
t42406_v_npv = df1[(df1['Site'] == 'T42406') & (df1['cells'].str.startswith('V'))].set_index('cell_suffix')['npv']
# Map those values to the U-prefixed cells
df1.loc[(df1['Site'] == 'T42406') & (df1['cells'].str.startswith('U')), 'npv'] = df1.loc[
    (df1['Site'] == 'T42406') & (df1['cells'].str.startswith('U')), 'cell_suffix'
].map(t42406_v_npv)

Quick Pre-Checks to Guide Your Choice #

Before picking a method, it's helpful to understand your missing data distribution:

• Check total missing values: print(df1['npv'].isna().sum())
• Check missing values per group: print(df1.groupby(['Site', 'technology'])['npv'].isna().sum())
• Ensure npv is a numeric column after filling: df1['npv'] = pd.to_numeric(df1['npv'], errors='coerce')

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