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]}];
   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.


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

  • $\begingroup$ @ rasher, thank you for your answer. I will get back to you after I check whether it works with my real data. $\endgroup$ – ramesh Mar 29 '15 at 6:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.