CODEWITHSUNDEEP
rdata.tabletidyr
Amer
Amer

Reputation: 2251

Merging specific rows into columns

I have longitudinal data with many columns but included here the columns of interest;

dfin1 <- 
 ID    ROUND   CONC    PD
  1     0      100     15
  1     2      120     10
  1     4      14      10 
  1     6      10      8
  2     0      120     15

and a subject level data with many columns;

 dfin2 <- 
  ID   HEIGHT  GENDER
   1     120     0
   2     130     1

for each subject, I want to add columns for the values of CONC and PD at ROUND 0, 2, 6 into the df2. Column names CONC_ROUNDx, PD_ROUNDx, where x is Round number. Output shoudl be like this:

 dfout <-  
  ID   HEIGHT  GENDER  CONC_ROUND0  PD_ROUND0  CONC_ROUND2  PD_ROUND2  CONC_ROUND6  PD_ROUND6   
   1     120     0      100          15          120          10          10            8      
   2     130     1      120          15           NA          NA          NA            NA

I am using RStudio.

Upvotes: 1

Views: 117

Answers (2)

akrun
akrun

Reputation: 886938

Here is an option with tidyverse where we filter the 'ROUND' with the values of interest, then mutate by adding a prefix 'ROUND' to 'ROUND' column, using pivot_wider (from the dev version of tidyr), change the data from long to wide (which can take multiple value columns) and do a join with the second dataset

library(dplyr) # 0.8.3
library(tidyr) # 0.8.3.9000
library(stringr)
dfin1 %>%
   filter(ROUND %in% c(0, 2, 6)) %>%
   mutate(ROUND =  str_c("ROUND", ROUND)) %>% 
   pivot_wider(names_from = ROUND, values_from = c("CONC", "PD")) %>% 
   inner_join(dfin2, .)
#    ID HEIGHT GENDER CONC_ROUND0 CONC_ROUND2 CONC_ROUND6 PD_ROUND0 PD_ROUND2 PD_ROUND6
#1  1    120      0         100         120          10        15        10         8
#2  2    130      1         120          NA          NA        15        NA        NA

data

dfin1 <- structure(list(ID = c(1L, 1L, 1L, 1L, 2L), ROUND = c(0L, 2L, 
4L, 6L, 0L), CONC = c(100L, 120L, 14L, 10L, 120L), PD = c(15L, 
10L, 10L, 8L, 15L)), class = "data.frame", row.names = c(NA, 
-5L))

dfin2 <- structure(list(ID = 1:2, HEIGHT = c(120L, 130L), GENDER = 0:1), 
     class = "data.frame", row.names = c(NA, 
-2L))

Upvotes: 3

chinsoon12
chinsoon12

Reputation: 25225

A possible approach to pivot into wide form and then join with subject level data:

pvt <- dcast(dfin1, ID ~ ROUND, value.var=c("CONC", "PD"))
dfin2[pvt, on=.(ID), names(pvt) := mget(paste0("i.", names(pvt)))]

output:

   ID HEIGHT GENDER CONC_0 CONC_2 CONC_4 CONC_6 PD_0 PD_2 PD_4 PD_6
1:  1    120      0    100    120     14     10   15   10   10    8
2:  2    130      1    120     NA     NA     NA   15   NA   NA   NA

data:

library(data.table)
dfin1 <- fread("ID    ROUND   CONC    PD
1     0      100     15
1     2      120     10
1     4      14      10 
1     6      10      8
2     0      120     15")
dfin2 <- fread("ID   HEIGHT  GENDER
1     120     0
2     130     1")

Upvotes: 4

Related Questions