2
\$\begingroup\$

I am looking for any feedback for this first script I have written. It is a mix of openpyxl and pandas, where it takes employee times from different Excel workbooks, then creates a weekly sheet for each person in a new workbook, and fills in the individual sheets.

Somewhere else that I had asked about this code suggested it should be written using classes instead to organize it better. Would it make it "cleaner" in the areas with the if/else statements or how would it be written that way? In this iteration of the code, the date and folder location is inputted by a separate GUI that I am working on using Tkinter.

# Imports
import pandas as pd
import os
from pathlib import Path
import glob
import xlrd
import openpyxl
import pprint
import datetime
import threading
import time
from employee_data_base import employee_db as db
from openpyxl.styles import Font, Alignment



start = time.time()
# dts_folder = ('/Users/Documents/PythonExcel/DTS/')
template_loc = '/Users/Documents/PythonExcel'
template_file = 'TCBlank.xlsx'
re_rates = [{'Position': 'Condor Op', 'Code': '5431'}, {'Position': 'S/O', 'Code': '5431'}]
occ_codes = {'CLT': '5401', 'ACLT': '5403', 'LCP': '5422', 'SLT': '5451', 'Condor Op': '5431', 'S/O': '5431'}

# xlsx_files = [path for path in Path(dts_folder).rglob('*.xlsx')]



# Get values from GUI
def get_data(dts_folder, selected_date):
    # global end_date, file
    file = dts_folder
    end_date = selected_date
    main(file, end_date)
    # use the passed in arguments to do the work, not the hardcoded globals

