How to manipulate paired dataset in R?

user_7988767 0
Viewed 39

My data looks something like this (a simplified version):

df <- read.table(text="cohort,med1,med2,med3,meq1,meq2,meq3
a,drugA,drugB,NA,4.3,1.9,NA
b,drugB,drugC,drugA,2.1,2.0,4.5
a,drugC,NA,NA,2.0,NA,NA
a,drugA,drugB,NA,5.5,3.6,NA
b,drugB,drugA,drugC,4.9,4.1,4.1", sep=",", header=TRUE)

## > df
##   cohort  med1  med2  med3 meq1 meq2 meq3
## 1      a drugA drugB  <NA>  4.3  1.9   NA
## 2      b drugB drugC drugA  2.1  2.0  4.5
## 3      a drugC  <NA>  <NA>  2.0   NA   NA
## 4      a drugA drugB  <NA>  5.5  3.6   NA
## 5      b drugB drugA drugC  4.9  4.1  4.1

Each of the med values is paired with each of the meq values (e.g. the value of meq1 is the value for that instance of med1). How can I restructure the data to be grouped by med and by cohort and get both the count of med and average value of meq?

For example:

cohort  med    meq
a       drugA  4.9
a       drugB  2.75
a       drugC  2.0
b       drugA  4.3
b       drugB  4.5
b       drugC  3.05
data-manipulation r
2 Answers

Although you could do this using tidyr::pivot_longer, you can reshape your data fairly easily in base R like this:

f   <- function(df, index) setNames(df[,index], c("cohort", "med", "meq"))
df2 <- rbind(f(df, c(1, 2, 5)), f(df, c(1, 3, 6)), f(df, c(1, 4, 7)))
df2 <- df2[!is.na(df2$med),]
df2
#>    cohort   med meq
#> 1       a drugA 4.3
#> 2       b drugB 2.1
#> 3       a drugC 2.0
#> 4       a drugA 5.5
#> 5       b drugB 4.9
#> 6       a drugB 1.9
#> 7       b drugC 2.0
#> 9       a drugB 3.6
#> 10      b drugA 4.1
#> 12      b drugA 4.5
#> 15      b drugC 4.1

You can then get a count of drugs like this:

table(df2$med)

#> drugA drugB drugC 
#>     4     4     3

And a mean of meq like this:

mean(df2$meq)
#> [1] 3.545455

tapply(df2$meq, df2$med, mean)
#> drugA drugB drugC 
#> 4.600 3.125 2.700
user_125003150

And here's a tidyr way:

library(dplyr)
library(tidyr)
df <- read.table(text ="
cohort med1   med2   med3   meq1   meq2   meq3
a      drugA  drugB  NA     4.3    1.9    NA
b      drugB  drugC  drugA  2.1    2.0    4.5
a      drugC  NA     NA     2.0    NA     NA
a      drugA  drugB  NA     5.5    3.6    NA
b      drugB  drugA  drugC  4.9    4.1    4.1
", header = TRUE)

df_long <- df %>%
  pivot_longer(cols = starts_with("me"), 
               names_to = c(".value", "m"),
               names_pattern = "(me(?:d|q))(\\d)")

This first step groups the multiple observations per row (med and meq for each of 1, 2, and 3)

print(df_long)
#> # A tibble: 15 x 4
#>    cohort m     med     meq
#>    <fct>  <chr> <fct> <dbl>
#>  1 a      1     drugA   4.3
#>  2 a      2     drugB   1.9
#>  3 a      3     <NA>   NA  
#>  4 b      1     drugB   2.1
#>  5 b      2     drugC   2  
#>  6 b      3     drugA   4.5
#>  7 a      1     drugC   2  
#>  8 a      2     <NA>   NA  
#>  9 a      3     <NA>   NA  
#> 10 a      1     drugA   5.5
#> 11 a      2     drugB   3.6
#> 12 a      3     <NA>   NA  
#> 13 b      1     drugB   4.9
#> 14 b      2     drugA   4.1
#> 15 b      3     drugC   4.1

Then we can take the mean of each drug for each cohort:

df_long %>%
  group_by(cohort, med) %>%
  summarize(meq = mean(meq))
#> Warning: Factor `med` contains implicit NA, consider using
#> `forcats::fct_explicit_na`
#> # A tibble: 7 x 3
#> # Groups:   cohort [2]
#>   cohort med     meq
#>   <fct>  <fct> <dbl>
#> 1 a      drugA  4.9 
#> 2 a      drugB  2.75
#> 3 a      drugC  2   
#> 4 a      <NA>  NA   
#> 5 b      drugA  4.3 
#> 6 b      drugB  3.5 
#> 7 b      drugC  3.05

Created on 2020-04-10 by the reprex package (v0.3.0)

user_131575360
Related