sql sqlite airbnb analýza
Dnešní doba má pro datové analytiky spoustu výhod. Jednou z nich je i to, že velké firmy běžně sdílejí svoje data s veřejností. Nesdílejí samozřejmě úplně vše, ale to, co dávají k dispozici, bývá zajímavým zdrojem informací.
Data z Airbnb
Airbnb poskytuje několik datových sad, které si můžete stáhnout zcela volně a bez registrace.
Data, která pro naši dnešní datovou analýzu použijeme, jsou z března 2022. Airbnb nabízí i několik starších, archivních datových sad. To pro případ, že bychom potřebovali srovnat, jak se situace vyvíjela v průběhu času (např. během epidemie, po různých regulacích, apod.).
V tomto článku použijeme soubor listings.csv a uložíme ho pod názvem airbnb-prague-listings.csv.
Tato sada obsahuje souhrnné informace a metriky pro pronájmy a ubytování v Praze, které jsou dle autorů vhodné pro vizualizace. Tak uvidíme.
Struktura dat
Soubor (tabulka) obsahuje následující sloupce:
- id
- name
- host_id
- host_name
- neighbourhood_group
- neighbourhood
- latitude
- longitude
- room_type
- price
- minimum_nights
- number_of_reviews
- last_review
- reviews_per_month
- calculated_host_listings_count
- availability_365
- number_of_reviews_ltm
- license
I když jsou názvy většiny sloupců popisné a zřejmé, je vždy dobré mít po ruce Data Dictionary, které nám význam jednotlivých položek vysvětlí.
Lokální datová analytika
Další z výhod, které si dnes datoví analytici užívají, je výběr z obrovského množství nástrojů. Mnoho z nich je zdarma a často fungují na lokálním PC.
Věděli jste, že databáze SQLite, kterou pro tuto analýzu používáme má limit velikosti databáze neuvěřitelných 281 terabyte!
Kdo by si ještě před pár lety představil, že bude bez problémů analyzovat na svém PC desítky nebo i stovky milionů řádků?
DB Browser for SQLite
Než vůbec začneme data analyzovat, převedeme zdrojový soubor CSV do SQL tabulky. SQL je ideální jazyk, který nám pomůže snadno a rychle zkoumat data.
Jako SQL databázi využijeme SQLite, která je součástí nástroje s mírně krkolomným názvem DB Browser for SQLite. Ten umí spoustu věcí a navíc je zdarma.
Pokud DB Browser for SQLite na svém PC nemáte, bude se vám hodit tento návod.
Rychlé grafy
Skvělou funkcionalitou DB Browseru je možnost jednoduchého vytváření vizualizací. Stačí pár kliknutí a zobrazí se graf, který je propojen s daty v tabulce. Barvy můžete měnit a graf také snadno uložíte do souboru.
Pokud se vám možnost Plot nezobrazuje, zapněte jí v menu Pohled -> Plot.
Vytvoření nové databáze
Nejdřív si vytvoříme novou, prázdnou databázi.
Do ní naimportujeme CSV soubor airbnb-prague-listings.csv, který automaticky vytvoří novou tabulku s názvem airbnb-prague-listings.
Takto jednoduše získáme z textového CSV souboru SQL databázi, díky které můžeme analyzovat data lokálně. Velmi rychlé a praktické.
Praha a Airbnb
Airbnb zveřejňuje vybrané pražské statistiky na webu. My se jimi budeme inspirovat, ale nebudeme je na 100 % kopírovat.
I když jsou v datové sadě ceny za noc uvedené v lokální měně (tedy v Kč), na výše uvedené stránce se zobrazují v amerických dolarech. Pouhým selským rozumem ale odhalíme, že průměrná cena ubytování $3505/noc (asi 82000 Kč) je nereálná.
Celkový počet záznamů
Kolik je záznamů v souboru, který jsme si stáhli? Na to odpoví jednoduché SQL.
SELECT COUNT(*) AS "Celkový počet"
FROM "airbnb-prague-listings"
;
Celkový počet
-------------
6632
Zápasíte s SQL? Získejte nové dovednosti v SQL kurzech, kterým věří největší společnosti a státní instituce.
Typ pokoje
Hostitelé na Airbnb mohou nabízet celé domy, byty, soukromé anebo sdílené pokoje. Nověji také hotelové pokoje.
Tento SQL dotaz nám ukáže, jakých typů ubytování je v Praze nejvíc.
WITH airbnb AS (
SELECT room_type, COUNT(*) AS "Počet"
FROM "airbnb-prague-listings"
GROUP BY room_type
ORDER BY 2 DESC
)
SELECT *, ROUND(((0.0+"počet")/SUM("počet") OVER()) * 100, 2) || '%' AS "Podíl"
FROM airbnb
;
room_type | Počet | Podíl
----------------|-------|--------
Entire home/apt | 5095 | 76.82%
Private room | 1208 | 18.21%
Hotel room | 234 | 3.53%
Shared room | 95 | 1.43%
Všimli jste si způsobu, jakým počítáme podíl v procentech? SQLite (stejně jako další RDBMS) umí pokročilé agregační funkce, které jsou nazývány window functions nebo také analytical functions. Takové funkce dokáží pracovat s hodnotami z více řádků. My jsme využili SUM, abychom dostali celkový součet jednotlivých typů pokojů. Díky tomu pak snadno spočítáme podíl v procentech.
Průměrná doba pobytu
Co myslíte, bude v nabídce pražských Airbnb pronájmů více krátkodobých nebo dlouhodobých pobytů?
Airbnb definuje pobyty s minimální délkou pobytu do 30 dnů jako krátkodobé. Použijeme tedy stejnou definici.
WITH airbnb AS (
SELECT minimum_nights,
CASE
WHEN minimum_nights < 30 THEN 'Krátkodobý pronájem'
ELSE 'Dlouhodobý pronájem'
END AS Typ
FROM "airbnb-prague-listings"
)
SELECT Typ, COUNT(*) AS "Počet"
FROM airbnb
GROUP BY Typ
ORDER BY "Počet" DESC
;
Na první pohled je jasné, že nabídka krátkodobých pobytů převažuje. To není nic překvapivého, protože cílová skupina pronajímatelů jsou hlavně turisté, kteří nebudou v bytě bydlet dlouho.
Airbnb sice tvrdí, že se trh přesunul k dlouhodobějším pobytům, ale v případě Prahy to určitě neplatí.
Průměrná cena
Kolik stojí noc v Praze? Pokud bychom zahrnuli do výpočtu průměrné ceny všechny záznamy, byl by výsledek hodně zkreslený.
SELECT neighbourhood,
COUNT(price) AS "Počet ubytování",
CAST(AVG(price) AS INTEGER) AS "Průměrná cena"
FROM "airbnb-prague-listings"
WHERE last_review > '2021-10-01'
AND neighbourhood LIKE 'Praha%'
GROUP BY neighbourhood
HAVING "Počet ubytování" > 10
ORDER BY "Průměrná cena" DESC
LIMIT 5
;
neighbourhood | Počet ubytování | Průměrná cena
--------------|-----------------|---------------
Praha 1 | 1531 | 2515
Praha 7 | 158 | 2072
Praha 2 | 559 | 1950
Praha 3 | 341 | 1784
Praha 13 | 22 | 1721
Určitě jste si všimli, že jsme pro výpočet uplatnili následující omezení:
- Ubytování, které bylo hodnoceno během posledních 6 měsíců
- Název čtvrti začíná na Praha
- Čtvrť má v nabídce více než 10 ubytování, aby nám příliš malý počet nezkreslil výsledky
- Pouze TOP 5 podle ceny
Počet nabídek jednotlivých hostitelů
Někteří hostitelé Airbnb nabízí více než jedno ubytování. Mohou nabízet samostatné pokoje v jednom bytě nebo více bytů či domů jako celek.
Hostitele s více nabídkami můžeme považovat za podnikatele, kteří ve všech nemovitostech určitě nebydlí.
WITH airbnb AS (
SELECT DISTINCT host_name || ' (' || host_id || ')' AS "Hostitel",
calculated_host_listings_count,
CASE calculated_host_listings_count WHEN 1 THEN 'Jedno' ELSE 'Více' END AS "Počet ubytování"
FROM "airbnb-prague-listings"
)
SELECT "Počet ubytování", COUNT(*) AS "Počet"
FROM airbnb
GROUP BY "Počet ubytování"
Výsledek ukazuje, že téměř polovina hostitelů nabízí více než jedno ubytování.
Pražští ubytovací magnáti
Kdo má v nabídce nejvíc ubytování? A o jakém počtu nabídek za jednoho hostitele se vůbec bavíme? Jsou to jednotky, desítky nebo ještě víc?
Pro zajímavost se také podíváme, za jakou průměrnou cenu za noc ubytování nabízí.
SELECT host_name || ' (' || host_id || ')' AS "Hostitel",
calculated_host_listings_count,
CAST(AVG(price) AS INTEGER) AS "Průměrná cena/noc"
FROM "airbnb-prague-listings"
GROUP BY Hostitel, calculated_host_listings_count
ORDER BY calculated_host_listings_count DESC
LIMIT 5
Hostitel | calculated_host_listings_count | Průměrná cena/noc
----------------------------|--------------------------------|-------------------
Josef (8087351) | 86 | 2113
Michal & Friends (52431987) | 76 | 2537
Anton & Vika (9550037) | 65 | 2060
Prague For You (1167781) | 58 | 4405
Klara (5282) | 55 | 3970
Poznámka: Pro analýzu nebyly použity žádné soukromé informace. Jména, nabídky a další údaje o recenzích jsou veřejně přístupné na stránkách Airbnb.
Závěrem
Jako při každé analýze, je potřeba vzít v úvahu nejen data samotná, ale i data která nejsou k dispozici a také širší souvislosti. Profesionální analytik nikdy nesklouzne k rychlým a zjednodušujícím interpretacím.
Nejlepší analytici proto vždy kromě dat používají selský rozum.
