Gathering data from a file and analyzing it

Question

Recently started learning python, and it's quite a difference coming from C and knowledge taught in CS1. Overall, I am trying to build a team building script for my summer side-project (probably will become a full application eventually), but specifically, am I using python dictionaries and classes correctly with what I am trying to do in these early stages.

1. Find text file, if there is not one, allow user to input employees (for now) and their info.
2. Gather data using python dictionaries.
3. Produce excel tables of this said information (2 as of right now: List of their senorities and a table of all the positions).

I'm unsure if I am efficiently solving this problem. The code starts towards the bottom of the code block. You can also ignore the 2 blocks of code where I am trying to create teams - I've researched and found that it's a "knapsack" problem and will be trying to learn that over the next week.

from distutils.command.build import build
import os
from os.path import exists
import csv
import sys
#from tkinter import Y
from openpyxl import Workbook, load_workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.styles import Color, PatternFill, Font, Border, Alignment
#from openpyxl.cell import Cell


class Positions:
    def __init__(self, position):
        self.position = position
        self.total = 1

class Employee:

    total_num_of_employees = 0
    total_num_of_seniors = 0
    total_num_of_juniors = 0
    total_num_of_sophomores = 0
    total_num_of_freshman = 0

    def __init__(self, emp_num, name, position, senority):
        self.emp_num = emp_num
        self.name = name
        self.position = position
        self.senority = senority
        Employee.total_num_of_employees += 1
        Employee.totals(self.senority)

    def totals(senority):
        if senority == "senior":
            Employee.total_num_of_seniors += 1
        elif senority == "junior":
            Employee.total_num_of_juniors += 1
        elif senority == "sophomore":
            Employee.total_num_of_sophomores += 1
        elif senority == "freshman":
            Employee.total_num_of_freshman += 1

    def showData(self):
        print("ID\t\t:", self.emp_num)
        print("Name\t\t:", self.name)
        print("Positon\t\t:", self.position)
        print("Seniority\t:", self.senority)

def createTeams(employees, numOfTeammates, numOfTeams):
    # Create teams
    teams = {}
    for i in range(numOfTeams):
        teams[i] = []
        for j in range(numOfTeammates): # N^2 plus doesn't work, gross.
            teams[i].append(employees[j])

    return teams

def setupTeams(employees):
    # Set up number of teams and number of teammates
    numOfTeammates = int(input("How many teammates do you want to have in each team? "))
    if(Employee.total_num_of_employees % numOfTeammates == 0):
        numOfTeams = int(Employee.total_num_of_employees / numOfTeammates)
        teams = createTeams(employees, numOfTeammates, numOfTeams)
    else:
        remainder = Employee.total_num_of_employees % numOfTeammates # how many teams that will have 1 extra player
        numOfTeams = int(Employee.total_num_of_employees / numOfTeammates) + remainder

    return teams

# Excel Cell Location
def getCellLoc(column_int):
    start_index = 1   #  starts at A
    letter = ''
    while column_int > 25 + start_index:   
        letter += chr(65 + int((column_int-start_index)/26) - 1)
        column_int = column_int - (int((column_int-start_index)/26))*26
    letter += chr(65 - start_index + (int(column_int)))

    return letter

def tableExists(tables, table_name):
    for table in tables:
        if table == table_name:
            return True
    return False

