Row Selection with DataFrames

One of the most important activities when working with data is to select a subset of the rows. Below is the full Course Catalog dataset, displaying all 8,589 courses offered in Fall 2019 at The University of Illinois. When Python displays the contents of a large DataFrame, it will show three things:

1. The first five rows of data,
2. A row of ..., and
3. The last five rows.

An example of displaying the full Course Catalog dataset:

import pandas as pd
df = pd.read_csv("https://waf.cs.illinois.edu/discovery/course-catalog.csv")
df

What is Row Selection?

Row selection is any operation that selects only a subset of all the rows in a DataFrame. All of the following are examples of selecting a subset of rows from the Course Catalog dataset:

• Selecting all courses offered in the STAT department,
• Selecting a random 1% sample of all courses offered at Illinois,
• Selecting five random courses offered at Illinois,
• Selecting the first ten courses from the dataset,
• Selecting the last five courses from the dataset,
• Selecting all courses with a Number in the 100s, or
• Selecting all courses with a course number greater than or equal to 241.

Quick Reference: Pandas Cheat Sheet

The pandas project provides an official "cheat sheet" of incredibly useful commands that can be found at https://pandas.pydata.org/Pandas_Cheat_Sheet.pdf. This sheet is incredibly useful, and many data scientists even have one printed out at their workspace!

Pandas Cheat Sheet, Page 1

Pandas Cheat Sheet, Page 2

We will explore areas of this cheat sheet so you can become familiar with it as a reference sheet. As you do more and more data science, you will find you will start to memorize the commands you use most often.

Pandas Cheat Sheet Section: "Subset Observation (Rows)"

The section in the bottom-center of the pandas cheat sheet is titled "Subset Observations (Rows)" and provides us with the most common functions to select a subset of the rows (also called observations) from our data:

Pandas Cheat Sheet, Page 1 with the "Subset Observation (Rows)" section highlighted

"Subset Observation (Rows)" section from Page 1 of the Pandas Cheat Sheet

Row Selection Based on the Contents of the Row

One of the most useful forms of row selection is to select only rows that match a specific criteria.

To select the rows that match a criteria, we need a conditional and to follow the first example given on the cheat sheet: df[df.Length > 7]. This line of code has four components:

1. The outer of df[ ___________ ] specifies that we will be selecting data from the DataFrame named df. The "square brackets" are necessary.

2. The inner df.Length specifies that we want to look at the column named Length inside of the DataFrame named df.

3. The > is a logical operation meaning "greater than". This is one of six common options that include:

   • >, for strictly greater than,
   • >=, for greater than or equal to
   • <, for strictly less than,
   • <=, for less than or equal to,
   • ==, for exactly equal to, and
   • !=, for NOT exactly equal to,
     
     

4. The 7 is value we are checking the data against, specifically checking if the data in each row is greater than 7.

   • Since 7 is a number, quotes are NOT used.
   • If we had a string like "Discovery", quotes MUST be used.

Example: Selecting the Courses Taught in Statistics

Let's use this syntax to select all courses taught by the STAT department. Referring to the dataset, the department that teaches the courses in the dataset is contained in the column named Subject:

Therefore, we need to check if the contents of each row's Subject data is equal to "STAT":

df[ df.Subject == "STAT" ]

Notice that the first row has an index of 8002 and the subject is STAT for ALL of the rows! 🎉

A few important things to note:

1. We are checking if the subject is exactly equal to to the string "STAT". To check if something is exactly equal to another value in Python, we use == or "double equals".

2. The data we're checking is NOT all numbers, so the data type of the subject names are strings. This requires that the value is contained in quotes (either 'STAT' or "STAT").

3. The column name (Subject) is also a string, but the use of the "dot notation" is a shortcut and one of the few exceptions where a string does not need quotes. If we wanted to be more explicit, the longer way to write this same statement is: df[ df["Subject"] == "STAT" ]. This style will be required if the column name contains a space.

Example: Selecting all Graduate / Professional Courses

At the University of Illinois, courses are specifically graduate or professional courses if their course number is at least 500 (specifically 500-799, but there are no courses higher than that).

The column name that contains the course number is Number and we can select the rows where the course number is greater than or equal to 500:

# Dot Notation:
df[ df.Number >= 500 ]

# Standard Notation:
df[ df['Number'] >= 500 ]

Random Row Selection

In many areas of data science, it will be important to divide the data into random groups or randomly sample a subset of the collected data. In both cases, we are taking a "random sample" of our data. The pandas library provides two methods to randomly sample the data:

1. Sample a fraction of the data by using df.sample() with a frac parameter:

   • Ex: df.sample(frac=0.5) samples 50% (0.5) of our data randomly.
   • Ex: df.sample(frac=0.01) samples 1% (0.01) of our data randomly.
     
     

2. Sample a specific number of rows/observations from the data by using df.sample() with a n parameter (or no parameter at all!):

   • Ex: df.sample(n=10) samples exactly 10 rows randomly.
   • Ex: df.sample(n=107) samples exactly 107 rows randomly.
   • Ex: df.sample(10) also samples exactly 10 rows randomly (the n is not required, but frac is required).

Since this is a random sample, every time you run df.sample the result will be different!

Example: A Random Course Schedule of Five Courses

An average student at the University of Illinois will take around five courses each semester. Using the Course Catalog dataset, we can randomly pick five courses to create a random schedule:

df.sample(n=5)

Running the exact same code again, our results are completely different since the selection is random:

df.sample(n=5)

Interested in learning different methods to select rows in a DataFrame? Check out our Guides on Row Selection using DataFrames:

• Data Science Guides: Row Selection using DataFrames

Example Walk-Throughs with Worksheets

Video 1: Row Selection with Pandas (Part 1)

Video 2: Row Selection with Pandas (Part 2)

Practice Questions