• Data cleansing
• Data overview
  • How many Tree forts in each city?
  • The highest price for one tree fort
  • How many reviews in each city?
  • where is the most reviewed tree fort?
  • The ratio of reviewed tree fort in each city
• Which are the most expensive cities in America to book a Tree Fort
• How to make a PivotTable in R

Recently, I read the The Priceonomics Data Puzzle: TreefortBnb and write my answer at here.

In general, to deal with question like this, I often using three steps:

1. Data cleansing
2. Data overview
3. Answser the question.

Data cleansing

At first look of TreefortBnb data, I find that City names are given in mixed case.

TreefortBnb <- read.csv(url("https://s3.amazonaws.com/pix-media/Data+for+TreefortBnB+Puzzle.csv"),
                        comment.char="@")
names(TreefortBnb)[4:5] <- c("Price","Reviews")
unique(TreefortBnb$City[grepl("new york",TreefortBnb$City,ignore.case = TRUE)])

## [1] New York new york
## 106 Levels: Albuquerque Alexandria Anchorage Ann Arbor ... West Hollywood

For example, there are 8043 Tree forts in New York City and 1 Tree forts in new york City. To avoid the misunderstanding, I convert city names to lower case.

TreefortBnb[,"City"] <- tolower(TreefortBnb$City)

Also, Some cities have more than one occurrence of the same name in different states. We need a new tag ‘city-state’ to distinguish them.

TreefortBnb[,"CityState"] <- paste(TreefortBnb$City,TreefortBnb$State,sep=", ")

After data cleansing, there are 124 cities in dataset.

Data overview

Let’s take a look at the overview of TreefortBnb data. Here are some questions on my mind.

• How many Tree forts in each city?
• What is the highest price for one tree fort?
• How many reviews in each city?
• where is the most reviewed tree fort?
• The ratio of reviewed tree fort in each city.

How many Tree forts in each city?

In the figure shown below, the highest number of tree forts (8044) is in new york, NY.

The median number of tree forts is 85, some top 10 big city like new york, brooklyn, los angeles and san francisco control more than half of the total tree forts.

Top 10 Cities by Tree fort numbers are:

The highest price for one tree fort

As we can see from the figure above, range of the price for one tree fort is from 10 to 10000 and the highest price is in san francisco, CA, park city, UT and miami beach, FL.

How many reviews in each city?

From the figure above, the highest reviewed city is in new york, NY.

Top 10 Cities by reviews are:

It seems that the top 10 reviewed city is correlated with the top 10 cities by tree fort number. There is 8 overlaped cities between top 10 reviews and top 10 tree fort number.

In conclusion, the more tree fort in a city, the more chance a visitor will book a tree fort and give a review about it.

where is the most reviewed tree fort?

There are 208 units are reviewed by 99 times, and their locations are in denver, new york, los angeles, las vegas, washington, silver spring, chicago, long island city, austin, brooklyn, seattle, san francisco, nashville, salt lake city, philadelphia, somerville, san diego, venice, new orleans, cambridge, portland, queens, incline village, santa cruz, boston, paris, eugene, savannah, santa rosa, jersey city, albuquerque, miami beach, arlington, boulder, baltimore, honolulu, alexandria, sonoma, carmel. We can see the highest Total Reviews is in illinois.

There is no tree fort in the gray state around USA.

I am not familiar with geography and climate of USA, so just guess some reasons may relate to the result:

1. maybe these states have dry climates, there is no trees suitable to build tree fort.
2. no visitors need a tree fort in these state. (no-tree-fort-demand)
3. just no data at that time.

By the way, I must metioned at here: geographic profile maps which are basically just population maps

The ratio of reviewed tree fort in each city

We can find there are many tree fort without any reviews. After calculate ratio of reviewed tree fort in each city, brookline, berkeley, dallas, long beach, madison, paris, pasadena, phoenix, richmond are the cities without any tree fort reivews.

The high review ratio doesn’t mean the tree fort is popular, cause there is only one tree fort in some cities, if one tourist reviewed it, the ratio will be 100%.

Which are the most expensive cities in America to book a Tree Fort

A naive thought about that is sort the tree fort price and find the top 100 as below:

Back to the original question, as the generate study above. I think we can not just sort the price to get the answer.

To define the most expensive tree fort, I think it must including three aspects:

1. tree fort median price;
2. tree fort number;
3. reviews number/ratio.

If a tree fort with high price in Texas without any tourist visit, the expensive tree fort does not popular. Texas may be the most expensive cities, or the most no-tree-fort-demand cites.

As we can see the most reviews ratios are between 0.25 to 0.75 in the below figure.

I also calculated the reviews z-score.

Some tree fort without reivews are much expensive than the reviewed one in the same city. It will promotes the median price of tree fort in one city, but doesn’t show us the reality of visitor acceptable fort tree price in cities.

After filter out tree fort without reviews, I pick up top 100 cities with median price sort in decreasing order.

That’s my answer about the question.

How to make a PivotTable in R

I use dplyr package to calculate/count the number I needed and make a PivotTabel.

library(dplyr)
TreefortBnb.df <- 
    TreefortBnb %>% 
    group_by(CityState) %>%
    summarise(FortNumber = length(CityState),MedianPrice = median(Price),
              TotalReviews = sum(Reviews),MedianReviews = median(Reviews),
              ZscoreReviews = mean(Reviews)/sd(Reviews),
              City = unique(City),State = unique(State),
              ReviewsRatio=sum(Reviews>0)/length(Reviews),
              MedianPricewithReviewsNumber=median(Price[Reviews>0])
              ) %>%
    arrange(desc(FortNumber)) %>%
    as.data.frame