BC Liberal Party Donations in 2016-2017

The BC Liberal Party disclosed donations received during the run-up to the 41st British Columbia general election held May 9, 2017. The data was shared in the form of PDF files containing donations during time intervals of varying length. In this format, the disclosed donations data is not presented for easy analysis using a computer.

I munged the disclosed donations data into CSV format and made the collection available as an open Google docs spreadsheet.

BC Liberals Screen Capture

This notebook explores the donations data and achieves the following objectives:

  • Plot Donations vs. Time.
  • List the top donors by accumulating across all disclosed transactions.
  • Identify all donors matching a string.
  • Explore donations made by UBCBoG members.

Set up some tools

In [88]:
import pandas as pd
import numpy as np
from datetime import *
from dateutil.relativedelta import *
import calendar
from dateutil.rrule import *
from dateutil.parser import *
import matplotlib.pyplot as plt 
In [89]:
from bokeh.plotting import figure, output_notebook, show
from bokeh.charts import Bar
from bokeh.models import DatetimeTickFormatter
In [90]:
output_notebook()
Loading BokehJS ...
In [91]:
%matplotlib inline

Import the data

In [92]:
## Import multisheet Google doc in as an Excel file.
xl = pd.ExcelFile('https://docs.google.com/spreadsheets/d/1K26qXaaKPaXV4FAmce9W7xX_lCy2RGfDSBO2lwRxfkA/export?format=xlsx&id', 
                )
In [93]:
xl
Out[93]:
<pandas.io.excel.ExcelFile at 0x7f6a1c941780>
In [94]:
## List the names of the sheets in the imported Excel file.
## The sheet names correspond to the filenames used to label the disclosed files.
xl.sheet_names
Out[94]:
['Copy-of-2016-Contributions-v4',
 '2017-Contributions-June-2',
 '2017-Contributions-May-26-2017',
 '2017-Contributions-May-19',
 '2017-Contributions-May-12',
 '2017-Contributions-May-5',
 '2017-Contributions-April-28-201',
 '2017-Contributions-April-21',
 '2017-Contributions-April-14',
 '2017-Contributions-April-7',
 '2017-Contributions-March-31-201',
 '2017-Contributions-March-23',
 '2017-Contributions-—-March-17-2',
 '2017-Contributions-March-10',
 '2017-Contributions-March-3-2017',
 '2017-Contributions-_-February-2',
 '2017-Contributions-February-17',
 'Copy-of-2017-Contributions-Janu',
 'Sheet2',
 '2017-Contributions-—-February-3',
 '2017-Contributions-—-February-1',
 'Real-Time-Donations-Jan-25V3']
In [95]:
## Make a dictionary of Pandas Dataframes for each sheet
dict = pd.ExcelFile.parse(xl, sheetname=None)
In [96]:
## Merge the data frames in the dictionary into a single large data frame.
## The `df` file contains the list of disclosed BC Liberal Party data in the form of a Pandas Dataframe.
df = pd.concat(dict)
In [97]:
## List descriptive statistics of the collection of all donations.
df.describe()
Out[97]:
Amount
count 29509.000000
mean 628.985388
std 2279.642803
min 0.000000
25% 36.000000
50% 100.000000
75% 300.000000
max 85000.000000
In [101]:
## Convert Date to a better Format for ordering
df['datetime'] = pd.to_datetime(df['Date'])
In [98]:
## Number of unique Donor Names
len(df['Donor Name'].unique())
Out[98]:
15306

There were 29,509 donation transactions reported. These donations were made by 15,306 unique donors. The mean donation is \$628.99 with a maximum single donation of \$85,000.

In [99]:
## Calculate total amount donated.
df['Amount'].sum()
Out[99]:
18560729.800000001

Donation Total: \$18,560,729.80

Analyze donations vs. time

In [102]:
## Plot donations vs. time
df.sort_values(by='datetime').plot(x='datetime',y="Amount", style ="x")
Out[102]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f6a1bc7b080>
In [103]:
## Accumulate donations vs. time starting from oldest donation
df['cumsum'] = df.sort_values(by='datetime')['Amount'].cumsum()
In [104]:
## Accumulated Donations vs. Time
df.plot(x='datetime',y='cumsum')
Out[104]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f6a1ceb8b70>

