# Data Table Manipulation in Mathematica: Step 2

In this previous post (Data Table Manipulation in Mathematica) we find the brilliant suggestion proposed by Leonid Shifrin that allows to select specific rows identified by the header name.

I'm now looking for a way to then also select the data of a specific column (or more columns) of the subset of data generated by Leonid's select function.

Suppose we have this data:

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

which when formatted as a grid looks like this:

ID     Variable 1 Variable 2
Alpha       1          0
Beta        1          1
Alpha       0          0

Using Leonid's function we can obtain this:

subset = select[table, where["Variable 1" == 1]]

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

Suppose that we now want to sum all the values that are in the column named 'Variable 2'. We could just do this:

Total[subset[[All,3]]]

However, in this way we loose the advantage of using Leonid's function that can work directly with header names. Ideally, I would like to find a way to get those data by using the header name. Something like:

Total[subset[[All,"Variable 2"]]]

or, even better:

select[table, where["Variable 1" == 1], columns["Variable 2"]]

Any hints would be highly appreciated.

This modification of select should do what you ask, while keeping the previous functionality:

ClearAll[select, where];
SetAttributes[where, HoldAll];
select[table : {colNames_List, rows__List}, where[condition_],
cols : (columns[varNames__] | All) : All] :=
With[{namingRules =
With[{selF = Apply[Function, Hold[condition] /. namingRules],
cl = {varNames} /. namingRules /. Verbatim[Slot][n_] :> n},
If[cols ===  All, #, #[[All, cl ]]] &@Select[{rows}, selF @@ # &]]];

For example,

select[table,where["Variable 1"==1],columns["Variable 1"]]
(* {{1},{1}} *)

select[table,where["Variable 1"==1],columns["Variable 2","Variable 1"]]
(* {{0,1},{1,1}} *)

select[table,where["Variable 1"==1]]
(* {{Alpha,1,0},{Beta,1,1}} *)

You can also use the plain column numbers in columns:

select[table,where["Variable 1"==1],columns[3,2]]
(* {{0,1},{1,1}} *)

or a mix:

select[table,where["Variable 1"==1],columns[3,"Variable 1"]]
(* {{0,1},{1,1}} *)

EDIT

Per request of @Viktor, expressed in comments, here is a generalization of select where the where clause is made optional as well:

ClearAll[select, where];
SetAttributes[where, HoldAll];
select[table : {colNames_List, rows__List}, w : (where[condition_] | None) : None,
cols : (columns[varNames__] | All) : All] :=
With[{cl = {varNames} /. namingRules /. Verbatim[Slot][n_] :> n},
If[cols === All, #, #[[All, cl]]] &@
If[w === None,
{rows},
(* else *)
With[{selF = Apply[Function, Hold[condition] /. namingRules]},
Select[{rows}, selF @@ # &]
]
]
]
];

For example, in addition to the previous inputs, we may enter:

select[table,columns["Variable 1"]]
(* {{1},{1},{0}} *)
• +1 Perhaps you should mention that this version also allows the use of raw column numbers (the Slot part of your code). Nov 23, 2011 at 12:28
• @Sjoerd True, I meant this, but then had a second thought that this might not be a good style, so did not mention that. Perhaps better to mention. Will edit. Nov 23, 2011 at 12:47
• Perfect. Thank you a lot. Nov 23, 2011 at 13:34
• @Viktor Glad I could help. I actually liked these questions because these are good and small examples of putting meta-programming (construction of a function programmatically and at run-time) to do some real non-trivial work that makes our life easier. Nov 23, 2011 at 13:38
• @Viktor Please see my edit Nov 26, 2011 at 2:33

I had to leave just when you posted the question, so I'm a little late, but here's my implementation anyway.

Cases[table, {id_, var1_, var2_} /; var1 == 1 -> var2]

What you require is not to have to write out the full row pattern, but to be able to refer to row items by names. Here's my implementation for that. It should be easy to understand and modify if you so desire.

SetAttributes[namedCases, HoldRest]

With[
},
SetAttributes[patternNames, Temporary]; (* avoid name pollution *)
Module[{r = {rows}},
Unevaluated@Cases[r, headPatt /; condition :> transform] /.
]
]

Note: I used Module to avoid replacement inside the table (not header).

It was my aim to make this as easily modifyable as possible, if you want to extend this way of working with tables to other operations. You can do a lot by only changing the Cases[] line to something else.

Here I am assuming that your header consists of strings only, not symbols or numbers. Otherwise this function may need to be strengthened to avoid unexpected breakage.

Usage:

namedCases[table, "Variable 1" == 1, 2*"Variable 2"]
• Very nice - +1. Nov 23, 2011 at 15:20
• Very nice indeed. Nov 23, 2011 at 17:29