df_1 = pd.read_excel(os.path.join(directory,'copy.xlsm'), sheet_name= "weekly",header= None)
df_1 = df_1.drop(df_1.columns[[0,1]], axis=1)
df_1.columns = df_1.loc[3].rename(None)
df_1 = df_1.drop(range(5))

df_1.to_csv(directory + '1.csv', index=False, header= True)

df_weekly=pd.read_csv(os.path.join(directory,'1.csv'))

columns =['Order Qty (SL)','Confirmed Qty (SL)','Unconfirmed Qty (SL)','Cancelled Qty (SL)','Open Qty (SL)','Reserved Qty (SL)',

    'Fixed Qty (SL)','% Allocation (SL)','Delivered Qty (SL)','PGI Qty (SL)','Invoiced Qty (SL)',
    'Net Unit Price','Confirmed Net Value (SL)','Dollars Shipped (SL)','% Shipped/Allocated (SL)']
df_weekly.loc[:len(df_1) - 2, columns] = df_weekly.loc[:len(df_1) - 2, columns].replace(',', '', regex=True).apply(pd.to_numeric) / 1000

df_weekly= df_weekly.drop(index=df_weekly.index[-2:])

# df_weekly.to_csv(directory + '1.csv', index=False, header= True)
#Read CSV of SAP extract

# df_weekly=pd.read_csv(os.path.join(directory,'1.csv'), low_memory=False)

#read other files for merging

df_mpim = pd.read_excel('MPIM Product Master.xlsx', sheet_name='OutputA')
df_rejection = pd.read_excel(os.path.join(directory,'Rejection.xlsx'))
df_ros = pd.read_excel(os.path.join(directory,'A ROS.xlsx'), sheet_name='H221 ROS', header=6)
df_dock =pd.read_excel(os.path.join(directory,'Dock A Seasonnality.xlsx'))

# rename columns for pd.merge
df_weekly.rename(columns={'Material': 'PC9'}, inplace=True)
df_ros.rename(columns={'Colorway Code' : 'PC9'}, inplace=True)
df_ros.rename(columns={'Account Seasonality':'Seasonality'}, inplace=True)
df_weekly.rename(columns={'Rej.Reason (SL)': 'Cancellation Code'}, inplace=True)
df_dock.rename(columns={'Material': 'PC9'}, inplace =True)
df_dock.rename(columns={'A Seasonality':'Seasonality'}, inplace =True)


#Add columns

df_ros['Division']='10'
df_dock['Division']='20'
df_all = pd.concat([df_ros,df_dock])
# Vlookup

df1 = pd.merge(df_weekly, df_mpim[['PC9','Gender','Category', 'Product Description']], on='PC9', how='left')
#df2 = pd.concat([df1,df_ros[['PC9', 'Seasonality']],df_dock[['PC9','Seasonality']]]).groupby('PC9',as_index=False).first()

#df2 = pd.merge(df1, df_ros[['PC9','Account Seasonality']], on='PC9', how='left')
#df3 =pd.merge(df2,df_rejection[['Rej.Reason (SL)', 'Rej.Reason description']], on='Rej.Reason (SL)', how='left')
df2= pd.merge(df1,df_all[['PC9','Seasonality']], on='PC9', how='left')
df3 = pd.merge(df2, df_rejection[['Cancellation Code', 'Description']], on='Cancellation Code',
                   how='left')

df3['Seasonality']=df3['Seasonality'].replace(np.nan,'NIS')

# rename back columns
df3.rename(columns={'PC9': 'Material',
                     'Product Description': 'Product Name',
                     'Seasonality' : 'A Seasonality',
                     'Description': 'Rej.Reason description',
                    'Cancellation Code':'Rej.Reason (SL)'}, inplace =True)

df3.to_csv(directory + '3Weekly.csv', index=False, header= True)
print("Finished VL1")