Concatenate words based on groups

Question

I have a data set as below -

cust  cards
  a  ICICI
  a   HDFC
  a    PNB
  b  ICICI 
  b    PNB
  b   AMEX
  c    RCB
  c   AMEX
  d    PNB

I want one record for each customer so my output should be like

Cust new_cards
a     ICICI,HDFC,PNB
b     ICICI,PNB,AMEX

I am using following command -

x = group_by(data,cust)%>% mutate(new_cards=cat(cards,sep=","))

And getting error. Please let me know how can I get the desired output and if I can do this with function as well?

There is this previous answer: https://stackoverflow.com/questions/38514988/concatenate-strings-by-group-with-dplyr — Harlan Nelson, Apr 10 '18 at 13:17

score 1 · Answer 1 · answered Apr 10 '18 at 13:10

1

A slight variation from the previous answer using toString:

library(tidyverse);
df %>%
    group_by(cust) %>%
    summarise(new_cards = toString(cards));
## A tibble: 4 x 2
#  cust  new_cards
#  <fct> <chr>
#1 a     ICICI, HDFC, PNB
#2 b     ICICI, PNB, AMEX
#3 c     RCB, AMEX
#4 d     PNB

answered Apr 10 '18 at 13:10

Maurits Evers

49,617
4
47
68

Nice. According to the documentation, the specific purpose of `toString` is to produce a concise description of R objects so its use might be misleading. – Konrad Rudolph Apr 10 '18 at 13:12
@KonradRudolph `toString` actually just does a `paste(x, collapse = ", ")`, see `getAnywhere(toString.default)`. So your solution is cleaner;-) – Maurits Evers Apr 10 '18 at 13:17
Well (a) that’s only the default implementation and (b) it also limits the maximum width (if given). – Konrad Rudolph Apr 10 '18 at 13:18
@KonradRudolph I actually never noticed the option to limit its `width`. Good to know. – Maurits Evers Apr 10 '18 at 13:20

score 0 · Answer 2 · answered Apr 10 '18 at 13:09

The correct way of doing this is to use summarize, with paste:

data %>%
    group_by(cust) %>%
    summarize(new_cards = paste(cards, collapse = ', '))

On your example data, this results in

# A tibble: 4 x 2
  cust  new_cards
  <fct> <chr>
1 a     ICICI, HDFC, PNB
2 b     ICICI, PNB, AMEX
3 c     RCB, AMEX
4 d     PNB

The cat function misled you since it’s badly named: it doesn’t concatenate strings, it prints strings (to the terminal or a file).

Concatenate words based on groups

2 Answers2