Add new column with a list of the matching values between two dataframes

user_5817754 0
Viewed 106

I have 2 dataframes with the following structure:

DF1

ItemID     Item
Id1        Item1
Id2        Item2
Id3        Item3
...        ...
1000       Item1000

DF2

Index     ListOfItems
0         [Item1]
1         [Item1, Item3, Item5]
2         [Item2, Item3]
...       ...
N         [NItems]

This will be my expected output:

Index     ListOfItems               ListOfIds
0         [Item1]                   [Id1]
1         [Item1, Item3, Item5]     [Id1, Id3, Id5]
2         [Item2, Item3]            [Id2, Id3]
...       ...                       ...
N         [NItems]                  [NIds]

which is matching the ListOfItmes of the second Dataframe with the Ids of the first Dataframe and create the list of Ids in a new column

This is done over a large dataframe that is constantly changing, so performance is important. I have tried some approaches but the performance is pretty poor.

pandas dataframe list-comprehension python loops
2 Answers

Try:

df2['ListOfIds'] = df2['ListOfItems'].apply(lambda x: df1[df1['Item'].isin(x)].index.to_list())

SAMPLE DATA:

>>> df1
         Item
ItemID       
Id1     Item1
Id2     Item2
Id3     Item3

>>> df2
                 ListOfItems
Index                       
0                    [Item1]
1      [Item1, Item3, Item5]
2             [Item2, Item3]

OUTPUT:

                 ListOfItems   ListOfIds
Index                                   
0                    [Item1]       [Id1]
1      [Item1, Item3, Item5]  [Id1, Id3]
2             [Item2, Item3]  [Id2, Id3]

The solution above expects you to have the values in ListOfItems columns to be lists instead of string, if it is not list, you can do the following to convert it to list from string:

df2['ListOfItems'] = df2['ListOfItems'].str[1:-1].str.split(',').apply(lambda x: [i.strip() for  i in x])
user_91363480

df2=df2.replace(regex={'\[':'','\]':''})#Strip corner brackets

#in df2 make ListOfItems a list and explode, Create new column called

ListOfIds and map over the ids
df2=df2.assign(ListOfItems=df2['ListOfItems'].str.split(',')).explode('ListOfItems').assign(ListOfIds=df2['ListOfItems'].map(dict(zip(df1['Item'], df1['ItemID']))))

#Groupby Index and age list

df2.groupby('Index').agg(list)
user_89869750
Related