Matching keywords of list elements with pandas columns

user_7319366 0
Viewed 336

This question is further part of this. So I added it as new question

If my dataframe B would be something like:

ID   category              words                   bucket_id
 1   audi                  a4, a6                  94
 2   bugatti               veyron, chiron          86
 3   mercedez              s-class, e-class        79
 4   dslr                  canon, nikon            69
 5   apple                 iphone,macbook,ipod     51
 6   finance               sales,loans,sales price 12
 7   politics              trump, election, votes  77
 8   entertainment         spiderman,thor, ironmen 88
 9   music                 beiber, rihana,drake    14
     ........              ..............
     .........             .........

I want mapped category along with its corresponding column ID as dictionary. Something like:-

{'id': 2, 'term': 'bugatti', 'bucket_id': 86}
{'id': 3, 'term': 'mercedez', 'bucket_id': 79}
{'id': 6, 'term': 'finance', 'bucket_id': 12}
{'id': 7, 'term': 'politics', 'bucket_id': 77}
{'id': 9, 'term': 'music', 'bucket_id': 14}

edit

I just want to map keywords with exact match in between two commas in column words not in between strings or along with any other words. Image

pandas dataframe python-3.x python
1 Answers

EDIT:

df = pd.DataFrame({'ID': [1, 2, 3], 
                   'category': ['bugatti', 'entertainment', 'mercedez'], 
                   'words': ['veyron,chiron', 'spiderman,thor,ironmen',
                             's-class,e-class,s-class'], 
                   'bucket_id': [94, 86, 79]}) 
print (df)
   ID       category                    words  bucket_id
0   1        bugatti            veyron,chiron         94
1   2  entertainment   spiderman,thor,ironmen         86
2   3       mercedez  s-class,e-class,s-class         79

A = ['veyron','s-class','derman']

idx = [i for i, x in enumerate(df['words']) for y in x.split(',') if y in A]
print (idx)
[0, 2, 2]

L = (df.loc[idx, ['ID','category','bucket_id']]
       .rename(columns={'category':'term'})
       .to_dict(orient='r'))
print (L)

[{'ID': 1, 'term': 'bugatti', 'bucket_id': 94}, 
 {'ID': 3, 'term': 'mercedez', 'bucket_id': 79}, 
 {'ID': 3, 'term': 'mercedez', 'bucket_id': 79}]
user_29010020
Related