meta.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "meta.ipynb",
      "provenance": [],
      "collapsed_sections": [
        "2QQ1ZunhEVrw"
      ],
      "toc_visible": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lS8D2LTXgd9r"
      },
      "source": [
        "# Overview\n",
        "\n",
        "This notebook takes in the raw datasets and add in the meta data for each article (author name, author position, and tags) by utilizing DP article APIs and staff positions CSV files obtained from the staff."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2QQ1ZunhEVrw"
      },
      "source": [
        "## Add Tags and Authors names\n",
        "\n",
        "Obtain tags and author names of an article by calling DP article APIs"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cDnBQzbO76BA"
      },
      "source": [
        "# import packages\n",
        "\n",
        "import numpy as np\n",
        "import pandas as pd\n",
        "import requests as r\n",
        "import math\n",
        "import json"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VTuvTuR3LGKZ"
      },
      "source": [
        "# load train data\n",
        "train_df = pd.read_csv('train.csv', index_col=False)\n",
        "train_df = train_df.drop(columns=['Unnamed: 0'])\n",
        "\n",
        "# load test data\n",
        "test_df = pd.read_csv('test.csv', index_col=False)\n",
        "test_df = test_df.drop(columns=['Unnamed: 0'])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HeKtdLHtLjYK"
      },
      "source": [
        "# function for getting the tags of an article\n",
        "def getTags(titleURL):\n",
        "  try:\n",
        "    article = r.get(f'https://www.thedp.com{titleURL}.json').json()['article']\n",
        "    return json.dumps([tag['slug'] for tag in article['tags']])\n",
        "  except:\n",
        "    return json.dumps([])\n",
        "\n",
        "# function for getting the author names of an article\n",
        "def getAuthors(titleURL):\n",
        "  try:\n",
        "    article = r.get(f'https://www.thedp.com{titleURL}.json').json()['article']\n",
        "    return json.dumps([author['name'] for author in article['authors']])\n",
        "  except:\n",
        "    return json.dumps([])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DsslDiFIL9Lg"
      },
      "source": [
        "test_df['authors'] = test_df['article'].apply(getAuthors)\n",
        "test_df['tags'] = test_df['article'].apply(getTags)\n",
        "test_df"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uxyWGa6wbvgt"
      },
      "source": [
        "train_df['authors'] = train_df['article'].apply(getAuthors)\n",
        "train_df['tags'] = train_df['article'].apply(getTags)\n",
        "train_df"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tiefIZc1ansG"
      },
      "source": [
        "# save author names and tags to csv files\n",
        "test_df.to_csv('test_name_tag.csv', index=False)\n",
        "train_df.to_csv('train_name_tag.csv', index=False)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "chlNBiHlERUI"
      },
      "source": [
        "## Processing Staff Positions CSVs\n",
        "\n",
        "This section cleans up the CSVs obtained from DP staff and produces a dictionary that maps author names to their positions."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qlsTzG-UAoRS"
      },
      "source": [
        "# this function combines positions in different departments into a single array\n",
        "\n",
        "def combinePositions(row):\n",
        "  exp = row['DP Experience']\n",
        "  biz_pos = row['DP Business Department']\n",
        "  ed_pos = row['Editorial Department']\n",
        "  st_pos = row['Street Department']\n",
        "  utb_pos = row['UTB Department']\n",
        "\n",
        "  pos_list = []\n",
        "\n",
        "  if exp:\n",
        "    exp = exp.replace('\\n', ' ')\n",
        "    pos_list.append(exp[exp.find(':') + 2:])\n",
        "  \n",
        "  if biz_pos:\n",
        "    biz_pos = biz_pos.replace('\\n', ', ')\n",
        "    pos_list.append(biz_pos)\n",
        "\n",
        "  if ed_pos:\n",
        "    ed_pos = ed_pos.replace('\\n', ', ')\n",
        "    pos_list.append(f'DP {ed_pos}')\n",
        "  \n",
        "  if st_pos:\n",
        "    st_pos = st_pos.replace('\\n', ', ')\n",
        "    pos_list.append(f'Street {st_pos}')\n",
        "  \n",
        "  if utb_pos:\n",
        "    utb_pos = utb_pos.replace('\\n', ', ')\n",
        "    pos_list.append(f'UTB {utb_pos}')\n",
        "  \n",
        "  return pos_list"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "U8voJWdAMwhF"
      },
      "source": [
        "# load the first CSV (all staff positions after 2015)\n",
        "positions_df = pd.read_csv('drive/MyDrive/CIS520 Project/data set/DP Staff Positions/staff-1.csv', index_col=False, na_filter=False)\n",
        "\n",
        "# combine first name and last time\n",
        "positions_df['name'] = positions_df['Name'] + ' ' + positions_df['Last']\n",
        "# lowercase the name\n",
        "positions_df['name'] = positions_df['name'].apply(lambda x : x.lower())\n",
        "# drop the columns of first name and last name\n",
        "positions_df = positions_df.drop(columns=['Name', 'Last'])\n",
        "# drop duplicates and keep the most recent position\n",
        "positions_df = positions_df.drop_duplicates(subset=['name'], keep='last')\n",
        "# combine positions using the function defined above\n",
        "positions_df['positions'] = positions_df.apply(combinePositions, axis=1)\n",
        "positions_df = positions_df.drop(columns=['DP Experience', 'DP Business Department', 'Editorial Department', 'Street Department', 'UTB Department'])\n",
        "\n",
        "# set name to be the index key\n",
        "positions_df = positions_df.set_index('name')\n",
        "\n",
        "# convert the dataframe to a dictionary to allow easy retrieval of positions\n",
        "# by name\n",
        "pos_dict = positions_df.to_dict('index')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fIl6epwFtent"
      },
      "source": [
        "# load the second CSV (all staff positions from 2010-2015)\n",
        "staff2_df = pd.read_csv('drive/MyDrive/CIS520 Project/data set/DP Staff Positions/staff-2.csv', index_col=False, na_filter=False)\n",
        "\n",
        "# combine first name and last name\n",
        "staff2_df['name'] = staff2_df['First Name'] + ' ' + staff2_df['Last Name']\n",
        "# lowercase the anem\n",
        "staff2_df['name'] = staff2_df['name'].apply(lambda x : x.lower())\n",
        "staff2_df = staff2_df.drop(columns=['First Name', 'Last Name', 'DP Experience', 'Notes'])\n",
        "# drop duplicates and keep the most recent position\n",
        "staff2_df = staff2_df.drop_duplicates(subset=['name'], keep='last')\n",
        "\n",
        "# set name to the index key and output a dictionary\n",
        "staff2_df = staff2_df.set_index('name')\n",
        "pos_dict_2 = staff2_df.to_dict('index')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "c72QucT4Htzx"
      },
      "source": [
        "# a dictionary that maps author name as appeared in the article\n",
        "# to the author name appeared in the CSV files\n",
        "ACTUAL_NAMES = {\n",
        "    \"tori sousa\": \"victoria sousa\",\n",
        "    \"isaac lee\": \"enwook lee\",\n",
        "    \"sanjary dureseti\": \"sanjay dureseti\",\n",
        "    \"pat zancolli\": \"patrick zancolli\",\n",
        "    \"nikkita collins\": \"nikki collins\",\n",
        "    \"abigail baggini\": \"abby baggini\",\n",
        "    \"jenn wright\": \"Jennifer Wright\",\n",
        "    \"cathy han\": \"Kyoung Won (Cathy) Han\",\n",
        "    \"alfredo praticò\": \"Alfredo Pratico'\",\n",
        "    \"juan sebastián pinto\": \"juan Sebastián Pinto-Díaz\",\n",
        "    \"tom nowlan\": \"thomas nowlan\",\n",
        "    \"will snow\": \"william snow\",\n",
        "    \"greg robinov\": \"gregory robinov\",\n",
        "    \"will agathis\": \"william agathis\",\n",
        "    \"sam altland\": \"samuel altland\",\n",
        "    \"ben claar\": \"benjamin claar\",\n",
        "    \"joe li\": \"zhiyao (joe) li\",\n",
        "    \"maddy strohm\": \"madelyn strohm\",\n",
        "    \"alexandra getsos\": \"alex getsos\",\n",
        "    \"oscar a. rudenstam\": \"Oscar rudenstam\",\n",
        "    \"christian gilberti\": \"christian read gilberti\",\n",
        "    \"eunice lim\": \"Chan Mi (Eunice) Lim\",\n",
        "    \"mike wisniewski\": \"michael wisniewski\",\n",
        "    \"cherry zhi\": \"Qiu Yi (Cherry) Zhi\",\n",
        "    \"luis ferre sadurni\": \"Luis Ferré Sadurní\",\n",
        "    \"matt fine\": \"matthew fine\",\n",
        "    \"jill moely\": \"Jillian moely\",\n",
        "    \"alessandro van den brink\": \"Alexander van den brink\",\n",
        "    \"oj singh\": \"Ojasvinee singh\",\n",
        "    \"ali s mohammad\": \"ali mohammad\",\n",
        "    \"noa ortiz\": \"noa Ortiz-Langleben\",\n",
        "    \"amanda o'brien\": \"amanda O’Brien\",\n",
        "    \"zach jacobs\": \"zachary jacobs\",\n",
        "    \"christine olagun-samuel\": \"christine Olaogun\",\n",
        "    \"jy_lee\": \"Jun Youb lee\",\n",
        "    \"eason zhao\": \"Yixin (Eason) zhao\",\n",
        "    \"aidan mayer ahearn\": \"aidan ahearn\",\n",
        "    \"dia sotiropoulou\": \"Dionysia Sotiropoulou\",\n",
        "    \"andie pinga\": \"andrea pinga\",\n",
        "    \"chris schiller\": \"Christopher Schiller\",\n",
        "    \"cass dinh\": \"Cassandra dinh\",\n",
        "    \"michael a. keshmiri\": \"michael keshmiri\",\n",
        "    \"m. earl smith\": \"martin smith\",\n",
        "    \"albert chen-feng chou\": \"chen-feng chou\",\n",
        "    \"lucien wang\": \"Alexander Lucien wang\",\n",
        "    \"jessie washington\": \"jessica washington\",\n",
        "    \"lavi ben dor\": \"lavi ben-dor\",\n",
        "    \"dan eder\": \"daniel eder\",\n",
        "    \"ben facey\": \"benjamin facey\",\n",
        "    \"chris proano\": \"christopher proano\",\n",
        "    \"theodore l. caputi\": \"theodore caputi\",\n",
        "    \"matt mantica\": \"matthew mantica\",\n",
        "    \"sergio w. guadix\": \"sergio guadix\",\n",
        "    \"colleen o&#039;malley\": \"colleen O'Malley\",\n",
        "    \"evie artis\": \"Qiana (Evie) artis\",\n",
        "    \"ola osinaike\": \"Olatunbosun Osinaike\",\n",
        "    \"becky demarre\": \"Rebecca demarre\",\n",
        "    \"dan hayes\": \"daniel hayes\",\n",
        "    \"nikki hardison\": \"Nikki (Christine) hardison\",\n",
        "    \"ari goldfine\": \"ariel goldfine\",\n",
        "    \"freda zhao\": \"Freda (Fang Bin) zhao\",\n",
        "    \"yiwen chan\": \"Yiwen (Rachee) chan\",\n",
        "    \"sanjay menghani\": \"sanjay meghani\"\n",
        "}\n",
        "\n",
        "missing_names = []\n",
        "\n",
        "# recursive function that retrieves the positions of the authors\n",
        "# of an article\n",
        "def getAuthorsPos(names):\n",
        "  poss_ls = []\n",
        "\n",
        "  for name in names:\n",
        "    if ' and ' in name:\n",
        "      poss_ls += getAuthorsPos(name.split(' and '))\n",
        "      continue\n",
        "\n",
        "    name = name.strip().lower()\n",
        "    official_name = name\n",
        "    actual_name = ACTUAL_NAMES.get(name, None)\n",
        "    \n",
        "    if actual_name:\n",
        "      official_name = actual_name.lower()\n",
        "    \n",
        "    poss = pos_dict.get(official_name, None)\n",
        "    poss_2 = pos_dict_2.get(official_name, None)\n",
        "\n",
        "    if poss:\n",
        "      poss_ls += poss['positions']\n",
        "      continue\n",
        "    if poss_2:\n",
        "      poss_ls += poss_2['Specific Department']\n",
        "      continue\n",
        "    \n",
        "    missing_names.append(name)\n",
        "  \n",
        "  return poss_ls"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6z91bAhQydcF"
      },
      "source": [
        "## Add Author Positions\n",
        "\n",
        "With the function `getAuthorsPos` ready, we can now proceed to add the author positions to `test_df` and `train_df` by applying this function on the `author` column"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FlpxHJ72Q8OW"
      },
      "source": [
        "# load train data\n",
        "train_df = pd.read_csv('drive/MyDrive/CIS520 Project/data set/train_name_tag.csv', index_col=False)\n",
        "\n",
        "# load test data\n",
        "test_df = pd.read_csv('drive/MyDrive/CIS520 Project/data set/test_name_tag.csv', index_col=False)\n",
        "\n",
        "# parse data by converting author and tags columns from JSON string to lists\n",
        "def parse_json(df):\n",
        "  df['authors'] = df['authors'].apply(lambda x : json.loads(x))\n",
        "  df['tags'] = df['tags'].apply(lambda x : json.loads(x))\n",
        "  return df\n",
        "\n",
        "train_df = parse_json(train_df)\n",
        "test_df = parse_json(test_df)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3SsoBN6ThtzZ",
        "outputId": "b8078ce3-1801-440a-ed4f-5b3e0fe7e579"
      },
      "source": [
        "# count the number of articles that do not have metadata\n",
        "train_count, _ = train_df[train_df['authors'].apply(lambda x : len(x) == 0)].shape\n",
        "print(f'{train_count}/{train_df.shape[0]} articles in the train data do not have metadata')\n",
        "\n",
        "test_count, _ = test_df[test_df['authors'].apply(lambda x : len(x) == 0)].shape\n",
        "print(f'{test_count}/{test_df.shape[0]} articles in the train data do not have metadata')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "567/16772 articles in the train data do not have metadata\n",
            "138/4194 articles in the train data do not have metadata\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dRu9zHH7zKAl"
      },
      "source": [
        "# apply getAuthorsPos function\n",
        "\n",
        "test_df['author_positions'] = test_df['authors'].apply(getAuthorsPos)\n",
        "train_df['author_positions'] = train_df['authors'].apply(getAuthorsPos)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rDvxb4h59Snn"
      },
      "source": [
        "# getting missing names\n",
        "from collections import Counter\n",
        "result = Counter(missing_names)\n",
        "result.most_common()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4yUJXI1Au01g",
        "outputId": "262646e0-0e42-4192-9261-c5e09d774113"
      },
      "source": [
        "train_df[train_df['author_positions'].apply(lambda x : len(x) == 0)].shape"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(1556, 22)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 107
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "j6Al-KmkvXzx",
        "outputId": "4a2b6949-a24b-4801-921f-8de4305f19f2"
      },
      "source": [
        "test_df[test_df['author_positions'].apply(lambda x : len(x) == 0)].shape"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(412, 22)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 108
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gHk1HhHyFOol"
      },
      "source": [
        "# conver to JSON\n",
        "\n",
        "def to_json(df):\n",
        "  df['authors'] = df['authors'].apply(lambda x : json.dumps(x))\n",
        "  df['tags'] = df['tags'].apply(lambda x : json.dumps(x))\n",
        "  df['author_positions'] = df['author_positions'].apply(lambda x : json.dumps(x))\n",
        "  return df\n",
        "\n",
        "train_df = to_json(train_df)\n",
        "test_df = to_json(test_df)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oPJ3Vl274mAv"
      },
      "source": [
        "# store the data to CSV files\n",
        "\n",
        "test_df.to_csv('test_name_pos_tag.csv', index=False)\n",
        "train_df.to_csv('drive/MyDrive/CIS520 Project/data set/train_name_pos_tag.csv', index=False)"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}