0

I would like to create a new data table in R, where if two values in the same column of anohter data table are equal, the values in the next column of those observations that were equal are paired into one variable. For instance:

award            actor year
1: 10th_Annual_Critics__Choice_Awards__The_2005      Aiken__Liam 2005
2: 10th_Annual_Critics__Choice_Awards__The_2005        Bain__Bob 2005
3: 10th_Annual_Critics__Choice_Awards__The_2005   Bardem__Javier 2005
4: 10th_Annual_Critics__Choice_Awards__The_2005 Bello__Maria__I_ 2005
5: 10th_Annual_Critics__Choice_Awards__The_2005     Berlin__Joey 2005

Liam Aiken, and Bob Bain both attended the same award, so in a different data table I would like to have and entry of something like "Aiken_Liam Bain_Bob", as well as one pair for all the people who attended the same award (Aiken + Bardem, Bardem + Berlin, and so on). I was thinking of approaching this with a while loop loop like:

i <- 1
j <- i + 1    
while(dt.awards.actor[i,]$award == dt.awards.actor[j,]$award){
      w <- paste(dt.awards.actor[i,]$actor, dt.awards.actor[j,]$actor))
      print(w)
      i <- i + 1
    }

However, the code above returns me all the pairs for "Bain_Bob", while skipping over "Aiken_Liam", and also that while loop stops searching when it finds the pairs for "Bain_Bob" (it does not search for anyone else's pairs). Also how can I code so that I get the show to which both actors attended, and turn the output into a data.table? Maybe using an lapply, or an apply function?

Diego Molina
  • 35
  • 1
  • 8
  • I would separate the name and first namen and then just use `merge`. Perhaps add an additional example how the result should look like. This looks like a merge on `year` and `award` and then you can just concatenate all the name columns. – hannes101 Feb 15 '18 at 12:22
  • Related: [*Collapse / concatenate / aggregate a column to a single comma separated string within each group*](https://stackoverflow.com/questions/15933958/collapse-concatenate-aggregate-a-column-to-a-single-comma-separated-string-w) – Jaap Feb 16 '18 at 16:48

0 Answers0