library(csodata)
library(tidyverse)
library(knitr)
library(kableExtra)
library(ggplot2)
library(lubridate)
library(ggrepel)
library(zoo)
library(sf)
library(tmap)
library(rgdal)
library(readxl)
library(maps)
library(mapproj)
library(mapview)
library(patchwork)
library(Hmisc)
library(formattable)

#Load
## HPM09
HPM09 <- cso_get_data("HPM09")
HPM09_long <- HPM09 %>%
  pivot_longer(!1:2, names_to = "year_month")
rm(HPM09)
HPM09 <- HPM09_long
rm(HPM09_long)

HPM09$Month <- as.Date(paste(HPM09$year_month, "01", sep = "-"), "%YM%m-%d")
HPM09$Year <- year(HPM09$Month)

### Given the aggregation of the 'Type of Residential Property Field, only 'National - all residential properties' will be kept across two Statistics: 'Residential Property Price Index' & 'Percentage Change over 12 months for Residential Property Price Index' 

### More granular detail loaded through 'Market-based Household Purchases'

## HPM04
HPM04 <- cso_get_data("HPM04")
HPM04_long <- HPM04 %>%
  pivot_longer(!1:5, names_to = "year_month")
rm(HPM04)
HPM04 <- HPM04_long
rm(HPM04_long)
### Date transformation
HPM04$Year <-substr(HPM04$year_month,1,4)
HPM04$Month <- sub(".* ", "", HPM04$year_month)
HPM04$Month_NR <- as.integer(factor(HPM04$Month, levels=month.name))
HPM04$Date <- as.yearmon(paste(HPM04$Year, HPM04$Month_NR), "%Y %m")

## GeoSpatial
setwd("/Users/charten/OneDrive - Glenveagh Properties/Research & Development/1. Analysis/")
#shp = readOGR(dsn=".", layer = "Constituency_Boundaries_Ungeneralised___OSi_National_Electoral_Boundaries___2017")
#shp = readOGR(dsn=".", layer = "counties")
shp <- sf::read_sf("Constituency_Boundaries_Ungeneralised___OSi_National_Electoral_Boundaries___2017.shp")
GEO_Merge <- readxl::read_xlsx(path="C:\\Users\\charten\\OneDrive - Glenveagh Properties\\Research & Development\\1. Analysis\\HPM04_GEO.xlsx",sheet = 1)
#st <- st_read("/Users/charten/OneDrive - Glenveagh Properties/Research & Development/1. Analysis/Constituency_Boundaries_Ungeneralised___OSi_National_Electoral_Boundaries___2017.shp", stringsAsFactors = FALSE)
#dfCon <- fortify(shp)

HPM04_join <- full_join(HPM04, GEO_Merge, by = "Eircode.Output")
HPM04 <- HPM04_join
rm(HPM04_join)

Residential Property Price Index

HPM09$month <- months(as.Date(HPM09$Month))

HPM09_1 <- HPM09 %>%
  filter(Statistic == "Residential Property Price Index")%>%
  filter(Type.of.Residential.Property == "National - all residential properties")

HPM09_2 <- HPM09 %>%
  filter(Statistic == "Percentage Change over 12 months for Residential Property Price Index")%>%
  filter(Type.of.Residential.Property == "National - all residential properties")

RPPI_tail_2 <- tail(HPM09_2,1)
RPPI_tail_2_lag <-head(tail(HPM09_2,2),1)

Headline

The national house price inflation for May is 14.4%. This represents a -0.1 change from April which was 14.5%.

Previous 6 month inflation print
HPM09_2_trim <- subset(HPM09_2, select = -c(Statistic, year_month, Month))
RPPI_tail_2 <- kable(tail(HPM09_2_trim), caption = "Percentage Change over 12 months for Residential Property Price Index")
RPPI_tail_2 %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed"), full_width = F) %>%
  row_spec(6, bold = T)%>%
  pack_rows("Latest Period", 6, 6, color="#1b5545")
