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

Jim Lemon drj|m|emon @end|ng |rom gm@||@com
Thu Sep 12 23:45:19 CEST 2019

```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 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_campaign=signaturevirality5&>
> Sender
> notified by
> Mailtrack
> <https://mailtrack.io?utm_source=gmail&utm_medium=signature&utm_campaign=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