5.2-Different-Formats-CPA.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9615d55",
   "metadata": {},
   "outputs": [],
   "source": [
    "import multiprocessing\n",
    "import pandas as pd\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "480f5cfd",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = pd.read_csv('output-data/statistics/expanded_tables_annotations_cpa.csv')\n",
    "tables_70 = tables.loc[ (tables['overall_table_density'] >= 70) ]\n",
    "tables_70"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8eb05b7a",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = tables_70['file_name'].tolist()\n",
    "text = pd.read_csv('output-data/statistics/textcols.csv.gz', compression='gzip')\n",
    "text = text.loc[text['file_name'].isin(tables)]\n",
    "text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81d5253c",
   "metadata": {},
   "outputs": [],
   "source": [
    "num = pd.read_csv('output-data/statistics/numcols.csv.gz', compression='gzip')\n",
    "num = num.loc[num['file_name'].isin(tables)]\n",
    "num"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a7940b0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "selection = pd.read_csv('output-data/cpa-datasets/selected_1.csv')\n",
    "selection_to_dict = selection.to_dict('records')\n",
    "selected_cols = {}\n",
    "for row in selection_to_dict:\n",
    "    selected_cols[row['file_name']] = eval(row['selected_cols'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f188a1fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "def num_perc(index):\n",
    "    text = str(v[index]).lower()\n",
    "    text = re.sub(r\"[^\\w\\s]\", \"\", text)\n",
    "    total = len(text)\n",
    "    \n",
    "    text = re.sub(r\"[^\\x00-\\x7F]+\", \"\", str(text))\n",
    "    text = re.sub(' +', '', str(text)).strip()\n",
    "    \n",
    "    num = re.sub(r\"[a-z]\", \"\", text)\n",
    "    num = re.sub(' +', '', str(num)).strip()\n",
    "    \n",
    "    if(total == 0):\n",
    "        return 0\n",
    "    else:\n",
    "        per = len(num) / total\n",
    "        return per"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5389e057",
   "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": "14bcf708",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Returns values of cleaned textual columns\n",
    "def get_values(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",
    "    #Open table\n",
    "    df = pd.read_json(file, compression='gzip', lines=True)\n",
    "    \n",
    "    text_props = {}\n",
    "\n",
    "    for column_name in df.columns:\n",
    "        if column_name == col_name:\n",
    "            text_props[column_name] = df[df[column_name].notna()][column_name].tolist()\n",
    "                \n",
    "    return text_props"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eaf2654e",
   "metadata": {},
   "source": [
    "### priceRange"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc2c93bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Locate all pricerange columns and remove already selected ones\n",
    "col_name = 'pricerange'\n",
    "pricerange = text.loc[(text['column_name'] == 'pricerange') & (text['file_name'].isin(tables)) ]['file_name'].tolist()\n",
    "for tab in selected_cols:\n",
    "    if 'pricerange' in selected_cols[tab] and tab in pricerange:\n",
    "        pricerange.remove(tab)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "879354b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get all values of these columns\n",
    "pool = multiprocessing.Pool(processes=30)\n",
    "values = pool.map(get_values, pricerange)\n",
    "pool.close()\n",
    "pool.join()\n",
    "\n",
    "#and put them in a dataframe\n",
    "pricerange_list = []\n",
    "i = 0\n",
    "for val in values:\n",
    "    class_ = pricerange[i].split('_')[0]\n",
    "    \n",
    "    for col in val:\n",
    "        pricerange_list.append([class_, col, pricerange[i], val[col]])\n",
    "    i += 1\n",
    "\n",
    "pricer = pd.DataFrame( pricerange_list, columns=['class', 'column_name', 'file_name', 'value'] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d58cbc40",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Calculate numerical percentage of values in a column\n",
    "v = pricer['value'].tolist()\n",
    "\n",
    "pool = multiprocessing.Pool(processes=21)\n",
    "res = pool.map(num_perc, range(len(pricer)))\n",
    "pool.close()\n",
    "pool.join()\n",
    "\n",
    "pricer['num_percentage'] = res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f439024d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Devide the columns into three categories based on the numerical percentage of the column values\n",
    "def cat(row):\n",
    "    if row <= 0.3:\n",
    "        return 1\n",
    "    elif row > 0.3 and row < 0.8:\n",
    "        return 2\n",
    "    else:\n",
    "        return 3\n",
    "    \n",
    "pricer['num_category'] = pricer['num_percentage'].apply(lambda row: cat(row))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6551900e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#From each category sample 1700/3 = 570 columns\n",
    "cat_1 = pricer.loc[pricer['num_category'] == 1]['file_name'].tolist()[:570]\n",
    "cat_2 = pricer.loc[pricer['num_category'] == 2]['file_name'].tolist()[:570]\n",
    "cat_3 = pricer.loc[pricer['num_category'] == 3]['file_name'].tolist()[:570]\n",
    "\n",
    "for cat in cat_1:\n",
    "    if cat not in selected_cols:\n",
    "        selected_cols[cat] = {}\n",
    "    selected_cols[cat]['pricerange'] = 'Value Heterogeneity'\n",
    "    \n",
    "for cat in cat_2:\n",
    "    if cat not in selected_cols:\n",
    "        selected_cols[cat] = {}\n",
    "    selected_cols[cat]['pricerange'] = 'Value Heterogeneity'\n",
    "    \n",
    "for cat in cat_3:\n",
    "    if cat not in selected_cols:\n",
    "        selected_cols[cat] = {}\n",
    "    selected_cols[cat]['pricerange'] = 'Value Heterogeneity'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1eab8a49",
   "metadata": {},
   "source": [
    "### offers:price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3daaacd",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Locate all offers:price columns and remove already selected ones\n",
    "col_name = 'offers:price'\n",
    "offerprice = text.loc[(text['column_name'] == 'offers:price') & (text['file_name'].isin(tables)) ]['file_name'].tolist()\n",
    "for tab in selected_cols:\n",
    "    if 'offers:price' in selected_cols[tab] and tab in offerprice:\n",
    "        offerprice.remove(tab)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6c04ba7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get all values of these columns\n",
    "pool = multiprocessing.Pool(processes=30)\n",
    "values = pool.map(get_values, offerprice)\n",
    "pool.close()\n",
    "pool.join()\n",
    "\n",
    "#and put them in a dataframe\n",
    "offerprice_list = []\n",
    "i = 0\n",
    "for val in values:\n",
    "    class_ = offerprice[i].split('_')[0]\n",
    "    \n",
    "    for col in val:\n",
    "        offerprice_list.append([class_, col, offerprice[i], val[col]])\n",
    "    i += 1\n",
    "\n",
    "priceoffer = pd.DataFrame( offerprice_list, columns=['class', 'column_name', 'file_name', 'value'] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84491cb0",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Calculate numerical percentage of values in a column\n",
    "v = priceoffer['value'].tolist()\n",
    "\n",
    "pool = multiprocessing.Pool(processes=21)\n",
    "res = pool.map(num_perc, range(len(priceoffer)))\n",
    "pool.close()\n",
    "pool.join()\n",
    "\n",
    "priceoffer['num_percentage'] = res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e47b4ec4",
   "metadata": {},
   "outputs": [],
   "source": [
    "off = priceoffer.loc[(priceoffer['num_percentage'] < 0.5) & (priceoffer['num_percentage'] > 0.1) ][:800]['file_name'].tolist()\n",
    "\n",
    "for tab in off:\n",
    "    if tab not in selected_cols:\n",
    "        selected_cols[tab] = {}\n",
    "    if 'offers:price' not in selected_cols[tab]:\n",
    "        selected_cols[tab]['offers:price'] = 'Value Heterogeneity'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35916d2e",
   "metadata": {},
   "source": [
    "### telephone"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf672b27",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Locate all telephone columns and remove already selected ones\n",
    "col_name = 'telephone'\n",
    "tel = text.loc[(text['column_name'] == 'telephone') & (text['file_name'].isin(tables)) ]['file_name'].tolist()\n",
    "for tab in selected_cols:\n",
    "    if 'telephone' in selected_cols[tab] and tab in tel:\n",
    "        tel.remove(tab)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6bc49afe",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get all values of these columns\n",
    "pool = multiprocessing.Pool(processes=30)\n",
    "values = pool.map(get_values, tel)\n",
    "pool.close()\n",
    "pool.join()\n",
    "\n",
    "#and put them in a dataframe\n",
    "telephone_list = []\n",
    "i = 0\n",
    "for val in values:\n",
    "    class_ = tel[i].split('_')[0]\n",
    "    \n",
    "    for col in val:\n",
    "        telephone_list.append([class_, col, tel[i], val[col]])\n",
    "    i += 1\n",
    "    \n",
    "telephone = pd.DataFrame( telephone_list, columns=['class', 'column_name', 'file_name', 'value'] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b0eef95",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Calculate numerical percentage of values in a column\n",
    "v = telephone['value'].tolist()\n",
    "\n",
    "pool = multiprocessing.Pool(processes=21)\n",
    "res = pool.map(num_perc, range(len(telephone)))\n",
    "pool.close()\n",
    "pool.join()\n",
    "\n",
    "telephone['num_percentage'] = res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c2a5dc3",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Select columns from 2 categories\n",
    "tel_1 = telephone.loc[(telephone['num_percentage'] > 0.5) & (telephone['num_percentage'] < 0.7)][:800]['file_name'].tolist()\n",
    "tel_2 = telephone.loc[(telephone['num_percentage'] > 0.8)][:800]['file_name'].tolist()\n",
    "\n",
    "for tab in tel_1:\n",
    "    if tab not in selected_cols:\n",
    "        selected_cols[tab] = {}\n",
    "    if 'telephone' not in selected_cols[tab]:\n",
    "        selected_cols[tab]['telephone'] = 'Value Heterogeneity'\n",
    "        \n",
    "        \n",
    "for tab in tel_2:\n",
    "    if tab not in selected_cols:\n",
    "        selected_cols[tab] = {}\n",
    "    if 'telephone' not in selected_cols[tab]:\n",
    "        selected_cols[tab]['telephone'] = 'Value Heterogeneity'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c34b03db",
   "metadata": {},
   "source": [
    "### duration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f9bfe36",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Locate all duration columns and remove already selected ones\n",
    "col_name = 'duration'\n",
    "dur = text.loc[(text['column_name'] == 'duration') & (text['file_name'].isin(tables)) ]['file_name'].tolist()\n",
    "for tab in selected_cols:\n",
    "    if 'duration' in selected_cols[tab] and tab in dur:\n",
    "        dur.remove(tab)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7321a750",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get all values of these columns\n",
    "pool = multiprocessing.Pool(processes=30)\n",
    "values = pool.map(get_values, dur)\n",
    "pool.close()\n",
    "pool.join()\n",
    "\n",
    "#and put them in a dataframe\n",
    "duration_list = []\n",
    "i = 0\n",
    "for val in values:\n",
    "    class_ = dur[i].split('_')[0]\n",
    "    \n",
    "    for col in val:\n",
    "        duration_list.append([class_, col, dur[i], val[col]])\n",
    "    i += 1\n",
    "    \n",
    "duration = pd.DataFrame( duration_list, columns=['class', 'column_name', 'file_name', 'value'] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d79e3fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Group columns using different metrics for duration\n",
    "def cat_duration(row):\n",
    "    if 'min' in row:\n",
    "        if 'sec' in row:\n",
    "            return 1\n",
    "        else:\n",
    "            return 2\n",
    "    elif 'PT' in row:\n",
    "        return 3\n",
    "    elif 'PD' in row:\n",
    "        return 4\n",
    "    elif 'Min.' in row:\n",
    "        return 5\n",
    "    elif ':' in row:\n",
    "        return 6\n",
    "    elif re.search('[0-9]+h [0-9]+m', row):\n",
    "        return 7\n",
    "    elif re.search('[0-9]+m [0-9]+s', row):\n",
    "         return 8\n",
    "    else:\n",
    "        return None\n",
    "    \n",
    "duration['category'] = duration['value'].apply(lambda row: cat_duration(str(row)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bd18d87d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#From each category sample 1700/3 = 570 columns\n",
    "cat_1 = duration.loc[duration['category'] == 1]['file_name'].tolist()[2:102]\n",
    "cat_2 = duration.loc[duration['category'] == 2]['file_name'].tolist()[:100]\n",
    "cat_3 = duration.loc[duration['category'] == 3]['file_name'].tolist()[:100]\n",
    "cat_4 = duration.loc[duration['category'] == 4]['file_name'].tolist()[:100]\n",
    "cat_5 = duration.loc[duration['category'] == 5]['file_name'].tolist()[:100]\n",
    "cat_6 = duration.loc[duration['category'] == 6]['file_name'].tolist()[:100]\n",
    "cat_7 = duration.loc[duration['category'] == 7]['file_name'].tolist()[:100]\n",
    "cat_8 = duration.loc[duration['category'] == 8]['file_name'].tolist()[:100]\n",
    "\n",
    "all_duration = cat_1 + cat_2 + cat_3 + cat_4 + cat_5 + cat_6 + cat_7 + cat_8\n",
    "\n",
    "for cat in all_duration:\n",
    "    if cat not in selected_cols:\n",
    "        selected_cols[cat] = {}\n",
    "    if 'duration' not in selected_cols[cat]:\n",
    "        selected_cols[cat]['duration'] = 'Value Heterogeneity'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19cd3605",
   "metadata": {},
   "source": [
    "### weight"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e64dd0fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Locate all weight columns and remove already selected ones\n",
    "col_name = 'weight'\n",
    "w = text.loc[(text['column_name'] == 'weight') & (text['file_name'].isin(tables)) ]['file_name'].tolist()\n",
    "for tab in selected_cols:\n",
    "    if 'weight' in selected_cols[tab] and tab in w:\n",
    "        w.remove(tab)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8dabf88",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get all values of these columns\n",
    "pool = multiprocessing.Pool(processes=30)\n",
    "values = pool.map(get_values, w)\n",
    "pool.close()\n",
    "pool.join()\n",
    "\n",
    "#and put them in a dataframe\n",
    "weight_list = []\n",
    "i = 0\n",
    "for val in values:\n",
    "    class_ = w[i].split('_')[0]\n",
    "    \n",
    "    for col in val:\n",
    "        weight_list.append([class_, col, w[i], val[col]])   \n",
    "    i += 1\n",
    "    \n",
    "weight = pd.DataFrame( weight_list, columns=['class', 'column_name', 'file_name', 'value'] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57c5d10b",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Group weight columns under different categories based on measure metrics used\n",
    "def weight_cat(row):\n",
    "    if 'kgs' in row:\n",
    "        return 1\n",
    "    elif 'kg' in row:\n",
    "        return 2\n",
    "    elif 'lbs' in row:\n",
    "        return 3\n",
    "    elif 'lb' in row:\n",
    "        return 4\n",
    "    elif 'ounces' in row:\n",
    "        return 5\n",
    "    elif 'oz' in row:\n",
    "        return 6\n",
    "    elif 'grams' in row:\n",
    "        return 7\n",
    "    elif re.search('[0-9]+ g', row):\n",
    "        return 8\n",
    "    else:\n",
    "        return None\n",
    "    \n",
    "weight['category'] = weight['value'].apply(lambda row: weight_cat(str(row).lower()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0fec6641",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Select 100 columns from each category\n",
    "cat_1 = weight.loc[weight['category'] == 1]['file_name'].tolist()[:100]\n",
    "cat_2 = weight.loc[weight['category'] == 2]['file_name'].tolist()[:100]\n",
    "cat_3 = weight.loc[weight['category'] == 3]['file_name'].tolist()[:100]\n",
    "cat_4 = weight.loc[weight['category'] == 4]['file_name'].tolist()[:100]\n",
    "cat_5 = weight.loc[weight['category'] == 5]['file_name'].tolist()[:100]\n",
    "cat_6 = weight.loc[weight['category'] == 6]['file_name'].tolist()[:100]\n",
    "cat_7 = weight.loc[weight['category'] == 7]['file_name'].tolist()[:100]\n",
    "cat_8 = weight.loc[weight['category'] == 8]['file_name'].tolist()[:100]\n",
    "\n",
    "all_weight = cat_1 + cat_2 + cat_3 + cat_4 + cat_5 + cat_6 + cat_7 + cat_8\n",
    "\n",
    "for cat in all_weight:\n",
    "    if cat not in selected_cols:\n",
    "        selected_cols[cat] = {}\n",
    "    if 'weight' not in selected_cols[cat]:\n",
    "        selected_cols[cat]['weight'] = 'Value Heterogeneity'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3fe66ce",
   "metadata": {},
   "source": [
    "### height"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b5ed32c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Locate all height columns and remove already selected ones\n",
    "col_name = 'height'\n",
    "h = text.loc[(text['column_name'] == 'height') & (text['file_name'].isin(tables)) ]['file_name'].tolist()\n",
    "for tab in selected_cols:\n",
    "    if 'height' in selected_cols[tab] and tab in h:\n",
    "        h.remove(tab)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "051cfc02",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get all values of these columns\n",
    "pool = multiprocessing.Pool(processes=30)\n",
    "values = pool.map(get_values, h)\n",
    "pool.close()\n",
    "pool.join()\n",
    "\n",
    "#and put them in a dataframe\n",
    "height_list = []\n",
    "i = 0\n",
    "for val in values:\n",
    "    class_ = h[i].split('_')[0]\n",
    "    \n",
    "    for col in val:\n",
    "        height_list.append([class_, col, h[i], val[col]])\n",
    "        \n",
    "    i += 1\n",
    "    \n",
    "height = pd.DataFrame( height_list, columns=['class', 'column_name', 'file_name', 'value'] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a00960bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Group height columns under different categories based on measure metrics used\n",
    "def height_cat(row):\n",
    "    if 'undefined' in row:\n",
    "        return None\n",
    "    elif 'inches' in row:\n",
    "        return 1\n",
    "    elif 'in' in row:\n",
    "        return 6\n",
    "    elif 'cm' in row:\n",
    "        return 2\n",
    "    elif 'mm' in row:\n",
    "        return 3\n",
    "    elif 'm' in row:\n",
    "        return 4\n",
    "    elif re.search('[0-9]+\\'[0-9]+', row):\n",
    "        return 5\n",
    "    else:\n",
    "        return None\n",
    "    \n",
    "height['category'] = height['value'].apply(lambda row: height_cat(str(row).lower()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "379abd74",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Select 100 columns from each category\n",
    "\n",
    "cat_1 = height.loc[height['category'] == 1]['file_name'].tolist()[:100]\n",
    "cat_2 = height.loc[height['category'] == 2]['file_name'].tolist()[:100]\n",
    "cat_3 = height.loc[height['category'] == 3]['file_name'].tolist()[:100]\n",
    "cat_4 = height.loc[height['category'] == 4]['file_name'].tolist()[:100]\n",
    "cat_5 = height.loc[height['category'] == 5]['file_name'].tolist()[:100]\n",
    "cat_6 = height.loc[height['category'] == 6]['file_name'].tolist()[:100]\n",
    "\n",
    "all_height = cat_1 + cat_2 + cat_3 + cat_4 + cat_5 + cat_6\n",
    "\n",
    "for cat in all_height:\n",
    "    if cat not in selected_cols:\n",
    "        selected_cols[cat] = {}\n",
    "    if 'height' not in selected_cols[cat]:\n",
    "        selected_cols[cat]['height'] = 'Value Heterogeneity'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "739b9d04",
   "metadata": {},
   "source": [
    "### width"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d0ab3ae2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Locate all width columns and remove already selected ones\n",
    "col_name = 'width'\n",
    "wid = text.loc[(text['column_name'] == 'width') & (text['file_name'].isin(tables)) ]['file_name'].tolist()\n",
    "for tab in selected_cols:\n",
    "    if 'width' in selected_cols[tab] and tab in wid:\n",
    "        wid.remove(tab)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75c75009",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get all values of these columns\n",
    "pool = multiprocessing.Pool(processes=30)\n",
    "values = pool.map(get_values, wid)\n",
    "pool.close()\n",
    "pool.join()\n",
    "\n",
    "#and put them in a dataframe\n",
    "width_list = []\n",
    "i = 0\n",
    "for val in values:\n",
    "    class_ = wid[i].split('_')[0]\n",
    "    \n",
    "    for col in val:\n",
    "        width_list.append([class_, col, wid[i], val[col]])\n",
    "        \n",
    "    i += 1\n",
    "    \n",
    "width = pd.DataFrame( width_list, columns=['class', 'column_name', 'file_name', 'value'] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3b78fc7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Group width columns under different categories based on measure metrics used\n",
    "width['category'] = width['value'].apply(lambda row: height_cat(str(row).lower()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a022588c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Select 100 columns from each category\n",
    "\n",
    "cat_1 = width.loc[width['category'] == 1]['file_name'].tolist()[:100]\n",
    "cat_2 = width.loc[width['category'] == 2]['file_name'].tolist()[:100]\n",
    "cat_3 = width.loc[width['category'] == 3]['file_name'].tolist()[:100]\n",
    "cat_4 = width.loc[width['category'] == 4]['file_name'].tolist()[:100]\n",
    "cat_6 = width.loc[width['category'] == 6]['file_name'].tolist()[:100]\n",
    "\n",
    "all_width = cat_1 + cat_2 + cat_3 + cat_4 + cat_6\n",
    "\n",
    "for cat in all_width:\n",
    "    if cat not in selected_cols:\n",
    "        selected_cols[cat] = {}\n",
    "    if 'width' not in selected_cols[cat]:\n",
    "        selected_cols[cat]['width'] = 'Value Heterogeneity'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31fe5eeb",
   "metadata": {},
   "source": [
    "### faxNumber"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c12418a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Locate all faxnumber columns and remove already selected ones\n",
    "col_name = 'faxnumber'\n",
    "fax = text.loc[(text['column_name'] == 'faxnumber') & (text['file_name'].isin(tables)) ]['file_name'].tolist()\n",
    "for tab in selected_cols:\n",
    "    if 'faxnumber' in selected_cols[tab] and tab in fax:\n",
    "        fax.remove(tab)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0672173",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get all values of these columns\n",
    "pool = multiprocessing.Pool(processes=30)\n",
    "values = pool.map(get_values, fax)\n",
    "pool.close()\n",
    "pool.join()\n",
    "\n",
    "#and put them in a dataframe\n",
    "faxnumber_list = []\n",
    "i = 0\n",
    "for val in values:\n",
    "    class_ = fax[i].split('_')[0]\n",
    "    \n",
    "    for col in val:\n",
    "        faxnumber_list.append([class_, col, fax[i], val[col]])\n",
    "        \n",
    "    i += 1\n",
    "    \n",
    "faxnumber = pd.DataFrame( faxnumber_list, columns=['class', 'column_name', 'file_name', 'value'] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97fcdf3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "faxnumber"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb9e2efe",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Calculate numerical percentage of values in a column\n",
    "v = faxnumber['value'].tolist()\n",
    "\n",
    "pool = multiprocessing.Pool(processes=21)\n",
    "res = pool.map(num_perc, range(len(v)))\n",
    "pool.close()\n",
    "pool.join()\n",
    "\n",
    "faxnumber['num_percentage'] = res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c8ae38b",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Select 800 columns from 2 categories\n",
    "fax_1 = faxnumber.loc[(faxnumber['num_percentage'] > 0.5) & (faxnumber['num_percentage'] < 0.7)][:800]['file_name'].tolist()\n",
    "fax_2 = faxnumber.loc[(faxnumber['num_percentage'] > 0.8)][:800]['file_name'].tolist()\n",
    "\n",
    "for tab in fax_1:\n",
    "    if tab not in selected_cols:\n",
    "        selected_cols[tab] = {}\n",
    "    if 'faxnumber' not in selected_cols[tab]:\n",
    "        selected_cols[tab]['faxnumber'] = 'Value Heterogeneity'\n",
    "        \n",
    "        \n",
    "for tab in fax_2:\n",
    "    if tab not in selected_cols:\n",
    "        selected_cols[tab] = {}\n",
    "    if 'faxnumber' not in selected_cols[tab]:\n",
    "        selected_cols[tab]['faxnumber'] = 'Value Heterogeneity'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac7aeb70",
   "metadata": {},
   "source": [
    "### servingSize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75fdaf15",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Locate all nutrition:servingsize columns and remove already selected ones\n",
    "col_name = 'nutrition:servingsize'\n",
    "ser = text.loc[(text['column_name'] == 'nutrition:servingsize') & (text['file_name'].isin(tables)) ]['file_name'].tolist()\n",
    "for tab in selected_cols:\n",
    "    if 'nutrition:servingsize' in selected_cols[tab] and tab in ser:\n",
    "        ser.remove(tab)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c5fa7fef",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get all values of these columns\n",
    "pool = multiprocessing.Pool(processes=30)\n",
    "values = pool.map(get_values, ser)\n",
    "pool.close()\n",
    "pool.join()\n",
    "\n",
    "#and put them in a dataframe\n",
    "serving_list = []\n",
    "i = 0\n",
    "for val in values:\n",
    "    class_ = ser[i].split('_')[0]\n",
    "    \n",
    "    for col in val:\n",
    "        serving_list.append([class_, col, ser[i], val[col]])\n",
    "        \n",
    "    i += 1\n",
    "    \n",
    "    \n",
    "serving = pd.DataFrame( serving_list, columns=['class', 'column_name', 'file_name', 'value'] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88e6d318",
   "metadata": {},
   "outputs": [],
   "source": [
    "def serving_cat(row):\n",
    "    if 'cup' in row:\n",
    "        return 1\n",
    "    elif 'serving' in row:\n",
    "        return 2\n",
    "    elif 'ounce' in row:\n",
    "        return 3\n",
    "    elif 'slice' in row:\n",
    "        return 4\n",
    "    elif 'oz' in row:\n",
    "        return 5\n",
    "    elif 'portion' in row:\n",
    "        return 6\n",
    "    elif 'gram' in row:\n",
    "        return 7\n",
    "    elif 'mg' in row:\n",
    "        return 8\n",
    "    else:\n",
    "        return None\n",
    "    \n",
    "serving['category'] = serving['value'].apply(lambda row: serving_cat(str(row).lower()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86f2216a",
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_1 = serving.loc[serving['category'] == 1]['file_name'].tolist()[:100]\n",
    "cat_2 = serving.loc[serving['category'] == 2]['file_name'].tolist()[:100]\n",
    "cat_3 = serving.loc[serving['category'] == 3]['file_name'].tolist()[:100]\n",
    "cat_4 = serving.loc[serving['category'] == 4]['file_name'].tolist()[:100]\n",
    "cat_5 = serving.loc[serving['category'] == 5]['file_name'].tolist()[:100]\n",
    "cat_6 = serving.loc[serving['category'] == 6]['file_name'].tolist()[:100]\n",
    "cat_7 = serving.loc[serving['category'] == 7]['file_name'].tolist()[:100]\n",
    "cat_8 = serving.loc[serving['category'] == 8]['file_name'].tolist()[:100]\n",
    "\n",
    "all_serving = cat_1 + cat_2 + cat_3 + cat_4 + cat_5 + cat_6 + cat_7 + cat_8\n",
    "\n",
    "for cat in all_serving:\n",
    "    if cat not in selected_cols:\n",
    "        selected_cols[cat] = {}\n",
    "    if 'nutrition:servingsize' not in selected_cols[cat]:\n",
    "        selected_cols[cat]['nutrition:servingsize'] = 'Value Heterogeneity'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "811f89fd",
   "metadata": {},
   "source": [
    "## content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61ba728d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Locate all content columns and remove already selected ones\n",
    "col_names = ['nutrition:fatcontent', 'nutrition:carbohydratecontent', 'nutrition:proteincontent', 'nutrition:sodiumcontent', 'nutrition:sugarcontent', 'nutrition:saturatedfatcontent', 'nutrition:fibercontent', 'nutrition:cholesterolcontent', 'nutrition:transfatcontent', 'nutrition:unsaturatedfatcontent']\n",
    "con = set()\n",
    "for col in col_names:\n",
    "    for t in text.loc[(text['column_name'] == col) & (text['file_name'].isin(tables)) ]['file_name'].tolist():\n",
    "        con.add(t)\n",
    "for tab in selected_cols:\n",
    "    for col in col_names:\n",
    "        if col in selected_cols[tab] and tab in con:\n",
    "            con.remove(tab)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03243fac",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get all values of these columns\n",
    "fatcontent = pd.DataFrame(columns=['class', 'column_name', 'file_name', 'value'])\n",
    "for col in col_names:\n",
    "    \n",
    "    con = []\n",
    "    for t in text.loc[(text['column_name'] == col) & (text['file_name'].isin(tables)) ]['file_name'].tolist():\n",
    "        con.append(t)\n",
    "        \n",
    "    for tab in selected_cols:\n",
    "        if col in selected_cols[tab] and tab in con:\n",
    "            con.remove(tab)\n",
    "    \n",
    "    col_name = col\n",
    "    pool = multiprocessing.Pool(processes=30)\n",
    "    values = pool.map(get_values, con)\n",
    "    pool.close()\n",
    "    pool.join()\n",
    "    \n",
    "    sub = []\n",
    "    i = 0\n",
    "    for val in values:\n",
    "        class_ = con[i].split('_')[0]\n",
    "\n",
    "        for col in val:\n",
    "            sub.append([class_, col, con[i], val[col]])\n",
    "\n",
    "        i += 1\n",
    "        \n",
    "    c = pd.DataFrame( sub, columns=['class', 'column_name', 'file_name', 'value'] )\n",
    "    fatcontent = pd.concat([fatcontent, c])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "664e6cbd",
   "metadata": {},
   "outputs": [],
   "source": [
    "fatcontent['category'] = fatcontent['value'].apply(lambda row: weight_cat(str(row).lower()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30b27165",
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in col_names:\n",
    "    cat_7 = fatcontent.loc[(fatcontent['category'] == 7) & (fatcontent['column_name'] == col )]['file_name'].tolist()[:200]\n",
    "    cat_8 = fatcontent.loc[(fatcontent['category'] == 8) & (fatcontent['column_name'] == col )]['file_name'].tolist()[:200]\n",
    "    \n",
    "    for cat in cat_7+cat_8:\n",
    "        if cat not in selected_cols:\n",
    "            selected_cols[cat] = {}\n",
    "        if col not in selected_cols[cat]:\n",
    "            selected_cols[cat][col] = 'Value Heterogeneity'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a874ac0",
   "metadata": {},
   "source": [
    "### Date columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "04a20896",
   "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": "42fdfeca",
   "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": "156e85c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "#CPA labels: column name to its CPA label\n",
    "rel_lbls = {}\n",
    "for index, row in rels.iterrows():\n",
    "    rel_lbls[row['column_name']] = row['CPA label']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a357c0e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "date = pd.read_csv('output-data/statistics/datecols.csv.gz', compression='gzip')\n",
    "date = date.loc[(date['file_name'].isin(tables)) & (date['column_name'].isin(rel_lbls))]\n",
    "date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2106dc06",
   "metadata": {},
   "outputs": [],
   "source": [
    "format_1 = '^(Aug|Jan|Feb|Mar|May|Apr|June|July|Sep|Oct|Nov|Dec)+ [0-9]*, [0-9]{4}$'\n",
    "format_2 = '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}.[0-9]{3}Z$'\n",
    "format_3 = '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'\n",
    "format_4 = '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}\\+[0-9]{2}:[0-9]{2}$'\n",
    "format_5 = '^(January|February|March|April|May|June|July|August|September|October|November|December)+ [0-9]{1,}, [0-9]{4}$'\n",
    "format_6 = '^(Aug|Jan|Feb|Mar|May|Apr|June|July|Sep|Oct|Nov|Dec)+\\. [0-9]{2}, [0-9]{4}, [0-9]{1,2}:[0-9]{2} (a|p)+\\.m\\.$'\n",
    "format_7 = '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}\\.[0-9]{6}$'\n",
    "format_8 = '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}$'\n",
    "format_9 = '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}(\\+|\\-)+[0-9]{2}:[0-9]{2}$'\n",
    "format_10 = '^[0-9]{2}\\/[0-9]{2}\\/[0-9]{4}$'\n",
    "format_11 = '^[0-9]{2}\\/[0-9]{2}\\/[0-9]{4} [0-9]{1,2}:[0-9]{2}:[0-9]{2} (am|pm|AM|PM)$'\n",
    "format_12 = '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{1,2}:[0-9]{2}:[0-9]{2} [a-zA-Z]{3}$'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c5c3f3e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Group all date columns under different categories based on the formats\n",
    "different_date_formats = {1:[], 2:[], 3:[], 4:[], 5:[], 6:[], 7:[], 8:[], 9:[], 10:[], 11:[], 12:[]}\n",
    "\n",
    "for index, row in date.iterrows():\n",
    "    val = eval(row['value'])\n",
    "    \n",
    "    #print(val[0])\n",
    "    \n",
    "    if isinstance(val[0], str):\n",
    "        check = val[0]\n",
    "    elif isinstance(val[0], dict):\n",
    "        if 'date' in val[0]:\n",
    "            check = val[0]['date']\n",
    "        elif 'datepublished' in val[0]:\n",
    "            check = val[0]['datepublished']\n",
    "        else:\n",
    "            check = val[0]['date']\n",
    "            #print(val[0])\n",
    "    elif isinstance(val[0], list):\n",
    "        check = val[0][0]\n",
    "        \n",
    "    #print(check)\n",
    "    if isinstance(check,list):\n",
    "        check = check[0]\n",
    "    elif pd.isnull(check):\n",
    "        check = ''        \n",
    "                \n",
    "    if re.match(format_1, check):\n",
    "        different_date_formats[1].append([row['class'], row['column_name'], row['file_name'], row['value']])\n",
    "    elif re.match(format_2, check):\n",
    "        different_date_formats[2].append([row['class'], row['column_name'], row['file_name'], row['value']])\n",
    "    elif re.match(format_3, check):\n",
    "        different_date_formats[3].append([row['class'], row['column_name'], row['file_name'], row['value']])\n",
    "    elif re.match(format_4, check):\n",
    "        different_date_formats[4].append([row['class'], row['column_name'], row['file_name'], row['value']])\n",
    "    elif re.match(format_5, check):\n",
    "        different_date_formats[5].append([row['class'], row['column_name'], row['file_name'], row['value']])\n",
    "    elif re.match(format_6, check):\n",
    "        different_date_formats[6].append([row['class'], row['column_name'], row['file_name'], row['value']])    \n",
    "    elif re.match(format_7, check):\n",
    "        different_date_formats[7].append([row['class'], row['column_name'], row['file_name'], row['value']])  \n",
    "    elif re.match(format_8, check):\n",
    "        different_date_formats[8].append([row['class'], row['column_name'], row['file_name'], row['value']])\n",
    "    elif re.match(format_9, check):\n",
    "        different_date_formats[9].append([row['class'], row['column_name'], row['file_name'], row['value']])\n",
    "    elif re.match(format_10, check):\n",
    "        different_date_formats[10].append([row['class'], row['column_name'], row['file_name'], row['value']])\n",
    "    elif re.match(format_11, check):\n",
    "        different_date_formats[11].append([row['class'], row['column_name'], row['file_name'], row['value']])\n",
    "    elif re.match(format_12, check):\n",
    "        different_date_formats[12].append([row['class'], row['column_name'], row['file_name'], row['value']])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12677181",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Choose 400 columns from each category\n",
    "for date_format in different_date_formats:\n",
    "    df = pd.DataFrame(different_date_formats[date_format], columns=['class', 'column_name', 'file_name', 'value'])\n",
    "    \n",
    "    cols = list(df['column_name'].unique())\n",
    "    \n",
    "    for col in cols:\n",
    "        some_cols = df.loc[df['column_name'] == col ]['file_name'].tolist()[:400]\n",
    "        \n",
    "        for c in some_cols:\n",
    "            if c not in selected_cols:\n",
    "                selected_cols[c] = {}\n",
    "            if col not in selected_cols[c]:\n",
    "                selected_cols[c][col] = 'Value Heterogeneity'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7eee0bf9",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = pd.read_csv('output-data/statistics/expanded_tables_annotations_cpa.csv')\n",
    "remove_tabs = []\n",
    "for tab in selected_cols:\n",
    "    if not selected_cols[tab]:\n",
    "        remove_tabs.append(tab)\n",
    "\n",
    "for tab in remove_tabs:\n",
    "    del selected_cols[tab]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc25528f",
   "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.to_csv('output-data/cpa-datasets/selected_1_2.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
}