Page 846

. PROJECT : DETERMINING THE

PRICE OF AIRBNB NIGHTLY RENTALS
IN NEW YORK CITY
LO .
Propose the appropriate price to charge for Airbnb rentals using the SOAR
analytics model.

Airbnb ( Exhibit 12.2) is an online marketplace that connects people who would like
to rent an available room (or home) from someone who owns a room or home. Airbnb is
the broker, or intermediary, that matches those wishing to rent with those who have the
accommodations. Airbnb offers listings in over 81,000 cities in 191 countries and has
about 150 million users.1

Exhibit .
Daniel Krason/Shutterstock

In this assignment, we do not give you the same level of detailed lab instructions found
in the rest of this textbook. Instead, we provide general directions. From there, you
decide what needs to be done and the best ways to perform the analysis.
Page 847

Specify the Question

There are many potential questions, but the general question we want to answer is “What
predicts the price for a nightly Airbnb rental in New York City?”

Imagine that you’re a homeowner in New York City and you are trying to decide how to
set prices for a nightly rental. Which factors are important? Which are not? What should
you include in your model to predict the nightly rental price? Here are some factors you
might consider:

Location: Is it close to Central Park or the Empire State Building? Is it in Manhattan

or the Bronx?
Size: How large (or small) is the rental unit?
Furnishings: Are they old and dilapidated, or shiny and new?
Privacy: Do you need to share the space with the renter, or will the renter have
complete privacy?
Reviews: If past reviews are strong and the rental unit is popular, can you charge
more for it?
Day of the week: Can you charge more on weekends than on weekdays?

Obtain the Data

Now that we’ve specified the question, it is time to consider which data are available to
help us perform the analysis.

The data for this analysis are available at kaggle.com at this website ( Exhibit 12.3): ht
tps://www.kaggle.com/dgomonov/new-york-city-airbnb-open-data. Kaggle, which is a
subsidiary of Google, is an online community of data scientists and machine learning
practitioners. It allows users to publish data sets, build models, and work with other
members of the community.

Exhibit . Kaggle Site with the New York City Airbnb Open Data
Source: https://www.kaggle.com/datasets/dgomonov/new-york-city-airbnb-open-data. Accessed 8/2/2022.

Browse the site to get some ideas of what you can do with the data. There is plenty of
information about the sample, much of it relevant to the question we have specified.
Page 848

Step : Download the Data and Review the Data Set

Download and open the file named “AB_NYC_2019.csv” in Excel and browse the data.
Exhibit 12.4 is the data dictionary describing the data. (You may need to sign in using
a Google account and agree to Kaggle’s terms of use.)

Exhibit .4 Data Dictionary for Airbnb Listing Data in New York City

