The Data Science PipelineWrangling

Data is said to be in tidy format if each row corresponds to an observation and each column corresponds a different observation variable. For example, in the iris dataset, each row represents a flower, and the entries of a row specify the flower's species and various measurements made for that flower. Here's the head of the Iris data frame.

Note that four of the columns are quantitative (that is, they contain numerical data), while one is categorical (that is, they contain strings which represent categories).

Continue

Many types of data do not fit naturally into the tidy data framework, like image data and passages of text. However, tidy data occupies a sweet spot of generality which covers a wide variety of use cases but is nevertheless specific enough to support tools which are both concise and powerful. In this mini-course, we will focus on tidy data.

Continue

Pandas

A data frame is an object for storing tidy data, and the package which provides data frames in the Python ecosystem is Pandas. Pandas is built on NumPy, which is the Python library for multi-dimensional arrays. If you aren't comfortable with the basics of NumPy, a brief detour through this interactive notebook is recommended.

import pydataset
iris = pydataset.data("iris")
print(type(iris))
iris.head()

A data frame's index attribute stores the labels of the rows, and the columns attribute stores the column names.

iris.columns, iris.index

Continue

Columns of the data frame can be accessed using by indexing the data frame with the name of the column. For column names that are valid Python variable names, dot syntax access is also supported:

import pandas as pd
data = [[179_335,47.7],[81_274,73.4],[24_590,19.9],[22_131,25.4]]
columns = ['population', 'area']
index = ['Providence', 'Cranston', 'Newport', 'Bristol']
ri = pd.DataFrame(data, columns = columns, index = index)
ri['population'] # or ri.population, same thing

Continue

Pandas DataFrame objects provide two attributes, called loc and iloc, for accessing entries using names or integers, respectively.

ri.loc['Providence',:'area'], ri.iloc[0,:1]

Continue

Note that slices built with row or column names are inclusive, while integer slices follow the usual Python convention of being exclusive of the upper bound.

Solution. We index columns using loc and the rows using iloc

boston = pydataset.data('Boston')
boston_trimmed = boston.loc[:,'indus':'rad']
boston_trimmed.iloc[:25,:]

Continue

The six verbs of data manipulation

Although data frames support many transformations, we will follow the prominent data scientist Hadley Wickham in suggesting the following six as fundamental. They can be combined to cover most of your data manipulation needs, so you can get up and running quickly in any data manipulation framework by learning how these actions are performed and composed.

Continue

1. Filter. Pick rows based on their values.

Continue

2. Sort. Re-order the rows.

Continue

3. Select. Choose specific columns.

Continue

4. Transform. Create new columns from existing ones.

Continue

5. Aggregate. Reduce the data frame to a single row by applying a function (like sum, min, max, etc.) which maps each column of values to a single value.

Continue

6. Group. Collect the rows of the data frame into groups.

Continue

The grouping operation is different from the others because it outputs a grouped data frame object, which can be visualized as a stack of data frames. Here's how the popular data table product Airtable presents grouped data frames (using some of the rows from the iris data set above, grouped on species):

The other operations can be applied group-by-group.

Continue

Filter

There are two main ways to filter rows in Pandas. The first is to obtain a column of boolean values and use it to index the rows of the data frame. For example:

ri[ri.population > 50_000]

In the last line above, ri.population > 50_000 returns a column with the values [True, True, False, False], and indexing the data frame with a boolean array selects only those rows corresponding to the True values.

Continue

We can combine predicates using the usual Python operations for sets:

ri[(ri.population > 50_000) & (ri.area > 50)]

