How to Filter Text Columns in Vaex DataFrames

This quick tutorial shows how to filter text columns in Vaex DataFrames using string methods. For a complete introduction to working with large datasets, see our Working with Large Datasets in Python Using Vaex guide.

The Problem

Most real datasets contain text columns like names, categories, or descriptions. While filtering numeric columns is straightforward, text columns require different operations like checking if a string contains a substring, starts with a prefix, or matches a pattern. These operations need to work efficiently on millions of rows without loading all the data into memory at once.

The Solution

Vaex provides string methods through the .str accessor that work efficiently on large text columns. First, let’s add a text column to work with:

 
import vaex

# Load dataset and create a text column
df = vaex.datasets.iris_1e6()

# Map numeric class to species names
df['species'] = df['class_'].map({0: 'setosa', 1: 'versicolor', 2: 'virginica'})

print(f"Dataset shape: {df.shape}")
print(f"Columns: {list(df.columns)}")
print("\nFirst few rows with species names:")
print(df[['sepal_length', 'class_', 'species']].head(5))

This creates a virtual text column from the numeric class values:

 
Dataset shape: (1005000, 6)
Columns: ['sepal_length', 'sepal_width', 'petal_length', 'petal_width', 'class_', 'species']

First few rows with species names:
  #    sepal_length    class_  species
  0             5.9         1  versicolor
  1             6.1         1  versicolor
  2             6.6         1  versicolor
  3             6.7         2  virginica
  4             5.5         0  setosa

Now we can filter using text operations:

 
# Filter for species containing 'color'
has_color = df[df.species.str.contains('color')]
print(f"Species containing 'color': {len(has_color):,} rows")

# Filter for species starting with 'v'
starts_v = df[df.species.str.startswith('v')]
print(f"Species starting with 'v': {len(starts_v):,} rows")

# Combine text filter with numeric filter
large_versicolor = df[(df.species == 'versicolor') & (df.sepal_length > 6.5)]
print(f"Large versicolor flowers: {len(large_versicolor):,} rows")

The string methods work across all million rows instantly:

 
Species containing 'color': 335,000 rows
Species starting with 'v': 670,000 rows
Large versicolor flowers: 53,600 rows

These filters create views without copying data. The .str.contains() method searches for substrings, while .str.startswith() checks prefixes. You can combine text filters with numeric filters using standard operators like & for AND and | for OR.

Case Sensitivity and Performance

Text filters are case-sensitive by default. To perform case-insensitive filtering, convert the column to lowercase first using .str.lower() before comparing. For example, df[df.species.str.lower() == ‘setosa’] matches regardless of case. String operations in Vaex are lazy like other operations, so they’re computed only when needed. For repeated text filtering on the same column, consider creating a lowercase version as a virtual column once, then filter against that column multiple times for better performance.

Conclusion

Filtering text columns in Vaex uses familiar string methods through the .str accessor. These operations work efficiently on large datasets and can be combined with numeric filters for complex queries.

Leave a Reply

Your email address will not be published. Required fields are marked *