Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

I often work with big tables that I want to treat kind of like a database. Here's an example table.

theTable = {
 {"id","color","size","flavor"},
 {1,"blue",5,"cherry"},
 {2,"green",5,"piquant"},
 {3,"blue",20,"peppermint"}
}

In a database I would ask

SELECT * FROM `theTable` WHERE `color` = 'blue' AND `size` > 10

and get effectively

{{3, "blue", 20, "peppermint"}}

in response. In Mathematica, I need to determine the "column number" for color and size, then use Cases with And to do the same thing.

Cases[theTable[[2 ;;]], a_ /; And[a[[2]] == "blue", a[[3]] > 10]]

This is operationally much clumsier than the database way. Unfortunately setting up a database for every such table is too much extra work, particularly since I want the data to end up in mma anyway.

How can this approach be improved? Specifically, how can I more easily use the column names directly, instead of their part numbers? And how can I avoid the ugly a_/;f[a] pattern?

share|improve this question
5  
Have you seen this answer, and those linked to it, particularly this one? Those can be a starting point. –  Leonid Shifrin Sep 26 '12 at 22:52
    
I had not seen those. That is a neat solution and I'll give it a try. Thank you. –  ArgentoSapiens Sep 26 '12 at 23:01
2  
You know that Extract[theTable, Position[theTable, {_, "blue", x_ /; x > 10, _}]] works, right? –  J. M. Sep 26 '12 at 23:14
1  
@ArgentoSapiens that was important information for us ;) –  Mike Honeychurch Sep 26 '12 at 23:40
1  
@Argento: "that was important information for us" - which you should have included to begin with. –  J. M. Sep 26 '12 at 23:41
show 15 more comments

4 Answers 4

up vote 10 down vote accepted

A way of getting around the a_/;test[a] syntax is to write out the tests in string form, and use replace to insert the values. For this to work you need to build rules from your table. Here is a simple implementation:

 SetAttributes[queryCriteria, HoldAll]
 queryCriteria[theTable_, query_] := Function[{entry}, 
 Unevaluated[query] /. (Rule @@@ Transpose[{theTable[[1]], entry}]), HoldAll]

 Select[theTable, queryCriteria[theTable, "color" == "blue" && "size" > 10]]

Personally I would prefer not having to give theTable as an argument to the query function constructor, since conceptually you shouldn't need a table to define a query, however it's needed during the construction because you have the field names listed in the first row. A way to nicely work around this is to consider a query an indpependent entitiy, which doesn't use the table until it's used in Select. This can be defined by setting an Upvalue pattern for Select, to resemble your included example, I use where as a name for the query:

 SetAttributes[where, HoldAll]
 Select[table_, where[query_]] ^:= Select[table, queryCriteria[table, query]]

So that the query can be written:

 Select[theTable, where["color" == "blue" && "size" > 10]]

This is all just ways of doing a similar thing with different syntax however. I would expect that performance issues become more important with big Databases.

share|improve this answer
add comment

I had forgot about this question, or the answer linked by @Leonid, or @jVincent's etc, and last week I was under the same "need".

I'll just post what I used since it's no extra work, in case it still helps someone.

Speed wasn't a concern, so I have no clue how much time this wastes

LabeledMatrix[cs_, mat_][cols : {__String}, funQ_] := 
    LabeledMatrix[cs, mat][cols, funQ, cols];

Normal[LabeledMatrix[_, mat_, ___]] ^:= mat

(lm : LabeledMatrix[cs_, mat_?MatrixQ])[cols : {__String}, funQ_, showCols : {___String}] :=
    Extract[mat[[All, label2Position[lm, showCols]]], 
      Position[LabeledMatrix[cs, mat][cols], {i___} /; funQ[i], {1}]];

LabeledMatrix[cs_, mat_?MatrixQ][cols : {__String}, All] := 
    LabeledMatrix[cs, mat][cols];
(lm : LabeledMatrix[cs_, mat_?MatrixQ])[cols : {__String}] := 
    mat[[All, label2Position[lm, cols]]];

SetAttributes[label2Position, Listable];
label2Position[LabeledMatrix[cols_List, ___], lab_] := 
    First@Flatten@Position[cols, lab, {1}, 1];

There's basically no error checking, formatting rules, etc.

Usage

LabeledMatrix is a wrapper. It takes, as a first argument, the names of the columns, and as a second, the data matrix.

