{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "# Data Wrangling: Join, Combine, "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "pd.options.display.max_rows = 20\n",
    "np.random.seed(12345)\n",
    "import matplotlib.pyplot as plt\n",
    "plt.rc('figure', figsize=(10, 6))\n",
    "np.set_printoptions(precision=4, suppress=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Hierarchical Indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "data = pd.Series(np.random.randn(9),\n",
    "                 index=[['a', 'a', 'a', 'b', 'b', 'c', 'c', 'd', 'd'],\n",
    "                        [1, 2, 3, 1, 3, 1, 2, 2, 3]])\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "data.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "data['b']\n",
    "data['b':'c']\n",
    "data.loc[['b', 'd']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "data.loc[:, 2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "data.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "data.unstack().stack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "frame = pd.DataFrame(np.arange(12).reshape((4, 3)),\n",
    "                     index=[['a', 'a', 'b', 'b'], [1, 2, 1, 2]],\n",
    "                     columns=[['Ohio', 'Ohio', 'Colorado'],\n",
    "                              ['Green', 'Red', 'Green']])\n",
    "frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "frame.index.names = ['key1', 'key2']\n",
    "frame.columns.names = ['state', 'color']\n",
    "frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "frame['Ohio']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "MultiIndex.from_arrays([['Ohio', 'Ohio', 'Colorado'], ['Green', 'Red', 'Green']],\n",
    "                       names=['state', 'color'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Reordering and Sorting Levels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "frame.swaplevel('key1', 'key2')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "frame.sort_index(level=1)\n",
    "frame.swaplevel(0, 1).sort_index(level=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Summary Statistics by Level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "frame.sum(level='key2')\n",
    "frame.sum(level='color', axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Indexing with a DataFrame's columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "frame = pd.DataFrame({'a': range(7), 'b': range(7, 0, -1),\n",
    "                      'c': ['one', 'one', 'one', 'two', 'two',\n",
    "                            'two', 'two'],\n",
    "                      'd': [0, 1, 2, 0, 1, 2, 3]})\n",
    "frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "frame2 = frame.set_index(['c', 'd'])\n",
    "frame2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "frame.set_index(['c', 'd'], drop=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "frame2.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Combining and Merging Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Database-Style DataFrame Joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],\n",
    "                    'data1': range(7)})\n",
    "df2 = pd.DataFrame({'key': ['a', 'b', 'd'],\n",
    "                    'data2': range(3)})\n",
    "df1\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.merge(df1, df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.merge(df1, df2, on='key')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df3 = pd.DataFrame({'lkey': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],\n",
    "                    'data1': range(7)})\n",
    "df4 = pd.DataFrame({'rkey': ['a', 'b', 'd'],\n",
    "                    'data2': range(3)})\n",
    "pd.merge(df3, df4, left_on='lkey', right_on='rkey')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.merge(df1, df2, how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'b'],\n",
    "                    'data1': range(6)})\n",
    "df2 = pd.DataFrame({'key': ['a', 'b', 'a', 'b', 'd'],\n",
    "                    'data2': range(5)})\n",
    "df1\n",
    "df2\n",
    "pd.merge(df1, df2, on='key', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.merge(df1, df2, how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "left = pd.DataFrame({'key1': ['foo', 'foo', 'bar'],\n",
    "                     'key2': ['one', 'two', 'one'],\n",
    "                     'lval': [1, 2, 3]})\n",
    "right = pd.DataFrame({'key1': ['foo', 'foo', 'bar', 'bar'],\n",
    "                      'key2': ['one', 'one', 'one', 'two'],\n",
    "                      'rval': [4, 5, 6, 7]})\n",
    "pd.merge(left, right, on=['key1', 'key2'], how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.merge(left, right, on='key1')\n",
    "pd.merge(left, right, on='key1', suffixes=('_left', '_right'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Merging on Index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "left1 = pd.DataFrame({'key': ['a', 'b', 'a', 'a', 'b', 'c'],\n",
    "                      'value': range(6)})\n",
    "right1 = pd.DataFrame({'group_val': [3.5, 7]}, index=['a', 'b'])\n",
    "left1\n",
    "right1\n",
    "pd.merge(left1, right1, left_on='key', right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.merge(left1, right1, left_on='key', right_index=True, how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "lefth = pd.DataFrame({'key1': ['Ohio', 'Ohio', 'Ohio',\n",
    "                               'Nevada', 'Nevada'],\n",
    "                      'key2': [2000, 2001, 2002, 2001, 2002],\n",
    "                      'data': np.arange(5.)})\n",
    "righth = pd.DataFrame(np.arange(12).reshape((6, 2)),\n",
    "                      index=[['Nevada', 'Nevada', 'Ohio', 'Ohio',\n",
    "                              'Ohio', 'Ohio'],\n",
    "                             [2001, 2000, 2000, 2000, 2001, 2002]],\n",
    "                      columns=['event1', 'event2'])\n",
    "lefth\n",
    "righth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.merge(lefth, righth, left_on=['key1', 'key2'], right_index=True)\n",
    "pd.merge(lefth, righth, left_on=['key1', 'key2'],\n",
    "         right_index=True, how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "left2 = pd.DataFrame([[1., 2.], [3., 4.], [5., 6.]],\n",
    "                     index=['a', 'c', 'e'],\n",
    "                     columns=['Ohio', 'Nevada'])\n",
    "right2 = pd.DataFrame([[7., 8.], [9., 10.], [11., 12.], [13, 14]],\n",
    "                      index=['b', 'c', 'd', 'e'],\n",
    "                      columns=['Missouri', 'Alabama'])\n",
    "left2\n",
    "right2\n",
    "pd.merge(left2, right2, how='outer', left_index=True, right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "left2.join(right2, how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "left1.join(right1, on='key')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "another = pd.DataFrame([[7., 8.], [9., 10.], [11., 12.], [16., 17.]],\n",
    "                       index=['a', 'c', 'e', 'f'],\n",
    "                       columns=['New York', 'Oregon'])\n",
    "another\n",
    "left2.join([right2, another])\n",
    "left2.join([right2, another], how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Concatenating Along an Axis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "arr = np.arange(12).reshape((3, 4))\n",
    "arr\n",
    "np.concatenate([arr, arr], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "s1 = pd.Series([0, 1], index=['a', 'b'])\n",
    "s2 = pd.Series([2, 3, 4], index=['c', 'd', 'e'])\n",
    "s3 = pd.Series([5, 6], index=['f', 'g'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.concat([s1, s2, s3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.concat([s1, s2, s3], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "s4 = pd.concat([s1, s3])\n",
    "s4\n",
    "pd.concat([s1, s4], axis=1)\n",
    "pd.concat([s1, s4], axis=1, join='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.concat([s1, s4], axis=1, join_axes=[['a', 'c', 'b', 'e']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "result = pd.concat([s1, s1, s3], keys=['one', 'two', 'three'])\n",
    "result\n",
    "result.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.concat([s1, s2, s3], axis=1, keys=['one', 'two', 'three'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame(np.arange(6).reshape(3, 2), index=['a', 'b', 'c'],\n",
    "                   columns=['one', 'two'])\n",
    "df2 = pd.DataFrame(5 + np.arange(4).reshape(2, 2), index=['a', 'c'],\n",
    "                   columns=['three', 'four'])\n",
    "df1\n",
    "df2\n",
    "pd.concat([df1, df2], axis=1, keys=['level1', 'level2'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.concat({'level1': df1, 'level2': df2}, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.concat([df1, df2], axis=1, keys=['level1', 'level2'],\n",
    "          names=['upper', 'lower'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame(np.random.randn(3, 4), columns=['a', 'b', 'c', 'd'])\n",
    "df2 = pd.DataFrame(np.random.randn(2, 3), columns=['b', 'd', 'a'])\n",
    "df1\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.concat([df1, df2], ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Combining Data with Overlap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "a = pd.Series([np.nan, 2.5, np.nan, 3.5, 4.5, np.nan],\n",
    "              index=['f', 'e', 'd', 'c', 'b', 'a'])\n",
    "b = pd.Series(np.arange(len(a), dtype=np.float64),\n",
    "              index=['f', 'e', 'd', 'c', 'b', 'a'])\n",
    "b[-1] = np.nan\n",
    "a\n",
    "b\n",
    "np.where(pd.isnull(a), b, a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "b[:-2].combine_first(a[2:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({'a': [1., np.nan, 5., np.nan],\n",
    "                    'b': [np.nan, 2., np.nan, 6.],\n",
    "                    'c': range(2, 18, 4)})\n",
    "df2 = pd.DataFrame({'a': [5., 4., np.nan, 3., 7.],\n",
    "                    'b': [np.nan, 3., 4., 6., 8.]})\n",
    "df1\n",
    "df2\n",
    "df1.combine_first(df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Reshaping and Pivoting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Reshaping with Hierarchical Indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "data = pd.DataFrame(np.arange(6).reshape((2, 3)),\n",
    "                    index=pd.Index(['Ohio', 'Colorado'], name='state'),\n",
    "                    columns=pd.Index(['one', 'two', 'three'],\n",
    "                    name='number'))\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "result = data.stack()\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "result.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "result.unstack(0)\n",
    "result.unstack('state')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "s1 = pd.Series([0, 1, 2, 3], index=['a', 'b', 'c', 'd'])\n",
    "s2 = pd.Series([4, 5, 6], index=['c', 'd', 'e'])\n",
    "data2 = pd.concat([s1, s2], keys=['one', 'two'])\n",
    "data2\n",
    "data2.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "data2.unstack()\n",
    "data2.unstack().stack()\n",
    "data2.unstack().stack(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'left': result, 'right': result + 5},\n",
    "                  columns=pd.Index(['left', 'right'], name='side'))\n",
    "df\n",
    "df.unstack('state')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df.unstack('state').stack('side')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Pivoting “Long” to “Wide” Format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "data = pd.read_csv('examples/macrodata.csv')\n",
    "data.head()\n",
    "periods = pd.PeriodIndex(year=data.year, quarter=data.quarter,\n",
    "                         name='date')\n",
    "columns = pd.Index(['realgdp', 'infl', 'unemp'], name='item')\n",
    "data = data.reindex(columns=columns)\n",
    "data.index = periods.to_timestamp('D', 'end')\n",
    "ldata = data.stack().reset_index().rename(columns={0: 'value'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "ldata[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pivoted = ldata.pivot('date', 'item', 'value')\n",
    "pivoted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "ldata['value2'] = np.random.randn(len(ldata))\n",
    "ldata[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pivoted = ldata.pivot('date', 'item')\n",
    "pivoted[:5]\n",
    "pivoted['value'][:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "unstacked = ldata.set_index(['date', 'item']).unstack('item')\n",
    "unstacked[:7]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Pivoting “Wide” to “Long” Format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'key': ['foo', 'bar', 'baz'],\n",
    "                   'A': [1, 2, 3],\n",
    "                   'B': [4, 5, 6],\n",
    "                   'C': [7, 8, 9]})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "melted = pd.melt(df, ['key'])\n",
    "melted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "reshaped = melted.pivot('key', 'variable', 'value')\n",
    "reshaped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "reshaped.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.melt(df, id_vars=['key'], value_vars=['A', 'B'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.melt(df, value_vars=['A', 'B', 'C'])\n",
    "pd.melt(df, value_vars=['key', 'A', 'B'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Conclusion"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}