python-Best way to perform multiple amount of Pandas lookups between two DataFrames

sammywemmy 2020-11-29 08:47:00

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.

Coldchain9 2020-11-28 01:08:24

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?

sammywemmy 2020-11-28 01:10:54

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?

sammywemmy 2020-11-29 00:47:22

@Coldchain9, made an update to my answer, with a much simpler code.

Coldchain9 2020-11-29 02:51:02

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 the name the new index of the resulting pd.Series. So when .map() is used later, it maps the value from col3 to the proper index value of item_df and returns the single value that matches (since it is a single column series)? Is that how .map() works in this case?

sammywemmy 2020-11-29 04:16:00

Yes. That is how map works. Had to read the documentation again :)

Best way to perform multiple amount of Pandas lookups between two DataFrames

热门github