5.1-TableSelection-CPA.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb67e2b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import copy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "babd9606",
   "metadata": {},
   "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": "9bebdc03",
   "metadata": {},
   "outputs": [],
   "source": [
    "cpa_labels = cpa_statistics['cpa_label'].tolist()\n",
    "cpa_labels.remove('name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c5a44ba",
   "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": "2f58e092",
   "metadata": {},
   "outputs": [],
   "source": [
    "annotated_tables = pd.read_csv('output-data/statistics/expanded_tables_annotations_cpa.csv')\n",
    "annotated_tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0793665b",
   "metadata": {},
   "outputs": [],
   "source": [
    "rel_lbls = {}\n",
    "for index, row in annotated_tables.iterrows():\n",
    "    for col in eval(row['rel_labels']):\n",
    "        rel_lbls[col] = eval(row['rel_labels'])[col] \n",
    "del rel_lbls['name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "728b8008",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_names = set(annotated_tables['file_name'].tolist())\n",
    "#Columns to ignore in selection with a density less than 70%\n",
    "ignore = []\n",
    "tables_to_dict = annotated_tables.to_dict('records')\n",
    "for table in tables_to_dict:\n",
    "    for col in eval(table['all_cols']):\n",
    "        if eval(table['all_cols'])[col] < 70:\n",
    "            ignore.append(col+'_'+table['file_name'])\n",
    "ignore = set(ignore)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b51691ee",
   "metadata": {},
   "source": [
    "## 1. Corner Cases Columns Selection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae850590",
   "metadata": {},
   "outputs": [],
   "source": [
    "num_sim = pd.read_csv('output-data/similarities/subset8000_num_sim.csv.gz', compression='gzip')\n",
    "num_sim2 = pd.read_csv('output-data/similarities/subset7000_num_sim.csv.gz', compression='gzip')\n",
    "date_sim = pd.read_csv('output-data/similarities/subset6000_datetime_sim.csv.gz', compression='gzip')\n",
    "date_sim2 = pd.read_csv('output-data/similarities/subset6000_2_datetime_sim.csv.gz', compression='gzip')\n",
    "text_sim = pd.read_csv('output-data/similarities/subset240000_textcols_sim.csv.gz', compression='gzip')\n",
    "text_sim2 = pd.read_csv('output-data/similarities/subset239000_textcols_sim.csv.gz', compression='gzip')\n",
    "similarities = pd.concat([num_sim, num_sim2, date_sim, date_sim2, text_sim, text_sim2], ignore_index=True)\n",
    "similarities['file_name'] = similarities['col_name'].apply(lambda row: row.split('_')[1]+'_'+row.split('_')[2]+'_'+row.split('_')[3])\n",
    "similarities = similarities.loc[similarities['file_name'].isin(file_names)]\n",
    "#similarities = similarities.loc[~similarities['col_name'].isin(ignore)]\n",
    "similarities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c768a0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create a file to hold already selected columns from tables and their selection type\n",
    "# Selection type: 'Missing values', 'Intra similarity', 'Inter simimilarity/dissimilarity', 'format heterogeneity'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05e44397",
   "metadata": {},
   "source": [
    "### Select intra similarity columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0311a542",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Column -> its similar cols\n",
    "intra_similarities = {}\n",
    "for index, row in similarities.iterrows():\n",
    "    col_name, class_name, table_name, ending = row['col_name'].split('_')\n",
    "    file_name = '_'.join([class_name, table_name, ending])\n",
    "    \n",
    "    #If correct column\n",
    "    if col_name in rel_lbls:\n",
    "        \n",
    "        #if pd.isnull(row['similar_cols']):\n",
    "        #similar_cols = row['not_similar_cols'].split('; ')[:10]\n",
    "        #else:\n",
    "        similar_cols = row['similar_cols'].split('; ')[:10]\n",
    "        \n",
    "        intra_similarities[row['col_name']] = []\n",
    "\n",
    "        for col in similar_cols:\n",
    "            col_name2, class_name2, table_name2, ending2 = col.split('_')\n",
    "            file_name_2 = '_'.join([class_name2, table_name2, ending2])\n",
    "            \n",
    "            # More than 70% of density, same table and in selected tables for CPA\n",
    "            if col_name2 in rel_lbls and rel_lbls[col_name] != rel_lbls[col_name2] and file_name_2 in file_names and file_name == file_name_2 and col not in ignore:\n",
    "                intra_similarities[row['col_name']].append(col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a19ce5ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "intra_tables = {} # Mark which columns can be selected in each table\n",
    "intra_cols = set() # Which columns selected overall\n",
    "intra_class = {} # How many columns per schema.org type/class\n",
    "intra_labels = {}\n",
    "\n",
    "for key in intra_similarities:\n",
    "    if intra_similarities[key]:\n",
    "\n",
    "        col_name, class_, table_name, ending = key.split('_')\n",
    "        file_name = '_'.join([class_, table_name, ending])\n",
    "        label = rel_lbls[col_name]\n",
    "        \n",
    "        if file_name not in intra_tables:\n",
    "            intra_tables[file_name] = set()\n",
    "        \n",
    "        if class_ not in intra_class:\n",
    "            intra_class[class_] = 0\n",
    "            \n",
    "        if label not in intra_labels:\n",
    "            intra_labels[label] = []\n",
    "            \n",
    "        if key not in intra_labels[label]:\n",
    "            intra_labels[label].append(key)\n",
    "            \n",
    "        intra_cols.add(key)\n",
    "        intra_tables[file_name].add(key)\n",
    "        intra_class[class_] += 1\n",
    "        \n",
    "        for col in intra_similarities[key]:\n",
    "            label = rel_lbls[col.split('_')[0]]\n",
    "            \n",
    "            if label not in intra_labels:\n",
    "                intra_labels[label] = []\n",
    "            \n",
    "            if col not in intra_labels[label]:\n",
    "                intra_labels[label].append(col)\n",
    "            \n",
    "            intra_cols.add(col)\n",
    "            intra_tables[file_name].add(col)\n",
    "            intra_class[class_] += 1\n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5154fa5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Mark all selected columns in a dictionary:\n",
    "selected_cols_tables = {}\n",
    "\n",
    "for table in intra_tables:\n",
    "    \n",
    "    selected_cols_tables[table] = {}\n",
    "    \n",
    "    for col in intra_tables[table]:\n",
    "        selected_cols_tables[table][col.split('_')[0]] = 'Intra similarity'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a03bda2",
   "metadata": {},
   "outputs": [],
   "source": [
    "low_class = [] #Schema.org types that have less than 1500 selected columns until now\n",
    "low_label = [] #CPA labels that have less than 100 examples until now\n",
    "\n",
    "for cl in intra_class:\n",
    "    if intra_class[cl] < 1500:\n",
    "        low_class.append(cl)\n",
    "        \n",
    "for lb in intra_labels:\n",
    "    if len(intra_labels[lb]) < 100:\n",
    "        low_label.append(lb)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ad93400",
   "metadata": {},
   "source": [
    "### Select inter table similarity columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5bf50c46",
   "metadata": {},
   "outputs": [],
   "source": [
    "inter_similarities = {}\n",
    "for index, row in similarities.iterrows():\n",
    "    col_name, class_name, table_name, ending = row['col_name'].split('_')\n",
    "    file_name = '_'.join([class_name, table_name, ending])\n",
    "        \n",
    "    #If correct column\n",
    "    if col_name in rel_lbls:\n",
    "        \n",
    "        #if pd.isnull(row['similar_cols']):\n",
    "        #similar_cols = row['not_similar_cols'].split('; ')[:10]\n",
    "        #else:\n",
    "        similar_cols = row['similar_cols'].split('; ')[:10]\n",
    "        \n",
    "        inter_similarities[row['col_name']] = []\n",
    "\n",
    "        for col in similar_cols:\n",
    "            col_name2, class_name2, table_name2, ending2 = col.split('_')\n",
    "            file_name_2 = '_'.join([class_name2, table_name2, ending2])\n",
    "\n",
    "            if col_name2 in rel_lbls and rel_lbls[col_name] != rel_lbls[col_name2] and  file_name_2 in file_names and file_name != file_name_2 and col not in ignore:\n",
    "                inter_similarities[row['col_name']].append(col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7501c975",
   "metadata": {},
   "outputs": [],
   "source": [
    "inter_sims = [ [x] + inter_similarities[x] for x in inter_similarities if len(inter_similarities[x]) > 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2bdc966e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Selecting inter similarities: \n",
    "#Count how many columns for low class number of columns and for low CTA labels can be selected\n",
    "#Filter out above inter sims\n",
    "\n",
    "select_test = []\n",
    "i = 0\n",
    "\n",
    "for cols in inter_sims:\n",
    "    select_test.append([])\n",
    "    \n",
    "    for col in cols:\n",
    "        \n",
    "        col_name, class_name, table_name, ending = col.split('_')\n",
    "        table = '_'.join([class_name, table_name, ending])\n",
    "        \n",
    "        #If table is in low class and in low label \n",
    "        if class_name in low_class and rel_lbls[col_name] in low_label: #\n",
    "            #If it hasn't been selected already in the intra similarity phase\n",
    "            if (table in selected_cols_tables and col_name not in selected_cols_tables[table]) or table not in selected_cols_tables:\n",
    "                select_test[i].append(col)\n",
    "    i+=1\n",
    "\n",
    "# And Filter out columns with less than 1 similar column\n",
    "s = [ x for x in select_test if len(x) > 1]\n",
    "\n",
    "# And Select maximum 3500 cols for each CPA label\n",
    "sel_labels = {}\n",
    "\n",
    "for sim in s:\n",
    "    for col in sim:\n",
    "        \n",
    "        if rel_lbls[col.split('_')[0]] not in sel_labels:\n",
    "            sel_labels[rel_lbls[col.split('_')[0]]] = []\n",
    "        \n",
    "        if len(sel_labels[rel_lbls[col.split('_')[0]]]) < 3500:\n",
    "            sel_labels[rel_lbls[col.split('_')[0]]].append(col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49da607c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Select columns which are included in sel_labels\n",
    "selected_test_2 = []\n",
    "i = 0\n",
    "\n",
    "for cols in inter_sims:\n",
    "    selected_test_2.append([])\n",
    "    \n",
    "    for col in cols:\n",
    "        if rel_lbls[col.split('_')[0]] in sel_labels and col in sel_labels[rel_lbls[col.split('_')[0]]]:\n",
    "            selected_test_2[i].append(col)\n",
    "    i+=1\n",
    "    \n",
    "#Filter out columns with no similar columns\n",
    "s_2 = [ x for x in selected_test_2 if len(x) > 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6721b8b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add the new selected columns to the already selected ones\n",
    "selected_cols = copy.deepcopy(selected_cols_tables)\n",
    "\n",
    "for s in s_2:\n",
    "    for col in s:\n",
    "        tab = col.split('_')[1] + '_' + col.split('_')[2] + '_' + col.split('_')[3]\n",
    "        \n",
    "        if tab not in selected_cols:\n",
    "            selected_cols[tab] = {}\n",
    "        \n",
    "        selected_cols[tab][col.split('_')[0]] = 'Inter Similarity'\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d86d42f",
   "metadata": {},
   "source": [
    "## 2. Select Missing values columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e0a0b88",
   "metadata": {},
   "outputs": [],
   "source": [
    "low_dens_70 = annotated_tables.loc[ (annotated_tables['overall_table_density'] < 70) ]\n",
    "low_dens_70"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "885d050e",
   "metadata": {},
   "outputs": [],
   "source": [
    "low_dens_70['low_cols'] = low_dens_70['all_cols'].apply(lambda row: len( [x for x in eval(row) if eval(row)[x] < 70 and eval(row)[x] > 10 ] ))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4737f71a",
   "metadata": {},
   "outputs": [],
   "source": [
    "low_dens_70.loc[ (low_dens_70['low_cols'] >= 3) ]['file_name'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6564cc76",
   "metadata": {},
   "outputs": [],
   "source": [
    "# That have at least three columns to be annotated\n",
    "low_dens_70.loc[ (low_dens_70['low_cols'] >= 3) ].groupby(['class'])['low_cols'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15113000",
   "metadata": {},
   "outputs": [],
   "source": [
    "low_dens_70.loc[ (low_dens_70['low_cols'] >= 3) ]['low_cols'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8b45e3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Limit Product tables to 1500\n",
    "prods = low_dens_70.loc[ (low_dens_70['low_cols'] >= 3) & (low_dens_70['class'] == 'Product' ) ][1000:]\n",
    "recipe = low_dens_70.loc[ (low_dens_70['low_cols'] >= 3) & (low_dens_70['class'] == 'Recipe' ) ][1000:]\n",
    "event = low_dens_70.loc[ (low_dens_70['low_cols'] >= 3) & (low_dens_70['class'] == 'Event' ) ][1000:]\n",
    "\n",
    "remove_tables = prods['file_name'].tolist() + recipe['file_name'].tolist() + event['file_name'].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6033fcb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Select columns with low density from the low density tables that have at least 3 low density columns\n",
    "#Add to all other selected columns\n",
    "# count_missing_labels = {}\n",
    "# for rel in cpa_labels:\n",
    "#     count_missing_labels[rel] = 0\n",
    "\n",
    "for index, row in low_dens_70.loc[ (low_dens_70['low_cols'] >= 3) ].iterrows():\n",
    "    file_name = row['file_name']\n",
    "    \n",
    "    if file_name not in remove_tables:    \n",
    "        if file_name not in selected_cols:\n",
    "            selected_cols[file_name] = {}\n",
    "\n",
    "        #Look at low density columns and select if not already annotated from intra sim\n",
    "        annotated_cols = eval(row['all_cols'])\n",
    "\n",
    "        for column in annotated_cols:\n",
    "            if column in rel_lbls: #and count_missing_labels[rel_lbls[column]] < 1000:\n",
    "                if annotated_cols[column] < 70 and annotated_cols[column] > 10 and column not in selected_cols[file_name]:\n",
    "                    selected_cols[file_name][column] = 'Missing values'\n",
    "                    #count_missing_labels[rel_lbls[column]] += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58e74f5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_tabs = []\n",
    "for tab in selected_cols:\n",
    "    if not selected_cols[tab]:\n",
    "        remove_tabs.append(tab)\n",
    "for tab in remove_tabs:\n",
    "    del selected_cols[tab]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "022779d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Save selected columns to file\n",
    "selection = annotated_tables.loc[ annotated_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.to_csv('output-data/cpa-datasets/selected_1.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
}