(Source of Data: https://www.kaggle.com/datasets/dgomonov/new-york-city-airbnb-open-data. Accessed 8/ / .

Column Variable Name Variable Explanation

A id listing id
B name name of the listing
C host_id host id
D host_name name of the host
E neighbourhood_group broad location
F neighbourhood more specific neighborhood
G latitude latitude coordinates
H longitude longitude coordinates
1 room_type listing space type (entire house, private room, or
shared room)
J price price in dollars
K minimum_nights minimum rental nights required
L num be rofrevi e ws how many reviews are made?
M last_review date of most recent review for this listing
N reviewspermonth reviews made within the last month
0 calculated host count of host listings
listings count
P availability_365 number of days available for rental per year

The Kaggle website contains additional descriptions, definitions, and discussions about
the data and other types of analysis that could be performed on the data. To access this
discussion, click on the “Discussion” tab.
Step : Identify the Dependent (Outcome) Variable
Because we are trying to determine the appropriate nightly rental rate, price is our
dependent (outcome) variable.

Step : Identify Potential Independent (or Explanatory)

Variables
The first step in determining the best data to use is to consider the various explanatory,
or independent, variables. Which variables are most likely to explain the price charged
for an Airbnb rental in New York City? Recall that the price of one night’s rental is the
dependent (outcome) variable that we are trying to explain (or predict). As an analyst, it
is your job to identify the variables that are most likely to explain the price. Look over
the data dictionary to determine which are likely to be the best candidate explanatory
variables.

Deliverable :
Once you have identified the variables that may explain the price of an Airbnb
rental, prepare a document (in Microsoft Word) and save it as “Project 1,
Deliverable 1: Airbnb Price Analysis First Name and Last Name” (inserting your
first and last name). Title the first section of the document “Dependent and
Independent Variables”. Next, within the document, identify and type the names
of the dependent (outcome) variable and at least five proposed independent
variables that you think explain price.

Page 849

Step 4: Clean and Transform the Data in Preparation for

Analysis
To prepare the data for analysis, complete this four-step process:
1. Because some of the listings have no date of last review, they are missing data. To
remedy this problem and still be able to include the listing in the analysis, replace
date of last_review with March 28, 2011. Color the font in red so you know you
inserted those data.
2. There are also missing data for reviews_per_month. To remedy this problem, insert
the value of zero (0) if reviews_per_month is missing. Color the font in red so you
know you inserted those data.
3. Calculate a variable called EntDum (a dummy variable that can carry the value of 1
or 0, which equals 1 if “room_type” = “Entire House” and 0 if “room_type” =
“shared room” or “private room”). To do so, sort the database by room_type and
insert a variable and name it “EntDum”.
4. The borough of Manhattan in New York is an important area for tourism, and it may
well command a higher price for Airbnb rentals than accommodations in the other
four boroughs (Brooklyn, Queens, Staten Island, and the Bronx). Create a new
Manhattan dummy variable (a dummy variable that can carry the value of 1 or 0)
that equals 1 if neighborhood_group = “Manhattan” and zero (0) otherwise.

Deliverable :
In the second section of your report (after Deliverable 1), prepare a descriptive
analysis titled “Descriptives” that includes a screenshot of descriptive statistics for
all the proposed dependent (outcome) and independent variables. Using the
Descriptive Statistics tool in Excel’s Data Analysis ToolPak ( Exhibit 12.5),
describe any unusual findings.

Exhibit .5 Descriptive Statistics in the Data Analysis ToolPak

Deliverable :
In the third section of your report (after Deliverable 2), predict how the
independent variables might relate to the price charged for a one-night rental. For
example, we would predict that the houses with more reviews are more popular
and thus likely to command a higher price, so there would be a positive
relationship between number of reviews and rental price. Make a prediction for
each independent variable you think might be important and include them in your
report as Deliverable 3.
 Page 850

Analyze the Data

Now that you have obtained the data, you can begin your analysis.

Step : Hypothesize Links Between Dependent/Outcome

Variable (Price) and the Independent Variables You Chose
What do you hypothesize are the links between the dependent (outcome) variable of
price and the independent variables that might predict it? Look through the list of
variables in the data dictionary. Which ones predict the price of Airbnb rentals, and are
they positively or negatively related to price?

Step : Select and Run Desired Analytics Techniques to

Evaluate Relationships
What types of analysis will help show a relationship between price and the various
independent variables? Consider the following:

Pivot tables: Create a pivot table of price (dependent variable) and each independent
variable you proposed (Excel: Insert > Pivot Table). After running the pivot analysis,
do you still believe there is a relationship between the dependent variable and each
independent variable you proposed?
Correlations: Using the Data Analysis ToolPak (Excel: Data > Analysis > Data
Analysis > Correlation), run a correlation between price (dependent variable) and a
few independent (now transformed) variables one by one. After running the
correlation analysis, do you still believe there is a relationship between the dependent
variable and each independent variable you proposed?
Regression analysis: Using the Data Analysis ToolPak (Excel: Data > Analysis > Data
Analysis > Regression), run a regression of price (dependent variable) on your
proposed independent variables. (For additional description of regression analysis,
see the statistics review in Chapter 3.)

SOARing to Success

Data Tip
 Recall that Excel requires you to group the independent variables together in
side-by-side columns before you run a regression.

Deliverable 4:
In the fourth section of your report after Deliverable 3, prepare an analysis titled
“Analysis” that includes the following:
1. Screenshots of at least two pivot table analyses and your interpretation (at
least 100 words).
2. Screenshots of the output of at least two correlations between the dependent
variable (price) and one independent variable. What is your interpretation of
the findings?
3. Screenshot of the results (output) of the regression, including the dependent
variable (price) and all of your proposed independent variables. What is your
interpretation of the findings? (Hint: In general, if the t-statistic is greater than
2 or the p-value is less than 0.05, then the independent variable is likely a
significant variable in predicting the price of a night’s rental on Airbnb.)

Was the direction of the relationship for each variable (positive vs. negative vs. no
relationship) different from what you anticipated in Deliverable 3?

Page 851

Report the Results

Based on your analysis, consider how to present your findings to Airbnb management.
Would you use graphs, tables, correlations, pivot tables, and/or regression results to
share the story? How would you most effectively communicate the findings to Airbnb
management?

Deliverable 5:
In the fifth section of your report after Deliverable 4, prepare an analysis titled
“Final Report” that includes the following:
1. An executive summary that is a maximum of one page. It should concisely
describe the variables that affect rental price per night and help management
 determine how to price Airbnb rentals. Be sure to provide the main support
for your recommendation.
2. A report summarizing your findings and any graphs, reports, correlations,
pivot tables, or regression results that help communicate your findings
effectively.
3. A brief discussion of how these findings might help predict the price of a
night’s rental on Airbnb in the future even before an accommodation is listed.