基于Pandas melt按名称多列分组转目标列：特殊数据结构处理

Hey there! This is a common but tricky reshaping scenario because of the inconsistent column name separators (__ vs _). Let's break this down into actionable steps based on what you mean by "merge multiple column groups into target columns":

Case 1: Reshape to a long table (each column value becomes a row) #

This is useful if you want to analyze each value in the context of its original prefix, separator type, and sequence number.

Step 1: Clean column names to avoid conflicts #

First, we'll rename columns to explicitly distinguish between the __ and _ separator groups—directly replacing __ with _ would create duplicate column names (e.g., a__1 and a_1 would both become a_1).

import pandas as pd

# Your sample data
df = pd.DataFrame(
    [
        (101, 'a', 'b', 'c', 'd', 'e', 'f', 1, 2, 3, 4, 5, 6, 'aa', 'bb', 'cc', 'dd', 'ee', 'ff'),
        (102,'g', 'h', 'i', 'j', 'k', 'l' , 7, 8, 9, 10, 11, 12, 'gg', 'hh', 'ii', 'jj', 'kk', 'll')
    ],
    columns=['id','a__1', 'a__2', 'a__3', 'a_1', 'a_2', 'a_3','b__1', 'b__2', 'b__3', 'b_1', 'b_2', 'b_3','c__1', 'c__2', 'c__3', 'c_1', 'c_2', 'c_3']
)

# Rename columns to include separator type
new_columns = []
for col in df.columns:
    if col == 'id':
        new_columns.append(col)
    elif '__' in col:
        prefix, seq = col.split('__', 1)
        new_columns.append(f"{prefix}_double_{seq}")
    elif '_' in col:
        prefix, seq = col.split('_', 1)
        new_columns.append(f"{prefix}_single_{seq}")

df.columns = new_columns

Step 2: Reshape with melt and split metadata #

Use melt to convert wide columns to long format, then split the new variable name to extract category, separator type, and sequence:

# Melt the dataframe to long format
melted_df = df.melt(id_vars=['id'], var_name='metadata', value_name='value')

# Split metadata into category, separator type, and sequence
melted_df[['category', 'sep_type', 'seq']] = melted_df['metadata'].str.split('_', n=2, expand=True)

# Drop the original metadata column if you don't need it
melted_df = melted_df.drop('metadata', axis=1)

Sample Output snippet:

Case 2: Merge values into list columns (keep wide format) #

If you want all values from a__* and a_* grouped into a single a column (as a list), follow these steps:

Step 1: Reuse cleaned column names #

We'll use the renamed dataframe from Step 1 of Case 1, then group by category and aggregate values into lists:

# Melt first (same as Case 1)
melted_df = df.melt(id_vars=['id'], var_name='metadata', value_name='value')
melted_df[['category', 'sep_type', 'seq']] = melted_df['metadata'].str.split('_', n=2, expand=True)

# Group by id and category, aggregate values into lists
merged_df = melted_df.groupby(['id', 'category'])['value'].agg(list).unstack().reset_index()

# Rename columns for clarity
merged_df.columns = ['id'] + [f"{col}_values" for col in merged_df.columns if col != 'id']

Sample Output:

Bonus: Auto-detect all categories #

If you have many categories (not just a, b, c), you can auto-detect them instead of hardcoding:

# Auto-extract unique categories from original columns
categories = list(set(
    col.split('__')[0] if '__' in col else col.split('_')[0]
    for col in df.columns
    if col != 'id'
))

print(categories)  # Output: ['a', 'b', 'c']

This works for any number of prefixes, making the code scalable.

内容的提问来源于stack exchange，提问作者Elias K.