LogoLogo
HomeDocumentationLoginTry for free
  • CARTO Academy
  • Working with geospatial data
    • Geospatial data: the basics
      • What is location data?
      • Types of location data
      • Changing between types of geographical support
    • Optimizing your data for spatial analysis
    • Introduction to Spatial Indexes
      • Spatial Index support in CARTO
      • Create or enrich an index
      • Work with unique Spatial Index properties
      • Scaling common geoprocessing tasks with Spatial Indexes
      • Using Spatial Indexes for analysis
        • Calculating traffic accident rates
        • Which cell phone towers serve the most people?
    • The modern geospatial analysis stack
      • Spatial data management and analytics with CARTO QGIS Plugin
      • Using data from a REST API for real-time updates
  • Building interactive maps
    • Introduction to CARTO Builder
    • Data sources & map layers
    • Widgets & SQL Parameters
    • AI Agents
    • Data visualization
      • Build a dashboard with styled point locations
      • Style qualitative data using hex color codes
      • Create an animated visualization with time series
      • Visualize administrative regions by defined zoom levels
      • Build a dashboard to understand historic weather events
      • Customize your visualization with tailored-made basemaps
      • Visualize static geometries with attributes varying over time
      • Mapping the precipitation impact of Hurricane Milton with raster data
    • Data analysis
      • Filtering multiple data sources simultaneously with SQL Parameters
      • Generate a dynamic index based on user-defined weighted variables
      • Create a dashboard with user-defined analysis using SQL Parameters
      • Analyzing multiple drive-time catchment areas dynamically
      • Extract insights from your maps with AI Agents
    • Sharing and collaborating
      • Dynamically control your maps using URL parameters
      • Embedding maps in BI platforms
    • Solving geospatial use-cases
      • Build a store performance monitoring dashboard for retail stores in the USA
      • Analyzing Airbnb ratings in Los Angeles
      • Assessing the damages of La Palma Volcano
    • CARTO Map Gallery
  • Creating workflows
    • Introduction to CARTO Workflows
    • Step-by-step tutorials
      • Creating a composite score for fire risk
      • Spatial Scoring: Measuring merchant attractiveness and performance
      • Using crime data & spatial analysis to assess home insurance risk
      • Identify the best billboards and stores for a multi-channel product launch campaign
      • Estimate the population covered by LTE cells
      • A no-code approach to optimizing OOH advertising locations
      • Optimizing site selection for EV charging stations
      • How to optimize location planning for wind turbines
      • Calculate population living around top retail locations
      • Identifying customers potentially affected by an active fire in California
      • Finding stores in areas with weather risks
      • How to run scalable routing analysis the easy way
      • Geomarketing techniques for targeting sportswear consumers
      • How to use GenAI to optimize your spatial analysis
      • Analyzing origin and destination patterns
      • Understanding accident hotspots
      • Real-Time Flood Claims Analysis
      • Train a classification model to estimate customer churn
      • Space-time anomaly detection for real-time portfolio management
      • Identify buildings in areas with a deficit of cell network antennas
    • Workflow templates
      • Data Preparation
      • Data Enrichment
      • Spatial Indexes
      • Spatial Analysis
      • Generating new spatial data
      • Statistics
      • Retail and CPG
      • Telco
      • Insurance
      • Out Of Home Advertising
      • BigQuery ML
      • Snowflake ML
  • Advanced spatial analytics
    • Introduction to the Analytics Toolbox
    • Spatial Analytics for BigQuery
      • Step-by-step tutorials
        • How to create a composite score with your spatial data
        • Space-time hotspot analysis: Identifying traffic accident hotspots
        • Spacetime hotspot classification: Understanding collision patterns
        • Time series clustering: Identifying areas with similar traffic accident patterns
        • Detecting space-time anomalous regions to improve real estate portfolio management (quick start)
        • Detecting space-time anomalous regions to improve real estate portfolio management
        • Computing the spatial autocorrelation of POIs locations in Berlin
        • Identifying amenity hotspots in Stockholm
        • Applying GWR to understand Airbnb listings prices
        • Analyzing signal coverage with line-of-sight calculation and path loss estimation
        • Generating trade areas based on drive/walk-time isolines
        • Geocoding your address data
        • Find similar locations based on their trade areas
        • Calculating market penetration in CPG with merchant universe matching
        • Measuring merchant attractiveness and performance in CPG with spatial scores
        • Segmenting CPG merchants using trade areas characteristics
        • Store cannibalization: quantifying the effect of opening new stores on your existing network
        • Find Twin Areas of top-performing stores
        • Opening a new Pizza Hut location in Honolulu
        • An H3 grid of Starbucks locations and simple cannibalization analysis
        • Data enrichment using the Data Observatory
        • New police stations based on Chicago crime location clusters
        • Interpolating elevation along a road using kriging
        • Analyzing weather stations coverage using a Voronoi diagram
        • A NYC subway connection graph using Delaunay triangulation
        • Computing US airport connections and route interpolations
        • Identifying earthquake-prone areas in the state of California
        • Bikeshare stations within a San Francisco buffer
        • Census areas in the UK within tiles of multiple resolutions
        • Creating simple tilesets
        • Creating spatial index tilesets
        • Creating aggregation tilesets
        • Using raster and vector data to calculate total rooftop PV potential in the US
        • Using the routing module
      • About Analytics Toolbox regions
    • Spatial Analytics for Snowflake
      • Step-by-step tutorials
        • How to create a composite score with your spatial data
        • Space-time hotspot analysis: Identifying traffic accident hotspots
        • Computing the spatial autocorrelation of POIs locations in Berlin
        • Identifying amenity hotspots in Stockholm
        • Applying GWR to understand Airbnb listings prices
        • Opening a new Pizza Hut location in Honolulu
        • Generating trade areas based on drive/walk-time isolines
        • Geocoding your address data
        • Creating spatial index tilesets
        • A Quadkey grid of stores locations and simple cannibalization analysis
        • Minkowski distance to perform cannibalization analysis
        • Computing US airport connections and route interpolations
        • New supplier offices based on store locations clusters
        • Analyzing store location coverage using a Voronoi diagram
        • Enrichment of catchment areas for store characterization
        • Data enrichment using the Data Observatory
    • Spatial Analytics for Redshift
      • Step-by-step tutorials
        • Generating trade areas based on drive/walk-time isolines
        • Geocoding your address data
        • Creating spatial index tilesets
