如何将DataFrame行数据转换为不同列？已尝试Pivot Table未成功

Using Python Pandas #

You can easily reshape the data using pandas to match your desired format. Here's the step-by-step code:

import pandas as pd

# Load your original data
data = [
    [1, 10, 210, 220],
    [2, 10, 245, 255],
    [3, 10, 275, 285],
    [4, 10, 295, 305],
    [1, 20, 215, 225],
    [2, 20, 250, 260],
    [3, 20, 280, 290],
    [4, 20, 300, 310]
]

df = pd.DataFrame(data, columns=['depth', 'density', 'tb10', 'tb18'])

# Reshape by unstacking density values
df.set_index(['depth', 'density'], inplace=True)
df_unstacked = df.unstack(level='density')

# Flatten multi-level column names
df_unstacked.columns = [f'den{density}_{tb_col}' for tb_col, density in df_unstacked.columns]

# Reset index and add density column (as per your desired output)
result = df_unstacked.reset_index()
result['density'] = 10

# Reorder columns to match your target format
result = result[['depth', 'density', 'den10_tb10', 'den10_tb18', 'den20_tb10', 'den20_tb18']]

# Output as markdown table
print(result.to_markdown(index=False))

Output: #

Fixing the Excel Pivot Table Approach #

If you want to use Excel's Pivot Table instead, follow these steps:

1. Select your original data range and insert a Pivot Table.
2. In the PivotTable Fields pane:
   • Drag depth to the Rows area.
   • Drag density to the Columns area.
   • Drag both tb10 and tb18 to the Values area (you'll see two entries: "Sum of tb10" and "Sum of tb18").
3. The pivot table will now have a multi-level column header: the top level is tb10/tb18, and the second level is 10/20.
4. Rename the columns to match your desired format (e.g., "den10_tb10" instead of "Sum of tb10" for density 10).
5. If you need the density column with value 10, add it manually next to the depth column (since it's redundant here, but matches your target layout).

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