Find the price from a pricelist with scales-CodePudding

In python I am trying to lookup the relevant price depending on qty from a list of scale prices. For example when getting a quotation request:

  Product  Qty  Price
0       A    6
1       B  301
2       C    1
3       D  200
4       E   48

Price list with scale prices:

   Product  Scale Qty  Scale Price
0        A          1           48
1        A          5           43
2        A         50           38
3        B          1           10
4        B         10            9
5        B         50            7
6        B        100            5
7        B        150            2
8        C          1          300
9        C          2          250
10       C          3          200
11       D          1            5
12       D        100            3
13       D        200            1
14       E          1          100
15       E         10           10
16       E        100            1

Output that I would like:

  Product  Qty  Price
0       A    6     43
1       B  301      2
2       C    1    300
3       D  200      1
4       E   48     10

CodePudding user response：

Try with merge_asof:

output = (pd.merge_asof(df2.sort_values("Qty"),df1.sort_values("Scale Qty"),left_on="Qty",right_on="Scale Qty",by="Product")
          .sort_values("Product", ignore_index=True)
          .drop("Scale Qty", axis=1)
          .rename(columns={"Scale Price":"Price"}))

>>> output
  Product  Qty  Price
0       A    6     43
1       B  301      2
2       C    1    300
3       D  200      1
4       E   48     10

Inputs:

df1 = pd.DataFrame({'Product': ['A','A','A','B','B','B','B','B','C','C','C','D','D','D','E','E','E'],
                   'Scale Qty': [1, 5, 50, 1, 10, 50, 100, 150, 1, 2, 3, 1, 100, 200, 1, 10, 100],
                   'Scale Price': [48, 43, 38, 10, 9, 7, 5, 2, 300, 250, 200, 5, 3, 1, 100, 10, 1]})

df2 = pd.DataFrame({"Product": list("ABCDE"),
                    "Qty": [6,301,1,200,48]})

CodePudding user response：

Assuming df1 and df2, use merge_asof:

pd.merge_asof(df1.sort_values(by='Qty'),
              df2.sort_values(by='Scale Qty').rename(columns={'Scale Price': 'Price'}),
              by='Product', left_on='Qty', right_on='Scale Qty')

output:

  Product  Qty  Scale Qty  Price
0       C    1          1    300
1       A    6          5     43
2       E   48         10     10
3       D  200        200      1
4       B  301        150      2