taking rows from data.frames in list to form new data.frame? - R-help

James

Wed, Apr 20, 2011 4:13 PM #

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:

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 ...
 . . .

$X2011.01.11
[1] 234

$X2011.01.12
[1] 234
 . . .

$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

Wed, Apr 20, 2011 4:36 PM #

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.

James

Wed, Apr 20, 2011 4:42 PM #

Dennis,

Thanks, the first example works perfectly.

I haven't tried the second, but will look into plyr.

Thanks, again,

James

On Wed, Apr 20, 2011 at 6:36 PM, Dennis Murphy <djmuser at gmail.com> wrote:

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.

Hadley Wickham

Thu, Apr 21, 2011 5:02 AM #

On Wed, Apr 20, 2011 at 6:36 PM, Dennis Murphy <djmuser at gmail.com> wrote:

That's a bit redundant.  All you need is:

ldply(database, function(df) subset(df, Symbol == 'IBM'))

Hadley

Assistant Professor / Dobelman Family Junior Chair
Department of Statistics / Rice University
http://had.co.nz/