Data Wrangling

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 numpy as np
import pandas as pd

# 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")

Sorting and Renaming

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

If we want to count observations across 2 categorical variables, we can use pd.crosstab().

pd.crosstab(df_listings['neighbourhood'], df_listings['room_type'])

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').head()

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 (in this case, it makes no difference).

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])
)

(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

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

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

Reshaping

First, let’s compute average prices by neighbourhood and date using the merged dataset.

df_long = df_merged.groupby(['neighbourhood', 'date'])['price_x'].agg('mean').reset_index()
df_long.head()

This is what is called long format since it has one or more variables (price_x in this case) stacked vertically along a categorical variable (neighborhood and date here), which acts as index.

The alternative is the wide format where we have one separate column for each neighborhood.

We can reshape the dataset from long to wide using the pd.pivot() command. d

df_wide = pd.pivot(data=df_long, index='date', columns='neighbourhood').reset_index()
df_wide.head()

We can reshape the dataset from wide to long using the pd.melt() command. It takes the following arguments

• data: the dataframe
• id_vars: the variable that was indexing the old dataset

pd.melt(df_wide, id_vars='date', value_name='price').head()

If we do not have MultiIndex columns, but just a common prefix, we can reshape the dataset from wide to long using the pd.wide_to_long() command.

df_wide2 = df_wide.copy()
df_wide2.columns = [''.join(col) for col in df_wide2.columns]
df_wide2.head()

The pd.wide_to_long() command takes the following arguments

• data: the dataframe
• stubnames: the prefixes of the variables that we want to reshape into one
• i: the variable that was indexing the old dataset
• j: the name of the new categorical variable that we extract from stubnames
• suffix: regular expression of the suffix, the default is \d+, i.e. digits

pd.wide_to_long(df_wide2, stubnames='price_x', i='date', j='neighborhood', suffix='\D+').head()

Note that we had to change the suffix to \D+, i.e. not digits.

Window Functions

Methods

• shift()
• expanding()
• rolling()

When we have time series data, we might want to do operations across time. First, let’s aggregate the df_price dataset at the year-month level.

temp = df_prices.copy()
temp['price'] = temp['price'].str.replace('[$|,]', '', regex=True).astype(float)
temp['date'] = pd.to_datetime(temp['date']).dt.to_period('M')
temp = temp.groupby(['listing_id', 'date'])['price'].mean().reset_index()\
    .sort_values(by=['listing_id', 'date'], ascending=[False, True])
temp.head()

We can lead or lag one variable using shift().

temp['price1'] = temp['price'].shift(1)
temp.head(15)

If we want to lead or lag a variable within a group, we can combine shift() with groupby()

temp['price1'] = temp.groupby('listing_id')['price'].shift(1)
temp.head(15)

We can perform cumulative operations using the expanding() function

temp['avg_cum_price'] = temp['price'].expanding().mean()
temp.head(15)

To perform cumulative operations within a group, we can combine expanding() with groupby(). Since groups with not enough observations get dropped, we need to merge the dataset back.

temp.groupby('listing_id')['price'].expanding().mean().reset_index(level=0).head(15)

If we want to perform an operation over a rolling window, we can use the rolling() function

temp['avg3_price'] = temp['price'].rolling(3).mean()
temp.head(15)