Percentage Change over 12 months for Residential Property Price Index
Type.of.Residential.Property value Year month
National - all residential properties 14.2 2021 December
National - all residential properties 14.8 2022 January
National - all residential properties 15.1 2022 February
National - all residential properties 15.1 2022 March
National - all residential properties 14.5 2022 April
Latest Period
National - all residential properties 14.4 2022 May
rm(HPM09_2_trim)

Overview

RRPI_Line_1 <- ggplot(data=HPM09_1, aes(x=Month, y=value, group = Type.of.Residential.Property))+
  geom_line(linejoin="mitre",size = 1.25, linetype = 1,alpha = 0.5, colour="#1b5545")+
  labs(title = "Residential Property Price Index" ,
       y="2015 = 100",
       x="Month")+
  geom_text_repel(aes(label=value),data = HPM09_1, size = 3)+
  theme(legend.position = "bottom")

RRPI_Line_1

HPM09_1_T12 <- tail(HPM09_1,12)
RRPI_Line_2 <- ggplot(data=HPM09_1_T12, aes(x=Month, y=value, group = Type.of.Residential.Property))+
  geom_line(linejoin="mitre",size = 1.25, linetype = 1,alpha = 0.5, colour="#1b5545")+
  labs(title = "Residential Property Price Index" ,
       subtitle = "12 Month Series",
       y="2015 = 100",
       x="Month")+
  geom_text(aes(label=value),vjust= 1.5, hjust = 0, size=3)+
  theme(legend.position = "bottom")

RRPI_Line_2

HPM09_2_T24 <- tail(HPM09_2,24)
colour <- ifelse(HPM09_2_T24$value < 0,"#CC0000","#1b5545")
RPPI_Bar_2 <- ggplot(data=HPM09_2_T24, aes(x=Month, y=value, group = Type.of.Residential.Property))+
  geom_col(alpha = 0.5, colour="#373634", fill = colour)+
  labs(title = "Year on Year Percentage Change for Residential Property Price Index" ,
       subtitle = "24 Month Series",
       y="Percentage change",
       x="Month")+
  geom_text(aes(label=value),vjust= 1.5, size=3)+
  theme(legend.position = "bottom")

rm(HPM09)


House Price Index

Subset

    1. Mean Sale Price, All Dwelling Statuses, Executions, All Buyer Types, All
    1. Mean Sale Price, All Dwelling Statuses, Executions, All Buyer Types, Each Eircode Output
HPM04_1A <- HPM04 %>%
  filter(Statistic == "Mean Sale Price")%>%
  filter(Dwelling.Status == "All Dwelling Statuses") %>%
  filter(Stamp.Duty.Event == "Executions") %>%
  filter(Type.of.Buyer == "All Buyer Types") %>%
  filter(Eircode.Output == "All")

HPM04_1B <- HPM04 %>%
  filter(Statistic == "Mean Sale Price")%>%
  filter(Dwelling.Status == "All Dwelling Statuses") %>%
  filter(Stamp.Duty.Event == "Executions") %>%
  filter(Type.of.Buyer == "All Buyer Types") %>%
  filter(Eircode.Output != "All")

HPM04 - Market-based Household Purchases of Residential Dwellings

HPM04_1A_trim <- subset(HPM04_1A, select = -c(Statistic, Month ,year_month, Month_NR, Year, Eircode.Output.Adjusted, EirCode, Return.County, Return.NUTS3, Return.Lat, Return.Lat, Return.Long, Nearest.Match.to.ED, Constituency, GUID, GUID_TAG, Seat_Tag,ID,Longitude,Latitude,Apply))
HPM04_1A_tail <- subset(HPM04_1A, select = -c(year_month, Month, Year, Month_NR))
tail_1A <- kable(tail(HPM04_1A_trim), caption = "Market-based Household Purchases of Residential Dwellings - All")
tail_1A %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed"), full_width = F) %>%
  row_spec(6, bold = T)%>%
  pack_rows("Latest Period", 6, 6, color = "navy")
