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:

• Merging multiple rows into one row by several conditions in R
• Multiples rows to one row in R

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

Answer 1

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