R语言中独热编码列转换与长格式数据重构问题

我有一个结构复杂的数据框，定义代码如下：

ID = c(1,2,3)
Sessions = c("2023-11-14 19:01:39+01:00", "2023-11-14 20:01:39+01:00", "2023-11-14 21:01:39+01:00")

P_affect = c(10,20,30) 
N_affect = c(15,30,40)
NMeals = c(0,1,2)

Meal1_Where_Home = c(NA, 1, 0)
Meal1_Where_Restaurant = c(NA, 0, 1)
Meal1_Who_Alone =  c(NA, 1, 0)
Meal1_Who_Friends = c(NA, 0 , 1 )
Meal1_Type_Big_Meal = c(NA, 1, 1)
Meal1_Type_Small_Meal = c(NA, 0, 0)

Meal2_Where_Home = c(NA, NA, 1)
Meal2_Where_Restaurant = c(NA, NA, 0)
Meal2_Who_Alone =  c(NA, NA, 1)
Meal2_Who_Friends = c(NA, NA , 0 )
Meal2_Type_Big_Meal = c(NA, NA, 1)
Meal2_Type_Small_Meal = c(NA, NA, 0)

Meal3_Where_Home = c(NA, NA, NA)
Meal3_Where_Restaurant = c(NA, NA, NA)
Meal3_Who_Alone =  c(NA, NA, NA)
Meal3_Who_Friends = c(NA, NA , NA )
Meal3_Type_Big_Meal = c(NA, NA, NA)
Meal3_Type_Small_Meal = c(NA, NA, NA)


# Create a data frame
df1 <- data.frame(ID, Sessions, P_affect, N_affect, NMeals, Meal1_Where_Home, Meal1_Where_Restaurant,
                            Meal1_Who_Alone, Meal1_Who_Friends, Meal1_Type_Big_Meal, Meal1_Type_Small_Meal,
                            Meal2_Where_Home, Meal2_Where_Restaurant, Meal2_Who_Alone, Meal2_Who_Friends,
                            Meal2_Type_Big_Meal, Meal2_Type_Small_Meal, Meal3_Where_Home, Meal3_Where_Restaurant,
                            Meal3_Who_Alone, Meal3_Who_Friends, Meal3_Type_Big_Meal, Meal3_Type_Small_Meal)


df2 <- data.frame(
  `ID` = c(1,2,3),
  `Context_Family` = c(0,1,0),
  `Context_Friends` = c(1,1,0),
  `Context_Spouse` = c(0,1,0),
  `Context_Alone` = c(0,0,1),
  `Disposition_Stress` = c(0,1,0),
  `Disposition_Melancholic` = c(1,1,0),
  Stress = c(20,24,35)
)

df = merge(df1,df2, by = 'ID')

需要完成两个核心数据处理步骤：

• 将所有以"Context_"或"Disposition_"开头的独热编码列转换为非独热编码形式；
• 按餐次将数据集转换为长格式。

期望输出样例：

ID | Sessions            | P_affect | N_affect | NMeals | MealNumber | MealObs | MealValue | Context | Disposition

1  | 2023-11-14 19:01:39 | 10       | 15       | 0      | Meal1      | Where   | NA        | Friends | Melancholic
    
1  | 2023-11-14 19:01:39 | 10       | 15       | 0      | Meal1      | Who     | NA        | Friends | Melancholic

我尝试了以下代码处理步骤1，但效果不佳，且无法批量处理指定前缀列：

df_modified = df %>%
  pivot_longer(col=starts_with("Context"), names_to="Context", names_prefix="Context_") %>% 
  filter(value==1) %>%
  select(-value) 

处理长格式转换的代码在无独热编码的数据集上有效，但当前数据集下存在问题：

data_long <- df %>%
  pivot_longer(cols = starts_with("Meal"),
               names_to = c("Meal Number", "Value"),
               names_sep = "_",
               values_to = "value")

使用tidyr包的pivot_longer和pivot_wider组合，分两步完成需求：

步骤1：批量转换独热编码列 #

针对Context_和Disposition_前缀的列，分别处理后合并到原数据集：

library(tidyverse)

# 处理Context独热编码
df_context <- df %>%
  select(ID, starts_with("Context_")) %>%
  pivot_longer(cols = starts_with("Context_"), 
               names_to = "Context", 
               names_prefix = "Context_",
               values_to = "context_val") %>%
  filter(context_val == 1) %>%
  select(-context_val)

# 处理Disposition独热编码
df_disposition <- df %>%
  select(ID, starts_with("Disposition_")) %>%
  pivot_longer(cols = starts_with("Disposition_"), 
               names_to = "Disposition", 
               names_prefix = "Disposition_",
               values_to = "dispo_val") %>%
  filter(dispo_val == 1) %>%
  select(-dispo_val)

# 合并处理后的列，保留原数据集非独热编码列
df_processed <- df %>%
  select(-starts_with("Context_"), -starts_with("Disposition_")) %>%
  left_join(df_context, by = "ID") %>%
  left_join(df_disposition, by = "ID")

步骤2：转换为餐次长格式 #

拆分Meal相关列的结构，整理为目标长格式：

df_final <- df_processed %>%
  pivot_longer(cols = starts_with("Meal"),
               names_to = c("MealNumber", "MealObs", "MealValue"),
               names_sep = "_",
               values_to = "IsSelected") %>%
  # 保留选中的选项（值为1）或无餐次的NA行
  filter(IsSelected == 1 | is.na(IsSelected)) %>%
  # 匹配期望输出的列顺序
  select(ID, Sessions, P_affect, N_affect, NMeals, MealNumber, MealObs, MealValue, Context, Disposition) %>%
  # 无餐次的行将MealValue设为NA
  mutate(MealValue = ifelse(is.na(IsSelected), NA, MealValue)) %>%
  select(-IsSelected)

验证结果 #

运行代码后查看前几行：

head(df_final)

输出示例：

ID Sessions                  P_affect N_affect NMeals MealNumber MealObs MealValue Context Disposition
1  1 2023-11-14 19:01:39+01:00       10       15      0      Meal1    Where     NA      Friends  Melancholic
2  1 2023-11-14 19:01:39+01:00       10       15      0      Meal1      Who     NA      Friends  Melancholic
3  1 2023-11-14 19:01:39+01:00       10       15      0      Meal1     Type     NA      Friends  Melancholic
4  2 2023-11-14 20:01:39+01:00       20       30      1      Meal1    Where     Home      Family      Stress
5  2 2023-11-14 20:01:39+01:00       20       30      1      Meal1      Who     Alone      Family      Stress
6  2 2023-11-14 20:01:39+01:00       20       30      1      Meal1     Type Big_Meal      Family      Stress

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