CODEWITHSUNDEEP
pythonpandas
Eduardo
Eduardo

Reputation: 171

Pandas: search multiple columns and return column with found value

I'm try to do some auditing for our purchase orders, and I created this dataframe (here's a csv sample of it):

ProductName,Qty,LineCost,BuyQty1,BuyQty1Cost,BuyQty2,BuyQty2Cost,BuyQty3,BuyQty3Cost

SIGN2WH,48,40.63,5,43.64,48,40.63,72,39.11
SIGN2BK,144,39.11,5,43.64,48,40.63,72,39.11

In my data source, some products get different breaks, depending on the quantity purchased. Hence the columns BuyQty1 and BuyQty1Cost. Qty and LineCost are the values I need to audit. So, what I'm trying to do is:

  1. Check what quantity break corresponds to the value on the column Qty. Example a Qty of 48 implies that the break is BuyQty2, and the corresponding price should be BuyQty2Cost.

  2. Then add a column with the ratio of LineCost/BuyQty2Cost. It would be BuyQty3Cost in the case of SIGN2BK (2nd line).

How should I tackle this?

Upvotes: 0

Views: 372

Answers (1)

Jianxun Li
Jianxun Li

Reputation: 24742

import pandas as pd

def calculate_break_level(row):
    if row.Qty >= row.BuyQty3:
        return row.BuyQty3Cost
    elif row.Qty >= row.BuyQty2:
        return row.BuyQty2Cost
    else:
        return row.BuyQty1Cost

# apply the function row-by-row by specifying axis=1
# the newly produced Line_Cost is in the last column.
df['Line_Cost'] = df.apply(calculate_break_level, axis=1)

Out[58]: 
  ProductName  Qty  LineCost  BuyQty1  BuyQty1Cost  BuyQty2  BuyQty2Cost  BuyQty3  BuyQty3Cost  Line_Cost
0     SIGN2WH   48     40.63        5        43.64       48        40.63       72        39.11      40.63
1     SIGN2BK  144     39.11        5        43.64       48        40.63       72        39.11      39.11

Upvotes: 1

Related Questions