4
$\begingroup$

I'm a Mathematica newbie and I have the following problem: I connect to a Oracle DB and read out a set of variables, further on I want to histogram some of the variables. A Dataset seems to be the right tool, but I produce only datasets without a key via:

DBtest = SQLSelect[db, "TABLE1", {"ID", "VAR_1"}]

Dataset[DBtest]

Result is a Dataset with one column containing ID and the other containing VAR_1 (sorry for the generic names - I can't copy my real work, since it contains sensitive data).

Then I can't use the DBtest[Histogram, "key"] functionality, since there is no "key" available.

Is there anyway to add keys after filling the dataset from the DB? Or shouldn't I use the Dataset class?

Improve this question
$\endgroup$
1
  • 2
    $\begingroup$ You have to thread the keys "ID" and "VAR_1", like RunnyKine's answer here mathematica.stackexchange.com/questions/59684/… ... Dataset[AssociationThread[First @ data, #] & /@ Rest @ data] $\endgroup$ Commented Oct 29, 2014 at 19:07

1 Answer 1

Reset to default
1
$\begingroup$

For discussion purposes, let's use some data from Mathematica's example databases:

$connection = OpenSQLConnection["publisher"];

$dbTest = SQLSelect[$connection, "SALESDETAILS", {"TITLE_ID", "QTY_ORDERED"}]

(* {{"PY2002", 75}, {"PY2002", 10}, <<17>> , {"CK4017", 20}, {"CK4016", 20}} *)

We can create a histogram directly from this data without wrapping it in a Dataset:

Histogram[$dbTest[[All, 2]]]

histogram screenshot

As observed in the question, a Dataset with this data carries no column names since the original result set is comprised of lists instead of associations:

$dataset = Dataset[$dbTest]

dataset screenshot

However, the columns are still accessible by their indices:

$dataset[Histogram, 2]

histogram screenshot

When we retrieve the original SQL data, we have the option of including a header row by means of the "ShowColumnHeadings" option. We can use the headers from this row to create associations that provide names for our columns:

$dataset2 =
  SQLSelect[$connection, "SALESDETAILS", "ShowColumnHeadings" -> True] //
    Inner[#2->#1&, Rest@#, First@#, Association]& // Dataset

dataset screenshot

Now we can produce histograms using the column names:

$dataset2[Histogram, "QTY_ORDERED"]

histogram screenshot

Improve this answer
$\endgroup$
1
  • $\begingroup$ This solution works as well. And the definition of $dataset2, including the SQL connection, is quite elegant. The way to produce histograms without using Dataset at all, was new for me. Thanks! $\endgroup$ Commented Nov 17, 2014 at 9:24

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.