CODEWITHSUNDEEP
r
Beta
Beta

Reputation: 1746

Transposing of Dataset

My dataset looks like this:

A   B   C   B   E
1   144 119 120 52
2   102 44  97  40
3   128 81  88  39

Now I want to transpose the dataset in the following format:

A   Vars    Values
1    B        43
2    B        78
3    B       110
1    C        46
2    C        49
3    C       130
1    B        39
2    B        86
3    B       143
1    E        59
2    E       134
3    E        49

But when I'm using the following code, one of the duplicate variable is not coming in the dataset.

df_transpose<-reshape2::melt(df,id.vars="A")

    A   Vars    Values
    1    B        43
    2    B        78
    3    B       110
    1    C        46
    2    C        49
    3    C       130
    1    E        59
    2    E       134
    3    E        49

I can't rename the duplicate variable "B" before transposing, as the location of B is dynamic. E.g. There might be 2 or 3 or more variables before "B". So, each time finding the location of "B", renaming it and then transposing is bit of a hastle.

Can anyone please help me to resolve the problem?

Thanks a lot!

Upvotes: 0

Views: 48

Answers (2)

jogo
jogo

Reputation: 12559

This is not pretty but it works:

data.frame(A=df$A, Vars=rep(names(df)[-1], each=nrow(df)), Values=c(as.matrix(df[-1])))

or:

data.frame(A=df$A, Vars=rep(names(df)[-1], each=nrow(df)), Values=stack(df[-1])[[1]])

Data used:

df <- read.table(header=TRUE, check.names=FALSE, text=
"A   B   C   B   E
1   144 119 120 52
2   102 44  97  40
3   128 81  88  39")

Upvotes: 1

Sotos
Sotos

Reputation: 51582

How about something like,

data.frame(df1$A, stack(df1[,-1]))
#   df1.A values ind
#1      1    144   B
#2      2    102   B
#3      3    128   B
#4      1    119   C
#5      2     44   C
#6      3     81   C
#7      1    120 B.1
#8      2     97 B.1
#9      3     88 B.1
#10     1     52   E
#11     2     40   E
#12     3     39   E

Upvotes: 3

Related Questions