Market-based Household Purchases of Residential Dwellings - All
Dwelling.Status Eircode.Output Stamp.Duty.Event Type.of.Buyer value Date
All Dwelling Statuses All Executions All Buyer Types 340777 Dec 2021
All Dwelling Statuses All Executions All Buyer Types 345213 Jan 2022
All Dwelling Statuses All Executions All Buyer Types 339751 Feb 2022
All Dwelling Statuses All Executions All Buyer Types 339313 Mar 2022
All Dwelling Statuses All Executions All Buyer Types 337318 Apr 2022
Latest Period
All Dwelling Statuses All Executions All Buyer Types 339798 May 2022
rm(HPM04_1A_trim)
rm(HPM04_1A_tail)
rm(HPM09_1)
rm(HPM09_1_T12)
rm(HPM09_2)
rm(HPM09_2_T24)

Map

Average Value by Constituency

HPM04_1B_Map <- na.omit(HPM04_1B)
HPM04_1B_Map<- HPM04_1B_Map %>%
  group_by(Seat_Tag, Date)%>% # change to/from County / Constituency
  summarise(Average_Value = mean(value))

HPM04_1B_Map$lagvalue <- Lag(HPM04_1B_Map$Average_Value,1)
HPM04_1B_Map$Diff <- HPM04_1B_Map$Average_Value - HPM04_1B_Map$lagvalue
HPM04_1B_Map$Diffpc <- HPM04_1B_Map$Diff / HPM04_1B_Map$lagvalue

HPM04_1B_Map$Diffpc<-percent(HPM04_1B_Map$Diffpc,2)

HPM04_1B_Map_May2022 <- HPM04_1B_Map%>%
  filter(Date == "May 2022")

shpHPM <- merge(shp,HPM04_1B_Map_May2022, by.x = "CON_SEAT_", by.y="Seat_Tag")
HPM04_1B_Map_May2022_Dub <- HPM04_1B_Map_May2022 %>% 
  filter(grepl('Dublin|Laoghaire', Seat_Tag))

shpHPMDub <- merge(shp,HPM04_1B_Map_May2022_Dub, by.x = "CON_SEAT_", by.y="Seat_Tag")

Map_AvgValueAll <-  shpHPM %>%
  ggplot()+
  geom_sf(aes(fill = Average_Value))+
  scale_fill_gradientn(
    colors=c("#FAD3DA","#80D7F5","#0C4F00"),
    labels = scales::comma,
    limits = c(150000,850000),
    oob = scales::squish,
    breaks=c(100000,200000,300000,400000,500000,600000,700000,800000,900000),
    name = "Average Value")+
    theme_void()+
  ggtitle('Average Value by Constituency - HPM04')+
  theme(legend.title = element_text(size = 10),
        legend.text = element_text(size = 8))+
    theme(legend.position = "left")+
  theme(plot.title = element_text(hjust = 1))

Map_AvgValueDub <- shpHPMDub %>%
  ggplot()+
  geom_sf(aes(fill = Average_Value))+
  scale_fill_gradientn(
    colors=c("#FAD3DA","#80D7F5","#0C4F00"),
    labels = scales::comma,
    limits = c(150000,850000),
    oob = scales::squish,
    breaks=c(100000,200000,300000,400000,500000,600000,700000,800000,900000),
    name = "Average Value")+
    theme_void()+
  theme(legend.position = "none")


Map_AvgValue <- Map_AvgValueAll + Map_AvgValueDub +
  plot_layout(widths = c(2,1))

Map_AvgValue

The average value by constituency is 336817.3, with a standard deviation of 180080.6. This was 537903.8 and 216238.7 respectively in the Dublin constituencies.

Percentage Change by Constituency

