Get information from another row in an efficient way

user_16155676 0
Viewed 39

I'm struggling with the way to pick up the value of a variable based on the value of another variable in a different row in a data frame, within groups, and without usign for() loops. I'd be thankful if anyone could help me. These are a simplified form of my data:

library(data.table)

df<-data.table(personid<-c(101, 102, 103, 104, 105, 201, 202, 203, 301, 302, 401),
   hh_id<-c(1, 1, 1, 1, 1, 2, 2, 2, 3, 3, 4),
   fatherid<-c(NA, NA, 101, 101, 101, NA, NA, 201, NA, NA, NA),
   cancer<-(1,0,0,0,0,0,0,0,0,0,1))

In my data frame, cases are grouped in households (hh_id), and there is a variable "fatherid" pointing at the person's father. I'd need to create a variable that indicates whether the person's father has cancer. This is: to pick up the value of the "cancer" variable from the fathers' register.

The expected result for this sample dataset would be:

fathercancer<-c(NA, NA, 1, 1, 1, NA, NA, 0, NA, NA, NA)

Any ideas on how to solve this without using a for loop?

Thanks in advance!

dataframe r
2 Answers

We may use match

df[, fathercancer := cancer[match(fatherid, personid)], hh_id]

-output

> df
    personid hh_id fatherid cancer fathercancer
 1:      101     1       NA      1           NA
 2:      102     1       NA      0           NA
 3:      103     1      101      0            1
 4:      104     1      101      0            1
 5:      105     1      101      0            1
 6:      201     2       NA      0           NA
 7:      202     2       NA      0           NA
 8:      203     2      201      0            0
 9:      301     3       NA      0           NA
10:      302     3       NA      0           NA
11:      401     4       NA      1           NA

data

df <- structure(list(personid = c(101, 102, 103, 104, 105, 201, 202, 
203, 301, 302, 401), hh_id = c(1, 1, 1, 1, 1, 2, 2, 2, 3, 3, 
4), fatherid = c(NA, NA, 101, 101, 101, NA, NA, 201, NA, NA, 
NA), cancer = c(1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1)), row.names = c(NA, 
-11L), class = c("data.table", "data.frame"))
user_37322710

If you don't want to use data.table, here's a base R version.

First generate a true/false list, then combine it and generate the output:

tf <- sapply( unique(df$hh_id[as.logical(df$cancer)]), function(x) x == hh_id )
tfv <- tf[,1] > tf[,2] | tf[,1] < tf[,2]
as.numeric(tfv)[ !is.na(df$fatherid) | NA ]

#[1] NA NA  1  1  1 NA NA  0 NA NA NA

data.frame( df, fathercancer=as.numeric(tfv)[ !is.na(df$fatherid) | NA ] )
#   personid hh_id fatherid cancer fathercancer
#1       101     1       NA      1           NA
#2       102     1       NA      0           NA
#3       103     1      101      0            1
#4       104     1      101      0            1
#5       105     1      101      0            1
#6       201     2       NA      0           NA
#7       202     2       NA      0           NA
#8       203     2      201      0            0
#9       301     3       NA      0           NA
#10      302     3       NA      0           NA
#11      401     4       NA      1           NA
user_94620950
Related