5 Best Ways to Create a Subset in Python Pandas Using Specific Values from Columns

by

💡 Problem Formulation: When working with datasets in Python’s Pandas library, you may encounter situations where you need to extract a subset of data by selecting specific values based on column indexes. For instance, suppose you have a DataFrame containing sales data, and you want to create a smaller dataset that only includes sales from the second quarter. This article will guide you through various methods to achieve this task, helping you efficiently manipulate and analyze your data.

Method 1: Use .loc[] for Label-Based Indexing

The .loc[] indexer in Pandas allows you to select data based on label information. It’s useful when the index of the DataFrame is a label (e.g., a string). You can specify the rows and the columns you want to access with labels.

Here’s an example:

import pandas as pd

# Sample DataFrame
data = pd.DataFrame({
    'Product': ['A', 'B', 'C', 'D'],
    'Sales_Q2': [250, 150, 200, 300],
    'Sales_Q3': [260, 160, 210, 310]
})
second_quarter_sales = data.loc[:, ['Product', 'Sales_Q2']]

print(second_quarter_sales)

Output:

  Product  Sales_Q2
0      A       250
1      B       150
2      C       200
3      D       300

This code snippet illustrates how to use .loc[] to select all rows (indicated by the colon) and the specific columns ‘Product’ and ‘Sales_Q2’. This method returns data for the second quarter for all products.

Method 2: Use .iloc[] for Position-Based Indexing

The .iloc[] indexer in Pandas is position-based. It is ideal when you want to select data based on the integer index regardless of the DataFrame index labels.

Here’s an example:

import pandas as pd

# Sample DataFrame
data = pd.DataFrame({
    'Product': ['A', 'B', 'C', 'D'],
    'Sales_Q2': [250, 150, 200, 300],
    'Sales_Q3': [260, 160, 210, 310]
})
second_quarter_sales = data.iloc[:, [0, 1]]

print(second_quarter_sales)

Output:

  Product  Sales_Q2
0      A       250
1      B       150
2      C       200
3      D       300

In this code snippet, we use .iloc[] to select all rows and the first two columns by their positions, which correspond to ‘Product’ and ‘Sales_Q2’. Position-based indexing is straightforward when you know the column positions.

Method 3: Boolean Indexing with .loc[]

Boolean indexing uses a boolean vector to filter the DataFrame. Combined with .loc[], it enables row-wise subsetting based on conditions.

Here’s an example:

import pandas as pd

# Sample DataFrame
data = pd.DataFrame({
    'Product': ['A', 'B', 'C', 'D'],
    'Sales_Q2': [250, 150, 200, 300],
    'Sales_Q3': [260, 160, 210, 310]
})
condition = (data['Sales_Q2'] > 200)
second_quarter_sales = data.loc[condition, ['Product', 'Sales_Q2']]

print(second_quarter_sales)

Output:

  Product  Sales_Q2
0      A       250
3      D       300

This example filters the DataFrame to include only rows where ‘Sales_Q2’ values are greater than 200, alongside selecting the ‘Product’ and ‘Sales_Q2’ columns. Boolean indexing is powerful for conditional subsetting.

Method 4: Using query() Method for Column Filtering

The query() method allows for querying the columns of a DataFrame with a boolean expression.

Here’s an example:

import pandas as pd

# Sample DataFrame
data = pd.DataFrame({
    'Product': ['A', 'B', 'C', 'D'],
    'Sales_Q2': [250, 150, 200, 300],
    'Sales_Q3': [260, 160, 210, 310]
})
second_quarter_sales = data.query('Sales_Q2 > 200')[['Product', 'Sales_Q2']]

print(second_quarter_sales)

Output:

  Product  Sales_Q2
0      A       250
3      D       300

This snippet uses the query() function to filter rows where ‘Sales_Q2’ is greater than 200. Afterward, it selects the ‘Product’ and ‘Sales_Q2’ columns. It delivers similar functionality to boolean indexing but with a different syntax.

Bonus One-Liner Method 5: Using List Comprehensions with iloc()

List comprehensions in Python offer a concise way to create lists. When combined with iloc(), it can select subsets quickly.

Here’s an example:

import pandas as pd

# Sample DataFrame
data = pd.DataFrame({
    'Product': ['A', 'B', 'C', 'D'],
    'Sales_Q2': [250, 150, 200, 300],
    'Sales_Q3': [260, 160, 210, 310]
})
second_quarter_sales = data.iloc[[index for index in range(len(data)) if data.iloc[index, 1] > 200], [0, 1]]

print(second_quarter_sales)

Output:

  Product  Sales_Q2
0      A       250
3      D       300

A list comprehension is used here to create a list of row indices where ‘Sales_Q2’ is greater than 200. It’s then used to subset the DataFrame with iloc(). This method is a mix of Boolean logic and position-based indexing.

Summary/Discussion

  • Method 1: .loc[]. Great for label-based indexing. May not be suitable for large datasets due to potentially slower performance on integer-based indexing.
  • Method 2: .iloc[]. Best for position-based indexing. Requires knowing the exact position of columns, which can be error-prone if the DataFrame structure changes.
  • Method 3: Boolean Indexing with .loc[]. Ideal for conditional selection. Adds complexity if multiple conditions are involved.
  • Method 4: query(). Enables concise syntax for Boolean expressions. It can be less intuitive than standard Python Boolean indexing for those new to Pandas.
  • Bonus Method 5: List Comprehensions with iloc(). Offers a Pythonic and compact way to combine conditionals with indexing. However, it could be less readable for people unfamiliar with list comprehensions.