I have following pandas DataFrame

data = ['18#38#123#23=>21', '18#38#23#55=>35']
d = pd.DataFrame(data, columns = ['rule'])

and I have list of integers

r = [18, 55]

and I want to filter rules from above DataFrame if all integers in the list r are present in the rule too. I tried the following code and failed

d[d['rule'].str.replace('=>','#').split('#').astype(set).issuperset(set(r))]

How can I achieve the desired filtering with pandas

4 Answers

1
Marcus Lim On

My initial instinct would be to use a list comprehension:

df = pd.DataFrame(['18#38#123#23=>21', '188#38#123#23=>21', '#18#38#23#55=>35'], columns = ['rule'])

def wrap(n):
    return r'(?<=[^|^\d]){}(?=[^\d])'.format(n)

patterns = [18, 55]
pd.concat([df['rule'].str.contains(wrap(pattern)) for pattern in patterns], axis=1).all(axis=1)

Output:

0    False
1    False
2     True
1
RafaelC On

Using str.get_dummies

d.rule.str.replace('=>','#').str.get_dummies(sep='#').loc[:, map(str, r)].all(1)

Outputs

0    False
1     True
dtype: bool

Detail:

get_dummies+loc returns

    18  55
0   1   0
1   1   1
-1
Quang Hoang On

My approach is similar to @RafaelC's answer, but convert all string into int:

new_df = d.rule.str.replace('=>','#').str.get_dummies(sep='#')
new_df.columns = new_df.columns.astype(int)
has_all = new_df[r].all(1)

# then you can assign new column for initial data frame
d['new_col'] = 10
d.loc[has_all, 'new_col'] = 100

Output:

+-------+-------------------+------------+
|       |    rule           |   new_col  |
+-------+-------------------+------------+
|    0  | 18#38#123#23=>21  |      10    |
|    1  | 188#38#23#55=>35  |      10    |
|    2  | 18#38#23#55=>35   |     100    |
+-------+-------------------+------------+
2
hacker315 On

You were going in right direction, just need to use apply function instead:

d[d['rule'].str.replace('=>','#').str.split('#').apply(lambda x: set(x).issuperset(set(map(str,r))))]