Python Forum

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]

You've got a bunch of data on industries, their average monthly transactions, and reference tables with prices and transaction tiers. You want to determine the price for each industry based on its transactions and the industry type.To do this, you first merge your input table with the transaction tier reference table based on the average monthly transactions. Then, you merge this with the reference table containing prices for each industry. After that, you calculate the price for each industry based on its tier (A, B, C, etc.) and the given price tier discounts.Finally, you organize the data into a table with the industry name, average monthly transactions, and the calculated price. That's it! You've got your output table with the prices determined for each industry.
Link Removed