Split Data into groups of equal means

Question

I'm looking for a way to split a data frame into groups of equal size (essentially same number of rows in each group), whose groups have a nearly equal mean.

User Data
1 5.0
2 4.5
3 3.5
4 6.0
5 7.0
6 6.5
7 5.5
8 6.2
9 5.7
10 5.9

This is very similar to this request However this only splits the data into 2 groups.

My actual dataset contains anywhere from 75-150 rows, and I need to split it into anywhere from 5-10 groups of equal mean and fairly equal size.

I've researched on Google & Stack Exchange for the last few days, and I'm just not having much luck. Any guidance would be great.

Thanks in advance!

More details:

Maybe I need to provide some more details, below I've included a real dataset. We are a transportation company, this data set has Driver ID, Miles, Gallons provided. What I have been doing is reading the data into R, and adding and MPG column like so:

data <- read.csv('filename')  
data$MPG <- data$Miles / data$Gallons

Then I tried the two provided answers below. Arun's idea gives me almost equal group sizes (9 members per group, 10 groups), however the variation of the means is large, from 6.615 - 7.093 which is too large of a variation for me to start off with. Thomas' idea gets a little bit tighter variation, but the group sizes are all different from 6 - 13 members.

What we are looking to do is improve fleet MPG, and we're going to accomplish this with a team based competition, so I need to randomly put the teams together with them all starting from relatively the same group MPG.

Maybe that helps and can lead us in the correct direction? I tried doing this just in my programming language, but it locks the computer up every time, so I figured that R would probably be able to process the data better.

Thanks again!

Answer 1

Going by Thomas' idea, here's a brute-force/greedy approach, which'll give more or less the same values (you can opt for more repetitions until you agree with the closeness of the solution).

# Assuming the data you provided is in `df`
grp <- 5
myfun <- function() {
    samp <- sample(nrow(df))
    s.mean <- tapply(df$Data, samp %% grp, mean)
    s.var <- var(s.mean)
    list(samp, s.mean, s.var)
}
out <- replicate(1000, myfun(), simplify=FALSE)
min.pos <- which.min(sapply(out, `[[`, 3))
min.idx <- out[[min.pos]][[1]]
split(df$Data[min.idx], min.idx %% grp)

$`0`
[1] 7.0 5.9

$`1`
[1] 5.0 6.5

$`2`
[1] 5.5 4.5

$`3`
[1] 6.2 3.5

$`4`
[1] 5.7 6.0

---

This is how out[min.pos] looks like:

out[min.pos]

[[1]]
[[1]][[1]]
 [1]  7  9  8  5  3  4  1  2 10  6

[[1]][[2]]
   0    1    2    3    4 
5.85 5.70 5.60 5.25 5.50 

[[1]][[3]]
[1] 0.05075

Answer 2

If similar means is really all that matters, I've put together a simulation below that basically looks at a bunch of different combinations of the data (n) for a particular group size (k) and then minimizes the variance of the group means. With that minimization you can then extract that grouping from the simulation results.

df <- data.frame(User=1:1000,Data=rnorm(1000,0,1))     # example data
myfun = function(){
    k <- 5                                             # number of groups
    tmp <- seq(length(mpg))%%ngroups                   # really efficient code from @qwwqwwq's answer
    thisgroup <- sample(tmp, dim(df)[1], FALSE)        # pull a sample
    # thisgroup <- sample(1:k,dim(df)[1],TRUE)         # original version
    thisavg <- as.vector(by(df$Data, thisgroup, mean)) # group means
    thisvar <- var(thisavg)                            # variance of means
    return(list(group=thisgroup, avgs=thisavg, var=thisvar))
}
n <- 1000 # number of simulations
sorts <- replicate(n, myfun(), simplify=FALSE)
wh <- which.min(sapply(sorts, function(x) x$var))      # minimization
# sorts[[wh]]                   # this is the sample you want
split(df, sorts[[wh]]$group)    # list of separate dataframes for each group

You could also have k of different sizes, if you don't care about how many cases are in each group by just moving the k <- 5 line into the function and having it be a random draw from the range of number of groups you're willing to have.

There are probably other ways to do this, though.

Answer 3

Simplest way I can think of: Sort the data, modulo all the indicies by the number of groups, and you're done. Should work well if the data are normally distributed I think. Has the advantage of the groups being as equally sized as possible.

mpg <- rnorm(150)
mpg <- sort(mpg)
ngroups = 13
df = data.frame( mpg=mpg, group=seq(length(mpg))%%ngroups)
tapply(df$mpg, df$group, mean)

           0            1            2            3            4            5            6            7            8 
 0.080400272 -0.110797283 -0.046698548 -0.014177675  0.024410834  0.048370962  0.066265303  0.087119914 -0.062259638 
           9           10           11           12 
-0.042172496 -0.003451581  0.033853024  0.056947458