def numOfEmployeesTable():
    column_num = Employee.total_num_of_employees + 8 # 8 extra spaces to the right.
    loc = getCellLoc(column_num) # get excel cell location (letter)
    # Create total table for each senority level
    wb = load_workbook("Team Creator.xlsx")
    ws = wb.active

    # Create styles
    greenFill = PatternFill(start_color='C6E0B4',
                   end_color='C6E0B4',
                   fill_type='solid')
    yellowFill = PatternFill(start_color='FFE699',
                    end_color='FFE699',
                    fill_type='solid')
    blueFill = PatternFill(start_color='B4C6E7',
                    end_color='B4C6E7',
                    fill_type='solid')
    redFill = PatternFill(start_color='F8CBAD',
                    end_color='F8CBAD',
                    fill_type='solid')
    greyFill = PatternFill(start_color='BFBFBF',
                end_color='BFBFBF',
                fill_type='solid')

    # Populate table with totals
    ws[loc + str(1)] = "Total"

    ws[loc + str(2)] = Employee.total_num_of_seniors
    ws[loc + str(2)].fill = greenFill
    ws[loc + str(2)].alignment = Alignment(horizontal='center')

    ws[loc + str(3)] = Employee.total_num_of_juniors
    ws[loc + str(3)].fill = yellowFill
    ws[loc + str(3)].alignment = Alignment(horizontal='center')

    ws[loc + str(4)] = Employee.total_num_of_sophomores
    ws[loc + str(4)].fill = blueFill
    ws[loc + str(4)].alignment = Alignment(horizontal='center')

    ws[loc + str(5)] = Employee.total_num_of_freshman
    ws[loc + str(5)].fill = redFill
    ws[loc + str(5)].alignment = Alignment(horizontal='center')

    ws[loc + str(6)] = Employee.total_num_of_employees
    ws[loc + str(6)].fill = greyFill
    ws[loc + str(6)].alignment = Alignment(horizontal='center')

    loc = getCellLoc(column_num + 1) # Move one letter over to the right
    ws.column_dimensions[loc].width = 20
    ws[loc + str(1)] = "Class"

    ws[loc + str(2)] = "Senior Class"
    ws[loc + str(3)] = "Junior Class"
    ws[loc + str(4)] = "Sophomore Class"
    ws[loc + str(5)] = "Freshman Class"
    ws[loc + str(6)] = "Total"

    wb.save("Team Creator.xlsx")

def positionsTable(employees):
    column_num = Employee.total_num_of_employees + 8
    loc = getCellLoc(column_num)

    wb = load_workbook("Team Creator.xlsx")
    ws = wb.active
    current_tables = ws._tables

    # Styles
    lighestColor = PatternFill(start_color='FF9999',
                   end_color='FF9999',
                   fill_type='solid')
    lighterColor = PatternFill(start_color='FF7A7A',
                    end_color='FF7A7A',
                    fill_type='solid')
    lightColor = PatternFill(start_color='FF5E5E',
                    end_color='F8CBAD',
                    fill_type='solid')
    darkerColor = PatternFill(start_color='FF3030',
                    end_color='FF3030',
                    fill_type='solid')
    darkestColor = PatternFill(start_color='FF1212',
                    end_color='FF1212',
                    fill_type='solid')
    tabStyle = TableStyleInfo(name="TableStyleLight1", showFirstColumn=True, showLastColumn=False, showRowStripes=False, showColumnStripes=True)

    positions = {} # dictionary to hold position objects
    pos_total = 0
    for key in employees:
        pos = employees[key].position
        if(pos in positions):
            positions[pos].total += 1
            continue # if position is already in dictionary, increment total
        data = Positions(pos)
        positions[pos] = data

        pos_total += 1
        
    if(tableExists(current_tables, "Positions_Table")): # if a table already exists, delete it
        # Hack solution to obtain reference of a table and to delete it.
        tables = ws.tables.items()
        ref = tables[0][1]
        del ws._tables["Positions_Table"] # delete table
        ws.delete_rows(int(ref[2]), int(ref[6])) # delete rows       

    if(pos_total < 6): # if less than 6 positions, create a small list otherwise create table.
        # Populate list with pos. totals
        row_num = 9 # starting location for list
        ws[loc + str(row_num)] = "Total"

        next_column = getCellLoc(column_num + 1)
        ws[next_column + str(row_num)] = "Positions"

        for key in positions:
            row_num += 1

            new_loc = loc + str(row_num) # letter + number for cell location
            ws[new_loc] = positions[key].total
            ws[new_loc].alignment = Alignment(horizontal='center')
            
            # Color Code Positions (lightest to darkest)
            if(positions[key].total < 3):
                ws[new_loc].fill = lighestColor
            elif(positions[key].total < 6):
                ws[new_loc].fill = lighterColor
            elif(positions[key].total < 9):
                ws[new_loc].fill = lightColor
            elif(positions[key].total < 12):
                ws[new_loc].fill = darkerColor
            else:
                ws[new_loc].fill = darkestColor

            ws[next_column + str(row_num)] = key

    else:

        # Populate table with pos. totals
        column_num += 3 # Move table over 3 columns to make room for bigger table
        loc = getCellLoc(column_num)
        ws[loc + str(1)] = "Total"

        next_loc = getCellLoc(column_num + 1)
        ws[next_loc + str(1)] = "Position"
        ws.column_dimensions[next_loc].width = 12

        row_num = 2 # starting location for table
        for key in positions:
            new_loc = loc + str(row_num) # letter + number for cell location
            ws[new_loc] = positions[key].total
            ws[new_loc].alignment = Alignment(horizontal='center')
            
            # Color Code Cells (lightest to darkest)
            if(positions[key].total < 3):
                ws[new_loc].fill = lighestColor
            elif(positions[key].total < 6):
                ws[new_loc].fill = lighterColor
            elif(positions[key].total < 9):
                ws[new_loc].fill = lightColor
            elif(positions[key].total < 12):
                ws[new_loc].fill = darkerColor
            else:
                ws[new_loc].fill = darkestColor

            ws[next_loc + str(row_num)] = key # add position to table

            row_num += 1
        
        # Create new table
        table_column = (loc + str(1)) + ":" + (next_loc + str(row_num - 1))
        tab = Table(displayName="Positions_Table", ref=table_column)
        tab.tableStyleInfo = tabStyle
        ws.add_table(tab)

    wb.save("Team Creator.xlsx")