Powered by GitBook
On this page
  • Context
  • Step-by-Step Guide:
  • Visualizing Airbnb listings
  • Aggregating data to a H3 grid
  • Enriching the grid with demographic data
  • Estimating the influence of variables on the score

Was this helpful?

Export as PDF
  1. Building interactive maps
  2. Solving geospatial use-cases

Analyzing Airbnb ratings in Los Angeles

Last updated 10 months ago

Was this helpful?

Context

Founded in 2008, Airbnb has quickly gained global popularity among travelers. To elevate this service, identifying the determinants of listing success and their role in drawing tourism is pivotal. The users' property ratings focus on criteria such as accuracy, communication, cleanliness, location, check-in, and value.

This tutorial aim to extract insights into Airbnb users' overall impressions, connecting the overall rating score with distinct variables while taking into account the geographical neighbors behavior through a Geographically Weighted Regression model.

We'll also dive into the regions where location ratings significantly influence the overall score and enrich this analysis with sociodemographic data from CARTO's Data Observatory.

This tutorial will take you through the following sections:


Step-by-Step Guide:

Visualizing Airbnb listings

  1. Access the Maps section from your CARTO Workspace using the navigation menu and create a New Map.

  1. Add Los Angeles Airbnb data from CARTO Data Warehouse.

    • Select the Add source from button at the bottom left on the page.

    • Click on the CARTO Data Warehouse connection.

    • Navigate through demo data > demo tables to losangeles_airbnb_data and select Add source.

  2. Let's add some basic styling! Rename the map to Map 1 Airbnb initial data exploration. Then click on Layer 1 in the Layers panel and apply the following:

    • Name (select the three dots next to the layer name): Airbnb listings

    • Color: your pick!

    • Outline: white, 1px stroke

    • Radius: 3

  3. Switch from Layers to Interactions at the top left of the UI. Enable interactions for the layer.

    • Select a style for the pop-up window; we'll use light.

    • From the drop-down menu, select the variable price_num.

    • Select # to format the numbers as dollars. In the box to the right, rename the field Price per night.

