8-CreatingSplits-CTA.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85c4a4ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from collections import Counter\n",
    "from skmultilearn.model_selection import iterative_train_test_split\n",
    "from skmultilearn.model_selection.measures import get_combination_wise_output_matrix\n",
    "import multiprocessing"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1bf86599",
   "metadata": {},
   "source": [
    "## Read the created dataset and attach column values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75f02e3d",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = pd.read_csv('output-data/cta-datasets/dataset_cta.csv')\n",
    "dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be199ef4",
   "metadata": {},
   "outputs": [],
   "source": [
    "col_table = (dataset['column_name']+'|'+dataset['file_name']).tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "915d5aa5",
   "metadata": {},
   "source": [
    "### Add value columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0167f893",
   "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()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8cd529fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Returns values of cleaned textual columns\n",
    "def get_values(col_table_name):\n",
    "    column_name, file_name = col_table_name.split('|')\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",
    "    #Open table\n",
    "    df = pd.read_json(file, compression='gzip', lines=True)\n",
    "           \n",
    "    return df[column_name].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5fd9db9",
   "metadata": {},
   "outputs": [],
   "source": [
    "pool = multiprocessing.Pool(processes=25)\n",
    "values = pool.map(get_values, col_table)\n",
    "pool.close()\n",
    "pool.join()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bed2a087",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset['values'] = values\n",
    "dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bddc8ca3",
   "metadata": {},
   "source": [
    "## Create training, validation and test split"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b16e531f",
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_by_table = dataset.groupby(['file_name'])['type_label'].apply(','.join).reset_index()\n",
    "grouped_by_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7ea1673",
   "metadata": {},
   "outputs": [],
   "source": [
    "X = grouped_by_table[[\"file_name\"]].values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3302a60f",
   "metadata": {},
   "source": [
    "### One hot encoding of CTA labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7de1e41",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_labels = dataset['type_label'].unique()\n",
    "len(all_labels)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84a1667d",
   "metadata": {},
   "outputs": [],
   "source": [
    "y = np.zeros(shape=(len(grouped_by_table['file_name'].tolist()), len(all_labels))) #encoded labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf9597d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in grouped_by_table.iterrows():\n",
    "    table_labels = row['type_label'].split(',')\n",
    "    count = 0\n",
    "    \n",
    "    for label in all_labels:\n",
    "        if label in table_labels:\n",
    "            y[index][count] = 1\n",
    "        else:\n",
    "            y[index][count] = 0\n",
    "        count += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b754a4d9",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "X_train, y_train, X_test, y_test = iterative_train_test_split(X, y, test_size = 0.2)\n",
    "print('Training set length: '+str(len(X_train)) +', Testing set length: '+ str(len(X_test)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b7a6727",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Relation Labels in each set\n",
    "pd.DataFrame({\n",
    "    'train': Counter(str(combination) for row in get_combination_wise_output_matrix(y_train, order=1) for combination in row),\n",
    "    'test' : Counter(str(combination) for row in get_combination_wise_output_matrix(y_test, order=1) for combination in row)\n",
    "}).T.fillna(0.0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee24143b",
   "metadata": {},
   "source": [
    "### Split testing set into validation and testing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9904e013",
   "metadata": {},
   "outputs": [],
   "source": [
    "X_val, y_val, X_test, y_test = iterative_train_test_split(X_test, y_test, test_size = 0.5)\n",
    "print('Validation set length: '+str(len(X_val)) +', Testing set length: '+ str(len(X_test)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67c4b1b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame({\n",
    "    'val': Counter(str(combination) for row in get_combination_wise_output_matrix(y_val, order=1) for combination in row),\n",
    "    'test' : Counter(str(combination) for row in get_combination_wise_output_matrix(y_test, order=1) for combination in row)\n",
    "}).T.fillna(0.0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dcab3a4a",
   "metadata": {},
   "source": [
    "## Statistics for each set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72aefd6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f52c6156",
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_by_table_dict = grouped_by_table.to_dict('records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef09d877",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Dictionary with table names as key and relation labels as values\n",
    "file_to_label = {}\n",
    "for row in grouped_by_table_dict:\n",
    "    file_to_label[row['file_name']] = row['type_label']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a113c0b6",
   "metadata": {},
   "source": [
    "### Training set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6319619",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Count how many columns per label\n",
    "label_and_number_train = {}\n",
    "for row in X_train:\n",
    "    \n",
    "    for label in file_to_label[row[0]].split(','):\n",
    "        if label in label_and_number_train:\n",
    "            label_and_number_train[label] += 1\n",
    "        else:\n",
    "            label_and_number_train[label] = 1\n",
    "print('Number of unique relation labels in training set: '+ str(len(label_and_number_train)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "009ab4ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "label_and_number_train.values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1c7fb4fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Minimum column count per label is: '+str(min(label_and_number_train.values())) )\n",
    "print('Maximum column count per label is: '+str(max(label_and_number_train.values())) )\n",
    "print('Total column count is: '+str(sum(label_and_number_train.values())) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96e9ce67",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(15,5))\n",
    "plt.hist(label_and_number_train.values(), bins=10)\n",
    "plt.ylabel('Label Count')\n",
    "plt.xlabel('Number of columns')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "749a7725",
   "metadata": {},
   "source": [
    "### Validation set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dfeb1675",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Count how many columns per label\n",
    "label_and_number_val = {}\n",
    "for row in X_val:\n",
    "    \n",
    "    for label in file_to_label[row[0]].split(','):\n",
    "        if label in label_and_number_val:\n",
    "            label_and_number_val[label] += 1\n",
    "        else:\n",
    "            label_and_number_val[label] = 1\n",
    "print('Number of unique relation labels in validation set: '+ str(len(label_and_number_val)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac4b7ec7",
   "metadata": {},
   "outputs": [],
   "source": [
    "label_and_number_val.values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be717978",
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Minimum column count per label is: '+str(min(label_and_number_val.values())) )\n",
    "print('Maximum column count per label is: '+str(max(label_and_number_val.values())) )\n",
    "print('Total column count is: '+str(sum(label_and_number_val.values())) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0775b70",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(15,5))\n",
    "plt.hist(label_and_number_val.values(), bins=10)\n",
    "plt.ylabel('Label Count')\n",
    "plt.xlabel('Number of columns')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37ad560b",
   "metadata": {},
   "source": [
    "### Testing set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81cfea07",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Count how many columns per label\n",
    "label_and_number_test = {}\n",
    "for row in X_test:\n",
    "    \n",
    "    for label in file_to_label[row[0]].split(','):\n",
    "        if label in label_and_number_test:\n",
    "            label_and_number_test[label] += 1\n",
    "        else:\n",
    "            label_and_number_test[label] = 1\n",
    "print('Number of unique relation labels in testing set: '+ str(len(label_and_number_test)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e8a4f95",
   "metadata": {},
   "outputs": [],
   "source": [
    "label_and_number_test.values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b8d1dce7",
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Minimum column count per label is: '+str(min(label_and_number_test.values())) )\n",
    "print('Maximum column count per label is: '+str(max(label_and_number_test.values())) )\n",
    "print('Total column count is: '+str(sum(label_and_number_test.values())) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb931181",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(15,5))\n",
    "plt.hist(label_and_number_test.values(), bins=10)\n",
    "plt.ylabel('Label Count')\n",
    "plt.xlabel('Number of columns')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b01f38db",
   "metadata": {},
   "source": [
    "## Prepare csv file for each set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4866b169",
   "metadata": {},
   "outputs": [],
   "source": [
    "training_tables = [ table[0] for table in X_train ]\n",
    "validation_tables = [ table[0] for table in X_val ]\n",
    "testing_tables = [ table[0] for table in X_test ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0a107fc2",
   "metadata": {},
   "outputs": [],
   "source": [
    "training_set = dataset.loc[dataset['file_name'].isin(training_tables)]\n",
    "validation_set = dataset.loc[dataset['file_name'].isin(validation_tables)]\n",
    "testing_set = dataset.loc[dataset['file_name'].isin(testing_tables)]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e504bcbb",
   "metadata": {},
   "outputs": [],
   "source": [
    "alltypes = list(testing_set['type_label'].unique())\n",
    "training_set = training_set.loc[training_set['type_label'].isin(alltypes)]\n",
    "validation_set = validation_set.loc[validation_set['type_label'].isin(alltypes)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c201f83",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Manual corrections:\n",
    "#Remove some types that do not have at least 10 examples in test set\n",
    "#Remove tables with less than 10% density\n",
    "\n",
    "# training_set = training_set.loc[~training_set['type_label'].isin(['Map', 'PublicationEvent', 'VideoObject', 'AggregateRating'])]\n",
    "# training_set = training_set.loc[training_set['density'] >= 10 ]\n",
    "\n",
    "# validation_set = validation_set.loc[~validation_set['type_label'].isin(['Map', 'PublicationEvent', 'VideoObject', 'AggregateRating'])]\n",
    "# validation_set = validation_set.loc[validation_set['density'] >= 10 ]\n",
    "\n",
    "# testing_set = testing_set.loc[~testing_set['type_label'].isin(['Map', 'PublicationEvent', 'VideoObject', 'AggregateRating'])]\n",
    "# testing_set = testing_set.loc[testing_set['density'] >= 10 ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37b43e32",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Write all CTA labels in a file\n",
    "with open('output-data/cta-datasets/type_vocab.txt', 'a') as file:\n",
    "    i = 0\n",
    "    for label in all_types:\n",
    "        file.write(str(i)+ '\\t' + label +'\\n')\n",
    "        i += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f97cf75",
   "metadata": {},
   "outputs": [],
   "source": [
    "training_set.to_csv('output-data/cta-datasets/training_set_cta.csv.gz', index=False, compression='gzip')\n",
    "validation_set.to_csv('output-data/cta-datasets/validation_set_cta.csv.gz', index=False, compression='gzip')\n",
    "testing_set.to_csv('output-data/cta-datasets/testing_set_cta.csv.gz', index=False, compression='gzip')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "baad30ed",
   "metadata": {},
   "source": [
    "### Create small subset of training set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7359721",
   "metadata": {},
   "outputs": [],
   "source": [
    "training_set = pd.read_csv('output-data/cta-datasets/training_set_cta.csv.gz', compression='gzip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55e5e77d",
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_by_table = training_set.groupby(['file_name'])['type_label'].apply(','.join).reset_index()\n",
    "grouped_by_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6de3a05",
   "metadata": {},
   "outputs": [],
   "source": [
    "X_train = grouped_by_table[[\"file_name\"]].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a29a5880",
   "metadata": {},
   "outputs": [],
   "source": [
    "types_file = open(\"output-data/cta-datasets/type_vocab.txt\", 'r')\n",
    "all_labels = [line.replace('\\n', '').split('\\t')[1] for line in types_file.readlines()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c50db0ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "y = np.zeros(shape=(len(grouped_by_table['file_name'].tolist()), len(all_labels))) #encoded labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "87426cc6",
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in grouped_by_table.iterrows():\n",
    "    table_labels = row['type_label'].split(',')\n",
    "    count = 0\n",
    "    \n",
    "    for label in all_labels:\n",
    "        if label in table_labels:\n",
    "            y[index][count] = 1\n",
    "        else:\n",
    "            y[index][count] = 0\n",
    "        count += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7fae05a",
   "metadata": {},
   "outputs": [],
   "source": [
    "X_rest, y_rest, X_test, y_test = iterative_train_test_split(X_train, y, test_size = 0.25)\n",
    "print('Training set length: '+str(len(X_train)) +', Testing set length: '+ str(len(X_test)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9270c7ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Relation Labels in each set\n",
    "pd.DataFrame({\n",
    "    'train': Counter(str(combination) for row in get_combination_wise_output_matrix(y_rest, order=1) for combination in row),\n",
    "    'test' : Counter(str(combination) for row in get_combination_wise_output_matrix(y_test, order=1) for combination in row)\n",
    "}).T.fillna(0.0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ba7071c",
   "metadata": {},
   "outputs": [],
   "source": [
    "training_tables_small = [ table[0] for table in X_test ]\n",
    "training_set_small = training_set.loc[training_set['file_name'].isin(training_tables_small)]\n",
    "training_set_small"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c2ada1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "training_set_small.to_csv('output-data/cta-datasets/training_set_small_cta.csv.gz', index=False, compression='gzip')"
   ]
  }
 ],
 "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
}