Isle of Wight Local Elections¶

#Define scraper sqlite database filename
dbname= "norfolk.sqlite"

#Optionally delete the previous instance
!rm {dbname}

#Filename for map of candidates
mapname='norfolkmap.html'

# The localarea is a search keyword we look for in the address of a companies
# that may be associated with directors with the same exact name as a candidate
# USe it to just limit the dispplay of companies to companies with addresses that contain that keyword
localarea='Norfolk'

#Candidates filename
candsfilename='norfolkcands.csv'

#Supporters filename
supportersfilename='norfolksupporters.csv'

#filename for companies
companiesfilename='norfolkcos.csv'

#Path to the Notice of election file (note, this can be the path/name of a local file)
url='https://www.south-norfolk.gov.uk/sites/default/files/Notice%20of%20Poll.pdf'

#Companies House API token
#Available from: https://developer.companieshouse.gov.uk/api/docs/index/gettingStarted/apikey_authorisation.html
CH_API_TOKEN=''

#Set the path to the pip installer for your Python kernel

#It may be available on your path directly, or you may need to specify a path, as below, or pip3
#pip='~/anaconda/bin/pip'
#pip='pip3'

pip='pip'

#Configure name of scraper sqlite database file
import os
os.environ["SCRAPERWIKI_DATABASE_NAME"] ='sqlite:///{}'.format(dbname)

# SCRAPER CODE REUSED FROM A LONG TIME AGO, WITH MINOR TWEAKS

#code from https://classic.scraperwiki.com/scrapers/iw_poll_notices_scrape/
#with a couple of minor tweaks - seach for strings using 'in' rather than 'startwsith'
import scraperwiki
import requests, lxml.etree

#Current local election notice of election PDF for Isle of Wight - looks to be same format-ish as before
#url='https://www.iwight.com/azservices/documents/1174-Notice-of-Poll-IOWC-2017.pdf'


#Read in the Notice of Poll PDF
pdfdata = requests.get(url).content

#Convert PDF to XML - this breaks in Python 3?
xmldata = scraperwiki.pdftoxml(pdfdata)

root = lxml.etree.fromstring(xmldata)
pages = list(root)

# this function has to work recursively because we might have "<b>Part1 <i>part 2</i></b>"
def gettext_with_bi_tags(el):
    res = [ ]
    if el.text:
        res.append(el.text)
    for lel in el:
        res.append("<%s>" % lel.tag)
        res.append(gettext_with_bi_tags(lel))
        res.append("</%s>" % lel.tag)
        if el.tail:
            res.append(el.tail)
    return "".join(res).strip()


#Scrape the separate pages
#print(pages)
for page in pages:
    data={'stations':[]}
    phase=0
    for el in page:
        #print(el.attrib, gettext_with_bi_tags(el))
        if 'Election of' in gettext_with_bi_tags(el):
            phase=1
            continue
        tmp=gettext_with_bi_tags(el).strip()
        if phase==1:
            if tmp=='':pass
            else:
                data['ward']=tmp
                phase=phase+1
        elif phase==2:
            if 'Proposers' in tmp:
                phase=3
                record={'candidate':[],'address':[],'desc':[],'proposers':[],'seconders':[]}
                data['list']=[]
                continue
        elif phase==3:
            if tmp.strip()=='':
                phase=4
                #print('-------------------------------')
                data['list'].append(record)
                continue
            elif int(el.attrib['left'])<100:
                if record['address']!=[]:
                    data['list'].append(record)
                    record={'candidate':[],'address':[],'desc':[],'proposers':[],'seconders':[]}
                record['candidate'].append(tmp)
            elif int(el.attrib['left'])<300: record['address'].append(tmp)
            elif int(el.attrib['left'])<450: record['desc'].append(tmp)
            elif int(el.attrib['left'])<600:
                if tmp.startswith('('): record['proposers'][-1]=record['proposers'][-1]+' '+tmp
                elif len(record['proposers'])>0 and record['proposers'][-1].strip().endswith('-'): record['proposers'][-1]=record['proposers'][-1]+tmp
                elif len(record['proposers'])>0 and record['proposers'][-1].strip().endswith('.'): record['proposers'][-1]=record['proposers'][-1]+' '+tmp
                else: record['proposers'].append(tmp)
            elif int(el.attrib['left'])<750:
                if tmp.startswith('('): record['seconders'][-1]=record['seconders'][-1]+' '+tmp
                elif len(record['seconders'])>0 and record['seconders'][-1].strip().endswith('-'): record['seconders'][-1]=record['seconders'][-1]+tmp
                elif len(record['seconders'])>0 and record['seconders'][-1].strip().endswith('.'): record['seconders'][-1]=record['seconders'][-1]+' '+tmp
                else: record['seconders'].append(tmp)
        elif phase==4:
            if 'persons entitled to vote' in tmp:
                phase=5
                record={'station':[],'range':[]}
                continue
        elif phase==5: #Not implemented... TO DO
            #print(el.attrib, gettext_with_bi_tags(el))
            if tmp.strip()=='':
                data['stations'].append(record)
                break #The following bits are broken...
            #need to add situation
            elif int(el.attrib['left'])<100:
                if record['range']!=[]:
                    data['stations'].append(record)
                    record={'situation':[],'station':[],'range':[]}
                record['station'].append(tmp)
            elif int(el.attrib['left'])>300:
                record['range'].append(tmp)
    #print(data)
    tmpdata=[]
    for station in data['stations']:
        tmpdata.append({'ward':data['ward'],
                        #'situation':' '.join(station['situation']),
                        'station':' '.join(station['station']),
                        'range':' '.join(station['range'])})
    scraperwiki.sqlite.save(unique_keys=[], table_name='stations', data=tmpdata)
    tmpdata=[]
    tmpdata2=[]
