I have two files with dates and prices in each. The number of rows in each of them will differ. How do I create a new file which contains data from both these files? Cbind and merge are not helpful. For cbind because the rows are not the same replication occurs. Also if I have similar data how do I write a vlookup kind of function? I am giving an example below: Say Price1 file contains the following: Date Price 2/3/2010 134.00 3/3/2010 133.90 4/3/2010 135.55 And say price2 contains the following: Date Price 2/3/2010 2300 3/3/2010 3200 4/3/2010 1800 5/3/2010 1900 I want to take both these data together in a single file, and take the smaller vector (or matrix or dataframe??..i am new to R and still confused with the various objects) which is file1 (because it contains fewer rows ) and vlookup prices in the second file basedon the dates on file1 and write three columns (date, price from 1 and price from2) in a new file. How do i do this please? Many thanks... R -- View this message in context: http://r.789695.n4.nabble.com/Adding-two-files-into-one-and-vlookup-tp2280277p2280277.html Sent from the R help mailing list archive at Nabble.com.
Tena koe Why is merge() not helpful? From your description I would imagine merge(file1, file2, by='Date') would do what you require. HTH .... Peter Alspach> -----Original Message----- > From: r-help-bounces at r-project.org [mailto:r-help-bounces at r- > project.org] On Behalf Of raghu > Sent: Wednesday, 7 July 2010 10:18 a.m. > To: r-help at r-project.org > Subject: [R] Adding two files into one and vlookup > > > I have two files with dates and prices in each. The number of rows in > each of > them will differ. How do I create a new file which contains data from > both > these files? Cbind and merge are not helpful. For cbind because the > rows are > not the same replication occurs. Also if I have similar data how do I > write > a vlookup kind of function? I am giving an example below: > Say Price1 file contains the following: > Date Price > 2/3/2010 134.00 > 3/3/2010 133.90 > 4/3/2010 135.55 > > And say price2 contains the following: > Date Price > 2/3/2010 2300 > 3/3/2010 3200 > 4/3/2010 1800 > 5/3/2010 1900 > > I want to take both these data together in a single file, and take the > smaller vector (or matrix or dataframe??..i am new to R and still > confused > with the various objects) which is file1 (because it contains fewer > rows ) > and vlookup prices in the second file basedon the dates on file1 and > write > three columns (date, price from 1 and price from2) in a new file. How > do i > do this please? > > Many thanks... > R > -- > View this message in context: http://r.789695.n4.nabble.com/Adding-two- > files-into-one-and-vlookup-tp2280277p2280277.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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.
raghu wrote:> I have two files with dates and prices in each. The number of rows in each of > them will differ. How do I create a new file which contains data from both > these files? Cbind and merge are not helpful. For cbind because the rows are > not the same replication occurs. Also if I have similar data how do I write > a vlookup kind of function? I am giving an example below: > Say Price1 file contains the following: > Date Price > 2/3/2010 134.00 > 3/3/2010 133.90 > 4/3/2010 135.55 > > And say price2 contains the following: > Date Price > 2/3/2010 2300 > 3/3/2010 3200 > 4/3/2010 1800 > 5/3/2010 1900 > > I want to take both these data together in a single file, and take the > smaller vector (or matrix or dataframe??..i am new to R and still confused > with the various objects) which is file1 (because it contains fewer rows ) > and vlookup prices in the second file basedon the dates on file1 and write > three columns (date, price from 1 and price from2) in a new file. How do i > do this please?I think all this can be accomplished with merge. Can you give reproducible examples as the posting guide suggests? Use read.table to read in your data into R objects, then use ?dput to give us the exact copies of the objects (probably data.frames by your example), and what output you want to have. Being precise with the classes of objects you're working with is key, and ?dput is a great way to make sure we have the same objects as you. Another tip is common terminology. For instance, `vlookup` is not a term used in R, and many people will not know what it means. This way, everything is reproducible for us, and we can offer suggestions and show you what the exact output will be. In short, making sure everyone is on the same page goes a long way when getting help from a mailing list.
On Tue, Jul 6, 2010 at 6:18 PM, raghu <r.raghuraman at gmail.com> wrote:> > I have two files with dates and prices in each. The number of rows in each of > them will differ. How do I create a new file which contains data from both > these files? Cbind and merge are not helpful. For cbind because the rows are > not the same replication occurs. Also if I have similar data how do I write > a vlookup kind of function? I am giving an example below: > Say Price1 file contains the following: > Date ? ? ? ? ? ? Price > 2/3/2010 ? ? ? 134.00 > 3/3/2010 ? ? ? 133.90 > 4/3/2010 ? ? ? 135.55 > > And say price2 contains the following: > Date ? ? ? ? ? ? ?Price > 2/3/2010 ? ? ? ?2300 > 3/3/2010 ? ? ? ?3200 > 4/3/2010 ? ? ? ?1800 > 5/3/2010 ? ? ? ?1900 > > I want to take both these data together in a single file, and take the > smaller vector (or matrix or dataframe??..i am new to R and still confused > with the various objects) which is file1 (because it contains fewer rows ) > and vlookup prices in the second file basedon the dates on file1 and write > three columns (date, price from 1 and price from2) in a new file. How do i > do this please? >Try this and for more read the three vignettes (pdf documents) in the zoo package and also read the R News 4/1 article on dates and times: Lines1 <- "Date Price 2/3/2010 134.00 3/3/2010 133.90 4/3/2010 135.55" Lines2 <- "Date Price 2/3/2010 2300 3/3/2010 3200 4/3/2010 1800 5/3/2010 1900" library(zoo) library(chron) z1 <- read.zoo(textConnection(Lines1), header = TRUE, FUN = chron) z2 <- read.zoo(textConnection(Lines2), header = TRUE, FUN = chron) merge(z1, z2) # keep all rows in each merge(z1, z2, all = FALSE) # keep only rows in both merge(z1, z2, all = c(TRUE, FALSE)) # keep all rows in z1 merge(z1, z2, all = c(FALSE, TRUE)) # keep all rows in z2
On Tue, Jul 6, 2010 at 8:26 PM, Gabor Grothendieck <ggrothendieck at gmail.com> wrote:> On Tue, Jul 6, 2010 at 6:18 PM, raghu <r.raghuraman at gmail.com> wrote: >> >> I have two files with dates and prices in each. The number of rows in each of >> them will differ. How do I create a new file which contains data from both >> these files? Cbind and merge are not helpful. For cbind because the rows are >> not the same replication occurs. Also if I have similar data how do I write >> a vlookup kind of function? I am giving an example below: >> Say Price1 file contains the following: >> Date ? ? ? ? ? ? Price >> 2/3/2010 ? ? ? 134.00 >> 3/3/2010 ? ? ? 133.90 >> 4/3/2010 ? ? ? 135.55 >> >> And say price2 contains the following: >> Date ? ? ? ? ? ? ?Price >> 2/3/2010 ? ? ? ?2300 >> 3/3/2010 ? ? ? ?3200 >> 4/3/2010 ? ? ? ?1800 >> 5/3/2010 ? ? ? ?1900 >> >> I want to take both these data together in a single file, and take the >> smaller vector (or matrix or dataframe??..i am new to R and still confused >> with the various objects) which is file1 (because it contains fewer rows ) >> and vlookup prices in the second file basedon the dates on file1 and write >> three columns (date, price from 1 and price from2) in a new file. How do i >> do this please? >> > > Try this and for more read the three vignettes (pdf documents) in the > zoo package and also read the R News 4/1 article on dates and times: > > Lines1 <- "Date ? ? ? ? ? ? Price > 2/3/2010 ? ? ? 134.00 > 3/3/2010 ? ? ? 133.90 > 4/3/2010 ? ? ? 135.55" > > Lines2 <- "Date ? ? ? ? ? ? ?Price > 2/3/2010 ? ? ? ?2300 > 3/3/2010 ? ? ? ?3200 > 4/3/2010 ? ? ? ?1800 > 5/3/2010 ? ? ? ?1900" > > library(zoo) > library(chron) > z1 <- read.zoo(textConnection(Lines1), header = TRUE, FUN = chron) > z2 <- read.zoo(textConnection(Lines2), header = TRUE, FUN = chron)I originally assumed that the dates were the usual month/day/year but looking at it again I suspect they are day/month/year so lets use Date class instead of chron replacing the last three statements with: fmt <- "%d/%m/%Y" z1 <- read.zoo(textConnection(Lines1), header = TRUE, format = fmt) z2 <- read.zoo(textConnection(Lines2), header = TRUE, format = fmt)> merge(z1, z2) # keep all rows in each > merge(z1, z2, all = FALSE) # keep only rows in both > merge(z1, z2, all = c(TRUE, FALSE)) # keep all rows in z1 > merge(z1, z2, all = c(FALSE, TRUE)) # keep all rows in z2 >