data-processing/pandas-dataloading-storage-fileformats.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas数据导入存储与格式\n",
    "- 文本格式\n",
    "    - Parsing functions in pandas\n",
    "    - pd.read_csv('filepath')\n",
    "    - pd.read_table('filepath', sep=',')\n",
    "    - pd.read_csv('filepath', header=None):不带文件头\n",
    "    - pd.read_csv('filepath', names=['col1', 'col2', 'col3']):指定文件头（列名）\n",
    "    - 指定names和index\n",
    "        - names = ['col1', 'col2', 'col3']\n",
    "        - pd.read_csv('filepath', names=names, index_col='col3')\n",
    "    - 指定层次化index\n",
    "        - pd.read_csv('filepath', index_col=['col1', 'col2'])\n",
    "    - 使用正则表达式作为分隔符\n",
    "        - pd.read_table('filepath', sep='\\s+')\n",
    "    - 忽略某些行：skiprows\n",
    "        - pd.read_csv('filepath', skiprows=[0, 2, 3]):忽略第0，2，3行\n",
    "    - 缺失值:NA, -1.#IND, and NULL\n",
    "        - 检验：pd.isnull(dataframeObj)\n",
    "        - 指定特定形式的缺失值\n",
    "            - sentinels = {'col1': ['foo', 'NA'], 'col3': ['two']}\n",
    "            - pd.read_csv('filepath', na_values=sentinels)\n",
    "            - 即NA,foo,two都被定义为缺失值\n",
    "    - read_csv / read_table function arguments\n",
    "- 读入部分数据\n",
    "    - pd.options.display.max_rows\n",
    "    - 设置浏览的行数\n",
    "        - pd.read_csv('filepath', nrows=5)\n",
    "    - iterate方式\n",
    "\n",
    "```py\n",
    "chunker = pd.read_csv('data/dataload/ex6.csv', chunksize=1000)\n",
    "\n",
    "tot = pd.Series([])\n",
    "for piece in chunker:\n",
    "    tot = tot.add(piece['key'].value_counts(), fill_value=0)\n",
    "```\n",
    "\n",
    "- 导出数据到文本\n",
    "    - data.to_csv('filepath')\n",
    "    - 指定分割符\n",
    "        - import sys\n",
    "        - data.to_csv(sys.stdout, sep='|')\n",
    "    - 缺失值显式地“展现”\n",
    "        - data.to_csv(sys.stdout, na_rep='NULL')\n",
    "    - 关闭index和header\n",
    "        - data.to_csv(sys.stdout, index=False, header=False)\n",
    "    - 指定列\n",
    "        - data.to_csv(sys.stdout, index=False, columns=['col1', 'col2', 'col3'])\n",
    "- 手动操作数据\n",
    "\n",
    "```py\n",
    "import csv\n",
    "f = open('data/dataload/ex7.csv')\n",
    "\n",
    "reader = csv.reader(f)\n",
    "for line in reader:\n",
    "     print(line)\n",
    "```\n",
    "\n",
    "    - csv.Dialect:设置读取csv的配置信息\n",
    "    - CSV dialect options\n",
    "- JSON数据\n",
    "    - jsonStr = json.loads(jsonObj)\n",
    "    - jsonObj = json.dumps(jsonStr)\n",
    "    - 转成dataframe\n",
    "        - pd.DataFrame(jsonStr['json_key'], columns=['json_key_1', 'json_key_2'])\n",
    "    - data = pd.read_json('jsonfile')\n",
    "        - data.to_json()\n",
    "        - data.to_json(orient='records') \n",
    "- XML 与 HTML\n",
    "    - pd.read_html('htmlfilepath')\n",
    "    - objectify.parse(open('xmlfilepath'))\n",
    "- 二进制文件格式\n",
    "    - dataframeObj.to_pickle('picklepath')\n",
    "    - pd.read_pickle('picklepath')\n",
    "- 使用HDF5格式\n",
    "    - store = pd.HDFStore('h5filepath')\n",
    "    - store['key1'] = dataframeObj\n",
    "    - store['key1_1'] = dataframeObj['col1']\n",
    "    - store.put('key2', frame, format='table')\n",
    "    - dataframeObj.to_hdf('h5filepath', 'key3', format='table')\n",
    "    - pd.read_hdf('h5filepath', 'key3', where=['index < 5'])\n",
    "- 读取Excel文件\n",
    "    - 读\n",
    "        - xlsx = pd.ExcelFile('xlsxfilepath')\n",
    "        - pd.read_excel(xlsx, 'Sheet1')\n",
    "    - 写\n",
    "        - writer = pd.ExcelWriter('xlsxfilepath')\n",
    "        - frame.to_excel(writer, 'Sheet1')\n",
    "        - writer.save()\n",
    "- 操作Web API\n",
    "    - import requests\n",
    "    - url = 'https://api.github.com/repos/pandas-dev/pandas/issues'\n",
    "    - resp = requests.get(url)\n",
    "    - data = resp.json()\n",
    "    - pd.DataFrame(data, columns=['number', 'title', 'labels', 'state'])\n",
    "- 数据库操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'/Users/zhangjun/Documents/machine-learning-notes/data-processing'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# coding:utf-8\n",
    "from pandas import Series, DataFrame\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "%pwd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 文本格式\n",
    "Table 6-1. Parsing functions in pandas\n",
    "\n",
    "Function | Description\n",
    "---------|------------\n",
    "read_csv | Load delimited data from a file, URL, or file-like object. Use comma as default delimiter\n",
    "read_table | Load delimited data from a file, URL, or file-like object. Use tab ('\\t') as default delimiter\n",
    "read_fwf | Read data in fixed-width column format (that is, no delimiters)\n",
    "read_clipboard | Version of read_table that reads data from the clipboard. Useful for converting tables from web pages\n",
    "read_excel | Read tabular data from an Excel XLS or XLSX file\n",
    "read_hdf | Read pandas data from an HDF5 file\n",
    "read_html | Read all tables found in the given HTML document\n",
    "read_json | Read data from a JSON (JavaScript Object Notation) string representation\n",
    "read_msgpack | Read pandas data encoded using the MessagePack binary format\n",
    "read_pickle | Read an arbitrary object stored in Python pickle format\n",
    "read_sas | Read a SAS dataset stored in one of the SAS system’s custom storage formats\n",
    "read_sql | Read the results of a SQL query (using SQLAlchemy) as a pandas DataFrame.\n",
    "read_stata | Read a dataset from Stata file format\n",
    "read_feather | Read the Feather binary file format\n",
    "\n",
    "The optional arguments for these functions may fall into a few categories:\n",
    "\n",
    "- Indexing: can treat one or more columns as the returned DataFrame, and whether to get column names from the file, the user, or not at all.\n",
    "- Type inference and data conversion: this includes the user-defined value conversions and custom list of missing value markers.\n",
    "- Datetime parsing: includes combining capability, including combining date and time information spread over multiple columns into a single column in the result.\n",
    "- Iterating: support for iterating over chunks of very large files.\n",
    "- Unclean data issues: skipping rows or a footer, comments, or other minor things like numeric data with thousands separated by commas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a,b,c,d,message\r\n",
      "1,2,3,4,hello\r\n",
      "5,6,7,8,world\r\n",
      "9,10,11,12,foo"
     ]
    }
   ],
   "source": [
    "!cat data/dataload/ex1.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since this is comma-delimited, we can use `read_csv` to read it into a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "   a   b   c   d message\n",
       "0  1   2   3   4   hello\n",
       "1  5   6   7   8   world\n",
       "2  9  10  11  12     foo"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/dataload/ex1.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could also have used `read_table` and specifying the delimiter:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "   a   b   c   d message\n",
       "0  1   2   3   4   hello\n",
       "1  5   6   7   8   world\n",
       "2  9  10  11  12     foo"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_table('data/dataload/ex1.csv', sep=',')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A file will not always have a header row. Consider this file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1,2,3,4,hello\r\n",
      "5,6,7,8,world\r\n",
      "9,10,11,12,foo"
     ]
    }
   ],
   "source": [
    "!cat data/dataload/ex2.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can allow pandas to assign default column names, or you can specify names yourself:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</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>"
      ],
      "text/plain": [
       "   0   1   2   3      4\n",
       "0  1   2   3   4  hello\n",
       "1  5   6   7   8  world\n",
       "2  9  10  11  12    foo"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/dataload/ex2.csv', header=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "   a   b   c   d message\n",
       "0  1   2   3   4   hello\n",
       "1  5   6   7   8   world\n",
       "2  9  10  11  12     foo"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/dataload/ex2.csv', names=['a', 'b', 'c', 'd', 'message'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Suppose you wanted the message column to be the index of the returned DataFrame. You can either indicate you want the column at index 4 or named 'message' using the `index_col` argument:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "         a   b   c   d\n",
       "message               \n",
       "hello    1   2   3   4\n",
       "world    5   6   7   8\n",
       "foo      9  10  11  12"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "names = ['a', 'b', 'c', 'd', 'message']\n",
    "pd.read_csv('data/dataload/ex2.csv', names=names, index_col='message')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the event that you want to form a `hierarchical index` from multiple columns, pass a list of column numbers or names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "key1,key2,value1,value2\r\n",
      "one,a,1,2\r\n",
      "one,b,3,4\r\n",
      "one,c,5,6\r\n",
      "one,d,7,8\r\n",
      "two,a,9,10\r\n",
      "two,b,11,12\r\n",
      "two,c,13,14\r\n",
      "two,d,15,16\r\n"
     ]
    }
   ],
   "source": [
    "!cat data/dataload/csv_mindex.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "           value1  value2\n",
       "key1 key2                \n",
       "one  a          1       2\n",
       "     b          3       4\n",
       "     c          5       6\n",
       "     d          7       8\n",
       "two  a          9      10\n",
       "     b         11      12\n",
       "     c         13      14\n",
       "     d         15      16"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parsed = pd.read_csv('data/dataload/csv_mindex.csv', index_col=['key1', 'key2'])\n",
    "parsed"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In some cases, a table might not have a fixed delimiter, using whitespace or some other pattern to separate fields. In these cases, you can pass a `regular expression` as a delimiter for `read_table`. Consider a text file that looks like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "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']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(open('data/dataload/ex3.txt'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "            A         B         C\n",
       "aaa -0.264438 -1.026059 -0.619500\n",
       "bbb  0.927272  0.302904 -0.032399\n",
       "ccc -0.264273 -0.386314 -0.217601\n",
       "ddd -0.871858 -0.348382  1.100491"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.read_table('data/dataload/ex3.txt', sep='\\s+')\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For example, you can skip the first, third, and fourth rows of a file with `skiprows`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# hey!\r\n",
      "a,b,c,d,message\r\n",
      "# just wanted to make things more difficult for you\r\n",
      "# who reads CSV files with computers, anyway?\r\n",
      "1,2,3,4,hello\r\n",
      "5,6,7,8,world\r\n",
      "9,10,11,12,foo"
     ]
    }
   ],
   "source": [
    "!cat data/dataload/ex4.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "   a   b   c   d message\n",
       "0  1   2   3   4   hello\n",
       "1  5   6   7   8   world\n",
       "2  9  10  11  12     foo"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/dataload/ex4.csv', skiprows=[0, 2, 3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Missing data is usually either not present (empty string) or marked by some sentinel value. By default, pandas uses a set of commonly occurring `sentinels`, such as NA, -1.#IND, and NULL:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "something,a,b,c,d,message\r\n",
      "one,1,2,3,4,NA\r\n",
      "two,5,6,,8,world\r\n",
      "three,9,10,11,12,foo"
     ]
    }
   ],
   "source": [
    "!cat data/dataload/ex5.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "  something  a   b     c   d message\n",
       "0       one  1   2   3.0   4     NaN\n",
       "1       two  5   6   NaN   8   world\n",
       "2     three  9  10  11.0  12     foo"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.read_csv('data/dataload/ex5.csv')\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  something      a      b      c      d message\n",
       "0     False  False  False  False  False    True\n",
       "1     False  False  False   True  False   False\n",
       "2     False  False  False  False  False   False"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.isnull(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `na_values` option can take either a list or set of strings to consider missing values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "  something  a   b     c   d message\n",
       "0       one  1   2   3.0   4     NaN\n",
       "1       two  5   6   NaN   8   world\n",
       "2     three  9  10  11.0  12     foo"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.read_csv('data/dataload/ex5.csv', na_values=['NULL'])\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Different NA sentinels can be specified for each column in a dict:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>NaN</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>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  something  a   b     c   d message\n",
       "0       one  1   2   3.0   4     NaN\n",
       "1       NaN  5   6   NaN   8   world\n",
       "2     three  9  10  11.0  12     NaN"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sentinels = {'message': ['foo', 'NA'], 'something': ['two']}\n",
    "pd.read_csv('data/dataload/ex5.csv', na_values=sentinels)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Table 6-2. Some read_csv / read_table function arguments\n",
    "\n",
    "Argument | Description\n",
    "---------|------------\n",
    "path | String indicating filesystem location, URL, or file-like object\n",
    "sep or delimiter | Character sequence or regular expression to use to split fields in each row\n",
    "header | Row number to use as column names. Defaults to 0 (first row), but should be None if there is no header row\n",
    "index_col | Column numbers or names to use as the row index in the result. Can be a single name/number or a list of them for a hierarchical index\n",
    "names | List of column names for result, combine with header=None\n",
    "skiprows | Number of rows at beginning of file to ignore or list of row numbers (starting from 0) to skip\n",
    "na_values | Sequence of values to replace with NA\n",
    "comment | Character or characters to split comments off the end of lines\n",
    "parse_dates | Attempt to parse data to datetime; False by default. If True, will attempt to parse all columns. Otherwise can specify a list of column numbers or name to parse. If element of list is tuple or list, will combine multiple columns together and parse to date (for example if date/time split across two columns)\n",
    "keep_date_col | If joining columns to parse date, keep the joined columns. Default False\n",
    "converters | Dict containing column number of name mapping to functions. For example {'foo': f} would apply the function f to all values in the 'foo' column\n",
    "dayfirst | When parsing potentially ambiguous dates, treat as international format (e.g. 7/6/2012 -> June 7, 2012). Default False\n",
    "date_parser | Function to use to parse dates\n",
    "nrows | Number of rows to read from beginning of file\n",
    "iterator | Return a TextParser object for reading file piecemeal\n",
    "chunksize | For iteration, size of file chunks\n",
    "skip_footer | Number of lines to ignore at end of file\n",
    "verbose | Print various parser output information, like the number of missing values placed in non-numeric columns\n",
    "encoding | Text encoding for unicode. For example 'utf-8' for UTF-8 encoded text\n",
    "squeeze | If the parsed data only contains one column return a Series\n",
    "thousands | Separator for thousands, e.g. ',' or '.'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 读入部分数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "           one       two     three      four key\n",
       "0     0.467976 -0.038649 -0.295344 -1.824726   L\n",
       "1    -0.358893  1.404453  0.704965 -0.200638   B\n",
       "2    -0.501840  0.659254 -0.421691 -0.057688   G\n",
       "3     0.204886  1.074134  1.388361 -0.982404   R\n",
       "4     0.354628 -0.133116  0.283763 -0.837063   Q\n",
       "...        ...       ...       ...       ...  ..\n",
       "9995  2.311896 -0.417070 -1.409599 -0.515821   L\n",
       "9996 -0.479893 -0.650419  0.745152 -0.646038   E\n",
       "9997  0.523331  0.787112  0.486066  1.093156   K\n",
       "9998 -0.362559  0.598894 -1.843201  0.887292   G\n",
       "9999 -0.096376 -1.012999 -0.657431 -0.573315   0\n",
       "\n",
       "[10000 rows x 5 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.options.display.max_rows = 10  # Make display more compact\n",
    "result = pd.read_csv('data/dataload/ex6.csv')\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to only read out a small number of rows (avoiding reading the entire file), specify that with `nrows`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "        one       two     three      four key\n",
       "0  0.467976 -0.038649 -0.295344 -1.824726   L\n",
       "1 -0.358893  1.404453  0.704965 -0.200638   B\n",
       "2 -0.501840  0.659254 -0.421691 -0.057688   G\n",
       "3  0.204886  1.074134  1.388361 -0.982404   R\n",
       "4  0.354628 -0.133116  0.283763 -0.837063   Q"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('data/dataload/ex6.csv', nrows=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `TextParser` object returned by read_csv allows you to iterate over the parts of the file according to the `chunksize`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "E    368.0\n",
       "X    364.0\n",
       "L    346.0\n",
       "O    343.0\n",
       "Q    340.0\n",
       "M    338.0\n",
       "J    337.0\n",
       "F    335.0\n",
       "K    334.0\n",
       "H    330.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chunker = pd.read_csv('data/dataload/ex6.csv', chunksize=1000)\n",
    "\n",
    "tot = pd.Series([])\n",
    "for piece in chunker:\n",
    "    tot = tot.add(piece['key'].value_counts(), fill_value=0)\n",
    "\n",
    "tot = tot.sort_values(ascending=False)\n",
    "tot[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`TextParser` is also equipped with a `get_chunk` method which enables you to read pieces of an arbitrary size."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 导出数据到文本"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "  something  a   b     c   d message\n",
       "0       one  1   2   3.0   4     NaN\n",
       "1       two  5   6   NaN   8   world\n",
       "2     three  9  10  11.0  12     foo"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv('data/dataload/ex5.csv')\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using DataFrame’s `to_csv` method, we can write the data out to a comma-separated file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data.to_csv('data/out/out.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ",something,a,b,c,d,message\r\n",
      "0,one,1,2,3.0,4,\r\n",
      "1,two,5,6,,8,world\r\n",
      "2,three,9,10,11.0,12,foo\r\n"
     ]
    }
   ],
   "source": [
    "!cat data/out/out.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Other delimiters can be used, of course (writing to `sys.stdout` so it prints the text result to the console):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|something|a|b|c|d|message\n",
      "0|one|1|2|3.0|4|\n",
      "1|two|5|6||8|world\n",
      "2|three|9|10|11.0|12|foo\n"
     ]
    }
   ],
   "source": [
    "import sys\n",
    "data.to_csv(sys.stdout, sep='|')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Missing values appear as empty strings in the output. You might want to denote them by some other `sentinel` value:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ",something,a,b,c,d,message\n",
      "0,one,1,2,3.0,4,NULL\n",
      "1,two,5,6,NULL,8,world\n",
      "2,three,9,10,11.0,12,foo\n"
     ]
    }
   ],
   "source": [
    "data.to_csv(sys.stdout, na_rep='NULL')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With no other options specified, both the row and column labels are written. Both of these can be disabled:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "one,1,2,3.0,4,\n",
      "two,5,6,,8,world\n",
      "three,9,10,11.0,12,foo\n"
     ]
    }
   ],
   "source": [
    "data.to_csv(sys.stdout, index=False, header=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also write only a subset of the columns, and in an order of your choosing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a,b,c\n",
      "1,2,3.0\n",
      "5,6,\n",
      "9,10,11.0\n"
     ]
    }
   ],
   "source": [
    "data.to_csv(sys.stdout, index=False, columns=['a', 'b', 'c'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Series also has a `to_csv` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2000-01-01', '2000-01-02', '2000-01-03', '2000-01-04',\n",
       "               '2000-01-05', '2000-01-06', '2000-01-07'],\n",
       "              dtype='datetime64[ns]', freq='D')"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dates = pd.date_range('1/1/2000', periods=7)\n",
    "dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2000-01-01,0\r\n",
      "2000-01-02,1\r\n",
      "2000-01-03,2\r\n",
      "2000-01-04,3\r\n",
      "2000-01-05,4\r\n",
      "2000-01-06,5\r\n",
      "2000-01-07,6\r\n"
     ]
    }
   ],
   "source": [
    "ts = pd.Series(np.arange(7), index=dates)\n",
    "ts.to_csv('data/out/tseries.csv')\n",
    "!cat data/out/tseries.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 手动操作数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\"a\",\"b\",\"c\"\r\n",
      "\"1\",\"2\",\"3\"\r\n",
      "\"1\",\"2\",\"3\",\"4\"\r\n"
     ]
    }
   ],
   "source": [
    "!cat data/dataload/ex7.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['a', 'b', 'c']\n",
      "['1', '2', '3']\n",
      "['1', '2', '3', '4']\n"
     ]
    }
   ],
   "source": [
    "import csv\n",
    "f = open('data/dataload/ex7.csv')\n",
    "\n",
    "reader = csv.reader(f)\n",
    "for line in reader:\n",
    "     print(line)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'a': ('1', '1'), 'b': ('2', '2'), 'c': ('3', '3')}"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lines = list(csv.reader(open('data/dataload/ex7.csv')))\n",
    "header, values = lines[0], lines[1:]\n",
    "data_dict = {h: v for h, v in zip(header, zip(*values))}\n",
    "data_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Defining a new format with a different delimiter, string quoting convention, or line terminator is done by defining a simple subclass of `csv.Dialect`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "class my_dialect(csv.Dialect):\n",
    "    lineterminator = '\\n'\n",
    "    delimiter = ';'\n",
    "    quotechar = '\"'\n",
    "    quoting = csv.QUOTE_MINIMAL\n",
    "\n",
    "reader = csv.reader(f, dialect=my_dialect)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Table 6-3. CSV dialect options\n",
    "\n",
    "Argument | Description\n",
    "---------|------------\n",
    "delimiter | One-character string to separate fields. Defaults to ','.\n",
    "lineterminator | Line terminator for writing, defaults to '\\r\\n'. Reader ignores this and recognizes cross-platform line terminators.\n",
    "quotechar | Quote character for fields with special characters (like a delimiter). Default is '\"'.\n",
    "quoting | Quoting convention. Options include csv.QUOTE_ALL (quote all fields), csv.QUOTE_MINIMAL (only fields with special characters like the delimiter), csv.QUOTE_NONNUMERIC, and csv.QUOTE_NONE (no quoting). See Python’s documentation for full details. Defaults to QUOTE_MINIMAL.\n",
    "skipinitialspace | Ignore whitespace after each delimiter. Default False.\n",
    "doublequote | How to handle quoting character inside a field. If True, it is doubled. See online documentation for full detail and behavior.\n",
    "escapechar | String to escape the delimiter if quoting is set to csv.QUOTE_NONE. Disabled by default\n",
    "\n",
    "To write delimited files manually, you can use `csv.writer`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with open('data/out/mydata.csv', 'w') as f:\n",
    "    writer = csv.writer(f, dialect=my_dialect)\n",
    "    writer.writerow(('one', 'two', 'three'))\n",
    "    writer.writerow(('1', '2', '3'))\n",
    "    writer.writerow(('4', '5', '6'))\n",
    "    writer.writerow(('7', '8', '9'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "one;two;three\r\n",
      "1;2;3\r\n",
      "4;5;6\r\n",
      "7;8;9\r\n"
     ]
    }
   ],
   "source": [
    "!cat data/out/mydata.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## JSON数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'name': u'Wes',\n",
       " u'pet': None,\n",
       " u'places_lived': [u'United States', u'Spain', u'Germany'],\n",
       " u'siblings': [{u'age': 29, u'name': u'Scott', u'pets': [u'Zeus', u'Zuko']},\n",
       "  {u'age': 38, u'name': u'Katie', u'pets': [u'Sixes', u'Stache', u'Cisco']}]}"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import json\n",
    "\n",
    "obj = \"\"\"\n",
    "{\"name\": \"Wes\",\n",
    " \"places_lived\": [\"United States\", \"Spain\", \"Germany\"],\n",
    " \"pet\": null,\n",
    " \"siblings\": [{\"name\": \"Scott\", \"age\": 29, \"pets\": [\"Zeus\", \"Zuko\"]},\n",
    "              {\"name\": \"Katie\", \"age\": 38,\n",
    "               \"pets\": [\"Sixes\", \"Stache\", \"Cisco\"]}]\n",
    "}\n",
    "\"\"\"\n",
    "\n",
    "result = json.loads(obj)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "asjson = json.dumps(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Conveniently, you can pass a list of JSON objects to the DataFrame constructor and select a subset of the data fields:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Scott</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Katie</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age\n",
       "0  Scott   29\n",
       "1  Katie   38"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "siblings = pd.DataFrame(result['siblings'], columns=['name', 'age'])\n",
    "siblings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The default options for `pandas.read_json` assume that each object in the JSON array is a row in the table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c\n",
       "0  1  2  3\n",
       "1  4  5  6\n",
       "2  7  8  9"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_json('data/dataload/example.json')\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you need to export data from pandas to JSON, one way is to use the `to_json` methods on Series and DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\"a\":{\"0\":1,\"1\":4,\"2\":7},\"b\":{\"0\":2,\"1\":5,\"2\":8},\"c\":{\"0\":3,\"1\":6,\"2\":9}}\n"
     ]
    }
   ],
   "source": [
    "print(data.to_json())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{\"a\":1,\"b\":2,\"c\":3},{\"a\":4,\"b\":5,\"c\":6},{\"a\":7,\"b\":8,\"c\":9}]\n"
     ]
    }
   ],
   "source": [
    "print(data.to_json(orient='records'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## XML 与 HTML"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, you must install some additional libraries used by `read_html`:\n",
    "\n",
    "```\n",
    "conda install lxml\n",
    "pip install beautifulsoup4 html5lib\n",
    "```\n",
    "\n",
    "The `pandas.read_html` function has a number of options, but by default it searches for and attempts to parse all tabular data contained within `<table>` tags."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tables = pd.read_html('data/dataload/fdic_failed_bank_list.html')\n",
    "len(tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Bank Name</th>\n",
       "      <th>City</th>\n",
       "      <th>ST</th>\n",
       "      <th>CERT</th>\n",
       "      <th>Acquiring Institution</th>\n",
       "      <th>Closing Date</th>\n",
       "      <th>Updated Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Allied Bank</td>\n",
       "      <td>Mulberry</td>\n",
       "      <td>AR</td>\n",
       "      <td>91</td>\n",
       "      <td>Today's Bank</td>\n",
       "      <td>September 23, 2016</td>\n",
       "      <td>November 17, 2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Woodbury Banking Company</td>\n",
       "      <td>Woodbury</td>\n",
       "      <td>GA</td>\n",
       "      <td>11297</td>\n",
       "      <td>United Bank</td>\n",
       "      <td>August 19, 2016</td>\n",
       "      <td>November 17, 2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>First CornerStone Bank</td>\n",
       "      <td>King of Prussia</td>\n",
       "      <td>PA</td>\n",
       "      <td>35312</td>\n",
       "      <td>First-Citizens Bank &amp; Trust Company</td>\n",
       "      <td>May 6, 2016</td>\n",
       "      <td>September 6, 2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trust Company Bank</td>\n",
       "      <td>Memphis</td>\n",
       "      <td>TN</td>\n",
       "      <td>9956</td>\n",
       "      <td>The Bank of Fayette County</td>\n",
       "      <td>April 29, 2016</td>\n",
       "      <td>September 6, 2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>North Milwaukee State Bank</td>\n",
       "      <td>Milwaukee</td>\n",
       "      <td>WI</td>\n",
       "      <td>20364</td>\n",
       "      <td>First-Citizens Bank &amp; Trust Company</td>\n",
       "      <td>March 11, 2016</td>\n",
       "      <td>June 16, 2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Bank Name             City  ST   CERT  \\\n",
       "0                   Allied Bank         Mulberry  AR     91   \n",
       "1  The Woodbury Banking Company         Woodbury  GA  11297   \n",
       "2        First CornerStone Bank  King of Prussia  PA  35312   \n",
       "3            Trust Company Bank          Memphis  TN   9956   \n",
       "4    North Milwaukee State Bank        Milwaukee  WI  20364   \n",
       "\n",
       "                 Acquiring Institution        Closing Date       Updated Date  \n",
       "0                         Today's Bank  September 23, 2016  November 17, 2016  \n",
       "1                          United Bank     August 19, 2016  November 17, 2016  \n",
       "2  First-Citizens Bank & Trust Company         May 6, 2016  September 6, 2016  \n",
       "3           The Bank of Fayette County      April 29, 2016  September 6, 2016  \n",
       "4  First-Citizens Bank & Trust Company      March 11, 2016      June 16, 2016  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "failures = tables[0]\n",
    "failures.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2010    157\n",
       "2009    140\n",
       "2011     92\n",
       "2012     51\n",
       "2008     25\n",
       "       ... \n",
       "2004      4\n",
       "2001      4\n",
       "2007      3\n",
       "2003      3\n",
       "2000      2\n",
       "Name: Closing Date, dtype: int64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "close_timestamps = pd.to_datetime(failures['Closing Date'])\n",
    "close_timestamps.dt.year.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### PARSING XML WITH LXML.OBJECTIFY\n",
    "The New York Metropolitan Transportation Authority (MTA) publishes a number of data series about its bus and train services (http://www.mta.info/developers/download.html). Here we’ll look at the performance data which is contained in a set of XML files. Each train or bus service has a different file (like Performance_MNR.xml for the Metro-North Railroad) containing monthly data as a series of XML records that look like this:\n",
    "\n",
    "```xml\n",
    "<INDICATOR>\n",
    "  <INDICATOR_SEQ>373889</INDICATOR_SEQ>\n",
    "  <PARENT_SEQ></PARENT_SEQ>\n",
    "  <AGENCY_NAME>Metro-North Railroad</AGENCY_NAME>\n",
    "  <INDICATOR_NAME>Escalator Availability</INDICATOR_NAME>\n",
    "  <DESCRIPTION>Percent of the time that escalators are operational\n",
    "  systemwide. The availability rate is based on physical observations performed\n",
    "  the morning of regular business days only. This is a new indicator the agency\n",
    "  began reporting in 2009.</DESCRIPTION>\n",
    "  <PERIOD_YEAR>2011</PERIOD_YEAR>\n",
    "  <PERIOD_MONTH>12</PERIOD_MONTH>\n",
    "  <CATEGORY>Service Indicators</CATEGORY>\n",
    "  <FREQUENCY>M</FREQUENCY>\n",
    "  <DESIRED_CHANGE>U</DESIRED_CHANGE>\n",
    "  <INDICATOR_UNIT>%</INDICATOR_UNIT>\n",
    "  <DECIMAL_PLACES>1</DECIMAL_PLACES>\n",
    "  <YTD_TARGET>97.00</YTD_TARGET>\n",
    "  <YTD_ACTUAL></YTD_ACTUAL>\n",
    "  <MONTHLY_TARGET>97.00</MONTHLY_TARGET>\n",
    "  <MONTHLY_ACTUAL></MONTHLY_ACTUAL>\n",
    "</INDICATOR>\n",
    "```\n",
    "\n",
    "Using `lxml.objectify`, we parse the file and get a reference to the root node of the XML file with `getroot`:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from lxml import objectify\n",
    "\n",
    "path = 'data/dataload/mta_perf/Performance_MNR.xml'\n",
    "parsed = objectify.parse(open(path))\n",
    "root = parsed.getroot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`root.INDICATOR` return a generator yielding each <INDICATOR> XML element. For each record, we can populate a dict of tag names (like YTD_ACTUAL) to data values (excluding a few tags):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "data = []\n",
    "\n",
    "skip_fields = ['PARENT_SEQ', 'INDICATOR_SEQ',\n",
    "               'DESIRED_CHANGE', 'DECIMAL_PLACES']\n",
    "\n",
    "for elt in root.INDICATOR:\n",
    "    el_data = {}\n",
    "    for child in elt.getchildren():\n",
    "        if child.tag in skip_fields:\n",
    "            continue\n",
    "        el_data[child.tag] = child.pyval\n",
    "    data.append(el_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lastly, convert this list of dicts into a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AGENCY_NAME</th>\n",
       "      <th>CATEGORY</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "      <th>FREQUENCY</th>\n",
       "      <th>INDICATOR_NAME</th>\n",
       "      <th>INDICATOR_UNIT</th>\n",
       "      <th>MONTHLY_ACTUAL</th>\n",
       "      <th>MONTHLY_TARGET</th>\n",
       "      <th>PERIOD_MONTH</th>\n",
       "      <th>PERIOD_YEAR</th>\n",
       "      <th>YTD_ACTUAL</th>\n",
       "      <th>YTD_TARGET</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of commuter trains that arrive at thei...</td>\n",
       "      <td>M</td>\n",
       "      <td>On-Time Performance (West of Hudson)</td>\n",
       "      <td>%</td>\n",
       "      <td>96.9</td>\n",
       "      <td>95</td>\n",
       "      <td>1</td>\n",
       "      <td>2008</td>\n",
       "      <td>96.9</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of commuter trains that arrive at thei...</td>\n",
       "      <td>M</td>\n",
       "      <td>On-Time Performance (West of Hudson)</td>\n",
       "      <td>%</td>\n",
       "      <td>95</td>\n",
       "      <td>95</td>\n",
       "      <td>2</td>\n",
       "      <td>2008</td>\n",
       "      <td>96</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of commuter trains that arrive at thei...</td>\n",
       "      <td>M</td>\n",
       "      <td>On-Time Performance (West of Hudson)</td>\n",
       "      <td>%</td>\n",
       "      <td>96.9</td>\n",
       "      <td>95</td>\n",
       "      <td>3</td>\n",
       "      <td>2008</td>\n",
       "      <td>96.3</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of commuter trains that arrive at thei...</td>\n",
       "      <td>M</td>\n",
       "      <td>On-Time Performance (West of Hudson)</td>\n",
       "      <td>%</td>\n",
       "      <td>98.3</td>\n",
       "      <td>95</td>\n",
       "      <td>4</td>\n",
       "      <td>2008</td>\n",
       "      <td>96.8</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of commuter trains that arrive at thei...</td>\n",
       "      <td>M</td>\n",
       "      <td>On-Time Performance (West of Hudson)</td>\n",
       "      <td>%</td>\n",
       "      <td>95.8</td>\n",
       "      <td>95</td>\n",
       "      <td>5</td>\n",
       "      <td>2008</td>\n",
       "      <td>96.6</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            AGENCY_NAME            CATEGORY  \\\n",
       "0  Metro-North Railroad  Service Indicators   \n",
       "1  Metro-North Railroad  Service Indicators   \n",
       "2  Metro-North Railroad  Service Indicators   \n",
       "3  Metro-North Railroad  Service Indicators   \n",
       "4  Metro-North Railroad  Service Indicators   \n",
       "\n",
       "                                         DESCRIPTION FREQUENCY  \\\n",
       "0  Percent of commuter trains that arrive at thei...         M   \n",
       "1  Percent of commuter trains that arrive at thei...         M   \n",
       "2  Percent of commuter trains that arrive at thei...         M   \n",
       "3  Percent of commuter trains that arrive at thei...         M   \n",
       "4  Percent of commuter trains that arrive at thei...         M   \n",
       "\n",
       "                         INDICATOR_NAME INDICATOR_UNIT MONTHLY_ACTUAL  \\\n",
       "0  On-Time Performance (West of Hudson)              %           96.9   \n",
       "1  On-Time Performance (West of Hudson)              %             95   \n",
       "2  On-Time Performance (West of Hudson)              %           96.9   \n",
       "3  On-Time Performance (West of Hudson)              %           98.3   \n",
       "4  On-Time Performance (West of Hudson)              %           95.8   \n",
       "\n",
       "  MONTHLY_TARGET  PERIOD_MONTH  PERIOD_YEAR YTD_ACTUAL YTD_TARGET  \n",
       "0             95             1         2008       96.9         95  \n",
       "1             95             2         2008         96         95  \n",
       "2             95             3         2008       96.3         95  \n",
       "3             95             4         2008       96.8         95  \n",
       "4             95             5         2008       96.6         95  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "perf = pd.DataFrame(data)\n",
    "perf.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 二进制文件格式\n",
    "pandas objects all have a `to_pickle` method which writes the data to disk in pickle format:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "   a   b   c   d message\n",
       "0  1   2   3   4   hello\n",
       "1  5   6   7   8   world\n",
       "2  9  10  11  12     foo"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame = pd.read_csv('data/dataload/ex1.csv')\n",
    "frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "frame.to_pickle('data/out/frame_pickle')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Any “pickled” object stored in a file can be read by using the built-in pickle directly, or even more conveniently using pandas.`read_pickle`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "   a   b   c   d message\n",
       "0  1   2   3   4   hello\n",
       "1  5   6   7   8   world\n",
       "2  9  10  11  12     foo"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_pickle('data/out/frame_pickle')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 使用HDF5格式\n",
    "HDF5 is a well-regarded file format intended for storing large quantities of scientific array data.\n",
    "\n",
    "The `HDFStore` class works like a dict and handles the low-level details:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<class 'pandas.io.pytables.HDFStore'>\n",
       "File path: data/out/mydata.h5\n",
       "/obj1                frame        (shape->[100,1])\n",
       "/obj1_col            series       (shape->[100])  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame = pd.DataFrame({'a': np.random.randn(100)})\n",
    "store = pd.HDFStore('data/out/mydata.h5')\n",
    "store['obj1'] = frame\n",
    "store['obj1_col'] = frame['a']\n",
    "store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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.429688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-1.157171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.917111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.808405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.231996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>-1.000404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>-2.345141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>0.706472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>0.296808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>0.164909</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           a\n",
       "0   0.429688\n",
       "1  -1.157171\n",
       "2   0.917111\n",
       "3   0.808405\n",
       "4   0.231996\n",
       "..       ...\n",
       "95 -1.000404\n",
       "96 -2.345141\n",
       "97  0.706472\n",
       "98  0.296808\n",
       "99  0.164909\n",
       "\n",
       "[100 rows x 1 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "store['obj1']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "HDFStore supports two storage schemas, `'fixed'` and `'table'`. The latter is generally slower, but it supports query operations using a special syntax"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "store.put('obj2', frame, format='table')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The pandas.`read_hdf` function gives you a shortcut to these tools:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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.429688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-1.157171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.917111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.808405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.231996</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          a\n",
       "0  0.429688\n",
       "1 -1.157171\n",
       "2  0.917111\n",
       "3  0.808405\n",
       "4  0.231996"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.to_hdf('data/out/mydata.h5', 'obj3', format='table')\n",
    "pd.read_hdf('data/out/mydata.h5', 'obj3', where=['index < 5'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 读取Excel文件\n",
    "pandas also supports reading tabular data stored in Excel 2003 (and higher) files using either the `ExcelFile` class or pandas.`read_excel` function. Internally these tools use the add-on packages `xlrd` and `openpyxl` to read XLS and XLSX files, respectively. You may need to install these manually with pip or conda."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "   a   b   c   d message\n",
       "0  1   2   3   4   hello\n",
       "1  5   6   7   8   world\n",
       "2  9  10  11  12     foo"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xlsx = pd.ExcelFile('data/dataload/ex1.xlsx')\n",
    "pd.read_excel(xlsx, 'Sheet1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "   a   b   c   d message\n",
       "0  1   2   3   4   hello\n",
       "1  5   6   7   8   world\n",
       "2  9  10  11  12     foo"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame = pd.read_excel('data/dataload/ex1.xlsx', 'Sheet1')\n",
    "frame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To write pandas data to Excel format, you must first create an `ExcelWriter`, then write data to it using pandas objects’ `to_excel` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "writer = pd.ExcelWriter('data/out/ex2.xlsx')\n",
    "frame.to_excel(writer, 'Sheet1')\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 操作Web API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Response [200]>"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import requests\n",
    "\n",
    "url = 'https://api.github.com/repos/pandas-dev/pandas/issues'\n",
    "resp = requests.get(url)\n",
    "resp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'DOC: correct grammar in unicode section'"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = resp.json()\n",
    "data[0]['title']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>17678</td>\n",
       "      <td>DOC: correct grammar in unicode section</td>\n",
       "      <td>[]</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17677</td>\n",
       "      <td>Melt enhance</td>\n",
       "      <td>[]</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17676</td>\n",
       "      <td>Simultaneously melt multiple columns</td>\n",
       "      <td>[{u'url': u'https://api.github.com/repos/panda...</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17675</td>\n",
       "      <td>DOC: Add examples for MultiIndex.get_locs + cl...</td>\n",
       "      <td>[{u'url': u'https://api.github.com/repos/panda...</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17674</td>\n",
       "      <td>as_block depr warning on tab completion</td>\n",
       "      <td>[{u'url': u'https://api.github.com/repos/panda...</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>17628</td>\n",
       "      <td>BUG: coerce pd.wide_to_long suffixes to ints</td>\n",
       "      <td>[{u'url': u'https://api.github.com/repos/panda...</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>17627</td>\n",
       "      <td>wide_to_long does not convert integer suffixes...</td>\n",
       "      <td>[{u'url': u'https://api.github.com/repos/panda...</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>17621</td>\n",
       "      <td>BUG: Fix unexpected sort in groupby</td>\n",
       "      <td>[{u'url': u'https://api.github.com/repos/panda...</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>17618</td>\n",
       "      <td>BUG: roundtrip fidelity for a tz-aware DTI wit...</td>\n",
       "      <td>[{u'url': u'https://api.github.com/repos/panda...</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>17616</td>\n",
       "      <td>Add teardowns in asv benchmarks</td>\n",
       "      <td>[{u'url': u'https://api.github.com/repos/panda...</td>\n",
       "      <td>open</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>30 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    number                                              title  \\\n",
       "0    17678            DOC: correct grammar in unicode section   \n",
       "1    17677                                       Melt enhance   \n",
       "2    17676               Simultaneously melt multiple columns   \n",
       "3    17675  DOC: Add examples for MultiIndex.get_locs + cl...   \n",
       "4    17674            as_block depr warning on tab completion   \n",
       "..     ...                                                ...   \n",
       "25   17628       BUG: coerce pd.wide_to_long suffixes to ints   \n",
       "26   17627  wide_to_long does not convert integer suffixes...   \n",
       "27   17621                BUG: Fix unexpected sort in groupby   \n",
       "28   17618  BUG: roundtrip fidelity for a tz-aware DTI wit...   \n",
       "29   17616                    Add teardowns in asv benchmarks   \n",
       "\n",
       "                                               labels state  \n",
       "0                                                  []  open  \n",
       "1                                                  []  open  \n",
       "2   [{u'url': u'https://api.github.com/repos/panda...  open  \n",
       "3   [{u'url': u'https://api.github.com/repos/panda...  open  \n",
       "4   [{u'url': u'https://api.github.com/repos/panda...  open  \n",
       "..                                                ...   ...  \n",
       "25  [{u'url': u'https://api.github.com/repos/panda...  open  \n",
       "26  [{u'url': u'https://api.github.com/repos/panda...  open  \n",
       "27  [{u'url': u'https://api.github.com/repos/panda...  open  \n",
       "28  [{u'url': u'https://api.github.com/repos/panda...  open  \n",
       "29  [{u'url': u'https://api.github.com/repos/panda...  open  \n",
       "\n",
       "[30 rows x 4 columns]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "issues = pd.DataFrame(data, columns=['number', 'title', 'labels', 'state'])\n",
    "issues"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据库操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "query = \"\"\"\n",
    "CREATE TABLE test\n",
    "(a VARCHAR(20), b VARCHAR(20),\n",
    " c REAL,        d INTEGER\n",
    ");\"\"\"\n",
    "\n",
    "con = sqlite3.connect('mydata.sqlite')\n",
    "con.execute(query)\n",
    "con.commit()\n",
    "\n",
    "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",
    "\n",
    "con.executemany(stmt, data)\n",
    "con.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(u'Atlanta', u'Georgia', 1.25, 6),\n",
       " (u'Tallahassee', u'Florida', 2.6, 3),\n",
       " (u'Sacramento', u'California', 1.7, 5)]"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor = con.execute('select * from test')\n",
    "rows = cursor.fetchall()\n",
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(('a', None, None, None, None, None, None),\n",
       " ('b', None, None, None, None, None, None),\n",
       " ('c', None, None, None, None, None, None),\n",
       " ('d', None, None, None, None, None, None))"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.description"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "             a           b     c  d\n",
       "0      Atlanta     Georgia  1.25  6\n",
       "1  Tallahassee     Florida  2.60  3\n",
       "2   Sacramento  California  1.70  5"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(rows, columns=[x[0] for x in cursor.description])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import sqlalchemy as sqla\n",
    "\n",
    "db = sqla.create_engine('sqlite:///mydata.sqlite')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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>"
      ],
      "text/plain": [
       "             a           b     c  d\n",
       "0      Atlanta     Georgia  1.25  6\n",
       "1  Tallahassee     Florida  2.60  3\n",
       "2   Sacramento  California  1.70  5"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('select * from test', db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "!rm mydata.sqlite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [Root]",
   "language": "python",
   "name": "Python [Root]"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}