Convert R to wide format with arrangement depending on value in column

user_13015230 0
Viewed 19

I have got a table containing data from various samples ("sample1" etc) with which several types measurement (A to C) were made. Every measurement gave 3 values: concentration, maximum and minimum.

my.sample <- c("sample1", "sample1", "sample2", "sample2", "sample3")
type <- c("A", "B", "A", "C", "C")
concentration <- c(12, 5, 7, 10, 14)
max <- c(13, 6, 7, 11, 15)
min <- c(11, 4, 6, 9, 13)

mydata <- data.frame(my.sample, type, concentration, max, min)

> mydata
  my.sample type concentration max min
1   sample1    A            12  13  11
2   sample1    B             5   6   4
3   sample2    A             7   7   6
4   sample2    C            10  11   9
5   sample3    C            14  15  13

I'd like to convert this data to a new table where I only have one row per sample. This means creating 3 columns (concentration, max, min) for every measurement type, with the type of measurement indicated in the column. Missing values should be defined as NA. Here's an example of the result I'd like to obtain:

A_concentration <- c(12, 7, NA)
A_max <- c(13, 7, NA)
A_min <- c(11, 6, NA)

B_concentration <- c(5, NA, NA)
B_max <- c(6, NA, NA)
B_min <- c(4, NA, NA)

C_concentration <- c(NA, 10, 14)
C_max <- c(NA, 11, 15)
C_min <- c(NA, 9, 13)

mydata.new <- data.frame(my.sample.new, A_concentration, A_max, A_min, B_concentration, B_max, B_min, C_concentration, C_max, C_min)

> mydata.new
  my.sample.new A_concentration A_max A_min B_concentration B_max B_min
1       sample1              12    13    11               5     6     4
2       sample2               7     7     6              NA    NA    NA
3       sample3              NA    NA    NA              NA    NA    NA
  C_concentration C_max C_min
1              NA    NA    NA
2              10    11     9
3              14    15    13

Is there a method to widen data based on a condition and include a value (here: from type ) in the column name? I have got many more types in my real dataset, so it should ideally be generalisable.

dplyr pivot r
1 Answers

This works:

library(dplyr)

mydata %>% 
  pivot_wider(id_cols = my.sample, names_from = type, values_from = c(concentration, max, min), names_glue = "{type}_{.value}") %>% 
  select(my.sample, starts_with("A"), starts_with("B"), starts_with("C"))

This gives us:

# A tibble: 3 x 10
  my.sample A_concentration B_concentration C_concentration A_max B_max C_max A_min B_min C_min
  <chr>               <dbl>           <dbl>           <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 sample1                12               5              NA    13     6    NA    11     4    NA
2 sample2                 7              NA              10     7    NA    11     6    NA     9
3 sample3                NA              NA              14    NA    NA    15    NA    NA    13
user_129679380
Related