col_names_ama =['PC9','Account Seasonality']
# col_names_dock = ['Materials', 'Amazon seasonality']
df_ros = pd.read_excel(os.path.join(directory,'Amazon Seasonality.xlsx'),usecols=col_names_ama)
df_dock =pd.read_excel(os.path.join(directory,'Dock seasonality.xlsx'))
df_dock = df_dock.drop(df_dock.columns[0], axis=1)

print('1 Done')

# rename columns for pd.merge
df.rename(columns={'Material': 'PC9'}, inplace=True)
# df_ros.rename(columns={'Colorway Code' : 'PC9'}, inplace=True)  not applied anymore in new file
df_ros.rename(columns={'Account Seasonality':'Seasonality'}, inplace=True)
df.rename(columns={'Rej.Reason (SL)': 'Cancellation Code'}, inplace=True)
df_dock.rename(columns={'Material': 'PC9'}, inplace =True)
df_dock.rename(columns={'Amazon Seasonality':'Seasonality'}, inplace =True)

print('2 Done')

#Add columns

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



df_all.to_csv(directory + "new.csv", index =False, header =True)