{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.11.11","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"nvidiaTeslaT4","dataSources":[{"sourceId":105399,"databundleVersionId":12733338,"sourceType":"competition"}],"dockerImageVersionId":31041,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":true}},"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":{"iopub.status.busy":"2025-07-05T22:35:20.096307Z","iopub.execute_input":"2025-07-05T22:35:20.096878Z","iopub.status.idle":"2025-07-05T22:35:20.103174Z","shell.execute_reply.started":"2025-07-05T22:35:20.096843Z","shell.execute_reply":"2025-07-05T22:35:20.102616Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"import os\n\n\ndata_path = '/kaggle/input/aeroclub-recsys-2025/'\n\nprint(os.listdir(data_path))","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-07-05T22:35:25.271052Z","iopub.execute_input":"2025-07-05T22:35:25.271439Z","iopub.status.idle":"2025-07-05T22:35:25.275625Z","shell.execute_reply.started":"2025-07-05T22:35:25.271418Z","shell.execute_reply":"2025-07-05T22:35:25.274822Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"import duckdb\nimport pandas as pd\nimport numpy as np\nimport os\n\nprint(\"Libraries loaded.\")\n\ndata_path = '/kaggle/input/aeroclub-recsys-2025/'\ntrain_parquet_path = data_path + 'train.parquet'\ntest_parquet_path = data_path + 'test.parquet'\n\ncon = duckdb.connect(database=':memory:', read_only=False)\nprint(\"DuckDB connection established successfully.\")\n\n\nrow_count_query = f\"SELECT COUNT(*) FROM '{train_parquet_path}'\"\ntotal_rows = con.execute(row_count_query).fetchone()[0]\nprint(f\"\\nTotal number of rows in the training set: {total_rows:,}\")\n\nschema_query = f\"DESCRIBE SELECT * FROM '{train_parquet_path}'\"\nschema_df = con.execute(schema_query).fetchdf()\nprint(\"\\nData Schema (Columns and Types):\")\ndisplay(schema_df)\n\nhead_query = f\"SELECT * FROM '{train_parquet_path}' LIMIT 5\"\ntrain_head_df = con.execute(head_query).fetchdf()\nprint(\"\\nFirst 5 sample rows from training data:\")\ndisplay(train_head_df)\n\nprint(\"\\nFirst look at data with DuckDB completed. No memory issues!\")","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-07-05T22:37:05.224171Z","iopub.execute_input":"2025-07-05T22:37:05.224423Z","iopub.status.idle":"2025-07-05T22:37:05.394862Z","shell.execute_reply.started":"2025-07-05T22:37:05.224403Z","shell.execute_reply":"2025-07-05T22:37:05.394074Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"print(\"Preparing query: Which are the most preferred airlines?\")\n\nquery = f\"\"\"\nSELECT\n    companyID,\n    COUNT(*) AS total_options,\n    SUM(selected) AS total_selections\nFROM '{train_parquet_path}'\nGROUP BY\n    companyID\nORDER BY\n    total_selections DESC\nLIMIT 20;\n\"\"\"\n\nprint(\"DuckDB runs the query...\")\ntop_airlines_df = con.execute(query).fetchdf()\nprint(\"The query is completed and the results are stored.\")\n\n\nepsilon = 1e-9\ntop_airlines_df['selection_rate_%'] = (top_airlines_df['total_selections'] / (top_airlines_df['total_options'] + epsilon)) * 100\n\nprint(\"\\nTop 20 Most Preferred Airlines:\")\ndisplay(top_airlines_df.sort_values(by='total_selections', ascending=False))","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-07-05T22:38:04.988895Z","iopub.execute_input":"2025-07-05T22:38:04.989389Z","iopub.status.idle":"2025-07-05T22:38:05.212853Z","shell.execute_reply.started":"2025-07-05T22:38:04.989364Z","shell.execute_reply":"2025-07-05T22:38:05.212253Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"import matplotlib.pyplot as plt\nimport seaborn as sns\nprint(\"Queries are being prepared: Price distributions of selected and unselected flights...\")\n\nquery_selected = f\"\"\"\nSELECT totalPrice\nFROM '{train_parquet_path}'\nWHERE selected = 1;\n\"\"\"\nselected_prices_df = con.execute(query_selected).fetchdf()\n\n\nquery_not_selected_sample = f\"\"\"\nSELECT totalPrice\nFROM '{train_parquet_path}'\nWHERE selected = 0 AND random() < 0.1;\n\"\"\"\nnot_selected_prices_df = con.execute(query_not_selected_sample).fetchdf()\n\nprint(\"Queries completed. Price data stored.\")\n\n\nprint(\"Creating scatter plot (histogram)...\")\n\nplt.figure(figsize=(14, 7))\n\nsns.histplot(not_selected_prices_df['totalPrice'], color='skyblue', label='Flights Not Selected (Sample 10%)', kde=True, log_scale=True)\n\nsns.histplot(selected_prices_df['totalPrice'], color='red', label='Selected Flights', kde=True, log_scale=True)\n\nplt.title('Price Distribution of Selected and Unselected Flights (Logarithmic Scale)', fontsize=16)\nplt.xlabel('Total Price - Logarithmic Scale', fontsize=12)\nplt.ylabel('Frequency (Number of Flights)', fontsize=12)\nplt.legend()\nplt.grid(True, which=\"both\", ls=\"--\", c='0.7')\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-07-05T22:39:33.329894Z","iopub.execute_input":"2025-07-05T22:39:33.330177Z","iopub.status.idle":"2025-07-05T22:39:44.202993Z","shell.execute_reply.started":"2025-07-05T22:39:33.330156Z","shell.execute_reply":"2025-07-05T22:39:44.202276Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"print(\"Queries are being prepared: Times in 'HH:MM:SS' format will be converted to minutes...\")\n\nduration_calculation = \"\"\"\n    (\n        COALESCE(EPOCH(CAST(legs0_segments0_duration AS INTERVAL)) / 60, 0) +\n        COALESCE(EPOCH(CAST(legs0_segments1_duration AS INTERVAL)) / 60, 0) +\n        COALESCE(EPOCH(CAST(legs0_segments2_duration AS INTERVAL)) / 60, 0) +\n        COALESCE(EPOCH(CAST(legs1_segments0_duration AS INTERVAL)) / 60, 0) +\n        COALESCE(EPOCH(CAST(legs1_segments1_duration AS INTERVAL)) / 60, 0) +\n        COALESCE(EPOCH(CAST(legs1_segments2_duration AS INTERVAL)) / 60, 0)\n    ) AS total_duration\n\"\"\"\n\nquery_selected_duration = f\"\"\"\nSELECT {duration_calculation}\nFROM '{train_parquet_path}'\nWHERE selected = 1;\n\"\"\"\nselected_duration_df = con.execute(query_selected_duration).fetchdf()\n\n\nquery_not_selected_duration = f\"\"\"\nSELECT {duration_calculation}\nFROM '{train_parquet_path}'\nWHERE selected = 0 AND random() < 0.1;\n\"\"\"\nnot_selected_duration_df = con.execute(query_not_selected_duration).fetchdf()\n\nprint(\"Queries completed. Flight time data stored.\")\n\n\nprint(\"Creating scatter plot (histogram)...\")\n\nplt.figure(figsize=(14, 7))\n\nsns.histplot(not_selected_duration_df['total_duration'], color='skyblue', label='Seçilmeyen Uçuşlar (Örneklem %10)', kde=True)\nsns.histplot(selected_duration_df['total_duration'], color='red', label='Seçilen Uçuşlar', kde=True)\n\nplt.title('Duration Distribution of Selected and Unselected Flights', fontsize=16)\nplt.xlabel('Calculated Total Flight Time (Minutes)', fontsize=12)\nplt.ylabel('Frequency (Number of Flights)', fontsize=12)\nplt.legend()\nplt.grid(True, which=\"both\", ls=\"--\", c='0.7')\nplt.xlim(0, 2000)\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-07-05T22:40:43.114531Z","iopub.execute_input":"2025-07-05T22:40:43.115453Z","iopub.status.idle":"2025-07-05T22:40:54.174735Z","shell.execute_reply.started":"2025-07-05T22:40:43.115425Z","shell.execute_reply":"2025-07-05T22:40:54.174094Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"print(\"Preparing test query: New features will be created...\")\n\nduration_calculation = \"\"\"\n    (\n        COALESCE(EPOCH(CAST(legs0_segments0_duration AS INTERVAL)) / 60, 0) +\n        COALESCE(EPOCH(CAST(legs0_segments1_duration AS INTERVAL)) / 60, 0) +\n        COALESCE(EPOCH(CAST(legs0_segments2_duration AS INTERVAL)) / 60, 0) +\n        COALESCE(EPOCH(CAST(legs1_segments0_duration AS INTERVAL)) / 60, 0) +\n        COALESCE(EPOCH(CAST(legs1_segments1_duration AS INTERVAL)) / 60, 0) +\n        COALESCE(EPOCH(CAST(legs1_segments2_duration AS INTERVAL)) / 60, 0)\n    )\n\"\"\"\n\nfeature_query = f\"\"\"\nWITH DataWithDuration AS (\n    SELECT *, {duration_calculation} AS total_duration\n    FROM '{train_parquet_path}'\n)\nSELECT\n    Id,\n    ranker_id,\n    companyID,\n    totalPrice,\n    total_duration,\n    selected,\n    -- YENİ ÖZELLİK 1: Grup içindeki fiyat sırası (en ucuz = 1)\n    RANK() OVER(PARTITION BY ranker_id ORDER BY totalPrice ASC) AS price_rank_in_group,\n    -- YENİ ÖZELLİK 2: Grup içindeki süre sırası (en kısa = 1)\n    RANK() OVER(PARTITION BY ranker_id ORDER BY total_duration ASC) AS duration_rank_in_group,\n    -- YENİ ÖZELLİK 3: Direkt uçuş mu? (1 = Evet, 0 = Hayır)\n    CAST(CASE WHEN legs0_segments1_duration IS NULL AND legs1_segments0_duration IS NULL THEN 1 ELSE 0 END AS TINYINT) AS is_direct_flight\n\nFROM DataWithDuration\n\"\"\"\n\ntest_ids = \"'ce0dabf6964640b63079fbafd42cbe', '4a26a333c5d64e999b8673a5a71141a3', '6e81f72744f445458066f774314bee4f'\"\ntest_query = f\"\"\"\n{feature_query}\nWHERE ranker_id IN ({test_ids})\nORDER BY ranker_id, price_rank_in_group;\n\"\"\"\n\nprint(\"Running test query for only 3 groups...\")\nfeatured_test_df = con.execute(test_query).fetchdf()\n\nprint(\"\\nTest Output with New Features Added:\")\ndisplay(featured_test_df)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-07-05T22:41:43.453914Z","iopub.execute_input":"2025-07-05T22:41:43.454844Z","iopub.status.idle":"2025-07-05T22:41:44.367559Z","shell.execute_reply.started":"2025-07-05T22:41:43.454809Z","shell.execute_reply":"2025-07-05T22:41:44.367021Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"print(\"For testing, 3 ranker_id are randomly taken from the dataset...\")\nget_ids_query = f\"SELECT DISTINCT ranker_id FROM '{train_parquet_path}' LIMIT 3;\"\ntest_ids_df = con.execute(get_ids_query).fetchdf()\ntest_ids_list = test_ids_df['ranker_id'].tolist()\ntest_ids_sql_format = \", \".join([f\"'{id_}'\" for id_ in test_ids_list])\nprint(f\"IDs selected for testing: {test_ids_list}\")\n\n\ntest_query = f\"\"\"\n{feature_query}\nWHERE ranker_id IN ({test_ids_sql_format})\nORDER BY ranker_id, price_rank_in_group;\n\"\"\"\n\nprint(\"\\nRunning test query with dynamic IDs...\")\nfeatured_test_df = con.execute(test_query).fetchdf()\n\nprint(\"\\nTest Output with New Features Added:\")\ndisplay(featured_test_df.head(15)) ","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-07-05T22:42:39.198042Z","iopub.execute_input":"2025-07-05T22:42:39.198322Z","iopub.status.idle":"2025-07-05T22:42:40.115042Z","shell.execute_reply.started":"2025-07-05T22:42:39.198301Z","shell.execute_reply":"2025-07-05T22:42:40.114452Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"print(\"\\n--- FULL VERSION LAUNCHED ---\")\nprint(\"Running feature engineering query for all training data...\")\nprint(\"This may take a few minutes, please wait...\")\n\nfull_featured_df = con.execute(feature_query).fetchdf()\n\nprint(\"\\nFeature engineering complete!\")\nprint(f\"Size of new dataset: {full_featured_df.shape}\")\n\noutput_path = \"train_featured.parquet\"\nprint(f\"processed data '{output_path}' saving to file...\")\nfull_featured_df.to_parquet(output_path)\n\nprint(\"\\nProcess completed! Now we can use the 'train_featured.parquet' file for modeling.\")\ndisplay(full_featured_df.head())","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-07-05T22:44:01.806907Z","iopub.execute_input":"2025-07-05T22:44:01.807513Z","iopub.status.idle":"2025-07-05T22:44:32.902160Z","shell.execute_reply.started":"2025-07-05T22:44:01.807486Z","shell.execute_reply":"2025-07-05T22:44:32.901414Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport lightgbm as lgb\nfrom sklearn.model_selection import GroupKFold\nimport gc\n\nprint(\"Libraries for modeling have been loaded.\")\n\ndf = pd.read_parquet('train_featured.parquet')\nprint(\"Feature engineered data 'train_featured.parquet' loaded.\")\nprint(f\"Data size: {df.shape}\")\n\n\ndef calculate_hitrate3(df_preds):\n    group_sizes = df_preds.groupby('ranker_id')['Id'].count()\n    valid_groups = group_sizes[group_sizes > 10].index\n    df_filtered = df_preds[df_preds['ranker_id'].isin(valid_groups)]\n    \n    if df_filtered.empty:\n        return 0.0\n\n    df_filtered['rank'] = df_filtered.groupby('ranker_id')['prediction'].rank(method='first', ascending=False)\n    correctly_selected = df_filtered[df_filtered['selected'] == 1]\n    hits = (correctly_selected['rank'] <= 3).sum()\n    hit_rate = hits / len(valid_groups.unique())\n    return hit_rate\n\n\nfeatures = [\n    'totalPrice', 'total_duration', 'price_rank_in_group',\n    'duration_rank_in_group', 'is_direct_flight', 'companyID'\n]\ntarget = 'selected'\ngroup_col = 'ranker_id'\n\nprint(f\"\\nFeatures to Use: {features}\")\n\ngkf = GroupKFold(n_splits=5)\ngroups = df[group_col]\n\nscores = []\n\nlgbm_ranker = lgb.LGBMRanker(\n    objective=\"lambdarank\", metric=\"ndcg\", n_estimators=2000,\n    learning_rate=0.05, random_state=42, n_jobs=-1,\n    colsample_bytree=0.8, device='gpu'\n)\n\nfor fold, (train_idx, val_idx) in enumerate(gkf.split(df, df[target], groups=groups)):\n    print(f\"\\n===== FOLD {fold+1} BAŞLADI =====\")\n    \n    train_fold_df = df.iloc[train_idx]\n    val_fold_df = df.iloc[val_idx]\n    \n    train_group = train_fold_df.groupby(group_col).size().to_numpy()\n    val_group = val_fold_df.groupby(group_col).size().to_numpy()\n    \n    X_train, y_train = train_fold_df[features], train_fold_df[target]\n    X_val, y_val = val_fold_df[features], val_fold_df[target]\n    \n    print(\"The model is being trained...\")\n    lgbm_ranker.fit(\n        X_train, y_train, group=train_group,\n        eval_set=[(X_val, y_val)], eval_group=[val_group],\n        eval_at=[3], callbacks=[lgb.early_stopping(100, verbose=False)]\n    )\n    \n    print(\"Predictions are being made...\")\n    val_predictions = lgbm_ranker.predict(X_val)\n    \n    val_preds_df = val_fold_df[['Id', 'ranker_id', 'selected']].copy()\n    val_preds_df['prediction'] = val_predictions\n    \n    score = calculate_hitrate3(val_preds_df)\n    scores.append(score)\n    print(f\"FOLD {fold+1} HitRate@3 Score: {score:.5f}\")\n    \n    del train_fold_df, val_fold_df, X_train, y_train, X_val, y_val, val_preds_df\n    gc.collect()\n    \nprint(\"\\n===== CROSS VERIFICATION COMPLETED =====\")\nprint(f\"Average HitRate@3 Score: {np.mean(scores):.5f} (+/- {np.std(scores):.5f})\")","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-07-05T22:46:19.389815Z","iopub.execute_input":"2025-07-05T22:46:19.390093Z","iopub.status.idle":"2025-07-06T00:55:09.235441Z","shell.execute_reply.started":"2025-07-05T22:46:19.390073Z","shell.execute_reply":"2025-07-06T00:55:09.234608Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"","metadata":{"trusted":true},"outputs":[],"execution_count":null}]}