Concatenate values from one column per group

Question

I want to concatenate values per group that are now in one column. The following is a short version of the data frame I want to wrangle.

library(tidyverse)

df <- tibble::tribble(
  ~county,  ~party,
      "A",   "VVD",
      "A",    "GL",
      "A", "Local",
      "B",   "D66",
      "B", "Local"
  )

Now I want to create one row per county with all the parties in their own column:

df2 <- tibble::tribble(
  ~county, ~party1, ~party2, ~party3,
      "A",   "VVD",    "GL", "Local",
      "B",   "D66", "Local",      NA
  )

To later concatenate with unite() and replace the underscores for comma's and Remove the NA's.

df2 %>%
  unite(party, c("party1", "party2", "party3")) %>%
  mutate(party = gsub("_NA", "", party),
         party = gsub("_", ", ", party))

My desired df output:

  county party         
  <chr>  <chr>         
1 A      VVD, GL, Local
2 B      D66, Local

score 1 · Answer 1 · answered Sep 05 '18 at 14:47

We can do this by creating a sequence column and spread

library(tidyverse)
df %>%
   group_by(county) %>% 
   mutate(v1 = paste0('party', row_number())) %>% 
   spread(v1, party)
# A tibble: 2 x 4
# Groups:   county [2]
#  county party1 party2 party3
#  <chr>  <chr>  <chr>  <chr> 
#1 A      VVD    GL     Local 
#2 B      D66    Local  <NA>

For the second output, we group by 'county' and paste the elements of 'party

df %>%
  group_by(county) %>%
  summarise(party = toString(party))
# A tibble: 2 x 2
#  county party         
#  <chr>  <chr>         
#1 A      VVD, GL, Local
#2 B      D66, Local

Andre Elrico · Answer 2 · 2018-09-06T06:43:13.907

0

df %>%
    group_by(county) %>%
    dplyr::summarise( paste0(party, collapse = ", "))

You should prompt ?group_by (?paste0 .. etc) to R console if anything is unclear.

edited Sep 06 '18 at 06:43

answered Sep 05 '18 at 14:50

Andre Elrico

10,956
6
50
69

1

Code only answers are meh, try to add supporting language to make it more helpful for the asker and future visitors who might not be `dplyr` wizards – Nate Sep 05 '18 at 15:13
Just added @Nate :-) – Andre Elrico Sep 06 '18 at 06:44

Concatenate values from one column per group

2 Answers2