wvs_wb_data_processing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2a3cfdd1-4fce-489f-93f5-0d1aac9f4363",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import wbgapi as wb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c5637a2-5467-4d1a-8ace-3d67ccd0ba10",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "054b8f8e-9749-4aa4-9ce8-affd039f5de0",
   "metadata": {},
   "source": [
    "## WVS"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92f09e24-1e6e-4782-9d21-892c715cbbd8",
   "metadata": {
    "tags": []
   },
   "source": [
    "### load data & create dictionaries for relevant variables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83904e2c-ec85-4be0-bf10-c9b104cffa4f",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### wave 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "dc02ba3c-ffce-4651-a9d5-8af10558efd2",
   "metadata": {},
   "outputs": [],
   "source": [
    "url_w5 = \"/Users/jinyanxiang/Downloads/WV5_Data_csv_v20180912.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "32dbb48f-2aa2-4608-8ebe-aaa65ffd130b",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/r9/fw5bm81j1h72ff0690bt25x80000gn/T/ipykernel_17102/944982171.py:1: DtypeWarning: Columns (351,352,374,377,378,379,407,411) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df_w5 = pd.read_csv(url_w5, sep = ';')\n"
     ]
    }
   ],
   "source": [
    "df_w5 = pd.read_csv(url_w5, sep = ';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "761406c5-a761-4a71-840c-fb6d99353a37",
   "metadata": {},
   "outputs": [],
   "source": [
    "info_dict_w5 = {'V1': \"wave\",\n",
    "                 'V260': \"year\",\n",
    "                 'V2': \"country_iso\",\n",
    "                 #'C_COW_ALPHA': \"country_str\",\n",
    "                 'V3': \"interview_ID\",\n",
    "                 'V261': \"interview_date\",\n",
    "                 #'Q_MODE': \"interview_mode\",\n",
    "                 'V257B': \"region_code\",\n",
    "                 #'N_TOWN': \"settlement_name\",\n",
    "                 'V255': \"settlement_size\",\n",
    "                 #'H_SETTLEMENT': \"settlement_type\",\n",
    "                 'V263': \"urban_rural\",\n",
    "                 'V254': \"res_interest\"}\n",
    "                 #'V252': \"interview_privacy\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e13e04f7-9e8c-4a11-87c0-9d1374e1f6da",
   "metadata": {},
   "outputs": [],
   "source": [
    "trust_dict_w5 = {'V23': \"social_trust\",\n",
    "                 'V125': \"trust_family\",\n",
    "                 'V126': \"trust_neighborhood\",\n",
    "                 'V127': \"trust_personal_known\",\n",
    "                 'V128': \"trust_first_time\",\n",
    "                 'V129': \"trust_diff_religion\",\n",
    "                 'V130': \"trust_diff_country\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ec5eb448-217e-4c45-83c5-71a5411839bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "cov_dict_w5 = {'V252': \"sub_class\",\n",
    "               'V238': \"education\",\n",
    "               'V253': \"income\",\n",
    "               'V235': \"gender\",\n",
    "               'V236': \"year_of_birth\", #year of birth\n",
    "               'V55': 'marital',\n",
    "               'V241': \"employment\",\n",
    "               'V114': \"ideology\"}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b2846d8-e9a9-42ca-a2d0-6bc8bd0ef84e",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### wave 6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "be5cd990-adab-4da0-af20-2162cc7e933a",
   "metadata": {},
   "outputs": [],
   "source": [
    "url_w6 =   \"/Users/jinyanxiang/Downloads/WV6_Data_csv_v20201117.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "87776d41-1fb1-4856-ac28-79881edd05d0",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/r9/fw5bm81j1h72ff0690bt25x80000gn/T/ipykernel_17102/848070417.py:1: DtypeWarning: Columns (340,341,408,411,412,413,414,416,421,422,423,424,426,431,436,441) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df_w6 = pd.read_csv(url_w6, sep = ';')\n"
     ]
    }
   ],
   "source": [
    "df_w6 = pd.read_csv(url_w6, sep = ';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "33e057bd-48fe-4ad1-abeb-a1dcf678fedb",
   "metadata": {},
   "outputs": [],
   "source": [
    "info_dict_w6 = {'V1': \"wave\",\n",
    "                 'V262': \"year\",\n",
    "                 'V2': \"country_iso\",\n",
    "                 'C_COW_ALPHA': \"country_cow\",\n",
    "                 'V3': \"interview_ID\",\n",
    "                 'V261': \"interview_date\",\n",
    "                 #'Q_MODE': \"interview_mode\",\n",
    "                 'V256B': \"region_code\",\n",
    "                 #'N_TOWN': \"settlement_name\",\n",
    "                 'V253': \"settlement_size\",\n",
    "                 #'H_SETTLEMENT': \"settlement_type\",\n",
    "                 #'H_URBRURAL': \"urban_rural\",\n",
    "                 'V251': \"res_interest\",\n",
    "                 'V252': \"interview_privacy\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "44adfa30-2bfc-4a89-abf1-fc96e517a2e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "trust_dict_w6 = {'V24': \"social_trust\",\n",
    "                 'V102': \"trust_family\",\n",
    "                 'V103': \"trust_neighborhood\",\n",
    "                 'V104': \"trust_personal_known\",\n",
    "                 'V105': \"trust_first_time\",\n",
    "                 'V106': \"trust_diff_religion\",\n",
    "                 'V107': \"trust_diff_country\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "0413b74e-2da4-45d4-89c0-59412f00ddfd",
   "metadata": {},
   "outputs": [],
   "source": [
    "cov_dict_w6 = {'V238': \"sub_class\",\n",
    "               'V248': \"education\",\n",
    "               'V239': \"income\",\n",
    "               'V240': \"gender\",\n",
    "               'V241': \"year_of_birth\",\n",
    "               'V57': 'marital',\n",
    "               'V229': \"employment\",\n",
    "               'V95': \"ideology\"}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a99e5f65-cff1-4cf6-a9e1-5fe60cd03c70",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### wave 7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ca04e243-085e-4ca5-91e9-05b8ceede92d",
   "metadata": {},
   "outputs": [],
   "source": [
    "url_w7 = \"/Users/jinyanxiang/Downloads/WVS_Cross-National_Wave_7_csv_v5_0.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ab1582c5-733a-495d-a728-0dde9114a734",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/r9/fw5bm81j1h72ff0690bt25x80000gn/T/ipykernel_17102/2414592701.py:1: DtypeWarning: Columns (345,347,349,574,575,576) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df_w7 = pd.read_csv(url_w7)\n"
     ]
    }
   ],
   "source": [
    "df_w7 = pd.read_csv(url_w7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "560db499-a7fb-4471-bb01-600cb9564e22",
   "metadata": {},
   "outputs": [],
   "source": [
    "info_dict_w7 = {'A_WAVE': \"wave\",\n",
    "                 'A_YEAR': \"year\",\n",
    "                 'B_COUNTRY': \"country_iso\",\n",
    "                 'C_COW_ALPHA': \"country_cow\",\n",
    "                 'D_INTERVIEW': \"interview_ID\",\n",
    "                 'J_INTDATE': \"interview_date\",\n",
    "                 'Q_MODE': \"interview_mode\",\n",
    "                 'N_REGION_ISO': \"region_code\",\n",
    "                 'N_TOWN': \"settlement_name\",\n",
    "                 'G_TOWNSIZE': \"settlement_size\",\n",
    "                 'H_SETTLEMENT': \"settlement_type\",\n",
    "                 'H_URBRURAL': \"urban_rural\",\n",
    "                 'E_RESPINT': \"res_interest\",\n",
    "                 'F_INTPRIVACY': \"interview_privacy\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "1ff36c78-20ad-46ca-b05d-e708abfa1485",
   "metadata": {},
   "outputs": [],
   "source": [
    "trust_dict_w7 = {'Q57': \"social_trust\",\n",
    "                 'Q58': \"trust_family\",\n",
    "                 'Q59': \"trust_neighborhood\",\n",
    "                 'Q60': \"trust_personal_known\",\n",
    "                 'Q61': \"trust_first_time\",\n",
    "                 'Q62': \"trust_diff_religion\",\n",
    "                 'Q63': \"trust_diff_country\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "7ed3124a-b0b1-4842-8af9-47d3af8aa127",
   "metadata": {},
   "outputs": [],
   "source": [
    "cov_dict_w7 = {'Q287': \"sub_class\",\n",
    "               'Q275': \"education\",\n",
    "               'Q288': \"income\",\n",
    "               'Q260': \"gender\",\n",
    "               'Q261': \"year_of_birth\",\n",
    "               'Q273': 'marital',\n",
    "               'Q280': \"employment\",\n",
    "               'Q240': \"ideology\"}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77d7e1ed-70bb-4066-b41a-547d76180e41",
   "metadata": {
    "tags": []
   },
   "source": [
    "### create new dataframe with selected variables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3fe403c-11e1-40ad-967d-cab88c388170",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### function ```CreateSelData```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "6ef5de2c-d7a3-46e2-8ff4-9f46f9ef2ada",
   "metadata": {},
   "outputs": [],
   "source": [
    "def CreateSelData(df, wave):\n",
    "\n",
    "    info_list = 'info_list_' + wave\n",
    "    trust_list = 'trust_list_' + wave\n",
    "    cov_list = 'cov_list_' + wave\n",
    "    \n",
    "    info_dict = globals()['info_dict_' + wave]\n",
    "    trust_dict = globals()['trust_dict_' + wave]\n",
    "    cov_dict = globals()['cov_dict_' + wave]\n",
    "    \n",
    "    locals()[info_list] = list(info_dict.keys())\n",
    "    locals()[trust_list] = list(trust_dict.keys())\n",
    "    locals()[cov_list] = list(cov_dict.keys())\n",
    "    \n",
    "    var_list = locals()[info_list] + locals()[trust_list] + locals()[cov_list]  \n",
    "    var_dict = {**info_dict, **trust_dict, **cov_dict}\n",
    "    \n",
    "    df_sel = df[var_list]\n",
    "    df_sel_final = df_sel.rename(columns=var_dict)\n",
    "    \n",
    "    return df_sel_final"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00cab414-ecd8-43df-96ec-1b611e7909ff",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### wave 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "ef0dcce9-5536-444a-8b1a-1e1f79d761a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_w5_sel = CreateSelData(df_w5,'w5')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "9e75414e-60c0-4611-9483-6c63b47b639d",
   "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>wave</th>\n",
       "      <th>year</th>\n",
       "      <th>country_iso</th>\n",
       "      <th>interview_ID</th>\n",
       "      <th>interview_date</th>\n",
       "      <th>region_code</th>\n",
       "      <th>settlement_size</th>\n",
       "      <th>urban_rural</th>\n",
       "      <th>res_interest</th>\n",
       "      <th>social_trust</th>\n",
       "      <th>...</th>\n",
       "      <th>trust_diff_religion</th>\n",
       "      <th>trust_diff_country</th>\n",
       "      <th>sub_class</th>\n",
       "      <th>education</th>\n",
       "      <th>income</th>\n",
       "      <th>gender</th>\n",
       "      <th>year_of_birth</th>\n",
       "      <th>marital</th>\n",
       "      <th>employment</th>\n",
       "      <th>ideology</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>-4</td>\n",
       "      <td>-4</td>\n",
       "      <td>5</td>\n",
       "      <td>-4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1967</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>2</td>\n",
       "      <td>-4</td>\n",
       "      <td>-4</td>\n",
       "      <td>5</td>\n",
       "      <td>-4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>1980</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>-4</td>\n",
       "      <td>-4</td>\n",
       "      <td>5</td>\n",
       "      <td>-4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1957</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>-4</td>\n",
       "      <td>-4</td>\n",
       "      <td>3</td>\n",
       "      <td>-4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>1977</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>6</td>\n",
       "      <td>-4</td>\n",
       "      <td>-4</td>\n",
       "      <td>3</td>\n",
       "      <td>-4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>1981</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   wave  year  country_iso  interview_ID  interview_date  region_code  \\\n",
       "0     5  2005           20             1              -4           -4   \n",
       "1     5  2005           20             2              -4           -4   \n",
       "2     5  2005           20             3              -4           -4   \n",
       "3     5  2005           20             5              -4           -4   \n",
       "4     5  2005           20             6              -4           -4   \n",
       "\n",
       "   settlement_size  urban_rural  res_interest  social_trust  ...  \\\n",
       "0                5           -4             1             1  ...   \n",
       "1                5           -4             1             2  ...   \n",
       "2                5           -4             1             2  ...   \n",
       "3                3           -4             1             2  ...   \n",
       "4                3           -4             1             2  ...   \n",
       "\n",
       "   trust_diff_religion  trust_diff_country  sub_class  education  income  \\\n",
       "0                    2                   2          4          8       3   \n",
       "1                    3                   3          3          7       6   \n",
       "2                    2                   2          2          9      10   \n",
       "3                    2                   2          3          6       6   \n",
       "4                    2                   2          3          7       8   \n",
       "\n",
       "   gender  year_of_birth  marital  employment  ideology  \n",
       "0       2           1967        6           1         4  \n",
       "1       2           1980        1           1        -2  \n",
       "2       1           1957        1           1         5  \n",
       "3       2           1977        2           1         2  \n",
       "4       2           1981        6           1         5  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_w5_sel.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "cf82e53c-ce09-41a5-887c-d3037ea884bb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 83975 entries, 0 to 83974\n",
      "Data columns (total 24 columns):\n",
      " #   Column                Non-Null Count  Dtype\n",
      "---  ------                --------------  -----\n",
      " 0   wave                  83975 non-null  int64\n",
      " 1   year                  83975 non-null  int64\n",
      " 2   country_iso           83975 non-null  int64\n",
      " 3   interview_ID          83975 non-null  int64\n",
      " 4   interview_date        83975 non-null  int64\n",
      " 5   region_code           83975 non-null  int64\n",
      " 6   settlement_size       83975 non-null  int64\n",
      " 7   urban_rural           83975 non-null  int64\n",
      " 8   res_interest          83975 non-null  int64\n",
      " 9   social_trust          83975 non-null  int64\n",
      " 10  trust_family          83975 non-null  int64\n",
      " 11  trust_neighborhood    83975 non-null  int64\n",
      " 12  trust_personal_known  83975 non-null  int64\n",
      " 13  trust_first_time      83975 non-null  int64\n",
      " 14  trust_diff_religion   83975 non-null  int64\n",
      " 15  trust_diff_country    83975 non-null  int64\n",
      " 16  sub_class             83975 non-null  int64\n",
      " 17  education             83975 non-null  int64\n",
      " 18  income                83975 non-null  int64\n",
      " 19  gender                83975 non-null  int64\n",
      " 20  year_of_birth         83975 non-null  int64\n",
      " 21  marital               83975 non-null  int64\n",
      " 22  employment            83975 non-null  int64\n",
      " 23  ideology              83975 non-null  int64\n",
      "dtypes: int64(24)\n",
      "memory usage: 15.4 MB\n"
     ]
    }
   ],
   "source": [
    "df_w5_sel.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91e83b4f-de1e-4074-aca2-543a5d70cebe",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### wave 6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "153bb8e2-66a0-4c05-b822-e34be6e3fe94",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_w6_sel = CreateSelData(df_w6,'w6')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "77198a2c-0f46-4900-836d-01995065da11",
   "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>wave</th>\n",
       "      <th>year</th>\n",
       "      <th>country_iso</th>\n",
       "      <th>country_cow</th>\n",
       "      <th>interview_ID</th>\n",
       "      <th>interview_date</th>\n",
       "      <th>region_code</th>\n",
       "      <th>settlement_size</th>\n",
       "      <th>res_interest</th>\n",
       "      <th>interview_privacy</th>\n",
       "      <th>...</th>\n",
       "      <th>trust_diff_religion</th>\n",
       "      <th>trust_diff_country</th>\n",
       "      <th>sub_class</th>\n",
       "      <th>education</th>\n",
       "      <th>income</th>\n",
       "      <th>gender</th>\n",
       "      <th>year_of_birth</th>\n",
       "      <th>marital</th>\n",
       "      <th>employment</th>\n",
       "      <th>ideology</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>2014</td>\n",
       "      <td>12</td>\n",
       "      <td>ALG</td>\n",
       "      <td>1</td>\n",
       "      <td>-4</td>\n",
       "      <td>-4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1993</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>2014</td>\n",
       "      <td>12</td>\n",
       "      <td>ALG</td>\n",
       "      <td>2</td>\n",
       "      <td>-4</td>\n",
       "      <td>-4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>1990</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>2014</td>\n",
       "      <td>12</td>\n",
       "      <td>ALG</td>\n",
       "      <td>3</td>\n",
       "      <td>-4</td>\n",
       "      <td>-4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>1988</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>2014</td>\n",
       "      <td>12</td>\n",
       "      <td>ALG</td>\n",
       "      <td>4</td>\n",
       "      <td>-4</td>\n",
       "      <td>-4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1986</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>2014</td>\n",
       "      <td>12</td>\n",
       "      <td>ALG</td>\n",
       "      <td>5</td>\n",
       "      <td>-4</td>\n",
       "      <td>-4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>1979</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   wave  year  country_iso country_cow  interview_ID  interview_date  \\\n",
       "0     6  2014           12         ALG             1              -4   \n",
       "1     6  2014           12         ALG             2              -4   \n",
       "2     6  2014           12         ALG             3              -4   \n",
       "3     6  2014           12         ALG             4              -4   \n",
       "4     6  2014           12         ALG             5              -4   \n",
       "\n",
       "   region_code  settlement_size  res_interest  interview_privacy  ...  \\\n",
       "0           -4                1             2                  2  ...   \n",
       "1           -4                1             1                  2  ...   \n",
       "2           -4                1             1                  1  ...   \n",
       "3           -4                1             2                  1  ...   \n",
       "4           -4                1             1                  1  ...   \n",
       "\n",
       "   trust_diff_religion  trust_diff_country  sub_class  education  income  \\\n",
       "0                    2                   2          4          7       5   \n",
       "1                    3                   3          3          7       6   \n",
       "2                    4                   4          4          5       6   \n",
       "3                    2                   3          4          6       5   \n",
       "4                    2                   3          3          3       7   \n",
       "\n",
       "   gender  year_of_birth  marital  employment  ideology  \n",
       "0       1           1993        6           6         5  \n",
       "1       2           1990        6           6         5  \n",
       "2       2           1988        6           3         6  \n",
       "3       2           1986        6           1         6  \n",
       "4       2           1979        1           3         5  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_w6_sel.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "1bd6d782-f8b6-4fd7-84ed-a50c1775acca",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 89565 entries, 0 to 89564\n",
      "Data columns (total 25 columns):\n",
      " #   Column                Non-Null Count  Dtype \n",
      "---  ------                --------------  ----- \n",
      " 0   wave                  89565 non-null  int64 \n",
      " 1   year                  89565 non-null  int64 \n",
      " 2   country_iso           89565 non-null  int64 \n",
      " 3   country_cow           89565 non-null  object\n",
      " 4   interview_ID          89565 non-null  int64 \n",
      " 5   interview_date        89565 non-null  int64 \n",
      " 6   region_code           89565 non-null  int64 \n",
      " 7   settlement_size       89565 non-null  int64 \n",
      " 8   res_interest          89565 non-null  int64 \n",
      " 9   interview_privacy     89565 non-null  int64 \n",
      " 10  social_trust          89565 non-null  int64 \n",
      " 11  trust_family          89565 non-null  int64 \n",
      " 12  trust_neighborhood    89565 non-null  int64 \n",
      " 13  trust_personal_known  89565 non-null  int64 \n",
      " 14  trust_first_time      89565 non-null  int64 \n",
      " 15  trust_diff_religion   89565 non-null  int64 \n",
      " 16  trust_diff_country    89565 non-null  int64 \n",
      " 17  sub_class             89565 non-null  int64 \n",
      " 18  education             89565 non-null  int64 \n",
      " 19  income                89565 non-null  int64 \n",
      " 20  gender                89565 non-null  int64 \n",
      " 21  year_of_birth         89565 non-null  int64 \n",
      " 22  marital               89565 non-null  int64 \n",
      " 23  employment            89565 non-null  int64 \n",
      " 24  ideology              89565 non-null  int64 \n",
      "dtypes: int64(24), object(1)\n",
      "memory usage: 17.1+ MB\n"
     ]
    }
   ],
   "source": [
    "df_w6_sel.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1968b6df-1b75-4435-b451-b0dda59c37b5",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### wave 7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "4bc2a340-ee3b-4fb8-840a-1f21e58b26d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_w7_sel = CreateSelData(df_w7,'w7')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "1b1ab998-061a-4009-8b11-d9255c563bf5",
   "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>wave</th>\n",
       "      <th>year</th>\n",
       "      <th>country_iso</th>\n",
       "      <th>country_cow</th>\n",
       "      <th>interview_ID</th>\n",
       "      <th>interview_date</th>\n",
       "      <th>interview_mode</th>\n",
       "      <th>region_code</th>\n",
       "      <th>settlement_name</th>\n",
       "      <th>settlement_size</th>\n",
       "      <th>...</th>\n",
       "      <th>trust_diff_religion</th>\n",
       "      <th>trust_diff_country</th>\n",
       "      <th>sub_class</th>\n",
       "      <th>education</th>\n",
       "      <th>income</th>\n",
       "      <th>gender</th>\n",
       "      <th>year_of_birth</th>\n",
       "      <th>marital</th>\n",
       "      <th>employment</th>\n",
       "      <th>ideology</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>20</td>\n",
       "      <td>AND</td>\n",
       "      <td>20070001</td>\n",
       "      <td>20180704</td>\n",
       "      <td>2</td>\n",
       "      <td>20007</td>\n",
       "      <td>20005</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1958</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>20</td>\n",
       "      <td>AND</td>\n",
       "      <td>20070002</td>\n",
       "      <td>20180714</td>\n",
       "      <td>2</td>\n",
       "      <td>20003</td>\n",
       "      <td>20002</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>1971</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>20</td>\n",
       "      <td>AND</td>\n",
       "      <td>20070003</td>\n",
       "      <td>20180704</td>\n",
       "      <td>2</td>\n",
       "      <td>20003</td>\n",
       "      <td>20002</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1969</td>\n",
       "      <td>4</td>\n",
       "      <td>-3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>20</td>\n",
       "      <td>AND</td>\n",
       "      <td>20070004</td>\n",
       "      <td>20180702</td>\n",
       "      <td>2</td>\n",
       "      <td>20003</td>\n",
       "      <td>20002</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1956</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>20</td>\n",
       "      <td>AND</td>\n",
       "      <td>20070005</td>\n",
       "      <td>20180708</td>\n",
       "      <td>2</td>\n",
       "      <td>20003</td>\n",
       "      <td>20002</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1969</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   wave  year  country_iso country_cow  interview_ID  interview_date  \\\n",
       "0     7  2018           20         AND      20070001        20180704   \n",
       "1     7  2018           20         AND      20070002        20180714   \n",
       "2     7  2018           20         AND      20070003        20180704   \n",
       "3     7  2018           20         AND      20070004        20180702   \n",
       "4     7  2018           20         AND      20070005        20180708   \n",
       "\n",
       "   interview_mode  region_code  settlement_name  settlement_size  ...  \\\n",
       "0               2        20007            20005                5  ...   \n",
       "1               2        20003            20002                3  ...   \n",
       "2               2        20003            20002                3  ...   \n",
       "3               2        20003            20002                3  ...   \n",
       "4               2        20003            20002                3  ...   \n",
       "\n",
       "   trust_diff_religion  trust_diff_country  sub_class  education  income  \\\n",
       "0                    2                   2          3          3       5   \n",
       "1                    2                   2          2          7       9   \n",
       "2                    3                   3          3          7       5   \n",
       "3                    3                   3          4          2       4   \n",
       "4                    3                   3          4          2       4   \n",
       "\n",
       "   gender  year_of_birth  marital  employment  ideology  \n",
       "0       2           1958        1           1         6  \n",
       "1       1           1971        2           1         5  \n",
       "2       1           1969        4          -3         7  \n",
       "3       2           1956        2           1         5  \n",
       "4       1           1969        2           1         6  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_w7_sel.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "497f794b-817e-4fc3-a1c8-89c0d4600eaa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 94278 entries, 0 to 94277\n",
      "Data columns (total 29 columns):\n",
      " #   Column                Non-Null Count  Dtype \n",
      "---  ------                --------------  ----- \n",
      " 0   wave                  94278 non-null  int64 \n",
      " 1   year                  94278 non-null  int64 \n",
      " 2   country_iso           94278 non-null  int64 \n",
      " 3   country_cow           94278 non-null  object\n",
      " 4   interview_ID          94278 non-null  int64 \n",
      " 5   interview_date        94278 non-null  int64 \n",
      " 6   interview_mode        94278 non-null  int64 \n",
      " 7   region_code           94278 non-null  int64 \n",
      " 8   settlement_name       94278 non-null  int64 \n",
      " 9   settlement_size       94278 non-null  int64 \n",
      " 10  settlement_type       94278 non-null  int64 \n",
      " 11  urban_rural           94278 non-null  int64 \n",
      " 12  res_interest          94278 non-null  int64 \n",
      " 13  interview_privacy     94278 non-null  int64 \n",
      " 14  social_trust          94278 non-null  int64 \n",
      " 15  trust_family          94278 non-null  int64 \n",
      " 16  trust_neighborhood    94278 non-null  int64 \n",
      " 17  trust_personal_known  94278 non-null  int64 \n",
      " 18  trust_first_time      94278 non-null  int64 \n",
      " 19  trust_diff_religion   94278 non-null  int64 \n",
      " 20  trust_diff_country    94278 non-null  int64 \n",
      " 21  sub_class             94278 non-null  int64 \n",
      " 22  education             94278 non-null  int64 \n",
      " 23  income                94278 non-null  int64 \n",
      " 24  gender                94278 non-null  int64 \n",
      " 25  year_of_birth         94278 non-null  int64 \n",
      " 26  marital               94278 non-null  int64 \n",
      " 27  employment            94278 non-null  int64 \n",
      " 28  ideology              94278 non-null  int64 \n",
      "dtypes: int64(28), object(1)\n",
      "memory usage: 20.9+ MB\n"
     ]
    }
   ],
   "source": [
    "df_w7_sel.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "989660e8-f588-46e1-a6bd-f16ce5342953",
   "metadata": {
    "tags": []
   },
   "source": [
    "### check duplicated responses ```CheckDupID```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "ab48b4c0-02b1-404b-832c-0e7715bb8b5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def CheckDupID(df):\n",
    "    grouped = df.groupby(['country_iso','year', 'interview_ID']).size().reset_index(name='count')\n",
    "    \n",
    "    total_responses = df.groupby(['country_iso','year']).size().reset_index(name='total_count')\n",
    "    \n",
    "    merged = pd.merge(grouped, total_responses, on=['country_iso','year'])\n",
    "    \n",
    "    duplicated_responses = merged[merged['count'] > 1].groupby(['country_iso','year'])['count'].sum().reset_index(name='dup_count')\n",
    "    \n",
    "    result = pd.merge(total_responses, duplicated_responses, on=['country_iso', 'year'], how='left').fillna(0)\n",
    "    \n",
    "    result['dup_percent'] = round(result['dup_count']/result['total_count']*100,2)\n",
    "    \n",
    "    result_dup = result.query('dup_count > 0')\n",
    "    \n",
    "    return result_dup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "27520084-ba0c-47ea-be8f-715ca46b8128",
   "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_iso</th>\n",
       "      <th>year</th>\n",
       "      <th>total_count</th>\n",
       "      <th>dup_count</th>\n",
       "      <th>dup_percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>158</td>\n",
       "      <td>2006</td>\n",
       "      <td>1227</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>170</td>\n",
       "      <td>2005</td>\n",
       "      <td>3025</td>\n",
       "      <td>86.0</td>\n",
       "      <td>2.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>231</td>\n",
       "      <td>2007</td>\n",
       "      <td>1500</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>356</td>\n",
       "      <td>2006</td>\n",
       "      <td>2001</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>368</td>\n",
       "      <td>2006</td>\n",
       "      <td>2701</td>\n",
       "      <td>2688.0</td>\n",
       "      <td>99.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>504</td>\n",
       "      <td>2007</td>\n",
       "      <td>1200</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>704</td>\n",
       "      <td>2006</td>\n",
       "      <td>1495</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>724</td>\n",
       "      <td>2007</td>\n",
       "      <td>1200</td>\n",
       "      <td>52.0</td>\n",
       "      <td>4.33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    country_iso  year  total_count  dup_count  dup_percent\n",
       "8           158  2006         1227        4.0         0.33\n",
       "9           170  2005         3025       86.0         2.84\n",
       "11          231  2007         1500        2.0         0.13\n",
       "20          356  2006         2001       20.0         1.00\n",
       "23          368  2006         2701     2688.0        99.52\n",
       "32          504  2007         1200        4.0         0.33\n",
       "42          704  2006         1495        4.0         0.27\n",
       "45          724  2007         1200       52.0         4.33"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CheckDupID(df_w5_sel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "f2def074-f423-4fa5-9286-2a5e1009f7bd",
   "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_iso</th>\n",
       "      <th>year</th>\n",
       "      <th>total_count</th>\n",
       "      <th>dup_count</th>\n",
       "      <th>dup_percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>51</td>\n",
       "      <td>2011</td>\n",
       "      <td>1100</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>196</td>\n",
       "      <td>2011</td>\n",
       "      <td>1000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>528</td>\n",
       "      <td>2012</td>\n",
       "      <td>1902</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>566</td>\n",
       "      <td>2012</td>\n",
       "      <td>1759</td>\n",
       "      <td>353.0</td>\n",
       "      <td>20.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>702</td>\n",
       "      <td>2012</td>\n",
       "      <td>1972</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>780</td>\n",
       "      <td>2010</td>\n",
       "      <td>999</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>804</td>\n",
       "      <td>2011</td>\n",
       "      <td>1500</td>\n",
       "      <td>1499.0</td>\n",
       "      <td>99.93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    country_iso  year  total_count  dup_count  dup_percent\n",
       "4            51  2011         1100        2.0         0.18\n",
       "11          196  2011         1000        2.0         0.20\n",
       "33          528  2012         1902        2.0         0.11\n",
       "35          566  2012         1759      353.0        20.07\n",
       "44          702  2012         1972        2.0         0.10\n",
       "51          780  2010          999        2.0         0.20\n",
       "54          804  2011         1500     1499.0        99.93"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CheckDupID(df_w6_sel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "700ab91b-75e5-4805-878d-2f0d9569b971",
   "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_iso</th>\n",
       "      <th>year</th>\n",
       "      <th>total_count</th>\n",
       "      <th>dup_count</th>\n",
       "      <th>dup_percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>858</td>\n",
       "      <td>2022</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    country_iso  year  total_count  dup_count  dup_percent\n",
       "61          858  2022         1000     1000.0        100.0"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CheckDupID(df_w7_sel)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67a46d70-387a-46c8-8523-9358ee62078f",
   "metadata": {
    "tags": []
   },
   "source": [
    "### drop duplicates ```DropDupID```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "77d26b35-a671-4d47-91cb-3eb4f9fed757",
   "metadata": {},
   "outputs": [],
   "source": [
    "def DropDupID(df):\n",
    "    df = df[~df.duplicated(subset=['country_iso','year', 'interview_ID'], keep='first')]\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "816b38a8-3c41-4b21-8a46-2db0e1585ab6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_w5_sel = DropDupID(df_w5_sel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "bd6b0266-bc1e-4f58-8944-4c6de9f65e83",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_w6_sel = DropDupID(df_w6_sel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "03f3dcbc-48ae-4b51-a22f-ea998006acab",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_w7_sel = DropDupID(df_w7_sel)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d88ce2ab-803d-4c88-bcc7-e7c3a204e433",
   "metadata": {},
   "source": [
    "#### recheck duplicated ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "5ed224f9-e4e4-48cc-bbd3-2f74207ae1f6",
   "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_iso</th>\n",
       "      <th>year</th>\n",
       "      <th>total_count</th>\n",
       "      <th>dup_count</th>\n",
       "      <th>dup_percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [country_iso, year, total_count, dup_count, dup_percent]\n",
       "Index: []"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CheckDupID(df_w5_sel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "ccd38961-4658-4ec9-bc1a-ebd69f81d459",
   "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_iso</th>\n",
       "      <th>year</th>\n",
       "      <th>total_count</th>\n",
       "      <th>dup_count</th>\n",
       "      <th>dup_percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [country_iso, year, total_count, dup_count, dup_percent]\n",
       "Index: []"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CheckDupID(df_w6_sel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "fef5b8ef-7662-49a8-966c-259df02ab38f",
   "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_iso</th>\n",
       "      <th>year</th>\n",
       "      <th>total_count</th>\n",
       "      <th>dup_count</th>\n",
       "      <th>dup_percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [country_iso, year, total_count, dup_count, dup_percent]\n",
       "Index: []"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CheckDupID(df_w7_sel)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57b06ebf-1b19-4cdb-8c49-46f8edf70c1e",
   "metadata": {
    "tags": []
   },
   "source": [
    "### check frequencies of variables ```CheckFreq```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "69b3017f-4178-49da-a25e-a78334745489",
   "metadata": {},
   "outputs": [],
   "source": [
    "def CheckFreq(df):\n",
    "    frequency_tables = {}\n",
    "    \n",
    "    for column in df.columns:\n",
    "        frequency_tables[column] = df[column].value_counts()\n",
    "    \n",
    "    for column, frequencies in frequency_tables.items():\n",
    "        print(f\"Frequency table for {column}:\")\n",
    "        print(frequencies)\n",
    "        print()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e62a4dbd-44a2-48f5-a896-6b559aeb5bad",
   "metadata": {
    "tags": []
   },
   "source": [
    "### replace negative values (i.e., not asked, not applicable, no answer, do not know) with np.nan ```ReplaceNA```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "202465e1-6715-49b6-9895-b214d7531969",
   "metadata": {},
   "outputs": [],
   "source": [
    "def ReplaceNA(df):\n",
    "\n",
    "    return df.applymap(lambda x: np.nan if isinstance(x, int) and x <= 0 else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "4dead8cd-f71d-4b45-a5bc-0eb58d947f62",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_w5_sel = ReplaceNA(df_w5_sel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "b3ff5dd8-26fa-47d6-972e-994d5d22c8d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_w6_sel = ReplaceNA(df_w6_sel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "452308b0-5c6b-4f35-b907-f299e9938b6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_w7_sel = ReplaceNA(df_w7_sel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "5818215a-4a99-41bd-a4e9-2a780b8ca445",
   "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>wave</th>\n",
       "      <th>year</th>\n",
       "      <th>country_iso</th>\n",
       "      <th>interview_ID</th>\n",
       "      <th>interview_date</th>\n",
       "      <th>region_code</th>\n",
       "      <th>settlement_size</th>\n",
       "      <th>urban_rural</th>\n",
       "      <th>res_interest</th>\n",
       "      <th>social_trust</th>\n",
       "      <th>...</th>\n",
       "      <th>trust_diff_religion</th>\n",
       "      <th>trust_diff_country</th>\n",
       "      <th>sub_class</th>\n",
       "      <th>education</th>\n",
       "      <th>income</th>\n",
       "      <th>gender</th>\n",
       "      <th>year_of_birth</th>\n",
       "      <th>marital</th>\n",
       "      <th>employment</th>\n",
       "      <th>ideology</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1967.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1957.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1977.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1981.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   wave  year  country_iso  interview_ID  interview_date  region_code  \\\n",
       "0     5  2005           20           1.0             NaN          NaN   \n",
       "1     5  2005           20           2.0             NaN          NaN   \n",
       "2     5  2005           20           3.0             NaN          NaN   \n",
       "3     5  2005           20           5.0             NaN          NaN   \n",
       "4     5  2005           20           6.0             NaN          NaN   \n",
       "\n",
       "   settlement_size  urban_rural  res_interest  social_trust  ...  \\\n",
       "0              5.0          NaN           1.0           1.0  ...   \n",
       "1              5.0          NaN           1.0           2.0  ...   \n",
       "2              5.0          NaN           1.0           2.0  ...   \n",
       "3              3.0          NaN           1.0           2.0  ...   \n",
       "4              3.0          NaN           1.0           2.0  ...   \n",
       "\n",
       "   trust_diff_religion  trust_diff_country  sub_class  education  income  \\\n",
       "0                  2.0                 2.0        4.0        8.0     3.0   \n",
       "1                  3.0                 3.0        3.0        7.0     6.0   \n",
       "2                  2.0                 2.0        2.0        9.0    10.0   \n",
       "3                  2.0                 2.0        3.0        6.0     6.0   \n",
       "4                  2.0                 2.0        3.0        7.0     8.0   \n",
       "\n",
       "   gender  year_of_birth  marital  employment  ideology  \n",
       "0     2.0         1967.0      6.0         1.0       4.0  \n",
       "1     2.0         1980.0      1.0         1.0       NaN  \n",
       "2     1.0         1957.0      1.0         1.0       5.0  \n",
       "3     2.0         1977.0      2.0         1.0       2.0  \n",
       "4     2.0         1981.0      6.0         1.0       5.0  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_w5_sel.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "0f1dcbe4-5fb3-41af-9876-2df171ca72fa",
   "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>wave</th>\n",
       "      <th>year</th>\n",
       "      <th>country_iso</th>\n",
       "      <th>country_cow</th>\n",
       "      <th>interview_ID</th>\n",
       "      <th>interview_date</th>\n",
       "      <th>region_code</th>\n",
       "      <th>settlement_size</th>\n",
       "      <th>res_interest</th>\n",
       "      <th>interview_privacy</th>\n",
       "      <th>...</th>\n",
       "      <th>trust_diff_religion</th>\n",
       "      <th>trust_diff_country</th>\n",
       "      <th>sub_class</th>\n",
       "      <th>education</th>\n",
       "      <th>income</th>\n",
       "      <th>gender</th>\n",
       "      <th>year_of_birth</th>\n",
       "      <th>marital</th>\n",
       "      <th>employment</th>\n",
       "      <th>ideology</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>2014</td>\n",
       "      <td>12</td>\n",
       "      <td>ALG</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>2014</td>\n",
       "      <td>12</td>\n",
       "      <td>ALG</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>2014</td>\n",
       "      <td>12</td>\n",
       "      <td>ALG</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>2014</td>\n",
       "      <td>12</td>\n",
       "      <td>ALG</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1986.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>2014</td>\n",
       "      <td>12</td>\n",
       "      <td>ALG</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1979.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   wave  year  country_iso country_cow  interview_ID  interview_date  \\\n",
       "0     6  2014           12         ALG             1             NaN   \n",
       "1     6  2014           12         ALG             2             NaN   \n",
       "2     6  2014           12         ALG             3             NaN   \n",
       "3     6  2014           12         ALG             4             NaN   \n",
       "4     6  2014           12         ALG             5             NaN   \n",
       "\n",
       "   region_code  settlement_size  res_interest  interview_privacy  ...  \\\n",
       "0          NaN              1.0           2.0                2.0  ...   \n",
       "1          NaN              1.0           1.0                2.0  ...   \n",
       "2          NaN              1.0           1.0                1.0  ...   \n",
       "3          NaN              1.0           2.0                1.0  ...   \n",
       "4          NaN              1.0           1.0                1.0  ...   \n",
       "\n",
       "   trust_diff_religion  trust_diff_country  sub_class  education  income  \\\n",
       "0                  2.0                 2.0        4.0        7.0     5.0   \n",
       "1                  3.0                 3.0        3.0        7.0     6.0   \n",
       "2                  4.0                 4.0        4.0        5.0     6.0   \n",
       "3                  2.0                 3.0        4.0        6.0     5.0   \n",
       "4                  2.0                 3.0        3.0        3.0     7.0   \n",
       "\n",
       "   gender  year_of_birth  marital  employment  ideology  \n",
       "0     1.0         1993.0      6.0         6.0       5.0  \n",
       "1     2.0         1990.0      6.0         6.0       5.0  \n",
       "2     2.0         1988.0      6.0         3.0       6.0  \n",
       "3     2.0         1986.0      6.0         1.0       6.0  \n",
       "4     2.0         1979.0      1.0         3.0       5.0  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_w6_sel.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "fe63dc3a-aec4-4830-a4ff-396720f482c8",
   "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>wave</th>\n",
       "      <th>year</th>\n",
       "      <th>country_iso</th>\n",
       "      <th>country_cow</th>\n",
       "      <th>interview_ID</th>\n",
       "      <th>interview_date</th>\n",
       "      <th>interview_mode</th>\n",
       "      <th>region_code</th>\n",
       "      <th>settlement_name</th>\n",
       "      <th>settlement_size</th>\n",
       "      <th>...</th>\n",
       "      <th>trust_diff_religion</th>\n",
       "      <th>trust_diff_country</th>\n",
       "      <th>sub_class</th>\n",
       "      <th>education</th>\n",
       "      <th>income</th>\n",
       "      <th>gender</th>\n",
       "      <th>year_of_birth</th>\n",
       "      <th>marital</th>\n",
       "      <th>employment</th>\n",
       "      <th>ideology</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>20</td>\n",
       "      <td>AND</td>\n",
       "      <td>20070001</td>\n",
       "      <td>20180704.0</td>\n",
       "      <td>2</td>\n",
       "      <td>20007.0</td>\n",
       "      <td>20005.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1958.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>20</td>\n",
       "      <td>AND</td>\n",
       "      <td>20070002</td>\n",
       "      <td>20180714.0</td>\n",
       "      <td>2</td>\n",
       "      <td>20003.0</td>\n",
       "      <td>20002.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1971.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>20</td>\n",
       "      <td>AND</td>\n",
       "      <td>20070003</td>\n",
       "      <td>20180704.0</td>\n",
       "      <td>2</td>\n",
       "      <td>20003.0</td>\n",
       "      <td>20002.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1969.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>20</td>\n",
       "      <td>AND</td>\n",
       "      <td>20070004</td>\n",
       "      <td>20180702.0</td>\n",
       "      <td>2</td>\n",
       "      <td>20003.0</td>\n",
       "      <td>20002.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1956.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>20</td>\n",
       "      <td>AND</td>\n",
       "      <td>20070005</td>\n",
       "      <td>20180708.0</td>\n",
       "      <td>2</td>\n",
       "      <td>20003.0</td>\n",
       "      <td>20002.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1969.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   wave  year  country_iso country_cow  interview_ID  interview_date  \\\n",
       "0     7  2018           20         AND      20070001      20180704.0   \n",
       "1     7  2018           20         AND      20070002      20180714.0   \n",
       "2     7  2018           20         AND      20070003      20180704.0   \n",
       "3     7  2018           20         AND      20070004      20180702.0   \n",
       "4     7  2018           20         AND      20070005      20180708.0   \n",
       "\n",
       "   interview_mode  region_code  settlement_name  settlement_size  ...  \\\n",
       "0               2      20007.0          20005.0              5.0  ...   \n",
       "1               2      20003.0          20002.0              3.0  ...   \n",
       "2               2      20003.0          20002.0              3.0  ...   \n",
       "3               2      20003.0          20002.0              3.0  ...   \n",
       "4               2      20003.0          20002.0              3.0  ...   \n",
       "\n",
       "   trust_diff_religion  trust_diff_country  sub_class  education  income  \\\n",
       "0                  2.0                 2.0        3.0        3.0     5.0   \n",
       "1                  2.0                 2.0        2.0        7.0     9.0   \n",
       "2                  3.0                 3.0        3.0        7.0     5.0   \n",
       "3                  3.0                 3.0        4.0        2.0     4.0   \n",
       "4                  3.0                 3.0        4.0        2.0     4.0   \n",
       "\n",
       "   gender  year_of_birth  marital  employment  ideology  \n",
       "0     2.0         1958.0      1.0         1.0       6.0  \n",
       "1     1.0         1971.0      2.0         1.0       5.0  \n",
       "2     1.0         1969.0      4.0         NaN       7.0  \n",
       "3     2.0         1956.0      2.0         1.0       5.0  \n",
       "4     1.0         1969.0      2.0         1.0       6.0  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_w7_sel.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f47841f-0974-4049-becd-7b07890bb4dc",
   "metadata": {
    "tags": []
   },
   "source": [
    "### merge all waves"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "1732e08e-9a2e-4784-aa3b-4b468a2260b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wvs = pd.concat([df_w5_sel, df_w6_sel, df_w7_sel], axis = 0, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "a00979fb-6824-4372-a0b7-71493cf10d08",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wvs = df_wvs.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05368845-97e3-4aca-9cb4-64b7c23c9fc5",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### create new variables ```year_country```, ```age```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "a44f0d2d-40ac-4909-a969-d181ad8804d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wvs['year_country'] = df_wvs['year'].astype(str) + df_wvs['country_iso'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "d6bffdf6-9127-48ed-a69f-f53b900c89df",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wvs['year_country'] = df_wvs['year_country'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "38b7bb0e-c0d3-4292-ad4c-530f00e8b8f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wvs['age'] = df_wvs['year'] - df_wvs['year_of_birth']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe7dce9a-4e18-4018-a025-9dff24c9ebd8",
   "metadata": {},
   "source": [
    "#### recode trust-related dvs to be 1 = dot not trust at all, 4 = completely trust"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "9ce30003-c084-436d-9a74-b2366f6be4a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "trust_var_list = ['social_trust', 'trust_family', \n",
    "                  'trust_neighborhood', 'trust_personal_known',\n",
    "                  'trust_first_time', 'trust_diff_religion',\n",
    "                  'trust_diff_country']\n",
    "\n",
    "for trust_var in trust_var_list:\n",
    "    trust_var_recoded = trust_var+'_recoded'\n",
    "    df_wvs[trust_var_recoded] = df_wvs[trust_var].apply(lambda x:5-x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "8030cb7d-2e15-4629-ac08-251ed784f470",
   "metadata": {},
   "outputs": [],
   "source": [
    "#### recode social class to be 1 = lower class, 5 = upper class\n",
    "df_wvs['sub_class_recoded'] = df_wvs['sub_class'].apply(lambda x:6-x)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92566336-2b17-4a5e-a3ef-5705e261615d",
   "metadata": {},
   "source": [
    "#### add country abbreviation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "3df8d5fc-ef37-471c-a418-9f010b15ff9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_iso = pd.read_csv(\"/Users/jinyanxiang/Downloads/iso_codes.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "9299aca2-8157-4c59-b49c-914fd57da9eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wvs_master = df_wvs.merge(df_iso)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "c3ebaea7-a2dc-41fb-bde4-b8bd6e689536",
   "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>index</th>\n",
       "      <th>wave</th>\n",
       "      <th>year</th>\n",
       "      <th>country_iso</th>\n",
       "      <th>interview_ID</th>\n",
       "      <th>interview_date</th>\n",
       "      <th>region_code</th>\n",
       "      <th>settlement_size</th>\n",
       "      <th>urban_rural</th>\n",
       "      <th>res_interest</th>\n",
       "      <th>...</th>\n",
       "      <th>social_trust_recoded</th>\n",
       "      <th>trust_family_recoded</th>\n",
       "      <th>trust_neighborhood_recoded</th>\n",
       "      <th>trust_personal_known_recoded</th>\n",
       "      <th>trust_first_time_recoded</th>\n",
       "      <th>trust_diff_religion_recoded</th>\n",
       "      <th>trust_diff_country_recoded</th>\n",
       "      <th>sub_class_recoded</th>\n",
       "      <th>country_name</th>\n",
       "      <th>country_iso_str</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  wave  year  country_iso  interview_ID  interview_date  region_code  \\\n",
       "0      0     5  2005           20           1.0             NaN          NaN   \n",
       "1      1     5  2005           20           2.0             NaN          NaN   \n",
       "2      2     5  2005           20           3.0             NaN          NaN   \n",
       "3      3     5  2005           20           5.0             NaN          NaN   \n",
       "4      4     5  2005           20           6.0             NaN          NaN   \n",
       "\n",
       "   settlement_size  urban_rural  res_interest  ...  social_trust_recoded  \\\n",
       "0              5.0          NaN           1.0  ...                   4.0   \n",
       "1              5.0          NaN           1.0  ...                   3.0   \n",
       "2              5.0          NaN           1.0  ...                   3.0   \n",
       "3              3.0          NaN           1.0  ...                   3.0   \n",
       "4              3.0          NaN           1.0  ...                   3.0   \n",
       "\n",
       "   trust_family_recoded  trust_neighborhood_recoded  \\\n",
       "0                   4.0                         3.0   \n",
       "1                   4.0                         1.0   \n",
       "2                   4.0                         3.0   \n",
       "3                   4.0                         3.0   \n",
       "4                   4.0                         3.0   \n",
       "\n",
       "   trust_personal_known_recoded  trust_first_time_recoded  \\\n",
       "0                           4.0                       3.0   \n",
       "1                           1.0                       1.0   \n",
       "2                           3.0                       2.0   \n",
       "3                           3.0                       1.0   \n",
       "4                           4.0                       3.0   \n",
       "\n",
       "   trust_diff_religion_recoded  trust_diff_country_recoded  sub_class_recoded  \\\n",
       "0                          3.0                         3.0                2.0   \n",
       "1                          2.0                         2.0                3.0   \n",
       "2                          3.0                         3.0                4.0   \n",
       "3                          3.0                         3.0                3.0   \n",
       "4                          3.0                         3.0                3.0   \n",
       "\n",
       "   country_name  country_iso_str  \n",
       "0       Andorra              AND  \n",
       "1       Andorra              AND  \n",
       "2       Andorra              AND  \n",
       "3       Andorra              AND  \n",
       "4       Andorra              AND  \n",
       "\n",
       "[5 rows x 42 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_wvs_master.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b516a7e5-fdad-432e-8eca-b4af162e76f8",
   "metadata": {},
   "source": [
    "## WORLD BANK"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb443d16-3df7-4701-832c-6b875a473428",
   "metadata": {
    "tags": []
   },
   "source": [
    "### check relevant variables using ```wbgapi```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "e88e8cf8-905b-4e49-875b-720bb7083a1e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id           value\n",
      "-----------  ----------\n",
      "SI.POV.GINI  Gini index\n",
      "             1 elements\n"
     ]
    }
   ],
   "source": [
    "print(wb.series.info(q = 'gini'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "c5a88c95-aec2-474c-beb2-f253a1276636",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id                 value\n",
      "-----------------  ---------------------------------------------------------------------------------------------------------------------------\n",
      "SH.UHC.NOP1.CG     Increase in poverty gap at $1.90 ($ 2011 PPP) poverty line due to out-of-pocket health care expenditure (USD)\n",
      "SH.UHC.NOP1.TO     Number of people pushed below the $2.15 ($ 2017 PPP) poverty line by out-of-pocket health care expenditure\n",
      "SH.UHC.NOP1.ZG     Increase in poverty gap at $1.90 ($ 2011 PPP) poverty line due to out-of-pocket health care expenditure (% of poverty line)\n",
      "SH.UHC.NOP1.ZS     Proportion of population pushed below the $2.15 ($ 2017 PPP) poverty line by out-of-pocket health care expenditure (%)\n",
      "SH.UHC.NOP2.CG     Increase in poverty gap at $3.20 ($ 2011 PPP) poverty line due to out-of-pocket health care expenditure (USD)\n",
      "SH.UHC.NOP2.TO     Number of people pushed below the $3.65 ($ 2017 PPP) poverty line by out-of-pocket health care expenditure\n",
      "SH.UHC.NOP2.ZG     Increase in poverty gap at $3.20 ($ 2011 PPP) poverty line due to out-of-pocket health care expenditure (% of poverty line)\n",
      "SH.UHC.NOP2.ZS     Proportion of population pushed below the $3.65 ($ 2017 PPP) poverty line by out-of-pocket health care expenditure (%)\n",
      "SI.POV.DDAY        Poverty headcount ratio at $2.15 a day (2017 PPP) (% of population)\n",
      "SI.POV.GAPS        Poverty gap at $2.15 a day (2017 PPP) (%)\n",
      "SI.POV.LMIC        Poverty headcount ratio at $3.65 a day (2017 PPP) (% of population)\n",
      "SI.POV.LMIC.GP     Poverty gap at $3.65 a day (2017 PPP) (%)\n",
      "SI.POV.MDIM        Multidimensional poverty headcount ratio (% of total population)\n",
      "SI.POV.MDIM.17     Multidimensional poverty headcount ratio, children (% of population ages 0-17)\n",
      "SI.POV.MDIM.17.XQ  Multidimensional poverty index, children (population ages 0-17) (scale 0-1)\n",
      "SI.POV.MDIM.FE     Multidimensional poverty headcount ratio, female (% of female population)\n",
      "SI.POV.MDIM.HH     Multidimensional poverty headcount ratio, household (% of total households)\n",
      "SI.POV.MDIM.IT     Multidimensional poverty intensity (average share of deprivations experienced by the poor)\n",
      "SI.POV.MDIM.MA     Multidimensional poverty headcount ratio, male (% of male population)\n",
      "SI.POV.MDIM.XQ     Multidimensional poverty index (scale 0-1)\n",
      "SI.POV.NAHC        Poverty headcount ratio at national poverty lines (% of population)\n",
      "SI.POV.UMIC        Poverty headcount ratio at $6.85 a day (2017 PPP) (% of population)\n",
      "SI.POV.UMIC.GP     Poverty gap at $6.85 a day (2017 PPP) (%)\n",
      "                   23 elements\n"
     ]
    }
   ],
   "source": [
    "print(wb.series.info(q = 'poverty'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "cce9dd56-856b-408f-b378-985cc9dd6704",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id                       value\n",
      "-----------------------  -------------------------------------------------------------------------------------------------------------------------------\n",
      "EN.ATM.PM25.MC.T1.ZS     PM2.5 pollution, population exposed to levels exceeding WHO Interim Target-1 value (% of total)\n",
      "EN.ATM.PM25.MC.T2.ZS     PM2.5 pollution, population exposed to levels exceeding WHO Interim Target-2 value (% of total)\n",
      "EN.ATM.PM25.MC.T3.ZS     PM2.5 pollution, population exposed to levels exceeding WHO Interim Target-3 value (% of total)\n",
      "EN.ATM.PM25.MC.ZS        PM2.5 air pollution, population exposed to levels exceeding WHO guideline value (% of total)\n",
      "EN.POP.DNST              Population density (people per sq. km of land area)\n",
      "EN.POP.EL5M.RU.ZS        Rural population living in areas where elevation is below 5 meters (% of total population)\n",
      "EN.POP.EL5M.UR.ZS        Urban population living in areas where elevation is below 5 meters (% of total population)\n",
      "EN.POP.EL5M.ZS           Population living in areas where elevation is below 5 meters (% of total population)\n",
      "EN.POP.SLUM.UR.ZS        Population living in slums (% of urban population)\n",
      "EN.URB.LCTY              Population in largest city\n",
      "EN.URB.LCTY.UR.ZS        Population in the largest city (% of urban population)\n",
      "EN.URB.MCTY              Population in urban agglomerations of more than 1 million\n",
      "EN.URB.MCTY.TL.ZS        Population in urban agglomerations of more than 1 million (% of total population)\n",
      "SE.PRM.CUAT.FE.ZS        Educational attainment, at least completed primary, population 25+ years, female (%) (cumulative)\n",
      "SE.PRM.CUAT.MA.ZS        Educational attainment, at least completed primary, population 25+ years, male (%) (cumulative)\n",
      "SE.PRM.CUAT.ZS           Educational attainment, at least completed primary, population 25+ years, total (%) (cumulative)\n",
      "SE.SEC.CUAT.LO.FE.ZS     Educational attainment, at least completed lower secondary, population 25+, female (%) (cumulative)\n",
      "SE.SEC.CUAT.LO.MA.ZS     Educational attainment, at least completed lower secondary, population 25+, male (%) (cumulative)\n",
      "SE.SEC.CUAT.LO.ZS        Educational attainment, at least completed lower secondary, population 25+, total (%) (cumulative)\n",
      "SE.SEC.CUAT.PO.FE.ZS     Educational attainment, at least completed post-secondary, population 25+, female (%) (cumulative)\n",
      "SE.SEC.CUAT.PO.MA.ZS     Educational attainment, at least completed post-secondary, population 25+, male (%) (cumulative)\n",
      "SE.SEC.CUAT.PO.ZS        Educational attainment, at least completed post-secondary, population 25+, total (%) (cumulative)\n",
      "SE.SEC.CUAT.UP.FE.ZS     Educational attainment, at least completed upper secondary, population 25+, female (%) (cumulative)\n",
      "SE.SEC.CUAT.UP.MA.ZS     Educational attainment, at least completed upper secondary, population 25+, male (%) (cumulative)\n",
      "SE.SEC.CUAT.UP.ZS        Educational attainment, at least completed upper secondary, population 25+, total (%) (cumulative)\n",
      "SE.TER.CUAT.BA.FE.ZS     Educational attainment, at least Bachelor's or equivalent, population 25+, female (%) (cumulative)\n",
      "SE.TER.CUAT.BA.MA.ZS     Educational attainment, at least Bachelor's or equivalent, population 25+, male (%) (cumulative)\n",
      "SE.TER.CUAT.BA.ZS        Educational attainment, at least Bachelor's or equivalent, population 25+, total (%) (cumulative)\n",
      "SE.TER.CUAT.DO.FE.ZS     Educational attainment, Doctoral or equivalent, population 25+, female (%) (cumulative)\n",
      "SE.TER.CUAT.DO.MA.ZS     Educational attainment, Doctoral or equivalent, population 25+, male (%) (cumulative)\n",
      "SE.TER.CUAT.DO.ZS        Educational attainment, Doctoral or equivalent, population 25+, total (%) (cumulative)\n",
      "SE.TER.CUAT.MS.FE.ZS     Educational attainment, at least Master's or equivalent, population 25+, female (%) (cumulative)\n",
      "SE.TER.CUAT.MS.MA.ZS     Educational attainment, at least Master's or equivalent, population 25+, male (%) (cumulative)\n",
      "SE.TER.CUAT.MS.ZS        Educational attainment, at least Master's or equivalent, population 25+, total (%) (cumulative)\n",
      "SE.TER.CUAT.ST.FE.ZS     Educational attainment, at least completed short-cycle tertiary, population 25+, female (%) (cumulative)\n",
      "SE.TER.CUAT.ST.MA.ZS     Educational attainment, at least completed short-cycle tertiary, population 25+, male (%) (cumulative)\n",
      "SE.TER.CUAT.ST.ZS        Educational attainment, at least completed short-cycle tertiary, population 25+, total (%) (cumulative)\n",
      "SH.CON.1524.FE.ZS        Condom use, population ages 15-24, female (% of females ages 15-24)\n",
      "SH.CON.1524.MA.ZS        Condom use, population ages 15-24, male (% of males ages 15-24)\n",
      "SH.DYN.AIDS.FE.ZS        Women's share of population ages 15+ living with HIV (%)\n",
      "SH.UHC.NOP1.ZS           Proportion of population pushed below the $2.15 ($ 2017 PPP) poverty line by out-of-pocket health care expenditure (%)\n",
      "SH.UHC.NOP2.ZS           Proportion of population pushed below the $3.65 ($ 2017 PPP) poverty line by out-of-pocket health care expenditure (%)\n",
      "SH.UHC.OOPC.10.ZS        Proportion of population spending more than 10% of household consumption or income on out-of-pocket health care expenditure (%)\n",
      "SH.UHC.OOPC.25.ZS        Proportion of population spending more than 25% of household consumption or income on out-of-pocket health care expenditure (%)\n",
      "SI.POV.MDIM.17.XQ        Multidimensional poverty index, children (population ages 0-17) (scale 0-1)\n",
      "SI.SPR.PC40              Survey mean consumption or income per capita, bottom 40% of population (2017 PPP $ per day)\n",
      "SI.SPR.PC40.ZG           Annualized average growth rate in per capita real survey mean consumption or income, bottom 40% of population (%)\n",
      "SI.SPR.PCAP              Survey mean consumption or income per capita, total population (2017 PPP $ per day)\n",
      "SI.SPR.PCAP.ZG           Annualized average growth rate in per capita real survey mean consumption or income, total population (%)\n",
      "SL.EMP.1524.SP.FE.NE.ZS  Employment to population ratio, ages 15-24, female (%) (national estimate)\n",
      "SL.EMP.1524.SP.FE.ZS     Employment to population ratio, ages 15-24, female (%) (modeled ILO estimate)\n",
      "SL.EMP.1524.SP.MA.NE.ZS  Employment to population ratio, ages 15-24, male (%) (national estimate)\n",
      "SL.EMP.1524.SP.MA.ZS     Employment to population ratio, ages 15-24, male (%) (modeled ILO estimate)\n",
      "SL.EMP.1524.SP.NE.ZS     Employment to population ratio, ages 15-24, total (%) (national estimate)\n",
      "SL.EMP.1524.SP.ZS        Employment to population ratio, ages 15-24, total (%) (modeled ILO estimate)\n",
      "SL.EMP.TOTL.SP.FE.NE.ZS  Employment to population ratio, 15+, female (%) (national estimate)\n",
      "SL.EMP.TOTL.SP.FE.ZS     Employment to population ratio, 15+, female (%) (modeled ILO estimate)\n",
      "SL.EMP.TOTL.SP.MA.NE.ZS  Employment to population ratio, 15+, male (%) (national estimate)\n",
      "SL.EMP.TOTL.SP.MA.ZS     Employment to population ratio, 15+, male (%) (modeled ILO estimate)\n",
      "SL.EMP.TOTL.SP.NE.ZS     Employment to population ratio, 15+, total (%) (national estimate)\n",
      "SL.EMP.TOTL.SP.ZS        Employment to population ratio, 15+, total (%) (modeled ILO estimate)\n",
      "SL.TLF.ACTI.FE.ZS        Labor force participation rate, female (% of female population ages 15-64) (modeled ILO estimate)\n",
      "SL.TLF.ACTI.MA.ZS        Labor force participation rate, male (% of male population ages 15-64) (modeled ILO estimate)\n",
      "SL.TLF.ACTI.ZS           Labor force participation rate, total (% of total population ages 15-64) (modeled ILO estimate)\n",
      "SL.TLF.CACT.FE.NE.ZS     Labor force participation rate, female (% of female population ages 15+) (national estimate)\n",
      "SL.TLF.CACT.FE.ZS        Labor force participation rate, female (% of female population ages 15+) (modeled ILO estimate)\n",
      "SL.TLF.CACT.MA.NE.ZS     Labor force participation rate, male (% of male population ages 15+) (national estimate)\n",
      "SL.TLF.CACT.MA.ZS        Labor force participation rate, male (% of male population ages 15+) (modeled ILO estimate)\n",
      "SL.TLF.CACT.NE.ZS        Labor force participation rate, total (% of total population ages 15+) (national estimate)\n",
      "SL.TLF.CACT.ZS           Labor force participation rate, total (% of total population ages 15+) (modeled ILO estimate)\n",
      "SM.POP.REFG              Refugee population by country or territory of asylum\n",
      "SM.POP.REFG.OR           Refugee population by country or territory of origin\n",
      "SN.ITK.MSFI.ZS           Prevalence of moderate or severe food insecurity in the population (%)\n",
      "SN.ITK.SVFI.ZS           Prevalence of severe food insecurity in the population (%)\n",
      "SP.POP.0004.FE.5Y        Population ages 00-04, female (% of female population)\n",
      "SP.POP.0004.MA.5Y        Population ages 00-04, male (% of male population)\n",
      "SP.POP.0014.FE.IN        Population ages 0-14, female\n",
      "SP.POP.0014.FE.ZS        Population ages 0-14, female (% of female population)\n",
      "SP.POP.0014.MA.IN        Population ages 0-14, male\n",
      "SP.POP.0014.MA.ZS        Population ages 0-14, male (% of male population)\n",
      "SP.POP.0014.TO           Population ages 0-14, total\n",
      "SP.POP.0014.TO.ZS        Population ages 0-14 (% of total population)\n",
      "SP.POP.0509.FE.5Y        Population ages 05-09, female (% of female population)\n",
      "SP.POP.0509.MA.5Y        Population ages 05-09, male (% of male population)\n",
      "SP.POP.1014.FE.5Y        Population ages 10-14, female (% of female population)\n",
      "SP.POP.1014.MA.5Y        Population ages 10-14, male (% of male population)\n",
      "SP.POP.1519.FE.5Y        Population ages 15-19, female (% of female population)\n",
      "SP.POP.1519.MA.5Y        Population ages 15-19, male (% of male population)\n",
      "SP.POP.1564.FE.IN        Population ages 15-64, female\n",
      "SP.POP.1564.FE.ZS        Population ages 15-64, female (% of female population)\n",
      "SP.POP.1564.MA.IN        Population ages 15-64, male\n",
      "SP.POP.1564.MA.ZS        Population ages 15-64, male (% of male population)\n",
      "SP.POP.1564.TO           Population ages 15-64, total\n",
      "SP.POP.1564.TO.ZS        Population ages 15-64 (% of total population)\n",
      "SP.POP.2024.FE.5Y        Population ages 20-24, female (% of female population)\n",
      "SP.POP.2024.MA.5Y        Population ages 20-24, male (% of male population)\n",
      "SP.POP.2529.FE.5Y        Population ages 25-29, female (% of female population)\n",
      "SP.POP.2529.MA.5Y        Population ages 25-29, male (% of male population)\n",
      "SP.POP.3034.FE.5Y        Population ages 30-34, female (% of female population)\n",
      "SP.POP.3034.MA.5Y        Population ages 30-34, male (% of male population)\n",
      "SP.POP.3539.FE.5Y        Population ages 35-39, female (% of female population)\n",
      "SP.POP.3539.MA.5Y        Population ages 35-39, male (% of male population)\n",
      "SP.POP.4044.FE.5Y        Population ages 40-44, female (% of female population)\n",
      "SP.POP.4044.MA.5Y        Population ages 40-44, male (% of male population)\n",
      "SP.POP.4549.FE.5Y        Population ages 45-49, female (% of female population)\n",
      "SP.POP.4549.MA.5Y        Population ages 45-49, male (% of male population)\n",
      "SP.POP.5054.FE.5Y        Population ages 50-54, female (% of female population)\n",
      "SP.POP.5054.MA.5Y        Population ages 50-54, male (% of male population)\n",
      "SP.POP.5559.FE.5Y        Population ages 55-59, female (% of female population)\n",
      "SP.POP.5559.MA.5Y        Population ages 55-59, male (% of male population)\n",
      "SP.POP.6064.FE.5Y        Population ages 60-64, female (% of female population)\n",
      "SP.POP.6064.MA.5Y        Population ages 60-64, male (% of male population)\n",
      "SP.POP.6569.FE.5Y        Population ages 65-69, female (% of female population)\n",
      "SP.POP.6569.MA.5Y        Population ages 65-69, male (% of male population)\n",
      "SP.POP.65UP.FE.IN        Population ages 65 and above, female\n",
      "SP.POP.65UP.FE.ZS        Population ages 65 and above, female (% of female population)\n",
      "SP.POP.65UP.MA.IN        Population ages 65 and above, male\n",
      "SP.POP.65UP.MA.ZS        Population ages 65 and above, male (% of male population)\n",
      "SP.POP.65UP.TO           Population ages 65 and above, total\n",
      "SP.POP.65UP.TO.ZS        Population ages 65 and above (% of total population)\n",
      "SP.POP.7074.FE.5Y        Population ages 70-74, female (% of female population)\n",
      "SP.POP.7074.MA.5Y        Population ages 70-74, male (% of male population)\n",
      "SP.POP.7579.FE.5Y        Population ages 75-79, female (% of female population)\n",
      "SP.POP.7579.MA.5Y        Population ages 75-79, male (% of male population)\n",
      "SP.POP.80UP.FE.5Y        Population ages 80 and above, female (% of female population)\n",
      "SP.POP.80UP.MA.5Y        Population ages 80 and above, male (% of male population)\n",
      "SP.POP.GROW              Population growth (annual %)\n",
      "SP.POP.TOTL              Population, total\n",
      "SP.POP.TOTL.FE.IN        Population, female\n",
      "SP.POP.TOTL.FE.ZS        Population, female (% of total population)\n",
      "SP.POP.TOTL.MA.IN        Population, male\n",
      "SP.POP.TOTL.MA.ZS        Population, male (% of total population)\n",
      "SP.RUR.TOTL              Rural population\n",
      "SP.RUR.TOTL.ZG           Rural population growth (annual %)\n",
      "SP.RUR.TOTL.ZS           Rural population (% of total population)\n",
      "SP.URB.GROW              Urban population growth (annual %)\n",
      "SP.URB.TOTL              Urban population\n",
      "SP.URB.TOTL.IN.ZS        Urban population (% of total population)\n",
      "                         138 elements\n"
     ]
    }
   ],
   "source": [
    "print(wb.series.info(q = 'population'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "876fcfcc-0db3-4da0-945b-13409e0fe6bb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id                    value\n",
      "--------------------  -------------------------------------------------------------------------\n",
      "EG.GDP.PUSE.KO.PP     GDP per unit of energy use (PPP $ per kg of oil equivalent)\n",
      "EG.GDP.PUSE.KO.PP.KD  GDP per unit of energy use (constant 2017 PPP $ per kg of oil equivalent)\n",
      "EG.USE.COMM.GD.PP.KD  Energy use (kg of oil equivalent) per $1,000 GDP (constant 2017 PPP)\n",
      "NY.GDP.DEFL.KD.ZG     Inflation, GDP deflator (annual %)\n",
      "NY.GDP.DEFL.KD.ZG.AD  Inflation, GDP deflator: linked series (annual %)\n",
      "NY.GDP.DEFL.ZS        GDP deflator (base year varies by country)\n",
      "NY.GDP.DEFL.ZS.AD     GDP deflator: linked series (base year varies by country)\n",
      "NY.GDP.DISC.CN        Discrepancy in expenditure estimate of GDP (current LCU)\n",
      "NY.GDP.DISC.KN        Discrepancy in expenditure estimate of GDP (constant LCU)\n",
      "NY.GDP.MKTP.CD        GDP (current US$)\n",
      "NY.GDP.MKTP.CN        GDP (current LCU)\n",
      "NY.GDP.MKTP.CN.AD     GDP: linked series (current LCU)\n",
      "NY.GDP.MKTP.KD        GDP (constant 2015 US$)\n",
      "NY.GDP.MKTP.KD.ZG     GDP growth (annual %)\n",
      "NY.GDP.MKTP.KN        GDP (constant LCU)\n",
      "NY.GDP.MKTP.PP.CD     GDP, PPP (current international $)\n",
      "NY.GDP.MKTP.PP.KD     GDP, PPP (constant 2017 international $)\n",
      "NY.GDP.PCAP.CD        GDP per capita (current US$)\n",
      "NY.GDP.PCAP.CN        GDP per capita (current LCU)\n",
      "NY.GDP.PCAP.KD        GDP per capita (constant 2015 US$)\n",
      "NY.GDP.PCAP.KD.ZG     GDP per capita growth (annual %)\n",
      "NY.GDP.PCAP.KN        GDP per capita (constant LCU)\n",
      "NY.GDP.PCAP.PP.CD     GDP per capita, PPP (current international $)\n",
      "NY.GDP.PCAP.PP.KD     GDP per capita, PPP (constant 2017 international $)\n",
      "PA.NUS.PPP            PPP conversion factor, GDP (LCU per international $)\n",
      "PA.NUS.PPPC.RF        Price level ratio of PPP conversion factor (GDP) to market exchange rate\n",
      "SL.GDP.PCAP.EM.KD     GDP per person employed (constant 2017 PPP $)\n",
      "                      27 elements\n"
     ]
    }
   ],
   "source": [
    "print(wb.series.info(q = 'gdp'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "3bf4c9b4-0b21-400f-964e-066d9a7c0899",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id                        value\n",
      "------------------------  ------------------------------------------------------------------------------------------------------\n",
      "per_lm_alllm.adq_pop_tot  Adequacy of unemployment benefits and ALMP (% of total welfare of beneficiary households)\n",
      "per_lm_alllm.ben_q1_tot   Benefit incidence of unemployment benefits and ALMP to poorest quintile (% of total U/ALMP benefits)\n",
      "per_lm_alllm.cov_pop_tot  Coverage of unemployment benefits and ALMP (% of population)\n",
      "per_lm_alllm.cov_q1_tot   Coverage of unemployment benefits and ALMP in poorest quintile (% of population)\n",
      "per_lm_alllm.cov_q2_tot   Coverage of unemployment benefits and ALMP in 2nd quintile (% of population)\n",
      "per_lm_alllm.cov_q3_tot   Coverage of unemployment benefits and ALMP in 3rd quintile (% of population)\n",
      "per_lm_alllm.cov_q4_tot   Coverage of unemployment benefits and ALMP in 4th quintile (% of population)\n",
      "per_lm_alllm.cov_q5_tot   Coverage of unemployment benefits and ALMP in richest quintile (% of population)\n",
      "SL.UEM.1524.FE.NE.ZS      Unemployment, youth female (% of female labor force ages 15-24) (national estimate)\n",
      "SL.UEM.1524.FE.ZS         Unemployment, youth female (% of female labor force ages 15-24) (modeled ILO estimate)\n",
      "SL.UEM.1524.MA.NE.ZS      Unemployment, youth male (% of male labor force ages 15-24) (national estimate)\n",
      "SL.UEM.1524.MA.ZS         Unemployment, youth male (% of male labor force ages 15-24) (modeled ILO estimate)\n",
      "SL.UEM.1524.NE.ZS         Unemployment, youth total (% of total labor force ages 15-24) (national estimate)\n",
      "SL.UEM.1524.ZS            Unemployment, youth total (% of total labor force ages 15-24) (modeled ILO estimate)\n",
      "SL.UEM.ADVN.FE.ZS         Unemployment with advanced education, female (% of female labor force with advanced education)\n",
      "SL.UEM.ADVN.MA.ZS         Unemployment with advanced education, male (% of male labor force with advanced education)\n",
      "SL.UEM.ADVN.ZS            Unemployment with advanced education (% of total labor force with advanced education)\n",
      "SL.UEM.BASC.FE.ZS         Unemployment with basic education, female (% of female labor force with basic education)\n",
      "SL.UEM.BASC.MA.ZS         Unemployment with basic education, male (% of male labor force with basic education)\n",
      "SL.UEM.BASC.ZS            Unemployment with basic education (% of total labor force with basic education)\n",
      "SL.UEM.INTM.FE.ZS         Unemployment with intermediate education, female (% of female labor force with intermediate education)\n",
      "SL.UEM.INTM.MA.ZS         Unemployment with intermediate education, male (% of male labor force with intermediate education)\n",
      "SL.UEM.INTM.ZS            Unemployment with intermediate education (% of total labor force with intermediate education)\n",
      "SL.UEM.TOTL.FE.NE.ZS      Unemployment, female (% of female labor force) (national estimate)\n",
      "SL.UEM.TOTL.FE.ZS         Unemployment, female (% of female labor force) (modeled ILO estimate)\n",
      "SL.UEM.TOTL.MA.NE.ZS      Unemployment, male (% of male labor force) (national estimate)\n",
      "SL.UEM.TOTL.MA.ZS         Unemployment, male (% of male labor force) (modeled ILO estimate)\n",
      "SL.UEM.TOTL.NE.ZS         Unemployment, total (% of total labor force) (national estimate)\n",
      "SL.UEM.TOTL.ZS            Unemployment, total (% of total labor force) (modeled ILO estimate)\n",
      "                          29 elements\n"
     ]
    }
   ],
   "source": [
    "print(wb.series.info(q = 'unemployment'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f536c575-cf5b-4caf-8212-787dac9b5a92",
   "metadata": {
    "tags": []
   },
   "source": [
    "### collect relevant variables and store in the dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "530c7bad-5305-424a-a660-c756738a460d",
   "metadata": {},
   "outputs": [],
   "source": [
    "wb_var_list = ['SI.POV.GINI', #Gini index\n",
    "               'SI.POV.MDIM.XQ', #Multidimensional poverty index (scale 0-1)\n",
    "               'SI.POV.DDAY',  #Poverty headcount ratio at $2.15 a day (2017 PPP) (% of population)\n",
    "               'SP.POP.TOTL', # Population, total\n",
    "               'EN.POP.DNST', #Population density (people per sq. km of land area)\n",
    "               'SL.UEM.TOTL.NE.ZS', #Unemployment, total (% of total labor force) (national estimate)\n",
    "               'NY.GDP.MKTP.KD'] #GDP (constant 2015 US$) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d36792f-65fa-4200-b4d3-78729b9f825a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wb = wb.data.DataFrame(wb_var_list, time=range(2005, 2023), skipBlanks=True, columns='series')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "064bcdca-693f-4911-8139-2da4c31cd7e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wb = df_wb.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03c62fc3-2bdd-4b58-b9bd-ba6db70e92de",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c4d614f-7a92-49e1-82b9-ce1361db3f22",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wb.columns=['country_iso_str', \n",
    "               'year',\n",
    "               'pop_density', \n",
    "               'gdp_2015usd',\n",
    "               'poverty_ratio',\n",
    "               'gini',\n",
    "               'poverty_index',\n",
    "               'unemployment',\n",
    "               'total_pop']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d467036-e21d-4e6f-9116-160ab333b67d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wb['year'] = df_wb['year'].apply(lambda x:int(x[-4:]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d92d428a-3ac1-4481-949b-f13b485cb36b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wb.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24bef888-a405-4646-8c3c-23d78b00a921",
   "metadata": {},
   "source": [
    "## MASTER Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82217323-6152-4dc2-8bd4-50c8890280af",
   "metadata": {
    "tags": []
   },
   "source": [
    "### create the master dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 640,
   "id": "013acd40-2d22-4595-a08f-aeb660134ec1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_master = df_wvs_master.merge(df_wb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 641,
   "id": "7901f358-1428-49d8-9066-f5c998fe57b0",
   "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>index</th>\n",
       "      <th>wave</th>\n",
       "      <th>year</th>\n",
       "      <th>country_iso</th>\n",
       "      <th>interview_ID</th>\n",
       "      <th>interview_date</th>\n",
       "      <th>region_code</th>\n",
       "      <th>settlement_size</th>\n",
       "      <th>urban_rural</th>\n",
       "      <th>res_interest</th>\n",
       "      <th>...</th>\n",
       "      <th>sub_class_recoded</th>\n",
       "      <th>country_name</th>\n",
       "      <th>country_iso_str</th>\n",
       "      <th>pop_density</th>\n",
       "      <th>gdp_2015usd</th>\n",
       "      <th>poverty_ratio</th>\n",
       "      <th>gini</th>\n",
       "      <th>poverty_index</th>\n",
       "      <th>unemployment</th>\n",
       "      <th>total_pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "      <td>169.842553</td>\n",
       "      <td>3.137732e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>79826.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "      <td>169.842553</td>\n",
       "      <td>3.137732e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>79826.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "      <td>169.842553</td>\n",
       "      <td>3.137732e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>79826.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "      <td>169.842553</td>\n",
       "      <td>3.137732e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>79826.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2005</td>\n",
       "      <td>20</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "      <td>169.842553</td>\n",
       "      <td>3.137732e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>79826.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 49 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  wave  year  country_iso  interview_ID  interview_date  region_code  \\\n",
       "0      0     5  2005           20           1.0             NaN          NaN   \n",
       "1      1     5  2005           20           2.0             NaN          NaN   \n",
       "2      2     5  2005           20           3.0             NaN          NaN   \n",
       "3      3     5  2005           20           5.0             NaN          NaN   \n",
       "4      4     5  2005           20           6.0             NaN          NaN   \n",
       "\n",
       "   settlement_size  urban_rural  res_interest  ...  sub_class_recoded  \\\n",
       "0              5.0          NaN           1.0  ...                2.0   \n",
       "1              5.0          NaN           1.0  ...                3.0   \n",
       "2              5.0          NaN           1.0  ...                4.0   \n",
       "3              3.0          NaN           1.0  ...                3.0   \n",
       "4              3.0          NaN           1.0  ...                3.0   \n",
       "\n",
       "   country_name  country_iso_str  pop_density   gdp_2015usd  poverty_ratio  \\\n",
       "0       Andorra              AND   169.842553  3.137732e+09            NaN   \n",
       "1       Andorra              AND   169.842553  3.137732e+09            NaN   \n",
       "2       Andorra              AND   169.842553  3.137732e+09            NaN   \n",
       "3       Andorra              AND   169.842553  3.137732e+09            NaN   \n",
       "4       Andorra              AND   169.842553  3.137732e+09            NaN   \n",
       "\n",
       "   gini  poverty_index  unemployment  total_pop  \n",
       "0   NaN            NaN           NaN    79826.0  \n",
       "1   NaN            NaN           NaN    79826.0  \n",
       "2   NaN            NaN           NaN    79826.0  \n",
       "3   NaN            NaN           NaN    79826.0  \n",
       "4   NaN            NaN           NaN    79826.0  \n",
       "\n",
       "[5 rows x 49 columns]"
      ]
     },
     "execution_count": 641,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_master.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36b60f05-094a-44ae-8905-72f5cb757c06",
   "metadata": {
    "tags": []
   },
   "source": [
    "### check missing values (counts and percentage)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 642,
   "id": "5fbe4c0c-a772-43da-92dd-261ea596b373",
   "metadata": {},
   "outputs": [],
   "source": [
    "n_missing_list = []\n",
    "n_non_missing_list = []\n",
    "percent_missing_list = []\n",
    "\n",
    "for column in df_master.columns:\n",
    "    n_missing = df_master[column].isna().sum()\n",
    "    n_non_missing = df_master.shape[0] - n_missing\n",
    "    percent_missing = n_missing/df_master.shape[0]\n",
    "    \n",
    "    n_missing_list.append(n_missing)\n",
    "    n_non_missing_list.append(n_non_missing)\n",
    "    percent_missing_list.append(percent_missing)\n",
    "    \n",
    "    col_list = list(df_master.columns)\n",
    "    \n",
    "df_master_missing = pd.DataFrame({\n",
    "    'variable': df_master.columns,\n",
    "    'n_missing': n_missing_list, \n",
    "    'n_non_missing': n_non_missing_list,\n",
    "    'percent_missing': percent_missing_list\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 643,
   "id": "47061f21-4e45-4dda-9730-9a772e3839e5",
   "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>variable</th>\n",
       "      <th>n_missing</th>\n",
       "      <th>n_non_missing</th>\n",
       "      <th>percent_missing</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>poverty_index</td>\n",
       "      <td>253269</td>\n",
       "      <td>3602</td>\n",
       "      <td>0.985977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>settlement_name</td>\n",
       "      <td>193350</td>\n",
       "      <td>63521</td>\n",
       "      <td>0.752712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>settlement_type</td>\n",
       "      <td>165027</td>\n",
       "      <td>91844</td>\n",
       "      <td>0.642451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>interview_mode</td>\n",
       "      <td>164815</td>\n",
       "      <td>92056</td>\n",
       "      <td>0.641626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>urban_rural</td>\n",
       "      <td>156001</td>\n",
       "      <td>100870</td>\n",
       "      <td>0.607313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>interview_date</td>\n",
       "      <td>122474</td>\n",
       "      <td>134397</td>\n",
       "      <td>0.476792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>region_code</td>\n",
       "      <td>120620</td>\n",
       "      <td>136251</td>\n",
       "      <td>0.469574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>gini</td>\n",
       "      <td>118485</td>\n",
       "      <td>138386</td>\n",
       "      <td>0.461263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>poverty_ratio</td>\n",
       "      <td>118485</td>\n",
       "      <td>138386</td>\n",
       "      <td>0.461263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>interview_privacy</td>\n",
       "      <td>101785</td>\n",
       "      <td>155086</td>\n",
       "      <td>0.396249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>country_cow</td>\n",
       "      <td>78178</td>\n",
       "      <td>178693</td>\n",
       "      <td>0.304347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>ideology</td>\n",
       "      <td>70981</td>\n",
       "      <td>185890</td>\n",
       "      <td>0.276329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>unemployment</td>\n",
       "      <td>49975</td>\n",
       "      <td>206896</td>\n",
       "      <td>0.194553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>settlement_size</td>\n",
       "      <td>46344</td>\n",
       "      <td>210527</td>\n",
       "      <td>0.180417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>employment</td>\n",
       "      <td>39494</td>\n",
       "      <td>217377</td>\n",
       "      <td>0.153750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>res_interest</td>\n",
       "      <td>23878</td>\n",
       "      <td>232993</td>\n",
       "      <td>0.092957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>trust_diff_country</td>\n",
       "      <td>21682</td>\n",
       "      <td>235189</td>\n",
       "      <td>0.084408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>trust_diff_country_recoded</td>\n",
       "      <td>21682</td>\n",
       "      <td>235189</td>\n",
       "      <td>0.084408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>trust_diff_religion</td>\n",
       "      <td>21316</td>\n",
       "      <td>235555</td>\n",
       "      <td>0.082983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>trust_diff_religion_recoded</td>\n",
       "      <td>21316</td>\n",
       "      <td>235555</td>\n",
       "      <td>0.082983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>sub_class_recoded</td>\n",
       "      <td>20830</td>\n",
       "      <td>236041</td>\n",
       "      <td>0.081091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>sub_class</td>\n",
       "      <td>20830</td>\n",
       "      <td>236041</td>\n",
       "      <td>0.081091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>pop_density</td>\n",
       "      <td>14716</td>\n",
       "      <td>242155</td>\n",
       "      <td>0.057289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>income</td>\n",
       "      <td>12994</td>\n",
       "      <td>243877</td>\n",
       "      <td>0.050586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>trust_first_time_recoded</td>\n",
       "      <td>11855</td>\n",
       "      <td>245016</td>\n",
       "      <td>0.046152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>trust_first_time</td>\n",
       "      <td>11855</td>\n",
       "      <td>245016</td>\n",
       "      <td>0.046152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>social_trust</td>\n",
       "      <td>9513</td>\n",
       "      <td>247358</td>\n",
       "      <td>0.037034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>social_trust_recoded</td>\n",
       "      <td>9513</td>\n",
       "      <td>247358</td>\n",
       "      <td>0.037034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>trust_neighborhood</td>\n",
       "      <td>9346</td>\n",
       "      <td>247525</td>\n",
       "      <td>0.036384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>trust_neighborhood_recoded</td>\n",
       "      <td>9346</td>\n",
       "      <td>247525</td>\n",
       "      <td>0.036384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>trust_personal_known</td>\n",
       "      <td>8966</td>\n",
       "      <td>247905</td>\n",
       "      <td>0.034905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>trust_personal_known_recoded</td>\n",
       "      <td>8966</td>\n",
       "      <td>247905</td>\n",
       "      <td>0.034905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>trust_family</td>\n",
       "      <td>7707</td>\n",
       "      <td>249164</td>\n",
       "      <td>0.030003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>trust_family_recoded</td>\n",
       "      <td>7707</td>\n",
       "      <td>249164</td>\n",
       "      <td>0.030003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>year_of_birth</td>\n",
       "      <td>7301</td>\n",
       "      <td>249570</td>\n",
       "      <td>0.028423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>age</td>\n",
       "      <td>7301</td>\n",
       "      <td>249570</td>\n",
       "      <td>0.028423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>education</td>\n",
       "      <td>7002</td>\n",
       "      <td>249869</td>\n",
       "      <td>0.027259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>gdp_2015usd</td>\n",
       "      <td>1190</td>\n",
       "      <td>255681</td>\n",
       "      <td>0.004633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>marital</td>\n",
       "      <td>1044</td>\n",
       "      <td>255827</td>\n",
       "      <td>0.004064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>gender</td>\n",
       "      <td>261</td>\n",
       "      <td>256610</td>\n",
       "      <td>0.001016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>interview_ID</td>\n",
       "      <td>1</td>\n",
       "      <td>256870</td>\n",
       "      <td>0.000004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>index</td>\n",
       "      <td>0</td>\n",
       "      <td>256871</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>country_iso_str</td>\n",
       "      <td>0</td>\n",
       "      <td>256871</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>country_name</td>\n",
       "      <td>0</td>\n",
       "      <td>256871</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>year_country</td>\n",
       "      <td>0</td>\n",
       "      <td>256871</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>wave</td>\n",
       "      <td>0</td>\n",
       "      <td>256871</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>country_iso</td>\n",
       "      <td>0</td>\n",
       "      <td>256871</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>year</td>\n",
       "      <td>0</td>\n",
       "      <td>256871</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>total_pop</td>\n",
       "      <td>0</td>\n",
       "      <td>256871</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        variable  n_missing  n_non_missing  percent_missing\n",
       "46                 poverty_index     253269           3602         0.985977\n",
       "28               settlement_name     193350          63521         0.752712\n",
       "29               settlement_type     165027          91844         0.642451\n",
       "27                interview_mode     164815          92056         0.641626\n",
       "8                    urban_rural     156001         100870         0.607313\n",
       "5                 interview_date     122474         134397         0.476792\n",
       "6                    region_code     120620         136251         0.469574\n",
       "45                          gini     118485         138386         0.461263\n",
       "44                 poverty_ratio     118485         138386         0.461263\n",
       "26             interview_privacy     101785         155086         0.396249\n",
       "25                   country_cow      78178         178693         0.304347\n",
       "24                      ideology      70981         185890         0.276329\n",
       "47                  unemployment      49975         206896         0.194553\n",
       "7                settlement_size      46344         210527         0.180417\n",
       "23                    employment      39494         217377         0.153750\n",
       "9                   res_interest      23878         232993         0.092957\n",
       "16            trust_diff_country      21682         235189         0.084408\n",
       "38    trust_diff_country_recoded      21682         235189         0.084408\n",
       "15           trust_diff_religion      21316         235555         0.082983\n",
       "37   trust_diff_religion_recoded      21316         235555         0.082983\n",
       "39             sub_class_recoded      20830         236041         0.081091\n",
       "17                     sub_class      20830         236041         0.081091\n",
       "42                   pop_density      14716         242155         0.057289\n",
       "19                        income      12994         243877         0.050586\n",
       "36      trust_first_time_recoded      11855         245016         0.046152\n",
       "14              trust_first_time      11855         245016         0.046152\n",
       "10                  social_trust       9513         247358         0.037034\n",
       "32          social_trust_recoded       9513         247358         0.037034\n",
       "12            trust_neighborhood       9346         247525         0.036384\n",
       "34    trust_neighborhood_recoded       9346         247525         0.036384\n",
       "13          trust_personal_known       8966         247905         0.034905\n",
       "35  trust_personal_known_recoded       8966         247905         0.034905\n",
       "11                  trust_family       7707         249164         0.030003\n",
       "33          trust_family_recoded       7707         249164         0.030003\n",
       "21                 year_of_birth       7301         249570         0.028423\n",
       "31                           age       7301         249570         0.028423\n",
       "18                     education       7002         249869         0.027259\n",
       "43                   gdp_2015usd       1190         255681         0.004633\n",
       "22                       marital       1044         255827         0.004064\n",
       "20                        gender        261         256610         0.001016\n",
       "4                   interview_ID          1         256870         0.000004\n",
       "0                          index          0         256871         0.000000\n",
       "41               country_iso_str          0         256871         0.000000\n",
       "40                  country_name          0         256871         0.000000\n",
       "30                  year_country          0         256871         0.000000\n",
       "1                           wave          0         256871         0.000000\n",
       "3                    country_iso          0         256871         0.000000\n",
       "2                           year          0         256871         0.000000\n",
       "48                     total_pop          0         256871         0.000000"
      ]
     },
     "execution_count": 643,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_master_missing.sort_values(by = 'percent_missing', ascending = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "287bfa9a-d75c-4e1d-8e99-532a0e6c943c",
   "metadata": {
    "tags": []
   },
   "source": [
    "### recode multi-categorical variables ```employment```,```marital```,```education``` into binary\n",
    "### recode ```gender``` to be 0 vs. 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 644,
   "id": "0b99362d-38d0-4b04-b1ac-57137777df7a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_master['employment_binary'] = np.where(\n",
    "    df_master['employment']==7,0,\n",
    "    np.where(df_master['employment'].isna(), np.nan, 1)) #1 = employed, #0 = unemployed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 645,
   "id": "0b810a09-9520-42a2-83c1-55e39b7607e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_master['marital_binary'] = np.where(\n",
    "    df_master['marital']==1,1,\n",
    "    np.where(df_master['marital'].isna(), np.nan, 0)) #1 = married, #0 = not married"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 646,
   "id": "8b185f40-1703-41d3-a38e-691ad7cc45ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "if (df_master['wave'] == 7).any():  # Check if all values in 'wave' column are 7\n",
    "    df_master['hs_binary'] = np.where(\n",
    "        df_master['education'] <= 2, 0,\n",
    "        np.where(df_master['education'].isna(), np.nan, 1))\n",
    "else:\n",
    "    df_master['hs_binary'] = np.where(\n",
    "        df_master['education'] <= 6, 0,\n",
    "        np.where(df_master['education'].isna(), np.nan, 1)) #1 = high school graduate, #0 = less than high school"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 649,
   "id": "6dc35048-db9f-480f-aa6b-ff2c606afc93",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_master['gender_recoded'] = np.where(\n",
    "        df_master['gender'] == 2, 0,\n",
    "        np.where(df_master['gender'].isna(), np.nan, 1)) #1 = male #0 = female"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b5ae500-a8d8-487b-89f1-af0ab6e84e81",
   "metadata": {
    "tags": []
   },
   "source": [
    "### cluster(country)-centering & grand-mean-centering relevant continous IVs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 682,
   "id": "521dfcf7-b251-4dba-b65f-37cf9b6f169f",
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_iv_list = ['gender_recoded', #1 = male, #0 = female\n",
    "               'employment_binary',\n",
    "               'marital_binary',\n",
    "               'hs_binary']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 709,
   "id": "f916caec-cf0d-4a9a-b282-7df2f744f690",
   "metadata": {},
   "outputs": [],
   "source": [
    "con_iv_list = ['settlement_size', #essentially ordinal\n",
    "               'sub_class', #1 = upper, 2 = uppper-mid, 3 = lower-mid, 4 = working 5 = lower ##essentially ordinal\n",
    "               'sub_class_recoded',\n",
    "               'ideology', #1 = left, 7 = right\n",
    "               'age', 'income',\n",
    "               'pop_density', 'total_pop',\n",
    "               'gdp_2015usd',\n",
    "               'poverty_ratio', \n",
    "               'gini', \n",
    "               'total_pop',\n",
    "               'unemployment'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 766,
   "id": "627299cb-dabf-4ca1-b831-fd3c7b2ef358",
   "metadata": {},
   "outputs": [],
   "source": [
    "for var in con_iv_list:\n",
    "    \n",
    "    var_cmc = var + '_cmc'\n",
    "    df_master[var_cmc] = df_master.groupby('country_iso')[var].transform(lambda x: x - x.mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 767,
   "id": "5c5e2428-54df-41a0-81ed-f3ba2f6a81d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "for var in con_iv_list:\n",
    "    var_gmc = var + '_gmc'\n",
    "    df_master[var_gmc] = df_master[var].transform(lambda x: x - x.mean())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f36091ad-51dd-4aba-8331-7d5179e1d34c",
   "metadata": {},
   "source": [
    "### recheck MASTER dataset and save"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 787,
   "id": "c6c28ee9-89c4-47ff-9c77-9dac7cdca0e8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(256871, 77)"
      ]
     },
     "execution_count": 787,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_master.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 711,
   "id": "8a945891-edf0-4a3c-9464-8c44c85137de",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['index', 'wave', 'year', 'country_iso', 'interview_ID',\n",
       "       'interview_date', 'region_code', 'settlement_size', 'urban_rural',\n",
       "       'res_interest', 'social_trust', 'trust_family', 'trust_neighborhood',\n",
       "       'trust_personal_known', 'trust_first_time', 'trust_diff_religion',\n",
       "       'trust_diff_country', 'sub_class', 'education', 'income', 'gender',\n",
       "       'year_of_birth', 'marital', 'employment', 'ideology', 'country_cow',\n",
       "       'interview_privacy', 'interview_mode', 'settlement_name',\n",
       "       'settlement_type', 'year_country', 'age', 'social_trust_recoded',\n",
       "       'trust_family_recoded', 'trust_neighborhood_recoded',\n",
       "       'trust_personal_known_recoded', 'trust_first_time_recoded',\n",
       "       'trust_diff_religion_recoded', 'trust_diff_country_recoded',\n",
       "       'sub_class_recoded', 'country_name', 'country_iso_str', 'pop_density',\n",
       "       'gdp_2015usd', 'poverty_ratio', 'gini', 'poverty_index', 'unemployment',\n",
       "       'total_pop', 'employment_binary', 'marital_binary', 'hs_binary',\n",
       "       'gender_recoded', 'gini_cmc', 'settlement_size_cmc', 'sub_class_cmc',\n",
       "       'sub_class_recoded_cmc', 'ideology_cmc', 'age_cmc', 'pop_density_cmc',\n",
       "       'total_pop_cmc', 'gdp_2015usd_cmc', 'poverty_ratio_cmc', 'income_cmc',\n",
       "       'unemployment_cmc'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 711,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_master.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 772,
   "id": "00c7bc24-128e-4998-b960-fa569d3e531d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_master.to_csv('wvs_wb_master.csv')"
   ]
  }
 ],
 "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.8.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}