Transforming multiple hot-encoded columns and converting to long-format

Question

I have a quite complex data frame structure:

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')

What I would like to is essentially two steps:

1. convert all the columns of a specific type (that start with "Context_" or "Disposition_") into their not hot-encoded form
2. make the data set long format by meal number

Desired output:

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

I tried for step1:

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

But this did not work very well, and also I would like an approach that just asks for column names and does the hot-encoding transformation for all of them, instead taking one by one. And for the long formatting:

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

Which works, but on a data set without hot-encoded values. I included a larger data frame just to check if the code works properly for all cases.

Answer 1

Your df dataset isn´t tidy because there´s more than one value per variable/observation. For ID == 2, Context is "Family", "Friends" and "Spouse". You´ll get list-columns:

library(dplyr)

# --------
df_modified <- select(df, ID, starts_with("Context"))

df_modified

  ID Context_Family Context_Friends Context_Spouse Context_Alone
1  1              0               1              0             0
2  2              1               1              1             0
3  3              0               0              0             1

Pivoting:

df_modified <- pivot_longer(df_modified, -ID, names_to = "column", values_to = "value")

# A tibble: 12 × 3
      ID column          value
   <dbl> <chr>           <dbl>
 1     1 Context_Family      0
 2     1 Context_Friends     1
 3     1 Context_Spouse      0
 4     1 Context_Alone       0
 5     2 Context_Family      1
 6     2 Context_Friends     1
 7     2 Context_Spouse      1
 8     2 Context_Alone       0
 9     3 Context_Family      0
10     3 Context_Friends     0
11     3 Context_Spouse      0
12     3 Context_Alone       1

#
df_modified <- mutate(
  df_modified, 
  value = if_else(value == 1, str_extract(column, "(?<=_).*$"), NA_character_), 
  column = str_extract(column, "^.*(?=_)"))

# A tibble: 12 × 3
      ID column  value  
   <dbl> <chr>   <chr>  
 1     1 Context NA     
 2     1 Context Friends
 3     1 Context NA     
 4     1 Context NA     
 5     2 Context Family 
 6     2 Context Friends
 7     2 Context Spouse 
 8     2 Context NA     
 9     3 Context NA     
10     3 Context NA     
11     3 Context NA     
12     3 Context Alone

#
df_modified <- filter(df_modified, !is.na(value))

# A tibble: 5 × 3
     ID column  value  
  <dbl> <chr>   <chr>  
1     1 Context Friends
2     2 Context Family 
3     2 Context Friends
4     2 Context Spouse 
5     3 Context Alone 

#
df_modified <- pivot_wider(df_modified, names_from = column, values_from = value)

Warning message:
Values from `value` are not uniquely identified; output will contain list-cols.
• Use `values_fn = list` to suppress this warning.
• Use `values_fn = {summary_fun}` to summarise duplicates.
• Use the following dplyr code to identify duplicates.
  {data} %>%
  dplyr::group_by(ID, column) %>%
  dplyr::summarise(n = dplyr::n(), .groups = "drop") %>%
  dplyr::filter(n > 1L) 
> df_modified
# A tibble: 3 × 2
     ID Context  
  <dbl> <list>   
1     1 <chr [1]>
2     2 <chr [3]>
3     3 <chr [1]>

EDIT: A alternative for list-columns is to summarise before the pivot_wider step:

summarise(
  df_modified, .by = c(ID, column),
  value = str_flatten_comma(value, na.rm = TRUE))

# A tibble: 3 × 3
   ID column  value                  
<dbl> <chr>   <chr>                  
  1     1 Context Friends                
  2     2 Context Family, Friends, Spouse
  3     3 Context Alone 

#
df_modified <- pivot_wider(df_modified, names_from = column, values_from = value)

# A tibble: 3 × 2
     ID Context                
  <dbl> <chr>                  
1     1 Friends                
2     2 Family, Friends, Spouse
3     3 Alone