10_transforms.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": [
    "## Split-apply-combine"
   ]
  },
  {
   "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>v</th></tr><tr><th></th><th>Int64</th><th>Int64</th><th>Float64</th></tr></thead><tbody><p>8 rows × 3 columns</p><tr><th>1</th><td>1</td><td>1</td><td>0.669188</td></tr><tr><th>2</th><td>2</td><td>2</td><td>0.109742</td></tr><tr><th>3</th><td>3</td><td>1</td><td>0.565507</td></tr><tr><th>4</th><td>4</td><td>2</td><td>0.371262</td></tr><tr><th>5</th><td>1</td><td>1</td><td>0.769019</td></tr><tr><th>6</th><td>2</td><td>2</td><td>0.525894</td></tr><tr><th>7</th><td>3</td><td>1</td><td>0.839502</td></tr><tr><th>8</th><td>4</td><td>2</td><td>0.282013</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & id2 & v\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & 0.669188 \\\\\n",
       "\t2 & 2 & 2 & 0.109742 \\\\\n",
       "\t3 & 3 & 1 & 0.565507 \\\\\n",
       "\t4 & 4 & 2 & 0.371262 \\\\\n",
       "\t5 & 1 & 1 & 0.769019 \\\\\n",
       "\t6 & 2 & 2 & 0.525894 \\\\\n",
       "\t7 & 3 & 1 & 0.839502 \\\\\n",
       "\t8 & 4 & 2 & 0.282013 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "8×3 DataFrame\n",
       "│ Row │ id    │ id2   │ v        │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │ \u001b[90mFloat64\u001b[39m  │\n",
       "├─────┼───────┼───────┼──────────┤\n",
       "│ 1   │ 1     │ 1     │ 0.669188 │\n",
       "│ 2   │ 2     │ 2     │ 0.109742 │\n",
       "│ 3   │ 3     │ 1     │ 0.565507 │\n",
       "│ 4   │ 4     │ 2     │ 0.371262 │\n",
       "│ 5   │ 1     │ 1     │ 0.769019 │\n",
       "│ 6   │ 2     │ 2     │ 0.525894 │\n",
       "│ 7   │ 3     │ 1     │ 0.839502 │\n",
       "│ 8   │ 4     │ 2     │ 0.282013 │"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = DataFrame(id=[1,2,3,4,1,2,3,4], id2=[1,2,1,2,1,2,1,2], v=rand(8))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<p><b>GroupedDataFrame with 4 groups based on key: id</b></p><p><i>First Group (2 rows): id = 1</i></p><table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>id2</th><th>v</th></tr><tr><th></th><th>Int64</th><th>Int64</th><th>Float64</th></tr></thead><tbody><tr><th>1</th><td>1</td><td>1</td><td>0.669188</td></tr><tr><th>2</th><td>1</td><td>1</td><td>0.769019</td></tr></tbody></table><p>&vellip;</p><p><i>Last Group (2 rows): id = 4</i></p><table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>id2</th><th>v</th></tr><tr><th></th><th>Int64</th><th>Int64</th><th>Float64</th></tr></thead><tbody><tr><th>1</th><td>4</td><td>2</td><td>0.371262</td></tr><tr><th>2</th><td>4</td><td>2</td><td>0.282013</td></tr></tbody></table>"
      ],
      "text/latex": [
       "GroupedDataFrame with 4 groups based on key: id\n",
       "\n",
       "First Group (2 rows): id = 1\n",
       "\n",
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & id2 & v\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & 0.669188 \\\\\n",
       "\t2 & 1 & 1 & 0.769019 \\\\\n",
       "\\end{tabular}\n",
       "\n",
       "$\\dots$\n",
       "\n",
       "Last Group (2 rows): id = 4\n",
       "\n",
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & id2 & v\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 4 & 2 & 0.371262 \\\\\n",
       "\t2 & 4 & 2 & 0.282013 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "GroupedDataFrame with 4 groups based on key: id\n",
       "First Group (2 rows): id = 1\n",
       "│ Row │ id    │ id2   │ v        │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │ \u001b[90mFloat64\u001b[39m  │\n",
       "├─────┼───────┼───────┼──────────┤\n",
       "│ 1   │ 1     │ 1     │ 0.669188 │\n",
       "│ 2   │ 1     │ 1     │ 0.769019 │\n",
       "⋮\n",
       "Last Group (2 rows): id = 4\n",
       "│ Row │ id    │ id2   │ v        │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │ \u001b[90mFloat64\u001b[39m  │\n",
       "├─────┼───────┼───────┼──────────┤\n",
       "│ 1   │ 4     │ 2     │ 0.371262 │\n",
       "│ 2   │ 4     │ 2     │ 0.282013 │"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gx1 = groupby(x, :id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<p><b>GroupedDataFrame with 4 groups based on keys: id, id2</b></p><p><i>First Group (2 rows): id = 1, id2 = 1</i></p><table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>id2</th><th>v</th></tr><tr><th></th><th>Int64</th><th>Int64</th><th>Float64</th></tr></thead><tbody><tr><th>1</th><td>1</td><td>1</td><td>0.669188</td></tr><tr><th>2</th><td>1</td><td>1</td><td>0.769019</td></tr></tbody></table><p>&vellip;</p><p><i>Last Group (2 rows): id = 4, id2 = 2</i></p><table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>id2</th><th>v</th></tr><tr><th></th><th>Int64</th><th>Int64</th><th>Float64</th></tr></thead><tbody><tr><th>1</th><td>4</td><td>2</td><td>0.371262</td></tr><tr><th>2</th><td>4</td><td>2</td><td>0.282013</td></tr></tbody></table>"
      ],
      "text/latex": [
       "GroupedDataFrame with 4 groups based on keys: id, id2\n",
       "\n",
       "First Group (2 rows): id = 1, id2 = 1\n",
       "\n",
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & id2 & v\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & 0.669188 \\\\\n",
       "\t2 & 1 & 1 & 0.769019 \\\\\n",
       "\\end{tabular}\n",
       "\n",
       "$\\dots$\n",
       "\n",
       "Last Group (2 rows): id = 4, id2 = 2\n",
       "\n",
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & id2 & v\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 4 & 2 & 0.371262 \\\\\n",
       "\t2 & 4 & 2 & 0.282013 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "GroupedDataFrame with 4 groups based on keys: id, id2\n",
       "First Group (2 rows): id = 1, id2 = 1\n",
       "│ Row │ id    │ id2   │ v        │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │ \u001b[90mFloat64\u001b[39m  │\n",
       "├─────┼───────┼───────┼──────────┤\n",
       "│ 1   │ 1     │ 1     │ 0.669188 │\n",
       "│ 2   │ 1     │ 1     │ 0.769019 │\n",
       "⋮\n",
       "Last Group (2 rows): id = 4, id2 = 2\n",
       "│ Row │ id    │ id2   │ v        │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │ \u001b[90mFloat64\u001b[39m  │\n",
       "├─────┼───────┼───────┼──────────┤\n",
       "│ 1   │ 4     │ 2     │ 0.371262 │\n",
       "│ 2   │ 4     │ 2     │ 0.282013 │"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gx2 = groupby(x, [:id, :id2])"
   ]
  },
  {
   "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>v</th></tr><tr><th></th><th>Int64</th><th>Int64</th><th>Float64</th></tr></thead><tbody><p>8 rows × 3 columns</p><tr><th>1</th><td>1</td><td>1</td><td>0.669188</td></tr><tr><th>2</th><td>2</td><td>2</td><td>0.109742</td></tr><tr><th>3</th><td>3</td><td>1</td><td>0.565507</td></tr><tr><th>4</th><td>4</td><td>2</td><td>0.371262</td></tr><tr><th>5</th><td>1</td><td>1</td><td>0.769019</td></tr><tr><th>6</th><td>2</td><td>2</td><td>0.525894</td></tr><tr><th>7</th><td>3</td><td>1</td><td>0.839502</td></tr><tr><th>8</th><td>4</td><td>2</td><td>0.282013</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & id2 & v\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & 0.669188 \\\\\n",
       "\t2 & 2 & 2 & 0.109742 \\\\\n",
       "\t3 & 3 & 1 & 0.565507 \\\\\n",
       "\t4 & 4 & 2 & 0.371262 \\\\\n",
       "\t5 & 1 & 1 & 0.769019 \\\\\n",
       "\t6 & 2 & 2 & 0.525894 \\\\\n",
       "\t7 & 3 & 1 & 0.839502 \\\\\n",
       "\t8 & 4 & 2 & 0.282013 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "8×3 DataFrame\n",
       "│ Row │ id    │ id2   │ v        │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │ \u001b[90mFloat64\u001b[39m  │\n",
       "├─────┼───────┼───────┼──────────┤\n",
       "│ 1   │ 1     │ 1     │ 0.669188 │\n",
       "│ 2   │ 2     │ 2     │ 0.109742 │\n",
       "│ 3   │ 3     │ 1     │ 0.565507 │\n",
       "│ 4   │ 4     │ 2     │ 0.371262 │\n",
       "│ 5   │ 1     │ 1     │ 0.769019 │\n",
       "│ 6   │ 2     │ 2     │ 0.525894 │\n",
       "│ 7   │ 3     │ 1     │ 0.839502 │\n",
       "│ 8   │ 4     │ 2     │ 0.282013 │"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parent(gx2) # get the parent DataFrame "
   ]
  },
  {
   "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>v</th></tr><tr><th></th><th>Int64</th><th>Int64</th><th>Float64</th></tr></thead><tbody><p>8 rows × 3 columns</p><tr><th>1</th><td>1</td><td>1</td><td>0.669188</td></tr><tr><th>2</th><td>1</td><td>1</td><td>0.769019</td></tr><tr><th>3</th><td>2</td><td>2</td><td>0.109742</td></tr><tr><th>4</th><td>2</td><td>2</td><td>0.525894</td></tr><tr><th>5</th><td>3</td><td>1</td><td>0.565507</td></tr><tr><th>6</th><td>3</td><td>1</td><td>0.839502</td></tr><tr><th>7</th><td>4</td><td>2</td><td>0.371262</td></tr><tr><th>8</th><td>4</td><td>2</td><td>0.282013</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & id2 & v\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & 0.669188 \\\\\n",
       "\t2 & 1 & 1 & 0.769019 \\\\\n",
       "\t3 & 2 & 2 & 0.109742 \\\\\n",
       "\t4 & 2 & 2 & 0.525894 \\\\\n",
       "\t5 & 3 & 1 & 0.565507 \\\\\n",
       "\t6 & 3 & 1 & 0.839502 \\\\\n",
       "\t7 & 4 & 2 & 0.371262 \\\\\n",
       "\t8 & 4 & 2 & 0.282013 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "8×3 DataFrame\n",
       "│ Row │ id    │ id2   │ v        │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │ \u001b[90mFloat64\u001b[39m  │\n",
       "├─────┼───────┼───────┼──────────┤\n",
       "│ 1   │ 1     │ 1     │ 0.669188 │\n",
       "│ 2   │ 1     │ 1     │ 0.769019 │\n",
       "│ 3   │ 2     │ 2     │ 0.109742 │\n",
       "│ 4   │ 2     │ 2     │ 0.525894 │\n",
       "│ 5   │ 3     │ 1     │ 0.565507 │\n",
       "│ 6   │ 3     │ 1     │ 0.839502 │\n",
       "│ 7   │ 4     │ 2     │ 0.371262 │\n",
       "│ 8   │ 4     │ 2     │ 0.282013 │"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vcat(gx2...) # back to the DataFrame, but in a different order of rows than the original"
   ]
  },
  {
   "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>v</th></tr><tr><th></th><th>Int64</th><th>Int64</th><th>Float64</th></tr></thead><tbody><p>8 rows × 3 columns</p><tr><th>1</th><td>1</td><td>1</td><td>0.669188</td></tr><tr><th>2</th><td>1</td><td>1</td><td>0.769019</td></tr><tr><th>3</th><td>2</td><td>2</td><td>0.109742</td></tr><tr><th>4</th><td>2</td><td>2</td><td>0.525894</td></tr><tr><th>5</th><td>3</td><td>1</td><td>0.565507</td></tr><tr><th>6</th><td>3</td><td>1</td><td>0.839502</td></tr><tr><th>7</th><td>4</td><td>2</td><td>0.371262</td></tr><tr><th>8</th><td>4</td><td>2</td><td>0.282013</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & id2 & v\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & 0.669188 \\\\\n",
       "\t2 & 1 & 1 & 0.769019 \\\\\n",
       "\t3 & 2 & 2 & 0.109742 \\\\\n",
       "\t4 & 2 & 2 & 0.525894 \\\\\n",
       "\t5 & 3 & 1 & 0.565507 \\\\\n",
       "\t6 & 3 & 1 & 0.839502 \\\\\n",
       "\t7 & 4 & 2 & 0.371262 \\\\\n",
       "\t8 & 4 & 2 & 0.282013 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "8×3 DataFrame\n",
       "│ Row │ id    │ id2   │ v        │\n",
       "│     │ \u001b[90mInt64\u001b[39m │ \u001b[90mInt64\u001b[39m │ \u001b[90mFloat64\u001b[39m  │\n",
       "├─────┼───────┼───────┼──────────┤\n",
       "│ 1   │ 1     │ 1     │ 0.669188 │\n",
       "│ 2   │ 1     │ 1     │ 0.769019 │\n",
       "│ 3   │ 2     │ 2     │ 0.109742 │\n",
       "│ 4   │ 2     │ 2     │ 0.525894 │\n",
       "│ 5   │ 3     │ 1     │ 0.565507 │\n",
       "│ 6   │ 3     │ 1     │ 0.839502 │\n",
       "│ 7   │ 4     │ 2     │ 0.371262 │\n",
       "│ 8   │ 4     │ 2     │ 0.282013 │"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DataFrame(gx2) # the same"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2-element Array{Symbol,1}:\n",
       " :id \n",
       " :id2"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupvars(gx2) # vector of names of grouping variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8-element Array{Union{Missing, Int64},1}:\n",
       " 1\n",
       " 2\n",
       " 3\n",
       " 4\n",
       " 1\n",
       " 2\n",
       " 3\n",
       " 4"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupindices(gx2) # group indices in parent(gx2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>x</th></tr><tr><th></th><th>Int64⍰</th><th>Int64</th></tr></thead><tbody><p>5 rows × 2 columns</p><tr><th>1</th><td>missing</td><td>1</td></tr><tr><th>2</th><td>5</td><td>2</td></tr><tr><th>3</th><td>1</td><td>3</td></tr><tr><th>4</th><td>3</td><td>4</td></tr><tr><th>5</th><td>missing</td><td>5</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& id & x\\\\\n",
       "\t\\hline\n",
       "\t& Int64⍰ & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 &  & 1 \\\\\n",
       "\t2 & 5 & 2 \\\\\n",
       "\t3 & 1 & 3 \\\\\n",
       "\t4 & 3 & 4 \\\\\n",
       "\t5 &  & 5 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "5×2 DataFrame\n",
       "│ Row │ id      │ x     │\n",
       "│     │ \u001b[90mInt64⍰\u001b[39m  │ \u001b[90mInt64\u001b[39m │\n",
       "├─────┼─────────┼───────┤\n",
       "│ 1   │ \u001b[90mmissing\u001b[39m │ 1     │\n",
       "│ 2   │ 5       │ 2     │\n",
       "│ 3   │ 1       │ 3     │\n",
       "│ 4   │ 3       │ 4     │\n",
       "│ 5   │ \u001b[90mmissing\u001b[39m │ 5     │"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = DataFrame(id = [missing, 5, 1, 3, missing], x = 1:5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<p><b>GroupedDataFrame with 4 groups based on key: id</b></p><p><i>First Group (2 rows): id = missing</i></p><table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>x</th></tr><tr><th></th><th>Int64⍰</th><th>Int64</th></tr></thead><tbody><tr><th>1</th><td>missing</td><td>1</td></tr><tr><th>2</th><td>missing</td><td>5</td></tr></tbody></table><p>&vellip;</p><p><i>Last Group (1 row): id = 3</i></p><table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>x</th></tr><tr><th></th><th>Int64⍰</th><th>Int64</th></tr></thead><tbody><tr><th>1</th><td>3</td><td>4</td></tr></tbody></table>"
      ],
      "text/latex": [
       "GroupedDataFrame with 4 groups based on key: id\n",
       "\n",
       "First Group (2 rows): id = missing\n",
       "\n",
       "\\begin{tabular}{r|cc}\n",
       "\t& id & x\\\\\n",
       "\t\\hline\n",
       "\t& Int64⍰ & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 &  & 1 \\\\\n",
       "\t2 &  & 5 \\\\\n",
       "\\end{tabular}\n",
       "\n",
       "$\\dots$\n",
       "\n",
       "Last Group (1 row): id = 3\n",
       "\n",
       "\\begin{tabular}{r|cc}\n",
       "\t& id & x\\\\\n",
       "\t\\hline\n",
       "\t& Int64⍰ & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 3 & 4 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "GroupedDataFrame with 4 groups based on key: id\n",
       "First Group (2 rows): id = missing\n",
       "│ Row │ id      │ x     │\n",
       "│     │ \u001b[90mInt64⍰\u001b[39m  │ \u001b[90mInt64\u001b[39m │\n",
       "├─────┼─────────┼───────┤\n",
       "│ 1   │ \u001b[90mmissing\u001b[39m │ 1     │\n",
       "│ 2   │ \u001b[90mmissing\u001b[39m │ 5     │\n",
       "⋮\n",
       "Last Group (1 row): id = 3\n",
       "│ Row │ id     │ x     │\n",
       "│     │ \u001b[90mInt64⍰\u001b[39m │ \u001b[90mInt64\u001b[39m │\n",
       "├─────┼────────┼───────┤\n",
       "│ 1   │ 3      │ 4     │"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupby(x, :id) # by default groups include mising values and are not sorted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<p><b>GroupedDataFrame with 3 groups based on key: id</b></p><p><i>First Group (1 row): id = 1</i></p><table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>x</th></tr><tr><th></th><th>Int64⍰</th><th>Int64</th></tr></thead><tbody><tr><th>1</th><td>1</td><td>3</td></tr></tbody></table><p>&vellip;</p><p><i>Last Group (1 row): id = 5</i></p><table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>x</th></tr><tr><th></th><th>Int64⍰</th><th>Int64</th></tr></thead><tbody><tr><th>1</th><td>5</td><td>2</td></tr></tbody></table>"
      ],
      "text/latex": [
       "GroupedDataFrame with 3 groups based on key: id\n",
       "\n",
       "First Group (1 row): id = 1\n",
       "\n",
       "\\begin{tabular}{r|cc}\n",
       "\t& id & x\\\\\n",
       "\t\\hline\n",
       "\t& Int64⍰ & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 3 \\\\\n",
       "\\end{tabular}\n",
       "\n",
       "$\\dots$\n",
       "\n",
       "Last Group (1 row): id = 5\n",
       "\n",
       "\\begin{tabular}{r|cc}\n",
       "\t& id & x\\\\\n",
       "\t\\hline\n",
       "\t& Int64⍰ & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 5 & 2 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "GroupedDataFrame with 3 groups based on key: id\n",
       "First Group (1 row): id = 1\n",
       "│ Row │ id     │ x     │\n",
       "│     │ \u001b[90mInt64⍰\u001b[39m │ \u001b[90mInt64\u001b[39m │\n",
       "├─────┼────────┼───────┤\n",
       "│ 1   │ 1      │ 3     │\n",
       "⋮\n",
       "Last Group (1 row): id = 5\n",
       "│ Row │ id     │ x     │\n",
       "│     │ \u001b[90mInt64⍰\u001b[39m │ \u001b[90mInt64\u001b[39m │\n",
       "├─────┼────────┼───────┤\n",
       "│ 1   │ 5      │ 2     │"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupby(x, :id, sort=true, skipmissing=true) # but we can change it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>v_mean</th></tr><tr><th></th><th>Char</th><th>Float64</th></tr></thead><tbody><p>4 rows × 2 columns</p><tr><th>1</th><td>'a'</td><td>0.422648</td></tr><tr><th>2</th><td>'d'</td><td>0.49053</td></tr><tr><th>3</th><td>'c'</td><td>0.512698</td></tr><tr><th>4</th><td>'b'</td><td>0.476415</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& id & v\\_mean\\\\\n",
       "\t\\hline\n",
       "\t& Char & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 'a' & 0.422648 \\\\\n",
       "\t2 & 'd' & 0.49053 \\\\\n",
       "\t3 & 'c' & 0.512698 \\\\\n",
       "\t4 & 'b' & 0.476415 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "4×2 DataFrame\n",
       "│ Row │ id   │ v_mean   │\n",
       "│     │ \u001b[90mChar\u001b[39m │ \u001b[90mFloat64\u001b[39m  │\n",
       "├─────┼──────┼──────────┤\n",
       "│ 1   │ 'a'  │ 0.422648 │\n",
       "│ 2   │ 'd'  │ 0.49053  │\n",
       "│ 3   │ 'c'  │ 0.512698 │\n",
       "│ 4   │ 'b'  │ 0.476415 │"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "using Statistics\n",
    "x = DataFrame(id=rand('a':'d', 100), v=rand(100));\n",
    "by(x, :id, :v=>mean) # apply a function to each group of a data frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>v_mean</th></tr><tr><th></th><th>Char</th><th>Float64</th></tr></thead><tbody><p>4 rows × 2 columns</p><tr><th>1</th><td>'a'</td><td>0.422648</td></tr><tr><th>2</th><td>'b'</td><td>0.476415</td></tr><tr><th>3</th><td>'c'</td><td>0.512698</td></tr><tr><th>4</th><td>'d'</td><td>0.49053</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& id & v\\_mean\\\\\n",
       "\t\\hline\n",
       "\t& Char & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 'a' & 0.422648 \\\\\n",
       "\t2 & 'b' & 0.476415 \\\\\n",
       "\t3 & 'c' & 0.512698 \\\\\n",
       "\t4 & 'd' & 0.49053 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "4×2 DataFrame\n",
       "│ Row │ id   │ v_mean   │\n",
       "│     │ \u001b[90mChar\u001b[39m │ \u001b[90mFloat64\u001b[39m  │\n",
       "├─────┼──────┼──────────┤\n",
       "│ 1   │ 'a'  │ 0.422648 │\n",
       "│ 2   │ 'b'  │ 0.476415 │\n",
       "│ 3   │ 'c'  │ 0.512698 │\n",
       "│ 4   │ 'd'  │ 0.49053  │"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "by(x, :id, :v=>mean, sort=true) # we can sort the output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>res</th></tr><tr><th></th><th>Char</th><th>Float64</th></tr></thead><tbody><p>4 rows × 2 columns</p><tr><th>1</th><td>'a'</td><td>0.422648</td></tr><tr><th>2</th><td>'d'</td><td>0.49053</td></tr><tr><th>3</th><td>'c'</td><td>0.512698</td></tr><tr><th>4</th><td>'b'</td><td>0.476415</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& id & res\\\\\n",
       "\t\\hline\n",
       "\t& Char & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 'a' & 0.422648 \\\\\n",
       "\t2 & 'd' & 0.49053 \\\\\n",
       "\t3 & 'c' & 0.512698 \\\\\n",
       "\t4 & 'b' & 0.476415 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "4×2 DataFrame\n",
       "│ Row │ id   │ res      │\n",
       "│     │ \u001b[90mChar\u001b[39m │ \u001b[90mFloat64\u001b[39m  │\n",
       "├─────┼──────┼──────────┤\n",
       "│ 1   │ 'a'  │ 0.422648 │\n",
       "│ 2   │ 'd'  │ 0.49053  │\n",
       "│ 3   │ 'c'  │ 0.512698 │\n",
       "│ 4   │ 'b'  │ 0.476415 │"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "by(x, :id, res=:v=>mean) # this way we can set a name for a column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>res1</th><th>res2</th></tr><tr><th></th><th>Char</th><th>Float64</th><th>Float64</th></tr></thead><tbody><p>4 rows × 3 columns</p><tr><th>1</th><td>'a'</td><td>0.422648</td><td>10.5662</td></tr><tr><th>2</th><td>'d'</td><td>0.49053</td><td>12.2632</td></tr><tr><th>3</th><td>'c'</td><td>0.512698</td><td>14.3555</td></tr><tr><th>4</th><td>'b'</td><td>0.476415</td><td>10.4811</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & res1 & res2\\\\\n",
       "\t\\hline\n",
       "\t& Char & Float64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 'a' & 0.422648 & 10.5662 \\\\\n",
       "\t2 & 'd' & 0.49053 & 12.2632 \\\\\n",
       "\t3 & 'c' & 0.512698 & 14.3555 \\\\\n",
       "\t4 & 'b' & 0.476415 & 10.4811 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "4×3 DataFrame\n",
       "│ Row │ id   │ res1     │ res2    │\n",
       "│     │ \u001b[90mChar\u001b[39m │ \u001b[90mFloat64\u001b[39m  │ \u001b[90mFloat64\u001b[39m │\n",
       "├─────┼──────┼──────────┼─────────┤\n",
       "│ 1   │ 'a'  │ 0.422648 │ 10.5662 │\n",
       "│ 2   │ 'd'  │ 0.49053  │ 12.2632 │\n",
       "│ 3   │ 'c'  │ 0.512698 │ 14.3555 │\n",
       "│ 4   │ 'b'  │ 0.476415 │ 10.4811 │"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "by(x, :id, res1=:v=>mean, res2=:v=>sum) # you can have multiple operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>x1_sum</th><th>x2_sum</th></tr><tr><th></th><th>Char</th><th>Float64</th><th>Float64</th></tr></thead><tbody><p>4 rows × 3 columns</p><tr><th>1</th><td>'a'</td><td>14.607</td><td>13.2739</td></tr><tr><th>2</th><td>'b'</td><td>13.7696</td><td>12.2106</td></tr><tr><th>3</th><td>'d'</td><td>14.6563</td><td>13.3629</td></tr><tr><th>4</th><td>'c'</td><td>8.99698</td><td>8.32711</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & x1\\_sum & x2\\_sum\\\\\n",
       "\t\\hline\n",
       "\t& Char & Float64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 'a' & 14.607 & 13.2739 \\\\\n",
       "\t2 & 'b' & 13.7696 & 12.2106 \\\\\n",
       "\t3 & 'd' & 14.6563 & 13.3629 \\\\\n",
       "\t4 & 'c' & 8.99698 & 8.32711 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "4×3 DataFrame\n",
       "│ Row │ id   │ x1_sum  │ x2_sum  │\n",
       "│     │ \u001b[90mChar\u001b[39m │ \u001b[90mFloat64\u001b[39m │ \u001b[90mFloat64\u001b[39m │\n",
       "├─────┼──────┼─────────┼─────────┤\n",
       "│ 1   │ 'a'  │ 14.607  │ 13.2739 │\n",
       "│ 2   │ 'b'  │ 13.7696 │ 12.2106 │\n",
       "│ 3   │ 'd'  │ 14.6563 │ 13.3629 │\n",
       "│ 4   │ 'c'  │ 8.99698 │ 8.32711 │"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = DataFrame(id=rand('a':'d', 100), x1=rand(100), x2=rand(100))\n",
    "aggregate(x, :id, sum) # apply a function over all columns of a data frame in groups given by id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>x1_sum</th><th>x2_sum</th></tr><tr><th></th><th>Char</th><th>Float64</th><th>Float64</th></tr></thead><tbody><p>4 rows × 3 columns</p><tr><th>1</th><td>'a'</td><td>14.607</td><td>13.2739</td></tr><tr><th>2</th><td>'b'</td><td>13.7696</td><td>12.2106</td></tr><tr><th>3</th><td>'c'</td><td>8.99698</td><td>8.32711</td></tr><tr><th>4</th><td>'d'</td><td>14.6563</td><td>13.3629</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & x1\\_sum & x2\\_sum\\\\\n",
       "\t\\hline\n",
       "\t& Char & Float64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 'a' & 14.607 & 13.2739 \\\\\n",
       "\t2 & 'b' & 13.7696 & 12.2106 \\\\\n",
       "\t3 & 'c' & 8.99698 & 8.32711 \\\\\n",
       "\t4 & 'd' & 14.6563 & 13.3629 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "4×3 DataFrame\n",
       "│ Row │ id   │ x1_sum  │ x2_sum  │\n",
       "│     │ \u001b[90mChar\u001b[39m │ \u001b[90mFloat64\u001b[39m │ \u001b[90mFloat64\u001b[39m │\n",
       "├─────┼──────┼─────────┼─────────┤\n",
       "│ 1   │ 'a'  │ 14.607  │ 13.2739 │\n",
       "│ 2   │ 'b'  │ 13.7696 │ 12.2106 │\n",
       "│ 3   │ 'c'  │ 8.99698 │ 8.32711 │\n",
       "│ 4   │ 'd'  │ 14.6563 │ 13.3629 │"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aggregate(x, :id, sum, sort=true) # also can be sorted"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A new feature is `mapcols` convinience function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>x1</th><th>x2</th><th>x3</th><th>x4</th><th>x5</th></tr><tr><th></th><th>Float64</th><th>Float64</th><th>Float64</th><th>Float64</th><th>Float64</th></tr></thead><tbody><p>3 rows × 5 columns</p><tr><th>1</th><td>0.794226</td><td>0.573936</td><td>0.0182556</td><td>0.970179</td><td>0.152322</td></tr><tr><th>2</th><td>0.37604</td><td>0.667302</td><td>0.0819991</td><td>0.785111</td><td>0.57178</td></tr><tr><th>3</th><td>0.0236083</td><td>0.474579</td><td>0.699137</td><td>0.653099</td><td>0.407289</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccccc}\n",
       "\t& x1 & x2 & x3 & x4 & x5\\\\\n",
       "\t\\hline\n",
       "\t& Float64 & Float64 & Float64 & Float64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 0.794226 & 0.573936 & 0.0182556 & 0.970179 & 0.152322 \\\\\n",
       "\t2 & 0.37604 & 0.667302 & 0.0819991 & 0.785111 & 0.57178 \\\\\n",
       "\t3 & 0.0236083 & 0.474579 & 0.699137 & 0.653099 & 0.407289 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "3×5 DataFrame\n",
       "│ Row │ x1        │ x2       │ x3        │ x4       │ x5       │\n",
       "│     │ \u001b[90mFloat64\u001b[39m   │ \u001b[90mFloat64\u001b[39m  │ \u001b[90mFloat64\u001b[39m   │ \u001b[90mFloat64\u001b[39m  │ \u001b[90mFloat64\u001b[39m  │\n",
       "├─────┼───────────┼──────────┼───────────┼──────────┼──────────┤\n",
       "│ 1   │ 0.794226  │ 0.573936 │ 0.0182556 │ 0.970179 │ 0.152322 │\n",
       "│ 2   │ 0.37604   │ 0.667302 │ 0.0819991 │ 0.785111 │ 0.57178  │\n",
       "│ 3   │ 0.0236083 │ 0.474579 │ 0.699137  │ 0.653099 │ 0.407289 │"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = DataFrame(rand(3, 5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>x1</th><th>x2</th><th>x3</th><th>x4</th><th>x5</th></tr><tr><th></th><th>Float64</th><th>Float64</th><th>Float64</th><th>Float64</th><th>Float64</th></tr></thead><tbody><p>1 rows × 5 columns</p><tr><th>1</th><td>0.397958</td><td>0.571939</td><td>0.266464</td><td>0.802796</td><td>0.37713</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccccc}\n",
       "\t& x1 & x2 & x3 & x4 & x5\\\\\n",
       "\t\\hline\n",
       "\t& Float64 & Float64 & Float64 & Float64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 0.397958 & 0.571939 & 0.266464 & 0.802796 & 0.37713 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "1×5 DataFrame\n",
       "│ Row │ x1       │ x2       │ x3       │ x4       │ x5      │\n",
       "│     │ \u001b[90mFloat64\u001b[39m  │ \u001b[90mFloat64\u001b[39m  │ \u001b[90mFloat64\u001b[39m  │ \u001b[90mFloat64\u001b[39m  │ \u001b[90mFloat64\u001b[39m │\n",
       "├─────┼──────────┼──────────┼──────────┼──────────┼─────────┤\n",
       "│ 1   │ 0.397958 │ 0.571939 │ 0.266464 │ 0.802796 │ 0.37713 │"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mapcols(mean, x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5-element Array{Float64,1}:\n",
       " 0.39795790415870774\n",
       " 0.5719388527626287 \n",
       " 0.2664640269034721 \n",
       " 0.802796253753856  \n",
       " 0.3771303884586173 "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "map(mean, eachcol(x, false)) # map a function over each column and return a vector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "x1: 0.39795790415870774\n",
      "x2: 0.5719388527626287\n",
      "x3: 0.2664640269034721\n",
      "x4: 0.802796253753856\n",
      "x5: 0.3771303884586173\n"
     ]
    }
   ],
   "source": [
    "foreach(c -> println(c[1], \": \", mean(c[2])), eachcol(x, true)) # an iteration returns a Pair with column name and values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3-element Array{Float64,1}:\n",
       " 1.3838231125266938\n",
       " 0.5635224473407595\n",
       " 0.0497457748145321"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "map(r -> r.x1/r.x2, eachrow(x)) # now the returned value is DataFrameRow which works similarly to a one-row DataFrame"
   ]
  }
 ],
 "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
}