如何将指定Pandas DataFrame转换为二维宽表形式？

To transform your DataFrame into the desired wide format, follow these steps:

1. Fill empty product values: The empty strings in the Product column belong to the previous non-empty product (e.g., rows after "Car" are part of the "Car" group). Use forward fill to propagate these values down.
2. Pivot the data: Convert the long-format data into wide format using pivot(), with Product as rows, Name as columns, and Price as values. Fill missing entries with 0.
3. Reorder columns: Adjust the column order to match your desired output.
4. Cast to integers: Ensure numeric columns are integers instead of floats.

Complete Code #

import pandas as pd

input = {"Product": ["Car", "", "", "House", "", "", ""], 
         "Name": ["Wheel", "Glass", "Seat", "Glass", "Roof", "Door", "Kitchen"],
         "Price": [5, 3, 4, 2, 6, 4, 12]}

df_input = pd.DataFrame(input)

# Fill empty Product values with the last valid product
df_input['Product'] = df_input['Product'].replace('', pd.NA).ffill()

# Pivot to wide format, filling missing values with 0
df_pivoted = df_input.pivot(index='Product', columns='Name', values='Price', fill_value=0)

# Reset index to make Product a column again
df_output = df_pivoted.reset_index()

# Reorder columns to match desired output
desired_columns = ['Product', 'Wheel', 'Glass', 'Seat', 'Roof', 'Door', 'Kitchen']
df_output = df_output[desired_columns]

# Convert numeric columns to integers
df_output[desired_columns[1:]] = df_output[desired_columns[1:]].astype(int)

# Verify the result
print(df_output)

Output #

Product  Wheel  Glass  Seat  Roof  Door  Kitchen
0      Car      5      3     4     0     0        0
1     House      0      2     0     6     4       12

This matches exactly the structure you requested.

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