Data Exploration

import numpy as np
import pandas as pd

Setup

For the scope of this tutorial we are going to use AirBnb Scraped data for the city of Bologna. The data is freely available at Inside AirBnb: http://insideairbnb.com/get-the-data.html.

A description of all variables in all datasets is avaliable here.

We are going to use 2 datasets:

• listing dataset: contains listing-level information
• pricing dataset: contains pricing data, over time

Importing Data

Pandas has a variety of function to import data

• pd.read_csv()
• pd.read_html()
• pd.read_parquet()

Importatly for our purpose, pd.read_csv() can directly import data from the web.

The first dataset that we are going to import is the dataset of Airbnb listings in Bologna. It contains listing-level information.

url_listings = "http://data.insideairbnb.com/italy/emilia-romagna/bologna/2021-12-17/visualisations/listings.csv"
df_listings = pd.read_csv(url_listings)

The second dataset that we are going to use is the dataset of calendar prices. This time the dataset is compressed but we can use the compression option to import it directly.

url_prices = "http://data.insideairbnb.com/italy/emilia-romagna/bologna/2021-12-17/data/calendar.csv.gz"
df_prices = pd.read_csv(url_prices, compression="gzip")

Inspecting Data

Methods

• info()
• head()
• describe()

The first way yo have a quick look at the data is the info() method. If called with the option verbose=False, it gives a quick overview of the dimensions of the data.

df_listings.info(verbose=False)

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3453 entries, 0 to 3452
Columns: 18 entries, id to license
dtypes: float64(4), int64(8), object(6)
memory usage: 485.7+ KB

If we want to know how the data looks like, we can use the head() method. It prints the first 5 lines of the data by default.

df_listings.head()

We can print a description of the data using describe(). If we have many variables, it’s best to print it transposed using the .T attribute.

df_listings.describe().T[:5]

You can select which variables to display using the include option. include='all' includes also categorical variables.

df_listings.describe(include='all').T[:5]

We can get the list of columns using the .columns attribute.

df_listings.columns

Index(['id', 'name', 'host_id', 'host_name', 'neighbourhood_group',
       'neighbourhood', 'latitude', 'longitude', 'room_type', 'price',
       'minimum_nights', 'number_of_reviews', 'last_review',
       'reviews_per_month', 'calculated_host_listings_count',
       'availability_365', 'number_of_reviews_ltm', 'license'],
      dtype='object')

We can get the index using the .index attribute,

df_listings.index

RangeIndex(start=0, stop=3453, step=1)

Data Selection

We can access single columns as if the DataFrame was a dictionary.

df_listings['price']

0        68
1        29
2        50
3       126
4        50
       ... 
3448     32
3449     45
3450     50
3451    134
3452    115
Name: price, Length: 3453, dtype: int64

We can select rows and columns by index, using the .iloc attribute.

df_listings.iloc[:7, 5:9]

If we want to condition only on rows or columns, we have use : for the unrestricted dimesion, otherwise we get an error.

df_listings.iloc[:, 5:9].head()

Instead, the .loc attribute allows us to use row and column names.

df_listings.loc[:, ['neighbourhood', 'latitude', 'longitude']].head()

We can also select ranges.

df_listings.loc[:, 'neighbourhood':'room_type'].head()

There is an easy way to select numerical columns, the .select_dtypes() function.

df_listings.select_dtypes(include=['number']).head()

Other types include

• object for strings
• bool for booleans
• int for integers
• float for floats (numbers that are not integers)

We can also use logical operators to selet rows.

df_listings.loc[df_listings['number_of_reviews']>500, :].head()

We can use logical operations as well. But remember to use paranthesis.

Note: the and and or expressions do not work in this setting. We have to use & and | instead.

df_listings.loc[(df_listings['number_of_reviews']>300) &
                (df_listings['reviews_per_month']>7), 
                :].head()

For a single column (i.e. a Series), we can get the unique values using the unique() function.

df_listings['neighbourhood'].unique()

array(['Santo Stefano', 'Porto - Saragozza', 'Navile',
       'San Donato - San Vitale', 'Savena', 'Borgo Panigale - Reno'],
      dtype=object)

For multiple columns, we can use the drop_duplicates function.

df_listings[['neighbourhood', 'room_type']].drop_duplicates()

Aggregation and Pivot Tables

We can compute statistics by group using .groupby().

df_listings.groupby('neighbourhood')[['price', 'reviews_per_month']].mean()

If you want to perform more than one function, maybe on different columns, you can use .aggregate() which can be shortened to .agg(). It takes as argument a dictionary with variables as keys and lists of functions as values.

df_listings.groupby('neighbourhood').agg({"reviews_per_month": ["mean"],
                                          "price": ["min", np.max]}).reset_index()

The problem with this syntax is that it generates a hierarchical structure for variable names, which might not be so easy to work with. In the example above, to access the mean price, you have to use df.price["min"].

To perform variable naming and aggregation and the same time, you can ise the following syntax: agg(output_var = ("input_var", function)).

df_listings.groupby('neighbourhood').agg(mean_reviews=("reviews_per_month", "mean"),
                                         min_price=("price", "min"),
                                         max_price=("price", np.max)).reset_index()

We can make pivot tables with the .pivot_table() function. It takes the folling arguments:

• index: rows
• columns: columns
• values: values
• aggfunc: aggregation function

df_listings.pivot_table(index='neighbourhood', columns='room_type', values='price', aggfunc='mean')