I have a dataframe and a dictionary in Python as shown below and I need to filter the dataframe based on the dictionary. As you see, the keys and values of the dictionary are two columns of the dataframe. I want to have a subset of dataframe which contains the keys and values of dictionary plus other columns.
df :
| Customer_ID | Category | Type | Delivery |
|---|---|---|---|
| 40275 | Book | Buy | True |
| 40275 | Software | Sell | False |
| 40275 | Video Game | Sell | False |
| 40275 | Cell Phone | Sell | False |
| 39900 | CD/DVD | Sell | True |
| 39900 | Book | Buy | True |
| 39900 | Software | Sell | True |
| 35886 | Cell Phone | Sell | False |
| 35886 | Video Game | Buy | False |
| 35886 | CD/DVD | Sell | False |
| 35886 | Software | Sell | False |
| 40350 | Software | Sell | True |
| 28129 | Software | Buy | False |
And dictionary is:
d = {
40275: ['Book','Software'],
39900: ['Book'],
35886: ['Software'],
40350: ['Software'],
28129: ['Software']
}
And I need the following dataframe:
| Customer_ID | Category | Type | Delivery |
|---|---|---|---|
| 40275 | Book | Buy | True |
| 40275 | Software | Sell | False |
| 39900 | Book | Buy | True |
| 35886 | Software | Sell | False |
| 40350 | Software | Sell | True |
| 28129 | Software | Buy | False |
>Solution :
Flatten the dictionary and create a new dataframe, then inner merge df with the new dataframe
df.merge(pd.DataFrame([{'Customer_ID': k, 'Category': i}
for k, v in d.items() for i in v]))
Customer_ID Category Type Delivery
0 40275 Book Buy True
1 40275 Software Sell False
2 39900 Book Buy True
3 35886 Software Sell False
4 40350 Software Sell True
5 28129 Software Buy False