基于条件高效填充Pandas DataFrame及生成规则化方阵的方法

Hey there! Let's tackle your two Pandas problems one by one with efficient, vectorized solutions—no slow loops or clunky workarounds here.

1. Efficiently Fill a DataFrame Using Conditions from Another DataFrame #

First, let's ground this in a common scenario: you have a target DataFrame (df_target) that needs values filled based on rules or matching data from a source DataFrame (df_source). Here are the fastest, most scalable approaches:

a. Key-Based Filling with merge + fillna #

If your fill logic relies on matching a shared key (like an ID column), merging is Pandas' optimized go-to. It’s way faster than row-wise checks:

import pandas as pd
import numpy as np

# Sample data
df_target = pd.DataFrame({"user_id": [1,2,3,4], "subscription_plan": [np.nan, np.nan, np.nan, np.nan]})
df_source = pd.DataFrame({"user_id": [1,3], "plan": ["Premium", "Basic"]})

# Merge and fill missing values
merged = df_target.merge(df_source, on="user_id", how="left")
df_target["subscription_plan"] = merged["plan"].fillna(df_target["subscription_plan"])

b. Vectorized Conditional Filling with np.where #

If your fill logic is based on column value comparisons (not just key matches), use numpy’s vectorized operations—these run in C, avoiding Python-level loops:

# Example: Fill 'status' in df_target based on 'score' in df_source
df_target["status"] = np.where(
    df_source["score"] > 90, "Excellent",
    np.where(df_source["score"] > 70, "Good", "Needs Improvement")
)

c. Lightweight Lookup with map #

For simple value mapping from a source column to target, map is lean and efficient:

# Create a lookup series from df_source
plan_lookup = df_source.set_index("user_id")["plan"]
# Map to target, keeping existing values where no match exists
df_target["subscription_plan"] = df_target["user_id"].map(plan_lookup).fillna(df_target["subscription_plan"])

2. Optimized Rank Comparison Matrix Conversion #

I’m guessing your current solution uses apply or nested loops—let’s replace that with numpy broadcasting, which is orders of magnitude faster for large datasets (we’re talking 10-100x speedups for big ID lists).

Step-by-Step Implementation #

First, let’s define sample input data:

# Sample rank DataFrame: higher rank_score = better rank
df_ranks = pd.DataFrame({
    "id": ["Alice", "Bob", "Charlie", "Diana"],
    "rank_score": [85, 92, 85, 78]
})

The Vectorized Solution #

# Extract rank scores as a numpy array for fast broadcasting
rank_values = df_ranks["rank_score"].values

# Broadcast to create a matrix of rank comparisons
# Compare every row's rank to every column's rank
comparison_matrix = np.where(
    rank_values[:, np.newaxis] > rank_values, 1,  # Row rank > Column rank → 1
    np.where(rank_values[:, np.newaxis] < rank_values, 0, 0.5)  # Equal → 0.5, else 0
)

# Set the diagonal (same ID comparison) to NaN
np.fill_diagonal(comparison_matrix, np.nan)

# Convert back to a DataFrame with IDs as both index and columns
rank_matrix = pd.DataFrame(
    comparison_matrix,
    index=df_ranks["id"],
    columns=df_ranks["id"]
)

Why This Is Better Than Your Current Code #

• No loops: All operations are handled by numpy’s optimized C backend, which avoids slow Python-level iteration.
• Memory efficient: Broadcasting creates the matrix in place without intermediate DataFrames.
• Readable: The logic is explicit—easy to tweak if your ranking rules change (e.g., swap 1 and 0 if lower scores are better).

Handling Edge Cases #

If you have missing IDs or need to include specific IDs not in df_ranks, first reindex your source DataFrame to include all desired IDs (filling missing ranks as needed) before creating the matrix.

Content of the question comes from Stack Exchange, asked by sacuL