# New Introduction to the data_algebra

*This article was first published on*

Want to share your content on python-bloggers? click here.

**python – Win-Vector Blog**, and kindly contributed to python-bloggers. (You can report issue about the content on this page here)Want to share your content on python-bloggers? click here.

We’ve had really good progress in bringing the `Python`

`data_algebra`

to feature parity with `R`

`rquery`

. In fact we are able to reproduced the New Introduction to `rquery`

article as a “New Introduction to the `data_algebra`

” here.

The idea is: you may have good reasons to want to work in `R`

or to want to work in `Python`

. And Win-Vector LLC wants to leave the choice of `R`

versus `Python`

to you, by providing equivalent strong tools for each platform.

In the article below notice we can explore the same concepts in `R`

or in `Python`

(with a different syntax emphasizing quotation and method-chaining, to be more natively “Pythonic”).

## Introduction to the `data_algebra`

The `data_algebra`

is a data wrangling system designed to express complex data manipulation as a series of simple data transforms. This is in the spirit of `R`

‘s `base::transform()`

, `dplyr`

‘s `dplyr::mutate()`

, or `rquery`

‘s `rquery::extend()`

and uses a method chaining notation. The operators themselves follow the selections in Codd’s relational algebra, with the addition of the traditional `SQL`

“window functions.” More on the background and context of `data_algebra`

can be found here.

The `Python`

/`data_algebra`

version of this introduction is here, and the`R`

/`rquery`

version of this introduction is here.

In transform formulations data manipulation is written as transformations that produce new `DataFrame`

s, instead of as alterations of a primary data structure (as is the case with `data.table`

). Transform system *can* use more space and time than in-place methods. However, in our opinion, transform systems have a number of pedagogical advantages.

In `data_algebra`

‘s case the primary set of data operators is as follows:

`drop_columns`

`select_columns`

`rename_columns`

`select_rows`

`order_rows`

`extend`

`project`

`natural_join`

`convert_records`

.

These operations break into a small number of themes:

- Simple column operations (selecting and re-naming columns).
- Simple row operations (selecting and re-ordering rows).
- Creating new columns or replacing columns with new calculated values.
- Aggregating or summarizing data.
- Combining results between two
`DataFrame`

s. - General conversion of record layouts.

The point is: Codd worked out that a great number of data transformations can be decomposed into a small number of the above steps. `data_algebra`

supplies a high performance implementation of these methods that scales from in-memory scale up through big data scale (to just about anything that supplies a sufficiently powerful `SQL`

interface, such as PostgreSQL, Apache Spark, or Google BigQuery).

We will work through simple examples/demonstrations of the `data_algebra`

data manipulation operators.

`data_algebra`

operators

### Simple column operations (selecting and re-naming columns)

The simple column operations are as follows.

`drop_columns`

`select_columns`

`rename_columns`

These operations are easy to demonstrate.

We set up some simple data.

```
import pandas
d = pandas.DataFrame({
'x': [1, 1, 2],
'y': [5, 4, 3],
'z': [6, 7, 8],
})
d
```

`drop_columns`

works as follows. `drop_columns`

creates a new `DataFrame`

without certain columns. We can start by wrapping our `DataFrame`

`d`

for processing, then applying the `drop_columns()`

operators, and finally ending and executing the chain with the `ex()`

method.```
from data_algebra.data_ops import *
wrap(d). \
drop_columns(['y', 'z']). \
ex()
```

`data_algebra`

operator is either the data to be processed, or an earlier `data_algebra`

pipeline to be extended. We will take about composing `data_algebra`

operations after we work through examples of all of the basic operations.`select_columns`

‘s action is also obvious from example.```
wrap(d). \
select_columns(['x', 'y']). \
ex()
```

`rename_columns`

is given as name-assignments of the form `'new_name': 'old_name'`

:```
wrap(d). \
rename_columns({
'x_new_name': 'x',
'y_new_name': 'y'
}). \
ex()
```

```
wrap(d). \
select_rows('x == 1'). \
ex()
```

`order_rows`

re-orders rows by a selection of column names (and allows reverse ordering by naming which columns to reverse in the optional `reverse`

argument). Multiple columns can be selected in the order, each column breaking ties in the earlier comparisons.```
wrap(d). \
order_rows(
['x', 'y'],
reverse = ['x']). \
ex()
```

`data_algebra`

operations do not depend on row-order and are not guaranteed to preserve row-order, so if you do want to order rows you should make it the last step of your pipeline.### Creating new columns or replacing columns with new calculated values

The important create or replace column operation is:

`extend`

`extend`

accepts arbitrary expressions to create new columns (or replace existing ones). For example:

```
wrap(d). \
extend({'zzz': 'y / x'}). \
ex()
```

