{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "1408922d-4311-41bf-9467-fe650b14ce8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df_fund = pd.read_excel('Fund.xlsx')\n",
    "df_manager = pd.read_excel('Manager.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "6d7abed4-1f1a-45ed-b42e-a545517c9f0b",
   "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>Fund_Codeinfo.FundID</th>\n",
       "      <th>Symbol</th>\n",
       "      <th>Startdate</th>\n",
       "      <th>EndDate</th>\n",
       "      <th>FundID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38078</td>\n",
       "      <td>38168</td>\n",
       "      <td>108426.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38169</td>\n",
       "      <td>38260</td>\n",
       "      <td>108426.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38261</td>\n",
       "      <td>38352</td>\n",
       "      <td>108426.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38353</td>\n",
       "      <td>38442</td>\n",
       "      <td>108426.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38443</td>\n",
       "      <td>38533</td>\n",
       "      <td>108426.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Fund_Codeinfo.FundID  Symbol  Startdate  EndDate    FundID\n",
       "0              108426.0       1      38078    38168  108426.0\n",
       "1              108426.0       1      38169    38260  108426.0\n",
       "2              108426.0       1      38261    38352  108426.0\n",
       "3              108426.0       1      38353    38442  108426.0\n",
       "4              108426.0       1      38443    38533  108426.0"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fund['FundID']=df_fund['Fund_Codeinfo.FundID']\n",
    "df_fund.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "55761930-8bc3-4f4a-9cd3-5da265dc2ee2",
   "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>FundID</th>\n",
       "      <th>FundCompanyID</th>\n",
       "      <th>FullName</th>\n",
       "      <th>ServiceStartDate</th>\n",
       "      <th>ServiceEndDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>108426</td>\n",
       "      <td>1059</td>\n",
       "      <td>王亚伟</td>\n",
       "      <td>37165</td>\n",
       "      <td>38533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>108426</td>\n",
       "      <td>1059</td>\n",
       "      <td>田擎</td>\n",
       "      <td>37987</td>\n",
       "      <td>38717</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>108426</td>\n",
       "      <td>1059</td>\n",
       "      <td>巩怀志</td>\n",
       "      <td>38626</td>\n",
       "      <td>40268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>108426</td>\n",
       "      <td>1059</td>\n",
       "      <td>童汀</td>\n",
       "      <td>40179</td>\n",
       "      <td>41820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>108426</td>\n",
       "      <td>1059</td>\n",
       "      <td>孙振峰</td>\n",
       "      <td>41000</td>\n",
       "      <td>41455</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FundID  FundCompanyID FullName  ServiceStartDate  ServiceEndDate\n",
       "0  108426           1059      王亚伟             37165           38533\n",
       "1  108426           1059       田擎             37987           38717\n",
       "2  108426           1059      巩怀志             38626           40268\n",
       "3  108426           1059       童汀             40179           41820\n",
       "4  108426           1059      孙振峰             41000           41455"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_manager.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "7a383e87-42f4-4e10-965f-af084b2dd66e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all = pd.merge(df_fund, df_manager, how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "a2e47bb0-f20c-4e29-8f10-f9d7df8a9d59",
   "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>Fund_Codeinfo.FundID</th>\n",
       "      <th>Symbol</th>\n",
       "      <th>Startdate</th>\n",
       "      <th>EndDate</th>\n",
       "      <th>FundID</th>\n",
       "      <th>FundCompanyID</th>\n",
       "      <th>FullName</th>\n",
       "      <th>ServiceStartDate</th>\n",
       "      <th>ServiceEndDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38078</td>\n",
       "      <td>38168</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>王亚伟</td>\n",
       "      <td>37165.0</td>\n",
       "      <td>38533.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38078</td>\n",
       "      <td>38168</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>田擎</td>\n",
       "      <td>37987.0</td>\n",
       "      <td>38717.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38078</td>\n",
       "      <td>38168</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>巩怀志</td>\n",
       "      <td>38626.0</td>\n",
       "      <td>40268.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38078</td>\n",
       "      <td>38168</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>童汀</td>\n",
       "      <td>40179.0</td>\n",
       "      <td>41820.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38078</td>\n",
       "      <td>38168</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>孙振峰</td>\n",
       "      <td>41000.0</td>\n",
       "      <td>41455.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Fund_Codeinfo.FundID  Symbol  ...  ServiceStartDate  ServiceEndDate\n",
       "0              108426.0       1  ...           37165.0         38533.0\n",
       "1              108426.0       1  ...           37987.0         38717.0\n",
       "2              108426.0       1  ...           38626.0         40268.0\n",
       "3              108426.0       1  ...           40179.0         41820.0\n",
       "4              108426.0       1  ...           41000.0         41455.0\n",
       "\n",
       "[5 rows x 9 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "53897d56-6ad2-4e9b-9a58-4d59e38a20f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "identifiers_test = 'Startdate >= ServiceStartDate and EndDate <= ServiceEndDate'\n",
    "df_test = df_all.query(identifiers_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "bc60ced8-60ae-4d92-b681-e6a107c597f4",
   "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>Fund_Codeinfo.FundID</th>\n",
       "      <th>Symbol</th>\n",
       "      <th>Startdate</th>\n",
       "      <th>EndDate</th>\n",
       "      <th>FundID</th>\n",
       "      <th>FundCompanyID</th>\n",
       "      <th>FullName</th>\n",
       "      <th>ServiceStartDate</th>\n",
       "      <th>ServiceEndDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38078</td>\n",
       "      <td>38168</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>王亚伟</td>\n",
       "      <td>37165.0</td>\n",
       "      <td>38533.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38078</td>\n",
       "      <td>38168</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>田擎</td>\n",
       "      <td>37987.0</td>\n",
       "      <td>38717.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38169</td>\n",
       "      <td>38260</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>王亚伟</td>\n",
       "      <td>37165.0</td>\n",
       "      <td>38533.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38169</td>\n",
       "      <td>38260</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>田擎</td>\n",
       "      <td>37987.0</td>\n",
       "      <td>38717.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38261</td>\n",
       "      <td>38352</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>王亚伟</td>\n",
       "      <td>37165.0</td>\n",
       "      <td>38533.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38261</td>\n",
       "      <td>38352</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>田擎</td>\n",
       "      <td>37987.0</td>\n",
       "      <td>38717.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38353</td>\n",
       "      <td>38442</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>王亚伟</td>\n",
       "      <td>37165.0</td>\n",
       "      <td>38533.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38353</td>\n",
       "      <td>38442</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>田擎</td>\n",
       "      <td>37987.0</td>\n",
       "      <td>38717.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38443</td>\n",
       "      <td>38533</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>王亚伟</td>\n",
       "      <td>37165.0</td>\n",
       "      <td>38533.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38443</td>\n",
       "      <td>38533</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>田擎</td>\n",
       "      <td>37987.0</td>\n",
       "      <td>38717.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38534</td>\n",
       "      <td>38625</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>田擎</td>\n",
       "      <td>37987.0</td>\n",
       "      <td>38717.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38626</td>\n",
       "      <td>38717</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>田擎</td>\n",
       "      <td>37987.0</td>\n",
       "      <td>38717.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38626</td>\n",
       "      <td>38717</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>巩怀志</td>\n",
       "      <td>38626.0</td>\n",
       "      <td>40268.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38718</td>\n",
       "      <td>38807</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>巩怀志</td>\n",
       "      <td>38626.0</td>\n",
       "      <td>40268.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38808</td>\n",
       "      <td>38898</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>巩怀志</td>\n",
       "      <td>38626.0</td>\n",
       "      <td>40268.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38899</td>\n",
       "      <td>38990</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>巩怀志</td>\n",
       "      <td>38626.0</td>\n",
       "      <td>40268.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>38991</td>\n",
       "      <td>39082</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>巩怀志</td>\n",
       "      <td>38626.0</td>\n",
       "      <td>40268.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>39083</td>\n",
       "      <td>39172</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>巩怀志</td>\n",
       "      <td>38626.0</td>\n",
       "      <td>40268.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>39173</td>\n",
       "      <td>39263</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>巩怀志</td>\n",
       "      <td>38626.0</td>\n",
       "      <td>40268.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>108426.0</td>\n",
       "      <td>1</td>\n",
       "      <td>39264</td>\n",
       "      <td>39355</td>\n",
       "      <td>108426.0</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>巩怀志</td>\n",
       "      <td>38626.0</td>\n",
       "      <td>40268.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Fund_Codeinfo.FundID  Symbol  ...  ServiceStartDate  ServiceEndDate\n",
       "0                108426.0       1  ...           37165.0         38533.0\n",
       "1                108426.0       1  ...           37987.0         38717.0\n",
       "14               108426.0       1  ...           37165.0         38533.0\n",
       "15               108426.0       1  ...           37987.0         38717.0\n",
       "28               108426.0       1  ...           37165.0         38533.0\n",
       "29               108426.0       1  ...           37987.0         38717.0\n",
       "42               108426.0       1  ...           37165.0         38533.0\n",
       "43               108426.0       1  ...           37987.0         38717.0\n",
       "56               108426.0       1  ...           37165.0         38533.0\n",
       "57               108426.0       1  ...           37987.0         38717.0\n",
       "71               108426.0       1  ...           37987.0         38717.0\n",
       "85               108426.0       1  ...           37987.0         38717.0\n",
       "86               108426.0       1  ...           38626.0         40268.0\n",
       "100              108426.0       1  ...           38626.0         40268.0\n",
       "114              108426.0       1  ...           38626.0         40268.0\n",
       "128              108426.0       1  ...           38626.0         40268.0\n",
       "142              108426.0       1  ...           38626.0         40268.0\n",
       "156              108426.0       1  ...           38626.0         40268.0\n",
       "170              108426.0       1  ...           38626.0         40268.0\n",
       "184              108426.0       1  ...           38626.0         40268.0\n",
       "\n",
       "[20 rows x 9 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test.head(n=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "9964f2ab-877f-4707-9983-4ec1fcaf9847",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_test.to_csv('df_test.csv',index = False, encoding='utf_8_sig')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba4ac974-b01e-4269-9988-540e0c11d65a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c942eb9d-e770-4071-bc2c-874b791e4ed0",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81e449d3-8d39-453b-8840-91e75f3f323e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d9fe387-e77c-4f7c-ad30-6c2d8e3353df",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "deb35b45-33fb-43db-b0c3-4acc5f35cb93",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3c2c224-52c5-40c4-9163-2f40b12e331b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "21edd89a-9c9d-4a35-8ba1-2638fac2f413",
   "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>V2</th>\n",
       "      <th>V2A</th>\n",
       "      <th>V9</th>\n",
       "      <th>V186</th>\n",
       "      <th>V187</th>\n",
       "      <th>V192</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   V2  V2A  V9  V186  V187  V192\n",
       "0  20   20   4     7     2     3\n",
       "1  20   20   3     7     1    10\n",
       "2  20   20   4     7     2     5\n",
       "3  20   20   3     7     1     8\n",
       "4  20   20   4     7     3     1"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "cols = ['V2','V2A','V9','V186','V187','V192']\n",
    "df_wv = pd.read_excel('WV5_Data_Spss_v20180912.xlsx',header=0, usecols=cols)\n",
    "df_wv.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "0b30ce3e-4a19-4430-8594-124121470fd7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_melt = df_wv.melt(id_vars=['V2','V2A'],value_vars=['V9','V186','V187','V192'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "2afcf31b-2380-4b9c-a07c-d4765cd572b9",
   "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>V2</th>\n",
       "      <th>V2A</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V9</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V9</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V9</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V9</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V9</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   V2  V2A variable  value\n",
       "0  20   20       V9      4\n",
       "1  20   20       V9      3\n",
       "2  20   20       V9      4\n",
       "3  20   20       V9      3\n",
       "4  20   20       V9      4"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_melt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "66d4bbd4-57c1-4214-bf5a-9f4157455faf",
   "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>V2</th>\n",
       "      <th>V2A</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>-2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>3</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>4</td>\n",
       "      <td>125</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   V2  V2A variable  value  count\n",
       "0  20   20     V186     -2      5\n",
       "1  20   20     V186      1     11\n",
       "2  20   20     V186      2     50\n",
       "3  20   20     V186      3     56\n",
       "4  20   20     V186      4    125"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_t1 = df_melt.groupby(['V2','V2A','variable','value']).agg('size').reset_index(name='count')\n",
    "df_t1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65e18586-c3c7-44fb-9704-7054eb8262a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_t2 = df_t1.groupby(['V2','V2A','variable']).agg({'count': 'sum'})\n",
    "df_t2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "536fcf9a-aba6-4dc9-9c65-5e01a4b9a7bf",
   "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>V2</th>\n",
       "      <th>V2A</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "      <th>count_x</th>\n",
       "      <th>count_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>-2</td>\n",
       "      <td>5</td>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>3</td>\n",
       "      <td>56</td>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>4</td>\n",
       "      <td>125</td>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   V2  V2A variable  value  count_x  count_y\n",
       "0  20   20     V186     -2        5     1003\n",
       "1  20   20     V186      1       11     1003\n",
       "2  20   20     V186      2       50     1003\n",
       "3  20   20     V186      3       56     1003\n",
       "4  20   20     V186      4      125     1003"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tt = pd.merge(df_t1,df_t2,on=['V2','V2A','variable'],how='left')\n",
    "df_tt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "6190a9e9-516b-4b73-a837-3b9c5a775e75",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_tt['percent']= df_tt['count_x']/df_tt['count_y']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "14b79b6a-20bb-49f7-97fe-6e83c7896190",
   "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>V2</th>\n",
       "      <th>V2A</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "      <th>count_x</th>\n",
       "      <th>count_y</th>\n",
       "      <th>percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>-2</td>\n",
       "      <td>5</td>\n",
       "      <td>1003</td>\n",
       "      <td>0.004985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>1003</td>\n",
       "      <td>0.010967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>1003</td>\n",
       "      <td>0.049850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>3</td>\n",
       "      <td>56</td>\n",
       "      <td>1003</td>\n",
       "      <td>0.055833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>4</td>\n",
       "      <td>125</td>\n",
       "      <td>1003</td>\n",
       "      <td>0.124626</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   V2  V2A variable  value  count_x  count_y   percent\n",
       "0  20   20     V186     -2        5     1003  0.004985\n",
       "1  20   20     V186      1       11     1003  0.010967\n",
       "2  20   20     V186      2       50     1003  0.049850\n",
       "3  20   20     V186      3       56     1003  0.055833\n",
       "4  20   20     V186      4      125     1003  0.124626"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "748e87fc-d74d-46ec-8dde-f354d08dfcda",
   "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>V2</th>\n",
       "      <th>V2A</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "      <th>count_x</th>\n",
       "      <th>count_y</th>\n",
       "      <th>percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>1003</td>\n",
       "      <td>0.010967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>32</td>\n",
       "      <td>32</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>57</td>\n",
       "      <td>1002</td>\n",
       "      <td>0.056886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>1421</td>\n",
       "      <td>0.039409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>76</td>\n",
       "      <td>76</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>335</td>\n",
       "      <td>1500</td>\n",
       "      <td>0.223333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>1001</td>\n",
       "      <td>0.012987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>124</td>\n",
       "      <td>124</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>133</td>\n",
       "      <td>2164</td>\n",
       "      <td>0.061460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>152</td>\n",
       "      <td>152</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>70</td>\n",
       "      <td>1000</td>\n",
       "      <td>0.070000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>156</td>\n",
       "      <td>156</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>1991</td>\n",
       "      <td>0.011050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>158</td>\n",
       "      <td>158</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>41</td>\n",
       "      <td>1227</td>\n",
       "      <td>0.033415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>271</th>\n",
       "      <td>170</td>\n",
       "      <td>170</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>326</td>\n",
       "      <td>3025</td>\n",
       "      <td>0.107769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>295</th>\n",
       "      <td>196</td>\n",
       "      <td>196</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>44</td>\n",
       "      <td>1050</td>\n",
       "      <td>0.041905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>322</th>\n",
       "      <td>231</td>\n",
       "      <td>231</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>559</td>\n",
       "      <td>1500</td>\n",
       "      <td>0.372667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>352</th>\n",
       "      <td>246</td>\n",
       "      <td>246</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>1014</td>\n",
       "      <td>0.018738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>386</th>\n",
       "      <td>250</td>\n",
       "      <td>250</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>1001</td>\n",
       "      <td>0.015984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>416</th>\n",
       "      <td>268</td>\n",
       "      <td>268</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>57</td>\n",
       "      <td>1500</td>\n",
       "      <td>0.038000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>276</td>\n",
       "      <td>900</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>988</td>\n",
       "      <td>0.015182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>483</th>\n",
       "      <td>276</td>\n",
       "      <td>901</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1076</td>\n",
       "      <td>0.004647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514</th>\n",
       "      <td>288</td>\n",
       "      <td>288</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>692</td>\n",
       "      <td>1534</td>\n",
       "      <td>0.451108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>544</th>\n",
       "      <td>320</td>\n",
       "      <td>320</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>339</td>\n",
       "      <td>1000</td>\n",
       "      <td>0.339000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>572</th>\n",
       "      <td>344</td>\n",
       "      <td>344</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>69</td>\n",
       "      <td>1252</td>\n",
       "      <td>0.055112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>601</th>\n",
       "      <td>348</td>\n",
       "      <td>348</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>1007</td>\n",
       "      <td>0.013903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>633</th>\n",
       "      <td>356</td>\n",
       "      <td>356</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>427</td>\n",
       "      <td>2001</td>\n",
       "      <td>0.213393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658</th>\n",
       "      <td>360</td>\n",
       "      <td>360</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>599</td>\n",
       "      <td>2015</td>\n",
       "      <td>0.297270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>692</th>\n",
       "      <td>364</td>\n",
       "      <td>364</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>541</td>\n",
       "      <td>2667</td>\n",
       "      <td>0.202850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>722</th>\n",
       "      <td>368</td>\n",
       "      <td>368</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>418</td>\n",
       "      <td>2701</td>\n",
       "      <td>0.154757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>754</th>\n",
       "      <td>380</td>\n",
       "      <td>380</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>74</td>\n",
       "      <td>1012</td>\n",
       "      <td>0.073123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>785</th>\n",
       "      <td>392</td>\n",
       "      <td>392</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>1096</td>\n",
       "      <td>0.017336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>830</th>\n",
       "      <td>410</td>\n",
       "      <td>410</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>228</td>\n",
       "      <td>1200</td>\n",
       "      <td>0.190000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>858</th>\n",
       "      <td>458</td>\n",
       "      <td>458</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>809</td>\n",
       "      <td>1201</td>\n",
       "      <td>0.673605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>886</th>\n",
       "      <td>466</td>\n",
       "      <td>466</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>861</td>\n",
       "      <td>1534</td>\n",
       "      <td>0.561278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>920</th>\n",
       "      <td>484</td>\n",
       "      <td>484</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>186</td>\n",
       "      <td>1560</td>\n",
       "      <td>0.119231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>950</th>\n",
       "      <td>498</td>\n",
       "      <td>498</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>47</td>\n",
       "      <td>1046</td>\n",
       "      <td>0.044933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>980</th>\n",
       "      <td>504</td>\n",
       "      <td>504</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>1014</td>\n",
       "      <td>1200</td>\n",
       "      <td>0.845000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1009</th>\n",
       "      <td>528</td>\n",
       "      <td>528</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>1050</td>\n",
       "      <td>0.023810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1043</th>\n",
       "      <td>554</td>\n",
       "      <td>554</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>46</td>\n",
       "      <td>954</td>\n",
       "      <td>0.048218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1075</th>\n",
       "      <td>578</td>\n",
       "      <td>578</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>1025</td>\n",
       "      <td>0.007805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1104</th>\n",
       "      <td>604</td>\n",
       "      <td>604</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>156</td>\n",
       "      <td>1500</td>\n",
       "      <td>0.104000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1133</th>\n",
       "      <td>616</td>\n",
       "      <td>616</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>91</td>\n",
       "      <td>1000</td>\n",
       "      <td>0.091000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1164</th>\n",
       "      <td>642</td>\n",
       "      <td>642</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>78</td>\n",
       "      <td>1776</td>\n",
       "      <td>0.043919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1197</th>\n",
       "      <td>643</td>\n",
       "      <td>643</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>2033</td>\n",
       "      <td>0.009346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1230</th>\n",
       "      <td>646</td>\n",
       "      <td>646</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>1238</td>\n",
       "      <td>1507</td>\n",
       "      <td>0.821500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1260</th>\n",
       "      <td>688</td>\n",
       "      <td>688</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "      <td>1220</td>\n",
       "      <td>0.042623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1293</th>\n",
       "      <td>704</td>\n",
       "      <td>704</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>27</td>\n",
       "      <td>1495</td>\n",
       "      <td>0.018060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1328</th>\n",
       "      <td>705</td>\n",
       "      <td>705</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>1037</td>\n",
       "      <td>0.015429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1358</th>\n",
       "      <td>710</td>\n",
       "      <td>710</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>487</td>\n",
       "      <td>2988</td>\n",
       "      <td>0.162985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1388</th>\n",
       "      <td>724</td>\n",
       "      <td>724</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>1200</td>\n",
       "      <td>0.016667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1421</th>\n",
       "      <td>752</td>\n",
       "      <td>752</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1003</td>\n",
       "      <td>0.004985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1450</th>\n",
       "      <td>756</td>\n",
       "      <td>756</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>33</td>\n",
       "      <td>1241</td>\n",
       "      <td>0.026591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1482</th>\n",
       "      <td>764</td>\n",
       "      <td>764</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>348</td>\n",
       "      <td>1534</td>\n",
       "      <td>0.226858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1510</th>\n",
       "      <td>780</td>\n",
       "      <td>780</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>184</td>\n",
       "      <td>1002</td>\n",
       "      <td>0.183633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1543</th>\n",
       "      <td>792</td>\n",
       "      <td>792</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>184</td>\n",
       "      <td>1346</td>\n",
       "      <td>0.136701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1576</th>\n",
       "      <td>804</td>\n",
       "      <td>804</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>1000</td>\n",
       "      <td>0.022000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1608</th>\n",
       "      <td>818</td>\n",
       "      <td>818</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>737</td>\n",
       "      <td>3051</td>\n",
       "      <td>0.241560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1635</th>\n",
       "      <td>826</td>\n",
       "      <td>826</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>74</td>\n",
       "      <td>1041</td>\n",
       "      <td>0.071085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1669</th>\n",
       "      <td>840</td>\n",
       "      <td>840</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>147</td>\n",
       "      <td>1249</td>\n",
       "      <td>0.117694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1698</th>\n",
       "      <td>854</td>\n",
       "      <td>854</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>839</td>\n",
       "      <td>1534</td>\n",
       "      <td>0.546936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1732</th>\n",
       "      <td>858</td>\n",
       "      <td>858</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>43</td>\n",
       "      <td>1000</td>\n",
       "      <td>0.043000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1759</th>\n",
       "      <td>894</td>\n",
       "      <td>894</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>413</td>\n",
       "      <td>1500</td>\n",
       "      <td>0.275333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       V2  V2A variable  value  count_x  count_y   percent\n",
       "1      20   20     V186      1       11     1003  0.010967\n",
       "31     32   32     V186      1       57     1002  0.056886\n",
       "62     36   36     V186      1       56     1421  0.039409\n",
       "90     76   76     V186      1      335     1500  0.223333\n",
       "119   100  100     V186      1       13     1001  0.012987\n",
       "150   124  124     V186      1      133     2164  0.061460\n",
       "182   152  152     V186      1       70     1000  0.070000\n",
       "214   156  156     V186      1       22     1991  0.011050\n",
       "244   158  158     V186      1       41     1227  0.033415\n",
       "271   170  170     V186      1      326     3025  0.107769\n",
       "295   196  196     V186      1       44     1050  0.041905\n",
       "322   231  231     V186      1      559     1500  0.372667\n",
       "352   246  246     V186      1       19     1014  0.018738\n",
       "386   250  250     V186      1       16     1001  0.015984\n",
       "416   268  268     V186      1       57     1500  0.038000\n",
       "449   276  900     V186      1       15      988  0.015182\n",
       "483   276  901     V186      1        5     1076  0.004647\n",
       "514   288  288     V186      1      692     1534  0.451108\n",
       "544   320  320     V186      1      339     1000  0.339000\n",
       "572   344  344     V186      1       69     1252  0.055112\n",
       "601   348  348     V186      1       14     1007  0.013903\n",
       "633   356  356     V186      1      427     2001  0.213393\n",
       "658   360  360     V186      1      599     2015  0.297270\n",
       "692   364  364     V186      1      541     2667  0.202850\n",
       "722   368  368     V186      1      418     2701  0.154757\n",
       "754   380  380     V186      1       74     1012  0.073123\n",
       "785   392  392     V186      1       19     1096  0.017336\n",
       "830   410  410     V186      1      228     1200  0.190000\n",
       "858   458  458     V186      1      809     1201  0.673605\n",
       "886   466  466     V186      1      861     1534  0.561278\n",
       "920   484  484     V186      1      186     1560  0.119231\n",
       "950   498  498     V186      1       47     1046  0.044933\n",
       "980   504  504     V186      1     1014     1200  0.845000\n",
       "1009  528  528     V186      1       25     1050  0.023810\n",
       "1043  554  554     V186      1       46      954  0.048218\n",
       "1075  578  578     V186      1        8     1025  0.007805\n",
       "1104  604  604     V186      1      156     1500  0.104000\n",
       "1133  616  616     V186      1       91     1000  0.091000\n",
       "1164  642  642     V186      1       78     1776  0.043919\n",
       "1197  643  643     V186      1       19     2033  0.009346\n",
       "1230  646  646     V186      1     1238     1507  0.821500\n",
       "1260  688  688     V186      1       52     1220  0.042623\n",
       "1293  704  704     V186      1       27     1495  0.018060\n",
       "1328  705  705     V186      1       16     1037  0.015429\n",
       "1358  710  710     V186      1      487     2988  0.162985\n",
       "1388  724  724     V186      1       20     1200  0.016667\n",
       "1421  752  752     V186      1        5     1003  0.004985\n",
       "1450  756  756     V186      1       33     1241  0.026591\n",
       "1482  764  764     V186      1      348     1534  0.226858\n",
       "1510  780  780     V186      1      184     1002  0.183633\n",
       "1543  792  792     V186      1      184     1346  0.136701\n",
       "1576  804  804     V186      1       22     1000  0.022000\n",
       "1608  818  818     V186      1      737     3051  0.241560\n",
       "1635  826  826     V186      1       74     1041  0.071085\n",
       "1669  840  840     V186      1      147     1249  0.117694\n",
       "1698  854  854     V186      1      839     1534  0.546936\n",
       "1732  858  858     V186      1       43     1000  0.043000\n",
       "1759  894  894     V186      1      413     1500  0.275333"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tt_query = \"variable == 'V186' and value == 1 \"\n",
    "df_tt.query(tt_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "e5da4408-de69-4a79-8585-aaff547abfe3",
   "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>V2</th>\n",
       "      <th>V2A</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "      <th>count_x</th>\n",
       "      <th>count_y</th>\n",
       "      <th>percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>1003</td>\n",
       "      <td>0.010967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V9</td>\n",
       "      <td>1</td>\n",
       "      <td>79</td>\n",
       "      <td>1003</td>\n",
       "      <td>0.078764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V9</td>\n",
       "      <td>2</td>\n",
       "      <td>205</td>\n",
       "      <td>1003</td>\n",
       "      <td>0.204387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>32</td>\n",
       "      <td>32</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>57</td>\n",
       "      <td>1002</td>\n",
       "      <td>0.056886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>32</td>\n",
       "      <td>32</td>\n",
       "      <td>V9</td>\n",
       "      <td>1</td>\n",
       "      <td>315</td>\n",
       "      <td>1002</td>\n",
       "      <td>0.314371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1755</th>\n",
       "      <td>858</td>\n",
       "      <td>858</td>\n",
       "      <td>V9</td>\n",
       "      <td>1</td>\n",
       "      <td>226</td>\n",
       "      <td>1000</td>\n",
       "      <td>0.226000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1756</th>\n",
       "      <td>858</td>\n",
       "      <td>858</td>\n",
       "      <td>V9</td>\n",
       "      <td>2</td>\n",
       "      <td>230</td>\n",
       "      <td>1000</td>\n",
       "      <td>0.230000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1759</th>\n",
       "      <td>894</td>\n",
       "      <td>894</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>413</td>\n",
       "      <td>1500</td>\n",
       "      <td>0.275333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1786</th>\n",
       "      <td>894</td>\n",
       "      <td>894</td>\n",
       "      <td>V9</td>\n",
       "      <td>1</td>\n",
       "      <td>1134</td>\n",
       "      <td>1500</td>\n",
       "      <td>0.756000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1787</th>\n",
       "      <td>894</td>\n",
       "      <td>894</td>\n",
       "      <td>V9</td>\n",
       "      <td>2</td>\n",
       "      <td>244</td>\n",
       "      <td>1500</td>\n",
       "      <td>0.162667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>174 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       V2  V2A variable  value  count_x  count_y   percent\n",
       "1      20   20     V186      1       11     1003  0.010967\n",
       "27     20   20       V9      1       79     1003  0.078764\n",
       "28     20   20       V9      2      205     1003  0.204387\n",
       "31     32   32     V186      1       57     1002  0.056886\n",
       "57     32   32       V9      1      315     1002  0.314371\n",
       "...   ...  ...      ...    ...      ...      ...       ...\n",
       "1755  858  858       V9      1      226     1000  0.226000\n",
       "1756  858  858       V9      2      230     1000  0.230000\n",
       "1759  894  894     V186      1      413     1500  0.275333\n",
       "1786  894  894       V9      1     1134     1500  0.756000\n",
       "1787  894  894       V9      2      244     1500  0.162667\n",
       "\n",
       "[174 rows x 7 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tt_query = \"variable == 'V186' and value == 1 | variable == 'V9' and value == [1,2]\"\n",
    "df_tt.query(tt_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "4c8fa8bc-10f6-4101-a3f3-6271a4b6c4a8",
   "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></th>\n",
       "      <th></th>\n",
       "      <th>percent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V2</th>\n",
       "      <th>V2A</th>\n",
       "      <th>variable</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">20</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">20</th>\n",
       "      <th>V186</th>\n",
       "      <td>0.010967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V9</th>\n",
       "      <td>0.283151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">32</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">32</th>\n",
       "      <th>V186</th>\n",
       "      <td>0.056886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V9</th>\n",
       "      <td>0.632735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <th>36</th>\n",
       "      <th>V186</th>\n",
       "      <td>0.039409</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  percent\n",
       "V2 V2A variable          \n",
       "20 20  V186      0.010967\n",
       "       V9        0.283151\n",
       "32 32  V186      0.056886\n",
       "       V9        0.632735\n",
       "36 36  V186      0.039409"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_t_final = df_tt.query(tt_query)\n",
    "# df_final_new = df_t_final.groupby(['V2','V2A','variable']).agg({'percent': 'sum'})\n",
    "# df_final_new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "c1ee9e45-fecd-41b1-925d-0fda25b8866f",
   "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>V2</th>\n",
       "      <th>V2A</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "      <th>count_x</th>\n",
       "      <th>count_y</th>\n",
       "      <th>percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>1003</td>\n",
       "      <td>0.010967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V9</td>\n",
       "      <td>1</td>\n",
       "      <td>79</td>\n",
       "      <td>1003</td>\n",
       "      <td>0.078764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V9</td>\n",
       "      <td>2</td>\n",
       "      <td>205</td>\n",
       "      <td>1003</td>\n",
       "      <td>0.204387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>32</td>\n",
       "      <td>32</td>\n",
       "      <td>V186</td>\n",
       "      <td>1</td>\n",
       "      <td>57</td>\n",
       "      <td>1002</td>\n",
       "      <td>0.056886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>32</td>\n",
       "      <td>32</td>\n",
       "      <td>V9</td>\n",
       "      <td>1</td>\n",
       "      <td>315</td>\n",
       "      <td>1002</td>\n",
       "      <td>0.314371</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    V2  V2A variable  value  count_x  count_y   percent\n",
       "1   20   20     V186      1       11     1003  0.010967\n",
       "27  20   20       V9      1       79     1003  0.078764\n",
       "28  20   20       V9      2      205     1003  0.204387\n",
       "31  32   32     V186      1       57     1002  0.056886\n",
       "57  32   32       V9      1      315     1002  0.314371"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_t_final.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "cdc955e2-3c40-41e4-82c6-7517ec453f63",
   "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></th>\n",
       "      <th></th>\n",
       "      <th>percent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V2</th>\n",
       "      <th>V2A</th>\n",
       "      <th>variable</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">20</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">20</th>\n",
       "      <th>V186</th>\n",
       "      <td>0.010967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V9</th>\n",
       "      <td>0.283151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">32</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">32</th>\n",
       "      <th>V186</th>\n",
       "      <td>0.056886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V9</th>\n",
       "      <td>0.632735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <th>36</th>\n",
       "      <th>V186</th>\n",
       "      <td>0.039409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>854</th>\n",
       "      <th>854</th>\n",
       "      <th>V9</th>\n",
       "      <td>0.947849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">858</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">858</th>\n",
       "      <th>V186</th>\n",
       "      <td>0.043000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V9</th>\n",
       "      <td>0.456000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">894</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">894</th>\n",
       "      <th>V186</th>\n",
       "      <td>0.275333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V9</th>\n",
       "      <td>0.918667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>116 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   percent\n",
       "V2  V2A variable          \n",
       "20  20  V186      0.010967\n",
       "        V9        0.283151\n",
       "32  32  V186      0.056886\n",
       "        V9        0.632735\n",
       "36  36  V186      0.039409\n",
       "...                    ...\n",
       "854 854 V9        0.947849\n",
       "858 858 V186      0.043000\n",
       "        V9        0.456000\n",
       "894 894 V186      0.275333\n",
       "        V9        0.918667\n",
       "\n",
       "[116 rows x 1 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_t_final.groupby(['V2','V2A','variable']).agg({'percent': 'sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "28efd42b-666a-49fc-96c1-9296dff91505",
   "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></th>\n",
       "      <th></th>\n",
       "      <th>percent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V2</th>\n",
       "      <th>V2A</th>\n",
       "      <th>variable</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">20</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">20</th>\n",
       "      <th>V186</th>\n",
       "      <td>0.010967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V9</th>\n",
       "      <td>0.283151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">32</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">32</th>\n",
       "      <th>V186</th>\n",
       "      <td>0.056886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V9</th>\n",
       "      <td>0.632735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <th>36</th>\n",
       "      <th>V186</th>\n",
       "      <td>0.039409</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  percent\n",
       "V2 V2A variable          \n",
       "20 20  V186      0.010967\n",
       "       V9        0.283151\n",
       "32 32  V186      0.056886\n",
       "       V9        0.632735\n",
       "36 36  V186      0.039409"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final_new = df_t_final.groupby(['V2','V2A','variable']).agg({'percent': 'sum'})\n",
    "df_final_new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "a0f19f13-c75d-47f1-af35-8786214a93b1",
   "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>V2</th>\n",
       "      <th>V2A</th>\n",
       "      <th>variable</th>\n",
       "      <th>percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V186</td>\n",
       "      <td>0.010967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>V9</td>\n",
       "      <td>0.283151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>32</td>\n",
       "      <td>32</td>\n",
       "      <td>V186</td>\n",
       "      <td>0.056886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>32</td>\n",
       "      <td>32</td>\n",
       "      <td>V9</td>\n",
       "      <td>0.632735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>V186</td>\n",
       "      <td>0.039409</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   V2  V2A variable   percent\n",
       "0  20   20     V186  0.010967\n",
       "1  20   20       V9  0.283151\n",
       "2  32   32     V186  0.056886\n",
       "3  32   32       V9  0.632735\n",
       "4  36   36     V186  0.039409"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df_final_new对variable进行行透视成列，variable的value放到列名去。\n",
    "df_final_melt = df_final_new.reset_index()\n",
    "df_final_melt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "e03ff0f6-6096-4d73-a5a7-add89cc53a13",
   "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>V186</th>\n",
       "      <th>V9</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>V2</th>\n",
       "      <th>V2A</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <th>20</th>\n",
       "      <td>0.010967</td>\n",
       "      <td>0.283151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <th>32</th>\n",
       "      <td>0.056886</td>\n",
       "      <td>0.632735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <th>36</th>\n",
       "      <td>0.039409</td>\n",
       "      <td>0.387051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <th>76</th>\n",
       "      <td>0.223333</td>\n",
       "      <td>0.912667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <th>100</th>\n",
       "      <td>0.012987</td>\n",
       "      <td>0.490509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <th>124</th>\n",
       "      <td>0.061460</td>\n",
       "      <td>0.617375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <th>152</th>\n",
       "      <td>0.070000</td>\n",
       "      <td>0.701000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <th>156</th>\n",
       "      <td>0.011050</td>\n",
       "      <td>0.162732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <th>158</th>\n",
       "      <td>0.033415</td>\n",
       "      <td>0.509372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <th>170</th>\n",
       "      <td>0.107769</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <th>196</th>\n",
       "      <td>0.041905</td>\n",
       "      <td>0.770476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <th>231</th>\n",
       "      <td>0.372667</td>\n",
       "      <td>0.936667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <th>246</th>\n",
       "      <td>0.018738</td>\n",
       "      <td>0.450690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <th>250</th>\n",
       "      <td>0.015984</td>\n",
       "      <td>0.410589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <th>268</th>\n",
       "      <td>0.038000</td>\n",
       "      <td>0.964000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">276</th>\n",
       "      <th>900</th>\n",
       "      <td>0.015182</td>\n",
       "      <td>0.440283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>901</th>\n",
       "      <td>0.004647</td>\n",
       "      <td>0.225836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>288</th>\n",
       "      <th>288</th>\n",
       "      <td>0.451108</td>\n",
       "      <td>0.973272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320</th>\n",
       "      <th>320</th>\n",
       "      <td>0.339000</td>\n",
       "      <td>0.953000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>344</th>\n",
       "      <th>344</th>\n",
       "      <td>0.055112</td>\n",
       "      <td>0.270767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>348</th>\n",
       "      <th>348</th>\n",
       "      <td>0.013903</td>\n",
       "      <td>0.367428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356</th>\n",
       "      <th>356</th>\n",
       "      <td>0.213393</td>\n",
       "      <td>0.779610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <th>360</th>\n",
       "      <td>0.297270</td>\n",
       "      <td>0.980149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>364</th>\n",
       "      <th>364</th>\n",
       "      <td>0.202850</td>\n",
       "      <td>0.943382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368</th>\n",
       "      <th>368</th>\n",
       "      <td>0.154757</td>\n",
       "      <td>0.989263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>380</th>\n",
       "      <th>380</th>\n",
       "      <td>0.073123</td>\n",
       "      <td>0.751976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392</th>\n",
       "      <th>392</th>\n",
       "      <td>0.017336</td>\n",
       "      <td>0.173358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>400</th>\n",
       "      <th>400</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.995833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>410</th>\n",
       "      <th>410</th>\n",
       "      <td>0.190000</td>\n",
       "      <td>0.480833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458</th>\n",
       "      <th>458</th>\n",
       "      <td>0.673605</td>\n",
       "      <td>0.959201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>466</th>\n",
       "      <th>466</th>\n",
       "      <td>0.561278</td>\n",
       "      <td>0.964798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>484</th>\n",
       "      <th>484</th>\n",
       "      <td>0.119231</td>\n",
       "      <td>0.846154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>498</th>\n",
       "      <th>498</th>\n",
       "      <td>0.044933</td>\n",
       "      <td>0.728489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>504</th>\n",
       "      <th>504</th>\n",
       "      <td>0.845000</td>\n",
       "      <td>0.984167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>528</th>\n",
       "      <th>528</th>\n",
       "      <td>0.023810</td>\n",
       "      <td>0.320952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>554</th>\n",
       "      <th>554</th>\n",
       "      <td>0.048218</td>\n",
       "      <td>0.342767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>578</th>\n",
       "      <th>578</th>\n",
       "      <td>0.007805</td>\n",
       "      <td>0.327805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>604</th>\n",
       "      <th>604</th>\n",
       "      <td>0.104000</td>\n",
       "      <td>0.760667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>616</th>\n",
       "      <th>616</th>\n",
       "      <td>0.091000</td>\n",
       "      <td>0.851000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>642</th>\n",
       "      <th>642</th>\n",
       "      <td>0.043919</td>\n",
       "      <td>0.890203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643</th>\n",
       "      <th>643</th>\n",
       "      <td>0.009346</td>\n",
       "      <td>0.457452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>646</th>\n",
       "      <th>646</th>\n",
       "      <td>0.821500</td>\n",
       "      <td>0.956204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688</th>\n",
       "      <th>688</th>\n",
       "      <td>0.042623</td>\n",
       "      <td>0.655738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>704</th>\n",
       "      <th>704</th>\n",
       "      <td>0.018060</td>\n",
       "      <td>0.323746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>705</th>\n",
       "      <th>705</th>\n",
       "      <td>0.015429</td>\n",
       "      <td>0.423337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>710</th>\n",
       "      <th>710</th>\n",
       "      <td>0.162985</td>\n",
       "      <td>0.910643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>724</th>\n",
       "      <th>724</th>\n",
       "      <td>0.016667</td>\n",
       "      <td>0.389167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>752</th>\n",
       "      <th>752</th>\n",
       "      <td>0.004985</td>\n",
       "      <td>0.294118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>756</th>\n",
       "      <th>756</th>\n",
       "      <td>0.026591</td>\n",
       "      <td>0.460113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>764</th>\n",
       "      <th>764</th>\n",
       "      <td>0.226858</td>\n",
       "      <td>0.937419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>780</th>\n",
       "      <th>780</th>\n",
       "      <td>0.183633</td>\n",
       "      <td>0.901198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>792</th>\n",
       "      <th>792</th>\n",
       "      <td>0.136701</td>\n",
       "      <td>0.913076</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>804</th>\n",
       "      <th>804</th>\n",
       "      <td>0.022000</td>\n",
       "      <td>0.536000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>818</th>\n",
       "      <th>818</th>\n",
       "      <td>0.241560</td>\n",
       "      <td>0.996067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>826</th>\n",
       "      <th>826</th>\n",
       "      <td>0.071085</td>\n",
       "      <td>0.392891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>840</th>\n",
       "      <th>840</th>\n",
       "      <td>0.117694</td>\n",
       "      <td>0.720576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>854</th>\n",
       "      <th>854</th>\n",
       "      <td>0.546936</td>\n",
       "      <td>0.947849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>858</th>\n",
       "      <th>858</th>\n",
       "      <td>0.043000</td>\n",
       "      <td>0.456000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>894</th>\n",
       "      <th>894</th>\n",
       "      <td>0.275333</td>\n",
       "      <td>0.918667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "variable      V186        V9\n",
       "V2  V2A                     \n",
       "20  20    0.010967  0.283151\n",
       "32  32    0.056886  0.632735\n",
       "36  36    0.039409  0.387051\n",
       "76  76    0.223333  0.912667\n",
       "100 100   0.012987  0.490509\n",
       "124 124   0.061460  0.617375\n",
       "152 152   0.070000  0.701000\n",
       "156 156   0.011050  0.162732\n",
       "158 158   0.033415  0.509372\n",
       "170 170   0.107769       NaN\n",
       "196 196   0.041905  0.770476\n",
       "231 231   0.372667  0.936667\n",
       "246 246   0.018738  0.450690\n",
       "250 250   0.015984  0.410589\n",
       "268 268   0.038000  0.964000\n",
       "276 900   0.015182  0.440283\n",
       "    901   0.004647  0.225836\n",
       "288 288   0.451108  0.973272\n",
       "320 320   0.339000  0.953000\n",
       "344 344   0.055112  0.270767\n",
       "348 348   0.013903  0.367428\n",
       "356 356   0.213393  0.779610\n",
       "360 360   0.297270  0.980149\n",
       "364 364   0.202850  0.943382\n",
       "368 368   0.154757  0.989263\n",
       "380 380   0.073123  0.751976\n",
       "392 392   0.017336  0.173358\n",
       "400 400        NaN  0.995833\n",
       "410 410   0.190000  0.480833\n",
       "458 458   0.673605  0.959201\n",
       "466 466   0.561278  0.964798\n",
       "484 484   0.119231  0.846154\n",
       "498 498   0.044933  0.728489\n",
       "504 504   0.845000  0.984167\n",
       "528 528   0.023810  0.320952\n",
       "554 554   0.048218  0.342767\n",
       "578 578   0.007805  0.327805\n",
       "604 604   0.104000  0.760667\n",
       "616 616   0.091000  0.851000\n",
       "642 642   0.043919  0.890203\n",
       "643 643   0.009346  0.457452\n",
       "646 646   0.821500  0.956204\n",
       "688 688   0.042623  0.655738\n",
       "704 704   0.018060  0.323746\n",
       "705 705   0.015429  0.423337\n",
       "710 710   0.162985  0.910643\n",
       "724 724   0.016667  0.389167\n",
       "752 752   0.004985  0.294118\n",
       "756 756   0.026591  0.460113\n",
       "764 764   0.226858  0.937419\n",
       "780 780   0.183633  0.901198\n",
       "792 792   0.136701  0.913076\n",
       "804 804   0.022000  0.536000\n",
       "818 818   0.241560  0.996067\n",
       "826 826   0.071085  0.392891\n",
       "840 840   0.117694  0.720576\n",
       "854 854   0.546936  0.947849\n",
       "858 858   0.043000  0.456000\n",
       "894 894   0.275333  0.918667"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final_melt.set_index([\"V2\", \"V2A\",\"variable\"])[\"percent\"].unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce7f4e1f-1368-4c8b-90b8-24c27d6ced07",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}