Data Wrangling

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

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

# Import pricing data
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")

Editing the Dataframe

You can sort the data using the sort_values function.

Options

• ascending: bool or list of bool, default True
• na_position: {‘first’, ‘last’}, default ‘last’

df_listings.sort_values(by=['name', 'price'], 
                        ascending=[False, True], 
                        na_position='last').head()

You can remane columns using the rename() function. It takes a dictionary as column argument in the form {"old_name": "new_name"}.

df_listings.rename(columns={'name': 'listing_name', 
                            'id': 'listing_id'}).head()

Aggregating

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(). The sintax is agg(output_var = ("input_var", function)) and it accepts also numpy functions.

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

If we want to build a new column by group, we can use transform() on the grouped data. Unfortunately, it does not work as nicely as aggregate() and we have to do one column at the time.

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

Reshaping

Combining Datasets

We can concatenate datasets using pd.concat(). It takes as argument a list of dataframes. By default, pd.concat() performs the outer join. We can change it using the join option.

df_listings1 = df_listings[:2000]
np.shape(df_listings1)

(2000, 18)

df_listings2 = df_listings[1000:]
np.shape(df_listings2)

(2453, 18)

np.shape(
    pd.concat([df_listings1, df_listings2], join='inner')
)

(4453, 18)

To instead merge dataframes, we can use the pd.merge function.

Options

• how: {‘left’, ‘right’, ‘outer’, ‘inner’, ‘cross’}, default ‘inner’
• on: label or list

pd.merge(df_listings, df_prices, left_on='id', right_on='listing_id', how='inner')

As you can see, since the variable price was present in both datasets, we now have a price.x and a price.y.