In this data visualization analysis, data from Zillow are used to examine the averag single-family home sale price in each county in California.
I’m going to visualize the trend of average property price in California over the years to a multi-line chart and property price distribution through geographic analysis to a choropleth map.
#let's read the data from excel first
library(readxl)
CA_house_price <- read_excel("~/Desktop/R_Property/CA_PropertyPrice_byCounty/CA_house_price.xlsx")
head(CA_house_price) #before reshape
## # A tibble: 6 x 59
## Date `Los Angeles Co… `San Diego Coun… `Orange County`
## <dttm> <dbl> <dbl> <dbl>
## 1 2003-04-30 00:00:00 330673 395896 454004
## 2 2003-05-31 00:00:00 335215 401471 459880
## 3 2003-06-30 00:00:00 339000 405144 465204
## 4 2003-07-31 00:00:00 344318 410305 470774
## 5 2003-08-31 00:00:00 349916 414954 478019
## 6 2003-09-30 00:00:00 355752 421785 488222
## # … with 55 more variables: `Riverside County` <dbl>, `San Bernardino
## # County` <dbl>, `Santa Clara County` <dbl>, `Alameda County` <dbl>,
## # `Sacramento County` <dbl>, `Contra Costa County` <dbl>, `Fresno
## # County` <dbl>, `Kern County` <dbl>, `Ventura County` <dbl>, `San Francisco
## # County` <dbl>, `San Mateo County` <dbl>, `San Joaquin County` <dbl>,
## # `Stanislaus County` <dbl>, `Sonoma County` <dbl>, `Tulare County` <dbl>,
## # `Santa Barbara County` <dbl>, `Monterey County` <dbl>, `Solano
## # County` <dbl>, `Placer County` <dbl>, `San Luis Obispo County` <dbl>,
## # `Santa Cruz County` <dbl>, `Merced County` <dbl>, `Marin County` <dbl>,
## # `Butte County` <dbl>, `Yolo County` <dbl>, `El Dorado County` <dbl>,
## # `Shasta County` <dbl>, `Imperial County` <dbl>, `Kings County` <dbl>,
## # `Madera County` <dbl>, `Napa County` <dbl>, `Humboldt County` <dbl>,
## # `Nevada County` <dbl>, `Sutter County` <dbl>, `Mendocino County` <dbl>,
## # `Yuba County` <dbl>, `Lake County` <dbl>, `Tehama County` <dbl>, `Tuolumne
## # County` <dbl>, `San Benito County` <dbl>, `Calaveras County` <dbl>,
## # `Siskiyou County` <dbl>, `Amador County` <dbl>, `Lassen County` <dbl>, `Del
## # Norte County` <dbl>, `Glenn County` <dbl>, `Colusa County` <dbl>, `Plumas
## # County` <dbl>, `Inyo County` <dbl>, `Mariposa County` <dbl>, `Mono
## # County` <dbl>, `Trinity County` <dbl>, `Modoc County` <dbl>, `Sierra
## # County` <dbl>, `Alpine County` <dbl>
Let’s take a look at the data above. The data is messy. I’m going to reshape the data to the format I prefer, which all counties are under a new column called “County” and each county has a corresponding value — property price.
library(tidyr)
CA_house_price <- CA_house_price %>%
gather('Los Angeles County':'Alpine County', key = 'County', value = 'Price')
head(CA_house_price) #after reshape
## # A tibble: 6 x 3
## Date County Price
## <dttm> <chr> <dbl>
## 1 2003-04-30 00:00:00 Los Angeles County 330673
## 2 2003-05-31 00:00:00 Los Angeles County 335215
## 3 2003-06-30 00:00:00 Los Angeles County 339000
## 4 2003-07-31 00:00:00 Los Angeles County 344318
## 5 2003-08-31 00:00:00 Los Angeles County 349916
## 6 2003-09-30 00:00:00 Los Angeles County 355752
I will need to create a map later, but in order to create a geographic visualization, I need the longitude and latitude of each county. To do this, I geocode each county on Google Sheet with the help of Google Map GEOCODE_GOOGLE function.
But first, after reshaping the data, I’m going to visualize the trend of property price over the years.
library(dplyr)
##
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
##
## filter, lag
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
CA_house_price$Date <- as.Date(CA_house_price$Date)
#as.Date is a function that converts text to date data type
#transform the date from dttm type to date type (to better filter a certain date or period of time)
CA_house_price <- CA_house_price %>%
arrange(desc(Price)) #make the price in descending order
library(ggplot2)
library(plotly)
##
## Attaching package: 'plotly'
## The following object is masked from 'package:ggplot2':
##
## last_plot
## The following object is masked from 'package:stats':
##
## filter
## The following object is masked from 'package:graphics':
##
## layout
library(dplyr)
library(viridis)
## Loading required package: viridisLite
ca_county_lonlat <- read_excel("~/Desktop/R_Property/CA_PropertyPrice_byCounty/ca_county_lonlat.xlsx")
CA_house_price <- merge(CA_house_price,ca_county_lonlat, by="County") #merge the dataset that contains longitude and latitude with the original dataset
house_price <- ggplot()+
geom_line(data = CA_house_price, aes(x = Date, y = Price, group = County, color=Region))+
scale_color_manual(values = c("#fdc086", "#7fc97f", "#beaed4"))+
theme_bw()
ggplotly(house_price)
From the line chart above, it shows the trend of property price in all 58 counties from 2003 to date.
Now, I’m going to create a chropleth map by using the most up-to-date property price of each county in California.
library(rgdal) # read the shape file with the rgdal library
## Loading required package: sp
## rgdal: version: 1.5-8, (SVN revision 990)
## Geospatial Data Abstraction Library extensions to R successfully loaded
## Loaded GDAL runtime: GDAL 2.4.2, released 2019/06/28
## Path to GDAL shared files: /Library/Frameworks/R.framework/Versions/3.6/Resources/library/rgdal/gdal
## GDAL binary built with GEOS: FALSE
## Loaded PROJ runtime: Rel. 5.2.0, September 15th, 2018, [PJ_VERSION: 520]
## Path to PROJ shared files: /Library/Frameworks/R.framework/Versions/3.6/Resources/library/rgdal/proj
## Linking to sp version:1.4-2
## To mute warnings of possible GDAL/OSR exportToProj4() degradation,
## use options("rgdal_show_exportToProj4_warnings"="none") before loading rgdal.
#California counties shapefile from https://gis.data.ca.gov/
CA_spdf = readOGR(
dsn= "~/Desktop/R_Property/California_County_Boundaries" ,
layer="cnty19_1"
)
## OGR data source with driver: ESRI Shapefile
## Source: "/Users/yihsuanshih/Desktop/R_Property/California_County_Boundaries", layer: "cnty19_1"
## with 69 features
## It has 10 fields
# filter only on the date 2021-06-30 (the latest data)
CA_house_price <- CA_house_price %>%
filter(Date == "2021-06-30")
# ca_county_lonlat <- read_excel("~/Desktop/R_Property/CA_MediaIncome+PropertyPrice_byCounty/ca_county_lonlat.xlsx")
#
# CA_house_price <- merge(CA_house_price,ca_county_lonlat, by="County") #merging the dataset with longitude and latitude and the original dataset
CA_house_price <- CA_house_price %>%
mutate(County=as.factor(County)) #mutate County data type from character to factor
library(leaflet)
library(data.table)
##
## Attaching package: 'data.table'
## The following objects are masked from 'package:dplyr':
##
## between, first, last
library(sp)
library(rgdal)
library(dplyr)
library(ggpubr)
library(grid)
library(leaflet)
# prepare the text for tooltips:
labels <- sprintf(
"<strong>%s, CA</strong><br/>Average property price: <br/>$%g",
CA_house_price$County, CA_house_price$Price) %>%
lapply(htmltools::HTML)
# #make data spatial
coordinates(CA_house_price) = c("lon","lat")
ccrs.geo1 = CRS("+proj=longlat")
proj4string(CA_house_price) = CRS("+proj=longlat") #project "CA_house_price"
CA_spdf = spTransform(CA_spdf, CRS("+proj=longlat"))
#reproject shapefile's coordinates, but need to transform shapefile same as CA_house_price first!!! IT IS IMPORTANT! THIS IS THE WHOLE POINT!
#set colors
pal <- colorBin("OrRd", CA_house_price$Price)
# Final Map
map <- leaflet(CA_spdf) %>%
addTiles()%>%
addProviderTiles(providers$CartoDB.Positron)%>%
setView(-119.4179, 36.7783, 5.8) %>% #set the lon and lat in California
addPolygons(
fillColor = ~pal(CA_house_price$Price),
weight = 2,
opacity = 1,
color = "white",
dashArray = "1",
fillOpacity = 0.9,
highlight = highlightOptions(
weight = 3,
color = "#666",
dashArray = "",
fillOpacity = 0.7,
bringToFront = TRUE),
label = labels,
labelOptions = labelOptions(
style = list("font-weight" = "normal", padding = "4px 8px"),
textsize = "15px",
direction = "auto")) %>%
addLegend( pal=pal, values=~CA_house_price$Price, opacity=0.9, title = "Average Property Price", position = "bottomleft" )
map