How to concatenate values of a column B grouped by the other column A SQL and R

user_19923561 0
Viewed 54

I have this dataframe

df <- data.frame(scientific_name = c("Mandevilla grazielae",
                                     "Aosa parvifolia",
                                     "Mandevilla grazielae",
                                     "Dyckia ebracteata"), 
                 collection_number = c("254", "658","445", "568"))

And I'd like to combine the collection number of the plants in a column named "vouchers"

scientific_name       vouchers
Mandevilla grazielae  254, 445
Aosa parvifolia       658
Dyckia ebracteata     568

So far this is what I was able to do:

x <- sqldf('SELECT * FROM df GROUP BY scientific_name ORDER BY scientific_name ASC')

And then I started to learn more...

x <- sqldf('SELECT scientific_name, COUNT(collection_number) 
            FROM df GROUP BY scientific_name ORDER BY scientific_name ASC')

I'd like to know if there's something like the COUNT function above to concatenate the column "collection_number" in a "vouchers" column. I also tried CONCAT and it did't work.

I'd be very grateful!

dataframe r sql
2 Answers

In base R, use aggregate with paste

aggregate(cbind(vouchers = collection_number) ~ scientific_name, df, toString)

-output

        scientific_name vouchers
1      Aosa parvifolia      658
2    Dyckia ebracteata      568
3 Mandevilla grazielae 254, 445
user_37322710

1) Using the same setup as in the question use group_concat to concatenate all collection numbers associated with a scientific name. 1 means the first column in the output or write out the column name in its place, i.e. scientific_name.

library(sqldf)

sqldf("select scientific_name, group_concat(collection_number) as vouchers
  from df
  group by 1
  order by 1")

giving:

       scientific_name vouchers
1      Aosa parvifolia      658
2    Dyckia ebracteata      568
3 Mandevilla grazielae  254,445

2) Using tidyverse it would be done like this:

library(dplyr)

df %>%
  group_by(scientific_name) %>%
  summarize(vendors = toString(collection_number)) %>%
  ungroup %>%
  arrange(scientific_name)

giving:

# A tibble: 3 × 2
  scientific_name      vendors 
  <chr>                <chr>   
1 Aosa parvifolia      658     
2 Dyckia ebracteata    568     
3 Mandevilla grazielae 254, 445
user_5165480
Related