Accumulate LAPD roster totals by division, rank, etc.

accumulate-lapd.py

import pandas as pd
from utils.list_files import list_files

mapping = {
    'name': 'name',
    'employeename': 'name',
    'first name': 'first name',
    'firstname': 'first name',
    'last name': 'last name',
    'lastname': 'last name',

    'rank': 'rank',
    'ranktile': 'rank',
    'title': 'rank',

    'ethnicity': 'ethnicity',
    'sex': 'gender',
    'gender': 'gender',

    'area': 'division',
    'division': 'division',

    'number': 'serial',
    'serial number': 'serial',
    'no': 'serial',
    'serial no.': 'serial',
    'serial_no': 'serial',
    'serial': 'serial',
    'serialno': 'serial',

    'supervisor': 'supervisor',

    # this is not useful, always equals A
    # 'status': None,

    # these are missing
    # 'div': None,
    # 'mo/yr': None,

    'hire_date': 'hire date',
    'sworn entry date': 'hire date',
    'year of hire': 'hire date',

    'biweekly rate': 'biweekly rate'
}

# step 1, load and combine by serial

from collections import defaultdict
import os

by_serial = defaultdict(list)

def load_acronyms(fn):
    division_acronyms = pd.read_csv(fn)
    return dict(division_acronyms[['Acronym', 'Name']].values)


acronyms = ['division', 'ethnicity', 'gender', 'rank']
acronyms = {k:load_acronyms(f'{k}-acronyms.csv') for k in acronyms}

for fn in sorted(list_files('cleaned')):
    roster_id = os.path.split(fn)[-1].split('.')[0]
    print(roster_id)
    
    df = pd.read_csv(fn)
    print(','.join(df.columns))
    
    # drop unnamed columns
    df = df.loc[:, ~df.columns.str.contains('^Unnamed')]
    
    # convert column names to lowercase
    df.columns = df.columns.str.lower()

    # drop columns that aren't in mapping
    df = df[df.columns.intersection(mapping.keys())]

    # rename columns based on mapping
    df = df.rename(columns=mapping)

    # add the source of the data
    df['src'] = roster_id

    print(','.join(df.columns))

    # replace all acronyms in given columns
    for k,v in acronyms.items():
        if k in df:
            print('remapping', k)
            df[k] = df[k].replace(v)

    # drop all rows with non-int 'serial' (i.e., civilians in 2021-01-14 roster)
    df = df[df['serial'].astype(str).str.isnumeric()]
    df['serial'] = df['serial'].astype(int)

    for e in df.to_dict('records'):

        # ignore empty cells
        record = {k:v for k,v in e.items() if v == v}
                
        # extract serial
        serial = record['serial']
        del record['serial']

        by_serial[serial].append(record)

def accumulate_by_src_and_key(key):
    accumulated_by_src_and_key = defaultdict(lambda:defaultdict(int))

    # propagate forward and backward in time
    for serial,data in by_serial.items():
        # forward
        cached = None
        for i,record in enumerate(data):
            if key in record:
                cached = record[key]
            elif cached is not None:
                by_serial[serial][i][key] = cached

        # backward
        cached = None
        for i,record in enumerate(data[::-1]):
            if key in record:
                cached = record[key]
            elif cached is not None:
                by_serial[serial][-1-i][key] = cached

    for serial,data in by_serial.items():
        for record in data:
            src = record['src']
            if key in record:
                v = record[key]
                accumulated_by_src_and_key[src][v] += 1

    df = pd.DataFrame(accumulated_by_src_and_key).fillna(0).astype(int)
    df['sum'] = df.sum(axis=1)
    df = df.sort_values(by='sum', ascending=False).drop(columns=['sum'])
    df.to_csv(f'{key}-totals-by-date.csv')

accumulate_by_src_and_key('ethnicity')
accumulate_by_src_and_key('gender')
accumulate_by_src_and_key('division')
accumulate_by_src_and_key('rank')

division-acronyms.csv

ethnicity-acronyms.csv

gender-acronyms.csv

rank-acronyms.csv