1
$\begingroup$

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!

|improve this question
$\endgroup$
  • $\begingroup$ 1) "first column should equal to 18 to 60" means that values in that column should be in 18 to 60 interval, or 18 or 60? 2) Do does not return anthing, you need to use Table 3) but this approach is not ok anyway you can start with: Select[mytable, (#[[1]] == 18 || #[[1]] == 60) && (#[[2]] == 1 || #[[2]] == 5)&], or Cases[mytable, { 18|60, 1|5, ___}] $\endgroup$ – Kuba♦ Sep 19 '18 at 7:59
  • $\begingroup$ 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. $\endgroup$ – Johu Sep 19 '18 at 8:23
  • $\begingroup$ Is your data an array? I.e., do all columns contain integers? $\endgroup$ – LLlAMnYP Sep 19 '18 at 8:34
  • $\begingroup$ 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). $\endgroup$ – Rick Sep 27 '18 at 7:49
3
$\begingroup$

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.

|improve this answer
$\endgroup$
  • $\begingroup$ Thank you for your help! $\endgroup$ – Rick Nov 11 '18 at 4:13
2
$\begingroup$

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@%

enter image description here

(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@%

enter image description here

|improve this answer
$\endgroup$
  • $\begingroup$ Thank you for your help! $\endgroup$ – Rick Nov 11 '18 at 4:13

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.