{"id":289,"date":"2017-05-04T11:49:12","date_gmt":"2017-05-04T18:49:12","guid":{"rendered":"https:\/\/blogs.ubc.ca\/yiwang28\/?p=289"},"modified":"2017-05-04T11:49:12","modified_gmt":"2017-05-04T18:49:12","slug":"my-r-learning-notes-quick-ways-to-aggregate-minutely-data-into-hourly-data","status":"publish","type":"post","link":"https:\/\/blogs.ubc.ca\/yiwang28\/2017\/05\/04\/my-r-learning-notes-quick-ways-to-aggregate-minutely-data-into-hourly-data\/","title":{"rendered":"My R learning notes: quick ways to aggregate minutely data into hourly data"},"content":{"rendered":"

Recently I ran into a stone wall when I tried to analyse the time series data from my first-year experiments.<\/p>\n

In my experiments, I measured tree sap flow velocity and different environmental controls such as temperature, solar radiation, relative humidity, wind speed and soil moisture. However, these data were at different time steps. The sap flow velocity were at a hourly basis, while the environmental controls were measured at either every 10 minutes or 20 minutes. So I had to aggregate those into the same time sequences.<\/p>\n

There are two types of aggregating data. The first is to assemble these data at every hour at each date, in order to make a correlation analysis or multiple linear regression; and the second is to average data which are measured at the same hour, disregarding the date, aiming at analyzing the 24-hour profile of the variables.<\/p>\n

Thanks to R, I succeeded in dealing with my four-month data at just ten seconds. Now, I’d like to share the codes with you, giving my soil moisture data as an example. \u00a0\ud83d\ude42<\/p>\n

    \n
  1. \n
    Making a 24-hour profile of soil moisture<\/span><\/h6>\n<\/li>\n<\/ol>\n

    Here is part of my soil moisture data (unit: VW%) at my Treatment 2 plot. I measured the soil moisture at two depth: Shallow ( at the 20 cm depth), and Deep (at the 40 cm depth).<\/p>\n

    \"\"<\/p>\n

    Fig. 1 Example of initial soil moisture data<\/p>\n

    (1) Save the excel file as *.csv, and import it into R Studio. In my case, I input my data as data frame named soilt2. Please remember to change the data type of Datetime column as Datetime, and turn Shallow column and Deep colunm as numeric.<\/p>\n

    \"\"<\/p>\n

    Fig. 2 Input soil moisture dataset<\/p>\n

    (2) Input code:<\/p>\n

    soilt2$Datetime1 <- as.POSIXlt(soilt2$Datetime)$hour<\/span> \u00a0### This will add a new colunm named Datetime1 in the soilt2 dataset. Datetime1 displayed below showed that only the hour was extracted from Datetime.<\/p>\n

    \"\"<\/p>\n

    Fig. 3 the added new colunm Datetime1 is showed on the right of the table.<\/p>\n

    soilt2 <- aggregate(cbind(Shallow, Deep) ~ Datetime1, data=soilt2, FUN=mean)<\/span> ### This averaged Shallow and Deep data into hourly data, \u00a0regardless of the date. In my case, I changed my original dataset. If you don’t want to make change of the original dataset, please give a new name to your new dataset, such as soilt3.<\/p>\n

    \"\"<\/p>\n

    Fig. 4 The changed soilt2 data frame.<\/p>\n

    Done! Yay!<\/p>\n

    \n

    2. Assemble the data at each hour at each date.<\/span><\/h6>\n

    (1) Import the dataset (the same as the first method).<\/p>\n

    (2) Input the following code<\/p>\n

    soilt2$Datetime2 <- droplevels(cut(soilt2$Datetime, breaks=”hour”))<\/span> ### This added a new colunm named Datetime2 in soilt2, which includes the date and the hour extracted from Datetime column.<\/p>\n

    \"\"<\/p>\n

    Fig. 5 The added new column Datetime2 is showed on the right of the table.<\/p>\n

    soilt3 <- aggregate(cbind(Shallow, Deep) ~ Datetime2, data=soilt2, FUN=mean)<\/span> ### This calculate the mean of data in each hour at each date.<\/p>\n

    \"\"<\/p>\n

    Fig. 6 The new soilt3 data frame.<\/p>\n

    Yay again! Done!<\/p>\n

    If you want to output these data into excel, you can load package (xlsx), and using the code of write.xlsx( \u00a0). \u00a0For example, I use the following code:<\/p>\n

    write.xlsx(soilc1, “c:\/Users\/Yi Wang\/Desktop\/soilc1.xlsx”)<\/span><\/p>\n

    There are definitely other ways to aggregate the data, but the codes that I put on this blog, in my opinion, is the most simple code. If you disagree with me, or you’d like to share your code, please feel free to leave your comments.<\/p>\n

    Thank you for reading!<\/p>\n

    May, 04, 2017<\/p>\n

     <\/p>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    Recently I ran into a stone wall when I tried to analyse the time series data from my first-year experiments. In my experiments, I measured tree sap flow velocity and different environmental controls such as temperature, solar radiation, relative humidity, wind speed and soil moisture. However, these data were at different time steps. The sap … Continue reading My R learning notes: quick ways to aggregate minutely data into hourly data<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":40393,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[67],"tags":[],"class_list":["post-289","post","type-post","status-publish","format-standard","hentry","category-blogs"],"_links":{"self":[{"href":"https:\/\/blogs.ubc.ca\/yiwang28\/wp-json\/wp\/v2\/posts\/289","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.ubc.ca\/yiwang28\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.ubc.ca\/yiwang28\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/yiwang28\/wp-json\/wp\/v2\/users\/40393"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/yiwang28\/wp-json\/wp\/v2\/comments?post=289"}],"version-history":[{"count":8,"href":"https:\/\/blogs.ubc.ca\/yiwang28\/wp-json\/wp\/v2\/posts\/289\/revisions"}],"predecessor-version":[{"id":305,"href":"https:\/\/blogs.ubc.ca\/yiwang28\/wp-json\/wp\/v2\/posts\/289\/revisions\/305"}],"wp:attachment":[{"href":"https:\/\/blogs.ubc.ca\/yiwang28\/wp-json\/wp\/v2\/media?parent=289"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.ubc.ca\/yiwang28\/wp-json\/wp\/v2\/categories?post=289"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.ubc.ca\/yiwang28\/wp-json\/wp\/v2\/tags?post=289"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}