Data wrangling

# standard imports
%matplotlib inline
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import seaborn as sns; sns.set_style('darkgrid')
import re
import os

from collections import defaultdict
from pandas.api.types import CategoricalDtype
from functools import partial
from matplotlib.axes._axes import _log as matplotlib_axes_logger

# settings
pd.set_option('display.max_columns', None)
matplotlib_axes_logger.setLevel('ERROR')

# download and unzip datasets for each city
os.system('sh getdata.sh');

def load_dfs(file_prefixes, city_names, files_path):
    """Load all but .geojson data files for each city into nested dict of dataframes."""
    dfs = defaultdict(dict)

    for file_prefix in file_prefixes:
        for city_name in city_names:
            file_path = files_path + city_name + '_' + file_prefix + '.csv'
            dfs[file_prefix][city_name] = pd.read_csv(file_path)

    return dfs

file_prefixes = ['listings', 'calendar']
city_names = ['seattle', 'portland', 'vancouver']
files_path = 'data/'
dfs = load_dfs(file_prefixes, city_names, files_path)

def merge_dfs(dfs, file_prefixes, city_names):
    """Merge dataframes for each kind of data file."""
    merged_dfs = defaultdict(list)
    
    for file_prefix in file_prefixes:
        dfs_list = [dfs[file_prefix][city_name] for city_name in city_names]
        merged_dfs[file_prefix] = pd.concat(dfs_list, keys=city_names, names=['city'])
        try:
            merged_dfs[file_prefix].drop(columns=['city'], inplace=True)
        except:
            pass

    return merged_dfs
        
dfs = merge_dfs(dfs, file_prefixes, city_names)

listings_df = dfs['listings']
listings_df.head(3)

listings_df.info()

calendar_df = dfs['calendar']
calendar_df.head(3)

calendar_df.info()

# number of duplicate rows
num_dups = listings_df.duplicated().sum()
num_dups

def prop_missing_vals_df(df, axis=0, threshold=0.25, ascending=False):
    """Get missing values in df by proportion above threshold along axis."""
    prop = df.isna().sum(axis=axis).sort_values(ascending=ascending)
    prop = prop/df.shape[axis]
    prop.name = 'prop_miss_vals'
    return pd.DataFrame(prop[prop > threshold])

# rows with high proportion of missing values in listings df
prop_missing_vals_df(listings_df, axis=1, threshold=0.25)

# columns with high proportion of missing values in listings df
prop_missing_vals_df(listings_df, axis=0, threshold=0.25)

# start collecting columns to drop from listings df
drop_cols = set(prop_missing_vals_df(listings_df, axis=0).index)

# alphabetize columns besides those selected for dropping and inspect
alph_cols = list(set(listings_df.columns).difference(drop_cols))
alph_cols.sort()
listings_df[alph_cols].head(1)

# add remaining columns to drop and drop
more_drop_cols = {'interaction', 'neighborhood_overview', 'description', 'house_rules', 
                  'name', 'space', 'summary', 'calendar_updated', 'host_location',
                  'host_name', 'host_neighbourhood', 'host_picture_url', 
                  'host_thumbnail_url', 'host_url', 'host_verifications', 
                  'is_location_exact', 'jurisdiction_names', 'listing_url', 'market', 
                  'name', 'picture_url', 'scrape_id', 'maximum_minimum_nights', 
                  'minimum_maximum_nights', 'maximum_maximum_nights', 
                  'minimum_minimum_nights', 'minimum_nights_avg_ntm',
                  'maximum_nights_avg_ntm', 'last_scraped', 'calendar_last_scraped', 
                  'country', 'country_code', 'experiences_offered', 'has_availability', 
                  'host_has_profile_pic', 'smart_location', 'street', 'state', 
                  'host_listings_count', 'host_total_listings_count', 
                  'neighbourhood', 'zipcode', 'number_of_reviews_ltm', 'license'}
drop_cols = drop_cols.union(more_drop_cols)
listings_df = listings_df.drop(columns=drop_cols)

def alphabetize_cols(df, first_col='id'):
    """Alphabetize columns."""
    df_cp = df.copy()
    alph_cols = list(df_cp.columns.sort_values())
    alph_cols.remove(first_col)
    alph_cols = [first_col] + alph_cols
    df_cp = df_cp[alph_cols]
    return df_cp

listings_df = alphabetize_cols(listings_df)

listings_df.select_dtypes('int64').info()

listings_df.select_dtypes('float64').info()

