Home > Back-end >  Finding the price based on industry and number of transactions
Finding the price based on industry and number of transactions

Time:07-27

I am trying to determine the price for my input table which has the specific industry type and the average monthly transactions based on the reference table 1 which has the price for different industries (these represent the A tier prices) and the reference table 2 which classifies the price tier based on the average monthly transactions..Reference table 1 has the price for pricing tier A for all the industries .. The price for tier B is calculated as 90% of price of Tier A price; the price for tier C is calculated as 90% of price of Tier B price and so on

Input table

Industry Avg Monthly Transactions
Automotive 1129
Financial Services 7219
Retail 11795
Financial Services 10092
Retail 9445

Reference table 1

Industry price
Automotive 35
Financial Services 40
Retail 30

Reference table 2

Pricing Tier   Minimum Average Monthly Transactions Maximum Average Monthly Transactions
A 1 100
B 101 1000
C 1001 2500
D 2501 5000
E 5001 10000
F 10001

Output table

Industry Avg Monthly Transactions Price
Automotive 1129 28.35
Financial Services 7219 26.24
Retail 11795 17.71
Financial Services 10092 23.62
Retail 9445 19.68

Python Code Tried

    import pandas as pd
    
    df1=pd.read_csv("input.csv")
    df2=pd.read_csv("reference1.csv")
    df3=pd.read_csv("reference2.csv")

industry =df1[industry]
avgmonthlytransaction=df1[Avg Monthly Transactions]
price=df1.where(df1[avg Monthly Transactions]>=df3[min average] & <=df3[maximum average],pricingtier)
&& df1.where(df1[industry]=df2[Industry],df2[Price]

CodePudding user response:

df3['factor'] = [0.9**i for i in range(6)]
df3

enter image description here




Use cut() to create bins, find the corresponding Pricing Tier, and we can map its factor via df3.

tier = pd.cut(
    df1['Avg Monthly Transactions'], 
    bins=(df3.iloc[:,1].values.tolist()   [np.inf]),
    labels=df3['Pricing Tier'].values.tolist())

tier = tier.to_frame(name='Pricing Tier')

output = df1.merge(df2, on='Industry', how='left')
output['price'] = output['price']*tier.merge(df3[['Pricing Tier','factor']], on='Pricing Tier', how='left')['factor']

###
             Industry  Avg Monthly Transactions    price
0          Automotive                      1129  28.3500
1  Financial Services                      7219  26.2440
2              Retail                     11795  17.7147
3  Financial Services                     10092  23.6196
4              Retail                      9445  19.6830
  • Related