{"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":"none","dataSources":[{"sourceId":105399,"databundleVersionId":12733338,"sourceType":"competition"}],"dockerImageVersionId":31040,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"Published on June 21, 2025. By Prata, Marília (mpwolke)","metadata":{}},{"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)\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\n#Two lines Required to Plot Plotly\nimport plotly.io as pio\npio.renderers.default = 'iframe'\n\nimport plotly.graph_objs as go\nimport plotly.offline as py\nimport plotly.express as px\n\n#Ignore warnings\nimport warnings\nwarnings.filterwarnings('ignore')\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-06-21T22:45:00.02952Z","iopub.execute_input":"2025-06-21T22:45:00.029824Z","iopub.status.idle":"2025-06-21T22:45:02.530987Z","shell.execute_reply.started":"2025-06-21T22:45:00.029803Z","shell.execute_reply":"2025-06-21T22:45:02.530182Z"},"_kg_hide-input":true,"_kg_hide-output":true},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"![](https://kaggle.com/competitions/105399/images/header)","metadata":{}},{"cell_type":"markdown","source":"# Competition Citation\n\n@misc{aeroclub-recsys-2025,\n\n    author = {Aeroclub IT},\n    \n    title = {FlightRank 2025: Aeroclub RecSys Cup},\n    year = {2025},\n    \n    howpublished = {\\url{https://kaggle.com/competitions/aeroclub-recsys-2025}},\n    note = {Kaggle}\n}","metadata":{}},{"cell_type":"markdown","source":"## Competition description\n\n**The Challenge**\n\n\"The dataset contains real flight search sessions with various attributes including pricing, timing, route information, user features and booking policies. The key technical challenge lies in ranking flight options to identify the most suitable choices for each business traveler on their specific route and circumstances. This becomes particularly complex as the number of available options can vary dramatically - from a handful of alternatives on smaller routes to thousands of possibilities on major trunk routes. Your model must effectively rank this entire spectrum of options to enhance the user experience by accurately identifying which flights best match traveler preferences.\"\n\n**Why This Matters**\n\n\"Flight recommendation systems power major travel platforms serving millions of business travelers. Accurate ranking models can significantly improve user experience by surfacing relevant options faster, ultimately leading to higher conversion rates and customer satisfaction.\"\n\n\"Your model will be evaluated based on ranking quality - how well it places the actually selected flight at the top of each search session's ranked list.\"\n\nhttps://www.kaggle.com/competitions/aeroclub-recsys-2025/overview","metadata":{}},{"cell_type":"markdown","source":"## My kernel died. Plan B: DuckDB \n\nYour notebook tried to allocate more memory than is available. It has restarted. That happened after the next snippet. Skip it!","metadata":{}},{"cell_type":"code","source":"#Read One parquet file. Obviously, it's big.\n\ntrain = pd.read_parquet(\"../input/aeroclub-recsys-2025/train.parquet\")\ntrain.tail()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-06-21T22:00:44.598423Z","iopub.execute_input":"2025-06-21T22:00:44.59875Z","execution_failed":"2025-06-21T22:01:32.788Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## Install DuckDB\n\nDuckDB offers a rich SQL dialect. It can read and write file formats such as CSV, Parquet, and JSON.","metadata":{}},{"cell_type":"code","source":"!pip install duckdb","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-06-21T22:45:56.217954Z","iopub.execute_input":"2025-06-21T22:45:56.21829Z","iopub.status.idle":"2025-06-21T22:46:00.612308Z","shell.execute_reply.started":"2025-06-21T22:45:56.218267Z","shell.execute_reply":"2025-06-21T22:46:00.611232Z"},"_kg_hide-output":true},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## Open this huge data: Only with Andrew's D. Blevins snippets\n\nThe Original was WHERE binds. We don't have binds. Use selected column. That information was on here: \n\nhttps://www.kaggle.com/competitions/aeroclub-recsys-2025/data\n\nTraining Data Target\n\nIn the training data, the **selected** column is binary:\n\n1 = This flight was chosen by the traveler\n0 = This flight was not chosen","metadata":{}},{"cell_type":"code","source":"#By Andrew D. Blevins https://www.kaggle.com/code/andrewdblevins/leash-tutorial-ecfps-and-random-forest\n\nimport duckdb\nimport pandas as pd\n\ntrain_path = '/kaggle/input/aeroclub-recsys-2025/train.parquet'\ntest_path = '/kaggle/input/aeroclub-recsys-2025/test.parquet'\n\ncon = duckdb.connect()\n\ndf = con.query(f\"\"\"(SELECT *\n                        FROM parquet_scan('{train_path}')\n                        WHERE selected = 0\n                        ORDER BY random()\n                        LIMIT 30000)\n                        UNION ALL\n                        (SELECT *\n                        FROM parquet_scan('{train_path}')\n                        WHERE selected = 1\n                        ORDER BY random()\n                        LIMIT 30000)\"\"\").df()\n\ncon.close()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-06-21T22:46:10.060927Z","iopub.execute_input":"2025-06-21T22:46:10.061267Z","iopub.status.idle":"2025-06-21T22:46:27.286029Z","shell.execute_reply.started":"2025-06-21T22:46:10.061239Z","shell.execute_reply":"2025-06-21T22:46:27.285255Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"# Voilà Le Parquet file opened with DuckDB and SQL\n\nSome of the Columns:\n\nId - Unique identifier for each flight option\n\nranker_id - Group identifier for each search session (key grouping variable for ranking)\n\nprofileId - User identifier\n\ncompanyID - Company identifier\n\nsex - User gender\n\nnationality - User nationality/citizenship\n\nfrequentFlyer - Frequent flyer program status\n\nisVip - VIP status indicator\n\nbySelf - Whether user books flights independently\n\nisAccess3D - Binary marker for internal feature\n\nFlight Timing and Duration\n\nlegs0_departureAt - Departure time for outbound flight\n\nlegs0_arrivalAt - Arrival time for outbound flight\n\nlegs0_duration - Duration of outbound flight\n\nlegs1_departureAt - Departure time for return flight\n\nlegs1_arrivalAt - Arrival time for return flight\n\nlegs1_duration - Duration of return flight\n\nhttps://www.kaggle.com/competitions/aeroclub-recsys-2025/data?select=jsons_structure.md","metadata":{}},{"cell_type":"code","source":"df.tail()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-06-21T22:24:51.153541Z","iopub.execute_input":"2025-06-21T22:24:51.153911Z","iopub.status.idle":"2025-06-21T22:24:51.201255Z","shell.execute_reply.started":"2025-06-21T22:24:51.153888Z","shell.execute_reply":"2025-06-21T22:24:51.200327Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## Data description","metadata":{}},{"cell_type":"code","source":"#By Iqbal Syah Akbar https://www.kaggle.com/code/iqbalsyahakbar/ps3e22-multi-class-classification-for-beginners\n\ndesc = pd.DataFrame(index = list(df))\ndesc['count'] = df.count()\ndesc['nunique'] = df.nunique()\ndesc['%unique'] = desc['nunique'] / len(df) * 100\ndesc['null'] = df.isnull().sum()\ndesc['type'] = df.dtypes\ndesc = pd.concat([desc, df.describe().T], axis = 1)\ndesc","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-06-21T23:33:40.942356Z","iopub.execute_input":"2025-06-21T23:33:40.943179Z","iopub.status.idle":"2025-06-21T23:33:41.523326Z","shell.execute_reply.started":"2025-06-21T23:33:40.943145Z","shell.execute_reply":"2025-06-21T23:33:41.522407Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## info() method","metadata":{}},{"cell_type":"code","source":"df.info()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-06-21T22:34:27.74159Z","iopub.execute_input":"2025-06-21T22:34:27.742055Z","iopub.status.idle":"2025-06-21T22:34:27.771876Z","shell.execute_reply.started":"2025-06-21T22:34:27.742025Z","shell.execute_reply":"2025-06-21T22:34:27.770871Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## Missing values Percent","metadata":{}},{"cell_type":"code","source":"# Check if there are any missing values left\ntrain_na = (df.isnull().sum() / len(df)) * 100\ntrain_na = train_na.drop(train_na[train_na == 0].index).sort_values(ascending=False)\nmissing_data = pd.DataFrame({'Missing Ratio' :train_na})\nmissing_data.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-06-21T22:56:01.385334Z","iopub.execute_input":"2025-06-21T22:56:01.385627Z","iopub.status.idle":"2025-06-21T22:56:01.517167Z","shell.execute_reply.started":"2025-06-21T22:56:01.385605Z","shell.execute_reply":"2025-06-21T22:56:01.516148Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## Boolean features\n\nI tried **hasAssistant** and **isGlobal** though they didn't appear on my dataframe (input 7). Maybe only on json file (JSON Structure Documentation - FlightRank 2025)\n\npersonalData - User Data\n{\n  \"$id\": \"string\",              // Service ID\n  \"profileId\": integer,         // User ID\n  **\"sex\": boolean,**               // Gender (true/false)\n  **\"bySelf\": boolean,**            // Self booking\n  \"yearOfBirth\": integer,       // Birth year\n  \"nationality\": integer,       // Nationality code\n  \"companyID\": integer,         // Company ID\n  \"isVip\": boolean,             // VIP status\n  **\"hasAssistant\": boolean,**      // Has assistant\n  **\"isGlobal\": boolean,**          // Global status\n  \"frequentFlyer\": string,      // Frequent flyer programs (e.g., \"SU/S7\")\n  \"position\": null,             // Position (usually null)\n  \"pointOfSale\": null","metadata":{}},{"cell_type":"code","source":"#https://stackoverflow.com/questions/43816122/how-to-represent-boolean-data-in-graph\n\n# Set up a grid of plots\nfig = plt.figure(figsize=(10,10)) \nfig_dims = (3, 2)\n\n\n# Plot accidents depending on type\nplt.subplot2grid(fig_dims, (0, 0))\ndf['sex'].value_counts().plot(kind='bar', \n                                     title='Gender')\nplt.subplot2grid(fig_dims, (0, 1))\ndf['bySelf'].value_counts().plot(kind='bar', \n                                     title='Travel by Self')\nplt.subplot2grid(fig_dims, (1, 0))\ndf['isVip'].value_counts().plot(kind='bar', \n                                     title='Vip')\nplt.subplot2grid(fig_dims, (1, 1))\ndf['selected'].value_counts().plot(kind='bar', \n                                     title='selected');","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-06-21T23:47:38.0352Z","iopub.execute_input":"2025-06-21T23:47:38.03551Z","iopub.status.idle":"2025-06-21T23:47:38.529594Z","shell.execute_reply.started":"2025-06-21T23:47:38.035488Z","shell.execute_reply":"2025-06-21T23:47:38.528734Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"#### I had No idea what is Sex/Gender true or false.","metadata":{}},{"cell_type":"markdown","source":"## Histograms","metadata":{}},{"cell_type":"code","source":"#https://stackoverflow.com/questions/64791405/log-scale-for-multiple-subplot-histograms-in-pandas\n\n# no need to initiate `fig,ax` to avoid the warning\naxes = df.hist(bins=25, figsize=(20,30), layout=(-1, 4), edgecolor=\"black\")\nplt.tight_layout()\n#plt.yscale('log') or xscale('log') Both didn't help\n\n# set log scale\n#for a in axes.ravel(): a.set_yscale('log')#xscale didn't change the size of the bars","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-06-22T00:16:07.429737Z","iopub.execute_input":"2025-06-22T00:16:07.430029Z","iopub.status.idle":"2025-06-22T00:16:18.613788Z","shell.execute_reply.started":"2025-06-22T00:16:07.430009Z","shell.execute_reply":"2025-06-22T00:16:18.612921Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### Selected (chosen by the traveler)\n\nIn the training data, the selected column is binary:\n\n1 = This flight was chosen by the traveler\n\n0 = This flight was not chosen\n\nhttps://www.kaggle.com/competitions/aeroclub-recsys-2025/data\n\n**Target Variable**\n\nselected - In training data: binary variable (0 = not selected, 1 = selected). \n\nIn submission: ranks within ranker_id groups","metadata":{}},{"cell_type":"code","source":"plt.scatter(df[\"totalPrice\"], df[\"selected\"], color=\"yellow\", edgecolor=\"black\")\nplt.ylabel(\"Selected\")\nplt.xlabel(\"Total Price\")\nplt.title(\"Relationship between Total Price vs Selected flight\", fontsize=14)\n#plt.xscale('log')\n#plt.yscale('log')\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-06-22T00:34:39.055825Z","iopub.execute_input":"2025-06-22T00:34:39.056313Z","iopub.status.idle":"2025-06-22T00:34:39.309629Z","shell.execute_reply.started":"2025-06-22T00:34:39.056291Z","shell.execute_reply":"2025-06-22T00:34:39.308894Z"},"_kg_hide-input":true},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## Relationship between Selected flights and their Price\n\nBoth scatter and regplot didn't provide a helpful insight.","metadata":{}},{"cell_type":"code","source":"#By Karnika Kapoor https://www.kaggle.com/code/karnikakapoor/diamond-price-prediction\n\nax = sns.regplot(x=\"totalPrice\", y=\"selected\", data=df, fit_reg=True, scatter_kws={\"color\": \"#006400\"}, line_kws={\"color\": \"#FF1493\"})\nax.set_title(\"Regression Line on Total Price vs Selected flight\", color=\"#4e4c39\");","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-06-22T00:47:54.371217Z","iopub.execute_input":"2025-06-22T00:47:54.371502Z","iopub.status.idle":"2025-06-22T00:47:56.999365Z","shell.execute_reply.started":"2025-06-22T00:47:54.371483Z","shell.execute_reply":"2025-06-22T00:47:56.998432Z"},"_kg_hide-input":true},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## Pearson correlation\n\nThey didn't seem very correlated : (","metadata":{}},{"cell_type":"code","source":"from scipy import stats\nfrom scipy.stats import ttest_ind\nfrom scipy.stats import pearsonr\n\ndf.plot(\"selected\",\"totalPrice\",style='o') \nprint(\"Pearson correlation:\",df[\"selected\"].corr(df[\"totalPrice\"]))\nprint(\"T Test and P value:\",stats.ttest_ind(df[\"selected\"],df[\"totalPrice\"]))","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-06-22T00:58:16.14371Z","iopub.execute_input":"2025-06-22T00:58:16.143973Z","iopub.status.idle":"2025-06-22T00:58:16.91322Z","shell.execute_reply.started":"2025-06-22T00:58:16.143957Z","shell.execute_reply":"2025-06-22T00:58:16.912226Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## In general, VIPs travel in private jets. So, they don't need to book Comercial flights. ","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=(7,2))\ndf['isVip'].value_counts().plot(kind='barh', color='black')\nplt.title('Is a Vip Passenger?');","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-06-22T01:00:52.863298Z","iopub.execute_input":"2025-06-22T01:00:52.863611Z","iopub.status.idle":"2025-06-22T01:00:52.990212Z","shell.execute_reply.started":"2025-06-22T01:00:52.863589Z","shell.execute_reply":"2025-06-22T01:00:52.989161Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### Draft Session: 2h:20m","metadata":{}},{"cell_type":"markdown","source":"### My own AeroClub. Am I Vip or Not?\n\n![](https://aerojota.com.br/wp-content/uploads/2025/04/Aeroclube-de-Marilia.jpg)Aero.Jota","metadata":{}},{"cell_type":"markdown","source":"#Acknowledgements:\n\nAndrew D. Blevins https://www.kaggle.com/code/andrewdblevins/leash-tutorial-ecfps-and-random-forest\n\nKarnika Kapoor https://www.kaggle.com/code/karnikakapoor/diamond-price-prediction\n\nIqbal Syah Akbar https://www.kaggle.com/code/iqbalsyahakbar/ps3e22-multi-class-classification-for-beginners\n\nMarília Prata https://www.kaggle.com/code/mpwolke/belka-pysmiles-duckdb/notebook\n\nMarília Prata https://www.kaggle.com/code/mpwolke/duckdb-sql-parquet/notebook","metadata":{}}]}