Crosstabs are here!
(and the X Menu is coming)
Tod Landis
March 16, 2008
The Entrance "Cross tabs" tool creates a new table from another one based on three of its columns:. Entries in one column become row headings, entries in a second become the for columns names, and entries in a third are combined to fill out the table.
An example will make everything clear. Suppose you have a table with columns containing year and month values like the one below:
The "Calculate Cross Tabs" tool can be used to make a new table with a new column for each month, JAN-DEC, and a row for each year, like this:
The remaining values in the table, are filled in by combining values in the original table. Entrance supports combining values by count, sum, max, min, and average. Often you will have only one value for row/column heading pair in a table, in which case you can aggregate by average sum.
There are a number of interesting applications for cross tabs. For example, they are useful for analyizing the Statement-Of-Vote (SOV) election results published by a county election office. (Crosstab to use precinct numbers for row headings, candidates for column names, and sum() the vote counts in that case)
Entrance cross tabs are basically the same thing as the "pivot tables you find in spreadsheets. In poking around just now I found an interesting history of pivot tables on Wikipeidia. It turns out that, though Microsoft trademarked the result of joining "pivot" and "table" together, Borland Quattro Pro and Lotus 123 first brought pivot tables to market .They are now in OpenOffice, too.
You can use another Entrance tool to go in the reverse direction. The "Consolidate columns..." tool lets you combine entries from multiple columns into three columns containing many more rows. For example, to convert KeelingCurve_crosstab back (almost) to KeelingCurver, we could keep the year column and consolidate the month columns:
Finally, there are cases when you want to transpose a a table without aggregating. For example, you may have values in a row you want to plot as a single line. For those cases, there is the transpose tools. Use it with the default settings and it provides distinct column names automatically. Apply it with the default settings to the new table and you will recover the original table (how cool is that?).
These are relatively fresh additions, so be aware that there will be a few things that go bump in the night. Here is what I know about so far: The consolidate column tool has a problem with NULL values (#144), and the way it handles overwrrting a table is rude (#145). Also, in any case a new column is being created, a legal column name must be in the table field or you will see a cryptic error message.
Crosstabs are here as the result of a suggestion by Roland Bouman, who will be one of the speakers at the upcoming MySQL Conference. I am very grateful to him for it. Roland will be giving two talks, including one on writing user defined functions (UDFs) in C that is particularly relevant for Entrance users.
My next post will describe the new Entrance "X Menu" , which is on track for release in about two weeks.
Paper for CommunityOne: Entrance: A Data Analysis Tool Written In Java
Copyright (c) 2008 Tod Landis, All Rights Reserved
MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries.