Suppose I have the following data frame (df): Year Price ------------------- 2001 10 2002 20 2003 30 I would like to produce another data frame like this: a.Year a.Price b.Year b.Price ---------------------------------------------------- 2002 20 2001 10 2003 30 2001 10 2003 30 2002 20 In SQL, this can be done as select a.*, b.* from df as a, df as b where a.Year > b.Year How do I do this efficiently in R? Many thanks.
Hi: One option is to read your data frame into R and then use your SQL code in conjunction with the sqldf package. It uses SQLite as its engine. HTH, Dennis On Mon, Sep 27, 2010 at 4:29 AM, Xin Zhang <xin.zhang@gmail.com> wrote:> Suppose I have the following data frame (df): > > Year Price > ------------------- > 2001 10 > 2002 20 > 2003 30 > > I would like to produce another data frame like this: > > a.Year a.Price b.Year b.Price > ---------------------------------------------------- > 2002 20 2001 10 > 2003 30 2001 10 > 2003 30 2002 20 > > In SQL, this can be done as > > select a.*, b.* > from df as a, df as b > where a.Year > b.Year > > How do I do this efficiently in R? > > Many thanks. > > ______________________________________________ > R-help@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. >[[alternative HTML version deleted]]
Thanks Dennis. I will explore that. On Mon, Sep 27, 2010 at 7:45 AM, Dennis Murphy <djmuser at gmail.com> wrote:> Hi: > > One option is to read your data frame into R and then use your SQL code in > conjunction with the sqldf package. It uses SQLite as its engine. > > HTH, > Dennis > > > On Mon, Sep 27, 2010 at 4:29 AM, Xin Zhang <xin.zhang at gmail.com> wrote: >> >> Suppose I have the following data frame (df): >> >> Year ? ?Price >> ------------------- >> 2001 ? ?10 >> 2002 ? ?20 >> 2003 ? ?30 >> >> I would like to produce another data frame like this: >> >> a.Year ? ?a.Price ? ?b.Year ? ?b.Price >> ---------------------------------------------------- >> 2002 ? ?20 ? ?2001 ? ?10 >> 2003 ? ?30 ? ?2001 ? ?10 >> 2003 ? ?30 ? ?2002 ? ?20 >> >> In SQL, this can be done as >> >> select a.*, b.* >> from df as a, df as b >> where a.Year > b.Year >> >> How do I do this efficiently in R? >> >> Many thanks. >> >> ______________________________________________ >> 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. > >
try sqldf:> require(sqldf) > x <- read.table(textConnection("Year Price+ 2001 10 + 2002 20 + 2003 30"), as.is=TRUE, header=TRUE)> sqldf("select a.*, b.*+ from x as a, x as b + where a.Year > b.Year", method='raw') Year Price Year Price 1 2002 20 2001 10 2 2003 30 2001 10 3 2003 30 2002 20>On Mon, Sep 27, 2010 at 7:29 AM, Xin Zhang <xin.zhang at gmail.com> wrote:> Suppose I have the following data frame (df): > > Year ? ?Price > ------------------- > 2001 ? ?10 > 2002 ? ?20 > 2003 ? ?30 > > I would like to produce another data frame like this: > > a.Year ? ?a.Price ? ?b.Year ? ?b.Price > ---------------------------------------------------- > 2002 ? ?20 ? ?2001 ? ?10 > 2003 ? ?30 ? ?2001 ? ?10 > 2003 ? ?30 ? ?2002 ? ?20 > > In SQL, this can be done as > > select a.*, b.* > from df as a, df as b > where a.Year > b.Year > > How do I do this efficiently in R? > > Many thanks. > > ______________________________________________ > 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 that you are trying to solve?