Amin Amin - 3 months ago 18
R Question

Grouping in Embedded Group Structures in R data.table

I have a data.table object looks like this:

FamilyID InterFamilyID MumInFamilyID Edu
1 1 NA 2
1 2 NA 5
1 3 2 3
2 1 NA 6
2 2 1 9
2 2 1 3


I want to perform a query like this one:

tbl1[, MumEdu:= Edu[InterFamilyID == MumInFamilyID], by=FamilyID]


to get something like this:

FamilyID InterFamilyID MumInFamilyID Edu MumEdu
1 1 NA 2 NA
1 2 NA 5 NA
1 3 2 3 5
2 1 NA 6 NA
2 2 1 9 6
2 2 1 3 6


In fact I have a data.table grouped by a column (FamilyID) and each of these groups are 1-1 grouped by another column (InterFamilyID). In another column there is reference to smaller group id of another group member. I want to use these values to access the referenced rows values.

Answer

You can use match to:

returns a vector of the positions of (first) matches of its first argument in its second.

and use the result positions to find out the corresponding element in Edu column:

tbl1[, MumEdu := Edu[match(MumInFamilyID, InterFamilyID)], by = FamilyID]
tbl1
#   FamilyID InterFamilyID MumInFamilyID Edu MumEdu
#1:        1             1            NA   2     NA
#2:        1             2            NA   5     NA
#3:        1             3             2   3      5
#4:        2             1            NA   6     NA
#5:        2             2             1   9      6
#6:        2             2             1   3      6