Look up the matching element from another data frame and return its id- python

user_15511421 0
Viewed 76

i have a orders dataframe:

items chat_id
curd,vada,rice 74374374h4473
idly,sambar 7949759459h34

I have another unique menu items data frame with its own particular id:

id items
1 idly
2 vada
3 rice
4 curd
5 sambar

now I want to match the elements and return its id's by printing its chat_id in the orders data frame

chat_id id
74374374h4473 4
74374374h4473 2
74374374h4473 3
7949759459h34 1
7949759459h34 5
data-science pandas dataframe python
1 Answers

Id you have dataframe df_orders:

            items        chat_id
0  curd,vada,rice  74374374h4473
1     idly,sambar  7949759459h34

and dataframe df_menu:

   id   items
0   1    idly
1   2    vada
2   3    rice
3   4    curd
4   5  sambar

Then:

df_orders["items"] = df_orders["items"].str.split(",")
df_orders = df_orders.explode("items")
print(df_orders.merge(df_menu, on="items")[["chat_id", "id"]])

Prints:

         chat_id  id
0  74374374h4473   4
1  74374374h4473   2
2  74374374h4473   3
3  7949759459h34   1
4  7949759459h34   5
user_100359850
Related