# Getting Started: Doorstep Analytics Airbnb Data (for AI agents)

Read this first. It tells you what the files are, how they join, and which columns to reach for. For the full per-column breakdown (type, source, description, example, notes) open [`DoorstepAnalytics_DataDictionary.xlsx`](DoorstepAnalytics_DataDictionary.xlsx) (one sheet, `Data Dict`; filter on the `File` column, e.g. `overview.csv`).

## What you have
One scrape of one location (e.g. Edinburgh, UK) delivered as a properly named zip file containing simply named CSVs (`overview.csv`, `calendar.csv`, ...). Each CSV is a snapshot taken on `Scrape_Date`. Every file carries `Country`, `Location`, `Scrape_Date` and `RecordInserted` (always the last column).

| File | One row per | Use it for |
|---|---|---|
| `overview.csv` | listing | **Start here.** Listing attributes, ratings, location, prices, availability stats, amenity flags, host summary. |
| `calendar.csv` | listing x calendar day | Availability and nightly price over time. Large (~350 days per listing). |
| `pricing_flexible.csv` | listing x guests x stay window | How price changes with guest count and stay length (`one_week`, `one_month`, `weekend_trip`). |
| `description.csv` | listing | Free text (title, description, house rules, photo captions), check-in times, registration number. |
| `amenities.csv` | listing x amenity | Long-form amenity list (`Amenity_Group`, `Amenity_Title`). |
| `reviews.csv` | review | Review text, rating, language, reviewer region, host replies. |
| `hosts.csv` | host | Host profile, verification, tenure, portfolio size, business details. |

## Keys and joins
- `Airbnb_ListingID` joins overview, calendar, pricing_flexible, description, amenities, reviews. Not in hosts. Treat it as an **integer/string, never a float** (19 digits; floats corrupt it).
- `Host_ID` joins overview, description, reviews to `hosts.csv`. (`Contextual_Host_ID` in hosts is only for profile URLs.)
- Listing URL: `https://airbnb.com/rooms/<Airbnb_ListingID>`.
- A listing can legitimately have no rows in `amenities.csv` or `reviews.csv`. Do not treat that as an error.
- Listings are scoped to one `Location`. If you combine locations or dates, group by `Location` and `Scrape_Date` too.

## Where to go for common questions

| Question | Go to | Key columns |
|---|---|---|
| How many listings / what is the mix? | `overview` | `RoomType_Clean`, `SpaceType_Clean`, `Bedrooms`, `PersonCapacity` |
| Average price | `overview` | `AvgNightPrice_Final` (recorded + estimated), `AvgNightPrice_Recorded` (observed only), `Currency`, `ExchangeRate_toUSD` |
| Seasonal / monthly price | `overview` | `Price_<Season>_Final`, `PriceRecorded_<Mon>`, `PriceEstimated_<Mon>` |
| Price by day | `calendar` | `Calendar_Date`, `FinalPricePerNight`, `FinalPrice_Source` |
| Availability | `calendar` (daily) or `overview` | `isAvailable`, `Available_For_Checkin`, `AvailableDays_Total`, `FirstAvailable_Date` |
| Occupancy / demand | Not yet supported | An occupancy product is being built for early 2027. Do not derive or report occupancy figures for now. |
| Weekend, Christmas, holiday price uplift | `overview` | `DynamicPricing_*`, `isDynamicPricing_*` |
| Extra-guest fees | `pricing_flexible` / `overview` | `Guests`, `DynamicPricing_IncreasePerGuest`, `DynamicPricing_FreeGuestAllowance` |
| Quality / ratings | `overview` | `StarRating`, `ReviewCount`, `*Rating`, `is_GuestFavourite`, `RatingPct_*star` |
| Review sentiment, guest origin | `reviews` | `ReviewComments`, `Rating`, `Reviewer_Country`, `ReviewLanguage` |
| Maps / neighbourhoods | `overview` | `Lat`, `Lng`, `MapMarkerType`, `Neighbourhood`, `Suburb`, `NearestMetro_Distance`, `NearestAirport_Distance` |
| Professional hosts / multi-listing | `overview`, `hosts` | `Host_MultipleListings`, `Host_ManagedListingsCount`, `Host_IsBusiness` |
| Regulation / compliance | `description`, `hosts` | `RegistrationNumber`, `Host_PolicyNumber`, `Business_*` |
| Amenity analysis | `overview` (flags) or `amenities` (full list) | `Amenity_Has*`, `Amenity_Group`, `Amenity_Title` |

