I am a statistician searching for an efficient way to select rows or columns from a table of data in Mathematica. Let me pose the question in 2 parts with a SQL-style table of data:
List[{"ID", "Variable 1", "Variable 2"}, {"Alpha", 1, 0}, {"Beta", 1, 1}, {"Alpha", 1, 0}]]
Which, when formatted as a Grid looks roughly like this:
ID Variable 1 Variable 2
Alpha 1 0
Beta 1 1
Alpha 1 0
Part 1: How can the data in the header of the table, for example "ID", be set as the name of the list for that column? Ideally, the result would allow you to do the following:
In[24]:= ID
Out[24]= {"Alpha", "Beta", "Alpha"}
Would one need to write a function to dissect the header row and then line up the header names as the name of a list that corresponds to the appropriate header? Although one might ask 'Why not refer to everything as a position and avoid the renaming headache entirely?' it is extremely cumbersome when working with tens or hundreds of columns/variables to use a meaningless position to reference a variable.
Part 2: How can an individual row, or subset of rows, be returned from a table? Essentially I'm looking for the equivalent of the "WHERE" clause in SQL or the "subset" function in R.
For example in the "ID" column I might want to retrieve all the rows where "ID" == "Alpha". Do I have to create a method that iterates over the "ID" list, stores the position in the list where the value of the element is equal to "Alpha", and then concatenate a list that contains the value in that position for all the other lists?
I'm confident I could write the functions I mention, but it seems unconscionable that Mathematica would overlook such a rudimentary data manipulation task. I understand there's also the DataManipulation package that allows for SQL queries, but I have to believe (hope?) there's a way native to Mathematica that's quicker.
Thank you for indulging me! And my apologies in advance to all the Mathematica aficionados who might see this as a corrupt question for trying to program in another language while in Mathematica!