CODEWITHSUNDEEP
pythonpandasnumpycsv
miwa_p
miwa_p

Reputation: 435

creating a column in one table based on multiple columns from another table [python]

I am creating a csv table where I have informations about all of my Orders. Now I want to sell those items away but I want to add the extra surcharge depending on the price of the Item. I created a new table with the surcharge , where I have columns called 'from' and 'to' from where I have to compare the item price and then include the right surcharge in the sale Price.

But I am not able to do this. I tried different approaches but non of them seem to work. Any help would be nice :)

My table looks like this:

    OrderNo      NetPerPiece costsDividedPerOrder  HandlingPerPiece

0  7027514279        44.24     0.008007          0.354232

1  7027514279        15.93     0.008007          0.127552

2  7027514279        15.93     0.008007          0.127552

3  7027514279        15.93     0.008007          0.127552

4  7027514279        15.93     0.008007          0.127552

surcharges = {'surcharge': [0.35, 0.25, 0.2, 0.15, 0.12, 0.1],
'from': [0, 20, 200, 500, 1500, 5000], 
'to' : [20, 200, 500, 1500, 5000,1000000000] }
surchargeTable = DataFrame(surcharges, columns=['surcharge', 'from', 'to'])


productsPerOrder['NetPerpieceSale'] = numpy.where(((productsPerOrder['NetPerPiece'] >= surchargeTable['from']) & (productsPerOrder['NetPerPiece'] < surchargeTable['to'])), surchargeTable['surcharge'])


#I also tried this:

for index, row in productsPerOrder.iterrows():
        if row['NetPerPiece'] >= surchargeTable['from'] & row['NetPerPiece'] < surchargeTable['to']:
                productsPerOrder.loc[index,'NerPerPieceSale'] = surchargeTable.loc[row,'NetPerPieceSale'].values(0)

I want it to look like this:

 OrderNo   NetPerPiece costsDividedPerOrder  HandlingPerPiece NetPerPieceSale

0  7027514279   44.24           0.008007          0.354232    0.25

1  7027514279   15.93           0.008007          0.127552    0.35

2  7027514279   15.93           0.008007          0.127552    0.35

3  7027514279   15.93           0.008007          0.127552    0.35

4  7027514279   15.93           0.008007          0.127552    0.35

Just to remind, the file with items is much bigger, I only showed the head of the csv list. So the tables are of the different lengths

SurchargeTable looks like this:

 surcharge  from          to
0       0.35     0          20
1       0.25    20         200
2       0.20   200         500
3       0.15   500        1500
4       0.12  1500        5000
5       0.10  5000  1000000000

Upvotes: 1

Views: 1007

Answers (3)

Anna Nevison
Anna Nevison

Reputation: 2759

Create a function to calculate the surcharge, then use .apply to apply it to the 'NetPerPiece' row.

import pandas as pd
df = pd.read_csv('something.csv')   

def get_surcharges(x):
    to = [0, 20, 200, 500, 1500, 5000] 
    fr = [20, 200, 500, 1500, 5000,1000000000]
    surcharges = [0.35, 0.25, 0.2, 0.15, 0.12, 0.1]
    rr = list(zip(to, fr, surcharges))
    price = [r[2] for r in rr if x > r[0] and x <r[1]]
    return price[0]

df['NetPerpieceSale'] = df['NetPerPiece'].apply(lambda x: get_surcharges(x))

print(df)

This outputs:

      OrderNo  NetPerPiece  costsDividedPerOrder  HandlingPerPiece  NetPerpieceSale
0  7027514279        44.24              0.008007          0.354232             0.25
1  7027514279        15.93              0.008007          0.127552             0.35
2  7027514279        15.93              0.008007          0.127552             0.35
3  7027514279        15.93              0.008007          0.127552             0.35
4  7027514279        15.93              0.008007          0.127552             0.35

Option without the for loop (kind of verbose):

def get_surcharges(x):
    if x > 0:
        if x > 20:
            if x > 200:
                if x > 500:
                    if x > 1500:
                        if x > 5000:
                            return 0.1
                        else:
                            return 0.12
                    else:
                        return 0.15
                else:
                    return 0.2
            else:
                return 0.25
        else:
            return 0.35

Upvotes: 1

Scott Boston
Scott Boston

Reputation: 153500

Another way to do this is to use pd.IntervalIndex and map:

# Create IntervalIndex on surchageTable dataframe
surchargeTable = surchargeTable.set_index(pd.IntervalIndex.from_arrays(surchargeTable['from'],
                                                                       surchargeTable['to']))

#Use map to pd.Series created from surchargeTable IntervalIndex and surcharge column.
productsPerOrder['NetPerPieceSale'] = productsPerOrder['NetPerPiece'].map(surchargeTable['surcharge'])

productsPerOrder

Output:

      OrderNo  NetPerPiece  costsDividedPerOrder  HandlingPerPiece  NetPerPieceSale
0  7027514279        44.24              0.008007          0.354232             0.25
1  7027514279        15.93              0.008007          0.127552             0.35
2  7027514279        15.93              0.008007          0.127552             0.35
3  7027514279        15.93              0.008007          0.127552             0.35
4  7027514279        15.93              0.008007          0.127552             0.35

Upvotes: 2

psn1997
psn1997

Reputation: 144

Simply add a column to existing dataframe with the above calculations of NetPerPieceScale
or you can save the calculations to a dataframe like this:
net=pd.DataFrame(NetPerPieceScale, columns=['NetPerPieceScale '])

and simply concat this to existing Dataframe you will have everything in 1 table

Upvotes: 0

Related Questions