combine_and_nest.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import glob\n",
    "import re\n",
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Utility functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def write_json(data_dict,filename,data_write_path):\n",
    "    '''\n",
    "    Write JSON file into designated raw data folder.\n",
    "    '''\n",
    "    \n",
    "    # Serializing json\n",
    "    json_object = json.dumps(data_dict, indent=4)\n",
    "\n",
    "    # Writing to .json\n",
    "    with open(data_write_path + filename, \"w\") as outfile:\n",
    "        outfile.write(json_object)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Make nested JSON from all the yearly results data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'pd' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[1], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m combined_df \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241m.\u001b[39mread_csv(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m./data/cleaned/combined/combined_results_parliament.csv\u001b[39m\u001b[38;5;124m'\u001b[39m, converters \u001b[38;5;241m=\u001b[39m {\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mparliament_code_digits\u001b[39m\u001b[38;5;124m'\u001b[39m: \u001b[38;5;28mstr\u001b[39m})\n\u001b[1;32m      2\u001b[0m combined_df[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mresults_added\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m=\u001b[39m \u001b[38;5;241m1\u001b[39m\n\u001b[1;32m      3\u001b[0m \u001b[38;5;66;03m# ge15_candidates_clean_df = pd.read_csv('./data/cleaned/combined/combined_results_parliament_ge15.csv', converters = {'parliament_code_digits': str})\u001b[39;00m\n\u001b[1;32m      4\u001b[0m \u001b[38;5;66;03m# ge15_incumbents_clean_df = pd.read_csv('./data/cleaned/malaysiakini_newslab/ge15_incumbents_clean.csv', converters = {'parliament_code_digits': str})\u001b[39;00m\n\u001b[1;32m      5\u001b[0m \u001b[38;5;66;03m# ge15_incumbents_clean_df['results_added'] = 1\u001b[39;00m\n\u001b[1;32m      6\u001b[0m \n\u001b[1;32m      7\u001b[0m \u001b[38;5;66;03m# combined_df = pd.concat([combined_df, ge15_incumbents_clean_df, ge15_candidates_clean_df])\u001b[39;00m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'pd' is not defined"
     ]
    }
   ],
   "source": [
    "combined_df = pd.read_csv('./data/cleaned/combined/combined_results_parliament.csv', converters = {'parliament_code_digits': str})\n",
    "combined_df['results_added'] = 1\n",
    "# ge15_candidates_clean_df = pd.read_csv('./data/cleaned/combined/combined_results_parliament_ge15.csv', converters = {'parliament_code_digits': str})\n",
    "# ge15_incumbents_clean_df = pd.read_csv('./data/cleaned/malaysiakini_newslab/ge15_incumbents_clean.csv', converters = {'parliament_code_digits': str})\n",
    "# ge15_incumbents_clean_df['results_added'] = 1\n",
    "\n",
    "# combined_df = pd.concat([combined_df, ge15_incumbents_clean_df, ge15_candidates_clean_df])\n",
    "\n",
    "combined_df = pd.read_csv('./data/raw/Thevesh/prn15.csv', converters = {'parliament_code_digits': str, 'dunCode': str, 'threeDunCode': str})\n",
    "\n",
    "combined_df.fillna('',inplace=True)\n",
    "# combined_df.sort_values(by=['year','parliament_code_digits','votes'],ascending=[True,True,False],inplace=True)\n",
    "combined_df.sort_values(by=['year','dunCode','votes'],ascending=[True,True,False],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>year</th>\n",
       "      <th>name</th>\n",
       "      <th>coalition</th>\n",
       "      <th>party_code</th>\n",
       "      <th>votes</th>\n",
       "      <th>vote_share</th>\n",
       "      <th>parliament_code_digits</th>\n",
       "      <th>constituency</th>\n",
       "      <th>state</th>\n",
       "      <th>winner</th>\n",
       "      <th>total_votes</th>\n",
       "      <th>results_added</th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>gender</th>\n",
       "      <th>spr_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004</td>\n",
       "      <td>DATUK SERI AZMI KHALID</td>\n",
       "      <td>BN</td>\n",
       "      <td>UMNO</td>\n",
       "      <td>18322</td>\n",
       "      <td>66.917458</td>\n",
       "      <td>001</td>\n",
       "      <td>PADANG BESAR</td>\n",
       "      <td>Perlis</td>\n",
       "      <td>1.0</td>\n",
       "      <td>27380</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004</td>\n",
       "      <td>WAN KHARIZAL WAN KHAZIM</td>\n",
       "      <td>BA</td>\n",
       "      <td>PAS</td>\n",
       "      <td>9058</td>\n",
       "      <td>33.082542</td>\n",
       "      <td>001</td>\n",
       "      <td>PADANG BESAR</td>\n",
       "      <td>Perlis</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27380</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004</td>\n",
       "      <td>DATUK RADZI SHEIKH AHMAD</td>\n",
       "      <td>BN</td>\n",
       "      <td>UMNO</td>\n",
       "      <td>22498</td>\n",
       "      <td>69.335552</td>\n",
       "      <td>002</td>\n",
       "      <td>KANGAR</td>\n",
       "      <td>Perlis</td>\n",
       "      <td>1.0</td>\n",
       "      <td>32448</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>ISHAR SAAD</td>\n",
       "      <td>BA</td>\n",
       "      <td>PAS</td>\n",
       "      <td>9950</td>\n",
       "      <td>30.664448</td>\n",
       "      <td>002</td>\n",
       "      <td>KANGAR</td>\n",
       "      <td>Perlis</td>\n",
       "      <td>0.0</td>\n",
       "      <td>32448</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004</td>\n",
       "      <td>DATUK SERI SYED RAZLAN SYED PUTRA JAMALULLAIL</td>\n",
       "      <td>BN</td>\n",
       "      <td>UMNO</td>\n",
       "      <td>17367</td>\n",
       "      <td>55.149090</td>\n",
       "      <td>003</td>\n",
       "      <td>ARAU</td>\n",
       "      <td>Perlis</td>\n",
       "      <td>1.0</td>\n",
       "      <td>31491</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>2022 Incumbents</td>\n",
       "      <td>LUKANISMAN AWANG SAUNI</td>\n",
       "      <td>GPS</td>\n",
       "      <td>PBB</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>218</td>\n",
       "      <td>SIBUTI</td>\n",
       "      <td>Sarawak</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>210</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>217</th>\n",
       "      <td>2022 Incumbents</td>\n",
       "      <td>MICHAEL TEO YU KENG</td>\n",
       "      <td>PH</td>\n",
       "      <td>PKR</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>219</td>\n",
       "      <td>MIRI</td>\n",
       "      <td>Sarawak</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>217</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>2022 Incumbents</td>\n",
       "      <td>ANYI NGAU</td>\n",
       "      <td>GPS</td>\n",
       "      <td>PDP</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>220</td>\n",
       "      <td>BARAM</td>\n",
       "      <td>Sarawak</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>209</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>2022 Incumbents</td>\n",
       "      <td>HASBI HABIBOLLAH</td>\n",
       "      <td>GPS</td>\n",
       "      <td>PBB</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>221</td>\n",
       "      <td>LIMBANG</td>\n",
       "      <td>Sarawak</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>198</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>2022 Incumbents</td>\n",
       "      <td>HENRY SUM AGONG</td>\n",
       "      <td>GPS</td>\n",
       "      <td>PBB</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>222</td>\n",
       "      <td>LAWAS</td>\n",
       "      <td>Sarawak</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>211</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3348 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                year                                           name coalition  \\\n",
       "0               2004                         DATUK SERI AZMI KHALID        BN   \n",
       "1               2004                        WAN KHARIZAL WAN KHAZIM        BA   \n",
       "2               2004                       DATUK RADZI SHEIKH AHMAD        BN   \n",
       "3               2004                                     ISHAR SAAD        BA   \n",
       "4               2004  DATUK SERI SYED RAZLAN SYED PUTRA JAMALULLAIL        BN   \n",
       "..               ...                                            ...       ...   \n",
       "210  2022 Incumbents                         LUKANISMAN AWANG SAUNI       GPS   \n",
       "217  2022 Incumbents                            MICHAEL TEO YU KENG        PH   \n",
       "209  2022 Incumbents                                      ANYI NGAU       GPS   \n",
       "198  2022 Incumbents                               HASBI HABIBOLLAH       GPS   \n",
       "211  2022 Incumbents                                HENRY SUM AGONG       GPS   \n",
       "\n",
       "    party_code  votes  vote_share parliament_code_digits  constituency  \\\n",
       "0         UMNO  18322   66.917458                    001  PADANG BESAR   \n",
       "1          PAS   9058   33.082542                    001  PADANG BESAR   \n",
       "2         UMNO  22498   69.335552                    002        KANGAR   \n",
       "3          PAS   9950   30.664448                    002        KANGAR   \n",
       "4         UMNO  17367   55.149090                    003          ARAU   \n",
       "..         ...    ...         ...                    ...           ...   \n",
       "210        PBB      0    0.000000                    218        SIBUTI   \n",
       "217        PKR      0    0.000000                    219          MIRI   \n",
       "209        PDP      0    0.000000                    220         BARAM   \n",
       "198        PBB      0    0.000000                    221       LIMBANG   \n",
       "211        PBB      0    0.000000                    222         LAWAS   \n",
       "\n",
       "       state  winner  total_votes  results_added Unnamed: 0 gender spr_id  \n",
       "0     Perlis     1.0        27380              1                           \n",
       "1     Perlis     0.0        27380              1                           \n",
       "2     Perlis     1.0        32448              1                           \n",
       "3     Perlis     0.0        32448              1                           \n",
       "4     Perlis     1.0        31491              1                           \n",
       "..       ...     ...          ...            ...        ...    ...    ...  \n",
       "210  Sarawak     1.0            0              1        210                \n",
       "217  Sarawak     1.0            0              1        217                \n",
       "209  Sarawak     1.0            0              1        209                \n",
       "198  Sarawak     1.0            0              1        198                \n",
       "211  Sarawak     1.0            0              1        211                \n",
       "\n",
       "[3348 rows x 15 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['year', 'name', 'coalition', 'party_code', 'votes', 'vote_share',\n",
       "       'parliament_code_digits', 'constituency', 'state', 'winner',\n",
       "       'total_votes', 'results_added', 'Unnamed: 0', 'gender', 'spr_id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['BN', '', 'PH', 'PR', 'PN', 'GS', 'BA', 'GTA', 'GPS', 'USA', 'GRS'], dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_df['coalition'].value_counts().index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['AMANAH',\n",
       " 'ANAK NEGERI',\n",
       " 'BERJASA',\n",
       " 'BERSAMA',\n",
       " 'BERSATU',\n",
       " 'BERSEKUTU',\n",
       " 'BN',\n",
       " 'DAP',\n",
       " 'DIRECT',\n",
       " 'GERAKAN',\n",
       " 'IKATAN',\n",
       " 'IMAN',\n",
       " 'IND',\n",
       " 'INDEPENDENT',\n",
       " 'IPF',\n",
       " 'KDM',\n",
       " 'KIMMA',\n",
       " 'KITA',\n",
       " 'LDP',\n",
       " 'MCA',\n",
       " 'MDP',\n",
       " 'MIC',\n",
       " 'MMSP',\n",
       " 'MUDA',\n",
       " 'MUP',\n",
       " 'MUPP',\n",
       " 'MYPPP',\n",
       " 'PAP',\n",
       " 'PAS',\n",
       " 'PASOK',\n",
       " 'PBB',\n",
       " 'PBDS',\n",
       " 'PBDSB',\n",
       " 'PBK',\n",
       " 'PBM',\n",
       " 'PBRS',\n",
       " 'PBS',\n",
       " 'PCM',\n",
       " 'PCS',\n",
       " 'PDP',\n",
       " 'PEACE',\n",
       " 'PEJUANG',\n",
       " 'PERPADUAN',\n",
       " 'PFP',\n",
       " 'PHRS',\n",
       " 'PKAN',\n",
       " 'PKR',\n",
       " 'PPBM',\n",
       " 'PPP',\n",
       " 'PPRS',\n",
       " 'PRM',\n",
       " 'PRS',\n",
       " 'PSB',\n",
       " 'PSM',\n",
       " 'PUR',\n",
       " 'PUTRA',\n",
       " \"S'WAK STAR\",\n",
       " 'SABAH STAR',\n",
       " 'SAPP',\n",
       " 'SEDAR',\n",
       " 'SETIA',\n",
       " 'SNAP',\n",
       " 'SPDP',\n",
       " 'STAR',\n",
       " 'SUPP',\n",
       " 'SWP',\n",
       " 'UMNO',\n",
       " 'UPKO',\n",
       " 'VACANT',\n",
       " 'WARISAN']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parties = combined_df['party_code'].value_counts().index\n",
    "parties = parties.to_list()\n",
    "parties.sort()\n",
    "parties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "combined_df_year_district = combined_df.set_index(['year','parliament_code_digits'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "year_district_pairs = combined_df_year_district.index.unique().to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "nested_year_district = {}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "for year_district in year_district_pairs:\n",
    "\n",
    "  [year,district] = year_district\n",
    "  \n",
    "  if year not in nested_year_district:\n",
    "    nested_year_district[year] = {}\n",
    "  \n",
    "  nested_year_district[year][district] = {}\n",
    "\n",
    "  district_columns = ['total_votes','constituency','state','results_added']\n",
    "\n",
    "  result_columns = ['name','party_code','coalition','votes','vote_share','winner','gender']\n",
    "\n",
    "  nested_year_district[year][district]['info'] = combined_df_year_district.loc[(year,district),:][district_columns].to_dict('records')[0]\n",
    "\n",
    "  nested_year_district[year][district]['results'] = combined_df_year_district.loc[(year,district),:][result_columns].to_dict('records')\n",
    "\n",
    "  winner_list = [row for row in nested_year_district[year][district]['results'] if row['winner'] == 1]\n",
    "  winner = ''\n",
    "  if len(winner_list) > 0:\n",
    "    winner = winner_list[0]\n",
    "  nested_year_district[year][district]['winner'] = winner\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "write_json(nested_year_district,'nested_results_parliament.json','./data/cleaned/combined/')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Make Parties data into nested JSON"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "parties_df = pd.read_csv('./data/cleaned/combined/party_info.csv')\n",
    "parties_df.fillna('',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>year</th>\n",
       "      <th>coalition</th>\n",
       "      <th>coalition_name</th>\n",
       "      <th>coalition_name_bahasa</th>\n",
       "      <th>party</th>\n",
       "      <th>party_name</th>\n",
       "      <th>party_color</th>\n",
       "      <th>coalition_color</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004</td>\n",
       "      <td>BA</td>\n",
       "      <td>Alternative Front</td>\n",
       "      <td>Barisan Alternatif</td>\n",
       "      <td>PAS</td>\n",
       "      <td>Pan-Malaysian Islamic Party</td>\n",
       "      <td>#009000</td>\n",
       "      <td>#ed161e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004</td>\n",
       "      <td>BA</td>\n",
       "      <td>Alternative Front</td>\n",
       "      <td>Barisan Alternatif</td>\n",
       "      <td>PKR</td>\n",
       "      <td>People's Justice Party</td>\n",
       "      <td>#00ADEF</td>\n",
       "      <td>#ed161e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004</td>\n",
       "      <td>BN</td>\n",
       "      <td>National Front</td>\n",
       "      <td>Barisan Nasional</td>\n",
       "      <td>UMNO</td>\n",
       "      <td>United Malays National Organisation</td>\n",
       "      <td>#E62020</td>\n",
       "      <td>#000080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>BN</td>\n",
       "      <td>National Front</td>\n",
       "      <td>Barisan Nasional</td>\n",
       "      <td>MCA</td>\n",
       "      <td>Malaysian Chinese Association</td>\n",
       "      <td>#0000FF</td>\n",
       "      <td>#000080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004</td>\n",
       "      <td>BN</td>\n",
       "      <td>National Front</td>\n",
       "      <td>Barisan Nasional</td>\n",
       "      <td>GERAKAN</td>\n",
       "      <td>Malaysian People's Movement Party</td>\n",
       "      <td>#FF0000</td>\n",
       "      <td>#000080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>2022 Incumbents</td>\n",
       "      <td>PH</td>\n",
       "      <td>Alliance of Hope</td>\n",
       "      <td>Pakatan Harapan</td>\n",
       "      <td>MUDA</td>\n",
       "      <td>Malaysian United Democratic Alliance</td>\n",
       "      <td>#000000</td>\n",
       "      <td>#ED1C24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>2022 Incumbents</td>\n",
       "      <td>PH</td>\n",
       "      <td>Alliance of Hope</td>\n",
       "      <td>Pakatan Harapan</td>\n",
       "      <td>UPKO</td>\n",
       "      <td>United Pasokmomogun Kadazandusun Murut Organis...</td>\n",
       "      <td>#ABCDFF</td>\n",
       "      <td>#ED1C24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>2022 Incumbents</td>\n",
       "      <td>BN</td>\n",
       "      <td>National Front</td>\n",
       "      <td>Barisan Nasional</td>\n",
       "      <td>PBRS</td>\n",
       "      <td>United Sabah People's Party</td>\n",
       "      <td>#FEDF00</td>\n",
       "      <td>#000080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>2022 Incumbents</td>\n",
       "      <td>GRS</td>\n",
       "      <td>Sabah People's Coalition</td>\n",
       "      <td>Gabungan Rakyat Sabah</td>\n",
       "      <td>PBS</td>\n",
       "      <td>United Sabah Party</td>\n",
       "      <td>#ADD8E6</td>\n",
       "      <td>#91cbf2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>168 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                year coalition            coalition_name  \\\n",
       "0               2004        BA         Alternative Front   \n",
       "1               2004        BA         Alternative Front   \n",
       "2               2004        BN            National Front   \n",
       "3               2004        BN            National Front   \n",
       "4               2004        BN            National Front   \n",
       "..               ...       ...                       ...   \n",
       "163  2022 Incumbents        PH          Alliance of Hope   \n",
       "164  2022 Incumbents        PH          Alliance of Hope   \n",
       "165  2022 Incumbents        BN            National Front   \n",
       "166  2022 Incumbents       GRS  Sabah People's Coalition   \n",
       "167                                                        \n",
       "\n",
       "     coalition_name_bahasa    party  \\\n",
       "0       Barisan Alternatif      PAS   \n",
       "1       Barisan Alternatif      PKR   \n",
       "2         Barisan Nasional     UMNO   \n",
       "3         Barisan Nasional      MCA   \n",
       "4         Barisan Nasional  GERAKAN   \n",
       "..                     ...      ...   \n",
       "163        Pakatan Harapan     MUDA   \n",
       "164        Pakatan Harapan     UPKO   \n",
       "165       Barisan Nasional     PBRS   \n",
       "166  Gabungan Rakyat Sabah      PBS   \n",
       "167                                   \n",
       "\n",
       "                                            party_name party_color  \\\n",
       "0                          Pan-Malaysian Islamic Party     #009000   \n",
       "1                               People's Justice Party     #00ADEF   \n",
       "2                  United Malays National Organisation     #E62020   \n",
       "3                        Malaysian Chinese Association     #0000FF   \n",
       "4                    Malaysian People's Movement Party     #FF0000   \n",
       "..                                                 ...         ...   \n",
       "163               Malaysian United Democratic Alliance     #000000   \n",
       "164  United Pasokmomogun Kadazandusun Murut Organis...     #ABCDFF   \n",
       "165                        United Sabah People's Party     #FEDF00   \n",
       "166                                 United Sabah Party     #ADD8E6   \n",
       "167                                                                  \n",
       "\n",
       "    coalition_color  \n",
       "0           #ed161e  \n",
       "1           #ed161e  \n",
       "2           #000080  \n",
       "3           #000080  \n",
       "4           #000080  \n",
       "..              ...  \n",
       "163         #ED1C24  \n",
       "164         #ED1C24  \n",
       "165         #000080  \n",
       "166         #91cbf2  \n",
       "167                  \n",
       "\n",
       "[168 rows x 8 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parties_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "parties_df_year_party = parties_df.set_index(['year','party'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "year_party_pairs = parties_df_year_party.index.unique().to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "parties_nested_year_party = {}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pandas/core/indexing.py:1761: PerformanceWarning: indexing past lexsort depth may impact performance.\n",
      "  return self._getitem_tuple(key)\n"
     ]
    }
   ],
   "source": [
    "for year_party in year_party_pairs:\n",
    "\n",
    "  [year,party] = year_party\n",
    "  \n",
    "  if year not in parties_nested_year_party:\n",
    "    parties_nested_year_party[year] = {}\n",
    "  \n",
    "  parties_nested_year_party[year][party] = parties_df_year_party.loc[(year,party),:].to_dict('records')[0]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "write_json(parties_nested_year_party,'parties_nested.json','./data/cleaned/combined/')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.11.3"
  },
  "vscode": {
   "interpreter": {
    "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}