#'desc': ['The Conservative Party', 'Candidate'], 'candidate': ['OULTON', 'Erica'], 'address': ['Blandings, Horringford,', 'Arreton, IW, PO30 3AP']
    for candidate in data['list']:
        tmpdata.append( {'ward':data['ward'],'candidate':' '.join(candidate['candidate']).encode('ascii','ignore'),
                         'address':' '.join(candidate['address']),'desc':' '.join(candidate['desc']) } )
        party=' '.join(candidate['desc']).replace('Candidate','').strip()
        cand=' '.join(candidate['candidate']).encode('ascii','ignore')
        cs=cand.strip(' ').split(' ')
        if len(cs)>2:
            cand2=cs[:2]
            for ci in cs[2:]:
                cand2.append(ci[0]+'.')
        else: cand2=cs
        ctmp=cand2[0]
        cand2.remove(ctmp)
        cand2.append(ctmp.title())
        candi=' '.join(cand2).encode('ascii','ignore')
        for proposer in candidate['proposers']:
            if proposer.find('(+)')>-1:
                proposer=proposer.replace('(+)','').strip()
                typ='proposer'
            else:typ='assentor'
            tmpdata2.append({ 'ward':data['ward'],'candidate':cand, 'candinit':candi, 'support':proposer,'role':'proposal', 'typ':typ, 'desc':party }.copy())
        for seconder in candidate['seconders']:
            if seconder.find('(++)')>-1:
                seconder=seconder.replace('(++)','').strip().encode('ascii','ignore')
                typ='seconder'
            else:typ='assentor'
            tmpdata2.append({ 'ward':data['ward'],'candidate':cand, 'candinit':candi, 'support':seconder,'role':'seconding', 'typ':typ, 'desc':party }.copy())

    scraperwiki.sqlite.save(unique_keys=[], table_name='candidates', data=tmpdata)
    scraperwiki.sqlite.save(unique_keys=[], table_name='support', data=tmpdata2)

#Save supportes data file
candidates.to_csv(candsfilename,index=False)

import geocoder
def gc(address):
    g=geocoder.google(address)
    try:
        return '{},{}'.format(g.latlng[0],g.latlng[1])
    except:
        pass
    try:
        pc=address.split(',')[-1].strip()
        if pc.startswith('PO'):
            g=geocoder.google(pc)
            return '{},{}'.format(g.latlng[0],g.latlng[1])
        else: return ''
    except:
        return ''

candidates['latlong']=candidates['address'].apply(gc)

#save the map
localmap.save(mapname)

#Save supportes data file
supporters.to_csv(supportersfilename,index=False)

colourmap={'Liberal Democrat':'yellow', 'Independent':'black', 'Labour Party':"red",
       'Conservative Party':'blue', 'Green Party':'green', 'UKIP':'purple',
       'Labour and Co- operative Party':'red'}

#Create a graph
import networkx as nx

#G=nx.from_pandas_dataframe(supporters, 'support', 'candinit')
def build_graph(row,DG):
    DG.add_node(row['support'],color=colourmap[row['desc']])
    DG.add_node(row['candinit'],color=colourmap[row['desc']])
    DG.add_edge(row['support'],row['candinit'],color=colourmap[row['desc']])
    return

DG=nx.DiGraph()
supporters.apply(lambda x: build_graph(x,DG), axis=1);

nodes = DG.nodes()
edges = DG.edges()

#filter on people who are supported and who support
supports_deg = DG.out_degree(nodes)
supported_deg = DG.in_degree(nodes)
supports = [n for n in supports_deg if supports_deg[n]]
supported = [n for n in supported_deg if supported_deg[n]]

