Merge multiple rows in R

Question

I currently have some data where people answered questions about a behavior. Some questions are an overall statement about past and current behavior (the both condition) and other questions asked about the behavior in response to a certain question framing (condition x and y).

I want to merge these rows so I have two rows for each participant

One row will have all their answers to the conditions marked "both" and answers to condition "x"
Another row will have all their answers to the conditions marked "both and answers to condition "y".

I've tried several grouping solutions and cannot seem to get this to work. Also looked at these answers:

And neither of these worked either.

Example code below. Appreciate any help because at the moment I'm reduced to copy-pasting in excel which is not ideal.

library(tidyverse)
a <- 
  tibble(id = c(rep(1, 3), rep(2,3), rep(3, 3)) , condition = rep(c("both", "x", "y"),3), belief = c(4.8, NA, NA, 3.2, NA, NA, 6.5, NA, NA), past = c(1.8, NA, NA, 4.8, NA, NA, 2.6, NA, NA), current= c(1.8, NA, NA, 3.8, NA, NA, 3.1, NA, NA), feel = c(NA, .6, 1, NA, 2.1, 2.3, NA, 2.6, 1.5), willing = c(NA, 3.6, 205, NA, 2.1, 2.3, NA, 1.3, 0.3))

#what my data currently looks like
a
#> # A tibble: 9 × 7
#>      id condition belief  past current  feel willing
#>   <dbl> <chr>      <dbl> <dbl>   <dbl> <dbl>   <dbl>
#> 1     1 both         4.8   1.8     1.8  NA      NA  
#> 2     1 x           NA    NA      NA     0.6     3.6
#> 3     1 y           NA    NA      NA     1     205  
#> 4     2 both         3.2   4.8     3.8  NA      NA  
#> 5     2 x           NA    NA      NA     2.1     2.1
#> 6     2 y           NA    NA      NA     2.3     2.3
#> 7     3 both         6.5   2.6     3.1  NA      NA  
#> 8     3 x           NA    NA      NA     2.6     1.3
#> 9     3 y           NA    NA      NA     1.5     0.3

b <- 
  tibble(id = c(rep(1, 2), rep(2,2), rep(3, 2)), condition = rep(c("x", "y"),3), belief = c(4.8, 4.8, 3.2, 3.2, 6.5, 6.5), past = c(1.8, 1.8, 4.8, 4.8, 2.6, 2.6), current= c(1.8, 1.8, 3.8, 3.8, 3.1, 3.1), feel = c(.6, 1,  2.1, 2.3, 2.6, 1.5), willing = c( 3.6, 205,  2.1, 2.3,  1.3, 0.3))

#what I want my data to look like
b
#> # A tibble: 6 × 7
#>      id condition belief  past current  feel willing
#>   <dbl> <chr>      <dbl> <dbl>   <dbl> <dbl>   <dbl>
#> 1     1 x            4.8   1.8     1.8   0.6     3.6
#> 2     1 y            4.8   1.8     1.8   1     205  
#> 3     2 x            3.2   4.8     3.8   2.1     2.1
#> 4     2 y            3.2   4.8     3.8   2.3     2.3
#> 5     3 x            6.5   2.6     3.1   2.6     1.3
#> 6     3 y            6.5   2.6     3.1   1.5     0.3

score 3 · Answer 1 · answered Dec 06 '21 at 17:52

Consider a group by fill and then filter out the 'both' row

library(dplyr)
library(tidyr)
out <- a %>%
   group_by(id) %>% 
   fill(belief:willing, .direction = "downup") %>%
   ungroup %>% 
   filter(condition != 'both')

-checking

> identical(out, b)
[1] TRUE

Merge multiple rows in R

1 Answers1