Jill Sellum Jill Sellum - 9 months ago 53
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
function but I am not sure how to collapse and paste the cell values. Any suggestions or pointers is appreciated folks.

Answer Source

I would use dplyr rather than reshape.


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