This approach has some drawbacks: First, we have to repeat the name of the data frame multiple times. This violates the programming maxim "Don't repeat yourself". Second, computing an expression like (ri.population > 50_000) & (ri.area > 50) requires three array allocations: one for ri.population > 50_000, one for ri.area > 50, and one for the (ri.population > 50_000) & (ri.area > 50). These allocations are unnecessary, since it is possible to just loop over the rows and directly check the whole condition for each row (although you don't want to program that yourself in Python, because loops in Python are slow).

Continue

Pandas does provide a solution to this problem: the query method. You supply your condition as a string, and Pandas makes things efficient for you under the hood:

ri.query('population > 50_000 & area > 50')

Continue

Python variables can be interpolated in query strings using the @ prefix. For example, the query above could also be written as

min_pop = 50_000
ri.query('population > @min_pop & area > 50')

Solution. We use parentheses to group the given logical conditions:

import pydataset
boston = pydataset.data('Boston')
boston.query('indus >= 10 & (medv < 8 | chas == 1)')

Continue

Sort

The DataFrame method that sorts values is called sort_values. It takes an argument for the column labels (or list of columns labels) to use for sorting, and you can use the ascending argument to specify whether the values in that column should be in increasing or decreasing order.

ri.sort_values('population',ascending=False)

If a list of columns is supplied, then each column after the first is used to break ties in the preceding columns:

d = pd.DataFrame([[3,2],[3,1],[2,4]],columns=['a','b'])
d.sort_values(['a','b'])

Note that sort_values returns a new data frame. It does not modify the original one.

Solution. We can inspect columns or look at the data frame's head to see that the first column is 'crim'. Then we sort:

boston = pydataset.data('Boston')
boston.sort_values(['crim'], ascending = False)

Continue

Select

To select columns in Pandas, you can just index the data frame with a list of column names:

ri[['population','area']]

Continue

If you want to keep all columns except specific ones, you can use the drop method:

ri.drop('population', axis=1)

We have to specify that 'population' refers to columns (axis=1), because the drop method's default is to look for rows to drop. Like sort_values, drop doesn't modify the original data frame.

Continue

Solution. We inspect the data frame to find that the columns which are not floats are 'chas', rad, and 'tax'. So we use drop instead of selecting:

boston_float_only = boston.drop(['chas', 'rad', 'tax'],axis=1)
boston_float_only.head()

As a follow-up, we note that this is a sufficiently common operation that Pandas supplies a convenience method for it:

import numpy as np
boston_float_only = boston.select_dtypes(exclude=['int'])
boston_float_only.head()

You can inspect the types of the columns of a data frame using its dtypes attribute.

Continue

Transform

We can create new columns in a data frame using the assign method. For example:

ri.assign(density = ri.population / ri.area)

If the name of the data frame is quite long, you can avoid having to type it repeatedly by supplying an anonymous function to be applied to the data frame:

ri.assign(density = lambda d: d.population / d.area) \
  .assign(**{'inverse density': lambda d: 1/d.density})

We are using two assign calls to create a column called density and then a second new column called inverse density. (Note how we used splatting to get a space in the column name.)

Solution. Since the formula is quite involved, we use an anonymous function:

import numpy as np
toothpaste = pydataset.data('toothpaste')
toothpaste.assign(score = lambda d: 
      (d.meanA - d.meanB)/np.sqrt(d.sdA**2 + d.sdB**2))

Continue

Aggregate

The agg method applies a specified function (called the aggregation function) to each column in a data frame. Several aggregation functions are built-in and can be specified using a string:

For example, to find the total population of the four cities in our data frame, we run:

ri.population.agg('sum')

To find the average population and area:

ri.agg('mean')

You can also supply a custom aggregation function instead of a string.

Solution. We have to drop the categorical column first since we can't compute a range for that.

import numpy as np
import pydataset
iris = pydataset.data('iris')
iris.drop(['Species'],axis=1).agg(lambda r: np.max(r) - np.min(r))

Continue

Group

The aggregation function is a little bland by itself. We often want to apply the aggregation function only to specific rows. For example consider the following transaction log:

What is the total transaction amount for each company? We could filter for each company and aggregate the two resulting data frames, but that would get unwieldy quickly as the number of companies grows. Instead, we group the data frame by customer name and then apply the aggregation function:

columns = ['Customer name', 'Transaction amount']
transactions = pd.DataFrame([['Acme, Inc.',45.03],
                             ['Acme, Inc.',118.22],
                             ['XYZ Widgets',35.55],
                             ['Acme, Inc.',22.04],
                             ['XYZ Widgets',34.78]],
                             columns=columns)
transactions.groupby('Customer name').agg('sum')

When a data frame is grouped along a particular column, the resulting grouped data frame is a dictionary-like object whose keys are the unique values of that column and whose values are the data frames obtained by filtering for the given key in that column. For example, transactions.groupby('Customer name') maps the key 'Acme, Inc.' to the data frame

and 'XYZ Widgets' to

The agg method of DataFrameGroupBy object operates on each each of these data frames to produce a single row, and these rows are collected into a new data frame. The row index for this output data frame comes from the keys of the dictionary-like DataFrameGroupBy object.

Continue

import pydataset
iris = pydataset.data('iris')
 

Solution. We create a new column using the np.floor function. Then we perform the grouping, select the column we want, and aggregate using the mean function:

import pydataset
import numpy as np
iris = pydataset.data('iris') 
iris.assign(sepal_length_floor = np.floor(iris["Sepal.Length"])) \
    .groupby('sepal_length_floor') \
    [['Sepal.Width']] \
    .agg('mean')

Continue

We conclude this section by noting that the other four operations (filter, sort, select, and transform) can be applied to grouped data frames as well. However, only selection works directly on grouped data frames. For the others, we use the apply method of the grouped data frame object to operate group-by-group and collect the results into a single data frame.

For example, we can sort by petal length within species as follows:

iris.groupby('Species').apply(lambda d: d.sort_values('Petal.Length'))

You'll notice in data frame returned above that the row indices are retained from the original data frame. Actually, the group values are also incorporated into the indexing scheme—this is advanced feature of Pandas called a multi-indexing. The reset_index method is useful for dropping this extra structure and simply re-indexing the rows from 0. We need to supply the value True to the keyword argument drop, because otherwise the reset_index method will try to keep the old indices around as new columns, and that will fail since we already have a Species column:

(iris.groupby('Species')
     .apply(lambda d: d.sort_values('Petal.Length'))
     .reset_index(drop = True))