# Data Table Manipulation in Mathematica

I am a statistician searching for an efficient way to select rows or columns from a table of data in Mathematica. Let me pose the question in 2 parts with a SQL-style table of data:

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


Which, when formatted as a Grid looks roughly like this:

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


Part 1: How can the data in the header of the table, for example "ID", be set as the name of the list for that column? Ideally, the result would allow you to do the following:

In[24]:= ID

Out[24]= {"Alpha", "Beta", "Alpha"}


Would one need to write a function to dissect the header row and then line up the header names as the name of a list that corresponds to the appropriate header? Although one might ask 'Why not refer to everything as a position and avoid the renaming headache entirely?' it is extremely cumbersome when working with tens or hundreds of columns/variables to use a meaningless position to reference a variable.

Part 2: How can an individual row, or subset of rows, be returned from a table? Essentially I'm looking for the equivalent of the "WHERE" clause in SQL or the "subset" function in R.

For example in the "ID" column I might want to retrieve all the rows where "ID" == "Alpha". Do I have to create a method that iterates over the "ID" list, stores the position in the list where the value of the element is equal to "Alpha", and then concatenate a list that contains the value in that position for all the other lists?

I'm confident I could write the functions I mention, but it seems unconscionable that Mathematica would overlook such a rudimentary data manipulation task. I understand there's also the DataManipulation package that allows for SQL queries, but I have to believe (hope?) there's a way native to Mathematica that's quicker.

Thank you for indulging me! And my apologies in advance to all the Mathematica aficionados who might see this as a corrupt question for trying to program in another language while in Mathematica!

-

## migrated from stackoverflow.comJul 28 '13 at 6:31

This question came from our site for professional and enthusiast programmers.

Just check out the DatabaseLink reference.wolfram.com/mathematica/DatabaseLink/tutorial/… It has an easy syntax ... and is real SQL. – Dr. belisarius Jan 25 '11 at 4:40

I think, your question has 3 levels: convenient syntax, data representation, and efficiency. I can offer a very lightweight solution which addresses all of these in the simplest way: syntax is resembling SQL but not exactly the same, data representation - just lists, as in your example (we do not make custom wrappers, objects of any kind, etc), and efficiency will be similar to the standard SQL select, in terms of asymptotic complexity of the query (but not in absolute timings of course):

Clear[getIds];
getIds[table : {colNames_List, rows__List}] := {rows}[[All, 1]];

