Data Table Manipulation

Question

Suppose that I have the following

table = List[{"ID", "Variable 1", "Variable 2"}, {"Alpha", 1, 
   0}, {"Beta", 1, 1}, {"Alpha", 0, 0}, {"Gamma", 1, 0}, {"Gamma", 2, 
   2}]

Regard to this Post, we can use select (user-defined function) to select what we want based on either rows or columns. For example, we can use

select[table, where["ID" == "Alpha"]]

the return will be {{"Alpha", 1, 0}, {"Alpha", 0, 0}}. If I want to select multiple categories, for example, I want "Alpha" and "Gamma", I can use

select[table, where["ID" == "Alpha"||"ID" == "Gamma"]]

My point here is that you can do this if you have small table. What if you have very large table and you need to select multiple categories. You may have to write

select[table, where["ID" == "Alpha"||"ID" == "Gamma"||"ID"=="Beta"||....so on]]

What I did was to use

Flatten[select[table, where["ID"==#]]&/@{"Alpha", "Gamma"},1]

but it is slow if you have very large table.

Could anyone give me suggestions?

Answer 1

This is what you want (if you want to stay within the syntax of the linked post):

select[table, where[MemberQ[{"Alpha", "Beta"}, "ID"]]]

On a large table, this will be rather slow, yes. But not much slower than using explicit lists, because my solution you linked simply constructs a pure function at the start, and then everything would be the same as if you would've constructed it yourself.

If one wants to optimize some queries, one needs indexes, which I wasn't concerned with in that post. Generally, that post should not be viewed as a complete solution, but rather as an illustration that one can add friendly syntax rather easily.

Answer 2

Make your own, e.g.:

mySelect[col_, vals_] := 
 Select[table[[2 ;;]], 
  MemberQ[vals, #[[Position[table[[1]], col][[1, 1]]]]] &]

mySelect["ID", {"Beta", "Gamma"}]

(*  {{"Beta", 1, 1}, {"Gamma", 1, 0}, {"Gamma", 2, 2}}  *)

mySelect["Variable 1", {1, 2}]

(* {"Alpha", 1, 0}, {"Beta", 1, 1}, {"Gamma", 1, 0}, {"Gamma", 2, 2} *)

But honestly, better to just do things with native list capabilities vs a toy (no slight intended or implied) "SQL", or if you need/want SQL, use one with Mathematica's SQL interface capabilities.

Answer 3

For this kind of task I use a helper function to create pure functions from expressions such as "ID" == "Alpha", replacing column labels like "ID" with their appropriate Slot. The resulting pure functions may then be used in Select, Map and Apply etc.

ColumnFunction::usage = "ColumnFunction[expr_, columns_List]; Constucts a pure function,
suitable for Apply or MapThread to use, taking in expr as the function expressed in
terms of column names and replacing them with the appropriate #n taken from their order 
in the list columns.";
ColumnFunction[expr_, columns_List] := ColumnFunction[expr, columns] =
    Module[ {rules},
      rules = Apply[Thread[# -> Range[Length[#]]]&, {columns}] /. i_Integer :> Slot[i];
      Reap[Off[Part::"partw"];
      Sow[Apply[Function, Hold[expr] /. rules]];
      On[Part::"partw"];][[2, 1, 1]]
    ];
SetAttributes[ColumnFunction, HoldFirst];

Then use Select wrapped in this manner...

selectResultsBy::usage = StringJoin["Selects from the data given those rows for which
the given expression as passed through ColumnFunction with the first row of headedData
which matches the pattern ", headedDataForm, " returns True."];
SetAttributes[selectResultsBy, HoldRest];
selectResultsBy[headedData_List, expr_] := Module[{f},
  Apply[Apply[Function[{header, data},
    f = ColumnFunction[expr, header];
    Join[{header}, Select[data, Apply[f, #]&]]], {First[#], Rest[#]}]&, {headedData}]];