# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# ## This Notebook Explore Time Series Analytics and Predictions

# #### USAspending.gov GFY Archive Data Source

# +
# recommend creating a conda env for this to use latest version of Prophet
# it should include install of:
# fbprophet (v.7 or newer), dask, pyarrow, fastparquet, plotly, jupyter, ipyfilechooser, ipywidgets (https://ipywidgets.readthedocs.io/en/stable/user_install.html )

# +
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 scipy.stats as st
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('seaborn')


# +
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()


# +
def CurrentGFY():
    if datetime.now().month >= 10: 
        return datetime.now().year + 1
    else:
        return datetime.now().year
    
def getGFY(datestamp):
    if datestamp.month >= 10: 
        return datestamp.year + 1
    else:
        return datestamp.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)
    
    if 'highly_compensated' in df.columns:
        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


# +
# 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:])))

# +
# 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()

# +
## Load GFY2010-GFY2019 with limited Columns even though we will mostly use GFY19 (demonstrate usecols feature)

# 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 = ['FY2020','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]
# -

# ### Select a subset of fields we will use for forecasting

# +
# %%time

dtype = Build_DTypes_Dict(csv_files_list_restricted) # this handles reduces ambiguity for dask interpreting data type to infer on read

### Since we only need a few fields from the ~280 fields, we can radically reduce the memory needed and use pandas

fields = sorted(dtype.keys())

# +
#fields

# +
# 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

usecols = ['action_date',
           'action_date_fiscal_year',
           'recipient_parent_name',
           'recipient_name',
           'federal_action_obligation',
           'funding_agency_name',
           'funding_sub_agency_name',
           'funding_office_name',
           'product_or_service_code_description',
           'product_or_service_code',
          ]

# +
# %%time

USAspending_parquet_file_name = "USAspending_GFY2010_GFY2020ytd_Time_Series_Analytics.parquet"

if glob.glob(USAspending_parquet_file_name): #if this file read has already happened, read from parquet file
    print(f"Reading parquet file: {USAspending_parquet_file_name} instead of CSV file sources.")
    df = pd.read_parquet(USAspending_parquet_file_name)
else:
    df = Load_CSV_Files_Into_DF(csv_files_list_restricted, usecols = usecols)
    df = Enhance_Spending_File(df)
    # Save the data in a parquet file to restart below if needed
    print(f"Saving the data to a parquet file: {USAspending_parquet_file_name} in local directory.")
    df.to_parquet(USAspending_parquet_file_name)
    #df.to_csv("USAspending_GFY2010_GFY2020ytd_Time_Series_Analytics_Fields_Subset.csv", index=False)

df = df.reset_index(drop=True) # creating a new index is key here for later index searches - duplicates otherwise

df.head()
# -

df.columns

assert set(df.columns) - set(usecols) == {'PSC_Cat', 'PSC_Cat_2'} # make sure we retrieved the fields we expected

assert df.shape[0] == len(set(df.index)) #confirm we have created unique index values after append since CSV read will have duplicate index numbers

print(f"There are ~{int(df.shape[0]/1e6)} million transactions in the dataset.")

print(f"pandas DataFrame memory usage: ~{round(df.memory_usage().sum()/1e9,1)} GB")

# +
# %time
### 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[:-1].plot(x='action_date_fiscal_year', y='federal_action_obligation',title="Federal Contractor Obligations $s",grid=True)

# ### Collect Information about the Federal Product or Service Code Categories

# +
# retrieve the PSC Codes for reference and reporting purposes
# source my colab notebook and github code - https://github.com/leifulstrup/USAspending_Medium_Blog_Analytics_Series/blob/master/Medium_Analytics_Edge_Open_Data_CMS_Fraud_Contracts_Example-via_Colab.ipynb

url_psc_lookup = 'https://www.acquisition.gov/sites/default/files/manual/PSC_Data_March_2020_Edition.xls' # latest version when coded - may have bene updated - see here https://www.acquisition.gov/psc-manual

# def Cleanup_Category_Text(text): #fixes the typos in the source excel document
#   text = text.strip() # remove preceding and trailing spaces
#   clean_list = []
#   for token in text.split(" "):
#     if token[0] != '&' and not token[1] in ['&', 'T']:
#       clean_list.append(token.capitalize())
#     else:
#       clean_list.append(token)
#   return " ".join(clean_list)

