如何基于2个分类变量与1个数值变量创建新变量并重构数据表

Got it, let's figure out how to reshape your meal data into the wide format you need! You want each ID to have a single row, with columns like Cereal_BF or Meat_Lunch that hold the corresponding Amount values. Here are practical solutions using two popular data tools:

Pandas makes reshaping data like this super easy with the pivot_table function. We'll first set up your sample data, then reshape it and clean up the column names to match your desired format.

import pandas as pd

# Load your sample data into a DataFrame
data = [
    [1, "Lunch", "Meat", 50],
    [1, "Lunch", "Potato", 10],
    [1, "Dinner", "Fish", 105],
    [1, "Dinner", "Rice", 100],
    [1, "Dinner", "Pulses", 50],
    [2, "BF", "Cereal", 100],
    [2, "BF", "Milk", 200],
    [2, "Lunch", "Rice", 200],
    [2, "Lunch", "Chicken", 150],
    [2, "Lunch", "Veg", 100],
    [2, "Dinner", "Pasta", 200],
    [2, "Dinner", "Meat", 200],
    [2, "Dinner", "Tomato", 50],
    [2, "Dinner", "Cheese", 10]
]

df = pd.DataFrame(data, columns=["ID", "Meal", "Food", "Amount"])

# Reshape to wide format
wide_df = df.pivot_table(
    index="ID",
    columns=["Food", "Meal"],
    values="Amount",
    fill_value=0  # Replace missing combinations with 0; use NaN if you prefer
).reset_index()

# Rename columns to the Food_Meal format you want
wide_df.columns = ["ID"] + ["_".join(col_pair) for col_pair in wide_df.columns[1:]]

# Check the result
print(wide_df)

What this does: #

• pivot_table groups the data by ID, then creates columns for every unique Food-Meal pair, filling in the corresponding Amount values.
• fill_value=0 ensures any Food-Meal combination that an ID doesn't have gets a 0 instead of a missing value (adjust this to NaN if you'd rather keep blanks).
• The final line renames the multi-level columns to your desired Food_Meal format.

If you're working in R, the tidyr package's pivot_wider function is perfect for this task. We'll use dplyr for pipe syntax to keep the code clean.

library(tidyr)
library(dplyr)

# Load your sample data into a data frame
data <- data.frame(
  ID = c(1,1,1,1,1,2,2,2,2,2,2,2,2,2),
  Meal = c("Lunch","Lunch","Dinner","Dinner","Dinner","BF","BF","Lunch","Lunch","Lunch","Dinner","Dinner","Dinner","Dinner"),
  Food = c("Meat","Potato","Fish","Rice","Pulses","Cereal","Milk","Rice","Chicken","Veg","Pasta","Meat","Tomato","Cheese"),
  Amount = c(50,10,105,100,50,100,200,200,150,100,200,200,50,10)
)

# Reshape to wide format
wide_data <- data %>%
  pivot_wider(
    id_cols = ID,
    names_from = c(Food, Meal),
    values_from = Amount,
    values_fill = 0  # Fill missing combinations with 0; use NA if preferred
  )

# View the result
print(wide_data)

What this does: #

• pivot_wider uses ID as the row identifier, creates columns from every Food-Meal pair, and pulls in the Amount values.
• values_fill = 0 handles missing combinations (swap with NA if you don't want to fill with zeros).
• The column names automatically default to Food_Meal format, which is exactly what you need!

Quick note for edge cases: #

If your dataset has duplicate ID-Food-Meal entries (same ID, same meal, same food with multiple amounts), both solutions will automatically aggregate the values (Pandas uses mean by default, R uses sum). To change this, specify an aggregation function:

• In Pandas, add aggfunc='sum' to pivot_table
• In R, add values_fn = sum to pivot_wider

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