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.

In [1]:
import doctest
import io
import pandas as pd

Import statements¶

We've been writing some curious lines of code called import statements that deserve a short explanation: importing the doctest module for running our doctests and importing the pandas module for reading CSV data. The word module refers to code written in a file designed to be used elsewhere.

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 Counter 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 be able 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.

In [2]:
pd.read_csv("earthquakes.csv")
Out[2]:
id year month day latitude longitude name magnitude
0 nc72666881 2016 7 27 37.672333 -121.619000 California 1.43
1 us20006i0y 2016 7 27 21.514600 94.572100 Burma 4.90
2 nc72666891 2016 7 27 37.576500 -118.859167 California 0.06
3 nc72666896 2016 7 27 37.595833 -118.994833 California 0.40
4 nn00553447 2016 7 27 39.377500 -119.845000 Nevada 0.30
... ... ... ... ... ... ... ... ...
8389 nc72685246 2016 8 25 36.515499 -121.099831 California 2.42
8390 ak13879193 2016 8 25 61.498400 -149.862700 Alaska 1.40
8391 nc72685251 2016 8 25 38.805000 -122.821503 California 1.06
8392 ci37672328 2016 8 25 34.308000 -118.635333 California 1.55
8393 ci37672360 2016 8 25 34.119167 -116.933667 California 0.89

8394 rows × 8 columns

In [3]:
csv = """
Name,Hours
Anna,20
Iris,15
Abiy,10
Gege,12
"""

staff = pd.read_csv(io.StringIO(csv))
staff
Out[3]:
Name Hours
0 Anna 20
1 Iris 15
2 Abiy 10
3 Gege 12

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.

In [4]:
staff = staff.set_index("Name")
staff
Out[4]:
Hours
Name
Anna 20
Iris 15
Abiy 10
Gege 12

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.

In [5]:
staff.index
Out[5]:
Index(['Anna', 'Iris', 'Abiy', 'Gege'], dtype='object', name='Name')
In [6]:
staff["Hours"]
Out[6]:
Name
Anna    20
Iris    15
Abiy    10
Gege    12
Name: Hours, dtype: int64

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.
In [12]:
staff["Hours"].unique()
Out[12]:
array([20, 15, 10, 12])
In [13]:
staff["Hours"].describe()
Out[13]:
count     4.000000
mean     14.250000
std       4.349329
min      10.000000
25%      11.500000
50%      13.500000
75%      16.250000
max      20.000000
Name: Hours, dtype: float64

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

In [16]:
staff["Hours"]
Out[16]:
Name
Anna    20
Iris    15
Abiy    10
Gege    12
Name: Hours, dtype: int64
In [20]:
# How to select the TA(s) with 10 hours
staff[staff["Hours"] == 10]
Out[20]:
Hours
Name
Abiy 10
In [15]:
staff["Hours"]["Iris"]
Out[15]:
15

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.

In [24]:
staff["Hours"].max() - staff["Hours"].min()
Out[24]:
10
In [25]:
staff.max() - staff.min()
Out[25]:
Hours    10
dtype: int64
In [28]:
staff.min()
Out[28]:
Hours    10
dtype: int64

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.

In [29]:
csv = """
City,Country,Emissions,Population
New York,USA,200,1500
Paris,France,48,42
Beijing,China,300,2000
Nice,France,40,60
Seattle,USA,100,1000
"""

emissions = pd.read_csv(io.StringIO(csv), index_col="City")
# index_col="City" is like calling set_index("City"), but a little less coding
emissions
Out[29]:
Country Emissions Population
City
New York USA 200 1500
Paris France 48 42
Beijing China 300 2000
Nice France 40 60
Seattle USA 100 1000

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

In [30]:
emissions["Emissions"] / emissions["Population"]
Out[30]:
City
New York    0.133333
Paris       1.142857
Beijing     0.150000
Nice        0.666667
Seattle     0.100000
dtype: float64

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.

In [31]:
emissions["Population"] + 4
Out[31]:
City
New York    1504
Paris         46
Beijing     2004
Nice          64
Seattle     1004
Name: Population, dtype: int64

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?

In [32]:
high_emissions = emissions["Emissions"] >= 200
emissions[high_emissions]
Out[32]:
Country Emissions Population
City
New York USA 200 1500
Beijing China 300 2000

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.

In [33]:
high_emissions
Out[33]:
City
New York     True
Paris       False
Beijing      True
Nice        False
Seattle     False
Name: Emissions, dtype: bool

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 masks in a single expression.

In [46]:
emissions["Country"]
Out[46]:
City
New York       USA
Paris       France
Beijing      China
Nice        France
Seattle        USA
Name: Country, dtype: object
In [51]:
"USA" not in list(emissions["Country"]) and "France" in list(emissions["Country"])
Out[51]:
False
In [40]:
emissions[high_emissions | (emissions["Country"] == "USA")]
Out[40]:
Country Emissions Population
City
New York USA 200 1500
Beijing China 300 2000
Seattle USA 100 1000

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

In [55]:
[True, False] & [True, True] # [True, False]

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[55], line 1
----> 1 [True, False] & [True, True] # [True, False]

