Filtering products based on description scenarios and status in Python Pandas

user_8973609 0
Viewed 330

Let's say I have the following product descriptions in a Pandas DataFrame. I would like to keep all product descriptions of products that satisfy the following condition:

For every id in product_descriptions, check if it contains all descriptions from at least 1 scenario in scenario_descriptions. If so, check if the descriptions of all these scenarios have status 4, 5 or 6.

Input

scenario_descriptions = [
    ['world1', 'world2', 'world3'],  #scenario1
    ['world4', 'world2'],            #scenario2
    ['world5'],                      #scenario3
    ['world6', 'world7'],            #scenario4
    ['world6', 'world2']             #scenario5
]

product_descriptions = 

id | description | status
-------------------------
1  | world1      | 1
1  | world2      | 4
1  | world3      | 1
1  | world4      | 4
1  | world5      | 4
1  | world6      | 4
1  | world7      | 1
1  | world8      | 4
1  | world9      | 4
1  | world10     | 4
1  | world11     | 4
1  | world12     | 4
1  | world13     | 4
1  | world14     | 4
1  | world15     | 1
2  | world1      | 1
2  | world2      | 1
2  | world3      | 5
2  | world15     | 6
2  | world8      | 6
2  | world4      | 5
2  | world7      | 5

Product with id == 1 is not excluded because:

  • It has world2 andworld4` (scenario2) and their statuses are 4
  • It has world5 (scenario3) with status 4
  • It has world6 and world2 (scenario5) with status 4

Product with id == 2 is excluded because:

  • world1 (scenario1) doesn't have status 4, 5 or 6
  • world2 (scenario2) doesn't have status 4, 5 or 6
  • It doesn't have world5 (scenario3)
  • It doesn't have world6 (scenario4)
  • It doesn't have world6 (scenario5) and status of world2 is not 4, 5 or 6

Output

filtered_product_descriptions = 

id | description | status
-------------------------
1  | world1      | 1
1  | world2      | 4
1  | world3      | 1
1  | world4      | 4
1  | world5      | 4
1  | world6      | 4
1  | world7      | 1
1  | world8      | 4
1  | world9      | 4
1  | world10     | 4
1  | world11     | 4
1  | world12     | 4
1  | world13     | 4
1  | world14     | 4
1  | world15     | 1

Any idea how to solve this? I have been trying for days, but no success :( The code below is the closest I could get. This solves the first section of my problem. I don't know how to combine this with the second condition:

If so, check if the descriptions of all these scenarios have status 4, 5 or 6.

filtered_product_descriptions = (product_descriptions.groupby('id').filter(lambda x: (pd.Series([(pd.Series(y).isin(x['description']).all()) for y in scenario_descriptions])).any()))
pandas dataframe filter python
2 Answers

Use:

#create dictionary by scenaries
d = {'scenario{}'.format(k):v for k, v in enumerate(scenario_descriptions, 1)}

#unique id for reindex
uniq_id = df['id'].unique()

def f(x):
    #check if all description
    c = set(x['description']) >= set(v)
    #check if 4,5 or 6 value
    d = x['status'].isin([4,5,6]).all()
    return (c & d)

d1 = {}
for k, v in d.items():
     #filter df by scenary first for remove not relevant rows
     a = df[df['description'].isin(v)]
     #call groupby with custom function 
     b = a.groupby('id').apply(f)
     #add missing ids and fill by False
     #output to dictionary 
     d1[k] = b.reindex(uniq_id, fill_value=False)

print (d1)
{'scenario1': id
1    False
2    False
dtype: bool, 'scenario4': id
1    False
2    False
dtype: bool, 'scenario5': id
1     True
2    False
dtype: bool, 'scenario3': id
1     True
2    False
dtype: bool, 'scenario2': id
1     True
2    False
dtype: bool}

#reduce dict to DataFrame and check at least one True per row
m = pd.concat(d1, axis=1).any(axis=1)
print (m)
id
1     True
2    False

#last filtering
df = df[df['id'].isin(m.index[m])]
print (df)
    id description  status
0    1      world1       1
1    1      world2       4
2    1      world3       1
3    1      world4       4
4    1      world5       4
5    1      world6       4
6    1      world7       1
7    1      world8       4
8    1      world9       4
9    1     world10       4
10   1     world11       4
11   1     world12       4
12   1     world13       4
13   1     world14       4
14   1     world15       1
user_29010020

Use

In [260]: product_descriptions.groupby('id').filter(
     ...:   lambda x: all(any(w in x.description.values for w in L)
     ...:                 for L in scenario_descriptions))
Out[260]:
    id description  status
0    1      world1       1
1    1      world2       4
2    1      world3       1
3    1      world4       4
4    1      world5       4
5    1      world6       4
6    1      world7       1
7    1      world8       4
8    1      world9       4
9    1     world10       4
10   1     world11       4
11   1     world12       4
12   1     world13       4
13   1     world14       4
14   1     world15       1
user_21372550
Related