Analyze donations based on size

In [106]:
## Restrict attention to donations exceeding a threshold amount
big = df.loc[df['Amount'] > 30000]
In [107]:
## Big Donations as a fraction of total donations
big['Amount'].sum()/df['Amount'].sum()
Out[107]:
0.061702853947046846
In [108]:
## 20 biggest single donations
big.sort_values(by="Amount", ascending = False).head(n=20)
Out[108]:
Donor Name Amount Date datetime cumsum
2017-Contributions-May-5 945 Sandman Hotels, Inns & Suites Ltd 85000.0 2017-04-28 00:00:00 2017-04-28 15239725.38
2017-Contributions-May-19 433 Wesgroup Properties Ltd 75000.0 2017-05-12 00:00:00 2017-05-12 18053801.51
Copy-of-2016-Contributions-v4 779 Aquilini Investment Group LP 75000.0 2016-11-10 00:00:00 2016-11-10 8234965.96
6602 Independent Contractors & Businesses Associati... 60000.0 2016-08-07 00:00:00 2016-08-07 6123515.38
15516 Westbridge Finance Inc 50000.0 2016-04-15 00:00:00 2016-04-15 3092883.44
2017-Contributions-May-12 34 Anthem Properties Group Ltd. 50000.0 2017-05-06 00:00:00 2017-05-06 16739929.01
259 Interfor Corporation 50000.0 2017-05-06 00:00:00 2017-05-06 16994279.01
Copy-of-2016-Contributions-v4 14013 Starline Windows (2001) Ltd. 50000.0 2016-03-06 00:00:00 2016-03-06 1788467.44
13635 Shape Properties Corp. 50000.0 2016-07-13 00:00:00 2016-07-13 5701462.88
2017-Contributions-May-19 283 Maple Ridge Plaza Properties Ltd. 50000.0 2017-05-12 00:00:00 2017-05-12 17573426.01
Copy-of-2016-Contributions-v4 11933 Peter Redekop 50000.0 2016-01-15 00:00:00 2016-01-15 322847.79
2017-Contributions-March-31-201 1665 The Pacific Investment Corporation Ltd. 50000.0 2017-03-25 00:00:00 2017-03-25 12894264.39
Copy-of-2016-Contributions-v4 3481 Dayhu Investments Ltd. 50000.0 2016-10-06 00:00:00 2016-10-06 7088503.35
14471 Tech Projects Ltd 40000.0 2016-02-26 00:00:00 2016-02-26 1086720.44
6721 ITC Management Inc 40000.0 2016-02-26 00:00:00 2016-02-26 868810.44
2017-Contributions-March-31-201 1360 Polygon Homes Ltd 40000.0 2017-03-25 00:00:00 2017-03-25 13138710.53
2017-Contributions-May-12 493 Robert Lee 40000.0 2017-05-06 00:00:00 2017-05-06 16816079.01
2017-Contributions-March-31-201 1759 West Fraser Mills Ltd 37500.0 2017-03-25 00:00:00 2017-03-25 12645513.57
Copy-of-2016-Contributions-v4 1947 Canadian Classified Network Inc 35000.0 2016-01-15 00:00:00 2016-01-15 371222.79
12149 Progressive Waste Solutions Canada Inc 35000.0 2016-03-06 00:00:00 2016-03-06 1583542.44

Identify donations based on a string in 'Donor Name'

In [109]:
def df_filter(name):
    return df.loc[df['Donor Name'].str.contains(name)].sort_values(by='datetime')
