<jupyter_start><jupyter_text>BackgroundIn this project, I will be looking at the trends of the housing market in the UK. The dataset used shall be the most recent iteration of the UK House Price Index data. This dataset is released monthly as a CSV file, available from the gov.uk website $^1$.Knowing that this will be a large dataset (~55MB file), it makes sense to import this csv into Python as a pandas dataframe. This object type is well suited to manipulating large datasets and the associated pandas functions are built for data science tasks. Please note: for ease of reference, the csv is saved in the same directory as this notebook.<jupyter_code># Alias pandas as pd
import pandas as pd

# Import the dataset under a new object, hpi
hpi = pd.read_csv("UK-HPI-full-file-2020-11.csv")<jupyter_output><empty_output><jupyter_text>Exploring the dataFirstly, we need to take a look at the shape of the dataframe and the first few rows of data to get a better idea of the dataset.<jupyter_code>print(hpi.shape)
print(hpi.head())<jupyter_output>(131222, 54)
         Date     RegionName   AreaCode  AveragePrice      Index  IndexSA  \
0  01/01/2004  Aberdeenshire  S12000034   81693.66964  40.864214      NaN   
1  01/02/2004  Aberdeenshire  S12000034   81678.76231  40.856757      NaN   
2  01/03/2004  Aberdeenshire  S12000034   83525.09702  41.780317      NaN   
3  01/04/2004  Aberdeenshire  S12000034   84333.67900  42.184780      NaN   
4  01/05/2004  Aberdeenshire  S12000034   86379.95396  43.208353      NaN   

   1m%Change  12m%Change  AveragePriceSA  SalesVolume  ...     NewPrice  \
0        NaN         NaN             NaN        388.0  ...  88436.13953   
1  -0.018248         NaN             NaN        326.0  ...  88606.44649   
2   2.260483         NaN             NaN        453.0  ...  90296.91375   
3   0.968071         NaN             NaN        571.0  ...  90319.87844   
4   2.426403         NaN             NaN        502.0  ...  91989.17763   

    NewIndex  New1m%Change  New12m%Change  NewSalesVolume     OldPrice  \[...]<jupyter_text>We can see that this is a large dataframe with 131222 rows, 54 columns (some of which are not displayed) and there are several missing values.We need more information to get a better idea of what is and isn't in our dataframe.<jupyter_code>print(hpi.info())<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 131222 entries, 0 to 131221
Data columns (total 54 columns):
 #   Column                  Non-Null Count   Dtype  
---  ------                  --------------   -----  
 0   Date                    131222 non-null  object 
 1   RegionName              131222 non-null  object 
 2   AreaCode                131222 non-null  object 
 3   AveragePrice            131222 non-null  float64
 4   Index                   131222 non-null  float64
 5   IndexSA                 4329 non-null    float64
 6   1m%Change               130771 non-null  float64
 7   12m%Change              126086 non-null  float64
 8   AveragePriceSA          4329 non-null    float64
 9   SalesVolume             126711 non-null  float64
 10  DetachedPrice           125153 non-null  float64
 11  DetachedIndex           125153 non-null  float64
 12  Detached1m%Change       124736 non-null  float64
 13  Detached12m%Change      120173 non-null  float64
 14  SemiDetachedPrice   [...]<jupyter_text>We now have an idea of all the column names and those with missing values - we now know that all but the first 5 columns have missing data. Also, all but the first 3 columns contain numeric data. A crucial step is to make sure the date column is formatted as a date (adapted from code at stackoverflow $^2$).<jupyter_code>hpi['Date'] = pd.to_datetime(hpi['Date'], format="%d/%m/%Y")
#Check that the Date column has now changed type to datetime
hpi[['Date']].info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 131222 entries, 0 to 131221
Data columns (total 1 columns):
 #   Column  Non-Null Count   Dtype         
---  ------  --------------   -----         
 0   Date    131222 non-null  datetime64[ns]
