09_reshaping.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to DataFrames\n",
    "**[Bogumił Kamiński](http://bogumilkaminski.pl/about/), May 6, 2020**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "using DataFrames # load package"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reshaping DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Wide to long"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>id2</th><th>M1</th><th>M2</th></tr><tr><th></th><th>Int64</th><th>Int64</th><th>Int64</th><th>Int64</th></tr></thead><tbody><p>4 rows × 4 columns</p><tr><th>1</th><td>1</td><td>1</td><td>11</td><td>111</td></tr><tr><th>2</th><td>2</td><td>1</td><td>12</td><td>112</td></tr><tr><th>3</th><td>3</td><td>2</td><td>13</td><td>113</td></tr><tr><th>4</th><td>4</td><td>2</td><td>14</td><td>114</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& id & id2 & M1 & M2\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & 11 & 111 \\\\\n",
       "\t2 & 2 & 1 & 12 & 112 \\\\\n",
       "\t3 & 3 & 2 & 13 & 113 \\\\\n",
       "\t4 & 4 & 2 & 14 & 114 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "4×4 DataFrame\n",
       "│ Row │ id    │ id2   │ M1    │ M2    │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │\n",
       "├─────┼───────┼───────┼───────┼───────┤\n",
       "│ 1   │ 1     │ 1     │ 11    │ 111   │\n",
       "│ 2   │ 2     │ 1     │ 12    │ 112   │\n",
       "│ 3   │ 3     │ 2     │ 13    │ 113   │\n",
       "│ 4   │ 4     │ 2     │ 14    │ 114   │"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = DataFrame(id=[1,2,3,4], id2=[1,1,2,2], M1=[11,12,13,14], M2=[111,112,113,114])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>variable</th><th>value</th></tr><tr><th></th><th>Int64</th><th>Cat…</th><th>Int64</th></tr></thead><tbody><p>8 rows × 3 columns</p><tr><th>1</th><td>1</td><td>M1</td><td>11</td></tr><tr><th>2</th><td>2</td><td>M1</td><td>12</td></tr><tr><th>3</th><td>3</td><td>M1</td><td>13</td></tr><tr><th>4</th><td>4</td><td>M1</td><td>14</td></tr><tr><th>5</th><td>1</td><td>M2</td><td>111</td></tr><tr><th>6</th><td>2</td><td>M2</td><td>112</td></tr><tr><th>7</th><td>3</td><td>M2</td><td>113</td></tr><tr><th>8</th><td>4</td><td>M2</td><td>114</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & variable & value\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Cat… & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & M1 & 11 \\\\\n",
       "\t2 & 2 & M1 & 12 \\\\\n",
       "\t3 & 3 & M1 & 13 \\\\\n",
       "\t4 & 4 & M1 & 14 \\\\\n",
       "\t5 & 1 & M2 & 111 \\\\\n",
       "\t6 & 2 & M2 & 112 \\\\\n",
       "\t7 & 3 & M2 & 113 \\\\\n",
       "\t8 & 4 & M2 & 114 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "8×3 DataFrame\n",
       "│ Row │ id    │ variable │ value │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mCat…\u001b[39m     │ \u001b[90mInt64\u001b[39m │\n",
       "├─────┼───────┼──────────┼───────┤\n",
       "│ 1   │ 1     │ M1       │ 11    │\n",
       "│ 2   │ 2     │ M1       │ 12    │\n",
       "│ 3   │ 3     │ M1       │ 13    │\n",
       "│ 4   │ 4     │ M1       │ 14    │\n",
       "│ 5   │ 1     │ M2       │ 111   │\n",
       "│ 6   │ 2     │ M2       │ 112   │\n",
       "│ 7   │ 3     │ M2       │ 113   │\n",
       "│ 8   │ 4     │ M2       │ 114   │"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stack(x, [:M1, :M2], :id) # first pass measure variables and then id-variable"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "add `view=true` keyword argument to make a view; in that case columns of the resulting data frame share memory with columns of the source data frame, so the operation is potentially unsafe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>key</th><th>observed</th></tr><tr><th></th><th>Int64</th><th>Cat…</th><th>Int64</th></tr></thead><tbody><p>8 rows × 3 columns</p><tr><th>1</th><td>1</td><td>M1</td><td>11</td></tr><tr><th>2</th><td>2</td><td>M1</td><td>12</td></tr><tr><th>3</th><td>3</td><td>M1</td><td>13</td></tr><tr><th>4</th><td>4</td><td>M1</td><td>14</td></tr><tr><th>5</th><td>1</td><td>M2</td><td>111</td></tr><tr><th>6</th><td>2</td><td>M2</td><td>112</td></tr><tr><th>7</th><td>3</td><td>M2</td><td>113</td></tr><tr><th>8</th><td>4</td><td>M2</td><td>114</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & key & observed\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Cat… & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & M1 & 11 \\\\\n",
       "\t2 & 2 & M1 & 12 \\\\\n",
       "\t3 & 3 & M1 & 13 \\\\\n",
       "\t4 & 4 & M1 & 14 \\\\\n",
       "\t5 & 1 & M2 & 111 \\\\\n",
       "\t6 & 2 & M2 & 112 \\\\\n",
       "\t7 & 3 & M2 & 113 \\\\\n",
       "\t8 & 4 & M2 & 114 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "8×3 DataFrame\n",
       "│ Row │ id    │ key  │ observed │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mCat…\u001b[39m │ \u001b[90mInt64\u001b[39m    │\n",
       "├─────┼───────┼──────┼──────────┤\n",
       "│ 1   │ 1     │ M1   │ 11       │\n",
       "│ 2   │ 2     │ M1   │ 12       │\n",
       "│ 3   │ 3     │ M1   │ 13       │\n",
       "│ 4   │ 4     │ M1   │ 14       │\n",
       "│ 5   │ 1     │ M2   │ 111      │\n",
       "│ 6   │ 2     │ M2   │ 112      │\n",
       "│ 7   │ 3     │ M2   │ 113      │\n",
       "│ 8   │ 4     │ M2   │ 114      │"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# optionally you can rename columns\n",
    "stack(x, [\"M1\", \"M2\"], \"id\", variable_name=\"key\", value_name=\"observed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "if second argument is omitted in `stack` , all other columns are assumed to be the id-variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>id2</th><th>variable</th><th>value</th></tr><tr><th></th><th>Int64</th><th>Int64</th><th>Cat…</th><th>Int64</th></tr></thead><tbody><p>8 rows × 4 columns</p><tr><th>1</th><td>1</td><td>1</td><td>M1</td><td>11</td></tr><tr><th>2</th><td>2</td><td>1</td><td>M1</td><td>12</td></tr><tr><th>3</th><td>3</td><td>2</td><td>M1</td><td>13</td></tr><tr><th>4</th><td>4</td><td>2</td><td>M1</td><td>14</td></tr><tr><th>5</th><td>1</td><td>1</td><td>M2</td><td>111</td></tr><tr><th>6</th><td>2</td><td>1</td><td>M2</td><td>112</td></tr><tr><th>7</th><td>3</td><td>2</td><td>M2</td><td>113</td></tr><tr><th>8</th><td>4</td><td>2</td><td>M2</td><td>114</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& id & id2 & variable & value\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Cat… & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & M1 & 11 \\\\\n",
       "\t2 & 2 & 1 & M1 & 12 \\\\\n",
       "\t3 & 3 & 2 & M1 & 13 \\\\\n",
       "\t4 & 4 & 2 & M1 & 14 \\\\\n",
       "\t5 & 1 & 1 & M2 & 111 \\\\\n",
       "\t6 & 2 & 1 & M2 & 112 \\\\\n",
       "\t7 & 3 & 2 & M2 & 113 \\\\\n",
       "\t8 & 4 & 2 & M2 & 114 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "8×4 DataFrame\n",
       "│ Row │ id    │ id2   │ variable │ value │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │ \u001b[90mCat…\u001b[39m     │ \u001b[90mInt64\u001b[39m │\n",
       "├─────┼───────┼───────┼──────────┼───────┤\n",
       "│ 1   │ 1     │ 1     │ M1       │ 11    │\n",
       "│ 2   │ 2     │ 1     │ M1       │ 12    │\n",
       "│ 3   │ 3     │ 2     │ M1       │ 13    │\n",
       "│ 4   │ 4     │ 2     │ M1       │ 14    │\n",
       "│ 5   │ 1     │ 1     │ M2       │ 111   │\n",
       "│ 6   │ 2     │ 1     │ M2       │ 112   │\n",
       "│ 7   │ 3     │ 2     │ M2       │ 113   │\n",
       "│ 8   │ 4     │ 2     │ M2       │ 114   │"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stack(x, Not([:id, :id2]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>id2</th><th>variable</th><th>value</th></tr><tr><th></th><th>Int64</th><th>Int64</th><th>Cat…</th><th>Int64</th></tr></thead><tbody><p>8 rows × 4 columns</p><tr><th>1</th><td>1</td><td>1</td><td>M1</td><td>11</td></tr><tr><th>2</th><td>2</td><td>1</td><td>M1</td><td>12</td></tr><tr><th>3</th><td>3</td><td>2</td><td>M1</td><td>13</td></tr><tr><th>4</th><td>4</td><td>2</td><td>M1</td><td>14</td></tr><tr><th>5</th><td>1</td><td>1</td><td>M2</td><td>111</td></tr><tr><th>6</th><td>2</td><td>1</td><td>M2</td><td>112</td></tr><tr><th>7</th><td>3</td><td>2</td><td>M2</td><td>113</td></tr><tr><th>8</th><td>4</td><td>2</td><td>M2</td><td>114</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& id & id2 & variable & value\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Cat… & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & M1 & 11 \\\\\n",
       "\t2 & 2 & 1 & M1 & 12 \\\\\n",
       "\t3 & 3 & 2 & M1 & 13 \\\\\n",
       "\t4 & 4 & 2 & M1 & 14 \\\\\n",
       "\t5 & 1 & 1 & M2 & 111 \\\\\n",
       "\t6 & 2 & 1 & M2 & 112 \\\\\n",
       "\t7 & 3 & 2 & M2 & 113 \\\\\n",
       "\t8 & 4 & 2 & M2 & 114 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "8×4 DataFrame\n",
       "│ Row │ id    │ id2   │ variable │ value │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │ \u001b[90mCat…\u001b[39m     │ \u001b[90mInt64\u001b[39m │\n",
       "├─────┼───────┼───────┼──────────┼───────┤\n",
       "│ 1   │ 1     │ 1     │ M1       │ 11    │\n",
       "│ 2   │ 2     │ 1     │ M1       │ 12    │\n",
       "│ 3   │ 3     │ 2     │ M1       │ 13    │\n",
       "│ 4   │ 4     │ 2     │ M1       │ 14    │\n",
       "│ 5   │ 1     │ 1     │ M2       │ 111   │\n",
       "│ 6   │ 2     │ 1     │ M2       │ 112   │\n",
       "│ 7   │ 3     │ 2     │ M2       │ 113   │\n",
       "│ 8   │ 4     │ 2     │ M2       │ 114   │"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stack(x, Not([1, 2])) # you can use index instead of symbol"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>id2</th><th>a1</th><th>a2</th></tr><tr><th></th><th>Int64</th><th>Char</th><th>Float64</th><th>Float64</th></tr></thead><tbody><p>3 rows × 4 columns</p><tr><th>1</th><td>1</td><td>'a'</td><td>0.0291231</td><td>0.0856714</td></tr><tr><th>2</th><td>1</td><td>'b'</td><td>0.00764146</td><td>0.488177</td></tr><tr><th>3</th><td>1</td><td>'c'</td><td>0.844594</td><td>0.93089</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& id & id2 & a1 & a2\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Char & Float64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 'a' & 0.0291231 & 0.0856714 \\\\\n",
       "\t2 & 1 & 'b' & 0.00764146 & 0.488177 \\\\\n",
       "\t3 & 1 & 'c' & 0.844594 & 0.93089 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "3×4 DataFrame\n",
       "│ Row │ id    │ id2  │ a1         │ a2        │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mChar\u001b[39m │ \u001b[90mFloat64\u001b[39m    │ \u001b[90mFloat64\u001b[39m   │\n",
       "├─────┼───────┼──────┼────────────┼───────────┤\n",
       "│ 1   │ 1     │ 'a'  │ 0.0291231  │ 0.0856714 │\n",
       "│ 2   │ 1     │ 'b'  │ 0.00764146 │ 0.488177  │\n",
       "│ 3   │ 1     │ 'c'  │ 0.844594   │ 0.93089   │"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = DataFrame(id = [1,1,1], id2=['a','b','c'], a1 = rand(3), a2 = rand(3))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " if `stack` is not passed any measure variables by default numeric variables are selected as measures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>id2</th><th>variable</th><th>value</th></tr><tr><th></th><th>Int64</th><th>Char</th><th>Cat…</th><th>Float64</th></tr></thead><tbody><p>6 rows × 4 columns</p><tr><th>1</th><td>1</td><td>'a'</td><td>a1</td><td>0.0291231</td></tr><tr><th>2</th><td>1</td><td>'b'</td><td>a1</td><td>0.00764146</td></tr><tr><th>3</th><td>1</td><td>'c'</td><td>a1</td><td>0.844594</td></tr><tr><th>4</th><td>1</td><td>'a'</td><td>a2</td><td>0.0856714</td></tr><tr><th>5</th><td>1</td><td>'b'</td><td>a2</td><td>0.488177</td></tr><tr><th>6</th><td>1</td><td>'c'</td><td>a2</td><td>0.93089</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& id & id2 & variable & value\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Char & Cat… & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 'a' & a1 & 0.0291231 \\\\\n",
       "\t2 & 1 & 'b' & a1 & 0.00764146 \\\\\n",
       "\t3 & 1 & 'c' & a1 & 0.844594 \\\\\n",
       "\t4 & 1 & 'a' & a2 & 0.0856714 \\\\\n",
       "\t5 & 1 & 'b' & a2 & 0.488177 \\\\\n",
       "\t6 & 1 & 'c' & a2 & 0.93089 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "6×4 DataFrame\n",
       "│ Row │ id    │ id2  │ variable │ value      │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mChar\u001b[39m │ \u001b[90mCat…\u001b[39m     │ \u001b[90mFloat64\u001b[39m    │\n",
       "├─────┼───────┼──────┼──────────┼────────────┤\n",
       "│ 1   │ 1     │ 'a'  │ a1       │ 0.0291231  │\n",
       "│ 2   │ 1     │ 'b'  │ a1       │ 0.00764146 │\n",
       "│ 3   │ 1     │ 'c'  │ a1       │ 0.844594   │\n",
       "│ 4   │ 1     │ 'a'  │ a2       │ 0.0856714  │\n",
       "│ 5   │ 1     │ 'b'  │ a2       │ 0.488177   │\n",
       "│ 6   │ 1     │ 'c'  │ a2       │ 0.93089    │"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stack(x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "here all columns are treated as measures:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>variable</th><th>value</th></tr><tr><th></th><th>Cat…</th><th>Float64</th></tr></thead><tbody><p>6 rows × 2 columns</p><tr><th>1</th><td>x1</td><td>0.893946</td></tr><tr><th>2</th><td>x1</td><td>0.604032</td></tr><tr><th>3</th><td>x1</td><td>0.0258379</td></tr><tr><th>4</th><td>x2</td><td>0.85813</td></tr><tr><th>5</th><td>x2</td><td>0.804899</td></tr><tr><th>6</th><td>x2</td><td>0.651169</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& variable & value\\\\\n",
       "\t\\hline\n",
       "\t& Cat… & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & x1 & 0.893946 \\\\\n",
       "\t2 & x1 & 0.604032 \\\\\n",
       "\t3 & x1 & 0.0258379 \\\\\n",
       "\t4 & x2 & 0.85813 \\\\\n",
       "\t5 & x2 & 0.804899 \\\\\n",
       "\t6 & x2 & 0.651169 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "6×2 DataFrame\n",
       "│ Row │ variable │ value     │\n",
       "│     │ \u001b[90mCat…\u001b[39m     │ \u001b[90mFloat64\u001b[39m   │\n",
       "├─────┼──────────┼───────────┤\n",
       "│ 1   │ x1       │ 0.893946  │\n",
       "│ 2   │ x1       │ 0.604032  │\n",
       "│ 3   │ x1       │ 0.0258379 │\n",
       "│ 4   │ x2       │ 0.85813   │\n",
       "│ 5   │ x2       │ 0.804899  │\n",
       "│ 6   │ x2       │ 0.651169  │"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stack(DataFrame(rand(3,2)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>key</th><th>variable</th><th>value</th></tr><tr><th></th><th>Int64</th><th>Cat…</th><th>Float64</th></tr></thead><tbody><p>6 rows × 3 columns</p><tr><th>1</th><td>1</td><td>x1</td><td>0.6063</td></tr><tr><th>2</th><td>1</td><td>x1</td><td>0.868893</td></tr><tr><th>3</th><td>1</td><td>x1</td><td>0.60774</td></tr><tr><th>4</th><td>1</td><td>x2</td><td>0.36719</td></tr><tr><th>5</th><td>1</td><td>x2</td><td>0.355047</td></tr><tr><th>6</th><td>1</td><td>x2</td><td>0.517009</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& key & variable & value\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Cat… & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & x1 & 0.6063 \\\\\n",
       "\t2 & 1 & x1 & 0.868893 \\\\\n",
       "\t3 & 1 & x1 & 0.60774 \\\\\n",
       "\t4 & 1 & x2 & 0.36719 \\\\\n",
       "\t5 & 1 & x2 & 0.355047 \\\\\n",
       "\t6 & 1 & x2 & 0.517009 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "6×3 DataFrame\n",
       "│ Row │ key   │ variable │ value    │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mCat…\u001b[39m     │ \u001b[90mFloat64\u001b[39m  │\n",
       "├─────┼───────┼──────────┼──────────┤\n",
       "│ 1   │ 1     │ x1       │ 0.6063   │\n",
       "│ 2   │ 1     │ x1       │ 0.868893 │\n",
       "│ 3   │ 1     │ x1       │ 0.60774  │\n",
       "│ 4   │ 1     │ x2       │ 0.36719  │\n",
       "│ 5   │ 1     │ x2       │ 0.355047 │\n",
       "│ 6   │ 1     │ x2       │ 0.517009 │"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = DataFrame(rand(3,2))\n",
    "df.key = [1,1,1]\n",
    "mdf = stack(df) # duplicates in key are silently accepted"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Long to wide"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>id2</th><th>a1</th><th>a2</th></tr><tr><th></th><th>Int64</th><th>Char</th><th>Float64</th><th>Float64</th></tr></thead><tbody><p>3 rows × 4 columns</p><tr><th>1</th><td>1</td><td>'a'</td><td>0.967125</td><td>0.56558</td></tr><tr><th>2</th><td>1</td><td>'b'</td><td>0.871342</td><td>0.468136</td></tr><tr><th>3</th><td>1</td><td>'c'</td><td>0.60536</td><td>0.114597</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& id & id2 & a1 & a2\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Char & Float64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 'a' & 0.967125 & 0.56558 \\\\\n",
       "\t2 & 1 & 'b' & 0.871342 & 0.468136 \\\\\n",
       "\t3 & 1 & 'c' & 0.60536 & 0.114597 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "3×4 DataFrame\n",
       "│ Row │ id    │ id2  │ a1       │ a2       │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mChar\u001b[39m │ \u001b[90mFloat64\u001b[39m  │ \u001b[90mFloat64\u001b[39m  │\n",
       "├─────┼───────┼──────┼──────────┼──────────┤\n",
       "│ 1   │ 1     │ 'a'  │ 0.967125 │ 0.56558  │\n",
       "│ 2   │ 1     │ 'b'  │ 0.871342 │ 0.468136 │\n",
       "│ 3   │ 1     │ 'c'  │ 0.60536  │ 0.114597 │"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = DataFrame(id = [1,1,1], id2=['a','b','c'], a1 = rand(3), a2 = rand(3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>id2</th><th>variable</th><th>value</th></tr><tr><th></th><th>Int64</th><th>Char</th><th>Cat…</th><th>Float64</th></tr></thead><tbody><p>6 rows × 4 columns</p><tr><th>1</th><td>1</td><td>'a'</td><td>a1</td><td>0.967125</td></tr><tr><th>2</th><td>1</td><td>'b'</td><td>a1</td><td>0.871342</td></tr><tr><th>3</th><td>1</td><td>'c'</td><td>a1</td><td>0.60536</td></tr><tr><th>4</th><td>1</td><td>'a'</td><td>a2</td><td>0.56558</td></tr><tr><th>5</th><td>1</td><td>'b'</td><td>a2</td><td>0.468136</td></tr><tr><th>6</th><td>1</td><td>'c'</td><td>a2</td><td>0.114597</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& id & id2 & variable & value\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Char & Cat… & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 'a' & a1 & 0.967125 \\\\\n",
       "\t2 & 1 & 'b' & a1 & 0.871342 \\\\\n",
       "\t3 & 1 & 'c' & a1 & 0.60536 \\\\\n",
       "\t4 & 1 & 'a' & a2 & 0.56558 \\\\\n",
       "\t5 & 1 & 'b' & a2 & 0.468136 \\\\\n",
       "\t6 & 1 & 'c' & a2 & 0.114597 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "6×4 DataFrame\n",
       "│ Row │ id    │ id2  │ variable │ value    │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mChar\u001b[39m │ \u001b[90mCat…\u001b[39m     │ \u001b[90mFloat64\u001b[39m  │\n",
       "├─────┼───────┼──────┼──────────┼──────────┤\n",
       "│ 1   │ 1     │ 'a'  │ a1       │ 0.967125 │\n",
       "│ 2   │ 1     │ 'b'  │ a1       │ 0.871342 │\n",
       "│ 3   │ 1     │ 'c'  │ a1       │ 0.60536  │\n",
       "│ 4   │ 1     │ 'a'  │ a2       │ 0.56558  │\n",
       "│ 5   │ 1     │ 'b'  │ a2       │ 0.468136 │\n",
       "│ 6   │ 1     │ 'c'  │ a2       │ 0.114597 │"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y = stack(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id2</th><th>a1</th><th>a2</th></tr><tr><th></th><th>Char</th><th>Float64?</th><th>Float64?</th></tr></thead><tbody><p>3 rows × 3 columns</p><tr><th>1</th><td>'a'</td><td>0.967125</td><td>0.56558</td></tr><tr><th>2</th><td>'b'</td><td>0.871342</td><td>0.468136</td></tr><tr><th>3</th><td>'c'</td><td>0.60536</td><td>0.114597</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id2 & a1 & a2\\\\\n",
       "\t\\hline\n",
       "\t& Char & Float64? & Float64?\\\\\n",
       "\t\\hline\n",
       "\t1 & 'a' & 0.967125 & 0.56558 \\\\\n",
       "\t2 & 'b' & 0.871342 & 0.468136 \\\\\n",
       "\t3 & 'c' & 0.60536 & 0.114597 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "3×3 DataFrame\n",
       "│ Row │ id2  │ a1       │ a2       │\n",
       "│     │ \u001b[90mChar\u001b[39m │ \u001b[90mFloat64?\u001b[39m │ \u001b[90mFloat64?\u001b[39m │\n",
       "├─────┼──────┼──────────┼──────────┤\n",
       "│ 1   │ 'a'  │ 0.967125 │ 0.56558  │\n",
       "│ 2   │ 'b'  │ 0.871342 │ 0.468136 │\n",
       "│ 3   │ 'c'  │ 0.60536  │ 0.114597 │"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unstack(y, :id2, :variable, :value) # stndard unstack with a specified key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>id2</th><th>a1</th><th>a2</th></tr><tr><th></th><th>Int64</th><th>Char</th><th>Float64?</th><th>Float64?</th></tr></thead><tbody><p>3 rows × 4 columns</p><tr><th>1</th><td>1</td><td>'a'</td><td>0.967125</td><td>0.56558</td></tr><tr><th>2</th><td>1</td><td>'b'</td><td>0.871342</td><td>0.468136</td></tr><tr><th>3</th><td>1</td><td>'c'</td><td>0.60536</td><td>0.114597</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& id & id2 & a1 & a2\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Char & Float64? & Float64?\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 'a' & 0.967125 & 0.56558 \\\\\n",
       "\t2 & 1 & 'b' & 0.871342 & 0.468136 \\\\\n",
       "\t3 & 1 & 'c' & 0.60536 & 0.114597 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "3×4 DataFrame\n",
       "│ Row │ id    │ id2  │ a1       │ a2       │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mChar\u001b[39m │ \u001b[90mFloat64?\u001b[39m │ \u001b[90mFloat64?\u001b[39m │\n",
       "├─────┼───────┼──────┼──────────┼──────────┤\n",
       "│ 1   │ 1     │ 'a'  │ 0.967125 │ 0.56558  │\n",
       "│ 2   │ 1     │ 'b'  │ 0.871342 │ 0.468136 │\n",
       "│ 3   │ 1     │ 'c'  │ 0.60536  │ 0.114597 │"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unstack(y, :variable, :value) # all other columns are treated as keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>id2</th><th>a1</th><th>a2</th></tr><tr><th></th><th>Int64</th><th>Char</th><th>Float64?</th><th>Float64?</th></tr></thead><tbody><p>3 rows × 4 columns</p><tr><th>1</th><td>1</td><td>'a'</td><td>0.967125</td><td>0.56558</td></tr><tr><th>2</th><td>1</td><td>'b'</td><td>0.871342</td><td>0.468136</td></tr><tr><th>3</th><td>1</td><td>'c'</td><td>0.60536</td><td>0.114597</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& id & id2 & a1 & a2\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Char & Float64? & Float64?\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 'a' & 0.967125 & 0.56558 \\\\\n",
       "\t2 & 1 & 'b' & 0.871342 & 0.468136 \\\\\n",
       "\t3 & 1 & 'c' & 0.60536 & 0.114597 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "3×4 DataFrame\n",
       "│ Row │ id    │ id2  │ a1       │ a2       │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mChar\u001b[39m │ \u001b[90mFloat64?\u001b[39m │ \u001b[90mFloat64?\u001b[39m │\n",
       "├─────┼───────┼──────┼──────────┼──────────┤\n",
       "│ 1   │ 1     │ 'a'  │ 0.967125 │ 0.56558  │\n",
       "│ 2   │ 1     │ 'b'  │ 0.871342 │ 0.468136 │\n",
       "│ 3   │ 1     │ 'c'  │ 0.60536  │ 0.114597 │"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# all columns other than named :variable and :value are treated as keys\n",
    "unstack(y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>id2</th><th>unstacked_a1</th><th>unstacked_a2</th></tr><tr><th></th><th>Int64</th><th>Char</th><th>Float64?</th><th>Float64?</th></tr></thead><tbody><p>3 rows × 4 columns</p><tr><th>1</th><td>1</td><td>'a'</td><td>0.967125</td><td>0.56558</td></tr><tr><th>2</th><td>1</td><td>'b'</td><td>0.871342</td><td>0.468136</td></tr><tr><th>3</th><td>1</td><td>'c'</td><td>0.60536</td><td>0.114597</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& id & id2 & unstacked\\_a1 & unstacked\\_a2\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Char & Float64? & Float64?\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 'a' & 0.967125 & 0.56558 \\\\\n",
       "\t2 & 1 & 'b' & 0.871342 & 0.468136 \\\\\n",
       "\t3 & 1 & 'c' & 0.60536 & 0.114597 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "3×4 DataFrame\n",
       "│ Row │ id    │ id2  │ unstacked_a1 │ unstacked_a2 │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mChar\u001b[39m │ \u001b[90mFloat64?\u001b[39m     │ \u001b[90mFloat64?\u001b[39m     │\n",
       "├─────┼───────┼──────┼──────────────┼──────────────┤\n",
       "│ 1   │ 1     │ 'a'  │ 0.967125     │ 0.56558      │\n",
       "│ 2   │ 1     │ 'b'  │ 0.871342     │ 0.468136     │\n",
       "│ 3   │ 1     │ 'c'  │ 0.60536      │ 0.114597     │"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# you can rename the unstacked columns\n",
    "unstack(y, renamecols=n->string(\"unstacked_\", n))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>variable</th><th>value</th></tr><tr><th></th><th>Cat…</th><th>Float64</th></tr></thead><tbody><p>6 rows × 2 columns</p><tr><th>1</th><td>x1</td><td>0.258192</td></tr><tr><th>2</th><td>x1</td><td>0.595795</td></tr><tr><th>3</th><td>x1</td><td>0.511016</td></tr><tr><th>4</th><td>x2</td><td>0.26596</td></tr><tr><th>5</th><td>x2</td><td>0.426824</td></tr><tr><th>6</th><td>x2</td><td>0.826271</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& variable & value\\\\\n",
       "\t\\hline\n",
       "\t& Cat… & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & x1 & 0.258192 \\\\\n",
       "\t2 & x1 & 0.595795 \\\\\n",
       "\t3 & x1 & 0.511016 \\\\\n",
       "\t4 & x2 & 0.26596 \\\\\n",
       "\t5 & x2 & 0.426824 \\\\\n",
       "\t6 & x2 & 0.826271 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "6×2 DataFrame\n",
       "│ Row │ variable │ value    │\n",
       "│     │ \u001b[90mCat…\u001b[39m     │ \u001b[90mFloat64\u001b[39m  │\n",
       "├─────┼──────────┼──────────┤\n",
       "│ 1   │ x1       │ 0.258192 │\n",
       "│ 2   │ x1       │ 0.595795 │\n",
       "│ 3   │ x1       │ 0.511016 │\n",
       "│ 4   │ x2       │ 0.26596  │\n",
       "│ 5   │ x2       │ 0.426824 │\n",
       "│ 6   │ x2       │ 0.826271 │"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = stack(DataFrame(rand(3,2)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "ename": "ArgumentError",
     "evalue": "ArgumentError: No key column found",
     "output_type": "error",
     "traceback": [
      "ArgumentError: No key column found",
      "",
      "Stacktrace:",
      " [1] unstack(::DataFrame, ::InvertedIndex{InvertedIndices.TupleVector{Tuple{Int64,Int64}}}, ::Int64, ::Int64; renamecols::Function) at /home/bkamins/.julia/packages/DataFrames/3ZmR2/src/abstractdataframe/reshape.jl:228",
      " [2] unstack(::DataFrame, ::Symbol, ::Symbol; renamecols::Function) at /home/bkamins/.julia/packages/DataFrames/3ZmR2/src/abstractdataframe/reshape.jl:242",
      " [3] unstack(::DataFrame, ::Symbol, ::Symbol) at /home/bkamins/.julia/packages/DataFrames/3ZmR2/src/abstractdataframe/reshape.jl:240",
      " [4] top-level scope at In[18]:1"
     ]
    }
   ],
   "source": [
    "unstack(df, :variable, :value) # unable to unstack when no key column is present"
   ]
  }
 ],
 "metadata": {
  "@webio": {
   "lastCommId": null,
   "lastKernelId": null
  },
  "kernelspec": {
   "display_name": "Julia 1.4.1",
   "language": "julia",
   "name": "julia-1.4"
  },
  "language_info": {
   "file_extension": ".jl",
   "mimetype": "application/julia",
   "name": "julia",
   "version": "1.4.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}