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 python3
import 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 tab
class EmployeeData:
    filtered_datapull = “” #DATA PULL tab and filters it based on what is passed to it
    sorted_path_dict = {} #Stores what path the associate belongs to
#Main class with Contructor and functions to calculate
class VirtualStaffingBoard:
    #Constructor that needs an employee login to call
    #Example call: employee = VirtualStaffingBoard(14477173)
    def __init__(self, employee_barcode):
        self.employee_barcode = employee_barcode
        self.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.prioritization
        self.datapull = DataSource.datapull
        self.logsheet = DataSource.logsheet
        self.error_exception = ErrorException.error_exception
 #This class calculates best path
    def 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 script
        if(EmployeeData.filtered_datapull.empty):
            print(“Barcode does not exist”)
            ErrorException.error_exception = “Barcode does not exist”
            return ErrorException.error_exception
            exit()
        #Calculate best process path
        mmd_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_lp
        sl = (float(sl_datapull) – LPTargetRate.sl_lp) / LPTargetRate.sl_lp
        ss = (float(ss_datapull) – LPTargetRate.ss_lp) / LPTargetRate.ss_lp
        path_dict = {}
        #path_dict[2] is BEST RATE PATH
        #If DATA PULL tab has MMD, SL as 0
        if((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 0
        if((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 0
        if((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, ss
        if((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]
Written 09/13/2021