01-wishes_data_gathering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ad5811b8-bcbe-4256-88d8-d90c3f5784cd",
   "metadata": {},
   "source": [
    "# Step 1: gather proposals data\n",
    "* Proposals & categories, by year\n",
    "* User names for proposers, dicussants and voters\n",
    "* Reject reasons, if any"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12682fac-72ac-4708-a149-c13f8a850b38",
   "metadata": {},
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5e743c5c-de1c-4f85-a341-86a371ba80ef",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "You are using Wmfdata v2.0.0, but v2.0.1 is available.\n",
      "\n",
      "To update, run `pip install --upgrade git+https://github.com/wikimedia/wmfdata-python.git@release`.\n",
      "\n",
      "To see the changes, refer to https://github.com/wikimedia/wmfdata-python/blob/release/CHANGELOG.md.\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import wmfdata as wmf\n",
    "\n",
    "pd.options.display.max_columns = None\n",
    "\n",
    "import re\n",
    "import requests\n",
    "import time\n",
    "\n",
    "import os\n",
    "import json\n",
    "import warnings\n",
    "import importlib\n",
    "\n",
    "\n",
    "os.environ.pop('HTTP_PROXY', None)\n",
    "os.environ.pop('HTTPS_PROXY', None)\n",
    "os.environ.pop('http_proxy', None)\n",
    "os.environ.pop('https_proxy', None)\n",
    "\n",
    "import data_gathering_functions as dg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b3a05bb0-ce24-429b-adad-7283650c276c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def rdg(): importlib.reload(dg)\n",
    "def ig_warn(): warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8100e14c-f512-440a-954b-0f99fb5e55be",
   "metadata": {},
   "source": [
    "# 2015 & 2016\n",
    "2015 & 2016 iterations of the survey had similar survey formats:\n",
    "- proposals were listed as sections (level 2) of a page\n",
    "- sections were anchor tagged\n",
    "- discussion and voting as subsections (level 3) of the propsoals section\n",
    "- phab tickets in results table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d0c1c3c6-2f1e-4ebf-9869-4563dff361d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "ig_warn()\n",
    "\n",
    "categories_201516_query = \"\"\"\n",
    "WITH base AS (\n",
    "    SELECT\n",
    "        CASE \n",
    "            WHEN {YEAR} = 2015 THEN REPLACE(page_title, '{SURVEY_TITLE}/', '')\n",
    "            WHEN {YEAR} = 2016 THEN REPLACE(page_title, '{SURVEY_TITLE}/Categories/', '')\n",
    "        END AS category,\n",
    "        page_title AS category_title\n",
    "    FROM\n",
    "        categorylinks cl\n",
    "        JOIN page p\n",
    "        ON cl.cl_from = p.page_id\n",
    "    WHERE\n",
    "        cl_to = '{CATEGORY_TITLE}' \n",
    "        AND (\n",
    "            ({YEAR} = 2015 AND page_title LIKE '{SURVEY_TITLE}/%' AND NOT page_title LIKE '{SURVEY_TITLE}/%/%')\n",
    "            OR\n",
    "            ({YEAR} = 2016 AND page_title LIKE '{SURVEY_TITLE}/Categories/%')\n",
    "        )\n",
    ")\n",
    "\n",
    "SELECT category, category_title\n",
    "FROM base\n",
    "WHERE \n",
    "    NOT (\n",
    "        category LIKE '%report%'\n",
    "        OR category LIKE '%Result%'\n",
    "        OR category LIKE '%Archive%'\n",
    "        OR category LIKE '%Translations%'\n",
    "    )\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "6cf0c726-f9b7-4a9f-8b02-52173fbd9b87",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2015 data was extracted in 0.65 minutes\n",
      "2016 data was extracted in 1.9 minutes\n",
      "CPU times: user 22.1 s, sys: 761 ms, total: 22.9 s\n",
      "Wall time: 2min 33s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "rdg()\n",
    "\n",
    "data = {}\n",
    "error_log = {}\n",
    "\n",
    "for year in [2015, 2016]:\n",
    "    start_time = time.time()\n",
    "    \n",
    "    # get categories for a year\n",
    "    categories = wmf.mariadb.run(categories_201516_query\n",
    "                                 .format(YEAR=year, \n",
    "                                         CATEGORY_TITLE=dg.get_title(year, 'category_subpages'), \n",
    "                                         SURVEY_TITLE=dg.get_title(year, 'main_page')), \n",
    "                                 'metawiki')\n",
    "    \n",
    "    categories.sort_values('category', inplace=True, ignore_index=True)\n",
    "    \n",
    "    data[year] = {}\n",
    "    error_log[year] = {}\n",
    "\n",
    "    for i in categories.index:\n",
    "        \n",
    "        # get data related to proposals under each category \n",
    "        category_name = categories.loc[i, 'category']\n",
    "        category_title = categories.loc[i, 'category_title']\n",
    "        wishes = dg.process_wishes_201516(category_title, year=year)\n",
    "\n",
    "        data[year][category_name] = {}\n",
    "\n",
    "        for wish_index, wish in wishes.items():\n",
    "            try:\n",
    "                wish_text = dg.get_wikitext(category_title, section_index=wish_index)['parse']['wikitext']['*']\n",
    "                \n",
    "                if year == 2015:\n",
    "                    proposal, discussion = dg.split_proposal_2015(wish_text)\n",
    "                    proposer = dg.extract_usernames_from_text(proposal)\n",
    "                    discussion_participants = dg.extract_usernames_from_text(discussion)\n",
    "                elif year == 2016:\n",
    "                    proposal = dg.split_sections_l2(wish_text)[0]\n",
    "                    proposer = dg.extract_proposer_username(proposal)\n",
    "                    discussion_participants = dg.extract_usernames_from_parser(dg.parse_iwlinks(category_title, section_index=wish['discussion_index']))\n",
    "\n",
    "                phab_tickets = dg.extract_phab_tickets(proposal)\n",
    "                voters = dg.extract_usernames_from_parser(dg.parse_iwlinks(category_title, section_index=wish['votes_index']))\n",
    "\n",
    "                data[year][category_name][wish['title']] = {\n",
    "                    'proposer': proposer,\n",
    "                    'phab_tickets': phab_tickets,\n",
    "                    'discussion_participants': discussion_participants,\n",
    "                    'voters': voters\n",
    "                }\n",
    "                \n",
    "            except Exception as e:\n",
    "                error_log[year][wish['title']] = {\n",
    "                    'category': category_name,\n",
    "                    'error': repr(e)\n",
    "                }\n",
    "    \n",
    "    end_time = time.time()\n",
    "    elapsed_time = round((end_time - start_time)/60, 2)\n",
    "    print(f\"{year} data was extracted in {elapsed_time} minutes\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "918ae327-66e8-401e-9b29-88df6a2e114f",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"data/01-cws_proposals_data.json\", \"w\") as outfile:\n",
    "    json.dump(data, outfile)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30cb73c0-63d9-47b5-b665-a7093785f565",
   "metadata": {},
   "source": [
    "# 2017 & beyond"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "cb863fbc-ee9c-46d0-ae33-202a454e3ac4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017 data was extracted in 2.66 minutes\n",
      "2019 data was extracted in 2.69 minutes\n",
      "2020 data was extracted in 0.91 minutes\n",
      "2021 data was extracted in 3.49 minutes\n",
      "2022 data was extracted in 3.92 minutes\n",
      "2023 data was extracted in 2.28 minutes\n",
      "CPU times: user 2min 51s, sys: 5.11 s, total: 2min 56s\n",
      "Wall time: 15min 57s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "ig_warn()\n",
    "rdg()\n",
    "\n",
    "for year in range(2017, 2023+1):\n",
    "    \n",
    "    start_time = time.time()\n",
    "    \n",
    "    if year == 2018:\n",
    "        continue\n",
    "    \n",
    "    data[year] = {}\n",
    "    error_log[year] = {}\n",
    "    \n",
    "    categories = dg.get_categories_std(dg.get_title(year, 'category_subpages'), dg.get_title(year, 'main_page'), year)\n",
    "    \n",
    "    for i in categories.index:\n",
    "        category = categories.loc[i, 'category']\n",
    "        category_title = categories.loc[i, 'category_title']\n",
    "        proposals_list = dg.extract_proposals(dg.get_wikitext(category_title)['parse']['wikitext']['*'])\n",
    "        \n",
    "        if category != 'Archive':\n",
    "            data[year][category] = {}\n",
    "\n",
    "            for proposal in proposals_list:\n",
    "                proposal_name = proposal.replace(category_title.replace('_', ' '), '').strip('/')\n",
    "                \n",
    "                try:\n",
    "                    if year >= 2022:\n",
    "                        if (year == 2023) & (category == 'Larger_suggestions'):\n",
    "                            page_wikitext = dg.get_wikitext(proposal)['parse']['wikitext']['*']\n",
    "                        else:\n",
    "                            page_wikitext = dg.get_wikitext(f'{proposal}/Proposal')['parse']['wikitext']['*']\n",
    "                    else:\n",
    "                        page_wikitext = dg.get_wikitext(proposal)['parse']['wikitext']['*']\n",
    "                    \n",
    "                    page_sections = dg.parse_page_sections(proposal)['parse']['sections']\n",
    "\n",
    "                    \n",
    "                    data[year][category][proposal_name] = {\n",
    "                        'phab_tickets': dg.extract_phab_tickets(page_wikitext),\n",
    "                        'discussion_participants': dg.extract_usernames_from_parser(dg.parse_iwlinks(proposal, section_index=dg.get_section_index('discussion', page_sections))),\n",
    "                        'voters': dg.extract_usernames_from_parser(dg.parse_iwlinks(proposal, section_index=dg.get_section_index('voting', page_sections)))\n",
    "                    }\n",
    "                    \n",
    "                    if year >= 2022:\n",
    "                        if (year == 2023) & (category == 'Larger_suggestions'):\n",
    "                            data[year][category][proposal_name]['proposer'] = dg.extract_proposer_username(page_wikitext)\n",
    "                        else:\n",
    "                            data[year][category][proposal_name]['proposer'] = dg.extract_usernames_from_parser(dg.parse_iwlinks(f'{proposal}/Proposal'))[0]\n",
    "                    else:\n",
    "                        data[year][category][proposal_name]['proposer'] = dg.extract_proposer_username(page_wikitext)\n",
    "                        \n",
    "                    \n",
    "                except Exception as e:\n",
    "                    error_log[year][proposal_name] = {\n",
    "                        'category': category,\n",
    "                        'error': e\n",
    "                    }\n",
    "        \n",
    "        elif category == 'Archive':\n",
    "            for proposal in proposals_list:\n",
    "                try:\n",
    "                    if year == 2017:\n",
    "                        proposal = dg.get_redirect_target(proposal)\n",
    "                        \n",
    "                    proposal_initial_title = dg.get_ar_category(proposal, year)\n",
    "\n",
    "                    initial_category = proposal_initial_title.split('/')[1]\n",
    "                    proposal_name = proposal_initial_title.split('/')[2]\n",
    "\n",
    "                    page_wikitext = dg.get_wikitext(proposal)['parse']['wikitext']['*']\n",
    "                        \n",
    "                    page_sections = dg.parse_page_sections(proposal)['parse']['sections']\n",
    "                    \n",
    "                    try:\n",
    "                        data[year][initial_category]\n",
    "                    except:\n",
    "                        data[year][initial_category] = {}\n",
    "                \n",
    "                    data[year][initial_category][proposal_name] = {\n",
    "                        'proposer': dg.extract_proposer_username(page_wikitext),\n",
    "                        'phab_tickets': dg.extract_phab_tickets(page_wikitext),\n",
    "                        'discussion_participants': dg.extract_usernames_from_parser(dg.parse_iwlinks(proposal, section_index=dg.get_section_index('discussion', page_sections))),\n",
    "                    }\n",
    "\n",
    "                    try:\n",
    "                        data[year][initial_category][proposal_name]['reject_reason'] = dg.extract_reject_reason(page_wikitext)\n",
    "                    except Exception as e:\n",
    "                        data[year][initial_category][proposal_name]['reject_reason'] = 'archived_no_reason'\n",
    "                        \n",
    "                except Exception as e:\n",
    "                    try:\n",
    "                        error_log[year][proposal.split('/')[2]] = {\n",
    "                            'category': category,\n",
    "                            'error': repr(e)\n",
    "                        }\n",
    "                    except:\n",
    "                        error_log[year][proposal] = {\n",
    "                            'category': category,\n",
    "                            'error': 'error_logging_failed'\n",
    "                        }\n",
    "                    \n",
    "    end_time = time.time()\n",
    "    elapsed_time = round((end_time - start_time)/60, 2)\n",
    "    print(f\"{year} data was extracted in {elapsed_time} minutes\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d77316c-2e9f-4800-b10c-4251f173ed18",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"data/01-cws_proposals_data.json\", \"w\") as outfile:\n",
    "    json.dump(data, outfile)\n",
    "    \n",
    "error_log = dg.convert_errors_to_strings(error_log)\n",
    "with open(\"data/02-cws_proposals_error_log.json\", \"w\") as outfile:\n",
    "    json.dump(error_log, outfile)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}