Using R-style Data Pipelines in Notebooks
Tags: python, datascience, jupyter, pandas
Published: 2020-03-17 23:48:51

---
title: Using R-style Data Pipelines in Notebooks
published: true
date: 2020-03-17 05:00:00 UTC
tags: python, datascience, jupyter, pandas
series: Data Science for Developers
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/2tf89vvthexnx1wvo0u1.png
canonical_url: https://jhermann.github.io/blog/python/data-science/2020/03/17/data_pipes.html
---
> *Mutate Pandas data frames simply and elegantly with data pipelines.*

## Overview

This post shows how mutating data frames can be written more elegantly (and thus understood more easily) by using _data pipelines_. R users know this concept from the `dplyr` package, and Python offers a similar one named `dfply`.


## Setting the Stage

We start off with some global definitions…

```python
import numpy as np
import pandas as pd
```

The sample data (about OS package deployments) is read into the `raw_data` dataframe from a CSV file.

```python
raw_data = pd.read_csv("../assets/data/cmdb-packages.csv", sep=',')

print('♯ of Records: {}\n'.format(len(raw_data)))

for name in raw_data.columns[1:]:
    if not name.startswith('Last '):
        print(name, '=', list(sorted(set(raw_data[name].fillna('')))))

raw_data.head(3).transpose()
```
```
♯ of Records: 146

Distribution = ['Debian 8.11', 'Debian 8.6', 'Debian 8.9', 'jessie']
Architecture = ['amd64']
Environment = ['', 'Canary', 'DEV', 'LIVE', 'QA']
Team = ['Automation', 'Big Data', 'Email', 'Ops App1', 'Ops Linux', 'Persistence', 'Platform']
Installed version = ['41.15-2(amd64)', '42.28-2(amd64)', '42.44-1(amd64)', '45.11-1(amd64)', '48.33-1(amd64)']
```

| | 0 | 1 | 2 |
| --- | --- | --- | --- |
| CMDB\_Id | 274656589 | 153062618 | 282201163 |
| Distribution | jessie | jessie | jessie |
| Architecture | amd64 | amd64 | amd64 |
| Environment | DEV | DEV | LIVE |
| Team | Ops App1 | Ops App1 | Ops App1 |
| Last seen | 2019-02-18 11:43 | 2019-02-18 11:56 | 2019-02-18 12:04 |
| Last modified | 2019-02-18 11:43 | 2019-02-18 11:56 | 2019-02-18 12:04 |
| Installed version | 42.28-2(amd64) | 42.28-2(amd64) | 48.33-1(amd64) |

The `map_distro()` helper function is used in the following sections to clean up the `Distribution` column.

```python
def map_distro(name):
    """Helper to create canonical OS names."""
    return (name.split('.', 1)[0]
        .replace('Debian 7', 'wheezy')
        .replace('Debian 8', 'jessie')
        .replace('Debian 9', 'stretch')
        .replace('Debian 10', 'buster')
        .replace('squeeze', 'Squeeze [6]')
        .replace('wheezy', 'Wheezy [7]')
        .replace('jessie', 'Jessie [8]')
        .replace('stretch', 'Stretch [9]')
        .replace('buster', 'Buster [10]')
    )
```


## Data Cleaning With Pandas

This code cleans up the imported data using the _Pandas_ API.

To get sensible version statistics, we split off the auxiliary information in the version column (anything after `-`), leaving just the _upstream_ part of the version string. The environment classifier is also cleaned up a little, and distributions are mapped to a canonical set of names. Some unused columns are dropped.

Finally, a subset of unique version samples is selected.

```python
data = raw_data
data = data.assign(Version=data['Installed version'].str.split('-', 1, expand=True)[0])
data = data.assign(Environment=data.Environment.fillna('UNDEFINED').str.upper())
data = data.assign(Distribution=data.Distribution.apply(map_distro))
data = data.drop(columns=['CMDB_Id', 'Last seen', 'Last modified', 'Installed version'])
data = data.drop_duplicates(subset='Version', keep='first')

data.transpose()
```

| | 0 | 2 | 26 | 45 | 62 |
| --- | --- | --- | --- | --- | --- |
| Distribution | Jessie [8] | Jessie [8] | Jessie [8] | Jessie [8] | Jessie [8] |
| Architecture | amd64 | amd64 | amd64 | amd64 | amd64 |
| Environment | DEV | LIVE | LIVE | UNDEFINED | DEV |
| Team | Ops App1 | Ops App1 | Platform | Email | Platform |
| Version | 42.28 | 48.33 | 41.15 | 45.11 | 42.44 |


## Data Cleaning With Pipelines

This does the exact same processing as the code above, but is arguably more readable and maintained more easily:

- It has less boilerplate, and makes the use of pipelined processing transparent.
- Each step clearly states what it does to the data.
- When steps are copied into other pipelines, the `X` placeholder ensures you use the data of _this_ pipeline (the code is more DRY).

```python
from dfply import *

piped = (raw_data
    >> mutate(Version=X['Installed version'].str.split('-', 1, expand=True)[0])
    >> mutate(Environment=X.Environment.fillna('UNDEFINED').str.upper())
    >> mutate(Distribution=X.Distribution.apply(map_distro))
    >> drop(X.CMDB_Id, X['Last seen'], X['Last modified'], X['Installed version'])
    >> distinct(X.Version)
)

piped.transpose()
```
The result is identical to the pure Pandas code, as expected.

| | 0 | 2 | 26 | 45 | 62 |
| --- | --- | --- | --- | --- | --- |
| Distribution | Jessie [8] | Jessie [8] | Jessie [8] | Jessie [8] | Jessie [8] |
| Architecture | amd64 | amd64 | amd64 | amd64 | amd64 |
| Environment | DEV | LIVE | LIVE | UNDEFINED | DEV |
| Team | Ops App1 | Ops App1 | Platform | Email | Platform |
| Version | 42.28 | 48.33 | 41.15 | 45.11 | 42.44 |

To learn more about `dfply`, read the [dplyr-style Data Manipulation with Pipes in Python](https://towardsdatascience.com/dplyr-style-data-manipulation-with-pipes-in-python-380dcb137000) blog post, which has more examples.


## Reference Links

### dfply

- [dplyr-style Data Manipulation with Pipes in Python – Towards Data Science](https://towardsdatascience.com/dplyr-style-data-manipulation-with-pipes-in-python-380dcb137000)
- [kieferk/dfply: dplyr-style piping operations for Pandas dataframes](https://github.com/kieferk/dfply)

### Alternatives

- [has2k1/plydata: A grammar for data manipulation in Python](https://github.com/has2k1/plydata)
- [shaypal5/pdpipe: Easy pipelines for Pandas dataframes](https://github.com/shaypal5/pdpipe)