CODEWITHSUNDEEP
rdata.tablereshapereshape2
mukund
mukund

Reputation: 603

Transpose whole dataframe into one row dataframe- (or transposing each row of data.table and column binding)

I have tried to transform my_dataset with the help of library reshape & data.table in order to achieve the result.dataset but haven't been successful as yet.

I have a data table my_dataset that looks like this :- 

A    X     Count
id1  b     1
id1  c     2

And I want to have the result.dataset that should look like this :- 

 A   X1  Count1  X2  Count2  
 id1  b    1      c    2

It would be great if anyone could help me to get the result.dataset as above, preferably by using reshape or data.table (or both lib).

Upvotes: 2

Views: 256

Answers (3)

www
www

Reputation: 39154

We can aggregate the rows and use cSplit to split them.

library(data.table)
library(splitstackshape)

dat2 <- setDT(dat)[, lapply(.SD, paste, collapse = ","), by = A]

cols <- c(names(dat[, 1]), paste(names(dat[, -1]), 
                                 rep(1:nrow(dat), each = nrow(dat), 
                                 sep = "_"))

cSplit(dat2, splitCols = names(dat[, -1]))[, cols, with = FALSE]
#      A X_1 Count_1 X_2 Count_2
# 1: id1   b       1   c       2

DATA

dat <- read.table(text = "A    X     Count
id1  b     1
id1  c     2",
                  header = TRUE, stringsAsFactors = FALSE)

Upvotes: 1

IBrum
IBrum

Reputation: 345

Here's a solution that is using only reshape2 (trying to stick to the suggested packages). It starts by adding a column rep, that allows one to call dcast. 

require(reshape2)

#adding rep
my_dataset$rep = unlist(tapply(my_dataset$A, my_dataset$A, function(x)1:length(x)))

#cast at work
C1 = dcast(my_dataset, A ~ paste('X',rep, sep=''), value.var='X')
C2 = dcast(my_dataset, A ~ paste('Count',rep, sep=''), value.var='Count')

result.dataset = cbind(C1, C2[,-1])

The columns will not be in the same order as your example though.

Upvotes: 2

Rushabh Patel
Rushabh Patel

Reputation: 2764

Try this:

dt <- read.table(text = 'A    X     Count
id1  b     1
id1  c     2',header=T)
a <- aggregate(.~A, dt, paste, collapse=",")
library(splitstackshape)
result <- concat.split.multiple(data = a, split.cols = c("X","Count"), seps = ",")

output:

> result
A X_1 X_2 Count_1 Count_2
1: id1   b   c       1       2

Upvotes: 1

Related Questions