We will now inspect how Airbnb listings are distributed across Los Angeles and aggregate the raw data to have a better understanding on how different variables vary geographically within the city.

Now let's add a new data source to visualize the airbnb listings using an H3 grid.


Aggregating data to a H3 grid

  • Ease of interpreting spatial trends on your map

  • Ability to easily enrich that grid with multiple data sources

  • Suitability for spatial modelling like Geographically Weighted Regression...

...all of which we'll be covering in this tutorial!

  1. In the CARTO Workspace, head to Workflows and select + New Workflow, using the CARTO Data Warehouse connection.

  2. At the top left of the new workflow, rename the workflow "Airbnb analysis."

  3. In the Sources panel (left of the window), navigate to Connection Data > demo data > demo_tables and drag losangeles_airbnb_data onto the canvas.

  4. Switch from Sources to Components, and locate H3 from GeoPoint. Drag this onto the canvas to the right of losangeles_airbnb_data and connect the two together. Set the H3 resolution to 8. This will create a H3 grid cell for every Airbnb location.

  5. Back in Components, locate Group by. Drag this to the right of H3 from GeoPoint, connecting the two. We'll use this to create a frequency grid and aggregate the input numeric variables:

    1. Set the Group by field to H3.

    2. For the aggregation columns, set review_scores_cleanliness, review_scores_location, review_scores_value, review_scores_rating and price_num to AVG. Add a final aggregation column which is H3 - COUNT (see below).

  1. Connect this Group by component to a Rename column component, renaming h3_count to airbnb_count.

  2. Finally, connect the Rename column count to a Save as Table component, saving this to CARTO Data Warehouse > Organization > Private and calling it airbnb_h3r8. If you haven't already, run your workflow!

Prefer to use SQL?

You can replicate this in the CARTO Builder SQL console with the following code:

WITH h3_airbnb AS (
  SELECT
    `carto-un`.carto.H3_FROMGEOGPOINT(geom,
      8) AS h3,
      *
  FROM
    carto-demo-data.demo_tables.losangeles_airbnb_data),

aggregated_h3 AS (
  SELECT
    h3,
    ROUND(AVG(price_num), 2) price,
    ROUND(AVG(review_scores_rating), 2) overall_rating,
    ROUND(AVG(review_scores_value), 2) value_vs_price_rating,
    ROUND(AVG(review_scores_cleanliness), 2) cleanliness_rating,
    ROUND(AVG(review_scores_location), 2) location_rating,
    COUNT(*) AS total_listings
  FROM
    h3_airbnb
  GROUP BY
    h3)
	
SELECT * FROM aggregated_h3

Now, head back to the CARTO Builder map that we created earlier. Add the H3 aggregation table that you just created to the map (Sources > Add source from > Data Explorer > CARTO Data Warehouse > Organization > Private).

Let's style the new layer:

  • Name: H3 Airbnb aggregation

  • Order in display: 2

  • Fill color: 6 steps blue-yellow ramp based on column price_num_avg using Quantile color scale.

  • No stroke

Do you notice how it's difficult to see the grid beneath the Airbnb point layer? Let's enable zoom-based visibility to fix that, so we only see the points as we zoom in further. Go into the layer options for each layer, and set the Visibility by zoom layer to 11-21 for Airbnb listings.

You might also find the basemap more difficult to read now we have a grid layer covering it. Head to the basemaps panel (to the right of Layers) and switch to Google Maps > Positron. You'll now notice some of the labels sit on top of your grid data.

Now, let's try looking at this in 3D! At the center-top of the whole screen, switch to 3D view - then in H3 Airbnb aggregation:

  • Toggle the Height button and style this parameter using:

    • Column: airbnb_count (SUM)

    • Height scale: sqrt

    • Value: 50

Inspect the map results carefully. Notice where most listings are located and where the areas with highest prices are. Optionally, play with different variables and color ramps.

Now let's start to dig a little deeper into our data!


Enriching the grid with demographic data

