Pandas: search multiple columns and return column with found value

Question

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:

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.
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?

Jianxun Li · Accepted Answer · 2015-06-30 15:36:44Z

1

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

edited Jun 30, 2015 at 15:36

answered Jun 29, 2015 at 18:55

Jianxun Li

24.9k10 gold badges64 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Eduardo Over a year ago

Thanks, Lee. I had to study about cut because I was not familiar with it. This approach won't work because in our database system if an item has only one price break then the 3 columns will have the same values. Sorry for not clarifying that.

Jianxun Li Over a year ago

@Eduardo No problem. Is it possible to upload a sample of your data file? say about 100 rows? It would be great if I can play with your real data and see whether I can come up with a solution.

Eduardo Over a year ago

I was going to add it, Jianxun. But got timed out. I only have 4 lines that shows exactly the different scenarios. Here is a link for a sample data. I had to modify the order of columns to make it easy, too. sample Data

Eduardo Over a year ago

My essential difficulty is how to compare or search values across columns in the same row.

Jianxun Li Over a year ago

@Eduardo I've modified the code. Let me know whether this works.

|

Collectives™ on Stack Overflow

Pandas: search multiple columns and return column with found value

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related