Leipzig Rental Offers: Cleaning, Geocoding, and Reproducibility

1At a glance

The pipeline is deterministic given the same dataset snapshot and the same OpenStreetMap (OSM) extract. Coordinates are derived by geocoding street addresses, not read from the file. Two short scripts do the work: one cleans, one geocodes; both are listed in full below.

2Source dataset

The raw data is the public Kaggle dataset Apartment rental offers in Germany (immo_data.csv), scraped from the ImmoScout24 portal across 2018–19, comprising 268,850 listings nationwide and released under CC BY-NC-SA. We take the subset regio2 == "Leipzig" → 13,723 offers, the single largest city in the dataset.

Its spatial fields are administrative and textual only: regio1/2/3 (state / city / quarter), geo_bln, geo_krs, geo_plz (5-digit postal code, present for 100% of rows), and street / streetPlain / houseNumber (a street is given for ~73.6% of the German rows; the remainder read "no_information"). Rent and size fields (baseRent, serviceCharge, totalRent, livingSpace, noRooms, …) carry the usual portal noise: out-of-range values, blanks, and re-posted duplicates.

3Why Leipzig

The idea under test is spatial, so the demonstrator needed a city whose addresses geocode well. Among large German cities in the dataset, Leipzig combined the largest raw count with the most complete street information, which translates directly into geocoding precision: after processing, 90.4% of Leipzig offers resolve to an exact house-number point (see §6). A city with sparser street fields would have pushed more listings down to coarse postal-code centroids, weakening the spatial signal the technique relies on. Leipzig was therefore chosen on data quality, not convenience.

4No coordinates in the raw data

Because positions are derived, each row also carries a geoLevel flag recording how precisely it was located — house, street, or plz — so downstream users can weight or filter by spatial certainty.

5Cleaning pipeline

Cleaning applies seven deterministic steps. Plausibility bounds remove physically impossible values; out-of-range secondary fields are set to missing rather than dropped; exact duplicates (re-posts of the same unit) are collapsed.

1. Require a valid 5-digit geo_plz.
2. Derive totalRent = baseRent + serviceCharge where totalRent is missing.
3. Plausibility filter (drop the row if it fails): baseRent ∈ [50, 10000], livingSpace ∈ [10, 400], noRooms ∈ [1, 10], totalRent ≤ 15000.
4. Range-clip to missing (keep the row, blank the field): serviceCharge outside [0, 2000], yearConstructed outside [1850, 2025], floor outside [−2, 60].
5. Normalize booleans (hasKitchen, balcony, cellar, lift, garden, newlyConst) to 0/1.
6. Drop exact-duplicate listings keyed on (geo_plz, baseRent, totalRent, livingSpace, noRooms, typeOfFlat, floor).
7. Derive pricePerSqm = baseRent / livingSpace.

import pandas as pd, numpy as np

df = pd.read_csv("immo_data.csv", low_memory=False)

# 1. subset Leipzig  -> 13,723 rows
df = df[df["regio2"] == "Leipzig"].copy()

# 2. valid 5-digit postal code
df = df[df["geo_plz"].astype(str).str.fullmatch(r"\d{5}")]

# 3. derive totalRent where missing
df["totalRent"] = df["totalRent"].fillna(
        df["baseRent"] + df["serviceCharge"].fillna(0))

# 4. plausibility filter  -> 13,717 rows
keep = (df.baseRent.between(50, 10000) & df.livingSpace.between(10, 400)
        & df.noRooms.between(1, 10) & (df.totalRent <= 15000))
df = df[keep].copy()

# 5. range-clip secondary fields to missing (keep the row)
df.loc[~df.serviceCharge.between(0, 2000),     "serviceCharge"]   = np.nan
df.loc[~df.yearConstructed.between(1850, 2025),"yearConstructed"] = np.nan
df.loc[~df.floor.between(-2, 60),              "floor"]          = np.nan

# 6. booleans -> 0/1
for c in ["hasKitchen","balcony","cellar","lift","garden","newlyConst"]:
    df[c] = (df[c].map({True:1, False:0, "True":1, "False":0})
                  .fillna(0).astype(int))

# 7. drop exact duplicates  -> 12,849 rows
df = df.drop_duplicates(subset=["geo_plz","baseRent","totalRent",
        "livingSpace","noRooms","typeOfFlat","floor"])

# derive price per square metre
df["pricePerSqm"] = (df.baseRent / df.livingSpace).round(2)

df.to_csv("leipzig_clean.csv", index=False)   # feeds the geocoder

6Geocoding pipeline

To respect OSM usage policy, addresses are resolved with a single bulk Overpass query over the Leipzig administrative boundary, not thousands of per-address Nominatim calls. The query returned 81,448 address elements, normalized into a lookup of 73,878 unique house-number addresses, 2,937 street centroids, and 37 postal-code centroids.

Each listing is matched after normalizing both sides — HTML-entity decoding (so Hauptstraße → Hauptstraße), ß/umlaut folding, straße/str. collapse, and house-number whitespace stripping — with a graded fallback. Where the exact number is not found, the row falls back to the street centroid, then to the postal-code centroid.

House-number points are kept at their exact coordinates. Only postal-code-centroid points receive a small separating jitter (~15 m) at display time in the demonstrator so co-located markers do not overlap; the stored coordinates remain the unjittered centroids.

