Subhamitra Patra
2019-Sep-12 14:53 UTC
[R] Query about calculating the monthly average of daily data columns
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_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
Bert Gunter
2019-Sep-12 15:16 UTC
[R] Query about calculating the monthly average of daily data columns
No reproducible example, so hard to say. What class is your "date" column? -- factor, character, Date? See ?Date Once you have an object of appropriate class -- see ?format.Date -- ?months can extract the month and ?ave can do your averaging. No explicit looping is needed. The tidydata alternative universe can also do all these things if that's where you prefer to live. As usual, any attached data was stripped. See ?dput for one way to include data in your post. Cheers, Bert On Thu, Sep 12, 2019 at 7:54 AM Subhamitra Patra <subhamitra.patra at 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_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 at 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. >[[alternative HTML version deleted]]
Rui Barradas
2019-Sep-12 15:38 UTC
[R] Query about calculating the monthly average of daily data columns
Hello, Please include data, say dput(head(data, 20)) # post the output of this But, is the problem as simple as rowMeans(data[2:3], na.rm = TRUE) ? Hope this helps, Rui Barradas ?s 15:53 de 12/09/19, Subhamitra Patra escreveu:> 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. >
Rui Barradas
2019-Sep-12 19:13 UTC
[R] Query about calculating the monthly average of daily data columns
Hello, Inline. ?s 17:33 de 12/09/19, Bert Gunter escreveu:> But she wants *monthly* averages, Rui.Thanks, my mistake. Ergo ave() or tidyData> equivalent, right?Maybe. But ave() returns as many values as the input length, this seems more suited for tapply or aggregate. I will first create an example data set. set.seed(1234) start <- as.Date("03-01-1994", "%d-%m-%Y") end <- as.Date("29-12-2000", "%d-%m-%Y") date <- seq(start, end, by = "day") date <- date[as.integer(format(date, "%u")) %in% 1:5] df1 <- data.frame(date, CountryA = rnorm(length(date)), CountryB = rnorm(length(date))) Now the averages by month month <- zoo::as.yearmon(df1[[1]]) aggA <- aggregate(CountryA ~ month, df1, mean) aggB <- aggregate(CountryB ~ month, df1, mean) MonthReturns <- merge(aggA, aggB) head(MonthReturns) Final clean up. rm(date, month, aggA, aggB) Hope this helps, Rui Barradas> > -- Bert > > On Thu, Sep 12, 2019 at 8:41 AM Rui Barradas <ruipbarradas at sapo.pt > <mailto:ruipbarradas at sapo.pt>> wrote: > > Hello, > > Please include data, say > > dput(head(data, 20))? # post the output of this > > > But, is the problem as simple as > > rowMeans(data[2:3], na.rm = TRUE) > > ? > > Hope this helps, > > Rui Barradas > > > ?s 15:53 de 12/09/19, Subhamitra Patra escreveu: > > 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. > > > > ______________________________________________ > R-help at r-project.org <mailto:R-help at 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. >
Jim Lemon
2019-Sep-12 21:45 UTC
[R] Query about calculating the monthly average of daily data columns
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 at 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_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 at 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.
Subhamitra Patra
2019-Sep-13 08:08 UTC
[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_campaign=signaturevirality5&>
Sender
notified by
Mailtrack
<https://mailtrack.io?utm_source=gmail&utm_medium=signature&utm_campaign=signaturevirality5&>
09/13/19,
12:57:58 PM
On Fri, Sep 13, 2019 at 3:15 AM Jim Lemon <drjimlemon at 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 at 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_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 at 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]]