# What is the Mathematica way of joining two tables?

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", ""}}

-
Can you provide the data? – Leonid Shifrin May 10 '14 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 '14 at 16:23
I've edited above with the data I'm working with. – Tim H UK May 10 '14 at 16:29
And your desired output is..? – Öskå May 10 '14 at 16:32
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 '14 at 17:12

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"]]}&];


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.

-
@LeonidShifrin, If you want to show us a Dataset teaser for this example, be my guess :) – Murta May 10 '14 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 '14 at 18:16

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

-