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.

• CodedRoses: Thank you for all this insight, I will be making changes accordingly. All the commented code in main was just being used as tests, like when I first quickly tried to create teams linearly that horribly failed. Thank you so much again.