5.3-RandomColumns-CPA.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca71466c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import multiprocessing\n",
    "import pandas as pd\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32135c03",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = pd.read_csv('output-data/statistics/expanded_tables_annotations_cpa.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e856438",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "cpa_statistics = pd.read_csv('output-data/statistics/cpa_statistics.csv')\n",
    "cpa_statistics = cpa_statistics.loc[(cpa_statistics['column_count'] >= 50) ]\n",
    "cpa_statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9cb92220",
   "metadata": {},
   "outputs": [],
   "source": [
    "rels = pd.read_csv('data/Final CTA and CPA Labels.csv')\n",
    "rels = rels.loc[rels['CPA label'].isin(cpa_statistics['cpa_label'].tolist())]\n",
    "rels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ebbe234",
   "metadata": {},
   "outputs": [],
   "source": [
    "rel_lbls = {}\n",
    "for index, row in tables.iterrows():\n",
    "    for col in eval(row['rel_labels']):\n",
    "        if eval(row['rel_labels'])[col] in cpa_statistics['cpa_label'].tolist():\n",
    "            rel_lbls[col] = eval(row['rel_labels'])[col] \n",
    "del rel_lbls['name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fbacbf7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "selection = pd.read_csv('output-data/cpa-datasets/selected_1_2.csv')\n",
    "selection['selected_cols_number'] = selection['selected_cols'].apply(lambda row: len(eval(row)))\n",
    "selection_dict = selection.sort_values('selected_cols_number').to_dict('records')\n",
    "\n",
    "selected_cols = {}\n",
    "for row in selection_dict:\n",
    "    selected_cols[row['file_name']] = eval(row['selected_cols'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49078e61",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Select from already selected tables all non-annotated columns with a limit of 6500 columns\n",
    "count_class = {} #Count columns per Schema.org type/class\n",
    "rel_count = {} #Count columns per CPA label\n",
    "\n",
    "for r in cpa_statistics['cpa_label'].tolist():\n",
    "    if r != 'name':\n",
    "        rel_count[r] = 0\n",
    "\n",
    "for tab in selection_dict:\n",
    "    sel = eval(tab['selected_cols'])\n",
    "    rels = eval(tab['rel_labels'])\n",
    "\n",
    "    if tab['class'] not in count_class:\n",
    "        count_class[tab['class']] = 0\n",
    "\n",
    "    #Select a maximum of 6500 columns per Schema.org type/class \n",
    "    #if count_class[tab['class']] < 6500:\n",
    "    for col in eval(tab['all_cols']):\n",
    "        if col in rel_lbls:\n",
    "            #If column has not been yet selected:\n",
    "            if col not in sel and eval(tab['all_cols'])[col] >= 70 and count_class[tab['class']] < 6500:  #: and rel_count[rels[col]] < 3000\n",
    "                count_class[tab['class']] += 1\n",
    "                #Add to selected\n",
    "                selected_cols[tab['file_name']][col] = 'Random'\n",
    "                rel_count[rels[col]] += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d580fdfd",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Mark only labels that have not yet reached at least 100 examples\n",
    "low_labels = [ x for x in rel_count if rel_count[x] < 100 ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d6d061de",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Add more examples to CTA labels that have not reached enough examples\n",
    "#count_class_2 = {} #Count columns per Schema.org type, do not pass 6500 columns\n",
    "\n",
    "for cl in count_class:\n",
    "    #count_class_2[cl] = 0\n",
    "    if count_class[cl] < 6500:\n",
    "        d = tables.loc[(~tables['file_name'].isin(selected_cols)) & (tables['class'] == cl ) & (tables['overall_table_density'] >= 70 )]\n",
    "        \n",
    "        for index, row in d.sort_values('column_count', ascending=False).iterrows():\n",
    "            cpa = eval(row['rel_labels'])\n",
    "            for col in eval(row['all_cols']):\n",
    "                if eval(row['all_cols'])[col] >= 70 and col in rel_lbls:\n",
    "                    if rel_count[cpa[col]] < 2000: #2000\n",
    "                        rel_count[cpa[col]] += 1\n",
    "                        if count_class[cl] < (6500 - count_class[cl]):\n",
    "                            if row['file_name'] not in selected_cols:\n",
    "                                selected_cols[row['file_name']] = {}\n",
    "                            selected_cols[row['file_name']][col] = 'Random 2'\n",
    "                            count_class[cl] += 1\n",
    "                            \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8cd513f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select maximum 300 columns per CPA label\n",
    "# new_cols = {}\n",
    "# for l in low_labels:\n",
    "#     new_cols[l] = []\n",
    "    \n",
    "for index, row in tables.sort_values('column_count', ascending=False).iterrows():\n",
    "    rels = eval(row['rel_labels'])\n",
    "    \n",
    "    for col in eval(row['all_cols']):\n",
    "        if col in rel_lbls and rels[col] in low_labels and rel_count[rels[col]] < 700:\n",
    "                \n",
    "            if (row['file_name'] not in selected_cols or col not in selected_cols[row['file_name']]) and eval(row['all_cols'])[col] >= 70:\n",
    "                \n",
    "                rel_count[rels[col]] += 1\n",
    "                \n",
    "                #add to selected columns\n",
    "                if row['file_name'] not in selected_cols:\n",
    "                    selected_cols[row['file_name']] = {}\n",
    "                selected_cols[row['file_name']][col] = 'Random 3'\n",
    "          "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ed35edb",
   "metadata": {},
   "source": [
    "## Assemble all selected columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd578329",
   "metadata": {},
   "outputs": [],
   "source": [
    "selection = tables.loc[ tables['file_name'].isin(selected_cols)]\n",
    "sel_cols = []\n",
    "for index, row in selection.iterrows():\n",
    "    sel_cols.append(selected_cols[row['file_name']])\n",
    "selection['selected_cols'] = sel_cols\n",
    "selection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a567817",
   "metadata": {},
   "outputs": [],
   "source": [
    "res = []\n",
    "for index, row in selection.iterrows():\n",
    "    rel_labels = eval(row['rel_labels'])\n",
    "    type_labels = eval(row['type_labels'])\n",
    "    densities = eval(row['all_cols'])\n",
    "    \n",
    "    for cols in selected_cols[row['file_name']]:\n",
    "        if cols in rel_labels and cols in type_labels:\n",
    "            res.append([row['class'], cols, row['file_name'], rel_labels[cols], type_labels[cols], densities[cols], selected_cols[row['file_name']][cols] ])\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aac64251",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = pd.DataFrame(res, columns=['class', 'column_name', 'file_name', 'relation_label', 'type_label', 'density', 'selection_type'])\n",
    "dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6fce292f",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = dataset.loc[dataset['density'] >= 10 ]\n",
    "dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "512ac3c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove columns belonging to CTA labels that do not have a minimum of 50 examples\n",
    "s = dataset.groupby(['relation_label'])['column_name'].count()\n",
    "no_rels = list(s[s < 50].keys())\n",
    "dataset = dataset.loc[~dataset['relation_label'].isin(no_rels)]\n",
    "dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "936378f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset['selection_type'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78564346",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset['selection_type'].replace(['Intra similarity','Inter Similarity'], 'Corner Cases', inplace=True)\n",
    "dataset['selection_type'].replace(['Random', 'Random 2', 'Random 3'], 'Random', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7bd996c",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset.to_csv('output-data/cpa-datasets/dataset.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.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}