3

I have a data frame of names ('Name'), grouped by 'Dept':

 Dept Date      Name            
----- --------- --------------- 
   30 07-DEC-02 Raphaely        
   30 18-MAY-03 Khoo            
   40 07-JUN-02 Mavris          
   50 01-MAY-03 Kaufling        
   50 14-JUL-03 Ladwig          
   70 07-JUN-02 Baer            
   90 13-JAN-01 De Haan
   90 17-JUN-03 King  
  100 16-AUG-02 Faviet
  100 17-AUG-02 Greenberg 
  110 07-JUN-02 Gietz           
  110 07-JUN-02 Higgins

I want to concatenate the 'Name' column by 'Dept' and add the result to the original data. The desired result is the 'Emp_list' column:

 Dept Date      Name            Emp_list
----- --------- --------------- ---------------------------------------------
   30 07-DEC-02 Raphaely        Raphaely; Khoo
   30 18-MAY-03 Khoo            Raphaely; Khoo
   40 07-JUN-02 Mavris          Mavris
   50 01-MAY-03 Kaufling        Kaufling; Ladwig
   50 14-JUL-03 Ladwig          Kaufling; Ladwig
   70 07-JUN-02 Baer            Baer
   90 13-JAN-01 De Haan         De Haan; King
   90 17-JUN-03 King            De Haan; King
  100 16-AUG-02 Faviet          Faviet; Greenberg
  100 17-AUG-02 Greenberg       Faviet; Greenberg
  110 07-JUN-02 Gietz           Gietz; Higgins
  110 07-JUN-02 Higgins         Gietz; Higgins

Any advice?

Henrik
  • 65,555
  • 14
  • 143
  • 159
duber
  • 2,769
  • 4
  • 24
  • 32
  • possible duplicate of [Find repeated data from index and string it together](http://stackoverflow.com/questions/11550889/find-repeated-data-from-index-and-string-it-together) – Thomas Dec 11 '13 at 19:07
  • Also http://stackoverflow.com/questions/16596515/aggregating-by-unique-identifier-and-concatenating-related-values-into-a-string/16596601 – Thomas Dec 11 '13 at 19:08
  • 1
    @Thomas, in defense of the OP, those are aggregate type questions (consolidating rows) while this is not. – A5C1D2H2I1M1N2O1R2T1 Dec 11 '13 at 19:11

2 Answers2

8

You can use ave and paste:

within(mydf, {
  Emp_list <- ave(Name, Dept, FUN = function(x) paste(x, collapse = "; "))
})
#   Dept      Date      Name          Emp_list
# 1    30 07-DEC-02  Raphaely    Raphaely; Khoo
# 2    30 18-MAY-03      Khoo    Raphaely; Khoo
# 3    40 07-JUN-02    Mavris            Mavris
# 4    50 01-MAY-03  Kaufling  Kaufling; Ladwig
# 5    50 14-JUL-03    Ladwig  Kaufling; Ladwig
# 6    70 07-JUN-02      Baer              Baer
# 7    90 13-JAN-01   De Haan     De Haan; King
# 8    90 17-JUN-03      King     De Haan; King
# 9   100 16-AUG-02    Faviet Faviet; Greenberg
# 10  100 17-AUG-02 Greenberg Faviet; Greenberg
# 11  110 07-JUN-02     Gietz    Gietz; Higgins
# 12  110 07-JUN-02   Higgins    Gietz; Higgins
A5C1D2H2I1M1N2O1R2T1
  • 190,393
  • 28
  • 405
  • 485
1

Or plyr:

gr<-read.csv("gr.csv")
require(plyr)
merge(gr,ddply(gr,.(Dept),summarise,Emp_List=paste0(Name,collapse="; ")),by="Dept")

Dept      Date      Name          Emp_List
1    30 07-DEC-02  Raphaely    Raphaely; Khoo
2    30 18-MAY-03      Khoo    Raphaely; Khoo
3    40 07-JUN-02    Mavris            Mavris
4    50 01-MAY-03  Kaufling  Kaufling; Ladwig
5    50 14-JUL-03    Ladwig  Kaufling; Ladwig
6    70 07-JUN-02      Baer              Baer
7    90 13-JAN-01   De Haan     De Haan; King
8    90 17-JUN-03      King     De Haan; King
9   100 16-AUG-02    Faviet Faviet; Greenberg
10  100 17-AUG-02 Greenberg Faviet; Greenberg
11  110 07-JUN-02     Gietz    Gietz; Higgins
12  110 07-JUN-02   Higgins    Gietz; Higgins
Troy
  • 8,581
  • 29
  • 32