*Note: The city of Chicago buys goods and services from the local vendors. The actual purchases are made by the various Departments within the city government.
1. Objectives of This Analysis¶
First, I decided to look at the data from the point of view of an investigator looking for corrupt purchasing behavior. The results from this analysis are in Section 3.
In Section 3.1, I looked at the data from a political point of view, where I analyzed total payment amounts for all years, departments and vendors and ckecked if there is any pattern for changes since Rahm Emanuel took office in 2011.
- My purpose in Section 3.2 was finding vendors that received the highest amount of US dollars from departments.
- Section 3.3 includes analysis for vendors that were receiving payments through contract or direct vendors (no contract or paid by cash).
- Because payments from 1996 through 2002 have been rolled-up and appear as "2002" in the data, I devided the data set into two data sets and investigated them separately as before 2002, and after 2002. Then, I drilled it down to analyze the total dollars paid by department for every vendor for the years:
1) 1996-2002
2) 2002-2018
2. Reading In and Checking the Quality of the Payments Data¶
import numpy as np
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
from matplotlib.pyplot import figure
%matplotlib inline
import warnings
warnings.filterwarnings('ignore')
import datetime
from matplotlib.ticker import FuncFormatter
payments = pd.read_csv('Payments.csv', low_memory=False)
payments.head()
payments.info()
payments.shape
ax = sns.heatmap(payments.isnull(),yticklabels=False,cbar=False,cmap='viridis')
ax.set_title("Missing Data Values for the Payments DataSet")
Almost 85% of the department_name column's values is lost.¶
sum_missing = payments.isnull().sum()
percent_missing = payments.isnull().sum() * 100 / len(payments)
missing_value_df = pd.DataFrame({'column_name': payments.columns,
'total_missing':sum_missing,
'percent_missing': percent_missing})
missing_value_df
payments.drop("VOUCHER NUMBER", axis=1, inplace=True)
payments.rename(columns={'AMOUNT':'amount', 'CHECK DATE':'check_date',
'CONTRACT NUMBER':'contract_number',
'DEPARTMENT NAME':'department_name',
'VENDOR NAME':'vendor_name','CASHED':'cashed'},
inplace=True)
payments["check_date"] = pd.to_datetime(payments["check_date"])
payments['year'] = payments["check_date"].dt.year
payments['month'] = payments["check_date"].dt.month
payments['weekday'] = payments["check_date"].dt.weekday
payments.drop("check_date", axis=1, inplace=True)
payments.head()
payments_before_2002 includes 7 years of records. During this time there were 9,526 total payments to all vendors from all departments shown in the records for a total of \$11,120,745,925 which equates to an average payment per transaction of \$1,588,677,989.
payments_after_2002 includes records for 2003-2019. During this time there were 767,730 total payments to all vendors from all departments shown in the records for a total of \$79,800,684,681 which equates to an average payment per transaction of \$4,987,542,793.
payments_before_2002 = payments.loc[payments.year == 2002]
print(payments_before_2002["amount"].sum())
print("\n")
print(payments_before_2002["amount"].sum()/7)
payments_after_2002 = payments.loc[payments.year > 2002]
print(payments_after_2002["amount"].sum())
print("\n")
print(payments_after_2002["amount"].sum()/16)
3. Exploring and Visualizing the Payments Data.¶
3.1 Analyzing data from a political point of view.¶
- Looking at the city expenditures before and after 2002 (Fig. 3.1.1).
- Analyzing how the city expenditures have been evolving since 2003 (Fig. 3.1.2).
- The total dollars paid by the top 15 departments for all the orders for the years 1996-2018 (Fig. 3.1.3).
- Investigation of total dollars paid for vendors by Department of Finance for 2003-2018 (Fig. 3.1.4).
- The total dollars paid by the top 15 departments for small orders only for the years 2003-2018 (Fig. 3.1.5).
- The total dollars paid by year for the top five departments for all orders for 2003-2018 (Fig. 3.1.6/3.1.6(a)).
- Top six vendors paid by department of Water Management for the Years 2003-2018 (Fig. 3.1.7).
data = ("payments_before_2002", "payments_after_2002")
y_pos = np.arange(len(data))
amount = [1588677989.3500001, 4987542792.606253]
plt.barh(y_pos, amount, align='center', alpha=0.5)
plt.yticks(y_pos, data)
plt.xlabel('Amount in billions of dollars', fontsize=11)
plt.title("Fig. 3.1.1: The Average Payments Made \n by All Departments for Vendors for Years \n 1996-2002 and 2003-2019", fontsize=13)
def billions(val, pos):
return f'{val*1e-9:.0f}B'
formatter = FuncFormatter(billions)
plt.gca().xaxis.set_major_formatter(formatter)
Observation: The city expenditures have significantly increased by about 600% from 2003 to 2018.
payments_by_year = payments.groupby(['year']).amount.sum()
payments_by_year.loc[(payments_by_year.index >2002)&(payments_by_year.index != 2019)].plot(kind="line", y="amount", figsize=(8,5))
plt.ylim(bottom=0)
plt.xlabel("Year", fontsize=12)
plt.ylabel("Amount in billions of dollars", fontsize=12)
plt.title("Fig. 3.1.2: The Total Payments Made by \n All Departments for Vendors for 2003-2018", y=1.02, fontsize=14)
def billions(val, pos):
return f'{val*1e-9:.0f}B'
formatter = FuncFormatter(billions)
plt.gca().yaxis.set_major_formatter(formatter)
Observation: The total amount of payments has been steadly increasing except small declines in 2008 and 2013.
Note: It is likely that the 2007-2008 decline could be attributed at least in part to the financial crisis of 2007–2008. The reasons for the latter decline are less clear but perhaps the 2013 United States debt-ceiling crisis played some role.
- If departments are buying in amounts of over a \$100.000, it's considered a "Large Order".
- If departments are buying in amounts of under a \$100,000, it's considered a "Small Order".
grouped_by_dept = payments[payments["department_name"].notnull()]
grouped_by_dept = grouped_by_dept.groupby("department_name").amount.sum().sort_values(ascending=False).reset_index(name='sum').head(15)
grouped_by_dept.plot.barh(x="department_name", y='sum', figsize=(16,10), edgecolor="black")
plt.xlabel("Amount in billions of dollars", fontsize=14)
plt.ylabel("Department", fontsize=14)
plt.title("Fig. 3.1.3: The Total Dollars Paid by the Top 15 \n Departments for All the Orders for the Years 1996-2019", fontsize=18)
def billions(val, pos):
return f'{val*1e-9:.0f}B'
formatter = FuncFormatter(billions)
plt.gca().xaxis.set_major_formatter(formatter)
Observation: Department of finance has the highest payments followed by the department of aviation for 1996 - 2019.
This plot also represents departments that have the highest payments for large orders.
Note: Consider that we have only 15% available data for the department_name column.
dept_finance = payments.loc[payments.department_name == "DEPARTMENT OF FINANCE"]
top_four = dept_finance.loc[dept_finance.vendor_name.isin(["BLUE CROSS & BLUE SHIELD", "IBM CORPORATION ",
"GLADYS R. WILSON & ASSOCIATES PC", "SEAWAY BANK & TRUST COMPANY",
"A & O RECOVERY SOLUTIONS, LLC."])].groupby(["year",
"vendor_name"]).amount.sum().unstack()
top_four.fillna(0, inplace=True)
top_four.plot.line(figsize=(18,10))
plt.xlabel("Year", fontsize=14)
plt.ylabel("Amount in millions of dollars", fontsize=14)
plt.title("Figure 3.1.4: The Total Dollars Paid by Year by Department of Finance for the\n Top Four Departments for All Orders for 2003-2018", fontsize=20)
def millions(val, pos):
return f'{val*1e-6:.0f}M'
formatter = FuncFormatter(millions)
plt.gca().yaxis.set_major_formatter(formatter)
Observation: It's clear that primary purpose of the Department of Finance is to pay Health Insurance.
small_order = payments[(payments["amount"]<100000)&(payments["department_name"].notnull())]
small_order = small_order.groupby("department_name").amount.sum().sort_values(ascending=False).reset_index(name='sum').head(15)
small_order.plot.barh(x="department_name", y='sum', figsize=(16, 10), edgecolor="black")
plt.xlabel("Amount in millions of dollars", fontsize=14)
plt.ylabel("Department", fontsize=14)
plt.title('Fig. 3.1.5: The Total Dollars Paid by Top 15 Departments for Small Orders Only \n for the Years 1996-2019', fontsize=18)
def millions(val, pos):
return f'{val*1e-6:.0f}M'
formatter = FuncFormatter(millions)
plt.gca().xaxis.set_major_formatter(formatter)
Observation: Department of family and support services has the highest payments for small orders only for 1996 and 2019.
top_five_dept = payments.loc[(payments.department_name.isin(["DEPARTMENT OF FINANCE",
"DEPT OF AVIATION",
"CHICAGO DEPARTMENT OF TRANSPORTATION",
"DEPARTMENT OF WATER MANAGEMENT",
"OHARE MODERNIZATION PROJECT"]))&
(payments.year != 2002) & (payments.year != 2019)].groupby(["year",
"department_name"]).amount.sum().unstack()
top_five_dept.fillna(0, inplace=True)
top_five_dept.plot.bar(stacked=True, figsize=(18,10), edgecolor="black")
def billions(val, pos):
return f'{val*1e-9:.2f}B'
formatter = FuncFormatter(billions)
plt.gca().yaxis.set_major_formatter(formatter)
plt.xlabel("Year", fontsize=14)
plt.ylabel("Amount in billions of dollars", fontsize=14)
plt.title("Figure 3.1.6: The Total Dollars Paid by Year for the Top Five Departments \n for All Orders for 2003-2018", fontsize=20)
cols = top_five_dept.columns.tolist()
cols = ['DEPARTMENT OF FINANCE', 'DEPT OF AVIATION', 'CHICAGO DEPARTMENT OF TRANSPORTATION',
'DEPARTMENT OF WATER MANAGEMENT', 'OHARE MODERNIZATION PROJECT']
top_five_dept = top_five_dept[cols]
Observation: Department of Finance has the highest expenditures for years 2003-2018.
top_five_dept.plot.line( figsize=(18,10))
plt.xlabel("Year", fontsize=14)
plt.ylabel("Amount in millions of dollars", fontsize=14)
plt.title("Figure 3.1.6(a): The Total Dollars Paid by Year for the Top Five Departments \n for All Orders for 2003-2018", fontsize=20)
cols = top_five_dept.columns.tolist()
cols = ['DEPARTMENT OF FINANCE', 'DEPT OF AVIATION', 'CHICAGO DEPARTMENT OF TRANSPORTATION',
'DEPARTMENT OF WATER MANAGEMENT', 'OHARE MODERNIZATION PROJECT']
top_five_dept = top_five_dept[cols]
def millions(val, pos):
return f'{val*1e-6:.0f}M'
formatter = FuncFormatter(millions)
plt.gca().yaxis.set_major_formatter(formatter)
Observation: I looked at the changes after Rahm Emanuel took office in 2011. Amount of payments paid by the Department of Water, Transportation Management and Aviation had increased steadly before deeping slightly after 2016.
water_management_dept = payments_after_2002.loc[(payments_after_2002.department_name == "DEPARTMENT OF WATER MANAGEMENT")
& (payments_after_2002.year != 2019)]
vend = water_management_dept[water_management_dept["vendor_name"].notnull()]
vend = vend.groupby("vendor_name").amount.sum().sort_values(ascending=False).reset_index(name='sum').head(6)
vend.plot.barh(x="vendor_name", y='sum', figsize=(16,10), edgecolor="black")
plt.xlabel("Amount in millions of dollars", fontsize=14)
plt.ylabel("Vendors", fontsize=14)
plt.title("Fig 3.1.7: Top Six Vendors Paid by Department of Water Management \n for All Orders for the Years 2003-2018", fontsize=18)
def millions(val, pos):
return f'{val*1e-6:.0f}M'
formatter = FuncFormatter(millions)
plt.gca().xaxis.set_major_formatter(formatter)
Observation: Kenny Construction Company received the highest amount of payments from the Department of Water Management, almost
$500 millions.
3.2. Analyzing vendors via vizualization.¶
- Top 15 vendors who received the highest amount of payments for all orders for the years 1996-2018 (Fig. 3.2.1).
- Investigate the vendor that received the highest amount of payments for all orders for the years 1996-2018 and find out the departments that were financing it (Fig. 3.2.2).
- The Total Dollars Received by year by top four vendors excluding banks for all orders for the years 1996-2018 (Fig. 3.2.3).
grouped_by_vend = payments[payments["vendor_name"].notnull()]
grouped_by_vend = grouped_by_vend.groupby("vendor_name").amount.sum().sort_values(ascending=False).reset_index(name='sum').head(15)
grouped_by_vend.plot.barh(x="vendor_name", y='sum', figsize=(16,10), edgecolor="black")
plt.xlabel("Amount in billions of dollars", fontsize=14)
plt.ylabel("Vendors", fontsize=14)
plt.title("Fig. 3.2.1: The Total Dollars Received by the Top 15 Vendors \n for All Orders for the Years 1996-2018", fontsize=18)
def billions(val, pos):
return f'{val*1e-9:.0f}B'
formatter = FuncFormatter(billions)
plt.gca().xaxis.set_major_formatter(formatter)
blue_cross = payments.loc[(payments.vendor_name.isin(["BLUE CROSS & BLUE SHIELD"]))&
(payments.year != 2019)]
blue_cross.groupby("year").amount.sum().plot(kind="bar", y="amount", figsize=(14,5), colormap='Paired')
plt.xlabel("Year", fontsize=12)
plt.ylabel("Amount in millions of dollars", fontsize=12)
plt.title('Figure 3.2.2: The Total Dollars Paid by All Departments \n for Blue Cross & Blue Shield for the Years 1996-2018', y=1.02, fontsize=14)
def millions(val, pos):
return f'{val*1e-6:.0f}M'
formatter = FuncFormatter(millions)
plt.gca().yaxis.set_major_formatter(formatter)
Observation: The highest payments for Blue Cross & Blue Shield was made in 2016.
blue_cross.department_name.value_counts()
Observation: Department of Finance is the only department that has been financing Blue Cross & Blue Shield in the given data set.
Note: Consider that we have only 15% available data for the department_name column.
top_five_vend = payments_after_2002.loc[(payments_after_2002.vendor_name.isin(["KENNY CONSTRUCTION COMPANY",
"CAREMARK INC",
"NATIONWIDE RETIREMENT SOLUTION",
"WALSH CONSTRUCTION COMPANY OF ILL"])) &
(payments.year != 2019)]
top_five_vend = top_five_vend.groupby(["year","vendor_name"]).amount.sum().unstack()
top_five_vend.fillna(0, inplace=True)
top_five_vend.plot.line( figsize=(18,10))
def millions(val, pos):
return f'{val*1e-6:.0f}M'
formatter = FuncFormatter(millions)
plt.gca().yaxis.set_major_formatter(formatter)
plt.xlabel("Year", fontsize=14)
plt.ylabel("Amount in millions of dollars", fontsize=14)
plt.title("Fig. 3.2.3: The Total Dollars Received by Year by Top Four Vendors \n Excluding Banks for All Orders for the Years 2003-2018", fontsize=18)
plt.legend(loc=2)
Observation: After a significant raise in 2009, the "Nationwide Retirement Solution" dropped to zero in 2014.
Note: Top five vendors include mostly banks (look at Fig. 3.2.1)
3.3. Analyzing contract and non-contract vendors through vizualization.¶
- Investigate vendors who received their payment via contract or/and non-contract.
- The total dollars paid by departments for contract vendors (Fig. 3.3.1).
- The total dollars paid by departments for direct vendors or non-contract (Fig. 3.3.2).
- Analyze departments that were paying without contract.
- Analyze five top vendors that were receiving non-contract payments (Fig. 3.3.3).
payments.contract_number.value_counts().head()
payments[payments["contract_number"] == 'DV'].shape[0]/len(payments)
Observation: Almost 73% of the payments has transactioned to DV (Direct Vendor). I called them non-contract vendors.
via_contract = payments_after_2002.loc[(payments_after_2002.contract_number != "DV") &
(payments_after_2002.year !=2019)]
via_contract.head()
via_contract.groupby("year").amount.sum().plot(kind="bar", colormap="Paired", figsize=(14,5))
plt.xlabel("Year", fontsize=12)
plt.ylabel("Amount in billions of dollars", fontsize=12)
plt.title('Fig. 3.3.1: The Total Dollars Paid by Departments for \n Contract Vendors for the Years 2003-2018', y=1.02, fontsize=14)
def billions(val, pos):
return f'{val*1e-9:.0f}B'
formatter = FuncFormatter(billions)
plt.gca().yaxis.set_major_formatter(formatter)
via_contract.groupby("vendor_name").amount.sum().sort_values(ascending=False).head(10)
no_contract = payments.loc[(payments.contract_number == "DV") & (payments.year != 2019)]
no_contract.groupby("year").amount.sum().plot(kind="bar", colormap="Paired", figsize=(12,5))
plt.xlabel("Year", fontsize=12)
plt.ylabel("Amount in billions of dollars", fontsize=12)
plt.title('Fig. 3.3.2: The Total Dollars Paid by Departments for \n Direct Vendors for the Years 1996-2018', y=1.02, fontsize=14)
def billions(val, pos):
return f'{val*1e-9:.2f}B'
formatter = FuncFormatter(billions)
plt.gca().yaxis.set_major_formatter(formatter)
Observation: Note that there were no direct payments made before 2010.
I wanted to analyze departments that have been paying without contract, but there wasn't any available data.
ax = sns.heatmap(no_contract.isnull(),yticklabels=False,cbar=False,cmap='viridis')
ax.set_title("Missing Data Values for the No_Contract DataSet")
463134 rows are missing.
no_contract.isnull().sum()
no_contract.groupby("vendor_name").amount.sum().sort_values(ascending=False).head(25)
top_five = no_contract.loc[(no_contract.vendor_name.isin(["CITY TREASURER / HARRIS BANK", "POLICEMENS A & B FUND",
"NATIONWIDE RETIREMENT SOLUTION", "BANK OF NEW YORK TRUST ", "AMALGAMATED BANK OF CHICAGO",
"BANK OF NY MIDWEST TRUST CO" ])) & (no_contract.year != 2019)]
top_five = top_five.groupby(["year","vendor_name"]).amount.sum().unstack()
top_five.fillna(0, inplace=True)
top_five.plot(kind="line", figsize=(18,10))
def billions(val, pos):
return f'{val*1e-9:.2f}B'
formatter = FuncFormatter(billions)
plt.gca().yaxis.set_major_formatter(formatter)
plt.xlabel("Year", fontsize=14)
plt.ylabel("Amount in billions of dollars", fontsize=14)
plt.title(" Fig. 3.3.3: The Total Dollars Paid by Year by Departments for \n Top Five Non-Contract Vendors for the Years 1996-2018", fontsize=18)
plt.legend(loc=2)
4. Conclusions¶
It's difficult to draw any firm conclusions regarding the presence or absence of corruption from the given data, but the structure of many of the transactions as well as key missing data would appear to create an environment where corruption could occur:
- Top vendors that were receiving the majority of payments are banks (Fig. 3.2.1). The City Treasurer / Harris Bank is leading those banks by receiving almost $4.4 billion in payments between 1996-2018. Presumably, banks are just the conduits for payments to others. If so, the ultimate recipients are not revealed.
- There is no data available to show which Department made these payments either.
- These are direct payments, which means there is no contract between the city and the vendor. There may be good reasons for this in many cases, but, again, it does create an environment for corruption.
The data doesn't show any record for payments that were made with no contract until 2010. I wasn't able to tell if this means that the city only started to allow direct payments after 2010 or that it has all along but didn't distinguish between direct and contract payments before that.
Overall city expenditures, or at least those recorded in the data increased by about 600% from 2003 to 2018. While there were some variations from the overall trend most notably the dip during the great recession around 2008 and rise in its aftermath), the total annual payments fairly closely followed a straight line with an average annual increase of about $330M per year.
There is no ability to break out the annual payments from 1996-2002, since this data is rolled up as one year. The average annual total payments during this period were just under \$1.6B, which compares to total payments of about \\$1.9B in 2003, the first year the numbers were broken out annually.
I looked at the changes after Rahm Emanuel took office in 2011. Amount of payments paid by the Department of Water, Transportation Management and Aviation had increased steadly before deeping slightly after 2016.
5. Suggestions for Further Analysis¶
I would like to get answers to questions:
- Why are the top vendors that have received the highest amount of payments banks?
- Why doesn't data show us what departments made those payments?
- If banks are just the conduits for payments to others, the ultimate recipients must be revealed.
Departments play an essential role in this data set because they are the ones that make the city's most expenditures. The city has to do a better job of revealing the ultimate recipients for the Payments data, so citizens can validate the transparency and eliminate all the doubts about vague information in the given dataset.