def can_conv_to_int(list_float_vars):
    """Check which float values can be converted to int without rounding."""
    res = (np.abs(np.floor(list_float_vars) - list_float_vars) > 0).any()
    return ~ res

# dictionary for tracking col dtype conversions
conv_dtypes = defaultdict(set)
# see which float columns can be safely converted to int
conv_to_int = can_conv_to_int(listings_df.select_dtypes('float64').dropna())
conv_to_int_cols = set(conv_to_int[conv_to_int.T].index)
conv_dtypes['int'] = conv_to_int_cols
conv_dtypes['int']

listings_df.select_dtypes('object').info()

listings_df.select_dtypes('object').head(1)

# non-ordered categorical variables
conv_dtypes['categorical'] = {'host_identity_verified', 'host_is_superhost', 'instant_bookable', 
                              'is_business_travel_ready', 'neighbourhood_cleansed', 'property_type', 
                              'require_guest_phone_verification', 'require_guest_profile_picture', 
                              'requires_license'}
conv_dtypes['datetime'] = {'first_review', 'host_since', 'last_review'}

listings_df.loc[:, conv_dtypes['categorical']] = listings_df[conv_dtypes[
                                                 'categorical']].astype('category')
listings_df.loc[:, conv_dtypes['datetime']] = listings_df[conv_dtypes['datetime']]\
                                           .astype('datetime64')

def set_ord_cat_dtypes(listings_df, conv_dtypes, ord_cat_cols):
    """Set ordered categorical columns dtypes."""
    listings_df_cp = listings_df.copy()
    for (col, ordering) in conv_dtypes['ordered_categorical'].items():
        dtype = CategoricalDtype(categories=ordering, ordered=True)
        listings_df_cp[col] = listings_df_cp[col].astype(dtype)
    return listings_df_cp

# ordered categorical variables
ord_cat_cols = ['bed_type', 'cancellation_policy', 'host_response_time', 'room_type']
conv_dtypes['ordered_categorical'] = {col:None for col in ord_cat_cols}
conv_dtypes['ordered_categorical']['bed_type'] = ['Couch', 'Airbed', 'Pull-out Sofa', 
                                                  'Futon', 'Real Bed']
conv_dtypes['ordered_categorical']['cancellation_policy'] = ['super_strict_60', 
                                                             'super_strict_30', 
                                                             'strict', 
                                                             'strict_14_with_grace_period', 
                                                             'moderate', 'flexible']
conv_dtypes['ordered_categorical']['host_response_time'] = ['within an hour', 
                                                            'within a few hours', 
                                                            'within a day', 
                                                            'a few days of more']
conv_dtypes['ordered_categorical']['room_type'] = ['Shared room', 'Hotel room', 
                                                   'Private room', 'Entire home/apt']
listings_df = set_ord_cat_dtypes(listings_df, conv_dtypes, ord_cat_cols)

def conv_to_float(entry):
    """Float conversion helper."""
    try:
        return entry.replace('$', '').replace('%', '').replace(',', '')
    except AttributeError:
        return entry
    
def conv_cols(listings_df, conv_dtypes, conv_func, dtype_name):
    """Process/convert columns."""
    listings_df_cp = listings_df.copy()

    for col in conv_dtypes[dtype_name]:
        listings_df_cp[col] = listings_df[col].apply(conv_func)\
                              .astype(dtype_name)

    return listings_df_cp

# some float variables
conv_dtypes['float'] = {'cleaning_fee', 'extra_people', 'price', 
                        'security_deposit', 'host_response_rate', 
                        'host_acceptance_rate'}
listings_df = conv_cols(listings_df, conv_dtypes, conv_to_float, 'float')

# columns in listings dataset missing values
miss_vals_df = prop_missing_vals_df(listings_df, axis=0, threshold=0)
miss_vals_df = pd.DataFrame({'col': miss_vals_df.index, 
                            'prop_miss': miss_vals_df['prop_miss_vals'].values, 
                             'dtype': listings_df[miss_vals_df.index].dtypes})
miss_vals_df

# review related variables missing values
miss_val_rev_cols = [col for col in miss_vals_df.index if 'review' in col]
miss_vals_df.loc[miss_val_rev_cols, :]

# prop of listings missing all review variables
miss_val_rev_df = listings_df[listings_df[miss_val_rev_cols].isna().T.all()]\
                            [miss_val_rev_cols]
len(miss_val_rev_df)/len(listings_df)