In [110]:
## Donations containing `Belk` ordered in time
df_filter('Belk')
Out[110]:
Donor Name Amount Date datetime cumsum
Copy-of-2016-Contributions-v4 1166 Belkorp Environmental Services Inc. 5000.0 2016-02-26 00:00:00 2016-02-26 827810.44
1172 Belkorp Industries Inc 5000.0 2016-03-06 00:00:00 2016-03-06 1724467.44
21 338446 B.C. Ltd DBA Belkorp Industries 10000.0 2016-04-15 00:00:00 2016-04-15 3128768.44
1168 Belkorp Environmental Services Inc. 1200.0 2016-04-22 00:00:00 2016-04-22 3203878.44
1169 Belkorp Environmental Services Inc. 7500.0 2016-05-13 00:00:00 2016-05-13 4036478.78
1170 Belkorp Environmental Services Inc. 4000.0 2016-07-13 00:00:00 2016-07-13 5645962.88
1167 Belkorp Environmental Services Inc. 1000.0 2016-08-04 00:00:00 2016-08-04 6014540.38
1171 Belkorp Industries Inc 7500.0 2016-11-03 00:00:00 2016-11-03 8027629.96
1165 Belkorp Environmental Services Inc. 2500.0 2016-12-02 00:00:00 2016-12-02 9079312.66
2017-Contributions-March-31-201 128 Belkorp Capital Inc. 7500.0 2017-03-25 00:00:00 2017-03-25 12747389.39
129 Belkorp Industries Inc 5000.0 2017-03-25 00:00:00 2017-03-25 12752489.39
2017-Contributions-May-5 106 Belkorp Environmental Services Inc. 1000.0 2017-04-28 00:00:00 2017-04-28 15461565.38
2017-Contributions-May-12 56 Belkorp Industries Inc 12500.0 2017-05-06 00:00:00 2017-05-06 16858579.01
In [111]:
df_filter('Belk')['Amount'].sum()
Out[111]:
69700.0
In [112]:
df_filter('Belk').plot(x='datetime',y="Amount", kind='bar')
Out[112]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f6a1a1d3208>
In [85]:
df_filter('Lindsay Gordon')
Out[85]:
Donor Name Amount Date datetime cumsum
2017-Contributions-March-31-201 1059 Lindsay Gordon 1000.0 2017-03-28 00:00:00 2017-03-28 13503287.53
In [26]:
df_filter('Seaspan')
Out[26]:
Donor Name Amount Date datetime cumsum
Copy-of-2016-Contributions-v4 13558 Seaspan ULC 1000.0 2016-01-31 00:00:00 2016-01-31 595797.09
13559 Seaspan ULC 1 0.0 2016-02-26 00:00:00 2016-02-26 1060535.44
13560 Seaspan ULC 5000.0 2016-03-31 00:00:00 2016-03-31 2269388.44
13557 Seaspan International Ltd 15000.0 2016-05-13 00:00:00 2016-05-13 4205902.78
13561 Seaspan ULC 1050.0 2016-06-17 00:00:00 2016-06-17 5120717.45
2017-Contributions-March-3-2017 1151 Seaspan ULC 5000.0 2017-02-24 00:00:00 2017-02-24 11518535.63
2017-Contributions-March-31-201 1540 Seaspan ULC 1000.0 2017-03-25 00:00:00 2017-03-25 12029246.08
2017-Contributions-April-21 1130 Seaspan ULC 1000.0 2017-04-14 00:00:00 2017-04-14 14326906.75
2017-Contributions-May-19 392 Seaspan ULC 15000.0 2017-05-12 00:00:00 2017-05-12 17322034.40
In [27]:
df_filter('Polygon')
Out[27]:
Donor Name Amount Date datetime cumsum
Copy-of-2016-Contributions-v4 12056 Polygon Homes Ltd 500.0 2016-01-29 00:00:00 2016-01-29 454588.09
12057 Polygon Homes Ltd 15000.0 2016-04-15 00:00:00 2016-04-15 3158553.44
12058 Polygon Homes Ltd 10000.0 2016-05-13 00:00:00 2016-05-13 3751990.44
12059 Polygon Homes Ltd 1500.0 2016-11-15 00:00:00 2016-11-15 8276290.96
2017-Contributions-March-31-201 1360 Polygon Homes Ltd 40000.0 2017-03-25 00:00:00 2017-03-25 11953451.08
2017-Contributions-May-12 452 Polygon Homes Ltd 10000.0 2017-05-05 00:00:00 2017-05-05 16169330.57
In [29]:
df_filter('Car Dealers')
Out[29]:
Donor Name Amount Date datetime cumsum
Copy-of-2016-Contributions-v4 11088 New Car Dealers Association of BC 6000.0 2016-01-15 00:00:00 2016-01-15 276120.00
11089 New Car Dealers Association of BC 8500.0 2016-01-22 00:00:00 2016-01-22 402472.79
11090 New Car Dealers Association of BC 500.0 2016-01-31 00:00:00 2016-01-31 633627.09
11092 New Car Dealers Association of BC 2000.0 2016-02-29 00:00:00 2016-02-29 1377900.44
11100 New Car Dealers Association of BC 1500.0 2016-03-06 00:00:00 2016-03-06 1541417.44
11106 New Car Dealers Association of BC 8500.0 2016-03-11 00:00:00 2016-03-11 1908940.44
11093 New Car Dealers Association of BC 5000.0 2016-03-24 00:00:00 2016-03-24 2056565.44
11094 New Car Dealers Association of BC 10000.0 2016-03-31 00:00:00 2016-03-31 2395186.44
11102 New Car Dealers Association of BC 300.0 2016-04-07 00:00:00 2016-04-07 2767163.44
11096 New Car Dealers Association of BC 1250.0 2016-04-30 00:00:00 2016-04-30 3393400.44
11103 New Car Dealers Association of BC 7250.0 2016-05-08 00:00:00 2016-05-08 3578962.44
11097 New Car Dealers Association of BC 2200.0 2016-05-13 00:00:00 2016-05-13 4064346.11
11098 New Car Dealers Association of BC 3000.0 2016-05-20 00:00:00 2016-05-20 4222202.78
11099 New Car Dealers Association of BC 1500.0 2016-05-31 00:00:00 2016-05-31 4397048.11
11101 New Car Dealers Association of BC 30750.0 2016-06-17 00:00:00 2016-06-17 4947137.45
11095 New Car Dealers Association of BC 5000.0 2016-08-04 00:00:00 2016-08-04 6004715.38
11104 New Car Dealers Association of BC 9600.0 2016-09-16 00:00:00 2016-09-16 6508874.55
11105 New Car Dealers Association of BC 2000.0 2016-09-30 00:00:00 2016-09-30 6954306.35
11091 New Car Dealers Association of BC 3000.0 2016-12-02 00:00:00 2016-12-02 9106562.66
11107 New Car Dealers Association of BC 2000.0 2016-12-19 00:00:00 2016-12-19 9636984.36
Sheet2 718 New Car Dealers Association 250.0 1/20/2017 2017-01-20 10655693.67
2017-Contributions-—-February-3 378 New Car Dealers Association of BC 3600.0 2017-01-27 00:00:00 2017-01-27 10832601.52
2017-Contributions-March-10 128 New Car Dealers Association of BC 1200.0 2017-02-10 00:00:00 2017-02-10 11258990.52
2017-Contributions-February-17 128 New Car Dealers Association of BC 1200.0 2017-02-10 00:00:00 2017-02-10 11326105.52
2017-Contributions-_-February-2 86 New Car Dealers Association of BC 1500.0 2017-02-17 00:00:00 2017-02-17 11348775.52
2017-Contributions-March-23 95 New Car Dealers Association 1800.0 2017-03-17 00:00:00 2017-03-17 11767373.23
2017-Contributions-March-31-201 1247 New Car Dealers Association 2000.0 2017-03-24 00:00:00 2017-03-24 11795343.57
2017-Contributions-April-14 503 New Car Dealers Association of BC 7000.0 2017-04-07 00:00:00 2017-04-07 13844036.75
504 New Car Dealers Association of BC 3700.0 2017-04-08 00:00:00 2017-04-08 13893725.75
Real-Time-Donations-Jan-25V3 532 New Car Dealers Association of BC 2500.0 2017-05-01 00:00:00 2017-05-01 15508647.38
2017-Contributions-May-12 405 New Car Dealers Association of BC 2000.0 2017-05-08 00:00:00 2017-05-08 16990886.90
406 New Car Dealers Association of BC 4000.0 2017-05-09 00:00:00 2017-05-09 17014532.90
2017-Contributions-—-March-17-2 118 New Car Dealers Association 2000.0 2017-10-03 00:00:00 2017-10-03 18462689.69
In [30]:
df_filter('Aquilini')
Out[30]:
Donor Name Amount Date datetime cumsum
Copy-of-2016-Contributions-v4 774 Aquilini Investment Group LP 2500.0 2016-01-01 00:00:00 2016-01-01 69850.00
777 Aquilini Investment Group LP 10000.0 2016-01-04 00:00:00 2016-01-04 153600.00
775 Aquilini Investment Group LP 2500.0 2016-01-31 00:00:00 2016-01-31 644582.09
776 Aquilini Investment Group LP 10000.0 2016-02-26 00:00:00 2016-02-26 776450.44
778 Aquilini Investment Group LP 25000.0 2016-04-22 00:00:00 2016-04-22 3300890.44
780 Aquilini Investment Group LP 5000.0 2016-10-30 00:00:00 2016-10-30 7478741.35
781 Aquilini Investment Group LP 1000.0 2016-10-31 00:00:00 2016-10-31 7981051.96
779 Aquilini Investment Group LP 75000.0 2016-11-10 00:00:00 2016-11-10 8186670.96
2017-Contributions-—-February-3 25 Aquilini Investment Group 2500.0 2017-01-27 00:00:00 2017-01-27 10858295.52
26 Aquilini Investments 2500.0 2017-01-27 00:00:00 2017-01-27 10860895.52
2017-Contributions-March-31-201 86 Aquilini Investment Group LP 2400.0 2017-03-28 00:00:00 2017-03-28 13065640.42
In [32]:
df_filter('Robert J Macdonald')
Out[32]:
Donor Name Amount Date datetime cumsum
Copy-of-2016-Contributions-v4 12784 Robert J Macdonald 500.0 2016-01-31 00:00:00 2016-01-31 503038.09
12789 Robert J Macdonald 1000.0 2016-03-11 00:00:00 2016-03-11 1818359.44
12788 Robert J Macdonald 500.0 2016-05-08 00:00:00 2016-05-08 3607017.44
12786 Robert J Macdonald 10000.0 2016-05-27 00:00:00 2016-05-27 4360440.11
12787 Robert J Macdonald 1000.0 2016-06-28 00:00:00 2016-06-28 5290854.52
12785 Robert J Macdonald 10000.0 2016-11-03 00:00:00 2016-11-03 8059735.96
2017-Contributions-March-31-201 1453 Robert J Macdonald 3000.0 2017-03-28 00:00:00 2017-03-28 12911720.42
Real-Time-Donations-Jan-25V3 426 Robert J Macdonald 1000.0 2017-04-01 00:00:00 2017-04-01 13414133.75

