scripts/01_merge.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-25T05:51:23.702360Z",
     "start_time": "2020-10-25T05:51:22.530745Z"
    }
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "from IPython.display import clear_output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-25T05:51:23.718368Z",
     "start_time": "2020-10-25T05:51:23.704360Z"
    }
   },
   "outputs": [],
   "source": [
    "pd.set_option('display.max_rows', 500)\n",
    "pd.set_option('display.max_columns', 50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-25T05:51:23.734369Z",
     "start_time": "2020-10-25T05:51:23.721404Z"
    }
   },
   "outputs": [],
   "source": [
    "RAW_FILEPATH = os.path.join('..', 'raw')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read in Voteview, DIME, and SCTV"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Read in voteview** (https://voteview.com/articles/data_help_members)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-25T05:51:29.140114Z",
     "start_time": "2020-10-25T05:51:23.735369Z"
    },
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "VV_IDENTIFIERS = ['icpsr', 'bioname', 'congress', 'chamber']\n",
    "VV_IDEO = ['nominate_dim1', 'nominate_log_likelihood', 'nominate_geo_mean_probability', 'nokken_poole_dim1']\n",
    "VV_USECOLS = VV_IDENTIFIERS + VV_IDEO\n",
    "\n",
    "vv = pd.read_csv(os.path.join(RAW_FILEPATH, 'HSall_members.csv'), usecols=VV_USECOLS)\n",
    "\n",
    "vv['icpsr'] = vv['icpsr'].astype('str')\n",
    "\n",
    "vv.sort_values(by=['congress', 'icpsr'], ascending=True, inplace=True)\n",
    "\n",
    "# Fill forward, retain latest scores where available\n",
    "for col in  VV_IDEO:\n",
    "    vv[col] = vv.groupby('bioname')[col].apply(lambda x: x.ffill())\n",
    "\n",
    "# Retain lastest obs\n",
    "vv = vv.groupby('icpsr').tail(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Read in DIME"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-25T05:52:00.625846Z",
     "start_time": "2020-10-25T05:51:29.142114Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3146: DtypeWarning: Columns (18,21) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "DIME_IDENTIFIERS = ['cycle', 'name', 'ICPSR2', 'party', 'state', 'seat', 'district']\n",
    "DIME_IDEOLOGIES = ['recipient.cfscore', 'contributor.cfscore', 'dwnom1']\n",
    "DIME_USECOLS = DIME_IDENTIFIERS + DIME_IDEOLOGIES\n",
    "\n",
    "dime = pd.read_csv(os.path.join(RAW_FILEPATH, 'dime_recipients_1979_2014.csv'), usecols=DIME_USECOLS)\n",
    "\n",
    "# # Drop committees (cf codebook p. 17)\n",
    "# dime = dime[~dime['seat'].isin(['federal:committee', 'state:committee', 'federal:527'])]\n",
    "\n",
    "dime.columns = [col.lower() for col in dime.columns]\n",
    "\n",
    "dime.sort_values(by=['cycle', 'icpsr2'], ascending=True, inplace=True)\n",
    "\n",
    "# Fill forward, retain latest scores where available\n",
    "for col in  DIME_IDEOLOGIES:\n",
    "    dime[col] = dime.groupby('name')[col].apply(lambda x: x.ffill())\n",
    "\n",
    "# Remove nominee from ICPSR (only for seem fpr presidential candidates)\n",
    "dime['icpsr2'] = [s.replace('nominee', '') for s in dime['icpsr2']]\n",
    "\n",
    "# Retain last\n",
    "dime = dime.groupby('icpsr2').tail(1)\n",
    "\n",
    "dime.rename(columns={'icpsr2': 'icpsr'}, inplace=True)\n",
    "\n",
    "# Ideology coding\n",
    "ideo_dict = {100: 'D', 200: 'R', 328: 'I'}\n",
    "dime['party'] = dime['party'].map(ideo_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-25T05:52:02.593458Z",
     "start_time": "2020-10-25T05:52:00.626846Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Updated 2018 DIME for congress\n",
    "DIME_UPDATED_IDENTIFIERS = ['cycle', 'Name', 'ICPSR2', 'party', 'state', 'seat', 'district']\n",
    "DIME_UPDATED_IDEOLOGIES = ['recipient_cfscore', 'contributor_cfscore', 'dwnom1']\n",
    "DIME_UPDATED_USECOLS = DIME_UPDATED_IDEOLOGIES + DIME_UPDATED_IDENTIFIERS\n",
    "\n",
    "dime_updated = pd.read_csv(os.path.join(RAW_FILEPATH, 'dime_cong_elections_current.csv'), usecols=DIME_UPDATED_USECOLS)\n",
    "\n",
    "# Drop those before 2014\n",
    "dime_updated = dime_updated[dime_updated['cycle']>2014]\n",
    "\n",
    "# # Drop committees (cf codebook p. 17)\n",
    "# dime_updated = dime_updated[~dime_updated['seat'].isin(['federal:committee', 'state:committee', 'federal:527'])]\n",
    "\n",
    "dime_updated.columns = [col.lower() for col in dime_updated.columns]\n",
    "\n",
    "dime_updated.sort_values(by=['cycle', 'icpsr2'], ascending=True, inplace=True)\n",
    "\n",
    "# Fill forward, retain latest scores where available\n",
    "for col in  DIME_UPDATED_IDEOLOGIES:\n",
    "    dime_updated[col] = dime_updated.groupby('name')[col].apply(lambda x: x.ffill())\n",
    "\n",
    "# Remove nominee from ICPSR (only for seem fpr presidential candidates)\n",
    "dime_updated['icpsr2'] = [s.replace('nominee', '') for s in dime_updated['icpsr2']]\n",
    "\n",
    "# Retain last\n",
    "dime_updated = dime_updated.groupby('icpsr2').tail(1)\n",
    "\n",
    "dime_updated.rename(columns={'icpsr2': 'icpsr',\n",
    "                             'recipient_cfscore': 'recipient.cfscore', \n",
    "                             'contributor_cfscore': 'contributor.cfscore'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-25T05:52:02.766720Z",
     "start_time": "2020-10-25T05:52:02.595458Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "88123"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dime = dime.append(dime_updated, ignore_index=True, sort=True)\n",
    "\n",
    "dime = dime.groupby('icpsr').tail(1)\n",
    "\n",
    "dime['name'] = dime['name'].str.lower()\n",
    "len(dime)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Read in SCTV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-25T05:52:02.782724Z",
     "start_time": "2020-10-25T05:52:02.767719Z"
    },
    "code_folding": [
     0
    ],
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# # find icpsr using name\n",
    "# sctv = pd.read_csv(os.path.join(RAW_FILEPATH, 'sctvna.csv'), encoding = \"ISO-8859-1\")\n",
    "\n",
    "# sctv.columns = ['name', 'presenter', 'minutes']\n",
    "\n",
    "# # drop presenters\n",
    "# sctv = sctv[sctv['presenter'].isnull()]\n",
    "# sctv.drop('presenter', axis=1, inplace=True)\n",
    "\n",
    "# sctv.reset_index(inplace=True, drop=True)\n",
    "#-------------------------------------------------------------------------------------------------\n",
    "# name_to_icpsr = dict()\n",
    "\n",
    "# sctv['status'] = ''\n",
    "\n",
    "# _df = df[['vv_name', 'seat', 'icpsr']]\n",
    "# _df2 = df[['dime_name', 'seat', 'icpsr']]\n",
    "\n",
    "# for ix in range(len(sctv)):\n",
    "#     if pd.notna(sctv.loc[ix, 'icpsr']):\n",
    "#         continue\n",
    "        \n",
    "#     if ix<1073:\n",
    "#         continue\n",
    "                \n",
    "#     name = sctv.loc[ix, 'name']\n",
    "#     clear_output(wait=True)\n",
    "#     print(f\"{ix}/1225: politician is {name}\")\n",
    "#     print(_df[_df['vv_name'].str.lower().str.contains(name.split(' ')[-1], na=False)])\n",
    "#     print('--------------------------')\n",
    "#     print(_df2[_df2['dime_name'].str.lower().str.contains(name.split(' ')[-1], na=False)])\n",
    "    \n",
    "# #     icpsr = input(\"ICPSR no. = \")\n",
    "    \n",
    "#     if icpsr=='br':\n",
    "#         break\n",
    "#     name_to_icpsr.update({name: icpsr})\n",
    "    \n",
    "#     if icpsr=='?':\n",
    "#         sctv.loc[ix, 'status'] = 'no'\n",
    "#     else:\n",
    "#         sctv.loc[ix, 'status'] = 'yes'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-25T05:52:02.798734Z",
     "start_time": "2020-10-25T05:52:02.783726Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "388"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sctv = pd.read_csv(os.path.join('files','name-to-icpsr.csv'))\n",
    "sctv.columns = ['sctv_name', 'minutes', 'icpsr']\n",
    "len(sctv[sctv['icpsr'].notnull()])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-25T05:52:02.894037Z",
     "start_time": "2020-10-25T05:52:02.799734Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df = dime.merge(vv, how='outer', on='icpsr')\n",
    "\n",
    "df.rename(columns={'name': 'dime_name', 'bioname': 'vv_name'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-25T05:52:02.958051Z",
     "start_time": "2020-10-25T05:52:02.895038Z"
    }
   },
   "outputs": [],
   "source": [
    "_pre_merge_count = len(df)\n",
    "\n",
    "df = df.merge(sctv, how='left', on='icpsr')\n",
    "\n",
    "assert _pre_merge_count==len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-25T05:52:03.800746Z",
     "start_time": "2020-10-25T05:52:02.959052Z"
    }
   },
   "outputs": [],
   "source": [
    "df.to_csv(os.path.join('files', 'for-plots.csv'), index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "165px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "oldHeight": 667.77778,
   "position": {
    "height": "40px",
    "left": "1710.81px",
    "right": "20px",
    "top": "59.9583px",
    "width": "350px"
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "varInspector_section_display": "none",
   "window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}