CODEWITHSUNDEEP
r
paru80
paru80

Reputation: 93

how to group and concatenate

I have a data which I need to group on column ID and then concatenate the rows.

Current Data

ID   Name   Diagnosis    Medicine
1    A      a            a
1    A      b            a
1    A      c            b 
2    B      d            b
2    B      e            c
3    C      f            d

Result Expected

ID   Name   Diagnosis    Medicine
1    A      a|b|c         a|a|b
2    B      d|e           b|c
3    C      f             d

Code written till now is,

diagnoses<-read_xlsx("excel file.xlsx")
diagnoses <-diagnoses[,c( "ID" ,"Name" ,"Diagnosis" ,"Medicine"),] head(diagnoses)
aggregate(cbind(Diagnosis, Medicine)~ID, df, paste0, collapse = '|') write.xlsx(diagnoses, "Diagnoses.xlsx")

I have integer variables, bit variable, nvarchar variable

Upvotes: 0

Views: 61

Answers (2)

U13-Forward
U13-Forward

Reputation: 71610

Try groupby with mutate:

df %>% 
  group_by(ID, Name) %>% 
  summarize(Diagnosis = paste0(Diagnosis, collapse = "|"),
            Medicine = paste0(Medicine, collapse = "|"))

Output:

     ID Name  Diagnosis Medicine
  <int> <chr> <chr>     <chr>   
1     1 A     a|b|c     a|a|b   
2     2 B     d|e       b|c     
3     3 C     f         d

Upvotes: 2

Ronak Shah
Ronak Shah

Reputation: 389175

If you read the data in diagnoses you should use the same variable in aggregate function in place of df. Another important thing is to save the output in a variable after running the function.

Try -

diagnoses<- readxl::read_xlsx("excel file.xlsx")
result <- aggregate(cbind(Diagnosis, Medicine)~ID, diagnoses,paste0, collapse = '|')
openxlsx::write.xlsx(result, "Diagnoses.xlsx")

Upvotes: 0

Related Questions