dtypes: datetime64[ns](1)
memory usage: 1.0 MB<jupyter_text>We can see that the Datatype for the Date column is now set as a datetime.  Column definitionsFrom the full dataframe info, we see that some of the column names are slightly ambiguous. The gov.uk website provides definitions for each of these column headers on the 'about' section of the website $^3$. It may help the understanding of the data to be able to quickly view the definitions of the column headings from within Python. Also, for any future work on this dataset, it would be useful to have these column definitions quickly available. A dictionary may allow a quick way for any analyst to quickly access the defintion of a column name.<jupyter_code># Create dictionary of column headers and their defintions
colDict = {
    "Date": "The year and month to which the monthly statistics apply",
    "RegionName": "Name of geography (Country, Regional, County/Unitary/District Authority and London Borough)",
    "AreaCode": "Code of geography (Country, Regional, County/Unitary/District Authority and London Borough)",
    "Average Price": "Average house price for a geography in a particular period",
    "Index": "House price index for a geography in a particular period (January 2015=100).",
    "IndexSA": "Seasonally adjusted house price for a geography in a particular period (January 2015=100).",
    "1m%Change": "The percentage change in the Average Price compared to the previous month",
    "12m%Change": "The percentage change in the Average Price compared to the same period twelve months earlier.",
    "AveragePricesSA": "Seasonally adjusted Average Price for a geography in a particular period",
    "SalesVolume": "Number of registered transactions for a geography in a particular period",
    "[Property Type]Price": "Average house price for a particular property type (such as detached houses), for a geography in a particular period.",
    "[Property Type]Index": "House price index for a particular property type (such as detached houses), for a geography in a particular period (January 2015=100).",
    "[Property Type]1m%Change": "The percentage change in the [Property Type] Price (such as detached houses) compared to the previous month",
    "[Property Type]12m%Change": "The percentage change in the [Property Type] Price (such as detached houses) compared to the same period twelve months earlier.",
    "[Cash/Mortgage]Price": "Average house price by funding status (such as cash), for a geography in a particular period.",
    "[Cash/Mortgage]Index": "House price index by funding status (such as cash), for a geography in a particular period (January 2015=100).",
    "[Cash/Mortgage]1m%Change": "The percentage change in the [Cash/Mortgage]Price compared to the previous month",
    "[Cash/Mortgage]12m%Change": "The percentage change in the [Cash/Mortgage]Price compared to the same period twelve months earlier.",
    "[Cash/Mortgage]SalesVolume": "Number of registered transactions [Cash/Mortgage] for a geography in a particular period",
    "[FTB/FOO]Price": "Average house price by buyer status (such as first time buyer/former owner occupier), for a geography in a particular period.",
    "[FTB/FOO]Index": "House price index by buyer status (such as first time buyer/former owner occupier), for a geography in a particular period. (January 2015=100).",
    "[FTB/FOO]1m%Change": "The percentage change in the [FTB/FOO]Price compared to the previous month",
    "[FTB/FOO]12m%Change": "The percentage change in the [FTB/FOO]Price compared to the same period twelve months earlier.",
    "[New/Old]Price": "Average house price by property status (such as new or existing property), for a geography in a particular period.",
    "[New/Old]Index": "House price index by property status (such as new or existing property), for a geography in a particular period. (January 2015=100).",
    "[New/Old]1m%Change": "The percentage change in the [New/Old]Price compared to the previous month",
    "[New/Old]12m%Change": "The percentage change in the [New/Old]Price compared to the same period twelve months earlier.",
    "[New/Old]SalesVolume": "Number of registered transactions [New/Old] for a geography in a particular period"
}<jupyter_output><empty_output><jupyter_text>The obvious issue with this dictionary is that not all of the keys are written in the same form as in the column headings - for simplicity, gov.uk have combined certain definitions. For example, to get the definition of the column 'FTB Price', you would have to look up '\[FTB/FOO\]Price' in the dictionary. These 2 categories of heading forms will generally be referred to as the 'individual' form (eg. 'FTB Price') and the 'combined' form (eg. '\[FTB/FOO\]Price') of the column names respectively.A function is written to allow more intuitive querying of a definition. This is designed so that any subset of this data can also use the same definition function, provided the column names are not changed.<jupyter_code>def defHead(df,*head):
    """Return the definition from dataframe df of the column header(s)"""
    
    # Initialise an empty string
    input = ""
    
    # The first argument is the dataframe, df, and any further arguments are a column header name or index
    # Iterate through the column headers
    for col in head:
        
        # Save the query in the 'input' string
        input = col
        
        # If a column index has been specified, find the column name (adapted from code at w3schools [4])
        if (isinstance(col, int)):
            # Check the column index is not out of range of the dataframe (a negative index is allowed)
            if (abs(col) < len(df.columns)):
                # Get the column name
                colName = df.columns[col]
                # Append the column name to the query string, so that both the column index and name are shown
                input = str(input) + " - " + colName
                # Overwrite the integer in the col variable with the column name
                col = colName
            else:
                # The column index requested is not in the dataframe, put a message in the query string
                input = str(input) + " - Dataframe index out of range"
                # Set the col variable to an arbitrary string
                col = "Error"
        
        # Define a list of the 'individual' references - notice 'Detached' is AFTER 'SemiDetached' as the former string is contained within the latter
        indList = ["SemiDetached", "Detached", "Terraced", "Flat", "Cash", "Mortgage", "FTB", "FOO", "New", "Old"]

        def rmIndiv(col):
            """Function to remove the 'individual' reference where there is a 'combined' reference available"""
            # Check for the presence of any of the 'individual' references and remove them
            for indiv in indList:
                col = col.replace((indiv),"")
            return col
        
        # Remove the 'individual' reference and insert the appropriate 'combined' form (adapted from code at w3schools [5])
        if col.startswith(("Detached", "SemiDetached", "Terraced", "Flat")):
            col = rmIndiv(col)
            col = "[Property Type]" + col
        elif col.startswith(("Cash", "Mortgage")):
            col = rmIndiv(col)
            col = "[Cash/Mortgage]" + col
        elif col.startswith(("FTB", "FOO")):
            col = rmIndiv(col)
            col = "[FTB/FOO]" + col
        elif col.startswith(("New", "Old")):
            col = rmIndiv(col)
            col = "[New/Old]" + col
        
        # Print the query string
        print(input, ": ")
        # Print the definition of the column name if available
        try:
            print(colDict[col])
        except:
            print("-- Definition of column name not found --")
        # Print new line for better formatting
        print("\n")<jupyter_output><empty_output><jupyter_text>The function defHead now works to print a definition as desired. The following code shows an example of outputs, including instances with out-of-range errors (+ and -) and where the column name is not in the dictionary.<jupyter_code>defHead(hpi,53,92,-21,-60,'New12m%Change','RandomString')<jupyter_output>53 - OldSalesVolume : 
