Data Manipulation with pandas in Python

In this article, we will explore how to extract specific values from a column in a pandas DataFrame using the pandas library. We’ll use the Series.str.extract and Series.str.findall functions to achieve our goal.

Introduction

pandas is a powerful data manipulation library for Python that provides efficient data structures and operations for working with structured data, including tabular data such as spreadsheets and SQL tables.

In this article, we will focus on extracting specific values from a column in a pandas DataFrame. We’ll use the Series.str.extract and Series.str.findall functions to achieve our goal.

Background

The pandas library provides several ways to manipulate and extract data from DataFrames, including string extraction. In this section, we’ll cover the basics of how these functions work and when to use them.

Installing Pandas

Before you start using pandas, make sure it’s installed in your Python environment. You can install it using pip:

pip install pandas

Creating a DataFrame

To demonstrate the string extraction techniques, let’s first create a sample DataFrame.

import pandas as pd

data = {
    'Linked Issues': [
        'Requirement-12345, NewPr-8795, OldPr-78941',
        'MSR-85749, Requirement-74852, NewPr-95418',
        'Requirement-894895',
        'OldPr-85974, NewPr-968572, Requirement-985785'
    ]
}

df = pd.DataFrame(data)
print(df)

Output:

     Linked Issues
0  Requirement-12345, NewPr-...
1    MSR-85749, Requirement-7...
2  Requirement-894895
3  OldPr-85974, NewPr-96857...

Extracting Values from a Column

Now that we have our sample DataFrame, let’s extract the values from the Linked Issues column using the Series.str.extract function.

The Series.str.extract function is used to extract substrings from each element in the series. We’ll use a regular expression pattern to match the desired value.

import re

pattern = r'(Requirement-\d+)'

df['new'] = df['Linked Issues'].str.extract(pattern)
print(df)

Output:

     Linked Issues           new
0  Requirement-12345, NewPr-...   Requirement-12345
1    MSR-85749, Requirement-7...   Requirement-74852
2  Requirement-894895          Requirement-894895
3  OldPr-85974, NewPr-96857...   Requirement-985785

As you can see, the Series.str.extract function extracted the values from the Linked Issues column using the regular expression pattern.

Why Use str.extract Instead of Regular Expressions?

While it’s possible to use regular expressions directly with pandas, there are several reasons why we might prefer to use the str.extract function:

• Ease of Use: The str.extract function provides a more user-friendly interface for extracting substrings than regular expressions.
• Performance: The str.extract function is optimized for performance and can extract substrings much faster than regular expressions.

Handling Multiple Values per Row

In some cases, you might need to handle multiple values per row. In this section, we’ll explore how to do that using the Series.str.findall function.

The Series.str.findall function returns all non-overlapping matches of a pattern in the series as a list of strings.

import re

pattern = r'(Requirement-\d+)'

df['new'] = df['Linked Issues'].str.findall(pattern)
print(df)

Output:

     Linked Issues    new
0  Requirement-12345, NewPr... [Requirement-12345]
1    MSR-85749, Requirement-7... [Requirement-74852]
2  Requirement-894895          [Requirement-894895]
3  OldPr-85974, NewPr-96857... [Requirement-985785]

As you can see, the Series.str.findall function returned a list of strings containing all non-overlapping matches of the pattern.

Handling Multiple Values with Comma Separation

If your data has comma-separated values in the same column, you’ll need to handle them separately. In this section, we’ll explore how to do that using the Series.str.extract function.

import re

pattern = r'(Requirement-\d+)'

df['new'] = df['Linked Issues'].str.extract(pattern)
print(df)

Output:

     Linked Issues           new
0  Requirement-12345, NewPr...   Requirement-12345
1    MSR-85749, Requirement-7...   Requirement-74852
2  Requirement-894895          Requirement-894895
3  OldPr-85974, NewPr-96857...   Requirement-985785

As you can see, the Series.str.extract function extracted only one value from each row.

Using str.join to Handle Multiple Values

If you need to handle multiple values per row and store them as a single string separated by commas, you can use the str.join function.

import pandas as pd

data = {
    'Linked Issues': [
        'Requirement-12345, NewPr-8795, OldPr-78941',
        'MSR-85749, Requirement-74852, NewPr-95418',
        'Requirement-894895',
        'OldPr-85974, NewPr-968572, Requirement-985785'
    ]
}

df = pd.DataFrame(data)

pattern = r'(Requirement-\d+)'

df['new'] = df['Linked Issues'].str.extract(pattern)
print(df)

Output:

     Linked Issues           new
0  Requirement-12345, NewPr...   Requirement-12345
1    MSR-85749, Requirement-7...   Requirement-74852
2  Requirement-894895          Requirement-894895
3  OldPr-85974, NewPr-96857...   Requirement-985785

As you can see, the Series.str.extract function extracted only one value from each row.

Why Use str.join Instead of Regular Expressions?

While it’s possible to use regular expressions directly with pandas, there are several reasons why we might prefer to use the str.join function:

• Ease of Use: The str.join function provides a more user-friendly interface for joining strings than regular expressions.
• Performance: The str.join function is optimized for performance and can join strings much faster than regular expressions.

Conclusion

In this chapter, we explored how to extract values from the Linked Issues column using pandas. We covered several techniques including:

• Using the Series.str.extract function with regular expressions
• Handling multiple values per row using the Series.str.findall function
• Handling comma-separated values in the same column using the str.join function

We also discussed why we might prefer to use pandas functions over regular expressions for data manipulation tasks.

Further Reading

Last modified on 2024-11-14