Pandas DataFrame行转列：将日期行值设为列标题重塑表格

Here's a straightforward way to reshape your DataFrame to the desired format:

import pandas as pd

# Sample input DataFrame (matches your structure)
data = {
    'ID': ['ABC', 'DEF', 'GHI', 'ABC', 'DEF', 'GHI', 'ABC', 'DEF', 'GHI'],
    'DAY': ['Mon', 'Mon', 'Mon', 'Tue', 'Tue', 'Tue', 'Wed', 'Wed', 'Wed'],
    'Col1': ['A']*9,
    'ColA': ['A']*9,
    'ColB': [123, 456, 789, 123, 456, 789, 123, 456, 789],
    'ColC': ['']*9
}
df = pd.DataFrame(data).set_index('ID')

# Step 1: Reset index to make 'ID' a regular column (easier for pivoting)
df_reset = df.reset_index()

# Step 2: Drop empty/unnecessary columns (ColC is empty, Col1/ColA don't affect the output)
df_clean = df_reset.drop(['Col1', 'ColA', 'ColC'], axis=1)

# Step 3: Pivot to reshape - ID as index, DAY as columns, ColB as values
result = df_clean.pivot(index='ID', columns='DAY', values='ColB')

# Optional: Remove the redundant 'DAY' label from the columns header
result.columns.name = None

print(result)

Output: #

Mon  Tue  Wed
ID                
ABC  123  123  123
DEF  456  456  456
GHI  789  789  789

Key Explanations: #

• Resetting the Index: Since ID was your original index, converting it back to a column makes it easier to use as the grouping key in pivot().
• Cleaning Columns: We drop ColC (empty cells) and the constant columns Col1/ColA because they don't add value to your desired output.
• Pivoting: The pivot() function rearranges the data by:
  • index='ID': Keeps each unique ID as a row.
  • columns='DAY': Turns each unique date into a column header.
  • values='ColB': Fills the cells with the corresponding values from ColB.
• Optional Cleanup: Removing columns.name gets rid of the extra "DAY" label above the date columns for a cleaner look.

Handling Missing Values (If Needed): #

If some IDs are missing values for certain dates, you can fill them with a default value (like 0) using fillna():

result = result.fillna(0)

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