5
$\begingroup$

Is there a special Mathematica way of joining tables of data together, so that I would get similar functionality as if I was running a database with sql?

For example, I have one particular table here, and getting data out of it is getting a bit verbose. Here is an example, but is there a better way to work with data. I thought there would be a simple way of creating tables and joining them, something similar to a dataframe in R perhaps?

Tally[
  Flatten[
    GatherBy[Transpose[{data[[All, 9]], data[[All, 8]]}], # == "Yes"] , 
    1
  ]
]

returns

(* 
   {
     {{"Yes", "South West"}, 42}, {{"No", "The North"}, 325}, 
     {{"Yes", "Midlands & East Anglia"}, 92}, {{"No", "Scotland"}, 97},
     {{"No", "Midlands & East Anglia"}, 351}, {{"Yes", "Wales"}, 20}, 
     {{"No", "South East"}, 187}, {{"No", "South West"}, 115}, 
     {{"No", "Wales"}, 63}, {{"No", "London"}, 132}, {{"Yes", "London"}, 50},
     {{"Yes", "The North"}, 101}, {{"Yes", "South East"}, 65}, 
     {{"Yes", "Scotland"}, 51}
   }
*)

Here is an example of the data I am working with:

{{"gender", "age", "maritalStatus", "highestQualification", "nationality", "ethnicity", 
  "grossIncome", "region", "smoke", "amtWeekends", "amtWeekdays", "type"}, 
 {"Female", 51, "Divorced", "Other/Sub Degree", "English", "White", 
  "Unknown", "South West", "Yes", 25, 25, "Packets"}, 
 {"Female", 52, "Married", "No Qualification", "English", "White", 
  "Unknown", "The North", "No", "NA", "NA", ""}, 
 {"Female", 53, "Divorced", "GCSE/O Level", "English", "White", 
  "Unknown", "Midlands & East Anglia", "Yes", 7, 12, "Packets"}, 
 {"Female", 59, "Divorced", "Degree", "Scottish", "White", 
  "Unknown", "Scotland", "No", "NA", "NA", ""}, 
 {"Female", 61, "Married", "A Levels", "English", "White", 
  "Unknown", "Midlands & East Anglia", "No", "NA", "NA", ""},
 {"Male", 66, "Married", "No Qualification", "English", "White", 
  "Unknown", "The North", "No", "NA", "NA", ""}, 
 {"Female", 69, "Widowed", "No Qualification", "English", "White", 
  "Unknown", "Wales", "Yes", 15, 15, "Packets"}, 
 {"Female", 69, "Widowed", "No Qualification", "English", "White", 
  "Unknown", "The North", "No", "NA", "NA", ""}, 
 {"Male", 71, "Divorced", "No Qualification", "English", "White", 
  "Unknown", "The North", "No", "NA", "NA", ""}}
Improve this question
$\endgroup$
  • 1
    $\begingroup$ Can you provide the data? $\endgroup$ – Leonid Shifrin May 10 '14 at 16:18
  • $\begingroup$ By the way, one obvious mistake / type is that you have # == "Yes", while a pure function should end with an ampersand : # == "Yes"&. $\endgroup$ – Leonid Shifrin May 10 '14 at 16:23
  • $\begingroup$ I've edited above with the data I'm working with. $\endgroup$ – Tim H UK May 10 '14 at 16:29
  • 1
    $\begingroup$ And your desired output is..? $\endgroup$ – Öskå May 10 '14 at 16:32
  • 1
    $\begingroup$ Normally you use joins when you have more than one table, and you join on some column(s). So, what we'd expect is some minimal example containing 2 such tables, the join call syntax you'd like, and the result you'd expect. Regarding RLink, note that if you plan to transfer large amounts of data back and forth between R and Mathematica, there are a couple of subtleties to make that efficient. I would not write Mathematica off with regards to data table manipulation just yet, though. $\endgroup$ – Leonid Shifrin May 10 '14 at 17:12
4
$\begingroup$

To simulate a generic SQL Group By, here is an option:

Let data = {{"gender", "age", "maritalStatus"...}...}

d["Data"]=Rest@data;
MapIndexed[(d[#1]=#2[[1]])&,First@data];

Now we can use columns names:

dataG={#[[1,d@"smoke"]],#[[1,d@"region"]],Mean@#[[All,d@"age"]],Length@#}&/@GatherBy[d["Data"],{#[[d@"smoke"]],#[[d@"region"]]}&];
TableForm[dataG,TableHeadings->{None,{"Smoke","Region","MeanAge","Qtd"}}]

tableExample

If you use these operations frequently (I use a lot), you can look at this answer, that shows some technics to avoid use MapIndexed, and less verbose columns names notation, overloading Dot operator.

I believe Mathematica 10 will be much better in this area with the new Dataset objects. I'll improve this answers a soon I learn how it works.

Improve this answer
$\endgroup$
  • $\begingroup$ @LeonidShifrin, If you want to show us a Dataset teaser for this example, be my guess :) $\endgroup$ – Murta May 10 '14 at 17:58
  • $\begingroup$ Dataset currently is more geared towards general hierarchical representation of data. Tabular Dataset-s are possible, of course. The bigger problem is that the question is not really quite clear to me. As I mentioned in the comment below the question, one needs at least 2 tables for join, and also to specify the type of join (left, right, inner or outer). $\endgroup$ – Leonid Shifrin May 10 '14 at 18:16
2
$\begingroup$

Here is a general query function you might find helpful in itself or for use as a template for further development.

data =
{{"gender", "age", "maritalStatus", "highestQualification", "nationality", "ethnicity", 
  "grossIncome", "region", "smoke", "amtWeekends", "amtWeekdays", "type"}, 
 {"Female", 51, "Divorced", "Other/Sub Degree", "English", "White", 
  "Unknown", "South West", "Yes", 25, 25, "Packets"}, 
 {"Female", 52, "Married", "No Qualification", "English", "White", 
  "Unknown", "The North", "No", "NA", "NA", ""}, 
 {"Female", 53, "Divorced", "GCSE/O Level", "English", "White", 
  "Unknown", "Midlands & East Anglia", "Yes", 7, 12, "Packets"}, 
 {"Female", 59, "Divorced", "Degree", "Scottish", "White", 
  "Unknown", "Scotland", "No", "NA", "NA", ""}, 
 {"Female", 61, "Married", "A Levels", "English", "White", 
  "Unknown", "Midlands & East Anglia", "No", "NA", "NA", ""},
 {"Male", 66, "Married", "No Qualification", "English", "White", 
  "Unknown", "The North", "No", "NA", "NA", ""}, 
 {"Female", 69, "Widowed", "No Qualification", "English", "White", 
  "Unknown", "Wales", "Yes", 15, 15, "Packets"}, 
 {"Female", 69, "Widowed", "No Qualification", "English", "White", 
  "Unknown", "The North", "No", "NA", "NA", ""}, 
 {"Male", 71, "Divorced", "No Qualification", "English", "White", 
  "Unknown", "The North", "No", "NA", "NA", ""}}

queryByFields[data_List, fields : _String ..] :=
  Module[{names, records, rules, columns},
    names = First @ data;
    records = Rest @ data;
    rules = Thread[Rule[names, Range @ Length @ names]];
    columns = {fields} /. rules;
    Tally[Transpose[records[[All, #]] & /@ columns]]]

queryByFields[data, "smoke", "region"]

{{{"Yes", "South West"}, 1}, {{"No", "The North"}, 4}, 
 {{"Yes", "Midlands & East Anglia"}, 1}, {{"No", "Scotland"}, 1}, 
 {{"No", "Midlands & East Anglia"}, 1}, {{"Yes", "Wales"}, 1}}

queryByFields[data, "gender", "smoke", "nationality"]

{{{"Female", "Yes", "English"}, 3}, {{"Female", "No", "English"}, 3}, 
 {{"Female", "No", "Scottish"}, 1}, {{"Male", "No", "English"}, 2}}
Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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