{"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 of the notebook\nThe goal of this notebook is creating a pandas DataFrame from the list of training annotation JSON objects, whose structure is reported below:\n\n* `source` Whether `generated` or `extracted`.\n* `chart-type` One of `dot`, `horizontal_bar`, `vertical_bar`, `line`, `scatter`.\n* `plot-bb` Bounding box of the plot within the figure, given by `height`, `width`, `x0`, and `y0`.\n* `text/id` Identifier for a text item within the figure.\n* `text/polygon` Region bounding the text item in the image.\n* `text/text` The text itself.\n* `text/role` The function of the text in the image, whether `chart_title`, `axis_title`, `tick_label`, etc.\n* `axes/{x|y}-axis/ticks/id` Identifier matching the tick to the associated text element id.\n* `axes/{x|y}-axis/ticks/tick_pt` Coordinates of each tick in the figure.\n* `axes/{x|y}-axis/tick-type` The graphical depiction of the tick element.\n* `axes/{x|y}-axis/values-type` The data type of the values represented by the tick element, whether `categorical` or `numerical`. This field determines how the predicted data series are scored. \n* `data-series/{x|y}` The x and y coordinates of values depicted in the figure. For the test set images, this is the target to be predicted.\n\nThe pandas Dataframe this notebook aims to create is characterized by the following features:\n1. each column is associated to a key of at least one JSON object. Since the lengths of the lists corresponding to keys `text/id`, `axes/{x|y}-axis/ticks/id` and `data-series/{x|y}` are not the same for all JSON objects, the DataFrame contains several NaN values;\n2. column indices are set so as to follow the order of the keys in the JSON objects. As an example, the first four columns are `source`, `chart-type`, `plot-bb/height`, `plot-bb/width`. This feature, although not essential, was implemented to facilitate the DataFrame visualization;\n3. the last column of the DataFrame, `filename`, stores training annotation filenames. Such column can be used to match each annotation with the corresponding image.\n\nThe relation between JSON keys and DataFrame column names is shown in the table below\n<table style=\"margin: 0px auto\">\n<tr>\n    <th style=\"border: 1px solid black; background-color: #EEEEEE\"><center>JSON</center></th>\n<th style=\"border: 1px solid black; background-color: #EEEEEE\"><center>DataFrame columns</center></th>\n</tr>\n<tr style=\"border: 1px solid black\">\n<td style=\"background-color:#FAFAFA; border: 1px solid black\">\n<pre style=\"background-color:#FAFAFA; padding: 0 8px; border: 0px solid transparent\">\n{\n    \"source\": \"extracted\",\n    \"chart-type\": \"line\",\n    \"plot-bb\": {\n        \"height\": 236,\n        \"width\": 426,\n        \"x0\": 63,\n        \"y0\": 15\n    }\n    \"text\": [\n        {\n            \"id\": 0,\n            \"polygon\": {\n                \"x0\": 11,\n                \"x1\": 23,\n            },\n            \"text\": \"Millions\",\n            \"role\": \"axis_title\"\n        },\n        {\n            \"id\": 1,\n            \"polygon\": {\n                \"x0\": 38,\n                \"x1\": 53\n             },\n             \"text\": \"20\",\n             \"role\": \"tick_label\"\n        }\n    ]\n}\n</pre>\n</td>\n<td style=\"background-color:#FAFAFA; border: 1px solid black\">\n<pre style=\"background-color:#FAFAFA; padding: 0 32px; border: 0px solid transparent\">\nsource\nchart-type\nplot-bb/height\nplot-bb/width\nplot-bb/x0\nplot-bb/y0\ntext/0/id\ntext/0/polygon/x0\ntext/0/polygon/x1\ntext/0/text\ntext/0/role\ntext/1/id\ntext/1/polygon/x0\ntext/1/polygon/x1\ntext/1/text\ntext/1/role\n</pre>\n</td>\n</tr>\n</table>","metadata":{}},{"cell_type":"markdown","source":"## 2. Libraries import","metadata":{}},{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport json\nimport os\nimport itertools\nfrom tqdm import tqdm","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2023-03-28T18:02:26.635254Z","iopub.execute_input":"2023-03-28T18:02:26.635584Z","iopub.status.idle":"2023-03-28T18:02:26.665943Z","shell.execute_reply.started":"2023-03-28T18:02:26.635556Z","shell.execute_reply":"2023-03-28T18:02:26.664732Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 3. Functions definition\n\n### 3.1 Annotations loading\n\n**load_annotations(annotations_folder_path)**\n\n* `annotations_folder_path` **str**. Path of the folder containing annotation JSON objects\n\nConverts annotation JSON objects to dictionaries and returns list of annotation dictionaries.","metadata":{}},{"cell_type":"code","source":"def load_annotations(annotations_folder_path):\n    annotations = []\n    for annotation_name in tqdm(os.listdir(annotations_folder_path)):\n        annotation_path = os.path.join(annotations_folder_path, annotation_name)\n        annotation = json.load(open(annotation_path))\n        annotation[\"filename\"] = annotation_name.replace(\".json\", \"\")\n        annotations.append(annotation)\n    return annotations","metadata":{"execution":{"iopub.status.busy":"2023-03-28T18:02:26.667927Z","iopub.execute_input":"2023-03-28T18:02:26.668210Z","iopub.status.idle":"2023-03-28T18:02:26.674058Z","shell.execute_reply.started":"2023-03-28T18:02:26.668179Z","shell.execute_reply":"2023-03-28T18:02:26.673197Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.2 Nested dictionary flattening\n\n**get_flattened_dict(input_dict, base_key=\"\", output_dict={}, index_value=None)**\n\n* `input_dict` **dict**. Dictionary to be flattened\n* `base_key` **str**. Auxiliary string used in recursive function calls to generate DataFrame column names corresponding to nested JSON keys \n* `output_dict` **dict**. Dictionary containing key-value pairs obtained from the flattening operation \n* `index_value` **str**. String identifying the processed dictionary. Used for dictionaries contained in lists and lacking the `id` key.\n\nFlattens a nested annotation dictionary. Returns a dictionary whose keys are used as column names in the annotation DataFrame.","metadata":{}},{"cell_type":"code","source":"def get_flattened_dict(input_dict, base_key=\"\", output_dict={}, index_value=None):\n    \n    for key in input_dict.keys():\n        \n        if base_key == \"\":\n            full_key = key     \n        else:\n            if index_value is None:\n                full_key = base_key + \"/\" + key\n            else:\n                full_key = base_key + \"/\" + key + \"/\" + index_value\n\n        if isinstance(input_dict[key], dict):\n            get_flattened_dict(input_dict[key], full_key, output_dict)\n            \n        elif isinstance(input_dict[key], list):\n            for index, item in enumerate(input_dict[key]):\n                if \"id\" in item.keys():\n                    full_list_key = full_key + \"/\" + str(item[\"id\"])\n                    get_flattened_dict(item, full_list_key, output_dict)\n                else:\n                    full_list_key = full_key\n                    get_flattened_dict(item, full_list_key, output_dict, str(index))\n                    \n        else:\n            output_dict[full_key] = input_dict[key]","metadata":{"execution":{"iopub.status.busy":"2023-03-28T18:02:26.675358Z","iopub.execute_input":"2023-03-28T18:02:26.675604Z","iopub.status.idle":"2023-03-28T18:02:26.683861Z","shell.execute_reply.started":"2023-03-28T18:02:26.675579Z","shell.execute_reply":"2023-03-28T18:02:26.682990Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.3 DataFrame columns sorting\n\n**KEY_ORDER_DICT**\n\nDictionary defining the order relation between the annotation DataFrame columns. The dictionary keys are the annotation JSON objects keys, while the dictionary values are strings acting as indices.\n\nThe `-` separator delimits 3 substrings for each index:\n\n1. the first substring, consisting of 2 digits, defines the order relation between first-level keys, e.g. `source`, `char-type`, `plot-bb` etc.;\n2. the second substring, consisting of 5 chars, is equal to either \"00000\" (for keys whose value is not a list) or \"000id\" (for keys whose value is a list). This substring allows to define the order relation between objects contained in lists by replacing substring \"id\" with the `id` of each object;\n3. the third substring, consisting of 2 digits, defines the order relation between the properties of objects contained in lists, e.g.`id`, `poligon`, `text`, `role`, etc. ","metadata":{}},{"cell_type":"code","source":"KEY_ORDER_DICT = {\n    \"source\": \"00-00000-00\",\n    \"chart-type\": \"01-00000-00\",\n    \"plot-bb\": \"02-00000-00\",\n    \"text\": {\n        \"id\": \"03-0000id-00\",\n        \"polygon\": \"03-0000id-01\",\n        \"text\": \"03-0000id-02\",\n        \"role\": \"03-0000id-03\"\n    },\n    \"axes\": {\n        \"x-axis\": {\n            \"ticks\": {\n                \"id\": \"04-0000id-00\",\n                \"tick_pt\": \"04-0000id-01\"\n            }, \n            \"tick-type\": \"05-00000-00\",\n            \"values-type\": \"06-00000-00\"\n        },\n        \"y-axis\": {\n            \"ticks\": {\n                \"id\": \"07-0000id-00\",\n                \"tick_pt\": \"07-0000id-01\",\n            }, \n            \"tick-type\": \"08-00000-00\",\n            \"values-type\": \"09-00000-00\",\n        }\n    }, \n    \"data-series\": {\n        \"x\": \"10-0000id-00\",\n        \"y\": \"11-0000id-00\",\n    },\n    \"filename\": \"12-00000-00\"\n}","metadata":{"execution":{"iopub.status.busy":"2023-03-28T18:02:26.685708Z","iopub.execute_input":"2023-03-28T18:02:26.685938Z","iopub.status.idle":"2023-03-28T18:02:26.694130Z","shell.execute_reply.started":"2023-03-28T18:02:26.685915Z","shell.execute_reply":"2023-03-28T18:02:26.693122Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**get_column_index(split_key, key_order_dict, level=0, id_value=None)**\n\n* `split_key` **list**. List of keys obtained by splitting a DataFrame column name using `/` as separator  \n* `key_order_dict` **dict**. Dictionary defining the order relation between the keys of annotation JSON objects \n* `level` **int**. Level of the key to be processed\n* `id_value` **int**. Integer identifying each dictionary contained in a list\n\nReturns a string which is used to sort the annotation DataFrame columns according to the order relation defined by `key_order_dict`.","metadata":{}},{"cell_type":"code","source":"def get_column_index(split_key, key_order_dict, level=0, id_value=None):\n    key = split_key[level]\n   \n    if key == \"ticks\" or (key == \"text\" and level == 0):\n        level = level + 1\n        id_value = split_key[level]\n        \n    if key == \"data-series\":\n        id_value = split_key[level + 2]\n        \n    if isinstance(key_order_dict[key], dict):\n        return get_column_index(split_key, key_order_dict[key], level+1, id_value)\n    \n    else:\n        column_index = key_order_dict[key]\n        if id_value is not None:\n            split_column_index = column_index.split(\"-\")\n            split_column_index[1] = split_column_index[1].replace(\"id\", id_value)[-5:]\n            column_index = \"-\".join(split_column_index)\n        return column_index","metadata":{"execution":{"iopub.status.busy":"2023-03-28T18:02:26.695395Z","iopub.execute_input":"2023-03-28T18:02:26.695977Z","iopub.status.idle":"2023-03-28T18:02:26.707286Z","shell.execute_reply.started":"2023-03-28T18:02:26.695943Z","shell.execute_reply":"2023-03-28T18:02:26.706223Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**get_sorted_columns(annotation_df_columns)**\n* `annotation_df_columns` **list**. List of annotation DataFrame columns\n\nSorts DataFrame columns.","metadata":{}},{"cell_type":"code","source":"def get_sorted_columns(annotation_df_columns):\n    sorted_annotation_df_cols = []\n    for annotation_df_col in annotation_df_columns:\n        split_col = annotation_df_col.split(\"/\")\n        col_index = get_column_index(split_col, KEY_ORDER_DICT, 0)\n        sorted_annotation_df_cols.append(str(col_index) + \"#\" + annotation_df_col)\n    sorted_annotation_df_cols.sort()\n    sorted_annotation_df_cols = [sorted_annotation_df_col.split(\"#\")[1] for sorted_annotation_df_col in sorted_annotation_df_cols]\n    return sorted_annotation_df_cols","metadata":{"execution":{"iopub.status.busy":"2023-03-28T18:02:26.708929Z","iopub.execute_input":"2023-03-28T18:02:26.709643Z","iopub.status.idle":"2023-03-28T18:02:26.719921Z","shell.execute_reply.started":"2023-03-28T18:02:26.709608Z","shell.execute_reply":"2023-03-28T18:02:26.718668Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.4. DataFrame building\n\n**get_annotation_df(annotations)**\n\n* `annotations` **list**. List of annotation dictionaries\n\nReturns the annotation DataFrame.","metadata":{}},{"cell_type":"code","source":"def get_annotation_df(annotations):\n    annotation_dicts = []\n    for annotation in annotations:\n        annotation_dict = {}\n        get_flattened_dict(annotation, \"\", annotation_dict)\n        annotation_dicts.append(annotation_dict)\n    annotation_dicts_keys = set(list(itertools.chain.from_iterable([list(annotation_dict.keys()) for annotation_dict in annotation_dicts])))\n    annotation_dicts_keys = get_sorted_columns(list(annotation_dicts_keys))\n    annotation_df_dict = {key: [] for key in annotation_dicts_keys}\n    for annotation_dict in annotation_dicts:\n        for key in annotation_df_dict:\n            value = annotation_dict[key] if key in annotation_dict else np.nan\n            annotation_df_dict[key].append(value)\n    json_df = pd.DataFrame(annotation_df_dict)\n    return json_df","metadata":{"execution":{"iopub.status.busy":"2023-03-28T18:02:26.734976Z","iopub.execute_input":"2023-03-28T18:02:26.735570Z","iopub.status.idle":"2023-03-28T18:02:26.742494Z","shell.execute_reply.started":"2023-03-28T18:02:26.735542Z","shell.execute_reply":"2023-03-28T18:02:26.741692Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 4. Code testing","metadata":{}},{"cell_type":"code","source":"train_annotations_path = \"/kaggle/input/benetech-making-graphs-accessible/train/annotations\"\ntrain_annotations = load_annotations(train_annotations_path)","metadata":{"execution":{"iopub.status.busy":"2023-03-28T18:02:26.745466Z","iopub.execute_input":"2023-03-28T18:02:26.745886Z","iopub.status.idle":"2023-03-28T18:06:17.149849Z","shell.execute_reply.started":"2023-03-28T18:02:26.745860Z","shell.execute_reply":"2023-03-28T18:06:17.149118Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"annotation_df = get_annotation_df(train_annotations)\n\nprint(annotation_df.shape)\nannotation_df.head()","metadata":{"execution":{"iopub.status.busy":"2023-03-28T18:06:17.150906Z","iopub.execute_input":"2023-03-28T18:06:17.151624Z","iopub.status.idle":"2023-03-28T18:07:08.255679Z","shell.execute_reply.started":"2023-03-28T18:06:17.151597Z","shell.execute_reply":"2023-03-28T18:07:08.254380Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 5. Conclusion\n\nThank you for taking the time to read this notebook. I hope you found it useful. Any comments or suggestions are highly appreciated.\n\nBelow are two notebooks I found particularly interesting:\n* [📊Simple EDA and flattening JSON data with pandas](https://www.kaggle.com/code/kimtaehun/simple-eda-and-flattening-json-data-with-pandas) by [DataManyo](https://www.kaggle.com/kimtaehun)\n* [[EDA] 📊MGA📈 ~ Plot charts | Interactive anns](https://www.kaggle.com/code/leonidkulyk/eda-mga-plot-charts-interactive-anns) by [Leonid Kulyk](https://www.kaggle.com/leonidkulyk)","metadata":{}}]}