2
$\begingroup$

I have a large data set that I would like to group based upon the quantiles of one specific column. My test data set is about 2.1 million rows by 9 columns (the actual data set will be much larger). I've computed the quantiles of one of the columns of this data set. I would like to create sublists of this dataset based upon these quantile values as lower and upper bounds. For instance, say I compute the {0.25,0,50, 0.75) quantiles of the kth column and get the values {100,500,900}. I would like an efficient way to group the larger data according to {0<= k[i]<100,100<= k[i]<500,500<=k[i]< 900, k[i]>=900}. In practice I will have much larger sets of upper and lower bounds for grouping. It seems like BinLists[] would be one way, but it appears unwieldy for large sets of bounds.

$\endgroup$
  • 4
    $\begingroup$ By the way, you have asked 6 questions but have accepted no answers. Was there something wrong with the answers you received? $\endgroup$ – Sjoerd C. de Vries May 29 '15 at 6:02
4
$\begingroup$
(* generate some example data *)
data = RandomInteger[{1, 100}, {100, 5}];

(* find quantiles of the kth column; 
   must include the zeroth quantile,
   but not the upper limit (1) *)
k = 1;
quants = Quantile[data[[All, k]], {0, 0.25, 0.50, 0.75}]

(* {1, 16, 37, 62} *)

(* define a quantile membership function *)
qMember[x_] = 
 Piecewise[
  MapIndexed[
   {#2[[1]], #1[[1]]<=x<#[[2]]} &, 
   Partition[quants, 2, 1]
  ], 
  Length[quants]
 ] 

Mathematica graphics

(* group the data on the quantile membership of their kth column *)
GroupBy[data, qMember[#[[k]]] &]

(* <|
2 -> {{33, 5, 16, 63, 79}, {22, 16, 83, 30, 12}, ..., {29, 63, 82, 53, 13}}, 
3 -> {{52, 33, 56, 33, 56}, {40, 6, 2, 78, 85},..., {60, 9, 93, 8, 78}}, 
4 -> {{95, 46, 91, 87, 1}, {93, 17, 56, 26, 96}, ..., {97, 56, 58, 22, 30}}, 
1 -> {{13, 42, 87, 73, 74}, {3, 20, 57, 32, 48}, ..., {4, 99, 62, 50, 0}}|>*)

Alternatively, ArrangeBy could be used if you don't like getting an Association as result.

$\endgroup$
  • $\begingroup$ Just curious - is there a particular secret-decoder-ring reason you used a long form vs #[[1]] <= x < #[[2]]? $\endgroup$ – ciao May 29 '15 at 23:15
  • $\begingroup$ No, it's a remnant from an earlier development where I wrote a<x<b as Less[a,x,b] (I don't have to worry about operator precedence in that form) and I kept on working with the FullForms of the expression even when they became considerable less compact. I realized that after pasting the code here, but didn't have time the prettify things ( or was just too lazy). Updated it now, thanks. $\endgroup$ – Sjoerd C. de Vries May 30 '15 at 6:33

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.