0

I would like to change this dataset

Date    A1  A2  A3  B1  B2  B3
1.4.04  5   1   2   10  13  4
2.4.04  2   2   0   11  12  5
3.4.04  4   4   3   14  15  8

to something like this

Date    A   B   C
1.4.04  5   10  1
1.4.04  1   13  2
1.4.04  2   4   3
2.4.04  2   11  1
2.4.04  2   12  2
2.4.04  0   5   3
3.4.04  4   14  1
3.4.04  4   15  2
3.4.04  3   8   3

Columns A1 to A3 are measurements from different stations and I want to put them in one column. The same for B. Column C will be the number of stations. How to do it in R?

Here is a reproducible example of the data.frame:

mydf <- structure(
  list(Date = structure(1:3, .Label = c("1.4.04", "2.4.04",  "3.4.04"), 
                        class = "factor"), 
       A1 = c(5L, 2L, 4L), A2 = c(1L,  2L, 4L), A3 = c(2L, 0L, 3L), 
       B1 = c(10L, 11L, 14L), B2 = c(13L,  12L, 15L), B3 = c(4L, 5L, 8L)), 
  .Names = c("Date", "A1", "A2",  "A3", "B1", "B2", "B3"), 
  row.names = c(NA, 3L), class = "data.frame")
A5C1D2H2I1M1N2O1R2T1
  • 190,393
  • 28
  • 405
  • 485
user3624251
  • 185
  • 1
  • 8

2 Answers2

3

This is a classic "wide" to "long" reshape problem (and your data--at least the sample data--are in a good form to do the conversion quite easily):

reshape(mydf, direction = "long", idvar="Date", 
        varying = 2:ncol(mydf), sep = "", timevar="C")
#            Date C A  B
# 1.4.04.1 1.4.04 1 5 10
# 2.4.04.1 2.4.04 1 2 11
# 3.4.04.1 3.4.04 1 4 14
# 1.4.04.2 1.4.04 2 1 13
# 2.4.04.2 2.4.04 2 2 12
# 3.4.04.2 3.4.04 2 4 15
# 1.4.04.3 1.4.04 3 2  4
# 2.4.04.3 2.4.04 3 0  5
# 3.4.04.3 3.4.04 3 3  8
A5C1D2H2I1M1N2O1R2T1
  • 190,393
  • 28
  • 405
  • 485
  • I tried the reshape and at first it did not work but than I checked the `?reshape` and I realised that I have different variable names in my example which I provided and my real data. So if I want to use your code it is important to have variable names in the form of A1 to A3 and not something like SL_T_mean and SP_T_mean and so on. I also found a good example for reshape here [link]http://www.ats.ucla.edu/stat/r/faq/reshape.htm, but it is easer to change my variable names according to my example and use your code. – user3624251 May 14 '14 at 14:01
  • 1
    @user3624251, yes. That is one of the better resources available. I've also [posted some examples on my blog](http://news.mrdwab.com/?s=reshape), and there are also some good examples at [Tyler Rinker's blog](https://trinkerrstuff.wordpress.com/?s=reshape+%28from+base%29). – A5C1D2H2I1M1N2O1R2T1 May 14 '14 at 15:29
1

Does that work ? (replacing 'table' by the name of your dataset)

m1<-as.matrix(t(table[2:4]))
A<-as.vector(m1)
m2<-as.matrix(t(table[5:7]))
B<-as.vector(m2)

Date<-sort(rep(table$Date,3))
C<-rep(1:3,dim(table)[1])

output<-data.frame(Date,A,B,C)

If it doesn't, please provide reproducible example.

Vincent
  • 955
  • 2
  • 15
  • 32
  • I have an error after entering `Date<-sort(rep(table$Date),3)` : Error in sort(rep(pokus$Date), 3) : 'decreasing' must be a length-1 logical vector. Did you intend to set 'partial'? – user3624251 May 13 '14 at 15:39
  • 1
    presumably should be `sort(rep(table$Date,3))` ? – Ben Bolker May 13 '14 at 15:51
  • here is an reproducible exmaple `structure(list(Date = structure(1:3, .Label = c("1.4.04", "2.4.04", "3.4.04"), class = "factor"), A1 = c(5L, 2L, 4L), A2 = c(1L, 2L, 4L), A3 = c(2L, 0L, 3L), B1 = c(10L, 11L, 14L), B2 = c(13L, 12L, 15L), B3 = c(4L, 5L, 8L)), .Names = c("Date", "A1", "A2", "A3", "B1", "B2", "B3"), row.names = c(NA, 3L), class = "data.frame")` I used `dput()` according to this link [link]http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example I hope it works. – user3624251 May 13 '14 at 15:53
  • Thanks for that. Could you please put it in the original post so users can see it quickly when they arrive at this page? – Rich Scriven May 13 '14 at 16:01
  • Ben is right of course (thanks). I edited my code. I am no longer on a computer with R installed so I can't use your example to see if it works now... Does it ? – Vincent May 13 '14 at 16:06
  • @Vincent, you should look into [the `reshape` function](http://stackoverflow.com/a/23644617/1270695) in base R. – A5C1D2H2I1M1N2O1R2T1 May 14 '14 at 02:24
  • Avoid using `table` as a variable name. It is the name of a function in `base`. – Matthew Lundberg May 14 '14 at 02:25
  • @AnandaMahto : yes I just saw your answer, seems like a nice function to me. Thanks ! – Vincent May 14 '14 at 06:42
  • @MatthewLundberg : Indeed. I don't use 'table' as a name in my personnal code but here I knew the OP was going to change it to the name he used and it's a convenient and non ambiguous name for such examples. – Vincent May 14 '14 at 06:42