24
$\begingroup$

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?

$\endgroup$
20
  • 6
    $\begingroup$ Have you seen this answer, and those linked to it, particularly this one? Those can be a starting point. $\endgroup$ Sep 26, 2012 at 22:52
  • 2
    $\begingroup$ You know that Extract[theTable, Position[theTable, {_, "blue", x_ /; x > 10, _}]] works, right? $\endgroup$ Sep 26, 2012 at 23:14
  • 2
    $\begingroup$ @ArgentoSapiens that was important information for us ;) $\endgroup$ Sep 26, 2012 at 23:40
  • 1
    $\begingroup$ @Argento: "that was important information for us" - which you should have included to begin with. $\endgroup$ Sep 26, 2012 at 23:41
  • 1
    $\begingroup$ @LeonidShifrin yes it could easily balloon into something large. Reflecting on this a bit more, for larger tables for any user that is familiar with basic SQL queries it is probably easier to use a database -- this is what they are for -- and use DatabaseLink. $\endgroup$ Sep 27, 2012 at 0:20

5 Answers 5

13
$\begingroup$

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.

$\endgroup$
13
$\begingroup$

Don't reinvent the wheel: If you need a database you should be aware of the SQLite access readily built into Mathematica, though unfortunately undocumented:

db = Database`OpenDatabase[FileNameJoin[{$TemporaryDirectory, "mma-temp-db.sqlite"}]];

Database`QueryDatabase[db, 
    "CREATE TABLE stuff(id INTEGER PRIMARY KEY,color TEXT,size REAL,flavor TEXT)"
];

Database`QueryDatabase[db, "BEGIN"];

Scan[
  Database`QueryDatabase[db, 
    ToString@StringForm[
      "INSERT into stuff(color,size,flavor) VALUES ('`1`',`2`,'`3`')",
      Sequence @@ #
  ]] &,
  theTable[[2 ;; -1, 2 ;; 4]]
];

Database`QueryDatabase[db, "END"];

Database`QueryDatabase[db,"SELECT * FROM stuff WHERE color = 'blue' AND size > 10"]

Database`CloseDatabase[db]

and in case you are more into speed than persistency:

Database`OpenDatabase[":memory:"]

for details just look for documentation about sqlite, there is tons of good documentation around for it...

EDIT: as murta mentioned in his comment it is also possible to make use of SQLite with the officially supported and documented DatabaseLink`. In version 10 a corresponding driver is included, for earlier versions a SQLite JDBC-driver has to be installed manually. As far as I can tell using the Database`* functions is a very lightweight approach most probably making direct use of the sqlite libraries while DatabaseLink` makes use of Java/JLink/JDBC which is kind of heavyweight but of course also has its advantages. Also from murta is the above example using DatabaseLink:

Needs["DatabaseLink`"]
conn=OpenSQLConnection[JDBC["SQLite",$TemporaryDirectory<>"testBase.sqlite"]];
SQLExecute[conn,"CREATE TABLE stuff(id INTEGER PRIMARY KEY,color TEXT,size REAL,flavor TEXT)"];
SQLInsert[conn,"stuff",{"color","size","flavor"},theTable[[2;;-1,2;;4]]];
SQLExecute[conn,"SELECT * FROM stuff WHERE color = 'blue' AND size > 10"]
CloseSQLConnection[conn]

For in memory version use: conn=OpenSQLConnection[JDBC["SQLite(Memory)","jdbc:sqlite::memory:"]];

Just for completeness: there are also drivers for HSQL included in all versions of DatabaseLink that I can remember of which provide similar functionality as SQLite, since version 10 there are also drivers for H2 and Derby included which also claim similar functionality.

EDIT since version 11.1 the Database` functions have been removed. So for any version newer than 11.0 one has to use the DatabaseLink` approach, but as they come with the SQLite driver you still can access SQLite databases in those versions out of the box.

