Replace NA value with the correct one

Question

Suppose that we have the following data frame:

ID <- c(1, 1, 1, 2, 2, 3, 3, 3, 3, 4, 4, 5, 5, 5, 6, 6, 6)
age <- c(25, 25, 25, 22, 22, 56, 56, 56, 80, 33, 33, 90, 90, 90, 5, 5, 5)
gender <- c("m", "m", NA, "f", "f", "m", NA, "m", "m", "m", NA, NA, NA, "m", NA, NA, NA)
company <- c("c1", "c2", "c2", "c3", "c3", "c1", "c1", "c1", "c1", "c5", "c5", "c3", "c4", "c5", "c3", "c1", "c1")
income <- c(1000, 1000, 1000, 500, 1700, 200, 200, 250, 500, 700, 700, 300, 350, 300, 500, 1700, 200)

df <- data.frame(ID, age, gender, company, income)

In this data we have 6 unique IDs, and if you look at the gender variable, sometimes in includes NA

I want to replace the NAs with the correct gender category. Also, in case an ID has all NA's for gender, then leave it as is.

The expected outcome would be:

you could use `fill`, `df %>% group_by(age) %>% fill(gender) %>% fill(gender, .direction = "up") ` — Ronak Shah, Aug 15 '19 at 00:20

score 1 · Answer 1 · answered Aug 15 '19 at 00:16

Using the tidyverse library you can do this

library(tidyverse)
# for each ID get the gender
df_gender_ref <- df %>% filter(!is.na(gender)) %>% select(ID,gender) %>% unique() 
# add the new gender column to the original dataframe
df %>% select(-gender) %>% left_join(df_gender_ref)

score 1 · Accepted Answer · answered Aug 15 '19 at 00:18

Here's way in base R using ave -

df$gender <- with(df, ave(gender, ID, FUN = function(x) na.omit(x)[1]))

   ID age gender company income
1   1  25      m      c1   1000
2   1  25      m      c2   1000
3   1  25      m      c2   1000
4   2  22      f      c3    500
5   2  22      f      c3   1700
6   3  56      m      c1    200
7   3  56      m      c1    200
8   3  56      m      c1    250
9   3  80      m      c1    500
10  4  33      m      c5    700
11  4  33      m      c5    700
12  5  90      m      c3    300
13  5  90      m      c4    350
14  5  90      m      c5    300
15  6   5   <NA>      c3    500
16  6   5   <NA>      c1   1700
17  6   5   <NA>      c1    200

Some ways with dplyr and tidyr -

df %>% 
  group_by(ID) %>% 
  mutate(gender = na.omit(gender)[1])

df %>% 
  group_by(ID) %>% 
  fill(gender, .direction = "up") %>% 
  fill(gender, .direction = "down")

Replace NA value with the correct one

2 Answers2