Map_DiffpcAll <-  shpHPM %>%
  ggplot()+
  geom_sf(aes(fill = Diffpc))+
 scale_fill_stepsn(n.breaks = 10,
    colors=c("#F92306","#FFFFFF","#0C4F00"),
    limits = c(-1,1),
    name = "% change")+
    ggtitle('Month on Month Change by Constituency - HPM04')+
  theme_void()+
  theme(legend.title = element_text(size = 10),
        legend.text = element_text(size = 8))+
    theme(legend.position = "left")+
  theme(plot.title = element_text(hjust = 1))

Map_DiffpcDub <- shpHPMDub %>%
  ggplot()+
  geom_sf(aes(fill = Diffpc))+
 scale_fill_stepsn(n.breaks = 10,
    colors=c("#F92306","#FFFFFF","#0C4F00"),
    limits = c(-1,1),
    name = "% change")+
    theme_void()+
  theme(legend.position = "none")


Map_Diffpc <- Map_DiffpcAll + Map_DiffpcDub +
  plot_layout(widths = c(2,1))

Map_Diffpc

Average percentage change month on month is 2.07%. This was 8.73% in the Dublin constituencies.

HPM04_1B_Map_May2022%>%
  kbl(caption = "Month on Month ")%>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed"), full_width = T)%>%
  scroll_box(width = "600px", height = "300px") 
Month on Month
Seat_Tag Date Average_Value lagvalue Diff Diffpc
Carlow-Kilkenny (5) May 2022 216527.7 259982.3 -43454.667 -16.71%
Cavan-Monaghan (5) May 2022 177097.0 188724.9 -11627.857 -6.16%
Clare (4) May 2022 224667.0 196664.7 28002.333 14.24%
Cork East (4) May 2022 254092.0 269807.3 -15715.286 -5.82%
Cork North-Central (4) May 2022 362033.0 336483.0 25550.000 7.59%
Cork North-West (3) May 2022 295421.8 284945.8 10475.950 3.68%
Cork South-Central (4) May 2022 387499.0 377158.0 10341.000 2.74%
Cork South-West (3) May 2022 317929.7 328277.8 -10348.167 -3.15%
Donegal (5) May 2022 166165.3 162026.0 4139.333 2.55%
Dublin Bay North (5) May 2022 477757.7 484701.3 -6943.667 -1.43%
Dublin Bay South (4) May 2022 1061525.3 614099.0 447426.333 72.86%
Dublin Central (4) May 2022 380833.0 299362.0 81471.000 27.21%
Dublin Fingal (5) May 2022 400859.1 435193.5 -34334.375 -7.89%
Dublin Mid-West (4) May 2022 374310.0 372356.0 1954.000 0.52%
Dublin North-West (3) May 2022 465500.0 490469.0 -24969.000 -5.09%
Dublin Rathdown (3) May 2022 610032.0 614520.0 -4488.000 -0.73%
Dublin South-West (5) May 2022 558630.0 538180.3 20449.667 3.80%
Dublin South Central (4) May 2022 390266.2 392571.2 -2305.000 -0.59%
Dublin West (4) May 2022 399194.5 395483.0 3711.500 0.94%
Dun Laoghaire (4) May 2022 798034.5 749484.5 48550.000 6.48%
Galway East (3) May 2022 245063.7 240822.3 4241.333 1.76%
Galway West (5) May 2022 256908.0 336685.5 -79777.500 -23.69%
Kerry (5) May 2022 243051.5 246171.5 -3120.000 -1.27%
Kildare North (4) May 2022 387790.5 391812.0 -4021.500 -1.03%
Kildare South (4) May 2022 249614.0 292926.8 -43312.800 -14.79%
Laois-Offaly (5) May 2022 209237.0 225253.2 -16016.250 -7.11%
Limerick City (4) May 2022 273538.0 245495.0 28043.000 11.42%
Limerick County (3) May 2022 143393.0 152733.5 -9340.500 -6.12%
Longford-Westmeath (4) May 2022 225328.7 194342.7 30986.000 15.94%
Louth (5) May 2022 271900.0 277855.5 -5955.500 -2.14%
Mayo (4) May 2022 178087.7 173881.2 4206.500 2.42%
Meath East (3) May 2022 338315.4 338801.0 -485.600 -0.14%
Meath West (3) May 2022 271314.0 327649.5 -56335.500 -17.19%
Roscommon-Galway (3) May 2022 191973.7 146247.3 45726.333 31.27%
Sligo-Leitrim (4) May 2022 166604.0 189258.0 -22654.000 -11.97%
Tipperary (5) May 2022 214051.4 182901.2 31150.125 17.03%
Waterford (4) May 2022 285926.0 255514.7 30411.333 11.90%
Wexford (5) May 2022 230609.2 247395.2 -16786.000 -6.79%
Wicklow (5) May 2022 434795.8 504783.0 -69987.250 -13.86%
HPM04_1B_Map <- na.omit(HPM04_1B)
HPM04_1B_Map<- HPM04_1B_Map %>%
  group_by(Seat_Tag, Date)%>% # change to/from County / Constituency
  summarise(Average_Value = mean(value))

