<jupyter_start><jupyter_text>AirBnb for Madrid<jupyter_code>import requests
import pandas
import numpy
import geopandas
import geopy
import contextily
import json
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>Download files - Path to file<jupyter_code>url = (
    "http://data.insideairbnb.com/spain/comunidad-de-madrid/madrid/"\
    "2020-12-17/data/listings.csv.gz"
)
url<jupyter_output><empty_output><jupyter_text>- Download<jupyter_code>%time lst = pandas.read_csv(url)<jupyter_output>CPU times: user 714 ms, sys: 186 ms, total: 900 ms
Wall time: 10.9 s<jupyter_text>Parse columns  Prices<jupyter_code>lst["price_usd"] = lst["price"].apply(
    lambda p: float(
        str(p).replace(",", "").strip("$")
    )
)
lst["log1p_price_usd"] = numpy.log1p(lst["price_usd"])<jupyter_output><empty_output><jupyter_text>AmenitiesWe will pick the following ones:- Netflix- WiFi- Coffee maker- Gym- ParkingFor this we define a `finder` method:<jupyter_code>%%time
parser = lambda s: s.lower()\
                    .replace(" ", "")\
                    .replace(",", "")
def amenity_finder(term, amenities):
    pterm = parser(term)
    for amenity in amenities:
        if pterm in parser(amenity):
            return 1
    return 0

ams = set()
amenity_cols = [
    "WiFi", "Coffee", "Gym", "Parking"
]
# Set up containers for new columns
for new_col in amenity_cols:
    lst[new_col] = None
# Pull out amenities
for i, row in lst.iterrows():
    r = json.loads(row['amenities'])
    for amenity_col in amenity_cols:
        lst.loc[i, amenity_col] = amenity_finder(
            amenity_col, r
        )
    ams.update(set(r))

lst[amenity_cols].sum()<jupyter_output>CPU times: user 34.1 s, sys: 0 ns, total: 34.1 s
Wall time: 34.1 s<jupyter_text>We can plot the distribution of those which have and don't have each amenity:<jupyter_code>f, axs = plt.subplots(2, 4, figsize=(20, 12))
for j in range(len(amenity_cols)):
    amenity = amenity_cols[j]
    for i in range(2):
        ax = axs[i, j]
        lst.query(f"{amenity} == {i}")\
           .plot.hexbin(
            "longitude", 
            "latitude", 
            xlim=(lst["longitude"].min(), lst["longitude"].max()),
            ylim=(lst["latitude"].min(), lst["latitude"].max()),
            gridsize=50, 
            ax=ax, 
            cmap="viridis", 
            colorbar=False
        )
        t = "With"
        if i == 0:
            t += "out"
        t += f" {amenity}"
        lbls = contextily.providers.CartoDB.VoyagerOnlyLabels
        contextily.add_basemap(
            ax, crs="EPSG:4326", source=lbls, zoom=11
        )
        ax.set_title(t)
        ax.set_axis_off()<jupyter_output><empty_output><jupyter_text>Clean `bathrooms` `bathrooms_text` is non numeric and would be good to have as a count. The current values are:<jupyter_code>lst["bathrooms_text"].unique()<jupyter_output><empty_output><jupyter_text>To conver, we will simply remove the part with text, and convert the remaining "half-bath" to `0.5`.<jupyter_code>def bathroom_parser(s):
    try:
        return float(s.split(" ")[0])
    except:
        try:
            if "halfbath" in s.lower().replace(" ", "").replace("-", ""):
                return 0.5
            else:
                return numpy.nan
        except:
            return numpy.nan

lst["bathrooms"] = lst["bathrooms_text"].apply(bathroom_parser)<jupyter_output><empty_output><jupyter_text>Subset - Variables to keep<jupyter_code>vars_to_keep = [
    "price",
    "price_usd",
    "log1p_price_usd",
    "accommodates",
    "bathrooms_text",
    "bathrooms",
    "bedrooms",
    "beds",
    "neighbourhood_cleansed",
    "room_type",
    "property_type",
    "WiFi",
    "Coffee",
    "Gym",
    "Parking",
]<jupyter_output><empty_output><jupyter_text>- Point geometries<jupyter_code>pts = geopandas.points_from_xy(
    lst["longitude"], lst["latitude"]
)<jupyter_output><empty_output><jupyter_text>- One table with clearer names<jupyter_code>db = geopandas.GeoDataFrame(
    lst[vars_to_keep],
    geometry = pts,
    crs = "EPSG:4326"
).rename(columns= {
    "neighbourhood_cleansed": "neighbourhood"
})<jupyter_output><empty_output><jupyter_text>Distance to El Retiro - Locate the park<jupyter_code>geocoder = geopy.geocoders.Nominatim(user_agent="El_Retiro")
er = geocoder.geocode("El Retiro, Madrid, Spain")
er<jupyter_output><empty_output><jupyter_text>- Project the point to Spanish CRS (in metres)<jupyter_code>sp_crs = "EPSG:25830"
er_pt = geopandas.GeoSeries(
    geopandas.points_from_xy(
        [er.longitude], [er.latitude],
        crs = "EPSG:4326"
    )
).to_crs(sp_crs)<jupyter_output><empty_output><jupyter_text>- Distances (in Km)<jupyter_code>dists = db.to_crs(sp_crs)\
          .distance(
    geopandas.GeoSeries(
        er_pt.loc[0], index=db.index, crs=er_pt.crs
    )
) / 1000<jupyter_output><empty_output><jupyter_text>- Add to table<jupyter_code>db["km_to_retiro"] = dists<jupyter_output><empty_output><jupyter_text>Write out - Only _full_ rows<jupyter_code>out_p = "madrid_abb.gpkg"
! rm $out_p
db.dropna().to_file(out_p, driver="GPKG")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13876994939665238, 'fraction_numerical': 0.014402491241728299, 'mean_word_length': 3.5317460317460316, 'pattern_counts': {'":': 1, '<': 63, '<?xml version=': 0, '>': 63, 'https://': 0, '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': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '80315', 'license': 'CC0-1.0', 'n_tokens_mistral': 2010, 'n_tokens_neox': 1927, 'n_words': 360, 'path': 'data/assignment_1_madrid/clean_data.ipynb', 'repo_name': 'GDSL-UL/san', 'size': '5138'}