Reputation: 103

filling NA with values from another table

I have the following datasets in RStudio:

df =

and fill_with =

How do I fill the NA values in df in the b column according to the a column? Ex: a=1, b=NA, then I look at the table fill_with at a=1, and I see that I should fill it with b=A.

In the end it should look the following way: df =

Upvotes: 0

Answers (4)

Yuriy Saraykin

Reputation: 8880

library(tidyverse)

df <- data.frame(
  stringsAsFactors = FALSE,
  a = c(1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 3),
  b = c("A", NA, "A", NA, "C", NA, "B", "A", NA, "C", "D")
)

fill_with <- data.frame(
  stringsAsFactors = FALSE,
  a = c(1L, 2L, 3L),
  b = c("A", "B", "C")
)

rows_update(x = df, y = fill_with, by = "a")
#>    a b
#> 1  1 A
#> 2  1 A
#> 3  1 A
#> 4  1 A
#> 5  2 B
#> 6  2 B
#> 7  2 B
#> 8  3 C
#> 9  3 C
#> 10 3 C
#> 11 3 C

^{Created on 2022-08-22 with reprex v2.0.2}

Upvotes: 1

Mohamed Desouky

Reputation: 4425

We can use ifelse

df$b <- ifelse(is.na(df$b) , 
        fill_with$b[match(df$a , fill_with$a)] , df$b)

Output

Upvotes: 2

user2974951

Reputation: 10375

Base R

tmp=which(is.na(df$b))
df$b[tmp]=fill_with$b[match(df$a,fill_with$a)[tmp]]

   a b
1  1 A
2  1 A
3  1 A
4  1 A
5  2 C
6  2 B
7  2 B
8  3 A
9  3 C
10 3 C
11 3 D

Upvotes: 1

HoelR

Reputation: 6563

library(tidyverse)

df <- read_table("a   b
1   A           
1   NA          
1   A           
1   NA          
2   C           
2   NA          
2   B           
3   A           
3   NA          
3   C
3   D")

df %>%  
  group_by(a) %>%  
  fill(b, .direction = "updown")

# A tibble: 11 x 2
# Groups:   a [3]
       a b    
   <dbl> <chr>
 1     1 A    
 2     1 A    
 3     1 A    
 4     1 A    
 5     2 C    
 6     2 B    
 7     2 B    
 8     3 A    
 9     3 C    
10     3 C    
11     3 D

Upvotes: 2

filling NA with values from another table

Answers (4)

Related Questions