Dear R-help group, I have a dataset with daily closing prices from a stock exchange (consecutive 5 trading days) from a firm trading a specific commodity. The date variable looks like: quote_date 20080411 With the format; yyyymmdd. Moreover, I have another data set with a (average) weekly price of the underlying commodity. The date variables in this dataset are only year and a week number. I would like to calculate a common date number or ID based on week number that enables me to merge these two datasets, so that it looks like this: quote_date year week week.price 20080407 2008 15 27.45 20080408 2008 15 27.45 20080409 2008 15 27.45 20080410 2008 15 27.45 20080411 2008 15 27.45 The weekly price is constant for the 5 trading days in the daily file. Any good suggestions on how to do this? All the best, Oystein
Here is one way to get the 'week':> x <- as.character(seq(20080401, 20080430)) > # get the week > cbind(x, format(as.Date(x, "%Y%m%d"), "%W"))x [1,] "20080401" "13" [2,] "20080402" "13" [3,] "20080403" "13" [4,] "20080404" "13" [5,] "20080405" "13" [6,] "20080406" "13" [7,] "20080407" "14" [8,] "20080408" "14" [9,] "20080409" "14" [10,] "20080410" "14" [11,] "20080411" "14" On Mon, Apr 14, 2008 at 6:09 AM, ?ystein Myrland <Oystein.Myrland at nfh.uit.no> wrote:> Dear R-help group, > > I have a dataset with daily closing prices from a stock exchange (consecutive 5 trading days) from a firm trading a specific commodity. The date variable looks like: > > quote_date > 20080411 > > With the format; yyyymmdd. > > Moreover, I have another data set with a (average) weekly price of the underlying commodity. The date variables in this dataset are only year and a week number. > > I would like to calculate a common date number or ID based on week number that enables me to merge these two datasets, so that it looks like this: > > quote_date year week week.price > 20080407 2008 15 27.45 > 20080408 2008 15 27.45 > 20080409 2008 15 27.45 > 20080410 2008 15 27.45 > 20080411 2008 15 27.45 > > The weekly price is constant for the 5 trading days in the daily file. Any good suggestions on how to do this? > > All the best, > Oystein > > ______________________________________________ > R-help at r-project.org mailing list > 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 Holtman Cincinnati, OH +1 513 646 9390 What is the problem you are trying to solve?
On Mon, Apr 14, 2008 at 6:09 AM, ?ystein Myrland <Oystein.Myrland at nfh.uit.no> wrote:> Dear R-help group, > > I have a dataset with daily closing prices from a stock exchange (consecutive 5 trading days) from a firm trading a specific commodity. The date variable looks like: > > quote_date > 20080411 > > With the format; yyyymmdd. > > Moreover, I have another data set with a (average) weekly price of the underlying commodity. The date variables in this dataset are only year and a week number. > > I would like to calculate a common date number or ID based on week number that enables me to merge these two datasets, so that it looks like this: > > quote_date year week week.price > 20080407 2008 15 27.45 > 20080408 2008 15 27.45 > 20080409 2008 15 27.45 > 20080410 2008 15 27.45 > 20080411 2008 15 27.45 >> library(zoo) > library(xts) > > Lines.daily <- "quote_date value+ 20080407 1 + 20080408 2 + 20080409 3 + 20080410 4 + 20080411 6 + 20080412 7 + 20080413 8 + 20080414 9 + 20080415 10 + "> daily <- read.zoo(textConnection(Lines.daily),+ header = TRUE, format = "%Y%m%d")> > Lines.weekly <- "quote_date value+ 20080413 10 + "> weekly <- read.zoo(textConnection(Lines.weekly),+ header = TRUE, format = "%Y%m%d")> > merge(to.weekly(daily), weekly)daily.Open daily.High daily.Low daily.Close weekly 2008-04-13 1 8 1 8 10 2008-04-15 9 10 9 10 NA See ?to.weekly in xts package and the three zoo vignettes. Also see ?aggregate.zoo and the R Help Desk article in R News 4/1 for another approach. vignette(package = "zoo")
Hi Oystein,
Maybe this is what you are looking for:
Keyword: merge()
----------------------------------------------------------------------
table1<-read.table("daily.data")
table2<-read.table("weekly.data")
(maybe you need to create a new common coloumn for daily and weekly data set.
For example: year_week)
data<-merge(table1, table2, by.x="year_week",
by.y="year_week") ?
data
I hope it helps,
Edwin Sendjaja
Am Montag, 14. April 2008 11:09:12 schrieb ?ystein
Myrland:> Dear R-help group,
>
> I have a dataset with daily closing prices from a stock exchange
> (consecutive 5 trading days) from a firm trading a specific commodity. The
> date variable looks like:
>
> quote_date
> 20080411
>
> With the format; yyyymmdd.
>
> Moreover, I have another data set with a (average) weekly price of the
> underlying commodity. The date variables in this dataset are only year and
> a week number.
>
> I would like to calculate a common date number or ID based on week number
> that enables me to merge these two datasets, so that it looks like this:
>
> quote_date year week week.price
> 20080407 2008 15 27.45
> 20080408 2008 15 27.45
> 20080409 2008 15 27.45
> 20080410 2008 15 27.45
> 20080411 2008 15 27.45
>
> The weekly price is constant for the 5 trading days in the daily file. Any
> good suggestions on how to do this?
>
> All the best,
> Oystein
>
> ______________________________________________
> R-help at r-project.org mailing list
> 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.