# download the file from the website to read into pandas
r = requests.get(url_psc_lookup, allow_redirects=True)
file_name = "Federal_PSC_Lookup_Codes.xlsx"
with open(file_name, 'wb') as output:
  output.write(r.content)

# # !ls -al

xls = pd.ExcelFile(file_name)
df_psc_lookup = pd.read_excel(xls, sheet_name = 1, skiprows=0) #note that there are Excel Sheets for each of the Level 1 Cats with more details
df_psc_lookup.head()
df_PSC_Cat_Lookup = df_psc_lookup[df_psc_lookup['PSC CODE'].str.len() <= 2][['PSC CODE', 'PRODUCT AND SERVICE CODE NAME']].drop_duplicates()
df_PSC_Cat_Lookup.columns = ['PSC_Code', 'Product_and_Service_Code_Name']
df_PSC_Cat_Lookup

# +
df_PSC_Cat_Lookup['PSC_Cat'] = df_PSC_Cat_Lookup['PSC_Code'].str[0]
df_PSC_Cat_Lookup['PSC_Cat_2'] = df_PSC_Cat_Lookup['PSC_Code'].str[:2]
df_PSC_Cat_Lookup

df_PSC_Cat_numerical_codes = df_PSC_Cat_Lookup[~df_PSC_Cat_Lookup['PSC_Cat'].apply(lambda x: x.isalpha())].groupby(["PSC_Cat"])['Product_and_Service_Code_Name'].apply(lambda x: set(x)).apply(sorted).reset_index()
df_PSC_Cat_numerical_codes
# -

df_PSC_Cat_text_codes = df_PSC_Cat_Lookup[df_PSC_Cat_Lookup['PSC_Code'].apply(len) == 1][['PSC_Cat', 'Product_and_Service_Code_Name']].reset_index(drop=True)
df_PSC_Cat_text_codes

df_PSC_lookup_limited = pd.concat([df_PSC_Cat_numerical_codes, df_PSC_Cat_text_codes], axis=0, sort=False)
df_PSC_lookup_limited

df_PSC_Cat_Lookup = df_PSC_Cat_Lookup.merge(df_PSC_lookup_limited, on='PSC_Cat')
df_PSC_Cat_Lookup

df_PSC_Cat_Lookup =df_PSC_Cat_Lookup.rename(columns={'Product_and_Service_Code_Name_x' : 'Product_and_Service_Code_Name',
                                  'Product_and_Service_Code_Name_y' : 'Product_and_Service_Code_Name_PSC_Cat',
                                 })
df_PSC_Cat_Lookup

df_PSC_Cat_Lookup['Product_and_Service_Code_Name_PSC_Cat'] = df_PSC_Cat_Lookup['Product_and_Service_Code_Name_PSC_Cat'].apply(lambda x: x if type(x) == str else "+".join(x)[:40])
df_PSC_Cat_Lookup_PSC_Cat_only = df_PSC_Cat_Lookup[['PSC_Cat', 'Product_and_Service_Code_Name_PSC_Cat']].drop_duplicates()
df_PSC_Cat_Lookup_PSC_Cat_only['Product_and_Service_Code_Name_PSC_Cat'] = df_PSC_Cat_Lookup_PSC_Cat_only.apply(lambda x: x['PSC_Cat'] + " - " + x['Product_and_Service_Code_Name_PSC_Cat'], axis=1)
df_PSC_Cat_Lookup_PSC_Cat_only

# ## Federal Product or Service Category (PSC_Cat) Trends Analysis

df.head()

# +
# %%time
df_PSC_Cat_Trends = df.groupby(['action_date_fiscal_year', 'PSC_Cat'])['federal_action_obligation'].sum().reset_index().sort_values(by='federal_action_obligation', ascending=False)

