Convert long to wide with multiple rows with the same value in R-CodePudding

I'm trying to convert this data from long to wide, but I am getting errors. I believe it is because my id/time variables are not all unique (see how for ID 98 there are 2 "blue" observations). I want to just add a 2, 3 etc. for these, like the sample output below.

ID  color.    height   weight.  BMI
45.  red.      15       100.    19
33.  blue.     19       130.    17
33.  green.    25       120.    28
67.  red.      19.      134.    31
98.  blue.     26.      112.    19
98.  blue.     12.      116.    26
98.  red.      14.      88.     25


ID.   red_height  red_weight  red_BMI  blue_height  blue_weight  blue_BMI  blue2_height blue2_weight  blue2_BMI  green_height  green_weight  green_BMI 
45     15         100         19        NA           NA          NA         NA            NA             NA           NA            NA          NA
33.    NA          NA         NA       19            130         17         NA            NA             NA           25            120         28      
67     19.        134.        31       NA            NA          NA         NA            NA             NA           NA            NA          NA          
98.    14.        88.         25.      26            112         19         12            116            26.          NA            NA          NA

CodePudding user response：

We may create a sequence by group and then use pivot_wider

library(dplyr)
library(stringr)
df1 %>%
    group_by(ID, color) %>% 
    mutate(color = case_when(row_number() > 1 ~ 
     str_c(color, row_number()), TRUE ~ color)) %>% 
    ungroup %>% 
    pivot_wider(names_from = color, values_from = c(height, weight, BMI))

-output

# A tibble: 4 × 13
     ID height_red height_blue height_green height_blue2 weight_red weight_blue weight_green weight_blue2 BMI_red BMI_blue BMI_green BMI_blue2
  <dbl>      <dbl>       <dbl>        <dbl>        <dbl>      <dbl>       <dbl>        <dbl>        <dbl>   <int>    <int>     <int>     <int>
1    45         15          NA           NA           NA        100          NA           NA           NA      19       NA        NA        NA
2    33         NA          19           25           NA         NA         130          120           NA      NA       17        28        NA
3    67         19          NA           NA           NA        134          NA           NA           NA      31       NA        NA        NA
4    98         14          26           NA           12         88         112           NA          116      25       19        NA        26

data

df1 <- structure(list(ID = c(45, 33, 33, 67, 98, 98, 98), color = c("red", 
"blue", "green", "red", "blue", "blue", "red"), height = c(15, 
19, 25, 19, 26, 12, 14), weight = c(100, 130, 120, 134, 112, 
116, 88), BMI = c(19L, 17L, 28L, 31L, 19L, 26L, 25L)),
 class = "data.frame", row.names = c(NA, 
-7L))