如何利用Pandas的stack方法处理含#/#后缀列的DataFrame并新增x列？

Solution for Transforming df2 to Target DataFrame #

First, let's use a concrete example to make this clear. Suppose your df2 looks like this:

import pandas as pd

df2 = pd.DataFrame({
    'A': ['A1', 'A1', 'A2', 'A2'],
    'D': [9, 10, 19, 20],
    'B#1/#2': [5, 6, 15, 16],
    'C#1/#3': [7, 8, 17, 18],
    'B#2/#4': [11, 12, 21, 22],
    'C#2/#5': [13, 14, 23, 24]
})

Here's how to use regex and stacking to get your target DataFrame with the x column:

1. Extract x values from column names
   We'll use regex to pull out the first number in the #/# suffix, then restructure the columns into a multi-index to separate ID columns (like A and D) from value columns (like B#1/#2).

import re

# Define regex to capture base column name and x value
pattern = r'(.*)#(\d+)/\d+'

# Create multi-index columns
new_columns = []
for col in df2.columns:
    match = re.match(pattern, col)
    if match:
        base_name = match.group(1)
        x_val = match.group(2)
        new_columns.append(('value', base_name, x_val))
    else:
        new_columns.append(('id', col, ''))

df2.columns = pd.MultiIndex.from_tuples(new_columns, names=['type', 'name', 'x'])

2. Split and stack to get long format
   Separate the ID columns from the value columns, then stack the x level to create the new x column, and merge everything back together:

# Split into ID and value DataFrames
df_id = df2['id'].droplevel(['type', 'x'], axis=1)
df_value = df2['value'].droplevel('type', axis=1)

# Stack the x level to reshape
df_stacked = df_value.stack(level='x').reset_index(level='x')

# Combine ID columns with stacked values
target_df = df_id.join(df_stacked).reset_index(drop=True)

# Convert x to integer (optional but recommended)
target_df['x'] = target_df['x'].astype(int)

Your resulting target_df will look like this:

Optimal Approach Using df1 Directly #

If df1 is the raw data that was used to create df2, you can skip the regex extraction entirely if df1 already contains the x value as a separate column. For example, if df1 is in a long format like the target above, then your work is already done—no need to process df2 at all.

If df1 is in a wide format with x embedded in column names (similar to df2 but without the extra /# suffix), you can apply the same regex-based column restructuring we used for df2 directly to df1 to get the target without going through the intermediate df2 step. This is more efficient and avoids potential errors from parsing complex column names twice.

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