Find specific rows based on column entry

Question

Suppose we have a table like the following:

A B C
0 0 0
0 0 1
0 1 0
0 1 1
1 0 0
1 0 1
1 1 0
1 1 1

Say, if I want to find all rows satisfying (col A = 0 and col C = 1). Is there a way to do such query in Mathematica?

The real dataset is 4GB of hundreds of columns and hundreds of thousands of rows consists of strings, integers, and floating numbers, so doing a pattern matching might not be a feasible solution.

The end result should be a list of indices of matching rows.

Answer 1

Here are two options. The first relies on pattern-matching, creating a pattern on the fly based on your choice of columns and values. The second relies on using Sow and Reap while Selecting those rows that match.

findRows1[data_List, columns : {__Integer}, values_List] /; Length@columns == Length@values := 
  Flatten@Position[data, 
    Normal@SparseArray[Thread[columns -> values]~Append~(_ -> _), {Last@Dimensions@data}]
   ]

findRows2[data_List, columns : {__Integer}, values_List] /; Length@columns == Length@values :=
  Flatten@Module[{i = 0},
    Last@Reap@Select[data, If[i++; #[[columns]] === values, Sow[i]; True, False] &]
   ]

(I think there's a more elegant way of spitting out the current row in the second function, but this works.)

Let's take a sample list the following 1000 by 500 list:

SeedRandom[0]
mat = RandomChoice[{"a", "b"}, {500, 1000}];

Then, let's specify that we want to pick columns 1 and 3 to contain "a" and "b", respectively:

findRows1[mat, {1, 3}, {"a", "b"}]
findRows2[mat, {1, 3}, {"a", "b"}];
% === %%

Once we get more answers, someone can do these timings, but in the case above (when you're only testing two columns, the first evaluated in 0.05 seconds, and the second evaluated in 0.0014 seconds.

Answer 2

Import the data as a table, convert to a Association to enable fast lookups (if you have 4GB of data then I don't recommend using a Dataset

list = ImportString["A B C
   0 0 0
   0 0 1
   0 1 0
   0 1 1
   1 0 0
   1 0 1
   1 1 0
   1 1 1", "Table"];
dset = 
 Map[AssociationThread[First@list , #] &, Rest@list]
(* {<|"A" -> 0, "B" -> 0, "C" -> 0|>, <|"A" -> 0, "B" -> 0, 
  "C" -> 1|>, <|"A" -> 0, "B" -> 1, "C" -> 0|>, <|"A" -> 0, "B" -> 1, 
  "C" -> 1|>, <|"A" -> 1, "B" -> 0, "C" -> 0|>, <|"A" -> 1, "B" -> 0, 
  "C" -> 1|>, <|"A" -> 1, "B" -> 1, "C" -> 0|>, <|"A" -> 1, "B" -> 1, 
  "C" -> 1|>} *)

Use Select to get the rows you want,

Select[dset, #A == 0 && #C == 1 &]
(* {<|"A" -> 0, "B" -> 0, "C" -> 1|>, <|"A" -> 0, "B" -> 1, 
  "C" -> 1|>} *)

From here, it's not too painful to get the indices from the rows,

Select[dset, #A == 0 && #C == 1 &] // (Position[dset, 
     Alternatives @@ #] &) // Flatten
(* {2, 4} *)

Answer 3

If all of the columns are part of one table, try

matrix = RandomInteger[{0, 1}, {8, 3}];

For[i = 1, i <= Length[matrix], i++, 
    If[matrix[[i, 1]] == 0 && matrix[[i, 3]] == 1, Print[i]]];