Jill Sellum Jill Sellum - 1 month ago 14
R Question

reshape dataframe from columns to rows and collapse cell values

Here's the challenge i am facing. I am trying to transform this dataset

a b c
100 0 111
0 137 17
78 117 91


into (column to rows)

col1 col2
a 100,78
b 137,117
c 111,17,91


I know I can do this using
reshape
or
melt
function but I am not sure how to collapse and paste the cell values. Any suggestions or pointers is appreciated folks.

Answer

I would use dplyr rather than reshape.

library(dplyr)
library(tidyr)

Data <- data.frame(a=c(100,0,78),b=c(0,137,117),c=c(111,17,91))

Data %>%
  gather(Column, Value) %>%
  filter(Value != 0) %>%
  group_by(Column) %>%
  summarize(Value=paste0(Value,collapse=', '))

The gather function is similar to melt in reshape. The group_by function tells later functions that you want to seperate based off of values in Column. Finally summarize calculates whatever summary we want for each of the groups. In this case, paste all the terms together.

Which should give you:

# A tibble: 3 × 2
  Column       Value
   <chr>       <chr>
1      a     100, 78
2      b    137, 117
3      c 111, 17, 91