# summary statistics for listings missing review variable values
listings_df.loc[miss_val_rev_df.index].describe()

# summary statistics for full listings dataset
listings_df.describe()

# drop rows missing any review variables
mask = listings_df[miss_val_rev_cols].isna().sum(axis=1) == 0
listings_df = listings_df[mask]

# drop two more review columns
listings_df = listings_df.drop(columns=['first_review', 'last_review'])

# quantitative variables with small proportion of missing values
fig, axs = plt.subplots(nrows=1, ncols=3, figsize=(12, 3))
for (i, col) in enumerate(['bathrooms', 'beds', 'bedrooms']):
    sns.distplot(listings_df[col].dropna(), ax=axs[i], kde=False)
fig.tight_layout()

# categorical variables with small proportion of missing values
fig, axs = plt.subplots(nrows=1, ncols=2, figsize=(12, 3))
for (i, col) in enumerate(['host_is_superhost', 'host_identity_verified']):
    sns.countplot(listings_df[col].dropna(), ax=axs[i % 2])

# dictionary for imputation methods for columns with missing values
impute_vals = defaultdict(None)
# imputation values for some columns
for col in ['bathrooms', 'beds', 'bedrooms', 'host_since',
            'host_is_superhost', 'host_identity_verified']:
    impute_vals[col] = listings_df[col].mode().values[0]

fig, axs = plt.subplots(nrows=1, ncols=3, figsize=(12, 5))
for (i, col) in enumerate(['host_acceptance_rate', 'cleaning_fee', 'security_deposit']):
    sns.distplot(listings_df[col].dropna(), kde=False, norm_hist=True, ax=axs[i])

# imputation values for more columns
impute_vals['security_deposit'] = 0
impute_vals['cleaning_fee'] = listings_df['cleaning_fee'].dropna().median()
impute_vals['host_acceptance_rate'] = listings_df['host_acceptance_rate'].dropna().median()

fig, axs = plt.subplots(nrows=1, ncols=2, figsize=(12, 5))
sns.distplot(listings_df['host_response_rate'].dropna(), kde=False, norm_hist=True, ax=axs[0])
sns.countplot(listings_df['host_response_time'].dropna(), ax=axs[1])

# last imputation values
for col in ['host_response_rate', 'host_response_time']:
    impute_vals[col] = listings_df[col].mode().values[0]

# impute all missing values
listings_df = listings_df.fillna(impute_vals)

# downcast to int
listings_df.loc[:, conv_dtypes['int']] = listings_df[conv_dtypes['int']].astype('int') 

def conv_to_bool(entry):
    """Convert string entries to booleans."""
    return entry == 't'

conv_dtypes['bool'] = {'host_identity_verified', 'host_is_superhost', 
                       'instant_bookable', 'is_business_travel_ready', 
                       'require_guest_phone_verification',
                       'require_guest_profile_picture', 'requires_license'}

# downcast to boolean
listings_df = conv_cols(listings_df, conv_dtypes, conv_to_bool, 'bool') 

num_dups = calendar_df.duplicated().sum()
num_dups

# rows with high proportion of missing values in listings df
prop_missing_vals_df(calendar_df, axis=1)

# columns with high proportion of missing values in listings df
prop_missing_vals_df(calendar_df, axis=0)

# rows missing values
miss_vals_df = calendar_df.loc[calendar_df.isna().sum(axis=1) > 0]
miss_vals_df.info()

# listing ids of calendar entries with missing values
miss_cal_idx = miss_vals_df.reset_index(level=0)\
               .groupby(['city', 'listing_id'])['listing_id'].count()
miss_cal_idx

# drop listing ids with large number of missing entries
listing_ids = {index[1] for index in miss_cal_idx[miss_cal_idx > 8].index}
list_ids_mask = ~ listings_df['id'].apply(lambda x: x in listing_ids)
listings_df = listings_df[list_ids_mask]
cal_ids_mask = ~ calendar_df['listing_id'].apply(lambda x: x in listing_ids)
calendar_df = calendar_df[cal_ids_mask]

calendar_df = calendar_df.drop(columns=['adjusted_price'])

calendar_df.dtypes

# set dtypes
calendar_df.loc[:, 'date'] = calendar_df['date'].astype('datetime64')
conv_dtypes = defaultdict(set)
conv_dtypes['bool'] = {'available'}
conv_dtypes['float'] = {'price'}
calendar_df = conv_cols(calendar_df, conv_dtypes, conv_to_bool, 'bool')
calendar_df = conv_cols(calendar_df, conv_dtypes, conv_to_float, 'float')

