如何在Pandas中基于首列将行式键值对转置为列式结构化数据？

Hey there! I see you're trying to reshape your Pandas DataFrame from a "row-wise attribute-value" format into a proper tabular structure, and the transpose method didn't work out—totally get why that happened, let's walk through how to fix this.

First, let's recap your scenario #

Your original DataFrame looks like this:

import pandas as pd
df = pd.DataFrame({
    'Column_1': ['Name', 'Age', 'Gender', 'Name', 'Age', 'Gender'],
    'Column_2': ['Xxxx', 28, 'M', 'yyyy', 26, 'F']
})

Which prints out as:

And you want to turn it into this clean, structured table:

The df.T method didn't work because it just flips rows and columns based on the existing index, which leaves you with messy, duplicated column names instead of the grouped rows you need.

Here's the straightforward solution #

We'll use grouping + pivot to reorganize the data. The key is recognizing that every 3 rows belong to one person, so we first create a group ID to bundle those rows together.

Step 1: Add a group identifier

Since each person's data takes up 3 rows, we can generate a group ID using integer division on the DataFrame's index:

df['group_id'] = df.index // 3

This assigns 0 to the first 3 rows, 1 to the next 3, and so on—perfect for grouping each person's attributes.

Step 2: Reshape with pivot

Now we can use pivot to turn the attribute names (from Column_1) into columns, their corresponding values (from Column_2) into cell values, and use the group ID as the row index:

result = df.pivot(index='group_id', columns='Column_1', values='Column_2').reset_index(drop=True)

We add reset_index(drop=True) to get rid of the temporary group ID column, and optionally clear the column name label for a cleaner output:

result.columns.name = None

Full working code #

import pandas as pd

# Original DataFrame
df = pd.DataFrame({
    'Column_1': ['Name', 'Age', 'Gender', 'Name', 'Age', 'Gender'],
    'Column_2': ['Xxxx', 28, 'M', 'yyyy', 26, 'F']
})

# Create group IDs for each person's data
df['group_id'] = df.index // 3

# Reshape the data
result = df.pivot(index='group_id', columns='Column_1', values='Column_2').reset_index(drop=True)
result.columns.name = None

print(result)

Output you'll get #

Name Age Gender
0  Xxxx  28      M
1  yyyy  26      F

Alternative method (using groupby) #

If you prefer, you can also use groupby with a lambda function to achieve the same result:

result = df.groupby(df.index // 3).apply(
    lambda x: pd.Series(x['Column_2'].values, index=x['Column_1'].values)
).reset_index(drop=True)

This works because we group by the same 3-row chunks, then convert each group's attribute-value pairs into a Series with the attribute names as the index.

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