def createTotalTables(employees):
    # Create total tables in excel file
    positionsTable(employees)
    numOfEmployeesTable()


def createTextFile():
    # Create text file with employee info
    print("There is no employees.txt file in the current directory.\n")
    ans = input("Would you like to create one? (Y/N): ")
    if ans == "y" or ans == "Y":
        with open("employees.txt", "w") as file:
            file.write("Name \t Position \t Seniority\n")
            ans = input("Enter employee information with name, position, and senority separated by spaces (e.g.: J.Doe SE Senior):\n")
            subject = ans.split(" ")

            # Write employee info to file
            for field in range(len(subject)):
                if field != 0 and field % 3 == 0: # if field is divisible by 3, it is a new employee
                    file.write("\n")
                file.write("{} \t".format(subject[field]))
    else:
        print("No employees.txt file created. Exiting program...\n")
        sys.exit()

def checkExcel():
    cwd = os.getcwd()

    # Build Excel file
    wb = Workbook()

    if not(os.path.exists("Team Creator.xlsx")):
        wb.save("Team Creator.xlsx") # save excel to current directory
    else:
        wb = load_workbook("Team Creator.xlsx")
        wb.save("Team Creator - old.xlsx")

        # Delete old excel file
        file_path = os.path.join(cwd, 'Team Creator.xlsx')
        if os.path.exists(file_path):
            os.remove(file_path)

        wb.save("Team Creator.xlsx") # save new excel to current directory


# Get Delimiter from CSV file
def get_delimiter(file_path):
    with open(file_path, newline="") as file:
        secondline = file.readlines()[2]
        dialect = csv.Sniffer().sniff(secondline, delimiters=';,|\t') # sniff for these probable delimiters

    return dialect.delimiter

def createEmployees(file_path, delimit):
    # Read CSV file - Build Employee objects
    emp_num = 0
    with open(file_path, newline="") as file:
        e_reader = csv.reader(file, delimiter=delimit)
        employees = {} # dictionary to hold employee objects

        skip_first_row = False
        for line in e_reader:
            if not skip_first_row: # Skip first row
                skip_first_row = True
                continue
                        
            name = line[0]
            pos = line[1]
            senority = line[2]
            senority = senority.lower()

            emp = Employee(emp_num, name, pos, senority)
            employees[emp_num] = emp # add employee to dictionary, with key as employee number
                        
            emp_num += 1

    return employees

