09_reshaping.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to DataFrames\n",
    "**[Bogumił Kamiński](http://bogumilkaminski.pl/about/), April 27, 2019**"
   ]
  },
  {
   "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>variable</th><th>value</th><th>id</th></tr><tr><th></th><th>Symbol</th><th>Int64</th><th>Int64</th></tr></thead><tbody><p>8 rows × 3 columns</p><tr><th>1</th><td>M1</td><td>11</td><td>1</td></tr><tr><th>2</th><td>M1</td><td>12</td><td>2</td></tr><tr><th>3</th><td>M1</td><td>13</td><td>3</td></tr><tr><th>4</th><td>M1</td><td>14</td><td>4</td></tr><tr><th>5</th><td>M2</td><td>111</td><td>1</td></tr><tr><th>6</th><td>M2</td><td>112</td><td>2</td></tr><tr><th>7</th><td>M2</td><td>113</td><td>3</td></tr><tr><th>8</th><td>M2</td><td>114</td><td>4</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& variable & value & id\\\\\n",
       "\t\\hline\n",
       "\t& Symbol & Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & M1 & 11 & 1 \\\\\n",
       "\t2 & M1 & 12 & 2 \\\\\n",
       "\t3 & M1 & 13 & 3 \\\\\n",
       "\t4 & M1 & 14 & 4 \\\\\n",
       "\t5 & M2 & 111 & 1 \\\\\n",
       "\t6 & M2 & 112 & 2 \\\\\n",
       "\t7 & M2 & 113 & 3 \\\\\n",
       "\t8 & M2 & 114 & 4 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "8×3 DataFrame\n",
       "│ Row │ variable │ value │ id    │\n",
       "│     │ \u001b[90mSymbol\u001b[39m   │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │\n",
       "├─────┼──────────┼───────┼───────┤\n",
       "│ 1   │ M1       │ 11    │ 1     │\n",
       "│ 2   │ M1       │ 12    │ 2     │\n",
       "│ 3   │ M1       │ 13    │ 3     │\n",
       "│ 4   │ M1       │ 14    │ 4     │\n",
       "│ 5   │ M2       │ 111   │ 1     │\n",
       "│ 6   │ M2       │ 112   │ 2     │\n",
       "│ 7   │ M2       │ 113   │ 3     │\n",
       "│ 8   │ M2       │ 114   │ 4     │"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "melt(x, :id, [:M1, :M2]) # first pass id-variables and then measure variables; meltdf makes a view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>key</th><th>observed</th><th>id</th></tr><tr><th></th><th>Symbol</th><th>Int64</th><th>Int64</th></tr></thead><tbody><p>8 rows × 3 columns</p><tr><th>1</th><td>M1</td><td>11</td><td>1</td></tr><tr><th>2</th><td>M1</td><td>12</td><td>2</td></tr><tr><th>3</th><td>M1</td><td>13</td><td>3</td></tr><tr><th>4</th><td>M1</td><td>14</td><td>4</td></tr><tr><th>5</th><td>M2</td><td>111</td><td>1</td></tr><tr><th>6</th><td>M2</td><td>112</td><td>2</td></tr><tr><th>7</th><td>M2</td><td>113</td><td>3</td></tr><tr><th>8</th><td>M2</td><td>114</td><td>4</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& key & observed & id\\\\\n",
       "\t\\hline\n",
       "\t& Symbol & Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & M1 & 11 & 1 \\\\\n",
       "\t2 & M1 & 12 & 2 \\\\\n",
       "\t3 & M1 & 13 & 3 \\\\\n",
       "\t4 & M1 & 14 & 4 \\\\\n",
       "\t5 & M2 & 111 & 1 \\\\\n",
       "\t6 & M2 & 112 & 2 \\\\\n",
       "\t7 & M2 & 113 & 3 \\\\\n",
       "\t8 & M2 & 114 & 4 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "8×3 DataFrame\n",
       "│ Row │ key    │ observed │ id    │\n",
       "│     │ \u001b[90mSymbol\u001b[39m │ \u001b[90mInt64\u001b[39m    │ \u001b[90mInt64\u001b[39m │\n",
       "├─────┼────────┼──────────┼───────┤\n",
       "│ 1   │ M1     │ 11       │ 1     │\n",
       "│ 2   │ M1     │ 12       │ 2     │\n",
       "│ 3   │ M1     │ 13       │ 3     │\n",
       "│ 4   │ M1     │ 14       │ 4     │\n",
       "│ 5   │ M2     │ 111      │ 1     │\n",
       "│ 6   │ M2     │ 112      │ 2     │\n",
       "│ 7   │ M2     │ 113      │ 3     │\n",
       "│ 8   │ M2     │ 114      │ 4     │"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# optionally you can rename columns; melt and stack are identical but order of arguments is reversed\n",
    "stack(x, [:M1, :M2], :id, variable_name=:key, value_name=:observed) # first measures and then id-s; stackdf creates view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>variable</th><th>value</th><th>id</th><th>id2</th></tr><tr><th></th><th>Symbol</th><th>Int64</th><th>Int64</th><th>Int64</th></tr></thead><tbody><p>8 rows × 4 columns</p><tr><th>1</th><td>M1</td><td>11</td><td>1</td><td>1</td></tr><tr><th>2</th><td>M1</td><td>12</td><td>2</td><td>1</td></tr><tr><th>3</th><td>M1</td><td>13</td><td>3</td><td>2</td></tr><tr><th>4</th><td>M1</td><td>14</td><td>4</td><td>2</td></tr><tr><th>5</th><td>M2</td><td>111</td><td>1</td><td>1</td></tr><tr><th>6</th><td>M2</td><td>112</td><td>2</td><td>1</td></tr><tr><th>7</th><td>M2</td><td>113</td><td>3</td><td>2</td></tr><tr><th>8</th><td>M2</td><td>114</td><td>4</td><td>2</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& variable & value & id & id2\\\\\n",
       "\t\\hline\n",
       "\t& Symbol & Int64 & Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & M1 & 11 & 1 & 1 \\\\\n",
       "\t2 & M1 & 12 & 2 & 1 \\\\\n",
       "\t3 & M1 & 13 & 3 & 2 \\\\\n",
       "\t4 & M1 & 14 & 4 & 2 \\\\\n",
       "\t5 & M2 & 111 & 1 & 1 \\\\\n",
       "\t6 & M2 & 112 & 2 & 1 \\\\\n",
       "\t7 & M2 & 113 & 3 & 2 \\\\\n",
       "\t8 & M2 & 114 & 4 & 2 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "8×4 DataFrame\n",
       "│ Row │ variable │ value │ id    │ id2   │\n",
       "│     │ \u001b[90mSymbol\u001b[39m   │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │\n",
       "├─────┼──────────┼───────┼───────┼───────┤\n",
       "│ 1   │ M1       │ 11    │ 1     │ 1     │\n",
       "│ 2   │ M1       │ 12    │ 2     │ 1     │\n",
       "│ 3   │ M1       │ 13    │ 3     │ 2     │\n",
       "│ 4   │ M1       │ 14    │ 4     │ 2     │\n",
       "│ 5   │ M2       │ 111   │ 1     │ 1     │\n",
       "│ 6   │ M2       │ 112   │ 2     │ 1     │\n",
       "│ 7   │ M2       │ 113   │ 3     │ 2     │\n",
       "│ 8   │ M2       │ 114   │ 4     │ 2     │"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# if second argument is omitted in melt or stack , all other columns are assumed to be the second argument\n",
    "# but measure variables are selected only if they are <: AbstractFloat\n",
    "melt(x, [:id, :id2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>variable</th><th>value</th><th>id</th><th>id2</th></tr><tr><th></th><th>Symbol</th><th>Int64</th><th>Int64</th><th>Int64</th></tr></thead><tbody><p>8 rows × 4 columns</p><tr><th>1</th><td>M1</td><td>11</td><td>1</td><td>1</td></tr><tr><th>2</th><td>M1</td><td>12</td><td>2</td><td>1</td></tr><tr><th>3</th><td>M1</td><td>13</td><td>3</td><td>2</td></tr><tr><th>4</th><td>M1</td><td>14</td><td>4</td><td>2</td></tr><tr><th>5</th><td>M2</td><td>111</td><td>1</td><td>1</td></tr><tr><th>6</th><td>M2</td><td>112</td><td>2</td><td>1</td></tr><tr><th>7</th><td>M2</td><td>113</td><td>3</td><td>2</td></tr><tr><th>8</th><td>M2</td><td>114</td><td>4</td><td>2</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& variable & value & id & id2\\\\\n",
       "\t\\hline\n",
       "\t& Symbol & Int64 & Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & M1 & 11 & 1 & 1 \\\\\n",
       "\t2 & M1 & 12 & 2 & 1 \\\\\n",
       "\t3 & M1 & 13 & 3 & 2 \\\\\n",
       "\t4 & M1 & 14 & 4 & 2 \\\\\n",
       "\t5 & M2 & 111 & 1 & 1 \\\\\n",
       "\t6 & M2 & 112 & 2 & 1 \\\\\n",
       "\t7 & M2 & 113 & 3 & 2 \\\\\n",
       "\t8 & M2 & 114 & 4 & 2 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "8×4 DataFrame\n",
       "│ Row │ variable │ value │ id    │ id2   │\n",
       "│     │ \u001b[90mSymbol\u001b[39m   │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │\n",
       "├─────┼──────────┼───────┼───────┼───────┤\n",
       "│ 1   │ M1       │ 11    │ 1     │ 1     │\n",
       "│ 2   │ M1       │ 12    │ 2     │ 1     │\n",
       "│ 3   │ M1       │ 13    │ 3     │ 2     │\n",
       "│ 4   │ M1       │ 14    │ 4     │ 2     │\n",
       "│ 5   │ M2       │ 111   │ 1     │ 1     │\n",
       "│ 6   │ M2       │ 112   │ 2     │ 1     │\n",
       "│ 7   │ M2       │ 113   │ 3     │ 2     │\n",
       "│ 8   │ M2       │ 114   │ 4     │ 2     │"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "melt(x, [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.258335</td><td>0.377114</td></tr><tr><th>2</th><td>1</td><td>'b'</td><td>0.363965</td><td>0.372239</td></tr><tr><th>3</th><td>1</td><td>'c'</td><td>0.140952</td><td>0.766512</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.258335 & 0.377114 \\\\\n",
       "\t2 & 1 & 'b' & 0.363965 & 0.372239 \\\\\n",
       "\t3 & 1 & 'c' & 0.140952 & 0.766512 \\\\\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.258335 │ 0.377114 │\n",
       "│ 2   │ 1     │ 'b'  │ 0.363965 │ 0.372239 │\n",
       "│ 3   │ 1     │ 'c'  │ 0.140952 │ 0.766512 │"
      ]
     },
     "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": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>variable</th><th>value</th><th>id</th><th>id2</th></tr><tr><th></th><th>Symbol</th><th>Float64</th><th>Int64</th><th>Char</th></tr></thead><tbody><p>6 rows × 4 columns</p><tr><th>1</th><td>a1</td><td>0.258335</td><td>1</td><td>'a'</td></tr><tr><th>2</th><td>a1</td><td>0.363965</td><td>1</td><td>'b'</td></tr><tr><th>3</th><td>a1</td><td>0.140952</td><td>1</td><td>'c'</td></tr><tr><th>4</th><td>a2</td><td>0.377114</td><td>1</td><td>'a'</td></tr><tr><th>5</th><td>a2</td><td>0.372239</td><td>1</td><td>'b'</td></tr><tr><th>6</th><td>a2</td><td>0.766512</td><td>1</td><td>'c'</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& variable & value & id & id2\\\\\n",
       "\t\\hline\n",
       "\t& Symbol & Float64 & Int64 & Char\\\\\n",
       "\t\\hline\n",
       "\t1 & a1 & 0.258335 & 1 & 'a' \\\\\n",
       "\t2 & a1 & 0.363965 & 1 & 'b' \\\\\n",
       "\t3 & a1 & 0.140952 & 1 & 'c' \\\\\n",
       "\t4 & a2 & 0.377114 & 1 & 'a' \\\\\n",
       "\t5 & a2 & 0.372239 & 1 & 'b' \\\\\n",
       "\t6 & a2 & 0.766512 & 1 & 'c' \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "6×4 DataFrame\n",
       "│ Row │ variable │ value    │ id    │ id2  │\n",
       "│     │ \u001b[90mSymbol\u001b[39m   │ \u001b[90mFloat64\u001b[39m  │ \u001b[90mInt64\u001b[39m │ \u001b[90mChar\u001b[39m │\n",
       "├─────┼──────────┼──────────┼───────┼──────┤\n",
       "│ 1   │ a1       │ 0.258335 │ 1     │ 'a'  │\n",
       "│ 2   │ a1       │ 0.363965 │ 1     │ 'b'  │\n",
       "│ 3   │ a1       │ 0.140952 │ 1     │ 'c'  │\n",
       "│ 4   │ a2       │ 0.377114 │ 1     │ 'a'  │\n",
       "│ 5   │ a2       │ 0.372239 │ 1     │ 'b'  │\n",
       "│ 6   │ a2       │ 0.766512 │ 1     │ 'c'  │"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "melt(x)"
   ]
  },
  {
   "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>Symbol</th><th>Float64</th></tr></thead><tbody><p>6 rows × 2 columns</p><tr><th>1</th><td>x1</td><td>0.864447</td></tr><tr><th>2</th><td>x1</td><td>0.0040455</td></tr><tr><th>3</th><td>x1</td><td>0.396437</td></tr><tr><th>4</th><td>x2</td><td>0.0592978</td></tr><tr><th>5</th><td>x2</td><td>0.994284</td></tr><tr><th>6</th><td>x2</td><td>0.195183</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& variable & value\\\\\n",
       "\t\\hline\n",
       "\t& Symbol & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & x1 & 0.864447 \\\\\n",
       "\t2 & x1 & 0.0040455 \\\\\n",
       "\t3 & x1 & 0.396437 \\\\\n",
       "\t4 & x2 & 0.0592978 \\\\\n",
       "\t5 & x2 & 0.994284 \\\\\n",
       "\t6 & x2 & 0.195183 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "6×2 DataFrame\n",
       "│ Row │ variable │ value     │\n",
       "│     │ \u001b[90mSymbol\u001b[39m   │ \u001b[90mFloat64\u001b[39m   │\n",
       "├─────┼──────────┼───────────┤\n",
       "│ 1   │ x1       │ 0.864447  │\n",
       "│ 2   │ x1       │ 0.0040455 │\n",
       "│ 3   │ x1       │ 0.396437  │\n",
       "│ 4   │ x2       │ 0.0592978 │\n",
       "│ 5   │ x2       │ 0.994284  │\n",
       "│ 6   │ x2       │ 0.195183  │"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "melt(DataFrame(rand(3,2))) # by default stack and melt treats floats as value columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>variable</th><th>value</th><th>key</th></tr><tr><th></th><th>Symbol</th><th>Float64</th><th>Int64</th></tr></thead><tbody><p>6 rows × 3 columns</p><tr><th>1</th><td>x1</td><td>0.892777</td><td>1</td></tr><tr><th>2</th><td>x1</td><td>0.0342695</td><td>1</td></tr><tr><th>3</th><td>x1</td><td>0.473633</td><td>1</td></tr><tr><th>4</th><td>x2</td><td>0.817327</td><td>1</td></tr><tr><th>5</th><td>x2</td><td>0.693692</td><td>1</td></tr><tr><th>6</th><td>x2</td><td>0.426453</td><td>1</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& variable & value & key\\\\\n",
       "\t\\hline\n",
       "\t& Symbol & Float64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & x1 & 0.892777 & 1 \\\\\n",
       "\t2 & x1 & 0.0342695 & 1 \\\\\n",
       "\t3 & x1 & 0.473633 & 1 \\\\\n",
       "\t4 & x2 & 0.817327 & 1 \\\\\n",
       "\t5 & x2 & 0.693692 & 1 \\\\\n",
       "\t6 & x2 & 0.426453 & 1 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "6×3 DataFrame\n",
       "│ Row │ variable │ value     │ key   │\n",
       "│     │ \u001b[90mSymbol\u001b[39m   │ \u001b[90mFloat64\u001b[39m   │ \u001b[90mInt64\u001b[39m │\n",
       "├─────┼──────────┼───────────┼───────┤\n",
       "│ 1   │ x1       │ 0.892777  │ 1     │\n",
       "│ 2   │ x1       │ 0.0342695 │ 1     │\n",
       "│ 3   │ x1       │ 0.473633  │ 1     │\n",
       "│ 4   │ x2       │ 0.817327  │ 1     │\n",
       "│ 5   │ x2       │ 0.693692  │ 1     │\n",
       "│ 6   │ x2       │ 0.426453  │ 1     │"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = DataFrame(rand(3,2))\n",
    "df.key = [1,1,1]\n",
    "mdf = melt(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.568815</td><td>0.46165</td></tr><tr><th>2</th><td>1</td><td>'b'</td><td>0.960133</td><td>0.0587342</td></tr><tr><th>3</th><td>1</td><td>'c'</td><td>0.555774</td><td>0.574289</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.568815 & 0.46165 \\\\\n",
       "\t2 & 1 & 'b' & 0.960133 & 0.0587342 \\\\\n",
       "\t3 & 1 & 'c' & 0.555774 & 0.574289 \\\\\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.568815 │ 0.46165   │\n",
       "│ 2   │ 1     │ 'b'  │ 0.960133 │ 0.0587342 │\n",
       "│ 3   │ 1     │ 'c'  │ 0.555774 │ 0.574289  │"
      ]
     },
     "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>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.568815</td><td>0.46165</td></tr><tr><th>2</th><td>1</td><td>'b'</td><td>0.960133</td><td>0.0587342</td></tr><tr><th>3</th><td>1</td><td>'c'</td><td>0.555774</td><td>0.574289</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.568815 & 0.46165 \\\\\n",
       "\t2 & 1 & 'b' & 0.960133 & 0.0587342 \\\\\n",
       "\t3 & 1 & 'c' & 0.555774 & 0.574289 \\\\\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.568815 │ 0.46165   │\n",
       "│ 2   │ 1     │ 'b'  │ 0.960133 │ 0.0587342 │\n",
       "│ 3   │ 1     │ 'c'  │ 0.555774 │ 0.574289  │"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>variable</th><th>value</th><th>id</th><th>id2</th></tr><tr><th></th><th>Symbol</th><th>Float64</th><th>Int64</th><th>Char</th></tr></thead><tbody><p>6 rows × 4 columns</p><tr><th>1</th><td>a1</td><td>0.568815</td><td>1</td><td>'a'</td></tr><tr><th>2</th><td>a1</td><td>0.960133</td><td>1</td><td>'b'</td></tr><tr><th>3</th><td>a1</td><td>0.555774</td><td>1</td><td>'c'</td></tr><tr><th>4</th><td>a2</td><td>0.46165</td><td>1</td><td>'a'</td></tr><tr><th>5</th><td>a2</td><td>0.0587342</td><td>1</td><td>'b'</td></tr><tr><th>6</th><td>a2</td><td>0.574289</td><td>1</td><td>'c'</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& variable & value & id & id2\\\\\n",
       "\t\\hline\n",
       "\t& Symbol & Float64 & Int64 & Char\\\\\n",
       "\t\\hline\n",
       "\t1 & a1 & 0.568815 & 1 & 'a' \\\\\n",
       "\t2 & a1 & 0.960133 & 1 & 'b' \\\\\n",
       "\t3 & a1 & 0.555774 & 1 & 'c' \\\\\n",
       "\t4 & a2 & 0.46165 & 1 & 'a' \\\\\n",
       "\t5 & a2 & 0.0587342 & 1 & 'b' \\\\\n",
       "\t6 & a2 & 0.574289 & 1 & 'c' \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "6×4 DataFrame\n",
       "│ Row │ variable │ value     │ id    │ id2  │\n",
       "│     │ \u001b[90mSymbol\u001b[39m   │ \u001b[90mFloat64\u001b[39m   │ \u001b[90mInt64\u001b[39m │ \u001b[90mChar\u001b[39m │\n",
       "├─────┼──────────┼───────────┼───────┼──────┤\n",
       "│ 1   │ a1       │ 0.568815  │ 1     │ 'a'  │\n",
       "│ 2   │ a1       │ 0.960133  │ 1     │ 'b'  │\n",
       "│ 3   │ a1       │ 0.555774  │ 1     │ 'c'  │\n",
       "│ 4   │ a2       │ 0.46165   │ 1     │ 'a'  │\n",
       "│ 5   │ a2       │ 0.0587342 │ 1     │ 'b'  │\n",
       "│ 6   │ a2       │ 0.574289  │ 1     │ 'c'  │"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "y = melt(x, [1,2])\n",
    "display(x)\n",
    "display(y)"
   ]
  },
  {
   "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.568815</td><td>0.46165</td></tr><tr><th>2</th><td>'b'</td><td>0.960133</td><td>0.0587342</td></tr><tr><th>3</th><td>'c'</td><td>0.555774</td><td>0.574289</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.568815 & 0.46165 \\\\\n",
       "\t2 & 'b' & 0.960133 & 0.0587342 \\\\\n",
       "\t3 & 'c' & 0.555774 & 0.574289 \\\\\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.568815 │ 0.46165   │\n",
       "│ 2   │ 'b'  │ 0.960133 │ 0.0587342 │\n",
       "│ 3   │ 'c'  │ 0.555774 │ 0.574289  │"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unstack(y, :id2, :variable, :value) # stndard unstack with a unique 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.568815</td><td>0.46165</td></tr><tr><th>2</th><td>1</td><td>'b'</td><td>0.960133</td><td>0.0587342</td></tr><tr><th>3</th><td>1</td><td>'c'</td><td>0.555774</td><td>0.574289</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.568815 & 0.46165 \\\\\n",
       "\t2 & 1 & 'b' & 0.960133 & 0.0587342 \\\\\n",
       "\t3 & 1 & 'c' & 0.555774 & 0.574289 \\\\\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.568815 │ 0.46165   │\n",
       "│ 2   │ 1     │ 'b'  │ 0.960133 │ 0.0587342 │\n",
       "│ 3   │ 1     │ 'c'  │ 0.555774 │ 0.574289  │"
      ]
     },
     "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.568815</td><td>0.46165</td></tr><tr><th>2</th><td>1</td><td>'b'</td><td>0.960133</td><td>0.0587342</td></tr><tr><th>3</th><td>1</td><td>'c'</td><td>0.555774</td><td>0.574289</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.568815 & 0.46165 \\\\\n",
       "\t2 & 1 & 'b' & 0.960133 & 0.0587342 \\\\\n",
       "\t3 & 1 & 'c' & 0.555774 & 0.574289 \\\\\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.568815 │ 0.46165   │\n",
       "│ 2   │ 1     │ 'b'  │ 0.960133 │ 0.0587342 │\n",
       "│ 3   │ 1     │ 'c'  │ 0.555774 │ 0.574289  │"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# all columns other than :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>variable</th><th>value</th></tr><tr><th></th><th>Symbol</th><th>Float64</th></tr></thead><tbody><p>6 rows × 2 columns</p><tr><th>1</th><td>x1</td><td>0.14569</td></tr><tr><th>2</th><td>x1</td><td>0.0909384</td></tr><tr><th>3</th><td>x1</td><td>0.367413</td></tr><tr><th>4</th><td>x2</td><td>0.734355</td></tr><tr><th>5</th><td>x2</td><td>0.755325</td></tr><tr><th>6</th><td>x2</td><td>0.63808</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& variable & value\\\\\n",
       "\t\\hline\n",
       "\t& Symbol & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & x1 & 0.14569 \\\\\n",
       "\t2 & x1 & 0.0909384 \\\\\n",
       "\t3 & x1 & 0.367413 \\\\\n",
       "\t4 & x2 & 0.734355 \\\\\n",
       "\t5 & x2 & 0.755325 \\\\\n",
       "\t6 & x2 & 0.63808 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "6×2 DataFrame\n",
       "│ Row │ variable │ value     │\n",
       "│     │ \u001b[90mSymbol\u001b[39m   │ \u001b[90mFloat64\u001b[39m   │\n",
       "├─────┼──────────┼───────────┤\n",
       "│ 1   │ x1       │ 0.14569   │\n",
       "│ 2   │ x1       │ 0.0909384 │\n",
       "│ 3   │ x1       │ 0.367413  │\n",
       "│ 4   │ x2       │ 0.734355  │\n",
       "│ 5   │ x2       │ 0.755325  │\n",
       "│ 6   │ x2       │ 0.63808   │"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = stack(DataFrame(rand(3,2)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "ename": "ArgumentError",
     "evalue": "ArgumentError: No key column found",
     "output_type": "error",
     "traceback": [
      "ArgumentError: No key column found",
      "",
      "Stacktrace:",
      " [1] unstack(::DataFrame, ::Array{Symbol,1}, ::Int64, ::Int64) at C:\\Users\\bogum\\.julia\\packages\\DataFrames\\Wvy9E\\src\\abstractdataframe\\reshape.jl:279",
      " [2] unstack(::DataFrame, ::Int64, ::Int64) at C:\\Users\\bogum\\.julia\\packages\\DataFrames\\Wvy9E\\src\\abstractdataframe\\reshape.jl:269",
      " [3] unstack(::DataFrame, ::Symbol, ::Symbol) at C:\\Users\\bogum\\.julia\\packages\\DataFrames\\Wvy9E\\src\\abstractdataframe\\reshape.jl:265",
      " [4] top-level scope at In[17]:1"
     ]
    }
   ],
   "source": [
    "unstack(df, :variable, :value) # unable to unstack when no key column is present"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Julia 1.0.3",
   "language": "julia",
   "name": "julia-1.0"
  },
  "language_info": {
   "file_extension": ".jl",
   "mimetype": "application/julia",
   "name": "julia",
   "version": "1.1.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}