Python script to filter out raw data in a summary as well as singular agent wise view

automateAgentSegregation.py

from openpyxl import load_workbook
import xlwings as xw

wb = load_workbook('deliveryData - raw.xlsx')
ws = wb['rawdata']
ws.title = 'raw'

column_headers = list(ws.rows)[0]
SALES_AGENTS = []
AGENTS_SUMMARY = {}

def generate_rows_to_iterate(ws):
    rows_to_iterate = ws.iter_rows()
    next(rows_to_iterate)
    return rows_to_iterate

all_data = generate_rows_to_iterate(ws)

for row in all_data:
    agent_name = row[6].value
    if agent_name not in SALES_AGENTS:
        SALES_AGENTS.append(agent_name)

for agent in SALES_AGENTS:
    print(f'Working on {agent}...')
    AGENTS_SUMMARY[agent] = []
    new_ws = wb.create_sheet(agent)
    new_ws.append((cell.value for cell in column_headers))

    rows_to_iterate = generate_rows_to_iterate(ws)
    unsorted_list = []
    for row in rows_to_iterate:
        if row[6].value == agent:
            unsorted_list.append(row)

    unsorted_list.sort(key=lambda x:x[2].value)

    for row in unsorted_list:
        new_ws.append((cell.value for cell in row))
        AGENTS_SUMMARY[agent].append(row[2].value)


ws_summary = wb.create_sheet("Summary", 1)
initial_column = ['Delivery Agent Name', 'Total Deliveries']

for i in range(10, 130, 10):
    initial_column.append(f'{i - 10} - {i}')

ws_summary.append(initial_column)

un_sorted_agent_list = []

for agent in AGENTS_SUMMARY:
    agent_data = AGENTS_SUMMARY[agent]
    unique_data = set(agent_data)
    total_agent_data = len(agent_data)
    all_data_list = {}

    for day in unique_data:
        all_data_list[day] = agent_data.count(day)

    initial_column = [agent, total_agent_data]

    for i in range(10, 130, 10):
        count = 0
        for num in range(i - 9, i + 1):
            count += all_data_list.get(num, 0)

        initial_column.append(count)

    un_sorted_agent_list.append(initial_column)

un_sorted_agent_list.sort(key=lambda x:x[1], reverse=True)

for row in un_sorted_agent_list:
    ws_summary.append(row)

print('Widening columns!')
wb.save(filename = 'deliveryData - agent coded.xlsx')

path = r"deliveryData - agent coded.xlsx"

with xw.App(visible=False) as app:
    wb = xw.Book(path)

    for ws in wb.sheets:
        ws.autofit(axis="columns")

    wb.save(path)
    wb.close()

print('Done!')