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

Reference table 1

Reference table 2

Output table

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

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