Python3.6 Pandas0.21.1：如何将多行多列数据合并为单行多列

Hey there! I get that pivoting multiple temperature columns where each device_id has an unknown number of rows can be tricky—single-column solutions just don't cut it here. Let's walk through a reliable approach that works perfectly with your Python 3.6 and Pandas 0.21.1 setup.

Step 1: Add a Grouped Row Index #

First, we'll assign a sequential number to each row within the same device_id group. This helps us tell apart the first, second, third, etc., entries for each device:

df_raw['row_num'] = df_raw.groupby('device_id').cumcount()

This creates a new row_num column where values start at 0 for the first row of each device_id, 1 for the second, and so on.

Step 2: Reshape Data to Long Format #

Next, we'll convert the wide-format temperature columns into a long format using melt. This lets us combine temperature column names with our row_num to create unique labels later:

df_melted = df_raw.melt(
    id_vars=['device_id', 'row_num'],
    value_vars=['temp_a', 'temp_b', 'temp_c'],
    var_name='temp_col',
    value_name='temp_value'
)

Now we have rows like (device_id=1, row_num=0, temp_col='temp_a', temp_value=25.5) instead of separate columns for each temperature reading.

Step 3: Create Unique Column Names #

We'll combine the original temperature column name with row_num to make distinct labels. For the first row (row_num=0), we keep the original name; for subsequent rows, we append _row_num:

df_melted['new_col'] = df_melted.apply(
    lambda x: x['temp_col'] if x['row_num'] == 0 else f"{x['temp_col']}_{x['row_num']}",
    axis=1
)

This gives us column names like temp_a, temp_a_1, temp_b_2, etc.—exactly the naming convention you need.

Step 4: Pivot Back to Wide Format #

Finally, we'll pivot the data back to a wide format where each device_id occupies a single row, with all our unique temperature columns:

df_except2 = df_melted.pivot(
    index='device_id',
    columns='new_col',
    values='temp_value'
).reset_index()

# Clean up the extra column header
df_except2.columns.name = None

Full Example with Sample Data #

Let's test this with a sample df_raw to see it in action:

import pandas as pd

# Sample input data
df_raw = pd.DataFrame({
    'device_id': [1, 1, 2, 2, 2],
    'temp_a': [25.5, 26.1, 24.8, 25.3, 25.7],
    'temp_b': [30.2, 30.5, 29.9, 30.1, 30.3],
    'temp_c': [22.0, 22.3, 21.5, 21.8, 22.1]
})

# Run the full transformation
df_raw['row_num'] = df_raw.groupby('device_id').cumcount()
df_melted = df_raw.melt(id_vars=['device_id', 'row_num'], value_vars=['temp_a', 'temp_b', 'temp_c'], var_name='temp_col', value_name='temp_value')
df_melted['new_col'] = df_melted.apply(lambda x: x['temp_col'] if x['row_num'] == 0 else f"{x['temp_col']}_{x['row_num']}", axis=1)
df_except2 = df_melted.pivot(index='device_id', columns='new_col', values='temp_value').reset_index()
df_except2.columns.name = None

print(df_except2)

The output will be exactly the format you're looking for:

device_id  temp_a  temp_a_1  temp_a_2  temp_b  temp_b_1  temp_b_2  temp_c  temp_c_1  temp_c_2
0          1    25.5      26.1       NaN    30.2      30.5       NaN    22.0      22.3       NaN
1          2    24.8      25.3      25.7    29.9      30.1      30.3    21.5      21.8      22.1

This approach automatically handles any number of rows per device_id and is fully compatible with Pandas 0.21.1.

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