How to merge 2 columns with some same values-CodePudding

Here is a dataset::

wv1<- c(1,2,3,4, NA, NA, NA)
wv2 <- c(1,1.5,2,2.5,3,3.5,4) 
x1 <- c(0.2,0.5,0.2,0.3,NA,NA,NA) 
x2 <- c(0.3,0.4,0.6,0.4,0.7,0.8,0.9)

library(tidyverse)

df <-as.data.frame( cbind(wv1, x1,wv2, x2))
df
  wv1  x1 wv2  x2
1   1 0.2 1.0 0.3
2   2 0.5 1.5 0.4
3   3 0.2 2.0 0.6
4   4 0.3 2.5 0.4
5  NA  NA 3.0 0.7
6  NA  NA 3.5 0.8
7  NA  NA 4.0 0.9

I want to merge unique numbers in wv1 and wv2, but retain x1 and x2 columns. The desired output looks like below:

wv <- wv2
x1 <- c(0.2,NA,0.5,NA,0.2,NA,0.3)
x2 <- x2

df_clean <- cbind(wv, x1,x2)
df_clean
      wv  x1  x2
[1,] 1.0 0.2 0.3
[2,] 1.5  NA 0.4
[3,] 2.0 0.5 0.6
[4,] 2.5  NA 0.4
[5,] 3.0 0.2 0.7
[6,] 3.5  NA 0.8
[7,] 4.0 0.3 0.9

I tried using the following code, but it does not work. I tried creating two different dataframes, and the following code provides the desired output, but is there an easy way to do this?

merge(df$wv1, df$wv2, all= T)
    x   y
1   1 1.0
2   2 1.0
3   3 1.0
4   4 1.0
5  NA 1.0
6  NA 1.0
7  NA 1.0
8   1 1.5
9   2 1.5
10  3 1.5
11  4 1.5
12 NA 1.5
13 NA 1.5
14 NA 1.5
15  1 2.0
16  2 2.0
17  3 2.0
18  4 2.0
19 NA 2.0
20 NA 2.0
21 NA 2.0
22  1 2.5
23  2 2.5
24  3 2.5
25  4 2.5
26 NA 2.5
27 NA 2.5
28 NA 2.5
29  1 3.0
30  2 3.0
31  3 3.0
32  4 3.0
33 NA 3.0
34 NA 3.0
35 NA 3.0
36  1 3.5
37  2 3.5
38  3 3.5
39  4 3.5
40 NA 3.5
41 NA 3.5
42 NA 3.5
43  1 4.0
44  2 4.0
45  3 4.0
46  4 4.0
47 NA 4.0
48 NA 4.0
49 NA 4.0

CodePudding user response：

Does this work:

library(dplyr)
library(tidyr)

df %>% select(wv2) %>% mutate(x1 = NA_real_, x2 = NA_real_) %>% bind_rows(df %>% select(wv1, x1, x2)) %>% 
   mutate(wv = coalesce(wv1, wv2)) %>% select(wv, x1, x2) %>% filter(!is.na(wv)) %>% group_by(wv) %>% 
       fill(x1, x2, .direction = 'up') %>% distinct()
# A tibble: 7 × 3
# Groups:   wv [7]
     wv    x1    x2
  <dbl> <dbl> <dbl>
1   1     0.2   0.3
2   1.5  NA    NA  
3   2     0.5   0.4
4   2.5  NA    NA  
5   3     0.2   0.6
6   3.5  NA    NA  
7   4     0.3   0.4