[R] Query about calculating the monthly average of daily data columns

Fri Sep 13 12:21:46 CEST 2019

Hi

I may be completely wrong but reshape/aggregate should by what you want
spdat
       dates returnA returnB
1   1-1-1994      16      13
2   2-1-1994      44      10
3   3-1-1994      24      32
.....
> library(reshape2)
> spdat.m <- melt(spdat)
Using dates as id variables
> str(spdat.m)
'data.frame':   116 obs. of  3 variables:
 $ dates   : Factor w/ 58 levels "1-1-1994","1-2-1994",..: 1 23 44 47 49 51 53 55 57 3 ...
 $ variable: Factor w/ 2 levels "returnA","returnB": 1 1 1 1 1 1 1 1 1 1 ...
 $ value   : int  16 44 24 47 16 35 34 34 26 36 ...
> spdat.m$realdate <- as.Date(spdat.m[,1], format="%d-%m-%Y")
> aggregate(spdat.m$value, list(format(spdat.m$realdate, "%m.%Y"), spdat.m$variable), mean)
  Group.1 Group.2        x
1 01.1994 returnA 31.93333
2 02.1994 returnA 32.39286
3 01.1994 returnB 24.26667
4 02.1994 returnB 30.03571

Cheers
Petr

> -----Original Message-----
> From: R-help <r-help-bounces using r-project.org> On Behalf Of Subhamitra
> Patra
> Sent: Friday, September 13, 2019 10:08 AM
> To: Jim Lemon <drjimlemon using gmail.com>
> Cc: r-help mailing list <r-help using r-project.org>
> Subject: Re: [R] Query about calculating the monthly average of daily data
> columns
>
> Dear Sir,
>
> Thank you very much for your suggestion.
>
> Yes, your suggested code worked. But, actually, I have data from 3rd January
> 1994 to 3rd August 2017 for very large (i.e. for 84 countries) sample. From
> this, I have given the example of the years up to 2000. Before applying the
> same code for the long 24 years, I want to learn the logic behind the code.
> Actually, some part of the code is not understandable to me which I
> mentioned in the bold letter as follows.
>
> "spdat<-data.frame(
>   dates=paste(c(1:30,1:28),c(rep(1,30),rep(2,28)),rep(1994,58),sep="-"),
>   returnA=sample(*15:50*,58,TRUE),returnB=sample(*10:45*,58,TRUE))"
>
> A. Here, I need to define the no. of days in a month, and the no. of countries
> name separately, right? But, what is meant by 15:50, and 10:45 in return A,
> and B respectively?
>
> "# if you only have to get the monthly averages, it can be done this way
> spdat$month<-sapply(strsplit(spdat$dates,"-"),"["*,2*)
> spdat$year<-sapply(strsplit(spdat$dates,"-"),"[",*3*)"
>
> B. Here, I need to define the no. of months, and years separately, right?
> or else what 2, and 3 (in bold) indicates?
>
> "# get the averages by month and year - is this correct?
> monthlyA<-by(*spdat$returnA*,spdat[,c("month","year")],mean)
> monthlyB<-by(*spdat$returnB*,spdat[,c("month","year")],mean)"
>
> C. From this part, I got the exact average values of both January and
> February of 1994 for country A, and B. But, in code, I have a query that I
> need to define  spdat$returnA, and  spdat$returnB separately before writing
> this code, right? Like this, I need to define for each 84 countries separately
> with their respective number of months, and years before writing this code,
> right?
>
> Yes, after obtaining the monthly average for each country's data, I need to
> use them for further calculations. So, I want to export the result to excel. But,
> until understanding the code, I think I willn't able to apply for the entire
> sample, and cannot be able to discuss the format of the resulted column to
> export to excel.
>
> Therefore, kindly help me to understand the code.
>
> Thank you very much, Sir, and thanks to this R forum for helping the R-
> beginners.
>
>
>
> [image: Mailtrack]
> <https://mailtrack.io?utm_source=gmail&utm_medium=signature&utm_ca
> mpaign=signaturevirality5&>
> Sender
> notified by
> Mailtrack
> <https://mailtrack.io?utm_source=gmail&utm_medium=signature&utm_ca
> mpaign=signaturevirality5&>
> 09/13/19,
> 12:57:58 PM
>
> On Fri, Sep 13, 2019 at 3:15 AM Jim Lemon <drjimlemon using gmail.com> wrote:
>
> > Hi Subhamitra,
> > Your data didn't make it through, so I guess the first thing is to
> > guess what it looks like. Here's a try at just January and February of
> > 1994 so that we can see the result on the screen. The logic will work
> > just as well for the whole seven years.
> >
> > # create fake data for the first two months spdat<-data.frame(
> > dates=paste(c(1:30,1:28),c(rep(1,30),rep(2,28)),rep(1994,58),sep="-"),
> >  returnA=sample(15:50,58,TRUE),returnB=sample(10:45,58,TRUE))
> > # I'll assume that the dates in your file are character, not factor
> > spdat$dates<-as.character(spdat$dates)
> > # if you only have to get the monthly averages, it can be done this
> > way
> > spdat$month<-sapply(strsplit(spdat$dates,"-"),"[",2)
> > spdat$year<-sapply(strsplit(spdat$dates,"-"),"[",3)
> > # get the averages by month and year - is this correct?
> > monthlyA<-by(spdat$returnA,spdat[,c("month","year")],mean)
> > monthlyB<-by(spdat$returnB,spdat[,c("month","year")],mean)
> >
> > Now you have what you say you want:
> >
> > monthlyA
> > month: 1
> > year: 1994
> > [1] 34.1
> > ------------------------------------------------------------
> > month: 2
> > year: 1994
> > [1] 33.32143
> >
> > monthlyB
> > month: 1
> > year: 1994
> > [1] 29.7
> > ------------------------------------------------------------
> > month: 2
> > year: 1994
> > [1] 27.28571
> >
> > Sorry I didn't use a loop (for(month in 1:12) ... for (year in
> > 1994:2000) ...), too lazy.
> > Now you have to let us know how this information is to be formatted to
> > go into Excel. Excel will import the text as above, but I think you
> > want something that you can use for further calculations.
> >
> > Jim
> >
> > On Fri, Sep 13, 2019 at 12:54 AM Subhamitra Patra
> > <subhamitra.patra using gmail.com> wrote:
> > >
> > > Dear R-users,
> > >
> > > I have daily data from 03-01-1994 to 29-12-2000. In my datafile, he
> > > first column is date and the second and third columns are the
> > > returns of the country A, and B. Here, the date column is same for
> > > both countries. I
> > want
> > > to calculate the monthly average of both country's returns by using
> > > a
> > loop,
> > > and then, I want to export the results into excel.
> > >
> > > Please help me in this regard.
> > >
> > > Please find the attached datasheet.
> > >
> > > Thank you.
> > >
> > > --
> > > *Best Regards,*
> > > *Subhamitra Patra*
> > > *Phd. Research Scholar*
> > > *Department of Humanities and Social Sciences* *Indian Institute of
> > > Technology, Kharagpur*
> > > *INDIA*
> > >
> > > [image: Mailtrack]
> > > <
> >
> https://mailtrack.io?utm_source=gmail&utm_medium=signature&utm_cam
> paig
> > n=signaturevirality5&
> > >
> > > Sender
> > > notified by
> > > Mailtrack
> > > <
> >
> https://mailtrack.io?utm_source=gmail&utm_medium=signature&utm_cam
> paig
> > n=signaturevirality5&
> > >
> > > 09/12/19,
> > > 08:23:07 PM
> > > ______________________________________________
> > > 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.
> >
>
>
> --
> *Best Regards,*
> *Subhamitra Patra*
> *Phd. Research Scholar*
> *Department of Humanities and Social Sciences* *Indian Institute of
> Technology, Kharagpur*
> *INDIA*
>
> [[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.
Osobní údaje: Informace o zpracování a ochraně osobních údajů obchodních partnerů PRECHEZA a.s. jsou zveřejněny na: https://www.precheza.cz/zasady-ochrany-osobnich-udaju/ | Information about processing and protection of business partner’s personal data are available on website: https://www.precheza.cz/en/personal-data-protection-principles/
Důvěrnost: 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/