df_PSC_Cat_Trends = df_PSC_Cat_Trends.merge(df_GFY_obligations, on='action_date_fiscal_year')
df_PSC_Cat_Trends['Fraction_of_Total_Annual_Spending'] = df_PSC_Cat_Trends['federal_action_obligation_x']/df_PSC_Cat_Trends['federal_action_obligation_y']
df_PSC_Cat_Trends.drop(columns=['federal_action_obligation_y'], inplace=True)
df_PSC_Cat_Trends.rename(columns={'federal_action_obligation_x':'federal_action_obligation'}, inplace=True)
df_PSC_Cat_Trends.head()
# -

df_PSC_Cat_Trends = df_PSC_Cat_Trends.merge(df_PSC_Cat_Lookup_PSC_Cat_only, on='PSC_Cat')
df_PSC_Cat_Trends

# +
#df_PSC_Cat_Trends.query("action_date_fiscal_year == '2019'")['Fraction_of_Total_Annual_Spending'].cumsum()
# -

sorted(df_PSC_Cat_Trends['Product_and_Service_Code_Name_PSC_Cat'].unique())

# +
fig = plt.figure(figsize=(12, 6)) # Create matplotlib figure - source - https://stackoverflow.com/questions/24183101/pandas-bar-plot-with-two-bars-and-two-y-axis

ax = fig.add_subplot(111) # Create matplotlib axes

width = 0.1
title = "Product and Services Spending (Top 10) Relative Size"
graph1 = df_PSC_Cat_Trends.query("action_date_fiscal_year == '2010'").iloc[:10].sort_values(by='Fraction_of_Total_Annual_Spending', ascending=False).plot(kind="bar", ax=ax,title=title, x='Product_and_Service_Code_Name_PSC_Cat', y='Fraction_of_Total_Annual_Spending', color="red", width=width, align='edge', rot=80, position=2)
graph2 = df_PSC_Cat_Trends.query("action_date_fiscal_year == '2015'").iloc[:10].sort_values(by='Fraction_of_Total_Annual_Spending', ascending=False).plot(kind="bar", ax=ax,title=title, x='Product_and_Service_Code_Name_PSC_Cat', y='Fraction_of_Total_Annual_Spending', color="green", width=width, align='edge', rot=80, position=1)
graph3 = df_PSC_Cat_Trends.query("action_date_fiscal_year == '2019'").iloc[:10].sort_values(by='Fraction_of_Total_Annual_Spending', ascending=False).plot(kind="bar", ax=ax,title=title, x='Product_and_Service_Code_Name_PSC_Cat', y='Fraction_of_Total_Annual_Spending', color="blue", width=width, align='edge', rot=80, position=0)
ax.set_ylabel("Fraction of Total Annual Obligations")
ax.legend(["GFY2010", "GFY2015", "GFY2019"])
# -

psc_cats_all = sorted(df_PSC_Cat_Trends['PSC_Cat'].unique())
gfy_to_include = [str(value) for value in list(range(2010,2020))]
psc_cats_to_include = ['D - ADP AND TELECOMMUNICATIONS', 'R - SUPPORT SVCS (PROF, ADMIN, MGMT)']
#psc_cats_to_include= psc_cats_all
df_PSC_Cat_Trends_plotting_subset = df_PSC_Cat_Trends.query("action_date_fiscal_year in @gfy_to_include & Product_and_Service_Code_Name_PSC_Cat in @psc_cats_to_include")
df_PSC_Cat_Trends_plotting_subset = df_PSC_Cat_Trends_plotting_subset.pivot_table(index='action_date_fiscal_year', values='Fraction_of_Total_Annual_Spending', columns='Product_and_Service_Code_Name_PSC_Cat')
df_PSC_Cat_Trends_plotting_subset

# +
fig = plt.figure(figsize=(12, 8)) # Create matplotlib figure - source - https://stackoverflow.com/questions/24183101/pandas-bar-plot-with-two-bars-and-two-y-axis

ax = fig.add_subplot(111) # Create matplotlib axes
ax2 = ax.twinx() 

graph1 = df_PSC_Cat_Trends_plotting_subset.plot(title='Product or Service Category Budget Fraction Trends', ax=ax)
plt_result = graph1.set_ylabel("Fraction of GFY Spending on PSC_Cat")
plt_result = graph1.legend(loc='center')

