combining columns into one row by comma delimiter and them grouping up to sum other column in R programming

Question

I want to transpose data in such a way that column becomes single rows

   col1    Col2                    Col3 Col4       Col5  
 1 344230. masalas & spices        4    14           2
 2 344231. hair care               4    14           1
 3 344231. otc                     4    14           1
 4 344231. personal hygiene        4    14           1
 5 344232. detergents              4    14           2
 6 344233. biscuits                4    14           2
 7 344233. chocolates & sweets     4    14           1
 8 344233. dry fruits              4    14           2

Output will be like

col1   Col2                                    Col5
344230 masalas & spices                        2
344231 hair care,otc,personal hygine           1+1+1=3
344232 detergent                               2
344233 biscuits,choclates&sweets,dry fruits    2+1+ 2=5

`library(dplyr); df %>% group_by(col1) %>% summarise(col2 = toString(Col2), Col5 = sum(Col5)) ` — Ronak Shah, Jul 27 '18 at 05:55

Lennyy · Answer 1 · 2018-07-27T06:01:16.537

  library(dplyr)
  df %>% 
  group_by(col1) %>% 
  mutate(Col5 = sum(Col5),
         Col2=paste(Col2,collapse=',')) %>% 
  slice(1)

    col1                                 Col2  Col3  Col4  Col5
   <dbl>                                <chr> <int> <int> <int>
1 344230                       masalas&spices     4    14     2
2 344231         haircare,otc,personalhygiene     4    14     3
3 344232                           detergents     4    14     2
4 344233 biscuits,chocolates&sweets,dryfruits     4    14     5

If you don't need Col3 and Col4 you could replace mutate by summarise and skip the slice(1).

Data:

df <-  read.table(text = "
col1    Col2                    Col3 Col4       Col5  
 1 344230. masalas&spices        4    14           2
 2 344231. haircare               4    14           1
 3 344231. otc                     4    14           1
 4 344231. personalhygiene        4    14           1
 5 344232. detergents              4    14           2
 6 344233. biscuits                4    14           2
 7 344233. chocolates&sweets     4    14           1
 8 344233. dryfruits              4    14           2    ", h = T)

combining columns into one row by comma delimiter and them grouping up to sum other column in R programming

1 Answers1