CODEWITHSUNDEEP
rtransformationreshape2melt
Jill Sellum
Jill Sellum

Reputation: 329

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.

Upvotes: 0

Views: 818

Answers (3)

dww
dww

Reputation: 31452

With library(data.table)

melt(dt)[, .(value = paste(value[value !=0], collapse=', ')), by=variable]
#  variable       value
# 1:      a     100, 78
# 2:      b    137, 117
# 3:      c 111, 17, 91

The data:

dt = fread("a    b     c
100  0     111
0    137   17
78   117   91")

Upvotes: 0

Bishops_Guest
Bishops_Guest

Reputation: 1472

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

Upvotes: 1

akuiper
akuiper

Reputation: 214957

Here is a light weight option using toString() method to collapse each column to a string and using stack() to reshape the result list to your desired output:

stack(lapply(df, function(col) toString(col[col!=0])))

#       values ind
#1     100, 78   a
#2    137, 117   b
#3 111, 17, 91   c

Upvotes: 2

Related Questions