[R] Convert daily data to weekly data
Miluji Sb
miluji@b @ending from gm@il@com
Wed May 30 17:35:15 CEST 2018
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 <jholtman using 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 <milujisb using 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.pikal using 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-bounces using r-project.org] On Behalf Of
>> Miluji
>> > Sb
>> > > Sent: Tuesday, May 29, 2018 2:59 PM
>> > > To: r-help mailing list <r-help using 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 using 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 using 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]]
More information about the R-help
mailing list