import requests, html, re, pandas as pd
from collections import defaultdict

OVERPASS = "https://overpass-api.de/api/interpreter"
HEADERS  = {"User-Agent": "SoftSky-research/1.0 (contact: you@example.org)"}  # 406 fix

# --- 1. one bulk query over the Leipzig admin boundary (admin_level 6) ---
Q = """
[out:json][timeout:300];
area["name"="Leipzig"]["admin_level"="6"]->.a;
( node["addr:housenumber"](area.a);
  way ["addr:housenumber"](area.a); );
out center tags;
"""
els = requests.post(OVERPASS, data={"data": Q}, headers=HEADERS).json()["elements"]

# --- 2. normalisation (apply identically to OSM and to listings) ---
def nstreet(s):
    s = html.unescape(str(s)).lower()
    s = (s.replace("ä","ae").replace("ö","oe")
           .replace("ü","ue").replace("ß","ss"))
    s = re.sub(r"stra(ss|ße)e?\b", "str", s)
    s = re.sub(r"str\.?", "str", s)
    return re.sub(r"\s+", " ", s).strip()
def nnum(n): return re.sub(r"\s+", "", html.unescape(str(n))).lower()

# --- 3. build lookups: exact address, street centroid, plz centroid ---
addr, street_pts, plz_pts = {}, defaultdict(list), defaultdict(list)
for e in els:
    t = e.get("tags", {})
    lat = e.get("lat")  or e.get("center", {}).get("lat")
    lon = e.get("lon")  or e.get("center", {}).get("lon")
    if lat is None: continue
    st, nu, pz = t.get("addr:street"), t.get("addr:housenumber"), t.get("addr:postcode")
    if st and nu:
        addr[(nstreet(st), nnum(nu))] = (lat, lon)
        street_pts[nstreet(st)].append((lat, lon))
    if pz: plz_pts[str(pz)].append((lat, lon))

def centroid(pts):
    return (sum(p[0] for p in pts)/len(pts),
            sum(p[1] for p in pts)/len(pts))
street_c = {k: centroid(v) for k,v in street_pts.items()}
plz_c    = {k: centroid(v) for k,v in plz_pts.items()}

# --- 4. graded match per listing, recording geoLevel ---
df = pd.read_csv("leipzig_clean.csv", low_memory=False)
lat, lng, lvl = [], [], []
for _, r in df.iterrows():
    st, nu, pz = nstreet(r.get("street","")), nnum(r.get("houseNumber","")), str(r.geo_plz)
    if (st, nu) in addr:        p, L = addr[(st, nu)], "house"
    elif st in street_c:       p, L = street_c[st],   "street"
    elif pz in plz_c:          p, L = plz_c[pz],      "plz"
    else:                     p, L = (None, None),    None
    lat.append(p[0]); lng.append(p[1]); lvl.append(L)

df["lat"], df["lng"], df["geoLevel"] = lat, lng, lvl
df = df.dropna(subset=["lat","lng"])            # drop 4 unlocatable -> 12,845
df.to_csv("leipzig_clean_geo.csv", index=False)

The normalization above is the reference behavior; minor regex differences will not change which rows match at house level, only a handful of borderline street spellings.

7Output table & schema

leipzig_clean_geo.csv holds 12,845 rows in 27 columns: the original 24 retained fields plus the derived pricePerSqm and the geocoded lat, lng, and geoLevel. Coordinates are WGS-84.

8Reproducing this

Environment

# Python 3.11; only two third-party packages are needed
pip install pandas requests

# 1) obtain immo_data.csv from Kaggle (login required), place it here
#    https://www.kaggle.com/datasets/corrieaar/apartment-rental-offers-in-germany

python clean_leipzig.py        # immo_data.csv     -> leipzig_clean.csv
python geocode_overpass.py     # leipzig_clean.csv -> leipzig_clean_geo.csv

Reproducibility checklist

• Cleaning is fully deterministic; the same immo_data.csv yields identical counts (13,723 → 13,717 → 12,849).
• Geocoding depends on a live OSM snapshot. As OSM gains addresses over time, the house-level share can only rise; record the query date for exact reproduction.
• The bulk Overpass query is idempotent and contains no randomness; matching is a deterministic lookup.
• The only stochastic element — the ~15 m marker jitter — lives in the demonstrator, not in the stored coordinates, and is irrelevant to analysis.
• Set a descriptive User-Agent or Overpass returns HTTP 406 and no data.

9Limitations & ethics

Geocoding accuracy. 90.4% of rows are exact address points; the remaining ~9.6% sit at street or postal-code centroids and are coarser by design. The geoLevel flag makes this explicit so analyses can exclude or down-weight imprecise rows.

Temporal scope. Listings are from 2018–19 and reflect that market, not current rents. The OSM extract is contemporaneous with processing, not with the listings.

Selection & portal bias. The data is one portal's online offers, not a census of the housing stock; price and composition are biased toward what was advertised on ImmoScout24.

Privacy & license. Records are public, aggregated property advertisements with no personal identifiers. Processing adds none. Reuse is bound by CC BY-NC-SA (attribution, non-commercial, share-alike); OSM-derived coordinates are © OpenStreetMap contributors under the ODbL.