{"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":"## Kernel description","metadata":{}},{"cell_type":"markdown","source":"Adding new features to the `train_meta` dataframe for one batch only:  \n  \n1) Statistical, such as the total number of impulses or the average relative time within one event and so one;  \n2) Predictions of different models as features (***work in progress***).    \n  \nPolars library was used for feature engineering, because it allows to process all 660 batches and 131,953,924 events many times faster than Pandas.  \n  \nThis Kernel has separate functions that you can use and modify to create your own features.  \n  \nThe resulting feature table is shown at the end of the notebook.  \n\nPlease don't hesitate to leave your comments on this Kernel: use the features table for your models and share the results.","metadata":{}},{"cell_type":"markdown","source":"## Updates\n\n**Ver. 2:** Removed the use of Pandas functions to create features (now Polars only). Separate functions added for feature engineering. Added new features in the `train_meta` data as well. Feature engineering was implemented to one batch only due to memory error in all batches case.","metadata":{}},{"cell_type":"markdown","source":"## Sources","metadata":{}},{"cell_type":"markdown","source":"For this Kernel, [[日本語/Eng]🧊: FeatureEngineering](https://www.kaggle.com/code/utm529fg/eng-featureengineering) kernel was used, as well as separate articles about Polars library and feature engineering:  \n1) [📊 Построение и отбор признаков. Часть 1: feature engineering (RUS)](https://proglib.io/p/postroenie-i-otbor-priznakov-chast-1-feature-engineering-2021-09-15)  \n2) [Polars: Pandas DataFrame but Much Faster](https://towardsdatascience.com/pandas-dataframe-but-much-faster-f475d6be4cd4)  \n3) [Polars: calm](https://calmcode.io/polars/calm.html)  \n4) [Polars - User Guide](https://pola-rs.github.io/polars-book/user-guide/coming_from_pandas.html) ","metadata":{}},{"cell_type":"markdown","source":"## Import libraries","metadata":{}},{"cell_type":"code","source":"# List all installed packages and package versions\n#!pip freeze","metadata":{"execution":{"iopub.status.busy":"2023-04-08T15:21:35.160581Z","iopub.execute_input":"2023-04-08T15:21:35.161187Z","iopub.status.idle":"2023-04-08T15:21:35.191614Z","shell.execute_reply.started":"2023-04-08T15:21:35.161130Z","shell.execute_reply":"2023-04-08T15:21:35.190331Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"!pip install polars==0.16.8","metadata":{"execution":{"iopub.status.busy":"2023-04-08T17:47:37.009865Z","iopub.execute_input":"2023-04-08T17:47:37.010600Z","iopub.status.idle":"2023-04-08T17:47:48.491356Z","shell.execute_reply.started":"2023-04-08T17:47:37.010560Z","shell.execute_reply":"2023-04-08T17:47:48.490092Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"! pip install memory_profiler\n%load_ext memory_profiler","metadata":{"execution":{"iopub.status.busy":"2023-04-08T21:19:28.968861Z","iopub.execute_input":"2023-04-08T21:19:28.969269Z","iopub.status.idle":"2023-04-08T21:19:42.334563Z","shell.execute_reply.started":"2023-04-08T21:19:28.969237Z","shell.execute_reply":"2023-04-08T21:19:42.333149Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import numpy as np\nimport os\nimport pandas as pd\nimport polars as pl\nfrom tqdm.notebook import tqdm","metadata":{"execution":{"iopub.status.busy":"2023-04-08T21:20:18.803690Z","iopub.execute_input":"2023-04-08T21:20:18.804166Z","iopub.status.idle":"2023-04-08T21:20:19.098187Z","shell.execute_reply.started":"2023-04-08T21:20:18.804112Z","shell.execute_reply":"2023-04-08T21:20:19.096813Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Check types info for memory usage optimization:","metadata":{}},{"cell_type":"code","source":"int_types = [\"uint64\", \"int64\"]\nfor it in int_types:\n    print(np.iinfo(it))","metadata":{"execution":{"iopub.status.busy":"2023-04-08T19:33:19.845742Z","iopub.execute_input":"2023-04-08T19:33:19.846118Z","iopub.status.idle":"2023-04-08T19:33:19.850560Z","shell.execute_reply.started":"2023-04-08T19:33:19.846087Z","shell.execute_reply":"2023-04-08T19:33:19.849516Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Check existing paths:","metadata":{}},{"cell_type":"code","source":"# for dirname, _, filenames in os.walk('/kaggle/input'):\n#     for filename in filenames:\n#         print(os.path.join(dirname, filename))","metadata":{"execution":{"iopub.status.busy":"2023-04-08T15:21:48.294427Z","iopub.execute_input":"2023-04-08T15:21:48.295460Z","iopub.status.idle":"2023-04-08T15:21:48.300942Z","shell.execute_reply.started":"2023-04-08T15:21:48.295409Z","shell.execute_reply":"2023-04-08T15:21:48.299720Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Will try to use Polars as one of the fastest libraries because it uses parallelization and cache efficient algorithms to speed up tasks.  \n  \nLet's create LazyFrames from all existing inputs:","metadata":{}},{"cell_type":"code","source":"INPUT_DIR = '/kaggle/input/icecube-neutrinos-in-deep-ice'\n\ntrain_meta = pl.scan_parquet(f'{INPUT_DIR}/train_meta.parquet')\nsensor_geometry = pl.scan_csv(f'{INPUT_DIR}/sensor_geometry.csv')\n\nbatches_dict = {}\n\nfor i in range(1, train_meta.collect()['batch_id'].max() + 1):\n    key=str('train_batch_'+str(i))\n    batches_dict[key] = pl.scan_parquet(f'{INPUT_DIR}/train/batch_{i}.parquet')","metadata":{"execution":{"iopub.status.busy":"2023-04-08T21:20:28.997701Z","iopub.execute_input":"2023-04-08T21:20:28.998191Z","iopub.status.idle":"2023-04-08T21:20:59.053476Z","shell.execute_reply.started":"2023-04-08T21:20:28.998151Z","shell.execute_reply":"2023-04-08T21:20:59.051936Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Feature engineering","metadata":{}},{"cell_type":"code","source":"def add_cols_to_sensor_geometry(sensor):\n    '''\n    add new columns for groupby.sum() function\n    \n    Parameters:\n    -----------\n    sensor : LazyFrame\n        existing 'sensor_geometry' data\n    \n    Returns:\n    -----------\n    sensor : LazyFrame\n        updated 'sensor_geometry' data with additional \n        null-filled columns\n    '''\n    sensor=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    )\n    \n    return sensor","metadata":{"execution":{"iopub.status.busy":"2023-04-08T21:21:54.337571Z","iopub.execute_input":"2023-04-08T21:21:54.338029Z","iopub.status.idle":"2023-04-08T21:21:54.345894Z","shell.execute_reply.started":"2023-04-08T21:21:54.337987Z","shell.execute_reply":"2023-04-08T21:21:54.344340Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sensor_geometry = add_cols_to_sensor_geometry(sensor_geometry).collect()\nsensor_geometry.head()","metadata":{"execution":{"iopub.status.busy":"2023-04-08T21:21:57.764834Z","iopub.execute_input":"2023-04-08T21:21:57.765234Z","iopub.status.idle":"2023-04-08T21:21:57.823146Z","shell.execute_reply.started":"2023-04-08T21:21:57.765200Z","shell.execute_reply":"2023-04-08T21:21:57.822232Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def add_stat_features(meta):\n    '''\n    add new statistics features into the selected data\n    \n    Parameters:\n    -----------\n    meta : LazyFrame\n        existing 'train_meta' data\n    \n    Returns:\n    -----------\n    meta : LazyFrame\n        updated 'train_meta' data with additional columns:\n        * 'n_events_per_batch' (can be useful when creating \n        features for all batches at once)\n        * 'pulse_count' - count of pulses detected\n    '''\n    return (meta\n            .with_columns([\n                pl.col('event_id').count().over('batch_id').cast(pl.UInt64).alias('n_events_per_batch'),\n                (pl.col('last_pulse_index') - pl.col('first_pulse_index') + 1).alias('pulse_count')\n             ]))","metadata":{"execution":{"iopub.status.busy":"2023-04-08T21:22:09.245454Z","iopub.execute_input":"2023-04-08T21:22:09.246012Z","iopub.status.idle":"2023-04-08T21:22:09.254321Z","shell.execute_reply.started":"2023-04-08T21:22:09.245964Z","shell.execute_reply":"2023-04-08T21:22:09.252511Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_meta = train_meta.pipe(add_stat_features).collect()\ntrain_meta.head()","metadata":{"execution":{"iopub.status.busy":"2023-04-08T21:22:11.624868Z","iopub.execute_input":"2023-04-08T21:22:11.625319Z","iopub.status.idle":"2023-04-08T21:22:24.914212Z","shell.execute_reply.started":"2023-04-08T21:22:11.625277Z","shell.execute_reply":"2023-04-08T21:22:24.912701Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There is not enough memory to execute these codes for all batches - the reason why I commented out this code. Perhaps after some additional work with memory in the future, I can use one of these options to create features for all batches at once.","metadata":{}},{"cell_type":"code","source":"# def add_time_mean(train_meta, batches_dict):\n#     batches = []\n#     for batch_name, batch in tqdm(batches_dict.items()):\n#         batch_id = int(batch_name.split(\"_\")[-1])\n#         batch_df = batch.select(['sensor_id', 'time', 'event_id']).collect()\n#         batch_len = len(batch_df)\n        \n#         batch = batch_df.with_columns((pl.Series([batch_id] * batch_len)).alias('batch_id'))\n#         batches.append(batch)\n\n#     all_batches = pl.concat(batches)\n\n#     time_mean = all_batches.groupby('event_id').agg(\n#         pl.col('time').mean().alias('time_mean'))\n\n#     train_meta_with_time_mean = train_meta.join(\n#         time_mean, on='event_id', how='inner')\n\n#     return train_meta_with_time_mean","metadata":{"execution":{"iopub.status.busy":"2023-04-08T22:02:38.913882Z","iopub.execute_input":"2023-04-08T22:02:38.915078Z","iopub.status.idle":"2023-04-08T22:02:38.920744Z","shell.execute_reply.started":"2023-04-08T22:02:38.915026Z","shell.execute_reply":"2023-04-08T22:02:38.919401Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# %%time\n# add_time_mean(train_meta, batches_dict)","metadata":{"execution":{"iopub.status.busy":"2023-04-08T22:02:38.923393Z","iopub.execute_input":"2023-04-08T22:02:38.924285Z","iopub.status.idle":"2023-04-08T22:02:38.937534Z","shell.execute_reply.started":"2023-04-08T22:02:38.924229Z","shell.execute_reply":"2023-04-08T22:02:38.936437Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# def create_batch_1_features(batch_dict, sensor, meta):\n#     '''\n#     Creating new meta_total data including statistics features from batches\n    \n#     Parameters:\n#     -----------\n#     batch_dict : dictionary where keys are polars LazyFrames\n#     sensor : LazyFrame\n#     meta : train_meta pl.DataFrame\n    \n#     Returns:\n#     -----------\n#     sensor : polars DataFrame\n#     '''\n    \n#     meta_tmp = pl.DataFrame()\n#     meta_total = pl.DataFrame() # for output\n    \n#     for key in tqdm(batch_dict):\n#         batch = batch_dict[key].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#         ])\n        \n#         sensor = sensor.join(batch_tmp, on='sensor_id', how='left')\n    \n#         # groupby sensor_id and sum\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        \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])\n    \n#     return meta_total","metadata":{"execution":{"iopub.status.busy":"2023-04-08T19:40:52.594733Z","iopub.execute_input":"2023-04-08T19:40:52.595116Z","iopub.status.idle":"2023-04-08T19:40:52.608931Z","shell.execute_reply.started":"2023-04-08T19:40:52.595086Z","shell.execute_reply":"2023-04-08T19:40:52.608292Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def create_batch_features(batch_dict, key, sensor, meta):\n    '''\n    Creating new meta_total data including statistics features \n    for one selected batch only\n    \n    Parameters:\n    -----------\n    batch_dict : dict \n        keys - str, values - LazyFrames\n    key : str\n        name of batch\n    sensor : LazyFrame\n    meta : polars DataFrame\n        existing 'train_meta' data\n    \n    Returns:\n    -----------\n    sensor : polars DataFrame\n    meta_total : polars DataFrame\n    '''\n    # for output\n    meta_tmp = pl.DataFrame()\n    meta_total = pl.DataFrame() \n    \n    batch = batch_dict[key].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    ])\n         \n    sensor = sensor.join(batch_tmp, on='sensor_id', how='left')\n    \n    # groupby sensor_id and sum\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        \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])\n    \n    return sensor, meta_total","metadata":{"execution":{"iopub.status.busy":"2023-04-08T21:57:07.091952Z","iopub.execute_input":"2023-04-08T21:57:07.092531Z","iopub.status.idle":"2023-04-08T21:57:07.110538Z","shell.execute_reply.started":"2023-04-08T21:57:07.092468Z","shell.execute_reply":"2023-04-08T21:57:07.109349Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%memit sensor_geometry, meta_batch = create_batch_features(batches_dict, 'train_batch_1', sensor_geometry, train_meta)\n\nmeta_batch.head()","metadata":{"execution":{"iopub.status.busy":"2023-04-08T21:59:24.686861Z","iopub.execute_input":"2023-04-08T21:59:24.687305Z","iopub.status.idle":"2023-04-08T21:59:29.812659Z","shell.execute_reply.started":"2023-04-08T21:59:24.687261Z","shell.execute_reply":"2023-04-08T21:59:29.811060Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# feature engineering\n\ndef feature_engineering(sensor, meta_total):\n    '''\n    Creating new meta_total data including statistics features\n    for one selected batch only\n    \n    Parameters:\n    -----------\n    sensor : polars DataFrame\n    meta_total : polars DataFrame\n    \n    Returns:\n    -----------\n    sensor : polars DataFrame\n    meta_total : polars DataFrame\n    '''\n    sensor = sensor.with_columns(\n            [(pl.col('sensor_count')  / len(meta_total)).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')])\n    meta_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\n    sensor = sensor.select(pl.col(['sensor_id',\n                                   'x',\n                                   'y',\n                                   'z',\n                                   'sensor_count',\n                                   'sensor_count_mean',\n                                   'time_mean',\n                                   'charge_mean',\n                                   'auxiliary_ratio'])\n                          )\n    meta_total = meta_total.select(pl.col(['batch_id',\n                                           'event_id',\n                                           'first_pulse_index',\n                                           'last_pulse_index',\n                                           'azimuth',\n                                           'zenith',\n                                           'n_events_per_batch',\n                                           'pulse_count',\n                                           'charge_sum',\n                                           'auxiliary_sum',\n                                           'time_mean',\n                                           'charge_mean',\n                                           'auxiliary_ratio'])\n                                  )\n    return sensor, meta_total","metadata":{"execution":{"iopub.status.busy":"2023-04-08T22:08:55.536044Z","iopub.execute_input":"2023-04-08T22:08:55.536708Z","iopub.status.idle":"2023-04-08T22:08:55.549649Z","shell.execute_reply.started":"2023-04-08T22:08:55.536664Z","shell.execute_reply":"2023-04-08T22:08:55.548084Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sensor_geometry, meta_batch = feature_engineering(sensor_geometry, meta_batch)\n\ndisplay(sensor_geometry.head())\nmeta_batch.head()","metadata":{"execution":{"iopub.status.busy":"2023-04-08T22:08:57.923726Z","iopub.execute_input":"2023-04-08T22:08:57.924192Z","iopub.status.idle":"2023-04-08T22:08:57.947927Z","shell.execute_reply.started":"2023-04-08T22:08:57.924151Z","shell.execute_reply":"2023-04-08T22:08:57.946354Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<font color='red'>\nIf you know how to reduce memory usage for above code, please feel free to share your ideas in the comments of this notebook. I've found the good notebook:</font>    \n[14 Simple Tips to save RAM memory for 1+GB dataset](https://www.kaggle.com/code/pavansanagapati/14-simple-tips-to-save-ram-memory-for-1-gb-dataset/notebook)  \n<font color='red'>\nTried to change Int64 to UInt64 to reduce memory a little, but it need more precise work with all data to be sure that all data fit same type.\n</font>","metadata":{}},{"cell_type":"markdown","source":"## ","metadata":{}}]}