GG=nx.DiGraph()
#Merge the egographs of people of people who support and are supported
for s2 in list(set(supports).intersection(set(supported))):
    GG=nx.compose(GG,nx.ego_graph(DG,s2,5))

import urllib2, base64, json
from urllib import urlencode
from time import sleep

def url_nice_req(url,t=300):
    try:
        return urllib2.urlopen(url)
    except urllib2.HTTPError, e:
        if e.code == 429:
            print("Overloaded API, resting for a bit...")
            sleep(t)
            return url_req(url)
        
#Inspired by http://stackoverflow.com/a/2955687/454773
def ch_request(CH_API_TOKEN,url,args=None):
    if args is not None:
        url='{}?{}'.format(url,urlencode(args))
    request = urllib2.Request(url)
    # You need the replace to handle encodestring adding a trailing newline 
    # (https://docs.python.org/2/library/base64.html#base64.encodestring)
    base64string = base64.encodestring('%s:' % (CH_API_TOKEN)).replace('\n', '')
    request.add_header("Authorization", "Basic %s" % base64string)   
    result = url_nice_req(request)

    #This is too hacky - need to see why it fails if it does
    if result is None:
        print('Oops: {}, {}'.format(url,result))
        return None
        
    j=json.loads(result.read())        
        
    return j

def ch_getAppointments(slug,location=None,typ='all',role='all',n=500,start_index=''):
    if len(slug.split('/'))==1:
        slug='/officers/{}/appointments'.format(slug)
    url= 'https://api.companieshouse.gov.uk{}'.format(slug)
    properties={'items_per_page':n,'start_index':start_index}
    a=ch_request(CH_API_TOKEN,url,properties)

    if a is None: return None
    
    if location is not None:
        a['items']=[i for i in a['items'] if location.lower() in i['address']['locality'].lower()]
    if typ=='current':
        a['items']=[i for i in a['items'] if 'resigned_on' not in i]
        a['items']=[i for i in a['items'] if 'company_status' in i['appointed_to'] and i['appointed_to']['company_status'] == 'active']
        #should possibly check here that len(co['items'])==co['active_count'] ?
    elif typ=='previous':
        a['items']=[i for i in a['items'] if 'resigned_on' in i]
    elif typ=='dissolved':
        a['items']=[i for i in a['items'] if 'company_status' in i['appointed_to'] and i['appointed_to']['company_status'] == 'dissolved']

    if role!='all':
        a['items']=[i for i in a['items'] if role==i['officer_role']]
    return a

def ch_searchOfficers(name,n=50,start_index='',company='',companies=False,exact=None):
    url= 'https://api.companieshouse.gov.uk/search/officers'
    properties={'q':name,'items_per_page':n,'start_index':start_index} 
    o=ch_request(CH_API_TOKEN,url,properties)
    
    if o is None: return o
    
    if exact=='forename':
        #This isn't right eg double barrelled surnames
        s=name.lower().split(' ')
        o['items'] = [i for i in o['items'] if i['title'].lower().split(' ')[0]==s[0] and i['title'].lower().split(' ')[-1]==s[-1]]
    elif exact=='fullname':
        o['items'] = [i for i in o['items'] if i['title'].lower()==name.lower()]
    if company != '':
        for p in o['items']:
            p['items'] = [i for i in ch_getAppointments(p['links']['self'])['items'] if company.lower() in i['appointed_to']['company_name'].lower()]
        o['items'] = [i for i in o['items'] if len(i['items'])]
    if companies:
        for p in o['items']:
            p['items'] = [i for i in ch_getAppointments(p['links']['self'])['items']]
        o['items'] = [i for i in o['items'] if len(i['items'])]
    return o

appointments=pd.DataFrame()
for c in candidates['candidate'].tolist():
    name=c.split()
    cand=' '.join(name[1:]+name[0:1])
    results=ch_searchOfficers(cand,n=50,exact='fullname')
    for result in results['items']:
        appointments=pd.concat([appointments,pd.DataFrame.from_dict([result])])

companies=pd.DataFrame()
localAppointments=appointments[appointments['address_snippet'].str.contains(localarea)]
for appointment in localAppointments['links'].apply(pd.Series)['self'].tolist():
    ddx=pd.DataFrame.from_dict(ch_getAppointments(appointment)['items'])
    tmp=pd.concat([ddx.drop(['appointed_to','name_elements','links','address'], axis=1),
                   ddx['appointed_to'].apply(pd.Series),ddx['address'].apply(pd.Series)], axis=1)
    companies=pd.concat([companies,tmp])

#Save company data file
localcos.to_csv(companiesfilename,index=False)