Python中识别DataFrame中以“?”标识的缺失值并基于列间数学关系实现自动填充的最优方案问询

Let’s break down your questions with practical, efficient solutions tailored to your scenario:

1. Identifying "?" as Missing Values in Python #

First, you need to ensure pandas recognizes "?" as a missing value (NaN). Here’s how to handle it:

• When loading data: If reading from a CSV or similar file, use na_values='?' in pd.read_csv() to automatically convert "?" entries to np.nan:

  import pandas as pd
  import numpy as np
  
  df = pd.read_csv("your_data.csv", na_values="?")
  

• If the DataFrame already exists: Replace existing "?" values with np.nan in place:

  df.replace("?", np.nan, inplace=True)
  

To verify missing values:

• Use df.isna() to get a boolean DataFrame where True marks missing entries.
• Use df.isna().sum() to count missing values per column, which helps quickly assess the scale of missing data.

2. Optimal Missing Value Handling for Columns with Fixed Mathematical Relationships #

The best approach here is to compute missing values directly using your known equations instead of generic imputation methods (like mean/median). Since the relationships are guaranteed to hold for every row, this gives you 100% accurate values rather than estimates.

Key tips:

• Prioritize equations that use columns with the least missing data to maximize the number of fillable rows.
• Ensure each column with missing values has at least one equation that can compute it using other non-missing columns in the same row.

3. Efficient Automatic Filling for Large Datasets #

Naive column-by-column checks or row-wise loops are slow for big datasets—vectorized operations in pandas are the way to go, as they leverage optimized C code under the hood. Here’s a scalable solution:

Step 1: Define your equations as a dictionary #

Map each column to a lambda function that computes its value from other columns:

equations = {
    "col1": lambda df: df["col5"] - df["col2"] + df["col4"],
    "col3": lambda df: df["col6"] - df["col4"],
    "col6": lambda df: df["col3"] + df["col4"],
    # Add all other column relationships here
}

Step 2: Vectorized filling loop #

Iterate over each column and fill missing values using the corresponding equation. This avoids slow Python-level row loops:

for col, calc_func in equations.items():
    # Get mask of missing values in the current column
    missing_mask = df[col].isna()
    if missing_mask.any():
        # Compute values only for rows where the column is missing
        df.loc[missing_mask, col] = calc_func(df.loc[missing_mask])

Handling circular dependencies #

If columns depend on each other (e.g., col3 relies on col6 and vice versa), run the loop multiple times until no more missing values can be filled:

# Repeat until no new values are filled
prev_missing_count = df.isna().sum().sum()
while True:
    for col, calc_func in equations.items():
        missing_mask = df[col].isna()
        if missing_mask.any():
            df.loc[missing_mask, col] = calc_func(df.loc[missing_mask])
    current_missing_count = df.isna().sum().sum()
    if current_missing_count == prev_missing_count:
        break
    prev_missing_count = current_missing_count

This loop ensures you fill as many values as possible using your defined relationships, even when columns are interdependent.

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