CARTO: under the covers of Sin City Yelp reviews

Today we're going to look into publicly available data from Yelp specifically from Las Vegas. The data includes reviews on businesses mostly restaurants all around the Greater Las Vegas area. We thought this data would be interesting to build our latest installment of Mapping the Invisible. Using this data has some restrictions from Yelp so we suggest you look over their use agreement before going too deep. But we do think the methods we show you here could inspire you to extract new understanding from your own data.

Here we will show you how you can transform reviews into insightful visualizations. Using the Yelp data itself may be a bit tricky as the format of the associations takes a small bit of munging. If you are curious how we did this specific bit of work you can take a look at a some notes here. For now we'll focus a bit on some neat insights from the data!

The New Modern City

Over the last 20 years the population of metropolitan Las Vegas has exploded from 770 000 in 1990 to over [2 million in 2013]. This infusion of people is expected to continue as the growth is largely due to people finding work or moving to retire in the area. This has changed the culture of Las Vegas from a tourist town focused on casinos and poker tables to one that is attracting new industries to properly service its booming population.

Even in the face of the recent turnover Las Vegas still hosts 40 million visitors every year. Inspired by many maps before us we thought it would be interesting to take a closer look at the interaction of the city between the tourists and the locals.

Where the Tourists Prefer to Roam

To make this map we assume users that post less than 20% of their reviews in Vegas are tourists. With this simple division of the data into tourists and locals we then calculate what percentage of reviews posted by each category. This can all be done with a small bit of SQL. Next we make a map to show how the two categories spread out over the city. What pops out is something you might already expect: tourists love to spend time and review businesses on the Strip whereas the rest of the city is filled with locals.

While the map is simple it is a good starting point to dig deeper. For example if we focus on just the tourists we can start pulling interesting information about how they interact with the city. One thing we can do is look at the hotels the tourists review or more directly we can assume that if a tourist reviews a hotel that the tourist stayed in that hotel. That could be interesting! So armed with this little insight into our tourists let's look at how visitors of different hotels look at the city.

From the visualization we can quickly see that most of the establishments were frequented by people who stayed in hotels nearby. Vegas is hot so maybe it makes sense! For new businesses in Vegas this is a small insight that could help you decide where you will place your business to attract just the right crowd. In particular you could dig in further to see exactly what types of reviewers frequent each hotel. Next we wanted to look at how price range shifted across the Vegas area. Understand price might help uncover what types of clients move in different neighborhoods. The visualization below shows the average price (more read means more expensive) of businesses in each hexagon across the city.

The most obvious pattern in the map above is that the strip is just plain expensive compared to the rest of the city. You can also see that there might be a slight pattern of more expensive shops on the southwest end of the strip and slightly cheaper shops in the northeast.

We generated the price hexagons using the following SQL. If you are interested you can transform this code to also visualize other trends in the data (e.g. where highly rated institutions are or where the institutions are generally populated).

##_INIT_REPLACE_ME_PRE_##

select * from (
    select the_geom_webmercator  count(points) as shopcount  avg(stars) as avgStars  avg(price) as avgPrice
    from (
        select a.*  st_transform(the_geom  3857) as points  price  stars
        from(
            select st_transform(
                CDB_HexagonGrid(
                    st_setsrid(
                        st_envelope(
                            st_collect(the_geom)
                        )  4326) 
                .005)  3857) as the_geom_webmercator
            from vegas_pricegrid ) as a

        left join vegas_pricegrid
            on st_contains(a.the_geom_webmercator  st_transform(the_geom  3857))
    where the_geom is not null ) as b
group by the_geom_webmercator) as c

where shopcount >= 7 ##_END_REPLACE_ME_PRE_##

But What Happens In Vegas Happens In Phoenix?

In contrast to all the tourism the recent rapid growth of Vegas's local population opens the door to a few other questions. One in particular we were interested on was whether new shops restaurants and services have been built fast enough for the new locals. To explore the idea we compared Las Vegas with Phoenix in two specific categories — Mexican restaurants and dentists.

In the visualizations below we show a distance map by neighborhood to both of these institutions for Phoenix and Las Vegas. The institutions are distinguished by their ratings; red dots indicate stores that are now closed and orange indicates places that don’t have enough ratings hinting that it could be a newer business. From the chart of Mexican restaurants it seems that no neighborhood is very far from one (thank goodness!); however many new stores have opened up in Sunrise Manor and Charleston Heights. We also see that many of the failed restaurants are also located in the Charleston Heights area leaving us wondering is Charleston Heights not as attractive a place to start a restaurant? We see a similar tendency in Phoenix where Tucson shows a larger density of failed Mexican restaurants whereas higher rated ones are clustered in the Central Phoenix area.

Using a similar approach we look into the distribution of dentists. Here we note that in general the coverage seems better in Vegas than in Phoenix. However in places like North Las Vegas and Sunrise Manor there could be some opportunities to open a successful dentist. Given that we haven’t taken a closer look into demographic data one would have to understand these aspects before starting a business; however this could give some insight on where opportunities could exist in the future.

The maps above were created using some advanced SQL that we encourage you to explore.

This is just a few more examples of how you can use CartoDB to explore your data. If you’re interested in checking it out create a free account and start mapping your own data today.