HPM04_1B_Map$lagvalue_12mth <- Lag(HPM04_1B_Map$Average_Value,12)
HPM04_1B_Map$Diff <- HPM04_1B_Map$Average_Value - HPM04_1B_Map$lagvalue_12mth
HPM04_1B_Map$Diffpc <- HPM04_1B_Map$Diff / HPM04_1B_Map$lagvalue_12mth
HPM04_1B_Map$Diffpc<-percent(HPM04_1B_Map$Diffpc,2)


HPM04_1B_Map_May2022 <- HPM04_1B_Map%>%
  filter(Date == "May 2022")

shpHPM <- merge(shp,HPM04_1B_Map_May2022, by.x = "CON_SEAT_", by.y="Seat_Tag")
HPM04_1B_Map_May2022_Dub <- HPM04_1B_Map_May2022 %>% 
  filter(grepl('Dublin|Laoghaire', Seat_Tag))

shpHPMDub <- merge(shp,HPM04_1B_Map_May2022_Dub, by.x = "CON_SEAT_", by.y="Seat_Tag")

Map2_DiffpcAll <-  shpHPM %>%
  ggplot()+
  geom_sf(aes(fill = Diffpc))+
 scale_fill_stepsn(n.breaks = 10,
    colors=c("#F92306","#FFFFFF","#0C4F00"),
    limits = c(-1,1),
    name = "% change")+
    ggtitle('Year on Year Change by Constituency - HPM04')+
  theme_void()+
  theme(legend.title = element_text(size = 10),
        legend.text = element_text(size = 8))+
    theme(legend.position = "left")+
  theme(plot.title = element_text(hjust = 1))

Map2_DiffpcDub <- shpHPMDub %>%
  ggplot()+
  geom_sf(aes(fill = Diffpc))+
 scale_fill_stepsn(n.breaks = 10,
    colors=c("#F92306","#FFFFFF","#0C4F00"),
    limits = c(-1,1),
    name = "% change")+
    theme_void()+
  theme(legend.position = "none")


Map2_Diffpc <- Map2_DiffpcAll + Map2_DiffpcDub +
  plot_layout(widths = c(2,1))

Map2_Diffpc

Average percentage change year on year is 13.79%. This was 13.57% in the Dublin constituencies.

HPM04_1B_Map_May2022%>%
  kbl(caption = "Year on Year ")%>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed"), full_width = T)%>%
  scroll_box(width = "600px", height = "300px") 
