JupyterIntro/r-sqldf_notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## R Code Using sqldf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Loading required package: gsubfn\n",
      "Loading required package: proto\n",
      "Loading required package: RSQLite\n"
     ]
    }
   ],
   "source": [
    "library(sqldf)\n",
    "library(babynames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead><tr><th scope=col>year</th><th scope=col>sex</th><th scope=col>name</th><th scope=col>n</th><th scope=col>prop</th></tr></thead>\n",
       "<tbody>\n",
       "\t<tr><td>1880      </td><td>F         </td><td>Mary      </td><td>7065      </td><td>0.07238433</td></tr>\n",
       "\t<tr><td>1880      </td><td>F         </td><td>Anna      </td><td>2604      </td><td>0.02667923</td></tr>\n",
       "\t<tr><td>1880      </td><td>F         </td><td>Emma      </td><td>2003      </td><td>0.02052170</td></tr>\n",
       "\t<tr><td>1880      </td><td>F         </td><td>Elizabeth </td><td>1939      </td><td>0.01986599</td></tr>\n",
       "\t<tr><td>1880      </td><td>F         </td><td>Minnie    </td><td>1746      </td><td>0.01788861</td></tr>\n",
       "\t<tr><td>1880      </td><td>F         </td><td>Margaret  </td><td>1578      </td><td>0.01616737</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "\\begin{tabular}{r|lllll}\n",
       " year & sex & name & n & prop\\\\\n",
       "\\hline\n",
       "\t 1880       & F          & Mary       & 7065       & 0.07238433\\\\\n",
       "\t 1880       & F          & Anna       & 2604       & 0.02667923\\\\\n",
       "\t 1880       & F          & Emma       & 2003       & 0.02052170\\\\\n",
       "\t 1880       & F          & Elizabeth  & 1939       & 0.01986599\\\\\n",
       "\t 1880       & F          & Minnie     & 1746       & 0.01788861\\\\\n",
       "\t 1880       & F          & Margaret   & 1578       & 0.01616737\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "year | sex | name | n | prop | \n",
       "|---|---|---|---|---|---|\n",
       "| 1880       | F          | Mary       | 7065       | 0.07238433 | \n",
       "| 1880       | F          | Anna       | 2604       | 0.02667923 | \n",
       "| 1880       | F          | Emma       | 2003       | 0.02052170 | \n",
       "| 1880       | F          | Elizabeth  | 1939       | 0.01986599 | \n",
       "| 1880       | F          | Minnie     | 1746       | 0.01788861 | \n",
       "| 1880       | F          | Margaret   | 1578       | 0.01616737 | \n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "  year sex name      n    prop      \n",
       "1 1880 F   Mary      7065 0.07238433\n",
       "2 1880 F   Anna      2604 0.02667923\n",
       "3 1880 F   Emma      2003 0.02052170\n",
       "4 1880 F   Elizabeth 1939 0.01986599\n",
       "5 1880 F   Minnie    1746 0.01788861\n",
       "6 1880 F   Margaret  1578 0.01616737"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Classes 'tbl_df', 'tbl' and 'data.frame':\t1858689 obs. of  5 variables:\n",
      " $ year: num  1880 1880 1880 1880 1880 1880 1880 1880 1880 1880 ...\n",
      " $ sex : chr  \"F\" \"F\" \"F\" \"F\" ...\n",
      " $ name: chr  \"Mary\" \"Anna\" \"Emma\" \"Elizabeth\" ...\n",
      " $ n   : int  7065 2604 2003 1939 1746 1578 1472 1414 1320 1288 ...\n",
      " $ prop: num  0.0724 0.0267 0.0205 0.0199 0.0179 ...\n"
     ]
    }
   ],
   "source": [
    "head(babynames)\n",
    "str(babynames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead><tr><th scope=col>name</th><th scope=col>NameCount</th></tr></thead>\n",
       "<tbody>\n",
       "\t<tr><td>Aaban    </td><td>  87     </td></tr>\n",
       "\t<tr><td>Aabha    </td><td>  28     </td></tr>\n",
       "\t<tr><td>Aabid    </td><td>   5     </td></tr>\n",
       "\t<tr><td>Aabriella</td><td>  15     </td></tr>\n",
       "\t<tr><td>Aada     </td><td>   5     </td></tr>\n",
       "\t<tr><td>Aadam    </td><td> 160     </td></tr>\n",
       "\t<tr><td>Aadan    </td><td> 122     </td></tr>\n",
       "\t<tr><td>Aadarsh  </td><td> 168     </td></tr>\n",
       "\t<tr><td>Aaden    </td><td>4218     </td></tr>\n",
       "\t<tr><td>Aadesh   </td><td>  15     </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ll}\n",
       " name & NameCount\\\\\n",
       "\\hline\n",
       "\t Aaban     &   87     \\\\\n",
       "\t Aabha     &   28     \\\\\n",
       "\t Aabid     &    5     \\\\\n",
       "\t Aabriella &   15     \\\\\n",
       "\t Aada      &    5     \\\\\n",
       "\t Aadam     &  160     \\\\\n",
       "\t Aadan     &  122     \\\\\n",
       "\t Aadarsh   &  168     \\\\\n",
       "\t Aaden     & 4218     \\\\\n",
       "\t Aadesh    &   15     \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "name | NameCount | \n",
       "|---|---|---|---|---|---|---|---|---|---|\n",
       "| Aaban     |   87      | \n",
       "| Aabha     |   28      | \n",
       "| Aabid     |    5      | \n",
       "| Aabriella |   15      | \n",
       "| Aada      |    5      | \n",
       "| Aadam     |  160      | \n",
       "| Aadan     |  122      | \n",
       "| Aadarsh   |  168      | \n",
       "| Aaden     | 4218      | \n",
       "| Aadesh    |   15      | \n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "   name      NameCount\n",
       "1  Aaban       87     \n",
       "2  Aabha       28     \n",
       "3  Aabid        5     \n",
       "4  Aabriella   15     \n",
       "5  Aada         5     \n",
       "6  Aadam      160     \n",
       "7  Aadan      122     \n",
       "8  Aadarsh    168     \n",
       "9  Aaden     4218     \n",
       "10 Aadesh      15     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sqldf(\"select name, sum(n) as NameCount\n",
    "       from babynames \n",
    "       where year > 2000\n",
    "       Group By name limit 10\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead><tr><th scope=col>year</th><th scope=col>sex</th><th scope=col>name</th><th scope=col>n</th><th scope=col>prop</th><th scope=col>sex_code</th><th scope=col>sex_desc</th></tr></thead>\n",
       "<tbody>\n",
       "\t<tr><td>2001       </td><td>F          </td><td>Emily      </td><td>25052      </td><td>0.012655267</td><td>F          </td><td>Female     </td></tr>\n",
       "\t<tr><td>2001       </td><td>F          </td><td>Madison    </td><td>22158      </td><td>0.011193334</td><td>F          </td><td>Female     </td></tr>\n",
       "\t<tr><td>2001       </td><td>F          </td><td>Hannah     </td><td>20707      </td><td>0.010460347</td><td>F          </td><td>Female     </td></tr>\n",
       "\t<tr><td>2001       </td><td>F          </td><td>Ashley     </td><td>16524      </td><td>0.008347263</td><td>F          </td><td>Female     </td></tr>\n",
       "\t<tr><td>2001       </td><td>F          </td><td>Alexis     </td><td>16396      </td><td>0.008282603</td><td>F          </td><td>Female     </td></tr>\n",
       "\t<tr><td>2001       </td><td>F          </td><td>Sarah      </td><td>15889      </td><td>0.008026487</td><td>F          </td><td>Female     </td></tr>\n",
       "\t<tr><td>2001       </td><td>F          </td><td>Samantha   </td><td>15860      </td><td>0.008011837</td><td>F          </td><td>Female     </td></tr>\n",
       "\t<tr><td>2001       </td><td>F          </td><td>Abigail    </td><td>14803      </td><td>0.007477883</td><td>F          </td><td>Female     </td></tr>\n",
       "\t<tr><td>2001       </td><td>F          </td><td>Elizabeth  </td><td>14783      </td><td>0.007467780</td><td>F          </td><td>Female     </td></tr>\n",
       "\t<tr><td>2001       </td><td>F          </td><td>Olivia     </td><td>13975      </td><td>0.007059610</td><td>F          </td><td>Female     </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "\\begin{tabular}{r|lllllll}\n",
       " year & sex & name & n & prop & sex\\_code & sex\\_desc\\\\\n",
       "\\hline\n",
       "\t 2001        & F           & Emily       & 25052       & 0.012655267 & F           & Female     \\\\\n",
       "\t 2001        & F           & Madison     & 22158       & 0.011193334 & F           & Female     \\\\\n",
       "\t 2001        & F           & Hannah      & 20707       & 0.010460347 & F           & Female     \\\\\n",
       "\t 2001        & F           & Ashley      & 16524       & 0.008347263 & F           & Female     \\\\\n",
       "\t 2001        & F           & Alexis      & 16396       & 0.008282603 & F           & Female     \\\\\n",
       "\t 2001        & F           & Sarah       & 15889       & 0.008026487 & F           & Female     \\\\\n",
       "\t 2001        & F           & Samantha    & 15860       & 0.008011837 & F           & Female     \\\\\n",
       "\t 2001        & F           & Abigail     & 14803       & 0.007477883 & F           & Female     \\\\\n",
       "\t 2001        & F           & Elizabeth   & 14783       & 0.007467780 & F           & Female     \\\\\n",
       "\t 2001        & F           & Olivia      & 13975       & 0.007059610 & F           & Female     \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "year | sex | name | n | prop | sex_code | sex_desc | \n",
       "|---|---|---|---|---|---|---|---|---|---|\n",
       "| 2001        | F           | Emily       | 25052       | 0.012655267 | F           | Female      | \n",
       "| 2001        | F           | Madison     | 22158       | 0.011193334 | F           | Female      | \n",
       "| 2001        | F           | Hannah      | 20707       | 0.010460347 | F           | Female      | \n",
       "| 2001        | F           | Ashley      | 16524       | 0.008347263 | F           | Female      | \n",
       "| 2001        | F           | Alexis      | 16396       | 0.008282603 | F           | Female      | \n",
       "| 2001        | F           | Sarah       | 15889       | 0.008026487 | F           | Female      | \n",
       "| 2001        | F           | Samantha    | 15860       | 0.008011837 | F           | Female      | \n",
       "| 2001        | F           | Abigail     | 14803       | 0.007477883 | F           | Female      | \n",
       "| 2001        | F           | Elizabeth   | 14783       | 0.007467780 | F           | Female      | \n",
       "| 2001        | F           | Olivia      | 13975       | 0.007059610 | F           | Female      | \n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "   year sex name      n     prop        sex_code sex_desc\n",
       "1  2001 F   Emily     25052 0.012655267 F        Female  \n",
       "2  2001 F   Madison   22158 0.011193334 F        Female  \n",
       "3  2001 F   Hannah    20707 0.010460347 F        Female  \n",
       "4  2001 F   Ashley    16524 0.008347263 F        Female  \n",
       "5  2001 F   Alexis    16396 0.008282603 F        Female  \n",
       "6  2001 F   Sarah     15889 0.008026487 F        Female  \n",
       "7  2001 F   Samantha  15860 0.008011837 F        Female  \n",
       "8  2001 F   Abigail   14803 0.007477883 F        Female  \n",
       "9  2001 F   Elizabeth 14783 0.007467780 F        Female  \n",
       "10 2001 F   Olivia    13975 0.007059610 F        Female  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ref_sex <- data.frame(sex_code = c('F', 'M'), sex_desc = c('Female', 'Male'))\n",
    "\n",
    "sqldf(\"select * \n",
    "      from babynames \n",
    "      join ref_sex \n",
    "      on (babynames.sex = ref_sex.sex_code)\n",
    "      where year > 2000 limit 10\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}