<jupyter_start><jupyter_text>Blog 6 Medium Series on Gaining an Analytics Edge using USAspending.gov DataExploration of the top 5 highest paid executives data in the USAspending.gov files.<jupyter_code># previous blog post - https://medium.com/@lulstrup/5-is-federal-spending-on-ai-growing-cbb2881cf0d3
# highly_compensated employee reporting - https://usaspending-help.zendesk.com/hc/en-us/articles/360001252274-Award-Data-Question-What-entites-are-required-to-include-Executive-Compensation-data-
# https://www.law.cornell.edu/cfr/text/48/52.204-10

# From USAspending.gov FAQs

# Per FFATA, entities must report the executive compensation of their five most highly compensated officers when the following three conditions are met:
# During the preceding fiscal year, the entity received 80 percent or more of its annual gross revenues from federal awards.
# The entity received $25M or more in annual gross revenues from federal awards.
# The public does not have access to the compensation information through periodic reports filed under the Securities and Exchange Act of 1934 or the Internal Revenue Code of 1986. 
# If these conditions aren’t met, recipients aren’t required to provide executive compensation data.
import os, glob, pathlib
import math
import shutil
from datetime import datetime
from collections import Counter
import zipfile
import pandas as pd
import numpy as np
import dask
import dask.dataframe as dd
from dask.distributed import Client, progress
import psutil
import requests

import matplotlib
import matplotlib.pyplot as plt

# use import below to interactively select the folder where the USAspending Archives are located
# https://pypi.org/project/ipyfilechooser/
from ipyfilechooser import FileChooser
import random
matplotlib.style.use('ggplot')
# Use this to hide the names of the companies and people for display purposes
def Name_Scrambler(text):
    random_letters = random.sample(list(text), len(text))
    random_text = "".join(random_letters)
    random_text = " ".join([aWord.capitalize() for aWord in random_text.split()])
    return random_text.strip()

def Scramble_Company_Exec_Names(df):
    df_copy = df.copy()
    df_copy['recipient_parent_name'] = df_copy['recipient_parent_name'].apply(Name_Scrambler)
    for n in range(5):
        field_name = 'highly_compensated_officer_' + str(n+1) + '_name'
        if field_name in df_copy.columns:
            df_copy[field_name] = df_copy[field_name].apply(Name_Scrambler)
    return df_copy

Name_Scrambler("<NAME>")
def Display_System_Info(): # https://psutil.readthedocs.io/en/latest/#psutil.virtual_memory
    physical_cores = psutil.cpu_count(logical=False) #, psutil.cpu_count(logical=True)
    RAM_total_installed = psutil.virtual_memory()[0] #, psutil.swap_memory()
    RAM_available = psutil.virtual_memory().available #['available']
    dask_workers = int(physical_cores/2)
    print(f"Physical CPU Cores: {physical_cores}, RAM available: {round(RAM_available/1e9)} GB, total RAM: {round(RAM_total_installed/1e9)} GB")
    result_record = {'CPU_Cores_Physical' : physical_cores,
                    'RAM_available' : RAM_available,
                     'RAM_total_installed' : RAM_total_installed,
                    }
    return result_record

Display_System_Info()
def Get_Current_Time():
    return datetime.now().strftime("%d/%m/%Y %H:%M:%S")

Get_Current_Time()
# this assumes the jupyter notebook is running in the same higher level directory
# where the USAspending.gov zip archives and the decompressed Zip files are stored

original_cwd = os.getcwd() # use this if  files are located in local directory where jupyter is running
# 
folder_choice = FileChooser(os.getcwd(),
                           title='<b>Choose Folder with USAspending.gov GFY Archive Expanded CSV Files:</b>',
                            show_hidden=False,
                            select_default=True,
                            use_dir_icons=True,
                            show_only_dirs=True)
display(folder_choice)
download_file_path_CSV = str(folder_choice.selected_path)

your_path_dummy = 'Users/YourName/YourTopFolder_Documents/YourUSAspendingFolderName'
#print(original_cwd)
print(os.path.join(your_path_dummy, "/".join(download_file_path_CSV.split('/')[-2:])))
def CurrentGFY():
    if datetime.now().month >= 10: 
        return datetime.now().year + 1
    else:
        return datetime.now().year
    
def Check_Archive_Filename_Format(filename_complete):
    base_filename = os.path.basename(filename_complete)
    # rule checks
    if not base_filename.startswith("FY"):
        return False
    if not base_filename.endswith(".zip") and not base_filename.endswith(".csv"):
        return False
    if " " in base_filename:
        return False
    if len(base_filename.split(".")) > 2:
        return False
    if "copy" in base_filename:
        return False
    
    return True

