{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.14","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":84493,"databundleVersionId":9871156,"sourceType":"competition"},{"sourceId":10175682,"sourceType":"datasetVersion","datasetId":6285101}],"dockerImageVersionId":30804,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Review\n\nThe analysis of missing values comes in part from the notes: [https://www.kaggle.com/code/nicolesy/data-pre-processing-memory-and-missing-value](http://)\n\nThe dataset used was converted from float 32 to 16 and re-sliced for 5-folds cross-validation, [from the note: https://www.kaggle.com/code/nicolesy/data-pre-processing-cv-performance](http://)\n\nThe compressed data:\n\n| | |CV| | | |LB|\n|---|---|---|---|---|---|---|\n|***fold_0***|***fold_1***|***fold_2***|***fold_3***|***fold_4***|***mean_CV***|***_ LB _***|\n|0.01478|0.00801|0.00865|0.00844|0.00588|0.00915|0.0043|\n\n\nSystematic missing values： ***feature_15,17,32,33,39,41,42,44,50,52,53,55,58,73,74***\n\nRandom missing values：\n\n* Completely missing\n\n|**Parquet**|**Number of Missing Days**|**Feature**|\n|---|---|---|\n|partition_id=1|all|21,26,27,31|\n|partition_id=2|all|21,26,27,31|\n|partition_id=3|first_17|21,26,27,31|\n|partition_id=7|random:2date|08|\n|partition_id=8|random:5date|08|\n|partition_id=9|random:1date|08|\n\n* Partial missing ---- The remaining data...","metadata":{}},{"cell_type":"code","source":"import os\nfrom pathlib import Path\nimport numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\nimport lightgbm as lgb\nimport joblib\n\nfrom tqdm import tqdm\nimport pyarrow.parquet as pq\nimport shutil\nimport time\n\nimport warnings\nwarnings.filterwarnings(\"ignore\")","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"execution":{"iopub.status.busy":"2024-12-15T12:48:17.203081Z","iopub.execute_input":"2024-12-15T12:48:17.203482Z","iopub.status.idle":"2024-12-15T12:48:17.213562Z","shell.execute_reply.started":"2024-12-15T12:48:17.203447Z","shell.execute_reply":"2024-12-15T12:48:17.212061Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"folder_path = '/kaggle/input/js-dpp-cvp/lower/'","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-15T12:48:19.209176Z","iopub.execute_input":"2024-12-15T12:48:19.209568Z","iopub.status.idle":"2024-12-15T12:48:19.214820Z","shell.execute_reply.started":"2024-12-15T12:48:19.209540Z","shell.execute_reply":"2024-12-15T12:48:19.213649Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"# Experiment 1: feature_08\nDelete all data under the eight missing dates of ***feature_08***.","metadata":{}},{"cell_type":"code","source":"for i in tqdm(range(5)):\n    file_path = os.path.join(folder_path, f'combined_part{i}.parquet')\n\n    df = pd.read_parquet(file_path)\n\n    grouped = df.groupby('date_id')['feature_08']\n    \n    fully_missing_date_ids = grouped.apply(lambda x: x.isna().all()).loc[lambda x: x].index\n\n    if len(fully_missing_date_ids) > 0:\n        print(f\"File: {file_path}\")\n        print(\"Date_ids where feature_08 is completely missing for all time_id:\")\n        print(fully_missing_date_ids)","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"del8_folder = '/kaggle/working/dele_f8_8dates/'\nos.makedirs(del8_folder, exist_ok=True)","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"delete_date_ids_part3 = [1236, 1239, 1392, 1395]\ndelete_date_ids_part4 = [1469, 1471, 1485, 1637]\n\nfor i in tqdm(range(5)):\n    file_path = os.path.join(folder_path, f'combined_part{i}.parquet')\n    output_file_path = os.path.join(del8_folder, f'combined_part{i}.parquet')\n    \n    df = pd.read_parquet(file_path)\n    \n    if i == 3:  # combined_part3.parquet\n        df = df[~df['date_id'].isin(delete_date_ids_part3)]\n    elif i == 4:  # combined_part4.parquet\n        df = df[~df['date_id'].isin(delete_date_ids_part4)]\n    \n    df.to_parquet(output_file_path)\n    print(f\"Processed and saved: {output_file_path}\")","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"# Experiment 2: partial missing with ffill_&_bfill\n\n-- Systematic missing values -- \n\nFor ***feature_15,17,32,33,39,41,42,44,50,52,53,55,58,73,74***, under each date_id, before the time_id of the **first non-missing value** of the current feature, we ensure that data up to the time_id cutoff remain as missing values.\n\n-- Random missing values --\n\n* Completely absent:\n>* For some data of ***feature_21***, ***feature_26***, ***feature_27*** and ***feature_31*** with **date_id <= 527**, we keep their missing values without processing.\n>* For ***feature_08***, keep their missing values without processing **8 date_ids** that are completely missing.\n\n* Partially missing:\n\n> For all other missing values that were not systematic and not completely missing, we first converted the data type to float32 and then filled in the missing values using a combination of **forward and backward** padding.","metadata":{}},{"cell_type":"code","source":"fb_folder = '/kaggle/working/fbfill_random_partial/'\nos.makedirs(fb_folder, exist_ok=True)","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"systematic_missing_features = ['feature_15', 'feature_17', 'feature_32', 'feature_33', 'feature_39', \n                               'feature_41', 'feature_42', 'feature_44', 'feature_50', 'feature_52', \n                               'feature_53', 'feature_55', 'feature_58', 'feature_73', 'feature_74']\n# time_id cutoff for each feature (previous data remain missing)\nsystematic_missing_time_ids = { # the time_id of the first non-missing value of the current feature\n    'feature_15': 24,\n    'feature_17': 4,\n    'feature_32': 10,\n    'feature_33': 10,\n    'feature_39': 68,\n    'feature_41': 18,\n    'feature_42': 68,\n    'feature_44': 18,\n    'feature_50': 68,\n    'feature_52': 18,\n    'feature_53': 68,\n    'feature_55': 18,\n    'feature_58': 10,\n    'feature_73': 10,\n    'feature_74': 10\n}\n\n# Completely missing in one date_id\nfeature_21_26_27_31_missing_before_527 = ['feature_21', 'feature_26', 'feature_27', 'feature_31']\nfeature_08_missing_date_ids = [1236, 1239, 1392, 1395, 1469, 1471, 1485, 1637]  ","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-15T12:48:57.861862Z","iopub.execute_input":"2024-12-15T12:48:57.862198Z","iopub.status.idle":"2024-12-15T12:48:57.871088Z","shell.execute_reply.started":"2024-12-15T12:48:57.862174Z","shell.execute_reply":"2024-12-15T12:48:57.869661Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"%%time\nfor i in tqdm(range(5)):\n    file_path = os.path.join(folder_path, f'combined_part{i}.parquet')\n    output_file_path = os.path.join(fb_folder, f'combined_part{i}.parquet')\n    \n    df = pd.read_parquet(file_path)\n    \n    for column in df.select_dtypes(include=['float16']).columns:\n        df[column] = df[column].astype('float32')\n    \n    # Jump systematically missing values\n    for feature in systematic_missing_features:\n        cutoff_time_id = systematic_missing_time_ids[feature]\n        df.loc[df['time_id'] <= cutoff_time_id, feature] = pd.NA\n        \n    # Jump random missing values in date which is completely\n    # Handle feature_21, feature_26, feature_27, feature_31, and keep missing values when date_id <= 527\n    for feature in feature_21_26_27_31_missing_before_527:\n        df.loc[df['date_id'] <= 527, feature] = df.loc[df['date_id'] <= 527, feature]\n    \n    # Handle feature_08, keep missing values in completely missing date\n    untouched_feature_08 = df['feature_08'].copy()\n    df.loc[df['date_id'].isin(feature_08_missing_date_ids), 'feature_08'] = untouched_feature_08\n    \n    # Handly random missing values in date which is partially\n    # For other missing values, a combination of forward and backward padding was used\n    for feature in df.columns:\n        if feature not in systematic_missing_features and feature not in feature_21_26_27_31_missing_before_527 and feature != 'feature_08':\n            df[feature] = df.groupby('date_id')[feature].transform(lambda x: x.ffill().bfill())\n\n    df.loc[df['date_id'].isin(feature_08_missing_date_ids), 'feature_08'] = untouched_feature_08\n    \n    df.to_parquet(output_file_path)\n    print(f\"Processed and saved: {output_file_path}\")","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-15T13:26:57.985353Z","iopub.execute_input":"2024-12-15T13:26:57.985730Z","iopub.status.idle":"2024-12-15T13:26:58.070607Z","shell.execute_reply.started":"2024-12-15T13:26:57.985706Z","shell.execute_reply":"2024-12-15T13:26:58.069121Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"# Experiment 3: groupby (symbol_id) in partial missing with ffill_&_bfill\n\n* Partially missing:\n> We can group by **symbol_id**, so that when we fill forward or backward, we use the values before and after the current time under the same symbol_id, which makes more sense.","metadata":{}},{"cell_type":"code","source":"# df = pd.read_parquet(\"/kaggle/input/js-dpp-cvp/lower/combined_part2.parquet\")\n# df.head(10)\n\n# df_sorted = df.sort_values(by=['symbol_id', 'date_id', 'time_id'])\n# df_sorted.head(10)\n\n# df_resorted = df_sorted.sort_values(by=['date_id', 'time_id', 'symbol_id'])\n# df_resorted.head(10)","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"sfb_folder = '/kaggle/working/fbfill_gp_symbol_random_partial/'\nos.makedirs(sfb_folder, exist_ok=True)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-15T12:48:24.089211Z","iopub.execute_input":"2024-12-15T12:48:24.089839Z","iopub.status.idle":"2024-12-15T12:48:24.098288Z","shell.execute_reply.started":"2024-12-15T12:48:24.089782Z","shell.execute_reply":"2024-12-15T12:48:24.095606Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"%%time\nfor i in tqdm(range(5)):\n    file_path = os.path.join(folder_path, f'combined_part{i}.parquet')\n    output_file_path = os.path.join(sfb_folder, f'combined_part{i}.parquet')\n    \n    df = pd.read_parquet(file_path)\n    \n    for column in df.select_dtypes(include=['float16']).columns:\n        df[column] = df[column].astype('float32')\n    \n    # Jump systematically missing values\n    for feature in systematic_missing_features:\n        cutoff_time_id = systematic_missing_time_ids[feature]\n        df.loc[df['time_id'] <= cutoff_time_id, feature] = pd.NA\n        \n    # Jump random missing values in date which is completely\n    # Handle feature_21, feature_26, feature_27, feature_31, and keep missing values when date_id <= 527\n    for feature in feature_21_26_27_31_missing_before_527:\n        df.loc[df['date_id'] <= 527, feature] = df.loc[df['date_id'] <= 527, feature]\n    \n    # Handle feature_08, keep missing values in completely missing date\n    untouched_feature_08 = df['feature_08'].copy()\n    df.loc[df['date_id'].isin(feature_08_missing_date_ids), 'feature_08'] = untouched_feature_08\n\n    df_sorted = df.sort_values(by=['symbol_id', 'date_id', 'time_id'])\n\n    del df\n    \n    # Handly random missing values in date which is partially\n    # For other missing values, a combination of forward and backward padding was used\n    for feature in df_sorted.columns:\n        if feature not in systematic_missing_features and feature not in feature_21_26_27_31_missing_before_527 and feature != 'feature_08':\n            df_sorted[feature] = df_sorted.groupby(['date_id', 'symbol_id'])[feature].transform(lambda x: x.ffill().bfill())\n\n    df_sorted.loc[df_sorted['date_id'].isin(feature_08_missing_date_ids), 'feature_08'] = untouched_feature_08\n\n    df_resorted = df_sorted.sort_values(by=['date_id', 'time_id', 'symbol_id'])\n\n    del df_sorted\n    \n    df_resorted.to_parquet(output_file_path)\n    print(f\"Processed and saved: {output_file_path}\")","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2024-12-15T12:52:05.655760Z","iopub.execute_input":"2024-12-15T12:52:05.656143Z","iopub.status.idle":"2024-12-15T13:12:52.352420Z","shell.execute_reply.started":"2024-12-15T12:52:05.656118Z","shell.execute_reply":"2024-12-15T13:12:52.350721Z"}},"outputs":[],"execution_count":null}]}