We could use your key:value mapping with the map function:
The code below gets a dictionary of values of item_df name column that is in master_df col3 and col2 respectively.
col3 = dict(zip(*(value for _, value in
item_df[item_df.name.isin(master_df.col3)].items()))
)
col2 = dict(zip(*(value for _, value in
item_df[item_df.name.isin(master_df.col2)].items()))
)
col3
{55: 'p1', 59: 'p2', 50: 'p3'}
col2
{5: 'p4', 6: 'p5'}
Next is to use assign and create the columns first_title and second_title:
master_df.assign(
first_title=master_df.col3.map(col3),
second_title=master_df.col2.map(col2)
)
col1 col2 col3 first_title second_title
0 3 5 50 p3 p4
1 4 6 55 p1 p5
2 8 9 59 p2 NaN
3 10 10 60 NaN NaN
UPDATE
I thought about your comment for a single dictionary, and it seems achievable by using a Series. This will greatly reduce the bloated code I shared earlier.
In this case, we convert item_df to a series and map it to each relevant column:
item_df = item_df.set_index("name").loc[:, "title"]
item_df
name
55 p1
59 p2
50 p3
5 p4
6 p5
7 p6
Name: title, dtype: object
Now create your specific columns using the assign function:
master_df.assign(first_title=master_df.col3.map(item_df),
second_title=master_df.col2.map(item_df)
)
col1 col2 col3 first_title second_title
0 3 5 50 p3 p4
1 4 6 55 p1 p5
2 8 9 59 p2 NaN
3 10 10 60 NaN NaN
Much simpler and straight to the point.
So to make sure I understand, the idea is to create "lookup tables" from the item_df where the keys exist in the original frame. Then the idea is to use those dictionaries explicitly to lookup the matching values and map them to new columns in the final dataframe? Is it efficient to make all those lookup dictionaries prior to mapping? Or is there a way to make one super dictionary that works for all key lookups?
Your values are in different columns, so you have to make individual dictionaries. As for efficiency, you could run the code and compare with your present solution to see if it is better. One super dictionary for all key lookups. Not so sure. How would you go about it?
@Coldchain9, made an update to my answer, with a much simpler code.
Excellent! Just tried it out and I much prefer this over the multi-dictionary method. Thank you a ton for your help! :). Just to confirm that I understand. You use
set_index()to make thenamethe new index of the resultingpd.Series. So when.map()is used later, it maps the value fromcol3to the proper index value ofitem_dfand returns the single value that matches (since it is a single column series)? Is that how.map()works in this case?Yes. That is how map works. Had to read the documentation again :)