miss_vals_df = calendar_df.loc[calendar_df.isna().sum(axis=1) > 0]
miss_vals_df

# unique values of maximum nights
calendar_df[calendar_df['listing_id'] == 6132497]['maximum_nights'].unique()

# unique values of maximum nights
calendar_df[calendar_df['listing_id'] == 6132497]['minimum_nights'].unique()

# fill missing values for listing 6132497
miss_cal_idx = miss_vals_df[miss_vals_df['listing_id'] == 6132497].index
calendar_df.loc[miss_cal_idx, 'minimum_nights'] = 1.0
calendar_df.loc[miss_cal_idx, 'maximum_nights'] = 28.0

def backfill_missing_prices(calendar_df, miss_price_vals_df):
    """Backfill missing price data for two listings in calendar dataset."""
    week_delta = pd.Timedelta(1, unit='w')
    calendar_df_cp = calendar_df.copy()
    for index in miss_price_vals_df.index:
        listing_id = miss_price_vals_df.loc[index]['listing_id']
        one_week_ago = miss_price_vals_df.loc[index]['date'] - week_delta
        mask = ((calendar_df_cp['listing_id'] == listing_id) & 
               (calendar_df_cp['date'] == one_week_ago))
        price = calendar_df_cp[mask]['price'].values[0]
        calendar_df_cp.loc[index, 'price'] = price
    return calendar_df_cp

miss_price_vals_df = miss_vals_df[miss_vals_df['listing_id'] == 14237741]
calendar_df = backfill_missing_prices(calendar_df, miss_price_vals_df)

# check backfill worked
len(calendar_df[(cal_id_mask) &  calendar_df['price'].isna()])

maxmin_date = '2020-02-22'
minmax_date = '2021-02-13'
cal_date_mask = ((calendar_df['date'] >= maxmin_date) & (calendar_df['date'] <= minmax_date))
calendar_df = calendar_df[cal_date_mask]

amenities_series = listings_df['amenities']
amenities_series

def process_amenities(amenities_series):
    """Process entries in amenities series."""
    # convert amenities lists into sets of strings
    amenities_series = amenities_series.apply(lambda x: set(x.split(',')))
    # set for tracking unique amenities
    amenities_set = set()
    # for dropping all non alphanumeric characters
    regex = re.compile('[^0-9a-zA-Z ]+')
    # function for processing each amenity lists in series entries
    def process_and_add(amenities_set, regex, amens_entry):
        new_amens_entry = set()
        for amen in amens_entry:
            # drop non alpha numeric
            amen = regex.sub('', amen)
            # don't keep track of translation failures
            if 'translation' in amen:
                pass
            else:
                new_amens_entry.add(amen)
                # add to main set if it hasn't been seen
                if amen not in amenities_set:
                    amenities_set.add(amen)
        return new_amens_entry
    # process amenity list entries in series
    apply_func = partial(process_and_add, amenities_set, regex)
    amenities_series = amenities_series.apply(apply_func)

    return amenities_series, amenities_set


def rename_amenities(amenities_series, amenities_set, amenities_mapping):
    """Clean up amenities names."""
    amenities_set = {amen if amen not in amenities_mapping
                     else amenities_mapping[amen] for amen in amenities_set}
    amenities_set = {'amen_' + amen.replace(' ', '_').lower() for amen
                     in amenities_set}

    # function for renaming amenity lists in series entries
    def rename_amens(amenities_mapping, amens_entry):
        new_amens_entry = set()
        for amen in amens_entry:
            try:
                amen = amenities_mapping[amen]
            except KeyError:
                pass
            amen = 'amen_' + amen.replace(' ', '_').lower()
            new_amens_entry.add(amen)
        return new_amens_entry

    # process amenity list entries in series
    apply_func = partial(rename_amens, amenities_mapping)
    amenities_series = amenities_series.apply(apply_func)

    return amenities_series, amenities_set

# get renamed series and set of amenities
amenities_series, amenities_set = process_amenities(
                                      listings_df['amenities'])
