Introduction of data

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 

Multi-line chart

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.

Choropleth map

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