Search code examples
rexcelmergeexcel-2013

Concatenate data frame to list

I have a some row names in Excel with their respective publications. 

Name    Pub
AAAA    MNWP 
AAAA    TIWD 
BBBB    CGWK 
BBBB    MNWP 
CCCC    EXWE 
CCCC    EXWE 
CCCC    HXWT 
CCCC    MDWE 
CCCC    MDWE 
CCCC    MNWP 
CCCC    MNWP 
CCCC    NDWK 
DDDD    MNWP 
EEEE    EXWE 
EEEE    TIWE

Essentially I want a list to be of this form:

AAAA: MNWP, TIWD
BBBB: CGWK, MNWP
CCCC: EXWE, HXWT, MDWE, MNWP, NDWK, TIWE
DDDD: MNWP
EEEE: EXWE, TIWE

Is there a way of merging this list in the format above? Either Excel or R (preferably Excel).

Solution

  • Using data.table

    library(data.table)
setDT(dat)[, list(Group=paste(Pub, collapse=", ")), by=Name][,paste(Name, Group, sep=": ")]
#[1] "AAAA: MNWP, TIWD"                                    
#[2] "BBBB: CGWK, MNWP"                                    
#[3] "CCCC: EXWE, EXWE, HXWT, MDWE, MDWE, MNWP, MNWP, NDWK"
#[4] "DDDD: MNWP"                                          
#[5] "EEEE: EXWE, TIWE"