graph2 = df_GFY_obligations[:-1].plot(color='black', x='action_date_fiscal_year', y='federal_action_obligation', ax=ax2, linewidth=3)
plt_result = graph2.set_ylabel("Total Contractor Obligations")
plt_result = graph2.legend(loc='upper right')

leg = ax.legend()
leg.remove()
plt_result = ax2.add_artist(leg)

# +
psc_cats_all = sorted(df_PSC_Cat_Trends['Product_and_Service_Code_Name_PSC_Cat'].unique())
gfy_to_include = [str(value) for value in list(range(2010,2020))]
#psc_cats_to_include = ['D - ADP AND TELECOMMUNICATIONS', 'R - SUPPORT SVCS (PROF, ADMIN, MGMT)']
psc_cats_to_include= psc_cats_all
df_PSC_Cat_Trends_plotting_subset = df_PSC_Cat_Trends.query("action_date_fiscal_year in @gfy_to_include & Product_and_Service_Code_Name_PSC_Cat in @psc_cats_to_include")
df_PSC_Cat_Trends_plotting_subset = df_PSC_Cat_Trends_plotting_subset.pivot_table(index='action_date_fiscal_year', values='Fraction_of_Total_Annual_Spending', columns='Product_and_Service_Code_Name_PSC_Cat')
df_PSC_Cat_Trends_plotting_subset_transpose = df_PSC_Cat_Trends_plotting_subset.transpose().reset_index()
#df_PSC_Cat_Trends_plotting_subset_transpose

gfy_columns = [str(value) for value in range(2010,2020)]
df_PSC_Cat_Trends_plotting_subset_transpose = df_PSC_Cat_Trends_plotting_subset_transpose.join(df_PSC_Cat_Trends_plotting_subset_transpose[gfy_columns].agg(['mean','std','max','min'], axis="columns"))
df_PSC_Cat_Trends_plotting_subset_transpose['CAGR_last_3_years'] = df_PSC_Cat_Trends_plotting_subset_transpose.apply(lambda x: -1 + (x['2019']/x['2016'])**(1/3),axis=1)
df_PSC_Cat_Trends_plotting_subset_transpose['2019_vs_2010_delta'] = df_PSC_Cat_Trends_plotting_subset_transpose['2019'] - df_PSC_Cat_Trends_plotting_subset_transpose['2010']
df_PSC_Cat_Trends_plotting_subset_transpose.set_index('Product_and_Service_Code_Name_PSC_Cat', inplace=True)
df_PSC_Cat_Trends_plotting_subset_transpose
# -

gfy_included_list
df_PSC_Cat_Trends_plotting_subset_transpose.sort_values(by='2019', ascending=False).transpose().loc[gfy_included_list]

gfy_included_list = [str(value) for value in range(2010, 2020)]
df_PSC_Cat_Trends_plotting_subset_transpose.sort_values(by='2019', ascending=False).transpose().loc[gfy_included_list]

# +
columns_to_include = df_PSC_Cat_Trends_plotting_subset_transpose.sort_values(by='2019', ascending=False).transpose().loc[gfy_included_list].columns[:6]

#df_PSC_Cat_Trends_plotting_subset_transpose.sort_values(by='2019', ascending=False).transpose().loc[gfy_included_list,columns_to_include]

fig = plt.figure(figsize=(12, 8)) # Create matplotlib figure - source - https://stackoverflow.com/questions/24183101/pandas-bar-plot-with-two-bars-and-two-y-axis

ax = fig.add_subplot(111) # Create matplotlib axes
ax2 = ax.twinx() 

graph1 = df_PSC_Cat_Trends_plotting_subset_transpose.sort_values(by='2019', ascending=False).transpose().loc[gfy_included_list,columns_to_include].plot(ax=ax) #plot(ax=ax, position=0)
plt_result = graph1.set_title("Product and Service Code Trends")
plt_result = graph1.set_ylabel("Fraction of Total Obligations")
plt_result = graph1.set_xlabel("Government Fiscal Year (GFY)")
plt_result = graph1.legend(fancybox=True, framealpha=1, shadow=True, borderpad=1)
plt_result = graph1.legend(loc='center')


graph2 = df_GFY_obligations[:-1].plot(color='black', x='action_date_fiscal_year', y='federal_action_obligation',grid=True, ax=ax2, linewidth=3)
plt_result = graph2.set_ylabel("Total Contractor Obligations")
plt_result = graph2.legend(loc='upper right')
# -

