How can I efficiently and idiomatically filter rows of PandasDF based on multiple StringMethods on a single column?

user_6382901 0
Viewed 70

I have a Pandas DataFrame df with many columns, of which one is:

col
---
abc:kk__LL-z12-1234-5678-kk__z
def:kk_A_LL-z12-1234-5678-kk_ss_z
abc:kk_AAA_LL-z12-5678-5678-keek_st_z
abc:kk_AA_LL-xx-xxs-4rt-z12-2345-5678-ek__x
...

I am trying to fetch all records where col starts with abc: and has the first -num- between '1234' and '2345' (inclusive using a string search; the -num- parts are exactly 4 digits each).

In the case above, I'd return

col
---
abc:kk__LL-z12-1234-5678-kk__z
abc:kk_AA_LL-z12-2345-5678-ek__x
...

My current (working, I think) solution looks like:

df = df[df['col'].str.startswith('abc:')]
df = df[df['col'].str.extract('.*-(\d+)-(\d+)-.*')[0].ge('1234')]
df = df[df['col'].str.extract('.*-(\d+)-(\d+)-.*')[0].le('2345')]

What is a more idiomatic and efficient way to do this in Pandas?

pandas dataframe python
3 Answers

Complex string operations are not as efficient as numeric calculations. So the following approach might be more efficient:

m1 = df['col'].str.startswith('abc')
m2 = pd.to_numeric(df['col'].str.split('-').str[2]).between(1234, 2345)

dfn = df[m1&m2]

                                col
0    abc:kk__LL-z12-1234-5678-kk__z
3  abc:kk_AA_LL-z12-2345-5678-ek__x
user_90812670

One way would be to use regexp and apply function. I find it easier to play with regexp in a separate function than to crowd the pandas expression.

import pandas as pd
import re

def filter_rows(string):
    z = re.match(r"abc:.*-(\d+)-(\d+)-.*", string)

    if z:
        return 1234 <= (int(z.groups()[0])) <= 2345
    else:
        return False

Then use the defined function to select rows

df.loc[df['col'].apply(filter_rows)]
                                col
0    abc:kk__LL-z12-1234-5678-kk__z
3  abc:kk_AA_LL-z12-2345-5678-ek__x
user_41018830

Another play on regex : 

 #string starts with abc,greedy search, 
 #then look for either 1234, or 2345,   
#search on for 4 digit number and whatever else after

 pattern = r'(^abc.*(?<=1234-|2345-)\d{4}.*)'

 df.col.str.extract(pattern).dropna()

                          0
0   abc:kk__LL-z12-1234-5678-kk__z
3   abc:kk_AA_LL-z12-2345-5678-ek__x
user_71757130
Related