pandas join dataframes based on the TOP 1 result of an ordered list of matching records

user_1789006 0
Viewed 95

I have a dataframe with all available options for sale:

options = pd.DataFrame({'Name':['a', 'b', 'c', 'd'],
                  'Price':[1,3,6,4], 
                  'Size':[12,32,44,68], 
                  'Content':[10,8,22,20]}

And a list of needs:

criteria = pd.DataFrame({'Customer':['a1', 'b2', 'c3', 'd4'],
                  'MinSize':[8,20,12,40], 
                  'MinContent':[18,6,21,4]}

I need to get the lowest price from the options that meets the minimum criteria, resulting in:

pd.DataFrame({'Customer':['a1', 'b2', 'c3', 'd4'],
                  'MinSize':[8,20,12,40], 
                  'MinContent':[18,6,21,4],
                  'MatchingOption':['d','b','c','d']}

This would be the equivalent of a TOP 1 WHERE Content >= MinContent AND Size >= MinSize ORDER BY Price ASC in SQL.

How do I join dataframes based on the TOP 1 result of an ordered list of matching records?

pandas dataframe python
3 Answers

for each row in criteria, filter out the options for Size>=MinSize and Content>=MinContent, then just sort Price column and take the first value for Name, assign the resulting series back to the new column for criteria dataframe:

result=(criteria.assign(MatchingOption=criteria
                 .apply(lambda x: 
                        options[(options['Size'].ge(x['MinSize']))
                        & (options['Content'].ge(x['MinContent']))]
                        .sort_values('Price').iloc[0]['Name'],
                        axis=1))
 )

OUTPUT:

  Customer  MinSize  MinContent MatchingOption
0       a1        8          18              d
1       b2       20           6              b
2       c3       12          21              c
3       d4       40           4              d
user_91363480

Other option: you can use .idxmin():

criteria["MatchingOption"] = criteria.apply(
    lambda x: options.loc[
        options.loc[
            (options["Size"] >= x["MinSize"])
            & (options["Content"] >= x["MinContent"]),
            "Price",
        ].idxmin(),
        "Name",
    ],
    axis=1,
)

Prints:

  Customer  MinSize  MinContent MatchingOption
0       a1        8          18              d
1       b2       20           6              b
2       c3       12          21              c
3       d4       40           4              d
user_100359850

If your dataset is large and require fast execution time, here is a version with all vectorized operations for better system performance:

Use .merge() (cross merge) + .query() + .loc + .groupby() + idxmin():

# cross merge and filter with .query()
legitimate_matches =  criteria.merge(options, how='cross').query('(Size >= MinSize) & (Content >= MinContent)')

# get lowest price entries by .groupby() + .idxmin()
best_matches = legitimate_matches.loc[legitimate_matches.groupby('Customer')['Price'].idxmin()]

If your Pandas version is older than 1.2.0 (December 2020 version) and does not support merge with how='cross', you can use:

# cross merge and filter with .query()
legitimate_matches = criteria.assign(key=1).merge(options.assign(key=1), on='key').query('(Size >= MinSize) & (Content >= MinContent)') 

# get lowest price entries by .groupby() + .idxmin()
best_matches = legitimate_matches.loc[legitimate_matches.groupby('Customer')['Price'].idxmin()]

Result:

print(best_matches)

   Customer  MinSize  MinContent Name  Price  Size  Content
3        a1        8          18    d      4    68       20
5        b2       20           6    b      3    32        8
10       c3       12          21    c      6    44       22
15       d4       40           4    d      4    68       20

You can then remove unnecessary columns and rename column Name to MatchingOption.

final_result = best_matches[['Customer', 'MinSize', 'MinContent', 'Name']].rename({'Name': 'MatchingOption'}, axis=1)

Final result:

print(final_result)

   Customer  MinSize  MinContent MatchingOption
3        a1        8          18              d
5        b2       20           6              b
10       c3       12          21              c
15       d4       40           4              d
user_150706970
Related