Python pandas：匹配ID与CODE后将指定列数据移至目标行

Got it, let's tackle this problem step by step! You want to merge rows where ID and CODE match, stack all non-null TAG values into sequential columns (TAG1, TAG2, ...) and drop duplicate rows. Here's a flexible approach that delivers exactly the result you're looking for:

Full Working Code #

import pandas as pd
import numpy as np

# Your initial DataFrame
df = pd.DataFrame({
    'ID': ['123', '123', '123', '154', '167', '167'],
    'NAME': ['Adam', 'Adam', 'Adam', 'Bob', 'Charlie', 'Charlie'],
    'CODE': ['1001', '1001', '1011', '1002', 'A0101', 'A0101'],
    'TAG1': ['A123', 'B123', 'K123', 'D123', 'E123', 'G123'],
    'TAG2': [np.NaN, 'C123', 'L123', np.NaN, 'F123', 'H123'],
    'TAG3': [np.NaN, 'M123', np.NaN, np.NaN, np.NaN, np.NaN]
})

# Step 1: Identify all TAG columns (works even if you add more later!)
tag_columns = [col for col in df.columns if col.startswith('TAG')]

# Step 2: Define a function to collect non-null TAGs per group
def gather_tags(group):
    # Flatten all TAG values in the group into a single list, remove NaNs
    all_tags = group[tag_columns].values.flatten()
    valid_tags = all_tags[~pd.isna(all_tags)]
    # Return a Series with sequential TAG column names
    return pd.Series(valid_tags, index=[f'TAG{i+1}' for i in range(len(valid_tags))])

# Step 3: Group by ID/NAME/CODE and apply the function
merged_groups = df.groupby(['ID', 'NAME', 'CODE'], as_index=False).apply(gather_tags).reset_index()

# Step 4: Clean up the result (drop the extra index column created by apply)
final_df = merged_groups.drop(columns='level_3')

# Check the output
print(final_df)

Output (Matches Your Expected Result) #

ID     NAME    CODE  TAG1  TAG2  TAG3  TAG4
0  123     Adam    1001  A123  B123  C123  M123
1  123     Adam    1011  K123  L123   NaN   NaN
2  154      Bob    1002  D123   NaN   NaN   NaN
3  167  Charlie  A0101  E123  F123  G123  H123

Why This Works (And What Was Missing In Your Attempt) #

• Flexible TAG column detection: We automatically find all columns starting with TAG, so this works even if you add TAG4, TAG5, etc. later.
• Preserves order of TAG values: The function keeps the original order of non-null TAGs from your rows, which matches your expected output.
• Fixes your pivot_table issue: Your original code only handled TAG1 and used aggfunc='sum', which isn't right for collecting string values. This approach captures all TAG columns and filters out NaNs properly.

Key Steps Explained #

1. Grouping: We group rows by ID, NAME, and CODE (since NAME is tied to ID, this ensures we keep the correct name for each group).
2. Collecting Tags: For each group, we flatten all TAG values into a single list, remove any NaN entries, then map them to new sequential TAG columns.
3. Cleanup: The apply method adds an extra index column (level_3), so we drop it to get a clean DataFrame.

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