import os

import pandas as pd

dict={
    "Littlewoods Shop Direct Group": 'Emma',
    "NEXT PLC" : "Emma",
    "Asos.com UK" : "Nele",
    "Zalando SE" : "Emelie, Nilmara",
    "AMAZON EU SARL" : "Augustin",
    "Wehkamp Bv" : 'Manuel',
    "Otto-Versand GmbH & Co. KG": "Anna-lena",
    "Boozt Fashion AB": "Manuel",
    "Publikat GmbH" : "Manuel",
    "ASOS EU": "Nele",
    "ABOUT YOU SE & Co. KG": "Anna-lena"
}

path = r'C://Users//Desktop//Autoreport//Load_attachments//'
for filename in os.listdir(path):
    if filename.startswith("PB orders"):
        filename, extension = os.path.splitext(filename)
        print(filename)
        os.rename(path + filename + extension, path + filename[0:13] + extension)

for filename in os.listdir(path):
    if filename.startswith("PB orders"):
        full_path = os.path.join(path, filename)
        month = pd.read_excel(full_path, sheet_name="Raw data ", engine="pyxlsb")
        month = month[month['Sales Manager'] == 'MCLEAN, COLM']
        month=month.drop(month.columns[[10]], axis=1)
        month=month.drop(month.loc[month['Sold-To Name']=='JD Williams and Co Ltd'].index,inplace=True)
        month["O2CX"] = month['Sold-To Name'].map(dict)
        filename, extension = os.path.splitext(filename)
        line = filename + '.csv'
        month.to_csv (path + line, index = None, header=True)