How to search column elements and corresponding mappings in Python Pandas?

Question

I have a dataframe df1 such as the following that has a list of tags.

                       tags
            0            label
            0         document
            0             text
            0            paper
            0           poster
                     ...    
            21600         wood
            21600      hot tub
            21600          tub
            21600      terrace
            21600      blossom

There's another dataframe df2 that has mappings to the tags present in df mapped to a column name 'name'.

                name        iab
        0   abies       Nature and Wildlife 
        1   absinthe    Food & Drink    
        2   abyssinian  Pets    
        3   accessories Style & Fashion 
        4   accessory   Style & Fashion 
          ...   ... ... ... ...

        1595 rows × 4 columns

Essentially, the idea is to search the column 'name' in df2 that correspond to the tags in df1 to find corresponding 'iab' mappings and output a CSV that has two columns - tags and it's corresponding 'iab' mappings.

The Output would look something like this :

                        tags      iab
            0            label    <corresponding iab mapping 
                                   to 'name' found in df2>
            0         document
            0             text
            0            paper
            0           poster
                     ...    
            21600         wood
            21600      hot tub
            21600          tub
            21600      terrace
            21600      blossom

I need help in achieving this. Thank you in advance!

Note:

What I tried is

    df_iab[df_iab['name'].isin(df['image_CONTAINS_OBJECT'])]

But that would only cut down df2 to 'iab' that match 'tags' but not really perform a search and map found values.

score 1 · Answer 1 · answered Aug 23 '20 at 21:40

1

Merge:

new_df = df1.merge(df2, how='left', left_on='tags', right_on='name')

answered Aug 23 '20 at 21:40

AtanuCSE

8,832
14
74
112

score 0 · Accepted Answer · answered Aug 23 '20 at 21:49

0

Another way is to use .map to transfer iab details from df2 to df1.

df1['iab']=df1.tags.map(dict(zip(df2.name,df2.iab)))

How it works

#come up with a dictionary of name and `iab` in `df2`.

d=dict(zip(df2.name,df2.iab))

# Map the dict to df1 using the tag column

df1.tags.map(d)

answered Aug 23 '20 at 21:49

wwnde

26,119
6
18
32

Thank you!! It worked out great! – Neha Madhavan Aug 23 '20 at 22:16

How to search column elements and corresponding mappings in Python Pandas?

2 Answers2