def Get_GFY_from_file_path(filename_to_check_complete_path):
    if os.path.isdir(filename_to_check_complete_path):
        #print(f"{filename_to_check_complete_path} is a directory. Ignore")
        return
    filename_GFY = os.path.basename(filename_to_check_complete_path)[:6]
    assert filename_GFY[:2] == 'FY' # check this
    return filename_GFY

def Get_ArchiveDate_from_file_path(filename_to_check_complete_path):
    if os.path.isdir(filename_to_check_complete_path):
        #print(f"{filename_to_check_complete_path} is a directory. Ignore")
        return
    basefilename = os.path.basename(filename_to_check_complete_path)
    if filename_to_check_complete_path.endswith(".zip"):
        filename_latest_update = basefilename.split("_")[4][0:8]
    elif filename_to_check_complete_path.endswith(".csv"):
        filename_latest_update = basefilename.split("_")[4][0:8]
    else:
        filename_latest_update ="Filename_Format_Issue"
    # assert TBD
    return filename_latest_update
# Clean up the Data and add some fields

### function to create a recipient common name using common fields
### addresses problem with multiple versions of names, M&A, and misspellings

#### Please read this link on the USAspending.gov site about use of "D&B Open Data" - https://www.usaspending.gov/db_info
#### "D&B Open Data" is embedded in these records

#TODO - placeholder function - enhance this to find the most common name for the same company in the records 
def Add_Common_Recipient_Names(df): #placeholder for future data validation using a common key(s) or hash value
    # add transformation and validation code to select most common name for an entity-firm-contractor
    df['recipient_parent_name_common'] = df['recipient_parent_name']
    df['recipient_name_common'] = df['recipient_name']
    return

# !!! possible use in the future cage_codes are missing for most records prior to GFY19
# def Add_Common_Recipient_Names_Using_CAGE_Code(df): #works ok starting in FY19 (missing from ~200M $s in GFY19) but is unreliable prior to that - missing
#     # Build cage_code to recipient_parent_name, recipient_name mapping and collect the most common names
#     # There are naming inconsistencies in the USAspending.gov data for recipient_names
#     # Create code to build a unique identifier that can be used in groupby for recipient_parent_name when there are spelling and naming inconsistencies

#     cage_name_mapping = df[['action_date_fiscal_year','cage_code','recipient_parent_name', 'recipient_name']].drop_duplicates()
#     #cage_name_mapping.shape

#     #TODO add code to properyly handle how the recipient_parent_name and the recipient_name may change across GFY
#     # get the most common recipient_name for each cage_code
#     cage_name_mapping_recipient_parent = cage_name_mapping.groupby(['cage_code'])['recipient_parent_name'].apply(list).apply(Counter).apply(lambda x: x.most_common()[0][0])
#     cage_name_mapping_recipient = cage_name_mapping.groupby(['cage_code'])['recipient_name'].apply(list).apply(Counter).apply(lambda x: x.most_common()[0][0])

#     #cage_name_mapping_recipient_parent['00026'] # assert in FY2019 == 'MMC INTERNATIONAL CORP.'

#     # add a column with the most common recipient_name for downstream groupby actions
#     df['recipient_parent_name_common'] = df['cage_code'].apply(lambda x: cage_name_mapping_recipient_parent[x])
#     df['recipient_name_common'] = df['cage_code'].apply(lambda x: cage_name_mapping_recipient[x])    
#     return df

def Fix_Recipient_Name_UNSPECIFIED(df):
    #fix blank or UNSPECIFIED recipient_parent_names
    
#     fix_UNSPECIFIED_lambda = lambda x: x['recipient_name'] + "_UNSPECIFIED" if x['recipient_parent_name'] in ['', 'UNSPECIFIED'] else x['recipient_parent_name']
#     #pandas.core.frame.DataFrame, dask.dataframe.core.DataFrame
#     if type(df) == dask.dataframe.core.DataFrame:
#         df['recipient_parent_name'] = df.apply(fix_UNSPECIFIED_lambda, axis = 1, meta=('recipient_parent_name', 'object'))
#     else:
#         df['recipient_parent_name'] = df.apply(fix_UNSPECIFIED_lambda, axis = 1)
    
    df['recipient_parent_name'] = df['recipient_parent_name'].mask(df['recipient_parent_name'] == 'UNSPECIFIED', df['recipient_parent_name'] + "_UNSPECIFIED") # use of mask instead of other options - https://docs.dask.org/en/latest/dataframe-api.html#dask.dataframe.DataFrame.mask

    return

