with open('1.txt') as f:
    text = f.read().splitlines()

# split header row from data rows; discard garbage rows
cols = text[3].split('|')
data = text[5:]

# create df
df = pd.Series(data).str.split('|', expand=True).rename(columns=dict(enumerate(cols))).iloc[:-2, 1:]

# remove leading/trailing whitespace
df = df.apply(lambda s: s.str.strip(), axis=1)
df.columns = df.columns.str.strip()

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[columns] = df[columns].replace(',', '', regex=True).apply(pd.to_numeric) / 1000


###-----------------------------------------------------------------------------------------------------------------------
# df_weekly = pd.read_csv('weekly.csv')
df_mpim = pd.read_excel('MPIM Product Master.xlsx', sheet_name='OutputA')
df_rejection = pd.read_excel(os.path.join(directory,'Rejection.xlsx'))

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={'Materials': '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])

print('3 Done')
df1 = pd.merge(df, df_mpim[['PC9','Gender','Category', 'Product Description']], on='PC9', how='left')
df2= pd.merge(df1,df_all[['PC9','Seasonality']], on='PC9', how='left')
print(len(df2))
df2.to_csv(directory +'check.csv', index=False, header=True)
df3 = pd.merge(df2, df_rejection[['Cancellation Code', 'Description']], on='Cancellation Code',
                   how='left')

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

print('4 done')
# rename back columns
df3.rename(columns={'PC9': 'Material',
                     'Product Description': 'Product Name',
                     'Seasonality' : 'Amazon 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")