如何对Pandas中三个DataFrame的拼接结果进行扁平化处理？

Got it, let's tackle this problem step by step. You have three DataFrames with identical column structures but suffixes like _1, _2, _3, and you want to flatten the combined result into a single DataFrame with clean, unified column names (e.g., type, subject_id, first_name) instead of the suffixed versions.

First, let's complete the sample code you provided (since df_c was cut off):

import pandas as pd

# df_a
raw_data = { 
    'type_1': [1, 1, 0, 0, 1], 
    'subject_id_1': ['1', '2', '3', '4', '5'], 
    'first_name_1': ['Alex', 'Amy', 'Allen', 'Alice', 'Ayoung']
}
df_a = pd.DataFrame(raw_data, columns = ['type_1', 'subject_id_1', 'first_name_1'])

# df_b
raw_datab = { 
    'type_2': [1, 1, 0, 0, 0], 
    'subject_id_2': ['4', '5', '6', '7', '8'], 
    'first_name_2': ['Billy', 'Brian', 'Bran', 'Bryce', 'Betty']
}
df_b = pd.DataFrame(raw_datab, columns = ['type_2', 'subject_id_2', 'first_name_2'])

# df_c (added for complete example)
raw_datac = {
    'type_3': [0, 1, 1, 0, 1],
    'subject_id_3': ['9', '10', '11', '12', '13'],
    'first_name_3': ['Charlie', 'Chloe', 'Chris', 'Cindy', 'Cody']
}
df_c = pd.DataFrame(raw_datac, columns=['type_3', 'subject_id_3', 'first_name_3'])

Option 1: Rename Columns First, Then Concatenate (Most Efficient) #

This is the best approach if you haven't concatenated the DataFrames yet. We'll strip the numeric suffixes from each DataFrame's columns first, then stack them vertically.

Step 1: Create a helper function to clean column names #

We'll use a regex to remove the _X suffix at the end of each column name:

def clean_column_names(df):
    # Replace any "_[number]" suffix with an empty string
    df.columns = df.columns.str.replace(r'_\d+$', '', regex=True)
    return df

Step 2: Clean each DataFrame and concatenate #

# Apply the cleaning function to all DataFrames
df_a_clean = clean_column_names(df_a)
df_b_clean = clean_column_names(df_b)
df_c_clean = clean_column_names(df_c)

# Vertically concatenate the cleaned DataFrames
flattened_df = pd.concat([df_a_clean, df_b_clean, df_c_clean], ignore_index=True)

Option 2: Flatten an Already Concatenated Horizontal DataFrame #

If you already concatenated the DataFrames horizontally (side-by-side) and need to flatten them, this method will merge columns with the same base name (e.g., type_1, type_2, type_3 into one type column):

Step 1: First concatenate horizontally (if you haven't) #

# Concatenate the original DataFrames side-by-side
combined_horizontal_df = pd.concat([df_a, df_b, df_c], axis=1)

Step 2: Flatten into unified columns #

flattened_df = pd.DataFrame()

# Iterate over each base column name
for base_col in ['type', 'subject_id', 'first_name']:
    # Collect all columns matching the base name pattern
    matching_cols = [col for col in combined_horizontal_df.columns if col.startswith(base_col)]
    # Merge columns, taking the first non-null value per row (since each row only has data in one suffix column)
    flattened_df[base_col] = combined_horizontal_df[matching_cols].bfill(axis=1).iloc[:, 0]

# Reset index to clean up
flattened_df = flattened_df.reset_index(drop=True)

Sample Output #

The resulting flattened_df will look like this:

type subject_id first_name
0     1          1       Alex
1     1          2        Amy
2     0          3      Allen
3     0          4      Alice
4     1          5     Ayoung
5     1          4      Billy
6     1          5      Brian
7     0          6       Bran
8     0          7      Bryce
9     0          8       Betty
10    0          9    Charlie
11    1         10       Chloe
12    1         11       Chris
13    0         12       Cindy
14    1         13        Cody

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