def Fix_Recipient_Names_Known_Issues(df): # MANUAL Fixes needed due to USAspending.gov not updating recipient_parent_name (e.g., after SAIC spun out Leidos)
    #TODO - shoould be added at time of read of the files as part of prep
    
    #fix Naming Issues - Manual Fixes (large firms mostly - edit the external files)
    
    if glob.glob("USAspending_Parent_Name_Fixes_Manual.csv"):
        df_fixes = pd.read_csv("USAspending_Parent_Name_Fixes_Manual.csv")
        manual_fixes_lookup = {}
        for index, row in df_fixes.iterrows():
            manual_fixes_lookup[row['recipient_old_name']] = {'recipient_new_name' :row['recipient_new_name'],
                                                              'GFY_When_Switch_Happens' : row['GFY_When_Switch_Happens'],
                                                             }
    
    else:
        manual_fixes_lookup = {
            'SAIC' : {'recipient_new_name' : 'LEIDOS HOLDINGS  INC.',
                                                               'GFY_When_Switch_Happens' : '2014'}, #!!Notes ther is an extra space needed between HOLDINGS and Inc. name in USAspending, common name fix
            'NORTHROP GRUMMAN SYSTEMS CORPORATION' : {'recipient_new_name' : 'NORTHROP GRUMMAN CORPORATION',
                                                      'GFY_When_Switch_Happens' : ''
                                                     },

        }
    
    for old_name, new_name_record in manual_fixes_lookup.items():
        new_name = new_name_record['recipient_new_name']
        GFY_When_Switch_Happens = new_name_record['GFY_When_Switch_Happens']
        # mask preferred via https://stackoverflow.com/questions/54360549/dask-item-assignment-cannot-use-loc-for-item-assignment
        
        if not math.isnan(GFY_When_Switch_Happens) and GFY_When_Switch_Happens != '':
            selector = (df['recipient_parent_name'] == old_name) & (df['action_date_fiscal_year'].astype('int64') >= int(GFY_When_Switch_Happens))
        else:
            selector = (df['recipient_parent_name'] == old_name)
        
        df['recipient_parent_name'] = df['recipient_parent_name'].mask(selector, new_name) # use of mask instead of other options - https://docs.dask.org/en/latest/dataframe-api.html#dask.dataframe.DataFrame.mask
        
    return

def Add_PSC_Cat_Fields(df):
    print(f"{Get_Current_Time()} -> adding PSC_Cat field...")
    df['PSC_Cat'] = df['product_or_service_code'].str[:1]
    df['PSC_Cat_2'] = df['product_or_service_code'].str[:2]
    return

def Fix_High_Compensated_Field(df):
    # these fields were read in via pandas as strings (pandas objects), need to convert them to float
    # some of the fields contain text inside of values and some had NaN that was repalced with UNSPECIFIED
    fields_for_float =[
        'highly_compensated_officer_1_amount',
           'highly_compensated_officer_2_amount',
           'highly_compensated_officer_3_amount',
           'highly_compensated_officer_4_amount',
           'highly_compensated_officer_5_amount'
    ]


    for field in fields_for_float:
        df[field] = df[field].apply(lambda x: float(x) if x.replace(".","").isdigit() else 0.0)
    return
    

def Enhance_Spending_File(df): # this function addresses known shortcomings in the USAspending archive data
    print()
    print(f"{Get_Current_Time()} -> replacing NaN with UNSPECIFIED...")
    df = df.fillna("UNSPECIFIED") # IMPORTANT - NA fields can affect groupby sums and other problems
    
    if 'product_or_service_code' in df.columns:
        Add_PSC_Cat_Fields(df)
    
    print(f"{Get_Current_Time()} -> fixing missing recipient_parent_name...")
    Fix_Recipient_Name_UNSPECIFIED(df)
    print(f"{Get_Current_Time()} -> fixing recipient_parent_names that are inconsistent...")
    Fix_Recipient_Names_Known_Issues(df)
    
    #print(f"{Get_Current_Time()} -> adding recipient_name_common and recipient_parent_name_common fields for groupby")
    #Add_Common_Recipient_Names(df)
    
    print(f"{Get_Current_Time()} -> converting highly_compensated field amounts to float")
    Fix_High_Compensated_Field(df)
    print(f"{Get_Current_Time()} Done with fixes.")
    print()
    return df
## Key code for read CSV files into pandas and Dask
def Build_DTypes_Dict(filename_list):
    df = pd.read_csv(filename_list[0], nrows=1)
    
    dtype = dict(zip(sorted(df.columns), ['object'] * len(df.columns)))
    
    # ! important - may need to change other fields to category, int, date etc for better memory management
    dtype['federal_action_obligation'] = 'float64'
    #dtype['action_date_fiscal_year'] = 'int'
    
    return dtype

