scripts/02_everypol_download_csvs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b2b58f26",
   "metadata": {},
   "source": [
    "### Download and Merge EveryPol Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6485fba2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import requests\n",
    "from io import StringIO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c84822ef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>legislature</th>\n",
       "      <th>term</th>\n",
       "      <th>leg_start_year</th>\n",
       "      <th>row_count</th>\n",
       "      <th>n_unique_emails</th>\n",
       "      <th>person_count_legistype</th>\n",
       "      <th>ltype</th>\n",
       "      <th>url</th>\n",
       "      <th>cc</th>\n",
       "      <th>leg_start_date</th>\n",
       "      <th>lastmod</th>\n",
       "      <th>cc3</th>\n",
       "      <th>pop2024</th>\n",
       "      <th>lastmod_year</th>\n",
       "      <th>row_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>India</td>\n",
       "      <td>Lok Sabha</td>\n",
       "      <td>16th Lok Sabha</td>\n",
       "      <td>2014</td>\n",
       "      <td>541</td>\n",
       "      <td>511</td>\n",
       "      <td>541</td>\n",
       "      <td>unicameral legislature</td>\n",
       "      <td>https://cdn.rawgit.com/everypolitician/everypo...</td>\n",
       "      <td>IN</td>\n",
       "      <td>2014-05-26</td>\n",
       "      <td>1557812040</td>\n",
       "      <td>IND</td>\n",
       "      <td>1.441720e+09</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>China</td>\n",
       "      <td>National People’s Congress</td>\n",
       "      <td>12th National People’s Congress</td>\n",
       "      <td>2013</td>\n",
       "      <td>2987</td>\n",
       "      <td>0</td>\n",
       "      <td>2956</td>\n",
       "      <td>unicameral legislature</td>\n",
       "      <td>https://cdn.rawgit.com/everypolitician/everypo...</td>\n",
       "      <td>CN</td>\n",
       "      <td>2013-03-05</td>\n",
       "      <td>1554305411</td>\n",
       "      <td>CHN</td>\n",
       "      <td>1.410132e+09</td>\n",
       "      <td>2019</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>United-States-of-America</td>\n",
       "      <td>House of Representatives</td>\n",
       "      <td>113th Congress</td>\n",
       "      <td>2013</td>\n",
       "      <td>449</td>\n",
       "      <td>0</td>\n",
       "      <td>1739</td>\n",
       "      <td>lower house</td>\n",
       "      <td>https://cdn.rawgit.com/everypolitician/everypo...</td>\n",
       "      <td>US</td>\n",
       "      <td>2013-01-06</td>\n",
       "      <td>1557715874</td>\n",
       "      <td>USA</td>\n",
       "      <td>3.366107e+08</td>\n",
       "      <td>2019</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>United-States-of-America</td>\n",
       "      <td>House of Representatives</td>\n",
       "      <td>114th Congress</td>\n",
       "      <td>2015</td>\n",
       "      <td>447</td>\n",
       "      <td>0</td>\n",
       "      <td>1739</td>\n",
       "      <td>lower house</td>\n",
       "      <td>https://cdn.rawgit.com/everypolitician/everypo...</td>\n",
       "      <td>US</td>\n",
       "      <td>2015-01-06</td>\n",
       "      <td>1557715874</td>\n",
       "      <td>USA</td>\n",
       "      <td>3.366107e+08</td>\n",
       "      <td>2019</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>United-States-of-America</td>\n",
       "      <td>House of Representatives</td>\n",
       "      <td>108th Congress</td>\n",
       "      <td>2003</td>\n",
       "      <td>444</td>\n",
       "      <td>0</td>\n",
       "      <td>1739</td>\n",
       "      <td>lower house</td>\n",
       "      <td>https://cdn.rawgit.com/everypolitician/everypo...</td>\n",
       "      <td>US</td>\n",
       "      <td>2003-01-06</td>\n",
       "      <td>1557715874</td>\n",
       "      <td>USA</td>\n",
       "      <td>3.366107e+08</td>\n",
       "      <td>2019</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    country                 legislature  \\\n",
       "0                     India                   Lok Sabha   \n",
       "1                     China  National People’s Congress   \n",
       "2  United-States-of-America    House of Representatives   \n",
       "3  United-States-of-America    House of Representatives   \n",
       "4  United-States-of-America    House of Representatives   \n",
       "\n",
       "                              term  leg_start_year  row_count  \\\n",
       "0                   16th Lok Sabha            2014        541   \n",
       "1  12th National People’s Congress            2013       2987   \n",
       "2                   113th Congress            2013        449   \n",
       "3                   114th Congress            2015        447   \n",
       "4                   108th Congress            2003        444   \n",
       "\n",
       "   n_unique_emails  person_count_legistype                   ltype  \\\n",
       "0              511                     541  unicameral legislature   \n",
       "1                0                    2956  unicameral legislature   \n",
       "2                0                    1739             lower house   \n",
       "3                0                    1739             lower house   \n",
       "4                0                    1739             lower house   \n",
       "\n",
       "                                                 url  cc leg_start_date  \\\n",
       "0  https://cdn.rawgit.com/everypolitician/everypo...  IN     2014-05-26   \n",
       "1  https://cdn.rawgit.com/everypolitician/everypo...  CN     2013-03-05   \n",
       "2  https://cdn.rawgit.com/everypolitician/everypo...  US     2013-01-06   \n",
       "3  https://cdn.rawgit.com/everypolitician/everypo...  US     2015-01-06   \n",
       "4  https://cdn.rawgit.com/everypolitician/everypo...  US     2003-01-06   \n",
       "\n",
       "      lastmod  cc3       pop2024  lastmod_year  row_id  \n",
       "0  1557812040  IND  1.441720e+09          2019       1  \n",
       "1  1554305411  CHN  1.410132e+09          2019       2  \n",
       "2  1557715874  USA  3.366107e+08          2019       3  \n",
       "3  1557715874  USA  3.366107e+08          2019       4  \n",
       "4  1557715874  USA  3.366107e+08          2019       5  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv(\"../data/everypol-walkthrough.csv\")\n",
    "data['row_id'] = data.index + 1\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "843d9f32",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(757, 16)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(116, 16)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's filter to CSVs where we have more than 30\n",
    "print(data.shape)\n",
    "sm_data = data[data[\"n_unique_emails\"] > 30]\n",
    "sm_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9bc5db3a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    116.000000\n",
       "mean     116.948276\n",
       "std      108.956142\n",
       "min       31.000000\n",
       "25%       51.750000\n",
       "50%       73.000000\n",
       "75%      143.250000\n",
       "max      646.000000\n",
       "Name: n_unique_emails, dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sm_data[\"n_unique_emails\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "53e02ba1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Fetching data for India - Lok Sabha - 2014\n",
      "Saved ../data/legislature_data/India_Lok Sabha_2014_1.csv\n",
      "Fetching data for Nigeria - Senate - 2015\n",
      "Saved ../data/legislature_data/Nigeria_Senate_2015_45.csv\n",
      "Fetching data for Nigeria - House of Representatives - 2015\n",
      "Saved ../data/legislature_data/Nigeria_House of Representatives_2015_46.csv\n",
      "Fetching data for Iran - Majles - 2012\n",
      "Saved ../data/legislature_data/Iran_Majles_2012_62.csv\n",
      "Fetching data for Iran - Majles - 2016\n",
      "Saved ../data/legislature_data/Iran_Majles_2016_63.csv\n",
      "Fetching data for Tanzania - National Assembly - 2015\n",
      "Saved ../data/legislature_data/Tanzania_National Assembly_2015_110.csv\n",
      "Fetching data for Tanzania - National Assembly - 2005\n",
      "Saved ../data/legislature_data/Tanzania_National Assembly_2005_111.csv\n",
      "Fetching data for Tanzania - National Assembly - 2010\n",
      "Saved ../data/legislature_data/Tanzania_National Assembly_2010_113.csv\n",
      "Fetching data for UK - House of Commons - 2015\n",
      "Saved ../data/legislature_data/UK_House of Commons_2015_114.csv\n",
      "Fetching data for UK - House of Commons - 2010\n",
      "Saved ../data/legislature_data/UK_House of Commons_2010_115.csv\n",
      "Fetching data for UK - House of Commons - 1997\n",
      "Saved ../data/legislature_data/UK_House of Commons_1997_116.csv\n",
      "Fetching data for UK - House of Commons - 2001\n",
      "Saved ../data/legislature_data/UK_House of Commons_2001_117.csv\n",
      "Fetching data for UK - House of Commons - 2017\n",
      "Saved ../data/legislature_data/UK_House of Commons_2017_118.csv\n",
      "Fetching data for UK - House of Commons - 2005\n",
      "Saved ../data/legislature_data/UK_House of Commons_2005_119.csv\n",
      "Fetching data for South-Africa - National Assembly - 2014\n",
      "Saved ../data/legislature_data/South-Africa_National Assembly_2014_124.csv\n",
      "Fetching data for Italy - Senate - 2013\n",
      "Saved ../data/legislature_data/Italy_Senate_2013_125.csv\n",
      "Fetching data for Italy - Senate - 2018\n",
      "Saved ../data/legislature_data/Italy_Senate_2018_127.csv\n",
      "Fetching data for Kenya - National Assembly - 2013\n",
      "Saved ../data/legislature_data/Kenya_National Assembly_2013_128.csv\n",
      "Fetching data for Colombia - Cámara de Representantes - 2018\n",
      "Saved ../data/legislature_data/Colombia_Cámara de Representantes_2018_130.csv\n",
      "Fetching data for Colombia - Cámara de Representantes - 2014\n",
      "Saved ../data/legislature_data/Colombia_Cámara de Representantes_2014_131.csv\n",
      "Fetching data for South-Korea - National Assembly - 2016\n",
      "Saved ../data/legislature_data/South-Korea_National Assembly_2016_133.csv\n",
      "Fetching data for South-Korea - National Assembly - 2012\n",
      "Saved ../data/legislature_data/South-Korea_National Assembly_2012_134.csv\n",
      "Fetching data for Uganda - Parliament - 2016\n",
      "Saved ../data/legislature_data/Uganda_Parliament_2016_135.csv\n",
      "Fetching data for Uganda - Parliament - 2011\n",
      "Saved ../data/legislature_data/Uganda_Parliament_2011_136.csv\n",
      "Fetching data for Canada - House of Commons - 2011\n",
      "Saved ../data/legislature_data/Canada_House of Commons_2011_148.csv\n",
      "Fetching data for Canada - Senate - 2015\n",
      "Saved ../data/legislature_data/Canada_Senate_2015_149.csv\n",
      "Fetching data for Canada - House of Commons - 2015\n",
      "Saved ../data/legislature_data/Canada_House of Commons_2015_150.csv\n",
      "Fetching data for Nepal - Constituent Assembly - 2014\n",
      "Saved ../data/legislature_data/Nepal_Constituent Assembly_2014_184.csv\n",
      "Fetching data for Cameroon - Assemblée Nationale - 2013\n",
      "Saved ../data/legislature_data/Cameroon_Assemblée Nationale_2013_195.csv\n",
      "Fetching data for Australia - House of Representatives - 2016\n",
      "Saved ../data/legislature_data/Australia_House of Representatives_2016_201.csv\n",
      "Fetching data for Australia - House of Representatives - 2010\n",
      "Saved ../data/legislature_data/Australia_House of Representatives_2010_207.csv\n",
      "Fetching data for Australia - House of Representatives - 2013\n",
      "Saved ../data/legislature_data/Australia_House of Representatives_2013_208.csv\n",
      "Fetching data for Australia - House of Representatives - 2004\n",
      "Saved ../data/legislature_data/Australia_House of Representatives_2004_209.csv\n",
      "Fetching data for Australia - House of Representatives - 2007\n",
      "Saved ../data/legislature_data/Australia_House of Representatives_2007_214.csv\n",
      "Fetching data for Australia - Senate - 2016\n",
      "Saved ../data/legislature_data/Australia_Senate_2016_215.csv\n",
      "Fetching data for Australia - Senate - 2013\n",
      "Saved ../data/legislature_data/Australia_Senate_2013_218.csv\n",
      "Fetching data for Sri-Lanka - Parliament - 2015\n",
      "Saved ../data/legislature_data/Sri-Lanka_Parliament_2015_229.csv\n",
      "Fetching data for Zambia - National Assembly - 2016\n",
      "Saved ../data/legislature_data/Zambia_National Assembly_2016_231.csv\n",
      "Fetching data for Zambia - National Assembly - 2011\n",
      "Saved ../data/legislature_data/Zambia_National Assembly_2011_232.csv\n",
      "Fetching data for Romania - Chamber of Deputies - 2016\n",
      "Saved ../data/legislature_data/Romania_Chamber of Deputies_2016_244.csv\n",
      "Fetching data for Netherlands - Tweede Kamer - 2017\n",
      "Saved ../data/legislature_data/Netherlands_Tweede Kamer_2017_248.csv\n",
      "Fetching data for Netherlands - Tweede Kamer - 2012\n",
      "Saved ../data/legislature_data/Netherlands_Tweede Kamer_2012_249.csv\n",
      "Fetching data for Guatemala - Congress - 2012\n",
      "Saved ../data/legislature_data/Guatemala_Congress_2012_250.csv\n",
      "Fetching data for Guatemala - Congress - 2016\n",
      "Saved ../data/legislature_data/Guatemala_Congress_2016_251.csv\n",
      "Fetching data for Zimbabwe - House of Assembly - 2013\n",
      "Saved ../data/legislature_data/Zimbabwe_House of Assembly_2013_253.csv\n",
      "Fetching data for Rwanda - Chamber of Deputies - 2013\n",
      "Saved ../data/legislature_data/Rwanda_Chamber of Deputies_2013_255.csv\n",
      "Fetching data for Belgium - Chamber of Representatives - 2014\n",
      "Saved ../data/legislature_data/Belgium_Chamber of Representatives_2014_262.csv\n",
      "Fetching data for Papua-New-Guinea - National Parliament - 2012\n",
      "Saved ../data/legislature_data/Papua-New-Guinea_National Parliament_2012_287.csv\n",
      "Fetching data for Greece - Hellenic Parliament - 2012\n",
      "Saved ../data/legislature_data/Greece_Hellenic Parliament_2012_301.csv\n",
      "Fetching data for Greece - Hellenic Parliament - 2015\n",
      "Saved ../data/legislature_data/Greece_Hellenic Parliament_2015_302.csv\n",
      "Fetching data for Greece - Hellenic Parliament - 2015\n",
      "Saved ../data/legislature_data/Greece_Hellenic Parliament_2015_303.csv\n",
      "Fetching data for Greece - Hellenic Parliament - 2012\n",
      "Saved ../data/legislature_data/Greece_Hellenic Parliament_2012_304.csv\n",
      "Fetching data for Greece - Hellenic Parliament - 2009\n",
      "Saved ../data/legislature_data/Greece_Hellenic Parliament_2009_305.csv\n",
      "Fetching data for Greece - Hellenic Parliament - 2007\n",
      "Saved ../data/legislature_data/Greece_Hellenic Parliament_2007_306.csv\n",
      "Fetching data for Greece - Hellenic Parliament - 2004\n",
      "Saved ../data/legislature_data/Greece_Hellenic Parliament_2004_307.csv\n",
      "Fetching data for Hungary - Országgyűlés - 2018\n",
      "Saved ../data/legislature_data/Hungary_Országgyűlés_2018_341.csv\n",
      "Fetching data for Hungary - Országgyűlés - 2014\n",
      "Saved ../data/legislature_data/Hungary_Országgyűlés_2014_342.csv\n",
      "Fetching data for Belarus - House of Representatives - 2016\n",
      "Saved ../data/legislature_data/Belarus_House of Representatives_2016_346.csv\n",
      "Fetching data for Hong-Kong - Legislative Council - 2012\n",
      "Saved ../data/legislature_data/Hong-Kong_Legislative Council_2012_365.csv\n",
      "Fetching data for Hong-Kong - Legislative Council - 2016\n",
      "Saved ../data/legislature_data/Hong-Kong_Legislative Council_2016_366.csv\n",
      "Fetching data for Nicaragua - National Assembly - 2012\n",
      "Saved ../data/legislature_data/Nicaragua_National Assembly_2012_369.csv\n",
      "Fetching data for Bulgaria - National Assembly - 2013\n",
      "Saved ../data/legislature_data/Bulgaria_National Assembly_2013_378.csv\n",
      "Fetching data for Bulgaria - National Assembly - 2017\n",
      "Saved ../data/legislature_data/Bulgaria_National Assembly_2017_381.csv\n",
      "Fetching data for Bulgaria - National Assembly - 2014\n",
      "Saved ../data/legislature_data/Bulgaria_National Assembly_2014_382.csv\n",
      "Fetching data for Denmark - Folketing - 2015\n",
      "Saved ../data/legislature_data/Denmark_Folketing_2015_385.csv\n",
      "Fetching data for Denmark - Folketing - 2001\n",
      "Saved ../data/legislature_data/Denmark_Folketing_2001_389.csv\n",
      "Fetching data for Denmark - Folketing - 2005\n",
      "Saved ../data/legislature_data/Denmark_Folketing_2005_390.csv\n",
      "Fetching data for Denmark - Folketing - 2007\n",
      "Saved ../data/legislature_data/Denmark_Folketing_2007_391.csv\n",
      "Fetching data for Denmark - Folketing - 2011\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Saved ../data/legislature_data/Denmark_Folketing_2011_392.csv\n",
      "Fetching data for Finland - Eduskunta - 2003\n",
      "Saved ../data/legislature_data/Finland_Eduskunta_2003_408.csv\n",
      "Fetching data for Finland - Eduskunta - 2015\n",
      "Saved ../data/legislature_data/Finland_Eduskunta_2015_409.csv\n",
      "Fetching data for Finland - Eduskunta - 2011\n",
      "Saved ../data/legislature_data/Finland_Eduskunta_2011_410.csv\n",
      "Fetching data for Finland - Eduskunta - 2007\n",
      "Saved ../data/legislature_data/Finland_Eduskunta_2007_417.csv\n",
      "Fetching data for Slovakia - National Council - 2012\n",
      "Saved ../data/legislature_data/Slovakia_National Council_2012_441.csv\n",
      "Fetching data for Slovakia - National Council - 2006\n",
      "Saved ../data/legislature_data/Slovakia_National Council_2006_442.csv\n",
      "Fetching data for Slovakia - National Council - 2016\n",
      "Saved ../data/legislature_data/Slovakia_National Council_2016_443.csv\n",
      "Fetching data for Slovakia - National Council - 2010\n",
      "Saved ../data/legislature_data/Slovakia_National Council_2010_444.csv\n",
      "Fetching data for New-Zealand - New Zealand Parliament - 2011\n",
      "Saved ../data/legislature_data/New-Zealand_New Zealand Parliament_2011_458.csv\n",
      "Fetching data for New-Zealand - New Zealand Parliament - 2017\n",
      "Saved ../data/legislature_data/New-Zealand_New Zealand Parliament_2017_460.csv\n",
      "Fetching data for New-Zealand - New Zealand Parliament - 2014\n",
      "Saved ../data/legislature_data/New-Zealand_New Zealand Parliament_2014_461.csv\n",
      "Fetching data for New-Zealand - New Zealand Parliament - 2008\n",
      "Saved ../data/legislature_data/New-Zealand_New Zealand Parliament_2008_462.csv\n",
      "Fetching data for Georgia - Parliament of Georgia - 2016\n",
      "Saved ../data/legislature_data/Georgia_Parliament of Georgia_2016_472.csv\n",
      "Fetching data for Georgia - Parliament of Georgia - 2012\n",
      "Saved ../data/legislature_data/Georgia_Parliament of Georgia_2012_473.csv\n",
      "Fetching data for Uruguay - Chamber of Deputies - 2015\n",
      "Saved ../data/legislature_data/Uruguay_Chamber of Deputies_2015_477.csv\n",
      "Fetching data for Bosnia-and-Herzegovina - House of Representatives - 2014\n",
      "Saved ../data/legislature_data/Bosnia-and-Herzegovina_House of Representatives_2014_480.csv\n",
      "Fetching data for Armenia - National Assembly - 2019\n",
      "Saved ../data/legislature_data/Armenia_National Assembly_2019_485.csv\n",
      "Fetching data for Albania - Kuvendi - 2017\n",
      "Saved ../data/legislature_data/Albania_Kuvendi_2017_488.csv\n",
      "Fetching data for Albania - Kuvendi - 2013\n",
      "Saved ../data/legislature_data/Albania_Kuvendi_2013_489.csv\n",
      "Fetching data for Albania - Kuvendi - 2009\n",
      "Saved ../data/legislature_data/Albania_Kuvendi_2009_490.csv\n",
      "Fetching data for Namibia - National Council - 2015\n",
      "Saved ../data/legislature_data/Namibia_National Council_2015_501.csv\n",
      "Fetching data for Namibia - National Assembly - 2015\n",
      "Saved ../data/legislature_data/Namibia_National Assembly_2015_503.csv\n",
      "Fetching data for Moldova - Parlament - 2014\n",
      "Saved ../data/legislature_data/Moldova_Parlament_2014_505.csv\n",
      "Fetching data for Macedonia - Sobranie - 2014\n",
      "Saved ../data/legislature_data/Macedonia_Sobranie_2014_514.csv\n",
      "Fetching data for Macedonia - Sobranie - 2016\n",
      "Saved ../data/legislature_data/Macedonia_Sobranie_2016_515.csv\n",
      "Fetching data for Estonia - Riigikogu - 2019\n",
      "Saved ../data/legislature_data/Estonia_Riigikogu_2019_520.csv\n",
      "Fetching data for Estonia - Riigikogu - 2011\n",
      "Saved ../data/legislature_data/Estonia_Riigikogu_2011_521.csv\n",
      "Fetching data for Estonia - Riigikogu - 2015\n",
      "Saved ../data/legislature_data/Estonia_Riigikogu_2015_522.csv\n",
      "Fetching data for Cyprus - House of Representatives - 2016\n",
      "Saved ../data/legislature_data/Cyprus_House of Representatives_2016_523.csv\n",
      "Fetching data for Bhutan - National Assembly - 2013\n",
      "Saved ../data/legislature_data/Bhutan_National Assembly_2013_532.csv\n",
      "Fetching data for Luxembourg - Chamber of Deputies - 2013\n",
      "Saved ../data/legislature_data/Luxembourg_Chamber of Deputies_2013_535.csv\n",
      "Fetching data for Luxembourg - Chamber of Deputies - 2018\n",
      "Saved ../data/legislature_data/Luxembourg_Chamber of Deputies_2018_536.csv\n",
      "Fetching data for Suriname - National Assembly - 2015\n",
      "Saved ../data/legislature_data/Suriname_National Assembly_2015_537.csv\n",
      "Fetching data for Malta - Parliament - 2017\n",
      "Saved ../data/legislature_data/Malta_Parliament_2017_542.csv\n",
      "Fetching data for Malta - Parliament - 2013\n",
      "Saved ../data/legislature_data/Malta_Parliament_2013_543.csv\n",
      "Fetching data for French-Polynesia - Assembly - 2013\n",
      "Saved ../data/legislature_data/French-Polynesia_Assembly_2013_559.csv\n",
      "Fetching data for Seychelles - National Assembly - 2011\n",
      "Saved ../data/legislature_data/Seychelles_National Assembly_2011_575.csv\n",
      "Fetching data for Andorra - Consell General - 2015\n",
      "Saved ../data/legislature_data/Andorra_Consell General_2015_596.csv\n",
      "Fetching data for Bermuda - Parliament - 2017\n",
      "Saved ../data/legislature_data/Bermuda_Parliament_2017_672.csv\n",
      "Fetching data for Greenland - Inatsisartut - 2014\n",
      "Saved ../data/legislature_data/Greenland_Inatsisartut_2014_687.csv\n",
      "Fetching data for Guernsey - States - 2016\n",
      "Saved ../data/legislature_data/Guernsey_States_2016_699.csv\n",
      "Fetching data for Jersey - States - 2014\n",
      "Saved ../data/legislature_data/Jersey_States_2014_701.csv\n",
      "Fetching data for Scotland - Scottish Parliament - 2016\n",
      "Saved ../data/legislature_data/Scotland_Scottish Parliament_2016_732.csv\n",
      "Fetching data for Scotland - Scottish Parliament - 2011\n",
      "Saved ../data/legislature_data/Scotland_Scottish Parliament_2011_733.csv\n",
      "Fetching data for Scotland - Scottish Parliament - 2007\n",
      "Saved ../data/legislature_data/Scotland_Scottish Parliament_2007_734.csv\n",
      "Fetching data for Wales - National Assembly for Wales - 2016\n",
      "Saved ../data/legislature_data/Wales_National Assembly for Wales_2016_748.csv\n",
      "Fetching data for Wales - National Assembly for Wales - 2011\n",
      "Saved ../data/legislature_data/Wales_National Assembly for Wales_2011_749.csv\n"
     ]
    }
   ],
   "source": [
    "output_folder = \"../data/legislature_data\"\n",
    "os.makedirs(output_folder, exist_ok=True)\n",
    "\n",
    "dfs = []\n",
    "\n",
    "for _, row in sm_data.iterrows():\n",
    "    try:\n",
    "        print(f\"Fetching data for {row['country']} - {row['legislature']} - {row['leg_start_year']}\")\n",
    "        response = requests.get(row['url'])\n",
    "        response.raise_for_status()\n",
    "\n",
    "        csv_filename = os.path.join(output_folder, f\"{row['country']}_{row['legislature']}_{row['leg_start_year']}_{row['row_id']}.csv\")\n",
    "        with open(csv_filename, \"wb\") as file:\n",
    "            file.write(response.content)\n",
    "        print(f\"Saved {csv_filename}\")\n",
    "\n",
    "        csv_data = StringIO(response.text)\n",
    "        df = pd.read_csv(csv_data)\n",
    "\n",
    "        # Add key\n",
    "        df[\"row_id\"] = row[\"row_id\"]\n",
    "    \n",
    "        dfs.append(df)\n",
    "    except Exception as e:\n",
    "        print(f\"Failed to process {row['country']} - {row['legislature']} - {row['leg_start_year']}: {e}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "30193624",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(25087, 20)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_df = pd.concat(dfs, ignore_index=True)\n",
    "combined_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "1b0659db",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['id',\n",
       " 'name',\n",
       " 'sort_name',\n",
       " 'email',\n",
       " 'twitter',\n",
       " 'facebook',\n",
       " 'group',\n",
       " 'group_id',\n",
       " 'area_id',\n",
       " 'area',\n",
       " 'chamber',\n",
       " 'term_x',\n",
       " 'start_date',\n",
       " 'end_date',\n",
       " 'image',\n",
       " 'gender',\n",
       " 'wikidata',\n",
       " 'wikidata_group',\n",
       " 'wikidata_area',\n",
       " 'row_id',\n",
       " 'country',\n",
       " 'legislature',\n",
       " 'term_y',\n",
       " 'leg_start_year',\n",
       " 'row_count',\n",
       " 'n_unique_emails',\n",
       " 'person_count_legistype',\n",
       " 'ltype',\n",
       " 'url',\n",
       " 'cc',\n",
       " 'leg_start_date',\n",
       " 'lastmod',\n",
       " 'cc3',\n",
       " 'pop2024',\n",
       " 'lastmod_year']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df = pd.merge(combined_df, sm_data, on=\"row_id\", how=\"left\")\n",
    "list(final_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "35b5165a",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.to_csv(\"../data/everypol_combined_legislature_data.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "6b5706a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get Unique emails for running through HIBP\n",
    "pd.DataFrame(final_df['email'].unique(), columns=[\"email\"]).to_csv(\"../data/everypol_unique_emails.csv\", index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7e61647",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}