{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# 1. Goal\nCreate and save a data frame with additional features for analysis.<br>\n分析用に、特徴量を追加したデータフレームを作成して保存します。\n\n**Saved Dataset**: https://www.kaggle.com/datasets/utm529fg/icecube-featureengineering\n\n\n","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19"}},{"cell_type":"markdown","source":"# 2. Update History\n- **v2** : Add label of Deep Core. Deep core のラベルを追加。","metadata":{}},{"cell_type":"markdown","source":"# 3. import","metadata":{}},{"cell_type":"code","source":"!pip install polars","metadata":{"execution":{"iopub.status.busy":"2023-02-06T01:59:14.433110Z","iopub.execute_input":"2023-02-06T01:59:14.433635Z","iopub.status.idle":"2023-02-06T01:59:30.136573Z","shell.execute_reply.started":"2023-02-06T01:59:14.433536Z","shell.execute_reply":"2023-02-06T01:59:30.135237Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import numpy as np\nimport polars as pl\nfrom tqdm.notebook import tqdm","metadata":{"execution":{"iopub.status.busy":"2023-02-06T01:59:30.138905Z","iopub.execute_input":"2023-02-06T01:59:30.139406Z","iopub.status.idle":"2023-02-06T01:59:30.324036Z","shell.execute_reply.started":"2023-02-06T01:59:30.139354Z","shell.execute_reply":"2023-02-06T01:59:30.323063Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"INPUT_DIR = '/kaggle/input/icecube-neutrinos-in-deep-ice'\nmeta = pl.scan_parquet(f'{INPUT_DIR}/train_meta.parquet')\nsensor = pl.scan_csv(f'{INPUT_DIR}/sensor_geometry.csv')","metadata":{"execution":{"iopub.status.busy":"2023-02-06T01:59:30.325480Z","iopub.execute_input":"2023-02-06T01:59:30.326684Z","iopub.status.idle":"2023-02-06T01:59:30.344926Z","shell.execute_reply.started":"2023-02-06T01:59:30.326636Z","shell.execute_reply":"2023-02-06T01:59:30.343428Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 4. Feature Engineering","metadata":{}},{"cell_type":"code","source":"# add columns for groupby.sum()\nsensor=sensor.with_columns(\n    [(pl.col('sensor_id') * 0).alias('sensor_count'),\n     (pl.col('sensor_id') * 0.0).alias('charge_sum'),\n     (pl.col('sensor_id') * 0).alias('time_sum'),\n     (pl.col('sensor_id') * 0).alias('auxiliary_sum')]\n)\nsensor = sensor.collect()\n\nmeta=meta.with_columns((pl.col('last_pulse_index') - pl.col('first_pulse_index') + 1).alias('pulse_count'))\nmeta = meta.collect()\n\n# number of event\nevent_N = len(meta)","metadata":{"execution":{"iopub.status.busy":"2023-02-05T08:33:26.586500Z","iopub.execute_input":"2023-02-05T08:33:26.587267Z","iopub.status.idle":"2023-02-05T08:33:53.396664Z","shell.execute_reply.started":"2023-02-05T08:33:26.587221Z","shell.execute_reply":"2023-02-05T08:33:53.395355Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"meta_tmp = pl.DataFrame()\nmeta_total = pl.DataFrame() # for output\nfor batch_id in tqdm(range(1,661)):\n    batch = pl.scan_parquet(f'{INPUT_DIR}/train/batch_{batch_id}.parquet')\n    batch = batch.collect()\n    \n    # count detected sensor\n    batch_tmp = batch['sensor_id'].value_counts()\n    \n    # cast and join\n    batch_tmp = batch_tmp.with_columns(\n        [pl.col('sensor_id').cast(pl.Int64),\n         pl.col('counts').cast(pl.Int64)])\n    sensor = sensor.join(batch_tmp,on='sensor_id',how='left')\n    \n    # groupby sensor_id\n    batch_tmp = batch.select(pl.col(['sensor_id','time','charge','auxiliary'])).groupby(['sensor_id']).sum()\n    \n    # cast and join\n    batch_tmp = batch_tmp.with_columns(\n        [pl.col('sensor_id').cast(pl.Int64),\n         pl.col('auxiliary').cast(pl.Int64)])\n    sensor = sensor.join(batch_tmp,on='sensor_id',how='left')\n    sensor = sensor.fill_null(0)\n    \n    # add total value\n    sensor = sensor.with_columns(\n        [(pl.col('sensor_count')  + pl.col('counts')).alias('sensor_count'),\n         (pl.col('time_sum')      + pl.col('time')).alias('time_sum'),\n         (pl.col('charge_sum')    + pl.col('charge')).alias('charge_sum'),\n         (pl.col('auxiliary_sum') + pl.col('auxiliary')).alias('auxiliary_sum')])\n    \n    # exclude unnecessary columns\n    sensor = sensor.select(pl.exclude(['counts','time','charge','auxiliary']))\n    \n    # groupby event_id\n    batch_tmp = batch.select(pl.col(['event_id','time','charge','auxiliary'])).groupby(['event_id']).sum()\n    \n    # cast and join\n    batch_tmp = batch_tmp.with_columns(pl.col('auxiliary').cast(pl.Int64))\n    meta_tmp = meta.join(batch_tmp,on='event_id',how='inner')\n    \n    # add total value\n    meta_tmp = meta_tmp.with_columns(\n        [(pl.col('time')).alias('time_sum'),\n         (pl.col('charge')).alias('charge_sum'),\n         (pl.col('auxiliary')).alias('auxiliary_sum')])\n    \n    # exclude unnecessary columns\n    meta_tmp = meta_tmp.select(pl.exclude(['time','charge','auxiliary']))\n    \n    # append to output\n    meta_total = pl.concat([meta_total, meta_tmp])","metadata":{"execution":{"iopub.status.busy":"2023-02-05T08:34:00.019600Z","iopub.execute_input":"2023-02-05T08:34:00.020172Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# feature engineering\nsensor = sensor.with_columns(\n        [(pl.col('sensor_count')  / event_N).alias('sensor_count_mean'),\n         (pl.col('time_sum')      / pl.col('sensor_count')).alias('time_mean'),\n         (pl.col('charge_sum')    / pl.col('sensor_count')).alias('charge_mean'),\n         (pl.col('auxiliary_sum') / pl.col('sensor_count')).alias('auxiliary_ratio')])\nmeta_total = meta_total.with_columns(\n        [(pl.col('time_sum')      / pl.col('pulse_count')).alias('time_mean'),\n         (pl.col('charge_sum')    / pl.col('pulse_count')).alias('charge_mean'),\n         (pl.col('auxiliary_sum') / pl.col('pulse_count')).alias('auxiliary_ratio')])\n\n# select and sort columns\nsensor = sensor.select(pl.col(['sensor_id','x','y','z','sensor_count','sensor_count_mean','time_mean','charge_mean','auxiliary_ratio']))\nmeta_total = meta_total.select(pl.col(['batch_id','event_id','first_pulse_index','last_pulse_index','azimuth','zenith','pulse_count','time_mean','charge_mean','auxiliary_ratio']))","metadata":{"execution":{"iopub.status.busy":"2023-02-05T08:28:02.785007Z","iopub.execute_input":"2023-02-05T08:28:02.785903Z","iopub.status.idle":"2023-02-05T08:28:02.819536Z","shell.execute_reply.started":"2023-02-05T08:28:02.785851Z","shell.execute_reply":"2023-02-05T08:28:02.818099Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# DeepCore flag\nsensor = sensor.with_columns((pl.when(pl.col('sensor_id') >= 4680).then(True).otherwise(False)).alias('deepcore'))","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 5. Save","metadata":{}},{"cell_type":"code","source":"# write\nsensor.write_csv('sensor_geometory_add_feature.csv')\nmeta_total.write_parquet('train_meta_add_feature.parquet')","metadata":{"execution":{"iopub.status.busy":"2023-02-05T08:28:02.821341Z","iopub.execute_input":"2023-02-05T08:28:02.821715Z","iopub.status.idle":"2023-02-05T08:28:03.337617Z","shell.execute_reply.started":"2023-02-05T08:28:02.821681Z","shell.execute_reply":"2023-02-05T08:28:03.336630Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 6. Introduction of Added Features","metadata":{}},{"cell_type":"code","source":"sensor.head()","metadata":{"execution":{"iopub.status.busy":"2023-02-05T08:28:03.339144Z","iopub.execute_input":"2023-02-05T08:28:03.340116Z","iopub.status.idle":"2023-02-05T08:28:03.351378Z","shell.execute_reply.started":"2023-02-05T08:28:03.340079Z","shell.execute_reply":"2023-02-05T08:28:03.350121Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- **`sensor_count`** : Count of detections out of 131,953,924 events in the training data. multiple detections in one event are counted as duplicates.<br>学習データ131,953,924件のイベント中、検知した回数。1イベントの中で複数回検知しているものは、重複してカウント。\n- **`sensor_count_mean`** : Average number of detections per event. sensor_count ÷ 131,953,924<br>イベントごとの平均検知回数。\n- **`time_mean`** : Average of all times per sensor.<br>センサーごとの全timeの平均。\n- **`charge_mean`** : Average of all charges per sensor.<br>センサーごとの全chargeの平均。\n- **`auxiliary_ratio`** : Percentage of auxiliaries that are true for each sensor.<br>センサーごとのauxiliaryがTrueである割合。\n- **`deepcore`** : True if the sensor is Deep Core.<br>Deep Core か否かのフラグ。","metadata":{}},{"cell_type":"code","source":"meta_total.head()","metadata":{"execution":{"iopub.status.busy":"2023-02-05T08:28:03.353065Z","iopub.execute_input":"2023-02-05T08:28:03.354347Z","iopub.status.idle":"2023-02-05T08:28:03.367426Z","shell.execute_reply.started":"2023-02-05T08:28:03.354301Z","shell.execute_reply":"2023-02-05T08:28:03.366131Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- **`pulse_count`** : Count of pulses detected. last_pulse_index - first_pulse_index + 1<br>検知したパルスのカウント。\n- **`time_mean`** : Average of all times per event.<br>イベントごとの全timeの平均。\n- **`charge_mean`** : Average of all charges per event.<br>イベントごとの全chargeの平均。\n- **`auxiliary_ratio`** : Percentage of auxiliaries that are true for each event.<br>イベントごとのauxiliaryがTrueである割合。","metadata":{}}]}