[Python] Serially assign a product against each milestone

I have a table with columns like this



I have another file which has the hierarchy of products

Products
INKAKGZ001
INKAKGZ002
INKAKGZ003 till 68


I want to write pandas code which will sort the table on milestone and assign the product hierarchy correctly serially, by splitting the quantity and create line items for each product and calculate the amt for each product. It should give output like



Adding the code I wrote below. It is not working as expected

import pandas as pd

# Table 1: Quantities and Milestone
data1 = {"Quantity": [60, 45, 1, 1],
"Milestone": ["M1", "M2", "M2", "M2"],
"Amt": [4000, 4500, 100, 100],
"D": [1, 2, 3, 4]}

df1 = pd.DataFrame(data1)

# Table 2: Products
df2 = pd.read_excel(r"C:\Users\sohamapal\OneDrive - KPMG\desktop\test for py\M-M.xlsx", usecols="A")
df2 = df2.fillna("").reset_index(drop=True)

products = list(df2["Products"]) # Extract products
df1 = df1.sort_values(by=["Milestone", "Quantity"]).reset_index(drop=True)

# Convert Table 1 by splitting each Quantity across the products
output = []
product_index = 0

print(len(df2))
for _, row in df1.iterrows():
quantity = row["Quantity"]
milestone = row["Milestone"]
amt = row["Amt"] / quantity
d = row["D"]

# Get the corresponding products for the quantity
for _ in range(quantity):
if product_index >= len(df2): # Check if there are enough products
raise IndexError("Not enough products")

print(product_index)
if product_index < len(df2):
print(products[product_index])
output.append({
"Product": products[product_index],
"Milestone": milestone,
"Amt": amt,
"Date": d,
})
product_index += 1
else:
output.append({
"Product": "N/A",
"Milestone": milestone,
"Amt": amt,
"Date": d,
})
product_index = 0

output_df = pd.DataFrame(output)
output_df.to_excel("test.xlsx", index=False)

print(output_df)

Continue reading...