`=`

or `:=`

for column assignment. In these examples we will use `:=`

to keep column assignment clearly distinguishable from argument binding.`extend`

allows for very powerful per-group operations akin to what `SQL`

calls “window functions”. When the optional `partitionby`

argument is set to a vector of column names then aggregate calculations can be performed per-group. For example.

```
wrap(d). \
extend({
'max_y': 'y.max()',
'shift_z': 'z.shift()',
'row_number': '_row_number()',
'cumsum_z': 'z.cumsum()',},
partition_by = 'x',
order_by = ['y', 'z']). \
ex()
```

`partitionby`

) and in the order determined by the `orderby`

argument (without the `orderby`

argument order is not guaranteed, so always set `orderby`

for windowed operations that depend on row order!).More on the window functions can be found here.

### Aggregating or summarizing data

The main aggregation method for `data_algebra`

is:

`project`

`project`

performs per-group calculations, and returns only the grouping columns (specified by `groupby`

) and derived aggregates. For example:

```
wrap(d). \
project({
'max_y': 'y.max()',
'count': '_size()',},
group_by = ['x']). \
ex()
```

`groupby`

columns.```
wrap(d). \
project({
'max_y': 'y.max()',
'count': '_size()',
}). \
ex()
```

### Combining results between two `DataFrame`

s

To combine multiple tables in `data_algebra`

one uses what we call the `natural_join`

operator. In the `data_algebra`

`natural_join`

, rows are matched by column keys and any two columns with the same name are *coalesced* (meaning the first table with a non-missing values supplies the answer). This is easiest to demonstrate with an example.

Let’s set up new example tables.

```
d_left = pandas.DataFrame({
'k': ['a', 'a', 'b'],
'x': [1, None, 3],
'y': [1, None, None],
})
d_left
```

```
d_right = pandas.DataFrame({
'k': ['a', 'b', 'q'],
'y': [10, 20, 30],
})
d_right
```

`by`

argument) and what type of join we want (using the `jointype`

argument). For example we can use `jointype = 'LEFT'`

to augment our `d_left`

table with additional values from `d_right`

.```
ops = describe_table(d_left, table_name = 'd_left'). \
natural_join(b = describe_table(d_right, table_name = 'd_right'),
by = 'k',
jointype = 'LEFT')
ops.eval({'d_left': d_left, 'd_right': d_right})
```

### General conversion of record layouts

Record transformation is “simple once you get it”. However, we suggest reading up on that as a separate topic here.

## Composing operations

We could, of course, perform complicated data manipulation by sequencing `data_algebra`

operations, and saving intermediate values.

`data_algebra`

operators can also act on `data_algebra`

pipelines instead of acting on data. We can write our operations as follows.

We can use the `wrap()`

/`ex()`

pattern to capture both the operator pipeline and to apply it.

```
wrapped_ops = wrap(d). \
extend({
'row_number': '_row_number()',
},
partition_by = ['x'],
order_by = ['y', 'z']). \
select_rows(
'row_number == 1') . \
drop_columns(
"row_number")
wrapped_ops.underlying
```

```
wrapped_ops.ex()
```

`data_algebra`

operators can also act on `data_algebra`

pipelines instead of acting on data. We can write our operations as follows:```
ops = describe_table(d). \
extend({
'row_number': '_row_number()',
},
partition_by = ['x'],
order_by = ['y', 'z']). \
select_rows(
'row_number == 1') . \
drop_columns(
"row_number")
ops
```

`SQL`

to be run in remote databases. Applying this operator pipeline to our `DataFrame`

`d`

is performed as follows.```
ops.transform(d)
```

- Working over values with explicit intermediate variables.
- Working over values with a pipeline.
- Working over operators with a pipeline.

Being able to see these as all related gives some flexibility in decomposing problems into solutions. We have some more advanced notes on the differences in working modalities here and here.

## Conclusion

`data_algebra`

supplies a very teachable grammar of data manipulation based on Codd’s relational algebra and experience with pipelined data transforms (such as `base::transform()`

, `dplyr`

, `data.table`

, `Pandas`

, and `rquery`

).

For in-memory situations `data_algebra`

uses `Pandas`

as the implementation provider.

For bigger than memory situations `data_algebra`

can translate to any sufficiently powerful `SQL`

dialect, allowing `data_algebra`

pipelines to be executed on PostgreSQL, Apache Spark, or Google BigQuery.

In addition the `rquery`

R package supplies a nearly identical system for working with data in R. The two systems can even share data manipulation code between each other (allowing very powerful R/Python inter-operation or helping port projects from one to the other).

```
```

**leave a comment**for the author, please follow the link and comment on their blog:

**python – Win-Vector Blog**.

Want to share your content on python-bloggers? click here.