Select rows of data that satisfies both conditions simultaneously

Question

I'm a new user of Mathematica and now I am confused by a problem.

Now I have about 2 million rows of data and 3 columns, i.e. a 2million*3 table. What I am trying to do is select rows of data satisfying two 'AND' conditions, the first column should equal to 18 to 60 AND the second column equal to 1 to 5(both conditions with step 1). Then I have the data in the 3rd column with the same 1st and 2nd column value. The next step I will calculate the Mean or Median or apply distribution analysis base on the Selected results. For example, I need select rows with conditions of Cloume1=18 AND Column2=1.

I wrote

Do[Select[mytable, If[#[[1]] == i&&#[[2]] == j, True, False] &], {i, 18, 60, 1},{j,1,5,1}]

and nothing(NULL) is shown in the output.

I put some sample data as shown below.

{{18, 1, 3.195}, {18, 1, 3.902}, {60, 1, 5.4333}, {60, 1, 3.1}, {60,1, 6.8594}, {60, 1, 3.666}, {60, 1, 3.902}, {60, 1, 1.2666}, {60, 1,3.902}, {60, 1, 2.3}, {60, 1, 4.3}, {60, 1, 3.902}, {60, 1,3.902}, {60, 1, 3.563}, {60, 1, 3.902}, {60, 1, 51.835}, {60, 1,3.902}}

How can I fix it? Thank you for your help!

If your mytable is very large, the code will be very slow, as the Do loop will make Select go thourhg the data $60\times 5$ times. You should avoid procedurical loops. Maybe something like Tally is already does the sorting you need. — Johu, Sep 19 '18 at 08:23
Is your data an array? I.e., do all columns contain integers? — LLlAMnYP, Sep 19 '18 at 08:34
Thank you all for your response!This is an awesome place to learn! The problem was posed when I was analysing a excel file exported from the population of a huge city data base. I have solved the problem in a very clumsy way(which is part of answer from sunt05):Median[Select[data, #[[1 ;; 2]] == {18, 2} &][[All, 3]]], and repeat this command from 18 to 60.(it took me some time). — Rick, Sep 27 '18 at 07:49

sunt05 · Answer 1 · 2021-10-29T09:03:24.550

3

It looks your problem can be easily dealt with by using Cases.

How about the following:

Cases[sample, {18, 1, _}]

where sample = YourData.

Also, Select can work in this way:

Select[sample, #[[1 ;; 2]] == {18, 1} &]

This page can be a good start for you to learn these techniques: Finding Expressions That Match a Pattern.

edited Oct 29 '21 at 09:03

answered Sep 19 '18 at 08:42

sunt05

4,367
23
34

Thank you for your help! – Rick Nov 11 '18 at 04:13
Hi, I have a similar issue where I want to check two conditions, so e.g. if I have sample ={{0, 0, 7}, {0, 2, 5}, {0, 3, 3}, {5, 7, 10}, {2, 4, 10}, {9, 5, 2}} and then do sol=Cases[sample,{?(#>1),?(#<7),_}&] it gives an empty array, but if I do it in two steps i.e. intersol=Cases[sample,{_?(#>1&),__}] and then sol=Cases[intersol,{,?(#<7&),_}] it gives the answer I want – WaleeK Oct 28 '21 at 15:49
1

@WaleeK I would do it this way: Cases[{a_ /; a > 1, _, c_ /; c < 7}]@sample – sunt05 Oct 28 '21 at 15:52
Thank you so much for the help! I didn't want to pose a separate question as it was too similar but I really appreciate it! – WaleeK Oct 29 '21 at 09:01

LLlAMnYP · Answer 2 · 2018-09-19T08:59:03.270

Let's test the worst case scenario (when all datatypes are completely wrong and the data cannot be a Packed Array).

I generate the fake data with

data = RandomReal[100, {2000000, 3}]; 
data[[;; , 3]] = RandomColor[2000000];

I select the desired elements like so:

filtered = With[{tr = Transpose[data]},
   With[{picker = 
      UnitStep[tr[[1]] - 18]*UnitStep[60 - tr[[1]]]*
       UnitStep[tr[[2]] - 1]*UnitStep[5 - tr[[2]]]},
    Pick[data, picker, 1]]]

Between one and two seconds on my machine.

On the other hand, if the data is well formed, e.g.

data = RandomInteger[100, {2000000, 3}]

Then the code above runs 10 times faster.

And a small sample for a much smaller dataset (100 X 3):

data = RandomInteger[100, {100, 3}];
(* code above here *)

{{32, 1, 39}, {58, 1, 42}, {48, 5, 60}, {33, 4, 37}, {37, 3, 38},
   {24, 1, 83}, {28, 2, 55}, {18, 2, 47}}

I'm not sure I understood your intent correctly, but you seem to want to group the data by the first two elements of your lists?

In that case, you can follow up with the next step:

GroupBy[filtered, Most];
TableForm@Normal@%

(result from a dataset with 3000 elements)

This gives an Association with keys corresponding to the first two elements of your dataset. Alternatively, you can get a list of lists like so:

SplitBy[SortBy[filtered, Most], Most];
Grid@%

Select rows of data that satisfies both conditions simultaneously

2 Answers2