Lab 6 - Part 2

import pandas as pd
import calendar

xlsx = pd.ExcelFile("merge.xlsx")
skipRows = [1, 2]
sheets = []
for i, s in enumerate(xlsx.sheet_names):
    sheets += [xlsx.parse(s, skiprows = skipRows[i])]

def dateToMonthYear(date):
    return calendar.month_name[int(date[5:7])] + " " + date[0:4]

for s in sheets:
    s['item name'] = s['item name'].apply(str.strip)
    s['date/time'] = s['date/time'].apply(dateToMonthYear)

#trans stands for transactions
trans = pd.concat([s for i, s in enumerate(sheets)], ignore_index = True, sort = False)
trans['purchase'] = trans["cost per unit"] * trans["units purchased"]
trans['sale'] = trans["price per unit"] * trans["units sold"]
trans = trans.drop(["cost per unit", "price per unit"], axis=1)

#prof stands for cumulative profits
prof = pd.DataFrame(columns=['date', 'item name', "remainder", 'profit'])
monthBefore = ""
for month in trans["date/time"].unique():
    print("\n" + month)
    for itemName in sorted(trans["item name"].unique()):
        itemBefore = prof[(prof['date'] == monthBefore) & (prof['item name'] == itemName)]
        item  = trans[(trans['date/time'] == month) & (trans['item name'] == itemName)]
        remainder = item["units purchased"].sum() - item["units sold"].sum()
        remainder += int(itemBefore["remainder"]) if monthBefore else 0
        profit = item["sale"].sum() - item["purchase"].sum()
        profit += float(itemBefore["profit"]) if monthBefore else 0
        prof.loc[-1] = [month, itemName, remainder, profit]
        prof.index = prof.index + 1
        print(itemName + ": remaining " + str(remainder)
        + " items , profit of sales = RM " + str(profit))
    monthBefore = month
prof.reset_index(drop=True, inplace=True)