# Check employees.txt File, create one if doesn't exist.
def checkTextFile():
    cwd = os.getcwd()

    if not(os.path.exists("employees.txt")):
        createTextFile()
    else:
        # Get File Location
        employee_file = 'employees.txt'
        file_path = os.path.join(cwd, employee_file) 
        delimit = get_delimiter(file_path)

        ans = input("There is an employees.txt file, would you like to use it (If NO, more options will come up)? (Y/N): ")
        if ans == "y" or ans == "Y":

            employees = createEmployees(file_path, delimit)

            return employees 
        else:
            ans = input("Would you like to create a new employees.txt file, or edit? (Type New/Edit): ")
            ans = ans.lower()
            if ans == "new":
                with open("employees.txt", "r") as oldFile, open('employees-old.txt', 'a') as newFile: # Copy employees.txt into a new file
                    for line in oldFile:
                        newFile.write(line)
                
                os.remove(file_path) # Remove employees.txt
                createTextFile() # Create new employees.txt
                employees = createEmployees(file_path, delimit)

                return employees
            else:
                print("Edit is not implemented yet, so exiting program...\n") 
                sys.exit()
                # TODO: Add edit functionality - possibly when I integrate a GUI
                # Possible window shows a text box with current employees.txt file, and allows user to edit it (something similar to a JList)
                # Select an employee, choose delete, or edit
                # Or a button to add a new employee

# Start up script - Create excel file & Employee objects
def startUpScript():
    checkExcel()
    employees = checkTextFile()
    
    createTotalTables(employees)

    return employees

def main():
    # Build Excel file, Employee objects, and create total tables
    employees = startUpScript()
    
    # Create Teams
    #teams = setupTeams(employees)

    #for team in teams.items():
    #    print()

    #for key in employees:
    #    Employee.showData(employees[key])
    #    print("\n")


if __name__ == "__main__":
    main()

Any tips, information, or brutal comments are welcomed. Thank you!

Answer 1

You have a lot of imported symbols that are unused. If you use any self-respecting Python IDE, it will tell you about these and help you delete them.

In Employee, you have a handful of static-likes (your total_ variables). This is not a well-modelled class. If you really want object-oriented code, consider pulling these out to non-static members of a separate class named perhaps EmployeeSummary.

It's not spelled "senority", but "seniority"; and "Positon" is "Position".

All of your camelCase names should be replaced with lower_snake_case; i.e. createTeams should be create_teams.

Delete all of the parentheses surrounding your if conditions; you aren't in Java/C/etc.

Don't write numeric ASCII values such as 65. Write the actual letter (A) and use chr and ord accordingly.

Rather than int(x/26), use x//26 floor division.

str(1) is just '1', but more importantly, any time that you write out a literal series of 1, 2, 3, etc. that's a significant code smell and calls for a loop. For example, your ws block can instead be something like

for y, (colour, total) in enumerate((
    (green_fill, Employee.total_num_of_seniors),
    (yellow_fill, Employee.total_num_of_juniors),
    # ...
), 2):
    cell = loc + str(y)
    ws[cell] = total
    ws[cell].fill = colour
    ws[cell].alignment = Alignment(horizontal='center')

You probably shouldn't do this:

    wb = load_workbook("Team Creator.xlsx")
    wb.save("Team Creator - old.xlsx")

Instead, just shutil.copyfile.

This:

        name = line[0]
        pos = line[1]
        senority = line[2]

should be replaced with tuple unpacking:

name, pos, seniority = line

checkTextFile (which should be named check_text_file) has a big problem in its return type. In some cases it returns employees, but in your very first case - where the file doesn't exist - you implicitly return None. This None leaks through your createTotalTables call and then crashes.

main() has a bunch of commented-out code that needs to be deleted, or if you need it to be conditionally enabled, put it behind an if that checks a settings flag.