Dear Jim, Thank you so much. The code works perfectly and is very fast for the large amounts of data that I am processing!
Thanks again! Sincerely, Shouro On Tue, May 29, 2018 at 11:50 PM, jim holtman <jholt...@gmail.com> wrote: > Forgot the year if you also want to summarise by that. > > > > x <- structure(list(X1986.01.01.10.30.00 = c(16.8181762695312, > 16.8181762695312, > + 18.8294372558594, 16 .... > [TRUNCATED] > > > library(tidyverse) > > > library(lubridate) > > > # convert to long form > > x_long <- gather(x, key = 'date', value = "value", -ID) > > > # change the date to POSIXct > > x_long$date <- ymd_hms(substring(x_long$date, 2, 19)) > > > # add the week of the year > > x_long$week <- week(x_long$date) > > > x_long$year <- year(x_long$date) > > > # average by ID/week > > avg <- x_long %>% > + group_by(ID, year, week) %>% > + summarise(avg = mean(value)) > > avg > # A tibble: 6 x 4 > # Groups: ID, year [?] > ID year week avg > <int> <dbl> <dbl> <dbl> > 1 1 1986. 1. 16.0 > 2 2 1986. 1. 16.0 > 3 3 1986. 1. 17.9 > 4 4 1986. 1. 16.0 > 5 5 1986. 1. 17.9 > 6 6 1986. 1. 16.0 > > > > Jim Holtman > Data Munger Guru > > What is the problem that you are trying to solve? > Tell me what you want to do, not how you want to do it. > > On Tue, May 29, 2018 at 7:02 AM, Miluji Sb <miluj...@gmail.com> wrote: > >> Dear Petr, >> >> Thanks for your reply and the solution. The example dataset contains data >> for the first six days of the year 1986. "X1986.01.01.10.30.00" is 01 >> January 1986 and the rest of the variable is redundant information. The >> last date is given as "X2016.12.31.10.30.00". >> >> I realized that I missed one information in my previous email, I would >> like >> to compute the weekly average by the variable ID. Thanks again! >> >> Sincerely, >> >> Shouro >> >> On Tue, May 29, 2018 at 3:24 PM, PIKAL Petr <petr.pi...@precheza.cz> >> wrote: >> >> > Hi >> > >> > Based on your explanation I would advice to use >> > >> > ?cut.POSIXt >> > >> > with breaks "week". However your data are rather strange, you have data >> > frame with names which looks like dates >> > >> > names(test) >> > [1] "X1986.01.01.10.30.00" "X1986.01.02.10.30.00" "X1986.01.03.10.30.00" >> > [4] "X1986.01.04.10.30.00" "X1986.01.05.10.30.00" "X1986.01.06.10.30.00" >> > [7] "ID" >> > >> > and under each name you have 6 numeric values >> > test[,1] >> > [1] 16.81818 16.81818 18.82944 16.81818 18.82944 16.83569 >> > >> > You (probably) can get dates by >> > as.Date(substring(names(test),2,11), format="%Y.%m.%d") >> > [1] "1986-01-01" "1986-01-02" "1986-01-03" "1986-01-04" "1986-01-05" >> > [6] "1986-01-06" NA >> > >> > but if you want just average those 6 values below each date you could do >> > >> > colMeans(test) >> > >> > and/or bind it together. >> > >> > > ddd<-as.Date(substring(names(test),2,11), format="%Y.%m.%d") >> > > data.frame(ddd, aver=colMeans(test)) >> > ddd aver >> > X1986.01.01.10.30.00 1986-01-01 17.49152 >> > X1986.01.02.10.30.00 1986-01-02 16.84200 >> > X1986.01.03.10.30.00 1986-01-03 16.51526 >> > X1986.01.04.10.30.00 1986-01-04 16.90191 >> > X1986.01.05.10.30.00 1986-01-05 16.00480 >> > X1986.01.06.10.30.00 1986-01-06 16.04405 >> > ID <NA> 3.50000 >> > >> > Cheers >> > Petr >> > >> > Tento e-mail a jakékoliv k němu připojené dokumenty jsou důvěrné a >> > podléhají tomuto právně závaznému prohlášení o vyloučení odpovědnosti: >> > https://www.precheza.cz/01-dovetek/ | This email and any documents >> > attached to it may be confidential and are subject to the legally >> binding >> > disclaimer: https://www.precheza.cz/en/01-disclaimer/ >> > >> > > -----Original Message----- >> > > From: R-help [mailto:r-help-boun...@r-project.org] On Behalf Of >> Miluji >> > Sb >> > > Sent: Tuesday, May 29, 2018 2:59 PM >> > > To: r-help mailing list <r-help@r-project.org> >> > > Subject: [R] Convert daily data to weekly data >> > > >> > > Dear all, >> > > >> > > I have daily data in wide-format from 01/01/1986 to 31/12/2016 by ID. >> I >> > would >> > > like to convert this to weekly average data. The data has been >> generated >> > by an >> > > algorithm. >> > > >> > > I know that I can use the lubridate package but that would require me >> to >> > first >> > > convert the data to long-form (which is what I want). I am at a bit of >> > loss of >> > > how to extract the date from the variable names and then converting >> the >> > data >> > > to weekly average. Any help will be high appreciated. >> > > >> > > ### data >> > > structure(list(X1986.01.01.10.30.00 = c(16.8181762695312, >> > > 16.8181762695312, 18.8294372558594, 16.8181762695312, >> > > 18.8294372558594, 16.835693359375 ), X1986.01.02.10.30.00 = >> > > c(16.2272033691406, 16.2272033691406, 18.0772094726562, >> > > 16.2272033691406, 18.0772094726562, 16.2159423828125 ), >> > > X1986.01.03.10.30.00 = c(15.8944396972656, 15.8944396972656, >> > > 17.7444152832031, 15.8944396972656, 17.7444152832031, 15.91943359375 >> > > ), X1986.01.04.10.30.00 = c(16.2752380371094, 16.2752380371094, >> > > 18.125244140625, 16.2752380371094, 18.125244140625, 16.3352355957031 >> > > ), X1986.01.05.10.30.00 = c(15.3706359863281, 15.3706359863281, >> > > 17.2806396484375, 15.3706359863281, 17.2806396484375, >> > > 15.3556213378906 ), X1986.01.06.10.30.00 = c(15.3798828125, >> > > 15.3798828125, 17.3136291503906, 15.3798828125, 17.3136291503906, >> > > 15.4974060058594), ID = 1:6), .Names = c("X1986.01.01.10.30.00", >> > > "X1986.01.02.10.30.00", "X1986.01.03.10.30.00", >> "X1986.01.04.10.30.00", >> > > "X1986.01.05.10.30.00", "X1986.01.06.10.30.00", "ID"), row.names = >> c(NA, >> > 6L), >> > > class = "data.frame") >> > > >> > > Sincerely, >> > > >> > > Milu >> > > >> > > [[alternative HTML version deleted]] >> > > >> > > ______________________________________________ >> > > R-help@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. >> > >> > ________________________________ >> > Tento e-mail a jakékoliv k němu připojené dokumenty jsou důvěrné a jsou >> > určeny pouze jeho adresátům. >> > Jestliže jste obdržel(a) tento e-mail omylem, informujte laskavě >> > neprodleně jeho odesílatele. Obsah tohoto emailu i s přílohami a jeho >> kopie >> > vymažte ze svého systému. >> > Nejste-li zamýšleným adresátem tohoto emailu, nejste oprávněni tento >> email >> > jakkoliv užívat, rozšiřovat, kopírovat či zveřejňovat. >> > Odesílatel e-mailu neodpovídá za eventuální škodu způsobenou >> modifikacemi >> > či zpožděním přenosu e-mailu. >> > >> > V případě, že je tento e-mail součástí obchodního jednání: >> > - vyhrazuje si odesílatel právo ukončit kdykoliv jednání o uzavření >> > smlouvy, a to z jakéhokoliv důvodu i bez uvedení důvodu. >> > - a obsahuje-li nabídku, je adresát oprávněn nabídku bezodkladně >> přijmout; >> > Odesílatel tohoto e-mailu (nabídky) vylučuje přijetí nabídky ze strany >> > příjemce s dodatkem či odchylkou. >> > - trvá odesílatel na tom, že příslušná smlouva je uzavřena teprve >> > výslovným dosažením shody na všech jejích náležitostech. >> > - odesílatel tohoto emailu informuje, že není oprávněn uzavírat za >> > společnost žádné smlouvy s výjimkou případů, kdy k tomu byl písemně >> zmocněn >> > nebo písemně pověřen a takové pověření nebo plná moc byly adresátovi >> tohoto >> > emailu případně osobě, kterou adresát zastupuje, předloženy nebo jejich >> > existence je adresátovi či osobě jím zastoupené známá. >> > >> > This e-mail and any documents attached to it may be confidential and are >> > intended only for its intended recipients. >> > If you received this e-mail by mistake, please immediately inform its >> > sender. Delete the contents of this e-mail with all attachments and its >> > copies from your system. >> > If you are not the intended recipient of this e-mail, you are not >> > authorized to use, disseminate, copy or disclose this e-mail in any >> manner. >> > The sender of this e-mail shall not be liable for any possible damage >> > caused by modifications of the e-mail or by delay with transfer of the >> > email. >> > >> > In case that this e-mail forms part of business dealings: >> > - the sender reserves the right to end negotiations about entering into >> a >> > contract in any time, for any reason, and without stating any reasoning. >> > - if the e-mail contains an offer, the recipient is entitled to >> > immediately accept such offer; The sender of this e-mail (offer) >> excludes >> > any acceptance of the offer on the part of the recipient containing any >> > amendment or variation. >> > - the sender insists on that the respective contract is concluded only >> > upon an express mutual agreement on all its aspects. >> > - the sender of this e-mail informs that he/she is not authorized to >> enter >> > into any contracts on behalf of the company except for cases in which >> > he/she is expressly authorized to do so in writing, and such >> authorization >> > or power of attorney is submitted to the recipient or the person >> > represented by the recipient, or the existence of such authorization is >> > known to the recipient of the person represented by the recipient. >> > >> >> [[alternative HTML version deleted]] >> >> ______________________________________________ >> R-help@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/posti >> ng-guide.html >> and provide commented, minimal, self-contained, reproducible code. >> > > [[alternative HTML version deleted]] ______________________________________________ R-help@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.