lm = LabeledMatrix[
   {"ID", "Person", "Age"},
   {{4, "Peter", 23}, {5, "Mary", 33}, {55, "John", 23}}];

Say you want the "Person" and "Age", column

lm[{"Person", "Age"}]

(* {{"Peter", 23}, {"Mary", 33}, {"John", 23}} *)

The first argument, (unless you use the 3 argument form), is a list of the columns you want as output.

If you give a second argument, then that second argument is a predicate function to filter rows. The arguments taken by that function are those supplied in the first argument. Example

lm[{"Age", "ID"}, #2 > #1 &]

(* {{23, 55}} *)

If you supply a third argument, it's the list of columns returned. The first argument still works as the input to the predicate. So, say you want the IDs of the people aged under 30

lm[{"Age"}, # < 30 &, {"ID"}]

(* {{4}, {55}} *)

A second argument of All is the same as nothing. Normal gives the data matrix. First, or some other convenience function you want to create, the names of the columns.

share|improve this answer
add comment
  1. Keep your data in external .csv files. Data is stored/queried in/from "tables" in a db anyway.

  2. query=ReadList["!grep ...", String]

  3. Process query, use ToExpression where appropriate, etc.

This has the following advantages:

  • you don't actually keep your data in the kernel, you keep it in files, outside of the M system. That's where data storage SHOULD be: outside of the kernel. You shouldn't use the kernel for data storage, only for data processing.

  • now the kernel will contain and process only your extraction / query result / result set. MUCH more memory-efficient. You shouldn't have in the kernel what you're not interested in anyway.

  • grep and egrep are extremely fast. You're actually outsourcing the query to operating system speed.

  • you don't need the overhead of a real d/b system (CPU, memory, installation, drive space, etc.) You only consume the drive space needed for your .csv files

What I've described above is actually how I do store large data amounts of data that I want to be available for queries. I don't consider this a workaround, it's a solution. Instead of installing a d/b application, I ALWAYS ask if I can simply do my tables (which I'd have to set up in a d/b system anyway) as well-designed .csv files (conforming to certain d/b standards, such as normalization, etc.), and then use the speed of grep, o/s methods, and ReadList["!grep ...",...], and then string-based processing and possible type conversions. Should be extremely hard to beat that speed, you don't need external d/b applications, you don't have external links (as you may be aware of, M's DatabaseLink package uses JLink internally), and you have additional flexibilities with this approach. You could, for example, save the .csv files from spreadsheet programs or other generators, and you can zip them when you want to archive them (text files can be zipped down to 7%), and you could even include zip/unzip steps in your M program to "set up" your d/b. A collection of properly formatted .csv files is actually a relational database! A simple d/b is nothing but a collection of well-organized tables, so you can do it yourself with text files (I recommend .csv).

share|improve this answer
add comment

This is my way. I'm used to name my columns as:

cId=1;cColor=2;cSize=3;cFlavor=4;

I append c because it's easy to use with the autocomplete, and prevent variables mix. And instead of cases, I prefer to use select as below:

r=Select[Rest@theTable, #[[cColor]] == "blue" && #[[cSize]] > 10 &]

If you want to take just some columns you can make for example:

r[[All,{cID,cColor}]]

Sometimes I have a lot os columns, so is boring to make the first step so I created this function that is in my tool bag:

sequenceVarList[list_] := Module[{varList},
    varList = StringReplace[list, {"_" -> "", " " -> ""}];
    Clear@@varList // Quiet;
    MapIndexed[(Evaluate[Symbol[#1]] = #2[[1]]) &, varList];
    TableForm[MapIndexed[{#2[[1]], #1} &, varList]]
]

So I can use it as:

sequenceVarList[{"cId","cColor","cSize","cFlavor"}]

and the first step is done easily.

I think that the big advantage of this solution is that you can use your column names in another calculations like GatherBy, SortBy and so on.

Update

If you are dealing with many tables, you can use the function sequenceVarList as:

Unprotect[Dot];
SetAttributes[Dot, HoldAll];
Protect[Dot];

mrtSeqVarList[varList_,symbol_Symbol] := Module[{},
    Clear[UpValues[symbol]];
    MapIndexed[(symbol/:symbol.#1=First@#2)&, varList];
]

So now you can do:

sequenceVarList[{cId,cColor,cSize,cFlavor},tab1]
r=Select[Rest@theTable, #[[tab1.cColor]] == "blue" && #[[tab1.cSize]] > 10 &]
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.