0

I know there are a lot of similiar questions about this topic. But nevertheless I am not able to solve my problem.

I want to reshape a dataset of this structure:

Country Variable    2001    2002    2003
A   Demand  8   46  776
A   Supply  4576    576 576
A   Storage 5   765 765
B   Demand  7543    76  58
B   Supply  58  5   65
B   Storage 864 3   537
C   Demand  6   48  65
C   Supply  75  86  458
C   Storage 587 58  5

to this structure (long format):

Country Year  Demand  Supply  Storage
...

I tried this command:

a<-reshape(test1, direction="long", varying=list(names(test1)[3:ncol(test1)]),   
v.names="Value", idvar="Time", timevar="Year", times=names(test1)[3:ncol(test1)])

but it is not (exactly) working:

> edit(a)
 Country Variable Year Value Time
 1.X2001 A Demand X2001 8 1
 2.X2001 A Supply X2001 4576 2
 3.X2001 A Storage X2001 5 3
 4.X2001 B Demand X2001 7543 4
 5.X2001 B Supply X2001 58 5
 6.X2001 B Storage X2001 864 6
 7.X2001 C Demand X2001 6 7
 8.X2001 C Supply X2001 75 8
 9.X2001 C Storage X2001 587 9
 1.X2002 A Demand X2002 46 1
 2.X2002 A Supply X2002 576 2
 3.X2002 A Storage X2002 765 3
 4.X2002 B Demand X2002 76 4
 5.X2002 B Supply X2002 5 5
 6.X2002 B Storage X2002 3 6
 7.X2002 C Demand X2002 48 7
 8.X2002 C Supply X2002 86 8
 9.X2002 C Storage X2002 58 9
 1.X2003 A Demand X2003 776 1
 2.X2003 A Supply X2003 576 2
 3.X2003 A Storage X2003 765 3
 4.X2003 B Demand X2003 58 4
 5.X2003 B Supply X2003 65 5
 6.X2003 B Storage X2003 537 6
 7.X2003 C Demand X2003 65 7
 8.X2003 C Supply X2003 458 8
 9.X2003 C Storage X2003 5 9

Can you help me? Where is my mistake?

Daniel Ryback
  • 381
  • 2
  • 4
  • 15

1 Answers1

2
DF <- read.table(text="Country Variable    2001    2002    2003
A   Demand  8   46  776
A   Supply  4576    576 576
A   Storage 5   765 765
B   Demand  7543    76  58
B   Supply  58  5   65
B   Storage 864 3   537
C   Demand  6   48  65
C   Supply  75  86  458
C   Storage 587 58  5", header=TRUE, check.names=FALSE)

library(reshape2)
DFlong <- melt(DF, id.vars=c("Country", "Variable"), variable.name="Year")
DFwide <- dcast(DFlong, Country+Year~Variable)

#   Country Year Demand Storage Supply
# 1       A 2001      8       5   4576
# 2       A 2002     46     765    576
# 3       A 2003    776     765    576
# 4       B 2001   7543     864     58
# 5       B 2002     76       3      5
# 6       B 2003     58     537     65
# 7       C 2001      6     587     75
# 8       C 2002     48      58     86
# 9       C 2003     65       5    458
Roland
  • 5,758
  • 1
  • 28
  • 60
  • Thanks for your reply. But using your code, I optain the following: `> edit(DFlong) Country Variable Year value 1 A Demand 2001 8 2 A Supply 2001 4576 3 A Storage 2001 5 4 B Demand 2001 7543 ...` there are still no seperated columns for demand, supply and storage – Daniel Ryback Mar 25 '14 at 16:27
  • If the code doesn't work with your data, your data isn't what I assumed it to be. That's why on Stack Overflow (where this would be more on-topic) we ask for a reproducible example, e.g., the output of `dput(DF)`. – Roland Mar 25 '14 at 18:22