Accumulate donations across 'Donor Name'

In [33]:
pd.pivot_table(df,index=['Donor Name'], 
               aggfunc = sum, values = ['Amount']).sort_values(by=['Amount'], 
                                                               ascending=[False]).head(n=30).plot(kind='bar')
Out[33]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f5a431eaef0>
In [34]:
## Top 25 Donors 
pd.pivot_table(df,index=['Donor Name'], 
               aggfunc = sum, values = ['Amount']).sort_values(by=['Amount'], ascending=[False]).head(n=25)
Out[34]:
Amount
Donor Name
Independent Contractors & Businesses Association of BC 174700.00
New Car Dealers Association of BC 136550.00
Aquilini Investment Group LP 133400.00
Progressive Waste Solutions Canada Inc 123700.00
Wesgroup Properties Ltd 121750.00
Encana Corporation Ltd 121200.00
West Fraser Mills Ltd 114300.00
Gateway Casinos and Entertainment Limited 105738.74
Interfor Corporation 97050.00
Sandman Hotels, Inns & Suites Ltd 95000.00
Canadian Forest Products Ltd 90650.00
LifeLabs Inc 85955.00
Anthem Properties Group Ltd. 84000.00
Fasken Martineau DuMoulin LLP 83025.00
Polygon Homes Ltd 77000.00
Starline Windows (2001) Ltd. 75000.00
Wesbild Holdings Ltd 72500.00
Shato Holdings Ltd. 70200.00
Great Canadian Gaming Corp. 68995.00
Anthem Properties Corp Ltd 68200.00
Tolko Industries Ltd 67850.00
Woodfibre LNG Ltd. 67500.00
The Pacific Investment Corporation Ltd. 65000.00
Burnco Rock Products Ltd. 64000.00
Western Forest Products Inc. 63300.00
In [35]:
## Donation yield from top 100 donors
pd.pivot_table(df,index=['Donor Name'], 
               aggfunc = sum, values = ['Amount']).sort_values(by=['Amount'], ascending=[False]).head(n=100)['Amount'].sum()
