How to combine two or more groups while selecting form the unbalanced panel data if the number of obs is less than a scalar in certain groups

Question

I have an unbalanced panel data. I want to calculate group statistics for each group. However, if ith group has less than K obs, then I want to combine ith and (i+1)th groups to calculate group statistics and consider ith and (i+1)th as one group. Similarly, if number of obs after combining i and i+1 groups do not have K obs, then I want to combine i, i+1 and i+2 groups to calculate group statistics and so on. For example, I have the following unbalance panel:

data = Table[{RandomChoice[{1, 2, 4, 6, 7, 8, 10, 12, 13, 17, 18, 19, 
      20, 22, 25, 30}], i, i*2, i*3}, {i, 1, 200}];

By doing the following, I can calculate group wise mean and number of obs for each group.

key = {1, 2, 4, 6, 7, 8, 10, 12, 13, 17, 18, 19, 20, 22, 25, 30};
myfun[dat_] := Flatten[{N[Mean[dat]], Length[dat]}];
Prepend[Table[
   myfun[Select[data, #[[1]] == key[[i]] &]], {i, 1, 
    Length[key]}], {"ID", "Var1", "Var1", "Var1", "N"}];

However, this does not do what I want. I want to cobine two groups (ids) according to above criteria if any group has less than 12 observations. Any help is greately appreciated. If the equestion is not clear, please write comment. I will update it.

Answer 1

I think this does what you're after (to be honest, I had some trouble deciphering the question).

patchedData = 
 Module[{gb = GatherBy[SortBy[#, First], First], lens, tot = 0, 
     cnt = 1, mems = {}, sets},
    lens = Length /@ gb;
    sets = 
     Append[Reap[
        Scan[(tot += #; mems = {mems, cnt++}; 
           If[tot >= 12, Sow[Flatten@mems]; mems = {}; tot = 0];) &, 
         lens]][[2, 1]], Flatten@mems];

    Map[Which[Length@# == 0, Unevaluated@Sequence[], Length@# == 1, 
       gb[[First@#]], True, 
       gb[[#[[2 ;;]], All, 1]] = gb[[#[[1]], 1, 1]]; 
       Join @@ gb[[#]]] &, sets]] &@data;

This produces a new list patchedData that combines sets short of 12 elements with subsequent sets until 12 or more are reached, renumbering the ID for the latter to that of the first. Obviously if the last set ID (I assume these to be in order of ID) has too few elements, there's nothing to combine, so it is left untouched.