How do I fill in missing values based on the values in other rows?

user_16367290 0
Viewed 176
country <- c("USA","UK","Egypt","Brazil","USA", "UK")
continent <- c("North America","Europe","Africa","South America", NA, NA)
data.frame(country, continent)

How do I automatically impute those last two NAs based on the previous rows since we know that "USA" goes with "North America" and "UK" goes with "Europe"?

I have a large dataset so it would be super helpful if I could find a quick way to do this in dplyr when there are many NAs.

Thank you in advance!

data-wrangling dplyr r
2 Answers

We can do a group by fill

library(dplyr)
library(tidyr)
d1 %>% 
     group_by(country) %>% 
     fill(continent) %>% 
     ungroup

-output

# A tibble: 6 x 2
  country continent    
  <chr>   <chr>        
1 USA     North America
2 UK      Europe       
3 Egypt   Africa       
4 Brazil  South America
5 USA     North America
6 UK      Europe
user_37322710

We could use na.locf from zoo package:

library(dplyr)
library(zoo)
df1 %>% 
    mutate(id = row_number()) %>% 
    group_by(country) %>% 
    do(na.locf(.)) %>% 
    arrange(id) %>% 
    select(-id) %>% 
    ungroup

Output:

  country continent    
  <chr>   <chr>        
1 USA     North America
2 UK      Europe       
3 Egypt   Africa       
4 Brazil  South America
5 USA     North America
6 UK      Europe
user_133216470
Related