replace NA value with the group value

Question

I have a df as follows which has 20 people across 5 households. Some people within the household have missing data for whether they have a med_card or not. I want to give these people the same value as the other people in their household (not an NA value, a real binary value which is either 0 or 1).

I have tried the following code, which is a step in the right direction I think - but isn't 100% correct because a) it doesn't work if the first value for med_card per household is NA and b) it doesn't replace NA for all people in household 1.

DF<- ddply(df, .(hhold_no), function(df) {df$med_card[is.na(df$med_card)] <- head(df$med_card, na.rm=TRUE); return(df)})

Any pointers would be greatly appreciated, Thank you

Sample df

df
   person_id hhold_no med_card
1          1        1        1
2          2        1        1
3          3        1       NA
4          4        1       NA
5          5        1       NA
6          6        2        0
7          7        2        0
8          8        2        0
9          9        2        0
10        10        3       NA
11        11        3       NA
12        12        3       NA
13        13        3        1
14        14        3        1
15        15        4        1
16        16        4        1
17        17        5        1
18        18        5        1
19        19        5       NA
20        20        5       NA

and code to make

person_id<-as.numeric(c(1:20))
hhold_no<-as.numeric(c(1,1,1,1,1,2,2,2,2,3,3,3,3,3,4,4,5,5,5,5))
med_card<-as.numeric(c(1,1,NA,NA,NA,0,0,0,0,NA,NA,NA,1,1,1,1,1,1,NA,NA))
df<-data.frame(person_id,hhold_no, med_card)

Desired output

df
   person_id hhold_no med_card med_card_new
1          1        1        1            1
2          2        1        1            1
3          3        1       NA            1
4          4        1       NA            1
5          5        1       NA            1
6          6        2        0            0
7          7        2        0            0
8          8        2        0            0
9          9        2        0            0
10        10        3       NA            1
11        11        3       NA            1
12        12        3       NA            1
13        13        3        1            1
14        14        3        1            1
15        15        4        1            1
16        16        4        1            1
17        17        5        1            1
18        18        5        1            1
19        19        5       NA            1
20        20        5       NA            1

Katalin

Answer 1

Try ave. It applies a function to groups. Have a look at ?ave for details, e.g.:

df$med_card_new <- ave(df$med_card, df$hhold_no, FUN=function(x)unique(x[!is.na(x)]))

#   person_id hhold_no med_card med_card_new
#1          1        1        1            1
#2          2        1        1            1
#3          3        1       NA            1
#4          4        1       NA            1
#5          5        1       NA            1
#6          6        2        0            0
#7          7        2        0            0
#8          8        2        0            0
#9          9        2        0            0

Please note that this will only work if not all values in a household are NA and the should not differ (e.g. person 1 == 1, person 2 == 0).

Crackerbarrel

thank you! I had made some very clumsy attempts with ave, but couldn't quite get it to work for me. Thanks again! :) – Katalin

Answer 2

data.table solution

library(data.table)
setDT(df)[, med_card2 := unique(med_card[!is.na(med_card)]), by = hhold_no]

#     person_id hhold_no med_card med_card2
#  1:         1        1        1         1
#  2:         2        1        1         1
#  3:         3        1       NA         1
#  4:         4        1       NA         1
#  5:         5        1       NA         1
#  6:         6        2        0         0
#  7:         7        2        0         0
#  8:         8        2        0         0
#  9:         9        2        0         0
# 10:        10        3       NA         1
# 11:        11        3       NA         1
# 12:        12        3       NA         1
# 13:        13        3        1         1
# 14:        14        3        1         1
# 15:        15        4        1         1
# 16:        16        4        1         1
# 17:        17        5        1         1
# 18:        18        5        1         1
# 19:        19        5       NA         1
# 20:        20        5       NA         1

Howsoever

Answer 3

That is exactly what na.aggregate (link) in the zoo package does:

library(zoo)

transform(df, med_card_new = na.aggregate(med_card, by = hhold_no))

This uses mean; however, you can specify any function you like. For example, if you prefer to return an NA if all items in a group are NA (rather than NaN which is what mean would return if given a zero length vector) then

meanNA <- function(x, ...) if (all(is.na(x))) NA else mean(x, ...)
transform(df, med_card_new = na.aggregate(med_card, by = hhold_no, FUN = meanNA))

Peahen

thank you, had never seen this before. very handy. :) – Katalin

Just used this wonderfully, but got NaNs for cases that didn't have data it could aggregate. Had trouble converting NaN to NA properly & discovered the best way to do so is df[is.na(df)] <- NA. – Predominance

That is how mean works. If you take the mean of a zero length vector it gives NaN. If you don't want that you can specify an alternate function. See added code above. – Peahen

Answer 4

Using dplyr you could also group_by() and then take advantage of a function such as max with an na.rm argument to return all numerics for each group.

library(dplyr)
df %>% group_by(hhold_no) %>% mutate(med_card_new = max(med_card, na.rm = T))

Given that non-missings in a group are numeric and constant, you could also use mean or min instead of max.

Barn

Answer 5

This is late, but if are working on a numeric column, try this:

require(data.table)

setDT(df)[,'record_year':=mean( med_card,na.rm = T),by = c('hhold_no')]

Schriever