def Load_CSV_Files_Into_DF(filename_list, usecols = 'ALL'): #best approach if you have enough RAM memory on your machine
    
    dtype = Build_DTypes_Dict(filename_list) # ! important - may need to change other fields to category, int, date etc for better memory management
    
#     print(f"Reading file: {filename_list[0]}")
#     df = pd.read_csv(filename_list[0], dtype=dtype, low_memory=False) # read the first file
    
#     for filename in filename_list[1:]: #read the remaining files and append
#         print(f"Reading file: {filename}")
#         df = df.append(pd.read_csv(filename, dtype=dtype, low_memory=False))
        
    #alternative approach - faster?
    dataframe_loads_list = []
    for filename in filename_list:
        print(f"Reading file: {os.path.basename(filename)}")
        if usecols == 'ALL':
            dataframe_loads_list.append(pd.read_csv(filename, dtype=dtype, low_memory=False))
        else:
            dataframe_loads_list.append(pd.read_csv(filename, dtype=dtype, usecols = usecols, low_memory=False))
    df = pd.concat(dataframe_loads_list)
    print("Files loaded into pandas dataframe.")
    return df
# do a quick check to make sure the CSV files are there
csv_files_list = sorted(glob.glob(os.path.join(download_file_path_CSV, "*.csv")))

gfy_list = set()
size_of_files_GB = 0
for n, filename in enumerate(csv_files_list):
    filename_basename = os.path.basename(filename)
    print(n+1, filename_basename)
    gfy_list.add(filename_basename[:6])
    size_of_files_GB += os.path.getsize(filename)

all_GFY_list = sorted(set([Get_GFY_from_file_path(filename) for filename in csv_files_list]))
    
print()
print(f"There are {len(csv_files_list)} CSV files. They use {round(size_of_files_GB/1e9, 3)} GB of storage.")
print(f"Covering GFY: {sorted(gfy_list)}")
print(f"Current GFY: {'FY' + str(CurrentGFY())}")
print()<jupyter_output>1 FY2010_All_Contracts_Full_20200807_1.csv
2 FY2010_All_Contracts_Full_20200807_2.csv
3 FY2010_All_Contracts_Full_20200807_3.csv
4 FY2010_All_Contracts_Full_20200807_4.csv
5 FY2011_All_Contracts_Full_20200807_1.csv
6 FY2011_All_Contracts_Full_20200807_2.csv
7 FY2011_All_Contracts_Full_20200807_3.csv
8 FY2011_All_Contracts_Full_20200807_4.csv
9 FY2012_All_Contracts_Full_20200807_1.csv
10 FY2012_All_Contracts_Full_20200807_2.csv
11 FY2012_All_Contracts_Full_20200807_3.csv
12 FY2012_All_Contracts_Full_20200807_4.csv
13 FY2013_All_Contracts_Full_20200807_1.csv
14 FY2013_All_Contracts_Full_20200807_2.csv
15 FY2013_All_Contracts_Full_20200807_3.csv
16 FY2014_All_Contracts_Full_20200808_1.csv
17 FY2014_All_Contracts_Full_20200808_2.csv
18 FY2014_All_Contracts_Full_20200808_3.csv
19 FY2015_All_Contracts_Full_20200808_1.csv
20 FY2015_All_Contracts_Full_20200808_2.csv
21 FY2015_All_Contracts_Full_20200808_3.csv
22 FY2015_All_Contracts_Full_20200808_4.csv
23 FY2015_All_Contracts_Full_20200808_5.c[...]<jupyter_text>Load GFY2010-GFY2019 with limited Columns even though we will mostly use GFY19 (demonstrate usecols feature)<jupyter_code># You can limit the selection to a subset of the GFY to speed processing

ALL_GFY = False # switch to True to override the subset

user_select_GFY = ['FY2019', 'FY2018', 'FY2017', 'FY2016', 'FY2015', 'FY2014', 'FY2013', 'FY2012', 'FY2011', 'FY2010']

if ALL_GFY:
    user_select_GFY = all_GFY_list
    
# print(f"GFY to read for analysis: {sorted(user_select_GFY)}")
# print()

# Create csv_files_list_restricted to streamline downstream data processing

csv_files_list = sorted(glob.glob(os.path.join(download_file_path_CSV, "*.csv")))
csv_files_list_restricted = [filename for filename in csv_files_list for GFY in user_select_GFY if GFY in filename]
gfy_found_in_files = [Get_GFY_from_file_path(filename) for filename in csv_files_list_restricted]
csv_GFY_missing = sorted(set(user_select_GFY) - set(gfy_found_in_files))

