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 mask.
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 csv 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())
#import collections
#collections.Counter()

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 [5]:
csv = """
Name,Hours
Diana,10
Diana,11
Thrisha,15
Yuxiang,20
Sheamin,12
"""

staff = pd.read_csv(io.StringIO(csv))
staff
Out[5]:
Name Hours
0 Diana 10
1 Diana 11
2 Thrisha 15
3 Yuxiang 20
4 Sheamin 12
In [3]:
staff["Name"]
Out[3]:
0      Diana
1    Thrisha
2    Yuxiang
3    Sheamin
Name: Name, dtype: object

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 [6]:
staff = staff.set_index("Name")
staff
Out[6]:
Hours
Name
Diana 10
Diana 11
Thrisha 15
Yuxiang 20
Sheamin 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 [7]:
staff.index
Out[7]:
Index(['Diana', 'Diana', 'Thrisha', 'Yuxiang', 'Sheamin'], dtype='object', name='Name')
In [8]:
staff.columns
Out[8]:
Index(['Hours'], dtype='object')
In [9]:
staff["Hours"]
Out[9]:
Name
Diana      10
Diana      11
Thrisha    15
Yuxiang    20
Sheamin    12
Name: Hours, dtype: int64
In [10]:
type(staff["Hours"])
Out[10]:
pandas.core.series.Series

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 [11]:
staff["Hours"].describe()
Out[11]:
count     5.000000
mean     13.600000
std       4.037326
min      10.000000
25%      11.000000
50%      12.000000
75%      15.000000
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 [13]:
staff["Hours"]["Thrisha"]
Out[13]:
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 Yuxiang has 20 hours and Diana has 10 hours for a difference of 10.

In [14]:
staff["Hours"].max() - staff["Hours"].min()
Out[14]:
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.

In [15]:
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")
emissions
Out[15]:
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 [16]:
emissions = pd.read_csv(io.StringIO(csv))
emissions = emissions.set_index("City")
emissions
Out[16]:
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 [17]:
emissions["Emissions"] / emissions["Population"]
Out[17]:
City
New York    0.133333
Paris       1.142857
Beijing     0.150000
Nice        0.666667
Seattle     0.100000
dtype: float64
In [18]:
emissions["Emissions per Capita"] = emissions["Emissions"] / emissions["Population"]
emissions
Out[18]:
Country Emissions Population Emissions per Capita
City
New York USA 200 1500 0.133333
Paris France 48 42 1.142857
Beijing China 300 2000 0.150000
Nice France 40 60 0.666667
Seattle USA 100 1000 0.100000

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 [19]:
emissions["Population"] + 4 # this is not going to change emissions["Population"]
Out[19]:
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 [20]:
high_emissions = emissions["Emissions"] >= 200
emissions[high_emissions]
Out[20]:
Country Emissions Population Emissions per Capita
City
New York USA 200 1500 0.133333
Beijing China 300 2000 0.150000
In [21]:
high_emissions
Out[21]:
City
New York     True
Paris       False
Beijing      True
Nice        False
Seattle     False
Name: Emissions, dtype: bool

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 [22]:
high_emissions
Out[22]:
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 boolean series in a single expression.

In [23]:
emissions[high_emissions | (emissions["Country"] == "USA")]
Out[23]:
Country Emissions Population Emissions per Capita
City
New York USA 200 1500 0.133333
Beijing China 300 2000 0.150000
Seattle USA 100 1000 0.100000
In [24]:
high_emissions | (emissions["Country"] == "USA")
Out[24]:
City
New York     True
Paris       False
Beijing      True
Nice        False
Seattle      True
dtype: bool

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 [25]:
# Poll Question time!
emissions[(emissions["Country"] == "France") & (emissions["Population"] > 50)]
Out[25]:
Country Emissions Population Emissions per Capita
City
Nice France 40 60 0.666667

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 [26]:
emissions.loc[high_emissions, "Population"]
Out[26]:
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 [27]:
emissions.loc[high_emissions, "Country":"Population"]
Out[27]:
Country Emissions Population
City
New York USA 200 1500
Beijing China 300 2000
In [43]:
type(emissions)
Out[43]:
pandas.core.frame.DataFrame
In [28]:
emissions.loc[:, ["Country", "Emissions"]]
Out[28]:
Country Emissions
City
New York USA 200
Paris France 48
Beijing China 300
Nice France 40
Seattle USA 100
In [29]:
emissions.loc["Paris", "Country"]
Out[29]:
'France'

Returning to our prior staff hours example, we can get Thrisha'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 [33]:
staff.loc["Thrisha", "Hours"]
Out[33]:
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 [34]:
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 = []
    with open(path) as f:
        import csv
        reader = csv.DictReader(f)
        for row in reader:
            earthquakes.append(row)

    maxmag_earthquake = None
    for earthquake in earthquakes:
        if maxmag_earthquake is None or earthquake["magnitude"] > maxmag_earthquake["magnitude"]:
            maxmag_earthquake = earthquake
    return maxmag_earthquake["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 [35]:
earthquakes = pd.read_csv("earthquakes.csv", index_col="id")
display(earthquakes) # Helpful for debugging
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

In [36]:
earthquakes["magnitude"].idxmax()
Out[36]:
'us100068jg'
In [37]:
earthquakes.loc[earthquakes["magnitude"].idxmax(), "name"]
Out[37]:
'Northern Mariana Islands'
In [38]:
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")
    return earthquakes.loc[earthquakes["magnitude"].idxmax(), "name"]

doctest.run_docstring_examples(largest_earthquake_place, globals())

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 [41]:
# emissions.iloc[:, -2:]
# emissions.iloc[:, -2:-1]
emissions.iloc[:, -2]
Out[41]:
City
New York    1500
Paris         42
Beijing     2000
Nice          60
Seattle     1000
Name: Population, dtype: int64

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