# Import individual daily time sheets
def main(file, end_date):
    xlsx_files = [path for path in Path(file).rglob('*.xlsx')]
    col_names = ['Name', 'Position', 'Call', 'Lunch In', 'Lunch Out', 'Wrap']
    cols = [1, 2, 4, 5, 7, 8]
    skiprows = [0, 1, 2, 3, 21, 22, 23, 25, 25]
    df = pd.DataFrame()
    pd.set_option('max_r', None)
    pd.set_option('display.max_columns', None)
    df = pd.concat([pd.read_excel(f, usecols=cols, skiprows=skiprows, names=col_names,
                                  header=None).assign(Date=os.path.basename(f)) for f in xlsx_files])
    df['Date'] = df['Date'].str.rstrip(' Lucifer Lighting DTS.xlsx')
    df = df.dropna()
    # create_df_dict()
    # return df

    # Create dictionary from combined data frame
    df_dict = df.to_dict('r')

    # Create list of employee names
    employee_names = sorted(list(set(df['Name'])))
    for n in employee_names:
        fl = n.split(" ")
        first_name = fl[0]
        last_name = fl[1]
        lf_name = []
        lf_name.append(f'{last_name}, {first_name}')
        print(lf_name)

    # Create workbook for weekly data
    os.chdir(template_loc)
    wb = openpyxl.load_workbook(template_file, data_only=True)
    ws = wb.active

    # Get the week ending date to fill in cell and then excel fills in the rest of the week column
    template = wb['TC_Template']
    print('What is the week ending date?')
    # endDate = input()
    # end_date = '03-07-2020'
    weekEndDate = pd.date_range(end=end_date, periods=7)

    # Name weekly time card file
    weekly_tc = f"/Users/Desktop/Weekly_Time_Card_{end_date}.xlsx"

    # Write dates to cells
    template['F15'].value = weekEndDate[0].strftime('%m-%d')
    template['F17'].value = weekEndDate[1].strftime('%m-%d')
    template['F19'].value = weekEndDate[2].strftime('%m-%d')
    template['F21'].value = weekEndDate[3].strftime('%m-%d')
    template['F23'].value = weekEndDate[4].strftime('%m-%d')
    template['F25'].value = weekEndDate[5].strftime('%m-%d')
    template['F27'].value = weekEndDate[6].strftime('%m-%d')
    template['AE6'].value = weekEndDate[6].strftime('%m-%d-%y')
    print('Finished updating the template')

    # Create sheets for each person from template
    for copies in range(len(employee_names)):
        target = wb.copy_worksheet(from_worksheet=template)
    print('Copied sheets!')

    # Rename the sheets based on the name of the person
    sheets = wb.sheetnames
    counter = 0
    for sheet in sheets:
        ss_sheet = wb[sheet]
        ss_sheet.title = employee_names[counter - 1]
        ss_sheet['B8'].value = employee_names[counter - 1]
        counter += 1
        wb.save(weekly_tc)
    print('Sheets renamed!')

    # Delete duplicate sheet
    list_sheets = list(wb.sheetnames)
    list_sheets_len = len(list_sheets) - 1
    dup_sheet = list_sheets[list_sheets_len]
    del wb[dup_sheet]
    wb.save(weekly_tc)
    print('Finished creating all the sheets!')



    wtc_wb = openpyxl.load_workbook(weekly_tc)

    # Function to match up employee weekly data and write it into the excel time card
    def write_tc(emp):
        ws1 = wtc_wb[emp]
        name_cell = ws1['B8'].value
        sunday = ws1['F15'].value
        monday = ws1['F17'].value
        tuesday = ws1['F19'].value
        wednesday = ws1['F21'].value
        thursday = ws1['F23'].value
        friday = ws1['F25'].value
        saturday = ws1['F27'].value
        for d in df_dict:
            if d['Name'] == name_cell and d['Date'] == sunday:
                ws1['I15'].value = d['Call']
                ws1['J15'].value = d['Lunch In']
                ws1['K15'].value = d['Lunch Out']
                ws1['O15'].value = d['Wrap']
            elif d['Name'] == f"{name_cell}" and d['Date'] == monday:
                ws1['I17'].value = d['Call']
                ws1['J17'].value = d['Lunch In']
                ws1['K17'].value = d['Lunch Out']
                ws1['O17'].value = d['Wrap']
            elif d['Name'] == f"{name_cell}" and d['Date'] == tuesday:
                ws1['I19'].value = d['Call']
                ws1['J19'].value = d['Lunch In']
                ws1['K19'].value = d['Lunch Out']
                ws1['O19'].value = d['Wrap']
            elif d['Name'] == f"{name_cell}" and d['Date'] == wednesday:
                ws1['I21'].value = d['Call']
                ws1['J21'].value = d['Lunch In']
                ws1['K21'].value = d['Lunch Out']
                ws1['O21'].value = d['Wrap']
            elif d['Name'] == f"{name_cell}" and d['Date'] == thursday:
                ws1['I23'].value = d['Call']
                ws1['J23'].value = d['Lunch In']
                ws1['K23'].value = d['Lunch Out']
                ws1['O23'].value = d['Wrap']
            elif d['Name'] == f"{name_cell}" and d['Date'] == friday:
                ws1['I25'].value = d['Call']
                ws1['J25'].value = d['Lunch In']
                ws1['K25'].value = d['Lunch Out']
                ws1['O25'].value = d['Wrap']
            elif d['Name'] == f"{name_cell}" and d['Date'] == saturday:
                ws1['I27'].value = d['Call']
                ws1['J27'].value = d['Lunch In']
                ws1['K27'].value = d['Lunch Out']
                ws1['O27'].value = d['Wrap']
        # print(f"Finished writing {emp}'s time card!")

    # Fill in position code and last four of social
    def fill_position(emp):
        ws1 = wtc_wb[emp]
        name_cell = ws1['B8'].value
        sig = ws1['E45']
        sig.font = Font(bold=False, name='Brush Script MT', size=36)
        sig.alignment = Alignment(indent=1, horizontal='left', vertical='center')
        ss_cell = ws1['O8'].value
        position_cell = ws1['AM10'].value
        for i in db:
            if i['name'] == name_cell:
                ws1['O8'].value = i['social']
                ws1['AM10'].value = i['position']
                sig.value = i['name']
        # print(f"{emp} social and position filled in! Now filling in their times!")

    def check_rerates(emp):
        ws1 = wtc_wb[emp]
        name_cell = ws1['B8'].value
        sunday = ws1['F15'].value
        monday = ws1['F17'].value
        tuesday = ws1['F19'].value
        wednesday = ws1['F21'].value
        thursday = ws1['F23'].value
        friday = ws1['F25'].value
        saturday = ws1['F27'].value
        position_cell = ws1['AM10'].value
        for d in df_dict:
            for p in re_rates:
                if d['Position'] == p['Position'] and d['Name'] == name_cell and d['Date'] == sunday:
                    ws1['P15'].value = p['Position']
                    ws1['Q15'].value = p['Code']
                    print(f"{emp} was re-rated to {p['Position']} on Sunday.")
                elif d['Position'] == p['Position'] and d['Name'] == name_cell and d['Date'] == monday:
                    ws1['P17'].value = p['Position']
                    ws1['Q17'].value = p['Code']
                    print(f"{emp} was re-rated to {p['Position']} on Monday.")
                elif d['Position'] == p['Position'] and d['Name'] == name_cell and d['Date'] == tuesday:
                    ws1['P19'].value = p['Position']
                    ws1['Q19'].value = p['Code']
                    print(f"{emp} was re-rated to {p['Position']} on Tuesday.")
                elif d['Position'] == p['Position'] and d['Name'] == name_cell and d['Date'] == wednesday:
                    ws1['P21'].value = p['Position']
                    ws1['Q21'].value = p['Code']
                    print(f"{emp} was re-rated to {p['Position']} on Wednesday.")
                elif d['Position'] == p['Position'] and d['Name'] == name_cell and d['Date'] == thursday:
                    ws1['P23'].value = p['Position']
                    ws1['Q23'].value = p['Code']
                    print(f"{emp} was re-rated to {p['Position']} on Thursday.")
                elif d['Position'] == p['Position'] and d['Name'] == name_cell and d['Date'] == friday:
                    ws1['P25'].value = p['Position']
                    ws1['Q25'].value = p['Code']
                    print(f"{emp} was re-rated to {p['Position']} on Friday.")
                elif d['Position'] == p['Position'] and d['Name'] == name_cell and d['Date'] == saturday:
                    ws1['P27'].value = p['Position']
                    ws1['Q27'].value = p['Code']
                    print(f"{emp} was re-rated to {p['Position']} on Saturday.")

    def run_program():
        for emp in employee_names:
            fill_position(emp)
            write_tc(emp)
            print(f"Finished writing {emp} time card!")
            finished_cards = f'Finished writing {emp} time card!'
            check_rerates(emp)
            wtc_wb.save(weekly_tc)

    run_program()


    print('It took', time.time()-start, 'seconds.')

