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.

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", ""}}
share|improve this question
1  
Can you provide the data? –  Leonid Shifrin May 10 at 16:18
    
By the way, one obvious mistake / type is that you have # == "Yes", while a pure function should end with an ampersand : # == "Yes"&. –  Leonid Shifrin May 10 at 16:23
    
I've edited above with the data I'm working with. –  Tim H UK May 10 at 16:29
1  
And your desired output is..? –  Öskå May 10 at 16:32
1  
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. –  Leonid Shifrin May 10 at 17:12
show 4 more comments

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
@LeonidShifrin, If you want to show us a Dataset teaser for this example, be my guess :) –  Murta May 10 at 17:58
    
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). –  Leonid Shifrin May 10 at 18:16
add comment

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}}
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.