4-GeneratingLabels.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dcfd5765",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pandas as pd\n",
    "import os\n",
    "import multiprocessing\n",
    "\n",
    "import numbers\n",
    "import datetime\n",
    "import dateutil.parser\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "082d72f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Path to expanded tables\n",
    "dir_ = 'output-data/expanded-tables/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "468fc547",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Existing English Tables\n",
    "existing = open(\"output-data/english_table_names.txt\", 'r')\n",
    "existing_english_tables = [line.replace('\\n', '') for line in existing.readlines()]\n",
    "len(existing_english_tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f82df036",
   "metadata": {},
   "outputs": [],
   "source": [
    "#New English Tables\n",
    "new_english_tables = os.listdir('output-data/new-english-tables/')\n",
    "len(new_english_tables)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "695a5d7d",
   "metadata": {},
   "source": [
    "## Statistics about the expanded English tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1faecf10",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_table_statistics(file_name):\n",
    "    \n",
    "    if file_name in existing_english_tables:\n",
    "        file = 'output-data/expanded-tables/' + file_name\n",
    "    else:\n",
    "        file = 'output-data/new-english-tables/' + file_name\n",
    "    \n",
    "    try:\n",
    "        df = pd.read_json(file, compression='gzip', lines=True)\n",
    "        \n",
    "        number_of_rows = len(df.index)\n",
    "        column_count = len(df.columns)\n",
    "        empty_cells = df.isna().sum().sum()\n",
    "        total_cells = number_of_rows * column_count\n",
    "\n",
    "        column_name_and_density = {}\n",
    "        overall_table_density = int((total_cells - empty_cells)/total_cells *100)\n",
    "\n",
    "        for index, column in df.isna().sum().iteritems():\n",
    "            column_name_and_density[index] = int(((number_of_rows - column) / number_of_rows) * 100)\n",
    "    \n",
    "        return [ file_name, number_of_rows, column_count, column_name_and_density, overall_table_density ]\n",
    "\n",
    "    except ValueError:\n",
    "        print(file_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c29f2741",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = existing_english_tables + new_english_tables\n",
    "pool = multiprocessing.Pool(processes=30)\n",
    "res = pool.map(get_table_statistics, tables)\n",
    "pool.close()\n",
    "pool.join()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b2dc763",
   "metadata": {},
   "outputs": [],
   "source": [
    "r = [re for re in res if re]\n",
    "statistics = pd.DataFrame(r, columns=['file_name', 'number_of_rows', 'column_count', 'column_name_and_density', 'overall_table_density'])\n",
    "statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5da524e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "statistics.to_csv('output-data/statistics/expanded_tables_statistics.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dece6ac1",
   "metadata": {},
   "source": [
    "### Generate labels for columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5981768d",
   "metadata": {},
   "outputs": [],
   "source": [
    "statistics = pd.read_csv('output-data/statistics/expanded_tables_statistics.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4282e43a",
   "metadata": {},
   "outputs": [],
   "source": [
    "labels = pd.read_csv('data/Final CTA and CPA Labels.csv')\n",
    "labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "966a31cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "#column name to CPA label\n",
    "column_cpa = {}\n",
    "for index, row in labels.iterrows():\n",
    "    if row['column_name'] == 'name':\n",
    "        column_cpa[row['column_name']] = 'name'\n",
    "    else:\n",
    "        column_cpa[row['column_name']] = row['CPA label']     "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92d4addc",
   "metadata": {},
   "outputs": [],
   "source": [
    "#column name to CTA label\n",
    "column_cta = {}\n",
    "for index, row in labels.iterrows():\n",
    "    if row['column_name'] == 'name':\n",
    "        column_cta[row['class']+row['column_name']] = row['CTA label']\n",
    "    else:\n",
    "        column_cta[row['column_name']] = row['CTA label']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1dd45189",
   "metadata": {},
   "outputs": [],
   "source": [
    "#fallback columns: if there are multiple cta labels possible\n",
    "column_fallback = {}\n",
    "for index, row in labels.iterrows():\n",
    "     if not pd.isnull(row['fallback_CTA_label']):\n",
    "        column_fallback[row['column_name']] = row['fallback_CTA_label']\n",
    "\n",
    "# manual correction\n",
    "column_fallback['doortime'] = 'DateTime'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3fd8ef66",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Minimum 3 columns\n",
    "tables_dict = statistics.loc[ statistics['column_count'] >=3 ].to_dict('records')\n",
    "len(tables_dict)\n",
    "\n",
    "tables = {}\n",
    "for row in tables_dict:\n",
    "    tables[row['file_name']] = row\n",
    "\n",
    "#Existing English Tables\n",
    "existing = open(\"output-data/english_table_names.txt\", 'r')\n",
    "existing_english_tables = [line.replace('\\n', '') for line in existing.readlines()]\n",
    "len(existing_english_tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "369ed4bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "time_format = 'T*[0-9]{2}:[0-9]{2}:*[0-9]{0,2}'\n",
    "months = ['jan', 'feb', 'mar', 'may', 'apr', 'june', 'july', 'sep', 'oct', 'nov', 'dec', 'january', 'february', 'march', 'april', 'may', 'june', 'july', 'august', 'septmeber', 'october', 'november', 'december']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e325fa44",
   "metadata": {},
   "outputs": [],
   "source": [
    "#what properties do the expected schema.org types have\n",
    "types_dict = {}\n",
    "\n",
    "for cta in column_cta:\n",
    "    if ', ' in column_cta[cta]:\n",
    "        types = column_cta[cta].split(', ')\n",
    "        \n",
    "        for t in types:\n",
    "            df = pd.read_csv('data/PropsToTypes/'+t+'_propsToTypes.csv')\n",
    "            df['prop'] = df['property'].apply(lambda row: row.split('.')[1].lower())\n",
    "            props = df['prop'].tolist()\n",
    "            types_dict[t] = props\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce95a0c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_dict_type(row, column_types, column_name, type_counts):\n",
    "    \n",
    "    #Each index represents a Type, each number represents the number of overlapping properties to a type\n",
    "    find_match = []\n",
    "                    \n",
    "    for column_type in column_types:\n",
    "        #How many row properties are in common with each expected column type:\n",
    "        find_match.append( len( [prop for prop in list(row.keys()) if prop in types_dict[column_type] ]) )\n",
    "\n",
    "\n",
    "    #If find match are equal: add 1 count to all types\n",
    "    if all(x==find_match[0] for x in find_match):\n",
    "        if 'ItemList' in column_types:\n",
    "            for column_type in column_types:\n",
    "                if column_type != 'ItemList':\n",
    "                    if column_type not in type_counts:\n",
    "                        type_counts[column_type] = 0\n",
    "                    type_counts[column_type] += 1\n",
    "        \n",
    "        else:\n",
    "            \n",
    "            for column_type in column_types:\n",
    "                if column_type not in type_counts:\n",
    "                    type_counts[column_type] = 0\n",
    "                type_counts[column_type] += 1\n",
    "\n",
    "    else:\n",
    "        #1 count to majority type and 0 for all others\n",
    "        if column_types[find_match.index(max(find_match))] not in type_counts:\n",
    "            type_counts[column_types[find_match.index(max(find_match))]] = 0\n",
    "\n",
    "        type_counts[column_types[find_match.index(max(find_match))]] += 1\n",
    "\n",
    "    return type_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d83d6a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_list_of_dict_type(row, column_types, column_name, type_counts):\n",
    "\n",
    "    row_type = []\n",
    "    list_type_counts = {}\n",
    "    \n",
    "    #For element in row-list:\n",
    "    for element in row:\n",
    "        \n",
    "        if isinstance(element, dict):\n",
    "            list_type_counts = get_dict_type(element, column_types, column_name, list_type_counts) \n",
    "        elif isinstance(element, str):\n",
    "            list_type_counts = get_str_type(element, column_types, column_name, list_type_counts)\n",
    "        elif isinstance(element, list):\n",
    "            list_type_counts = get_list_of_dict_type(element, column_types, column_name, list_type_counts)\n",
    "        elif pd.isnull(element):\n",
    "            if 'ItemList' in column_types:\n",
    "                if 'ItemList' not in list_type_counts:\n",
    "                    list_type_counts['ItemList'] = 0\n",
    "                list_type_counts['ItemList'] += 1\n",
    "            else:\n",
    "                for column_type in column_types:\n",
    "                    if column_type not in list_type_counts:\n",
    "                        list_type_counts[column_type] = 0\n",
    "                    list_type_counts[column_type] += 1\n",
    "    \n",
    "    \n",
    "    if all(x==list(list_type_counts.values())[0] for x in list(list_type_counts.values())) and len(list_type_counts) > 1:\n",
    "        if 'ItemList' in column_types:\n",
    "            for column_type in column_types:\n",
    "                if column_type == 'ItemList':\n",
    "                    list_type_counts[column_type] = 0\n",
    "                else:\n",
    "                    list_type_counts[column_type] = 1\n",
    "        else:\n",
    "            for column_type in column_types:\n",
    "                list_type_counts[column_type] = 1\n",
    "    elif len(list_type_counts) > 1:\n",
    "        for column_type in column_types:\n",
    "            if column_type == max(list_type_counts, key=list_type_counts.get):\n",
    "                list_type_counts[column_type] = 1\n",
    "            else:\n",
    "                list_type_counts[column_type] = 0\n",
    "    else:\n",
    "        if list_type_counts[max(list_type_counts, key=list_type_counts.get)] > 0:\n",
    "            list_type_counts[max(list_type_counts, key=list_type_counts.get)] = 1\n",
    "\n",
    "    for found_types in list_type_counts:\n",
    "        if list_type_counts[found_types]:\n",
    "            if found_types in type_counts:\n",
    "                type_counts[found_types] += 1\n",
    "            else:\n",
    "                type_counts[found_types] = 1\n",
    "    \n",
    "    return type_counts            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23c3342d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_str_type(row, column_types, column_name, type_counts):\n",
    "\n",
    "    #If row is a number\n",
    "    if isinstance(row, numbers.Number):\n",
    "        if 'Number' not in column_types:\n",
    "            if (isinstance(row, int) or row.is_integer()) and 'Integer' in column_types:\n",
    "                if 'Integer' not in type_counts:\n",
    "                    type_counts['Integer'] = 0\n",
    "                type_counts['Integer'] += 1\n",
    "                \n",
    "            elif 'Text' in column_types:\n",
    "                if 'Text' not in type_counts:\n",
    "                    type_counts['Text'] = 0\n",
    "                type_counts['Text'] += 1\n",
    "\n",
    "            elif 'Date' in column_types:\n",
    "                if 'Date' not in type_counts:\n",
    "                    type_counts['Date'] = 0\n",
    "                type_counts['Date'] += 1\n",
    "\n",
    "            else:\n",
    "                for column_type in column_types:\n",
    "                    if column_type not in type_counts:\n",
    "                        type_counts[column_type] = 0\n",
    "                    type_counts[column_type] += 1\n",
    "        else:\n",
    "            if 'Number' not in type_counts:\n",
    "                type_counts['Number'] = 0\n",
    "            type_counts['Number'] += 1\n",
    "    else:\n",
    "        try:\n",
    "            #If row is Date/DateTime/Time\n",
    "            if isinstance(dateutil.parser.parse(row), datetime.date):\n",
    "                row_date = dateutil.parser.parse(row)\n",
    "\n",
    "                if re.match(time_format, row) and 'Time' in column_types:\n",
    "                    if 'Time' not in type_counts:\n",
    "                        type_counts['Time'] = 0\n",
    "                    type_counts['Time'] += 1\n",
    "                elif ':' in row and 'DateTime' in column_types:\n",
    "                    if 'DateTime' not in type_counts:\n",
    "                        type_counts['DateTime'] = 0\n",
    "                    type_counts['DateTime'] += 1\n",
    "                elif ':' in row and 'Date' in column_types :\n",
    "                    if 'Date' not in type_counts:\n",
    "                        type_counts['Date'] = 0\n",
    "                    type_counts['Date'] += 1\n",
    "                elif ':' not in row and 'Date' in column_types:\n",
    "                    if 'Date' not in type_counts:\n",
    "                        type_counts['Date'] = 0\n",
    "                    type_counts['Date'] += 1\n",
    "                elif ':' not in row and 'DateTime' in column_types:\n",
    "                    if 'DateTime' not in type_counts:\n",
    "                        type_counts['DateTime'] = 0\n",
    "                    type_counts['DateTime'] += 1\n",
    "                elif 'Text' in column_types:\n",
    "                    if 'Text' not in type_counts:\n",
    "                        type_counts['Text'] = 0\n",
    "                    type_counts['Text'] += 1\n",
    "                else:\n",
    "                    if 'Wrong' not in type_counts:\n",
    "                        type_counts['Wrong'] = 0\n",
    "                    type_counts['Wrong'] += 1\n",
    "\n",
    "        except Exception:\n",
    "            #if row is a string\n",
    "            if 'Date' in column_types or 'DateTime' in column_types or 'Time' in column_types:            \n",
    "                if ':' in row.lower() or 'pm' in row.lower() or 'am' in row.lower():\n",
    "                    if any(month in row.lower() for month in months) and 'DateTime' in column_types:\n",
    "                        if 'DateTime' not in type_counts:\n",
    "                            type_counts['DateTime'] = 0\n",
    "                        type_counts['DateTime'] += 1\n",
    "                    elif 'Time' in column_types:\n",
    "                        if 'Time' not in type_counts:\n",
    "                            type_counts['Time'] = 0\n",
    "                        type_counts['Time'] += 1\n",
    "                    elif 'Date' in column_types:\n",
    "                        if 'Date' not in type_counts:\n",
    "                            type_counts['Date'] = 0\n",
    "                        type_counts['Date'] += 1\n",
    "                elif 'Date' in column_types:\n",
    "                    if 'Date' not in type_counts:\n",
    "                        type_counts['Date'] = 0\n",
    "                    type_counts['Date'] += 1\n",
    "                elif 'DateTime' in column_types:\n",
    "                    if 'DateTime' not in type_counts:\n",
    "                        type_counts['DateTime'] = 0\n",
    "                    type_counts['DateTime'] += 1\n",
    "\n",
    "            elif 'URL' in column_types:\n",
    "                if (re.search('\\/[\\w_-]+\\/', row) or 'http' in row):\n",
    "                    if 'URL' not in type_counts:\n",
    "                        type_counts['URL'] = 0\n",
    "                    type_counts['URL'] += 1\n",
    "                else:\n",
    "                    for t in column_types:\n",
    "                        if t != 'URL':\n",
    "                            if t not in type_counts:\n",
    "                                type_counts[t] = 0\n",
    "                            type_counts[t] += 1\n",
    "\n",
    "            elif 'Text' in column_types:\n",
    "                if ('False' == row or 'True' == row or 'false' == row or 'true' == row) and 'Boolean' in column_types:\n",
    "                    if 'Boolean' not in type_counts:\n",
    "                        type_counts['Boolean'] = 0\n",
    "                    type_counts['Boolean'] += 1\n",
    "                else:\n",
    "                    if 'Text' not in type_counts:\n",
    "                        type_counts['Text'] = 0\n",
    "                    type_counts['Text'] += 1\n",
    "\n",
    "            elif 'ItemList' in column_types:\n",
    "                if 'ItemList' not in type_counts:\n",
    "                        type_counts['ItemList'] = 0\n",
    "                type_counts['ItemList'] += 1\n",
    "\n",
    "            else:\n",
    "                for column_type in column_types:\n",
    "                    if column_type not in type_counts:\n",
    "                        type_counts[column_type] = 0\n",
    "\n",
    "                    type_counts[column_type] += 1\n",
    "    \n",
    "    return type_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa6a84f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_type(file_name, column_name):\n",
    "    \n",
    "    if file_name in existing_english_tables:\n",
    "        file = 'output-data/expanded-tables/' + file_name\n",
    "    else:\n",
    "        file = 'output-data/new-english-tables/' + file_name\n",
    "        \n",
    "    df = pd.read_json('data/stc-zip-files/' + file_name, compression='gzip', lines=True)\n",
    "        \n",
    "    if ':name' in column_name:\n",
    "        column_name = column_name.split(':')[0]\n",
    "    \n",
    "    #Open table\n",
    "    if column_name not in df.columns:\n",
    "        #If new column check in new expanded tables for type\n",
    "        df = pd.read_json(file, compression='gzip', lines=True)\n",
    "        \n",
    "    # Check the types of all rows if all column values not empty:\n",
    "    if len(df[df[column_name].notna()][column_name].tolist()):\n",
    "        column_types = column_cta[column_name].split(', ')\n",
    "        # Count how many rows fall under a type\n",
    "        type_counts = {}\n",
    "\n",
    "        for row in df[df[column_name].notna()][column_name].tolist():\n",
    "\n",
    "            #Check if row is a dictionary\n",
    "            if isinstance(row, dict):\n",
    "                type_counts = get_dict_type(row, column_types, column_name, type_counts)\n",
    "\n",
    "            #If row is a list\n",
    "            elif isinstance(row, list):\n",
    "                type_counts = get_list_of_dict_type(row, column_types, column_name, type_counts)\n",
    "\n",
    "            #If row is empty/null: add count to all types == Undefined type\n",
    "            elif pd.isnull(row):\n",
    "                for column_type in column_types:\n",
    "                    if column_type not in type_counts:\n",
    "                        type_counts[column_type] = 0\n",
    "                    type_counts[column_type] += 1\n",
    "\n",
    "            #Else if row is a string, number or datetime value\n",
    "            else:\n",
    "                type_counts = get_str_type(row, column_types, column_name, type_counts)\n",
    "\n",
    "        #Choose the type where most of rows belong to:\n",
    "        row_number = len(df[df[column_name].notna()][column_name].tolist()) #Total number of non null rows\n",
    "        majority_rows = type_counts[max(type_counts, key=type_counts.get)] #How many rows with majority type?\n",
    "\n",
    "        #Majority type\n",
    "        if all(x==list(type_counts.values())[0] for x in list(type_counts.values())) and len(type_counts) > 1:\n",
    "            majority_type = column_fallback[column_name]\n",
    "        else:\n",
    "            majority_type = max(type_counts, key=type_counts.get)\n",
    "\n",
    "        return majority_type\n",
    "        \n",
    "    else:\n",
    "        #Return None for empty columns\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de83c935",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_labels(table):\n",
    "    \n",
    "    try:\n",
    "    \n",
    "        class_name = table.split(\"_\")[0] #Schema.org type\n",
    "        columns = list(eval(tables[table]['column_name_and_density']).keys())\n",
    "        columns_cpa_labels = {}\n",
    "        columns_cta_labels = {}\n",
    "\n",
    "\n",
    "        for column in columns:\n",
    "            columns_cpa_labels[column] = column_cpa[column]\n",
    "            \n",
    "            column_before = column\n",
    "\n",
    "            if column == 'name':\n",
    "                column = class_name + 'name'\n",
    "\n",
    "            #For CTA label of columns that can have multiple types: try to check from its original column in the tables what type it is\n",
    "            if ', ' in column_cta[column]:     \n",
    "                columns_cta_labels[column] = get_type(table, column)\n",
    "            else:   \n",
    "                columns_cta_labels[column_before] = column_cta[column]\n",
    "\n",
    "        return [\n",
    "            class_name, \n",
    "            table, \n",
    "            tables[table]['column_count'], \n",
    "            tables[table]['number_of_rows'], \n",
    "            tables[table]['overall_table_density'], \n",
    "            tables[table]['column_name_and_density'], \n",
    "            columns_cpa_labels, \n",
    "            columns_cta_labels\n",
    "        ]\n",
    "    \n",
    "    except KeyError:\n",
    "        print(table)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f837b42",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "table_names = list(tables.keys())\n",
    "pool = multiprocessing.Pool(processes=30)\n",
    "res = pool.map(get_labels, table_names)\n",
    "pool.close()\n",
    "pool.join()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32ad6e32",
   "metadata": {},
   "outputs": [],
   "source": [
    "annotated_tables = pd.DataFrame(res, columns=['class', 'file_name', 'column_count', 'number_of_rows', 'overall_table_density', 'all_cols', 'rel_labels', 'type_labels'])\n",
    "annotated_tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb2d56e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "annotations_dict = annotated_tables.to_dict('records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a04d5bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "rels = {}\n",
    "types = {}\n",
    "for row in annotations_dict:\n",
    "    rel_labels = row['rel_labels']\n",
    "    type_labels = row['type_labels']\n",
    "    densities = eval(row['all_cols'])\n",
    "\n",
    "    for col in densities:\n",
    "        if 'name' in densities:\n",
    "            if rel_labels[col] not in rels:\n",
    "                rels[rel_labels[col]] = {}\n",
    "\n",
    "            if row['file_name'] not in rels[rel_labels[col]]:\n",
    "                rels[rel_labels[col]][row['file_name']] = {}\n",
    "\n",
    "            rels[rel_labels[col]][row['file_name']][col] = densities[col]\n",
    "\n",
    "\n",
    "\n",
    "        if type_labels[col] not in types:\n",
    "            types[type_labels[col]] = {}\n",
    "\n",
    "\n",
    "        if row['file_name'] not in types[type_labels[col]]:\n",
    "            types[type_labels[col]][row['file_name']] = {}\n",
    "\n",
    "        types[type_labels[col]][row['file_name']][col] = densities[col]\n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "924dc50f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#To calculate some statistics about each CTA/CPA label\n",
    "rels_tab = []\n",
    "types_tab = []\n",
    "\n",
    "for rel in rels:\n",
    "    c = 0\n",
    "    for tab in rels[rel]:\n",
    "        c += len(rels[rel][tab])\n",
    "        \n",
    "    rels_tab.append([rel, len(rels[rel]), c, rels[rel]])\n",
    "\n",
    "for t in types:\n",
    "    c = 0\n",
    "    for tab in types[t]:\n",
    "        c += len(types[t][tab])\n",
    "        \n",
    "    types_tab.append([t, len(types[t]), c, types[t]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8edff0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "cpa_statistics = pd.DataFrame(rels_tab, columns=['cpa_label', 'table_count', 'column_count', 'tables_and_densities'])\n",
    "cta_statistics = pd.DataFrame(types_tab, columns=['cta_label', 'table_count', 'column_count', 'tables_and_densities'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fdba750c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Manual corrections for CTA labels:\n",
    "#Correct geo\n",
    "list_tables_geo = ['LocalBusiness_village-hotels.co.uk_September2020.json.gz', 'Hotel_bikershotel.it_September2020.json.gz']\n",
    "\n",
    "# Correct geo\n",
    "for geo_tab in list_tables_geo:\n",
    "    row = annotated_tables.loc[annotated_tables['file_name'] == geo_tab ]\n",
    "    \n",
    "    idx = row.index[0]\n",
    "    \n",
    "    annotated_tables.loc[annotated_tables['file_name'] == geo_tab ]['type_labels'][idx]['geo'] = 'geo'\n",
    "\n",
    "# Correct author\n",
    "row = annotated_tables.loc[annotated_tables['file_name'] == \"Recipe_t2tea.com_September2020.json.gz\" ]    \n",
    "idx = row.index[0]\n",
    "annotated_tables.loc[annotated_tables['file_name'] == 'Recipe_t2tea.com_September2020.json.gz' ]['type_labels'][idx]['author'] = 'Person'\n",
    "\n",
    "# Fix recipeinstructions\n",
    "row = annotated_tables.loc[annotated_tables['file_name'] == \"Recipe_accessiblechef.com_September2020.json.gz\" ]    \n",
    "idx = row.index[0]\n",
    "annotated_tables.loc[annotated_tables['file_name'] == 'Recipe_accessiblechef.com_September2020.json.gz' ]['type_labels'][idx]['recipeinstructions'] = 'ItemList'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c8a06f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "annotated_tables.to_csv('output-data/statistics/expanded_tables_annotations.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25be4a95",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Tables to choose from for CTA\n",
    "annotated_tables.loc[annotated_tables['overall_table_density'] >= 70].to_csv('output-data/statistics/expanded_tables_annotations_cta.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3f380be",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Tables to choose from for CPA (tables should have name column!)\n",
    "file_names = []\n",
    "\n",
    "for row in annotations_dict:    \n",
    "    if 'name' in eval(row['all_cols']):\n",
    "        file_names.append(row['file_name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1792f320",
   "metadata": {},
   "outputs": [],
   "source": [
    "annotated_tables.loc[annotated_tables['file_name'].isin(file_names)].to_csv('output-data/statistics/expanded_tables_annotations_cpa.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d3ab37d",
   "metadata": {},
   "outputs": [],
   "source": [
    "cpa_statistics.to_csv('output-data/statistics/cpa_statistics.csv', index=False)\n",
    "cta_statistics.to_csv('output-data/statistics/cta_statistics.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}