TypeError: unsupported operand type(s) for &: 'list' and 'list'
In [52]:
emissions[(emissions["Country"] == "France") & (emissions["Population"] > 50)]
Out[52]:
Country Emissions Population
City
Nice France 40 60
In [53]:
(emissions["Country"] == "France") & (emissions["Population"] > 50)
Out[53]:
City
New York    False
Paris       False
Beijing     False
Nice         True
Seattle     False
dtype: bool
In [54]:
emissions["Country" == "France" and "Population" > 50]

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File /opt/conda/lib/python3.10/site-packages/pandas/core/indexes/base.py:3791, in Index.get_loc(self, key)
   3790 try:
-> 3791     return self._engine.get_loc(casted_key)
   3792 except KeyError as err:

File index.pyx:152, in pandas._libs.index.IndexEngine.get_loc()

File index.pyx:181, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/hashtable_class_helper.pxi:7080, in pandas._libs.hashtable.PyObjectHashTable.get_item()

File pandas/_libs/hashtable_class_helper.pxi:7088, in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: False

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
Cell In[54], line 1
----> 1 emissions["Country" == "France" and "Population" > 50]

File /opt/conda/lib/python3.10/site-packages/pandas/core/frame.py:3893, in DataFrame.__getitem__(self, key)
   3891 if self.columns.nlevels > 1:
   3892     return self._getitem_multilevel(key)
-> 3893 indexer = self.columns.get_loc(key)
   3894 if is_integer(indexer):
   3895     indexer = [indexer]

File /opt/conda/lib/python3.10/site-packages/pandas/core/indexes/base.py:3798, in Index.get_loc(self, key)
   3793     if isinstance(casted_key, slice) or (
   3794         isinstance(casted_key, abc.Iterable)
   3795         and any(isinstance(x, slice) for x in casted_key)
   3796     ):
   3797         raise InvalidIndexError(key)
-> 3798     raise KeyError(key) from err
   3799 except TypeError:
   3800     # If we have a listlike key, _check_indexing_error will raise
   3801     #  InvalidIndexError. Otherwise we fall through and re-raise
   3802     #  the TypeError.
   3803     self._check_indexing_error(key)

KeyError: False

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.

In [58]:
emissions[high_emissions]["Population"]
Out[58]:
City
New York    1500
Beijing     2000
Name: Population, dtype: int64
In [56]:
emissions.loc[high_emissions, "Population"]
Out[56]:
City
New York    1500
Beijing     2000
Name: Population, dtype: int64

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.

In [59]:
emissions
Out[59]:
Country Emissions Population
City
New York USA 200 1500
Paris France 48 42
Beijing China 300 2000
Nice France 40 60
Seattle USA 100 1000
In [61]:
emissions.loc[high_emissions, "Country":"Emissions"]
Out[61]:
Country Emissions
City
New York USA 200
Beijing China 300
In [62]:
emissions.loc[:, ["Country", "Emissions"]]
Out[62]:
Country Emissions
City
New York USA 200
Paris France 48
Beijing China 300
Nice France 40
Seattle USA 100
In [63]:
emissions[["Country", "Emissions"]]
Out[63]:
Country Emissions
City
New York USA 200
Paris France 48
Beijing China 300
Nice France 40
Seattle USA 100
In [64]:
emissions.loc["Paris", "Country"]
Out[64]:
'France'
In [70]:
emissions.loc[["Paris"], ["Country", "Population"]]
Out[70]:
Country Population
City
Paris France 42

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.

In [65]:
staff.loc["Iris", "Hours"]
Out[65]:
15

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.

In [71]:
def largest_earthquake_place(path):
    """
    Returns the name of the place with the largest-magnitude earthquake in the specified CSV file.

    >>> largest_earthquake_place("earthquakes.csv")
    'Northern Mariana Islands'
    """
    earthquakes = pd.read_csv(path).to_dict("records")

    max_name = None
    max_magn = None
    for earthquake in earthquakes:
        if max_magn is None or earthquake["magnitude"] > max_magn:
            max_name = earthquake["name"]
            max_magn = earthquake["magnitude"]
    return max_name


doctest.run_docstring_examples(largest_earthquake_place, globals())

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

In [77]:
def largest_earthquake_place(path):
    """
    Returns the name of the place with the largest-magnitude earthquake in the specified CSV file.

    >>> largest_earthquake_place("earthquakes.csv")
    'Northern Mariana Islands'
    """
    earthquakes = pd.read_csv(path, index_col="id")
    display(earthquakes) # Helpful for debugging: delete when done
    return earthquakes.loc[earthquakes["magnitude"].idxmax(), "name"]


doctest.run_docstring_examples(largest_earthquake_place, globals())
year month day latitude longitude name magnitude
id
nc72666881 2016 7 27 37.672333 -121.619000 California 1.43
us20006i0y 2016 7 27 21.514600 94.572100 Burma 4.90
nc72666891 2016 7 27 37.576500 -118.859167 California 0.06
nc72666896 2016 7 27 37.595833 -118.994833 California 0.40
nn00553447 2016 7 27 39.377500 -119.845000 Nevada 0.30
... ... ... ... ... ... ... ...
nc72685246 2016 8 25 36.515499 -121.099831 California 2.42
ak13879193 2016 8 25 61.498400 -149.862700 Alaska 1.40
nc72685251 2016 8 25 38.805000 -122.821503 California 1.06
ci37672328 2016 8 25 34.308000 -118.635333 California 1.55
ci37672360 2016 8 25 34.119167 -116.933667 California 0.89

8394 rows × 7 columns

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.

In [ ]:
emissions.iloc[:, -2:]

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