This dataset holds information that can be useful to explore the influence of different factors, including variables such as the total population, the urbanity level or the presence of certain type of points of interests in different areas.

  • Countries: United States of America

  • Licenses: Public data

  • Sources: CARTO

  1. Select the Spatial Features - United States of America (H3 Resolution 8) dataset and click on Subscribe for free. This action will redirect us to the subscription level at the Data Explorer menu.

  1. Head back into the workflow you created earlier.

  2. Navigate to Sources > Data Observatory > CARTO and find the table you just subscribed to and drag it onto the canvas, just below the final Save as Table component. Can't find it? Try refreshing your page.

  3. Using a Join component, connect the output of Save as Table to the top input, and of Spatial Features to the bottom. Set the join columns from each table to H3, and the join type to left - meaning that all features from the first input (Save as Table) will be retained. Run!

  4. We now have a huge amount of contextual data to help our analysis - in fact, far more than we want! Connect the output of the join to an Edit schema component, selecting only the columns from your original Airbnb grid, plus population and urbanity.

From here, you can save this as a table and explore it on a map - or move on to the final stage of this tutorial.


Estimating the influence of variables on the score

This model will allow us to extract insights of what the overall impression of Airbnb users depends on, by relating the overall rating score with different variables (specifically we will use: value, cleanliness and location)

We will also visualize where the location score variable significantly influences the ‘Overall rating’ result.

We will now proceed to calculate the GWR model leveraging CARTO Analytics Toolbox for BigQuery. You can do so using CARTO Workflows or your data warehouse console.

  1. In your workflow, connect a GWR component to the Edit schema component from earlier. The parameters used in GWR model will be as follows:

  • Index column: h3

  • Feature Variables:

    • review_scores_value_avg,

    • review_scores_cleanliness_avg

    • review_scores_location_avg

  • Target variable:

    • review_scores_rating_avg

  • Kring Size: 3

  • Kernel function: gaussian

  • Fit intercept: True

  1. Finally, let's add another join to rejoin Edit Schema to the results of the GWR analysis so we have all of the contextual information in one table ready to start building our map.

Run!

Prefer to use SQL?

You can replicate this in your data warehouse SQL console with the following code:

CALL `carto-un.carto`.GWR_GRID(
          'yourproject.yourdataset.yourtable',
          ['review_scores_location_avg','review_scores_cleanliness_avg', 'review_scores_value_avg'],
          'review_scores_rating_avg',
          'h3',
          'h3',
          3,
          'gaussian',
          true,
          'yourproject.yourdataset.yourtable')

Feel free to use another Save as Table component to materialise it, otherwise it will be stored as a temporary table and deleted after 30 days.

  1. In the CARTO Workspace under the Map tab, click on the three dots next to your original map and duplicate it, calling it Map 2 GWR Model map.

  2. Add your GWR layer in the same way you had added previous layers, and turn off the layer H3 Airbnb aggregation.

  3. Style the new layer (you may find it easier to turn the other layers off as you do this - you can just toggle the eye to the right of their names in the layer panel to do this):

    1. Name: Location relevance (Model)

    2. Layer order: 3 (the bottom)

    3. Fill Color: 5 step diverging Colorbrewer blue-red ramp based on review_scores_location_avg_coef_estimate. Here, negative values depict a negative relationship between the location score and overall score, and positive values depict a positive relationship (i.e. location plays an important role in the overall ranking). A good way of visualizing this is to begin with a Quantile color scale, and then switch to Custom and play around with the color bands until they reflect the same values moving away from a neutral band around zero (see below, where we have bands which diverage from -0.05 to 0.05).

    4. No stroke

  4. In the Legend panel (to the right of Layers), change the Color based on text to Location - Overall rating coefficient so it's easier for the user to understand.

  1. In the Basemaps panel (to the right of Layers) change the basemap to Google Maps Roadmap basemap.

  1. Click on the Dual map view button at the top of the screen (next to 3D mode) to toggle the split map option.

  • Left map: disable the Location relevance (Model)

  • Right map: disable the H3 AirBnB aggregation

Inspect the model results in detail to understand where the location matters the most for users' overall rating score and how the location rating values are distributed.

