Merge three Variables to one and replicate observations

user_18169859 0
Viewed 37

I have a Dataframe which looks like the following:

B <-  data.frame(
    nr=c(1,2,3,4,5),
    A=c('a','b','c','d','e'),
    B=c("s", "t", "i", "u", "z"),
    B1=c("", "v", "", "", ""),
    B2 =c("", "g", "", "", ""))
B <- B %>% mutate_all(na_if,"")

Since my Varaibales B1 and B2 only have one value, I would like to merge B1 and B2 to the Variable B. Therefor it should create two new observation and replicating every other Variable of this Oberservation.

It should look like the following:

B <-  data.frame(
    nr=c(1,2,2, 2, 3,4,5),
    A=c("a","b", "b", "b", "c","d","e"),
    B=c("s", "v", "g", "t", "i", "u", "z"))

Thanks for your help!!

dataframe r merge
1 Answers

Reshape to 'long' format with pivot_longer on the 'B' columns and remove the NA with values_drop_na = TRUE

library(dplyr)
library(tidyr)
B %>% 
   pivot_longer(cols = starts_with("B"), values_to = "B", 
     values_drop_na = TRUE, names_to = NULL)

-output

# A tibble: 7 × 3
     nr A     B    
  <dbl> <chr> <chr>
1     1 a     s    
2     2 b     t    
3     2 b     v    
4     2 b     g    
5     3 c     i    
6     4 d     u    
7     5 e     z
user_37322710
Related