Data Subsetting: Select, Filter, and Sort

Introduction

In our previous lesson, we took the first crucial step in our hands-on data science journey: loading a dataset into a pandas DataFrame and performing an initial inspection. You learned how to use methods like df.head(), df.info(), and df.shape to get a high-level understanding of your data's structure. Now, it's time to move beyond simply looking at the data to actively shaping it.

This lesson addresses how to select, filter, and sort your data. These operations are the bedrock of data manipulation, allowing you to isolate the precise information needed to answer specific business questions. Instead of working with an entire massive dataset, you can create smaller, more relevant subsets. For instance, you might want to analyze "sales from the last quarter" or "top-performing employees in a specific region." Today, you'll learn the practical Python techniques to do just that.

Selecting Specific Columns

The simplest way to subset your data is by selecting only the columns you need. If your dataset has 50 columns but your analysis only requires five, creating a smaller DataFrame with just those columns makes your work cleaner and more efficient.

You can select a single column using single square brackets, which returns a pandas Series. More commonly, you'll select multiple columns by passing a list of column names inside double square brackets, which returns a new DataFrame.




# Assume 'df' is our loaded DataFrame




# Select a single column (returns a Series)
user_ids = df['UserID']




# Select multiple columns (returns a new DataFrame)
customer_info = df[['UserID', 'Name', 'SignupDate']]

This is a fundamental operation you'll use constantly. The following reading provides a concise summary and example code.

A Comprehensive Guide to Data Analysis using Pandas

This article from Analytics Vidhya uses an IMDB movie dataset to demonstrate core pandas operations. We will use it to quickly review the syntax for selecting columns.

Please read the short subsection titled "Extract Data Using Columns" within the "Data Selection – Indexing and Slicing" section. Focus on the distinction between selecting a single column and selecting multiple columns.

Filtering Rows Based on Conditions

Selecting columns narrows the width of your data; filtering rows narrows its length. This is where you start asking powerful questions. Filtering, often called "boolean indexing," works by creating a condition that evaluates to True or False for each row. Pandas then returns only the rows where the condition is True.

For example, to find all movies released after 2015:
df[df['Year'] > 2015]

Things get more interesting when you combine conditions. To find movies released after 2015 with a rating of 8.0 or higher, you would use the & (AND) operator.

df[(df['Year'] > 2015) & (df['Rating'] >= 8.0)]

Notice the parentheses around each individual condition. These are mandatory due to Python's operator precedence rules and are a common source of errors for beginners.

This image shows a typical filtering operation in pandas, where multiple conditions (`Team` and `Age`) are combined using the `&` operator to create a highly specific subset of the original DataFrame.

The following video from Alex The Analyst provides an excellent, fast-paced tutorial on various filtering techniques.

Filtering Columns and Rows in Pandas | Python Pandas Tutorials

This video offers a practical walkthrough of the most common ways to filter a DataFrame, from simple numeric comparisons to more advanced string matching.

Please watch the following segments carefully: Numeric Conditions: From the beginning of the filtering section, watch how to use comparison operators like < and <= to filter rows based on numerical values. This clip covers the basic syntax. Filtering with a List: Watch the section on the .isin() method, which is the programmatic way to filter for rows where a column's value is one of several options (similar to IN in SQL). This section demonstrates its use. String Matching: Finally, watch the explanation of .str.contains(). This is incredibly useful for finding rows based on partial text matches, like finding all products with "USB-C" in their name. This clip shows you how.

Precise Selection with `.loc` and `.iloc`

While boolean indexing is great for filtering based on data values, loc and iloc give you direct access to rows and columns by their labels and integer positions, respectively. This is essential for more complex data retrieval and manipulation.

df.iloc[row_positions, column_positions]: Selects data by integer position. df.iloc[0] gets the first row. df.iloc[0:5, 0:2] gets the first five rows and the first two columns.
df.loc[row_labels, column_labels]: Selects data by label. This uses the DataFrame's index and column names. df.loc[0] gets the row with an index label of 0. If you set a column like 'Title' as the index, you could use df.loc['The Dark Knight'].

The general syntax is df.loc[rows, columns]. You can use this to select specific rows and columns in a single, clean command.

A Comprehensive Guide to Data Analysis using Pandas

Let's return to the Analytics Vidhya article to see examples of loc and iloc in action.

In the "Data Selection – Indexing and Slicing" section, review the part on Extract Data Using Rows. Then, look at the first code block in the next section, "Data Selection – Based on Conditional Filtering," to see how multiple conditions are combined.

Sorting Data for Analysis

After filtering your data to get the subset you need, you'll often want to sort it. For example, you might want to see the highest-rated movies at the top of your list or sort customers by their most recent purchase date. The .sort_values() method is the tool for this.

You can sort by a single column or by multiple columns in a specified order. A key parameter is ascending, which you can set to True or False.




# Sort by a single column in descending order
df.sort_values(by='Rating', ascending=False)




# Sort by multiple columns: first by Year (descending), then by Rating (descending)
df.sort_values(by=['Year', 'Rating'], ascending=[False, False])

The next video clip demonstrates this clearly.

Filtering Columns and Rows in Pandas | Python Pandas Tutorials

This final clip from Alex The Analyst's video shows you how to use .sort_values() to order your data effectively.

Please watch these two segments: Single Column Sort: Watch the introduction to .sort_values(), which covers sorting by one column and using the ascending parameter. This section covers the basics. Multiple Column Sort: This is a powerful feature. Pay close attention to how you can pass a list of columns to sort by, and how the order of columns determines the sorting priority. This clip provides a great example.

As a final point, professional data analysts often chain these operations together to write concise and readable code. Instead of creating intermediate variables for each step, you can perform a sequence of operations in a single statement.

Pandas in the World: Master Real-World Data Analysis & Unlock Global Insights

This resource on "Pandas in the World" highlights several best practices. We'll focus on one that directly relates to what we've learned today: method chaining.

Please read the "Best Practices for Effective pandas Use" section, paying close attention to the first point on Chaining methods. The code example clearly illustrates how filtering, grouping (which we'll cover later), and sorting can be combined into a single, elegant expression.

Conclusion

In this lesson, you've acquired the fundamental skills for manipulating DataFrames. You are no longer just a passive observer of your data; you can now actively query, subset, and organize it to find the signals within the noise.

Here are the key takeaways:

Column Selection: Use df[['col1', 'col2']] to create a DataFrame with only the columns you need.
Conditional Filtering: Use boolean indexing like df[(df['colA'] > 5) & (df['colB'] == 'value')] to select rows that meet specific criteria. Remember the parentheses around each condition.
Precise Selection: Use df.iloc[] for integer-position-based selection and df.loc[] for label-based selection to access specific slices of your data.
Sorting: Use df.sort_values(by='col', ascending=False) to order your data, which is essential for ranking and finding extremes.

These skills—selecting, filtering, and sorting—form a loop you will run through countless times in any analysis. You'll isolate a subset of data, analyze it, form a new question, and then slice the data in a new way.

In our next lesson, we'll address a ubiquitous and critical problem in real-world data: what to do when your data is incomplete. We will learn how to handle missing values using various deletion and imputation strategies.

Recommended resources