Out[35]:
5012371.4699999997
In [36]:
## Fraction of total donations from top 100 donors
pd.pivot_table(df,index=['Donor Name'], 
               aggfunc = sum, 
               values = ['Amount']).sort_values(by=['Amount'], ascending=[False]).head(n=100)['Amount'].sum()/df['Amount'].sum()
Out[36]:
0.27005249922877489
In [37]:
len(df['Donor Name'].unique())
Out[37]:
15306

Donation Amount by Day

In [38]:
pd.pivot_table(df,index=['datetime'], 
               aggfunc = sum, values = ['Amount']).sort_values(by=['Amount'], 
                                                               ascending=[False]).head(n=30).plot(kind='bar')
Out[38]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f5a431913c8>
In [39]:
pd.pivot_table(df,index=['datetime'], 
               aggfunc = sum, values = ['Amount']).sort_values(by=['Amount'], 
                                                               ascending=[False]).head(n=4)
Out[39]:
Amount
datetime
2017-03-25 938817.34
2017-05-12 814751.50
2016-02-26 548565.00
2017-05-05 546109.52

The BC Liberal Party received $939K in donations on 2017-03-25.

Analyzing UBC BoG Donations

Copied from UBCBoG site

Appointed by the Province

Stuart Belkin (Chair) December 2018
Sandra Cawley December 2018
Raghwa Gopal December 2017
Celeste Haldane December 2017
Michael Korenberg (Vice-Chair) December 2017
Graham Lee December 2017
Fiona Macfarlane December 2018
Beverlee Park December 2017
Alan Shuster February 2020
David Sidoo December 2017
Kevin Smith December 2018

