ch6_data_load.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ch6 data loading, storage and file formats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "np.random.seed(12345)\n",
    "import matplotlib.pyplot as plt\n",
    "plt.rc('figure', figsize=(10, 6))\n",
    "np.set_printoptions(precision=4, suppress=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.1 reading & writing data in text format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "a,b,c,d,message\n1,2,3,4,hello\n5,6,7,8,world\n9,10,11,12,foo"
    }
   ],
   "source": [
    "!cat examples/ex1.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   a   b   c   d message\n0  1   2   3   4   hello\n1  5   6   7   8   world\n2  9  10  11  12     foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n      <td>hello</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5</td>\n      <td>6</td>\n      <td>7</td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>9</td>\n      <td>10</td>\n      <td>11</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 3
    }
   ],
   "source": [
    "df = pd.read_csv('examples/ex1.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "1,2,3,4,hello\n5,6,7,8,world\n9,10,11,12,foo"
    }
   ],
   "source": [
    "!cat examples/ex2.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "         a   b   c   d\nmessage               \nhello    1   2   3   4\nworld    5   6   7   8\nfoo      9  10  11  12",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n    </tr>\n    <tr>\n      <th>message</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>hello</th>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>world</th>\n      <td>5</td>\n      <td>6</td>\n      <td>7</td>\n      <td>8</td>\n    </tr>\n    <tr>\n      <th>foo</th>\n      <td>9</td>\n      <td>10</td>\n      <td>11</td>\n      <td>12</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "source": [
    "pd.read_csv(\n",
    "    'examples/ex2.csv',\n",
    "    names=['a', 'b', 'c', 'd', 'message'], \n",
    "    index_col='message')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "key1,key2,value1,value2\none,a,1,2\none,b,3,4\none,c,5,6\none,d,7,8\ntwo,a,9,10\ntwo,b,11,12\ntwo,c,13,14\ntwo,d,15,16\n"
    }
   ],
   "source": [
    "!cat examples/csv_mindex.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "           value1  value2\nkey1 key2                \none  a          1       2\n     b          3       4\n     c          5       6\n     d          7       8\ntwo  a          9      10\n     b         11      12\n     c         13      14\n     d         15      16",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th></th>\n      <th>value1</th>\n      <th>value2</th>\n    </tr>\n    <tr>\n      <th>key1</th>\n      <th>key2</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"4\" valign=\"top\">one</th>\n      <th>a</th>\n      <td>1</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>3</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>c</th>\n      <td>5</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>d</th>\n      <td>7</td>\n      <td>8</td>\n    </tr>\n    <tr>\n      <th rowspan=\"4\" valign=\"top\">two</th>\n      <th>a</th>\n      <td>9</td>\n      <td>10</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>11</td>\n      <td>12</td>\n    </tr>\n    <tr>\n      <th>c</th>\n      <td>13</td>\n      <td>14</td>\n    </tr>\n    <tr>\n      <th>d</th>\n      <td>15</td>\n      <td>16</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 7
    }
   ],
   "source": [
    "parsed = pd.read_csv(\n",
    "    'examples/csv_mindex.csv', \n",
    "    index_col=['key1', 'key2'])\n",
    "parsed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "['            A         B         C\\n',\n 'aaa -0.264438 -1.026059 -0.619500\\n',\n 'bbb  0.927272  0.302904 -0.032399\\n',\n 'ccc -0.264273 -0.386314 -0.217601\\n',\n 'ddd -0.871858 -0.348382  1.100491\\n']"
     },
     "metadata": {},
     "execution_count": 8
    }
   ],
   "source": [
    "list(open('examples/ex3.txt'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = pd.read_table('examples/ex3.txt', sep='\\s+')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "            A         B         C\naaa -0.264438 -1.026059 -0.619500\nbbb  0.927272  0.302904 -0.032399\nccc -0.264273 -0.386314 -0.217601\nddd -0.871858 -0.348382  1.100491",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>A</th>\n      <th>B</th>\n      <th>C</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>aaa</th>\n      <td>-0.264438</td>\n      <td>-1.026059</td>\n      <td>-0.619500</td>\n    </tr>\n    <tr>\n      <th>bbb</th>\n      <td>0.927272</td>\n      <td>0.302904</td>\n      <td>-0.032399</td>\n    </tr>\n    <tr>\n      <th>ccc</th>\n      <td>-0.264273</td>\n      <td>-0.386314</td>\n      <td>-0.217601</td>\n    </tr>\n    <tr>\n      <th>ddd</th>\n      <td>-0.871858</td>\n      <td>-0.348382</td>\n      <td>1.100491</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 10
    }
   ],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## read text file in pieces"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.options.display.max_rows = 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "           one       two     three      four key\n0     0.467976 -0.038649 -0.295344 -1.824726   L\n1    -0.358893  1.404453  0.704965 -0.200638   B\n2    -0.501840  0.659254 -0.421691 -0.057688   G\n3     0.204886  1.074134  1.388361 -0.982404   R\n4     0.354628 -0.133116  0.283763 -0.837063   Q\n...        ...       ...       ...       ...  ..\n9995  2.311896 -0.417070 -1.409599 -0.515821   L\n9996 -0.479893 -0.650419  0.745152 -0.646038   E\n9997  0.523331  0.787112  0.486066  1.093156   K\n9998 -0.362559  0.598894 -1.843201  0.887292   G\n9999 -0.096376 -1.012999 -0.657431 -0.573315   0\n\n[10000 rows x 5 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>one</th>\n      <th>two</th>\n      <th>three</th>\n      <th>four</th>\n      <th>key</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0.467976</td>\n      <td>-0.038649</td>\n      <td>-0.295344</td>\n      <td>-1.824726</td>\n      <td>L</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>-0.358893</td>\n      <td>1.404453</td>\n      <td>0.704965</td>\n      <td>-0.200638</td>\n      <td>B</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>-0.501840</td>\n      <td>0.659254</td>\n      <td>-0.421691</td>\n      <td>-0.057688</td>\n      <td>G</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>0.204886</td>\n      <td>1.074134</td>\n      <td>1.388361</td>\n      <td>-0.982404</td>\n      <td>R</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>0.354628</td>\n      <td>-0.133116</td>\n      <td>0.283763</td>\n      <td>-0.837063</td>\n      <td>Q</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>9995</th>\n      <td>2.311896</td>\n      <td>-0.417070</td>\n      <td>-1.409599</td>\n      <td>-0.515821</td>\n      <td>L</td>\n    </tr>\n    <tr>\n      <th>9996</th>\n      <td>-0.479893</td>\n      <td>-0.650419</td>\n      <td>0.745152</td>\n      <td>-0.646038</td>\n      <td>E</td>\n    </tr>\n    <tr>\n      <th>9997</th>\n      <td>0.523331</td>\n      <td>0.787112</td>\n      <td>0.486066</td>\n      <td>1.093156</td>\n      <td>K</td>\n    </tr>\n    <tr>\n      <th>9998</th>\n      <td>-0.362559</td>\n      <td>0.598894</td>\n      <td>-1.843201</td>\n      <td>0.887292</td>\n      <td>G</td>\n    </tr>\n    <tr>\n      <th>9999</th>\n      <td>-0.096376</td>\n      <td>-1.012999</td>\n      <td>-0.657431</td>\n      <td>-0.573315</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n<p>10000 rows × 5 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 12
    }
   ],
   "source": [
    "result = pd.read_csv('examples/ex6.csv')\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "        one       two     three      four key\n0  0.467976 -0.038649 -0.295344 -1.824726   L\n1 -0.358893  1.404453  0.704965 -0.200638   B\n2 -0.501840  0.659254 -0.421691 -0.057688   G\n3  0.204886  1.074134  1.388361 -0.982404   R\n4  0.354628 -0.133116  0.283763 -0.837063   Q",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>one</th>\n      <th>two</th>\n      <th>three</th>\n      <th>four</th>\n      <th>key</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0.467976</td>\n      <td>-0.038649</td>\n      <td>-0.295344</td>\n      <td>-1.824726</td>\n      <td>L</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>-0.358893</td>\n      <td>1.404453</td>\n      <td>0.704965</td>\n      <td>-0.200638</td>\n      <td>B</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>-0.501840</td>\n      <td>0.659254</td>\n      <td>-0.421691</td>\n      <td>-0.057688</td>\n      <td>G</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>0.204886</td>\n      <td>1.074134</td>\n      <td>1.388361</td>\n      <td>-0.982404</td>\n      <td>R</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>0.354628</td>\n      <td>-0.133116</td>\n      <td>0.283763</td>\n      <td>-0.837063</td>\n      <td>Q</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 13
    }
   ],
   "source": [
    "pd.read_csv('examples/ex6.csv', nrows=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "<pandas.io.parsers.TextFileReader at 0x11d40a090>"
     },
     "metadata": {},
     "execution_count": 14
    }
   ],
   "source": [
    "chunker = pd.read_csv('examples/ex6.csv', chunksize=1000)\n",
    "chunker"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "tot = pd.Series([])\n",
    "for piece in chunker:\n",
    "    tot = tot.add(piece['key'].value_counts(), fill_value=0)\n",
    "tot = tot.sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "E    368.0\nX    364.0\nL    346.0\nO    343.0\nQ    340.0\nM    338.0\nJ    337.0\nF    335.0\nK    334.0\nH    330.0\ndtype: float64"
     },
     "metadata": {},
     "execution_count": 17
    }
   ],
   "source": [
    "tot[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## write data to text file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "  something  a   b     c   d message\n0       one  1   2   3.0   4     NaN\n1       two  5   6   NaN   8   world\n2     three  9  10  11.0  12     foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>something</th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>one</td>\n      <td>1</td>\n      <td>2</td>\n      <td>3.0</td>\n      <td>4</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>two</td>\n      <td>5</td>\n      <td>6</td>\n      <td>NaN</td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>three</td>\n      <td>9</td>\n      <td>10</td>\n      <td>11.0</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 18
    }
   ],
   "source": [
    "data = pd.read_csv('examples/ex5.csv')\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "|something|a|b|c|d|message\n0|one|1|2|3.0|4|\n1|two|5|6||8|world\n2|three|9|10|11.0|12|foo\n"
    }
   ],
   "source": [
    "import sys\n",
    "data.to_csv(sys.stdout, sep='|')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## working with delimited formats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "['a', 'b', 'c']\n['1', '2', '3']\n['1', '2', '3']\n"
    }
   ],
   "source": [
    "import csv\n",
    "f = open('examples/ex7.csv')\n",
    "reader = csv.reader(f)\n",
    "for line in reader:\n",
    "    print(line)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## JSON"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "obj = \"\"\"\n",
    "{\"name\": \"Wes\",\n",
    " \"places_lived\": [\"United States\", \"Spain\", \"Germany\"],\n",
    " \"pet\": null,\n",
    " \"siblings\": [{\"name\": \"Scott\", \"age\": 30, \"pets\": [\"Zeus\", \"Zuko\"]},\n",
    "              {\"name\": \"Katie\", \"age\": 38,\n",
    "               \"pets\": [\"Sixes\", \"Stache\", \"Cisco\"]}]\n",
    "}\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "'\\n{\"name\": \"Wes\",\\n \"places_lived\": [\"United States\", \"Spain\", \"Germany\"],\\n \"pet\": null,\\n \"siblings\": [{\"name\": \"Scott\", \"age\": 30, \"pets\": [\"Zeus\", \"Zuko\"]},\\n              {\"name\": \"Katie\", \"age\": 38,\\n               \"pets\": [\"Sixes\", \"Stache\", \"Cisco\"]}]\\n}\\n'"
     },
     "metadata": {},
     "execution_count": 23
    }
   ],
   "source": [
    "obj"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## XML and HTML"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.2 binary data formats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   a   b   c   d message\n0  1   2   3   4   hello\n1  5   6   7   8   world\n2  9  10  11  12     foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n      <td>hello</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5</td>\n      <td>6</td>\n      <td>7</td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>9</td>\n      <td>10</td>\n      <td>11</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 25
    }
   ],
   "source": [
    "frame = pd.read_csv('examples/ex1.csv')\n",
    "frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "frame.to_pickle('examples/frame_pickle')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   a   b   c   d message\n0  1   2   3   4   hello\n1  5   6   7   8   world\n2  9  10  11  12     foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n      <td>hello</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5</td>\n      <td>6</td>\n      <td>7</td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>9</td>\n      <td>10</td>\n      <td>11</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 27
    }
   ],
   "source": [
    "pd.read_pickle('examples/frame_pickle')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## HDF5 format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "           a\n0  -0.204708\n1   0.478943\n2  -0.519439\n3  -0.555730\n4   1.965781\n..       ...\n95  0.795253\n96  0.118110\n97 -0.748532\n98  0.584970\n99  0.152677\n\n[100 rows x 1 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>-0.204708</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>0.478943</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>-0.519439</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>-0.555730</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>1.965781</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>95</th>\n      <td>0.795253</td>\n    </tr>\n    <tr>\n      <th>96</th>\n      <td>0.118110</td>\n    </tr>\n    <tr>\n      <th>97</th>\n      <td>-0.748532</td>\n    </tr>\n    <tr>\n      <th>98</th>\n      <td>0.584970</td>\n    </tr>\n    <tr>\n      <th>99</th>\n      <td>0.152677</td>\n    </tr>\n  </tbody>\n</table>\n<p>100 rows × 1 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 28
    }
   ],
   "source": [
    "frame = pd.DataFrame({'a': np.random.randn(100)})\n",
    "frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "<class 'pandas.io.pytables.HDFStore'>\nFile path: mydata.h5"
     },
     "metadata": {},
     "execution_count": 35
    }
   ],
   "source": [
    "store = pd.HDFStore('mydata.h5')\n",
    "store['obj1'] = frame\n",
    "store['obje1_col'] = frame['a']\n",
    "store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "           a\n0  -0.204708\n1   0.478943\n2  -0.519439\n3  -0.555730\n4   1.965781\n..       ...\n95  0.795253\n96  0.118110\n97 -0.748532\n98  0.584970\n99  0.152677\n\n[100 rows x 1 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>-0.204708</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>0.478943</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>-0.519439</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>-0.555730</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>1.965781</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>95</th>\n      <td>0.795253</td>\n    </tr>\n    <tr>\n      <th>96</th>\n      <td>0.118110</td>\n    </tr>\n    <tr>\n      <th>97</th>\n      <td>-0.748532</td>\n    </tr>\n    <tr>\n      <th>98</th>\n      <td>0.584970</td>\n    </tr>\n    <tr>\n      <th>99</th>\n      <td>0.152677</td>\n    </tr>\n  </tbody>\n</table>\n<p>100 rows × 1 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 36
    }
   ],
   "source": [
    "store['obj1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "0    -0.204708\n1     0.478943\n2    -0.519439\n3    -0.555730\n4     1.965781\n        ...   \n95    0.795253\n96    0.118110\n97   -0.748532\n98    0.584970\n99    0.152677\nName: a, Length: 100, dtype: float64"
     },
     "metadata": {},
     "execution_count": 37
    }
   ],
   "source": [
    "store['obje1_col']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## read Excel files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.3 interacting with web APIs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "<Response [200]>"
     },
     "metadata": {},
     "execution_count": 42
    }
   ],
   "source": [
    "import requests\n",
    "url = 'https://api.github.com/repos/pandas-dev/pandas/issues'\n",
    "resp = requests.get(url)\n",
    "resp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "'DOC: cleanup whatsnew post 1.0.4 release'"
     },
     "metadata": {},
     "execution_count": 43
    }
   ],
   "source": [
    "data = resp.json()\n",
    "data[0]['title']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "    number                                              title  \\\n0    34430           DOC: cleanup whatsnew post 1.0.4 release   \n1    34429  Fix read_sql empty result with chunksize bug G...   \n2    34427                    CLN: is pandas/tests/data used?   \n3    34426                      Categorical.(to|from)_dummies   \n4    34423  BUG: Fix failing MacPython 32bit wheels for gr...   \n..     ...                                                ...   \n25   34390  BUG: Result of rolling mean depends on more ob...   \n26   34388  BUG: pd.DataFrame.add() does not broadcast acr...   \n27   34385  use ensure_clean rather than explicit os.remov...   \n28   34384  TST: use ensure_clean rather than explicit os....   \n29   34383  DOC: Indexing and selecting data and the use o...   \n\n                                               labels state  \n0   [{'id': 134699, 'node_id': 'MDU6TGFiZWwxMzQ2OT...  open  \n1                                                  []  open  \n2   [{'id': 211029535, 'node_id': 'MDU6TGFiZWwyMTE...  open  \n3                                                  []  open  \n4   [{'id': 563047854, 'node_id': 'MDU6TGFiZWw1NjM...  open  \n..                                                ...   ...  \n25  [{'id': 307649777, 'node_id': 'MDU6TGFiZWwzMDc...  open  \n26  [{'id': 76811, 'node_id': 'MDU6TGFiZWw3NjgxMQ=...  open  \n27  [{'id': 127685, 'node_id': 'MDU6TGFiZWwxMjc2OD...  open  \n28  [{'id': 49254273, 'node_id': 'MDU6TGFiZWw0OTI1...  open  \n29  [{'id': 134699, 'node_id': 'MDU6TGFiZWwxMzQ2OT...  open  \n\n[30 rows x 4 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>number</th>\n      <th>title</th>\n      <th>labels</th>\n      <th>state</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>34430</td>\n      <td>DOC: cleanup whatsnew post 1.0.4 release</td>\n      <td>[{'id': 134699, 'node_id': 'MDU6TGFiZWwxMzQ2OT...</td>\n      <td>open</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>34429</td>\n      <td>Fix read_sql empty result with chunksize bug G...</td>\n      <td>[]</td>\n      <td>open</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>34427</td>\n      <td>CLN: is pandas/tests/data used?</td>\n      <td>[{'id': 211029535, 'node_id': 'MDU6TGFiZWwyMTE...</td>\n      <td>open</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>34426</td>\n      <td>Categorical.(to|from)_dummies</td>\n      <td>[]</td>\n      <td>open</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>34423</td>\n      <td>BUG: Fix failing MacPython 32bit wheels for gr...</td>\n      <td>[{'id': 563047854, 'node_id': 'MDU6TGFiZWw1NjM...</td>\n      <td>open</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>25</th>\n      <td>34390</td>\n      <td>BUG: Result of rolling mean depends on more ob...</td>\n      <td>[{'id': 307649777, 'node_id': 'MDU6TGFiZWwzMDc...</td>\n      <td>open</td>\n    </tr>\n    <tr>\n      <th>26</th>\n      <td>34388</td>\n      <td>BUG: pd.DataFrame.add() does not broadcast acr...</td>\n      <td>[{'id': 76811, 'node_id': 'MDU6TGFiZWw3NjgxMQ=...</td>\n      <td>open</td>\n    </tr>\n    <tr>\n      <th>27</th>\n      <td>34385</td>\n      <td>use ensure_clean rather than explicit os.remov...</td>\n      <td>[{'id': 127685, 'node_id': 'MDU6TGFiZWwxMjc2OD...</td>\n      <td>open</td>\n    </tr>\n    <tr>\n      <th>28</th>\n      <td>34384</td>\n      <td>TST: use ensure_clean rather than explicit os....</td>\n      <td>[{'id': 49254273, 'node_id': 'MDU6TGFiZWw0OTI1...</td>\n      <td>open</td>\n    </tr>\n    <tr>\n      <th>29</th>\n      <td>34383</td>\n      <td>DOC: Indexing and selecting data and the use o...</td>\n      <td>[{'id': 134699, 'node_id': 'MDU6TGFiZWwxMzQ2OT...</td>\n      <td>open</td>\n    </tr>\n  </tbody>\n</table>\n<p>30 rows × 4 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 44
    }
   ],
   "source": [
    "issues = pd.DataFrame(\n",
    "    data, \n",
    "    columns=['number', 'title',\n",
    "             'labels', 'state']\n",
    ")\n",
    "issues"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## interacting with database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "query = \"\"\"\n",
    "CREATE TABLE test\n",
    "(a VARCHAR(20), b VARCHAR(20),\n",
    " c REAL,        d INTEGER\n",
    ");\"\"\"\n",
    "conn = sqlite3.connect('mydata.sqlite')\n",
    "conn.execute(query)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [('Atlanta', 'Georgia', 1.25, 6),\n",
    "        ('Tallahassee', 'Florida', 2.6, 3),\n",
    "        ('Sacramento', 'California', 1.7, 5)]\n",
    "stmt = \"INSERT INTO test VALUES(?, ?, ?, ?)\"\n",
    "conn.executemany(stmt, data)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('Atlanta', 'Georgia', 1.25, 6),\n ('Tallahassee', 'Florida', 2.6, 3),\n ('Sacramento', 'California', 1.7, 5)]"
     },
     "metadata": {},
     "execution_count": 48
    }
   ],
   "source": [
    "cursor = conn.execute('select * from test')\n",
    "rows = cursor.fetchall()\n",
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "             a           b     c  d\n0      Atlanta     Georgia  1.25  6\n1  Tallahassee     Florida  2.60  3\n2   Sacramento  California  1.70  5",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Atlanta</td>\n      <td>Georgia</td>\n      <td>1.25</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Tallahassee</td>\n      <td>Florida</td>\n      <td>2.60</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Sacramento</td>\n      <td>California</td>\n      <td>1.70</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 49
    }
   ],
   "source": [
    "cursor.description\n",
    "pd.DataFrame(rows, columns=[x[0] for x in cursor.description])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "             a           b     c  d\n0      Atlanta     Georgia  1.25  6\n1  Tallahassee     Florida  2.60  3\n2   Sacramento  California  1.70  5",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Atlanta</td>\n      <td>Georgia</td>\n      <td>1.25</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Tallahassee</td>\n      <td>Florida</td>\n      <td>2.60</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Sacramento</td>\n      <td>California</td>\n      <td>1.70</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 50
    }
   ],
   "source": [
    "import sqlalchemy as sqla\n",
    "db = sqla.create_engine('sqlite:///mydata.sqlite')\n",
    "pd.read_sql('select * from test', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "!rm mydata.sqlite"
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.5-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python37564bit42731fe29a1c4072b88558cb14c14b00",
   "display_name": "Python 3.7.5 64-bit"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}