Year on Year
Seat_Tag Date Average_Value lagvalue_12mth Diff Diffpc
Carlow-Kilkenny (5) May 2022 216527.7 199519.3 17008.33 8.52%
Cavan-Monaghan (5) May 2022 177097.0 144608.4 32488.57 22.47%
Clare (4) May 2022 224667.0 172103.7 52563.33 30.54%
Cork East (4) May 2022 254092.0 222825.8 31266.17 14.03%
Cork North-Central (4) May 2022 362033.0 296311.0 65722.00 22.18%
Cork North-West (3) May 2022 295421.8 216368.6 79053.15 36.54%
Cork South-Central (4) May 2022 387499.0 355174.0 32325.00 9.10%
Cork South-West (3) May 2022 317929.7 218142.0 99787.67 45.74%
Donegal (5) May 2022 166165.3 137711.7 28453.67 20.66%
Dublin Bay North (5) May 2022 477757.7 462236.7 15521.00 3.36%
Dublin Bay South (4) May 2022 1061525.3 617102.3 444423.00 72.02%
Dublin Central (4) May 2022 380833.0 283600.0 97233.00 34.29%
Dublin Fingal (5) May 2022 400859.1 350144.0 50715.12 14.48%
Dublin Mid-West (4) May 2022 374310.0 328365.0 45945.00 13.99%
Dublin North-West (3) May 2022 465500.0 450125.0 15375.00 3.42%
Dublin Rathdown (3) May 2022 610032.0 584364.0 25668.00 4.39%
Dublin South-West (5) May 2022 558630.0 538289.7 20340.33 3.78%
Dublin South Central (4) May 2022 390266.2 406003.2 -15737.00 -3.88%
Dublin West (4) May 2022 399194.5 390229.0 8965.50 2.30%
Dun Laoghaire (4) May 2022 798034.5 789073.5 8961.00 1.14%
Galway East (3) May 2022 245063.7 232117.7 12946.00 5.58%
Galway West (5) May 2022 256908.0 330317.5 -73409.50 -22.22%
Kerry (5) May 2022 243051.5 193255.0 49796.50 25.77%
Kildare North (4) May 2022 387790.5 355096.5 32694.00 9.21%
Kildare South (4) May 2022 249614.0 258038.0 -8424.00 -3.26%
Laois-Offaly (5) May 2022 209237.0 187944.2 21292.75 11.33%
Limerick City (4) May 2022 273538.0 244928.0 28610.00 11.68%
Limerick County (3) May 2022 143393.0 158415.5 -15022.50 -9.48%
Longford-Westmeath (4) May 2022 225328.7 186934.0 38394.67 20.54%
Louth (5) May 2022 271900.0 250552.0 21348.00 8.52%
Mayo (4) May 2022 178087.7 163940.8 14146.83 8.63%
Meath East (3) May 2022 338315.4 346414.4 -8099.00 -2.34%
Meath West (3) May 2022 271314.0 288824.0 -17510.00 -6.06%
Roscommon-Galway (3) May 2022 191973.7 150357.3 41616.33 27.68%
Sligo-Leitrim (4) May 2022 166604.0 159675.0 6929.00 4.34%
Tipperary (5) May 2022 214051.4 152241.0 61810.38 40.60%
Waterford (4) May 2022 285926.0 212470.7 73455.33 34.57%
Wexford (5) May 2022 230609.2 208450.8 22158.50 10.63%
Wicklow (5) May 2022 434795.8 421322.0 13473.75 3.20%

Describe Categorical Variables

distinct(data.frame(HPM04$Statistic))
##   HPM04.Statistic
## 1 Volume of Sales
## 2  Value of Sales
## 3 Mean Sale Price
## 4    Median Price
distinct(data.frame(HPM04$Dwelling.Status))
##   HPM04.Dwelling.Status
## 1 All Dwelling Statuses
## 2                   New
## 3              Existing
distinct(data.frame(HPM04$Stamp.Duty.Event))
##   HPM04.Stamp.Duty.Event
## 1                Filings
## 2             Executions
distinct(data.frame(HPM04$Type.of.Buyer))
##                                 HPM04.Type.of.Buyer
## 1                                   All Buyer Types
## 2 Household Buyer - First-Time Buyer Owner-Occupier
## 3           Household Buyer - Former Owner-Occupier
## 4                    Household Buyer - Non-Occupier