import pandas as pd
from matplotlib import pyplot as plt
from IPython.display import display
%matplotlib inline
plt.rcParams['figure.figsize'] = (20.0, 10.0)
rhi_csv = 'rhi-nondomestic-beneficiaries-ltd-companies-organisations.csv'
#rhi_csv = 'tabula-RHI-beneficiaries-non-domestic-individuals-companies.csv'
df = pd.read_csv(rhi_csv)
display(df.head())
display(df.dtypes)
Acheson & Glover Precast Ltd | 06/11/2015 | BT75 | Solid Biomass Boiler | 99 | £27,600.66 | |
---|---|---|---|---|---|---|
0 | Acheson & Glover Precast Ltd | 06/11/2015 | BT75 | Solid Biomass Boiler | 99.0 | £30,507.19 |
1 | Acheson & Glover Precast Ltd | 06/11/2015 | BT75 | Solid Biomass Boiler | 99.0 | £34,416.23 |
2 | Acheson & Glover Precast Ltd | 13/07/2015 | BT75 | Solid Biomass Boiler | 99.0 | £50,543.44 |
3 | Acheson & Glover Precast Ltd | 13/07/2015 | BT75 | Solid Biomass Boiler | 99.0 | £52,297.88 |
4 | Acheson & Glover Precast Ltd | 13/07/2015 | BT75 | Solid Biomass Boiler | 99.0 | £53,369.70 |
Acheson & Glover Precast Ltd object 06/11/2015 object BT75 object Solid Biomass Boiler object 99 float64 £27,600.66 object dtype: object
df = pd.read_csv(rhi_csv,
names=['Company','Date','Postcode',
'Type','Capacity (kWtH)', 'Cash']
)
display(df.head())
display(df.dtypes)
Company | Date | Postcode | Type | Capacity (kWtH) | Cash | |
---|---|---|---|---|---|---|
0 | Acheson & Glover Precast Ltd | 06/11/2015 | BT75 | Solid Biomass Boiler | 99.0 | £27,600.66 |
1 | Acheson & Glover Precast Ltd | 06/11/2015 | BT75 | Solid Biomass Boiler | 99.0 | £30,507.19 |
2 | Acheson & Glover Precast Ltd | 06/11/2015 | BT75 | Solid Biomass Boiler | 99.0 | £34,416.23 |
3 | Acheson & Glover Precast Ltd | 13/07/2015 | BT75 | Solid Biomass Boiler | 99.0 | £50,543.44 |
4 | Acheson & Glover Precast Ltd | 13/07/2015 | BT75 | Solid Biomass Boiler | 99.0 | £52,297.88 |
Company object Date object Postcode object Type object Capacity (kWtH) float64 Cash object dtype: object
df = pd.read_csv(rhi_csv,
names=['Company','Date','Postcode',
'Type','Capacity (kWtH)', 'Cash'] )
df['Date'] = pd.to_datetime(df['Date'])
display(df.head())
display(df.dtypes)
Company | Date | Postcode | Type | Capacity (kWtH) | Cash | |
---|---|---|---|---|---|---|
0 | Acheson & Glover Precast Ltd | 2015-06-11 | BT75 | Solid Biomass Boiler | 99.0 | £27,600.66 |
1 | Acheson & Glover Precast Ltd | 2015-06-11 | BT75 | Solid Biomass Boiler | 99.0 | £30,507.19 |
2 | Acheson & Glover Precast Ltd | 2015-06-11 | BT75 | Solid Biomass Boiler | 99.0 | £34,416.23 |
3 | Acheson & Glover Precast Ltd | 2015-07-13 | BT75 | Solid Biomass Boiler | 99.0 | £50,543.44 |
4 | Acheson & Glover Precast Ltd | 2015-07-13 | BT75 | Solid Biomass Boiler | 99.0 | £52,297.88 |
Company object Date datetime64[ns] Postcode object Type object Capacity (kWtH) float64 Cash object dtype: object
df = pd.read_csv(rhi_csv,
names=['Company','Date','Postcode',
'Type','Capacity (kWtH)', 'Cash'] )
df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y')
display(df.head())
display(df.dtypes)
Company | Date | Postcode | Type | Capacity (kWtH) | Cash | |
---|---|---|---|---|---|---|
0 | Acheson & Glover Precast Ltd | 2015-11-06 | BT75 | Solid Biomass Boiler | 99.0 | £27,600.66 |
1 | Acheson & Glover Precast Ltd | 2015-11-06 | BT75 | Solid Biomass Boiler | 99.0 | £30,507.19 |
2 | Acheson & Glover Precast Ltd | 2015-11-06 | BT75 | Solid Biomass Boiler | 99.0 | £34,416.23 |
3 | Acheson & Glover Precast Ltd | 2015-07-13 | BT75 | Solid Biomass Boiler | 99.0 | £50,543.44 |
4 | Acheson & Glover Precast Ltd | 2015-07-13 | BT75 | Solid Biomass Boiler | 99.0 | £52,297.88 |
Company object Date datetime64[ns] Postcode object Type object Capacity (kWtH) float64 Cash object dtype: object
df = pd.read_csv(rhi_csv,
names=['Company','Date','Postcode',
'Type','Capacity (kWtH)', 'Cash']
)
df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y')
df['Cash'] = df['Cash'].replace('[£,]','', regex=True).astype(float)
display(df.head())
display(df.dtypes)
Company | Date | Postcode | Type | Capacity (kWtH) | Cash | |
---|---|---|---|---|---|---|
0 | Acheson & Glover Precast Ltd | 2015-11-06 | BT75 | Solid Biomass Boiler | 99.0 | 27600.66 |
1 | Acheson & Glover Precast Ltd | 2015-11-06 | BT75 | Solid Biomass Boiler | 99.0 | 30507.19 |
2 | Acheson & Glover Precast Ltd | 2015-11-06 | BT75 | Solid Biomass Boiler | 99.0 | 34416.23 |
3 | Acheson & Glover Precast Ltd | 2015-07-13 | BT75 | Solid Biomass Boiler | 99.0 | 50543.44 |
4 | Acheson & Glover Precast Ltd | 2015-07-13 | BT75 | Solid Biomass Boiler | 99.0 | 52297.88 |
Company object Date datetime64[ns] Postcode object Type object Capacity (kWtH) float64 Cash float64 dtype: object
rhi_csv = 'tabula-RHI-beneficiaries-non-domestic-individuals-companies.csv'
df = pd.read_csv(rhi_csv)
df['Date'] = pd.to_datetime(df['Date of\rApplication'], format='%d/%m/%Y')
df['Cash'] = df['Amount of payments\rmade to 28 February\r2017 (£)*'].replace('[£,]','', regex=True).astype(float)
df.drop(['Date of\rApplication','Amount of payments\rmade to 28 February\r2017 (£)*'], axis=1, inplace=True)
df.rename(columns={'Business or\rInstallation\rLocation':'Postcode', 'Installation\rCapacity\r(kWth)':'Capacity (kWtH)','Technology Type':'Type','Name':'Company'}, inplace=True)
df.Cash.describe()
count 1746.000000 mean 34533.699943 std 23752.919869 min 73.220000 25% 14418.435000 50% 31820.715000 75% 48982.615000 max 252844.050000 Name: Cash, dtype: float64
f,ax = plt.subplots()
df.Cash.hist(ax=ax)
<matplotlib.axes._subplots.AxesSubplot at 0x7f8f399fdcc0>
df.Postcode.unique()
array(['BT39', 'BT75', 'BT80', 'BT81', 'BT42', 'BT62', 'BT44', 'BT24', 'BT32', 'BT34', 'BT53', 'BT76', 'BT82', 'BT60', 'BT79', 'BT41', 'BT43', 'BT70', 'BT46', 'BT31', 'BT23', 'BT71', 'BT45', 'BT29', 'BT8', 'BT77', 'BT54', 'BT94', 'BT78', 'BT92', 'BT28', 'BT61', 'BT19', 'BT20', 'BT67', 'BT63', 'BT9', 'BT74', 'BT25', 'BT30', 'BT4', 'BT33', 'BT68', 'BT47', 'BT35', 'BT12', 'BT93', 'BT48', 'BT22', 'BT21', 'BT51', 'BT49', 'BT27', 'BT40', 'BT17', 'BT6', 'BT66', 'BT56', 'BT26', 'BT69', 'BT52', 'BT5', 'BA14', nan, 'BT36', 'Bt78', 'BT3', 'BT64', 'BT57', 'Withheld'], dtype=object)
df.Postcode.isnull()
0 False 1 False 2 False 3 False 4 False 5 False 6 False 7 False 8 False 9 False 10 False 11 False 12 False 13 False 14 False 15 False 16 False 17 False 18 False 19 False 20 False 21 False 22 False 23 False 24 False 25 False 26 False 27 False 28 False 29 False ... 1717 False 1718 False 1719 False 1720 False 1721 False 1722 False 1723 False 1724 False 1725 False 1726 False 1727 False 1728 False 1729 False 1730 False 1731 False 1732 False 1733 False 1734 False 1735 False 1736 False 1737 False 1738 False 1739 False 1740 False 1741 False 1742 False 1743 False 1744 False 1745 False 1746 False Name: Postcode, dtype: bool
df.dropna(how='all', inplace=True)
df.Postcode.sort_values().unique()
array(['BA14', 'BT12', 'BT17', 'BT19', 'BT20', 'BT21', 'BT22', 'BT23', 'BT24', 'BT25', 'BT26', 'BT27', 'BT28', 'BT29', 'BT3', 'BT30', 'BT31', 'BT32', 'BT33', 'BT34', 'BT35', 'BT36', 'BT39', 'BT4', 'BT40', 'BT41', 'BT42', 'BT43', 'BT44', 'BT45', 'BT46', 'BT47', 'BT48', 'BT49', 'BT5', 'BT51', 'BT52', 'BT53', 'BT54', 'BT56', 'BT57', 'BT6', 'BT60', 'BT61', 'BT62', 'BT63', 'BT64', 'BT66', 'BT67', 'BT68', 'BT69', 'BT70', 'BT71', 'BT74', 'BT75', 'BT76', 'BT77', 'BT78', 'BT79', 'BT8', 'BT80', 'BT81', 'BT82', 'BT9', 'BT92', 'BT93', 'BT94', 'Bt78', 'Withheld', nan], dtype=object)
# df['Postcode'] = df['Postcode'].apply(str.upper)
df['Postcode'] = df['Postcode'].str.upper()
df[df.Postcode=='BA14'] # df.query("Postcode == 'BA14'")
Company | Postcode | Type | Capacity (kWtH) | Date | Cash | |
---|---|---|---|---|---|---|
1155 | National Trust (Crom Estate) | BA14 | Solid Biomass Boiler | 99.0 | 2014-01-27 | 31564.68 |
1156 | National Trust (Florence Court) | BA14 | Solid Biomass Boiler | 36.0 | 2015-05-28 | 3148.25 |
1158 | National Trust (Innisfee) | BA14 | Solid Biomass Boiler | 99.0 | 2013-11-30 | 22766.50 |
1159 | National Trust (Springhill House) | BA14 | Solid Biomass Boiler | 80.0 | 2013-11-30 | 22717.76 |
1160 | National Trust (Springhill) | BA14 | Solid Biomass Boiler | 30.0 | 2013-11-30 | 4133.90 |
1161 | National Trust (The Argory) | BA14 | Solid Biomass Boiler | 95.0 | 2015-11-01 | 6005.90 |
1162 | National Trust (The Argory) | BA14 | Solid Biomass Boiler | 35.0 | 2015-11-01 | 3448.73 |
df.Cash.sum()
60295840.10000002
df.groupby('Postcode')['Cash'].sum().sort_values().head(20)
Postcode BT56 22349.97 BT21 22866.87 BT64 24221.85 BT36 29881.69 BT17 32642.83 BT6 34574.91 BT52 55697.38 BT3 57728.22 BT20 69649.89 BT4 73901.93 BT22 76448.21 BT63 80514.89 BT19 90062.63 BA14 93785.72 BT9 101001.24 BT8 123077.28 BT5 146119.69 BT40 162768.04 BT54 175783.89 BT31 199843.06 Name: Cash, dtype: float64
f,ax = plt.subplots()
_=df.groupby('Postcode')['Cash'].sum().hist(ax=ax)
f,ax = plt.subplots()
_=df.groupby('Postcode')['Cash'].sum().sort_values(ascending=False).plot.pie(ax=ax)
def make_autopct(total):
def my_autopct(pct):
val = int(round(pct*total/100.0))
return '{p:.1f}% (£{v:,.1f}m)'.format(p=pct,v=val/1000000)
return my_autopct
f,ax = plt.subplots()
_=df.groupby('Postcode')['Cash'].sum().sort_values(ascending=False)\
.plot.pie(ax=ax, autopct=make_autopct(df.Cash.sum()))
ea_url = "http://www.eoni.org.uk/Elections/Electoral-areas"
import urllib
_url = urllib.parse.urlparse(ea_url)
_base_url = _url.scheme + '://' + _url.netloc
import requests
from bs4 import BeautifulSoup as bs
r = bs(requests.get(_url.geturl()).text, 'lxml')
for link in r.find_all('a'):
print(link.contents)
['Sitemap'] ['About EONI'] ['Contact Us'] [<img alt="The Electoral Office for Northern Ireland home" id="p_lt_ctl01_Logo_btnElem_image" src="/getmedia/83efe217-5085-4f5e-b2bd-9088bb8323d0/logo?width=359&height=86&ext=.jpg" title="The Electoral Office for Northern Ireland home"/>, <span id="p_lt_ctl01_Logo_btnElem_lblText"></span>] [<img alt="follow us on Twitter" id="p_lt_ctl03_TwitterFeed_btnElem_image" src="/getmedia/6299767c-bc92-482b-af27-8d6bfaf30c2c/icon-twitter?width=30&height=30&ext=.png" title="follow us on Twitter"/>, <span id="p_lt_ctl03_TwitterFeed_btnElem_lblText"></span>] ['Home'] ['News'] ['Register To Vote'] ['Voting'] ['Elections'] ['Electoral Identity Card'] ['FAQs'] ['Elections'] ['Wards by Constituency (PDF)'] ['Wards by District (PDF)'] ['Wards by District Electoral Area (PDF)'] ['Belfast East Street Index 2017 (XLS)'] ['Belfast North Street Index 2017 (XLS)'] ['Belfast South Street Index 2017 (XLS)'] ['Belfast West Street Index 2017 (XLS)'] ['East Antrim Street Index 2017 (XLS)'] ['East Londonderry Street Index 2017 (XLS)'] ['Fermanagh and South Tyrone Street Index 2017 (XLS)'] ['Foyle Street Index 2017 (XLS)'] ['Lagan Valley Street Index 2017 (XLS)'] ['Mid Ulster Street Index 2017 (XLS)'] ['Newry and Armagh Street Index 2017 (XLS)'] ['North Antrim Street Index 2017 (XLS)'] ['North Down Street Index 2017 (XLS)'] ['South Antrim Street Index 2017 (XLS)'] ['South Down Street Index 2017 (XLS)'] ['Strangford Street Index 2017 (XLS)'] ['Upper Bann Street Index 2017 (XLS)'] ['West Tyrone Street Index 2017 (XLS)'] ['Types of election'] ['Election results and statistics'] ['Election results and statistics 2003 onwards'] ['Elections 2017'] ['NI Assembly Election 2017 - Notices of Appointment of Election Agents'] ['NI Assembly Election 2017 - Result Declarations'] ['NI Assembly Election 2017 - Result Sheet'] ['NI Assembly Election 2017 - Statements of Persons Nominated'] ['Elections 2016'] ['NI Assembly Election 2016 - Result Declarations'] ['NI Assembly Election 2016 - Result Sheets'] ['NI Assembly Election 2016 - Statements of Persons Nominated'] ['NI Assembly Election 2016 - Notices of Appointment of Election Agents'] ['Elections 2015'] ['UK Parliamentary Election - Statements of Persons Nominated'] ['UK Parliamentary Election - Results'] ['UK Parliamentary Election Result - Belfast East'] ['UK Parliamentary Election Result - Belfast North'] ['UK Parliamentary Election Result - Belfast South'] ['UK Parliamentary Election Result - Belfast West'] ['UK Parliamentary Election Result - East Antrim'] ['UK Parliamentary Election Result - East Londonderry'] ['UK Parliamentary Election Result - Fermanagh & South Tyrone'] ['UK Parliamentary Election Result - Foyle'] ['UK Parliamentary Election Result - Lagan Valley'] ['UK Parliamentary Election Result - Mid Ulster'] ['UK Parliamentary Election Result - Newry & Armagh'] ['UK Parliamentary Election Result - North Antrim'] ['UK Parliamentary Election Result - North Down'] ['UK Parliamentary Election Result - South Antrim'] ['UK Parliamentary Election Result - South Down'] ['UK Parliamentary Election Result - Strangford'] ['UK Parliamentary Election Result - Upper Bann'] ['UK Parliamentary Election Result - West Tyrone'] ['Elections 2014'] ['Local Council Elections 2014 - Results by Stage'] ['Elections 2013'] ['Elections 2011'] ['NI Assembly election candidates nominated'] ['NI Assembly election candidates elected'] ['NI Assembly election results by stage'] ['Local Council Election Results by Stage'] ['Belfast West by-election results and statistics'] ['Elections 2010'] ['Election results'] ['Elections 2009'] ['Elections 2007'] ['Results'] ['NI Assembly Election 2007 - Full Results'] ['Statistics'] ['Elections 2005'] ['Elections 2004'] ['Elections 2003'] ['Election results 1973-2001'] ['By-election results'] ['By-elections and substitutions'] ['Electoral areas'] ['Information for local councils'] ['Polling stations'] ['Information for candidates and agents'] ['Count Information Packs'] ['Notice of Appointment of Election Agents'] ['Notice of Issue and Opening of Postal Votes'] ['Statement of Persons Nominated and Notice of Poll'] ['Media Centre'] ['Media Accreditation Form'] ['Accessibility'] ['Contact Us'] ['Copyright'] ['FAQs'] [<img alt="Employers for Disability" id="p_lt_ctl10_FooterLogoRight_btnElem_image" src="/getmedia/2a537dc1-c235-432f-8f3c-880a033334ea/efdni_1?width=129&height=77&ext=.png" title="Employers for Disability"/>, <span id="p_lt_ctl10_FooterLogoRight_btnElem_lblText"></span>] ['i3Digital'] ['Kentico']
constituency_urls = {}
for link in r.find_all('a'):
if str(link.contents[0]).endswith('(XLS)'):
constituency = ' '.join(link.contents[0].split()[:-4])
constituency_urls[constituency] = _base_url+link.get('href')
print(constituency,constituency_urls[constituency])
Belfast East http://www.eoni.org.uk/getmedia/ed993710-4b80-46f5-ae6e-ecaf57ed78e8/Belfast-East-Street-Index-18-11-16_1 Belfast North http://www.eoni.org.uk/getmedia/57e481c9-0299-4cee-b3d5-339f1d902405/Belfast-North-Street-Index-18-11-16 Belfast South http://www.eoni.org.uk/getmedia/4616c6ce-52ba-4695-b09d-4eef48580068/Belfast-South-Street-Index-18-11-16 Belfast West http://www.eoni.org.uk/getmedia/aac9bb32-105d-45b9-9f89-46f237e2f9f7/Belfast-West-Street-Index-18-11-16 East Antrim http://www.eoni.org.uk/getmedia/b8193e4d-d21b-4c93-96cf-54029513f9d2/East-Antrim-Street-Index-18-11-16 East Londonderry http://www.eoni.org.uk/getmedia/9f97bc2a-e95a-4a6f-b46a-7b4c1f7aea99/East-Londonderry-Street-Index-18-11-16 Fermanagh and South Tyrone http://www.eoni.org.uk/getmedia/f03947bc-7eca-4c49-a244-1765e4492407/Fermanagh-and-South-Tyrone-Street-Index-18-11-16 Foyle http://www.eoni.org.uk/getmedia/22100a16-1ed1-4440-a24d-7d0508261d2b/Foyle-Street-Index-18-11-16 Lagan Valley http://www.eoni.org.uk/getmedia/0ae3e92b-8db7-4667-bf49-19a3d8d30fb6/Lagan-Valley-Street-Index-18-11-16 Mid Ulster http://www.eoni.org.uk/getmedia/8071d70b-4d05-44e8-9ab1-c17ebeb2dbb5/Mid-Ulster-Street-Index-18-11-16 Newry and Armagh http://www.eoni.org.uk/getmedia/77fffc7b-8801-4fc2-bda2-42cc1979e711/Newry-and-Armagh-Street-Index-18-11-16 North Antrim http://www.eoni.org.uk/getmedia/966baf78-2dd4-48f9-a1b3-6c9b6abf2f06/North-Antrim-Street-Index-18-11-16 North Down http://www.eoni.org.uk/getmedia/37bd5e8a-273e-4ae4-8cdc-69c380e5139f/North-Down-Street-Index-18-11-16 South Antrim http://www.eoni.org.uk/getmedia/b2e31e8b-f978-4f94-b023-91bc808d3a81/South-Antrim-Street-Index-18-11-16 South Down http://www.eoni.org.uk/getmedia/9fd78e7c-8293-449d-8fa5-8114da47fd7b/South-Down-Street-Index-18-11-16 Strangford http://www.eoni.org.uk/getmedia/266bb298-9cdb-4898-a193-4dc75110d078/Strangford-Street-Index-18-11-16 Upper Bann http://www.eoni.org.uk/getmedia/99a4f2d6-4e3f-451e-98d4-20dcda703c0a/Upper-Bann-Street-Index-18-11-16 West Tyrone http://www.eoni.org.uk/getmedia/c1a00455-b9bb-4b20-b512-688a71eab057/West-Tyrone-Street-Index-18-11-16
pd.read_excel(constituency_urls['North Antrim']).head()
NORTH ANTRIM CONSTITUENCY | Unnamed: 1 | Unnamed: 2 | Unnamed: 3 | |
---|---|---|---|---|
0 | NaN | NaN | NaN | NaN |
1 | Ward | Street | Postal Address | Post Code |
2 | 0401 | BALLINDREEN ROAD | BALLYBOGY, BALLYMONEY | BT53 6PA |
3 | 0401 | BALLYBOGY ROAD | TULLAGHGORE, BALNAMORE | BT53 6NX, BT53 6NY, BT53 6PD, BT53 6PG, BT53 6QD |
4 | 0401 | BALLYCLOGH ROAD | SHELLFIELD, BALLYMONEY | BT53 6NW |
pd.read_excel(constituency_urls['North Antrim'], skiprows=3).head()
Ward | Street | Postal Address | Post Code | |
---|---|---|---|---|
0 | 401 | BALLINDREEN ROAD | BALLYBOGY, BALLYMONEY | BT53 6PA |
1 | 401 | BALLYBOGY ROAD | TULLAGHGORE, BALNAMORE | BT53 6NX, BT53 6NY, BT53 6PD, BT53 6PG, BT53 6QD |
2 | 401 | BALLYCLOGH ROAD | SHELLFIELD, BALLYMONEY | BT53 6NW |
3 | 401 | BALLYMACFIN ROAD | BALLYMACFIN, MOSSIDE | BT53 8EJ |
4 | 401 | BALLYNARRY ROAD | DEEPSTOWN, BALLYMONEY | BT53 8AE |
_cons_df = pd.read_excel(constituency_urls['North Antrim'], skiprows=3)
_cons_df['Base Post Code'] = _cons_df['Post Code'].apply(lambda s: s.split()[0])
_cons_df.head()
Ward | Street | Postal Address | Post Code | Base Post Code | |
---|---|---|---|---|---|
0 | 401 | BALLINDREEN ROAD | BALLYBOGY, BALLYMONEY | BT53 6PA | BT53 |
1 | 401 | BALLYBOGY ROAD | TULLAGHGORE, BALNAMORE | BT53 6NX, BT53 6NY, BT53 6PD, BT53 6PG, BT53 6QD | BT53 |
2 | 401 | BALLYCLOGH ROAD | SHELLFIELD, BALLYMONEY | BT53 6NW | BT53 |
3 | 401 | BALLYMACFIN ROAD | BALLYMACFIN, MOSSIDE | BT53 8EJ | BT53 |
4 | 401 | BALLYNARRY ROAD | DEEPSTOWN, BALLYMONEY | BT53 8AE | BT53 |
_cons_df = pd.read_excel(constituency_urls['North Antrim'], skiprows=3)
_cons_df['Base Post Code'] = _cons_df['Post Code'].apply(lambda s: s.split()[0])
_cons_df.head()
Ward | Street | Postal Address | Post Code | Base Post Code | |
---|---|---|---|---|---|
0 | 401 | BALLINDREEN ROAD | BALLYBOGY, BALLYMONEY | BT53 6PA | BT53 |
1 | 401 | BALLYBOGY ROAD | TULLAGHGORE, BALNAMORE | BT53 6NX, BT53 6NY, BT53 6PD, BT53 6PG, BT53 6QD | BT53 |
2 | 401 | BALLYCLOGH ROAD | SHELLFIELD, BALLYMONEY | BT53 6NW | BT53 |
3 | 401 | BALLYMACFIN ROAD | BALLYMACFIN, MOSSIDE | BT53 8EJ | BT53 |
4 | 401 | BALLYNARRY ROAD | DEEPSTOWN, BALLYMONEY | BT53 8AE | BT53 |
constituency_frames = {}
for _constituency, _url in constituency_urls.items():
_cons_df = pd.read_excel(_url, skiprows=3)
_cons_df['Base Post Code'] = _cons_df['Post Code'].apply(lambda s: s.split()[0])
constituency_frames[_constituency] = _cons_df
constituency_frames['North Antrim'].head()
Ward | Street | Postal Address | Post Code | Base Post Code | |
---|---|---|---|---|---|
0 | 401 | BALLINDREEN ROAD | BALLYBOGY, BALLYMONEY | BT53 6PA | BT53 |
1 | 401 | BALLYBOGY ROAD | TULLAGHGORE, BALNAMORE | BT53 6NX, BT53 6NY, BT53 6PD, BT53 6PG, BT53 6QD | BT53 |
2 | 401 | BALLYCLOGH ROAD | SHELLFIELD, BALLYMONEY | BT53 6NW | BT53 |
3 | 401 | BALLYMACFIN ROAD | BALLYMACFIN, MOSSIDE | BT53 8EJ | BT53 |
4 | 401 | BALLYNARRY ROAD | DEEPSTOWN, BALLYMONEY | BT53 8AE | BT53 |
But there's a better way to do this; add the constituency information to each row using a single assign
Then simply concatenate all the frames together into one big one
_cons_df = pd.read_excel(constituency_urls['North Antrim'], skiprows=3)
_cons_df['Base Post Code'] = _cons_df['Post Code'].apply(lambda s: s.split()[0])
_cons_df['Constituency'] = 'North Antrim'
_cons_df.head()
Ward | Street | Postal Address | Post Code | Base Post Code | Constituency | |
---|---|---|---|---|---|---|
0 | 401 | BALLINDREEN ROAD | BALLYBOGY, BALLYMONEY | BT53 6PA | BT53 | North Antrim |
1 | 401 | BALLYBOGY ROAD | TULLAGHGORE, BALNAMORE | BT53 6NX, BT53 6NY, BT53 6PD, BT53 6PG, BT53 6QD | BT53 | North Antrim |
2 | 401 | BALLYCLOGH ROAD | SHELLFIELD, BALLYMONEY | BT53 6NW | BT53 | North Antrim |
3 | 401 | BALLYMACFIN ROAD | BALLYMACFIN, MOSSIDE | BT53 8EJ | BT53 | North Antrim |
4 | 401 | BALLYNARRY ROAD | DEEPSTOWN, BALLYMONEY | BT53 8AE | BT53 | North Antrim |
constituency_frames = {}
for _constituency, _url in constituency_urls.items():
_cons_df = pd.read_excel(_url, skiprows=3)
_cons_df['Base Post Code'] = _cons_df['Post Code'].apply(lambda s: s.split()[0])
_cons_df['Constituency'] = _constituency
constituency_frames[_constituency] = _cons_df
constituency_frame = pd.concat(constituency_frames.values())
constituency_frame.head()
Ward | Street | Postal Address | Post Code | Base Post Code | Constituency | |
---|---|---|---|---|---|---|
0 | 1401 | ANNAGORA ROAD | PORTADOWN | BT62 4JE | BT62 | Upper Bann |
1 | 1401 | BALLYNARRY ROAD | PORTADOWN | BT62 1TX, BT62 1TY | BT62 | Upper Bann |
2 | 1401 | BATTLEHILL ROAD | PORTADOWN | BT62 4ER | BT62 | Upper Bann |
3 | 1401 | BELMONT ROAD | PORTADOWN | BT62 1RJ, BT62 1RL | BT62 | Upper Bann |
4 | 1401 | BIRCHES ROAD | PORTADOWN | BT62 1LS, BT62 1TL | BT62 | Upper Bann |
We've only got time to look at the postcode-constituency angle, so we'll focus on those columns
constituency_frame[['Base Post Code','Constituency']].head()
Base Post Code | Constituency | |
---|---|---|
0 | BT62 | Upper Bann |
1 | BT62 | Upper Bann |
2 | BT62 | Upper Bann |
3 | BT62 | Upper Bann |
4 | BT62 | Upper Bann |
However, because postcodes suck, the mapping from Post code to Constituency, especially with only the first part as reported in the RHI stats, is not one-to-one
constituency_frame[constituency_frame['Base Post Code'] == 'BT41']\
.Constituency.unique()
array(['East Londonderry', 'Mid Ulster', 'South Antrim', 'East Antrim', 'North Antrim'], dtype=object)
constituency_frame[['Base Post Code','Constituency']]\
.groupby('Base Post Code')['Constituency'].describe()\
.unstack().sort_values('unique').tail()
count | unique | top | freq | |
---|---|---|---|---|
Base Post Code | ||||
BT17 | 448 | 4 | Belfast West | 301 |
BT71 | 825 | 4 | Fermanagh and South Tyrone | 392 |
BT39 | 490 | 4 | South Antrim | 455 |
BT32 | 532 | 4 | Upper Bann | 372 |
BT41 | 704 | 5 | South Antrim | 650 |
postcode_cons_map = constituency_frame[['Base Post Code','Constituency']]\
.groupby('Base Post Code')['Constituency'].max()\
.to_frame().reset_index()
postcode_cons_map
Base Post Code | Constituency | |
---|---|---|
0 | BFPO | South Antrim |
1 | BT | Foyle |
2 | BT1 | Belfast West |
3 | BT10 | Belfast West |
4 | BT11 | Belfast West |
5 | BT12 | Belfast West |
6 | BT13 | Belfast West |
7 | BT14 | South Antrim |
8 | BT15 | Belfast North |
9 | BT16 | Strangford |
10 | BT17 | South Antrim |
11 | BT18 | North Down |
12 | BT19 | Strangford |
13 | BT2 | Belfast South |
14 | BT20 | North Down |
15 | BT21 | Strangford |
16 | BT22 | Strangford |
17 | BT23 | Strangford |
18 | BT24 | Strangford |
19 | BT25 | Upper Bann |
20 | BT26 | Lagan Valley |
21 | BT27 | Lagan Valley |
22 | BT28 | Upper Bann |
23 | BT29 | South Antrim |
24 | BT3 | Belfast East |
25 | BT30 | Strangford |
26 | BT31 | South Down |
27 | BT32 | Upper Bann |
28 | BT32, | Upper Bann |
29 | BT33 | South Down |
... | ... | ... |
54 | BT56 | East Londonderry |
55 | BT57 | North Antrim |
56 | BT6 | Strangford |
57 | BT60 | Newry and Armagh |
58 | BT61 | Newry and Armagh |
59 | BT62 | Upper Bann |
60 | BT63 | Upper Bann |
61 | BT64 | Upper Bann |
62 | BT65 | Upper Bann |
63 | BT66 | Upper Bann |
64 | BT67 | Upper Bann |
65 | BT68 | Fermanagh and South Tyrone |
66 | BT69 | Fermanagh and South Tyrone |
67 | BT7 | Fermanagh and South Tyrone |
68 | BT70 | West Tyrone |
69 | BT71 | Upper Bann |
70 | BT74 | Fermanagh and South Tyrone |
71 | BT75 | Fermanagh and South Tyrone |
72 | BT76 | West Tyrone |
73 | BT77 | Fermanagh and South Tyrone |
74 | BT78 | West Tyrone |
75 | BT79 | West Tyrone |
76 | BT8 | Strangford |
77 | BT80 | Mid Ulster |
78 | BT81 | West Tyrone |
79 | BT82 | West Tyrone |
80 | BT9 | Belfast South |
81 | BT92 | Fermanagh and South Tyrone |
82 | BT93 | West Tyrone |
83 | BT94 | Fermanagh and South Tyrone |
84 rows × 2 columns
SQL-style joins (yes, it does inner, outer, left etc)
postcode_cons_map = postcode_cons_map.rename(columns={'Base Post Code':'Postcode'})
con_df=pd.merge(df,postcode_cons_map, on='Postcode', how='left')
display(df.shape)
display(con_df.shape)
con_df
(1747, 6)
(1747, 7)
Company | Postcode | Type | Capacity (kWtH) | Date | Cash | Constituency | |
---|---|---|---|---|---|---|---|
0 | Aaron Newell | BT39 | Solid Biomass Boiler | 60.0 | 2015-09-04 | 19084.69 | South Antrim |
1 | Acheson & Glover Precast Ltd | BT75 | Solid Biomass Boiler | 99.0 | 2015-11-06 | 27600.66 | Fermanagh and South Tyrone |
2 | Acheson & Glover Precast Ltd | BT75 | Solid Biomass Boiler | 99.0 | 2015-11-06 | 30507.19 | Fermanagh and South Tyrone |
3 | Acheson & Glover Precast Ltd | BT75 | Solid Biomass Boiler | 99.0 | 2015-11-06 | 34416.23 | Fermanagh and South Tyrone |
4 | Acheson & Glover Precast Ltd | BT75 | Solid Biomass Boiler | 99.0 | 2015-07-13 | 50543.44 | Fermanagh and South Tyrone |
5 | Acheson & Glover Precast Ltd | BT75 | Solid Biomass Boiler | 99.0 | 2015-07-13 | 52297.88 | Fermanagh and South Tyrone |
6 | Acheson & Glover Precast Ltd | BT75 | Solid Biomass Boiler | 99.0 | 2015-07-13 | 53369.70 | Fermanagh and South Tyrone |
7 | Acheson & Glover Precast Ltd | BT75 | Solid Biomass Boiler | 99.0 | 2015-07-13 | 53475.84 | Fermanagh and South Tyrone |
8 | ACL Developments Ltd | BT80 | Solid Biomass Boiler | 43.0 | 2015-11-06 | 7209.13 | Mid Ulster |
9 | Adam Cardwell OBO Cardwell Brothers Engineering | BT80 | Solid Biomass Boiler | 99.0 | 2015-10-30 | 34181.14 | Mid Ulster |
10 | Adam Cardwell OBO Cardwell Brothers Engineering | BT80 | Solid Biomass Boiler | 99.0 | 2015-11-04 | 44012.45 | Mid Ulster |
11 | Adam Cardwell OBO Cardwell Brothers Engineering | BT80 | Solid Biomass Boiler | 60.0 | 2015-11-07 | 15282.64 | Mid Ulster |
12 | Adrian & Helen Morrison | BT80 | Solid Biomass Boiler | 199.0 | 2016-02-16 | 8216.57 | Mid Ulster |
13 | Adrian Caldwell | BT81 | Solid Biomass Boiler | 99.0 | 2015-11-02 | 30488.62 | West Tyrone |
14 | Adrian Caldwell | BT81 | Solid Biomass Boiler | 99.0 | 2015-11-02 | 34081.26 | West Tyrone |
15 | Adrian Caldwell | BT81 | Solid Biomass Boiler | 198.0 | 2016-02-26 | 11741.57 | West Tyrone |
16 | Adrian Wilson | BT42 | Solid Biomass Boiler | 99.0 | 2015-10-13 | 21116.78 | South Antrim |
17 | Adrian Wilson | BT42 | Solid Biomass Boiler | 99.0 | 2015-10-13 | 25555.41 | South Antrim |
18 | Agricultural and Industrial Services 2012 Limited | BT62 | Solid Biomass Boiler | 99.0 | 2015-11-09 | 21627.16 | Upper Bann |
19 | Aidan & Julie McKillop | BT44 | Solid Biomass Boiler | 99.0 | 2015-06-16 | 56927.74 | North Antrim |
20 | AJ Plumbing Supplies | BT24 | Solid Biomass Boiler | 43.0 | 2016-02-28 | 1186.51 | Strangford |
21 | AJ Plumbing Supplies | BT32 | Solid Biomass Boiler | 43.0 | 2015-11-17 | 3872.90 | Upper Bann |
22 | AJ Plumbing Supplies | BT34 | Solid Biomass Boiler | 43.0 | 2014-11-21 | 8955.58 | Upper Bann |
23 | Alan & Ann Carson | BT53 | Solid Biomass Boiler | 99.0 | 2015-10-25 | 29642.03 | North Antrim |
24 | Alan Campbell | BT53 | Solid Biomass Boiler | 120.0 | 2016-02-14 | 11277.69 | North Antrim |
25 | Alan Hall | BT76 | Solid Biomass Boiler | 99.0 | 2015-11-10 | 23314.29 | West Tyrone |
26 | Alan McCay OBO Castlewarren Dairy | BT82 | Solid Biomass Boiler | 40.0 | 2015-10-14 | 9223.20 | West Tyrone |
27 | Alan McConnell OBO W,C & A McConnell | BT60 | Solid Biomass Boiler | 99.0 | 2015-03-26 | 34858.36 | Newry and Armagh |
28 | Alan McConnell OBO W,C & A McConnell | BT60 | Solid Biomass Boiler | 99.0 | 2015-03-26 | 35891.35 | Newry and Armagh |
29 | Alan McConnell OBO W,C & A McConnell | BT60 | Solid Biomass Boiler | 99.0 | 2015-03-26 | 37910.84 | Newry and Armagh |
... | ... | ... | ... | ... | ... | ... | ... |
1717 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-05-21 | 38004.08 | NaN |
1718 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-11-02 | 16978.91 | NaN |
1719 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-11-02 | 17333.37 | NaN |
1720 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2014-10-17 | 57834.02 | NaN |
1721 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2014-10-17 | 71399.37 | NaN |
1722 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2014-10-17 | 74397.57 | NaN |
1723 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-25 | 22725.27 | NaN |
1724 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-25 | 28355.31 | NaN |
1725 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-25 | 41155.07 | NaN |
1726 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-25 | 44172.60 | NaN |
1727 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-23 | 62523.73 | NaN |
1728 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-23 | 63436.63 | NaN |
1729 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-11-13 | 36724.48 | NaN |
1730 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-11-13 | 38223.39 | NaN |
1731 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-09 | 50708.91 | NaN |
1732 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-09 | 51967.73 | NaN |
1733 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-09 | 52295.96 | NaN |
1734 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-09 | 53637.02 | NaN |
1735 | Withheld | WITHHELD | Solid Biomass Boiler | 45.0 | 2014-08-31 | 7579.23 | NaN |
1736 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-28 | 46111.60 | NaN |
1737 | Woodland Kitchens (N.I.) Ltd | BT44 | Solid Biomass Boiler | 95.0 | 2015-11-03 | 11811.21 | North Antrim |
1738 | Woodland Kitchens (N.I.) Ltd | BT44 | Solid Biomass Boiler | 95.0 | 2015-11-03 | 16208.70 | North Antrim |
1739 | Woodland Kitchens (N.I.) Ltd | BT44 | Solid Biomass Boiler | 95.0 | 2015-11-03 | 22270.75 | North Antrim |
1740 | Woodland Kitchens (N.I.) Ltd | BT44 | Solid Biomass Boiler | 95.0 | 2015-11-03 | 24337.67 | North Antrim |
1741 | Woodvale Construction Company Ltd | BT79 | Solid Biomass Boiler | 50.0 | 2015-10-27 | 8805.18 | West Tyrone |
1742 | Woodvale Construction Company Ltd | BT79 | Solid Biomass Boiler | 99.0 | 2015-11-09 | 17016.91 | West Tyrone |
1743 | Woodvale Construction Company Ltd | BT79 | Solid Biomass Boiler | 49.0 | 2015-11-09 | 4138.04 | West Tyrone |
1744 | Wrights Poultry Ltd | BT77 | Solid Biomass Boiler | 199.0 | 2016-02-22 | 13727.99 | Fermanagh and South Tyrone |
1745 | Wrights Poultry Ltd | BT77 | Solid Biomass Boiler | 199.0 | 2016-02-22 | 17171.61 | Fermanagh and South Tyrone |
1746 | Youth With A Mission (NI) Ltd | BT34 | Solid Biomass Boiler | 99.0 | 2015-10-20 | 10892.78 | Upper Bann |
1747 rows × 7 columns
Pass-through plotting arguments to matplotlib
(i.e. autopct
)
con_df.loc[con_df.Postcode=='WITHHELD','Constituency'] = 'Withheld'
con_df['Constituency']= con_df.Constituency.fillna('National Trust')
con_df = con_df[~con_df.Cash.isnull()]
f,ax = plt.subplots()
_=con_df.groupby('Constituency')['Cash'].sum().sort_values(ascending=False).plot.pie(ax=ax, autopct='%.2f%%')
f,ax = plt.subplots()
_=con_df.groupby('Constituency')['Cash'].sum().sort_values(ascending=False).plot.pie(ax=ax, autopct=make_autopct(df.Cash.sum()))
f,ax = plt.subplots()
_=con_df.groupby('Constituency')['Cash'].sum().sort_values(ascending=False).plot.pie(ax=ax, autopct=make_autopct(df.Cash.sum()))
con_df.groupby(pd.TimeGrouper(key='Date',freq='M')).size()
Date 2013-01-31 2 2013-02-28 3 2013-03-31 2 2013-04-30 2 2013-05-31 8 2013-06-30 6 2013-07-31 5 2013-08-31 9 2013-09-30 7 2013-10-31 10 2013-11-30 9 2013-12-31 14 2014-01-31 12 2014-02-28 11 2014-03-31 18 2014-04-30 16 2014-05-31 27 2014-06-30 31 2014-07-31 29 2014-08-31 28 2014-09-30 21 2014-10-31 39 2014-11-30 38 2014-12-31 30 2015-01-31 52 2015-02-28 29 2015-03-31 61 2015-04-30 43 2015-05-31 49 2015-06-30 47 2015-07-31 45 2015-08-31 41 2015-09-30 87 2015-10-31 459 2015-11-30 284 2015-12-31 1 2016-01-31 1 2016-02-29 170 Freq: M, dtype: int64
f,ax = plt.subplots()
con_df.groupby(pd.TimeGrouper(key='Date',freq='M'))['Cash'].sum().plot(ax=ax)
<matplotlib.axes._subplots.AxesSubplot at 0x7f8f38267b70>
df.Cash.sum()-27621865.020000003
32673975.080000013
con_df[~con_df.Constituency.isnull()].groupby([pd.TimeGrouper(key='Date',freq='M'),'Constituency'])['Cash']\
.sum().unstack().head()
Constituency | Belfast East | Belfast South | Belfast West | East Londonderry | Fermanagh and South Tyrone | Foyle | Lagan Valley | Mid Ulster | National Trust | Newry and Armagh | North Antrim | North Down | South Antrim | South Down | Strangford | Upper Bann | West Tyrone | Withheld |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Date | ||||||||||||||||||
2013-01-31 | NaN | NaN | NaN | NaN | 64016.50 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 6995.34 | NaN | NaN | NaN | NaN |
2013-02-28 | NaN | NaN | NaN | NaN | 71743.65 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 27822.92 | NaN | NaN | NaN | 19873.24 | NaN |
2013-03-31 | NaN | NaN | 37234.47 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 38638.25 | NaN |
2013-04-30 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 26159.07 | NaN | NaN | NaN | NaN | 30542.34 | NaN | NaN |
2013-05-31 | NaN | NaN | NaN | NaN | 132964.34 | 51611.56 | NaN | 172751.55 | NaN | NaN | NaN | NaN | 31631.25 | NaN | NaN | 84285.55 | 58044.85 | NaN |
from matplotlib import dates
import matplotlib.ticker as tick
def _x_month_formatter(x,y):
return '{year}-{month}'.format(year=x.year, month=x.month)
f,ax = plt.subplots()
_con_df_month_con=con_df.groupby([pd.TimeGrouper(key='Date',freq='M'),'Constituency'])['Cash'].sum().unstack()
_con_df_month_con.plot.bar(ax=ax, stacked=True, cmap=plt.get_cmap('Vega20'))
ax.yaxis.set_major_formatter(tick.FuncFormatter(lambda x,y: '£{:,d}k'.format(x)))
ax.xaxis.set_major_formatter(tick.FixedFormatter(_con_df_month_con.index.map(lambda d: '{}-{}'.format(d.year,d.month))))
_=plt.setp( ax.xaxis.get_majorticklabels(), rotation=60 )
ax.legend(loc='upper center', bbox_to_anchor=(1.35, 1.05),
ncol=1, fancybox=True, shadow=True)
<matplotlib.legend.Legend at 0x7f8f3817fba8>
seaborn
¶Fancy plots and quick analysis
import seaborn as sns
f,ax = plt.subplots()
sns.boxplot(data=df, x=df.Date.dt.year, y='Cash',ax=ax)
<matplotlib.axes._subplots.AxesSubplot at 0x7f8f394d62b0>
df.sort_values('Cash', ascending=False)
Company | Postcode | Type | Capacity (kWtH) | Date | Cash | |
---|---|---|---|---|---|---|
518 | Eglinton (Timber Products) Ltd | BT47 | Solid Biomass Boiler | 990.0 | 2013-10-05 | 252844.05 |
1521 | Stewart Law OBO Oatlands Farm | BT26 | Solid Biomass Boiler | 99.0 | 2014-03-30 | 141430.53 |
1053 | McIlroy Farms Limited | BT51 | Solid Biomass Boiler | 90.0 | 2013-05-09 | 127605.27 |
1520 | Stewart Law OBO Oatlands Farm | BT26 | Solid Biomass Boiler | 99.0 | 2014-03-30 | 124776.63 |
636 | Green Biomass Ltd | BT82 | Solid Biomass Boiler | 99.0 | 2014-04-11 | 119012.29 |
1582 | Tom Forgrave OBO Knockanloan | BT53 | Solid Biomass Boiler | 99.0 | 2014-03-18 | 114769.04 |
1581 | Tom Forgrave OBO Knockanloan | BT53 | Solid Biomass Boiler | 99.0 | 2014-03-09 | 113719.79 |
1257 | Paul Hobson Ltd | BT71 | Solid Biomass Boiler | 99.0 | 2013-07-08 | 112267.25 |
1052 | McIlroy Farms Limited | BT51 | Solid Biomass Boiler | 99.0 | 2014-03-11 | 109827.91 |
1580 | Tom Forgrave OBO Knockanloan | BT53 | Solid Biomass Boiler | 99.0 | 2014-03-09 | 106048.09 |
824 | John Gilliland OBO Brook Hall Estate | BT48 | Solid Biomass Boiler | 99.0 | 2013-10-18 | 105223.64 |
559 | Frederick Maxwell OBO Maxwell Farms | BT77 | Solid Biomass Boiler | 99.0 | 2013-08-08 | 105186.06 |
561 | Frederick Maxwell OBO Maxwell Farms | BT77 | Solid Biomass Boiler | 99.0 | 2014-01-15 | 104605.46 |
560 | Frederick Maxwell OBO Maxwell Farms | BT77 | Solid Biomass Boiler | 99.0 | 2014-01-15 | 103779.48 |
563 | Frederick Maxwell OBO Maxwell Farms | BT77 | Solid Biomass Boiler | 99.0 | 2014-02-26 | 103381.97 |
1665 | William Calderwood | BT44 | Solid Biomass Boiler | 99.0 | 2014-11-25 | 102904.65 |
1585 | Tom Forgrave OBO Knockanloan | BT53 | Solid Biomass Boiler | 99.0 | 2014-07-30 | 101700.90 |
1256 | Paul Hobson Ltd | BT71 | Solid Biomass Boiler | 99.0 | 2014-03-28 | 100857.06 |
798 | Jeremy Hobson | BT70 | Solid Biomass Boiler | 99.0 | 2014-05-05 | 99029.34 |
159 | Ben Fearon | BT62 | Solid Biomass Boiler | 99.0 | 2014-06-02 | 98871.76 |
68 | Andrew McCrea | BT71 | Solid Biomass Boiler | 99.0 | 2014-02-03 | 98765.39 |
1584 | Tom Forgrave OBO Knockanloan | BT53 | Solid Biomass Boiler | 99.0 | 2014-07-30 | 98032.00 |
1281 | Phelim McGee | BT70 | Solid Biomass Boiler | 99.0 | 2014-11-07 | 97639.41 |
797 | Jeremy Hobson | BT70 | Solid Biomass Boiler | 99.0 | 2014-05-05 | 97413.67 |
801 | Jeremy Hobson | BT70 | Solid Biomass Boiler | 99.0 | 2014-06-20 | 97285.54 |
321 | Conor Teague | BT78 | Solid Biomass Boiler | 99.0 | 2013-09-16 | 97280.85 |
36 | Alastair Dale OBO Hillview Farm | BT43 | Solid Biomass Boiler | 99.0 | 2014-01-10 | 96816.09 |
558 | Frederick Maxwell OBO Maxwell Farms | BT77 | Solid Biomass Boiler | 99.0 | 2013-08-08 | 96004.01 |
1280 | Phelim McGee | BT70 | Solid Biomass Boiler | 99.0 | 2014-11-07 | 95665.24 |
800 | Jeremy Hobson | BT70 | Solid Biomass Boiler | 99.0 | 2014-05-06 | 95593.82 |
... | ... | ... | ... | ... | ... | ... |
1624 | Viscount Brookeborough | BT94 | Solid Biomass Boiler | 99.0 | 2015-11-09 | 2258.80 |
112 | Balcas Timber Ltd | BT94 | Solid Biomass Boiler | 49.0 | 2016-02-27 | 2235.47 |
756 | James Currie | BT42 | Solid Biomass Boiler | 99.0 | 2015-11-05 | 2158.91 |
348 | CRANN ENERGY LTD | BT92 | Solid Biomass Boiler | 50.0 | 2016-02-27 | 2057.80 |
1046 | McGrane Nurseries Ltd | BT62 | Solid Biomass Boiler | 199.0 | 2016-02-19 | 1945.81 |
810 | John Adams | BT44 | Solid Biomass Boiler | 43.0 | 2015-11-16 | 1931.14 |
643 | Green Energy Technology Ltd | BT66 | Solid Biomass Boiler | 40.0 | 2015-08-25 | 1897.92 |
291 | Colin Kane | BT24 | Solid Biomass Boiler | 99.0 | 2015-10-27 | 1730.08 |
640 | Green Energy Engineering Ltd | BT25 | Solid Biomass Boiler | 25.0 | 2015-11-09 | 1662.33 |
66 | Andrew McCrea | BT71 | Solid Biomass Boiler | 99.0 | 2015-09-08 | 1655.86 |
67 | Andrew McCrea | BT45 | Solid Biomass Boiler | 99.0 | 2015-10-25 | 1631.68 |
288 | Colin Kane | BT24 | Solid Biomass Boiler | 99.0 | 2015-10-26 | 1519.34 |
900 | Joy Rollston OBO Streamside Poultry Farm | BT60 | Solid Biomass Boiler | 40.0 | 2015-11-10 | 1492.30 |
290 | Colin Kane | BT24 | Solid Biomass Boiler | 99.0 | 2015-10-27 | 1483.71 |
1450 | Shane Muckian OBO HS Heating | BT35 | Solid Biomass Boiler | 16.0 | 2016-02-27 | 1468.95 |
20 | AJ Plumbing Supplies | BT24 | Solid Biomass Boiler | 43.0 | 2016-02-28 | 1186.51 |
1129 | Mosshill Services Ltd | BT41 | Solid Biomass Boiler | 99.0 | 2015-11-16 | 1120.00 |
700 | Heron Bros Ltd | BT45 | Solid Biomass Boiler | 95.0 | 2014-06-04 | 825.11 |
1213 | O'Neill's Irish International Sportswear Ltd | BT82 | Solid Biomass Boiler | 95.0 | 2016-02-23 | 650.04 |
1045 | McGrane Nurseries Ltd | BT62 | Solid Biomass Boiler | 199.0 | 2016-02-19 | 627.91 |
639 | Green Energy Engineering Ltd | BT25 | Solid Biomass Boiler | 25.0 | 2016-02-29 | 603.65 |
701 | Heron Bros Ltd | BT45 | Solid Biomass Boiler | 48.0 | 2014-02-27 | 566.87 |
610 | Gerry Kingston OBO Happy Vale | BT34 | Solar Thermal | 3.0 | 2015-04-03 | 434.14 |
638 | Green Energy Engineering Ltd | BT25 | Solid Biomass Boiler | 25.0 | 2014-03-24 | 288.02 |
1014 | Mary Hegan | BT71 | Solid Biomass Boiler | 60.0 | 2016-02-29 | 262.00 |
1565 | Thomas Troughton | BT71 | Solid Biomass Boiler | 198.0 | 2016-02-24 | 258.97 |
642 | Green Energy Technology Ltd | BT66 | Solid Biomass Boiler | 15.0 | 2015-08-24 | 134.09 |
1566 | Thomas Troughton | BT71 | Solid Biomass Boiler | 198.0 | 2016-02-29 | 84.04 |
1596 | Tri Met Engineering Ltd | BT80 | Solid Biomass Boiler | 99.0 | 2015-11-09 | 73.22 |
1157 | NaN | NaN | (GSHP) | NaN | NaT | NaN |
1747 rows × 6 columns
df[df.Date>=pd.Timestamp('2015/10/01')].sort_values('Cash', ascending=False)
Company | Postcode | Type | Capacity (kWtH) | Date | Cash | |
---|---|---|---|---|---|---|
347 | Crann Energy Ltd | BT92 | Solid Biomass Boiler | 99.0 | 2015-10-12 | 65453.82 |
1288 | PM Processing Ltd | BT92 | Solid Biomass Boiler | 99.0 | 2015-10-14 | 64595.05 |
1728 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-23 | 63436.63 |
1727 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-23 | 62523.73 |
1287 | PM Processing Ltd | BT92 | Solid Biomass Boiler | 99.0 | 2015-10-02 | 59385.45 |
1233 | Patsy McGinley OBO McGinley Poultry | BT78 | Solid Biomass Boiler | 99.0 | 2015-10-12 | 59104.80 |
734 | Inish Glow Ltd | BT92 | Solid Biomass Boiler | 99.0 | 2015-10-26 | 57683.51 |
1264 | Peacock Farms Poultry Ltd | BT82 | Solid Biomass Boiler | 99.0 | 2015-10-14 | 56757.26 |
1265 | Peacock Farms Poultry Ltd | BT82 | Solid Biomass Boiler | 99.0 | 2015-10-14 | 56489.45 |
809 | John Adams | BT44 | Solid Biomass Boiler | 99.0 | 2015-10-13 | 56457.47 |
503 | Ecobiomass NI Ltd | BT71 | Solid Biomass Boiler | 99.0 | 2015-10-26 | 56102.54 |
736 | Inish Glow Ltd | BT92 | Solid Biomass Boiler | 99.0 | 2015-10-26 | 55963.95 |
161 | Ben Fearon | BT62 | Solid Biomass Boiler | 99.0 | 2015-10-20 | 55533.51 |
735 | Inish Glow Ltd | BT92 | Solid Biomass Boiler | 99.0 | 2015-10-26 | 55401.57 |
502 | Ecobiomass NI Ltd | BT71 | Solid Biomass Boiler | 99.0 | 2015-10-26 | 54811.50 |
501 | Ecobiomass NI Ltd | BT71 | Solid Biomass Boiler | 99.0 | 2015-10-26 | 54588.17 |
1619 | Valley Services (NI) Ltd | BT74 | Solid Biomass Boiler | 99.0 | 2015-10-26 | 54528.59 |
500 | Ecobiomass NI Ltd | BT71 | Solid Biomass Boiler | 99.0 | 2015-10-26 | 54335.46 |
1022 | McAleer Farms Ltd | BT78 | Solid Biomass Boiler | 99.0 | 2015-10-01 | 54198.86 |
1262 | Peacock Farms Poultry Ltd | BT82 | Solid Biomass Boiler | 99.0 | 2015-10-14 | 54188.21 |
1694 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-17 | 54175.18 |
1263 | Peacock Farms Poultry Ltd | BT82 | Solid Biomass Boiler | 99.0 | 2015-10-14 | 54155.28 |
517 | Eglinton (Timber Products) Ltd | BT47 | Solid Biomass Boiler | 99.0 | 2015-10-29 | 54012.23 |
1025 | McAleer Farms Ltd | BT78 | Solid Biomass Boiler | 99.0 | 2015-10-01 | 53886.84 |
576 | Gareth Mutch | BT82 | Solid Biomass Boiler | 99.0 | 2015-10-29 | 53862.89 |
1693 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-17 | 53849.29 |
1734 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-09 | 53637.02 |
686 | Hegan Farms Ltd | BT71 | Solid Biomass Boiler | 99.0 | 2015-10-14 | 53598.97 |
1692 | Withheld | WITHHELD | Solid Biomass Boiler | 99.0 | 2015-10-25 | 53241.81 |
1618 | Valley Services (NI) Ltd | BT74 | Solid Biomass Boiler | 99.0 | 2015-10-26 | 53173.83 |
... | ... | ... | ... | ... | ... | ... |
1358 | Robert Hunter OBO Sweet Briar Farms | BT60 | Solid Biomass Boiler | 199.0 | 2016-02-25 | 2825.77 |
1357 | Robert Hunter OBO Sweet Briar Farms | BT60 | Solid Biomass Boiler | 199.0 | 2016-02-25 | 2773.92 |
1214 | O'Neill's Irish International Sportswear Ltd | BT82 | Solid Biomass Boiler | 95.0 | 2016-02-23 | 2613.62 |
1490 | Spark Enterprises Ltd | BT93 | Solid Biomass Boiler | 80.0 | 2015-11-03 | 2537.86 |
1485 | Spa Nursing Homes Ltd | BT5 | Solid Biomass Boiler | 199.0 | 2016-02-28 | 2506.53 |
289 | Colin Kane | BT24 | Solid Biomass Boiler | 99.0 | 2015-10-26 | 2369.04 |
1166 | Natural Waste Management Ltd | BT60 | Solid Biomass Boiler | 60.0 | 2016-02-24 | 2363.93 |
817 | John Crawford | BT77 | Solid Biomass Boiler | 99.0 | 2015-11-17 | 2362.60 |
1624 | Viscount Brookeborough | BT94 | Solid Biomass Boiler | 99.0 | 2015-11-09 | 2258.80 |
112 | Balcas Timber Ltd | BT94 | Solid Biomass Boiler | 49.0 | 2016-02-27 | 2235.47 |
756 | James Currie | BT42 | Solid Biomass Boiler | 99.0 | 2015-11-05 | 2158.91 |
348 | CRANN ENERGY LTD | BT92 | Solid Biomass Boiler | 50.0 | 2016-02-27 | 2057.80 |
1046 | McGrane Nurseries Ltd | BT62 | Solid Biomass Boiler | 199.0 | 2016-02-19 | 1945.81 |
810 | John Adams | BT44 | Solid Biomass Boiler | 43.0 | 2015-11-16 | 1931.14 |
291 | Colin Kane | BT24 | Solid Biomass Boiler | 99.0 | 2015-10-27 | 1730.08 |
640 | Green Energy Engineering Ltd | BT25 | Solid Biomass Boiler | 25.0 | 2015-11-09 | 1662.33 |
67 | Andrew McCrea | BT45 | Solid Biomass Boiler | 99.0 | 2015-10-25 | 1631.68 |
288 | Colin Kane | BT24 | Solid Biomass Boiler | 99.0 | 2015-10-26 | 1519.34 |
900 | Joy Rollston OBO Streamside Poultry Farm | BT60 | Solid Biomass Boiler | 40.0 | 2015-11-10 | 1492.30 |
290 | Colin Kane | BT24 | Solid Biomass Boiler | 99.0 | 2015-10-27 | 1483.71 |
1450 | Shane Muckian OBO HS Heating | BT35 | Solid Biomass Boiler | 16.0 | 2016-02-27 | 1468.95 |
20 | AJ Plumbing Supplies | BT24 | Solid Biomass Boiler | 43.0 | 2016-02-28 | 1186.51 |
1129 | Mosshill Services Ltd | BT41 | Solid Biomass Boiler | 99.0 | 2015-11-16 | 1120.00 |
1213 | O'Neill's Irish International Sportswear Ltd | BT82 | Solid Biomass Boiler | 95.0 | 2016-02-23 | 650.04 |
1045 | McGrane Nurseries Ltd | BT62 | Solid Biomass Boiler | 199.0 | 2016-02-19 | 627.91 |
639 | Green Energy Engineering Ltd | BT25 | Solid Biomass Boiler | 25.0 | 2016-02-29 | 603.65 |
1014 | Mary Hegan | BT71 | Solid Biomass Boiler | 60.0 | 2016-02-29 | 262.00 |
1565 | Thomas Troughton | BT71 | Solid Biomass Boiler | 198.0 | 2016-02-24 | 258.97 |
1566 | Thomas Troughton | BT71 | Solid Biomass Boiler | 198.0 | 2016-02-29 | 84.04 |
1596 | Tri Met Engineering Ltd | BT80 | Solid Biomass Boiler | 99.0 | 2015-11-09 | 73.22 |
915 rows × 6 columns
con_df.groupby('Company').sum().plot.scatter(x="Capacity (kWtH)",y="Cash")
<matplotlib.axes._subplots.AxesSubplot at 0x7f8f38279c50>
f,ax = plt.subplots()
sns.regplot(x="Capacity (kWtH)",y="Cash", data=con_df.groupby('Company').sum(), ax=ax)
<matplotlib.axes._subplots.AxesSubplot at 0x7f8f3829d3c8>
sns.jointplot(x="Capacity (kWtH)",y="Cash", data=con_df.groupby('Company').sum(), kind='reg')
<seaborn.axisgrid.JointGrid at 0x7f8f38db1550>
company_df = con_df.groupby('Company').sum()
company_df['N'] = con_df.groupby('Company').size()
company_df['Cash Per Cap.'] = company_df['Cash']/company_df['Capacity (kWtH)']
company_df.sort_values('Cash Per Cap.', ascending=False)
##http://www.newsletter.co.uk/news/farmer-who-got-660k-for-13-rhi-boilers-says-only-80k-was-profit-1-7870639
Capacity (kWtH) | Cash | N | Cash Per Cap. | |
---|---|---|---|---|
Company | ||||
John Gilliland OBO Brook Hall Estate | 198.0 | 199546.82 | 2 | 1007.812222 |
Green Biomass Ltd | 198.0 | 196804.93 | 2 | 993.964293 |
Conor Teague | 99.0 | 97280.85 | 1 | 982.634848 |
Phelim McGee | 198.0 | 193304.65 | 2 | 976.286111 |
James Hagan and Siobhan Hagan | 297.0 | 281593.06 | 3 | 948.124781 |
Noel Smith Ltd | 198.0 | 187532.13 | 2 | 947.131970 |
Frederick Maxwell OBO Maxwell Farms | 990.0 | 906415.26 | 10 | 915.570970 |
Jeremy Hobson | 738.0 | 665204.04 | 8 | 901.360488 |
Colin Newell | 198.0 | 176478.53 | 2 | 891.305707 |
Bruce McAlister | 396.0 | 352682.15 | 4 | 890.611490 |
McIlroy Farms Limited | 585.0 | 513312.78 | 6 | 877.457744 |
Jeff Wray | 198.0 | 171390.71 | 2 | 865.609646 |
Glenavon House Hotel (1982) Ltd | 198.0 | 167110.13 | 2 | 843.990556 |
William McNaugher | 198.0 | 162308.70 | 2 | 819.740909 |
Harry Nelson | 198.0 | 159773.26 | 2 | 806.935657 |
Charles McAllister | 198.0 | 159016.51 | 2 | 803.113687 |
MP Poultry Ltd | 297.0 | 234159.54 | 3 | 788.415960 |
Wesley Caldwell OBO W&I Caldwell | 198.0 | 151573.75 | 2 | 765.523990 |
Ballindarragh Poultry Farm Ltd | 495.0 | 377145.55 | 5 | 761.910202 |
William Montgomery | 198.0 | 150089.18 | 2 | 758.026162 |
Tom Forgrave OBO Knockanloan | 990.0 | 748120.98 | 10 | 755.677758 |
Ben Fearon | 396.0 | 298521.53 | 4 | 753.842247 |
Legmore Farms Ltd | 396.0 | 293496.26 | 4 | 741.152172 |
GSM Farms Ltd | 297.0 | 219257.87 | 3 | 738.241987 |
Seamus & Jacqueline Mullin | 198.0 | 145718.26 | 2 | 735.950808 |
Steven Wilson OBO Deerfin Farm | 297.0 | 218137.21 | 3 | 734.468721 |
Dermott Tiffney OBO James Tiffney and Sons | 99.0 | 72640.99 | 1 | 733.747374 |
Simon Campbell | 297.0 | 217797.70 | 3 | 733.325589 |
North Down Grain Ltd | 99.0 | 72275.46 | 1 | 730.055152 |
Moore (Unidrill) Ltd | 99.0 | 71804.01 | 1 | 725.293030 |
... | ... | ... | ... | ... |
Stanley Gordon & Andrew Gordon | 398.0 | 15992.25 | 2 | 40.181533 |
D&O Poultry Ltd. | 199.0 | 7889.70 | 1 | 39.646734 |
Meegan Enterprises Ltd | 199.0 | 7842.02 | 1 | 39.407136 |
McMenamin Poultry Ltd | 199.0 | 7685.80 | 1 | 38.622111 |
Department of Agriculture and Rural Development (Enniskillen College) | 300.0 | 11583.97 | 1 | 38.613233 |
Alan McFarland | 199.0 | 7427.52 | 1 | 37.324221 |
Peter Alexander OBO P&S Alexander Poultry | 398.0 | 14821.17 | 2 | 37.239121 |
John Wallace OBO JC&L Wallace | 198.0 | 7324.57 | 1 | 36.992778 |
Gareth Millar OBO Millar SJ & Others | 398.0 | 14566.95 | 2 | 36.600377 |
Derek McKeown OBO Todds Leap Poultry | 199.0 | 7274.04 | 1 | 36.552965 |
McGrane Nurseries Ltd | 997.0 | 35789.74 | 4 | 35.897432 |
John Mulligan OBO Mulligan Poultry | 198.0 | 6811.96 | 1 | 34.403838 |
Thomas Hill | 398.0 | 13659.62 | 2 | 34.320653 |
Norhil Poultry Ltd | 199.0 | 6750.79 | 1 | 33.923568 |
David & Linda Irvine | 199.0 | 6661.04 | 1 | 33.472563 |
Timothy Kernaghan | 398.0 | 13164.85 | 2 | 33.077513 |
Thomas Newell | 199.0 | 6542.70 | 1 | 32.877889 |
Robin Wells | 199.0 | 6107.36 | 1 | 30.690251 |
Clive & Rachael Allen | 199.0 | 6017.87 | 1 | 30.240553 |
Rowan Renewables Ltd | 199.0 | 5959.55 | 1 | 29.947487 |
John McCullough OBO JR, RA & ME McCullough | 398.0 | 11629.99 | 2 | 29.221080 |
Colin Maxwell | 198.0 | 5720.98 | 1 | 28.893838 |
W & A Nixon & Sons Ltd | 199.0 | 5682.95 | 1 | 28.557538 |
Cash Hill Farm Ltd | 198.0 | 5561.82 | 1 | 28.090000 |
Gregory Berry | 199.0 | 5221.99 | 1 | 26.241156 |
Curtis Developments Ltd T/A Curtis Toyota | 198.0 | 5170.90 | 1 | 26.115657 |
Craigfad Farm Limited | 398.0 | 10249.93 | 2 | 25.753593 |
Department of Agriculture and Rural Affairs (Loughry College) | 477.0 | 11964.84 | 2 | 25.083522 |
Derek Compton | 398.0 | 8155.81 | 2 | 20.491985 |
Daly Poultry Ltd. | 999.0 | 15795.00 | 1 | 15.810811 |
859 rows × 4 columns
company_df.sort_values('Cash', ascending=False)
Capacity (kWtH) | Cash | N | Cash Per Cap. | |
---|---|---|---|---|
Company | ||||
Withheld | 4445.0 | 2173362.99 | 46 | 488.945555 |
Frederick Maxwell OBO Maxwell Farms | 990.0 | 906415.26 | 10 | 915.570970 |
Tom Forgrave OBO Knockanloan | 990.0 | 748120.98 | 10 | 755.677758 |
Gareth Nelson | 990.0 | 679634.84 | 10 | 686.499838 |
Jeremy Hobson | 738.0 | 665204.04 | 8 | 901.360488 |
Paul Hobson Ltd | 963.0 | 659540.81 | 13 | 684.881423 |
Colin Newell OBO Ballyboyland Biomass | 990.0 | 545595.34 | 10 | 551.106404 |
Eglinton (Timber Products) Ltd | 1584.0 | 538885.63 | 7 | 340.205574 |
McIlroy Farms Limited | 585.0 | 513312.78 | 6 | 877.457744 |
Ecobiomass NI Ltd | 990.0 | 476383.18 | 10 | 481.195131 |
Mountain View Farm Ltd | 693.0 | 471971.94 | 7 | 681.056190 |
Stewart Law OBO Oatlands Farm | 693.0 | 465542.59 | 7 | 671.778629 |
McCaffrey Aggregates Ltd | 990.0 | 396647.94 | 10 | 400.654485 |
Ballindarragh Poultry Farm Ltd | 495.0 | 377145.55 | 5 | 761.910202 |
John Hogg & Co Ltd | 1089.0 | 368113.69 | 11 | 338.029100 |
L.W. Surphlis & Son Ltd. | 908.0 | 358236.27 | 10 | 394.533337 |
Bruce McAlister | 396.0 | 352682.15 | 4 | 890.611490 |
Ballybrocky Poultry Farms Ltd | 891.0 | 346633.01 | 9 | 389.038171 |
Dennison Commercials Ltd | 1089.0 | 343734.72 | 11 | 315.642534 |
Hegan Farms Ltd | 693.0 | 315511.64 | 7 | 455.283752 |
Valley Services (NI) Ltd | 594.0 | 305629.59 | 6 | 514.527929 |
Acheson & Glover Precast Ltd | 693.0 | 302210.94 | 7 | 436.090823 |
Ben Fearon | 396.0 | 298521.53 | 4 | 753.842247 |
Tullysaran Farms Ltd | 594.0 | 294956.31 | 6 | 496.559444 |
Legmore Farms Ltd | 396.0 | 293496.26 | 4 | 741.152172 |
Wendsley Arnold OBO WT & SD Arnold | 990.0 | 290192.50 | 10 | 293.123737 |
Ewing Farms Ltd | 594.0 | 289177.88 | 6 | 486.831448 |
James Hagan and Siobhan Hagan | 297.0 | 281593.06 | 3 | 948.124781 |
Janice Black OBO E&J Black | 495.0 | 281527.29 | 5 | 568.742000 |
Denver McLean | 396.0 | 280009.80 | 4 | 707.095455 |
... | ... | ... | ... | ... |
Mayobridge Golf Club | 32.0 | 5894.37 | 1 | 184.199062 |
Phillips Heating and Stoves Limited | 29.0 | 5859.01 | 1 | 202.034828 |
Mark McKenna OBO KJ Aviation | 35.0 | 5844.75 | 1 | 166.992857 |
Korwind Ltd | 46.0 | 5765.19 | 1 | 125.330217 |
St James of Jerusalem Mullaghbrack | 80.0 | 5748.38 | 1 | 71.854750 |
Colin Maxwell | 198.0 | 5720.98 | 1 | 28.893838 |
W & A Nixon & Sons Ltd | 199.0 | 5682.95 | 1 | 28.557538 |
Cash Hill Farm Ltd | 198.0 | 5561.82 | 1 | 28.090000 |
The Irish Landmark Trust Ltd | 60.0 | 5547.74 | 1 | 92.462333 |
MCM Electrical Contracts Ltd | 43.0 | 5495.24 | 1 | 127.796279 |
Colloide Engineering Systems Ltd | 49.0 | 5438.71 | 1 | 110.994082 |
Stanley Simpson OBO TS & MY Simpson | 60.0 | 5422.96 | 1 | 90.382667 |
Colin Bolton OBO CR Bolton Sole Trader | 60.0 | 5332.69 | 1 | 88.878167 |
Madeline Quinn OBO Caulfield House | 43.0 | 5322.51 | 1 | 123.779302 |
Solmatix Ltd | 28.0 | 5227.45 | 1 | 186.694643 |
Gregory Berry | 199.0 | 5221.99 | 1 | 26.241156 |
Connor Ward OBO Camera Guest House | 32.0 | 5177.01 | 1 | 161.781563 |
Curtis Developments Ltd T/A Curtis Toyota | 198.0 | 5170.90 | 1 | 26.115657 |
Manfreight Ltd | 25.0 | 5168.61 | 1 | 206.744400 |
Robert Greer | 36.0 | 5136.26 | 1 | 142.673889 |
Cloughbane Farm Foods Ltd | 99.0 | 5123.75 | 1 | 51.755051 |
Shane McKenna OBO McKenna Haulage | 49.0 | 5108.76 | 1 | 104.260408 |
Oakra Hardwood Flooring Ltd | 40.0 | 5012.98 | 1 | 125.324500 |
Department of Agriculture and Rural Affairs (Greenmount College) | 60.0 | 4895.07 | 1 | 81.584500 |
Culmore Energy | 99.0 | 4332.12 | 1 | 43.758788 |
National Trust (Springhill) | 30.0 | 4133.90 | 1 | 137.796667 |
Oakgrove Cabins ltd | 40.0 | 3538.21 | 1 | 88.455250 |
National Trust (Florence Court) | 36.0 | 3148.25 | 1 | 87.451389 |
Oakra Hardwood Flooring ltd | 60.0 | 3045.65 | 1 | 50.760833 |
CRANN ENERGY LTD | 50.0 | 2057.80 | 1 | 41.156000 |
859 rows × 4 columns
cufflinks
¶Fancy interactive plotting with plotly
import cufflinks
con_df.groupby(pd.TimeGrouper(key='Date',freq='M'))['Cash'].sum()\
.iplot()
_con_df_month_con=con_df.groupby([pd.TimeGrouper(key='Date',freq='M'),'Constituency'])['Cash'].sum().unstack()
_con_df_month_con.iplot()
# https://plot.ly/~bolster/211
_con_df_month_con.iplot(kind='bar',
barmode='stack',
filename='pybfs-stackedbar-may-17',
world_readable=True)
_con_df_month_con.iplot(kind='heatmap',colorscale='spectral')
_con_df_month_con.head()
Constituency | Belfast East | Belfast South | Belfast West | East Londonderry | Fermanagh and South Tyrone | Foyle | Lagan Valley | Mid Ulster | National Trust | Newry and Armagh | North Antrim | North Down | South Antrim | South Down | Strangford | Upper Bann | West Tyrone | Withheld |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Date | ||||||||||||||||||
2013-01-31 | NaN | NaN | NaN | NaN | 64016.50 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 6995.34 | NaN | NaN | NaN | NaN |
2013-02-28 | NaN | NaN | NaN | NaN | 71743.65 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 27822.92 | NaN | NaN | NaN | 19873.24 | NaN |
2013-03-31 | NaN | NaN | 37234.47 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 38638.25 | NaN |
2013-04-30 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 26159.07 | NaN | NaN | NaN | NaN | 30542.34 | NaN | NaN |
2013-05-31 | NaN | NaN | NaN | NaN | 132964.34 | 51611.56 | NaN | 172751.55 | NaN | NaN | NaN | NaN | 31631.25 | NaN | NaN | 84285.55 | 58044.85 | NaN |
_con_df_month_con.sum().sort_values(ascending=False)
Constituency West Tyrone 13496893.09 Upper Bann 11254545.39 North Antrim 8215759.26 Fermanagh and South Tyrone 5933986.94 South Antrim 5497459.97 Mid Ulster 4911558.45 Newry and Armagh 4316339.85 Withheld 2173362.99 Strangford 1365137.15 Lagan Valley 1043198.32 East Londonderry 678555.61 South Down 403570.20 Foyle 352278.21 Belfast West 257127.67 North Down 143551.82 Belfast South 101001.24 National Trust 93785.72 Belfast East 57728.22 dtype: float64
_con_df_month_con[_con_df_month_con.sum().sort_values(ascending=False).index]\
.iplot(kind='heatmap',colorscale='spectral', filename='pybfs-heatmap-may-17',world_readable=True)
from tqdm import tqdm, tqdm_notebook, tqdm_pandas, tnrange
from time import sleep
for j in tqdm_notebook(range(100), desc='2nd loop'):
sleep(0.01)
_df = pd.DataFrame(np.random.randint(0, 100, (100000, 6)))
tn=tqdm_notebook()
tn.pandas(desc='Wrong kind of bar')
def worker(value):
sleep(0.1)
return value.sum()
_df.progress_apply(worker)
0 4958893 1 4954431 2 4964293 3 4939769 4 4954198 5 4940869 dtype: int64
_df = pd.DataFrame(np.random.randint(0, 100, (100000, 6)))
tn=tqdm_notebook()
tn.pandas(desc='Wrong kind of bar')
def worker(value):
sleep(0.1)
if not value.sum()%10:
tn.write('Teven')
return value.sum()
_df.progress_apply(worker)
0 4961046 1 4942012 2 4952551 3 4969861 4 4950967 5 4951012 dtype: int64
df[df.Company.str.contains('urch')]
--------------------------------------------------------------------------- ValueError Traceback (most recent call last) <ipython-input-225-1ac991022ec2> in <module>() ----> 1 df[df.Company.str.contains('urch')] /home/bolster/anaconda3/lib/python3.5/site-packages/pandas/core/frame.py in __getitem__(self, key) 2051 if isinstance(key, (Series, np.ndarray, Index, list)): 2052 # either boolean or fancy integer index -> 2053 return self._getitem_array(key) 2054 elif isinstance(key, DataFrame): 2055 return self._getitem_frame(key) /home/bolster/anaconda3/lib/python3.5/site-packages/pandas/core/frame.py in _getitem_array(self, key) 2078 def _getitem_array(self, key): 2079 # also raises Exception if object array with NA values -> 2080 if com.is_bool_indexer(key): 2081 # warning here just in case -- previously __setitem__ was 2082 # reindexing but __getitem__ was not; it seems more reasonable to /home/bolster/anaconda3/lib/python3.5/site-packages/pandas/core/common.py in is_bool_indexer(key) 199 if not lib.is_bool_array(key): 200 if isnull(key).any(): --> 201 raise ValueError('cannot index with vector containing ' 202 'NA / NaN values') 203 return False ValueError: cannot index with vector containing NA / NaN values
df.groupby('Company')['Capacity (kWtH)'].sum().sort_values(ascending=False).head()
Company Withheld 4445.0 Eglinton (Timber Products) Ltd 1584.0 Jason Browne OBO EJ&MJ Browne 1387.0 John Hogg & Co Ltd 1089.0 Dennison Commercials Ltd 1089.0 Name: Capacity (kWtH), dtype: float64
df.groupby('Company').size().sort_values(ascending=False).head()
Company Withheld 46 Paul Hobson Ltd 13 John Hogg & Co Ltd 11 Dennison Commercials Ltd 11 Ecobiomass NI Ltd 10 dtype: int64
con_df[con_df.Date > pd.Timestamp('2015/09/28')].groupby('Constituency')['Cash'].sum().sort_values(ascending=False).plot.barh()
<matplotlib.axes._subplots.AxesSubplot at 0x7f8f5d1967b8>
# All-time ratios of how much cash each constituency got
con_ratio = con_df.groupby('Constituency')['Cash'].sum()/con_df['Cash'].sum()
# October/November/December 2015 ratios of how much cash each constituency got
nov_con_ratio = con_df[con_df.Date > pd.Timestamp('2015/09/28')].groupby('Constituency')['Cash'].sum()/con_df[con_df.Date > pd.Timestamp('2015/09/28')]['Cash'].sum()
# How much each constituency increased above their normal application cost ratio in the final months of RHI
pd.concat([con_ratio, nov_con_ratio], keys=['fuggit','this'], axis=1).diff(axis=1)['this'].plot.barh()
<matplotlib.axes._subplots.AxesSubplot at 0x7f8f5d0c2128>
con_df.to_hdf('datastore.h5','may')
old_con_df = pd.read_hdf('datastore.h5','original')
def ratio(con_df):
return con_df.groupby('Constituency')['Cash'].sum()/con_df['Cash'].sum()
pd.concat(map(ratio, [old_con_df, con_df]),keys=['old','new'], axis=1).diff(axis=1)['new'].plot.barh()
<matplotlib.axes._subplots.AxesSubplot at 0x7f8f3956cc50>
pd.concat(map(ratio, [con_df, old_con_df]),keys=['new','old'], axis=1)
new | old | |
---|---|---|
Belfast East | 0.000957 | NaN |
Belfast South | 0.001675 | NaN |
Belfast West | 0.004264 | 0.009309 |
East Londonderry | 0.011254 | 0.010527 |
Fermanagh and South Tyrone | 0.098415 | 0.133955 |
Foyle | 0.005842 | 0.004451 |
Lagan Valley | 0.017301 | 0.009091 |
Mid Ulster | 0.081458 | 0.106291 |
National Trust | 0.001555 | NaN |
Newry and Armagh | 0.071586 | 0.095074 |
North Antrim | 0.136257 | 0.034311 |
North Down | 0.002381 | 0.002522 |
South Antrim | 0.091175 | 0.081656 |
South Down | 0.006693 | 0.013669 |
Strangford | 0.022641 | 0.015583 |
Upper Bann | 0.186655 | 0.210182 |
West Tyrone | 0.223845 | 0.250086 |
Withheld | 0.036045 | NaN |
ratio(con_df)
Constituency Belfast East 0.000957 Belfast South 0.001675 Belfast West 0.004264 East Londonderry 0.011254 Fermanagh and South Tyrone 0.098415 Foyle 0.005842 Lagan Valley 0.017301 Mid Ulster 0.081458 National Trust 0.001555 Newry and Armagh 0.071586 North Antrim 0.136257 North Down 0.002381 South Antrim 0.091175 South Down 0.006693 Strangford 0.022641 Upper Bann 0.186655 West Tyrone 0.223845 Withheld 0.036045 Name: Cash, dtype: float64