[R] R - Aggregate 3-Hourly Block Data into Weekly (Melt)

ONKELINX, Thierry Thierry.ONKELINX at inbo.be
Fri Dec 19 10:31:21 CET 2014


You are looking for the round_date(), floor_date() or ceiling_date() functions from the lubridate package. Those functions can round timestamps to weeks.

ir. Thierry Onkelinx
Instituut voor natuur- en bosonderzoek / Research Institute for Nature and Forest
team Biometrie & Kwaliteitszorg / team Biometrics & Quality Assurance
Kliniekstraat 25
1070 Anderlecht
Belgium
+ 32 2 525 02 51
+ 32 54 43 61 85
Thierry.Onkelinx op inbo.be
www.inbo.be

To call in the statistician after the experiment is done may be no more than asking him to perform a post-mortem examination: he may be able to say what the experiment died of.
~ Sir Ronald Aylmer Fisher

The plural of anecdote is not data.
~ Roger Brinner

The combination of some data and an aching desire for an answer does not ensure that a reasonable answer can be extracted from a given body of data.
~ John Tukey

-----Oorspronkelijk bericht-----
Van: R-help [mailto:r-help-bounces op r-project.org] Namens Shouro Dasgupta
Verzonden: donderdag 18 december 2014 16:13
Aan: r-help op r-project.org
Onderwerp: [R] R - Aggregate 3-Hourly Block Data into Weekly (Melt)

I am trying to compute max, min, and mean from Global Circulation Models
(GCM) for the US. The data is in 3-hour blocks for 2026-2045 and 2081-2100.
Sample Data:

tmp1 <- structure(list(FIPS = c(1001L, 1003L, 1005L), X2026.01.01.1 = c(285.5533142,
  285.5533142, 286.2481079), X2026.01.01.2 = c(283.4977112, 283.4977112,
  285.0860291), X2026.01.01.3 = c(281.9733887, 281.9733887, 284.1548767
  ), X2026.01.01.4 = c(280.0234985, 280.0234985, 282.6075745),
      X2026.01.01.5 = c(278.7125854, 278.7125854, 281.2553711),
      X2026.01.01.6 = c(278.5204773, 278.5204773, 280.6148071)), .Names = c("FIPS",
  "X2026.01.01.1", "X2026.01.01.2", "X2026.01.01.3", "X2026.01.01.4",
  "X2026.01.01.5", "X2026.01.01.6"), class = "data.frame", row.names = c(NA,
  -3L))

I have extracted the data by FIPS code and reshaped the yearly data files using melt();

for (i in filelist) {
  tmp1 <- as.data.table(read.csv(i,header=T, sep=","))
  tmp2 <- melt(tmp1, id="FIPS")
  tmp2$year <- as.numeric(substr(tmp2$variable,2,5))
  tmp2$month <- as.numeric(substr(tmp2$variable,7,8))
  tmp2$day <- as.numeric(substr(tmp2$variable,10,11))}

I have added datestring and weekdays using the following code:
Inserting Date Variable

tmp2$date <- with(tmp2, ymd(sprintf('%04d%02d%02d', year, month, day)))

Inserting Day Variable

tmp2$day <- weekdays(as.Date(tmp2$date))

sample.tmp2 <- "FIPS         xdate     temp year month      day
date      dates weekdays
+ 5599311  1003 X2045.08.14.2 304.5995 2045     8   Monday 2045-08-14
2036-01-29        2
+ 468406  39093 X2045.01.19.7 267.8483 2045     1 Thursday 2045-01-19
2028-06-04        0
+ 5022078 21167 X2045.07.21.8 314.6772 2045     7   Friday 2045-07-21
2035-09-13        4
+ 186822   9005 X2045.01.08.5 269.0803 2045     1   Sunday 2045-01-08
2037-06-28        0
+ 3998678 13295 X2045.06.10.7 307.2408 2045     6 Saturday 2045-06-10
2033-10-13        4"

Data <- read.table(text=sample.tmp2, header = TRUE)

My goal is to aggregate these 3-hourly blocks into weekly data, however, GCM data is not consistent and the blocks vary between 7 and 8. I want to clip the data to start on the first Monday of 2026 and end on the last Sunday of 2045 and then use rep() to assign week numbers for the whole epoch.

I know I can count the number of each day using something like this;

length(which(weekdays == '0'))

Where 0, 1, 2..., 6 represent Sunday, Monday,...

My question is am I doing anything wrong in trying to aggregate the data to begin with? But importantly, I would be grateful for any help to clip the dataset to begin on the first Monday and end on the last Sunday. Thank you very much!

        [[alternative HTML version deleted]]

______________________________________________
R-help op r-project.org mailing list -- To UNSUBSCRIBE and more, see 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.
Disclaimer Bezoek onze website / Visit our website<https://drupal.inbo.be/nl/disclaimer-mailberichten-van-het-inbo>



More information about the R-help mailing list