Tutorial_33_DataFrames.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DataFrames\n",
    "\n",
    "This notebook illustrates the *basics* of [DataFrames.jl](https://github.com/JuliaData/DataFrames.jl). There are many packages that work with such data frames, for instance, [TidierData.jl](https://github.com/TidierOrg/TidierData.jl) which sticks close to R syntax. To do R style regressions, combine with [StatsModels.jl](https://github.com/JuliaStats/StatsModels.jl) and [GLM.jl](https://github.com/JuliaStats/GLM.jl).\n",
    "\n",
    "As an alternative to `DataFrames.jl`, you may want to condsider [InMemoryDatasets.jl](https://github.com/sl-solution/InMemoryDatasets.jl)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "using Printf, Dates, Statistics, DelimitedFiles, DataFrames, CSV\n",
    "include(\"jlFiles/printmat.jl\");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Loading Some Data with CSV.jl\n",
    "\n",
    "The [CSV.jl](https://github.com/JuliaData/CSV.jl) package is very powerful - and also quick for large files. \n",
    "\n",
    "(You could also use `DelimitedFiles.jl`, but it is not as well integrated with the `DataFrames.jl` as `CSV.jl`.) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "the raw contents of Data/dfData1.csv:\n",
      "Date,          id,         B,      C,   D,           E     \n",
      "01/05/2019,     1,      9.998,   0.000, The,        true\n",
      "01/05/2019,     2,      9.994,   0.037, highway,    true\n",
      "01/05/2019,     3,     10.044,   0.092, is,         true\n",
      "02/05/2019,     1,     10.061,   0.098, for,        true\n",
      "02/05/2019,     2,     10.076,   0.078, gamblers,   true\n",
      "02/05/2019,     3,     10.064,   0.061, Better,     false\n"
     ]
    }
   ],
   "source": [
    "println(\"the raw contents of Data/dfData1.csv:\")\n",
    "println(read(\"Data/dfData1.csv\",String))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To use `CSV.jl` to create a dataframe on the fly, do as in the cell below. Use `normalizenames` to get names that can be used in Julia as variables names and specify the `dateformat` used in the csv file (to convert to proper Julia dates)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[1m Row \u001b[0m│\u001b[1m Date       \u001b[0m\u001b[1m id    \u001b[0m\u001b[1m B       \u001b[0m\u001b[1m C       \u001b[0m\u001b[1m D         \u001b[0m\u001b[1m E     \u001b[0m\n",
      "     │\u001b[90m Date       \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m String15  \u001b[0m\u001b[90m Bool  \u001b[0m\n",
      "─────┼───────────────────────────────────────────────────────\n",
      "   1 │ 2019-05-01      1    9.998    0.0     The        true\n",
      "   2 │ 2019-05-01      2    9.994    0.037   highway    true\n",
      "   3 │ 2019-05-01      3   10.044    0.092   is         true\n",
      "   4 │ 2019-05-02      1   10.061    0.098   for        true\n",
      "   5 │ 2019-05-02      2   10.076    0.078   gamblers   true\n",
      "   6 │ 2019-05-02      3   10.064    0.061   Better    false"
     ]
    }
   ],
   "source": [
    "df1 = CSV.read(\"Data/dfData1.csv\",DataFrame,normalizenames=true,dateformat=\"dd/mm/yyyy\")\n",
    "show(df1;allrows=true,summary=false)    #to explicitly print the df, can be configured"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Picking out Rows and Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6-element Vector{Float64}:\n",
       "  9.998\n",
       "  9.994\n",
       " 10.044\n",
       " 10.061\n",
       " 10.076\n",
       " 10.064"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1[:,:B]     #create a traditional Julia vector\n",
    "#df1.B        #works too   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×6 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">Date</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">B</th><th style = \"text-align: left;\">C</th><th style = \"text-align: left;\">D</th><th style = \"text-align: left;\">E</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Bool\" style = \"text-align: left;\">Bool</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">9.994</td><td style = \"text-align: right;\">0.037</td><td style = \"text-align: left;\"> highway</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">10.044</td><td style = \"text-align: right;\">0.092</td><td style = \"text-align: left;\"> is</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">10.076</td><td style = \"text-align: right;\">0.078</td><td style = \"text-align: left;\"> gamblers</td><td style = \"text-align: right;\">true</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccccc}\n",
       "\t& Date & id & B & C & D & E\\\\\n",
       "\t\\hline\n",
       "\t& Date & Int64 & Float64 & Float64 & String15 & Bool\\\\\n",
       "\t\\hline\n",
       "\t1 & 2019-05-01 & 2 & 9.994 & 0.037 &  highway & 1 \\\\\n",
       "\t2 & 2019-05-01 & 3 & 10.044 & 0.092 &  is & 1 \\\\\n",
       "\t3 & 2019-05-02 & 2 & 10.076 & 0.078 &  gamblers & 1 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×6 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m Date       \u001b[0m\u001b[1m id    \u001b[0m\u001b[1m B       \u001b[0m\u001b[1m C       \u001b[0m\u001b[1m D         \u001b[0m\u001b[1m E    \u001b[0m\n",
       "     │\u001b[90m Date       \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m String15  \u001b[0m\u001b[90m Bool \u001b[0m\n",
       "─────┼──────────────────────────────────────────────────────\n",
       "   1 │ 2019-05-01      2    9.994    0.037   highway   true\n",
       "   2 │ 2019-05-01      3   10.044    0.092   is        true\n",
       "   3 │ 2019-05-02      2   10.076    0.078   gamblers  true"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1b = df1[(df1.id.>=2) .& (df1.E.==true), :]         #create new df by picking out some rows, prints autmatically"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\"Date\",\"B\"\n",
      "\"2019-05-01\",9.994\n",
      "\"2019-05-01\",10.044\n",
      "\"2019-05-02\",10.061\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">Date</th><th style = \"text-align: left;\">B</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">9.994</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">10.044</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">10.061</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df1b = df1[2:4,[:Date,:B]]  #creates a new df by picking out some rows and cols\n",
    "show(stdout,MIME(\"text/csv\"),df1b)                               #explicit printing, here as csv\n",
    "show(stdout,MIME(\"text/html\"),df1b;summary=false,eltypes=false)  #and as html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Changing a DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[1m6×6 DataFrame\u001b[0m\n",
      "\u001b[1m Row \u001b[0m│\u001b[1m Date       \u001b[0m\u001b[1m id    \u001b[0m\u001b[1m B       \u001b[0m\u001b[1m C       \u001b[0m\u001b[1m D         \u001b[0m\u001b[1m E     \u001b[0m\n",
      "     │\u001b[90m Date       \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m String15  \u001b[0m\u001b[90m Bool  \u001b[0m\n",
      "─────┼───────────────────────────────────────────────────────\n",
      "   1 │ 2019-05-01      1    9.998    0.0     The        true\n",
      "   2 │ 2019-05-01      2    9.994  100.0     highway    true\n",
      "   3 │ 2019-05-01      3   10.044  101.0     is         true\n",
      "   4 │ 2019-05-02      1   10.061    0.098   for        true\n",
      "   5 │ 2019-05-02      2   10.076    0.078   gamblers   true\n",
      "   6 │ 2019-05-02      3   10.064    0.061   Better    false"
     ]
    }
   ],
   "source": [
    "df1[2:3,:C] = [100,101]               #changing some values in :C\n",
    "show(df1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>6×6 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">Date</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">B</th><th style = \"text-align: left;\">C</th><th style = \"text-align: left;\">BabyBlue</th><th style = \"text-align: left;\">E</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Bool\" style = \"text-align: left;\">Bool</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">9.998</td><td style = \"text-align: right;\">0.0</td><td style = \"text-align: left;\"> The</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">9.994</td><td style = \"text-align: right;\">100.0</td><td style = \"text-align: left;\"> highway</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">10.044</td><td style = \"text-align: right;\">101.0</td><td style = \"text-align: left;\"> is</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">10.061</td><td style = \"text-align: right;\">0.098</td><td style = \"text-align: left;\"> for</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">10.076</td><td style = \"text-align: right;\">0.078</td><td style = \"text-align: left;\"> gamblers</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">10.064</td><td style = \"text-align: right;\">0.061</td><td style = \"text-align: left;\"> Better</td><td style = \"text-align: right;\">false</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccccc}\n",
       "\t& Date & id & B & C & BabyBlue & E\\\\\n",
       "\t\\hline\n",
       "\t& Date & Int64 & Float64 & Float64 & String15 & Bool\\\\\n",
       "\t\\hline\n",
       "\t1 & 2019-05-01 & 1 & 9.998 & 0.0 &  The & 1 \\\\\n",
       "\t2 & 2019-05-01 & 2 & 9.994 & 100.0 &  highway & 1 \\\\\n",
       "\t3 & 2019-05-01 & 3 & 10.044 & 101.0 &  is & 1 \\\\\n",
       "\t4 & 2019-05-02 & 1 & 10.061 & 0.098 &  for & 1 \\\\\n",
       "\t5 & 2019-05-02 & 2 & 10.076 & 0.078 &  gamblers & 1 \\\\\n",
       "\t6 & 2019-05-02 & 3 & 10.064 & 0.061 &  Better & 0 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×6 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m Date       \u001b[0m\u001b[1m id    \u001b[0m\u001b[1m B       \u001b[0m\u001b[1m C       \u001b[0m\u001b[1m BabyBlue  \u001b[0m\u001b[1m E     \u001b[0m\n",
       "     │\u001b[90m Date       \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m String15  \u001b[0m\u001b[90m Bool  \u001b[0m\n",
       "─────┼───────────────────────────────────────────────────────\n",
       "   1 │ 2019-05-01      1    9.998    0.0     The        true\n",
       "   2 │ 2019-05-01      2    9.994  100.0     highway    true\n",
       "   3 │ 2019-05-01      3   10.044  101.0     is         true\n",
       "   4 │ 2019-05-02      1   10.061    0.098   for        true\n",
       "   5 │ 2019-05-02      2   10.076    0.078   gamblers   true\n",
       "   6 │ 2019-05-02      3   10.064    0.061   Better    false"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rename!(df1,:D => :BabyBlue)          #renaming a column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[1m6×2 DataFrame\u001b[0m\n",
      "\u001b[1m Row \u001b[0m│\u001b[1m BabyBlue  \u001b[0m\u001b[1m C       \u001b[0m\n",
      "     │\u001b[90m String15  \u001b[0m\u001b[90m Float64 \u001b[0m\n",
      "─────┼────────────────────\n",
      "   1 │  The         0.0\n",
      "   2 │  highway   100.0\n",
      "   3 │  is        101.0\n",
      "   4 │  for         0.098\n",
      "   5 │  gamblers    0.078\n",
      "   6 │  Better      0.061\n",
      "\n",
      "\u001b[1m6×4 DataFrame\u001b[0m\n",
      "\u001b[1m Row \u001b[0m│\u001b[1m Date       \u001b[0m\u001b[1m id    \u001b[0m\u001b[1m B       \u001b[0m\u001b[1m E     \u001b[0m\n",
      "     │\u001b[90m Date       \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Bool  \u001b[0m\n",
      "─────┼───────────────────────────────────\n",
      "   1 │ 2019-05-01      1    9.998   true\n",
      "   2 │ 2019-05-01      2    9.994   true\n",
      "   3 │ 2019-05-01      3   10.044   true\n",
      "   4 │ 2019-05-02      1   10.061   true\n",
      "   5 │ 2019-05-02      2   10.076   true\n",
      "   6 │ 2019-05-02      3   10.064  false"
     ]
    }
   ],
   "source": [
    "df1c = select(df1,[:BabyBlue,:C])              #new df\n",
    "show(df1c)\n",
    "\n",
    "println(\"\\n\")\n",
    "df1b = select(df1,Not([:BabyBlue,:C]))         #new df, select and Not: all cols except those\n",
    "show(df1b)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Converting to and from a (traditional) Julia Matrix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6×3 Matrix{Float64}:\n",
       " 1.0   9.998  1.0\n",
       " 2.0   9.994  1.0\n",
       " 3.0  10.044  1.0\n",
       " 1.0  10.061  1.0\n",
       " 2.0  10.076  1.0\n",
       " 3.0  10.064  0.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arraydata = Matrix(df1[:,[:id,:B,:E]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>6×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">x1</th><th style = \"text-align: left;\">x2</th><th style = \"text-align: left;\">x3</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1.0</td><td style = \"text-align: right;\">9.998</td><td style = \"text-align: right;\">1.0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2.0</td><td style = \"text-align: right;\">9.994</td><td style = \"text-align: right;\">1.0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">3.0</td><td style = \"text-align: right;\">10.044</td><td style = \"text-align: right;\">1.0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: right;\">1.0</td><td style = \"text-align: right;\">10.061</td><td style = \"text-align: right;\">1.0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: right;\">2.0</td><td style = \"text-align: right;\">10.076</td><td style = \"text-align: right;\">1.0</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: right;\">3.0</td><td style = \"text-align: right;\">10.064</td><td style = \"text-align: right;\">0.0</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& x1 & x2 & x3\\\\\n",
       "\t\\hline\n",
       "\t& Float64 & Float64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1.0 & 9.998 & 1.0 \\\\\n",
       "\t2 & 2.0 & 9.994 & 1.0 \\\\\n",
       "\t3 & 3.0 & 10.044 & 1.0 \\\\\n",
       "\t4 & 1.0 & 10.061 & 1.0 \\\\\n",
       "\t5 & 2.0 & 10.076 & 1.0 \\\\\n",
       "\t6 & 3.0 & 10.064 & 0.0 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m x1      \u001b[0m\u001b[1m x2      \u001b[0m\u001b[1m x3      \u001b[0m\n",
       "     │\u001b[90m Float64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Float64 \u001b[0m\n",
       "─────┼───────────────────────────\n",
       "   1 │     1.0    9.998      1.0\n",
       "   2 │     2.0    9.994      1.0\n",
       "   3 │     3.0   10.044      1.0\n",
       "   4 │     1.0   10.061      1.0\n",
       "   5 │     2.0   10.076      1.0\n",
       "   6 │     3.0   10.064      0.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_from_matrix = DataFrame(arraydata,:auto)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reshuffling a DataFrame (Groupby)\n",
    "\n",
    "We now create a group for each id. These groups can be referred to as `dataG1[key]`.\n",
    "\n",
    "We list the keys below. For instance `(id=2,)` is one of the keys. This is a tuple with a single element (similar to `(x,)`) where the element is `id=2`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<p><b>GroupedDataFrame with 3 groups based on key: id</b></p><div><div style = \"float: left;\"><span>First Group (2 rows): id = 1</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">Date</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">B</th><th style = \"text-align: left;\">C</th><th style = \"text-align: left;\">BabyBlue</th><th style = \"text-align: left;\">E</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Bool\" style = \"text-align: left;\">Bool</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">9.998</td><td style = \"text-align: right;\">0.0</td><td style = \"text-align: left;\"> The</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">10.061</td><td style = \"text-align: right;\">0.098</td><td style = \"text-align: left;\"> for</td><td style = \"text-align: right;\">true</td></tr></tbody></table></div><p>&vellip;</p><div><div style = \"float: left;\"><span>Last Group (2 rows): id = 3</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">Date</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">B</th><th style = \"text-align: left;\">C</th><th style = \"text-align: left;\">BabyBlue</th><th style = \"text-align: left;\">E</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Bool\" style = \"text-align: left;\">Bool</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">10.044</td><td style = \"text-align: right;\">101.0</td><td style = \"text-align: left;\"> is</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">10.064</td><td style = \"text-align: right;\">0.061</td><td style = \"text-align: left;\"> Better</td><td style = \"text-align: right;\">false</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "GroupedDataFrame with 3 groups based on key: id\n",
       "\n",
       "First Group (2 rows): id = 1\n",
       "\n",
       "\\begin{tabular}{r|cccccc}\n",
       "\t& Date & id & B & C & BabyBlue & E\\\\\n",
       "\t\\hline\n",
       "\t& Date & Int64 & Float64 & Float64 & String15 & Bool\\\\\n",
       "\t\\hline\n",
       "\t1 & 2019-05-01 & 1 & 9.998 & 0.0 &  The & 1 \\\\\n",
       "\t2 & 2019-05-02 & 1 & 10.061 & 0.098 &  for & 1 \\\\\n",
       "\\end{tabular}\n",
       "\n",
       "$\\dots$\n",
       "\n",
       "Last Group (2 rows): id = 3\n",
       "\n",
       "\\begin{tabular}{r|cccccc}\n",
       "\t& Date & id & B & C & BabyBlue & E\\\\\n",
       "\t\\hline\n",
       "\t& Date & Int64 & Float64 & Float64 & String15 & Bool\\\\\n",
       "\t\\hline\n",
       "\t1 & 2019-05-01 & 3 & 10.044 & 101.0 &  is & 1 \\\\\n",
       "\t2 & 2019-05-02 & 3 & 10.064 & 0.061 &  Better & 0 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "GroupedDataFrame with 3 groups based on key: id\n",
       "First Group (2 rows): id = 1\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m Date       \u001b[0m\u001b[1m id    \u001b[0m\u001b[1m B       \u001b[0m\u001b[1m C       \u001b[0m\u001b[1m BabyBlue \u001b[0m\u001b[1m E    \u001b[0m\n",
       "     │\u001b[90m Date       \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m String15 \u001b[0m\u001b[90m Bool \u001b[0m\n",
       "─────┼─────────────────────────────────────────────────────\n",
       "   1 │ 2019-05-01      1    9.998    0.0     The      true\n",
       "   2 │ 2019-05-02      1   10.061    0.098   for      true\n",
       "⋮\n",
       "Last Group (2 rows): id = 3\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m Date       \u001b[0m\u001b[1m id    \u001b[0m\u001b[1m B       \u001b[0m\u001b[1m C       \u001b[0m\u001b[1m BabyBlue \u001b[0m\u001b[1m E     \u001b[0m\n",
       "     │\u001b[90m Date       \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m String15 \u001b[0m\u001b[90m Bool  \u001b[0m\n",
       "─────┼──────────────────────────────────────────────────────\n",
       "   1 │ 2019-05-01      3   10.044  101.0     is        true\n",
       "   2 │ 2019-05-02      3   10.064    0.061   Better   false"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataG1 = groupby(df1,:id)    #grouped by id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>6×6 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">Date</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">B</th><th style = \"text-align: left;\">C</th><th style = \"text-align: left;\">BabyBlue</th><th style = \"text-align: left;\">E</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Bool\" style = \"text-align: left;\">Bool</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">9.998</td><td style = \"text-align: right;\">0.0</td><td style = \"text-align: left;\"> The</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">10.061</td><td style = \"text-align: right;\">0.098</td><td style = \"text-align: left;\"> for</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">9.994</td><td style = \"text-align: right;\">100.0</td><td style = \"text-align: left;\"> highway</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">10.076</td><td style = \"text-align: right;\">0.078</td><td style = \"text-align: left;\"> gamblers</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">10.044</td><td style = \"text-align: right;\">101.0</td><td style = \"text-align: left;\"> is</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">10.064</td><td style = \"text-align: right;\">0.061</td><td style = \"text-align: left;\"> Better</td><td style = \"text-align: right;\">false</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccccc}\n",
       "\t& Date & id & B & C & BabyBlue & E\\\\\n",
       "\t\\hline\n",
       "\t& Date & Int64 & Float64 & Float64 & String15 & Bool\\\\\n",
       "\t\\hline\n",
       "\t1 & 2019-05-01 & 1 & 9.998 & 0.0 &  The & 1 \\\\\n",
       "\t2 & 2019-05-02 & 1 & 10.061 & 0.098 &  for & 1 \\\\\n",
       "\t3 & 2019-05-01 & 2 & 9.994 & 100.0 &  highway & 1 \\\\\n",
       "\t4 & 2019-05-02 & 2 & 10.076 & 0.078 &  gamblers & 1 \\\\\n",
       "\t5 & 2019-05-01 & 3 & 10.044 & 101.0 &  is & 1 \\\\\n",
       "\t6 & 2019-05-02 & 3 & 10.064 & 0.061 &  Better & 0 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×6 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m Date       \u001b[0m\u001b[1m id    \u001b[0m\u001b[1m B       \u001b[0m\u001b[1m C       \u001b[0m\u001b[1m BabyBlue  \u001b[0m\u001b[1m E     \u001b[0m\n",
       "     │\u001b[90m Date       \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m String15  \u001b[0m\u001b[90m Bool  \u001b[0m\n",
       "─────┼───────────────────────────────────────────────────────\n",
       "   1 │ 2019-05-01      1    9.998    0.0     The        true\n",
       "   2 │ 2019-05-02      1   10.061    0.098   for        true\n",
       "   3 │ 2019-05-01      2    9.994  100.0     highway    true\n",
       "   4 │ 2019-05-02      2   10.076    0.078   gamblers   true\n",
       "   5 │ 2019-05-01      3   10.044  101.0     is         true\n",
       "   6 │ 2019-05-02      3   10.064    0.061   Better    false"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataG2 = vcat(dataG1...)         #put together again, but now id=1 first, then id=2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The keys of the grouped DataFrame are: \n",
      "GroupKey: (id = 1,)\n",
      "GroupKey: (id = 2,)\n",
      "GroupKey: (id = 3,)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "println(\"The keys of the grouped DataFrame are: \")\n",
    "printmat(DataFrames.GroupKeys(dataG1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>2×6 SubDataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">Date</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">B</th><th style = \"text-align: left;\">C</th><th style = \"text-align: left;\">BabyBlue</th><th style = \"text-align: left;\">E</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Bool\" style = \"text-align: left;\">Bool</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">9.994</td><td style = \"text-align: right;\">100.0</td><td style = \"text-align: left;\"> highway</td><td style = \"text-align: right;\">true</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">10.076</td><td style = \"text-align: right;\">0.078</td><td style = \"text-align: left;\"> gamblers</td><td style = \"text-align: right;\">true</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccccc}\n",
       "\t& Date & id & B & C & BabyBlue & E\\\\\n",
       "\t\\hline\n",
       "\t& Date & Int64 & Float64 & Float64 & String15 & Bool\\\\\n",
       "\t\\hline\n",
       "\t1 & 2019-05-01 & 2 & 9.994 & 100.0 &  highway & 1 \\\\\n",
       "\t2 & 2019-05-02 & 2 & 10.076 & 0.078 &  gamblers & 1 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m2×6 SubDataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m Date       \u001b[0m\u001b[1m id    \u001b[0m\u001b[1m B       \u001b[0m\u001b[1m C       \u001b[0m\u001b[1m BabyBlue  \u001b[0m\u001b[1m E    \u001b[0m\n",
       "     │\u001b[90m Date       \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m String15  \u001b[0m\u001b[90m Bool \u001b[0m\n",
       "─────┼──────────────────────────────────────────────────────\n",
       "   1 │ 2019-05-01      2    9.994  100.0     highway   true\n",
       "   2 │ 2019-05-02      2   10.076    0.078   gamblers  true"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataG1[(id=2,)]       #the group for (id=2,), "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging DataFrames\n",
    "\n",
    "It is possible to stack DataFrames, if they are comformable.\n",
    "\n",
    "More generally, we can use one of the many `join` functions, for instance, `innerjoin()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>6×7 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">Date</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">B</th><th style = \"text-align: left;\">C</th><th style = \"text-align: left;\">BabyBlue</th><th style = \"text-align: left;\">E</th><th style = \"text-align: left;\">ff</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Bool\" style = \"text-align: left;\">Bool</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">9.998</td><td style = \"text-align: right;\">0.0</td><td style = \"text-align: left;\"> The</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">1</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">9.994</td><td style = \"text-align: right;\">100.0</td><td style = \"text-align: left;\"> highway</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">2</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">10.044</td><td style = \"text-align: right;\">101.0</td><td style = \"text-align: left;\"> is</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">3</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">10.061</td><td style = \"text-align: right;\">0.098</td><td style = \"text-align: left;\"> for</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">4</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">10.076</td><td style = \"text-align: right;\">0.078</td><td style = \"text-align: left;\"> gamblers</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">5</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">10.064</td><td style = \"text-align: right;\">0.061</td><td style = \"text-align: left;\"> Better</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">6</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccccccc}\n",
       "\t& Date & id & B & C & BabyBlue & E & ff\\\\\n",
       "\t\\hline\n",
       "\t& Date & Int64 & Float64 & Float64 & String15 & Bool & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 2019-05-01 & 1 & 9.998 & 0.0 &  The & 1 & 1 \\\\\n",
       "\t2 & 2019-05-01 & 2 & 9.994 & 100.0 &  highway & 1 & 2 \\\\\n",
       "\t3 & 2019-05-01 & 3 & 10.044 & 101.0 &  is & 1 & 3 \\\\\n",
       "\t4 & 2019-05-02 & 1 & 10.061 & 0.098 &  for & 1 & 4 \\\\\n",
       "\t5 & 2019-05-02 & 2 & 10.076 & 0.078 &  gamblers & 1 & 5 \\\\\n",
       "\t6 & 2019-05-02 & 3 & 10.064 & 0.061 &  Better & 0 & 6 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×7 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m Date       \u001b[0m\u001b[1m id    \u001b[0m\u001b[1m B       \u001b[0m\u001b[1m C       \u001b[0m\u001b[1m BabyBlue  \u001b[0m\u001b[1m E     \u001b[0m\u001b[1m ff    \u001b[0m\n",
       "     │\u001b[90m Date       \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m String15  \u001b[0m\u001b[90m Bool  \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────────────────────────────────────────────────────\n",
       "   1 │ 2019-05-01      1    9.998    0.0     The        true      1\n",
       "   2 │ 2019-05-01      2    9.994  100.0     highway    true      2\n",
       "   3 │ 2019-05-01      3   10.044  101.0     is         true      3\n",
       "   4 │ 2019-05-02      1   10.061    0.098   for        true      4\n",
       "   5 │ 2019-05-02      2   10.076    0.078   gamblers   true      5\n",
       "   6 │ 2019-05-02      3   10.064    0.061   Better    false      6"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = [df1 DataFrame(ff=1:6)]       #stacking 2 dataframes horizontally"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>6×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">Date</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">G</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">11</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">12</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">21</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">22</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">31</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">32</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& Date & id & G\\\\\n",
       "\t\\hline\n",
       "\t& Date & Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 2019-05-01 & 1 & 11 \\\\\n",
       "\t2 & 2019-05-02 & 1 & 12 \\\\\n",
       "\t3 & 2019-05-01 & 2 & 21 \\\\\n",
       "\t4 & 2019-05-02 & 2 & 22 \\\\\n",
       "\t5 & 2019-05-01 & 3 & 31 \\\\\n",
       "\t6 & 2019-05-02 & 3 & 32 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m Date       \u001b[0m\u001b[1m id    \u001b[0m\u001b[1m G     \u001b[0m\n",
       "     │\u001b[90m Date       \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────────────────\n",
       "   1 │ 2019-05-01      1     11\n",
       "   2 │ 2019-05-02      1     12\n",
       "   3 │ 2019-05-01      2     21\n",
       "   4 │ 2019-05-02      2     22\n",
       "   5 │ 2019-05-01      3     31\n",
       "   6 │ 2019-05-02      3     32"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "                       #loading another DataFrame, it has another order of observations\n",
    "df2 = CSV.read(\"Data/dfData2.csv\",DataFrame,normalizenames=true,dateformat=\"dd/mm/yyyy\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>6×7 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">Date</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">B</th><th style = \"text-align: left;\">C</th><th style = \"text-align: left;\">BabyBlue</th><th style = \"text-align: left;\">E</th><th style = \"text-align: left;\">G</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Date\" style = \"text-align: left;\">Date</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"String15\" style = \"text-align: left;\">String15</th><th title = \"Bool\" style = \"text-align: left;\">Bool</th><th title = \"Int64\" style = \"text-align: left;\">Int64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">9.998</td><td style = \"text-align: right;\">0.0</td><td style = \"text-align: left;\"> The</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">11</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">10.061</td><td style = \"text-align: right;\">0.098</td><td style = \"text-align: left;\"> for</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">12</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">9.994</td><td style = \"text-align: right;\">100.0</td><td style = \"text-align: left;\"> highway</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">21</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">4</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">10.076</td><td style = \"text-align: right;\">0.078</td><td style = \"text-align: left;\"> gamblers</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">22</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">5</td><td style = \"text-align: left;\">2019-05-01</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">10.044</td><td style = \"text-align: right;\">101.0</td><td style = \"text-align: left;\"> is</td><td style = \"text-align: right;\">true</td><td style = \"text-align: right;\">31</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">6</td><td style = \"text-align: left;\">2019-05-02</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">10.064</td><td style = \"text-align: right;\">0.061</td><td style = \"text-align: left;\"> Better</td><td style = \"text-align: right;\">false</td><td style = \"text-align: right;\">32</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccccccc}\n",
       "\t& Date & id & B & C & BabyBlue & E & G\\\\\n",
       "\t\\hline\n",
       "\t& Date & Int64 & Float64 & Float64 & String15 & Bool & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 2019-05-01 & 1 & 9.998 & 0.0 &  The & 1 & 11 \\\\\n",
       "\t2 & 2019-05-02 & 1 & 10.061 & 0.098 &  for & 1 & 12 \\\\\n",
       "\t3 & 2019-05-01 & 2 & 9.994 & 100.0 &  highway & 1 & 21 \\\\\n",
       "\t4 & 2019-05-02 & 2 & 10.076 & 0.078 &  gamblers & 1 & 22 \\\\\n",
       "\t5 & 2019-05-01 & 3 & 10.044 & 101.0 &  is & 1 & 31 \\\\\n",
       "\t6 & 2019-05-02 & 3 & 10.064 & 0.061 &  Better & 0 & 32 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×7 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m Date       \u001b[0m\u001b[1m id    \u001b[0m\u001b[1m B       \u001b[0m\u001b[1m C       \u001b[0m\u001b[1m BabyBlue  \u001b[0m\u001b[1m E     \u001b[0m\u001b[1m G     \u001b[0m\n",
       "     │\u001b[90m Date       \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m String15  \u001b[0m\u001b[90m Bool  \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────────────────────────────────────────────────────\n",
       "   1 │ 2019-05-01      1    9.998    0.0     The        true     11\n",
       "   2 │ 2019-05-02      1   10.061    0.098   for        true     12\n",
       "   3 │ 2019-05-01      2    9.994  100.0     highway    true     21\n",
       "   4 │ 2019-05-02      2   10.076    0.078   gamblers   true     22\n",
       "   5 │ 2019-05-01      3   10.044  101.0     is         true     31\n",
       "   6 │ 2019-05-02      3   10.064    0.061   Better    false     32"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = innerjoin(df1,df2,on=[:Date,:id])       #joining 2 DataFrames, match both :Date and :id\n",
    "                                              #innerjoin() for intersection of data points "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[1m Row \u001b[0m│\u001b[1m Date       \u001b[0m\u001b[1m id    \u001b[0m\u001b[1m B       \u001b[0m\u001b[1m C       \u001b[0m\u001b[1m BabyBlue  \u001b[0m\u001b[1m E     \u001b[0m\u001b[1m G     \u001b[0m\u001b[1m c     \u001b[0m\n",
      "     │\u001b[90m Date       \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m String15  \u001b[0m\u001b[90m Bool  \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
      "─────┼─────────────────────────────────────────────────────────────────────\n",
      "   1 │ 2019-05-01      1    9.998    0.0     The        true     11      1\n",
      "   2 │ 2019-05-02      1   10.061    0.098   for        true     12      1\n",
      "   3 │ 2019-05-01      2    9.994  100.0     highway    true     21      1\n",
      "   4 │ 2019-05-02      2   10.076    0.078   gamblers   true     22      1\n",
      "   5 │ 2019-05-01      3   10.044  101.0     is         true     31      1\n",
      "   6 │ 2019-05-02      3   10.064    0.061   Better    false     32      1"
     ]
    }
   ],
   "source": [
    "df3[:,:c] .= 1              #add a constant to the DataFrame\n",
    "show(df3;summary=false)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10.0525"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "median(df1[:,:B])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div style = \"float: left;\"><span>3×3 DataFrame</span></div><div style = \"clear: both;\"></div></div><div class = \"data-frame\" style = \"overflow-x: scroll;\"><table class = \"data-frame\" style = \"margin-bottom: 6px;\"><thead><tr class = \"header\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">Row</th><th style = \"text-align: left;\">id</th><th style = \"text-align: left;\">B_mean</th><th style = \"text-align: left;\">B_std</th></tr><tr class = \"subheader headerLastRow\"><th class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\"></th><th title = \"Int64\" style = \"text-align: left;\">Int64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th><th title = \"Float64\" style = \"text-align: left;\">Float64</th></tr></thead><tbody><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">1</td><td style = \"text-align: right;\">1</td><td style = \"text-align: right;\">10.0295</td><td style = \"text-align: right;\">0.0445477</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">2</td><td style = \"text-align: right;\">2</td><td style = \"text-align: right;\">10.035</td><td style = \"text-align: right;\">0.0579828</td></tr><tr><td class = \"rowNumber\" style = \"font-weight: bold; text-align: right;\">3</td><td style = \"text-align: right;\">3</td><td style = \"text-align: right;\">10.054</td><td style = \"text-align: right;\">0.0141421</td></tr></tbody></table></div>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id & B\\_mean & B\\_std\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Float64 & Float64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 10.0295 & 0.0445477 \\\\\n",
       "\t2 & 2 & 10.035 & 0.0579828 \\\\\n",
       "\t3 & 3 & 10.054 & 0.0141421 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id    \u001b[0m\u001b[1m B_mean  \u001b[0m\u001b[1m B_std     \u001b[0m\n",
       "     │\u001b[90m Int64 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Float64   \u001b[0m\n",
       "─────┼───────────────────────────\n",
       "   1 │     1  10.0295  0.0445477\n",
       "   2 │     2  10.035   0.0579828\n",
       "   3 │     3  10.054   0.0141421"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combine(groupby(df1,:id),:B.=>[mean,std])  #mean and std of B for each id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "@webio": {
   "lastCommId": null,
   "lastKernelId": null
  },
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Julia 1.10.3",
   "language": "julia",
   "name": "julia-1.10"
  },
  "language_info": {
   "file_extension": ".jl",
   "mimetype": "application/julia",
   "name": "julia",
   "version": "1.10.3"
  },
  "nteract": {
   "version": "0.23.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}