assert set(user_select_GFY) == set(user_select_GFY) # check to make sure this is working

print(f"You have selected these GFY for analysis:{user_select_GFY}")
print(f"Missing GFY: {csv_GFY_missing} -> if GFY missing, confirm you have decompressed the GFY zip file archive")

#[(os.path.basename(filename), round(os.path.getsize(filename)/1e9, 3)) for filename in csv_files_list_restricted]
%%time
# Dask uses pandas but operates in a 'lazy' mode by constructing a graph of operations to perform
# unlike pandas which immediately processes the data in memory, Dask requires you to add a '.compute()'
# to specify that you want to compute the values at that point. Dask then begins processing the files on disk.

dtype = Build_DTypes_Dict(csv_files_list_restricted) # this handles reduces ambiguity for dask interpreting data type to infer on read<jupyter_output>CPU times: user 29.2 ms, sys: 5.23 ms, total: 34.4 ms
Wall time: 30.1 ms<jupyter_text>Since we only need a few fields from the ~280 fields, we can radically reduce the memory needed and use pandas<jupyter_code>fields = sorted(dtype.keys())
# we do not need all of the 280+ fields from the records
# usecols = in pd.read_csv and Dask allows one to be selective on the fields and save memory
[column for column in fields if 'highly_compensated' in column]
usecols = ['action_date_fiscal_year',
           'recipient_parent_name',
           'recipient_name',
           'federal_action_obligation',
           'funding_agency_name',
           'funding_sub_agency_name',
           'product_or_service_code_description',
           'product_or_service_code',
           'highly_compensated_officer_1_amount',
 'highly_compensated_officer_1_name',
 'highly_compensated_officer_2_amount',
 'highly_compensated_officer_2_name',
 'highly_compensated_officer_3_amount',
 'highly_compensated_officer_3_name',
 'highly_compensated_officer_4_amount',
 'highly_compensated_officer_4_name',
 'highly_compensated_officer_5_amount',
 'highly_compensated_officer_5_name'
          ]
%%time
df = Load_CSV_Files_Into_DF(csv_files_list_restricted, usecols = usecols)
print(f"pandas DataFrame memory usage: {df.memory_usage().sum()/1e9} GB")
## Dask related routines for those w/o enough RAM
# def Close_Dask_Client_Robust():
#     global client
#     try:
#         client
#     except NameError:
#         client = None

#     if client:
#         client.close() # you can use this command to close the Dask processes and then repeat the command above to launch it again

# global client

# Close_Dask_Client_Robust()

# # psutil.cpu_count(logical=True)/2 => 1/2 of 'logical' cores available
# dask_workers = int(psutil.cpu_count(logical=True)/2) # use half of the available cores to be safe
# client = Client(n_workers=dask_workers) # if you have an NVIDIA card and CUDA installed you can take advantage of that here

# client # open the Dashboard link in a separate browser window that you can observe during the computations
# # for pandas DataFrames it is typical to see df and for Dask DataFrames ddf
# # note that dask operates above the pandas level and depends on pandas making it easy to 
# #    convert ddf files into pandas df if there is enough memory on your machine
# # Working with pandas df in memory is super fast and ideal.
# # If you can reduce the data to a smaller set that fits in RAM, you should do that early and use pandas for analysis
# # See https://docs.dask.org/en/latest/best-practices.html for more advice on streamlining your computations
# # here is a great overview of the advantages of Dask (vs PySpark) - https://www.youtube.com/watch?v=tx7qTHSlHKw presentation by https://www.linkedin.com/in/vaibhavs10/?originalSubdomain=in of Deloitte


# ddf = dd.read_csv(csv_files_list_restricted, dtype=dtype) #!!! note the .compute() which executes the task in a distributed way. If it fits in memory it will operate as a pandas dataframe.

# # Clean up the Data and add some fields
# ddf = Enhance_Spending_File(ddf)

# ddf.head()
df = Enhance_Spending_File(df)
df.head()
df.columns
### If you launch Dask computations but want to stop it, select the square jupyter button "interrupt the kernel"

##This can be used as a QC check to make sure the GFY requested were read
##this is a slow computation since Dask must read every record in all of the files
# action_date_fiscal_yearS = set(sorted(ddf['action_date_fiscal_year'].unique()))
# assert action_date_fiscal_yearS == user_select_GFY

# Without the command .compute() at the end of the next statement, Dask adds the task to a graph of operations.
# when Dask encounters a .compute(), the preceding graph operations are performed

df_GFY_obligations = df.groupby(['action_date_fiscal_year'])['federal_action_obligation'].sum().reset_index()

