Is there a way to replace value of all cells that have "a" and "b" and "c" in any order present in a string?

Question

I have a pandas dataframe where a column include string as any possible combination of "a", "b" and "c". As an example a cell could be just "a" or "a,b,c" or "b,a,c" and so on. I would like to be able to check if a cell contains "a" and "b" and "c" in any order and then either replace it with a number or add number to a new column next to it. I know it is possible to do this by using str.contains like below but would like to avoid having to write down all combinations.

df["cat"]=df["cat"].str.contains('a,b,c|a,c,b|c,b,a|b,a,c|c,a,b|b,c,a').astype(int) 

anyone any ideas on how I could do this?

Answer 1

You can use itertools.permutations to create your regex pattern.

import itertools

cats = ['a', 'b', 'c']
pat = '|'.join([','.join(x) for x in itertools.permutations(cats)])

# print(pat)
# 'a,b,c|a,c,b|b,a,c|b,c,a|c,a,b|c,b,a'

df["cat"] = df["cat"].str.contains(pat).astype(int)

Example

df = pd.DataFrame({'cat': {0: 'a,b,c',1: 'a,c,b',2: 'c,b,a',3: 'b,a,c',4: 'c,a,b',5: 'b,c,a',6: 'd,e,f',7: 'a,a,a',8: 'a',9:'b',10: 'c'}})

print(df)

      cat
0   a,b,c
1   a,c,b
2   c,b,a
3   b,a,c
4   c,a,b
5   b,c,a
6   d,e,f
7   a,a,a
8       a
9       b
10      c

cats = ['a', 'b', 'c']
pat = '|'.join([','.join(x) for x in itertools.permutations(cats)])

df["match"] = df["cat"].str.contains(pat).astype(int)

print(df)

      cat  match
0   a,b,c      1
1   a,c,b      1
2   c,b,a      1
3   b,a,c      1
4   c,a,b      1
5   b,c,a      1
6   d,e,f      0
7   a,a,a      0
8       a      0
9       b      0
10      c      0

Answer 2

Edit: Add regex negative lookahead option

Option 1: using negative lookahead

In [887]: df
Out[887]:
       cat
0    a,b,c
1    a,c,b
2    c,b,a
3    b,a,c
4    c,a,b
5    b,c,a
6    a,a,a
7    b,b,b
8    c,c,c
9      a,b
10    ab,a
11       b
12    ab,c
13   a,b,a
14   a,b,b
15   c,b,a
16   a,a,b
17    a,bc
18  a, b,c
19  a,bb,c    

In [888]: ignore_st = r'(?:([abc]),(?!.*\1)){2}[abc]'
In [896]: df['ignore'] = df.cat.str.contains(ignore_st).astype(int)

In [897]: df
Out[897]:
       cat  ignore
0    a,b,c       1
1    a,c,b       1
2    c,b,a       1
3    b,a,c       1
4    c,a,b       1
5    b,c,a       1
6    a,a,a       0
7    b,b,b       0
8    c,c,c       0
9      a,b       0
10    ab,a       0
11       b       0
12    ab,c       0
13   a,b,a       0
14   a,b,b       0
15   c,b,a       1
16   a,a,b       0
17    a,bc       0
18  a, b,c       0
19  a,bb,c       0

Option 2: This only works if each cell contains exact combination of 'a, b, c'

Instead of using str.contains regex, you may compare each cell to array ['a', 'b', 'c']

In [800]: df
Out[800]:
      cat
0   a,b,c
1   a,c,b
2   c,b,a
3   b,a,c
4   c,a,b
5   b,c,a
6   a,a,a
7   b,b,b
8   c,c,c
9     a,b
10    a,c
11      b
12      c    

On each cell, split on ',' to list, sort and compare each to ['a', 'b', 'c']

In [810]: df['ignore'] = df.cat.str.split(',').map(sorted).apply(lambda x: x == ['a', 'b', 'c']).astype(int)

In [811]: df
Out[811]:
      cat  ignore
0   a,b,c       1
1   a,c,b       1
2   c,b,a       1
3   b,a,c       1
4   c,a,b       1
5   b,c,a       1
6   a,a,a       0
7   b,b,b       0
8   c,c,c       0
9     a,b       0
10    a,c       0
11      b       0
12      c       0