My R learning notes: quick ways to aggregate minutely data into hourly data

blogs

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 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.

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.

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.  🙂

  1. Making a 24-hour profile of soil moisture

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).

Fig. 1 Example of initial soil moisture data

(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.

Fig. 2 Input soil moisture dataset

(2) Input code:

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

Fig. 3 the added new colunm Datetime1 is showed on the right of the table.

soilt2 <- aggregate(cbind(Shallow, Deep) ~ Datetime1, data=soilt2, FUN=mean) ### This averaged Shallow and Deep data into hourly data,  regardless 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.

Fig. 4 The changed soilt2 data frame.

Done! Yay!

2. Assemble the data at each hour at each date.

(1) Import the dataset (the same as the first method).

(2) Input the following code

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

Fig. 5 The added new column Datetime2 is showed on the right of the table.

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

Fig. 6 The new soilt3 data frame.

Yay again! Done!

If you want to output these data into excel, you can load package (xlsx), and using the code of write.xlsx(  ).  For example, I use the following code:

write.xlsx(soilc1, “c:/Users/Yi Wang/Desktop/soilc1.xlsx”)

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.

Thank you for reading!

May, 04, 2017

 

 

88 thoughts on “My R learning notes: quick ways to aggregate minutely data into hourly data”

  1. Hi there,

    Thanks for your code.
    Quick question. Do you know why when I input your line:
    soilt2$Datetime2 <- droplevels(cut(soilt2$Datetime, breaks=”hour”))
    which translates in my code to:
    Site27_summerBB$datetime2 <- droplevels(cut(Site27_summerBB$datetime, breaks=”hour”))

    I get:
    Error: unexpected input in "Site27_summerBB$datetime2 <- droplevels(cut(Site27_summerBB$datetime, breaks=”"

    It appears that it doesn't like "hour"

    Many Thanks,

    Jenni

    Reply

    1. Oh, totally realized why as soon as I posted it typical! haha

      change from this:
      soilt2$Datetime2 <- droplevels(cut(soilt2$Datetime, breaks=”hour”))

      to this:
      soilt2$Datetime2 <- droplevels(cut(soilt2$Datetime, breaks='hour'))

      Thanks

      Reply

  3. Hi Yi

    Thanks for sharing. I have some half-yearly electricity demand data and I wish to convert it into time series object. The data is between 2014-2017, therefore, includes the leap year 2016. I am not sure how to convert it to a time series object

    Reply

    1. Hi Heidi,

      Thanks for reading the post! The gap should not be an issue if u use the codes in this post. Could you please let me know how you would like to convert?

      Reply

  4. Thank you for this very helpful post!

    When I want to break into hour: data.merged_wake_sleep$Datetime2 <- droplevels(cut(data.merged_wake_sleep$absolute_Date, breaks= 'hour'))

    I get the following error message: Error in cut.default(data.merged_wake_sleep$absolute_Date, breaks = "hour") :
    'x' must be numeric.

    Anyone familiar with this?

    Best,
    Lea

    Reply

    1. Hi Lea,

      Thanks for reading it! Maybe try this:

      data.merged_wake_sleep$absolute_Date1 <- as.numeric(data.merged_wake_sleep$absolute_Date) # turn your raw data into numeric data data.merged_wake_sleep$Datetime2 <- droplevels(cut(data.merged_wake_sleep$absolute_Date1, breaks= 'hour')) Please let me know if it works! Thank you!

      Reply

  5. Hello,

    Thank you for this very helpful article!

    I came across the same issue as Lea, and tried to use the fix suggested but received the following error message:
    Error in breaks + 1 : non-numeric argument to binary operator

    When I take a look at my table, the new column I created with the as.numeric from the date/time column was simply numbered 1-12874 (number of values), rather than keeping any date/time format.

    Any ideas as to why this might be?

    Reply

  9. This is really a extremely beneficial read for me, Have to admit you might be 1 in the most effective bloggers and my seotons I ever saw.Thanks for posting this informative article.

    Reply

  10. Thanks, A Lot…
    Please don’t stop your Blogging and continue posting such solution..
    It helps a lot.

    Reply

  11. What a nice article i found , i was looking it for a long time , i been searching it for so long now that i found out really help me.
    We must be more passionate about everything espeCially when it comes in our nature and environment check this out also >>
    카지노사이트

    Reply

  14. Thank you so much for the article ! Simple, efficient and very clear. After hours spent to look for a solution, your article was a great help for me.

    Reply

  16. Your writing is perfect and complete. However, I think it will be more wonderful if your post includes additional topics that I am thinking of. I have a lot of posts on my site similar to your topic. Would you like to visit once? keonhacai

    Reply

  17. This is very sharp and straight to the point. Been struggling with the complicated posts on stack overflow. Thank you for this!

    Reply

  32. Looking to upgrade your smart device game? Look no further than our comprehensive list of the Best Smart Accessories! From smartwatches to Bluetooth headphones, we’ve got you covered with the latest and greatest in tech accessories. Discover the ultimate add-ons that will take your device experience to the next level. Don’t settle for mediocre accessories – read our guide and step up your smart device game today!
    t500 smart watch price in pakistan

    Reply

  33. At World Counseling, we believe in the power of personal growth, self-discovery, and well-being. Our mission is to provide unique and effective counseling services to individuals seeking support, guidance, and transformation.
    https://worldcounsling.com/

    Reply

  34. We have thousands of happy customers whom we have helped with our experience and professional team. Nothing is worth more than our relationship of trust with our well-esteemed customers, so our priority is to provide risk-free performance with high satisfaction. The Dream Cool AC Service team is highly equipped and ensures top-notch work delivery.
    AC installation in dubai

    Reply

  41. Nice post. I found this to be an informative and interesting post, so I think it is very useful and knowledgeable. I am glad to read this post and hope your next article is are so useful for me so you are meeting in the next process for more information click this link below: –
    calculating wpm typing

    ROHIT

    Reply

  47. comprar carta de conduçao preço, comprar carta de condução verdadeira, comprar carta de conduçao, comprar carta de condução lisboa, comprar carta de condução legal, comprar carta de condução, carta de condução comprar, comprar carta de conduçao, comprar carta de condução em Portugal, comprar carta, comprar carta de condução portugal, comprar carta de condução online, comprar a carta de condução, carta de condução, comprar carta de carro, imt carta de condução, comprar carta de condução no porto

    ;emw

    Reply

  48. gdzie mozna kupic prawo jazdy z wpisem do rejestru, kupić prawo jazdy, legalne prawo jazdy do kupienia, kupię prawo jazdy, jak załatwić prawo jazdy, bez egzaminu, jak kupić prawo jazdy, czy można kupić prawo jazdy, legalne prawo jazdy do kupienia 2022, pomogę zdać egzamin na prawo jazdy, prawo jazdy bez egzaminu, gdzie kupić prawo jazdy bez egzaminu, gdzie kupić prawo jazdy na lewo, jak kupić prawo jazdy w niemczech, gdzie kupic prawo jazdy legalnie, kupić prawo jazdy b

    ;lkw

    Reply

  49. sportbootführerschein binnen und see, sportbootführerschein binnen prüfungsfragen, sportbootführerschein binnen kosten, sportbootführerschein binnen online, sportbootführerschein binnen wo darf ich fahren, sportbootführerschein binnen berlin, sportbootführerschein binnen segel, sportbootführerschein kaufen, sportbootführerschein kaufen erfahrungen, sportbootführerschein kaufen schwarz, sportbootführerschein see kaufen, sportbootführerschein binnen kaufen, sportbootführerschein see kaufen ohne prüfung, bootsführerschein kaufen, bootsführerschein kaufen polen, bootsführerschein kaufen erfahrungen, bootsführerschein online kaufen, bootsführerschein tschechien kaufen.

    mw

    Reply

  50. acheter permis de conduire en ligne, acheter un permis de conduire belge, achat permis de conduire, acheter un permis de conduire, acheter permis de conduire belgique, acheter le permis de conduire, permis de conduire acheter, faux permis de conduire belge, j’ai acheter mon permis de conduire sur internet, acheter son permis de conduire belgique, acheter son permis de conduire légalement, acheter un vrai permis de conduire, acheter permis moto a2, acheter permis moto étranger, Acheter permis de conduire enregistré, acheter permis de conduire enregistré en préfecture forum, permis de conduire légalement enregistré.

    ;lkks

    Reply

  51. lkw führerschein kaufen legal, führerschein kaufen ohne vorkasse, registrierten führerschein kaufen erfahrungen, führerschein kaufen erfahrungen, führerschein kaufen ohne prüfung Köln, führerschein kaufen österreich, führerschein kaufen ohne prüfung österreich, führerschein kaufen ohne prüfung österreich, führerschein kaufen in österreich, führerschein kaufen Frankfurt, führerschein kaufen schweiz.

    jjb

    Reply

  52. Comprare patente registrata presso Motorizzazione civile (DMV)? La decisione di comprare patente online in Italia , comprare una patente, patente originale, comprare patente c, acquisto patente b, comprare patente prezzo, compro patente, acquistare patente b, dove posso comprare la patente b, compra patente online, comprare patente b online, comprare la patente a napoli, dove si può comprare la patente, quanto costa comprare la patente, comprare patente di guida, comprare patente senza.

    mnbj

    Reply

  53. Dramacool: Your go-to spot for Asian entertainment with English subs. Catch the hottest Korean, Chinese, Taiwanese, Thai, and Hong Kong dramas, movies, and shows.

    Reply

  54. Interesting read! Your post offers a fresh perspective that challenges conventional thinking. Looking forward to more thought-provoking content from you.

    Reply

  55. Without a doubt, a terrific effort! It’s encouraging to see such well-thought-out and articulate ideas written down. The writing was not only enjoyable, but also excellent. For additional information and expert assistance, please visit SEO News.

    Reply

  56. Se você se deparar com esta mensagem buscando comprar uma carteira de motorista registrada sem os exames então estamos aqui para ajudá-lo, basta clicar no link abaixo para falar com um agente. o agente irá orientá-lo sobre como obter a carteira de motorista

    Reply

  57. Als u dit bericht tegenkomt waarin u probeert een geregistreerd rijbewijs te kopen zonder de examens, dan staan ​​wij voor u klaar. Klik eenvoudig op de onderstaande link om met een agent te praten. de agent zal u begeleiden bij het verkrijgen van het rijbewijs

    Reply

  58. Se ti imbatti in questo messaggio che cerca di acquistare una patente di guida registrata senza gli esami, allora siamo qui per te, fai semplicemente clic sul collegamento in basso per parlare con un agente. l’agente ti guiderà su come ottenere la patente di guida

    Reply

  59. Se ti imbatti in questo messaggio che cerca di acquistare una patente di guida registrata senza gli esami, allora siamo qui per te, fai semplicemente clic sul collegamento in basso per parlare con un agente. l’agente ti guiderà su come ottenere la patente di guida

    Reply

  60. Wenn Sie auf diese Nachricht stoßen und versuchen, einen registrierten Führerschein ohne Prüfungen zu kaufen, dann sind wir für Sie da. Klicken Sie einfach auf den Link unten, um mit einem Agenten zu sprechen. Der Agent wird Sie dabei unterstützen, den Führerschein zu erhalten

    Reply

  61. Wenn Sie auf diese Nachricht stoßen und versuchen, einen registrierten Führerschein ohne Prüfungen zu kaufen, dann sind wir für Sie da. Klicken Sie einfach auf den Link unten, um mit einem Agenten zu sprechen. Der Agent wird Sie dabei unterstützen, den Führerschein zu erhalten

    Reply

  62. Achetez un permis de conduire réel et enregistré sur notre site Web sans passer d’examens ni passer le test pratique. tout ce dont nous avons besoin, ce sont vos données et elles seront enregistrées dans le système dans les huit prochains jours. Le permis de conduire doit subir la même procédure d’enregistrement que ceux délivrés dans les auto-écoles,

    Reply

  63. Achetez un permis de conduire réel et enregistré sur notre site Web sans passer d’examens ni passer le test pratique. tout ce dont nous avons besoin, ce sont vos données et elles seront enregistrées dans le système dans les huit prochains jours. Le permis de conduire doit subir la même procédure d’enregistrement que ceux délivrés dans les auto-écoles,

    Reply

  64. Se ti imbatti in questo messaggio che cerca di acquistare una patente di guida registrata senza gli esami, allora siamo qui per te, fai semplicemente clic sul collegamento in basso per parlare con un agente. l’agente ti guiderà su come ottenere la patente di guida

    Reply

  65. Are you suffering from your broken TV or the bad performance of your TV display? So, why wait? HK-Fixers is now offering affordable TV services in Dubai. As we know electronic appliances like TVs are becoming more advanced day by day. Also, their usage percentage is going to increase with time. So, its repair demand is also going higher day by day. Considering the higher demands of TV Repair Dubai, we proudly provide the best Television/LCD repair services in Dubai. If you are looking for the best TV repair near me then no need to look further call us now!TV Repair Near Me

    Reply

  66. Buggy Explorers is the best dune buggy rental Dubai company, offering thrilling rides through the stunning desert landscapes. Known for top-notch safety standards, well-maintained vehicles, and professional guides, Buggy Explorers ensures a unique and unforgettable adventure for all.
    Dune Buggy Rental Dubai

    Reply

  68. sportbootführerschein binnen und see, sportbootführerschein binnen prüfungsfragen, sportbootführerschein binnen kosten, sportbootführerschein binnen online, sporthochseeschifferschein kaufen, sportbootführerschein binnen berlin, sportbootführerschein binnen segel, sportbootführerschein kaufen, sportseeschifferschein kaufen erfahrungen, sportküstenschifferschein kaufen schwarz, sportbootführerschein see kaufen, sportbootführerschein binnen kaufen, sportbootführerschein see kaufen ohne prüfung, bootsführerschein kaufen, bootsführerschein kaufen polen, bootsführerschein kaufen erfahrungen, bootsführerschein online kaufen, bootsführerschein tschechien kaufen, SSS kaufen, SKS kaufen, SHS kaufen
    SHS

    Reply

  69. gdzie mozna kupic prawo jazdy z wpisem do rejestru, kupić prawo jazdy, legalne prawo jazdy do kupienia, kupię prawo jazdy, jak załatwić prawo jazdy, bez egzaminu, jak kupić prawo jazdy, czy można kupić prawo jazdy, legalne prawo jazdy do kupienia 2024, pomogę zdać egzamin na prawo jazdy, prawo jazdy bez egzaminu, gdzie kupić prawo jazdy bez egzaminu, gdzie kupić prawo jazdy na lewo, jak kupić prawo jazdy w niemczech, gdzie kupic prawo jazdy legalnie, kupić prawo jazdy b
    legalnie

    Reply

  70. acheter permis de conduire en ligne, acheter un permis de conduire belge, achat permis de conduire, acheter un permis de conduire, acheter permis de conduire belgique, acheter le permis de conduire, permis de conduire acheter, faux permis de conduire belge, j’ai acheter mon permis de conduire sur internet, acheter son permis de conduire belgique, acheter son permis de conduire légalement, acheter un vrai permis de conduire, acheter permis moto a2, acheter permis moto étranger, Acheter permis de conduire enregistré, acheter permis de conduire enregistré en préfecture forum, permis de conduire légalement enregistré.
    permis

    Reply

  71. lkw führerschein kaufen legal, führerschein kaufen ohne vorkasse, registrierten führerschein kaufen erfahrungen, führerschein kaufen Erfahrungen, führerschein kaufen ohne prüfung Köln, führerschein kaufen österreich, führerschein kaufen ohne prüfung österreich, führerschein kaufen ohne prüfung österreich, führerschein kaufen in österreich, führerschein kaufen Frankfurt, führerschein kaufen schweiz.
    österreich

    Reply

  72. kupovina vozacke dozvole,kako kupiti vozacku dozvolu u srbiji, lazna vozacka dozvola, kupiti vozacku u bosni, kako kupiti vozacku dozvolu, kupiti vozacka dozvola hrvatska cijena, kupovina vozacke dozvole na crno, kupiti vozačku dozvolu, kupljena vozacka dozvola, kupiti vozacka dozvola bez polaganja, kupiti vozacku dozvolu, kupi lažna vozačka dozvola, kupiti vozacku u hrvatskoj, kupite vozacka dozvola hrvatska, vozacka dozvola na crno, zamena srpske vozacke dozvole u italiji 2024
    vozacke

    Reply

  73. Comprare patente registrata presso Motorizzazione civile (DMV)? La decisione di comprare patente online in Italia , comprare una patente, patente originale, comprare patente c, acquisto patente b, comprare patente prezzo, compro patente, acquistare patente b, dove posso comprare la patente b, compra patente online, comprare patente b online, comprare la patente a napoli, dove si può comprare la patente, quanto costa comprare la patente, comprare patente di guida, comprare patente senza.
    DMV

    Reply

  74. “`Wenn Sie auf diese Nachricht stoßen und versuchen, einen registrierten deutschen, polnischen oder italienischen Führerschein ohne Prüfungen zu erhalten, dann sind wir für Sie da.
    Kaufen Sie einen Führerschein ohne Vorauszahlung, erwerben Sie registrierte Führerscheinerfahrungen, klicken Sie einfach auf den Link unten, um mit einem Agenten zu sprechen. Der Agent hilft Ihnen beim Erwerb des Führerscheins
    https://jetztlizenzieren.com/

    Reply

  77. Acquistare una patente di guida per moto, acquistare una patente di guida per camion senza esame in Italia, costi patente autobus in Italia. Acquistare la patente di guida online, auto c senza esame Colonia, acquistare la patente di guida classe B, acquistare Italia patente registrata Napoli, Italia acquistare patente di guida legalmente in Italia, acquistare patente di guida in Italia, acquistare patente di guida auto Italia acquistare Italia patente di guida legalmente in Italia, acquistare Italia patente di guida, eu -Acquistare una patente di guida, quanto costa
    https://comprarepatentedi-guida.com/

    Reply

  78. comprar carta de condução classe B, comprar carta de condução registrada em Porturgal Berlim, Áustria comprar carta de condução legalmente em Porturgal, compre carta de condução em Porturgal, comprar carta de condução de carro Áustria, compre carteira de condução alemã legalmente na Áustria, comprar carta de condução Porturgal, eu -Compre uma carta de condução, quanto custa uma carta. https://imtcartaonline.com/

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *