This note is a simple data wrangling example worked using both the Python data_algebra package and the R cdata package. Both of these packages make data wrangling easy through he use of coordinatized data concepts (relying heavily on Codd’s “rule of access”).

The advantages of data_algebra and cdata are:

• The user specifies their desired transform declaratively by example and in data. What one does is: work an example, and then write down what you want (we have a tutorial on this here).
• The transform systems can print what a transform is going to do. This makes reasoning about data transforms much easier.
• The transforms, as they themselves are written as data, can be easily shared between systems (such as R and Python).

Let’s re-work a small R cdata example, using the Python package data_algebra.

An Example

First we import some modules and packages, and import some notional data.

Our goal is to move from this de-normalized or wide-form (or “model matrix”/”data matrix” form, where each record is exactly one row) into a tall form where records may span more than one row.

Or, more concretely, we want our data to look like the following.

Notice each row of the original data set is now four rows of the derived one. This “tall form” is often useful for plotting.

This sort of conversion can be called an anti-pivot. In Python these sorts of transforms are specified with pandas.DataFrame.pivot, pandas.pivot_table, pandas.melt,
and others. Many R packages supply related operators: reshape, reshape2, data.table, tidyr, cdata, and more. We feel the data_algebra and cdata methods (which only perform data-reshaping, and not aggregation) offer a number of significant advantages (some of which we have already mentioned, and a few more of which we will see demonstrated in this note).

Back to our example.

To specify our desired transform, we build a structure describing what a data record looks like. We can crib this specification from the answer as follows.

We can derive the control table from the answer, as we did here, or just type one in directly. The idea is: we can use any method we want to derive the prototype record shape, we are not limited to a sub-query language or methodology from any one package.

For each record we take care to identify what keys identify records (the record_keys) and want parts identify rows within the record (the control_table_keys). We suppress the record_key when writing the control table, as these are exactly the values that do not vary inside each record.

We combine all of these specification into a RecordSpecification as follows:

In this notation any cells of the control table from columns that are not control_table_keys are “value stand ins”. During data transfrom these cells will be replaced by values coming from the columns named by these cells.

Now we can transform our original row-record oriented data into general block records. To do this we specify a RecordMap using our record specification to describe the outgoing record structure. The incoming record structure is implicitly assumed to be single-row records, unless we specify otherwise (using the blocks_in argument).

And we are ready to apply our specified transform.

We see the operation has been performed for us. Notice we specify the transform declaratively with data structures carrying deceptions of what we want, instead of having to build a sequence of verbs that realize the transformation.

Inverting the transform

An inverse transform is simply expressed by reversing the roles of the blocks_out and blocks_in arguments. In this case the output is row-records, as we didn’t specify an outgoing block structure with blocks_out.

Arbitrary transforms

Arbitrary record to record transforms can be specified by setting both blocks_in (to describe incoming structure) and blocks_out (to describe outgoing structure) at the same time.

Transforms in databases

data_algebra also implements all the transform steps in databases using SQL (via row_recs_to_blocks_query() and blocks_to_row_recs_query()).

These queries can be seen below.

The use case for SQL queries is: the queries can be used to with “CREATE TABLE table_name AS” to materialize transform results in a database, without round-tripping the data in and out of the database.

As complicated as the queries look, they actually expose some deep truths:

• The row_recs_to_blocks_query() is essentially a cross-join of the data to the record description. Each combination of data row and record description row builds a new result row.
• The blocks_to_row_recs_query() is an aggregation. Each set of rows corresponding to a given data record is aggregated into a single result row.
• Just about any arbitrary record shape to arbitrary record shape can be written as a transform from the first record shape to row-records (record sets that have exactly one row per record), followed by a transform from the row-records to the new format. This transform can preserve column types as in the intermediate form each different record entry has its own column. This is an advantage of using a “thin” intermediate form such as RDF triples.

This leads us to believe that transforming to and from single-row records are in fact fundemental operations, and not just implementation details.

The R cdata version

The data_algebra had been previously implemented in
R in the cdata, rquery, and rqdatatable packages.
This packages support both in-memory operations and translation of the transforms to SQL.

We would perform the above transforms in R as follows.

First we write out a copy of the transform.

Now we we start up our R environment and read in the transform specification and data.

Now that we have recovered the transform, we can use it in R.

Cross-language work

As the record transform specifications, both in Python data_algebra and R cata are simple data structures (just the control table, and a few lists of key column names), they can be moved from one language to another by YAML (as we also demonstrated in the logistic scoring example.

data_algebra supplies a write method, so cross-language interoperation is just a matter of adding additional read/write methods.

Conclusion

The cdata and data_algebra systems yield powerful implementations, and deep understanding of the nature of record transformations. They allow one to reshape data quickly and conveniently either in R. Python/Pandas, or even SQL.