Pandas DataFrame清洗：合并Entity列非空行下的跨行零散数据

Got it, let's solve this problem with efficient vectorized Pandas operations—no slow loops required! Here's a step-by-step solution tailored to your needs:

Step 1: Mock up a sample DataFrame (to replicate your scenario) #

First, let's create an example that matches your described data structure, so you can test the code directly:

import pandas as pd
import numpy as np

# Simulate your DataFrame with Entity anchor rows and split records
data = {
    'Entity': ['Customer A', np.nan, np.nan, 'Customer B', np.nan],
    'Order ID': [1001, np.nan, 1002, 1003, 1004],
    'Product': ['Laptop', 'Phone', np.nan, 'Tablet', 'Headphones'],
    'Unnamed: 0': [np.nan, np.nan, np.nan, np.nan, np.nan],
    'Unnamed: 1': [5000, 800, np.nan, 2000, 150]
}
df = pd.DataFrame(data)

Step 2: Create grouping keys using forward fill #

We'll use ffill() to assign every row with a null Entity to the nearest non-null Entity above it. This creates a vectorized grouping key without any loops:

# Generate a group key: forward-fill null Entity values to link rows to their anchor
group_key = df['Entity'].ffill()

Step 3: Aggregate groups to merge non-null values #

Next, we'll group by the key we just created and aggregate each column to merge all non-null values from the split rows into the anchor row. You can adjust the aggregation logic based on your needs:

Option 1: Concatenate all non-null values (for text/IDs)

Use this if you want to combine multiple non-null entries into a single string (e.g., multiple Order IDs for one Entity):

# Aggregate each column: join non-null values with a separator (customize as needed)
cleaned_df = df.groupby(group_key, as_index=False).agg(
    lambda x: ', '.join(str(v) for v in x.dropna())
)

Option 2: Fill missing values in the anchor row (if you just want to populate gaps)

Use this if the split rows only contain values that should fill missing spots in the anchor row:

# Take the first non-null value from each group (fills gaps in the anchor row)
cleaned_df = df.groupby(group_key, as_index=False).first()

Step 4: Remove all Unnamed: columns #

Finally, drop the unwanted columns with a vectorized column filter:

# Delete any column starting with "Unnamed:"
cleaned_df = cleaned_df.drop(columns=[col for col in cleaned_df.columns if col.startswith('Unnamed:')])

Why this works #

All operations here are fully vectorized (Pandas handles the underlying logic without explicit loops), making them fast even for large datasets. The ffill() and groupby() methods are optimized for performance, so you won't hit the slowdowns that come with manual row-by-row loops.

内容的提问来源于stack exchange，提问作者semmyk-research