{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.18","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"tpu1vmV38","dataSources":[{"sourceId":105399,"databundleVersionId":12733338,"sourceType":"competition"}],"dockerImageVersionId":31091,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"## Data import and preprocessing","metadata":{}},{"cell_type":"code","source":"import numpy as np \nimport pandas as pd ","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Load parquet files\ntrain = pd.read_parquet('/kaggle/input/aeroclub-recsys-2025/train.parquet')\ntest = pd.read_parquet('/kaggle/input/aeroclub-recsys-2025/test.parquet')\n\ntrain['purpose'] = 'train'\n\ntest['selected'] = 0\ntest['purpose'] = 'test'\n\ndf = pd.concat([train,test])\n\ndel(train,test)","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Create functions to calculate the number of segments in each leg\n\ndef flight_segment_num_leg0(row):\n    if pd.notna(row['legs0_segments3_flightNumber']):\n        return 4\n    elif pd.notna(row['legs0_segments2_flightNumber']):\n        return 3\n    elif pd.notna(row['legs0_segments1_flightNumber']):\n        return 2\n    elif pd.notna(row['legs0_segments0_flightNumber']):\n       return 1\n\ndef flight_segment_num_leg1(row):\n    if pd.notna(row['legs1_segments3_flightNumber']):\n        return 4\n    elif pd.notna(row['legs1_segments2_flightNumber']):\n        return 3\n    elif pd.notna(row['legs1_segments1_flightNumber']):\n        return 2\n    elif pd.notna(row['legs1_segments0_flightNumber']):\n        return 1\n    else:\n        return 0","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Craete features with number of segments in each leg\n\ndf['leg0_segment_num'] = df[['legs0_segments3_flightNumber',\n                                 'legs0_segments2_flightNumber',\n                                 'legs0_segments1_flightNumber',\n                                 'legs0_segments0_flightNumber']].apply(flight_segment_num_leg0, axis = 1)\n\ndf['leg1_segment_num'] = df[['legs1_segments3_flightNumber',\n                                 'legs1_segments2_flightNumber',\n                                 'legs1_segments1_flightNumber',\n                                 'legs1_segments0_flightNumber']].apply(flight_segment_num_leg1, axis = 1)","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Drop columns with 98.9%+ null values\n\nnull_col = df.columns[df.isna().sum()/len(df)>0.989]\n\ndf.drop(null_col, axis = 1, inplace = True)","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Convert to datetime format\ndf['legs0_arrivalAt'] = pd.to_datetime(df['legs0_arrivalAt'])\ndf['legs0_departureAt'] = pd.to_datetime(df['legs0_departureAt'])\ndf['legs1_arrivalAt'] = pd.to_datetime(df['legs1_arrivalAt'])\ndf['legs1_departureAt'] = pd.to_datetime(df['legs1_departureAt'])","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Fill nan values\nnum_cols = df.select_dtypes(include=['int64', 'float64']).columns.tolist()\ncat_cols = df.select_dtypes(include=['object']).columns.tolist()\n\ndf.loc[:,num_cols] = df.loc[:,num_cols].fillna(0)\ndf.loc[:,cat_cols] = df.loc[:,cat_cols].fillna('missing')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Downcast numeric columns format (64 to 32 bit)\nd1 = dict.fromkeys(df.select_dtypes(np.int64).columns, np.int32)\nd2 = dict.fromkeys(df.select_dtypes(np.float64).columns, np.float32)\n\ndf = df.astype(d1)\ndf = df.astype(d2)\ndf[cat_cols] = df[cat_cols].astype('category')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df['init_order'] = range(df.shape[0])\ndf['init_order'] = df['init_order'].astype('int32')\n\n# init_order_idx = df.index\n\ndf = df.sort_values(by = 'requestDate')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Cast 'object' columns considered to be categorical to 'category' data type\n\ndf['companyID'] = df['companyID'].astype('category')\ndf['corporateTariffCode'] = df['corporateTariffCode'].astype('category')\ndf['nationality'] = df['nationality'].astype('category')\n\nmeas_cols = df.columns[df.columns.str.contains('MeasurementType')].tolist()\n\nfor col in meas_cols:\n    df[col] = df[col].astype('category')\n\ndf['miniRules0_statusInfos'] = df['miniRules0_statusInfos'].astype('category')\ndf['miniRules1_statusInfos'] = df['miniRules1_statusInfos'].astype('category')\ndf['pricingInfo_isAccessTP'] = df['pricingInfo_isAccessTP'].astype('category')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## Feature engineering - carrier, baggage, cabin class, segments, date&time, duration and price features","metadata":{}},{"cell_type":"code","source":"%%time\n# check if each filled '..marketingCarrier_code' contains Carrier from 'frequentFlyer' carrier list\n\nmcc_cols = df.columns[df.columns.str.contains('marketingCarrier_code')].tolist()\n\nfor col in mcc_cols:\n    df[col+'_in_ff'] = [mcc in ff if mcc !='missing' else False for ff, mcc in zip(df['frequentFlyer'],df[col]) ]\n\n_in_ff_cols = df.columns[df.columns.str.contains('_in_ff')].tolist()\n\ndf['avg_in_ff'] = df[_in_ff_cols].where((df[mcc_cols].astype('str')!='missing').to_numpy()).mean(axis = 1).astype('float16')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Calc approximate baggage weight in kg for rows where it is measured in pieces count (using typical 23kg limit for 1 piece)\n\ndf['l_0_s_0_approx_bagg_weight'] = np.where(df['legs0_segments0_baggageAllowance_weightMeasurementType'] == 0,\n                                            df['legs0_segments0_baggageAllowance_quantity']*23,\n                                            df['legs0_segments0_baggageAllowance_quantity']).astype('int32')\n\ndf['l_1_s_0_approx_bagg_weight'] = np.where(df['legs1_segments0_baggageAllowance_weightMeasurementType'] == 0,\n                                            df['legs1_segments0_baggageAllowance_quantity']*23,\n                                            df['legs1_segments0_baggageAllowance_quantity'])\n\ndf['l_1_s_0_approx_bagg_weight'] = df['l_1_s_0_approx_bagg_weight'].fillna(0).astype('int32')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"%%time\n# Create additional date&time features\n\ndf['date_block_num'] = (df['requestDate'] - df['requestDate'].min()).dt.days.astype('int32')\n\ndf['requestDate_day'] = df['requestDate'].dt.day.astype('int32')\ndf['requestDate_dow'] = df['requestDate'].dt.day_name().astype('category')\ndf['requestDate_month'] = df['requestDate'].dt.month_name().astype('category')\n\ndf['days_till_flight'] = (df['legs0_departureAt'] - df['requestDate']).dt.days.astype('int32')\n\ndf['legs0_departureAt_day'] = df['legs0_departureAt'].dt.day.astype('int32')\ndf['legs0_departureAt_dow'] = df['legs0_departureAt'].dt.day_name().astype('category')\ndf['legs0_departureAt_month'] = df['legs0_departureAt'].dt.month_name().astype('category')\ndf['legs0_departureAt_hour'] = df['legs0_departureAt'].dt.hour.astype('int32')\n\ndf['legs0_arrivalAt_day'] = df['legs0_arrivalAt'].dt.day.astype('int32')\ndf['legs0_arrivalAt_dow'] = df['legs0_arrivalAt'].dt.day_name().astype('category')\ndf['legs0_arrivalAt_month'] = df['legs0_arrivalAt'].dt.month_name().astype('category')\ndf['legs0_arrivalAt_hour'] = df['legs0_arrivalAt'].dt.hour.astype('int32')\n\ndf['legs1_departureAt_day'] = df['legs1_departureAt'].dt.day.fillna(-1).astype('int32')\ndf['legs1_departureAt_dow'] = df['legs1_departureAt'].dt.day_name().fillna('missing').astype('category')\ndf['legs1_departureAt_month'] = df['legs1_departureAt'].dt.month_name().fillna('missing').astype('category')\ndf['legs1_departureAt_hour'] = df['legs1_departureAt'].dt.hour.fillna(-1).astype('int32')\n\ndf['legs1_arrivalAt_day'] = df['legs1_arrivalAt'].dt.day.fillna(-1).astype('int32')\ndf['legs1_arrivalAt_dow'] = df['legs1_arrivalAt'].dt.day_name().fillna('missing').astype('category')\ndf['legs1_arrivalAt_month'] = df['legs1_arrivalAt'].dt.month_name().fillna('missing').astype('category')\ndf['legs1_arrivalAt_hour'] = df['legs1_arrivalAt'].dt.hour.fillna(-1).astype('int32')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Create a manual (based on info from internet) rule for rating of flight hour of day\ndef depart_hour_rat(hour):\n    if hour in [23,0,1,2,3,4] : \n        return 1\n    elif hour in [5,6,7,21,22] :\n        return 2\n    elif hour in [8,12,13,14,15,20] :\n        return 3\n    elif hour == -1:\n        return -1\n    else :\n        return 4","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"%%time\ndf['legs0_departureAt_hour_rat'] = df['legs0_departureAt_hour'].apply(depart_hour_rat).astype('int32')\n# df['legs0_departureAt_hour_rat'].value_counts()\ndf['legs0_arrivalAt_hour_rat'] = df['legs0_arrivalAt_hour'].apply(depart_hour_rat).astype('int32')\ndf['legs1_departureAt_hour_rat'] = df['legs1_departureAt_hour'].apply(depart_hour_rat).astype('int32').fillna(-1)\ndf['legs1_arrivalAt_hour_rat'] = df['legs1_arrivalAt_hour'].apply(depart_hour_rat).astype('int32').fillna(-1)\n\n\ndf['hour_rat_avg'] = df[['legs0_departureAt_hour_rat',\n                        'legs0_arrivalAt_hour_rat',\n                        'legs1_departureAt_hour_rat',\n                        'legs1_arrivalAt_hour_rat']][df[['legs0_departureAt_hour_rat',\n                                                        'legs0_arrivalAt_hour_rat',\n                                                        'legs1_departureAt_hour_rat',\n                                                        'legs1_arrivalAt_hour_rat']] != -1].mean(axis = 1).astype('float32')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"%%time\n# Calc duration in minutes\n\ndur_cols = df.columns[df.columns.str.contains('duration')].tolist()\n\nfor col in dur_cols:\n    df[col+'_min'] = (pd.to_timedelta(df[col].astype('object').str.replace('.',' days '), \n                                      errors = 'coerce').dt.total_seconds().fillna(0)/60).astype('int32')\n","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Calc wait time in minutes\n\ndf['legs0_wait_time'] = df['legs0_duration_min'] - df['legs0_segments0_duration_min'] - df['legs0_segments1_duration_min']\ndf['legs1_wait_time'] = df['legs1_duration_min'] - df['legs1_segments0_duration_min'] - df['legs1_segments1_duration_min']","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df['tax_rate'] = df['taxes'] / df['totalPrice']\ndf['is_return'] = df['searchRoute'].astype('object').str.contains('/')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Calc the number of segments, total duration, wait time and cabin class characteristics\n\n#df['total_segments'] = df['leg0_segment_num'] + df['leg1_segment_num']\ndf['avg_segments'] = df[['leg0_segment_num',\n                         'leg1_segment_num']][df[['leg0_segment_num',\n                                                  'leg1_segment_num']]>0].mean(axis = 1).astype('float32')\n\ndf['total_dur_min'] = df['legs0_duration_min'] + df['legs1_duration_min']\ndf['seg_dur_rat'] = (df['legs0_duration_min']/df['legs1_duration_min']).replace([np.inf, -np.inf], 0).astype('float32')\ndf['total_wait_time'] = df['legs0_wait_time'] + df['legs1_wait_time']\n\ndf['avg_cabinClass'] = df[['legs0_segments0_cabinClass',\n                           'legs0_segments1_cabinClass',\n                           'legs1_segments0_cabinClass',\n                           'legs1_segments1_cabinClass']][df[['legs0_segments0_cabinClass',\n                                                               'legs0_segments1_cabinClass',\n                                                               'legs1_segments0_cabinClass',\n                                                               'legs1_segments1_cabinClass']]>0].mean(axis = 1)","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"%%time\n# Add Carrier rating features\n\noper_car_rat = pd.concat([df[(df['purpose'] != 'test')&(df['selected'] == 1)]['legs0_segments0_operatingCarrier_code'],\n                          df[(df['purpose'] != 'test')&(df['selected'] == 1)]['legs0_segments1_operatingCarrier_code'],\n                          df[(df['purpose'] != 'test')&(df['selected'] == 1)]['legs1_segments0_operatingCarrier_code'],\n                          df[(df['purpose'] != 'test')&(df['selected'] == 1)]['legs1_segments1_operatingCarrier_code']]).value_counts().to_frame().rename({'count':'rating'},\n                                                                                                                                                          axis = 1).drop(labels = 'missing', axis = 0)\n\nfor col in ['legs0_segments0_operatingCarrier_code',\n            'legs0_segments1_operatingCarrier_code',\n            'legs1_segments0_operatingCarrier_code',\n            'legs1_segments1_operatingCarrier_code']:\n    df[col+'_rating'] = df[[col]].merge(oper_car_rat, \n                                      how='left', \n                                      left_on = col, \n                                      right_index = True).rename({'rating':col+'_rating'}, axis = 1)[col+'_rating']\n    df[col+'_rating'] = df[col+'_rating'].fillna(0).astype('int32')\n\ndf['avg_operatingCarrier_code_rating'] = df[['legs0_segments0_operatingCarrier_code_rating',\n                                              'legs0_segments1_operatingCarrier_code_rating',\n                                              'legs1_segments0_operatingCarrier_code_rating',\n                                              'legs1_segments1_operatingCarrier_code_rating']][df[['legs0_segments0_operatingCarrier_code_rating',\n                                                                                                    'legs0_segments1_operatingCarrier_code_rating',\n                                                                                                    'legs1_segments0_operatingCarrier_code_rating',\n                                                                                                    'legs1_segments1_operatingCarrier_code_rating']]>0].mean(axis = 1).fillna(0).astype('float32')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df['final_dest_city'] = np.where(df['legs0_segments1_arrivalTo_airport_city_iata'] != 'missing',\n                                 df['legs0_segments1_arrivalTo_airport_city_iata'],\n                                 df['legs0_segments0_arrivalTo_airport_city_iata'])\n\ndf['final_dest_city'] = df['final_dest_city'].astype('category')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Create additional tables for route mean duration,price considering segments number, cabin class; route popularity\n\nroute_avg_dur = df[df['purpose'] != 'test'].groupby(['searchRoute',\n                                                      'legs0_segments0_departureFrom_airport_iata',\n                                                      'final_dest_city'],\n                                                     observed=True)['total_dur_min'].mean().to_frame().rename({'total_dur_min':'route_dur_avg'}, \n                                                                                                                axis = 1)\nroute_avg_dur_seg = df[df['purpose'] != 'test'].groupby(['searchRoute',\n                                                          'legs0_segments0_departureFrom_airport_iata',\n                                                          'final_dest_city',\n                                                          'leg0_segment_num',\n                                                          'leg1_segment_num'],\n                                                         observed=True)['total_dur_min'].mean().to_frame().rename({'total_dur_min':'route_seg_dur_avg'}, \n                                                                                                                   axis = 1)\nroute_cc_price = df[df['purpose'] != 'test'].groupby(['searchRoute',\n                                                       'legs0_segments0_departureFrom_airport_iata',\n                                                       'final_dest_city',\n                                                       'legs0_segments0_cabinClass',\n                                                        'legs0_segments1_cabinClass',\n                                                        'legs1_segments0_cabinClass',\n                                                        'legs1_segments1_cabinClass'],\n                                                       observed=True)['totalPrice'].mean().to_frame().rename({'totalPrice':'route_cc_avgPrice'}, \n                                                                                                            axis = 1)\nroute_popularity = df[(df['purpose'] != 'test')&(df['selected']==1)].groupby(['searchRoute'],\n                                                          observed=True)['searchRoute'].count().to_frame().rename({'searchRoute':'route_pop'}, \n                                                                                                                axis = 1)  ","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"%%time\n# Merging data from the cell abobe to the main df\n\ndf['route_dur_avg'] = df[['searchRoute',\n                          'legs0_segments0_departureFrom_airport_iata',\n                          'final_dest_city']].merge(route_avg_dur, \n                                                how='left', \n                                                left_on = (['searchRoute',\n                                                           'legs0_segments0_departureFrom_airport_iata',\n                                                            'final_dest_city']),\n                                                right_index = True)['route_dur_avg']\ndf['route_dur_avg'] = df['route_dur_avg'].fillna(0).astype('float32')\n\ndf['route_seg_dur_avg'] = df[['searchRoute',\n                              'legs0_segments0_departureFrom_airport_iata',\n                              'final_dest_city',\n                              'leg0_segment_num',\n                              'leg1_segment_num']].merge(route_avg_dur_seg, \n                                                        how='left', \n                                                        left_on = (['searchRoute',\n                                                                    'legs0_segments0_departureFrom_airport_iata',\n                                                                    'final_dest_city',\n                                                                    'leg0_segment_num',\n                                                                    'leg1_segment_num']), \n                                                        right_index = True)['route_seg_dur_avg']\ndf['route_seg_dur_avg'] = df['route_seg_dur_avg'].fillna(0).astype('float32')\n\ndf['route_cc_avgPrice'] = df[['searchRoute',\n                              'legs0_segments0_departureFrom_airport_iata',\n                              'final_dest_city',\n                              'legs0_segments0_cabinClass',\n                              'legs0_segments1_cabinClass',\n                              'legs1_segments0_cabinClass',\n                              'legs1_segments1_cabinClass']].merge(route_cc_price, \n                                                                    how='left', \n                                                                    left_on = (['searchRoute',\n                                                                                'legs0_segments0_departureFrom_airport_iata',\n                                                                                'final_dest_city',\n                                                                                'legs0_segments0_cabinClass',\n                                                                                'legs0_segments1_cabinClass',\n                                                                                'legs1_segments0_cabinClass',\n                                                                                'legs1_segments1_cabinClass']), \n                                                                    right_index = True)['route_cc_avgPrice']\ndf['route_cc_avgPrice'] = df['route_cc_avgPrice'].fillna(0).astype('float32')\n\ndf['route_pop'] = df[['searchRoute']].merge(route_popularity, \n                                                how='left', \n                                                left_on = (['searchRoute']),\n                                                right_index = True)['route_pop']\ndf['route_pop'] = df['route_pop'].fillna(0).astype('int32')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## Feature engineering - grouped by ranker_id features","metadata":{}},{"cell_type":"code","source":"%%time\n# Add rank and scaled features per each ranker_id\n\ncols_to_rank = ['avg_segments','total_dur_min','total_wait_time','totalPrice','hour_rat_avg','avg_in_ff']\n\nfor col in cols_to_rank:\n    df[col+'_rank'] = df.groupby('ranker_id',\n                                observed=True)[col].rank(method = 'dense').astype('float32')\n    df[col+'_std'] = df.groupby('ranker_id',\n                                observed=True)[col].transform('std').fillna(0).astype('float32')\n    df[col+'_nunique'] = df.groupby('ranker_id',\n                                    observed=True)[col].transform('nunique').astype('float32')\n    \n\ncols_to_scale = ['total_dur_min','total_wait_time','totalPrice']\n\nfor col in cols_to_scale:    \n    df[col+'_scaled'] = ((df[col] - df.groupby('ranker_id',\n                                             observed=True)[col].transform('min')) \\\n                       / \\\n                       (df.groupby('ranker_id',\n                                    observed=True)[col].transform('max') - df.groupby('ranker_id',\n                                                                                      observed=True)[col].transform('min'))*(10-1)+1).replace([np.inf, -np.inf], 1).fillna(1).astype('float32')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df['dur2price_ratio'] = ((1/df['total_dur_min_scaled']) / df['totalPrice_scaled']).astype('float32')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df['id_per_ranker_id'] = df.groupby('ranker_id',\n                                    observed=True)['Id'].transform('count').astype('int32')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## Feature engineering - user profile historical features","metadata":{}},{"cell_type":"code","source":"df['condition_flag'] = df['selected']==1","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df['prof_d_since_acq'] = (df['requestDate'] \\\n                          - \\\n                          df.groupby('profileId')['requestDate'].transform('min')).dt.days.astype('int32')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"%%time\ndf['prof_tot_purch'] = df.groupby(['profileId'],\n                                  observed = True)['selected'].transform('cumsum') \\\n                     - \\\n                       df.groupby(['profileId','ranker_id'],\n                                  observed = True)['selected'].transform('cumsum')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"%%time\n# Add expanding mean features based on profile historic data - previuos to a given ranker_id\n\ncols_exp_mean = ['avg_in_ff',\n                 'avg_segments',\n                 'avg_segments_rank',\n                 'total_dur_min_rank',\n                 'total_dur_min_scaled',\n                 'totalPrice_rank',\n                 'totalPrice_scaled',\n                 'dur2price_ratio',\n                 'avg_cabinClass',\n                 'legs0_departureAt_hour_rat',\n                 'legs0_arrivalAt_hour_rat',\n                 'hour_rat_avg',\n                 'avg_operatingCarrier_code_rating',\n                 'l_0_s_0_approx_bagg_weight']\n\nfor col in cols_exp_mean:\n    df[col+'_exp_mean'] = (df.groupby(['profileId'],\n                                     observed = True)[col].transform(lambda x: x[df.loc[x.index,'condition_flag']].cumsum()) \\\n                         - \\\n                         df.groupby(['profileId','ranker_id'],\n                                     observed = True)[col].transform(lambda x: x[df.loc[x.index,'condition_flag']].cumsum()) ) \\\n                         / df['prof_tot_purch']\n    # df[col+'_exp_mean'] = df.groupby(['profileId','ranker_id'],\n    #                                   observed = True)[col+'_exp_mean'].transform(lambda x: x.ffill().bfill())\n    df[col+'_exp_mean'] = df.groupby(['profileId','ranker_id'],\n                                      observed = True)[col+'_exp_mean'].ffill()\n    df[col+'_exp_mean'] = df.groupby(['profileId','ranker_id'],\n                                      observed = True)[col+'_exp_mean'].bfill()\n    df[col+'_exp_mean'] = df[col+'_exp_mean'].fillna(0).astype('float32')    ","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## Modeling","metadata":{}},{"cell_type":"code","source":"# Drop unnecessary columns\n\ncol_to_excl = ['Id',\n             'pricingInfo_passengerCount',\n             # 'requestDate',\n             'legs0_departureAt',\n             'legs0_arrivalAt',\n             'legs1_departureAt',\n             'legs1_arrivalAt',\n             'legs0_duration',\n             'legs0_segments0_duration',\n             'legs0_segments1_duration',\n             'legs1_duration',\n             'legs1_segments0_duration',\n             'legs1_segments1_duration',\n             'condition_flag']\n\ndf = df.drop(col_to_excl, axis = 1)\n\ndf = df.sort_values(by = 'init_order')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Creating a list of features to exclude from training\n\nspr_feat = ['selected','purpose','ranker_id','profileId','init_order','requestDate']\nfl_rout  = ['searchRoute'] + df.columns[df.columns.str.contains('flightNumber')].tolist()\naircr_code = df.columns[df.columns.str.contains('aircraft_code')].tolist()\n\ncols_no_use_all = [\n                    # 'bySelf',\n                     # 'is_return',\n                     'legs0_segments0_baggageAllowance_weightMeasurementType',\n                     'legs0_segments1_baggageAllowance_weightMeasurementType',\n                     'legs1_segments0_baggageAllowance_weightMeasurementType',\n                     'legs1_segments1_baggageAllowance_weightMeasurementType',\n                     # 'miniRules1_percentage'\n                  ]\n","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"cols_to_drop = spr_feat + fl_rout + cols_no_use_all\n# + aircr_code\ncat_cols = df.drop(columns = cols_to_drop).select_dtypes(include=['category']).columns.tolist()","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Split and allocate a part of data for validation\n\nval_perc = 90\n\nval_threshold = np.percentile(df[df['purpose'] != 'test']['requestDate'].unique(),val_perc)\n\ndf['purpose'] = df['purpose'].astype('object')\n\ndf.loc[(df['purpose'] != 'test')&(df['requestDate']>val_threshold),['purpose']] = 'valid'\ndf.loc[(df['purpose'] != 'test')&(df['requestDate']<=val_threshold),['purpose']] = 'train'\n\ndf['purpose'] = df['purpose'].astype('category')","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Define a func to calculate the target copetition 'hitrate@3' metric\n\ndef hit_rate_n(y_true, y_pred_score, ranker_id, n):\n    df = pd.DataFrame({'y_true':y_true,'y_pred_score':y_pred_score,'ranker_id':ranker_id})\n    df_sorted = df.sort_values(['ranker_id', 'y_pred_score'], ascending=[True, False])\n    df_top_n = df_sorted.groupby('ranker_id', observed = True).head(n).reset_index(drop=True)\n    return df_top_n['y_true'].sum() / df_top_n['ranker_id'].nunique()","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"!pip install -U xgboost\nimport xgboost as xgb","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Crating 'group_sizes' objects for group and eval_group parameters of XGB Ranker model\n\ntrain_group_sizes = df[(df['purpose'] == 'train')].groupby('ranker_id', \n                                                         observed = True, \n                                                         sort = False)['ranker_id'].count()\nval_group_sizes = df[(df['purpose'] == 'valid')].groupby('ranker_id', \n                                                         observed = True, \n                                                         sort = False)['ranker_id'].count()\n# tval_group_sizes = df[(df['purpose'] != 'test')].groupby('ranker_id', \n#                                                         observed = True, \n#                                                         sort = False)['ranker_id'].count()\ntest_group_sizes = df[df['purpose'] == 'test'].groupby('ranker_id', \n                                                         observed = True, \n                                                         sort = False)['ranker_id'].count()","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Parameters were determined with manual parameters search loops\n\nxgb_params = {\n    'max_depth': 8,\n    'min_child_weight': 11,\n    # 'subsample': 0.8,\n    # 'colsample_bytree': 0.8,\n    'lambda': 2.0,\n    'learning_rate': 0.05\n}\n\nxgbr = xgb.XGBRanker(\n                     **xgb_params,\n                     objective = 'rank:pairwise',\n                     # objective = 'rank:ndcg',\n                     eval_metric = 'ndcg@3',\n                     enable_categorical = True,\n                       n_estimators = 1000,\n                       early_stopping_rounds = 85,\n                       random_state = 42)","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"xgbr.fit(df[df['purpose'] == 'train'].drop(columns = cols_to_drop), \n         df[df['purpose'] == 'train']['selected'], \n         group = train_group_sizes,\n         eval_set = [(df[df['purpose'] == 'train'].drop(columns = cols_to_drop),\n                      df[df['purpose'] == 'train']['selected']),\n                     (df[df['purpose'] == 'valid'].drop(columns = cols_to_drop),\n                      df[df['purpose'] == 'valid']['selected'])],\n         eval_group = [train_group_sizes,\n                       val_group_sizes],\n         verbose = 10)","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Check target metric on validation dataset\n\nhit_rate_n(df[(df['purpose'] == 'valid')&(df['id_per_ranker_id']>10)]['selected'], \n           xgbr.predict(df[(df['purpose'] == 'valid')&(df['id_per_ranker_id']>10)].drop(columns = cols_to_drop)), \n           df[(df['purpose'] == 'valid')&(df['id_per_ranker_id']>10)]['ranker_id'],\n           3 )","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Check feature importance\n\nxgbr_feat_df = pd.DataFrame({'Feature': df[df['purpose'] == 'train'].drop(columns=cols_to_drop).columns, \n                              'Importance': xgbr.feature_importances_}).sort_values(by='Importance', ascending=False)\n# xgbr_feat_no_imp = set(xgbr_feat_df['Feature'][xgbr_feat_df['Importance'] == 0])\nxgbr_feat_df[:50]","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## Submission","metadata":{}},{"cell_type":"code","source":"# Create submission\n\nsubm_xgbr = df[df['purpose'] == 'test'][[ 'ranker_id']].copy()\nsubm_xgbr['Id'] = subm_xgbr.index\nsubm_xgbr['pred'] = xgbr.predict(df[df['purpose'] == 'test'].drop(columns = cols_to_drop))\nsubm_xgbr['selected'] = subm_xgbr.groupby(['ranker_id'], \n                                        observed = True)['pred'].rank(ascending=False, method='first').astype('int32')\nsubm_xgbr[['Id','ranker_id','selected']].to_csv('submission.csv', index=False)\nsubm_xgbr[['Id','ranker_id','selected']].head(20)","metadata":{"trusted":true},"outputs":[],"execution_count":null}]}