Finding the price based on industry and number of transactions

[chandramouliarun]

1


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]