Data Frames

Earlier, we learned how to process CSV files using the list of dictionaries representation. This week, we will introduce pandas, the most commonly-used Python data programming tool and one that we’ll be using for the remainder of the course. By the end of this lesson, students will be able to:

• Import values and functions from another module using import and from statements.
• Select individual columns from a pandas DataFrame and apply element-wise computations.
• Filter a pandas DataFrame or Series with a boolean series.

The last two learning objectives are particularly ambitious: it will take much more deliberate practice before you feel comfortable.

Import statements¶

We’ve been writing some curious lines of code called import statements to use code written in a separate module by other people.

The simplest syntax uses the import statement to import a module like doctest. We can then call the definitions within that module like doctest.testmod() to run all our doctests.

import doctest
doctest.testmod()

We can also import a module and rename it to a more convenient shorthand, like pd instead of pandas. We can then call the definitions within the module like pd.read_csv(path).to_dict("records") to read a CSV file and then convert it into our list of dictionaries (“records”) representation.

import pandas as pd
earthquakes = pd.read_csv(path).to_dict("records")

Finally, Python can also import just a single definition from a module. Here, we ask Python to only import the Counter dictionary type from the collections module.

from collections import Counter
with open(path) as f:
    return Counter(f.read().split())

A common practice in notebooks is to add your imports to the first code cell at the top of your notebook so that someone who’s running your notebook will know what modules they will need to install to run the code.

Creating a Data Frame¶

To create a dataframe, call pd.read_csv(path). In addition to reading CSV data from a file, pd.read_csv also accepts io.StringIO to read-in CSV data directly from a Python string for specifying small datasets directly in a code cell.

The index of a DataFrame appears in bold across the left (rows) and defines the keys for accessing values in a data frame. Like keys in a dictionary, the keys in an index should be unique.

By default, an integer index is provided, but you’ll often want to set a more meaningful index. We can use the df.set_index(colname) function to return a new DataFrame with a more meaningful index that will be handy for later. In the example below, we assume that each TA has a unique name, though this assumption has severe limits in practice: people can change their names, or we might eventually run a course where two people share the same names.

Column indexers¶

In pandas, tabular data is represented by a DataFrame as shown above. Unlike the list of dictionaries format that required us to write a loop to access the name of every TA, pandas provides special syntax to help us achieve this result.

df["Hours"] returns a pandas object called a Series that represents a single column or row of a DataFrame. A Series is very similar to a list from Python, but has several convenient functions for data analysis.

• s.mean() returns the average value in s.
• s.min() returns the minimum value in s.
  • s.idxmin() returns the label of the minimum value in s.
• s.max() returns the maximum value in s.
  • s.idxmax() returns the label of the maximum value in s.
• s.unique() returns a new Series with all the unique values in s.
• s.describe() returns a new Series containing descriptive statistics for the data in s.

Defining a more meaningful index allows us to select specific values from a series just by referring to the desired key.

How can we compute the range of TA hours by calling the min() and max() functions? For this example dataset, the range should be 10 since Anna has 20 hours and Abiy has 10 hours for a difference of 10.

Element-wise operations¶

Let’s consider a slightly more complex dataset that has more columns, like this made-up emissions dataset. The pd.read_csv function also includes an index_col parameter that you can use to set the index while reading the dataset.

pandas can help us answer questions like the emissions per capita: emissions divided by population for each city.

Element-wise operations also work if one of the operands is a single value rather than a Series. For example, the following cell adds 4 to each city population.

Row indexers¶

All the above operations apply to every row in the original data frame. What if our questions involve returning just a few rows, like filtering the data to identify only the cities that have at least 200 emissions?

This new syntax shows how we can filter a dataframe by indexing it with a boolean series. PandasTutor shows you how the above output is determined by selecting only the rows that are True in the following boolean series.

Multiple conditions can be combined using the following element-wise operators.

• & performs an element-wise and operation.
• | performs an element-wise or operation.
• ~ performs an element-wise not operation.

Due to how Python evaluates order of operations, parentheses are required when combining boolean series in a single expression.

Write a one-line pandas expression that returns all the cities in France that have a population greater than 50 from the emissions dataset.

Selection by label¶

To summarize what we’ve learned so far, pandas provides both column indexers and row indexers accessible through the square brackets notation.

• df[colname] returns the corresponding Series from the df.
• df[boolean_series] returns a new DataFrame containing just the rows specified True in the boolean_series.

These two access methods are special cases of a more general df.loc[rows, columns] function that provides more functionality. For example, we can select just the city populations for cities with at least 200 emissions and visualize the procedure in PandasTutor.

Whether a single value, a 1-dimensional Series, or a 2-dimensional DataFrame is returned depends on the selection.

Notice that label-based slicing includes the endpoint, unlike slicing a Python list.

Returning to our prior staff hours example, we can get Iris’s hours by using a single df.loc[index, columns] access rather than two separate accesses. This convenient syntax only works when we’ve specified a meaningful index.

Practice: Largest earthquake place (Pandas)¶

Previously, we learned about two ways to write Python code to read earthquakes as a list of dictionaries and return the name of the place with the largest-magnitude earthquake.

How might we convert this program to solve the problem directly with a DataFrame instead?

Optional: Selection by position¶

Everything we’ve learned so far is an example of label-based indexing. But it turns out there’s another system of position-based indexing that is also available. Let’s compare the 4 approaches.

• df[colname] returns the corresponding Series from the df.
  • df[[col1, col2, ...]] returns a new DataFrame containing the corresponding columns from the df.
• df[boolean_series] returns a new DataFrame containing just the rows specified True in the boolean_series.
• df.loc[index, columns] returns a single value, a Series, or a DataFrame for the label-based selection from the df.
• df.iloc[rows, columns] returns a single value, a Series, or a DataFrame for the position-based selection from the df.

Label-based indexing uses the bolded column and row indexers. Position-based indexing uses purely integer-based indexing. Slicing by position excludes the endpoint, just like slicing a Python list. Position-based indexing is most useful when you have a position-based query that can’t be easily specified using only label-based indexing. For example, we might know that we want to select just the rightmost two columns from a dataframe without knowing the column names.

We generally won’t use position-based selections in this course, but you may run into code that uses them elsewhere.