# You can check the computations against these articles:
# https://about.bgov.com/news/federal-contract-spending-five-trends-in-five-charts/
df_GFY_obligations
df_GFY_obligations.plot(x='action_date_fiscal_year', y='federal_action_obligation',title="Federal Contractor Obligations $s",grid=True)
df.groupby(['action_date_fiscal_year'])['federal_action_obligation'].count().plot(title='Obligation Transactions Count', grid=True)
%%time
df_firm_obs = df.groupby(['recipient_parent_name', 'action_date_fiscal_year'])['federal_action_obligation'].sum().reset_index()
df_firm_obs.head()
df_recipient_per_GFY = df_firm_obs.groupby(['action_date_fiscal_year'])['recipient_parent_name'].count().reset_index()
df_recipient_per_GFY
df_recipient_per_GFY.plot(x='action_date_fiscal_year', y='recipient_parent_name', title='Unique recipient_parent_name Counts',grid=True)<jupyter_output><empty_output><jupyter_text>Explore 'Highly Compendated Officer' Data<jupyter_code>df.columns
df[df['highly_compensated_officer_1_amount'] == 0.0]['highly_compensated_officer_1_amount'].count()/df['highly_compensated_officer_1_amount'].count()
#df[df['highly_compensated_officer_1_amount'] < 1e5]['highly_compensated_officer_1_amount'].count()/df['highly_compensated_officer_1_amount'].count()
for exec_n in range(1,5):
    numerator_field_name = 'highly_compensated_officer_' + str(exec_n+1) + '_amount'
    new_field_name = 'highly_compensated_officer_1_multiple_vs_officer_n_' + str(exec_n+1)
    df[new_field_name] = df['highly_compensated_officer_1_amount']/df[numerator_field_name]
## Note: the count field is the number of records; NOT the number of unique firms
df.groupby(['action_date_fiscal_year'])['highly_compensated_officer_1_amount'].describe()
# note addition of , showfliers=False to remove outliers - https://matplotlib.org/3.3.2/api/_as_gen/matplotlib.pyplot.boxplot.html
df[(df['highly_compensated_officer_1_amount'] > 0.0)].boxplot(column = 'highly_compensated_officer_1_amount', by = 'action_date_fiscal_year', showfliers=False)
# Identify the most common (aka mode) compensation figure for the executive_1 amount
def Get_Mode(compensation_list):
    counts = Counter(compensation_list)
    return counts.most_common(1)[0][0]

query = "action_date_fiscal_year == '2019' and highly_compensated_officer_1_amount > 0.0"
df_exec_1_mode = df.query(query).groupby(['recipient_parent_name'])['highly_compensated_officer_1_amount'].apply(list).apply(Get_Mode).reset_index()

print(f"There are {df_exec_1_mode.shape[0]} firms in GFY2019 with executive 1 compensation reported.")
fraction_of_GFY19_with_exec_1_compensation = df_exec_1_mode.shape[0]/df.query("action_date_fiscal_year == '2019'")['recipient_parent_name'].unique().shape[0]
print(f"Fraction of GFY19 recipient_parent_firms with executive 1 compensation: {fraction_of_GFY19_with_exec_1_compensation}")
fraction_of_GFY19_obligations_actions_with_exec_1_compensation =df.query(query).shape[0]/df.query("action_date_fiscal_year == '2019'").shape[0]
print(f"Fraction of GFY19 obligation transactions with executive 1 compensation: {fraction_of_GFY19_obligations_actions_with_exec_1_compensation}")<jupyter_output>There are 2154 firms in GFY2019 with executive 1 compensation reported.
Fraction of GFY19 recipient_parent_firms with executive 1 compensation: 0.01878302725893371
Fraction of GFY19 obligation transactions with executive 1 compensation: 0.10382345535710288<jupyter_text>Caveats: There are errors in the dataCompanies self report this information. There are data entry errors and likely some rounding of values<jupyter_code># Example case of the highest compensation listed in the data
df_exec_1_mode[df_exec_1_mode['highly_compensated_officer_1_amount'] == df_exec_1_mode['highly_compensated_officer_1_amount'].max()]
# it is a publicly traded company so one can look up the compensation in SEC reporting
company_name = df_exec_1_mode[df_exec_1_mode['highly_compensated_officer_1_amount'] == df_exec_1_mode['highly_compensated_officer_1_amount'].max()]['recipient_parent_name'].iloc[0]

