ch06_数据加载、存储和文件格式.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>hello</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "root_path = str(os.getcwd()).replace(\"\\\\\", \"/\")  #取当前目录\n",
    "path = root_path +'/datasets/ch06_data/'\n",
    "df = pd.read_csv(path+'ex1.csv',engine='python')  # 在调用read_csv()方法时指定engine为Python，避免文件名含中文报错\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>hello</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 也可用read_table 默认分隔符‘\\t’,也可以指定为其他分隔符\n",
    "pd.read_table(path+'ex1.csv',sep=',',engine='python')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# header=None：分配默认列名\n",
    "pd.read_csv(path+'ex2.csv',engine='python',header=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>hello</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv(path+'ex2.csv',engine='python',names=['a','b','c', 'd', 'message'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>message</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>hello</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>world</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 假设将message列作为DataFrame的索引\n",
    "names=['a','b','c', 'd', 'message']\n",
    "pd.read_csv(path+'ex2.csv',engine='python',names=names,index_col='message')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>value1</th>\n",
       "      <th>value2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>one</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>one</td>\n",
       "      <td>b</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>one</td>\n",
       "      <td>c</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>one</td>\n",
       "      <td>d</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>two</td>\n",
       "      <td>a</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>two</td>\n",
       "      <td>b</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>two</td>\n",
       "      <td>c</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>two</td>\n",
       "      <td>d</td>\n",
       "      <td>15</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2  value1  value2\n",
       "0  one    a       1       2\n",
       "1  one    b       3       4\n",
       "2  one    c       5       6\n",
       "3  one    d       7       8\n",
       "4  two    a       9      10\n",
       "5  two    b      11      12\n",
       "6  two    c      13      14\n",
       "7  two    d      15      16"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv(path+'csv_mindex.csv',engine='python')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>value1</th>\n",
       "      <th>value2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">one</th>\n",
       "      <th>a</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">two</th>\n",
       "      <th>a</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>15</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将多个列做成一个层次化索引 只需要传入由列编号或列名组成的列表即可\n",
    "pd.read_csv(path+'csv_mindex.csv',engine='python',index_col=['key1','key2'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "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": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "list(open(path+'ex3.txt'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>aaa</th>\n",
       "      <td>-0.264438</td>\n",
       "      <td>-1.026059</td>\n",
       "      <td>-0.619500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bbb</th>\n",
       "      <td>0.927272</td>\n",
       "      <td>0.302904</td>\n",
       "      <td>-0.032399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ccc</th>\n",
       "      <td>-0.264273</td>\n",
       "      <td>-0.386314</td>\n",
       "      <td>-0.217601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ddd</th>\n",
       "      <td>-0.871858</td>\n",
       "      <td>-0.348382</td>\n",
       "      <td>1.100491</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 上面文件不是用固定分隔符来分隔字段的，各个字段由数量不等的空白符分隔，可以用正则表达式\\s+表示\n",
    "# 由于列名比行的数量少，则read_table推断第一列应该是DataFrame的索引\n",
    "result = pd.read_table(path+'ex3.txt',engine='python',sep='\\s+')\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# skiprows跳过文件的某几行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['# hey!\\n',\n",
       " 'a,b,c,d,message\\n',\n",
       " '# just wanted to make things more difficult for you\\n',\n",
       " '# who reads CSV files with computers, anyway?\\n',\n",
       " '1,2,3,4,hello\\n',\n",
       " '5,6,7,8,world\\n',\n",
       " '9,10,11,12,foo']"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(open(path+'ex4.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>hello</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# skiprows跳过文件的第一行、第三行和第四行\n",
    "pd.read_csv(path+'ex4.csv',engine='python',skiprows=[0,2,3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 缺失值  na_values可以接受用于表示缺失值的字符串"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>something</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>two</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>three</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.read_csv(path+'ex5.csv',engine='python')\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>something</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>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": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.isnull(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>something</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</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.0</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.0</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   NaN     NaN\n",
       "1       two  5   6   NaN   8.0   world\n",
       "2     three  9  10  11.0  12.0     foo"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 把数据中是NULL的替换为NAN值\n",
    "result = pd.read_csv(path+'ex5.csv',engine='python',na_values=['NULL'])\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>something</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>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": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 可以用一个字典为各列指定不同的NA标记值\n",
    "sentinels = {'message':['foo','NA'],'something':['two']}\n",
    "pd.read_csv(path+'ex5.csv',engine='python',na_values=sentinels)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 逐块读取文本文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10000, 5)"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.read_csv(path+'ex6.csv',engine='python')\n",
    "result.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "      <th>four</th>\n",
       "      <th>key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.467976</td>\n",
       "      <td>-0.038649</td>\n",
       "      <td>-0.295344</td>\n",
       "      <td>-1.824726</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-0.358893</td>\n",
       "      <td>1.404453</td>\n",
       "      <td>0.704965</td>\n",
       "      <td>-0.200638</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-0.501840</td>\n",
       "      <td>0.659254</td>\n",
       "      <td>-0.421691</td>\n",
       "      <td>-0.057688</td>\n",
       "      <td>G</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.204886</td>\n",
       "      <td>1.074134</td>\n",
       "      <td>1.388361</td>\n",
       "      <td>-0.982404</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.354628</td>\n",
       "      <td>-0.133116</td>\n",
       "      <td>0.283763</td>\n",
       "      <td>-0.837063</td>\n",
       "      <td>Q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.817480</td>\n",
       "      <td>0.742273</td>\n",
       "      <td>0.419395</td>\n",
       "      <td>-2.251035</td>\n",
       "      <td>Q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>-0.776764</td>\n",
       "      <td>0.935518</td>\n",
       "      <td>-0.332872</td>\n",
       "      <td>-1.875641</td>\n",
       "      <td>U</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-0.913135</td>\n",
       "      <td>1.530624</td>\n",
       "      <td>-0.572657</td>\n",
       "      <td>0.477252</td>\n",
       "      <td>K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.358480</td>\n",
       "      <td>-0.497572</td>\n",
       "      <td>-0.367016</td>\n",
       "      <td>0.507702</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>-1.740877</td>\n",
       "      <td>-1.160417</td>\n",
       "      <td>-1.637830</td>\n",
       "      <td>2.172201</td>\n",
       "      <td>G</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\n",
       "5  1.817480  0.742273  0.419395 -2.251035   Q\n",
       "6 -0.776764  0.935518 -0.332872 -1.875641   U\n",
       "7 -0.913135  1.530624 -0.572657  0.477252   K\n",
       "8  0.358480 -0.497572 -0.367016  0.507702   S\n",
       "9 -1.740877 -1.160417 -1.637830  2.172201   G"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 只想读取几行，用nrows进行指定即可\n",
    "pd.read_csv(path+'ex6.csv',engine='python',nrows=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.io.parsers.TextFileReader at 0x1c1824213c8>"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 要逐块读取文件，需要设置chunksize（行数）\n",
    "chunker = pd.read_csv(path+'ex6.csv',engine='python',chunksize=1000)\n",
    "chunker  # TextFileReader 对象可以根据chunksize对文件进行逐块迭代"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将值计数聚合到“key”列\n",
    "tot = pd.Series([])\n",
    "for piece in chunker:\n",
    "    tot = tot.add(piece['key'].value_counts(),fill_value=0)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "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": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tot = tot.sort_values(ascending=False) # ascending：默认True升序排列；False降序排列\n",
    "tot[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 将数据写出到文本格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>something</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>two</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>three</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv(path+'ex5.csv',engine='python')\n",
    "data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to_csv将数据写入到csv中，分隔符为逗号\n",
    "# index=False:不输出行索引；header=True：输出列名称\n",
    "# na_rep='NULL':缺失值被表示为空字符串\n",
    "data.to_csv(path+'output_ex5.csv',na_rep='NULL',index=False,header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "# columns=['a','b','c']：只输出部这三列的数据\n",
    "data.to_csv(path+'output_ex5_1.csv',na_rep='NULL',index=False,columns=['a','b','c'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Series 的 to_csv\n",
    "dates = pd.date_range('1/1/2000',periods=7)\n",
    "ts = pd.Series(np.arange(7),index=dates)\n",
    "ts.to_csv(path+'output_tseries.csv')\n",
    "ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-01</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2000-01-02</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-03</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-04</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-05</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-06</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-07</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            0\n",
       "2000-01-01   \n",
       "2000-01-02  1\n",
       "2000-01-03  2\n",
       "2000-01-04  3\n",
       "2000-01-05  4\n",
       "2000-01-06  5\n",
       "2000-01-07  6"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Series 的 from_csv\n",
    "# pd.Series.from_csv(path+'output_tseries.csv',parse_dates=True)  # 报错 未调通\n",
    "# 用下面脚本代替\n",
    "pd.read_csv(path+'output_tseries.csv',engine='python',index_col=0)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 手工处理分隔符格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['\"a\",\"b\",\"c\"\\n', '\"1\",\"2\",\"3\"\\n', '\"1\",\"2\",\"3\"\\n']"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(open(path+'ex7.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['a', 'b', 'c']\n",
      "['1', '2', '3']\n",
      "['1', '2', '3']\n"
     ]
    }
   ],
   "source": [
    "# 用csv.reader来处理任何单字符文件\n",
    "import csv\n",
    "f = open(path+'ex7.csv')\n",
    "reader = csv.reader(f)  # reader为对象\n",
    "# reader迭代将会为每行产生一个列表\n",
    "for line in reader:\n",
    "    print(line)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'a': ('1', '1'), 'b': ('2', '2'), 'c': ('3', '3')}"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将数据整理成字典\n",
    "lines = list(csv.reader(open(path+'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": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "# csv.Dialect\n",
    "class my_dialect(csv.Dialect):\n",
    "    lineterminator = '\\n'  # 行结束符\n",
    "    delimiter = ';'  # 分隔字段的单字符字符串\n",
    "    quotechar = '\"' # 带有特殊字符串的字段的引用字符\n",
    "    quoting = 0\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['a,\"b\",\"c\"']\n",
      "['1,\"2\",\"3\"']\n",
      "['1,\"2\",\"3\"']\n"
     ]
    }
   ],
   "source": [
    "f = open(path+'ex7.csv')\n",
    "reader = csv.reader(f,dialect=my_dialect)\n",
    "for line in reader:\n",
    "    print(line)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['a,\"b\",\"c\"']\n",
      "['1,\"2\",\"3\"']\n",
      "['1,\"2\",\"3\"']\n"
     ]
    }
   ],
   "source": [
    "f = open(path+'ex7.csv')\n",
    "reader = csv.reader(f,delimiter = '|')\n",
    "for line in reader:\n",
    "    print(line)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 用 csv.writer 手工输入分隔符文件\n",
    "with open(path+'output_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'))\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# json数据\n",
    "## JavaScript Object Notation\n",
    "## 通过HTTP请求在Web浏览器和其他应用程序之间发送数据的标准格式之一"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "obj = \"\"\"\n",
    "{\"name\":\"Wes\",\n",
    " \"places_lived\":[\"United States\",\"Spain\",\"Germary\"],\n",
    " \"pet\":null,\n",
    " \"siblings\":[{\"name\":\"Scott\",\"age\":25,\"pet\":\"Zuko\"},\n",
    "             {\"name\":\"Katie\",\"age\":33,\"pet\":\"Cisco\"}]\n",
    "}\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'name': 'Wes',\n",
       " 'places_lived': ['United States', 'Spain', 'Germary'],\n",
       " 'pet': None,\n",
       " 'siblings': [{'name': 'Scott', 'age': 25, 'pet': 'Zuko'},\n",
       "  {'name': 'Katie', 'age': 33, 'pet': 'Cisco'}]}"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import json\n",
    "# json.loads:将json字符串转换成python形式\n",
    "result = json.loads(obj)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# json.dumps 将python对象转换成JSON格式\n",
    "asjson = json.dumps(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Scott</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Katie</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age\n",
       "0  Scott   25\n",
       "1  Katie   33"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将一个或一组json对象转换成DataFrame\n",
    "siblings = pd.DataFrame(result['siblings'],columns=['name','age'])  #只选取了部分列\n",
    "siblings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导出为json格式\n",
    "siblings.to_json(path+'output.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Scott</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Katie</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age\n",
       "0  Scott   25\n",
       "1  Katie   33"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 导入json\n",
    "pd.read_json(path+'output.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# XML和HTML:Web信息收集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Element a at 0x1c1826449f8>,\n",
       " <Element a at 0x1c182644b88>,\n",
       " <Element a at 0x1c182644868>,\n",
       " <Element a at 0x1c182644728>,\n",
       " <Element a at 0x1c182644db8>]"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from lxml.html import parse  # parse解析得到的数据流\n",
    "from urllib.request import urlopen  # 将对应的url打开\n",
    "\n",
    "parsed = parse(urlopen('http://finance.yahoo.com/q/op?s=AAPL+Options')) # 得到特定类型的所有HTML标签\n",
    "doc = parsed.getroot()  #文档根节点\n",
    "links= doc.findall(\".//a\")   # HTML中的链接是a标签，你也可以获取table标签\n",
    "links[15:20]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Element a at 0x1c1826630e8>"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 上面这些事HTML元素的对象，要得到URL和链接文本，必须使用各对象的get方法（针对URL）和text_content(针对显示文本)\n",
    "\n",
    "lnk = links[28]\n",
    "lnk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/quote/AAPL200320C00145000?p=AAPL200320C00145000'"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lnk.get('href')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'AAPL200320C00145000'"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lnk.text_content()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['/watchlists',\n",
       " '/portfolios',\n",
       " '/screener',\n",
       " '/premium?ncid=navbarprem_fqbo1nu0ks0',\n",
       " '/calendar',\n",
       " '/industries',\n",
       " 'https://money.yahoo.com',\n",
       " '/videos/',\n",
       " '/news/',\n",
       " '/tech']"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 用列表推导式 获取文档中的全部URL\n",
    "urls = [lnk.get('href') for lnk in doc.findall('.//a')]\n",
    "urls[-10:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "表格数量： 2\n"
     ]
    }
   ],
   "source": [
    "# 获取网站上的表格\n",
    "tables = doc.findall('.//table')\n",
    "print(\"表格数量：\",len(tables))\n",
    "calls = tables[0]  # 看涨数据\n",
    "puts = tables[1]   # 看跌数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 获取表格的所有行\n",
    "rows = calls.findall('.//tr')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Contract Name',\n",
       " 'Last Trade Date',\n",
       " 'Strike',\n",
       " 'Last Price',\n",
       " 'Bid',\n",
       " 'Ask',\n",
       " 'Change',\n",
       " '% Change',\n",
       " 'Volume',\n",
       " 'Open Interest',\n",
       " 'Implied Volatility']"
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 每个表格都有一个标题行，然后是数据行\n",
    "# 对于数据的标题行，就是th单元格，对于数据行，则是td单元格\n",
    "def _unpack(row,kind='td'):\n",
    "    elts = row.findall('.//%s'% kind)\n",
    "    return [val.text_content() for val in elts]\n",
    "\n",
    "_unpack(rows[0],kind='th') # 标题"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['AAPL200320C00105000',\n",
       " '2020-03-13 3:40PM EDT',\n",
       " '105.00',\n",
       " '156.25',\n",
       " '142.50',\n",
       " '146.50',\n",
       " '0.00',\n",
       " '-',\n",
       " '1',\n",
       " '11',\n",
       " '706.59%']"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "_unpack(rows[1],kind='td') # 数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandas.io.parsers import TextParser  # 将还是字符串格式的数值型数据转换为浮点数据格式\n",
    "# 将数据转换为一个DataFrame\n",
    "def parse_options_data(table):\n",
    "    rows = table.findall('.//tr') # 获取表格的所有行\n",
    "    header = _unpack(rows[0],kind='th') # 获取标题行\n",
    "    data = [_unpack(r) for r in rows[1:]] # 获取数据\n",
    "    return TextParser(data,names=header).get_chunk()\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Contract Name</th>\n",
       "      <th>Last Trade Date</th>\n",
       "      <th>Strike</th>\n",
       "      <th>Last Price</th>\n",
       "      <th>Bid</th>\n",
       "      <th>Ask</th>\n",
       "      <th>Change</th>\n",
       "      <th>% Change</th>\n",
       "      <th>Volume</th>\n",
       "      <th>Open Interest</th>\n",
       "      <th>Implied Volatility</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAPL200320C00105000</td>\n",
       "      <td>2020-03-13 3:40PM EDT</td>\n",
       "      <td>105.0</td>\n",
       "      <td>156.25</td>\n",
       "      <td>142.50</td>\n",
       "      <td>146.50</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>706.59%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAPL200320C00110000</td>\n",
       "      <td>2020-03-09 3:58PM EDT</td>\n",
       "      <td>110.0</td>\n",
       "      <td>169.85</td>\n",
       "      <td>139.30</td>\n",
       "      <td>141.90</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-</td>\n",
       "      <td>3</td>\n",
       "      <td>50</td>\n",
       "      <td>704.79%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AAPL200320C00115000</td>\n",
       "      <td>2020-03-12 1:45PM EDT</td>\n",
       "      <td>115.0</td>\n",
       "      <td>141.55</td>\n",
       "      <td>134.30</td>\n",
       "      <td>136.90</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>671.92%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAPL200320C00120000</td>\n",
       "      <td>2020-02-06 4:17PM EDT</td>\n",
       "      <td>120.0</td>\n",
       "      <td>178.10</td>\n",
       "      <td>166.40</td>\n",
       "      <td>167.70</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1,402.88%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAPL200320C00125000</td>\n",
       "      <td>2020-03-12 2:00PM EDT</td>\n",
       "      <td>125.0</td>\n",
       "      <td>132.90</td>\n",
       "      <td>123.65</td>\n",
       "      <td>127.15</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>1</td>\n",
       "      <td>605.66%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AAPL200320C00130000</td>\n",
       "      <td>2020-03-12 2:28PM EDT</td>\n",
       "      <td>130.0</td>\n",
       "      <td>124.30</td>\n",
       "      <td>119.35</td>\n",
       "      <td>121.95</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>1</td>\n",
       "      <td>583.06%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AAPL200320C00135000</td>\n",
       "      <td>2020-03-03 10:56AM EDT</td>\n",
       "      <td>135.0</td>\n",
       "      <td>158.40</td>\n",
       "      <td>113.40</td>\n",
       "      <td>116.15</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-</td>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>534.96%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AAPL200320C00140000</td>\n",
       "      <td>2020-02-20 4:03PM EDT</td>\n",
       "      <td>140.0</td>\n",
       "      <td>180.57</td>\n",
       "      <td>107.80</td>\n",
       "      <td>111.75</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>509.03%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AAPL200320C00145000</td>\n",
       "      <td>2020-03-13 2:30PM EDT</td>\n",
       "      <td>145.0</td>\n",
       "      <td>114.75</td>\n",
       "      <td>102.45</td>\n",
       "      <td>106.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>471.68%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AAPL200320C00150000</td>\n",
       "      <td>2020-03-16 3:46PM EDT</td>\n",
       "      <td>150.0</td>\n",
       "      <td>101.90</td>\n",
       "      <td>99.70</td>\n",
       "      <td>100.85</td>\n",
       "      <td>-20.85</td>\n",
       "      <td>-16.99%</td>\n",
       "      <td>164</td>\n",
       "      <td>61</td>\n",
       "      <td>470.22%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Contract Name         Last Trade Date  Strike  Last Price     Bid  \\\n",
       "0  AAPL200320C00105000   2020-03-13 3:40PM EDT   105.0      156.25  142.50   \n",
       "1  AAPL200320C00110000   2020-03-09 3:58PM EDT   110.0      169.85  139.30   \n",
       "2  AAPL200320C00115000   2020-03-12 1:45PM EDT   115.0      141.55  134.30   \n",
       "3  AAPL200320C00120000   2020-02-06 4:17PM EDT   120.0      178.10  166.40   \n",
       "4  AAPL200320C00125000   2020-03-12 2:00PM EDT   125.0      132.90  123.65   \n",
       "5  AAPL200320C00130000   2020-03-12 2:28PM EDT   130.0      124.30  119.35   \n",
       "6  AAPL200320C00135000  2020-03-03 10:56AM EDT   135.0      158.40  113.40   \n",
       "7  AAPL200320C00140000   2020-02-20 4:03PM EDT   140.0      180.57  107.80   \n",
       "8  AAPL200320C00145000   2020-03-13 2:30PM EDT   145.0      114.75  102.45   \n",
       "9  AAPL200320C00150000   2020-03-16 3:46PM EDT   150.0      101.90   99.70   \n",
       "\n",
       "      Ask  Change % Change Volume Open Interest Implied Volatility  \n",
       "0  146.50    0.00        -      1            11            706.59%  \n",
       "1  141.90    0.00        -      3            50            704.79%  \n",
       "2  136.90    0.00        -      1             9            671.92%  \n",
       "3  167.70    0.00        -      2             0          1,402.88%  \n",
       "4  127.15    0.00        -      -             1            605.66%  \n",
       "5  121.95    0.00        -      -             1            583.06%  \n",
       "6  116.15    0.00        -     10            12            534.96%  \n",
       "7  111.75    0.00        -      2             4            509.03%  \n",
       "8  106.00    0.00        -      1             2            471.68%  \n",
       "9  100.85  -20.85  -16.99%    164            61            470.22%  "
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "call_data = parse_options_data(calls)\n",
    "put_data = parse_options_data(puts)\n",
    "call_data[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 利用lxml.objectify解析XML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 纽约大都会运输发布的有关其公交和列车服务的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [],
   "source": [
    "from lxml import objectify\n",
    "xml_path =root_path+ '/datasets/mta_perf/Performance_MNR.xml'\n",
    "parsed = objectify.parse(open(xml_path))  # objectify.parse解析该文件\n",
    "root = parsed.getroot()  # 通过getroot得到该xml文件的根节点的引用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = []\n",
    "skip_field = ['PARENT_SEQ','INDICATOR_SEQ','DESIRED_CHANGE','DECIMAL_PLACES']  # 排除这些标记\n",
    "\n",
    "for elt in root.INDICATOR:#root.INDICATOR返回一个用于产生各个<INDICATOR>XML元素的生成器\n",
    "    el_data={}\n",
    "    for child in elt.getchildren():\n",
    "        if child.tag in skip_field:\n",
    "            continue\n",
    "        el_data[child.tag]=child.pyval\n",
    "    data.append(el_data)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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",
       "    <tr>\n",
       "      <th>5</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>94.4</td>\n",
       "      <td>95</td>\n",
       "      <td>6</td>\n",
       "      <td>2008</td>\n",
       "      <td>96.2</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</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</td>\n",
       "      <td>95</td>\n",
       "      <td>7</td>\n",
       "      <td>2008</td>\n",
       "      <td>96.2</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</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.4</td>\n",
       "      <td>95</td>\n",
       "      <td>8</td>\n",
       "      <td>2008</td>\n",
       "      <td>96.2</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</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>93.7</td>\n",
       "      <td>95</td>\n",
       "      <td>9</td>\n",
       "      <td>2008</td>\n",
       "      <td>95.9</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</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.4</td>\n",
       "      <td>95</td>\n",
       "      <td>10</td>\n",
       "      <td>2008</td>\n",
       "      <td>96</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</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>11</td>\n",
       "      <td>2008</td>\n",
       "      <td>96.1</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</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.1</td>\n",
       "      <td>95</td>\n",
       "      <td>12</td>\n",
       "      <td>2008</td>\n",
       "      <td>96</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</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>92.6</td>\n",
       "      <td>96.2</td>\n",
       "      <td>1</td>\n",
       "      <td>2009</td>\n",
       "      <td>92.6</td>\n",
       "      <td>96.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</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.8</td>\n",
       "      <td>96.2</td>\n",
       "      <td>2</td>\n",
       "      <td>2009</td>\n",
       "      <td>94.6</td>\n",
       "      <td>96.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</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>96.2</td>\n",
       "      <td>3</td>\n",
       "      <td>2009</td>\n",
       "      <td>95.4</td>\n",
       "      <td>96.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</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>97.1</td>\n",
       "      <td>96.2</td>\n",
       "      <td>4</td>\n",
       "      <td>2009</td>\n",
       "      <td>95.9</td>\n",
       "      <td>96.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</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>97.8</td>\n",
       "      <td>96.2</td>\n",
       "      <td>5</td>\n",
       "      <td>2009</td>\n",
       "      <td>96.2</td>\n",
       "      <td>96.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</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>97.3</td>\n",
       "      <td>96.2</td>\n",
       "      <td>6</td>\n",
       "      <td>2009</td>\n",
       "      <td>96.4</td>\n",
       "      <td>96.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</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.7</td>\n",
       "      <td>96.2</td>\n",
       "      <td>7</td>\n",
       "      <td>2009</td>\n",
       "      <td>96.5</td>\n",
       "      <td>96.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</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.7</td>\n",
       "      <td>96.2</td>\n",
       "      <td>8</td>\n",
       "      <td>2009</td>\n",
       "      <td>96.4</td>\n",
       "      <td>96.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</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.1</td>\n",
       "      <td>96.2</td>\n",
       "      <td>9</td>\n",
       "      <td>2009</td>\n",
       "      <td>96.3</td>\n",
       "      <td>96.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</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>94.8</td>\n",
       "      <td>96.2</td>\n",
       "      <td>10</td>\n",
       "      <td>2009</td>\n",
       "      <td>96.2</td>\n",
       "      <td>96.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</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.7</td>\n",
       "      <td>96.2</td>\n",
       "      <td>11</td>\n",
       "      <td>2009</td>\n",
       "      <td>96.1</td>\n",
       "      <td>96.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</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>96.2</td>\n",
       "      <td>12</td>\n",
       "      <td>2009</td>\n",
       "      <td>96</td>\n",
       "      <td>96.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</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</td>\n",
       "      <td>96.3</td>\n",
       "      <td>1</td>\n",
       "      <td>2010</td>\n",
       "      <td>98</td>\n",
       "      <td>96.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</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>93</td>\n",
       "      <td>96.3</td>\n",
       "      <td>2</td>\n",
       "      <td>2010</td>\n",
       "      <td>95.6</td>\n",
       "      <td>96.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</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>96.3</td>\n",
       "      <td>3</td>\n",
       "      <td>2010</td>\n",
       "      <td>96.1</td>\n",
       "      <td>96.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</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.1</td>\n",
       "      <td>96.3</td>\n",
       "      <td>4</td>\n",
       "      <td>2010</td>\n",
       "      <td>96.6</td>\n",
       "      <td>96.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</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>97.6</td>\n",
       "      <td>96.3</td>\n",
       "      <td>5</td>\n",
       "      <td>2010</td>\n",
       "      <td>96.8</td>\n",
       "      <td>96.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</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>97.4</td>\n",
       "      <td>96.3</td>\n",
       "      <td>6</td>\n",
       "      <td>2010</td>\n",
       "      <td>96.9</td>\n",
       "      <td>96.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>618</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>94</td>\n",
       "      <td></td>\n",
       "      <td>7</td>\n",
       "      <td>2009</td>\n",
       "      <td>95.14</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>619</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>97</td>\n",
       "      <td></td>\n",
       "      <td>8</td>\n",
       "      <td>2009</td>\n",
       "      <td>95.38</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>620</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>98.3</td>\n",
       "      <td></td>\n",
       "      <td>9</td>\n",
       "      <td>2009</td>\n",
       "      <td>95.7</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>621</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>98.7</td>\n",
       "      <td></td>\n",
       "      <td>10</td>\n",
       "      <td>2009</td>\n",
       "      <td>96</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>622</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>98.1</td>\n",
       "      <td></td>\n",
       "      <td>11</td>\n",
       "      <td>2009</td>\n",
       "      <td>96.21</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>623</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>100</td>\n",
       "      <td></td>\n",
       "      <td>12</td>\n",
       "      <td>2009</td>\n",
       "      <td>96.5</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>624</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>97.95</td>\n",
       "      <td>97</td>\n",
       "      <td>1</td>\n",
       "      <td>2010</td>\n",
       "      <td>97.95</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>625</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>100</td>\n",
       "      <td>97</td>\n",
       "      <td>2</td>\n",
       "      <td>2010</td>\n",
       "      <td>98.92</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>626</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>100</td>\n",
       "      <td>97</td>\n",
       "      <td>3</td>\n",
       "      <td>2010</td>\n",
       "      <td>99.29</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>627</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>100</td>\n",
       "      <td>97</td>\n",
       "      <td>4</td>\n",
       "      <td>2010</td>\n",
       "      <td>99.47</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>628</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>100</td>\n",
       "      <td>97</td>\n",
       "      <td>5</td>\n",
       "      <td>2010</td>\n",
       "      <td>99.58</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>629</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>91.21</td>\n",
       "      <td>97</td>\n",
       "      <td>6</td>\n",
       "      <td>2010</td>\n",
       "      <td>98.19</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>630</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>100</td>\n",
       "      <td>97</td>\n",
       "      <td>7</td>\n",
       "      <td>2010</td>\n",
       "      <td>98.46</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>631</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>100</td>\n",
       "      <td>97</td>\n",
       "      <td>8</td>\n",
       "      <td>2010</td>\n",
       "      <td>98.69</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>632</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>95.2</td>\n",
       "      <td>97</td>\n",
       "      <td>9</td>\n",
       "      <td>2010</td>\n",
       "      <td>98.3</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>633</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>90.91</td>\n",
       "      <td>97</td>\n",
       "      <td>10</td>\n",
       "      <td>2010</td>\n",
       "      <td>97.55</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>634</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>96.67</td>\n",
       "      <td>97</td>\n",
       "      <td>11</td>\n",
       "      <td>2010</td>\n",
       "      <td>97.47</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>635</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>90.03</td>\n",
       "      <td>97</td>\n",
       "      <td>12</td>\n",
       "      <td>2010</td>\n",
       "      <td>96.84</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>636</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>100</td>\n",
       "      <td>97</td>\n",
       "      <td>1</td>\n",
       "      <td>2011</td>\n",
       "      <td>100</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>637</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>100</td>\n",
       "      <td>97</td>\n",
       "      <td>2</td>\n",
       "      <td>2011</td>\n",
       "      <td>100</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>638</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>97.07</td>\n",
       "      <td>97</td>\n",
       "      <td>3</td>\n",
       "      <td>2011</td>\n",
       "      <td>98.86</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>639</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>98.18</td>\n",
       "      <td>97</td>\n",
       "      <td>4</td>\n",
       "      <td>2011</td>\n",
       "      <td>98.76</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>640</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td>79.18</td>\n",
       "      <td>97</td>\n",
       "      <td>5</td>\n",
       "      <td>2011</td>\n",
       "      <td>90.91</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>641</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td></td>\n",
       "      <td>97</td>\n",
       "      <td>6</td>\n",
       "      <td>2011</td>\n",
       "      <td></td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>642</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td></td>\n",
       "      <td>97</td>\n",
       "      <td>7</td>\n",
       "      <td>2011</td>\n",
       "      <td></td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td></td>\n",
       "      <td>97</td>\n",
       "      <td>8</td>\n",
       "      <td>2011</td>\n",
       "      <td></td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>644</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td></td>\n",
       "      <td>97</td>\n",
       "      <td>9</td>\n",
       "      <td>2011</td>\n",
       "      <td></td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>645</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td></td>\n",
       "      <td>97</td>\n",
       "      <td>10</td>\n",
       "      <td>2011</td>\n",
       "      <td></td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>646</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td></td>\n",
       "      <td>97</td>\n",
       "      <td>11</td>\n",
       "      <td>2011</td>\n",
       "      <td></td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>647</th>\n",
       "      <td>Metro-North Railroad</td>\n",
       "      <td>Service Indicators</td>\n",
       "      <td>Percent of the time that escalators are operat...</td>\n",
       "      <td>M</td>\n",
       "      <td>Escalator Availability</td>\n",
       "      <td>%</td>\n",
       "      <td></td>\n",
       "      <td>97</td>\n",
       "      <td>12</td>\n",
       "      <td>2011</td>\n",
       "      <td></td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>648 rows × 12 columns</p>\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",
       "5    Metro-North Railroad  Service Indicators   \n",
       "6    Metro-North Railroad  Service Indicators   \n",
       "7    Metro-North Railroad  Service Indicators   \n",
       "8    Metro-North Railroad  Service Indicators   \n",
       "9    Metro-North Railroad  Service Indicators   \n",
       "10   Metro-North Railroad  Service Indicators   \n",
       "11   Metro-North Railroad  Service Indicators   \n",
       "12   Metro-North Railroad  Service Indicators   \n",
       "13   Metro-North Railroad  Service Indicators   \n",
       "14   Metro-North Railroad  Service Indicators   \n",
       "15   Metro-North Railroad  Service Indicators   \n",
       "16   Metro-North Railroad  Service Indicators   \n",
       "17   Metro-North Railroad  Service Indicators   \n",
       "18   Metro-North Railroad  Service Indicators   \n",
       "19   Metro-North Railroad  Service Indicators   \n",
       "20   Metro-North Railroad  Service Indicators   \n",
       "21   Metro-North Railroad  Service Indicators   \n",
       "22   Metro-North Railroad  Service Indicators   \n",
       "23   Metro-North Railroad  Service Indicators   \n",
       "24   Metro-North Railroad  Service Indicators   \n",
       "25   Metro-North Railroad  Service Indicators   \n",
       "26   Metro-North Railroad  Service Indicators   \n",
       "27   Metro-North Railroad  Service Indicators   \n",
       "28   Metro-North Railroad  Service Indicators   \n",
       "29   Metro-North Railroad  Service Indicators   \n",
       "..                    ...                 ...   \n",
       "618  Metro-North Railroad  Service Indicators   \n",
       "619  Metro-North Railroad  Service Indicators   \n",
       "620  Metro-North Railroad  Service Indicators   \n",
       "621  Metro-North Railroad  Service Indicators   \n",
       "622  Metro-North Railroad  Service Indicators   \n",
       "623  Metro-North Railroad  Service Indicators   \n",
       "624  Metro-North Railroad  Service Indicators   \n",
       "625  Metro-North Railroad  Service Indicators   \n",
       "626  Metro-North Railroad  Service Indicators   \n",
       "627  Metro-North Railroad  Service Indicators   \n",
       "628  Metro-North Railroad  Service Indicators   \n",
       "629  Metro-North Railroad  Service Indicators   \n",
       "630  Metro-North Railroad  Service Indicators   \n",
       "631  Metro-North Railroad  Service Indicators   \n",
       "632  Metro-North Railroad  Service Indicators   \n",
       "633  Metro-North Railroad  Service Indicators   \n",
       "634  Metro-North Railroad  Service Indicators   \n",
       "635  Metro-North Railroad  Service Indicators   \n",
       "636  Metro-North Railroad  Service Indicators   \n",
       "637  Metro-North Railroad  Service Indicators   \n",
       "638  Metro-North Railroad  Service Indicators   \n",
       "639  Metro-North Railroad  Service Indicators   \n",
       "640  Metro-North Railroad  Service Indicators   \n",
       "641  Metro-North Railroad  Service Indicators   \n",
       "642  Metro-North Railroad  Service Indicators   \n",
       "643  Metro-North Railroad  Service Indicators   \n",
       "644  Metro-North Railroad  Service Indicators   \n",
       "645  Metro-North Railroad  Service Indicators   \n",
       "646  Metro-North Railroad  Service Indicators   \n",
       "647  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",
       "5    Percent of commuter trains that arrive at thei...         M   \n",
       "6    Percent of commuter trains that arrive at thei...         M   \n",
       "7    Percent of commuter trains that arrive at thei...         M   \n",
       "8    Percent of commuter trains that arrive at thei...         M   \n",
       "9    Percent of commuter trains that arrive at thei...         M   \n",
       "10   Percent of commuter trains that arrive at thei...         M   \n",
       "11   Percent of commuter trains that arrive at thei...         M   \n",
       "12   Percent of commuter trains that arrive at thei...         M   \n",
       "13   Percent of commuter trains that arrive at thei...         M   \n",
       "14   Percent of commuter trains that arrive at thei...         M   \n",
       "15   Percent of commuter trains that arrive at thei...         M   \n",
       "16   Percent of commuter trains that arrive at thei...         M   \n",
       "17   Percent of commuter trains that arrive at thei...         M   \n",
       "18   Percent of commuter trains that arrive at thei...         M   \n",
       "19   Percent of commuter trains that arrive at thei...         M   \n",
       "20   Percent of commuter trains that arrive at thei...         M   \n",
       "21   Percent of commuter trains that arrive at thei...         M   \n",
       "22   Percent of commuter trains that arrive at thei...         M   \n",
       "23   Percent of commuter trains that arrive at thei...         M   \n",
       "24   Percent of commuter trains that arrive at thei...         M   \n",
       "25   Percent of commuter trains that arrive at thei...         M   \n",
       "26   Percent of commuter trains that arrive at thei...         M   \n",
       "27   Percent of commuter trains that arrive at thei...         M   \n",
       "28   Percent of commuter trains that arrive at thei...         M   \n",
       "29   Percent of commuter trains that arrive at thei...         M   \n",
       "..                                                 ...       ...   \n",
       "618  Percent of the time that escalators are operat...         M   \n",
       "619  Percent of the time that escalators are operat...         M   \n",
       "620  Percent of the time that escalators are operat...         M   \n",
       "621  Percent of the time that escalators are operat...         M   \n",
       "622  Percent of the time that escalators are operat...         M   \n",
       "623  Percent of the time that escalators are operat...         M   \n",
       "624  Percent of the time that escalators are operat...         M   \n",
       "625  Percent of the time that escalators are operat...         M   \n",
       "626  Percent of the time that escalators are operat...         M   \n",
       "627  Percent of the time that escalators are operat...         M   \n",
       "628  Percent of the time that escalators are operat...         M   \n",
       "629  Percent of the time that escalators are operat...         M   \n",
       "630  Percent of the time that escalators are operat...         M   \n",
       "631  Percent of the time that escalators are operat...         M   \n",
       "632  Percent of the time that escalators are operat...         M   \n",
       "633  Percent of the time that escalators are operat...         M   \n",
       "634  Percent of the time that escalators are operat...         M   \n",
       "635  Percent of the time that escalators are operat...         M   \n",
       "636  Percent of the time that escalators are operat...         M   \n",
       "637  Percent of the time that escalators are operat...         M   \n",
       "638  Percent of the time that escalators are operat...         M   \n",
       "639  Percent of the time that escalators are operat...         M   \n",
       "640  Percent of the time that escalators are operat...         M   \n",
       "641  Percent of the time that escalators are operat...         M   \n",
       "642  Percent of the time that escalators are operat...         M   \n",
       "643  Percent of the time that escalators are operat...         M   \n",
       "644  Percent of the time that escalators are operat...         M   \n",
       "645  Percent of the time that escalators are operat...         M   \n",
       "646  Percent of the time that escalators are operat...         M   \n",
       "647  Percent of the time that escalators are operat...         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",
       "5    On-Time Performance (West of Hudson)              %           94.4   \n",
       "6    On-Time Performance (West of Hudson)              %             96   \n",
       "7    On-Time Performance (West of Hudson)              %           96.4   \n",
       "8    On-Time Performance (West of Hudson)              %           93.7   \n",
       "9    On-Time Performance (West of Hudson)              %           96.4   \n",
       "10   On-Time Performance (West of Hudson)              %           96.9   \n",
       "11   On-Time Performance (West of Hudson)              %           95.1   \n",
       "12   On-Time Performance (West of Hudson)              %           92.6   \n",
       "13   On-Time Performance (West of Hudson)              %           96.8   \n",
       "14   On-Time Performance (West of Hudson)              %           96.9   \n",
       "15   On-Time Performance (West of Hudson)              %           97.1   \n",
       "16   On-Time Performance (West of Hudson)              %           97.8   \n",
       "17   On-Time Performance (West of Hudson)              %           97.3   \n",
       "18   On-Time Performance (West of Hudson)              %           96.7   \n",
       "19   On-Time Performance (West of Hudson)              %           95.7   \n",
       "20   On-Time Performance (West of Hudson)              %           96.1   \n",
       "21   On-Time Performance (West of Hudson)              %           94.8   \n",
       "22   On-Time Performance (West of Hudson)              %           95.7   \n",
       "23   On-Time Performance (West of Hudson)              %             95   \n",
       "24   On-Time Performance (West of Hudson)              %             98   \n",
       "25   On-Time Performance (West of Hudson)              %             93   \n",
       "26   On-Time Performance (West of Hudson)              %           96.9   \n",
       "27   On-Time Performance (West of Hudson)              %           98.1   \n",
       "28   On-Time Performance (West of Hudson)              %           97.6   \n",
       "29   On-Time Performance (West of Hudson)              %           97.4   \n",
       "..                                    ...            ...            ...   \n",
       "618                Escalator Availability              %             94   \n",
       "619                Escalator Availability              %             97   \n",
       "620                Escalator Availability              %           98.3   \n",
       "621                Escalator Availability              %           98.7   \n",
       "622                Escalator Availability              %           98.1   \n",
       "623                Escalator Availability              %            100   \n",
       "624                Escalator Availability              %          97.95   \n",
       "625                Escalator Availability              %            100   \n",
       "626                Escalator Availability              %            100   \n",
       "627                Escalator Availability              %            100   \n",
       "628                Escalator Availability              %            100   \n",
       "629                Escalator Availability              %          91.21   \n",
       "630                Escalator Availability              %            100   \n",
       "631                Escalator Availability              %            100   \n",
       "632                Escalator Availability              %           95.2   \n",
       "633                Escalator Availability              %          90.91   \n",
       "634                Escalator Availability              %          96.67   \n",
       "635                Escalator Availability              %          90.03   \n",
       "636                Escalator Availability              %            100   \n",
       "637                Escalator Availability              %            100   \n",
       "638                Escalator Availability              %          97.07   \n",
       "639                Escalator Availability              %          98.18   \n",
       "640                Escalator Availability              %          79.18   \n",
       "641                Escalator Availability              %                  \n",
       "642                Escalator Availability              %                  \n",
       "643                Escalator Availability              %                  \n",
       "644                Escalator Availability              %                  \n",
       "645                Escalator Availability              %                  \n",
       "646                Escalator Availability              %                  \n",
       "647                Escalator Availability              %                  \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  \n",
       "5               95             6         2008       96.2         95  \n",
       "6               95             7         2008       96.2         95  \n",
       "7               95             8         2008       96.2         95  \n",
       "8               95             9         2008       95.9         95  \n",
       "9               95            10         2008         96         95  \n",
       "10              95            11         2008       96.1         95  \n",
       "11              95            12         2008         96         95  \n",
       "12            96.2             1         2009       92.6       96.2  \n",
       "13            96.2             2         2009       94.6       96.2  \n",
       "14            96.2             3         2009       95.4       96.2  \n",
       "15            96.2             4         2009       95.9       96.2  \n",
       "16            96.2             5         2009       96.2       96.2  \n",
       "17            96.2             6         2009       96.4       96.2  \n",
       "18            96.2             7         2009       96.5       96.2  \n",
       "19            96.2             8         2009       96.4       96.2  \n",
       "20            96.2             9         2009       96.3       96.2  \n",
       "21            96.2            10         2009       96.2       96.2  \n",
       "22            96.2            11         2009       96.1       96.2  \n",
       "23            96.2            12         2009         96       96.2  \n",
       "24            96.3             1         2010         98       96.3  \n",
       "25            96.3             2         2010       95.6       96.3  \n",
       "26            96.3             3         2010       96.1       96.3  \n",
       "27            96.3             4         2010       96.6       96.3  \n",
       "28            96.3             5         2010       96.8       96.3  \n",
       "29            96.3             6         2010       96.9       96.3  \n",
       "..             ...           ...          ...        ...        ...  \n",
       "618                            7         2009      95.14             \n",
       "619                            8         2009      95.38             \n",
       "620                            9         2009       95.7             \n",
       "621                           10         2009         96             \n",
       "622                           11         2009      96.21             \n",
       "623                           12         2009       96.5             \n",
       "624             97             1         2010      97.95         97  \n",
       "625             97             2         2010      98.92         97  \n",
       "626             97             3         2010      99.29         97  \n",
       "627             97             4         2010      99.47         97  \n",
       "628             97             5         2010      99.58         97  \n",
       "629             97             6         2010      98.19         97  \n",
       "630             97             7         2010      98.46         97  \n",
       "631             97             8         2010      98.69         97  \n",
       "632             97             9         2010       98.3         97  \n",
       "633             97            10         2010      97.55         97  \n",
       "634             97            11         2010      97.47         97  \n",
       "635             97            12         2010      96.84         97  \n",
       "636             97             1         2011        100         97  \n",
       "637             97             2         2011        100         97  \n",
       "638             97             3         2011      98.86         97  \n",
       "639             97             4         2011      98.76         97  \n",
       "640             97             5         2011      90.91         97  \n",
       "641             97             6         2011                    97  \n",
       "642             97             7         2011                    97  \n",
       "643             97             8         2011                    97  \n",
       "644             97             9         2011                    97  \n",
       "645             97            10         2011                    97  \n",
       "646             97            11         2011                    97  \n",
       "647             97            12         2011                    97  \n",
       "\n",
       "[648 rows x 12 columns]"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "perf = pd.DataFrame(data)\n",
    "perf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Element a at 0x1c1825324c8>"
      ]
     },
     "execution_count": 179,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import io\n",
    "tag = '<a href=\"http://www.google.com\">Google</a>'\n",
    "root = objectify.parse(io.StringIO(tag)).getroot()\n",
    "root"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'http://www.google.com'"
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "root.get('href')  # 获取连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Google'"
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "root.text  # 获取文本"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 二进制数据格式\n",
    "## 使用python内置的pickle序列化，pickle仅建议用于短期存储格式\n",
    "## df.to_pickle(file): 将数据以pickle形式保存在磁盘上\n",
    "## pd.read_pickle(file): 将pickle数据读回到python中"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>hello</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame = pd.read_csv(path+'ex1.csv',engine='python')\n",
    "frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将数据以pickle形式存储\n",
    "frame.to_pickle(path+'output_frame_pickle')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>hello</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将pickle数据读回到python中\n",
    "pd.read_pickle(path+'output_frame_pickle')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# HDF5格式,HDF5不是数据库，支持“一次写多次读”\n",
    "## HDF5中的HDF指的是层次型数据格式（hierarchical data format）\n",
    "## HDF5支持多种压缩器的即时压缩\n",
    "## 对于非常大的无法直接放入内存的数据集，HDF5可以高效地分块读写\n",
    "## pd.HDFStore(file):通过PyTables存储pandas对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<class 'pandas.io.pytables.HDFStore'>\n",
       "File path: output_mydata.h5"
      ]
     },
     "execution_count": 216,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "store = pd.HDFStore('output_mydata.h5')\n",
    "store.put('obj1', frame, format='table')  # format='table'指定存储格式，就不需要数据类型的转换\n",
    "store['obj1_col'] = frame['a']\n",
    "store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>hello</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 217,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# HDF5文件的对象可以通过于字典一样的方式进行获取\n",
    "store['obj1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1\n",
       "1    5\n",
       "2    9\n",
       "Name: a, dtype: int64"
      ]
     },
     "execution_count": 218,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "store['obj1_col'] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 读取Microsoft Excel 文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 223,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 通过传入一个xls或xlsx文件的路径即可创建一个ExcelFile实例\n",
    "xls_file = pd.ExcelFile(path+'data.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>something</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>two</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>three</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 224,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 存放在某个工作表中的数据可以通过parse读取到DataFrame中\n",
    "table = xls_file.parse('Sheet1')\n",
    "table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>something</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>two</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>world</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>three</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 230,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 吃饱饭excel中读取\n",
    "pd.read_excel(path+'data.xlsx','Sheet1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 写入Excel中\n",
    "writer = pd.ExcelWriter(path+'output_writer.xlsx')\n",
    "table.to_excel(writer,'Sheet1')\n",
    "writer.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 使用HTML和Web API\n",
    "## 使用requests包，发送一个HTTP GET 请求"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Response [200]>"
      ]
     },
     "execution_count": 243,
     "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": 247,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['url', 'repository_url', 'labels_url', 'comments_url', 'events_url', 'html_url', 'id', 'node_id', 'number', 'title', 'user', 'labels', 'state', 'locked', 'assignee', 'assignees', 'milestone', 'comments', 'created_at', 'updated_at', 'closed_at', 'author_association', 'body'])"
      ]
     },
     "execution_count": 247,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Response对象的text属性含有GET请求的内容，Web API 返回的大都是JSON字符串，需要加载到python对象中\n",
    "import json\n",
    "\n",
    "data = json.loads(resp.text)[0]\n",
    "data.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'pd.to_datetime does not work.'"
      ]
     },
     "execution_count": 252,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['title']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 使用数据库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3  # 嵌入式的SQLite数据库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 建表\n",
    "query = \"\"\"\n",
    "CREATE TABLE test\n",
    "(a VARCHAR(20), b VARCHAR(20),\n",
    "c REAL, d INTEGER\n",
    ");\"\"\"\n",
    "con = sqlite3.connect(':memory:')\n",
    "con.execute(query)\n",
    "con.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 插入几行数据\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",
    "con.executemany(stmt,data)\n",
    "con.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Atlanta', 'Georgia', 1.25, 6),\n",
       " ('Tallahassee', 'Florida', 2.6, 3),\n",
       " ('Sacramento', 'California', 1.7, 5)]"
      ]
     },
     "execution_count": 257,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor = con.execute('select * from test')\n",
    "rows = cursor.fetchall()\n",
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "metadata": {},
   "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": 261,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将rows传给DataFrame前，需要列名（位于游标的description属性中）\n",
    "cursor.description"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('a', 'b', 'c', 'd')"
      ]
     },
     "execution_count": 264,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(zip(*cursor.description))[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 266,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Atlanta</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>1.25</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tallahassee</td>\n",
       "      <td>Florida</td>\n",
       "      <td>2.60</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Sacramento</td>\n",
       "      <td>California</td>\n",
       "      <td>1.70</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 266,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(rows,columns=list(zip(*cursor.description))[0])  # 需要用list包装zip对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Atlanta</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>1.25</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tallahassee</td>\n",
       "      <td>Florida</td>\n",
       "      <td>2.60</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Sacramento</td>\n",
       "      <td>California</td>\n",
       "      <td>1.70</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 271,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from test',con)  # 将SQL语句读取结果转换为DataFrame对象\n",
    "# pd.read_sql_table()  # 将读取的整张数据表中数据转换成DataFrame对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 274,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Atlanta</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>1.25</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tallahassee</td>\n",
       "      <td>Florida</td>\n",
       "      <td>2.60</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Sacramento</td>\n",
       "      <td>California</td>\n",
       "      <td>1.70</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": 274,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 从sql中读取数据\n",
    "data = pd.read_sql('select * from test',con)\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 277,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to_sql()  将数据写入SQL数据库中test1表中，没有的该表，会自动新建\n",
    "data.to_sql('test1',con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</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>0</td>\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>1</td>\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>2</td>\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": [
       "   index            a           b     c  d\n",
       "0      0      Atlanta     Georgia  1.25  6\n",
       "1      1  Tallahassee     Florida  2.60  3\n",
       "2      2   Sacramento  California  1.70  5"
      ]
     },
     "execution_count": 279,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_sql('select * from test1',con)\n",
    "data"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}