import fiona
#This is a canned demo - I happen to have the Local Authority Code for the Isle of Wight...
#...and copies of ward geojson files by LA from https://github.com/martinjc/UK-GeoJSON
geojson_local='IWgeodata/wards_by_lad/E06000046.json'
fi=fiona.open(geojson_local)
centre_lat,centre_lon=((fi.bounds[0]+fi.bounds[2])/2,(fi.bounds[1]+fi.bounds[3])/2)
import json
with open(geojson_local) as f:
jdata = json.load(f)
for j in jdata['features']:
print(j['properties']['WD13CD'],j['properties']['WD13NM'])
E05008479 Arreton and Newchurch E05008480 Binstead and Fishbourne E05008481 Brading, St Helens and Bembridge E05008482 Carisbrooke E05008483 Central Wight E05008484 Chale, Niton and Whitwell E05008485 Cowes Medina E05008486 Cowes North E05008487 Cowes South and Northwood E05008488 Cowes West and Gurnard E05008489 East Cowes E05008490 Freshwater North E05008491 Freshwater South E05008492 Godshill and Wroxall E05008493 Havenstreet, Ashey and Haylands E05008494 Lake North E05008495 Lake South E05008496 Nettlestone and Seaview E05008497 Newport Central E05008498 Newport East E05008499 Newport North E05008500 Newport South E05008501 Newport West E05008502 Parkhurst E05008503 Ryde East E05008504 Ryde North East E05008505 Ryde North West E05008506 Ryde South E05008507 Ryde West E05008508 Sandown North E05008509 Sandown South E05008510 Shanklin Central E05008511 Shanklin South E05008512 Totland E05008513 Ventnor East E05008514 Ventnor West E05008515 West Wight E05008516 Whippingham and Osborne E05008517 Wootton Bridge
import pandas as pd
#previous results from https://blog.ouseful.info/2013/05/15/asking-questions-of-data-contained-in-a-google-spreadsheet-using-a-basic-structured-query-language/
url="https://docs.google.com/spreadsheets/d/1oyoF9Tgc2n0q2YksNACVf2AY4crOnJIhndTXyEoTJmQ/export?format=csv"
df=pd.read_csv("https://docs.google.com/spreadsheets/d/1oyoF9Tgc2n0q2YksNACVf2AY4crOnJIhndTXyEoTJmQ/export?format=csv")
df.head(10)
Electoral Division | NoOnRoll | Candidate | CandidateName | Party | Electorate | |
---|---|---|---|---|---|---|
0 | Arreton and Newchurch | 3070 | Erica Oulton (Conservative) | Erica Oulton | Conservative | 458 |
1 | Arreton and Newchurch | 3070 | Colin Richards (Island Independents) | Colin Richards | Island Independents | 529 |
2 | Arreton and Newchurch | 3070 | Ian Sherfield (UKIP) | Ian Sherfield | UKIP | 299 |
3 | Arreton and Newchurch | 3070 | Total votes cast | Total votes cast | NaN | 1286 |
4 | Arreton and Newchurch | 3070 | Postal votes (inc) | Postal votes | NaN | 381 |
5 | Arreton and Newchurch | 3070 | Papers spoilt | Papers spoilt | NaN | 3 |
6 | Binstead and Fishbourne | 2766 | Katie Curtis (Labour) | Katie Curtis | Labour | 61 |
7 | Binstead and Fishbourne | 2766 | Alan Darnell (UKIP) | Alan Darnell | UKIP | 225 |
8 | Binstead and Fishbourne | 2766 | Sylvia Sillar (Conservative) | Sylvia Sillar | Conservative | 315 |
9 | Binstead and Fishbourne | 2766 | Ivor Warlow (Island Independents) | Ivor Warlow | Island Independents | 559 |
#Compare the official ward names with ones in result file
set(df['Electoral Division'].unique()) - set(j['properties']['WD13NM'] for j in jdata['features']),set(j['properties']['WD13NM'] for j in jdata['features']) - set(df['Electoral Division'].unique())
({'Brading, St Helens & Bembridge', 'Chale, Niton & Whitwell', 'Cowes South & Northwood', 'Cowes West & Gurnard', 'Godshill & Wroxall', 'Havenstreet, Ashey & Haylands', 'Nettlestone & Seaview', 'Whippingham & Osborne'}, {'Brading, St Helens and Bembridge', 'Chale, Niton and Whitwell', 'Cowes South and Northwood', 'Cowes West and Gurnard', 'Godshill and Wroxall', 'Havenstreet, Ashey and Haylands', 'Nettlestone and Seaview', 'Whippingham and Osborne'})
#Clean the previous results data
df['Electoral Division']=df['Electoral Division'].str.replace('&','and')
df['Electoral Division'].unique()
array(['Arreton and Newchurch', 'Binstead and Fishbourne', 'Brading, St Helens and Bembridge', 'Carisbrooke', 'Central Wight', 'Chale, Niton and Whitwell', 'Cowes Medina', 'Cowes North', 'Cowes South and Northwood', 'Cowes West and Gurnard', 'East Cowes', 'Freshwater North', 'Freshwater South', 'Godshill and Wroxall', 'Havenstreet, Ashey and Haylands', 'Lake North', 'Lake South', 'Nettlestone and Seaview', 'Newport Central', 'Newport East', 'Newport North', 'Newport South', 'Newport West', 'Parkhurst', 'Ryde East', 'Ryde North East', 'Ryde North West', 'Ryde South', 'Ryde West', 'Sandown North', 'Sandown South', 'Shanklin Central', 'Shanklin South', 'Totland', 'Ventnor East', 'Ventnor West', 'West Wight', 'Whippingham and Osborne', 'Wootton Bridge'], dtype=object)
#Find winning percentages
winners=df[ ~df['Candidate'].isin(['Total votes cast',
'Postal votes',
'Postal votes'])].sort_values('Electorate', ascending=False).groupby('Electoral Division', as_index=False).first()
winners['pc']=100*winners['Electorate']/winners['NoOnRoll']
winners
Electoral Division | NoOnRoll | Candidate | CandidateName | Party | Electorate | pc | |
---|---|---|---|---|---|---|---|
0 | Arreton and Newchurch | 3070 | Colin Richards (Island Independents) | Colin Richards | Island Independents | 529 | 17.231270 |
1 | Binstead and Fishbourne | 2766 | Ivor Warlow (Island Independents) | Ivor Warlow | Island Independents | 559 | 20.209689 |
2 | Brading, St Helens and Bembridge | 6189 | Jonathan Bacon (Island Independents) | Jonathan Bacon | Island Independents | 1308 | 21.134270 |
3 | Carisbrooke | 2632 | John Hobart (Conservative) | John Hobart | Conservative | 431 | 16.375380 |
4 | Central Wight | 2904 | Bob Seely (Conservative) | Bob Seely | Conservative | 700 | 24.104683 |
5 | Chale, Niton and Whitwell | 2297 | Dave Stewart (Conservative) | Dave Stewart | Conservative | 658 | 28.646060 |
6 | Cowes Medina | 3145 | Cllr Lora Peacey-Wilcox (Independent) | Cllr Lora Peacey-Wilcox | Independent | 609 | 19.364070 |
7 | Cowes North | 2603 | Paul Bertie (Conservative) | Paul Bertie | Conservative | 416 | 15.981560 |
8 | Cowes South and Northwood | 2991 | John Nicholson (Conservative) | John Nicholson | Conservative | 518 | 17.318623 |
9 | Cowes West and Gurnard | 3037 | Paul Fuller JP (Independent) | Paul Fuller JP | Independent | 1143 | 37.635825 |
10 | East Cowes | 3039 | Luisa Hillard (Island Independents) | Luisa Hillard | Island Independents | 367 | 12.076341 |
11 | Freshwater North | 2349 | David Eccles (Conservative) | David Eccles | Conservative | 402 | 17.113665 |
12 | Freshwater South | 2465 | John Medland (Independent) | John Medland | Independent | 480 | 19.472617 |
13 | Godshill and Wroxall | 2583 | Rodney Downer (Island Independents) | Rodney Downer | Island Independents | 773 | 29.926442 |
14 | Havenstreet, Ashey and Haylands | 2811 | Conrad Gauntlett (Conservative) | Conrad Gauntlett | Conservative | 287 | 10.209890 |
15 | Lake North | 2835 | Alan Hollands (Labour) | Alan Hollands | Labour | 355 | 12.522046 |
16 | Lake South | 2955 | Ray Bloomfield (Conservative) | Ray Bloomfield | Conservative | 381 | 12.893401 |
17 | Nettlestone and Seaview | 2551 | Reg Barry (Island Independents) | Reg Barry | Island Independents | 830 | 32.536260 |
18 | Newport Central | 3050 | Julie Jones-Evans (Conservative) | Julie Jones-Evans | Conservative | 375 | 12.295082 |
19 | Newport East | 2557 | Geoff Lumley (Labour) | Geoff Lumley | Labour | 443 | 17.324990 |
20 | Newport North | 2475 | Matthew Price (Conservative) | Matthew Price | Conservative | 356 | 14.383838 |
21 | Newport South | 2664 | Shirley Smart (none) | Shirley Smart | none | 365 | 13.701201 |
22 | Newport West | 2416 | Chris Whitehouse (Conservative) | Chris Whitehouse | Conservative | 295 | 12.210265 |
23 | Parkhurst | 2410 | Postal votes (inc) | Postal votes | Conservative | 262 | 10.871369 |
24 | Ryde East | 2968 | Roger Whitby-Smith (Independent) | Roger Whitby-Smith | Independent | 377 | 12.702156 |
25 | Ryde North East | 2871 | Wayne Whittle (Conservative) | Wayne Whittle | Conservative | 324 | 11.285266 |
26 | Ryde North West | 3006 | Phil Jordan (Island Independents) | Phil Jordan | Island Independents | 390 | 12.974052 |
27 | Ryde South | 3177 | Charles Chapman (Island Independents) | Charles Chapman | Island Independents | 256 | 8.057916 |
28 | Ryde West | 2878 | Ian Stephens (Island Independents) | Ian Stephens | Island Independents | 533 | 18.519805 |
29 | Sandown North | 2514 | Bob Blezzard (Sandown Independents) | Bob Blezzard | Sandown Independents | 330 | 13.126492 |
30 | Sandown South | 3058 | Ian Ward (Conservative) | Ian Ward | Conservative | 389 | 12.720733 |
31 | Shanklin Central | 2916 | Jon Gilbey (Island Independents) | Jon Gilbey | Island Independents | 528 | 18.106996 |
32 | Shanklin South | 2983 | Richard Priest (Island Independents) | Richard Priest | Island Independents | 629 | 21.086155 |
33 | Totland | 2500 | John Howe (Island Independents) | John Howe | Island Independents | 420 | 16.800000 |
34 | Ventnor East | 2478 | Graham Perks (UKIP) | Graham Perks | UKIP | 412 | 16.626312 |
35 | Ventnor West | 2539 | Steve Stubbings (Island Independents) | Steve Stubbings | Island Independents | 395 | 15.557306 |
36 | West Wight | 2862 | Stuart Hutchinson (Conservative) | Stuart Hutchinson | Conservative | 494 | 17.260657 |
37 | Whippingham and Osborne | 3330 | Julia Hill (Island Independents) | Julia Hill | Island Independents | 646 | 19.399399 |
38 | Wootton Bridge | 2859 | Daryll Pitcher (UKIP) | Daryll Pitcher | UKIP | 608 | 21.266177 |
#Percentage of no on roll voting for winner
#crib? https://blog.ouseful.info/2015/04/17/creating-interactive-election-maps-using-folium-and-ipython-notebooks/
#http://nbviewer.jupyter.org/gist/psychemedia/fbcd7cf1daabe0004e27/folium_shapefiles.ipynb
import folium
iwmap=folium.Map([centre_lon,centre_lat], zoom_start=11)
iwmap.choropleth(
geo_path=geojson_local,
data=winners,
columns=['Electoral Division', 'pc'],
key_on='feature.properties.WD13NM',
fill_color='PuBuGn', fill_opacity=0.7
)
iwmap
turnout=df[df['Candidate'].isin(['Total votes cast'])][['Electoral Division','Candidate','Electorate','NoOnRoll']]
turnout['turnout']=turnout['Electorate']/turnout['NoOnRoll']
turnout
Electoral Division | Candidate | Electorate | NoOnRoll | turnout | |
---|---|---|---|---|---|
3 | Arreton and Newchurch | Total votes cast | 1286 | 3070 | 0.418893 |
10 | Binstead and Fishbourne | Total votes cast | 1160 | 2766 | 0.419378 |
19 | Brading, St Helens and Bembridge | Total votes cast | 4640 | 6189 | 0.749717 |
25 | Carisbrooke | Total votes cast | 883 | 2632 | 0.335486 |
31 | Central Wight | Total votes cast | 1339 | 2904 | 0.461088 |
37 | Chale, Niton and Whitwell | Total votes cast | 1082 | 2297 | 0.471049 |
43 | Cowes Medina | Total votes cast | 864 | 3145 | 0.274722 |
48 | Cowes North | Total votes cast | 756 | 2603 | 0.290434 |
53 | Cowes South and Northwood | Total votes cast | 1020 | 2991 | 0.341023 |
58 | Cowes West and Gurnard | Total votes cast | 1390 | 3037 | 0.457689 |
65 | East Cowes | Total votes cast | 1191 | 3039 | 0.391905 |
70 | Freshwater North | Total votes cast | 715 | 2349 | 0.304385 |
76 | Freshwater South | Total votes cast | 979 | 2465 | 0.397160 |
83 | Godshill and Wroxall | Total votes cast | 1121 | 2583 | 0.433991 |
90 | Havenstreet, Ashey and Haylands | Total votes cast | 859 | 2811 | 0.305585 |
96 | Lake North | Total votes cast | 860 | 2835 | 0.303351 |
102 | Lake South | Total votes cast | 989 | 2955 | 0.334687 |
108 | Nettlestone and Seaview | Total votes cast | 1368 | 2551 | 0.536260 |
115 | Newport Central | Total votes cast | 786 | 3050 | 0.257705 |
121 | Newport East | Total votes cast | 730 | 2557 | 0.285491 |
128 | Newport North | Total votes cast | 823 | 2475 | 0.332525 |
133 | Newport South | Total votes cast | 701 | 2664 | 0.263138 |
139 | Newport West | Total votes cast | 658 | 2416 | 0.272351 |
146 | Parkhurst | Total votes cast | 674 | 2410 | 0.279668 |
152 | Ryde East | Total votes cast | 974 | 2968 | 0.328167 |
160 | Ryde North East | Total votes cast | 816 | 2871 | 0.284222 |
166 | Ryde North West | Total votes cast | 850 | 3006 | 0.282768 |
174 | Ryde South | Total votes cast | 745 | 3177 | 0.234498 |
181 | Ryde West | Total votes cast | 834 | 2878 | 0.289785 |
189 | Sandown North | Total votes cast | 907 | 2514 | 0.360780 |
196 | Sandown South | Total votes cast | 808 | 3058 | 0.264225 |
202 | Shanklin Central | Total votes cast | 1041 | 2916 | 0.356996 |
207 | Shanklin South | Total votes cast | 1248 | 2983 | 0.418371 |
213 | Totland | Total votes cast | 977 | 2500 | 0.390800 |
220 | Ventnor East | Total votes cast | 1022 | 2478 | 0.412429 |
227 | Ventnor West | Total votes cast | 1140 | 2539 | 0.448996 |
234 | West Wight | Total votes cast | 1062 | 2862 | 0.371069 |
239 | Whippingham and Osborne | Total votes cast | 1044 | 3330 | 0.313514 |
246 | Wootton Bridge | Total votes cast | 1214 | 2859 | 0.424624 |
#Percentage of no on roll voting
#crib? https://blog.ouseful.info/2015/04/17/creating-interactive-election-maps-using-folium-and-ipython-notebooks/
#http://nbviewer.jupyter.org/gist/psychemedia/fbcd7cf1daabe0004e27/folium_shapefiles.ipynb
import folium
iwmap=folium.Map([centre_lon,centre_lat], zoom_start=11)
iwmap.choropleth(
geo_path=geojson_local,
data=turnout,
columns=['Electoral Division', 'turnout'],
key_on='feature.properties.WD13NM',
fill_color='PuBuGn', fill_opacity=0.7
)
iwmap
winners['Party'].unique()
array(['Island Independents', 'Conservative', 'Independent', 'Labour', 'none', 'Sandown Independents', 'UKIP'], dtype=object)
colmap={'Island Independents':'lightblue', 'Conservative':'blue', 'Independent':'black', 'Labour':'red',
'none':'grey', 'Sandown Independents':'darkgrey', 'UKIP':'purple'}
winners['col']=winners['Party'].map(colmap)
winners.head()
Electoral Division | NoOnRoll | Candidate | CandidateName | Party | Electorate | pc | col | |
---|---|---|---|---|---|---|---|---|
0 | Arreton and Newchurch | 3070 | Colin Richards (Island Independents) | Colin Richards | Island Independents | 529 | 17.231270 | lightblue |
1 | Binstead and Fishbourne | 2766 | Ivor Warlow (Island Independents) | Ivor Warlow | Island Independents | 559 | 20.209689 | lightblue |
2 | Brading, St Helens and Bembridge | 6189 | Jonathan Bacon (Island Independents) | Jonathan Bacon | Island Independents | 1308 | 21.134270 | lightblue |
3 | Carisbrooke | 2632 | John Hobart (Conservative) | John Hobart | Conservative | 431 | 16.375380 | blue |
4 | Central Wight | 2904 | Bob Seely (Conservative) | Bob Seely | Conservative | 700 | 24.104683 | blue |
#Ward by Party
#There has to be an easier way of doing qualitative choropleths using folium
#This hack is one I struggled towards finding before...
#http://nbviewer.jupyter.org/gist/psychemedia/fbcd7cf1daabe0004e27/folium_shapefiles.ipynb
jj=json.load(open(geojson_local))
results_map = folium.Map([centre_lon,centre_lat], zoom_start=11)
for c in jj['features']:
#The choropleth format requires that features are provided in a list and a FeatureCollection defined as the type
#So we wrap the feature definition for each constituency in the necessary format
geodata= {"type": "FeatureCollection", "features": [c]}
#Get the name of the seat for the current constituency
ward=c['properties']['WD13NM']
#We can now lookup the colour
colour= colmap[winners[winners['Electoral Division']==ward]['Party'].iloc[0]]
results_map.choropleth(geo_str= json.dumps(geodata),fill_color=colour,fill_opacity=1)
results_map