Number of registered transactions [New/Old] for a geography in a particular period


92 - Dataframe index out of range : 
-- Definition of column name not found --


-21 - Mortgage1m%Change : 
The percentage change in the [Cash/Mortgage]Price compared to the previous month


-60 - Dataframe index out of range : 
-- Definition of column name not found --


New12m%Change : 
The percentage change in the [New/Old]Price compared to the same period twelve months earlier.


RandomString : 
-- Definition of column name not found --<jupyter_text>Narrowing the DataThis report is looking to examine the general trends in the housing market. With this in mind, we can select an appropriate subset of the dataframe.Looking back at the info output, we can see in which columns to find the relevant information. A copy of the original dataframe is made to allow for further manipulation rather than taking a slice. Whilst this increases the memory usage, this is only a small fraction of the original dataset so should not cause any memory usage issues.<jupyter_code># Select all rows and the required columns
subdf = hpi.iloc[:,[0,1,2,3,6,7,9]].copy()
subdf.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 131222 entries, 0 to 131221
Data columns (total 7 columns):
 #   Column        Non-Null Count   Dtype         
---  ------        --------------   -----         
 0   Date          131222 non-null  datetime64[ns]
 1   RegionName    131222 non-null  object        
 2   AreaCode      131222 non-null  object        
 3   AveragePrice  131222 non-null  float64       
 4   1m%Change     130771 non-null  float64       
 5   12m%Change    126086 non-null  float64       
 6   SalesVolume   126711 non-null  float64       
dtypes: datetime64[ns](1), float64(4), object(2)
memory usage: 7.0+ MB<jupyter_text>The defHead function is used to find the definition of the ambiguous column names in the new dataset.<jupyter_code>defHead(subdf,4,5)<jupyter_output>4 - 1m%Change : 
The percentage change in the Average Price compared to the previous month


5 - 12m%Change : 
The percentage change in the Average Price compared to the same period twelve months earlier.<jupyter_text>Total SpendLooking at the whole market, it may be beneficial to know the total spend in each region. A total regional spend is clearly relevant to e.g. mortgage lenders, as properties are generally purchased using a mortgage - a greater spend in a region may provide an indication of greater debt apetite and therefore potential for new customers.The total spend is calculated by multiplying Average Price by Sales Volume, and the result is saved as a new column. As there is a new column, a new entry is added to the dictionary also.<jupyter_code>subdf['TotalSpend'] = subdf['AveragePrice']*subdf['SalesVolume']
colDict['TotalSpend'] = "The total expenditure on property in a region for a particular period. Calculated as AveragePrice x SalesVolume"