if __name__ == '__main__':
    main()
\$\endgroup\$
0
\$\begingroup\$

I don't see a huge benefit in adding classes here but there are other things you can do.

Avoid repetition 1

template['F15'].value = weekEndDate[0].strftime('%m-%d')
template['F17'].value = weekEndDate[1].strftime('%m-%d')
template['F19'].value = weekEndDate[2].strftime('%m-%d')
template['F21'].value = weekEndDate[3].strftime('%m-%d')
template['F23'].value = weekEndDate[4].strftime('%m-%d')
template['F25'].value = weekEndDate[5].strftime('%m-%d')
template['F27'].value = weekEndDate[6].strftime('%m-%d')

Whenever you repeat a lot of code in many lines, there will be more elegant ways to write it instead.

Since only the indexes change here, and the weekEndDate indexes are nicely lined up from 0 to 6, we can do:

date_cells = ['F15', 'F17', 'F19', 'F21', 'F23', 'F25', 'F27']
for index, cell in enumerate(date_cells):
    template[cell].value = weekEndDate[index].strftime('%m-%d')

You can do the same kind of changes in the other two parts of your code where you repeat the same things over and over for each weekday. Those are the major ugly parts in this code.

Avoid repetition 2

Assignments can be chained. Instead of

ss_sheet.title = employee_names[counter - 1]
ss_sheet['B8'].value = employee_names[counter - 1]

You can do

ss_sheet.title = ss_sheet['B8'].value = employee_names[counter - 1]
\$\endgroup\$
3
  • \$\begingroup\$ Thank you for the advice. I was looking at the areas for each weekday and not really sure how to write it since each day is a specific cell with a letter and number? Also, is it better to do less specific functions and just do one larger function? I ran into trouble when I wanted to pass in variables from the gui or other parts and to try to avoid global variables. \$\endgroup\$ – user13847909 Aug 23 '20 at 13:04
  • \$\begingroup\$ Yes, each day is a specific cell letter and number, and that's the only part of that code which changes. The other 98% is identical and repeated 7 times over, which means it can be made 7 times shorter. You need more practice with Python. Learn about dictionaries in Python. Create a dictionary mapping the weekday names to their cell ids. Then iterate over that. You could start reading here realpython.com/iterate-through-dictionary-python \$\endgroup\$ – user985366 Aug 23 '20 at 23:30
  • \$\begingroup\$ Also, is it better to do less specific functions and just do one larger function? I ran into trouble when I wanted to pass in variables from the gui or other parts and to try to avoid global variables. In general, it is good to to very specific functions that are short and do one thing well. Avoid general functions with a lot of parameters. One important purpose of functions is to split up the program into small parts that are short, well defined and easy to maintain and to test. To do that, we want them short and very specific. \$\endgroup\$ – user985366 Aug 23 '20 at 23:33

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.