# ### Explore PSC_Cat D "ADP and Telecommunications" by Department, Agency, and Contractor

# +
# %%time
df_PSC_Cat_D = df.query("PSC_Cat == 'D'")
gfy_years = ['2016', '2017', '2018', '2019']
df_PSC_Cat_D = df_PSC_Cat_D.query("action_date_fiscal_year in @gfy_years")

assert set(df_PSC_Cat_D['action_date_fiscal_year'].unique()) == set(gfy_years)

df_PSC_Cat_D.head()
# -

df_PSC_Cat_D_by_gfy = df_PSC_Cat_D.groupby(["funding_agency_name", "action_date_fiscal_year"])['federal_action_obligation'].sum().fillna(0.0).reset_index()
df_PSC_Cat_D_by_gfy_pivot = df_PSC_Cat_D_by_gfy.pivot_table(index='funding_agency_name', columns='action_date_fiscal_year', values='federal_action_obligation').fillna(0.0).sort_values(by='2019', ascending=False) #.set_index('funding_agency_name')

df_PSC_Cat_D_by_gfy_pivot['CAGR_last_3_years'] = df_PSC_Cat_D_by_gfy_pivot.sort_values(by='2019', ascending=False).apply(lambda x: -1 + (x['2019']/x['2016'])**(1/3) if x['2016'] > 0.0 else 0.0, axis=1)

df_PSC_Cat_D_by_gfy_pivot.head(10)


# +
def cagr(v_final, value_initial, years):
    if v_final < 0.0 or value_initial < 0.0: return 0.0
    return -1 + (v_final/value_initial)**(1/years)

assert cagr(2,1,1) == 1.0
# -

# Federal Overall Spending CAGR from GFY16 to GFY19
cagr(df_GFY_obligations[df_GFY_obligations['action_date_fiscal_year'] == '2019']['federal_action_obligation'].iloc[0], df_GFY_obligations[df_GFY_obligations['action_date_fiscal_year'] == '2016']['federal_action_obligation'].iloc[0],3)

cagr_3_year = cagr(df_PSC_Cat_D_by_gfy_pivot['2019'].sum(), df_PSC_Cat_D_by_gfy_pivot['2016'].sum(), 3)
print(f"The three (3) year market CAGR: {round(cagr_3_year, 3)}")
df_PSC_Cat_D_by_gfy_pivot_DoD = df_PSC_Cat_D_by_gfy_pivot.query("funding_agency_name == 'DEPARTMENT OF DEFENSE (DOD)'")
df_PSC_Cat_D_by_gfy_pivot_Civilian = df_PSC_Cat_D_by_gfy_pivot.query("funding_agency_name != 'DEPARTMENT OF DEFENSE (DOD)'")
print(f"The three (3) year market CAGR - DOD: {round(cagr(df_PSC_Cat_D_by_gfy_pivot_DoD['2019'].sum(), df_PSC_Cat_D_by_gfy_pivot_DoD['2016'].sum(), 3), 3)}")
print(f"The three (3) year market CAGR - Civilian: {round(cagr(df_PSC_Cat_D_by_gfy_pivot_Civilian['2019'].sum(), df_PSC_Cat_D_by_gfy_pivot_Civilian['2016'].sum(), 3), 3)}")

selector = (df_PSC_Cat_D_by_gfy_pivot['CAGR_last_3_years'] >= cagr_3_year) & (df_PSC_Cat_D_by_gfy_pivot['2019'] >= 50e6)
df_PSC_Cat_D_by_gfy_pivot[selector].sort_values(by='CAGR_last_3_years', ascending=False)

selector = (df_PSC_Cat_D_by_gfy_pivot['CAGR_last_3_years'] < cagr_3_year) & (df_PSC_Cat_D_by_gfy_pivot['2019'] >= 50e6)
df_PSC_Cat_D_by_gfy_pivot[selector].sort_values(by='CAGR_last_3_years', ascending=True)

# ### Which Agencies (below Department level) have seen the greatest PSC_Cat 'D' spending increases?

