{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# new_pivot_unstack_melt_MultiIndex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:19.620038",
     "start_time": "2017-01-22T04:27:18.466161"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## データ\n",
    "- 4人のデータ\n",
    "- 性別, 年齢\n",
    "- ある年の体重\n",
    "- データ入力がめんどうなので年しか変えていない"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:19.662984",
     "start_time": "2017-01-22T04:27:19.622091"
    },
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>year</th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>f</td>\n",
       "      <td>10</td>\n",
       "      <td>2016</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>m</td>\n",
       "      <td>10</td>\n",
       "      <td>2016</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>f</td>\n",
       "      <td>10</td>\n",
       "      <td>2016</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d</td>\n",
       "      <td>m</td>\n",
       "      <td>10</td>\n",
       "      <td>2016</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>f</td>\n",
       "      <td>10</td>\n",
       "      <td>2017</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>b</td>\n",
       "      <td>m</td>\n",
       "      <td>10</td>\n",
       "      <td>2017</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>c</td>\n",
       "      <td>f</td>\n",
       "      <td>10</td>\n",
       "      <td>2017</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>d</td>\n",
       "      <td>m</td>\n",
       "      <td>10</td>\n",
       "      <td>2017</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id sex  age  year  weight\n",
       "0  a   f   10  2016      49\n",
       "1  b   m   10  2016      59\n",
       "2  c   f   10  2016      54\n",
       "3  d   m   10  2016      64\n",
       "4  a   f   10  2017      49\n",
       "5  b   m   10  2017      59\n",
       "6  c   f   10  2017      54\n",
       "7  d   m   10  2017      64"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import io\n",
    "df = pd.read_csv(io.StringIO(\"\"\"\n",
    "id sex age year weight\n",
    "a f 10 2016 49\n",
    "b m 10 2016 59\n",
    "c f 10 2016 54\n",
    "d m 10 2016 64\n",
    "a f 10 2017 49\n",
    "b m 10 2017 59\n",
    "c f 10 2017 54\n",
    "d m 10 2017 64\n",
    "\"\"\"), sep=\" \")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 年ごとの体重"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:19.699079",
     "start_time": "2017-01-22T04:27:19.673672"
    },
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>year</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>49</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>59</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>54</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>64</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "year  2016  2017\n",
       "id              \n",
       "a       49    49\n",
       "b       59    59\n",
       "c       54    54\n",
       "d       64    64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot(index=\"id\", columns=\"year\", values=\"weight\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## pivotの限界\n",
    "各引数を複数指定できない"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:19.729443",
     "start_time": "2017-01-22T04:27:19.701720"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "err multi index\n",
      "err multi columns\n",
      "err multi values\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    df.pivot(index=[\"id\", \"sex\"], columns=\"year\", values=\"weight\")\n",
    "except:\n",
    "    print(\"err multi index\")\n",
    "\n",
    "try:\n",
    "    df.pivot(index=\"id\", columns=[\"year\", \"sex\"], values=\"weight\")\n",
    "except:\n",
    "    print(\"err multi columns\")\n",
    "\n",
    "try:\n",
    "    df.pivot(index=\"id\", columns=\"year\", values=[\"weight\", \"age\"])\n",
    "except:\n",
    "    print(\"err multi values\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T03:32:32.181894",
     "start_time": "2017-01-22T03:32:32.169018"
    }
   },
   "source": [
    "## メソッドのみで対応する"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### index と unstack\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:19.788056",
     "start_time": "2017-01-22T04:27:19.734181"
    },
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">age</th>\n",
       "      <th colspan=\"4\" halign=\"left\">weight</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th colspan=\"2\" halign=\"left\">2016</th>\n",
       "      <th colspan=\"2\" halign=\"left\">2017</th>\n",
       "      <th colspan=\"2\" halign=\"left\">2016</th>\n",
       "      <th colspan=\"2\" halign=\"left\">2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th>f</th>\n",
       "      <th>m</th>\n",
       "      <th>f</th>\n",
       "      <th>m</th>\n",
       "      <th>f</th>\n",
       "      <th>m</th>\n",
       "      <th>f</th>\n",
       "      <th>m</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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>a</th>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>64.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       age                   weight                  \n",
       "year  2016        2017         2016        2017      \n",
       "sex      f     m     f     m      f     m     f     m\n",
       "id                                                   \n",
       "a     10.0   NaN  10.0   NaN   49.0   NaN  49.0   NaN\n",
       "b      NaN  10.0   NaN  10.0    NaN  59.0   NaN  59.0\n",
       "c     10.0   NaN  10.0   NaN   54.0   NaN  54.0   NaN\n",
       "d      NaN  10.0   NaN  10.0    NaN  64.0   NaN  64.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "indexed_unstacked = df.set_index([\"id\", \"sex\", \"year\"]).unstack([\"year\", \"sex\"])\n",
    "indexed_unstacked"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:06:32.781298",
     "start_time": "2017-01-22T04:06:32.534099"
    }
   },
   "source": [
    "### MultiIndexを1次元のリストにする\n",
    "- MultiIndexはtupleのlistとして扱える\n",
    "- 文字列結合して対応する"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:19.803965",
     "start_time": "2017-01-22T04:27:19.794149"
    },
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MultiIndex(levels=[['age', 'weight'], [2016, 2017], ['f', 'm']],\n",
       "           labels=[[0, 0, 0, 0, 1, 1, 1, 1], [0, 0, 1, 1, 0, 0, 1, 1], [0, 1, 0, 1, 0, 1, 0, 1]],\n",
       "           names=[None, 'year', 'sex'])"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "indexed_unstacked.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:19.832458",
     "start_time": "2017-01-22T04:27:19.812748"
    },
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('age', 2016, 'f'),\n",
       " ('age', 2016, 'm'),\n",
       " ('age', 2017, 'f'),\n",
       " ('age', 2017, 'm'),\n",
       " ('weight', 2016, 'f'),\n",
       " ('weight', 2016, 'm'),\n",
       " ('weight', 2017, 'f'),\n",
       " ('weight', 2017, 'm')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(indexed_unstacked.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:19.893369",
     "start_time": "2017-01-22T04:27:19.840130"
    },
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>age_2016_f</th>\n",
       "      <th>age_2016_m</th>\n",
       "      <th>age_2017_f</th>\n",
       "      <th>age_2017_m</th>\n",
       "      <th>weight_2016_f</th>\n",
       "      <th>weight_2016_m</th>\n",
       "      <th>weight_2017_f</th>\n",
       "      <th>weight_2017_m</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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>a</th>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>64.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    age_2016_f  age_2016_m  age_2017_f  age_2017_m  weight_2016_f  \\\n",
       "id                                                                  \n",
       "a         10.0         NaN        10.0         NaN           49.0   \n",
       "b          NaN        10.0         NaN        10.0            NaN   \n",
       "c         10.0         NaN        10.0         NaN           54.0   \n",
       "d          NaN        10.0         NaN        10.0            NaN   \n",
       "\n",
       "    weight_2016_m  weight_2017_f  weight_2017_m  \n",
       "id                                               \n",
       "a             NaN           49.0            NaN  \n",
       "b            59.0            NaN           59.0  \n",
       "c             NaN           54.0            NaN  \n",
       "d            64.0            NaN           64.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "indexed_unstacked.columns = [\"_\".join(map(str, c)) for c in indexed_unstacked.columns]\n",
    "indexed_unstacked"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-21T23:52:47.225684",
     "start_time": "2017-01-21T23:52:47.168393"
    }
   },
   "source": [
    "## カラムを結合して対応する"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rのtidyrではuniteという関数を使って対応しているので、それの真似"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:19.951339",
     "start_time": "2017-01-22T04:27:19.897780"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>year</th>\n",
       "      <th>weight</th>\n",
       "      <th>year_sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>f</td>\n",
       "      <td>10</td>\n",
       "      <td>2016</td>\n",
       "      <td>49</td>\n",
       "      <td>2016_f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>m</td>\n",
       "      <td>10</td>\n",
       "      <td>2016</td>\n",
       "      <td>59</td>\n",
       "      <td>2016_m</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>f</td>\n",
       "      <td>10</td>\n",
       "      <td>2016</td>\n",
       "      <td>54</td>\n",
       "      <td>2016_f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d</td>\n",
       "      <td>m</td>\n",
       "      <td>10</td>\n",
       "      <td>2016</td>\n",
       "      <td>64</td>\n",
       "      <td>2016_m</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>f</td>\n",
       "      <td>10</td>\n",
       "      <td>2017</td>\n",
       "      <td>49</td>\n",
       "      <td>2017_f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>b</td>\n",
       "      <td>m</td>\n",
       "      <td>10</td>\n",
       "      <td>2017</td>\n",
       "      <td>59</td>\n",
       "      <td>2017_m</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>c</td>\n",
       "      <td>f</td>\n",
       "      <td>10</td>\n",
       "      <td>2017</td>\n",
       "      <td>54</td>\n",
       "      <td>2017_f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>d</td>\n",
       "      <td>m</td>\n",
       "      <td>10</td>\n",
       "      <td>2017</td>\n",
       "      <td>64</td>\n",
       "      <td>2017_m</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id sex  age  year  weight year_sex\n",
       "0  a   f   10  2016      49   2016_f\n",
       "1  b   m   10  2016      59   2016_m\n",
       "2  c   f   10  2016      54   2016_f\n",
       "3  d   m   10  2016      64   2016_m\n",
       "4  a   f   10  2017      49   2017_f\n",
       "5  b   m   10  2017      59   2017_m\n",
       "6  c   f   10  2017      54   2017_f\n",
       "7  d   m   10  2017      64   2017_m"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_unite = df.copy()\n",
    "df_unite[\"year_sex\"] = df_unite[\"year\"].apply(str) + \"_\" + df_unite[\"sex\"]\n",
    "df_unite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:19.989020",
     "start_time": "2017-01-22T04:27:19.955388"
    },
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>year_sex</th>\n",
       "      <th>2016_f</th>\n",
       "      <th>2016_m</th>\n",
       "      <th>2017_f</th>\n",
       "      <th>2017_m</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>49.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>54.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>64.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "year_sex  2016_f  2016_m  2017_f  2017_m\n",
       "id                                      \n",
       "a           49.0     NaN    49.0     NaN\n",
       "b            NaN    59.0     NaN    59.0\n",
       "c           54.0     NaN    54.0     NaN\n",
       "d            NaN    64.0     NaN    64.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_unite.pivot(index=\"id\", columns=\"year_sex\", values=\"weight\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### おまけ：元のデータフレームに戻す"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:20.015037",
     "start_time": "2017-01-22T04:27:19.991738"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['age_2016_f', 'age_2016_m', 'age_2017_f', 'age_2017_m', 'weight_2016_f',\n",
       "       'weight_2016_m', 'weight_2017_f', 'weight_2017_m'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# startswith(tuple) で複数対応できる, builtinも/endswithも\n",
    "all_columns = indexed_unstacked.columns[\n",
    "    indexed_unstacked.columns.str.startswith((\"age\", \"weight\",))]\n",
    "all_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:20.069068",
     "start_time": "2017-01-22T04:27:20.018317"
    },
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>age_2016_f</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>age_2016_f</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>age_2016_f</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d</td>\n",
       "      <td>age_2016_f</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>age_2016_m</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id    variable  value\n",
       "0  a  age_2016_f   10.0\n",
       "1  b  age_2016_f    NaN\n",
       "2  c  age_2016_f   10.0\n",
       "3  d  age_2016_f    NaN\n",
       "4  a  age_2016_m    NaN"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "melted = pd.melt(indexed_unstacked.reset_index(), id_vars='id', value_vars=list(all_columns))\n",
    "melted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:20.136669",
     "start_time": "2017-01-22T04:27:20.079372"
    },
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>value</th>\n",
       "      <th>column</th>\n",
       "      <th>year</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>10.0</td>\n",
       "      <td>age</td>\n",
       "      <td>2016</td>\n",
       "      <td>f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>c</td>\n",
       "      <td>10.0</td>\n",
       "      <td>age</td>\n",
       "      <td>2016</td>\n",
       "      <td>f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>10.0</td>\n",
       "      <td>age</td>\n",
       "      <td>2016</td>\n",
       "      <td>m</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d</td>\n",
       "      <td>10.0</td>\n",
       "      <td>age</td>\n",
       "      <td>2016</td>\n",
       "      <td>m</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>10.0</td>\n",
       "      <td>age</td>\n",
       "      <td>2017</td>\n",
       "      <td>f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>d</td>\n",
       "      <td>64.0</td>\n",
       "      <td>weight</td>\n",
       "      <td>2016</td>\n",
       "      <td>m</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>a</td>\n",
       "      <td>49.0</td>\n",
       "      <td>weight</td>\n",
       "      <td>2017</td>\n",
       "      <td>f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>c</td>\n",
       "      <td>54.0</td>\n",
       "      <td>weight</td>\n",
       "      <td>2017</td>\n",
       "      <td>f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>b</td>\n",
       "      <td>59.0</td>\n",
       "      <td>weight</td>\n",
       "      <td>2017</td>\n",
       "      <td>m</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>d</td>\n",
       "      <td>64.0</td>\n",
       "      <td>weight</td>\n",
       "      <td>2017</td>\n",
       "      <td>m</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  value  column  year sex\n",
       "0   a   10.0     age  2016   f\n",
       "1   c   10.0     age  2016   f\n",
       "2   b   10.0     age  2016   m\n",
       "3   d   10.0     age  2016   m\n",
       "4   a   10.0     age  2017   f\n",
       "11  d   64.0  weight  2016   m\n",
       "12  a   49.0  weight  2017   f\n",
       "13  c   54.0  weight  2017   f\n",
       "14  b   59.0  weight  2017   m\n",
       "15  d   64.0  weight  2017   m"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "melted = melted.dropna().reset_index(drop=True)\n",
    "melted = (melted\n",
    "    # join: index join\n",
    "    .join(melted.variable.str.split(\"_\", expand=True).reset_index(drop=True))\n",
    "    .drop(\"variable\", axis=1)\n",
    "    .rename(columns={0: \"column\", 1: \"year\", 2: \"sex\"})\n",
    ")\n",
    "pd.concat([melted.head(), melted.tail()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:20.186163",
     "start_time": "2017-01-22T04:27:20.140579"
    },
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>age</th>\n",
       "      <th>year</th>\n",
       "      <th>sex</th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>f</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>c</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>f</td>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>m</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2016</td>\n",
       "      <td>m</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2017</td>\n",
       "      <td>f</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>c</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2017</td>\n",
       "      <td>f</td>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>b</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2017</td>\n",
       "      <td>m</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>d</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2017</td>\n",
       "      <td>m</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id   age  year sex  weight\n",
       "0  a  10.0  2016   f    49.0\n",
       "1  c  10.0  2016   f    54.0\n",
       "2  b  10.0  2016   m    59.0\n",
       "3  d  10.0  2016   m    64.0\n",
       "4  a  10.0  2017   f    49.0\n",
       "5  c  10.0  2017   f    54.0\n",
       "6  b  10.0  2017   m    59.0\n",
       "7  d  10.0  2017   m    64.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat(\n",
    "    [\n",
    "        melted[melted.column == \"age\"].drop(\"column\", axis=1).rename(columns={\"value\": \"age\"}),\n",
    "        melted[melted.column == \"weight\"][\"value\"].rename(\"weight\").reset_index(drop=True),\n",
    "    ], axis=1)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ピボットテーブルでの対応\n",
    "- おすすめしない\n",
    "- index/columnsの組合せによってvaluesがユニークにならない場合正しくないため\n",
    "- pivot/unstackではユニークにならないときにErrorとなるため安全\n",
    "- ただし、一番記述しやすい"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:20.407432",
     "start_time": "2017-01-22T04:27:20.342545"
    },
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th colspan=\"2\" halign=\"left\">2016</th>\n",
       "      <th colspan=\"2\" halign=\"left\">2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th>f</th>\n",
       "      <th>m</th>\n",
       "      <th>f</th>\n",
       "      <th>m</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>49.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>54.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>64.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "year  2016        2017      \n",
       "sex      f     m     f     m\n",
       "id                          \n",
       "a     49.0   NaN  49.0   NaN\n",
       "b      NaN  59.0   NaN  59.0\n",
       "c     54.0   NaN  54.0   NaN\n",
       "d      NaN  64.0   NaN  64.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivot_table = df.pivot_table(index=\"id\", columns=[\"year\", \"sex\"], values=\"weight\", aggfunc=np.sum)\n",
    "pivot_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### おまけ: MultiIndexの扱い\n",
    "- MutiIndexを持つDataFrameからのカラム選択\n",
    "    - Index階層が減ることによりIndexに変化する\n",
    "- MultiIndexカラムの追加"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:20.407432",
     "start_time": "2017-01-22T04:27:20.342545"
    },
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>sex</th>\n",
       "      <th>f</th>\n",
       "      <th>m</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "sex    f    m\n",
       "id           \n",
       "a    0.0  NaN\n",
       "b    NaN  0.0\n",
       "c    0.0  NaN\n",
       "d    NaN  0.0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff = pivot_table.loc[:, 2017] - pivot_table.loc[:, 2016]\n",
    "diff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:20.302362",
     "start_time": "2017-01-22T04:27:20.296575"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['f', 'm'], dtype='object', name='sex')"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:20.407432",
     "start_time": "2017-01-22T04:27:20.342545"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">diff</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>f</th>\n",
       "      <th>m</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   diff     \n",
       "      f    m\n",
       "id          \n",
       "a   0.0  NaN\n",
       "b   NaN  0.0\n",
       "c   0.0  NaN\n",
       "d   NaN  0.0"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff.columns = pd.MultiIndex.from_product([(\"diff\",), (\"f\", \"m\")])\n",
    "diff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-01-22T04:27:20.407432",
     "start_time": "2017-01-22T04:27:20.342545"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th colspan=\"2\" halign=\"left\">2016</th>\n",
       "      <th colspan=\"2\" halign=\"left\">2017</th>\n",
       "      <th colspan=\"2\" halign=\"left\">diff</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th>f</th>\n",
       "      <th>m</th>\n",
       "      <th>f</th>\n",
       "      <th>m</th>\n",
       "      <th>f</th>\n",
       "      <th>m</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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>a</th>\n",
       "      <td>49.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>54.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>64.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>64.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "year  2016        2017       diff     \n",
       "sex      f     m     f     m    f    m\n",
       "id                                    \n",
       "a     49.0   NaN  49.0   NaN  0.0  NaN\n",
       "b      NaN  59.0   NaN  59.0  NaN  0.0\n",
       "c     54.0   NaN  54.0   NaN  0.0  NaN\n",
       "d      NaN  64.0   NaN  64.0  NaN  0.0"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([pivot_table, diff], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python [default]",
   "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.5.2"
  },
  "toc": {
   "toc_cell": false,
   "toc_number_sections": true,
   "toc_threshold": 6,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}