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.
$\begingroup$
$\endgroup$
1
-
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 VriesCommented May 29, 2015 at 6:02
Add a comment
|
1 Answer
$\begingroup$
$\endgroup$
2
(* 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]
]
(* 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.
answered May 28, 2015 at 19:39
-
$\begingroup$ Just curious - is there a particular secret-decoder-ring reason you used a long form vs
#[[1]] <= x < #[[2]]? $\endgroup$– ciaoCommented May 29, 2015 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$ Commented May 30, 2015 at 6:33