<jupyter_start><jupyter_text>Airbnb - Rio de Janeiro* Download [data](http://insideairbnb.com/get-the-data.html)* We downloaded `listings.csv` from all monthly dates available Questions1. What was the price and supply behavior before and during the pandemic?2. Does a title in English or Portuguese impact the price?3. What features correlate with the price? Can we predict a price? Which features matters?<jupyter_code>import numpy as np
import pandas as pd
import seaborn as sns
import glob
import re
import pendulum
import tqdm

import matplotlib.pyplot as plt

import langid
langid.set_languages(['en','pt'])<jupyter_output><empty_output><jupyter_text>Read filesRead all 30 files and get their date<jupyter_code>files = sorted(glob.glob('data/listings*.csv'))

df = []
for f in files:
    date = pendulum.from_format(re.findall(r"\d{4}_\d{2}_\d{2}", f)[0], fmt="YYYY_MM_DD").naive()
    csv = pd.read_csv(f)
    csv["date"] = date
    df.append(csv)
df = pd.concat(df)
df<jupyter_output><empty_output><jupyter_text>Deal with NaNs* Drop `neighbourhood_group` as it is all NaNs;* Fill `reviews_per_month` with zeros (if there is no review, then reviews per month are zero)* Keep `name` for now* Drop `host_name` rows, as there is not any null host_id* Keep `last_review` too, as there are rooms with no review<jupyter_code>df.isna().any()
df = df.drop(["host_name", "neighbourhood_group"], axis=1)
df["reviews_per_month"] = df["reviews_per_month"].fillna(0.)
df.head()<jupyter_output><empty_output><jupyter_text>Detect `name` language* Clean strings for evaluation* Remove common neighbourhoods name in Portuguese from `name` column to diminish misprediction* Remove several non-alphanumeric characters* Detect language using [langid](https://github.com/saffsd/langid.py)* I restricted between pt, en. There are very few rooms listed in other languages.* Drop `name` column<jupyter_code>import unicodedata
stopwords = pd.unique(df["neighbourhood"])
stopwords = [re.sub(r"[\(\)]", "", x.lower().strip()).split() for x in stopwords]
stopwords = [x for item in stopwords for x in item]
stopwords += [unicodedata.normalize("NFKD", x).encode('ASCII', 'ignore').decode() for x in stopwords]
stopwords += ["rio", "janeiro", "copa", "arpoador", "pepê", "pepe", "lapa", "morro", "corcovado"]
stopwords = set(stopwords)
docs = [re.sub(r"[\-\_\\\/\,\;\:\!\+\’\%\&\d\*\#\"\´\`\.\|\(\)\[\]\@\'\»\«\>\<\❤️\…]", " ", str(x)) for x in df["name"].tolist()]
docs = [" ".join(x.lower().strip().split()) for x in docs]
docs = ["".join(e for e in x if (e.isalnum() or " ")) for x in docs]
ndocs = []
for doc in tqdm.tqdm(docs):
    ndocs.append(" ".join([x for x in doc.split() if x not in stopwords]))
docs = ndocs
results = []
for d in tqdm.tqdm(docs):
    results.append(langid.classify(d)[0])
df["language"] = results

# Because we transformed NaNs into string, fill those detection with nans too
df.loc[df["name"].isna(), "language"] = pd.NA<jupyter_output><empty_output><jupyter_text>* Test accuracy, manually label 383 out of 88191 (95% conf. interval, 5% margin of error)<jupyter_code>df.loc[~df["name"].isna()].drop_duplicates("name").shape
df.loc[~df["name"].isna()].drop_duplicates("name")[["name", "language"]].sample(n=383, random_state=42).to_csv("lang_pred_1.csv")
lang_pred = pd.read_csv("lang_pred.csv", index_col=0)
lang_pred.head()
overall_accuracy = (lang_pred["pred"] == lang_pred["true"]).sum() / lang_pred.shape[0]
pt_accuracy = (lang_pred[lang_pred["true"] == "pt"]["true"] == lang_pred[lang_pred["true"] == "pt"]["pred"]).sum() / lang_pred[lang_pred["true"] == "pt"].shape[0]
en_accuracy = (lang_pred[lang_pred["true"] == "en"]["true"] == lang_pred[lang_pred["true"] == "en"]["pred"]).sum() / lang_pred[lang_pred["true"] == "en"].shape[0]
print(f"Overall accuracy: {overall_accuracy*100}%")
print(f"Portuguese accuracy: {pt_accuracy*100}%")
print(f"English accuracy: {en_accuracy*100}%")
df = df.drop("name", axis=1)
df.head()
df["language"].value_counts()<jupyter_output><empty_output><jupyter_text>Calculate how many times a room appeared* There are 30 months of data, and rooms appear multiple times* Calculate for a specific date, how many times the same room appeared up to that date<jupyter_code>df = df.set_index(["id", "date"])
df["appearances"] = df.groupby(["id", "date"])["host_id"].count().unstack().cumsum(axis=1).stack()
df = df.reset_index()
df.head()<jupyter_output><empty_output><jupyter_text>Days since last review* Calculate days since last review* Then categorize them by the length of the days<jupyter_code>df.loc[:, "last_review"] = pd.to_datetime(df["last_review"], format="%Y/%m/%d")
# For each scraping date, consider the last date to serve as comparision as the maximum date
last_date = df.groupby("date")["last_review"].max()
df["last_date"] = df.apply(lambda row: last_date.loc[row["date"]], axis=1)
df["days_last_review"] = (df["last_date"] - df["last_review"]).dt.days
df = df.drop("last_date", axis=1)
df.head()
df["days_last_review"].describe()
def categorize_last_review(days_last_review):
    """Transform days since last review into categories
    
    Transform days since last review into one of those categories:
    last_week, last_month, last_half_year, last_year, last_two_years, 
    long_time_ago, or never

    Args:
        days_last_review (int): Days since the last review

    Returns:
        str: A string with the category name. 
        
    """
    if days_last_review <= 7:
        return "last_week"
    elif days_last_review <= 30:
        return "last_month"
    elif days_last_review <= 182:
        return "last_half_year"
    elif days_last_review <= 365:
        return "last_year"
    elif days_last_review <= 730:
        return "last_two_years"
    elif days_last_review > 730:
        return "long_time_ago"
    else:
        return "never"
    
df.loc[:, "last_review"] = df.apply(lambda row: categorize_last_review(row["days_last_review"]), axis=1)
df = df.drop(["days_last_review"], axis=1)
df.head()
df = df.set_index(["id", "date"])

df.loc[:, "appearances"] = df["appearances"].astype(int)

df.loc[:, "host_id"] = df["host_id"].astype("category")
df.loc[:, "neighbourhood"] = df["neighbourhood"].astype("category")
df.loc[:, "room_type"] = df["room_type"].astype("category")
df.loc[:, "last_review"] = df["last_review"].astype("category")
df.loc[:, "language"] = df["language"].astype("category")
df
df.to_pickle("data.pkl")<jupyter_output><empty_output><jupyter_text>Distributions* Check the distribution of features<jupyter_code>df = pd.read_pickle("data.pkl")
df.head()
df["latitude"].hist(bins=250)
df["longitude"].hist(bins=250)
df["price"].hist(bins=250)
df["minimum_nights"].hist(bins=250)
df["number_of_reviews"].hist()
df["reviews_per_month"].hist(bins=250)
df["calculated_host_listings_count"].hist(bins=250)
df["availability_365"].hist()
df["appearances"].hist(bins=29)
df.describe()<jupyter_output><empty_output><jupyter_text>Limits* We are analising mostly for touristic purpose, so get the short-term rentals only* Prices between 10 and 10000 (The luxury Copacabana Palace Penthouse at 8000 for example)* Short-term rentals (minimum_nights < 31)* Impossibility of more than 31 reviews per month<jupyter_code>df = pd.read_pickle("data.pkl")
total_records = len(df)
outbound_values = (df["price"] < 10) | (df["price"] > 10000)
df = df[~outbound_values]

print(f"Removed values {outbound_values.sum()}, {outbound_values.sum()*100/total_records}%")
long_term = df["minimum_nights"] >= 31
df = df[~long_term]

print(f"Removed values {long_term.sum()}, {long_term.sum()*100/total_records}%")
reviews_limit = df["reviews_per_month"] > 31
df = df[~reviews_limit]

print(f"Removed values {reviews_limit.sum()}, {reviews_limit.sum()*100/total_records}%")<jupyter_output>Removed values 2, 0.00019089597982611286%<jupyter_text>Log skewed variables* Most numerical values are skewed, so log them<jupyter_code>df.describe()
# number_of_reviews, reviews_per_month, availability_365 have zeros, thus sum one to all
df["number_of_reviews"] = np.log(df["number_of_reviews"] + 1)
df["reviews_per_month"] = np.log(df["reviews_per_month"] + 1)
df["availability_365"] = np.log(df["availability_365"] + 1)
df["price"] = np.log(df["price"])
df["minimum_nights"] = np.log(df["minimum_nights"])
df["calculated_host_listings_count"] = np.log(df["calculated_host_listings_count"])
df["appearances"] = np.log(df["appearances"])
df.describe()<jupyter_output><empty_output><jupyter_text>Extreme outliers* Most outliers are clearly mistyped values (one can check these rooms ids in their website)* Remove extreme outliers first from large deviations within the same `id` (eliminate rate jumps of same room)* Then remove those from same scraping `date`, `neighbourhood` and `room_type`<jupyter_code>df = df.reset_index()
q25 = df.groupby(["id"])["price"].quantile(0.25)
q75 = df.groupby(["id"])["price"].quantile(0.75)

ext = q75 + 3 * (q75 - q25)
ext = ext[(q75 - q25) > 0.]
affected_rows = []
multiple_id = df[df["id"].isin(ext.index)]
for row in tqdm.tqdm(multiple_id.itertuples(), total=len(multiple_id)):
    if row.price >= ext.loc[row.id]:
        affected_rows.append(row.Index)
df = df.drop(affected_rows)
print(f"Removed values {len(affected_rows)}, {len(affected_rows)*100/total_records}%")
# Remove extreme outliers per neighbourhood, room_type and scraping date
q25 = df.groupby(["date", "neighbourhood", "room_type"])["price"].quantile(0.25)
q75 = df.groupby(["date", "neighbourhood", "room_type"])["price"].quantile(0.75)
ext = q75 + 3 * (q75 - q25)
ext
affected_rows = []
for row in tqdm.tqdm(df.itertuples(), total=len(df)):
    if row.price >= ext.loc[(row.date, row.neighbourhood, row.room_type)]:
        affected_rows.append(row.Index)
df = df.drop(affected_rows)
print(f"Removed values {len(affected_rows)}, {len(affected_rows)*100/total_records}%")
df.describe()
df["price"].hist()
df.to_pickle("treated_data.pkl")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16866498740554156, 'fraction_numerical': 0.02035264483627204, 'mean_word_length': 6.09, 'pattern_counts': {'":': 0, '<': 52, '<?xml version=': 0, '>': 52, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '436', 'license': 'MIT', 'n_tokens_mistral': 3708, 'n_tokens_neox': 3494, 'n_words': 814, 'path': 'airbnb-rj-1/Data Treatment.ipynb', 'repo_name': 'reneoctavio/analysis', 'size': '9925'}