gibbz00 gibbz00 - 3 months ago 6
R Question

Pasting all prior elements of a column as of a row with the highest score

This is what my data-frame looks like:

library(data.table)
dt <- fread('
Product Score Description
A 1 aapl
A 2 banana
A 3 orange
B 1 coke
B 2 pepsi
C 1 butter
D 1 milk
')


I am trying to get the max score of each product and concatenate all the Descriptions prior to that row with max score so the output looks like this:

Product Score(Max) Description2
A 3 aapl;banana;orange
B 2 coke;pepsi
C 1 butter
D 1 milk


I have tried

dt[,Description2 := as.character(ifelse(!max(Score),NA,paste(shift(Description,1),
Description,sep=";"))),by=Product]


Your help is appreciated!

Answer

We group by 'Product', get the max of 'Score' and paste the 'Description' together with collapse = ";".

dt[, .(ScoreMax = max(Score), Description2 = paste(Description, collapse=";")), 
          by = .(Product)]
#   Product ScoreMax       Description2
#1:       A        3 aapl;banana;orange
#2:       B        2         coke;pepsi
#3:       C        1             butter
#4:       D        1               milk

Using := (assignment) creates a new column in the initial dataset. For summarisation, we can keep it in a list or just use .( after grouping by the variable.