The Priceonomics Data Puzzle: TreefortBnb

##Load dependencies
library(pipeR)
library(dplyr)
library(formattable)

##URL to download data
url<-"https://s3.amazonaws.com/pix-media/Data+for+TreefortBnB+Puzzle.csv"

#Read in data and aggregate # of units by city and subset to top 100
df1 <- url %>%
  read.csv(col.names=c("ID","City","State","Price","Reviews")) %>%
  group_by(State, City) %>%
  summarize(Unit_Sum=n()) %>% 
  arrange(desc(Unit_Sum)) %>%
  head(100) 

#Read in data again and obtain median rental price by city
df2 <- url %>%
  read.csv(col.names=c("ID","City","State","Price","Reviews")) %>%
  group_by(State, City) %>%
  summarize(Price_Median=median(Price)) %>%
  arrange(desc(Price_Median)) 

#Merge two data frames
df3 <-merge(df1, df2, by=c("City", "State"), all.x=TRUE, all.y=FALSE)
df3<-arrange(df3, desc(Price_Median))

#Rename columns
colnames(df3)[3]<-"Unit Total"
colnames(df3)[4]<-"Median Price"

#Produce output table in html
formattable(df3, list(
  'Median Price' = color_bar("orange")))

#Read in data and aggregate # of units by city and subset to top 100
df1.b <- url %>%
  read.csv(col.names=c("ID","City","State","Price","Reviews")) %>%
  subset(Reviews > 0) %>% #subset to only include reviewed listings
  group_by(State, City) %>%
  summarize(Unit_Sum=n()) %>% 
  arrange(desc(Unit_Sum)) %>%
  head(100) 

#Read in data again and obtain median rental price by city
df2.b <- url %>%
  read.csv(col.names=c("ID","City","State","Price","Reviews")) %>%
  subset(Reviews > 0) %>% #subset to only include reviewed listings
  group_by(State, City) %>%
  summarize(Price_Median=median(Price)) %>%
  arrange(desc(Price_Median)) 

#Merge two data frames
df3.b <-merge(df1.b, df2.b, by=c("City", "State"), all.x=TRUE, all.y=FALSE)
df3.b<-arrange(df3.b, desc(Price_Median))

#Rename columns
colnames(df3.b)[3]<-"Unit Total"
colnames(df3.b)[4]<-"Median Price"

#Produce output table in html
df3.b<-head(df3.b, 10)
formattable(df3.b, list(
  'Median Price' = color_bar("orange")))

#Load Dependencies
library(ggplot2)
library(cowplot)

#Batch geocode with Bing
options(BingMapsKey="AtquMkrNaB7ME7krIpwQgrTwEqwB0HbUEpRKb9wfpBW-xCbgBzrGabEyUGkdpO0G")
df3$Location<-paste(df3$City, df3$State, sep = ", ")
df30<-t(geocodeVect(df3$Location, service="bing", returntype="coordinates"))
df10<-as.data.frame(df30)
colnames(df10)<-c("lat","lon")
df3<-cbind(df3, df10)
df3<-as.data.frame(df3)

#Subset to only include lower 48
df3<- df3 %>%
  subset(State!="HI") %>%
  subset(State!="AK")
row.names(df3) <- NULL 

states <- map_data("state")
states <- states[order(states$order), ]

ggplot() +
  geom_polygon(data=states, aes(long, lat, group=group),
               size=0.1,fill="black", color="white", alpha=0.75) +
  geom_point(data=df3, aes(lon, lat, size= `Median Price`), 
             color="blue", alpha=0.5) +
  geom_point(data=df3, shape = 1, 
             aes(lon, lat, size=`Median Price`), 
             colour = "white", alpha=0.7) +
   theme(axis.line=element_blank(),
         axis.text.x=element_blank(),
         axis.text.y=element_blank(),
         axis.ticks=element_blank(),
         axis.title.x=element_blank(),
         axis.title.y=element_blank(),
         legend.position="bottom",
         panel.background=element_blank(),
         panel.border=element_blank(),
         panel.grid.major=element_blank(),
         panel.grid.minor=element_blank(),
         plot.background=element_blank())

# Load dependencies
library(jsonlite)
library(data.table)

# Scrape data from site
appData <- fromJSON("http://priceonomics.com/static/js/hotels/all_data.json")

# replicate table
data2 <- data.frame(City = names(appData), Price = sapply(appData, function(x) x$air$apt$p), 
    stringsAsFactors = FALSE)

# Arrange data
setDT(data2)
data2 <- data2[order(Price, decreasing = TRUE)]
data2 <- as.data.frame(data2)
colnames(data2) <- c("Location", "AirPrice")

# Subset to only include 25 busiest tree fort markets
dddd <- df3
colnames(dddd)[3] <- "UnitTotal"
data3 <- arrange(dddd, desc(UnitTotal))
data3 <- head(data3, 25)

# Imput missing AirBnb values
data4 <- merge(data3, data2, by = "Location", all.x = TRUE, all.y = FALSE)
data4$AirPrice <- as.character(data4$AirPrice)
data4[6, 8] <- 104
data4[10, 8] <- 172
data4[13, 8] <- 158
data4[20, 8] <- 99
data4[23, 8] <- 141
colnames(data4)[5] <- c("PriceMedian")
data4$PriceMedian <- as.numeric(data4$PriceMedian)
data4$AirPrice <- as.numeric(data4$AirPrice)

# Generate first difference
data4$first_diff <- data4$PriceMedian - data4$AirPrice  #Negative value 
# mean AirBnb more expensive - positive means TreefortBnb is more
# expensive

# Create variable distinguishing neg from pos values of 1st difference
data4$abs_first_diff <- abs(data4$first_diff)
data4$Air_pricier <- ifelse(data4$first_diff < 0, 0, 1)
data4$Air_pricier <- as.factor(data4$Air_pricier)

# Plot
library(cowplot)  #theme
a <- ifelse(data4$first_diff < 1, ifelse(data4$Air_pricier != 0, "red", 
    "darkgrey"), "blue")

ggplot(data4, aes(x = Location, y = first_diff)) + geom_bar(stat = "identity", 
    aes(fill = Air_pricier)) + ylab("First Difference") + xlab("") + theme(axis.text.x = element_text(angle = 35, 
    hjust = 1)) + theme(axis.text.x = element_text(colour = a, size = 10)) + 
    scale_fill_manual(values = c("darkgrey", "darkblue")) + theme(legend.position = "none", 
    panel.background = element_blank())