Land Registry Linked Data - House Price Index Demo¶

#Import the necessary packages
#!pip3 import SPARQLWrapper

from SPARQLWrapper import SPARQLWrapper, JSON

#Add some helper functions
def runQuery(endpoint,prefix,q):
    ''' Run a SPARQL query with a declared prefix over a specified endpoint '''
    sparql = SPARQLWrapper(endpoint)
    sparql.setQuery(prefix+q)
    sparql.setReturnFormat(JSON)
    return sparql.query().convert()

import pandas as pd

def dict2df(results):
    ''' Hack a function to flatten the SPARQL query results and return the column values '''
    data=[]
    for result in results["results"]["bindings"]:
        tmp={}
        for el in result:
            tmp[el]=result[el]['value']
        data.append(tmp)

    df = pd.DataFrame(data)
    return df

def dfResults(endpoint,prefix,q):
    ''' Generate a data frame containing the results of running
        a SPARQL query with a declared prefix over a specified endpoint '''
    return dict2df( runQuery( endpoint, prefix, q ) )
        
def printQuery(results,limit=''):
    ''' Print the results from the SPARQL query '''
    resdata=results["results"]["bindings"]
    if limit!='': resdata=results["results"]["bindings"][:limit]
    for result in resdata:
        for ans in result:
            print('{0}: {1}'.format(ans,result[ans]['value']))
        print()

def printRunQuery(endpoint,prefix,q,limit=''):
    ''' Print the results from the SPARQL query '''
    results=runQuery(endpoint,prefix,q)
    printQuery(results,limit)

#The core of this query is based on an example query from http://landregistry.data.gov.uk/app/hpi/qonsole
prefix_lr='''
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX sr: <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/>
PREFIX lrhpi: <http://landregistry.data.gov.uk/def/hpi/>
PREFIX lrppi: <http://landregistry.data.gov.uk/def/ppi/>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX lrcommon: <http://landregistry.data.gov.uk/def/common/>
'''
q='''
SELECT ?index ?regionName ?yearmonth ?indexr ?region ?avgPriceAll ?avgDetached
  ?avgSemi ?avgFlats ?avgTerraced ?annual ?volume {
  ?region rdfs:label ?regionName.
  
  ?region rdfs:label "Isle of Wight"@en.
  
  ?index a <http://landregistry.data.gov.uk/def/hpi/MonthlyIndicesByRegion>.
  ?index <http://landregistry.data.gov.uk/def/hpi/refRegion> ?region.
  ?index
    lrhpi:refRegion ?regionURI ;
    lrhpi:indicesSASM ?indexr ;
    lrhpi:refPeriod ?yearmonth ;
    lrhpi:averagePricesSASM ?avgPriceAll ;
    lrhpi:monthlyChange ?monthly ;
    lrhpi:averagePricesDetachedSASM ?avgDetached ;
    lrhpi:averagePricesSemiDetachedSASM ?avgSemi ;
    lrhpi:averagePricesFlatMaisonetteSASM ?avgFlats ;
    lrhpi:averagePricesTerracedSASM ?avgTerraced .
    
    FILTER(LANG(?regionName ) = "" || LANGMATCHES(LANG(?regionName), "en"))
}
'''

df=dfResults(endpoint_landreg,prefix_lr,q)
df[:5]

df['dt']=pd.to_datetime(df.yearmonth,format='%Y-%m')
df.set_index('dt',inplace=True)

#Set the date to be the end of the month
#via http://stackoverflow.com/a/18233876/454773
df=df.to_period('M').to_timestamp('M')
df[:3]

df.reset_index(inplace=True)
df[:3]

ggplot(df,aes(x='dt',y='avgDetached'))+geom_line()

df.columns.values

df_melt=pd.melt(df,value_vars=['avgDetached', 'avgFlats', 'avgPriceAll', 'avgSemi',
       'avgTerraced'],id_vars='dt')
df_melt[:3]

ggplot(df_melt,aes(x='dt',y='value',colour='variable'))+geom_line()

#http://mpld3.github.io/
#!pip3 install mpld3
import mpld3
#Running the following command means charts generated in this notebook henceforth will be rendered using D3.js 
mpld3.enable_notebook()


ggplot(df_melt,aes(x='dt',y='value',colour='variable'))+geom_line()