Had a requirement to parse through many different tabs related to employee data in an excel file. Since we were in a Linux environment (Ubuntu), went to use Python since it was object oriented with libraries needed and can then be used to display on a website with another framework such as Flask.
Will have to paste snippets and modify for this one due to confidentiality.
Solution:
#!/usr/bin/env python3import pandas as pd, sys, numpy#This class is where we get all our data.class DataSource:datapull = pd.read_excel(“./files/excelfile.xlsm”,sheet_name=”DATA PULL”)prioritization = pd.read_excel(“./files/excelfile.xlsm”,sheet_name=”PRIORITIZATION”)logsheet = pd.read_excel(“./files/excelfile”,sheet_name=”LOG SHEET”)#This class stores all data from the DATA PULL tabclass EmployeeData:filtered_datapull = “” #DATA PULL tab and filters it based on what is passed to itsorted_path_dict = {} #Stores what path the associate belongs to#Main class with Contructor and functions to calculateclass VirtualStaffingBoard:#Constructor that needs an employee login to call#Example call: employee = VirtualStaffingBoard(14477173)def __init__(self, employee_barcode):self.employee_barcode = employee_barcodeself.calculatePath()self.best_rate_path = list(EmployeeData.sorted_path_dict.values())[0]self.employee_id = EmployeeData.filtered_datapull[EmployeeData.filtered_datapull.columns[0]].to_string(index=False)self.employee_id = self.employee_id[ 0 : self.employee_id.index(“.”)]self.employee_login = EmployeeData.filtered_datapull[EmployeeData.filtered_datapull.columns[2]].to_string(index=False)self.employee_datapull_info = DataSource.datapull[DataSource.datapull[‘EMPLOYEE_BARCODE’]==employee_barcode]self.prioritization = DataSource.prioritizationself.datapull = DataSource.datapullself.logsheet = DataSource.logsheetself.error_exception = ErrorException.error_exception#This class calculates best pathdef calculatePath(self):#Filter datapull tab#EmployeeData.filtered_datapull = DataSource.datapull[DataSource.datapull[‘EMPLOYEE_BARCODE’].to_string(index=False)==self.employee_barcode]EmployeeData.filtered_datapull = DataSource.datapull[DataSource.datapull[‘EMPLOYEE_BARCODE’]==self.employee_barcode]EmployeeData.filtered_datapull = EmployeeData.filtered_datapull[[“EMP ID”,”EMPLOYEE_BARCODE”,”EMPLOYEE_LOGIN”, “MMD”,”SL”,”SS”]]#If AA barcode does not exist in DATA PULL tab spreadsheet, then exit scriptif(EmployeeData.filtered_datapull.empty):print(“Barcode does not exist”)ErrorException.error_exception = “Barcode does not exist”return ErrorException.error_exceptionexit()#Calculate best process pathmmd_datapull = EmployeeData.filtered_datapull[EmployeeData.filtered_datapull.columns[3]].to_string(index=False)sl_datapull = EmployeeData.filtered_datapull[EmployeeData.filtered_datapull.columns[4]].to_string(index=False)ss_datapull = EmployeeData.filtered_datapull[EmployeeData.filtered_datapull.columns[5]].to_string(index=False)mmd = (float(mmd_datapull) – LPTargetRate.mmd_lp) / LPTargetRate.mmd_lpsl = (float(sl_datapull) – LPTargetRate.sl_lp) / LPTargetRate.sl_lpss = (float(ss_datapull) – LPTargetRate.ss_lp) / LPTargetRate.ss_lppath_dict = {}#path_dict[2] is BEST RATE PATH#If DATA PULL tab has MMD, SL as 0if((mmd == -1.0) & (sl == -1.0)):path_dict[0] = ‘MMD’path_dict[1] = ‘MMD’path_dict[2] = ‘SS’#If DATA PULL tab has MMD, SS as 0if((mmd == -1.0) & (ss == -1.0)):path_dict[0] = ‘SS’path_dict[1] = ‘SS’path_dict[2] = ‘SL’#If DATA PULL tab has SS, SL as 0if((ss == -1.0) & (sl == -1.0)):path_dict[0] = ‘SS’path_dict[1] = ‘SS’path_dict[2] = ‘MMD’#If DATA PULL tab has all 0 in mmd, sl, ssif((mmd == -1.0) & (sl == -1.0) & (ss ==-1.0)):path_dict[0] = ‘SS’path_dict[1] = ‘SS’path_dict[2] = ‘SS’else:path_dict[mmd] = ‘MMD’path_dict[sl] = ‘SL’path_dict[ss] = ‘SS’for key in sorted(path_dict,reverse=True):EmployeeData.sorted_path_dict[key] = path_dict[key]
