I am trying to merge 3 rows in to 1 based on multiple conditions. The dataframe is created as follows:
region <- c("Europe", "Asia", "Europe", "Africa","Europe")
enterprise <- c(1, 1, 2, 3, 3)
q1 <- c(NA, 0, NA, 1, 0)
q2 <- c(0, 1, 1, NA, NA)
q3 <- c(NA, 1, NA, 0, NA)
q4 <- c(NA, 0, 1, 0, 0)
q5 <- c(0,NA,1,NA,1)
df <- data.frame(region, enterprise, q1, q2, q3, q4,q5)
So, I'm working with survey data and I'm trying to merge rows corresponding to region 'Europe' in to one row. Conditions are as follows:
I have tried using
merged = coalesce(df[1,],df[3,],df[5,])
But couldn't get it to work. I don't know how to pass conditions especially the one with hierarchies. My desired output is as follows:
region q1 q2 q3 q4 q5
1 Asia 0 1 1 0 NA
2 Africa 1 NA 0 0 NA
3 Europe 0 0 NA 1 0
Thank you very much for any help, beforehand.
An approach using fill
library(dplyr)
library(tidyr)
df %>%
group_by(region) %>%
fill(q1:q5, .direction="updown") %>%
arrange(enterprise) %>%
summarise(across(q1:q5, ~ .x[1]))
# A tibble: 3 × 6
region q1 q2 q3 q4 q5
<chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Africa 1 NA 0 0 NA
2 Asia 0 1 1 0 NA
3 Europe 0 0 NA 1 0