Matching data with multiple tags in seperate dataframe

user_9507468 0
Viewed 33

I have two tables, or rather, Pandas Dataframes, calls and tags that look like:

calls
id | tags
--------
01 | [tag1]
02 | [tag1, tag2]
03 | []

tags
id | tag_name
-------------
01 | tag1
02 | tag2

And I want a resulting DF like:

matching table
id | calls_id | tag_id
----------------------
01 | 01       | 01
02 | 02       | 01
02 | 02       | 02
03 |          |

So essentially I'm trying to match each call with its respective tags in a seperate DF

My best approach so far was:

def match_tags(x):
insert_df = pd.DataFrame(columns=['call_id', 'tag_id'])
for y in x['tags']:
    insert_df = insert_df.append({'call_id':x.id, 'tag_id': tags_df['id'].loc[y]}, ignore_index=True)
insert_df.head()
return insert_df

calls_df.apply(lambda x: pd.concat([tag_matching_df, match_tags(x)]), axis=1)

I'm not certain that a lambda function is the right solution here.

pandas python database
1 Answers

You can use a combination of explode and merge. Explode creates a single row for each item in your embedded lists.

import pandas as pd
calls = pd.DataFrame([
[1 , ["tag1"]],
[2 , ["tag1", "tag2"]],
[3 , []]
], columns=["callid","tag"])

tags = pd.DataFrame([
[1 , "tag1"],
[2 , "tag2"]
], columns=["tagid","tag"])

pd.merge(calls.explode("tag"),tags, on="tag").drop(columns=["tag"])

Giving

    callid  tagid
0   1       1
1   2       1
2   2       2
user_11789710
Related