How can I access columns by name in an anonymous data frame object for filtering in R?

Question

I've got this one-liner:

aggregate(. ~ gear,mtcars[mtcars$mpg>20,c('gear','mpg')],mean)

Now I want to filter by mpg>25 like his:

aggregate(. ~ gear,mtcars[mtcars$mpg>20,c('gear','mpg')],mean)[mpg>25,]

But now I get some 1743 lines of NA entries

any ideas?

Answer 1

A dplyr solution could be:

 mtcars %>% group_by(gear) %>% filter(mpg>25) %>% summarise(mean(mpg))

or rather:

mtcars %>% group_by(gear) %>% filter(mpg>20) %>% 
summarise(mean(mpg)) %>% filter(`mean(mpg)`>25)

Answer 2

You can't (and I don't get lots of NAs, I correctly get an error because mpg isn't found. Did you do something previously that allows mpg to be found?). R doesn't work the way you are trying to force it.

You will need

tmp <- aggregate(. ~ gear,
                 data = subset(mtcars, mpg > 20, select = c('gear','mpg')),
                 FUN = mean)
with(tmp, tmp[mpg > 25, ])

> with(tmp, tmp[mpg > 25, ])
  gear   mpg
2    4 25.74
3    5 28.20

Answer 3

Here is another solution with base functions:

subset(aggregate(. ~ gear,mtcars[mtcars$mpg>20,c('gear','mpg')],mean), mpg>25)

and here is a solution with data.table

library(data.table)
M <- data.table(mtcars)
M[mpg>20, .(mpg=mean(mpg)), by=gear][mpg>25]