df[(df['recipient_parent_name'] == company_name) & (df['highly_compensated_officer_1_amount'] > 0.0)]['highly_compensated_officer_1_amount']
# The 2018 SEC filing shows the CEO compensation for 2017 closer to $17m (1.7e7)
# SEC document - https://www.sec.gov/Archives/edgar/data/1390777/000119312519069151/d638623ddef14a.htm#toc638623_12
# The 1.9e9 is clearly a typo.
# Also, many of the entries showed no data for the compensation.<jupyter_output><empty_output><jupyter_text>Select subset of Firms with work in PSC_Cat D and R with Exec_1 between 100k and 10M in compensation<jupyter_code># Which companies have work in PSC_Cat D (IT and Telecom) and R (Professional Services)?
recipient_parent_names_with_PSC_Cat_D_R_obligations = df[df['PSC_Cat'].isin(['D','R'])]['recipient_parent_name'].unique()
selector = (df['highly_compensated_officer_1_amount'] > 1e5) & (df['highly_compensated_officer_1_amount'] < 10e6) & (df['recipient_parent_name'].isin(recipient_parent_names_with_PSC_Cat_D_R_obligations))
df_study_subset = df[selector]
df_study_subset.groupby(['action_date_fiscal_year'])['highly_compensated_officer_1_amount'].describe()
df_study_subset.groupby(['action_date_fiscal_year'])['highly_compensated_officer_1_amount'].describe()['count'].plot(grid=True)
df_study_subset_GFY19 = df_study_subset[df_study_subset['action_date_fiscal_year'] == '2019']
df_study_subset_GFY19.shape
df_study_subset_GFY19['recipient_parent_name'].unique().shape
print(f"Our subset has {df_study_subset_GFY19['recipient_parent_name'].unique().shape[0]} parent firms of the {df_exec_1_mode.shape[0]} total firms that reported executive 1 compensation in GFY19.")
# note addition of , showfliers=False to remove outliers - https://matplotlib.org/3.3.2/api/_as_gen/matplotlib.pyplot.boxplot.html
df_study_subset_GFY19.boxplot(column = 'highly_compensated_officer_1_amount', by = 'action_date_fiscal_year', showfliers=False)
df_study_subset_GFY19.groupby(['recipient_parent_name'])['federal_action_obligation'].sum().reset_index().describe()
# df_study_subset_GFY19_recipient_obs = df_study_subset_GFY19.groupby(['recipient_parent_name'])['federal_action_obligation'].sum().reset_index().sort_values(by='federal_action_obligation', ascending=False)
# df_study_subset_GFY19_recipient_obs
df_study_subset_GFY19_exec1 = df_study_subset_GFY19.groupby(['recipient_parent_name'])['highly_compensated_officer_1_amount'].describe().sort_values(by='mean', ascending=False).reset_index()
#Scramble_Company_Exec_Names(df_study_subset_GFY19_exec1)

df_study_subset_GFY19_compensation_obs = df_study_subset_GFY19_recipient_obs.merge(df_study_subset_GFY19_exec1, on='recipient_parent_name').reset_index()
Scramble_Company_Exec_Names(df_study_subset_GFY19_compensation_obs)
df_study_subset_GFY19_compensation_obs.plot.scatter(x='federal_action_obligation', y='mean', grid=True)
plt.ylabel("Annual Compensation")
plt.ticklabel_format(axis="y", style="sci", scilimits=(0,0))
selector = (df_study_subset_GFY19_compensation_obs['federal_action_obligation'] > 25e6) & (df_study_subset_GFY19_compensation_obs['federal_action_obligation'] < 2e9)
df_study_subset_GFY19_compensation_obs[selector].plot.scatter(x='federal_action_obligation', y='mean', grid=True, rot=45)
plt.ylabel("Annual Compensation")
plt.ticklabel_format(axis="y", style="sci", scilimits=(0,0))
selector = (df_study_subset_GFY19_compensation_obs['federal_action_obligation'] > 25e6) & (df_study_subset_GFY19_compensation_obs['federal_action_obligation'] < 150e6)
df_study_subset_GFY19_compensation_obs[selector].plot.scatter(x='federal_action_obligation', y='mean', grid=True, rot=45)
plt.ylabel("Annual Compensation")
plt.ticklabel_format(axis="y", style="sci", scilimits=(0,0))
df_study_subset_GFY19_exec1['mean'].plot(title="Distribution of Executive 1 Comp", grid=True, rot=45)
plt.ylabel("Annual Compensation")
plt.ticklabel_format(axis="y", style="sci", scilimits=(0,0))
#axes = plt.axes()
#axes#.get_xaxis().set_visible(False) #https://stackoverflow.com/questions/40705614/hide-axis-label-only-not-entire-axis-in-pandas-plot<jupyter_output><empty_output><jupyter_text>Executive_1 Compensation as a Fraction of Total Federal Obligations<jupyter_code>df_study_subset_GFY19_compensation_obs['exec_1_comp_fraction_obligations'] = df_study_subset_GFY19_compensation_obs['mean']/df_study_subset_GFY19_compensation_obs['federal_action_obligation']
selector = (df_study_subset_GFY19_compensation_obs['exec_1_comp_fraction_obligations'] > 0.0) & (~df_study_subset_GFY19_compensation_obs['exec_1_comp_fraction_obligations'].isin([np.inf]))
df_study_subset_GFY19_compensation_obs[selector]['exec_1_comp_fraction_obligations'] #.hist()

