7

Here's what I'm trying to do. My data frame has a factor variable, "country", and I want to split the data frame based on country. Then, I want to take the column mean over every variable for every country's data frame.

Data here: https://github.com/pourque/country-data

I've done this so far...

myList <- split(df1, df1$country)
for(i in 1:length(myList)) {
aggregate <- mapply(myList[[i]][,-c(38:39)], colMeans)
}

(I'm not including the 38th and 39th columns because those are factors.)

I've read this (function over more than one list) , which makes me think mapply is the answer here...but I'm getting this error: 

Error in match.fun(FUN) : 
'myList[[i]][, -c(38:39)]' is not a function, character or symbol

Maybe I'm formatting it incorrectly?

Community
  • 1
  • 1
Anshu Chen
  • 423
  • 2
  • 9
  • 23

4 Answers4

7

It's straightforward in base R using aggregate without the need to split the data.frame into a list beforehand. Here's an example using the built-in iris data where you compute the mean of all variables except those in the first and second column by group of Species:

data(iris)
aggregate(. ~ Species, iris[-(1:2)], mean)
#     Species Petal.Length Petal.Width
#1     setosa        1.462       0.246
#2 versicolor        4.260       1.326
#3  virginica        5.552       2.026

The . inside aggregate is used to specify that you want to use all remaining columns of the data.frame except the grouping variable (Species in this case). And because you specify iris[-(1:2)] as input data, the first and second columns are not used either.

For your data, it should then be something like:

aggregate(. ~ country, df1[-c(38:39)], mean)
talat
  • 68,970
  • 21
  • 126
  • 157
  • This is a great answer. Thank you for the thorough explanation! When I run it, though, it still averages gender (col. 39) instead of omitting it. (Not a big deal for the data, but is there a way to prevent this?) – Anshu Chen May 04 '15 at 17:49
  • @Anshu, can you try again please with the slightly modified code at the bottom? – talat May 04 '15 at 17:52
  • 1
    Or try using `df1[-c("age", "gender")]` inside aggregate – talat May 04 '15 at 17:53
  • It's especially weird that I'm getting averaged gender values of >3 since the only gender choices are supposed to be 1 or 2. E.g., df1[df1$country=="AF",] gives only one observation whose gender is 2, but the aggregate gives gender=3! – Anshu Chen May 04 '15 at 17:55
  • The modified version doesn't seem to be working, and attempting df1[-c("age", "gender")] gives the warning "invalid argument to unary operator" :( – Anshu Chen May 04 '15 at 17:59
  • Thats strange. I haven't downloaded your data yet. Might do that when I'm back on my computer – talat May 04 '15 at 18:02
  • @Anshu, I downloaded your data, read it into R and then ran `x <- aggregate(. ~ country, df1[-c(38:39)], mean)`. It produces the correct output and doesn't include the columns "gender" or "age". There must have been a typo in the code you tried or perhaps a different data structure than in the example on github. – talat May 04 '15 at 21:09
  • Ah, thanks! I got it to work in the end...thanks for all your help :D – Anshu Chen May 04 '15 at 22:45
6
library(dplyr)

df1 %>%
    group_by(country) %>%
    select(-age, -gender) %>%
    summarise_each(funs(mean))
Gregor Thomas
  • 136,190
  • 20
  • 167
  • 294
4

A data.table answer:

library(data.table)

setDT(df1)[, lapply(.SD, mean), by = country, .SDcols = -c('age', 'gender')]

Now tidier syntax with deselection in .SDcols, thanks to user Arun

To explain what's happening here:

  • setDT(df1) make the data.frame a data.table
  • lapply(.SD, mean) for each column in the subset of data, take the mean
  • by = county do this by groups split according to country
  • .SDcols = -c('age', 'gender') omit age and gender columns from the subset of data
arvi1000
  • 9,393
  • 2
  • 42
  • 52
  • I kind of don't like the readability of this vs some of the other answers. Any other data.table people want to suggest an improvement? – arvi1000 May 04 '15 at 17:20
  • Instead of the `setdiff` thing, couldn't you just use `.SDcols`? – Frank May 04 '15 at 17:23
  • Could specify all the desired cols to .SDcols, but that seems about the same as the above. Is there a tidy way to use .SDcols to omit specified columns? – arvi1000 May 04 '15 at 17:31
  • `setDT(df1)[, lapply(.SD, mean), by = country, .SDcols = -c("age", "gender")]` – Arun May 04 '15 at 17:34
  • It looks like the latest version has nicer syntax for `.SDcols`, yeah, in items 14-17 here: https://github.com/Rdatatable/data.table#changes-in-v195--in-development-on-github Even with the CRAN version of `data.table` you could do `df1[ , lapply(.SD, mean), by=country,.SDcols=setdiff(names(df1),c('age','gender'))]`, though, right? – Frank May 04 '15 at 17:35
  • Aha, thanks @Arun! Didn't know you could do deselection with `-` in `.SDcols`. @Frank, yes def, but that seemed similar is all i meant (in that it still requires setdiff) – arvi1000 May 04 '15 at 17:54
4

If you insist on keeping all in list: 

#split and make list of df
myList <- split(df, df$country)

#aggregate without age and gender
my_aggregate <- function(df_inlist) {
  df_inlist <- aggregate(.~country, df_inlist[ , -c(38, 39)], mean)
}

#Apply aggregate function on all data frames in the list
out <- lapply(myList, function (x) {
  my_aggregate(x)
})

out is a list of data.frames for each country and colmeans over variables. How put it all together in a data.frame : 

composite_df <- do.call(rbind, out)
vagabond
  • 3,526
  • 5
  • 43
  • 76