preprocessing-sdm-lab2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "aaf869c0-1aed-4e2e-b3a9-2564d2b100a9",
   "metadata": {},
   "source": [
    "# Preprocessing Data for SDM Lab-2 Knowledge Graphs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a6e52593-947d-49b4-9aa9-8c303b6f971d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f93aa46a-3243-425d-b6d4-d51fca1abdbe",
   "metadata": {},
   "source": [
    "### Author"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "de3fdf2d-047f-41ce-a61d-5b6480867a59",
   "metadata": {},
   "outputs": [],
   "source": [
    "def modify_csv(file_path, output_path):\n",
    "    \n",
    "    data = pd.read_csv(file_path)\n",
    "    data.insert(0, 'authorID', ['a' + str(i) for i in range(len(data))])\n",
    "    data.rename(columns={'ID': 'authorRef'}, inplace=True)\n",
    "    data.to_csv(output_path, index=False)\n",
    "\n",
    "input_author_path = 'authors.csv'  \n",
    "output_author_path = 'authors1.csv' \n",
    "\n",
    "modify_csv(input_author_path,output_author_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6884bd21-fe5e-4532-abd6-15f85097ccec",
   "metadata": {},
   "source": [
    "### Conferences"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "bb396e1c-696e-413c-95f9-d91263a20672",
   "metadata": {},
   "outputs": [],
   "source": [
    "def modify_dataset(input_file_path, output_file_path):\n",
    "\n",
    "        df = pd.read_csv(input_file_path)\n",
    "        df = df.iloc[1:].reset_index(drop=True)\n",
    "        df.insert(0, 'conferenceID', ['c' + str(i) for i in range(len(df))])\n",
    "        df.rename(columns={'ID': 'conferenceRef'}, inplace=True)\n",
    "        edition_index = df.columns.get_loc('edition') + 1\n",
    "        df.insert(edition_index, 'conProIds', ['cp' + str(i) for i in range(len(df))])\n",
    "        df.to_csv(output_file_path, index=False)\n",
    "\n",
    "input_file_path = 'conference_semantic-2.csv'\n",
    "output_file_path = 'conference.csv'\n",
    "modify_dataset(input_file_path, output_file_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa4c8599-60d3-43a8-b3f2-b06f90996329",
   "metadata": {},
   "source": [
    "### Proceedings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "5631a8cb-2e7d-4c93-a610-b3570127c07c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def modify_proceeding(input_file_path, output_file_path):\n",
    "\n",
    "        df = pd.read_csv(input_file_path)\n",
    "        df = df.iloc[1:].reset_index(drop=True)\n",
    "        df = df[['year', 'name']]\n",
    "        df.insert(df.columns.get_loc('name'), 'proceedingID', ['cp' + str(i) for i in range(len(df))])\n",
    "        df.insert(df.columns.get_loc('proceedingID') + 1, 'proceedingTitle', ['proceeding' + str(i) for i in range(len(df))])\n",
    "        df.to_csv(output_file_path, index=False)\n",
    "    \n",
    "input_file_path = 'proceedings0.csv'  \n",
    "output_file_path = 'proceeding.csv'  \n",
    "modify_proceeding(input_file_path, output_file_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "edcd2c7a-c319-4e4c-acf2-3cfc802f4d2d",
   "metadata": {},
   "source": [
    "### Conference-Proceedings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "10e0ae86-a7f0-409c-a6c7-93e63530db2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def conference_proceeding(input_file_path, output_file_path):\n",
    "    \n",
    "        df = pd.read_csv(input_file_path)\n",
    "        df['proceedingTitle'] = ['proceeding' + str(i) for i in range(len(df))]\n",
    "        df.to_csv(output_file_path, index=False)\n",
    "    \n",
    "input_file_path = 'confPro-semantic.csv'  \n",
    "output_file_path = 'conferenceProceedings.csv'  \n",
    "conference_proceeding(input_file_path, output_file_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f25e5830-eb92-4602-aaed-9e3e513e8b9a",
   "metadata": {},
   "source": [
    "### Journal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "2ebbfefd-b41f-45a7-803e-a16cee8a933a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def journal(input_file_path, output_file_path):\n",
    "\n",
    "    df = pd.read_csv(input_file_path)\n",
    "    df = df.drop('END_ID', axis=1)\n",
    "    df.rename(columns={'START_ID': 'journalRef'}, inplace=True)\n",
    "    df.insert(0, 'journalID', ['j' + str(i) for i in range(len(df))])\n",
    "    year_index = df.columns.get_loc('year') + 1\n",
    "    df.insert(year_index, 'journalVID', ['jv' + str(i) for i in range(len(df))])\n",
    "    df.to_csv(output_file_path, index=False)\n",
    "\n",
    "input_file_path = 'paper_published_in_journal-2.csv'  \n",
    "output_file_path = 'journal.csv'  \n",
    "journal(input_file_path, output_file_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9fe2bfef-28eb-4384-8267-a7ab65c201b3",
   "metadata": {},
   "source": [
    "### Journal-Volume"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "11e5228e-9c73-47e9-8152-7f0780a057d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "def journal_volumes(input_file_path, output_file_path):\n",
    "\n",
    "    df = pd.read_csv(input_file_path)\n",
    "    df = df.drop('volume', axis=1)\n",
    "    year_index = df.columns.get_loc('year') + 1\n",
    "    df.insert(year_index, 'volumeName', ['journal' + str(i) for i in range(len(df))])\n",
    "    df.to_csv(output_file_path, index=False)\n",
    "\n",
    "input_file_path = 'journalVolumes-semantic.csv'  \n",
    "output_file_path = 'journal_volumes.csv'  \n",
    "journal_volumes(input_file_path, output_file_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7045f71d-e5b6-4e1f-9884-12939746bce9",
   "metadata": {},
   "source": [
    "### Volumes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "2b95fe36-0f7d-48f7-9671-da7720fe3b44",
   "metadata": {},
   "outputs": [],
   "source": [
    "def volumes(input_file_path, output_file_path):\n",
    "\n",
    "    df = pd.read_csv(input_file_path)\n",
    "    df = df.drop(['journalID', 'journalRef', 'journal'], axis=1)\n",
    "    df.rename(columns={'journalVID': 'volumeID'}, inplace=True)\n",
    "    df.to_csv(output_file_path, index=False)\n",
    "\n",
    "input_file_path = 'journal_volumes.csv'  \n",
    "output_file_path = 'volumes.csv'  \n",
    "volumes(input_file_path, output_file_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73d844a2-9ee9-44c0-af79-76e659e59d37",
   "metadata": {},
   "source": [
    "## Chair"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "427bbf37-2173-4a57-a4a5-1a413f511d5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "f7821dd3-d1f5-45e8-8d12-73db24cdff50",
   "metadata": {},
   "outputs": [],
   "source": [
    "def chair(conference_path, authors_path, output_path):\n",
    "    conference_df = pd.read_csv(conference_path)\n",
    "    authors_df = pd.read_csv(authors_path)\n",
    "\n",
    "    if 'conferenceID' not in conference_df.columns:\n",
    "        raise ValueError(\"The conference.csv file does not contain the 'conferenceID' column.\")\n",
    "    if 'authorID' not in authors_df.columns:\n",
    "        raise ValueError(\"The authors1.csv file does not contain the 'authorID' column.\")\n",
    "\n",
    "    min_length = min(len(conference_df['conferenceID']), len(authors_df['authorID']))\n",
    "    conference_ids = conference_df['conferenceID'].sample(min_length, random_state=1).reset_index(drop=True)\n",
    "    author_ids = authors_df['authorID'].sample(min_length, random_state=1).reset_index(drop=True)\n",
    "\n",
    "    new_df = pd.DataFrame({\n",
    "        'conferenceID': conference_ids,\n",
    "        'authorID': author_ids,\n",
    "        'chairID': [f'chair{i}' for i in range(min_length)]\n",
    "    })\n",
    "\n",
    "    new_df.to_csv(output_path, index=False)\n",
    "\n",
    "conference_csv_path = 'conference.csv'\n",
    "authors_csv_path = 'authors1.csv'\n",
    "output_csv_path = 'chair.csv'\n",
    "\n",
    "output_dir = os.path.dirname(output_csv_path)\n",
    "if output_dir:\n",
    "    os.makedirs(output_dir, exist_ok=True)\n",
    "\n",
    "chair(conference_csv_path, authors_csv_path, output_csv_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c62814c7-1650-4e42-a39e-3c5ed55ac903",
   "metadata": {},
   "source": [
    "## Editor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "ecd5d8fb-7af9-44c3-9248-ded1165a2ce2",
   "metadata": {},
   "outputs": [],
   "source": [
    "def editor(journal_path, authors_path, output_path):\n",
    "    journal_df = pd.read_csv(journal_path)\n",
    "    authors_df = pd.read_csv(authors_path)\n",
    "\n",
    "    if 'journalID' not in journal_df.columns:\n",
    "        raise ValueError(\"The journal.csv file does not contain the 'journalID' column.\")\n",
    "    if 'authorID' not in authors_df.columns:\n",
    "        raise ValueError(\"The authors1.csv file does not contain the 'authorID' column.\")\n",
    "\n",
    "    min_length = min(len(journal_df['journalID']), len(authors_df['authorID']))\n",
    "    journal_ids = journal_df['journalID'].sample(min_length, random_state=1).reset_index(drop=True)\n",
    "    author_ids = authors_df['authorID'].sample(min_length, random_state=1).reset_index(drop=True)\n",
    "\n",
    "    new_df = pd.DataFrame({\n",
    "        'journalID': journal_ids,\n",
    "        'authorID': author_ids,\n",
    "        'editorID': [f'chair{i}' for i in range(min_length)]\n",
    "    })\n",
    "\n",
    "    new_df.to_csv(output_path, index=False)\n",
    "\n",
    "journal_csv_path = 'journal.csv'\n",
    "authors_csv_path = 'authors1.csv'\n",
    "output_csv_path = 'editor.csv'\n",
    "\n",
    "output_dir = os.path.dirname(output_csv_path)\n",
    "if output_dir:\n",
    "    os.makedirs(output_dir, exist_ok=True)\n",
    "\n",
    "editor(journal_csv_path, authors_csv_path, output_csv_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd641384-966a-4148-a4c3-2162c3798237",
   "metadata": {},
   "source": [
    "## Papers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "d16e4e0d-3102-455a-8546-18b3b23f86bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_data(file_path):\n",
    "    return pd.read_csv(file_path)\n",
    "\n",
    "def drop_columns(dataframe, columns):\n",
    "    return dataframe.drop(columns=columns)\n",
    "\n",
    "def extract_combine_ids(conference_df, journal_df,):\n",
    "    conference_ids = conference_df['conferenceID']\n",
    "    journal_ids = journal_df['journalID']\n",
    "    combined_ids = pd.DataFrame({'conJourID': pd.concat([conference_ids, journal_ids]).sort_index(kind='merge').reset_index(drop=True)})\n",
    "    \n",
    "    return combined_ids\n",
    "\n",
    "def save_data(dataframe, file_path):\n",
    "    dataframe.to_csv(file_path, index=False)\n",
    "    \n",
    "def main():\n",
    "    papers_semantic = load_data('papers_semantic.csv')\n",
    "    conference = load_data('conference.csv')\n",
    "    journal = load_data('journal.csv')\n",
    "\n",
    "    papers_semantic_cleaned = drop_columns(papers_semantic, ['pages', 'doi', 'link'])\n",
    "    combined_ids = extract_combine_ids(conference, journal)\n",
    "    papers_semantic_final = pd.concat([papers_semantic_cleaned, combined_ids], axis=1)\n",
    "    \n",
    "    save_data(papers_semantic_final, 'papers.csv')\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    main()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "852221f7-d34d-487e-9dd7-4f600195d86e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "779312f0-cc14-48f9-b6bd-b1033ae97f12",
   "metadata": {},
   "outputs": [],
   "source": [
    "def combine_paper_data(papers_file, conference_file, journal_file, output_file):\n",
    "    try:\n",
    "        papers_semantic_df = pd.read_csv(papers_file)\n",
    "        conference_df = pd.read_csv(conference_file)\n",
    "        journal_df = pd.read_csv(journal_file)\n",
    "    except FileNotFoundError as e:\n",
    "        print(f\"Error: {e}\")\n",
    "        return\n",
    "    except pd.errors.EmptyDataError as e:\n",
    "        print(f\"Error: {e}\")\n",
    "        return\n",
    "    except pd.errors.ParserError as e:\n",
    "        print(f\"Error: {e}\")\n",
    "        return\n",
    "\n",
    "    papers_semantic_df.drop(columns=[\"pages\", \"doi\", \"link\"], inplace=True)\n",
    "    \n",
    "    conf_ids = list(conference_df[\"conferenceID\"])\n",
    "    jour_ids = list(journal_df[\"journalID\"])\n",
    "    conf_titles = list(conference_df[\"name\"])\n",
    "    jour_titles = list(journal_df[\"journal\"])\n",
    "\n",
    "    min_len = min(len(conf_ids), len(jour_ids))\n",
    "\n",
    "    mixed_ids = []\n",
    "    mixed_titles = []\n",
    "\n",
    "    for i in range(min_len):\n",
    "        mixed_ids.append(conf_ids[i])\n",
    "        mixed_ids.append(jour_ids[i])\n",
    "        mixed_titles.append(conf_titles[i])\n",
    "        mixed_titles.append(jour_titles[i])\n",
    "\n",
    "    mixed_ids.extend(conf_ids[min_len:])\n",
    "    mixed_ids.extend(jour_ids[min_len:])\n",
    "    mixed_titles.extend(conf_titles[min_len:])\n",
    "    mixed_titles.extend(jour_titles[min_len:])\n",
    "\n",
    "    np.random.shuffle(mixed_ids)\n",
    "    np.random.shuffle(mixed_titles)\n",
    "    \n",
    "    confjorID = pd.Series(mixed_ids[:len(papers_semantic_df)])\n",
    "    confjorTitle = pd.Series(mixed_titles[:len(papers_semantic_df)])\n",
    "    \n",
    "    conference_journal_df = pd.DataFrame({\"confjorID\": confjorID, \"confjorTitle\": confjorTitle})\n",
    "    combined_df = pd.concat([papers_semantic_df.reset_index(drop=True), conference_journal_df], axis=1)\n",
    "    combined_df.insert(0, \"paperID\", [\"p\" + str(i) for i in range(len(combined_df))])\n",
    "    combined_df.to_csv(output_file, index=False)\n",
    "\n",
    "papers_file = 'papers_semantic.csv'\n",
    "conference_file = 'conference.csv'\n",
    "journal_file = 'journal.csv'\n",
    "output_file = 'papers_combined.csv'\n",
    "\n",
    "combine_paper_data(papers_file, conference_file, journal_file, output_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18ba1451-eace-41ff-aa8f-65da315f53e0",
   "metadata": {},
   "source": [
    "## Author-Paper"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "9d8c1566-fabc-497e-874c-17c9ae1b8d82",
   "metadata": {},
   "outputs": [],
   "source": [
    "authors_df = pd.read_csv('authors1.csv')\n",
    "papers_combined_df = pd.read_csv('papers_combined.csv')\n",
    "\n",
    "def distribute_paper_data_evenly(paper_ids_df, chunk_size=100):\n",
    "    paper_ids = paper_ids_df['paperID'].tolist()\n",
    "    ids = paper_ids_df['ID'].tolist()\n",
    "    titles = paper_ids_df['title'].tolist()\n",
    "    abstracts = paper_ids_df['abstract'].tolist()\n",
    "    confjor_ids = paper_ids_df['confjorID'].tolist()\n",
    "    confjor_titles = paper_ids_df['confjorTitle'].tolist()\n",
    "    \n",
    "    arranged_paper_ids = []\n",
    "    arranged_ids = []\n",
    "    arranged_titles = []\n",
    "    arranged_abstracts = []\n",
    "    arranged_confjor_ids = []\n",
    "    arranged_confjor_titles = []\n",
    "    \n",
    "    for i in range(0, len(paper_ids), chunk_size):\n",
    "        chunk_end = min(i + chunk_size, len(paper_ids))\n",
    "        chunk_size_adjusted = chunk_end - i\n",
    "        \n",
    "        arranged_paper_ids.extend([paper_ids[i]] * chunk_size_adjusted)\n",
    "        arranged_ids.extend([ids[i]] * chunk_size_adjusted)\n",
    "        arranged_titles.extend([titles[i]] * chunk_size_adjusted)\n",
    "        arranged_abstracts.extend([abstracts[i]] * chunk_size_adjusted)\n",
    "        arranged_confjor_ids.extend([confjor_ids[i]] * chunk_size_adjusted)\n",
    "        arranged_confjor_titles.extend([confjor_titles[i]] * chunk_size_adjusted)\n",
    "    \n",
    "    arranged_paper_ids = arranged_paper_ids[:len(paper_ids)]\n",
    "    arranged_ids = arranged_ids[:len(ids)]\n",
    "    arranged_titles = arranged_titles[:len(titles)]\n",
    "    arranged_abstracts = arranged_abstracts[:len(abstracts)]\n",
    "    arranged_confjor_ids = arranged_confjor_ids[:len(confjor_ids)]\n",
    "    arranged_confjor_titles = arranged_confjor_titles[:len(confjor_titles)]\n",
    "    \n",
    "    return arranged_paper_ids, arranged_ids, arranged_titles, arranged_abstracts, arranged_confjor_ids, arranged_confjor_titles\n",
    "\n",
    "shuffled_paper_id_df = papers_combined_df.sample(frac=1, random_state=42).reset_index(drop=True)\n",
    "arranged_paper_ids, arranged_ids, arranged_titles, arranged_abstracts, arranged_confjor_ids, arranged_confjor_titles = distribute_paper_data_evenly(shuffled_paper_id_df)\n",
    "\n",
    "num_authors = len(authors_df)\n",
    "while len(arranged_paper_ids) < num_authors:\n",
    "    arranged_paper_ids.extend(arranged_paper_ids)\n",
    "    arranged_ids.extend(arranged_ids)\n",
    "    arranged_titles.extend(arranged_titles)\n",
    "    arranged_abstracts.extend(arranged_abstracts)\n",
    "    arranged_confjor_ids.extend(arranged_confjor_ids)\n",
    "    arranged_confjor_titles.extend(arranged_confjor_titles)\n",
    "\n",
    "arranged_paper_ids = arranged_paper_ids[:num_authors]\n",
    "arranged_ids = arranged_ids[:num_authors]\n",
    "arranged_titles = arranged_titles[:num_authors]\n",
    "arranged_abstracts = arranged_abstracts[:num_authors]\n",
    "arranged_confjor_ids = arranged_confjor_ids[:num_authors]\n",
    "arranged_confjor_titles = arranged_confjor_titles[:num_authors]\n",
    "\n",
    "authors_with_chunks = authors_df.copy()\n",
    "\n",
    "authors_with_chunks['paperID'] = ''\n",
    "authors_with_chunks['ID'] = ''\n",
    "authors_with_chunks['title'] = ''\n",
    "authors_with_chunks['abstract'] = ''\n",
    "authors_with_chunks['confjorID'] = ''\n",
    "authors_with_chunks['confjorTitle'] = ''\n",
    "\n",
    "authors_with_chunks['paperID'] = arranged_paper_ids\n",
    "authors_with_chunks['ID'] = arranged_ids\n",
    "authors_with_chunks['title'] = arranged_titles\n",
    "authors_with_chunks['abstract'] = arranged_abstracts\n",
    "authors_with_chunks['confjorID'] = arranged_confjor_ids\n",
    "authors_with_chunks['confjorTitle'] = arranged_confjor_titles\n",
    "\n",
    "authors_with_chunks.to_csv('authorsPapers_.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d46237d-5977-4bff-932e-db752abd6b5b",
   "metadata": {},
   "source": [
    "## Publications"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "1cf3aaf4-c224-49b3-bdcc-34e8cab101f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "def publications(input_file_path, output_file_path):\n",
    "    import pandas as pd\n",
    "\n",
    "    df = pd.read_csv(input_file_path)\n",
    "    df.drop(columns=[\"ID\", \"confjorID\", \"confjorTitle\"], inplace=True)\n",
    "    df.insert(0, 'publicationID', ['pub' + str(i) for i in range(len(df))])\n",
    "    df.to_csv(output_file_path, index=False)\n",
    "\n",
    "input_file_path = 'papers_combined.csv'  \n",
    "output_file_path = 'publications.csv' \n",
    "\n",
    "publications(input_file_path, output_file_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b20ed47c-0e20-420b-bbc0-38943c57ac0e",
   "metadata": {},
   "source": [
    "## Topic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "40d64ba0-89a3-4c2f-b035-5ad221b194b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def topic(input_file_path, output_file_path):\n",
    "    df = pd.read_csv(input_file_path)\n",
    "    df = df.drop(columns=[\"domain\", \"ID\"])\n",
    "    df.insert(0, 'topicID', ['t' + str(i) for i in range(len(df))])\n",
    "    df = df.rename(columns={'name': 'topicName'})\n",
    "    df.to_csv(output_file_path, index=False)\n",
    "\n",
    "input_file_path = 'keywords_semantic-2.csv'\n",
    "output_file_path = 'topic.csv'\n",
    "\n",
    "topic(input_file_path, output_file_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdfaef3a-db28-4549-aba5-48d2a802199b",
   "metadata": {},
   "source": [
    "## Topic-Paper"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "c2d7f605-3048-4f45-b430-c247fe0f2eb4",
   "metadata": {},
   "outputs": [],
   "source": [
    "topic_df = pd.read_csv('topic.csv')\n",
    "papers_combined_df = pd.read_csv('papers_combined.csv')\n",
    "\n",
    "if len(topic_df) < len(papers_combined_df):\n",
    "    raise ValueError(\"Not enough rows in topic.csv to match papers_combined.csv\")\n",
    "\n",
    "sampled_topic_df = topic_df.sample(n=len(papers_combined_df), replace=True, random_state=42).reset_index(drop=True)\n",
    "papers_combined_with_topics = pd.concat([papers_combined_df, sampled_topic_df], axis=1)\n",
    "\n",
    "columns = papers_combined_with_topics.columns.tolist()\n",
    "new_order = columns[-2:] + columns[:-2]\n",
    "reordered_df = papers_combined_with_topics[new_order]\n",
    "\n",
    "output_file = 'topicsPapers.csv'\n",
    "reordered_df.to_csv(output_file, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c242a63-f68d-4898-95d1-9b19970c2517",
   "metadata": {},
   "source": [
    "## Topics-Proceedings-Volumes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "eb8fbf02-b359-4db7-810c-4b8d4a03c492",
   "metadata": {},
   "outputs": [],
   "source": [
    "topic_df = pd.read_csv('topic.csv')\n",
    "tpv_df = pd.read_csv('conference.csv')\n",
    "\n",
    "if len(topic_df) < len(tpv_df):\n",
    "    raise ValueError(\"Not enough rows in topic.csv to match papers_combined.csv\")\n",
    "\n",
    "sampled_topic_df = topic_df.sample(n=len(tpv_df), replace=True, random_state=42).reset_index(drop=True)\n",
    "tpv_topics = pd.concat([tpv_df, sampled_topic_df], axis=1)\n",
    "\n",
    "columns = tpv_topics.columns.tolist()\n",
    "new_order = columns[-2:] + columns[:-2]\n",
    "reordered_df = tpv_topics[new_order]\n",
    "\n",
    "output_file = 'conference.csv'\n",
    "reordered_df.to_csv(output_file, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "4e8527d7-a5cf-47c1-9705-8d0c049c7fd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = 'conference.csv'\n",
    "df = pd.read_csv(file_path)\n",
    "\n",
    "combined_ids = []\n",
    "\n",
    "for i in range(len(df)):\n",
    "    if i % 2 == 0:\n",
    "        combined_ids.append(df.loc[i, 'journalID'])\n",
    "    else:\n",
    "        combined_ids.append(df.loc[i, 'conferenceID'])\n",
    "\n",
    "df['jcIDs'] = combined_ids\n",
    "\n",
    "df.to_csv('tpv.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "fc5acec9-ec42-4bd4-a906-67a8b01e0e86",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = 'tpv.csv'\n",
    "df = pd.read_csv(file_path)\n",
    "\n",
    "combined_ids = []\n",
    "\n",
    "for i in range(len(df)):\n",
    "    if i % 2 == 0:\n",
    "        combined_ids.append(df.loc[i, 'journalVID'])\n",
    "    else:\n",
    "        combined_ids.append(df.loc[i, 'conProIds'])\n",
    "\n",
    "df['vpIDs'] = combined_ids\n",
    "\n",
    "df.to_csv('vp.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "4f83360c-da26-4dee-bab6-8c4931abf711",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('vp.csv')\n",
    "df = df.drop(columns=[\"journalID\", \"conferenceID\", \"journalVID\", \"conProIds\"])\n",
    "df.to_csv('topicsProceedingsVolumes.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d18e4d4-572d-4d2a-a547-77767267932c",
   "metadata": {},
   "source": [
    "## Reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "e182e49d-08b4-4739-88dc-89d430e5b1a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = 'publications.csv'\n",
    "df = pd.read_csv(file_path)\n",
    "\n",
    "review_decision = []\n",
    "review_decision_boolean = []\n",
    "\n",
    "for i in range(len(df)):\n",
    "    if i % 4 == 0:\n",
    "        review_decision.append('rejected')\n",
    "        review_decision_boolean.append(0)\n",
    "    else:\n",
    "        review_decision.append('accepted')\n",
    "        review_decision_boolean.append(1)\n",
    "\n",
    "df['reviewDecision'] = review_decision\n",
    "df['reviewDecisionBoolean'] = review_decision_boolean\n",
    "\n",
    "df.to_csv('reviews.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "9c6e61cd-32a5-4296-b102-92242200bf66",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('reviews.csv')\n",
    "df = df.drop('abstract', axis=1)\n",
    "df.insert(0,'reviewID', ['r' + str(i) for i in range(len(df))])\n",
    "df.to_csv('reviews.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57648cff-0764-4878-afa5-b64960d3dc25",
   "metadata": {},
   "source": [
    "## Reviewers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "99cd0c8a-50f2-4a4d-a7d4-16ff4021e065",
   "metadata": {},
   "outputs": [],
   "source": [
    "topic_df = pd.read_csv('authors1.csv')\n",
    "reviewers_df = pd.read_csv('reviewers.csv')\n",
    "\n",
    "if len(topic_df) < len(reviewers_df):\n",
    "    raise ValueError(\"Not enough rows in topic.csv to match papers_combined.csv\")\n",
    "\n",
    "sampled_topic_df = topic_df.sample(n=len(reviewers_df), replace=True, random_state=42).reset_index(drop=True)\n",
    "reviewers_a = pd.concat([reviewers_df, sampled_topic_df], axis=1)\n",
    "\n",
    "columns = reviewers_a.columns.tolist()\n",
    "new_order = columns[-2:] + columns[:-2]\n",
    "reordered_df = reviewers_a[new_order]\n",
    "\n",
    "output_file = 'reviewers.csv'\n",
    "reordered_df.to_csv(output_file, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "238f11c7-1b72-40ce-96bb-752bff599b20",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('reviewers.csv')\n",
    "#df['reviewID'] = np.random.permutation(df['reviewID'])\n",
    "shuffled_indices = np.random.permutation(df.index)\n",
    "df_shuffled = df.copy()\n",
    "df_shuffled[['publicationID', 'paperID', 'title']] = df[['publicationID', 'paperID', 'title']].iloc[shuffled_indices].values\n",
    "df_shuffled.to_csv('reviewers.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c6e9bdf-fe92-4716-979d-c81378fbc516",
   "metadata": {},
   "source": [
    "## Supervisor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "2af89440-8f46-4138-b7ce-438a5bdc3c01",
   "metadata": {},
   "outputs": [],
   "source": [
    "chair_file_path = 'chair.csv'\n",
    "authors_papers_file_path = 'authorsPapers.csv'\n",
    "\n",
    "df_chair = pd.read_csv(chair_file_path)\n",
    "df_authors_papers = pd.read_csv(authors_papers_file_path)\n",
    "\n",
    "conference_ids = []\n",
    "chair_ids = []\n",
    "\n",
    "num_chairs = len(df_chair)\n",
    "for i in range(len(df_authors_papers)):\n",
    "    chair_index = i // 10 % num_chairs  \n",
    "    conference_ids.append(df_chair.loc[chair_index, 'conferenceID'])\n",
    "    chair_ids.append(f'chair{chair_index}')\n",
    "\n",
    "df_authors_papers['conferenceID'] = conference_ids\n",
    "df_authors_papers['chairID'] = chair_ids\n",
    "\n",
    "df_authors_papers.to_csv('supervisors.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e13ef047-1a40-433a-bdbd-25d49c015445",
   "metadata": {},
   "source": [
    "# Extra modifications of data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "2b969c42-c70c-4f83-a66d-c752dc2db8bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import random\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "85382add-294e-4297-b070-f08cb4f0f805",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The script has been executed successfully and the updated file is saved as 'updated_papers_combined.csv'.\n"
     ]
    }
   ],
   "source": [
    "def load_csv(file_path):\n",
    "    return pd.read_csv(file_path)\n",
    "\n",
    "def extract_column_values(df, column_name):\n",
    "    return df[column_name].tolist()\n",
    "\n",
    "def alternate_combine_lists(list1, list2):\n",
    "    combined_list = []\n",
    "    for i in range(max(len(list1), len(list2))):\n",
    "        if i < len(list1):\n",
    "            combined_list.append(list1[i])\n",
    "        if i < len(list2):\n",
    "            combined_list.append(list2[i])\n",
    "    random.shuffle(combined_list)  # Shuffle to introduce randomness\n",
    "    return combined_list\n",
    "\n",
    "def match_length(source_list, target_length):\n",
    "    while len(source_list) < target_length:\n",
    "        source_list.extend(random.sample(source_list, target_length - len(source_list)))\n",
    "    return source_list[:target_length]\n",
    "\n",
    "def add_combined_column(df, combined_list, new_column_name):\n",
    "    df[new_column_name] = combined_list\n",
    "\n",
    "def save_csv(df, file_path):\n",
    "    df.to_csv(file_path, index=False)\n",
    "\n",
    "def main():\n",
    "    papers_combined_path = 'data/papers_combined.csv'\n",
    "    conference_proceedings_path = 'data/conferenceProceedings.csv'\n",
    "    volumes_path = 'data/volumes.csv'\n",
    "    output_path = 'data/updated_papers_combined.csv'\n",
    "\n",
    "    papers_combined = load_csv(papers_combined_path)\n",
    "    conference_proceedings = load_csv(conference_proceedings_path)\n",
    "    volumes = load_csv(volumes_path)\n",
    "\n",
    "    conProIds = extract_column_values(conference_proceedings, 'conProIds')\n",
    "    volumeIDs = extract_column_values(volumes, 'volumeID')\n",
    "\n",
    "    combined_ids = alternate_combine_lists(conProIds, volumeIDs)\n",
    "    combined_ids = match_length(combined_ids, len(papers_combined))\n",
    "\n",
    "    add_combined_column(papers_combined, combined_ids, 'proceedingsVolumesIds')\n",
    "    save_csv(papers_combined, output_path)\n",
    "\n",
    "    print(\"The script has been executed successfully and the updated file is saved as 'updated_papers_combined.csv'.\")\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    main()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d1f49c24-3fec-4db8-b042-49e73c11a3f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "papers_combined = pd.read_csv('data/papers_combined.csv')\n",
    "conference_proceedings = pd.read_csv('data/conferenceProceedings.csv')\n",
    "volumes = pd.read_csv('data/volumes.csv')\n",
    "\n",
    "con_pro_ids = conference_proceedings['conProIds'].tolist()\n",
    "volume_ids = volumes['volumeID'].tolist()\n",
    "\n",
    "combined_ids = con_pro_ids + volume_ids\n",
    "np.random.shuffle(combined_ids)\n",
    "\n",
    "papers_combined['proceedingsVolumesIds'] = np.random.choice(combined_ids, size=len(papers_combined), replace=True)\n",
    "papers_combined.to_csv('data/updated_papers_combined.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ce3cf744-e9ff-4ae4-a013-047416b342a1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "done\n"
     ]
    }
   ],
   "source": [
    "papers_combined = pd.read_csv('data/papers_combined.csv')\n",
    "volumes = pd.read_csv('data/volumes.csv')\n",
    "conference_proceedings = pd.read_csv('data/conferenceProceedings.csv')\n",
    "papers_combined = papers_combined.merge(conference_proceedings[['conferenceID', 'conProIds']], \n",
    "                                        how='left', \n",
    "                                        left_on='confjorID', \n",
    "                                        right_on='conferenceID')\n",
    "\n",
    "papers_combined = papers_combined.merge(volumes[['volumeID', 'volumeName']], \n",
    "                                        how='left', \n",
    "                                        left_on='confjorID', \n",
    "                                        right_on='volumeID')\n",
    "\n",
    "papers_combined.drop(columns=['conferenceID', 'volumeName'], inplace=True)\n",
    "papers_combined.to_csv('data/updated_papers_combined.csv', index=False)\n",
    "\n",
    "print(\"done\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "2f90e674-d005-4c42-b48e-54acc3e88235",
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy.spatial import cKDTree"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "242c66c1-2bd1-44e2-a1a0-8dade72135c9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: scipy in /opt/anaconda3/lib/python3.8/site-packages (1.6.2)\n",
      "Collecting numpy<1.23.0,>=1.16.5\n",
      "  Downloading numpy-1.22.4-cp38-cp38-macosx_10_15_x86_64.whl (17.6 MB)\n",
      "\u001b[K     |████████████████████████████████| 17.6 MB 691 kB/s  eta 0:00:01\n",
      "\u001b[?25hInstalling collected packages: numpy\n",
      "  Attempting uninstall: numpy\n",
      "    Found existing installation: numpy 1.24.4\n",
      "    Uninstalling numpy-1.24.4:\n",
      "      Successfully uninstalled numpy-1.24.4\n",
      "Successfully installed numpy-1.22.4\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install scipy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "c95d5c95-5e67-49f7-b4b8-74b4d1b0e402",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "done.\n"
     ]
    }
   ],
   "source": [
    "papers_combined = pd.read_csv('data/papers_combined.csv')\n",
    "topics_proceedings_volumes = pd.read_csv('data/topicsProceedingsVolumes.csv')\n",
    "\n",
    "vpIDs = topics_proceedings_volumes['vpIDs']\n",
    "papers_indices = np.arange(len(papers_combined))\n",
    "topics_indices = np.arange(len(topics_proceedings_volumes))\n",
    "tree = cKDTree(topics_indices.reshape(-1, 1))\n",
    "distances, indices = tree.query(papers_indices.reshape(-1, 1), k=1)\n",
    "papers_combined['proceedingsVolumesIds'] = vpIDs.iloc[indices].values\n",
    "papers_combined.to_csv('data/updated_papers_combined.csv', index=False)\n",
    "\n",
    "print(\"done.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "10b1f3c4-4053-4afd-b1ee-d98484e930cc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "done.\n"
     ]
    }
   ],
   "source": [
    "papers_combined = pd.read_csv('data/papers_combined.csv')\n",
    "topics_proceedings_volumes = pd.read_csv('data/topicsProceedingsVolumes.csv')\n",
    "\n",
    "vpIDs = topics_proceedings_volumes['vpIDs']\n",
    "papers_indices = np.arange(len(papers_combined))\n",
    "topics_indices = np.arange(len(topics_proceedings_volumes))\n",
    "\n",
    "tree = cKDTree(topics_indices.reshape(-1, 1))\n",
    "distances, indices = tree.query(papers_indices.reshape(-1, 1), k=1)\n",
    "\n",
    "nearest_vpIDs = vpIDs.iloc[indices.flatten()].reset_index(drop=True)\n",
    "\n",
    "proceedings_volumes_ids = pd.Series(np.nan, index=papers_indices)\n",
    "proceedings_volumes_ids.iloc[:len(nearest_vpIDs)] = nearest_vpIDs.values\n",
    "\n",
    "proceedings_volumes_ids.interpolate(method='linear', limit_direction='forward', axis=0, inplace=True)\n",
    "proceedings_volumes_ids.fillna(method='bfill', inplace=True)\n",
    "proceedings_volumes_ids.fillna(method='ffill', inplace=True)\n",
    "\n",
    "papers_combined['proceedingsVolumesIds'] = proceedings_volumes_ids.values\n",
    "papers_combined.to_csv('data/updated_papers_combined.csv', index=False)\n",
    "\n",
    "print(\"done.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "379ed8f2-cfb4-402b-860f-72fa5b38a770",
   "metadata": {},
   "source": [
    "#### renamed modified file. updated_papers_combined.csv -> papers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9fbb836a-4c9d-4a0a-a324-1114c5687130",
   "metadata": {},
   "source": [
    "## Supervisor.csv modifications"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8c545c6-d801-4f6c-8634-fe52ea9f2f60",
   "metadata": {},
   "outputs": [],
   "source": [
    "papers_combined = pd.read_csv('path_to/papers_combined.csv')\n",
    "papers_combined['editorID'] = ['editor' + str(i) for i in range(len(papers_combined))]\n",
    "papers_combined.to_csv('data/editor.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "9a7eedfc-cc39-499d-88f3-b5beafb01911",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = 'data/editor.csv'\n",
    "df = pd.read_csv(file_path)\n",
    "\n",
    "df['editorID'] = ['editor' + str(i) for i in range(len(df))]\n",
    "\n",
    "updated_file_path = 'data/editor.csv'\n",
    "df.to_csv(updated_file_path, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "e115217c-42c4-422b-816c-253e32e2769d",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = 'data/supervisors.csv'\n",
    "df = pd.read_csv(file_path)\n",
    "\n",
    "df['supervisorID'] = df['authorID'].apply(lambda x: 's' + x[1:])\n",
    "\n",
    "updated_file_path = 'data/supervisors.csv'\n",
    "df.to_csv(updated_file_path, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "705215e1-fe1f-47a0-93f4-b57c1f743182",
   "metadata": {},
   "outputs": [],
   "source": [
    "supervisors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "a3c039e1-4f81-4d06-bfbd-2ace5d77c18e",
   "metadata": {},
   "outputs": [],
   "source": [
    "papers_path = 'data/papers.csv'\n",
    "supervisors_path = 'data/supervisors.csv'\n",
    "output_supervisors_path = 'data/supervisors.csv'\n",
    "\n",
    "papers = pd.read_csv(papers_path)\n",
    "supervisors = pd.read_csv(supervisors_path)\n",
    "\n",
    "confjorID = papers['confjorID']\n",
    "\n",
    "if len(confjorID) < len(supervisors):\n",
    "    extended_confjorID = pd.concat([confjorID] * (len(supervisors) // len(confjorID) + 1), ignore_index=True)[:len(supervisors)]\n",
    "else:\n",
    "    extended_confjorID = confjorID[:len(supervisors)]\n",
    "\n",
    "supervisors['confjorID'] = extended_confjorID\n",
    "supervisors.to_csv(output_supervisors_path, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "0d151555-feab-421f-b5ea-e3d6b65d3920",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = 'data/supervisors.csv'\n",
    "df = pd.read_csv(file_path)\n",
    "\n",
    "editor_counter = 0\n",
    "chair_counter = 0\n",
    "\n",
    "def generate_supervisor_id(confjor_id):\n",
    "    global editor_counter, chair_counter\n",
    "    if confjor_id.startswith('j'):\n",
    "        supervisor_id = f'editor{editor_counter}'\n",
    "        editor_counter += 1\n",
    "    elif confjor_id.startswith('c'):\n",
    "        supervisor_id = f'chair{chair_counter}'\n",
    "        chair_counter += 1\n",
    "    return supervisor_id\n",
    "\n",
    "df['supervisorsIDs'] = df['confjorID'].apply(generate_supervisor_id)\n",
    "\n",
    "output_file_path = 'data/supervisors.csv'\n",
    "df.to_csv(output_file_path, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "62424ea9-bec4-44b3-85d1-9a68ce6c4f91",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = 'data/supervisors.csv'\n",
    "df = pd.read_csv(file_path)\n",
    "df = df.drop(columns=[\"conferenceID\", \"chairID\"])\n",
    "output_file_path = 'data/supervisors.csv'\n",
    "df.to_csv(output_file_path, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32e358fe-62c0-42cc-b7e0-8991e66ebf0a",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}