ClearAll[select, where];
SetAttributes[where, HoldAll];
select[table : {colNames_List, rows__List}, where[condition_]] :=
With[{selF = Apply[Function, Hold[condition] /.
Select[{rows}, selF @@ # &]];


Here is how you could use it:

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

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

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

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

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

-
I haven't timed Dispatch, but wouldn't it a waste of time to built a dispatch table for every call to select (especially for very wide tables? – Sjoerd C. de Vries Nov 23 '11 at 11:35
@Sjoerd It would be much less waste than not doing it in this approach, assuming that the table in question has many rows - since otherwise, the full list of rules for the correspondence between the column names and indices will have to be applied generally several times for every invocation of the pure function in Select, that is - for every row. That would be a real waste. – Leonid Shifrin Nov 23 '11 at 11:39
I know you want to have it work more generic, i.e., for any table. In my case, I'm mostly the only user of the software and life is easier mostly addressing one table at a time. I just do a one-time definition like column["header1"]=1 using MapThread. – Sjoerd C. de Vries Nov 23 '11 at 11:56
@Sjoerd But this is essentially the same - Dispatch works similarly to DownValues - but is local (I don't have to clear or remove column later), which is why I prefer it. I could introduce a local symbol with Module, and do it your way. The point is, for many tables, it is easier to not think about bookkeeping and reconstruct the mapping for every table afresh. The performance hit will only be noticeable for lots of queries done on extremely shallow tables with just a few rows - which is probably not the most common use case. – Leonid Shifrin Nov 23 '11 at 12:01

I deal a lot with database table data, and have created some tools to handle that. For me the biggest difficult was that, when the code gets bigger, you don't remember the position of your columns, and the code readability gets very confusing . In your case, I would work like this:

dataObj ← mrtDataObject[{{"ID","Variable 1","Variable 2"},
{"Alpha",1,0},{"Beta",1,1},{"Alpha",1,0}}]; (* Use Esc <- Esc for ← *)
dataObj[[All, "ID"]]


{"Alpha", "Beta", "Alpha"}

Select[dataObj, #[["ID"]] == "Alpha" &]


{{"Alpha", 1, 0}, {"Alpha", 1, 0}}

GatherBy[dataObj, #[["ID"]] &]


{{{"Alpha", 1, 0}, {"Alpha", 1, 0}}, {{"Beta", 1, 1}}}

Some aditional Properties from DataObject can be viewed using dataObj["Properties"], where you get:

So if you ask for dataObj["Data"] you get:

{{Alpha,1,0},{Beta,1,1},{Alpha,1,0}}

Another advantage is that you can use Part to change dataObj value, and have the notation dataObj."Column Name" to get it Index. For example:

dataObj["Data"][[All, dataObj."Variable 1"]] = 3 dataObj["Data"][[All, dataObj."Variable 1"]]


So Dot was overload (with UpValues) to get dataObj."Variable 1" = 2

I also reserve the structure dataObj[{"columns A","columns B"}] to get index data, so: dataObj@{"ID","Variable 2"} would return {1,3}. Very useful in situations like dataObj[[All,dataObj@{"ID","Variable 2"}]]

Another interesting point is to attribute what I call Metadata into your dataset, for example, you can do dataObj["NewMetaData"]={"meta1","meta2"}, so when you have to use your data set, all your data information is in the object dataObj. Your code is much cleaner, for example, when you want to create some plots that use dataObj's information — your function needs to receive just dataObj as an argument, and all the information you need is inside it.

## The code:

(*mrtCreateBlockPart*)
SetAttributes[mrtSetPartBlock,HoldFirst]
mrtSetPartBlock[symb_[idx__][[part__]],var_]:=Module[{x=symb[idx]},
x[[part]]=var;
symb[idx]=x
]

mrtCreateBlockPart[symb_]:=Module[{},
Unprotect[Part];
Set[symb[idx__][[part__]], var_] ^:= mrtSetPartBlock[symb[idx][[part]],var];
Protect[Part];
]

(*mrtDataObject*)
mrtDataObject[data_]:=mrtDataObject[Rest@data,First@data]

mrtCreateBlockPart@newObj;

newObj["Data"]=body;
newObj["Dim"]=Dimensions@newObj["Data"];
newObj["Rows"]=First@newObj["Dim"];
newObj["Columns"]=Last@newObj["Dim"];

SetAttributes[newObj,HoldAll];

newObj/:Part[newObj,x__]:=Part[newObj["Data"],Sequence@@Evaluate@newObj@List@x];
newObj/:Select[newObj,x__]:=Select[newObj["Data"],Sequence@@Evaluate@newObj@List@x];
newObj/:GatherBy[newObj,x__]:=GatherBy[newObj["Data"],Sequence@@Evaluate@newObj@List@x];
newObj/:SortBy[newObj,x__]:=SortBy[newObj["Data"],Sequence@@Evaluate@newObj@List@x];

Format[newObj]:=Row[{"DataObject(Dimensions:",newObj["Dim"],")"}];
newObj
]

(*LeftArrow*)
SetAttributes[LeftArrow,HoldFirst];
new_ ← org_:=With[{prop={Attributes,UpValues, OwnValues, DownValues, SubValues, NValues, FormatValues, Messages,Options}},
ClearAll@new;
Set[#@new,#@org/.HoldPattern@org:>new]&~Scan~prop;
mrtCreateBlockPart@new;
If[MemberQ[Attributes@org,Temporary],ClearAttributes[new,Temporary];Remove[org]];
]


An observation: dataObj ← mrtDataObject[data] is necessary instead of dataObj=mrtDataObject[data] to make it possible to change dataObj using Part (use Esc+<+-+Esc). And the mrt comes from my pack Murta.

-
I always forgot how to format buttons! Tks for your revision @rm -rf! Much better english. – Murta Jul 28 '13 at 17:03

Now in V10 you can use Dataset to handle data using columns names. For example:

data={{"ID", "Variable1", "Variable2"}, {"Alpha", 1, 0}, {"Beta", 1, 1}, {"Alpha", 1, 0}};


Now, to get column ID you can just do.

ds[All,"ID"]


{"Alpha","Beta","Alpha"}

To select using "Where" clause, you can:

ds[Select[#Variable2==1&],{"ID","Variable1"}]


See Dataset docs for more details.

PS: In columns names, I have removed the space between Variable and it number, to make notation simpler. You can keep it, but you will have to use a more verbose notation inside select (Slot["Variable 2"] instead of #Variable 2), unfortunately the same occurs with underscore, that is a very usual separator in database column names.

-
I think it is very kind of you (and very useful for people reading this question) to provide this important update regarding v10! – Adrian Nov 30 '14 at 0:16
@Murta Probably worth adding the association versions for completeness assoc = AssociationThread[First@data -> #] & /@ Rest@data; assoc[[All, {"ID", "Variable1"}]] (* {<|"ID" -> "Alpha", "Variable1" -> 1|>, <|"ID" -> "Beta", "Variable1" -> 1|>, <|"ID" -> "Alpha", "Variable1" -> 1|>} ) Select[#"ID" == "Alpha" &]@assoc ( {<|"ID" -> "Alpha", "Variable1" -> 1, "Variable2" -> 0|>, <|"ID" -> "Alpha", "Variable1" -> 1, "Variable2" -> 0|>} *) – Gordon Coale Dec 23 '14 at 17:37