jctoll
2011-Apr-20 23:13 UTC
[R] taking rows from data.frames in list to form new data.frame?
Hi, I am having a problem figuring out how to extract a subset of rows. I have a list with 68 similar data.frames. Each data.frame is 500 rows by 5 columns. I want to take one row from each data.frame based upon the data in a particular column (i.e. it matches a symbol). For example:> str(database)List of 68 $ X2011.01.11:'data.frame': 500 obs. of 5 variables: ..$ Symbol : chr [1:500] "MMM" "ACE" "AES" "AFL" ... ..$ Price : num [1:500] 87.7 60.7 13.1 55.7 15.6 ... ..$ Shares.Out: num [1:500] 7.15e+08 3.39e+08 7.88e+08 4.71e+08 1.10e+08 ... ..$ Float : num [1:500] 7.13e+08 3.38e+08 6.61e+08 4.60e+08 1.09e+08 ... ..$ Market.Cap: num [1:500] 6.27e+10 2.06e+10 1.04e+10 2.62e+10 1.72e+09 ... $ X2011.01.12:'data.frame': 500 obs. of 5 variables: ..$ Symbol : chr [1:500] "MMM" "ACE" "AES" "AFL" ... ..$ Price : num [1:500] 88.7 60.9 12.9 57.1 15.2 ... ..$ Shares.Out: num [1:500] 7.15e+08 3.39e+08 7.88e+08 4.71e+08 1.10e+08 ... ..$ Float : num [1:500] 7.13e+08 3.38e+08 6.61e+08 4.60e+08 1.09e+08 ... ..$ Market.Cap: num [1:500] 6.34e+10 2.06e+10 1.02e+10 2.69e+10 1.68e+09 ... . . .> lapply(database, function(x) which(x == "IBM"))$X2011.01.11 [1] 234 $X2011.01.12 [1] 234 . . .> lapply(database, function(x) x[which(x == "IBM"), ])$X2011.01.11 Symbol Price Shares.Out Float Market.Cap 234 IBM 147.28 1.24e+09 1.24e+09 1.8297e+11 $X2011.01.12 Symbol Price Shares.Out Float Market.Cap 234 IBM 149.1 1.24e+09 1.24e+09 1.8524e+11 . . . What I would like to do is create a new data.frame with 68 rows by 5 columns of data, perhaps using the old data.frame names as the new row.names. I can get to the subset of data that I want, I just can't get it from list form into one new data.frame. Any suggestions? Thank you. James
Dennis Murphy
2011-Apr-20 23:36 UTC
[R] taking rows from data.frames in list to form new data.frame?
Hi: Perhaps you're looking for subset()? I'm not sure I understand the problem completely, but is do.call(rbind, lapply(database, function(df) subset(df, Symbol == 'IBM'))) or library(plyr) ldply(lapply(database, function(df) subset(df, Symbol == 'IBM'), rbind) in the vicinity of what you're looking for? [Obviously untested for the usual reasons...] HTH, Dennis On Wed, Apr 20, 2011 at 4:13 PM, jctoll <jctoll at gmail.com> wrote:> Hi, > > I am having a problem figuring out how to extract a subset of rows. ?I > have a list with 68 similar data.frames. ?Each data.frame is 500 rows > by 5 columns. ?I want to take one row from each data.frame based upon > the data in a particular column (i.e. it matches a symbol). ?For > example: > >> str(database) > List of 68 > ?$ X2011.01.11:'data.frame': ? ?500 obs. of ?5 variables: > ?..$ Symbol ? ?: chr [1:500] "MMM" "ACE" "AES" "AFL" ... > ?..$ Price ? ? : num [1:500] 87.7 60.7 13.1 55.7 15.6 ... > ?..$ Shares.Out: num [1:500] 7.15e+08 3.39e+08 7.88e+08 4.71e+08 1.10e+08 ... > ?..$ Float ? ? : num [1:500] 7.13e+08 3.38e+08 6.61e+08 4.60e+08 1.09e+08 ... > ?..$ Market.Cap: num [1:500] 6.27e+10 2.06e+10 1.04e+10 2.62e+10 1.72e+09 ... > ?$ X2011.01.12:'data.frame': ? ?500 obs. of ?5 variables: > ?..$ Symbol ? ?: chr [1:500] "MMM" "ACE" "AES" "AFL" ... > ?..$ Price ? ? : num [1:500] 88.7 60.9 12.9 57.1 15.2 ... > ?..$ Shares.Out: num [1:500] 7.15e+08 3.39e+08 7.88e+08 4.71e+08 1.10e+08 ... > ?..$ Float ? ? : num [1:500] 7.13e+08 3.38e+08 6.61e+08 4.60e+08 1.09e+08 ... > ?..$ Market.Cap: num [1:500] 6.34e+10 2.06e+10 1.02e+10 2.69e+10 1.68e+09 ... > ?. . . > >> lapply(database, function(x) which(x == "IBM")) > $X2011.01.11 > [1] 234 > > $X2011.01.12 > [1] 234 > ?. . . > >> lapply(database, function(x) x[which(x == "IBM"), ]) > $X2011.01.11 > ? ?Symbol ?Price Shares.Out ? ?Float Market.Cap > 234 ? ?IBM 147.28 ? 1.24e+09 1.24e+09 1.8297e+11 > > $X2011.01.12 > ? ?Symbol Price Shares.Out ? ?Float Market.Cap > 234 ? ?IBM 149.1 ? 1.24e+09 1.24e+09 1.8524e+11 > ?. . . > > What I would like to do is create a new data.frame with 68 rows by 5 > columns of data, perhaps using the old data.frame names as the new > row.names. I can get to the subset of data that I want, I just can't > get it from list form into one new data.frame. ?Any suggestions? > Thank you. > > James > > ______________________________________________ > 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. >