<jupyter_start><jupyter_text>Exploring the AirBnB listingsThe aim is to get an understanding of how each feature is distributed - taking note of the following:- Missing values- Categorical or continuous?- Skewed distributions Steps1. Download data2. Decide what to keep and what to drop  Downloading the data 1. Download from [download url](http://data.insideairbnb.com/denmark/hovedstaden/copenhagen/2020-05-30/data/listings.csv.gz)2. Unzip and place into the `data/raw` folder3. Rename it to `listings_2020_05_30.csv`  Drop columnsWe have 100+ features - many of which are obviously not relevant to our task, such as the host id. There are many features that might be included in a much more advanced model or for a different usecase. For our usecase, we can only include data that one of our potential customers would have - so including factors such as reviews or host picture is probably not useful at this point. Any text descriptions or host-provided prose is not included for now, though could potentially be used in another model to serve as input for this one.We can always come back and add more features as our model evolves - so it's important to write down what we know about each feature and update it when we know more1. Decide what columns to keep2. Document what each feature is and first impressions<jupyter_code>raw_data = pd.read_csv("../data/raw/listings_2020_05_30.csv")
raw_data.sample(10)<jupyter_output><empty_output><jupyter_text>I went through each column, writing down what I could see from a cursory glance [here](../docs/features/features.rst)The final list of features to include for now is:<jupyter_code>usecols = [
    "house_rules",
    "host_since",
    "host_location",
    "host_response_time",
    "host_neighbourhood",
    "host_listings_count",
    "host_total_listings_count",
    "host_verifications",
    "host_has_profile_pic",
    "host_identity_verified",
    "neighbourhood",
    "zipcode",
    "latitude",
    "longitude",
    "is_location_exact",
    "property_type",
    "room_type",
    "accommodates",
    "bathrooms",
    "bedrooms",
    "beds",
    "bed_type",
    "amenities",
    "square_feet",
    "price",
    "security_deposit",
    "cleaning_fee",
    "guests_included",
    "extra_people",
    "minimum_nights",
    "maximum_nights",
    "instant_bookable",
    "cancellation_policy",
    "require_guest_profile_picture",
    "require_guest_phone_verification",
]<jupyter_output><empty_output><jupyter_text>Starting work on dtypesIt's good practice to map the correct dtypes from the start, this will save processing time over the data, and can prevent memory explosions.Note that we are not setting their final form - some need to be loaded as strings, for example, so that we can handle missing values properly and then convert to their final form. This is documented [here](../docs/features/keep_features.rst)<jupyter_code>raw_df = pd.read_csv("../data/raw/listings_2020_05_30.csv", usecols=usecols)
raw_df.head()
mapping = {
    "house_rules": "string",
    "host_since": "string",
    "host_location": "string",
    "host_response_time": "category",
    "host_acceptance_rate": "string",
    "host_neighbourhood": "category",
    "host_listings_count": "Int64",
    "host_total_listings_count": "Int64",
    "host_has_profile_pic": "string",
    "host_identity_verified": "string",
    "neighbourhood": "category",
    "zipcode": "string",
    "latitude": "float64",
    "longitude": "float64",
    "property_type": "category",
    "accommodates": "Int8",
    "bathrooms": "float32",
    "bedrooms": "Int8",
    "beds": "Int8",
    "bed_type": "category",
    "amenities": "string",
    "square_feet": "Int64",
    "price": "string",    
    "security_deposit": "string",
    "cleaning_fee": "string",
    "guests_included": "Int8",
    "extra_people": "string",
    "minimum_nights": "Int64",
    "maximum_nights": "Int64",
    "instant_bookable": "string",
    "cancellation_policy": "category",
    "require_guest_profile_picture": "string",
    "require_guest_phone_verification": "string"
}
raw_df = pd.read_csv("../data/raw/listings_2020_05_30.csv", usecols=mapping.keys(), dtype=mapping)
processed_df = raw_df.copy()
raw_df.head().iloc[:, 15:]<jupyter_output><empty_output><jupyter_text>Preprocessing columnsNow the data is loaded optimally, we can start looking at necessary data cleanup. At this stage, we are not doing feature engineering, merely fixing flaws and converting to final form of data types.<jupyter_code>def convert_price(series):
    return pd.to_numeric(series.str.replace("$", "").str.replace(",", ""), errors="coerce").astype("float64")

def convert_percent(series):
    return pd.to_numeric(series.str.replace("%", ""), errors="coerce").astype("Int8")

def convert_bool(series):
    return series.map({"t": True, "f": False}).astype("bool")<jupyter_output><empty_output><jupyter_text>host_acceptance_rateWe need to remove the `%` sign from each column and convert to an integer<jupyter_code>processed_df["host_acceptance_rate"] = convert_percent(processed_df["host_acceptance_rate"])<jupyter_output><empty_output><jupyter_text>host_has_profile_picWe need to convert `f` to False and `t` to True<jupyter_code>processed_df["host_has_profile_pic"] = convert_bool(processed_df.host_has_profile_pic)<jupyter_output><empty_output><jupyter_text>host_identity_verifiedWe need to convert `f` to False and `t` to True<jupyter_code>processed_df["host_identity_verified"] = convert_bool(processed_df["host_identity_verified"])<jupyter_output><empty_output><jupyter_text>priceRemove the `,` and `$` sign and convert to correct dtype<jupyter_code>processed_df["price"] = convert_price(processed_df["price"])<jupyter_output><empty_output><jupyter_text>security_depositRemove the `,` and `$` sign and convert to correct dtype<jupyter_code>processed_df["security_deposit"] = convert_price(processed_df["security_deposit"])<jupyter_output><empty_output><jupyter_text>cleaning_feeRemove the `,` and `$` sign and convert to correct dtype<jupyter_code>processed_df["cleaning_fee"] = convert_price(processed_df["cleaning_fee"])<jupyter_output><empty_output><jupyter_text>extra_peopleRemove the `,` and `$` sign and convert to correct dtype<jupyter_code>processed_df["extra_people"] = convert_price(processed_df["extra_people"])<jupyter_output><empty_output><jupyter_text>instant_bookable<jupyter_code>processed_df["instant_bookable"] = convert_bool(processed_df["instant_bookable"])<jupyter_output><empty_output><jupyter_text>require_guest_profile_picture<jupyter_code>processed_df["require_guest_profile_picture"] = convert_bool(processed_df["require_guest_profile_picture"])<jupyter_output><empty_output><jupyter_text>require_guest_phone_verification<jupyter_code>processed_df["require_guest_phone_verification"] = convert_bool(processed_df["require_guest_phone_verification"])<jupyter_output><empty_output><jupyter_text>Extracting FeaturesNow that the main dataset is ready, it's time to extract the necessary features from the data  AmenitiesWe need to extract all possible categories and create a column for each, which we can then one-hot encode.So first, we must extract all possible categories<jupyter_code>def one_hot_encode_amenities(df: pd.DataFrame):
    return (
        df.amenities
            .str.slice(start=1, stop=-1)  # Remove the "{}" at the front and back
            .str.replace('"', '')  # Remove quoting of multi-word amenities
            .str.split(",")
            .apply(lambda x: pd.Series(index=set(x), data=1))  # For each row, make a Series of
            # 1s. `apply` will concatenate
            # the result
            .fillna(0)
            .astype("bool")
            .rename(columns=lambda x: "_".join(x.split(" ")).lower())  # Rename all columns to
        # lowercase snake_case
    )
def convert_amenities(df: pd.DataFrame):
    one_hot_df = one_hot_encode_amenities(df)
    return df.join(one_hot_df).drop(columns="amenities")
processed_df = convert_amenities(processed_df)<jupyter_output><empty_output><jupyter_text>House RulesA hypothesis is that more rules impact the price. We can start by counting the lenght of the description to represent having more rules<jupyter_code>processed_df["house_rules_len"] = processed_df.house_rules.str.len()<jupyter_output><empty_output><jupyter_text>ZipcodeThere are some missing zipcodes - we can look them up using DAWA and the reverse geocoding API. Let's mark all zipcodes longer than 4 characters as missing as well - that way we can get it from DAWA instead of trying to manipulate the strings.<jupyter_code>from concurrent.futures import ThreadPoolExecutor, wait
import requests
from requests.sessions import Session
url = "https://dawa.aws.dk/postnumre/reverse"
def get_zipcode(series: dict, client: Session):
    resp = client.get(url, params={"x": series["longitude"], "y": series["latitude"]})
    if resp.status_code == 200:
        return resp.json()["nr"]
    resp.raise_for_status()
lats_longs = processed_df.loc[processed_df.zipcode.isna(), ["latitude", "longitude"]].to_dict(orient="records")
session = Session()
with ThreadPoolExecutor(max_workers=10) as executor:
    futures = executor.map(get_zipcode, lats_longs, session)
    results = [future.result() for future in futures]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11242730992892526, 'fraction_numerical': 0.009476631488261899, 'mean_word_length': 5.5865248226950355, 'pattern_counts': {'":': 37, '<': 69, '<?xml version=': 0, '>': 69, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '176547', 'license': 'MIT', 'n_tokens_mistral': 3031, 'n_tokens_neox': 2911, 'n_words': 745, 'path': 'notebooks/ETL EDA.ipynb', 'repo_name': 'andersbogsnes/airbnb_priceforecaster', 'size': '9286'}