{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.14","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":83481,"databundleVersionId":9503589,"sourceType":"competition"}],"dockerImageVersionId":30761,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"!pip install duckdb --quiet\nimport duckdb\nprint(duckdb.__version__)","metadata":{"execution":{"iopub.status.busy":"2024-09-03T16:05:25.343287Z","iopub.execute_input":"2024-09-03T16:05:25.344413Z","iopub.status.idle":"2024-09-03T16:05:41.493395Z","shell.execute_reply.started":"2024-09-03T16:05:25.344355Z","shell.execute_reply":"2024-09-03T16:05:41.492047Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import duckdb\nimport json\nimport pandas as pd\n\n# Initialize a DuckDB connection in memory\ncon = duckdb.connect()\n\n# Load the training and test edges files directly into DuckDB\ntrain_edges_df = con.execute(\"SELECT * FROM parquet_scan('/kaggle/input/radio-direction-finding-challenge/training_edges.parquet')\").fetchdf()\ntest_edges_df = con.execute(\"SELECT * FROM parquet_scan('/kaggle/input/radio-direction-finding-challenge/test_edges.parquet')\").fetchdf()\n\n# Function to extract elevations from 'dem_path'\ndef extract_elevations(dem_path_str):\n    try:\n        dem_path = json.loads(dem_path_str)\n        if isinstance(dem_path, list) and len(dem_path) >= 2:\n            start_elevation = dem_path[0]['elevation'] if isinstance(dem_path[0], dict) else dem_path[0]\n            end_elevation = dem_path[-1]['elevation'] if isinstance(dem_path[-1], dict) else dem_path[-1]\n            return start_elevation, end_elevation\n        elif isinstance(dem_path, (int, float)):\n            return dem_path, dem_path\n        else:\n            return None, None\n    except (json.JSONDecodeError, TypeError):\n        return None, None\n\n# Apply the function to extract elevation differences for training and test datasets\ntrain_edges_df[['start_elevation', 'end_elevation']] = train_edges_df['dem_path'].apply(lambda x: pd.Series(extract_elevations(x)))\ntest_edges_df[['start_elevation', 'end_elevation']] = test_edges_df['dem_path'].apply(lambda x: pd.Series(extract_elevations(x)))\n\n# Register the dataframes in DuckDB\ncon.register('train_edges_df', train_edges_df)\ncon.register('test_edges_df', test_edges_df)\n\n# Define paths to your partitioned Parquet files\ntrain_path = '/kaggle/input/radio-direction-finding-challenge/training_beacons.parquet/'\ntest_path = '/kaggle/input/radio-direction-finding-challenge/test_beacons.parquet/'\n\n# Constants for FSPL\nfrequency_hz = 868e6  # Example: 868 MHz for LoRa in EU\nc = 3e8  # Speed of light in vacuum, m/s\nfour_pi_c = 4 * 3.141592653589793 / c\nmin_distance_km = 1e-6  # 1 mm to avoid log(0)\n\n# Aggregate data from the training dataset\ntrain_aggregates = con.execute(f\"\"\"\n    SELECT \n        ps.edge,\n        AVG(ps.witness.report_signal) AS rssi_mean,\n        STDDEV(ps.witness.report_signal) AS rssi_std,\n        MEDIAN(ps.witness.report_signal) AS rssi_median,\n        AVG(ps.witness.report_snr) AS snr_mean,\n        STDDEV(ps.witness.report_snr) AS snr_std,\n        MEDIAN(ps.witness.report_snr) AS snr_median,\n        MAX(ps.witness.report_snr) - MIN(ps.witness.report_snr) AS snr_range,\n        MAX(ps.witness.report_signal) - MIN(ps.witness.report_signal) AS rssi_range,\n        AVG(ps.witness.distance_km) AS distance_mean,\n        STDDEV(ps.witness.distance_km) AS distance_std,\n        MEDIAN(ps.witness.distance_km) AS distance_median,\n        AVG(20 * LOG10(CASE WHEN ps.witness.distance_km < {min_distance_km} THEN {min_distance_km} ELSE ps.witness.distance_km END * 1000) \n            + 20 * LOG10({frequency_hz}) \n            + 20 * LOG10({four_pi_c})) AS fspl_mean,\n        AVG(ps.beacon.report_txPower - (20 * LOG10(CASE WHEN ps.witness.distance_km < {min_distance_km} THEN {min_distance_km} ELSE ps.witness.distance_km END * 1000) \n            + 20 * LOG10({frequency_hz}) \n            + 20 * LOG10({four_pi_c}))) AS expected_rssi_mean,\n        AVG(ed.start_elevation - ed.end_elevation) AS elevation_difference\n    FROM parquet_scan('{train_path}/*/*.parquet') ps\n    LEFT JOIN train_edges_df ed ON ed.edge = ps.edge\n    GROUP BY ps.edge\n\"\"\").fetchdf()\n\n# Aggregate data from the test dataset\ntest_aggregates = con.execute(f\"\"\"\n    SELECT \n        ps.edge,\n        AVG(ps.witness.report_signal) AS rssi_mean,\n        STDDEV(ps.witness.report_signal) AS rssi_std,\n        MEDIAN(ps.witness.report_signal) AS rssi_median,\n        AVG(ps.witness.report_snr) AS snr_mean,\n        STDDEV(ps.witness.report_snr) AS snr_std,\n        MEDIAN(ps.witness.report_snr) AS snr_median,\n        MAX(ps.witness.report_snr) - MIN(ps.witness.report_snr) AS snr_range,\n        MAX(ps.witness.report_signal) - MIN(ps.witness.report_signal) AS rssi_range,\n        AVG(ed.start_elevation - ed.end_elevation) AS elevation_difference\n    FROM parquet_scan('{test_path}/*/*.parquet') ps\n    LEFT JOIN test_edges_df ed ON ed.edge = ps.edge\n    GROUP BY ps.edge\n\"\"\").fetchdf()\n\n# Merge the aggregates with the original edges dataframes\ntrain_edges_df = train_edges_df.merge(train_aggregates, how='left', on='edge')\ntest_edges_df = test_edges_df.merge(test_aggregates, how='left', on='edge')\n\n# Save the updated dataframes to new Parquet files\ncon.register('train_edges_df', train_edges_df)\ncon.register('test_edges_df', test_edges_df)\n\ncon.execute(\"COPY train_edges_df TO 'updated_training_edges.parquet' (FORMAT 'parquet')\")\ncon.execute(\"COPY test_edges_df TO 'updated_test_edges.parquet' (FORMAT 'parquet')\")\n\n# Display the first few rows to confirm the merge\nprint(train_edges_df.head())\nprint(test_edges_df.head())\n","metadata":{"execution":{"iopub.status.busy":"2024-09-03T16:05:41.495806Z","iopub.execute_input":"2024-09-03T16:05:41.496163Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandas as pd\nfrom sklearn.ensemble import RandomForestRegressor\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.metrics import mean_squared_error\nimport joblib\n\n# Load the training data\ntrain_edges_df = pd.read_parquet('/kaggle/input/radio-direction-finding-challenge/training_edges.parquet')\n\n# Select features for predicting distance\ndistance_features = [\n    'rssi_mean', 'rssi_std', 'rssi_median', 'snr_mean', 'snr_std', 'snr_median',\n    'snr_range', 'rssi_range', 'elevation_difference'\n]\n\n# Target variable is distance_km\ndistance_target = 'distance_km'\n\n# Drop rows with missing values\ntrain_edges_df = train_edges_df.dropna(subset=distance_features + [distance_target])\n\n# Split the data into training and validation sets\nX = train_edges_df[distance_features]\ny = train_edges_df[distance_target]\nX_train, X_val, y_train, y_val = train_test_split(X, y, test_size=0.2, random_state=42)\n\n# Train the model\ndistance_model = RandomForestRegressor(n_estimators=100, random_state=42)\ndistance_model.fit(X_train, y_train)\n\n# Evaluate the model\ny_pred = distance_model.predict(X_val)\nmse = mean_squared_error(y_val, y_pred)\nrmse = mse ** 0.5\nprint(f'Distance Estimation Validation RMSE: {rmse}')\n\n# Save the trained distance model\njoblib.dump(distance_model, 'distance_model.pkl')\n","metadata":{"execution":{"iopub.status.busy":"2024-09-03T15:37:57.869446Z","iopub.execute_input":"2024-09-03T15:37:57.869859Z","iopub.status.idle":"2024-09-03T15:38:06.564174Z","shell.execute_reply.started":"2024-09-03T15:37:57.869821Z","shell.execute_reply":"2024-09-03T15:38:06.562476Z"},"trusted":true},"execution_count":null,"outputs":[]}]}