I wrote some code to create marketshare reports. However, I'm sure it can be improved. The tasks performed are:
- Open the global data file, refresh the data, import its content as a DF.
- Create a backup copy of the old report file.
- Read the list of Distribution companies who should receive the report with their data (plus anonymize data of others).
- Perform data preprocessing tasks.
- Swap the Data sheet in the report file, change its name accordingly (name of the report, name of the Distribution company, month number).
I'm not expecting anyone to get too much into "how it works" (tried to explain it as much as possible in the comments), but is there anything you see that could be improved in this code from the technical point of view?
# Import the necessary libraries import pandas as pd import numpy as np import unidecode as ud import string import re from openpyxl import load_workbook import os import win32com.client as win32 import datetime from shutil import copyfile, move # Define the methods needed # This one is for refreshing the Excel files without having to do it manually def refresh(directory, file_name): xlapp = win32.DispatchEx('Excel.Application') xlapp.DisplayAlerts = False xlapp.Visible = True xlbook = xlapp.Workbooks.Open(directory + '\\' + file_name) xlbook.RefreshAll() xlbook.Save() xlbook.Close() xlapp.Quit() # This one is to clean up Distribution company names def remove_stopwords(text): stopword_list = ['SP', 'S', 'SPZOO', 'ZOO', 'OO', 'POLSKA', 'SPZ', 'Z', 'A', 'AKCYJNA', 'SPOLKA', 'KOMANDYTOWA', 'SPK', 'SK', 'K', 'O', 'SA', 'SJ', 'SPJ', 'J', 'JAWNA'] text_nopunct = ''.join([char.upper() for char in text if char not in string.punctuation]) text_unidecoded = ud.unidecode(text_nopunct) tokens = re.split('\W+', text_unidecoded) tokens_no_stopwords = [word for word in tokens if word not in stopword_list] formatted_text = ' '.join(tokens_no_stopwords) return formatted_text # This one is to swap sheets in the Excel file and rename it def excel_rewriter(data_source, df_name, target_file): book = load_workbook(data_source) writer = pd.ExcelWriter(data_source, engine='openpyxl') writer.book = book writer.sheets = dict((ws.title, ws) for ws in book.worksheets) df_name.to_excel(writer, 'Data', index=False) writer.save() os.rename(data_source, target_file) # Define timeframes month_ago = str(int(datetime.datetime.today().strftime('%Y%m'))-1) two_months_ago = str(int(datetime.datetime.today().strftime('%Y%m'))-2) # Data location and file name data_folder = os.getcwd() backup_folder = data_folder + '\\backup' chdna_data_file = 'ChannelDnAReport.xlsx' # Open list of Distribution companies from the text file distis =  with open('distis.txt', 'r') as filehandle: for line in filehandle: currentDisti = line[:-1] distis.append(currentDisti) # List names for old and new files old_list =  new_list =  for disti in distis: old = disti + '_marketshare_' + two_months_ago + '.xlsx' old_list.append(old) new = disti + '_marketshare_' + month_ago + '.xlsx' new_list.append(new) # Create backup copies and place them in the backup folder for old in old_list: copyfile(old, old + '_copy.xlsx') move(old + '_copy.xlsx', backup_folder) os.rename(backup_folder + '\\' + old + '_copy.xlsx', backup_folder + '\\' + old) # Refresh the main data file refresh(data_folder, chdna_data_file) # Read it as a dataframe df_chdna = pd.read_excel(chdna_data_file, sheet_name='Data') # Standarize Disti names in the DF column (they are a mess otherwise..) df_chdna['Reporter HQ Name'] = df_chdna['Reporter HQ Name'].apply(lambda x: remove_stopwords(x)) # Create DF Total with anonymized data df_total = df_chdna.copy() df_total.loc[:, ]['Reporter HQ Name'] = 'TOTAL' # Create DFs for each Disti df_list =  distis_chdna =  for disti in distis: df_list.append('df_' + disti) distis_chdna.append(disti.upper().replace('_', ' ')) for df, disti_chdna in zip(df_list, distis_chdna): vars()[df] = pd.concat([df_chdna[df_chdna['Reporter HQ Name']==disti_chdna], df_total]) # Swap the Data sheet and refresh Pivots in Excel report files for old, df, new in zip(old_list, df_list, new_list): excel_rewriter(old, vars()[df], new) refresh(data_folder, new)