## Things that will trip you up
1. **Recorded vs Estimated vs Final prices.** Airbnb only exposes a price for a date when it is searched, so only ~20-40% of in-span days have a *recorded* price. The rest are modelled. `Final` = recorded if present, else estimate. Check `FinalPrice_Source` (`recorded`, `estimate`, or null when out of span or no signal) and `PriceCoverage_RecordedPct` before trusting a listing's price. Use `*_Recorded` when you need observed-only numbers.
2. **Unavailable is not the same as booked.** Airbnb does not say why a day is unavailable, and the booked/not-for-rental status columns (`determinedStatus`, `Likelihood_*`, `isBooked`, `Days_BookedEstimated`) are experimental. Occupancy is being built for early 2027; if asked for occupancy, say so rather than estimating it. Use `isAvailable` (and `calendar not open` days are not a real signal) for availability questions.
3. **Dates.** Calendar covers roughly the next year from `Scrape_Date`; each file is one snapshot.
4. **Calendar prices are for 2 guests** (`Guests` is only populated on priced rows). Use `pricing_flexible` for other guest counts.
5. **Currency.** Prices are in the listing's local `Currency` (usually the country's; some use EUR/USD). `ExchangeRate_toUSD` converts. Do not mix currencies across locations without converting.
6. **Outliers.** `OutlierPrice_PerBedroom` / `OutlierPrice_PerGuest` flag prices beyond mean +/- 2.5 SD within the bedroom/guest group. Filter or review them before averaging. `IsLowReviewScore` and `NoReviewsNotNew` hint at suspect listings.
7. **Hotels.** Hotels are not included in the data. `IsHomeOrServicedApartment` and `RoomType_Clean` (`Entire Home`, `Private Room`, `Serviced Apartment`, `Shared Room`, `Hostel`, `Other`) are the clean categorisations; prefer them over raw `RoomType`/`SpaceType`.
8. **Approximate locations.** Airbnb jitters coordinates; `MapMarkerType` is `EXACT` or `APPROXIMATE`. Distances to metro/rail/airport are from those coordinates. Address fields come from OpenStreetMap reverse geocoding and can be blank.
9. **`*_Localized` text columns** are translations; the plain column is the original.
10. **Reviews:** `TypeOfTrip` or `LengthOfStay` is filled, not both; likewise reviewer region vs months on Airbnb. `hosts`/`reviews` text may contain newlines; use a real CSV parser.
11. **Booleans/nulls.** Empty cells are null (not 0 or False). Read with a nullable dtype (pandas `boolean`/`Int64`), and parse dates explicitly.

## Package tiers
- **Free:** limited to 1,000 listings per location.
- **Essentials:** contains all rows, but only a subset of columns; it drops most calculated columns (pricing and dynamic pricing, bed types, `RatingPct_*`/sub-ratings, most `Amenity_Has*`, most availability stats). Dictionary rows marked "Not included in the Essentials package" will be missing; check the header before using a column.
- **Complete:** everything in the dictionary.

If a column you need is absent, check the CSV header and say which tier is likely responsible instead of guessing a value.

## Suggested first steps
```python
import pandas as pd
ov = pd.read_csv("overview.csv", dtype={"Airbnb_ListingID": "string", "Host_ID": "string"}, low_memory=False)
print(ov.shape, ov.Location.unique(), ov.Scrape_Date.max(), ov.Currency.unique())
print(ov.RoomType_Clean.value_counts())
print(ov[["AvgNightPrice_Final", "StarRating", "ReviewCount", "PriceCoverage_RecordedPct"]].describe())
```
1. Open `overview.csv` and check shape, `Location`, `Scrape_Date`, `Currency`, and which columns exist.
2. Decide the grain you need (listing, day, host, review) and pick the file from the tables above.
3. Look up unfamiliar columns in `DoorstepAnalytics_DataDictionary.xlsx` before interpreting them.
4. Load `calendar.csv` only if you need daily data, selecting columns with `usecols` (it is large).
5. State assumptions (estimated vs recorded, currency, date range, filters) with every number you report.

## Example: listings and median nightly price by neighbourhood
```python
print(ov.groupby("Neighbourhood").agg(listings=("Airbnb_ListingID", "size"), median_price=("AvgNightPrice_Final", "median"), avg_rating=("StarRating", "mean")).sort_values("listings", ascending=False).head(10))
```