Try styling the map layers depending on other variables to have a better understanding on how different variables influence model results.

Now let's start adding some more elements to our map to help our users better navigate our analysis.

  1. Head to the Widgets panel, to the left of the Layers panel. Add the following widgets to the map:

  • Total listings

    • Layer: Airbnb listings

    • Type: Formula

    • Operation: COUNT

    • Formatting: Integers with thousand separators

    • Note: Total nº of Airbnb listings in the map extent.

  • Population near Airbnbs

    • Layer: H3 Airbnb aggregation

    • Type: Formula

    • Operation: SUM

    • Formatting: Decimal summarized (12.3K)

    • Aggregation column: population

    • Notes: Population in cells with Airbnbs

  • Urbanity

    • Layer: H3 Airbnb aggregation

    • Type: Pie

    • Operation: COUNT

    • Column: urbanity_joined_joined (MODE)

  1. In the Interactions tab (to the right of Widgets), add an interaction to H3 Airbnb aggregation so users can review attributes while navigating the map. Switch from Click to Hover and choose the style Light. Select the attributes population_joined_joined (sum), urbanity_joined_joined (mode) and airbnb_count_joined. Click on the variable options (#) to choose a more appropriate format and more readable field names. Your map should now be looking a bit like the below:

Navigate the map and observe how widget values vary depending on the viewport area. Check out specific areas by hovering over them and review pop-up attributes.

### Airbnb Ratings and Location Impact 🌟

![Image: Global Populated Places](https://app-gallery.cartocdn.com/builder/LosAngeles.jpg)

Explore the intricate relationship between Airbnb ratings and the geographical distribution of listings in Los Angeles with our dynamic map. This map provides valuable insights into what influences user ratings and offers a comprehensive view of the city's Airbnb landscape.

**Discover User Ratings** 📊
- Analyze how Airbnb users rate listings based on key factors such as accuracy, communication, cleanliness, location, check-in, and value.
- Visualize the distribution of ratings to uncover patterns that affect overall user impressions.

**Geographic Insights** 🗺️
- Dive into Los Angeles neighborhoods and observe how specific areas impact user ratings.
- Identify regions where location ratings significantly influence the overall score, and explore what makes these neighborhoods stand out.

**Sociodemographic Data Enrichment**
- Enhance your understanding of each neighborhood with sociodemographic insights from the CARTO Data Observatory.
- Access data on total population, urbanity level, tourism presence, and more to gain a holistic view of the city's dynamics.

If you click on the "eye" icon, you can preview what this looks like...

Now we are ready to share the results! 👇

You should have something that looks a little like this

Now let's aggregate this data to a H3 grid. This approach has multiple advantages:

So far we have seen how the Airbnb listings locations and its main variables are distributed across the city of Los Angeles. Next, we will enrich our visualization by adding CARTO Spatial Features H3 at resolution 8 dataset from .

In the CARTO Workspace, click on ‘Data Observatory’ to browse the and apply these filters:

Next we will apply a Geospatially Weighted Regression (GWR) model using the function to our Airbnb H3 aggregated data. We’ve already seen where different variables rate higher on our previous map.

Now let's add a rich description of our map so users can have more context - we'll be using . At the top right of the screen, select the "i" icon to bring up the Map Description tab (you can switch between this and widgets). You can copy and paste the below example or create your own.

Finally we can make the map public and share the link to anybody in the organization. For that you should go to “Share” on the top right corner and set the map as Public. For more details, see .

👇
Spatial Index
CARTO Data Observatory
Spatial Data Catalog
GWR_GRID
Markdown syntax
Publishing and sharing maps
Visualizing Airbnb listings
Aggregating Airbnb data to a H3 grid
Enriching the grid with demographic data
Estimating the influence of variables on the score
LogoIntroduction to Spatial Indexes | Academy
Intermediate difficulty banner
A screenshot of CARTO Builder
A screenshot of CARTO Workflows
A screenshot of CARTO Workflows
A screenshot of CARTO Builder
A screenshot of CARTO Builder
A screenshot of CARTO Builder
A screenshot of CARTO Builder
A screenshot of CARTO's Data Observatory