如何重塑具有重复列类型的DataFrame？

Hey there! Let's walk through how to reshape your nf DataFrame into a tidy, long-format structure. From your description, you've got 50 columns where most are grouped in sets of 5 (A-E, A.1-E.1, etc.) plus a one-off F.2 column, and some rows have missing values in the later columns. Here are two straightforward approaches using pandas:

Method 1: Use pd.wide_to_long (Best for Regular Grouped Columns) #

This function is built exactly for cases where you have repeated column names with numeric suffixes. First, we'll standardize the column names to make them compatible with the function, then reshape.

Step 1: Standardize Column Names #

The first group of columns (A, B, C, D, E) don't have a suffix, so we'll add .0 to match the pattern of the other groups (like A.1, B.1):

import pandas as pd

# Rename columns without suffix to add .0
nf_renamed = nf.rename(columns={col: f"{col}.0" for col in ['A', 'B', 'C', 'D', 'E']})

Step 2: Reshape with wide_to_long #

Now we can convert the wide table to a long table, creating a group column that tracks which original column group each row comes from:

# Reshape the core A-E columns
long_df = pd.wide_to_long(
    nf_renamed,
    stubnames=['A', 'B', 'C', 'D', 'E'],  # The base column names
    i=nf_renamed.index,  # Use original row index as the identifier
    j='group',  # Name of the new column for group numbers
    sep='.',  # Separator between base name and group number
    suffix=r'\d+'  # Regex to match numeric suffixes
)

Step 3: Handle the F.2 Column #

Since F.2 is a standalone column tied to group=2, we'll convert it to match the long format and merge it in:

# Convert F.2 to a compatible format
f_col = nf_renamed['F.2'].rename('F').to_frame().assign(group=2)

# Merge with the reshaped DataFrame
final_df = long_df.reset_index().merge(f_col, on=['index', 'group'], how='left').set_index(['index', 'group'])

Method 2: MultiIndex + Stack (More Flexible for Mixed Columns) #

If you have more irregular columns beyond just F.2, this method uses a MultiIndex to organize columns before reshaping, which works for all column types:

Step 1: Split Columns into a MultiIndex #

We'll split each column name into its base name (A, B, F, etc.) and group number (0, 1, 2, etc.), then turn this into a two-level column index:

# Split column names into base name and group
col_parts = pd.Series(nf.columns).str.split('.', expand=True)
# Fill group number for columns without a suffix (e.g., A → group 0)
col_parts[1] = col_parts[1].fillna('0')
# Create MultiIndex for columns
nf.columns = pd.MultiIndex.from_arrays(col_parts.values.T, names=['feature', 'group'])

Step 2: Stack to Long Format #

Now we can "stack" the group level of the column index into rows, which automatically handles all columns (including F.2):

# Stack the group level into rows
long_df = nf.stack(level='group').reset_index().rename(columns={'level_0': 'original_row'})

Example Output Snippet #

Using your sample data, the final long-format DataFrame will include rows like this (for the first original row):

Missing values from your original rows will be preserved as NaN in the final DataFrame, which pandas handles seamlessly.

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