apple

import pandas as pd
import calendar

xlsx = pd.ExcelFile(r"D:\Users\abc\abc\merge.xlsx")
sr = [1, 2]
sheets = []

for i, j in enumerate(xlsx.sheet_names):
    sheets += [xlsx.parse(j, skiprows=sr[i])]

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

df_apple = [item[item['item name'] == 'apple'] for item in sheets]

df = pd.concat([item for item in df_apple], ignore_index = True, sort = False)
df=df.fillna(0)

#split time into month years
df=df.rename(columns={"date/time": "date"})
def change_to_date(string):
    seq = (string[:5],string[5:7])
    return ''.join(seq)

df['DATE'] = df['date'].apply(change_to_date)
df=df.drop(columns=["date"])
cols = df.columns.tolist()
cols = cols[-1:] + cols[:-1]
df = df[cols]
df['DATE'] = pd.to_datetime(df['DATE'])
df['year'] = df['DATE'].dt.year
df["months"]= df['DATE'].dt.month
df=df.drop(columns=["DATE"])
cols = df.columns.tolist()
cols = cols[-2:] + cols[:-2]
df = df[cols]

#replace all NAN into 0
df['months'] = df['months'].replace(np.nan, 0)
df['units purchased'] = df['units purchased'].replace(np.nan, 0)
df['units sold'] = df['units sold'].replace(np.nan, 0)
df['cost per unit'] = df['cost per unit'].replace(np.nan, 0)
df['price per unit'] = df['price per unit'].replace(np.nan, 0)

#simple normal way
#df["remainder"]=df["units purchased"]-df["units sold"]
#df["profit"]=df["units sold"]*df["price per unit"]-df["units purchased"]*df["cost per unit"]

#list comprehension, faster computation speed
df["remainder"]=[x - y  for x, y in zip(df["units purchased"],df["units sold"])]
df["profit"]=[x* y -a*b  for x, y,a,b  in zip(df["units sold"],df["price per unit"],df["units purchased"],df["cost per unit"])]

#change months into int for conversion
df['months'] = df['months'].round(0).astype(int)
new_df=df.groupby(["year","months","item name"]).sum()
new_df=new_df.reset_index()

#convert numeric month to worded form
new_df['months'] = new_df['months'].apply(lambda x: calendar.month_abbr[x])

#convert years to int form
new_df['year'] = new_df['year'].round(0).astype(int)

#loop
for index, row in new_df.iterrows():
    print(row["months"],row["year"])
    print("\t",row["item name"],":","remaining",row["remainder"],"items,","profit of sales = $",row["profit"])