week3/6-4.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " \n",
    "\n",
    "### 5. pandas의 pivot_table 학습하기 \n",
    "\n",
    "예시를 통해 우선 먼저 pivot_table이 무엇인지 알아보겠습니다. 아래 이름의 엑셀파일을 통해서 연습부터 해볼겁니다. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>Account</th>\n",
       "      <th>Name</th>\n",
       "      <th>Rep</th>\n",
       "      <th>Manager</th>\n",
       "      <th>Product</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Price</th>\n",
       "      <th>Status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>1</td>\n",
       "      <td>30000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>Software</td>\n",
       "      <td>1</td>\n",
       "      <td>10000</td>\n",
       "      <td>presented</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>714466</td>\n",
       "      <td>Trantow-Barrows</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>Maintenance</td>\n",
       "      <td>2</td>\n",
       "      <td>5000</td>\n",
       "      <td>pending</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>737550</td>\n",
       "      <td>Fritsch, Russel and Anderson</td>\n",
       "      <td>Craig Booker</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>1</td>\n",
       "      <td>35000</td>\n",
       "      <td>declined</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>146832</td>\n",
       "      <td>Kiehn-Spinka</td>\n",
       "      <td>Daniel Hilton</td>\n",
       "      <td>Debra Henley</td>\n",
       "      <td>CPU</td>\n",
       "      <td>2</td>\n",
       "      <td>65000</td>\n",
       "      <td>won</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Account                          Name            Rep       Manager  \\\n",
       "0   714466               Trantow-Barrows   Craig Booker  Debra Henley   \n",
       "1   714466               Trantow-Barrows   Craig Booker  Debra Henley   \n",
       "2   714466               Trantow-Barrows   Craig Booker  Debra Henley   \n",
       "3   737550  Fritsch, Russel and Anderson   Craig Booker  Debra Henley   \n",
       "4   146832                  Kiehn-Spinka  Daniel Hilton  Debra Henley   \n",
       "\n",
       "       Product  Quantity  Price     Status  \n",
       "0          CPU         1  30000  presented  \n",
       "1     Software         1  10000  presented  \n",
       "2  Maintenance         2   5000    pending  \n",
       "3          CPU         1  35000   declined  \n",
       "4          CPU         2  65000        won  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = pd.read_excel(\"C:\\\\Users\\\\user\\\\Downloads\\\\sales-funnel.xlsx\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "항상 이 데이터가 뭔지 살펴보는 것 잊지 마시구요. \n",
    "\n",
    "Name을 기준으로 정보들을 합쳐주고 싶다면, 아래 코드를 작성하면 됩니다. Name의 중복된 이름을 가진 데이터는 합쳐지고 숫자형 데이터 컬럼만 남게 됩니다. 합쳐질 때에는 value들이 평균값을 가지게 됩니다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>Account</th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Barton LLC</th>\n",
       "      <td>740150</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fritsch, Russel and Anderson</th>\n",
       "      <td>737550</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herman LLC</th>\n",
       "      <td>141962</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jerde-Hilpert</th>\n",
       "      <td>412290</td>\n",
       "      <td>5000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kassulke, Ondricka and Metz</th>\n",
       "      <td>307599</td>\n",
       "      <td>7000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Keeling LLC</th>\n",
       "      <td>688981</td>\n",
       "      <td>100000</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kiehn-Spinka</th>\n",
       "      <td>146832</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Koepp Ltd</th>\n",
       "      <td>729833</td>\n",
       "      <td>35000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kulas Inc</th>\n",
       "      <td>218895</td>\n",
       "      <td>25000</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purdy-Kunde</th>\n",
       "      <td>163416</td>\n",
       "      <td>30000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stokes LLC</th>\n",
       "      <td>239344</td>\n",
       "      <td>7500</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Trantow-Barrows</th>\n",
       "      <td>714466</td>\n",
       "      <td>15000</td>\n",
       "      <td>1.333333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Account   Price  Quantity\n",
       "Name                                                   \n",
       "Barton LLC                     740150   35000  1.000000\n",
       "Fritsch, Russel and Anderson   737550   35000  1.000000\n",
       "Herman LLC                     141962   65000  2.000000\n",
       "Jerde-Hilpert                  412290    5000  2.000000\n",
       "Kassulke, Ondricka and Metz    307599    7000  3.000000\n",
       "Keeling LLC                    688981  100000  5.000000\n",
       "Kiehn-Spinka                   146832   65000  2.000000\n",
       "Koepp Ltd                      729833   35000  2.000000\n",
       "Kulas Inc                      218895   25000  1.500000\n",
       "Purdy-Kunde                    163416   30000  1.000000\n",
       "Stokes LLC                     239344    7500  1.000000\n",
       "Trantow-Barrows                714466   15000  1.333333"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "pd.pivot_table(df, index=['Name'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "인덱스를 여러개 지정할 수도 있습니다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>Account</th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th>Rep</th>\n",
       "      <th>Manager</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Barton LLC</th>\n",
       "      <th>John Smith</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>740150</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fritsch, Russel and Anderson</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>737550</td>\n",
       "      <td>35000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Herman LLC</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>141962</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jerde-Hilpert</th>\n",
       "      <th>John Smith</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>412290</td>\n",
       "      <td>5000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kassulke, Ondricka and Metz</th>\n",
       "      <th>Wendy Yule</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>307599</td>\n",
       "      <td>7000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Keeling LLC</th>\n",
       "      <th>Wendy Yule</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>688981</td>\n",
       "      <td>100000</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kiehn-Spinka</th>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>146832</td>\n",
       "      <td>65000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Koepp Ltd</th>\n",
       "      <th>Wendy Yule</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>729833</td>\n",
       "      <td>35000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kulas Inc</th>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>218895</td>\n",
       "      <td>25000</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Purdy-Kunde</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>163416</td>\n",
       "      <td>30000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stokes LLC</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <th>Fred Anderson</th>\n",
       "      <td>239344</td>\n",
       "      <td>7500</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Trantow-Barrows</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <th>Debra Henley</th>\n",
       "      <td>714466</td>\n",
       "      <td>15000</td>\n",
       "      <td>1.333333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                          Account   Price  \\\n",
       "Name                         Rep           Manager                          \n",
       "Barton LLC                   John Smith    Debra Henley    740150   35000   \n",
       "Fritsch, Russel and Anderson Craig Booker  Debra Henley    737550   35000   \n",
       "Herman LLC                   Cedric Moss   Fred Anderson   141962   65000   \n",
       "Jerde-Hilpert                John Smith    Debra Henley    412290    5000   \n",
       "Kassulke, Ondricka and Metz  Wendy Yule    Fred Anderson   307599    7000   \n",
       "Keeling LLC                  Wendy Yule    Fred Anderson   688981  100000   \n",
       "Kiehn-Spinka                 Daniel Hilton Debra Henley    146832   65000   \n",
       "Koepp Ltd                    Wendy Yule    Fred Anderson   729833   35000   \n",
       "Kulas Inc                    Daniel Hilton Debra Henley    218895   25000   \n",
       "Purdy-Kunde                  Cedric Moss   Fred Anderson   163416   30000   \n",
       "Stokes LLC                   Cedric Moss   Fred Anderson   239344    7500   \n",
       "Trantow-Barrows              Craig Booker  Debra Henley    714466   15000   \n",
       "\n",
       "                                                          Quantity  \n",
       "Name                         Rep           Manager                  \n",
       "Barton LLC                   John Smith    Debra Henley   1.000000  \n",
       "Fritsch, Russel and Anderson Craig Booker  Debra Henley   1.000000  \n",
       "Herman LLC                   Cedric Moss   Fred Anderson  2.000000  \n",
       "Jerde-Hilpert                John Smith    Debra Henley   2.000000  \n",
       "Kassulke, Ondricka and Metz  Wendy Yule    Fred Anderson  3.000000  \n",
       "Keeling LLC                  Wendy Yule    Fred Anderson  5.000000  \n",
       "Kiehn-Spinka                 Daniel Hilton Debra Henley   2.000000  \n",
       "Koepp Ltd                    Wendy Yule    Fred Anderson  2.000000  \n",
       "Kulas Inc                    Daniel Hilton Debra Henley   1.500000  \n",
       "Purdy-Kunde                  Cedric Moss   Fred Anderson  1.000000  \n",
       "Stokes LLC                   Cedric Moss   Fred Anderson  1.000000  \n",
       "Trantow-Barrows              Craig Booker  Debra Henley   1.333333  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "pd.pivot_table(df, index=['Name', 'Rep','Manager'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "특정 value만 지정해서 그 값만 나타나게 할 수 있습니다. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>20000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>38333.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>20000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>27500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>44250.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    Price\n",
       "Manager       Rep                        \n",
       "Debra Henley  Craig Booker   20000.000000\n",
       "              Daniel Hilton  38333.333333\n",
       "              John Smith     20000.000000\n",
       "Fred Anderson Cedric Moss    27500.000000\n",
       "              Wendy Yule     44250.000000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "pd.pivot_table(df, index=['Manager', 'Rep'], values=['Price'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "이 때 price 값은 평균으로 표시되어요. 그렇지만 우리는 평균이 되길 원하는게 아니라, 합계를 원하는 것이잖아요. 합계는 aggfunc를 사용해서 만들 수 있습니다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
       "      <th>Craig Booker</th>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daniel Hilton</th>\n",
       "      <td>115000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>40000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
       "      <th>Cedric Moss</th>\n",
       "      <td>110000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wendy Yule</th>\n",
       "      <td>177000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Price\n",
       "Manager       Rep                  \n",
       "Debra Henley  Craig Booker    80000\n",
       "              Daniel Hilton  115000\n",
       "              John Smith      40000\n",
       "Fred Anderson Cedric Moss    110000\n",
       "              Wendy Yule     177000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "pd.pivot_table(df, index=['Manager','Rep'], \n",
    "               values=['Price'], \n",
    "               aggfunc=np.sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "아래처럼 합계와 평균이 모두 표시되게 할 수도 있습니다. \n",
    "\n",
    "- fill_value : 결측치를 어떻게 채울 것이냐\n",
    "- margins=True : 아래 All(총계)를 표시해라"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">sum</th>\n",
       "      <th colspan=\"2\" halign=\"left\">mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager</th>\n",
       "      <th>Rep</th>\n",
       "      <th>Product</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"7\" valign=\"top\">Debra Henley</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Craig Booker</th>\n",
       "      <th>CPU</th>\n",
       "      <td>65000</td>\n",
       "      <td>2</td>\n",
       "      <td>32500.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>5000</td>\n",
       "      <td>2</td>\n",
       "      <td>5000.000000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Software</th>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Daniel Hilton</th>\n",
       "      <th>CPU</th>\n",
       "      <td>105000</td>\n",
       "      <td>4</td>\n",
       "      <td>52500.000000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Software</th>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">John Smith</th>\n",
       "      <th>CPU</th>\n",
       "      <td>35000</td>\n",
       "      <td>1</td>\n",
       "      <td>35000.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>5000</td>\n",
       "      <td>2</td>\n",
       "      <td>5000.000000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">Fred Anderson</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Cedric Moss</th>\n",
       "      <th>CPU</th>\n",
       "      <td>95000</td>\n",
       "      <td>3</td>\n",
       "      <td>47500.000000</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>5000</td>\n",
       "      <td>1</td>\n",
       "      <td>5000.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Software</th>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Wendy Yule</th>\n",
       "      <th>CPU</th>\n",
       "      <td>165000</td>\n",
       "      <td>7</td>\n",
       "      <td>82500.000000</td>\n",
       "      <td>3.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maintenance</th>\n",
       "      <td>7000</td>\n",
       "      <td>3</td>\n",
       "      <td>7000.000000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Monitor</th>\n",
       "      <td>5000</td>\n",
       "      <td>2</td>\n",
       "      <td>5000.000000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <td>522000</td>\n",
       "      <td>30</td>\n",
       "      <td>30705.882353</td>\n",
       "      <td>1.764706</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            sum                   mean  \\\n",
       "                                          Price Quantity         Price   \n",
       "Manager       Rep           Product                                      \n",
       "Debra Henley  Craig Booker  CPU           65000        2  32500.000000   \n",
       "                            Maintenance    5000        2   5000.000000   \n",
       "                            Software      10000        1  10000.000000   \n",
       "              Daniel Hilton CPU          105000        4  52500.000000   \n",
       "                            Software      10000        1  10000.000000   \n",
       "              John Smith    CPU           35000        1  35000.000000   \n",
       "                            Maintenance    5000        2   5000.000000   \n",
       "Fred Anderson Cedric Moss   CPU           95000        3  47500.000000   \n",
       "                            Maintenance    5000        1   5000.000000   \n",
       "                            Software      10000        1  10000.000000   \n",
       "              Wendy Yule    CPU          165000        7  82500.000000   \n",
       "                            Maintenance    7000        3   7000.000000   \n",
       "                            Monitor        5000        2   5000.000000   \n",
       "All                                      522000       30  30705.882353   \n",
       "\n",
       "                                                   \n",
       "                                         Quantity  \n",
       "Manager       Rep           Product                \n",
       "Debra Henley  Craig Booker  CPU          1.000000  \n",
       "                            Maintenance  2.000000  \n",
       "                            Software     1.000000  \n",
       "              Daniel Hilton CPU          2.000000  \n",
       "                            Software     1.000000  \n",
       "              John Smith    CPU          1.000000  \n",
       "                            Maintenance  2.000000  \n",
       "Fred Anderson Cedric Moss   CPU          1.500000  \n",
       "                            Maintenance  1.000000  \n",
       "                            Software     1.000000  \n",
       "              Wendy Yule    CPU          3.500000  \n",
       "                            Maintenance  3.000000  \n",
       "                            Monitor      2.000000  \n",
       "All                                      1.764706  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "pd.pivot_table(df, index=['Manager','Rep','Product'], \n",
    "               values=['Price','Quantity'], \n",
    "               aggfunc=[np.sum, np.mean], \n",
    "               fill_value=0, margins=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "이렇게 판다스의 pivot_table을 활용하면 간단하게 중복된 이름을 가지고 데이터를 합칠 수 있습니다. 판다스, 판다스 하는 데에는 다 이유가 있다니까요, 호호호.\n",
    "\n",
    "### 6. pivot_table을 이용해서 데이터 정리하기 \n",
    "\n",
    "앞서 저장했던 구 데이터를 한번 가져와봅시다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>Unnamed: 0</th>\n",
       "      <th>관서명</th>\n",
       "      <th>살인 발생</th>\n",
       "      <th>살인 검거</th>\n",
       "      <th>강도 발생</th>\n",
       "      <th>강도 검거</th>\n",
       "      <th>강간,추행 발생</th>\n",
       "      <th>강간,추행 검거</th>\n",
       "      <th>절도 발생</th>\n",
       "      <th>절도 검거</th>\n",
       "      <th>폭력 발생</th>\n",
       "      <th>폭력 검거</th>\n",
       "      <th>구별</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>중부</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>113.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>943</td>\n",
       "      <td>495</td>\n",
       "      <td>997</td>\n",
       "      <td>830</td>\n",
       "      <td>중구</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>종로</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>115.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>602</td>\n",
       "      <td>284</td>\n",
       "      <td>964</td>\n",
       "      <td>792</td>\n",
       "      <td>종로구</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>남대문</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>73.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>699</td>\n",
       "      <td>368</td>\n",
       "      <td>579</td>\n",
       "      <td>494</td>\n",
       "      <td>중구</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>서대문</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>159.0</td>\n",
       "      <td>149.0</td>\n",
       "      <td>1077</td>\n",
       "      <td>524</td>\n",
       "      <td>1292</td>\n",
       "      <td>1029</td>\n",
       "      <td>서대문구</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>혜화</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>77.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>581</td>\n",
       "      <td>310</td>\n",
       "      <td>747</td>\n",
       "      <td>628</td>\n",
       "      <td>종로구</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  관서명  살인 발생  살인 검거  강도 발생  강도 검거  강간,추행 발생  강간,추행 검거  절도 발생  \\\n",
       "0           0   중부      1      1      3      4     113.0      76.0    943   \n",
       "1           1   종로      9      6      4      4     115.0      93.0    602   \n",
       "2           2  남대문      1      1      2      2      73.0      52.0    699   \n",
       "3           3  서대문      3      3      2      1     159.0     149.0   1077   \n",
       "4           4   혜화      0      0      3      3      77.0      53.0    581   \n",
       "\n",
       "   절도 검거  폭력 발생  폭력 검거    구별  \n",
       "0    495    997    830    중구  \n",
       "1    284    964    792   종로구  \n",
       "2    368    579    494    중구  \n",
       "3    524   1292   1029  서대문구  \n",
       "4    310    747    628   종로구  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "crime_anal_raw = pd.read_csv('crime_in_Seoul_include_gu_name.csv', \n",
    "                             encoding='utf-8')\n",
    "crime_anal_raw.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "으잉, 근데 의도하지 않은 Unnamed:0이 나왔네요. 이를 처리하려면 아래와 같이 하면 됩니다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>살인 검거</th>\n",
       "      <th>강도 발생</th>\n",
       "      <th>강도 검거</th>\n",
       "      <th>강간,추행 발생</th>\n",
       "      <th>강간,추행 검거</th>\n",
       "      <th>절도 발생</th>\n",
       "      <th>절도 검거</th>\n",
       "      <th>폭력 발생</th>\n",
       "      <th>폭력 검거</th>\n",
       "      <th>구별</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>중부</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>113.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>943</td>\n",
       "      <td>495</td>\n",
       "      <td>997</td>\n",
       "      <td>830</td>\n",
       "      <td>중구</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>종로</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>115.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>602</td>\n",
       "      <td>284</td>\n",
       "      <td>964</td>\n",
       "      <td>792</td>\n",
       "      <td>종로구</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>남대문</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>73.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>699</td>\n",
       "      <td>368</td>\n",
       "      <td>579</td>\n",
       "      <td>494</td>\n",
       "      <td>중구</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>서대문</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>159.0</td>\n",
       "      <td>149.0</td>\n",
       "      <td>1077</td>\n",
       "      <td>524</td>\n",
       "      <td>1292</td>\n",
       "      <td>1029</td>\n",
       "      <td>서대문구</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>혜화</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>77.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>581</td>\n",
       "      <td>310</td>\n",
       "      <td>747</td>\n",
       "      <td>628</td>\n",
       "      <td>종로구</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   관서명  살인 발생  살인 검거  강도 발생  강도 검거  강간,추행 발생  강간,추행 검거  절도 발생  절도 검거  폭력 발생  \\\n",
       "0   중부      1      1      3      4     113.0      76.0    943    495    997   \n",
       "1   종로      9      6      4      4     115.0      93.0    602    284    964   \n",
       "2  남대문      1      1      2      2      73.0      52.0    699    368    579   \n",
       "3  서대문      3      3      2      1     159.0     149.0   1077    524   1292   \n",
       "4   혜화      0      0      3      3      77.0      53.0    581    310    747   \n",
       "\n",
       "   폭력 검거    구별  \n",
       "0    830    중구  \n",
       "1    792   종로구  \n",
       "2    494    중구  \n",
       "3   1029  서대문구  \n",
       "4    628   종로구  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "crime_anal_raw = pd.read_csv('crime_in_Seoul_include_gu_name.csv', \n",
    "                             index_col=0, encoding='utf-8')\n",
    "crime_anal_raw.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "그 다음에는 이제 구별을 인덱스로 하고 싶고, 구별에서 중복된 것들을 합치고 싶어요. 관서명도 이제는 필요없으니까 없애구 싶구요. 그렇다면 아래 코드를 쓰면 됩니다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>강도 검거</th>\n",
       "      <th>강도 발생</th>\n",
       "      <th>살인 검거</th>\n",
       "      <th>살인 발생</th>\n",
       "      <th>절도 검거</th>\n",
       "      <th>절도 발생</th>\n",
       "      <th>폭력 검거</th>\n",
       "      <th>폭력 발생</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>구별</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>강남구</th>\n",
       "      <td>627.0</td>\n",
       "      <td>708.0</td>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>1491</td>\n",
       "      <td>2951</td>\n",
       "      <td>3111</td>\n",
       "      <td>3677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>강동구</th>\n",
       "      <td>167.0</td>\n",
       "      <td>188.0</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>781</td>\n",
       "      <td>1640</td>\n",
       "      <td>1697</td>\n",
       "      <td>1942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>강북구</th>\n",
       "      <td>132.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>490</td>\n",
       "      <td>780</td>\n",
       "      <td>1625</td>\n",
       "      <td>1817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>강서구</th>\n",
       "      <td>222.0</td>\n",
       "      <td>265.0</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>1070</td>\n",
       "      <td>1689</td>\n",
       "      <td>2087</td>\n",
       "      <td>2445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>관악구</th>\n",
       "      <td>351.0</td>\n",
       "      <td>403.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>1137</td>\n",
       "      <td>2229</td>\n",
       "      <td>2355</td>\n",
       "      <td>2614</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     강간,추행 검거  강간,추행 발생  강도 검거  강도 발생  살인 검거  살인 발생  절도 검거  절도 발생  폭력 검거  \\\n",
       "구별                                                                         \n",
       "강남구     627.0     708.0     11     14      5      6   1491   2951   3111   \n",
       "강동구     167.0     188.0     14     15      3      3    781   1640   1697   \n",
       "강북구     132.0     159.0      4      5      8      9    490    780   1625   \n",
       "강서구     222.0     265.0      5      6      9     10   1070   1689   2087   \n",
       "관악구     351.0     403.0      3      3     12     12   1137   2229   2355   \n",
       "\n",
       "     폭력 발생  \n",
       "구별          \n",
       "강남구   3677  \n",
       "강동구   1942  \n",
       "강북구   1817  \n",
       "강서구   2445  \n",
       "관악구   2614  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "crime_anal = pd.pivot_table(crime_anal_raw, \n",
    "                            index='구별', \n",
    "                            aggfunc=np.sum)\n",
    "crime_anal.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "우왕, 정말 간단하죠? \n",
    "\n",
    "그럼 이제는 검거율을 살펴보려고 해요. 논리적으로는 발생 > 검거겠지요. \n",
    "\n",
    "- 검거율 = 사건 검거 / 사건 발생 * 100 \n",
    "\n",
    "그럼 이렇게 연산을 해보기로 합시다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>강도 검거</th>\n",
       "      <th>강도 발생</th>\n",
       "      <th>살인 검거</th>\n",
       "      <th>살인 발생</th>\n",
       "      <th>절도 검거</th>\n",
       "      <th>절도 발생</th>\n",
       "      <th>폭력 검거</th>\n",
       "      <th>폭력 발생</th>\n",
       "      <th>강간,추행 검거율</th>\n",
       "      <th>강도검거율</th>\n",
       "      <th>살인검거율</th>\n",
       "      <th>절도검거율</th>\n",
       "      <th>폭력검거율</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>구별</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>강남구</th>\n",
       "      <td>627.0</td>\n",
       "      <td>708.0</td>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>1491</td>\n",
       "      <td>2951</td>\n",
       "      <td>3111</td>\n",
       "      <td>3677</td>\n",
       "      <td>88.559322</td>\n",
       "      <td>78.571429</td>\n",
       "      <td>83.333333</td>\n",
       "      <td>50.525246</td>\n",
       "      <td>84.607017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>강동구</th>\n",
       "      <td>167.0</td>\n",
       "      <td>188.0</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>781</td>\n",
       "      <td>1640</td>\n",
       "      <td>1697</td>\n",
       "      <td>1942</td>\n",
       "      <td>88.829787</td>\n",
       "      <td>93.333333</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>47.621951</td>\n",
       "      <td>87.384140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>강북구</th>\n",
       "      <td>132.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>490</td>\n",
       "      <td>780</td>\n",
       "      <td>1625</td>\n",
       "      <td>1817</td>\n",
       "      <td>83.018868</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>88.888889</td>\n",
       "      <td>62.820513</td>\n",
       "      <td>89.433132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>강서구</th>\n",
       "      <td>222.0</td>\n",
       "      <td>265.0</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>1070</td>\n",
       "      <td>1689</td>\n",
       "      <td>2087</td>\n",
       "      <td>2445</td>\n",
       "      <td>83.773585</td>\n",
       "      <td>83.333333</td>\n",
       "      <td>90.000000</td>\n",
       "      <td>63.351095</td>\n",
       "      <td>85.357873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>관악구</th>\n",
       "      <td>351.0</td>\n",
       "      <td>403.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>1137</td>\n",
       "      <td>2229</td>\n",
       "      <td>2355</td>\n",
       "      <td>2614</td>\n",
       "      <td>87.096774</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>51.009421</td>\n",
       "      <td>90.091813</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     강간,추행 검거  강간,추행 발생  강도 검거  강도 발생  살인 검거  살인 발생  절도 검거  절도 발생  폭력 검거  \\\n",
       "구별                                                                         \n",
       "강남구     627.0     708.0     11     14      5      6   1491   2951   3111   \n",
       "강동구     167.0     188.0     14     15      3      3    781   1640   1697   \n",
       "강북구     132.0     159.0      4      5      8      9    490    780   1625   \n",
       "강서구     222.0     265.0      5      6      9     10   1070   1689   2087   \n",
       "관악구     351.0     403.0      3      3     12     12   1137   2229   2355   \n",
       "\n",
       "     폭력 발생  강간,추행 검거율       강도검거율       살인검거율      절도검거율      폭력검거율  \n",
       "구별                                                                   \n",
       "강남구   3677  88.559322   78.571429   83.333333  50.525246  84.607017  \n",
       "강동구   1942  88.829787   93.333333  100.000000  47.621951  87.384140  \n",
       "강북구   1817  83.018868   80.000000   88.888889  62.820513  89.433132  \n",
       "강서구   2445  83.773585   83.333333   90.000000  63.351095  85.357873  \n",
       "관악구   2614  87.096774  100.000000  100.000000  51.009421  90.091813  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "crime_anal['강간,추행 검거율'] = crime_anal['강간,추행 검거']/crime_anal['강간,추행 발생']*100\n",
    "crime_anal['강도검거율'] = crime_anal['강도 검거']/crime_anal['강도 발생']*100\n",
    "crime_anal['살인검거율'] = crime_anal['살인 검거']/crime_anal['살인 발생']*100\n",
    "crime_anal['절도검거율'] = crime_anal['절도 검거']/crime_anal['절도 발생']*100\n",
    "crime_anal['폭력검거율'] = crime_anal['폭력 검거']/crime_anal['폭력 발생']*100\n",
    "\n",
    "crime_anal.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "검거율이 생겼으니까 검거는 지우도록 합시다. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>살인 발생</th>\n",
       "      <th>절도 발생</th>\n",
       "      <th>폭력 발생</th>\n",
       "      <th>강간,추행 검거율</th>\n",
       "      <th>강도검거율</th>\n",
       "      <th>살인검거율</th>\n",
       "      <th>절도검거율</th>\n",
       "      <th>폭력검거율</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>구별</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>강남구</th>\n",
       "      <td>708.0</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>2951</td>\n",
       "      <td>3677</td>\n",
       "      <td>88.559322</td>\n",
       "      <td>78.571429</td>\n",
       "      <td>83.333333</td>\n",
       "      <td>50.525246</td>\n",
       "      <td>84.607017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>강동구</th>\n",
       "      <td>188.0</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>1640</td>\n",
       "      <td>1942</td>\n",
       "      <td>88.829787</td>\n",
       "      <td>93.333333</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>47.621951</td>\n",
       "      <td>87.384140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>강북구</th>\n",
       "      <td>159.0</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>780</td>\n",
       "      <td>1817</td>\n",
       "      <td>83.018868</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>88.888889</td>\n",
       "      <td>62.820513</td>\n",
       "      <td>89.433132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>강서구</th>\n",
       "      <td>265.0</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>1689</td>\n",
       "      <td>2445</td>\n",
       "      <td>83.773585</td>\n",
       "      <td>83.333333</td>\n",
       "      <td>90.000000</td>\n",
       "      <td>63.351095</td>\n",
       "      <td>85.357873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>관악구</th>\n",
       "      <td>403.0</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>2229</td>\n",
       "      <td>2614</td>\n",
       "      <td>87.096774</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>51.009421</td>\n",
       "      <td>90.091813</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     강간,추행 발생  강도 발생  살인 발생  절도 발생  폭력 발생  강간,추행 검거율       강도검거율       살인검거율  \\\n",
       "구별                                                                             \n",
       "강남구     708.0     14      6   2951   3677  88.559322   78.571429   83.333333   \n",
       "강동구     188.0     15      3   1640   1942  88.829787   93.333333  100.000000   \n",
       "강북구     159.0      5      9    780   1817  83.018868   80.000000   88.888889   \n",
       "강서구     265.0      6     10   1689   2445  83.773585   83.333333   90.000000   \n",
       "관악구     403.0      3     12   2229   2614  87.096774  100.000000  100.000000   \n",
       "\n",
       "         절도검거율      폭력검거율  \n",
       "구별                         \n",
       "강남구  50.525246  84.607017  \n",
       "강동구  47.621951  87.384140  \n",
       "강북구  62.820513  89.433132  \n",
       "강서구  63.351095  85.357873  \n",
       "관악구  51.009421  90.091813  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "crime_anal = crime_anal.drop(['강간,추행 검거','강도 검거','살인 검거',\n",
    "                              '절도 검거','폭력 검거'],1)\n",
    "crime_anal.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "column 이름의 발생이란 것도 지워서 더 간단하게 표현해볼게요."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>살인</th>\n",
       "      <th>절도</th>\n",
       "      <th>폭력</th>\n",
       "      <th>강간,추행 검거율</th>\n",
       "      <th>강도검거율</th>\n",
       "      <th>살인검거율</th>\n",
       "      <th>절도검거율</th>\n",
       "      <th>폭력검거율</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>구별</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>강남구</th>\n",
       "      <td>708.0</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>2951</td>\n",
       "      <td>3677</td>\n",
       "      <td>88.559322</td>\n",
       "      <td>78.571429</td>\n",
       "      <td>83.333333</td>\n",
       "      <td>50.525246</td>\n",
       "      <td>84.607017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>강동구</th>\n",
       "      <td>188.0</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>1640</td>\n",
       "      <td>1942</td>\n",
       "      <td>88.829787</td>\n",
       "      <td>93.333333</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>47.621951</td>\n",
       "      <td>87.384140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>강북구</th>\n",
       "      <td>159.0</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>780</td>\n",
       "      <td>1817</td>\n",
       "      <td>83.018868</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>88.888889</td>\n",
       "      <td>62.820513</td>\n",
       "      <td>89.433132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>강서구</th>\n",
       "      <td>265.0</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>1689</td>\n",
       "      <td>2445</td>\n",
       "      <td>83.773585</td>\n",
       "      <td>83.333333</td>\n",
       "      <td>90.000000</td>\n",
       "      <td>63.351095</td>\n",
       "      <td>85.357873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>관악구</th>\n",
       "      <td>403.0</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>2229</td>\n",
       "      <td>2614</td>\n",
       "      <td>87.096774</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>51.009421</td>\n",
       "      <td>90.091813</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     강간,추행  강도  살인    절도    폭력  강간,추행 검거율       강도검거율       살인검거율      절도검거율  \\\n",
       "구별                                                                             \n",
       "강남구  708.0  14   6  2951  3677  88.559322   78.571429   83.333333  50.525246   \n",
       "강동구  188.0  15   3  1640  1942  88.829787   93.333333  100.000000  47.621951   \n",
       "강북구  159.0   5   9   780  1817  83.018868   80.000000   88.888889  62.820513   \n",
       "강서구  265.0   6  10  1689  2445  83.773585   83.333333   90.000000  63.351095   \n",
       "관악구  403.0   3  12  2229  2614  87.096774  100.000000  100.000000  51.009421   \n",
       "\n",
       "         폭력검거율  \n",
       "구별              \n",
       "강남구  84.607017  \n",
       "강동구  87.384140  \n",
       "강북구  89.433132  \n",
       "강서구  85.357873  \n",
       "관악구  90.091813  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "crime_anal.rename(columns = {'강간,추행 발생': '강간,추행',\n",
    "                            '강도 발생': '강도',\n",
    "                            '살인 발생': '살인',\n",
    "                            '절도 발생': '절도',\n",
    "                            '폭력 발생': '폭력'}, inplace=True)\n",
    "crime_anal.head()"
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}