Reshape the dataframe using unique ID and transpose the row to column based on the corresponding value

user_15085600 0
Viewed 187

I have a dataframe like the following

Sample ID Parameter Value Unit
1 apple 30 g
1 pear 15 lb
1 organge 20 kg
2 apple 2 g
2 pear 3 lb
2 orange 10 kg
3 apple 15 g
3 pear 23 lb
3 orange 12 kg

I would like to transpose it based on the sample ID and put the corresponding value in the column

Sample ID apple_value_unit(g) pear_value_unit(lb) orange_value_unit(kg)
1 30 15 20
2 2 3 10
3 15 23 12

Is there any ways that I can transpose and match the value? I tried the cast(), but it does not work on mapping the value to the corresponding parameter.

data-cleaning transpose data-structures r
1 Answers

We can concatenate the 'Parameter' and 'Unit' columns to a single column before doing the reshape to 'wide' format with pivot_wider

library(dplyr)
library(stringr)
library(tidyr)
df1 %>% 
   mutate(Parameter = sprintf('%s_value_unit(%s)', Parameter, Unit),
        .keep = "unused") %>%
   pivot_wider(names_from = Parameter, values_from = Value)

-output

# A tibble: 3 × 4
  SampleID `apple_value_unit(g)` `pear_value_unit(lb)` `orange_value_unit(kg)`
     <int>                 <int>                 <int>                   <int>
1        1                    30                    15                      20
2        2                     2                     3                      10
3        3                    15                    23                      12

data

df1 <- structure(list(SampleID = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L
), Parameter = c("apple", "pear", "orange", "apple", "pear", 
"orange", "apple", "pear", "orange"), Value = c(30L, 15L, 20L, 
2L, 3L, 10L, 15L, 23L, 12L), Unit = c("g", "lb", "kg", "g", "lb", 
"kg", "g", "lb", "kg")), class = "data.frame", row.names = c(NA, 
-9L))
user_37322710
Related