df_PSC_Cat_D_by_agency = df_PSC_Cat_D.groupby(['action_date_fiscal_year', 'funding_agency_name', 'funding_sub_agency_name'])['federal_action_obligation'].sum().fillna(0.0).reset_index()
df_PSC_Cat_D_by_agency_pivot = df_PSC_Cat_D_by_agency.pivot_table(index=['funding_agency_name','funding_sub_agency_name'], columns='action_date_fiscal_year', values='federal_action_obligation').fillna(0.0).reset_index()
df_PSC_Cat_D_by_agency_pivot['2019_increase_vs_2016'] = df_PSC_Cat_D_by_agency_pivot['2019'] - df_PSC_Cat_D_by_agency_pivot['2016']
df_PSC_Cat_D_by_agency_pivot.sort_values(by='2019_increase_vs_2016', ascending=False).head(10)

# +
# the increase in VA PSC_Cat D spending 1.311045e+09 from GFY16-GFY19 is greater than how many Depts

df_PSC_Cat_D_by_gfy_pivot[df_PSC_Cat_D_by_gfy_pivot['2019'] > 1.311045e+09] #.shape[0]
# -

df_PSC_Cat_D_by_agency_pivot.sort_values(by='2019_increase_vs_2016', ascending=True).head(10)

# ### Which Contractors Have Seen the Greatest Growth and Declines in PSC_Cat 'D' Spending?

df_PSC_Cat_D_by_recipient = df_PSC_Cat_D.groupby(['action_date_fiscal_year', 'recipient_parent_name'])['federal_action_obligation'].sum().reset_index().sort_values(by='federal_action_obligation', ascending=False)
df_PSC_Cat_D_by_recipient.head(10)

# +
df_PSC_Cat_D_by_recipient_pivot = df_PSC_Cat_D_by_recipient.pivot_table(index='recipient_parent_name', columns='action_date_fiscal_year', values='federal_action_obligation').fillna(0.0).sort_values(by='2019', ascending=False).reset_index()

df_PSC_Cat_D_by_recipient_pivot['CAGR_last_3_years'] = df_PSC_Cat_D_by_recipient_pivot.apply(lambda x: cagr(x['2019'], x['2016'], 3) if x['2016'] > 0.0 else 0.0, axis=1)
df_PSC_Cat_D_by_recipient_pivot['2019_net_increase_vs_2016'] = df_PSC_Cat_D_by_recipient_pivot['2019'] - df_PSC_Cat_D_by_recipient_pivot['2016']
df_PSC_Cat_D_by_recipient_pivot.head(10)
# -

cagr_3_year_all_recipients = cagr(df_PSC_Cat_D_by_recipient_pivot['2019'].sum(), df_PSC_Cat_D_by_recipient_pivot['2016'].sum(), 3)
print(f"The three (3) year CAGR for recipients: {round(cagr_3_year_all_recipients,3)}")

selector = (df_PSC_Cat_D_by_recipient_pivot['CAGR_last_3_years'] >= cagr_3_year_all_recipients) & \
(df_PSC_Cat_D_by_recipient_pivot['2019'] >= 50e6)
df_PSC_Cat_D_by_recipient_pivot[selector].sort_values(by='CAGR_last_3_years', ascending=False).head(20)

df_PSC_Cat_D_by_recipient_pivot.sort_values(by='2019_net_increase_vs_2016', ascending=False).head(10)

selector = (df_PSC_Cat_D_by_recipient_pivot['CAGR_last_3_years'] >= cagr_3_year_all_recipients) & \
(df_PSC_Cat_D_by_recipient_pivot['2019'] >= 350e6)
df_PSC_Cat_D_by_recipient_pivot[selector].sort_values(by='CAGR_last_3_years', ascending=False)

# +
# selector = (df_PSC_Cat_D_by_recipient_pivot['CAGR_last_3_years'] < cagr_3_year_all_recipients) & (df_PSC_Cat_D_by_recipient_pivot['2019'] >= 350e6)
# df_PSC_Cat_D_by_recipient_pivot[selector].sort_values(by='CAGR_last_3_years', ascending=True)
# -

# ### End

# #### 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.

#

StarcoderdataJupyterScriptsDedupFiltered