# Which have exec_1 compensation 10% or higher of total prime obligations?
# some of these may be coding errors and others may be companies with a small fraction of revenue from USApsneding.gov-reported Federal obligations
selector = (df_study_subset_GFY19_compensation_obs['exec_1_comp_fraction_obligations'] > 0.1) & (~df_study_subset_GFY19_compensation_obs['exec_1_comp_fraction_obligations'].isin([np.inf]))
Scramble_Company_Exec_Names(df_study_subset_GFY19_compensation_obs[selector])[['recipient_parent_name', 'federal_action_obligation', 'exec_1_comp_fraction_obligations']]
#df_study_subset_GFY19[~df_study_subset_GFY19['highly_compensated_officer_1_multiple_vs_officer_n_2'].isin([np.nan, np.inf, -np.inf])]['highly_compensated_officer_1_multiple_vs_officer_n_2'].describe()
df_temp = df_study_subset_GFY19.groupby(['recipient_parent_name','highly_compensated_officer_1_name'])['highly_compensated_officer_1_amount'].mean().sort_values(ascending=False).reset_index()
Scramble_Company_Exec_Names(df_temp).head(10)<jupyter_output><empty_output><jupyter_text>Explore the relative compensation of the top 5 executives<jupyter_code># note addition of , showfliers=False to remove outliers - https://matplotlib.org/3.3.2/api/_as_gen/matplotlib.pyplot.boxplot.html
boxplot_columns = ['highly_compensated_officer_' + str(n) + '_amount' for n in range(1,6)]
df_study_subset_GFY19.boxplot(column = boxplot_columns,  showfliers=False, rot=80)
df_study_relative_comp = df_study_subset_GFY19[['recipient_parent_name'] + boxplot_columns].drop_duplicates()
exec_multiple_fields = []
for n in range(2,6):
    field_name = 'highly_compensated_officer_' + str(n) + '_amount'
    new_field = 'exec1_multiple_exec_' + str(n)
    exec_multiple_fields.append(new_field)
    df_study_relative_comp[new_field] = df_study_relative_comp['highly_compensated_officer_1_amount']/df_study_relative_comp[field_name]
df_study_relative_comp.boxplot(column = exec_multiple_fields, showfliers=False, rot=45)
bins = list(range(100000, 3000000, 10000))
df_study_relative_comp['highly_compensated_officer_1_amount'].hist(bins=bins)
# $1m/year or greater
selector = (df_study_relative_comp['highly_compensated_officer_1_amount'] >= 1e6)
df_study_relative_comp[selector]['recipient_parent_name'].unique().shape[0]
# Between $500k/year-$1M/year
selector = (df_study_relative_comp['highly_compensated_officer_1_amount'] >= 0.5e6) & ((df_study_relative_comp['highly_compensated_officer_1_amount'] < 1e6))
df_study_relative_comp[selector]['recipient_parent_name'].unique().shape[0]
# Between less than $500k/year
selector = (df_study_relative_comp['highly_compensated_officer_1_amount'] < 0.5e6)
df_study_relative_comp[selector]['recipient_parent_name'].unique().shape[0]
df_study_relative_comp['recipient_parent_name'].unique().shape[0]<jupyter_output><empty_output><jupyter_text>Assuming you can clean up the data sufficiently and the business is mostly Federal, can an analyst use the Top 5 compensation data to estimate profit margins of your competitors?  Analysis End<jupyter_code># https://opensource.org/licenses/MIT

# MIT Open Source License
# Copyright 2020 <NAME>

# Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

# The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0957371876144592, 'fraction_numerical': 0.027611078240779872, 'mean_word_length': 5.507884121745508, 'pattern_counts': {'":': 0, '<': 44, '<?xml version=': 0, '>': 59, 'https://': 19, 'lorem ipsum': 0, 'www.': 5, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 14, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '376584', 'license': 'MIT', 'n_tokens_mistral': 12474, 'n_tokens_neox': 11488, 'n_words': 2636, 'path': 'Blog06_Relationship_Between_Executive_Compensation_Obligations.ipynb', 'repo_name': 'leifulstrup/USAspending_Medium_Blog_Analystics_Series', 'size': '35503'}