0
\$\begingroup\$

I wrote the following script to run through several excel files and format them before saving as a CSV for upload to a Quickbase app I'm creating. Each excel file is roughly 100k lines and the code takes about 3-5 minutes per file. I'm currently using pandas to make my edits. If there is a more efficient way please let me know.

import pandas as pd
import os

# set working directories for files
starting_folder=('Purchasing/unformatted/')
save_folder = ('Purchasing/')

#create list of excel file names from folder  
files = []
for file in os.listdir(starting_folder):
    filename = os.fsdecode(file)
    files.append(filename)

# create list for file names to be saved as csv
save_files = [w.replace('xlsx','csv') for w in files]

# create data frame of fiscal calendar
calendar = pd.read_excel('Purchasing/Fiscal Calendar 15-18.xlsx')
fiscal_calendar = pd.DataFrame([])
#create new data frame with only two columns
fiscal_calendar['InvoiceDate'] = calendar['Date']
fiscal_calendar['Week'] = calendar['Week.1']


#loop through csv files to format and save to new location
for i in range(len(files)):
    #reads unformatted excel file into dataframe
    df = pd.read_excel(starting_folder+files[i])
    #change dtype of dates in report to date time
    df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])
    #drop unwanted rows
    df = df.drop(df[df.Brand == 'NONPROD'].index)
    #add in fiscal week based on invoice date
    Sygma = pd.merge(df,fiscal_calendar, how = 'left', on = 'InvoiceDate')
    #save to csv for db loading
    Sygma.to_csv(save_folder+save_files[i],index = False)
\$\endgroup\$
0
\$\begingroup\$

There are a few small fixes, but just to point out, pandas.read_excel is notoriously slow..

Code.py

import pandas as pd
import os

# # NOTE: If there are multiple worksheets this will take awhile,
# # and you can speed it up by specifying just the worksheet you want
# create data frame of fiscal calendar
fiscal_calendar = pd.read_excel('Purchasing/Fiscal Calendar 15-18.xlsx')
fiscal_calendar = fiscal_calendar[["Date", "Week.1"]].rename(columns={"Date": "InvoiceDate", "Week": "Week.1"})

# set working directories for files
starting_folder = ('Purchasing/unformatted/')
save_folder = ('Purchasing/')

#create list of excel file names from folder  
for file in os.listdir(starting_folder):
    input_name = starting_folder + os.fsdecode(file)
    df = pd.read_excel(input_name)

    # cleanup
    df["InvoiceDate"] = pd.to_datetime(df["InvoiceDate"])
    df = df.drop(df[df.Brand == 'NONPROD'].index)

    # create the output file and save it to .csv
    output_name = save_folder + filename + ".csv"        
    Sygma = pd.merge(df, fiscal_calendar, how='left', on='InvoiceDate')
    Sygma.to_csv(output_name, index=False)   
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.