{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.11.13","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":31089,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"# Disclaimer:\n\n# This code is a reference implementation extracted from production environment \n# and will not run directly. The competition dataset has certain truncations related \n# to personal data and internal ETL processes. Nevertheless, the code can be used \n# as a reference guide for similar implementations.","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-07-19T16:47:09.155555Z","iopub.status.idle":"2025-07-19T16:47:09.155907Z","shell.execute_reply.started":"2025-07-19T16:47:09.155708Z","shell.execute_reply":"2025-07-19T16:47:09.155728Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"def proccessing(df_answer) -> pd.DataFrame:\n    period_df = None\n    for tariff_id, content, ranker_id in df_answer.values:\n        json_data = json.loads(content)\n            \n        if json_data[\"routeData\"][\"searchRoute\"].count(\"/\") <= 1:\n            v2 = 'segments' in json_data.keys()\n            request_df = _json2table(json_data, v2)\n            request_df[\"selected\"] = (request_df[\"UID\"] == tariff_id).apply(\n                lambda x: 1 if x else 0\n            )\n            request_df[\"ranker_id\"] = ranker_id\n            if period_df is None:\n                period_df = request_df\n            else:\n                period_df = pd.concat([period_df, request_df])\n    return period_df\n\n\ndef __add_one(\n    prefix: str, key: str, length: int, df: dict, cur_elem, count_pricings: int\n) -> None:\n    \"\"\"\n    Function for writing a specific field from json: None, int, float, str, list, etc.\n    This function only receives lists with dict objects and dict variables themselves, as we break them down into\n    smaller entities.\n    params:\n        prefix (str) - string indicating the parent in the json request structure\n        key (str) - string name of the current field\n        length (int) - since there are gaps in the data, and writing them one by one each time is slow -\n        decided to fill them in chunks. Each time we get a new useful value, we fill all\n        values before it with None. length stores the current position of the element being read,\n        to fill all values before the current one with gaps (None).\n        df (dict) - pointer to the table where the parsing result is stored\n        cur_elem (any type) - value of the current element being added to the result table\n        count_pricings (int) - number of proposed options with this record (record multiplier)\n    \"\"\"\n    full_key = prefix + key\n\n    if full_key in df:\n        if len(df[full_key]) < length:\n            df[full_key].extend([None] * (length - len(df[full_key])))\n    else:\n        df[full_key] = [None] * length\n\n    df[full_key].extend([cur_elem] * count_pricings)\n\n\ndef __get_values(\n    dict_inside: dict, df: dict, count_pricings: int, prefix: str, length: int\n) -> None:\n    \"\"\"\n    Function for writing a specific field from json: None, int, float, str, list, etc.\n    This function only receives lists with dict objects and dict variables themselves, as we break them down into\n    smaller entities.\n    params:\n        dict_inside (dict) - input dictionary to process\n        df (dict) - pointer to the table where the parsing result is stored\n        count_pricings (int) - number of proposed options with this record (record multiplier)\n        prefix (str) - string indicating the parent in the json request structure\n        length (int) - since there are gaps in the data, and writing them one by one each time is slow -\n        decided to fill them in chunks. Each time we get a new useful value, we fill all\n        values before it with None. length stores the current position of the element being read,\n        to fill all values before the current one with gaps (None).\n    \"\"\"\n    for key, value in dict_inside.items():\n        # Skip all ids except UID\n        if key == \"id\":\n            # Save only one id for flight options\n            if prefix + key in [\n                \"data_pricings_id\",\n                \"data_$values_pricings_id\",\n                \"segments_id\",\n            ]:\n                if \"UID\" in df:\n                    df[\"UID\"].append(value)\n                else:\n                    df[\"UID\"] = [value]\n\n        if key == \"$id\":\n            continue\n\n        # Processing nested dictionaries\n        if isinstance(value, dict):\n            __get_values(value, df, count_pricings, prefix + key + \"_\", length)\n\n        # Processing missing values\n        elif value is None:\n            __add_one(prefix, key, length, df, value, count_pricings)\n\n        # inside legs there are several segments just like in values\n        elif isinstance(value, list):\n            if value:\n                # Processing lists with dictionaries\n                if isinstance(value[0], dict):\n                    if key in [\"pricings\", \"pricingInfo\"]:\n                        for item in value:\n                            __get_values(item, df, 1, prefix + f\"{key}_\", length)\n                    else:\n                        for i, item in enumerate(value):\n                            __get_values(\n                                item, df, count_pricings, prefix + f\"{key}{i}_\", length\n                            )\n\n                # Processing lists with other values\n                else:\n                    __add_one(prefix, key, length, df, value, count_pricings)\n            else:\n                # Processing empty lists\n                __add_one(prefix, key, length, df, value, count_pricings)\n        # Processing remaining regular values\n        else:\n            __add_one(prefix, key, length, df, value, count_pricings)\n\n\ndef __get_rec_values(\n    dict_inside: dict, df: dict, prefix: str, v2: bool = False\n) -> None:\n    \"\"\"\n    Main recursive function for parsing json file. Walks through nested dictionaries and lists and saves all data in df.\n    params:\n        dict_inside (dict) - Source dictionary. Contains information about client personal data,\n        request data and proposed flight options. This is what we parse into table df.\n        df (dict) - pointer to the table where the parsing result is stored\n        prefix (str) - string indicating the parent in the json request structure\n        v2 (bool) - JSON structure version\n    \"\"\"\n    for key, value in dict_inside.items():\n        if key in [\"$id\"]:\n            continue\n\n        # Processing personal data, etc.\n        if isinstance(value, dict):\n            __get_rec_values(value, df, prefix + key + \"_\", v2)\n\n        elif value is None:\n            df[prefix + key] = None\n\n        # Processing flight options\n        elif key == \"$values\":\n            length = 0\n            df[\"$values\"] = {}\n            for item in value:\n                __get_values(\n                    item,\n                    df[\"$values\"],\n                    len(item[\"pricings\"]),\n                    prefix + key + \"_\",\n                    length,\n                )\n                length += len(item[\"pricings\"])\n\n        # Processing flight options for second JSON version\n        elif (key == \"data\") & v2:\n            length = 0\n            df[\"data\"] = {}\n            for item in value:\n                __get_values(\n                    item, df[\"data\"], len(item[\"pricings\"]), prefix + key + \"_\", length\n                )\n                length += len(item[\"pricings\"])\n\n        # Processing segment options\n        elif (key == \"segments\") & v2:\n            df[\"segments\"] = {}\n            for item in value:\n                __get_values(item, df[\"segments\"], 1, prefix + key + \"_\", 0)\n        else:\n            df[prefix + key] = value\n\n\ndef __rename_columns(df_columns: pd.DataFrame, v2: bool = False):\n    \"\"\"\n    Function that renames columns to a more convenient form. Removes redundant parts from names,\n    which are formed as a result of field inheritance within the json file.\n        v2 (bool) - JSON structure version\n    \"\"\"\n    # New version of naming\n    prefixes_for_remove = ['routeData_', 'personalData_']\n    \n    if v2:\n        prefixes_for_remove.append(\"data_pricings_\")\n        prefixes_for_remove.append(\"metadata_\")\n        prefixes_for_remove.append(\"data_\")\n    else:\n        prefixes_for_remove.append(\"data_$values_pricings_\")\n        prefixes_for_remove.append(\"data_$values_\")\n\n    for each in prefixes_for_remove:\n        df_columns = df_columns.str.replace(each, \"\")\n    \n    return df_columns\n\n\ndef combine_dataframe(d: dict) -> pd.DataFrame:\n    \"\"\"\n    Converting dictionary to table with gap filling\n    \"\"\"\n    # Number of proposed options\n    length = len(d[\"UID\"])\n\n    # Fill missing data with None\n    for key in d.keys():\n        if len(d[key]) < length:\n            d[key].extend([None] * (length - len(d[key])))\n\n    # Convert dictionary to DataFrame\n    return pd.DataFrame(d, columns=d.keys())\n\n\ndef _json2table(\n    input_dict: dict, v2: bool = False, all_features: bool = False\n) -> pd.DataFrame:\n    \"\"\"\n    Converting dictionary to table with gap filling\n    \"\"\"\n    # Dictionary for collecting data from source dictionary\n    res_dict = {}\n    # Dictionary for personal data\n    personal_dict = {}\n\n    # Start parsing json\n    __get_rec_values(input_dict, res_dict, \"\", v2)\n\n    # Write parsed personal data\n    for key in res_dict.keys():\n        if ((key != \"$values\") & (v2 == False)) | (\n            (key != \"data\") & (key != \"segments\") & (v2)\n        ):\n            personal_dict[key] = res_dict.get(key)\n\n    # Convert dictionary to DataFrame\n    df = combine_dataframe(res_dict.get(\"data\") if v2 else res_dict.get(\"$values\"))\n\n    fares_df = pd.DataFrame()\n    fares_info_cols = [each for each in df.columns if \"faresInfo\" in each]\n    intent = 4 if v2 else 5\n    suffixes = set([\"_\".join(each.split(\"_\")[intent:]) for each in fares_info_cols])\n\n    # extract fare fields in breakdown by offers\n    for i in range(8):\n        if v2:\n            fares_cols = [\n                f\"data_pricings_pricingInfo_faresInfo{i}_{suffix}\"\n                for suffix in suffixes\n            ]\n        else:\n            fares_cols = [\n                f\"data_$values_pricings_pricingInfo_faresInfo{i}_{suffix}\"\n                for suffix in suffixes\n            ]\n\n        _tmp_df = df.reindex(columns=[\"UID\"] + fares_cols)\n        df = df.drop(columns=fares_cols, errors=\"ignore\")\n        _tmp_df.columns = [\"UID\"] + list(suffixes)\n        _tmp_df = _tmp_df[_tmp_df[\"applyToSegmentIds\"].notna()]\n        fares_df = pd.concat([fares_df, _tmp_df], axis=0)\n    fares_df = fares_df.explode(\"applyToSegmentIds\")\n\n    # Sometimes fare duplicates occur\n    fares_df = fares_df.drop_duplicates(subset=[\"UID\", \"applyToSegmentIds\"])\n\n    # extract values about travel segments\n    if v2:\n        df_segments = combine_dataframe(res_dict.get(\"segments\"))\n        df_segments.columns = df_segments.columns.str.replace(\"segments_\", \"\")\n\n        segments_df = pd.DataFrame()\n        for n_leg in range(MAX_LEGS):\n            leg_id_name = f\"data_legs{n_leg}_segments\"\n            if leg_id_name in df.columns:\n                _tmp_df = df[[\"UID\", leg_id_name]].explode(leg_id_name)\n                _tmp_df = _tmp_df.merge(\n                    df_segments,\n                    left_on=leg_id_name,\n                    right_on=\"UID\",\n                    how=\"left\",\n                    suffixes=[\"\", \"_seg\"],\n                )\n                _tmp_df[\"n_leg\"] = n_leg\n                _tmp_df[\"n_segment\"] = _tmp_df.groupby(\"UID\").cumcount()\n                _tmp_df = _tmp_df.drop(columns=[leg_id_name, \"UID_seg\"])\n                df = df.drop(columns=[f\"data_legs{n_leg}_segments\"])\n                segments_df = pd.concat([segments_df, _tmp_df], axis=0)\n    else:\n        suffixes = set(\n            [\n                \"_\".join(each.split(\"_\")[4:])\n                for each in df.columns\n                if (\"data_$values_legs\" in each) & (\"_segments\" in each)\n            ]\n        )\n        segments_df = pd.DataFrame()\n\n        for n_leg, n_segment in product(range(MAX_LEGS), range(MAX_SEGMENTS)):\n            seg_cols = [\n                f\"data_$values_legs{n_leg}_segments{n_segment}_{suffix}\"\n                for suffix in suffixes\n            ]\n            _tmp_df = df.reindex(columns=[\"UID\"] + seg_cols)\n            df = df.drop(columns=seg_cols, errors=\"ignore\")\n            _tmp_df.columns = [\"UID\"] + list(suffixes)\n            _tmp_df = _tmp_df[_tmp_df[\"id\"].notna()]\n            _tmp_df[\"n_leg\"] = n_leg\n            _tmp_df[\"n_segment\"] = n_segment\n            segments_df = pd.concat([segments_df, _tmp_df], axis=0)\n\n    # Merge segments and fares\n    merged_df = segments_df.merge(\n        fares_df,\n        how=\"left\",\n        left_on=[\"UID\", \"id\"],\n        right_on=[\"UID\", \"applyToSegmentIds\"],\n        suffixes=[\"\", \"Fares\"],\n    )\n    merged_df = merged_df.pivot(\n        index=[\"UID\", \"n_leg\"],\n        columns=[\"n_segment\"],\n    ).reset_index()\n    merged_df.columns = merged_df.columns.map(lambda x: f\"segments{x[1]}_{x[0]}\")\n    merged_df = merged_df.pivot(\n        index=[\"segments_UID\"],\n        columns=[\"segments_n_leg\"],\n    )\n    merged_df.columns = merged_df.columns.map(lambda x: f\"legs{x[1]}_{x[0]}\")\n\n    # Merge DataFrame with legs and travel segments\n    df = df.merge(merged_df, how=\"left\", left_on=\"UID\", right_index=True)\n\n    # Add personal data to DataFrame\n    for column in personal_dict.keys():\n        df[column] = personal_dict.get(column)\n    df.columns = __rename_columns(df.columns, v2)\n    \n    if all_features != True:\n        used_cols = (\n            USED_COLS\n            + [GROUP_COL, REQUEST_DATE]\n            + [\n                f\"legs{legn}_{leg_col}\"\n                for legn, leg_col in product(range(MAX_LEGS), LEGS_COLS)\n            ]\n            + [\n                f\"legs{legn}_segments{segment_n}_{leg_col}\"\n                for legn, segment_n, leg_col in product(\n                    range(MAX_LEGS), range(MAX_SEGMENTS), LEGS_SEGMENTS_COLS\n                )\n            ]\n        )\n        df = df.drop(columns=df.columns[df.columns.duplicated()])\n        df = df.reindex(columns=used_cols)\n\n    df = df.reindex(columns=sorted(df.columns))\n    return df","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"outputs":[],"execution_count":null}]}