$\endgroup$
9
  • $\begingroup$ Executing the first command: db = Database`OpenDatabase[FileNameJoin[{$TemporaryDirectory, "mma-temp-db.sqlite"}]]; I get Database`Database::liberror: A required library could not be located or opened. in MMA V10.0.2 $\endgroup$
    – Murta
    Feb 7, 2015 at 0:03
  • $\begingroup$ You can do the same with documented commands, with: Needs["DatabaseLink`"] conn=OpenSQLConnection[JDBC["SQLite",$TemporaryDirectory<>"testBase.sqlite"]]; $\endgroup$
    – Murta
    Feb 7, 2015 at 0:08
  • $\begingroup$ interesting, which OS are you on? I've used that in several MMA versions (example was written and tested with V10.0.2) on Windows and never had problems, but I remember to have seen complaints of others... $\endgroup$ Feb 7, 2015 at 0:08
  • $\begingroup$ @murta: Yes, since version 10 I think there is a SQLite driver for DatabaseLink predefined, I'll make an addition about it. It probably is also worth mentioning that the undocumented access is very lightweight compared to what DatabaseLink does, but often using the documented stuff is still a better idea... $\endgroup$ Feb 7, 2015 at 0:10
  • $\begingroup$ yes, I just confirmed. Worked in Windows but not in Mac OSX 10.10.2. Do you know if there is some memory version for JDBC? I would be great. $\endgroup$
    – Murta
    Feb 7, 2015 at 0:12
7
$\begingroup$

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.

$\endgroup$
7
$\begingroup$
  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).

$\endgroup$
14
  • $\begingroup$ I'm going to be dealing with large files and databases soon. It's been ten years since you wrote this. Have you discovered new methods to do this? Or is this still the optimal method? Are you dealing with other files besides CSVs now? $\endgroup$
    – jWey
    Mar 25 at 7:10
  • $\begingroup$ I still recommend to use such Linux command line tools, and to submit from ReadList (or sometimes Read) to the command line, rather than reading in everything into the M kernel. I still use mostly .csv, although the important thing here is to use text files, that can also be .xml or .json for non-rectangular formats, or others. $\endgroup$ Mar 26 at 19:09
  • $\begingroup$ That's what I've gathered as well. Any interesting command line tools? perhaps there's specific mathematica integration? I have text, pdfs, graphs, images, videos, xml, json, and databases to play with. I'm just thinking out loud here: dealing with a lot of data is even more important today; and now we have Apple Silicon and unified computer architectures that can access media engines and neural engines and other specialized engines. I'm sure the people working behind the scenes of wolfram language have a different view of what is possible with the code and language. Lots of potential. $\endgroup$
    – jWey
    Apr 3 at 3:40
  • 1
    $\begingroup$ I have found grep, egrep, curl, sed the most useful Linux command line tools for "pre-extracting" data that is then read into the M kernel. ReadList["!command",...], don't forget the !. You can even submit entire perl programs to the command with the -e option, such as ReadList["!perl -e <whatever>",...], although I acknowledge that nowadays people would probably prefer python over perl. ReadList["!python <option to submit code to command line><whatever>",...]. Also awk. You could write very effective extractors in awk, perl, phython and then read in just the extraction. $\endgroup$ Apr 5 at 18:50
  • 1
    $\begingroup$ You can also use Java's REPL, the jshell, from the command line, and with the - option specify that you don't want interactivity, this is like the -e option for perl: ReadList["!echo \"System.out.println(\\\"Hello World\\\");\" | jshell -"]. I also find the -n option for perl very useful when used with -e, because it gives you the diamond operator without specifying the outer while loop. So with perl -e -n you get to parse the raw text data, line by line, in a diamond operator, without while loop. I find that incredibly useful, but I acknowledge perl is considered outdated nowadays. $\endgroup$ Apr 5 at 20:12
3
$\begingroup$

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 &]
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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