In [40]:
ubc_board = ['Stuart Belkin',
'Sandra Cawley',
'Raghwa Gopal',
'Celeste Haldane',
'Michael Korenberg',
'Graham Lee',
'Fiona Macfarlan',
'Beverlee Park',
'Alan Shuster',
'David Sidoo',
'Kevin Smith',
]
In [41]:
for name in ubc_board:
    print(name, df_filter(name)['Amount'].sum())
Stuart Belkin 0
Sandra Cawley 0
Raghwa Gopal 1000.0
Celeste Haldane 0
Michael Korenberg 2500.0
Graham Lee 2300.0
Fiona Macfarlan 1800.0
Beverlee Park 0
Alan Shuster 0
David Sidoo 10000.0
Kevin Smith 0
In [86]:
df_filter('Graham Lee')
Out[86]:
Donor Name Amount Date datetime cumsum
Copy-of-2016-Contributions-v4 5813 Graham Lee 1150.0 2016-04-30 00:00:00 2016-04-30 3389935.44
2017-Contributions-March-3-2017 490 Graham Lee 1150.0 2017-02-24 00:00:00 2017-02-24 11745080.63
In [87]:
df_filter('David Sidoo')
Out[87]:
Donor Name Amount Date datetime cumsum
Copy-of-2016-Contributions-v4 3416 David Sidoo 2500.0 2016-01-31 00:00:00 2016-01-31 470908.09
3417 David Sidoo 5000.0 2016-02-29 00:00:00 2016-02-29 1306830.44
2017-Contributions-March-31-201 390 David Sidoo 2500.0 2017-03-25 00:00:00 2017-03-25 13079040.53
In [ ]: