ch08 pd.crosstab, sort_values, melt, pivot.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "63285961",
   "metadata": {},
   "source": [
    "# 8차시 데이터 전처리: 정렬 및 변환\n",
    "## 01 정렬 개요\n",
    "### 정렬 방법\n",
    "- 오름차순: 값이 작은 것부터 큰 순으로 정렬\n",
    "- 내림차순: 값이 큰 것부터 작은 순으로 정렬\n",
    "\n",
    "### 정렬의 활용\n",
    "- 특정 변수의 최대값 또는 최소값을 확인할 때 사용\n",
    "- 고객의 일별 결제 금액 데이터에서 가장 최근의 결제 금액 확인 가능\n",
    "- 시계열 데이터 분석에서 시간 순서대로 데이터를 정렬할 때 활용\n",
    "\n",
    "## 02 데이터 소개\n",
    "### 다이아몬드 데이터 - diamonds.csv\n",
    "- 가격 및 각종 제원이 기록된 다이아몬드 데이터\n",
    "- R 프로그램의 ggplot2 패키지에 수록된 데이터\n",
    "- price: price in US dollars\n",
    "- carat: weight of the diamond\n",
    "- cut: quality of the cut (Fair, Good, Very Good, Premium, Ideal)\n",
    "- color: diamond colour from D (best) to J (worst)\n",
    "- clarity: a measurement of how clear the diamond is (I1 (worst), SI2, SI1, VS2, VS1, VVS2, VVS1, IF (best))\n",
    "- x: length in mm\n",
    "- y: width in mm\n",
    "- z: depth in mm\n",
    "- depth: total depth percentage\n",
    "- table: width of top of diamond relative to widest point\n",
    "\n",
    "### 전력 부하(사용) 데이터 - elec_load_2017_7d.csv\n",
    "- 미공개 지역의 2017년 1월 1일부터 1월 7일까지의 전력 부하량\n",
    "- 시간 단위로 기록되어 있으며 대표적인 wide form 데이터\n",
    "- YEAR: 연\n",
    "- MONTH: 월\n",
    "- DAY: 일\n",
    "- X1TH ~ X24HR: 시"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f0db12c",
   "metadata": {},
   "source": [
    "## 03 주요 함수 및 메서드 소개\n",
    "### pandas - crosstab()\n",
    "- 기본적으로 데이터프레임의 두 변수의 연소 조합 빈도를 확인하는 함수\n",
    "- normalize 인자 설정으로 각 조합의 비율을 손쉽게 계산\n",
    "- value와 aggfunc 인자에 변수와 요약 함수를 지정하면 빈도가 아닌 별도 산술연산 가능"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "fd887d4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4cc431bb",
   "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>carat</th>\n",
       "      <th>cut</th>\n",
       "      <th>color</th>\n",
       "      <th>clarity</th>\n",
       "      <th>depth</th>\n",
       "      <th>table</th>\n",
       "      <th>price</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>z</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.23</td>\n",
       "      <td>Ideal</td>\n",
       "      <td>E</td>\n",
       "      <td>SI2</td>\n",
       "      <td>61.5</td>\n",
       "      <td>55.0</td>\n",
       "      <td>326</td>\n",
       "      <td>3.95</td>\n",
       "      <td>3.98</td>\n",
       "      <td>2.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.21</td>\n",
       "      <td>Premium</td>\n",
       "      <td>E</td>\n",
       "      <td>SI1</td>\n",
       "      <td>59.8</td>\n",
       "      <td>61.0</td>\n",
       "      <td>326</td>\n",
       "      <td>3.89</td>\n",
       "      <td>3.84</td>\n",
       "      <td>2.31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   carat      cut color clarity  depth  table  price     x     y     z\n",
       "0   0.23    Ideal     E     SI2   61.5   55.0    326  3.95  3.98  2.43\n",
       "1   0.21  Premium     E     SI1   59.8   61.0    326  3.89  3.84  2.31"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dia = pd.read_csv(\"강의자료/실습파일/diamonds.csv\")\n",
    "dia.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "55e8f0f7",
   "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>clarity</th>\n",
       "      <th>I1</th>\n",
       "      <th>IF</th>\n",
       "      <th>SI1</th>\n",
       "      <th>SI2</th>\n",
       "      <th>VS1</th>\n",
       "      <th>VS2</th>\n",
       "      <th>VVS1</th>\n",
       "      <th>VVS2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cut</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>Fair</th>\n",
       "      <td>210</td>\n",
       "      <td>9</td>\n",
       "      <td>408</td>\n",
       "      <td>466</td>\n",
       "      <td>170</td>\n",
       "      <td>261</td>\n",
       "      <td>17</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Good</th>\n",
       "      <td>96</td>\n",
       "      <td>71</td>\n",
       "      <td>1560</td>\n",
       "      <td>1081</td>\n",
       "      <td>648</td>\n",
       "      <td>978</td>\n",
       "      <td>186</td>\n",
       "      <td>286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ideal</th>\n",
       "      <td>146</td>\n",
       "      <td>1212</td>\n",
       "      <td>4282</td>\n",
       "      <td>2598</td>\n",
       "      <td>3589</td>\n",
       "      <td>5071</td>\n",
       "      <td>2047</td>\n",
       "      <td>2606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Premium</th>\n",
       "      <td>205</td>\n",
       "      <td>230</td>\n",
       "      <td>3575</td>\n",
       "      <td>2949</td>\n",
       "      <td>1989</td>\n",
       "      <td>3357</td>\n",
       "      <td>616</td>\n",
       "      <td>870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Very Good</th>\n",
       "      <td>84</td>\n",
       "      <td>268</td>\n",
       "      <td>3240</td>\n",
       "      <td>2100</td>\n",
       "      <td>1775</td>\n",
       "      <td>2591</td>\n",
       "      <td>789</td>\n",
       "      <td>1235</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "clarity     I1    IF   SI1   SI2   VS1   VS2  VVS1  VVS2\n",
       "cut                                                     \n",
       "Fair       210     9   408   466   170   261    17    69\n",
       "Good        96    71  1560  1081   648   978   186   286\n",
       "Ideal      146  1212  4282  2598  3589  5071  2047  2606\n",
       "Premium    205   230  3575  2949  1989  3357   616   870\n",
       "Very Good   84   268  3240  2100  1775  2591   789  1235"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(dia[\"cut\"], dia[\"clarity\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "388863e5",
   "metadata": {
    "scrolled": true
   },
   "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>clarity</th>\n",
       "      <th>I1</th>\n",
       "      <th>IF</th>\n",
       "      <th>SI1</th>\n",
       "      <th>SI2</th>\n",
       "      <th>VS1</th>\n",
       "      <th>VS2</th>\n",
       "      <th>VVS1</th>\n",
       "      <th>VVS2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cut</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>Fair</th>\n",
       "      <td>0.004</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.008</td>\n",
       "      <td>0.009</td>\n",
       "      <td>0.003</td>\n",
       "      <td>0.005</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Good</th>\n",
       "      <td>0.002</td>\n",
       "      <td>0.001</td>\n",
       "      <td>0.029</td>\n",
       "      <td>0.020</td>\n",
       "      <td>0.012</td>\n",
       "      <td>0.018</td>\n",
       "      <td>0.003</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ideal</th>\n",
       "      <td>0.003</td>\n",
       "      <td>0.022</td>\n",
       "      <td>0.079</td>\n",
       "      <td>0.048</td>\n",
       "      <td>0.067</td>\n",
       "      <td>0.094</td>\n",
       "      <td>0.038</td>\n",
       "      <td>0.048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Premium</th>\n",
       "      <td>0.004</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.066</td>\n",
       "      <td>0.055</td>\n",
       "      <td>0.037</td>\n",
       "      <td>0.062</td>\n",
       "      <td>0.011</td>\n",
       "      <td>0.016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Very Good</th>\n",
       "      <td>0.002</td>\n",
       "      <td>0.005</td>\n",
       "      <td>0.060</td>\n",
       "      <td>0.039</td>\n",
       "      <td>0.033</td>\n",
       "      <td>0.048</td>\n",
       "      <td>0.015</td>\n",
       "      <td>0.023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "clarity       I1     IF    SI1    SI2    VS1    VS2   VVS1   VVS2\n",
       "cut                                                              \n",
       "Fair       0.004  0.000  0.008  0.009  0.003  0.005  0.000  0.001\n",
       "Good       0.002  0.001  0.029  0.020  0.012  0.018  0.003  0.005\n",
       "Ideal      0.003  0.022  0.079  0.048  0.067  0.094  0.038  0.048\n",
       "Premium    0.004  0.004  0.066  0.055  0.037  0.062  0.011  0.016\n",
       "Very Good  0.002  0.005  0.060  0.039  0.033  0.048  0.015  0.023"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(dia[\"cut\"], dia[\"clarity\"], normalize = True).round(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6f285ee8",
   "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>clarity</th>\n",
       "      <th>I1</th>\n",
       "      <th>IF</th>\n",
       "      <th>SI1</th>\n",
       "      <th>SI2</th>\n",
       "      <th>VS1</th>\n",
       "      <th>VS2</th>\n",
       "      <th>VVS1</th>\n",
       "      <th>VVS2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cut</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>Fair</th>\n",
       "      <td>0.130</td>\n",
       "      <td>0.006</td>\n",
       "      <td>0.253</td>\n",
       "      <td>0.289</td>\n",
       "      <td>0.106</td>\n",
       "      <td>0.162</td>\n",
       "      <td>0.011</td>\n",
       "      <td>0.043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Good</th>\n",
       "      <td>0.020</td>\n",
       "      <td>0.014</td>\n",
       "      <td>0.318</td>\n",
       "      <td>0.220</td>\n",
       "      <td>0.132</td>\n",
       "      <td>0.199</td>\n",
       "      <td>0.038</td>\n",
       "      <td>0.058</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ideal</th>\n",
       "      <td>0.007</td>\n",
       "      <td>0.056</td>\n",
       "      <td>0.199</td>\n",
       "      <td>0.121</td>\n",
       "      <td>0.167</td>\n",
       "      <td>0.235</td>\n",
       "      <td>0.095</td>\n",
       "      <td>0.121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Premium</th>\n",
       "      <td>0.015</td>\n",
       "      <td>0.017</td>\n",
       "      <td>0.259</td>\n",
       "      <td>0.214</td>\n",
       "      <td>0.144</td>\n",
       "      <td>0.243</td>\n",
       "      <td>0.045</td>\n",
       "      <td>0.063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Very Good</th>\n",
       "      <td>0.007</td>\n",
       "      <td>0.022</td>\n",
       "      <td>0.268</td>\n",
       "      <td>0.174</td>\n",
       "      <td>0.147</td>\n",
       "      <td>0.214</td>\n",
       "      <td>0.065</td>\n",
       "      <td>0.102</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "clarity       I1     IF    SI1    SI2    VS1    VS2   VVS1   VVS2\n",
       "cut                                                              \n",
       "Fair       0.130  0.006  0.253  0.289  0.106  0.162  0.011  0.043\n",
       "Good       0.020  0.014  0.318  0.220  0.132  0.199  0.038  0.058\n",
       "Ideal      0.007  0.056  0.199  0.121  0.167  0.235  0.095  0.121\n",
       "Premium    0.015  0.017  0.259  0.214  0.144  0.243  0.045  0.063\n",
       "Very Good  0.007  0.022  0.268  0.174  0.147  0.214  0.065  0.102"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(dia[\"cut\"], dia[\"clarity\"], normalize = 0).round(3) # 0: row 기준으로 표준화"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "83b41de2",
   "metadata": {
    "scrolled": true
   },
   "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>clarity</th>\n",
       "      <th>I1</th>\n",
       "      <th>IF</th>\n",
       "      <th>SI1</th>\n",
       "      <th>SI2</th>\n",
       "      <th>VS1</th>\n",
       "      <th>VS2</th>\n",
       "      <th>VVS1</th>\n",
       "      <th>VVS2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cut</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>Fair</th>\n",
       "      <td>0.283</td>\n",
       "      <td>0.005</td>\n",
       "      <td>0.031</td>\n",
       "      <td>0.051</td>\n",
       "      <td>0.021</td>\n",
       "      <td>0.021</td>\n",
       "      <td>0.005</td>\n",
       "      <td>0.014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Good</th>\n",
       "      <td>0.130</td>\n",
       "      <td>0.040</td>\n",
       "      <td>0.119</td>\n",
       "      <td>0.118</td>\n",
       "      <td>0.079</td>\n",
       "      <td>0.080</td>\n",
       "      <td>0.051</td>\n",
       "      <td>0.056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ideal</th>\n",
       "      <td>0.197</td>\n",
       "      <td>0.677</td>\n",
       "      <td>0.328</td>\n",
       "      <td>0.283</td>\n",
       "      <td>0.439</td>\n",
       "      <td>0.414</td>\n",
       "      <td>0.560</td>\n",
       "      <td>0.514</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Premium</th>\n",
       "      <td>0.277</td>\n",
       "      <td>0.128</td>\n",
       "      <td>0.274</td>\n",
       "      <td>0.321</td>\n",
       "      <td>0.243</td>\n",
       "      <td>0.274</td>\n",
       "      <td>0.169</td>\n",
       "      <td>0.172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Very Good</th>\n",
       "      <td>0.113</td>\n",
       "      <td>0.150</td>\n",
       "      <td>0.248</td>\n",
       "      <td>0.228</td>\n",
       "      <td>0.217</td>\n",
       "      <td>0.211</td>\n",
       "      <td>0.216</td>\n",
       "      <td>0.244</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "clarity       I1     IF    SI1    SI2    VS1    VS2   VVS1   VVS2\n",
       "cut                                                              \n",
       "Fair       0.283  0.005  0.031  0.051  0.021  0.021  0.005  0.014\n",
       "Good       0.130  0.040  0.119  0.118  0.079  0.080  0.051  0.056\n",
       "Ideal      0.197  0.677  0.328  0.283  0.439  0.414  0.560  0.514\n",
       "Premium    0.277  0.128  0.274  0.321  0.243  0.274  0.169  0.172\n",
       "Very Good  0.113  0.150  0.248  0.228  0.217  0.211  0.216  0.244"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(dia[\"cut\"], dia[\"clarity\"], normalize = 1).round(3) # 1: Column 기준으로 표준화"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "86bde0c7",
   "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>clarity</th>\n",
       "      <th>I1</th>\n",
       "      <th>IF</th>\n",
       "      <th>SI1</th>\n",
       "      <th>SI2</th>\n",
       "      <th>VS1</th>\n",
       "      <th>VS2</th>\n",
       "      <th>VVS1</th>\n",
       "      <th>VVS2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cut</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>Fair</th>\n",
       "      <td>3703.533333</td>\n",
       "      <td>1912.333333</td>\n",
       "      <td>4208.279412</td>\n",
       "      <td>5173.916309</td>\n",
       "      <td>4165.141176</td>\n",
       "      <td>4174.724138</td>\n",
       "      <td>3871.352941</td>\n",
       "      <td>3349.768116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Good</th>\n",
       "      <td>3596.635417</td>\n",
       "      <td>4098.323944</td>\n",
       "      <td>3689.533333</td>\n",
       "      <td>4580.260870</td>\n",
       "      <td>3801.445988</td>\n",
       "      <td>4262.236196</td>\n",
       "      <td>2254.774194</td>\n",
       "      <td>3079.108392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ideal</th>\n",
       "      <td>4335.726027</td>\n",
       "      <td>2272.913366</td>\n",
       "      <td>3752.118169</td>\n",
       "      <td>4755.952656</td>\n",
       "      <td>3489.744497</td>\n",
       "      <td>3284.550385</td>\n",
       "      <td>2468.129458</td>\n",
       "      <td>3250.290100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Premium</th>\n",
       "      <td>3947.331707</td>\n",
       "      <td>3856.143478</td>\n",
       "      <td>4455.269371</td>\n",
       "      <td>5545.936928</td>\n",
       "      <td>4485.462041</td>\n",
       "      <td>4550.331248</td>\n",
       "      <td>2831.206169</td>\n",
       "      <td>3795.122989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Very Good</th>\n",
       "      <td>4078.226190</td>\n",
       "      <td>4396.216418</td>\n",
       "      <td>3932.391049</td>\n",
       "      <td>4988.688095</td>\n",
       "      <td>3805.353239</td>\n",
       "      <td>4215.759552</td>\n",
       "      <td>2459.441065</td>\n",
       "      <td>3037.765182</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "clarity             I1           IF          SI1          SI2          VS1  \\\n",
       "cut                                                                          \n",
       "Fair       3703.533333  1912.333333  4208.279412  5173.916309  4165.141176   \n",
       "Good       3596.635417  4098.323944  3689.533333  4580.260870  3801.445988   \n",
       "Ideal      4335.726027  2272.913366  3752.118169  4755.952656  3489.744497   \n",
       "Premium    3947.331707  3856.143478  4455.269371  5545.936928  4485.462041   \n",
       "Very Good  4078.226190  4396.216418  3932.391049  4988.688095  3805.353239   \n",
       "\n",
       "clarity            VS2         VVS1         VVS2  \n",
       "cut                                               \n",
       "Fair       4174.724138  3871.352941  3349.768116  \n",
       "Good       4262.236196  2254.774194  3079.108392  \n",
       "Ideal      3284.550385  2468.129458  3250.290100  \n",
       "Premium    4550.331248  2831.206169  3795.122989  \n",
       "Very Good  4215.759552  2459.441065  3037.765182  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(dia[\"cut\"], dia[\"clarity\"], values = dia[\"price\"],\n",
    "           aggfunc = pd.Series.mean) # 가격평균"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "11301244",
   "metadata": {
    "scrolled": true
   },
   "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>cut</th>\n",
       "      <th>clarity</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fair</td>\n",
       "      <td>I1</td>\n",
       "      <td>3703.533333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Fair</td>\n",
       "      <td>IF</td>\n",
       "      <td>1912.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Fair</td>\n",
       "      <td>SI1</td>\n",
       "      <td>4208.279412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fair</td>\n",
       "      <td>SI2</td>\n",
       "      <td>5173.916309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Fair</td>\n",
       "      <td>VS1</td>\n",
       "      <td>4165.141176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Fair</td>\n",
       "      <td>VS2</td>\n",
       "      <td>4174.724138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Fair</td>\n",
       "      <td>VVS1</td>\n",
       "      <td>3871.352941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Fair</td>\n",
       "      <td>VVS2</td>\n",
       "      <td>3349.768116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Good</td>\n",
       "      <td>I1</td>\n",
       "      <td>3596.635417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Good</td>\n",
       "      <td>IF</td>\n",
       "      <td>4098.323944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Good</td>\n",
       "      <td>SI1</td>\n",
       "      <td>3689.533333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Good</td>\n",
       "      <td>SI2</td>\n",
       "      <td>4580.260870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Good</td>\n",
       "      <td>VS1</td>\n",
       "      <td>3801.445988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Good</td>\n",
       "      <td>VS2</td>\n",
       "      <td>4262.236196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Good</td>\n",
       "      <td>VVS1</td>\n",
       "      <td>2254.774194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Good</td>\n",
       "      <td>VVS2</td>\n",
       "      <td>3079.108392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Ideal</td>\n",
       "      <td>I1</td>\n",
       "      <td>4335.726027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Ideal</td>\n",
       "      <td>IF</td>\n",
       "      <td>2272.913366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Ideal</td>\n",
       "      <td>SI1</td>\n",
       "      <td>3752.118169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Ideal</td>\n",
       "      <td>SI2</td>\n",
       "      <td>4755.952656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Ideal</td>\n",
       "      <td>VS1</td>\n",
       "      <td>3489.744497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Ideal</td>\n",
       "      <td>VS2</td>\n",
       "      <td>3284.550385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Ideal</td>\n",
       "      <td>VVS1</td>\n",
       "      <td>2468.129458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Ideal</td>\n",
       "      <td>VVS2</td>\n",
       "      <td>3250.290100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Premium</td>\n",
       "      <td>I1</td>\n",
       "      <td>3947.331707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Premium</td>\n",
       "      <td>IF</td>\n",
       "      <td>3856.143478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Premium</td>\n",
       "      <td>SI1</td>\n",
       "      <td>4455.269371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Premium</td>\n",
       "      <td>SI2</td>\n",
       "      <td>5545.936928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Premium</td>\n",
       "      <td>VS1</td>\n",
       "      <td>4485.462041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Premium</td>\n",
       "      <td>VS2</td>\n",
       "      <td>4550.331248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Premium</td>\n",
       "      <td>VVS1</td>\n",
       "      <td>2831.206169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Premium</td>\n",
       "      <td>VVS2</td>\n",
       "      <td>3795.122989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Very Good</td>\n",
       "      <td>I1</td>\n",
       "      <td>4078.226190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Very Good</td>\n",
       "      <td>IF</td>\n",
       "      <td>4396.216418</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Very Good</td>\n",
       "      <td>SI1</td>\n",
       "      <td>3932.391049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Very Good</td>\n",
       "      <td>SI2</td>\n",
       "      <td>4988.688095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Very Good</td>\n",
       "      <td>VS1</td>\n",
       "      <td>3805.353239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Very Good</td>\n",
       "      <td>VS2</td>\n",
       "      <td>4215.759552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Very Good</td>\n",
       "      <td>VVS1</td>\n",
       "      <td>2459.441065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Very Good</td>\n",
       "      <td>VVS2</td>\n",
       "      <td>3037.765182</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          cut clarity        price\n",
       "0        Fair      I1  3703.533333\n",
       "1        Fair      IF  1912.333333\n",
       "2        Fair     SI1  4208.279412\n",
       "3        Fair     SI2  5173.916309\n",
       "4        Fair     VS1  4165.141176\n",
       "5        Fair     VS2  4174.724138\n",
       "6        Fair    VVS1  3871.352941\n",
       "7        Fair    VVS2  3349.768116\n",
       "8        Good      I1  3596.635417\n",
       "9        Good      IF  4098.323944\n",
       "10       Good     SI1  3689.533333\n",
       "11       Good     SI2  4580.260870\n",
       "12       Good     VS1  3801.445988\n",
       "13       Good     VS2  4262.236196\n",
       "14       Good    VVS1  2254.774194\n",
       "15       Good    VVS2  3079.108392\n",
       "16      Ideal      I1  4335.726027\n",
       "17      Ideal      IF  2272.913366\n",
       "18      Ideal     SI1  3752.118169\n",
       "19      Ideal     SI2  4755.952656\n",
       "20      Ideal     VS1  3489.744497\n",
       "21      Ideal     VS2  3284.550385\n",
       "22      Ideal    VVS1  2468.129458\n",
       "23      Ideal    VVS2  3250.290100\n",
       "24    Premium      I1  3947.331707\n",
       "25    Premium      IF  3856.143478\n",
       "26    Premium     SI1  4455.269371\n",
       "27    Premium     SI2  5545.936928\n",
       "28    Premium     VS1  4485.462041\n",
       "29    Premium     VS2  4550.331248\n",
       "30    Premium    VVS1  2831.206169\n",
       "31    Premium    VVS2  3795.122989\n",
       "32  Very Good      I1  4078.226190\n",
       "33  Very Good      IF  4396.216418\n",
       "34  Very Good     SI1  3932.391049\n",
       "35  Very Good     SI2  4988.688095\n",
       "36  Very Good     VS1  3805.353239\n",
       "37  Very Good     VS2  4215.759552\n",
       "38  Very Good    VVS1  2459.441065\n",
       "39  Very Good    VVS2  3037.765182"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dia.groupby([\"cut\", \"clarity\"])[\"price\"].mean().reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a0e8949",
   "metadata": {},
   "source": [
    "### pandas - sort_values()\n",
    "- 데이터프레임의 특정 변수를 기준으로 정렬하는 메서드\n",
    "- ascending인자 설정으로 오름차순/내림차순 설정 가능\n",
    "- 두 변수 이상의 기준으로 정렬 시 리스트 형식으로 각 인자에 값 할당"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "5a71cafb",
   "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>cut</th>\n",
       "      <th>clarity</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fair</td>\n",
       "      <td>I1</td>\n",
       "      <td>3703.533333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Fair</td>\n",
       "      <td>IF</td>\n",
       "      <td>1912.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Fair</td>\n",
       "      <td>SI1</td>\n",
       "      <td>4208.279412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fair</td>\n",
       "      <td>SI2</td>\n",
       "      <td>5173.916309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Fair</td>\n",
       "      <td>VS1</td>\n",
       "      <td>4165.141176</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cut clarity        price\n",
       "0  Fair      I1  3703.533333\n",
       "1  Fair      IF  1912.333333\n",
       "2  Fair     SI1  4208.279412\n",
       "3  Fair     SI2  5173.916309\n",
       "4  Fair     VS1  4165.141176"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dia_agg = dia.groupby([\"cut\", \"clarity\"])[\"price\"].mean().reset_index()\n",
    "dia_agg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "eac9b144",
   "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>cut</th>\n",
       "      <th>clarity</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Fair</td>\n",
       "      <td>IF</td>\n",
       "      <td>1912.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Good</td>\n",
       "      <td>VVS1</td>\n",
       "      <td>2254.774194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Ideal</td>\n",
       "      <td>IF</td>\n",
       "      <td>2272.913366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Very Good</td>\n",
       "      <td>VVS1</td>\n",
       "      <td>2459.441065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Ideal</td>\n",
       "      <td>VVS1</td>\n",
       "      <td>2468.129458</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          cut clarity        price\n",
       "1        Fair      IF  1912.333333\n",
       "14       Good    VVS1  2254.774194\n",
       "17      Ideal      IF  2272.913366\n",
       "38  Very Good    VVS1  2459.441065\n",
       "22      Ideal    VVS1  2468.129458"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dia_agg.sort_values(\"price\").head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "8b067ef5",
   "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>cut</th>\n",
       "      <th>clarity</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Premium</td>\n",
       "      <td>SI2</td>\n",
       "      <td>5545.936928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fair</td>\n",
       "      <td>SI2</td>\n",
       "      <td>5173.916309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Very Good</td>\n",
       "      <td>SI2</td>\n",
       "      <td>4988.688095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Ideal</td>\n",
       "      <td>SI2</td>\n",
       "      <td>4755.952656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Good</td>\n",
       "      <td>SI2</td>\n",
       "      <td>4580.260870</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          cut clarity        price\n",
       "27    Premium     SI2  5545.936928\n",
       "3        Fair     SI2  5173.916309\n",
       "35  Very Good     SI2  4988.688095\n",
       "19      Ideal     SI2  4755.952656\n",
       "11       Good     SI2  4580.260870"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dia_agg.sort_values(\"price\", ascending = False).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b1968565",
   "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>cut</th>\n",
       "      <th>clarity</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Fair</td>\n",
       "      <td>VVS2</td>\n",
       "      <td>3349.768116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Fair</td>\n",
       "      <td>VVS1</td>\n",
       "      <td>3871.352941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Fair</td>\n",
       "      <td>VS2</td>\n",
       "      <td>4174.724138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Fair</td>\n",
       "      <td>VS1</td>\n",
       "      <td>4165.141176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fair</td>\n",
       "      <td>SI2</td>\n",
       "      <td>5173.916309</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cut clarity        price\n",
       "7  Fair    VVS2  3349.768116\n",
       "6  Fair    VVS1  3871.352941\n",
       "5  Fair     VS2  4174.724138\n",
       "4  Fair     VS1  4165.141176\n",
       "3  Fair     SI2  5173.916309"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dia_agg.sort_values([\"cut\", \"clarity\"], ascending = [True, False]).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1eb90a9",
   "metadata": {},
   "source": [
    "### pandas - melt()\n",
    "- wide form의 데이터프레임을 long form으로 자료구조를 변환하는 메서드\n",
    "- id_vars 인자에 기준이 되는 변수를 지정하여 처리\n",
    "- 통계 또는 머신러닝 실시 전에 데이터 구조를 맞추기 위해서 주로 활용"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "39dfcd87",
   "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>YEAR</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>DAY</th>\n",
       "      <th>X1HR</th>\n",
       "      <th>X2HR</th>\n",
       "      <th>X3HR</th>\n",
       "      <th>X4HR</th>\n",
       "      <th>X5HR</th>\n",
       "      <th>X6HR</th>\n",
       "      <th>X7HR</th>\n",
       "      <th>...</th>\n",
       "      <th>X15HR</th>\n",
       "      <th>X16HR</th>\n",
       "      <th>X17HR</th>\n",
       "      <th>X18HR</th>\n",
       "      <th>X19HR</th>\n",
       "      <th>X20HR</th>\n",
       "      <th>X21HR</th>\n",
       "      <th>X22HR</th>\n",
       "      <th>X23HR</th>\n",
       "      <th>X24HR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>60178</td>\n",
       "      <td>57862</td>\n",
       "      <td>56165</td>\n",
       "      <td>55135</td>\n",
       "      <td>54450</td>\n",
       "      <td>54401</td>\n",
       "      <td>54147</td>\n",
       "      <td>...</td>\n",
       "      <td>49213</td>\n",
       "      <td>49357</td>\n",
       "      <td>50362</td>\n",
       "      <td>53115</td>\n",
       "      <td>55205</td>\n",
       "      <td>55437</td>\n",
       "      <td>55348</td>\n",
       "      <td>55790</td>\n",
       "      <td>58195</td>\n",
       "      <td>59330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>57067</td>\n",
       "      <td>55148</td>\n",
       "      <td>53983</td>\n",
       "      <td>53571</td>\n",
       "      <td>54027</td>\n",
       "      <td>55388</td>\n",
       "      <td>57753</td>\n",
       "      <td>...</td>\n",
       "      <td>72228</td>\n",
       "      <td>71945</td>\n",
       "      <td>72641</td>\n",
       "      <td>73387</td>\n",
       "      <td>72294</td>\n",
       "      <td>69989</td>\n",
       "      <td>67893</td>\n",
       "      <td>66504</td>\n",
       "      <td>67435</td>\n",
       "      <td>69275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>66263</td>\n",
       "      <td>64233</td>\n",
       "      <td>63194</td>\n",
       "      <td>62461</td>\n",
       "      <td>62649</td>\n",
       "      <td>63396</td>\n",
       "      <td>65098</td>\n",
       "      <td>...</td>\n",
       "      <td>75576</td>\n",
       "      <td>75168</td>\n",
       "      <td>75791</td>\n",
       "      <td>75922</td>\n",
       "      <td>75469</td>\n",
       "      <td>73118</td>\n",
       "      <td>70769</td>\n",
       "      <td>68726</td>\n",
       "      <td>69593</td>\n",
       "      <td>71346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>68403</td>\n",
       "      <td>66143</td>\n",
       "      <td>65051</td>\n",
       "      <td>64245</td>\n",
       "      <td>64152</td>\n",
       "      <td>64678</td>\n",
       "      <td>66494</td>\n",
       "      <td>...</td>\n",
       "      <td>74977</td>\n",
       "      <td>74275</td>\n",
       "      <td>74909</td>\n",
       "      <td>75344</td>\n",
       "      <td>74504</td>\n",
       "      <td>71957</td>\n",
       "      <td>69715</td>\n",
       "      <td>68155</td>\n",
       "      <td>68948</td>\n",
       "      <td>70834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>68053</td>\n",
       "      <td>65801</td>\n",
       "      <td>64439</td>\n",
       "      <td>63437</td>\n",
       "      <td>63135</td>\n",
       "      <td>63250</td>\n",
       "      <td>63658</td>\n",
       "      <td>...</td>\n",
       "      <td>64767</td>\n",
       "      <td>63613</td>\n",
       "      <td>63095</td>\n",
       "      <td>63230</td>\n",
       "      <td>64253</td>\n",
       "      <td>63353</td>\n",
       "      <td>62455</td>\n",
       "      <td>62314</td>\n",
       "      <td>64350</td>\n",
       "      <td>66687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>64147</td>\n",
       "      <td>62010</td>\n",
       "      <td>60783</td>\n",
       "      <td>59779</td>\n",
       "      <td>59398</td>\n",
       "      <td>59296</td>\n",
       "      <td>58984</td>\n",
       "      <td>...</td>\n",
       "      <td>55408</td>\n",
       "      <td>55256</td>\n",
       "      <td>56000</td>\n",
       "      <td>58322</td>\n",
       "      <td>60456</td>\n",
       "      <td>60310</td>\n",
       "      <td>60173</td>\n",
       "      <td>60224</td>\n",
       "      <td>62288</td>\n",
       "      <td>63214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>60754</td>\n",
       "      <td>58974</td>\n",
       "      <td>57845</td>\n",
       "      <td>57387</td>\n",
       "      <td>57749</td>\n",
       "      <td>58794</td>\n",
       "      <td>60869</td>\n",
       "      <td>...</td>\n",
       "      <td>73064</td>\n",
       "      <td>72466</td>\n",
       "      <td>73274</td>\n",
       "      <td>73844</td>\n",
       "      <td>73287</td>\n",
       "      <td>71062</td>\n",
       "      <td>68628</td>\n",
       "      <td>66861</td>\n",
       "      <td>67697</td>\n",
       "      <td>69219</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   YEAR  MONTH  DAY   X1HR   X2HR   X3HR   X4HR   X5HR   X6HR   X7HR  ...  \\\n",
       "0  2017      1    1  60178  57862  56165  55135  54450  54401  54147  ...   \n",
       "1  2017      1    2  57067  55148  53983  53571  54027  55388  57753  ...   \n",
       "2  2017      1    3  66263  64233  63194  62461  62649  63396  65098  ...   \n",
       "3  2017      1    4  68403  66143  65051  64245  64152  64678  66494  ...   \n",
       "4  2017      1    5  68053  65801  64439  63437  63135  63250  63658  ...   \n",
       "5  2017      1    6  64147  62010  60783  59779  59398  59296  58984  ...   \n",
       "6  2017      1    7  60754  58974  57845  57387  57749  58794  60869  ...   \n",
       "\n",
       "   X15HR  X16HR  X17HR  X18HR  X19HR  X20HR  X21HR  X22HR  X23HR  X24HR  \n",
       "0  49213  49357  50362  53115  55205  55437  55348  55790  58195  59330  \n",
       "1  72228  71945  72641  73387  72294  69989  67893  66504  67435  69275  \n",
       "2  75576  75168  75791  75922  75469  73118  70769  68726  69593  71346  \n",
       "3  74977  74275  74909  75344  74504  71957  69715  68155  68948  70834  \n",
       "4  64767  63613  63095  63230  64253  63353  62455  62314  64350  66687  \n",
       "5  55408  55256  56000  58322  60456  60310  60173  60224  62288  63214  \n",
       "6  73064  72466  73274  73844  73287  71062  68628  66861  67697  69219  \n",
       "\n",
       "[7 rows x 27 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "elec = pd.read_csv(\"강의자료/실습파일/elec_load_2017_7d.csv\")\n",
    "elec"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "2bc33c6e",
   "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>YEAR</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>DAY</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>X1HR</td>\n",
       "      <td>60178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>X1HR</td>\n",
       "      <td>57067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>X1HR</td>\n",
       "      <td>66263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>X1HR</td>\n",
       "      <td>68403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>X1HR</td>\n",
       "      <td>68053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>X24HR</td>\n",
       "      <td>71346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>X24HR</td>\n",
       "      <td>70834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>X24HR</td>\n",
       "      <td>66687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>X24HR</td>\n",
       "      <td>63214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>X24HR</td>\n",
       "      <td>69219</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>168 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     YEAR  MONTH  DAY variable  value\n",
       "0    2017      1    1     X1HR  60178\n",
       "1    2017      1    2     X1HR  57067\n",
       "2    2017      1    3     X1HR  66263\n",
       "3    2017      1    4     X1HR  68403\n",
       "4    2017      1    5     X1HR  68053\n",
       "..    ...    ...  ...      ...    ...\n",
       "163  2017      1    3    X24HR  71346\n",
       "164  2017      1    4    X24HR  70834\n",
       "165  2017      1    5    X24HR  66687\n",
       "166  2017      1    6    X24HR  63214\n",
       "167  2017      1    7    X24HR  69219\n",
       "\n",
       "[168 rows x 5 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "elec_melt = elec.melt(id_vars = [\"YEAR\", \"MONTH\", \"DAY\"])\n",
    "elec_melt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "250fe53c",
   "metadata": {},
   "source": [
    "### pandas - pivot()\n",
    "- long term의 데이터프레임을 wide form으로 자료구조를 변환하는 메서드\n",
    "- index/columns/values 인자에 각각 대상 변수를 지정하여 출력 데이터 구조 결정\n",
    "- 데이터를 요약하거나 군집분석 실시 전 데이터 전처리에 주로 활용"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "2f2ec851",
   "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>variable</th>\n",
       "      <th>X10HR</th>\n",
       "      <th>X11HR</th>\n",
       "      <th>X12HR</th>\n",
       "      <th>X13HR</th>\n",
       "      <th>X14HR</th>\n",
       "      <th>X15HR</th>\n",
       "      <th>X16HR</th>\n",
       "      <th>X17HR</th>\n",
       "      <th>X18HR</th>\n",
       "      <th>X19HR</th>\n",
       "      <th>...</th>\n",
       "      <th>X23HR</th>\n",
       "      <th>X24HR</th>\n",
       "      <th>X2HR</th>\n",
       "      <th>X3HR</th>\n",
       "      <th>X4HR</th>\n",
       "      <th>X5HR</th>\n",
       "      <th>X6HR</th>\n",
       "      <th>X7HR</th>\n",
       "      <th>X8HR</th>\n",
       "      <th>X9HR</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YEAR</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>DAY</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",
       "      <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 rowspan=\"7\" valign=\"top\">2017</th>\n",
       "      <th rowspan=\"7\" valign=\"top\">1</th>\n",
       "      <th>1</th>\n",
       "      <td>49572</td>\n",
       "      <td>50034</td>\n",
       "      <td>50026</td>\n",
       "      <td>49515</td>\n",
       "      <td>49365</td>\n",
       "      <td>49213</td>\n",
       "      <td>49357</td>\n",
       "      <td>50362</td>\n",
       "      <td>53115</td>\n",
       "      <td>55205</td>\n",
       "      <td>...</td>\n",
       "      <td>58195</td>\n",
       "      <td>59330</td>\n",
       "      <td>57862</td>\n",
       "      <td>56165</td>\n",
       "      <td>55135</td>\n",
       "      <td>54450</td>\n",
       "      <td>54401</td>\n",
       "      <td>54147</td>\n",
       "      <td>52467</td>\n",
       "      <td>50469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>70869</td>\n",
       "      <td>72867</td>\n",
       "      <td>73115</td>\n",
       "      <td>69165</td>\n",
       "      <td>71429</td>\n",
       "      <td>72228</td>\n",
       "      <td>71945</td>\n",
       "      <td>72641</td>\n",
       "      <td>73387</td>\n",
       "      <td>72294</td>\n",
       "      <td>...</td>\n",
       "      <td>67435</td>\n",
       "      <td>69275</td>\n",
       "      <td>55148</td>\n",
       "      <td>53983</td>\n",
       "      <td>53571</td>\n",
       "      <td>54027</td>\n",
       "      <td>55388</td>\n",
       "      <td>57753</td>\n",
       "      <td>60781</td>\n",
       "      <td>66494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>75999</td>\n",
       "      <td>76522</td>\n",
       "      <td>76019</td>\n",
       "      <td>72203</td>\n",
       "      <td>74725</td>\n",
       "      <td>75576</td>\n",
       "      <td>75168</td>\n",
       "      <td>75791</td>\n",
       "      <td>75922</td>\n",
       "      <td>75469</td>\n",
       "      <td>...</td>\n",
       "      <td>69593</td>\n",
       "      <td>71346</td>\n",
       "      <td>64233</td>\n",
       "      <td>63194</td>\n",
       "      <td>62461</td>\n",
       "      <td>62649</td>\n",
       "      <td>63396</td>\n",
       "      <td>65098</td>\n",
       "      <td>67745</td>\n",
       "      <td>72982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>76175</td>\n",
       "      <td>76375</td>\n",
       "      <td>75862</td>\n",
       "      <td>72288</td>\n",
       "      <td>74360</td>\n",
       "      <td>74977</td>\n",
       "      <td>74275</td>\n",
       "      <td>74909</td>\n",
       "      <td>75344</td>\n",
       "      <td>74504</td>\n",
       "      <td>...</td>\n",
       "      <td>68948</td>\n",
       "      <td>70834</td>\n",
       "      <td>66143</td>\n",
       "      <td>65051</td>\n",
       "      <td>64245</td>\n",
       "      <td>64152</td>\n",
       "      <td>64678</td>\n",
       "      <td>66494</td>\n",
       "      <td>68851</td>\n",
       "      <td>73835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>66806</td>\n",
       "      <td>68030</td>\n",
       "      <td>67878</td>\n",
       "      <td>64439</td>\n",
       "      <td>64986</td>\n",
       "      <td>64767</td>\n",
       "      <td>63613</td>\n",
       "      <td>63095</td>\n",
       "      <td>63230</td>\n",
       "      <td>64253</td>\n",
       "      <td>...</td>\n",
       "      <td>64350</td>\n",
       "      <td>66687</td>\n",
       "      <td>65801</td>\n",
       "      <td>64439</td>\n",
       "      <td>63437</td>\n",
       "      <td>63135</td>\n",
       "      <td>63250</td>\n",
       "      <td>63658</td>\n",
       "      <td>63441</td>\n",
       "      <td>65640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>55679</td>\n",
       "      <td>56548</td>\n",
       "      <td>56435</td>\n",
       "      <td>55563</td>\n",
       "      <td>55759</td>\n",
       "      <td>55408</td>\n",
       "      <td>55256</td>\n",
       "      <td>56000</td>\n",
       "      <td>58322</td>\n",
       "      <td>60456</td>\n",
       "      <td>...</td>\n",
       "      <td>62288</td>\n",
       "      <td>63214</td>\n",
       "      <td>62010</td>\n",
       "      <td>60783</td>\n",
       "      <td>59779</td>\n",
       "      <td>59398</td>\n",
       "      <td>59296</td>\n",
       "      <td>58984</td>\n",
       "      <td>57533</td>\n",
       "      <td>56159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>75383</td>\n",
       "      <td>74984</td>\n",
       "      <td>74066</td>\n",
       "      <td>70335</td>\n",
       "      <td>72507</td>\n",
       "      <td>73064</td>\n",
       "      <td>72466</td>\n",
       "      <td>73274</td>\n",
       "      <td>73844</td>\n",
       "      <td>73287</td>\n",
       "      <td>...</td>\n",
       "      <td>67697</td>\n",
       "      <td>69219</td>\n",
       "      <td>58974</td>\n",
       "      <td>57845</td>\n",
       "      <td>57387</td>\n",
       "      <td>57749</td>\n",
       "      <td>58794</td>\n",
       "      <td>60869</td>\n",
       "      <td>64665</td>\n",
       "      <td>71544</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "variable        X10HR  X11HR  X12HR  X13HR  X14HR  X15HR  X16HR  X17HR  X18HR  \\\n",
       "YEAR MONTH DAY                                                                  \n",
       "2017 1     1    49572  50034  50026  49515  49365  49213  49357  50362  53115   \n",
       "           2    70869  72867  73115  69165  71429  72228  71945  72641  73387   \n",
       "           3    75999  76522  76019  72203  74725  75576  75168  75791  75922   \n",
       "           4    76175  76375  75862  72288  74360  74977  74275  74909  75344   \n",
       "           5    66806  68030  67878  64439  64986  64767  63613  63095  63230   \n",
       "           6    55679  56548  56435  55563  55759  55408  55256  56000  58322   \n",
       "           7    75383  74984  74066  70335  72507  73064  72466  73274  73844   \n",
       "\n",
       "variable        X19HR  ...  X23HR  X24HR   X2HR   X3HR   X4HR   X5HR   X6HR  \\\n",
       "YEAR MONTH DAY         ...                                                    \n",
       "2017 1     1    55205  ...  58195  59330  57862  56165  55135  54450  54401   \n",
       "           2    72294  ...  67435  69275  55148  53983  53571  54027  55388   \n",
       "           3    75469  ...  69593  71346  64233  63194  62461  62649  63396   \n",
       "           4    74504  ...  68948  70834  66143  65051  64245  64152  64678   \n",
       "           5    64253  ...  64350  66687  65801  64439  63437  63135  63250   \n",
       "           6    60456  ...  62288  63214  62010  60783  59779  59398  59296   \n",
       "           7    73287  ...  67697  69219  58974  57845  57387  57749  58794   \n",
       "\n",
       "variable         X7HR   X8HR   X9HR  \n",
       "YEAR MONTH DAY                       \n",
       "2017 1     1    54147  52467  50469  \n",
       "           2    57753  60781  66494  \n",
       "           3    65098  67745  72982  \n",
       "           4    66494  68851  73835  \n",
       "           5    63658  63441  65640  \n",
       "           6    58984  57533  56159  \n",
       "           7    60869  64665  71544  \n",
       "\n",
       "[7 rows x 24 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "elec_melt.pivot(index = [\"YEAR\", \"MONTH\", \"DAY\"], columns = \"variable\", values = \"value\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "105e9b8f",
   "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>variable</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>DAY</th>\n",
       "      <th>X10HR</th>\n",
       "      <th>X11HR</th>\n",
       "      <th>X12HR</th>\n",
       "      <th>X13HR</th>\n",
       "      <th>X14HR</th>\n",
       "      <th>X15HR</th>\n",
       "      <th>X16HR</th>\n",
       "      <th>...</th>\n",
       "      <th>X23HR</th>\n",
       "      <th>X24HR</th>\n",
       "      <th>X2HR</th>\n",
       "      <th>X3HR</th>\n",
       "      <th>X4HR</th>\n",
       "      <th>X5HR</th>\n",
       "      <th>X6HR</th>\n",
       "      <th>X7HR</th>\n",
       "      <th>X8HR</th>\n",
       "      <th>X9HR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>49572</td>\n",
       "      <td>50034</td>\n",
       "      <td>50026</td>\n",
       "      <td>49515</td>\n",
       "      <td>49365</td>\n",
       "      <td>49213</td>\n",
       "      <td>49357</td>\n",
       "      <td>...</td>\n",
       "      <td>58195</td>\n",
       "      <td>59330</td>\n",
       "      <td>57862</td>\n",
       "      <td>56165</td>\n",
       "      <td>55135</td>\n",
       "      <td>54450</td>\n",
       "      <td>54401</td>\n",
       "      <td>54147</td>\n",
       "      <td>52467</td>\n",
       "      <td>50469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>70869</td>\n",
       "      <td>72867</td>\n",
       "      <td>73115</td>\n",
       "      <td>69165</td>\n",
       "      <td>71429</td>\n",
       "      <td>72228</td>\n",
       "      <td>71945</td>\n",
       "      <td>...</td>\n",
       "      <td>67435</td>\n",
       "      <td>69275</td>\n",
       "      <td>55148</td>\n",
       "      <td>53983</td>\n",
       "      <td>53571</td>\n",
       "      <td>54027</td>\n",
       "      <td>55388</td>\n",
       "      <td>57753</td>\n",
       "      <td>60781</td>\n",
       "      <td>66494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>75999</td>\n",
       "      <td>76522</td>\n",
       "      <td>76019</td>\n",
       "      <td>72203</td>\n",
       "      <td>74725</td>\n",
       "      <td>75576</td>\n",
       "      <td>75168</td>\n",
       "      <td>...</td>\n",
       "      <td>69593</td>\n",
       "      <td>71346</td>\n",
       "      <td>64233</td>\n",
       "      <td>63194</td>\n",
       "      <td>62461</td>\n",
       "      <td>62649</td>\n",
       "      <td>63396</td>\n",
       "      <td>65098</td>\n",
       "      <td>67745</td>\n",
       "      <td>72982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>76175</td>\n",
       "      <td>76375</td>\n",
       "      <td>75862</td>\n",
       "      <td>72288</td>\n",
       "      <td>74360</td>\n",
       "      <td>74977</td>\n",
       "      <td>74275</td>\n",
       "      <td>...</td>\n",
       "      <td>68948</td>\n",
       "      <td>70834</td>\n",
       "      <td>66143</td>\n",
       "      <td>65051</td>\n",
       "      <td>64245</td>\n",
       "      <td>64152</td>\n",
       "      <td>64678</td>\n",
       "      <td>66494</td>\n",
       "      <td>68851</td>\n",
       "      <td>73835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>66806</td>\n",
       "      <td>68030</td>\n",
       "      <td>67878</td>\n",
       "      <td>64439</td>\n",
       "      <td>64986</td>\n",
       "      <td>64767</td>\n",
       "      <td>63613</td>\n",
       "      <td>...</td>\n",
       "      <td>64350</td>\n",
       "      <td>66687</td>\n",
       "      <td>65801</td>\n",
       "      <td>64439</td>\n",
       "      <td>63437</td>\n",
       "      <td>63135</td>\n",
       "      <td>63250</td>\n",
       "      <td>63658</td>\n",
       "      <td>63441</td>\n",
       "      <td>65640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>55679</td>\n",
       "      <td>56548</td>\n",
       "      <td>56435</td>\n",
       "      <td>55563</td>\n",
       "      <td>55759</td>\n",
       "      <td>55408</td>\n",
       "      <td>55256</td>\n",
       "      <td>...</td>\n",
       "      <td>62288</td>\n",
       "      <td>63214</td>\n",
       "      <td>62010</td>\n",
       "      <td>60783</td>\n",
       "      <td>59779</td>\n",
       "      <td>59398</td>\n",
       "      <td>59296</td>\n",
       "      <td>58984</td>\n",
       "      <td>57533</td>\n",
       "      <td>56159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>75383</td>\n",
       "      <td>74984</td>\n",
       "      <td>74066</td>\n",
       "      <td>70335</td>\n",
       "      <td>72507</td>\n",
       "      <td>73064</td>\n",
       "      <td>72466</td>\n",
       "      <td>...</td>\n",
       "      <td>67697</td>\n",
       "      <td>69219</td>\n",
       "      <td>58974</td>\n",
       "      <td>57845</td>\n",
       "      <td>57387</td>\n",
       "      <td>57749</td>\n",
       "      <td>58794</td>\n",
       "      <td>60869</td>\n",
       "      <td>64665</td>\n",
       "      <td>71544</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "variable  YEAR  MONTH  DAY  X10HR  X11HR  X12HR  X13HR  X14HR  X15HR  X16HR  \\\n",
       "0         2017      1    1  49572  50034  50026  49515  49365  49213  49357   \n",
       "1         2017      1    2  70869  72867  73115  69165  71429  72228  71945   \n",
       "2         2017      1    3  75999  76522  76019  72203  74725  75576  75168   \n",
       "3         2017      1    4  76175  76375  75862  72288  74360  74977  74275   \n",
       "4         2017      1    5  66806  68030  67878  64439  64986  64767  63613   \n",
       "5         2017      1    6  55679  56548  56435  55563  55759  55408  55256   \n",
       "6         2017      1    7  75383  74984  74066  70335  72507  73064  72466   \n",
       "\n",
       "variable  ...  X23HR  X24HR   X2HR   X3HR   X4HR   X5HR   X6HR   X7HR   X8HR  \\\n",
       "0         ...  58195  59330  57862  56165  55135  54450  54401  54147  52467   \n",
       "1         ...  67435  69275  55148  53983  53571  54027  55388  57753  60781   \n",
       "2         ...  69593  71346  64233  63194  62461  62649  63396  65098  67745   \n",
       "3         ...  68948  70834  66143  65051  64245  64152  64678  66494  68851   \n",
       "4         ...  64350  66687  65801  64439  63437  63135  63250  63658  63441   \n",
       "5         ...  62288  63214  62010  60783  59779  59398  59296  58984  57533   \n",
       "6         ...  67697  69219  58974  57845  57387  57749  58794  60869  64665   \n",
       "\n",
       "variable   X9HR  \n",
       "0         50469  \n",
       "1         66494  \n",
       "2         72982  \n",
       "3         73835  \n",
       "4         65640  \n",
       "5         56159  \n",
       "6         71544  \n",
       "\n",
       "[7 rows x 27 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "elec_pivot = elec_melt.pivot(index = [\"YEAR\", \"MONTH\", \"DAY\"], columns = \"variable\", values = \"value\").reset_index()\n",
    "elec_pivot"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a46a0b88",
   "metadata": {},
   "source": [
    "## Q1 workingday가 아니면서 holiday가 아닌 날의 비율은?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "f3760b88",
   "metadata": {
    "scrolled": true
   },
   "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>datetime</th>\n",
       "      <th>season</th>\n",
       "      <th>holiday</th>\n",
       "      <th>workingday</th>\n",
       "      <th>weather</th>\n",
       "      <th>temp</th>\n",
       "      <th>atemp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>windspeed</th>\n",
       "      <th>casual</th>\n",
       "      <th>registered</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-01-01 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>81</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-01-01 01:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-01-01 02:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>9.02</td>\n",
       "      <td>13.635</td>\n",
       "      <td>80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-01-01 03:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-01-01 04:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>9.84</td>\n",
       "      <td>14.395</td>\n",
       "      <td>75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              datetime  season  holiday  workingday  weather  temp   atemp  \\\n",
       "0  2011-01-01 00:00:00       1        0           0        1  9.84  14.395   \n",
       "1  2011-01-01 01:00:00       1        0           0        1  9.02  13.635   \n",
       "2  2011-01-01 02:00:00       1        0           0        1  9.02  13.635   \n",
       "3  2011-01-01 03:00:00       1        0           0        1  9.84  14.395   \n",
       "4  2011-01-01 04:00:00       1        0           0        1  9.84  14.395   \n",
       "\n",
       "   humidity  windspeed  casual  registered  count  \n",
       "0        81        0.0       3          13     16  \n",
       "1        80        0.0       8          32     40  \n",
       "2        80        0.0       5          27     32  \n",
       "3        75        0.0       3          10     13  \n",
       "4        75        0.0       0           1      1  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q1 = pd.read_csv(\"강의자료/실습파일/bike.csv\")\n",
    "Q1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "17bae2a0",
   "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>holiday</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>workingday</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3163</td>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7412</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "holiday        0    1\n",
       "workingday           \n",
       "0           3163  311\n",
       "1           7412    0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(Q1[\"workingday\"], Q1[\"holiday\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "9f0d68ec",
   "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>holiday</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>workingday</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.290557</td>\n",
       "      <td>0.028569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.680875</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "holiday            0         1\n",
       "workingday                    \n",
       "0           0.290557  0.028569\n",
       "1           0.680875  0.000000"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(Q1[\"workingday\"], Q1[\"holiday\"], normalize = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "878749d8",
   "metadata": {},
   "source": [
    "## Q2 가장 많은 데이터가 있는 세공 수준과 색상 조합을 순서대로 고르면?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "d676765f",
   "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>carat</th>\n",
       "      <th>cut</th>\n",
       "      <th>color</th>\n",
       "      <th>clarity</th>\n",
       "      <th>depth</th>\n",
       "      <th>table</th>\n",
       "      <th>price</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>z</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.23</td>\n",
       "      <td>Ideal</td>\n",
       "      <td>E</td>\n",
       "      <td>SI2</td>\n",
       "      <td>61.5</td>\n",
       "      <td>55.0</td>\n",
       "      <td>326</td>\n",
       "      <td>3.95</td>\n",
       "      <td>3.98</td>\n",
       "      <td>2.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.21</td>\n",
       "      <td>Premium</td>\n",
       "      <td>E</td>\n",
       "      <td>SI1</td>\n",
       "      <td>59.8</td>\n",
       "      <td>61.0</td>\n",
       "      <td>326</td>\n",
       "      <td>3.89</td>\n",
       "      <td>3.84</td>\n",
       "      <td>2.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.23</td>\n",
       "      <td>Good</td>\n",
       "      <td>E</td>\n",
       "      <td>VS1</td>\n",
       "      <td>56.9</td>\n",
       "      <td>65.0</td>\n",
       "      <td>327</td>\n",
       "      <td>4.05</td>\n",
       "      <td>4.07</td>\n",
       "      <td>2.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.29</td>\n",
       "      <td>Premium</td>\n",
       "      <td>I</td>\n",
       "      <td>VS2</td>\n",
       "      <td>62.4</td>\n",
       "      <td>58.0</td>\n",
       "      <td>334</td>\n",
       "      <td>4.20</td>\n",
       "      <td>4.23</td>\n",
       "      <td>2.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.31</td>\n",
       "      <td>Good</td>\n",
       "      <td>J</td>\n",
       "      <td>SI2</td>\n",
       "      <td>63.3</td>\n",
       "      <td>58.0</td>\n",
       "      <td>335</td>\n",
       "      <td>4.34</td>\n",
       "      <td>4.35</td>\n",
       "      <td>2.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   carat      cut color clarity  depth  table  price     x     y     z\n",
       "0   0.23    Ideal     E     SI2   61.5   55.0    326  3.95  3.98  2.43\n",
       "1   0.21  Premium     E     SI1   59.8   61.0    326  3.89  3.84  2.31\n",
       "2   0.23     Good     E     VS1   56.9   65.0    327  4.05  4.07  2.31\n",
       "3   0.29  Premium     I     VS2   62.4   58.0    334  4.20  4.23  2.63\n",
       "4   0.31     Good     J     SI2   63.3   58.0    335  4.34  4.35  2.75"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q2 = pd.read_csv(\"강의자료/실습파일/diamonds.csv\")\n",
    "Q2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "eaff40d9",
   "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>color</th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "      <th>F</th>\n",
       "      <th>G</th>\n",
       "      <th>H</th>\n",
       "      <th>I</th>\n",
       "      <th>J</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cut</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>Fair</th>\n",
       "      <td>163</td>\n",
       "      <td>224</td>\n",
       "      <td>312</td>\n",
       "      <td>314</td>\n",
       "      <td>303</td>\n",
       "      <td>175</td>\n",
       "      <td>119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Good</th>\n",
       "      <td>662</td>\n",
       "      <td>933</td>\n",
       "      <td>909</td>\n",
       "      <td>871</td>\n",
       "      <td>702</td>\n",
       "      <td>522</td>\n",
       "      <td>307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ideal</th>\n",
       "      <td>2834</td>\n",
       "      <td>3903</td>\n",
       "      <td>3826</td>\n",
       "      <td>4884</td>\n",
       "      <td>3115</td>\n",
       "      <td>2093</td>\n",
       "      <td>896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Premium</th>\n",
       "      <td>1603</td>\n",
       "      <td>2337</td>\n",
       "      <td>2331</td>\n",
       "      <td>2924</td>\n",
       "      <td>2360</td>\n",
       "      <td>1428</td>\n",
       "      <td>808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Very Good</th>\n",
       "      <td>1513</td>\n",
       "      <td>2400</td>\n",
       "      <td>2164</td>\n",
       "      <td>2299</td>\n",
       "      <td>1824</td>\n",
       "      <td>1204</td>\n",
       "      <td>678</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "color         D     E     F     G     H     I    J\n",
       "cut                                               \n",
       "Fair        163   224   312   314   303   175  119\n",
       "Good        662   933   909   871   702   522  307\n",
       "Ideal      2834  3903  3826  4884  3115  2093  896\n",
       "Premium    1603  2337  2331  2924  2360  1428  808\n",
       "Very Good  1513  2400  2164  2299  1824  1204  678"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(Q2[\"cut\"], Q2[\"color\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "9f47d9ce",
   "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>cut</th>\n",
       "      <th>color</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Ideal</td>\n",
       "      <td>G</td>\n",
       "      <td>4884</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      cut color  value\n",
       "17  Ideal     G   4884"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(Q2[\"cut\"], Q2[\"color\"]).reset_index().melt(id_vars = \"cut\").sort_values(\"value\", ascending = False).head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a22cbe5e",
   "metadata": {},
   "source": [
    "## Q3 세공수준별 색상별 카격과 케럿의 평균을 구하고 1캐럿당 가격이 가장 높은 세공수준과 색상 조합을 순서대로 고르면?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "6936d986",
   "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>carat</th>\n",
       "      <th>cut</th>\n",
       "      <th>color</th>\n",
       "      <th>clarity</th>\n",
       "      <th>depth</th>\n",
       "      <th>table</th>\n",
       "      <th>price</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>z</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.23</td>\n",
       "      <td>Ideal</td>\n",
       "      <td>E</td>\n",
       "      <td>SI2</td>\n",
       "      <td>61.5</td>\n",
       "      <td>55.0</td>\n",
       "      <td>326</td>\n",
       "      <td>3.95</td>\n",
       "      <td>3.98</td>\n",
       "      <td>2.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.21</td>\n",
       "      <td>Premium</td>\n",
       "      <td>E</td>\n",
       "      <td>SI1</td>\n",
       "      <td>59.8</td>\n",
       "      <td>61.0</td>\n",
       "      <td>326</td>\n",
       "      <td>3.89</td>\n",
       "      <td>3.84</td>\n",
       "      <td>2.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.23</td>\n",
       "      <td>Good</td>\n",
       "      <td>E</td>\n",
       "      <td>VS1</td>\n",
       "      <td>56.9</td>\n",
       "      <td>65.0</td>\n",
       "      <td>327</td>\n",
       "      <td>4.05</td>\n",
       "      <td>4.07</td>\n",
       "      <td>2.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.29</td>\n",
       "      <td>Premium</td>\n",
       "      <td>I</td>\n",
       "      <td>VS2</td>\n",
       "      <td>62.4</td>\n",
       "      <td>58.0</td>\n",
       "      <td>334</td>\n",
       "      <td>4.20</td>\n",
       "      <td>4.23</td>\n",
       "      <td>2.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.31</td>\n",
       "      <td>Good</td>\n",
       "      <td>J</td>\n",
       "      <td>SI2</td>\n",
       "      <td>63.3</td>\n",
       "      <td>58.0</td>\n",
       "      <td>335</td>\n",
       "      <td>4.34</td>\n",
       "      <td>4.35</td>\n",
       "      <td>2.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   carat      cut color clarity  depth  table  price     x     y     z\n",
       "0   0.23    Ideal     E     SI2   61.5   55.0    326  3.95  3.98  2.43\n",
       "1   0.21  Premium     E     SI1   59.8   61.0    326  3.89  3.84  2.31\n",
       "2   0.23     Good     E     VS1   56.9   65.0    327  4.05  4.07  2.31\n",
       "3   0.29  Premium     I     VS2   62.4   58.0    334  4.20  4.23  2.63\n",
       "4   0.31     Good     J     SI2   63.3   58.0    335  4.34  4.35  2.75"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q3 = pd.read_csv(\"강의자료/실습파일/diamonds.csv\")\n",
    "Q3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "8a70cedd",
   "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>cut</th>\n",
       "      <th>color</th>\n",
       "      <th>price</th>\n",
       "      <th>carat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fair</td>\n",
       "      <td>D</td>\n",
       "      <td>4291.061350</td>\n",
       "      <td>0.920123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Fair</td>\n",
       "      <td>E</td>\n",
       "      <td>3682.312500</td>\n",
       "      <td>0.856607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Fair</td>\n",
       "      <td>F</td>\n",
       "      <td>3827.003205</td>\n",
       "      <td>0.904712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fair</td>\n",
       "      <td>G</td>\n",
       "      <td>4239.254777</td>\n",
       "      <td>1.023822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Fair</td>\n",
       "      <td>H</td>\n",
       "      <td>5135.683168</td>\n",
       "      <td>1.219175</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cut color        price     carat\n",
       "0  Fair     D  4291.061350  0.920123\n",
       "1  Fair     E  3682.312500  0.856607\n",
       "2  Fair     F  3827.003205  0.904712\n",
       "3  Fair     G  4239.254777  1.023822\n",
       "4  Fair     H  5135.683168  1.219175"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q3_agg = Q3.groupby([\"cut\", \"color\"])[[\"price\", \"carat\"]].mean().reset_index()\n",
    "Q3_agg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "6211cc5b",
   "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>cut</th>\n",
       "      <th>color</th>\n",
       "      <th>price</th>\n",
       "      <th>carat</th>\n",
       "      <th>ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Premium</td>\n",
       "      <td>G</td>\n",
       "      <td>4500.742134</td>\n",
       "      <td>0.841488</td>\n",
       "      <td>5348.553755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Ideal</td>\n",
       "      <td>G</td>\n",
       "      <td>3720.706388</td>\n",
       "      <td>0.700715</td>\n",
       "      <td>5309.874382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Premium</td>\n",
       "      <td>F</td>\n",
       "      <td>4324.890176</td>\n",
       "      <td>0.827036</td>\n",
       "      <td>5229.388117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Premium</td>\n",
       "      <td>I</td>\n",
       "      <td>5946.180672</td>\n",
       "      <td>1.144937</td>\n",
       "      <td>5193.456761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Ideal</td>\n",
       "      <td>F</td>\n",
       "      <td>3374.939362</td>\n",
       "      <td>0.655829</td>\n",
       "      <td>5146.069664</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        cut color        price     carat        ratio\n",
       "24  Premium     G  4500.742134  0.841488  5348.553755\n",
       "17    Ideal     G  3720.706388  0.700715  5309.874382\n",
       "23  Premium     F  4324.890176  0.827036  5229.388117\n",
       "26  Premium     I  5946.180672  1.144937  5193.456761\n",
       "16    Ideal     F  3374.939362  0.655829  5146.069664"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q3_agg[\"ratio\"] = Q3_agg[\"price\"] / Q3_agg[\"carat\"]\n",
    "Q3_agg = Q3_agg.sort_values(\"ratio\", ascending = False)\n",
    "Q3_agg.head()"
   ]
  }
 ],
 "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}