# SalesVolume has missing data, so we remove those rows and select the most recent month (adapted from code at geeksforgeeks [6])
subRec = subdf[pd.notnull(subdf['SalesVolume'])]
subRec = subRec[subRec['Date'] == max(subRec['Date'])]
print(subRec.shape)
subRec.RegionName.drop_duplicates()
print(subRec.shape)<jupyter_output>(427, 8)
(427, 8)<jupyter_text>We can see from slicing this data by a single date that there are 427 regions reported per month with no duplicates (the shape doesn't change after dropping duplicates).This data is then sorted by Total Spend (constructed using pandas documentation $^7$).<jupyter_code>subRec.sort_values(by=['TotalSpend'], ascending = False).head(10)<jupyter_output><empty_output><jupyter_text>We can see the regions where most money is being spent, and therefore the possible target regions for new customer acquisition.Through sorting by the biggest total spend, we have also identified that the regions specified in this data are not only counties/locales, but also include nations and the whole country. It would make sense to examine and compare the trends in property prices in these broader regions over time.  10-year TrendsThe focus here will be on Scotland, England, and Wales, so Great Britain is selected as the 'whole country' region. Additionally, London is an anomaly as despite it being a city, (in September 2020) it had a larger spend than most larger regions and both Scotland and Wales. It may therefore be informative to see how the London region is changing over time also.A suitable timeframe for observing these trends is 10 years - this will be generated automatically as the most recent 10 years of the dataset.<jupyter_code># Put the regions of interest into a list
regions = ['Great Britain', 'England', 'Wales', 'Scotland', 'London']

# Determine the start date to give a 10 year timespan (adapted from code at stackoverflow [8])
import datetime
endDate = max(subdf['Date'])
startDate = pd.to_datetime(datetime.date((endDate.year - 10), endDate.month, endDate.day), format="%Y-%m-%d")

# Filter for required regions and date
subReg = subdf[(subdf['RegionName'].isin(regions)) & (subdf['Date'] >= startDate)]

# Alias pyplot to use for plotting graphs
import matplotlib.pyplot as plt

# Determine the graph's variables
date = subReg['Date']
price = subReg['AveragePrice']

# To colour code by region, first create a dictionary
regDic = {
    'Great Britain': 'Black',
    'Scotland': 'Blue',
    'Wales': 'Green',
    'England': 'Red',
    'London': 'Purple'
}
# Create region list
region = []
# Assign colours to regions
for reg in subReg['RegionName']:
    region.append(regDic[reg])

# Define graph attributes (built using matplotlib documentation [9])
plt.figure(figsize=(7,6))
plt.scatter(date, price, c=region,s=5)
plt.xlabel("Date")
plt.ylabel("Price (£)")
plt.title("Average House Price By Region", fontsize=14)
plt.grid(color='grey', linewidth=0.4, alpha=0.8)

# Import mpatches to generate a custom legend (code adapted from matplotlib tutorial [10])
import matplotlib.patches as mpatches
# Generate list of legend objects
regLeg = []
for reg in regDic:
    regLeg.append(mpatches.Patch(color=regDic[reg], label=reg))

plt.legend(handles=regLeg)
plt.show()<jupyter_output><empty_output><jupyter_text>Note that this is kept as a scatterplot rather than a line graph so that trend deviations are more evident. From this graph, we can see that London prices increased dramatically between 2013 and 2017 relative to Great Britain, before following the general trend, with another sharper increase towards the end of 2020. England prices have risen slightly above the country average, with Scotland and Wales prices not seeing the same absolute increase in price. This indicates that overall, existing loans provided to London and England should have reduced their loan-to-value ratio (and therefore risk) to a greater extent than in Wales and Scotland. As these are national averages, this could be examined against a mortgage lender's own loan book to confirm the validity of this trend or see how it compares.  Short-term Trends  Most recent year, grouped by nationIt is important to note that the 10-year trend graph above shows absolute price changes and not percentage changes. It is useful to know how prices are changing around the country on a percentage basis in the shorter term to have an idea of price trajectory.The subdf dataset is filtered to subshort by finding the most recent year, then further sliced by nation.<jupyter_code># endDate already contains the most recent date, find 1 year prior
shortStart = pd.to_datetime(datetime.date((endDate.year - 1), endDate.month, endDate.day), format="%Y-%m-%d")
subshort = subdf[subdf['Date'] >= shortStart].copy()

# Select each region required based on Area Code (built using pandas documentation [11])
shortS = subshort[subshort['AreaCode'].str.contains('S')]
shortW = subshort[subshort['AreaCode'].str.contains('W')]
shortE = subshort[subshort['AreaCode'].str.contains('E')]
shortL = subshort[subshort['RegionName'] == 'London']
shortG = subshort[subshort['RegionName'] == 'Great Britain']

print(
    shortE[['1m%Change', '12m%Change']].describe()
)<jupyter_output>1m%Change   12m%Change
count  4628.000000  4628.000000
mean      0.438487     2.066624
std       1.517710     3.064978
min     -11.902823   -14.063315
25%      -0.452851     0.097663
50%       0.336320     1.868972
75%       1.291585     3.855523
max      12.387546    28.646251<jupyter_text>Looking at the description for percentage changes for England as an example, we can see that the range is far greater in the 12month change than in the 1month change. As we are looking at shorter term trends, the monthly changes are likely more informative. However, comparing the regions in this way is not intuitive, so boxplots are constructed.  Boxplots<jupyter_code>fig, ax = plt.subplots(figsize=(7, 9))

# Initiate a counting integer for the total number of boxplots
pos = 0
def boxShort(df,region):
    """Plot a(n additional) 1-month %age change boxplot of the selected region"""
    global pos
    # Increment the number of boxplots
    pos = pos + 1
    # Build the boxplot attributes (code adapted from stackoverflow and using matplotlib documentation [12 - 15])
    return ax.boxplot(
        # Select the data
        df['1m%Change'],
        # Show the mean of the data    
        showmeans=True,
        # Add the boxplot to the end of the graph
        positions = [pos],
        # Choose the colour and thickness (based on previous graph's colours)
        boxprops=dict(color = regDic[region], linewidth=3.0),
        whiskerprops=dict(color = regDic[region], linewidth=2.0),
        # Label the plot
        labels = [region],
        # Choose size of boxplot
        widths = 0.5
    )

# Choose boxes to plot
box1 = boxShort(shortG,'Great Britain')
box2 = boxShort(shortS,'Scotland')
box3 = boxShort(shortW,'Wales')
box4 = boxShort(shortE,'England')
box5 = boxShort(shortL,'London')

# Label y-axis, insert title, and show gridlines
ax.set_ylabel('Log of Monthly Percentage Change', fontsize = 12)
ax.set_title('Monthly Change in %age House Price\n(All Regions grouped by Nation, 1 year)', fontsize = 14)
plt.grid(color='grey', linewidth=0.3, alpha=0.6)

# Change scale of graph from linear to logarithmic (symmetrical log allows -ve values)
plt.yscale('symlog')

plt.show()<jupyter_output><empty_output><jupyter_text>From the boxplots, we see that there is clear negative skew in the London data (mean = green triangle), suggesting there has generally been a weaker positive or a negative trend in housing prices over the short term. This could negatively impact the LTVs of London property loans.In Scotland, Wales and England overall, whilst skew is not obvious, there is clearly a greater spread of the percentage change in Scotland than Wales, and in Wales than England. This is whilst the means and medians of these nations are similar and low, which indicates that England prices in general are currently less volatile than Wales or Scotland, suggesting lower risk of valuation changes. However, Wales shows fewer outliers, suggesting that Wales property values are changing more predictably and homogenously, and therefore potentially require less complex modelling to predict future valuation changes.  Probability density plotTo further compare the short-term changes in all regions grouped by nation, a probability density plot is drawn.<jupyter_code># Select data to include
x = [shortS['1m%Change'],shortW['1m%Change'],shortE['1m%Change']]
# Turn off 'deprecated' warning for different data lengths (code from stackoverflow [16])
import numpy as np
np.warnings.filterwarnings('ignore', category=np.VisibleDeprecationWarning)
# Choose size of graph
plt.figure(figsize=(9,4))
# Construct histogram (written using matplotlib documentation [17])
plt.hist(
    x,
    bins=10,
    density=True,
    color=[regDic['Scotland'],regDic['Wales'],regDic['England']],
    label=['Scotland','Wales','England']
)
# Set graph labelling attributes
plt.xlabel('Percentage Change', fontsize=12)
plt.ylabel('Probability Density', fontsize=12)
plt.title('Probability Density Plot of Monthly %age House Price Change\n(All regions grouped by nation, 1 year)', fontsize=14)
plt.legend()
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08281909710945112, 'fraction_numerical': 0.03698441052289705, 'mean_word_length': 3.5431575064551826, 'pattern_counts': {'":': 29, '<': 57, '<?xml version=': 0, '>': 58, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '558047', 'license': 'MIT', 'n_tokens_mistral': 7436, 'n_tokens_neox': 6671, 'n_words': 2994, 'path': 'ProgAndDataAnalysis.ipynb', 'repo_name': 'faisaljina/prog-analysis-hpi', 'size': '24632'}