[R] Separating columns, and sorting by rows

[Gabor Grothendieck]

Try this:

Lines <- "    yyyy-mm     Rainfall(mm)
1   1977-02        17.4
2   1977-03        34.0
3   1977-04        26.2
4   1977-05        42.6
5   1977-06        58.6
6   1977-07        23.2
7   1977-08        26.8
8   1977-09        48.4
9   1977-10        47.0
10  1977-11        37.2
11  1977-12        15.0
12  1978-01         2.6
13  1978-02         6.8
14  1978-03         9.0
15  1978-04        46.6"

library(zoo)
z <- read.zoo(textConnection(Lines), FUN = as.yearmon)

sq <- seq(floor(start(z)), ceiling(end(z)) - 1/12, 1/12)
DF <- matrix(merge(z, zoo(, sq)), byrow = TRUE, nc = 12, dimnames =
list(unique(floor(sq)), month.abb))

The first statement reads the data into a zoo object z using yearmon
(year/month) time class.

yearmon objects represent Jan 1977 as 1977, Feb 1977 as 1977+1/12, Mar
1977 as 1977+2/12, ..., Dec 1977 as 1977+11/12, etc. so the floor and
ceiling operations on the start and end of the series can be used to
form a sequence from the beginning of the first year to the end of the
last.

Finally we merge z together with a zero width series (i.e. no data,
only times) and then form the result into a matrix with the
appropriate dimension names.

> DF
     Jan  Feb Mar  Apr  May  Jun  Jul  Aug  Sep Oct  Nov Dec
1977  NA 17.4  34 26.2 42.6 58.6 23.2 26.8 48.4  47 37.2  15
1978 2.6  6.8   9 46.6   NA   NA   NA   NA   NA  NA   NA  NA


On Mon, Feb 15, 2010 at 1:07 AM, RagingJim <nowa0021 at flinders.edu.au> wrote:
>
> Dear anyone who knows more about R than me (so everyone). I have been bashing
> my head on the keyboard all day trying to do something with my table.
>
> I have some data, like so:
>     yyyy-mm     Rainfall(mm)
> 1   1977-02        17.4
> 2   1977-03        34.0
> 3   1977-04        26.2
> 4   1977-05        42.6
> 5   1977-06        58.6
> 6   1977-07        23.2
> 7   1977-08        26.8
> 8   1977-09        48.4
> 9   1977-10        47.0
> 10  1977-11        37.2
> 11  1977-12        15.0
> 12  1978-01         2.6
> 13  1978-02         6.8
> 14  1978-03         9.0
> 15  1978-04        46.6
>
> The data continues on for x number of hundreds of data points. Simply put, I
> need to make that data.frame into this data.frame/table/matrix/grid/... you
> get the picture.
>
>         Jan  Feb  Mar ... etc
> Year   Rain Rain Rain
> Year   Rain Rain Rain
> Year   Rain Rain Rain
> Year   etc...
> Year
> Year
>
> How on earth do I do it? I have made little to no progress on it all day.
> Also, just like all the other problems, the year and month will change every
> time depending upon which csv file or sql query I load into the program. If
> anyone has any pointers, that would be awesome.
>
> Cheers lads.
> --
> View this message in context: http://n4.nabble.com/Separating-columns-and-sorting-by-rows-tp1555806p1555806.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>