Voters_and_MPs_datasets.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Design\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:18: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\Design\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:25: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\Design\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:53: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\Design\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:56: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\Design\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:59: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\Design\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:72: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\Design\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:68: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\Design\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:64: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\Design\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:70: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\Design\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:66: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\Design\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:62: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "\n",
    "# Load latest Momoko file\n",
    "latest_csv = pd.read_csv(\"combined_parliaments_new_JUN12-2021.csv\", index_col=0)\n",
    "\n",
    "# get the \"Name\", \"Gender\", \"Office\", Birth_year\" columns only\n",
    "data = latest_csv[[\"Name\",\"Gender\",\"Office\",\"Birth_year\"]]\n",
    "\n",
    "#rename the columns\n",
    "data.columns = [\"Name\",\"Gender\",\"Parliament\",\"YOB\"]\n",
    "\n",
    "#DROP rows that have YOB as null value\n",
    "data.dropna(subset=[\"YOB\"], inplace=True)\n",
    "### DROP rows with not a number for YOB\n",
    "data.drop(data.index[data['YOB'] == \"Baby bommer\"], inplace=True)\n",
    "data.drop(data.index[data['YOB'] == \"Baby boomer\"], inplace=True)\n",
    "data.drop(data.index[data['YOB'] == \"Generation X\"], inplace=True)\n",
    "data.drop(data.index[data['YOB'] == \"Millenial\"], inplace=True)\n",
    "# assign int type to YOB \n",
    "data[\"YOB\"] = data[\"YOB\"].astype(int)\n",
    "\n",
    "\n",
    "#reset the index\n",
    "data.reset_index(drop=True, inplace=True)\n",
    "\n",
    "#create a column for Parliament_year \n",
    "conditions = [\n",
    "    (data['Parliament'] == 28),\n",
    "    (data['Parliament'] == 29),\n",
    "    (data['Parliament'] == 30),\n",
    "    (data['Parliament'] == 31),\n",
    "    (data['Parliament'] == 32),\n",
    "    (data['Parliament'] == 33),\n",
    "    (data['Parliament'] == 34),\n",
    "    (data['Parliament'] == 35),\n",
    "    (data['Parliament'] == 36),\n",
    "    (data['Parliament'] == 37),\n",
    "    (data['Parliament'] == 38),\n",
    "    (data['Parliament'] == 39),\n",
    "    (data['Parliament'] == 40),\n",
    "    (data['Parliament'] == 41),\n",
    "    (data['Parliament'] == 42),\n",
    "    (data['Parliament'] == 43)\n",
    "    ]\n",
    "# create a list of the values we want to assign for each condition\n",
    "values = [1968,1972,1974,1979,1980,1984,1988,1993,1997,2000,2004,2006,2008,2011,2015,2019]\n",
    "# create a new column and use np.select to assign values to it using our lists as arguments\n",
    "data['Parliament_Year'] = np.select(conditions, values)\n",
    "\n",
    "# create Age@Parliament column\n",
    "data['Age@Parliament']=data[\"Parliament_Year\"]-data[\"YOB\"]\n",
    "\n",
    "### set Age_Buckets columns\n",
    "data[\"Age_buckets\"]=\"\"\n",
    "for i, k in enumerate(data['Age@Parliament']):   \n",
    "    if 17<k<31:\n",
    "        data[\"Age_buckets\"].loc[i]=\"18-30\"\n",
    "    elif 30<k<41:\n",
    "        data[\"Age_buckets\"].loc[i]=\"31-40\"\n",
    "    elif 40<k<51:\n",
    "        data[\"Age_buckets\"].loc[i]=\"41-50\"\n",
    "    elif 50<k<61:\n",
    "        data[\"Age_buckets\"].loc[i]=\"51-60\"\n",
    "    elif 60<k<71:\n",
    "        data[\"Age_buckets\"].loc[i]=\"61-70\"\n",
    "    else:\n",
    "        data[\"Age_buckets\"].loc[i]=\"71+\"    \n",
    "\n",
    "data.to_csv('MPs_by_Age.csv')\n",
    "#Parliaments_by_AgeBuckets = data.drop(['Parliament','YOB','Age@Parliament'], axis=1)\n",
    "Parliaments_by_AgeBuckets = data.drop(['Parliament','YOB'], axis=1)\n",
    "#Parliaments_by_AgeBuckets = Parliaments_by_AgeBuckets.groupby([\"Parliament_Year\",\"Age_buckets\"])[\"Name\"].count().reset_index()\n",
    "Parliaments_by_AgeBuckets = Parliaments_by_AgeBuckets.groupby([\"Parliament_Year\",\"Age_buckets\"]).agg({\"Age@Parliament\":[\"mean\"],\"Name\":[\"count\"]}).reset_index()\n",
    "#Parliaments_by_AgeBuckets.rename(columns = {'Name':'MP_count'}, inplace = True)\n",
    "#Parliaments_by_AgeBuckets.rename(columns = {'Parliament_Year':'Parliament_year'}, inplace = True)\n",
    "Parliaments_by_AgeBuckets.columns = [\"Parliament_year\",\"Age_buckets\",\"Avg_MP_age\", \"MP_count\"]\n",
    "Parliaments_by_AgeBuckets[\"Avg_MP_age\"] = Parliaments_by_AgeBuckets[\"Avg_MP_age\"].round().astype(int)\n",
    "Parliaments_by_AgeBuckets = Parliaments_by_AgeBuckets[(Parliaments_by_AgeBuckets[\"Parliament_year\"]>1968)]\n",
    "Parliaments_by_AgeBuckets.reset_index(drop=True, inplace=True)\n",
    "\n",
    "Parliaments_by_AgeBuckets.to_csv('Parliaments_by_AgeBuckets.csv')\n",
    "\n",
    "#Parliaments_by_AgeBuckets = Parliaments_by_AgeBuckets[(Parliaments_by_AgeBuckets[\"Parliament_year\"]>1968)]\n",
    "\n",
    "\n",
    "\n",
    "census_data = pd.read_csv(\"Age_Canada_FINAL.csv\", index_col=0)\n",
    "# drop columns we don't need\n",
    "census_data.drop(['0 – 4', '5 – 9', '10 – 14'], axis = 1, inplace = True)\n",
    "# convert columns with NAN from Float to Int46 (nullable integer type) - \n",
    "# regular int type doesnt support Nan in the values\n",
    "census_data[\"90 – 94\"] = census_data[\"90 – 94\"].astype('Int64')\n",
    "census_data[\"95 – 99\"] = census_data[\"95 – 99\"].astype('Int64') \n",
    "census_data[\"> 100\"] = census_data[\"> 100\"].astype('Int64') \n",
    "\n",
    "census_data[\"18-30\"] = (census_data[\"15 – 19\"]/5)*2+census_data[\"20 – 24\"]+census_data[\"25 – 29\"]+(census_data[\"30 – 34\"]/5)\n",
    "census_data[\"18-30\"] = census_data[\"18-30\"].apply(int)\n",
    "census_data[\"31-40\"] = (census_data[\"30 – 34\"]/5)*4+census_data[\"35 – 39\"]+(census_data[\"40 – 44\"]/5)\n",
    "census_data[\"31-40\"] = census_data[\"31-40\"].apply(int)\n",
    "census_data[\"41-50\"] = (census_data[\"40 – 44\"]/5)*4+census_data[\"45 – 49\"]+(census_data[\"50 – 54\"]/5)\n",
    "census_data[\"41-50\"] = census_data[\"41-50\"].apply(int)\n",
    "census_data[\"51-60\"] = (census_data[\"50 – 54\"]/5)*4+census_data[\"55 – 59\"]+(census_data[\"60 – 64\"]/5)\n",
    "census_data[\"51-60\"] = census_data[\"51-60\"].apply(int)\n",
    "census_data[\"61-70\"] = (census_data[\"60 – 64\"]/5)*4+census_data[\"65 – 69\"]+(census_data[\"70 – 74\"]/5)\n",
    "census_data[\"61-70\"] = census_data[\"61-70\"].apply(int)\n",
    "census_data = census_data.fillna(0)\n",
    "census_data[\"71+\"] = (census_data[\"70 – 74\"]/5)*4 + census_data[\"75 – 79\"] + census_data[\"80 – 84\"] + census_data[\"85 – 89\"] + census_data[\"90 – 94\"] + \\\n",
    "                        census_data[\"95 – 99\"] + census_data[\"> 100\"]\n",
    "census_data[\"71+\"] = census_data[\"71+\"].apply(int) \n",
    "\n",
    "census_data =census_data[[\"18-30\", \"31-40\", \"41-50\", \"51-60\", \"61-70\", \"71+\"]]\n",
    "# get census data from 1972 parliament\n",
    "census_final = census_data.loc[[1972,1974,1979,1980,1984,1988,1993,1997,2000,2004,2006,2008,2011,2015,2019]]\n",
    "# reswet the index\n",
    "census_final.reset_index(drop=False, inplace=True)\n",
    "# rename the column\n",
    "census_final.rename(columns = {'index':'Parliament_year'}, inplace = True)\n",
    "\n",
    "Census_by_AgeBuckets = pd.melt(census_final, id_vars=\"Parliament_year\", var_name=\"Age_buckets\", value_name=\"Voters_count\")\n",
    "Census_by_AgeBuckets.to_csv('Census_data_by_AgeBuckets.csv')\n",
    "\n",
    "\n",
    "# MERGE BOTH datasets\n",
    "all_data = pd.merge(Parliaments_by_AgeBuckets, Census_by_AgeBuckets, on=[\"Parliament_year\", \"Age_buckets\"])\n",
    "\n",
    "Voters_count_per_year = all_data.groupby(\"Parliament_year\")[\"Voters_count\"].sum().reset_index()\n",
    "MP_count_per_year = all_data.groupby(\"Parliament_year\")[\"MP_count\"].sum().reset_index()\n",
    "\n",
    "Total_MP_and_Voters_count_per_year = pd.merge(Voters_count_per_year, MP_count_per_year, on=\"Parliament_year\")\n",
    "\n",
    "final_data = pd.merge(all_data, Total_MP_and_Voters_count_per_year, on=\"Parliament_year\")\n",
    "\n",
    "final_data.rename(columns = {'MP_count_x':'MP_count','Voters_count_x':'Voters_count','Voters_count_y':'Total_Voters_count','MP_count_y':'Total_MP_count' }, inplace = True)\n",
    "final_data[\"Voters_count_%\"] = ((final_data[\"Voters_count\"]/final_data[\"Total_Voters_count\"])*100).round(decimals=1)\n",
    "final_data[\"MP_count_%\"] = ((final_data[\"MP_count\"]/final_data[\"Total_MP_count\"])*100).round(decimals=1)\n",
    "final_data.to_csv(\"Voters_and_MPs_Age_buckets_per_Parliament_June14-2021.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}