1

I have two dataframes which look somewhat like this:

dat1 <- data.frame(z = 5:20, val = seq(30, 105, by = 5))
dat2 <- data.frame(z = c(0, 2, 8, 10, 12, 15), val = c(15, 20, 40, 50, NaN, NaN))

Which looks like:

dat1        dat2
 z val      z val
 5  30      0  15
 6  35      2  20
 7  40      8  40
 8  45      10 50  
 9  50      12 NaN 
10  55      15 NaN
11  60     
12  65
13  70
14  75
15  80
16  85
17  90
18  95
19 100
20 105

What I want is to fill in the NaN of the second dataframe with the same value in the val column where the z values are the same.

So I want my dat2 dataframe to look like:

dat2
z val
0 15
2 20
8 40
10 50
12 65
15 80

I thought about getting the indexes of the NaN values, but they don't overlap. I need to match them somehow with the z column but fill in the NaN columns with those values. I don't really know how to do this.

I have tried the answers here, but all of these remove all values in dat2 below the minimum values of dat1, which I need to keep as well.

The code I'm using so far:

  filled <- inner_join(dat2, dat1, by = "z") %>% 
    mutate(answer = coalesce(val.x, val.y))

However, now the result looks like this:

dat2
z val
8 40
10 50
12 65
15 80

While I want to keep all other values as well.

B.Quaink
  • 456
  • 1
  • 4
  • 18

1 Answers1

2

We can do a left_join by 'z' and coalesce the 'val' columns

library(dplyr)
left_join(dat2, dat1, by = 'z') %>% 
       transmute(z, val = coalesce(val.x, val.y))
#   z val
#1  0  15
#2  2  20
#3  8  40
#4 10  50
#5 12  65
#6 15  80
akrun
  • 874,273
  • 37
  • 540
  • 662