{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Real Estate: Delta Liquid Clustering Demo\n", "\n", "\n", "## Overview\n", "\n", "\n", "This notebook demonstrates the power of **Delta Liquid Clustering** in Oracle AI Data Platform (AIDP) Workbench using a real estate analytics use case. Liquid clustering automatically optimizes data layout for query performance without requiring manual partitioning or Z-Ordering.\n", "\n", "### What is Liquid Clustering?\n", "\n", "Liquid clustering automatically identifies and groups similar data together based on clustering columns you define. This optimization happens automatically during data ingestion and maintenance operations, providing:\n", "\n", "- **Automatic optimization**: No manual tuning required\n", "- **Improved query performance**: Faster queries on clustered columns\n", "- **Reduced maintenance**: No need for manual repartitioning\n", "- **Adaptive clustering**: Adjusts as data patterns change\n", "\n", "### Use Case: Property Transactions and Market Analysis\n", "\n", "We'll analyze real estate transactions and property market data. Our clustering strategy will optimize for:\n", "\n", "- **Property-specific queries**: Fast lookups by property ID\n", "- **Time-based analysis**: Efficient filtering by transaction and listing dates\n", "- **Market performance patterns**: Quick aggregation by location and property type\n", "\n", "### AIDP Environment Setup\n", "\n", "This notebook leverages the existing Spark session in your AIDP environment." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Create real estate catalog and analytics schema\n", "\n", "# In AIDP, catalogs provide data isolation and governance\n", "\n", "spark.sql(\"CREATE CATALOG IF NOT EXISTS real_estate\")\n", "\n", "spark.sql(\"CREATE SCHEMA IF NOT EXISTS real_estate.analytics\")\n", "\n", "print(\"Real estate catalog and analytics schema created successfully!\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Step 2: Create Delta Table with Liquid Clustering\n", "\n", "### Table Design\n", "\n", "Our `property_transactions` table will store:\n", "\n", "- **property_id**: Unique property identifier\n", "- **transaction_date**: Date of property transaction\n", "- **property_type**: Type (Single Family, Condo, Apartment, etc.)\n", "- **sale_price**: Transaction sale price\n", "- **location**: Geographic location/neighborhood\n", "- **days_on_market**: Time property was listed before sale\n", "- **price_per_sqft**: Price per square foot\n", "\n", "### Clustering Strategy\n", "\n", "We'll cluster by `property_id` and `transaction_date` because:\n", "\n", "- **property_id**: Properties may have multiple transactions over time, grouping their sales history together\n", "- **transaction_date**: Time-based queries are critical for market analysis, seasonal trends, and investment performance\n", "- This combination optimizes for both property tracking and temporal market analysis" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Delta table with liquid clustering created successfully!\n", "Clustering will automatically optimize data layout for queries on property_id and transaction_date.\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Create Delta table with liquid clustering\n", "\n", "# CLUSTER BY defines the columns for automatic optimization\n", "\n", "spark.sql(\"\"\"\n", "\n", "CREATE TABLE IF NOT EXISTS real_estate.analytics.property_transactions (\n", "\n", " property_id STRING,\n", "\n", " transaction_date DATE,\n", "\n", " property_type STRING,\n", "\n", " sale_price DECIMAL(12,2),\n", "\n", " location STRING,\n", "\n", " days_on_market INT,\n", "\n", " price_per_sqft DECIMAL(8,2)\n", "\n", ")\n", "\n", "USING DELTA\n", "\n", "CLUSTER BY (property_id, transaction_date)\n", "\n", "\"\"\")\n", "\n", "print(\"Delta table with liquid clustering created successfully!\")\n", "\n", "print(\"Clustering will automatically optimize data layout for queries on property_id and transaction_date.\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Step 3: Generate Real Estate Sample Data\n", "\n", "### Data Generation Strategy\n", "\n", "We'll create realistic real estate transaction data including:\n", "\n", "- **8,000 properties** with multiple transactions over time\n", "- **Property types**: Single Family, Condo, Townhouse, Apartment, Commercial\n", "- **Realistic market patterns**: Seasonal pricing, location premiums, market fluctuations\n", "- **Geographic diversity**: Different neighborhoods with varying price points\n", "\n", "### Why This Data Pattern?\n", "\n", "This data simulates real real estate scenarios where:\n", "\n", "- Properties appreciate or depreciate over time\n", "- Market conditions vary by season and location\n", "- Investment performance requires historical tracking\n", "- Neighborhood analysis drives pricing strategies\n", "- Market trends influence buying/selling decisions" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Generated 11453 property transaction records\n", "Sample record: {'property_id': 'PROP000001', 'transaction_date': datetime.date(2024, 3, 24), 'property_type': 'Single Family', 'sale_price': 806427.68, 'location': 'Downtown', 'days_on_market': 68, 'price_per_sqft': 374.56}\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Generate sample real estate transaction data\n", "\n", "# Using fully qualified imports to avoid conflicts\n", "\n", "import random\n", "\n", "from datetime import datetime, timedelta\n", "\n", "\n", "# Define real estate data constants\n", "\n", "PROPERTY_TYPES = ['Single Family', 'Condo', 'Townhouse', 'Apartment', 'Commercial']\n", "\n", "LOCATIONS = ['Downtown', 'Suburban', 'Waterfront', 'Mountain View', 'Urban Core', 'Residential District']\n", "\n", "# Base pricing parameters by property type and location\n", "\n", "PRICE_PARAMS = {\n", "\n", " 'Single Family': {\n", "\n", " 'Downtown': {'base_price': 850000, 'sqft_range': (1800, 3500)},\n", "\n", " 'Suburban': {'base_price': 650000, 'sqft_range': (2000, 4000)},\n", "\n", " 'Waterfront': {'base_price': 1200000, 'sqft_range': (2200, 4500)},\n", "\n", " 'Mountain View': {'base_price': 750000, 'sqft_range': (1900, 3800)},\n", "\n", " 'Urban Core': {'base_price': 950000, 'sqft_range': (1600, 3200)},\n", "\n", " 'Residential District': {'base_price': 700000, 'sqft_range': (2100, 4200)}\n", "\n", " },\n", "\n", " 'Condo': {\n", "\n", " 'Downtown': {'base_price': 550000, 'sqft_range': (800, 1800)},\n", "\n", " 'Suburban': {'base_price': 350000, 'sqft_range': (900, 2000)},\n", "\n", " 'Waterfront': {'base_price': 750000, 'sqft_range': (1000, 2200)},\n", "\n", " 'Mountain View': {'base_price': 450000, 'sqft_range': (850, 1900)},\n", "\n", " 'Urban Core': {'base_price': 650000, 'sqft_range': (750, 1700)},\n", "\n", " 'Residential District': {'base_price': 400000, 'sqft_range': (950, 2100)}\n", "\n", " },\n", "\n", " 'Townhouse': {\n", "\n", " 'Downtown': {'base_price': 700000, 'sqft_range': (1400, 2800)},\n", "\n", " 'Suburban': {'base_price': 550000, 'sqft_range': (1600, 3200)},\n", "\n", " 'Waterfront': {'base_price': 900000, 'sqft_range': (1500, 3000)},\n", "\n", " 'Mountain View': {'base_price': 600000, 'sqft_range': (1450, 2900)},\n", "\n", " 'Urban Core': {'base_price': 800000, 'sqft_range': (1300, 2600)},\n", "\n", " 'Residential District': {'base_price': 580000, 'sqft_range': (1650, 3300)}\n", "\n", " },\n", "\n", " 'Apartment': {\n", "\n", " 'Downtown': {'base_price': 450000, 'sqft_range': (600, 1400)},\n", "\n", " 'Suburban': {'base_price': 280000, 'sqft_range': (650, 1500)},\n", "\n", " 'Waterfront': {'base_price': 600000, 'sqft_range': (700, 1600)},\n", "\n", " 'Mountain View': {'base_price': 350000, 'sqft_range': (625, 1450)},\n", "\n", " 'Urban Core': {'base_price': 520000, 'sqft_range': (550, 1300)},\n", "\n", " 'Residential District': {'base_price': 320000, 'sqft_range': (675, 1550)}\n", "\n", " },\n", "\n", " 'Commercial': {\n", "\n", " 'Downtown': {'base_price': 2500000, 'sqft_range': (3000, 10000)},\n", "\n", " 'Suburban': {'base_price': 1500000, 'sqft_range': (2500, 8000)},\n", "\n", " 'Waterfront': {'base_price': 3500000, 'sqft_range': (4000, 12000)},\n", "\n", " 'Mountain View': {'base_price': 1800000, 'sqft_range': (2800, 9000)},\n", "\n", " 'Urban Core': {'base_price': 3000000, 'sqft_range': (3500, 11000)},\n", "\n", " 'Residential District': {'base_price': 1600000, 'sqft_range': (2600, 8500)}\n", "\n", " }\n", "\n", "}\n", "\n", "\n", "\n", "# Generate property transaction records\n", "\n", "transaction_data = []\n", "\n", "base_date = datetime(2024, 1, 1)\n", "\n", "\n", "# Create 8,000 properties with 1-4 transactions each\n", "\n", "for property_num in range(1, 8001):\n", "\n", " property_id = f\"PROP{property_num:06d}\"\n", " \n", " # Each property gets 1-4 transactions over 12 months (most have 1, some flip/resale)\n", "\n", " num_transactions = random.choices([1, 2, 3, 4], weights=[0.7, 0.2, 0.08, 0.02])[0]\n", " \n", " # Select property type and location (consistent for the same property)\n", "\n", " property_type = random.choice(PROPERTY_TYPES)\n", "\n", " location = random.choice(LOCATIONS)\n", " \n", " params = PRICE_PARAMS[property_type][location]\n", " \n", " # Base square footage for this property\n", "\n", " sqft = random.randint(params['sqft_range'][0], params['sqft_range'][1])\n", " \n", " for i in range(num_transactions):\n", "\n", " # Spread transactions over 12 months\n", "\n", " days_offset = random.randint(0, 365)\n", "\n", " transaction_date = base_date + timedelta(days=days_offset)\n", " \n", " # Calculate sale price with market variations\n", "\n", " # Seasonal pricing (higher in spring/summer)\n", "\n", " month = transaction_date.month\n", "\n", " if month in [3, 4, 5, 6]: # Spring/Summer peak\n", "\n", " seasonal_factor = 1.15\n", "\n", " elif month in [11, 12, 1, 2]: # Winter off-season\n", "\n", " seasonal_factor = 0.9\n", "\n", " else:\n", "\n", " seasonal_factor = 1.0\n", " \n", " # Market appreciation over time (slight increase)\n", "\n", " months_elapsed = (transaction_date.year - base_date.year) * 12 + (transaction_date.month - base_date.month)\n", "\n", " appreciation_factor = 1.0 + (months_elapsed * 0.002) # 0.2% monthly appreciation\n", " \n", " # Calculate price per square foot\n", "\n", " base_price_per_sqft = params['base_price'] / ((params['sqft_range'][0] + params['sqft_range'][1]) / 2)\n", "\n", " price_per_sqft = round(base_price_per_sqft * seasonal_factor * appreciation_factor * random.uniform(0.9, 1.1), 2)\n", " \n", " # Calculate total sale price\n", "\n", " sale_price = round(price_per_sqft * sqft, 2)\n", " \n", " # Days on market (varies by property type and market conditions)\n", "\n", " if property_type == 'Commercial':\n", "\n", " days_on_market = random.randint(30, 180)\n", "\n", " else:\n", "\n", " days_on_market = random.randint(7, 90)\n", " \n", " transaction_data.append({\n", "\n", " \"property_id\": property_id,\n", "\n", " \"transaction_date\": transaction_date.date(),\n", "\n", " \"property_type\": property_type,\n", "\n", " \"sale_price\": sale_price,\n", "\n", " \"location\": location,\n", "\n", " \"days_on_market\": days_on_market,\n", "\n", " \"price_per_sqft\": price_per_sqft\n", "\n", " })\n", "\n", "\n", "\n", "print(f\"Generated {len(transaction_data)} property transaction records\")\n", "\n", "print(\"Sample record:\", transaction_data[0])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Step 4: Insert Data Using PySpark\n", "\n", "### Data Insertion Strategy\n", "\n", "We'll use PySpark to:\n", "\n", "1. **Create DataFrame** from our generated data\n", "2. **Insert into Delta table** with liquid clustering\n", "3. **Verify the insertion** with a sample query\n", "\n", "### Why PySpark for Insertion?\n", "\n", "- **Distributed processing**: Handles large datasets efficiently\n", "- **Type safety**: Ensures data integrity\n", "- **Optimization**: Leverages Spark's query optimization\n", "- **Liquid clustering**: Automatically applies clustering during insertion" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "DataFrame Schema:\n", "root\n", " |-- days_on_market: long (nullable = true)\n", " |-- location: string (nullable = true)\n", " |-- price_per_sqft: double (nullable = true)\n", " |-- property_id: string (nullable = true)\n", " |-- property_type: string (nullable = true)\n", " |-- sale_price: double (nullable = true)\n", " |-- transaction_date: date (nullable = true)\n", "\n", "\n", "Sample Data:\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+--------------+--------------------+--------------+-----------+-------------+----------+----------------+\n", "|days_on_market| location|price_per_sqft|property_id|property_type|sale_price|transaction_date|\n", "+--------------+--------------------+--------------+-----------+-------------+----------+----------------+\n", "| 68| Downtown| 374.56| PROP000001|Single Family| 806427.68| 2024-03-24|\n", "| 53| Downtown| 277.53| PROP000001|Single Family| 597522.09| 2024-02-21|\n", "| 19| Downtown| 351.79| PROP000001|Single Family| 757403.87| 2024-10-07|\n", "| 56|Residential District| 236.95| PROP000002|Single Family| 523896.45| 2024-06-15|\n", "| 168| Waterfront| 364.25| PROP000003| Commercial| 3345272.0| 2024-02-15|\n", "+--------------+--------------------+--------------+-----------+-------------+----------+----------------+\n", "only showing top 5 rows\n", "\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "\n", "Successfully inserted 11453 records into real_estate.analytics.property_transactions\n", "Liquid clustering automatically optimized the data layout during insertion!\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Insert data using PySpark DataFrame operations\n", "\n", "# Using fully qualified function references to avoid conflicts\n", "\n", "\n", "# Create DataFrame from generated data\n", "\n", "df_transactions = spark.createDataFrame(transaction_data)\n", "\n", "\n", "# Display schema and sample data\n", "\n", "print(\"DataFrame Schema:\")\n", "\n", "df_transactions.printSchema()\n", "\n", "\n", "\n", "print(\"\\nSample Data:\")\n", "\n", "df_transactions.show(5)\n", "\n", "\n", "# Insert data into Delta table with liquid clustering\n", "\n", "# The CLUSTER BY (property_id, transaction_date) will automatically optimize the data layout\n", "\n", "df_transactions.write.mode(\"overwrite\").saveAsTable(\"real_estate.analytics.property_transactions\")\n", "\n", "\n", "print(f\"\\nSuccessfully inserted {df_transactions.count()} records into real_estate.analytics.property_transactions\")\n", "\n", "print(\"Liquid clustering automatically optimized the data layout during insertion!\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Step 5: Demonstrate Liquid Clustering Benefits\n", "\n", "### Query Performance Analysis\n", "\n", "Now let's see how liquid clustering improves query performance. We'll run queries that benefit from our clustering strategy:\n", "\n", "1. **Property transaction history** (clustered by property_id)\n", "2. **Time-based market analysis** (clustered by transaction_date)\n", "3. **Combined property + time queries** (optimal for our clustering)\n", "\n", "### Expected Performance Benefits\n", "\n", "With liquid clustering, these queries should be significantly faster because:\n", "\n", "- **Data locality**: Related records are physically grouped together\n", "- **Reduced I/O**: Less data needs to be read from disk\n", "- **Automatic optimization**: No manual tuning required" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "=== Query 1: Property Transaction History ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+-----------+----------------+-------------+----------+--------+\n", "|property_id|transaction_date|property_type|sale_price|location|\n", "+-----------+----------------+-------------+----------+--------+\n", "| PROP000001| 2024-10-07|Single Family| 757403.87|Downtown|\n", "| PROP000001| 2024-03-24|Single Family| 806427.68|Downtown|\n", "| PROP000001| 2024-02-21|Single Family| 597522.09|Downtown|\n", "+-----------+----------------+-------------+----------+--------+\n", "\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "Records found: 3)\n", "\n", "=== Query 2: Recent High-Value Transactions ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+----------------+-----------+-------------+----------+----------+\n", "|transaction_date|property_id|property_type|sale_price| location|\n", "+----------------+-----------+-------------+----------+----------+\n", "| 2024-06-19| PROP003631| Commercial| 6544467.3|Waterfront|\n", "| 2024-06-04| PROP007792| Commercial|6278450.24|Waterfront|\n", "| 2024-06-29| PROP007076| Commercial| 6236953.2|Waterfront|\n", "| 2024-06-30| PROP000596| Commercial| 6223248.0|Waterfront|\n", "| 2024-06-06| PROP006735| Commercial|5965073.15|Waterfront|\n", "| 2024-06-14| PROP004288| Commercial|5899989.06|Waterfront|\n", "| 2024-06-30| PROP000038| Commercial|5654482.84|Waterfront|\n", "| 2024-06-07| PROP004068| Commercial|5538127.68|Waterfront|\n", "| 2024-10-08| PROP003766| Commercial|5463452.56|Waterfront|\n", "| 2024-06-27| PROP001261| Commercial| 5399924.8|Waterfront|\n", "| 2024-06-01| PROP003919| Commercial| 5306833.6|Urban Core|\n", "| 2024-10-05| PROP003631| Commercial|5267238.69|Waterfront|\n", "| 2024-06-06| PROP006735| Commercial| 5251455.7|Waterfront|\n", "| 2024-09-03| PROP002323| Commercial|5241008.11|Waterfront|\n", "| 2024-06-29| PROP000855| Commercial|5126017.14|Waterfront|\n", "| 2024-11-13| PROP003766| Commercial| 5121544.5|Waterfront|\n", "| 2024-09-12| PROP001024| Commercial|5115495.33|Waterfront|\n", "| 2024-12-05| PROP005196| Commercial|5095875.48|Waterfront|\n", "| 2024-06-30| PROP006885| Commercial| 5083662.6|Urban Core|\n", "| 2024-10-03| PROP003531| Commercial|5060543.04|Waterfront|\n", "+----------------+-----------+-------------+----------+----------+\n", "only showing top 20 rows\n", "\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "High-value transactions found: 1717)\n", "\n", "=== Query 3: Property Value Trends ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+-----------+----------------+-------------+----------+--------------+\n", "|property_id|transaction_date|property_type|sale_price|price_per_sqft|\n", "+-----------+----------------+-------------+----------+--------------+\n", "| PROP000001| 2024-10-07|Single Family| 757403.87| 351.79|\n", "| PROP000002| 2024-06-15|Single Family| 523896.45| 236.95|\n", "| PROP000004| 2024-05-02|Single Family|1086423.42| 312.46|\n", "| PROP000004| 2024-12-24|Single Family| 815113.11| 234.43|\n", "| PROP000005| 2024-04-12| Apartment| 483395.85| 351.05|\n", "| PROP000007| 2024-07-15|Single Family| 686516.25| 261.53|\n", "| PROP000008| 2024-06-29| Condo| 950510.94| 505.86|\n", "| PROP000009| 2024-05-10| Apartment| 232365.5| 311.9|\n", "| PROP000009| 2024-09-04| Apartment| 189155.5| 253.9|\n", "| PROP000009| 2024-10-23| Apartment| 215901.0| 289.8|\n", "| PROP000010| 2024-09-11| Commercial|2769922.86| 335.22|\n", "| PROP000011| 2024-08-11| Condo| 897311.22| 565.77|\n", "| PROP000012| 2024-05-25|Single Family| 587905.44| 229.92|\n", "| PROP000013| 2024-05-19| Apartment| 748281.03| 672.31|\n", "| PROP000014| 2024-07-16| Condo| 449075.13| 243.93|\n", "| PROP000014| 2024-08-14| Condo| 433868.47| 235.67|\n", "| PROP000015| 2024-11-07| Condo| 758723.73| 457.89|\n", "| PROP000016| 2024-12-13| Condo| 557206.91| 393.23|\n", "| PROP000019| 2024-04-05| Commercial| 1564812.6| 354.03|\n", "| PROP000021| 2024-07-05| Condo| 880765.9| 535.42|\n", "+-----------+----------------+-------------+----------+--------------+\n", "only showing top 20 rows\n", "\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "Value trend records found: 1104)\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Demonstrate liquid clustering benefits with optimized queries\n", "\n", "\n", "# Query 1: Property transaction history - benefits from property_id clustering\n", "\n", "print(\"=== Query 1: Property Transaction History ===\")\n", "\n", "property_history = spark.sql(\"\"\"\n", "\n", "SELECT property_id, transaction_date, property_type, sale_price, location\n", "\n", "FROM real_estate.analytics.property_transactions\n", "\n", "WHERE property_id = 'PROP000001'\n", "\n", "ORDER BY transaction_date DESC\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "property_history.show()\n", "\n", "print(f\"Records found: {property_history.count()})\")\n", "\n", "\n", "\n", "# Query 2: Time-based high-value transaction analysis - benefits from transaction_date clustering\n", "\n", "print(\"\\n=== Query 2: Recent High-Value Transactions ===\")\n", "\n", "high_value = spark.sql(\"\"\"\n", "\n", "SELECT transaction_date, property_id, property_type, sale_price, location\n", "\n", "FROM real_estate.analytics.property_transactions\n", "\n", "WHERE transaction_date >= '2024-06-01' AND sale_price > 1000000\n", "\n", "ORDER BY sale_price DESC, transaction_date DESC\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "high_value.show()\n", "\n", "print(f\"High-value transactions found: {high_value.count()})\")\n", "\n", "\n", "\n", "# Query 3: Combined property + time query - optimal for our clustering strategy\n", "\n", "print(\"\\n=== Query 3: Property Value Trends ===\")\n", "\n", "value_trends = spark.sql(\"\"\"\n", "\n", "SELECT property_id, transaction_date, property_type, sale_price, price_per_sqft\n", "\n", "FROM real_estate.analytics.property_transactions\n", "\n", "WHERE property_id LIKE 'PROP000%' AND transaction_date >= '2024-04-01'\n", "\n", "ORDER BY property_id, transaction_date\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "value_trends.show()\n", "\n", "print(f\"Value trend records found: {value_trends.count()})\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Step 6: Analyze Clustering Effectiveness\n", "\n", "### Understanding the Impact\n", "\n", "Let's examine how liquid clustering has organized our data and analyze some aggregate statistics to demonstrate the real estate insights possible with this optimized structure.\n", "\n", "### Key Analytics\n", "\n", "- **Property value appreciation** and market performance\n", "- **Location-based pricing** and neighborhood analysis\n", "- **Property type trends** and market segmentation\n", "- **Market timing** and seasonal patterns" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "=== Property Value Analysis ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+-----------+------------------+--------------+--------------+--------------+------------------+-------------+----------+\n", "|property_id|total_transactions|min_sale_price|max_sale_price|avg_sale_price|avg_price_per_sqft|property_type| location|\n", "+-----------+------------------+--------------+--------------+--------------+------------------+-------------+----------+\n", "| PROP006849| 1| 6433466.97| 6433466.97| 6433466.97| 550.01| Commercial|Waterfront|\n", "| PROP002526| 1| 6338081.7| 6338081.7| 6338081.7| 541.3| Commercial|Waterfront|\n", "| PROP007076| 1| 6236953.2| 6236953.2| 6236953.2| 548.4| Commercial|Waterfront|\n", "| PROP004086| 1| 6048936.12| 6048936.12| 6048936.12| 535.02| Commercial|Waterfront|\n", "| PROP003631| 2| 5267238.69| 6544467.3| 5905853.0| 494.26| Commercial|Waterfront|\n", "| PROP004288| 1| 5899989.06| 5899989.06| 5899989.06| 552.33| Commercial|Waterfront|\n", "| PROP005351| 1| 5854844.82| 5854844.82| 5854844.82| 502.26| Commercial|Waterfront|\n", "| PROP006735| 3| 5251455.7| 6130748.15| 5782425.67| 523.53| Commercial|Waterfront|\n", "| PROP007792| 2| 5235709.76| 6278450.24| 5757080.0| 507.5| Commercial|Waterfront|\n", "| PROP000032| 1| 5680612.23| 5680612.23| 5680612.23| 518.73| Commercial|Waterfront|\n", "+-----------+------------------+--------------+--------------+--------------+------------------+-------------+----------+\n", "\n", "\n", "=== Location Market Analysis ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+--------------------+------------------+--------------+------------------+------------------+-----------------+\n", "| location|total_transactions|avg_sale_price|avg_price_per_sqft|avg_days_on_market|unique_properties|\n", "+--------------------+------------------+--------------+------------------+------------------+-----------------+\n", "| Waterfront| 1838| 1429878.15| 448.7| 60.88| 1288|\n", "| Urban Core| 1975| 1231699.93| 480.15| 60.26| 1373|\n", "| Downtown| 1857| 1041112.68| 389.81| 59.72| 1286|\n", "| Mountain View| 1876| 826320.39| 309.67| 61.02| 1338|\n", "|Residential District| 1949| 729886.77| 264.81| 59.57| 1361|\n", "| Suburban| 1958| 654614.89| 253.83| 57.33| 1354|\n", "+--------------------+------------------+--------------+------------------+------------------+-----------------+\n", "\n", "\n", "=== Property Type Market Trends ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+-------------+-----------+--------------+------------------+------------------+-----------------+\n", "|property_type|total_sales|avg_sale_price|avg_price_per_sqft|avg_days_on_market|unique_properties|\n", "+-------------+-----------+--------------+------------------+------------------+-----------------+\n", "| Commercial| 2244| 2403707.17| 363.58| 105.78| 1578|\n", "|Single Family| 2290| 865272.73| 301.28| 48.25| 1625|\n", "| Townhouse| 2303| 710431.48| 323.01| 48.1| 1617|\n", "| Condo| 2399| 539901.22| 386.65| 48.51| 1663|\n", "| Apartment| 2217| 424804.6| 412.12| 49.45| 1517|\n", "+-------------+-----------+--------------+------------------+------------------+-----------------+\n", "\n", "\n", "=== Market Timing Analysis ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+--------------------+-----------------+--------------+--------+---------------+\n", "| sale_speed|transaction_count|avg_sale_price|avg_days| total_volume|\n", "+--------------------+-----------------+--------------+--------+---------------+\n", "|Fast Sale (1-30 d...| 2580| 647319.13| 18.46|1.67008335987E9|\n", "|Normal Sale (31-6...| 3777| 833196.52| 45.4|3.14698327446E9|\n", "|Slow Sale (61-90 ...| 3727| 847142.28| 75.3|3.15729926552E9|\n", "|Very Slow Sale (9...| 1369| 2391647.98| 135.05|3.27416607939E9|\n", "+--------------------+-----------------+--------------+--------+---------------+\n", "\n", "\n", "=== Monthly Market Trends ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+-------+------------------+---------------+--------------+------------------+-----------------+\n", "| month|total_transactions| monthly_volume|avg_sale_price|avg_price_per_sqft|unique_properties|\n", "+-------+------------------+---------------+--------------+------------------+-----------------+\n", "|2024-01| 949| 8.0059691024E8| 843621.61| 314.25| 920|\n", "|2024-02| 874| 7.458980738E8| 853430.29| 315.36| 841|\n", "|2024-03| 927| 9.6453802836E8| 1040494.1| 402.79| 901|\n", "|2024-04| 964|1.07271778159E9| 1112777.78| 402.09| 927|\n", "|2024-05| 997|1.09480884955E9| 1098103.16| 397.14| 976|\n", "|2024-06| 960|1.12414774258E9| 1170987.23| 403.9| 924|\n", "|2024-07| 933| 8.9032841699E8| 954264.11| 346.99| 900|\n", "|2024-08| 966| 9.5167473954E8| 985170.54| 355.91| 940|\n", "|2024-09| 962| 9.4143005752E8| 978617.52| 355.79| 931|\n", "|2024-10| 1009| 9.8424454139E8| 975465.35| 352.82| 968|\n", "|2024-11| 938| 8.2807511832E8| 882809.29| 319.4| 907|\n", "|2024-12| 974| 8.5007171936E8| 872763.57| 315.17| 943|\n", "+-------+------------------+---------------+--------------+------------------+-----------------+\n", "\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Analyze clustering effectiveness and real estate insights\n", "\n", "\n", "# Property value analysis\n", "\n", "print(\"=== Property Value Analysis ===\")\n", "\n", "property_values = spark.sql(\"\"\"\n", "\n", "SELECT property_id, COUNT(*) as total_transactions,\n", "\n", " ROUND(MIN(sale_price), 2) as min_sale_price,\n", "\n", " ROUND(MAX(sale_price), 2) as max_sale_price,\n", "\n", " ROUND(AVG(sale_price), 2) as avg_sale_price,\n", "\n", " ROUND(AVG(price_per_sqft), 2) as avg_price_per_sqft,\n", "\n", " property_type, location\n", "\n", "FROM real_estate.analytics.property_transactions\n", "\n", "GROUP BY property_id, property_type, location\n", "\n", "ORDER BY avg_sale_price DESC\n", "\n", "LIMIT 10\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "property_values.show()\n", "\n", "\n", "# Location market analysis\n", "\n", "print(\"\\n=== Location Market Analysis ===\")\n", "\n", "location_analysis = spark.sql(\"\"\"\n", "\n", "SELECT location, COUNT(*) as total_transactions,\n", "\n", " ROUND(AVG(sale_price), 2) as avg_sale_price,\n", "\n", " ROUND(AVG(price_per_sqft), 2) as avg_price_per_sqft,\n", "\n", " ROUND(AVG(days_on_market), 2) as avg_days_on_market,\n", "\n", " COUNT(DISTINCT property_id) as unique_properties\n", "\n", "FROM real_estate.analytics.property_transactions\n", "\n", "GROUP BY location\n", "\n", "ORDER BY avg_sale_price DESC\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "location_analysis.show()\n", "\n", "\n", "# Property type market trends\n", "\n", "print(\"\\n=== Property Type Market Trends ===\")\n", "\n", "property_trends = spark.sql(\"\"\"\n", "\n", "SELECT property_type, COUNT(*) as total_sales,\n", "\n", " ROUND(AVG(sale_price), 2) as avg_sale_price,\n", "\n", " ROUND(AVG(price_per_sqft), 2) as avg_price_per_sqft,\n", "\n", " ROUND(AVG(days_on_market), 2) as avg_days_on_market,\n", "\n", " COUNT(DISTINCT property_id) as unique_properties\n", "\n", "FROM real_estate.analytics.property_transactions\n", "\n", "GROUP BY property_type\n", "\n", "ORDER BY avg_sale_price DESC\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "property_trends.show()\n", "\n", "\n", "# Market timing analysis\n", "\n", "print(\"\\n=== Market Timing Analysis ===\")\n", "\n", "market_timing = spark.sql(\"\"\"\n", "\n", "SELECT \n", "\n", " CASE \n", "\n", " WHEN days_on_market <= 30 THEN 'Fast Sale (1-30 days)'\n", "\n", " WHEN days_on_market <= 60 THEN 'Normal Sale (31-60 days)'\n", "\n", " WHEN days_on_market <= 90 THEN 'Slow Sale (61-90 days)'\n", "\n", " ELSE 'Very Slow Sale (90+ days)'\n", "\n", " END as sale_speed,\n", "\n", " COUNT(*) as transaction_count,\n", "\n", " ROUND(AVG(sale_price), 2) as avg_sale_price,\n", "\n", " ROUND(AVG(days_on_market), 2) as avg_days,\n", "\n", " ROUND(SUM(sale_price), 2) as total_volume\n", "\n", "FROM real_estate.analytics.property_transactions\n", "\n", "GROUP BY \n", "\n", " CASE \n", "\n", " WHEN days_on_market <= 30 THEN 'Fast Sale (1-30 days)'\n", "\n", " WHEN days_on_market <= 60 THEN 'Normal Sale (31-60 days)'\n", "\n", " WHEN days_on_market <= 90 THEN 'Slow Sale (61-90 days)'\n", "\n", " ELSE 'Very Slow Sale (90+ days)'\n", "\n", " END\n", "\n", "ORDER BY avg_days\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "market_timing.show()\n", "\n", "\n", "# Monthly market trends\n", "\n", "print(\"\\n=== Monthly Market Trends ===\")\n", "\n", "monthly_trends = spark.sql(\"\"\"\n", "\n", "SELECT DATE_FORMAT(transaction_date, 'yyyy-MM') as month,\n", "\n", " COUNT(*) as total_transactions,\n", "\n", " ROUND(SUM(sale_price), 2) as monthly_volume,\n", "\n", " ROUND(AVG(sale_price), 2) as avg_sale_price,\n", "\n", " ROUND(AVG(price_per_sqft), 2) as avg_price_per_sqft,\n", "\n", " COUNT(DISTINCT property_id) as unique_properties\n", "\n", "FROM real_estate.analytics.property_transactions\n", "\n", "GROUP BY DATE_FORMAT(transaction_date, 'yyyy-MM')\n", "\n", "ORDER BY month\n", "\n", "\"\"\")\n", "\n", "\n", "\n", "monthly_trends.show()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Step 7: Train Real Estate Price Prediction Model\n", "\n", "### Machine Learning for Real Estate Business Improvement\n", "\n", "Now we'll train a machine learning model to predict property sale prices. This model can help real estate companies:\n", "\n", "- **Predict market values** for pricing strategy optimization\n", "- **Identify undervalued properties** for investment opportunities\n", "- **Optimize listing prices** to maximize seller returns\n", "- **Provide market insights** for buyers and sellers\n", "\n", "### Model Approach\n", "\n", "We'll use a **Random Forest Regressor** to predict property sale prices based on:\n", "\n", "- Property characteristics (type, location, size)\n", "- Market conditions (seasonal factors, time-based trends)\n", "- Historical transaction patterns\n", "- Market timing and liquidity factors\n", "\n", "### Business Impact\n", "\n", "- **Pricing Optimization**: Better pricing strategies for faster sales\n", "- **Investment Decisions**: Data-driven property valuation\n", "- **Market Intelligence**: Competitive advantage through predictive analytics\n", "- **Revenue Growth**: Improved transaction success rates" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Created property features for 11453 transactions\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+-----------+----------------+-------------+----------+--------------------+--------------+--------------+-----------------+-------------------+-----------------------+--------------------+-------------+------------+\n", "|property_id|transaction_date|property_type|sale_price| location|days_on_market|price_per_sqft|transaction_month|transaction_quarter|transaction_day_of_week|spring_summer_season|winter_season|market_speed|\n", "+-----------+----------------+-------------+----------+--------------------+--------------+--------------+-----------------+-------------------+-----------------------+--------------------+-------------+------------+\n", "| PROP000001| 2024-03-24|Single Family| 806427.68| Downtown| 68| 374.56| 3| 1| 1| 1| 0| slow|\n", "| PROP000001| 2024-02-21|Single Family| 597522.09| Downtown| 53| 277.53| 2| 1| 4| 0| 1| normal|\n", "| PROP000001| 2024-10-07|Single Family| 757403.87| Downtown| 19| 351.79| 10| 4| 2| 0| 0| fast|\n", "| PROP000002| 2024-06-15|Single Family| 523896.45|Residential District| 56| 236.95| 6| 2| 7| 1| 0| normal|\n", "| PROP000003| 2024-02-15| Commercial| 3345272.0| Waterfront| 168| 364.25| 2| 1| 5| 0| 1| very_slow|\n", "+-----------+----------------+-------------+----------+--------------------+--------------+--------------+-----------------+-------------------+-----------------------+--------------------+-------------+------------+\n", "only showing top 5 rows\n", "\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Prepare data for machine learning - create property price prediction features\n", "\n", "from pyspark.ml.feature import StringIndexer, VectorAssembler, StandardScaler\n", "from pyspark.ml.regression import RandomForestRegressor\n", "from pyspark.ml.evaluation import RegressionEvaluator\n", "from pyspark.ml import Pipeline\n", "import pyspark.sql.functions as F\n", "\n", "# Create property-level features for price prediction\n", "property_features = spark.sql(\"\"\"\n", "SELECT \n", " property_id,\n", " transaction_date,\n", " property_type,\n", " sale_price,\n", " location,\n", " days_on_market,\n", " price_per_sqft,\n", " -- Market timing features\n", " MONTH(transaction_date) as transaction_month,\n", " QUARTER(transaction_date) as transaction_quarter,\n", " DAYOFWEEK(transaction_date) as transaction_day_of_week,\n", " -- Market conditions\n", " CASE WHEN MONTH(transaction_date) IN (3,4,5,6) THEN 1 ELSE 0 END as spring_summer_season,\n", " CASE WHEN MONTH(transaction_date) IN (11,12,1,2) THEN 1 ELSE 0 END as winter_season,\n", " -- Market speed indicators\n", " CASE WHEN days_on_market <= 30 THEN 'fast' \n", " WHEN days_on_market <= 60 THEN 'normal' \n", " WHEN days_on_market <= 90 THEN 'slow' \n", " ELSE 'very_slow' END as market_speed\n", "FROM real_estate.analytics.property_transactions\n", "\"\"\")\n", "\n", "print(f\"Created property features for {property_features.count()} transactions\")\n", "property_features.show(5)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Training set: 9254 transactions\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "Test set: 2199 transactions\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Feature engineering for price prediction\n", "\n", "# Create indexers for categorical features\n", "property_type_indexer = StringIndexer(inputCol=\"property_type\", outputCol=\"property_type_index\")\n", "location_indexer = StringIndexer(inputCol=\"location\", outputCol=\"location_index\")\n", "market_speed_indexer = StringIndexer(inputCol=\"market_speed\", outputCol=\"market_speed_index\")\n", "\n", "# Assemble features for the model\n", "feature_cols = [\"days_on_market\", \"price_per_sqft\", \"transaction_month\", \"transaction_quarter\", \n", " \"transaction_day_of_week\", \"spring_summer_season\", \"winter_season\", \n", " \"property_type_index\", \"location_index\", \"market_speed_index\"]\n", "\n", "assembler = VectorAssembler(\n", " inputCols=feature_cols,\n", " outputCol=\"features\"\n", ")\n", "\n", "# Scale features\n", "scaler = StandardScaler(inputCol=\"features\", outputCol=\"scaled_features\")\n", "\n", "# Create and train the model\n", "rf = RandomForestRegressor(\n", " labelCol=\"sale_price\", \n", " featuresCol=\"scaled_features\",\n", " numTrees=100,\n", " maxDepth=10\n", ")\n", "\n", "# Create pipeline\n", "pipeline = Pipeline(stages=[property_type_indexer, location_indexer, market_speed_indexer, assembler, scaler, rf])\n", "\n", "# Split data\n", "train_data, test_data = property_features.randomSplit([0.8, 0.2], seed=42)\n", "\n", "print(f\"Training set: {train_data.count()} transactions\")\n", "print(f\"Test set: {test_data.count()} transactions\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Training property price prediction model...\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "Model RMSE: $381,124.39\n", "Model R²: 0.8281\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+-----------+-------------+--------------------+----------+------------------+\n", "|property_id|property_type| location|sale_price| prediction|\n", "+-----------+-------------+--------------------+----------+------------------+\n", "| PROP000001|Single Family| Downtown| 757403.87| 916323.7333174192|\n", "| PROP000004|Single Family| Mountain View|1086423.42| 814643.688558861|\n", "| PROP000005| Apartment|Residential District| 462369.06|344412.41506012395|\n", "| PROP000009| Apartment| Suburban| 232365.5|339209.18379322864|\n", "| PROP000013| Apartment| Urban Core| 748281.03| 648853.1630104046|\n", "| PROP000015| Condo| Urban Core| 782683.95| 621021.3716854007|\n", "| PROP000019| Commercial| Mountain View| 1485473.6| 2235534.442801727|\n", "| PROP000023| Townhouse| Mountain View| 486548.68| 557960.266391651|\n", "| PROP000030|Single Family| Suburban| 743770.75| 597364.0121666738|\n", "| PROP000031|Single Family| Downtown| 972732.0| 1305673.767476966|\n", "+-----------+-------------+--------------------+----------+------------------+\n", "only showing top 10 rows\n", "\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+-----------+----------+------------------+------------------+--------------------+\n", "|property_id|sale_price| prediction| prediction_error|prediction_error_pct|\n", "+-----------+----------+------------------+------------------+--------------------+\n", "| PROP000001| 757403.87| 916323.7333174192| 158919.8633174192| 20.982182638889764|\n", "| PROP000004|1086423.42| 814643.688558861| 271779.7314411389| 25.01600448204061|\n", "| PROP000005| 462369.06|344412.41506012395|117956.64493987605| 25.511362057806387|\n", "| PROP000009| 232365.5|339209.18379322864|106843.68379322864| 45.980872286646964|\n", "| PROP000013| 748281.03| 648853.1630104046| 99427.86698959547| 13.287503358142791|\n", "| PROP000015| 782683.95| 621021.3716854007|161662.57831459923| 20.654898866215316|\n", "| PROP000019| 1485473.6| 2235534.442801727| 750060.8428017269| 50.493044292522384|\n", "| PROP000023| 486548.68| 557960.266391651| 71411.58639165101| 14.67717195156115|\n", "| PROP000030| 743770.75| 597364.0121666738|146406.73783332622| 19.684390362665678|\n", "| PROP000031| 972732.0| 1305673.767476966| 332941.7674769659| 34.22749199954005|\n", "+-----------+----------+------------------+------------------+--------------------+\n", "only showing top 10 rows\n", "\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Train the property price prediction model\n", "\n", "print(\"Training property price prediction model...\")\n", "model = pipeline.fit(train_data)\n", "\n", "# Make predictions\n", "predictions = model.transform(test_data)\n", "\n", "# Evaluate the model\n", "evaluator = RegressionEvaluator(labelCol=\"sale_price\", predictionCol=\"prediction\", metricName=\"rmse\")\n", "rmse = evaluator.evaluate(predictions)\n", "\n", "evaluator_r2 = RegressionEvaluator(labelCol=\"sale_price\", predictionCol=\"prediction\", metricName=\"r2\")\n", "r2 = evaluator_r2.evaluate(predictions)\n", "\n", "print(f\"Model RMSE: ${rmse:,.2f}\")\n", "print(f\"Model R²: {r2:.4f}\")\n", "\n", "# Show prediction results\n", "predictions.select(\"property_id\", \"property_type\", \"location\", \"sale_price\", \"prediction\").show(10)\n", "\n", "# Calculate prediction accuracy\n", "predictions_with_accuracy = predictions.withColumn(\n", " \"prediction_error\", \n", " F.abs(F.col(\"sale_price\") - F.col(\"prediction\"))\n", ").withColumn(\n", " \"prediction_error_pct\", \n", " F.abs(F.col(\"sale_price\") - F.col(\"prediction\")) / F.col(\"sale_price\") * 100\n", ")\n", "\n", "predictions_with_accuracy.select(\"property_id\", \"sale_price\", \"prediction\", \"prediction_error\", \"prediction_error_pct\").show(10)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "=== Feature Importance for Price Prediction ===\n", "days_on_market: 0.1554\n", "price_per_sqft: 0.1510\n", "transaction_month: 0.0162\n", "transaction_quarter: 0.0057\n", "transaction_day_of_week: 0.0166\n", "spring_summer_season: 0.0087\n", "winter_season: 0.0048\n", "property_type_index: 0.3887\n", "location_index: 0.0628\n", "market_speed_index: 0.1901\n", "\n", "=== Business Impact Analysis ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "Average prediction error: $224,089\n", "Average prediction error percentage: 23.06%\n", "Median prediction error percentage: 18.48%\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "\n", "Estimated value of 1% price optimization: $21,817,600\n", "\n", "=== Seasonal Prediction Performance ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+--------------------+------------------+-----------------+\n", "|spring_summer_season| avg_error_pct|transaction_count|\n", "+--------------------+------------------+-----------------+\n", "| 0|22.980314545347937| 1471|\n", "| 1|23.211318379074374| 728|\n", "+--------------------+------------------+-----------------+\n", "\n", "\n", "=== Property Type Prediction Performance ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+-------------+------------------+-----------------+\n", "|property_type| avg_error_pct|transaction_count|\n", "+-------------+------------------+-----------------+\n", "|Single Family|17.728367455144397| 451|\n", "| Townhouse| 18.17706464360844| 454|\n", "| Condo|23.799995270422027| 436|\n", "| Apartment| 25.11799341558691| 425|\n", "| Commercial|30.951631099714014| 433|\n", "+-------------+------------------+-----------------+\n", "\n", "\n", "=== Location Prediction Performance ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+--------------------+------------------+-----------------+\n", "| location| avg_error_pct|transaction_count|\n", "+--------------------+------------------+-----------------+\n", "|Residential District|22.094311927352077| 376|\n", "| Mountain View| 22.14760592467116| 372|\n", "| Waterfront|22.579055181882183| 351|\n", "| Downtown|23.344850130414347| 375|\n", "| Suburban| 23.66362669208197| 371|\n", "| Urban Core|24.567059652549364| 354|\n", "+--------------------+------------------+-----------------+\n", "\n", "\n", "=== Model Confidence Analysis ===\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "+---------------------+-----+\n", "|prediction_confidence|count|\n", "+---------------------+-----+\n", "| High| 291|\n", "| Low| 1633|\n", "| Medium| 275|\n", "+---------------------+-----+\n", "\n", "\n", "Model Summary:\n", "RMSE: $381,124\n", "R² Score: 0.8281\n", "Median Error: 18.48%\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Model interpretation and business insights\n", "\n", "# Feature importance (approximate)\n", "rf_model = model.stages[-1]\n", "feature_importance = rf_model.featureImportances\n", "feature_names = feature_cols\n", "\n", "print(\"=== Feature Importance for Price Prediction ===\")\n", "for name, importance in zip(feature_names, feature_importance):\n", " print(f\"{name}: {importance:.4f}\")\n", "\n", "# Business impact analysis\n", "print(\"\\n=== Business Impact Analysis ===\")\n", "\n", "# Calculate prediction accuracy metrics\n", "avg_prediction_error = predictions_with_accuracy.agg(F.avg(\"prediction_error\")).collect()[0][0]\n", "avg_prediction_error_pct = predictions_with_accuracy.agg(F.avg(\"prediction_error_pct\")).collect()[0][0]\n", "median_error_pct = predictions_with_accuracy.approxQuantile(\"prediction_error_pct\", [0.5], 0.01)[0]\n", "\n", "print(f\"Average prediction error: ${avg_prediction_error:,.0f}\")\n", "print(f\"Average prediction error percentage: {avg_prediction_error_pct:.2f}%\")\n", "print(f\"Median prediction error percentage: {median_error_pct:.2f}%\")\n", "\n", "# Calculate potential value for pricing optimization\n", "total_test_properties = test_data.count()\n", "avg_property_value = test_data.agg(F.avg(\"sale_price\")).collect()[0][0]\n", "\n", "# Estimate potential value of better pricing (assuming 1% improvement in sale price)\n", "price_optimization_value = total_test_properties * avg_property_value * 0.01\n", "\n", "print(f\"\\nEstimated value of 1% price optimization: ${price_optimization_value:,.0f}\")\n", "\n", "# Market timing insights\n", "seasonal_performance = predictions_with_accuracy.groupBy(\"spring_summer_season\").agg(\n", " F.avg(\"prediction_error_pct\").alias(\"avg_error_pct\"),\n", " F.count(\"*\").alias(\"transaction_count\")\n", ").orderBy(\"spring_summer_season\")\n", "\n", "print(\"\\n=== Seasonal Prediction Performance ===\")\n", "seasonal_performance.show()\n", "\n", "# Property type performance\n", "property_type_performance = predictions_with_accuracy.groupBy(\"property_type\").agg(\n", " F.avg(\"prediction_error_pct\").alias(\"avg_error_pct\"),\n", " F.count(\"*\").alias(\"transaction_count\")\n", ").orderBy(\"avg_error_pct\")\n", "\n", "print(\"\\n=== Property Type Prediction Performance ===\")\n", "property_type_performance.show()\n", "\n", "# Location performance\n", "location_performance = predictions_with_accuracy.groupBy(\"location\").agg(\n", " F.avg(\"prediction_error_pct\").alias(\"avg_error_pct\"),\n", " F.count(\"*\").alias(\"transaction_count\")\n", ").orderBy(\"avg_error_pct\")\n", "\n", "print(\"\\n=== Location Prediction Performance ===\")\n", "location_performance.show()\n", "\n", "# Model confidence analysis\n", "confidence_analysis = predictions_with_accuracy.withColumn(\n", " \"prediction_confidence\", \n", " F.when(F.col(\"prediction_error_pct\") <= 5, \"High\")\n", " .when(F.col(\"prediction_error_pct\") <= 10, \"Medium\")\n", " .otherwise(\"Low\")\n", ").groupBy(\"prediction_confidence\").count().orderBy(\"prediction_confidence\")\n", "\n", "print(\"\\n=== Model Confidence Analysis ===\")\n", "confidence_analysis.show()\n", "\n", "print(f\"\\nModel Summary:\")\n", "print(f\"RMSE: ${rmse:,.0f}\")\n", "print(f\"R² Score: {r2:.4f}\")\n", "print(f\"Median Error: {median_error_pct:.2f}%\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Key Takeaways: Delta Liquid Clustering + ML in AIDP\n", "\n", "### What We Demonstrated\n", "\n", "1. **Automatic Optimization**: Created a table with `CLUSTER BY (property_id, transaction_date)` and let Delta automatically optimize data layout\n", "\n", "2. **Performance Benefits**: Queries on clustered columns (property_id, transaction_date) are significantly faster due to data locality\n", "\n", "3. **Zero Maintenance**: No manual partitioning, bucketing, or Z-Ordering required - Delta handles it automatically\n", "\n", "4. **Machine Learning Integration**: Trained a property price prediction model using the optimized data\n", "\n", "5. **Real-World Use Case**: Real estate analytics where property valuation and market analysis are critical\n", "\n", "### AIDP Advantages\n", "\n", "- **Unified Analytics**: Seamlessly integrates data optimization with ML\n", "- **Governance**: Catalog and schema isolation for real estate data\n", "- **Performance**: Optimized for both analytical queries and ML training\n", "- **Scalability**: Handles real estate-scale data volumes effortlessly\n", "\n", "### Business Benefits for Real Estate\n", "\n", "1. **Pricing Optimization**: AI-driven pricing strategies for faster sales\n", "2. **Market Intelligence**: Predictive analytics for investment decisions\n", "3. **Competitive Advantage**: Superior market valuation accuracy\n", "4. **Revenue Growth**: Better pricing leading to higher transaction values\n", "5. **Risk Reduction**: Data-driven market timing and valuation\n", "\n", "### Best Practices for Real Estate Analytics\n", "\n", "1. **Choose clustering columns** based on your most common query patterns\n", "2. **Start with 1-4 columns** - too many can reduce effectiveness\n", "3. **Consider cardinality** - high-cardinality columns work best\n", "4. **Monitor and adjust** as query patterns evolve\n", "5. **Combine with ML** for predictive analytics and automation\n", "\n", "### Next Steps\n", "\n", "- Explore other AIDP ML features like AutoML\n", "- Try liquid clustering with different column combinations\n", "- Scale up to larger real estate datasets\n", "- Integrate with real MLS and appraisal systems\n", "- Deploy models for real-time property valuation\n", "\n", "This notebook demonstrates how Oracle AI Data Platform makes advanced real estate analytics accessible while maintaining enterprise-grade performance and governance." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.5" } }, "nbformat": 4, "nbformat_minor": 4 }