amenities_mapping = {' toilet': 'Toilet',
                     '24hour checkin': '24 hour checkin',
                     'Accessibleheight bed': 'Accessible height bed',
                     'Accessibleheight toilet':
                     'Accessible height toilet',
                     'Buzzerwireless intercom': 'Buzzer/Wireless intercom',
                     'Familykid friendly': 'Family/kid friendly',
                     'Highresolution computer monitor':
                     'High resolution computer monitor',
                     'Pack n Playtravel crib': 'Pack-n-Play travel crib',
                     'Roomdarkening shades': 'Room darkening shades',
                     'Self checkin': 'Self check-in',
                     'SkiinSkiout': 'Ski-in/Ski-out',
                     'Stepfree shower': 'Step-free shower',
                     'Washer  Dryer': 'Washer/Dryer',
                     'Welllit path to entrance':
                     'Well-lit path to entrance'}
amenities_series, amenities_set = rename_amenities(amenities_series,
                                                   amenities_set,
                                                   amenities_mapping)

amenities_series

# count number of unique amenities
len(amenities_set)

def count_amenities(amenities_series, amenities_set):
    """Count total occurences of each amenity in dataset."""
    amenities_count = {amen: 0 for amen in amenities_set}

    for amens_entry in amenities_series:
        for amen in amens_entry:
            amenities_count[amen] += 1

    return amenities_count

def get_amenities_cols(amenities_series, amenities_count,
                       prop_low=0.1, prop_hi=0.9):
    """Return amenities with proportion in between thresholds."""
    # dataframe of amenities counts
    n = len(amenities_series)
    amenities_prop_df = pd.DataFrame(amenities_count, index=['prop']).T
    amenities_prop_df = amenities_prop_df.sort_values(by='prop')/n
    amenities_prop_df = amenities_prop_df.query('prop >= ' + str(prop_low))
    amenities_prop_df = amenities_prop_df.query('prop <= ' + str(prop_hi))

    return set(amenities_prop_df.index)

# get amenities present in > 10% and less than 90% of listings
amenities_count = count_amenities(amenities_series, amenities_set)
amenities_cols = get_amenities_cols(amenities_series, amenities_count,
                                    prop_low=0.1, prop_hi=0.9)
# check number of these
len(amenities_cols)

def get_amenities_df(amenities_series, amenities_cols):
    """Create dataframe of amenities variables."""
    amenities_df = pd.DataFrame(columns=amenities_cols,
                                index=amenities_series.index).astype('bool')

    def has_amenity(amenity, amens_entry):
        return amenity in amens_entry

    for amenity in amenities_cols:
        applyfunc = partial(has_amenity, amenity)
        amenities_df.loc[:, amenity] = amenities_series.apply(applyfunc)

    def num_amenities(amenities_cols, amen_entry):
        return len(set(amen_entry).intersection(amenities_cols))

    applyfunc = partial(num_amenities, amenities_cols)
    amenities_df['num_amenities'] = amenities_series.apply(applyfunc)

    return amenities_df

# dataframe with amenities as boolean columns
amenities_df = get_amenities_df(amenities_series, amenities_cols)
amenities_df

# add amenities to listings df
listings_df = pd.concat([listings_df, amenities_df], axis=1)\
              .drop(columns=['amenities'])

def conv_cad_to_usd(entry):
    """Currency conversion helper."""
    return round(0.76124 * entry, 0)

def conv_curr_cols(df, curr_cols, curr_conv_func):
    """Convert currency columns."""
    df_cp = df.copy()
    df_cp.loc[:, curr_cols] = \
        df_cp[curr_cols].apply(lambda x: conv_cad_to_usd(x) 
                               if 'vancouver' in x.name else x, axis=1)
    return df_cp

# convert currency columns in listings df
list_curr_cols = ['cleaning_fee', 'price', 'security_deposit']
listings_df = conv_curr_cols(listings_df, list_curr_cols, conv_cad_to_usd)

# workaround for converting calendar columns due to slowness of conv_curr_cols
cal_van_price = calendar_df.loc['vancouver']['price'].apply(conv_cad_to_usd).values
cal_other_price = calendar_df.loc[['seattle', 'portland']]['price'].values
calendar_df.loc[:, 'price'] = np.append(cal_other_price, cal_van_price)

# save dfs as h5 files to save write time
listings_df = alphabetize_cols(listings_df)
listings_df.to_hdf('data/listings.h5', key='listings', mode='w', format='table')
calendar_df = alphabetize_cols(calendar_df, first_col='listing_id')
calendar_df.to_hdf('data/calendar.h5', key='calendar', mode='w', format='table')

# delete unwanted dataset
datadir = './data'
for item in os.listdir(datadir):
    if item.endswith('csv'):
        os.remove(os.path.join(datadir, item))