CODEWITHSUNDEEP
rdataframedata.table
Santi
Santi

Reputation: 451

R Data Table Add Columns From Different Rows

Say I have this data table:

Name  |  Pet  | Hobby    | Sibling
------------------------------------------
Bob   | Dog   | Running  | Linda
Ross  | Cat   | Hiking   | Howie
Linda | Mouse | Beaches  | Bob
Howie | Plant | Gaming   | Ross

So next, I want to add the sibling's information to the same row. The sibling's information is in the table. So essentially you'd find the row of the sibling and append the rows 'Pet' and 'Hobby' of the sibling unto the row as such

Name  |  Pet  | Hobby    | Sibling | Sibling's Pet | Sibling Hobby's
-----------------------------------------------------------------
Bob   | Dog   | Running  | Linda   | Mouse         | Beaches     #Cols appended from Row 3 above
Ross  | Cat   | Hiking   | Howie   | Plant         | Gaming      #Cols appended from Row 4 above
Linda | Mouse | Beaches  | Bob     | Dog           | Running     #Cols appended from Row 1 above
Howie | Plant | Gaming   | Ross    | Cat           | Hiking      #Cols appended from Row 2 above

Upvotes: 2

Views: 26

Answers (2)

talat
talat

Reputation: 70266

Here's a data.table approach using a self-join and update-by-reference:

dt[dt, on = c("Sibling" = "Name"), `:=`(sib_pet = i.Pet, sib_hob = i.Hobby)]

#    Name   Pet   Hobby Sibling sib_pet sib_hob
#1:   Bob   Dog Running   Linda   Mouse Beaches
#2:  Ross   Cat  Hiking   Howie   Plant  Gaming
#3: Linda Mouse Beaches     Bob     Dog Running
#4: Howie Plant  Gaming    Ross     Cat  Hiking

Upvotes: 3

akrun
akrun

Reputation: 887108

We create an index with match and then use that to create the columns

i1 <- with(df1, match(Name, Sibling))
df1[paste0("Siblings_", c("Pet", "Hobby"))] <- lapply(df1[2:3], function(x) x[i1])
df1
#   Name   Pet   Hobby Sibling Siblings_Pet Siblings_Hobby
#1   Bob   Dog Running   Linda        Mouse        Beaches
#2  Ross   Cat  Hiking   Howie        Plant         Gaming
#3 Linda Mouse Beaches     Bob          Dog        Running
#4 Howie Plant  Gaming    Ross          Cat         Hiking

Upvotes: 3

Related Questions