6.3 Analyze Structured Data With SQL Query.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pandas\n",
    "df = pandas.read_csv('Data\\Region_Data.csv', encoding='gb2312', skiprows=3, skipfooter=2, engine = 'python')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>地区</th>\n",
       "      <th>2015年</th>\n",
       "      <th>2014年</th>\n",
       "      <th>2013年</th>\n",
       "      <th>2012年</th>\n",
       "      <th>2011年</th>\n",
       "      <th>2010年</th>\n",
       "      <th>2009年</th>\n",
       "      <th>2008年</th>\n",
       "      <th>2007年</th>\n",
       "      <th>2006年</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>北京市</td>\n",
       "      <td>23014.59</td>\n",
       "      <td>21330.83</td>\n",
       "      <td>19800.81</td>\n",
       "      <td>17879.40</td>\n",
       "      <td>16251.93</td>\n",
       "      <td>14113.58</td>\n",
       "      <td>12153.03</td>\n",
       "      <td>11115.00</td>\n",
       "      <td>9846.81</td>\n",
       "      <td>8117.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>天津市</td>\n",
       "      <td>16538.19</td>\n",
       "      <td>15726.93</td>\n",
       "      <td>14442.01</td>\n",
       "      <td>12893.88</td>\n",
       "      <td>11307.28</td>\n",
       "      <td>9224.46</td>\n",
       "      <td>7521.85</td>\n",
       "      <td>6719.01</td>\n",
       "      <td>5252.76</td>\n",
       "      <td>4462.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>河北省</td>\n",
       "      <td>29806.11</td>\n",
       "      <td>29421.15</td>\n",
       "      <td>28442.95</td>\n",
       "      <td>26575.01</td>\n",
       "      <td>24515.76</td>\n",
       "      <td>20394.26</td>\n",
       "      <td>17235.48</td>\n",
       "      <td>16011.97</td>\n",
       "      <td>13607.32</td>\n",
       "      <td>11467.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>山西省</td>\n",
       "      <td>12766.49</td>\n",
       "      <td>12761.49</td>\n",
       "      <td>12665.25</td>\n",
       "      <td>12112.83</td>\n",
       "      <td>11237.55</td>\n",
       "      <td>9200.86</td>\n",
       "      <td>7358.31</td>\n",
       "      <td>7315.40</td>\n",
       "      <td>6024.45</td>\n",
       "      <td>4878.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>内蒙古自治区</td>\n",
       "      <td>17831.51</td>\n",
       "      <td>17770.19</td>\n",
       "      <td>16916.50</td>\n",
       "      <td>15880.58</td>\n",
       "      <td>14359.88</td>\n",
       "      <td>11672.00</td>\n",
       "      <td>9740.25</td>\n",
       "      <td>8496.20</td>\n",
       "      <td>6423.18</td>\n",
       "      <td>4944.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       地区     2015年     2014年     2013年     2012年     2011年     2010年  \\\n",
       "0     北京市  23014.59  21330.83  19800.81  17879.40  16251.93  14113.58   \n",
       "1     天津市  16538.19  15726.93  14442.01  12893.88  11307.28   9224.46   \n",
       "2     河北省  29806.11  29421.15  28442.95  26575.01  24515.76  20394.26   \n",
       "3     山西省  12766.49  12761.49  12665.25  12112.83  11237.55   9200.86   \n",
       "4  内蒙古自治区  17831.51  17770.19  16916.50  15880.58  14359.88  11672.00   \n",
       "\n",
       "      2009年     2008年     2007年     2006年  \n",
       "0  12153.03  11115.00   9846.81   8117.78  \n",
       "1   7521.85   6719.01   5252.76   4462.74  \n",
       "2  17235.48  16011.97  13607.32  11467.60  \n",
       "3   7358.31   7315.40   6024.45   4878.61  \n",
       "4   9740.25   8496.20   6423.18   4944.25  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = pandas.melt(df, col_level=0, id_vars='地区')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 310 entries, 0 to 309\n",
      "Data columns (total 3 columns):\n",
      "地区          310 non-null object\n",
      "variable    310 non-null object\n",
      "value       310 non-null float64\n",
      "dtypes: float64(1), object(2)\n",
      "memory usage: 7.3+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['variable'] = df['variable'].map(lambda e : int(e.strip('年')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.columns = ['area', 'year', 'gross_product']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import sqlite3 as lite\n",
    "with lite.connect('country_stat.sqlite') as db:\n",
    "    df.to_sql('regional_gross_product', con = db, if_exists='replace', index=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas\n",
    "with lite.connect('country_stat.sqlite') as db:\n",
    "    df2 = pandas.read_sql('SELECT area, gross_product FROM regional_gross_product WHERE year = 2015', con = db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>area</th>\n",
       "      <th>gross_product</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>北京市</td>\n",
       "      <td>23014.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>天津市</td>\n",
       "      <td>16538.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>河北省</td>\n",
       "      <td>29806.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>山西省</td>\n",
       "      <td>12766.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>内蒙古自治区</td>\n",
       "      <td>17831.51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     area  gross_product\n",
       "0     北京市       23014.59\n",
       "1     天津市       16538.19\n",
       "2     河北省       29806.11\n",
       "3     山西省       12766.49\n",
       "4  内蒙古自治区       17831.51"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>area</th>\n",
       "      <th>year</th>\n",
       "      <th>gross_product</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>广东省</td>\n",
       "      <td>2015</td>\n",
       "      <td>72812.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>江苏省</td>\n",
       "      <td>2015</td>\n",
       "      <td>70116.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>山东省</td>\n",
       "      <td>2015</td>\n",
       "      <td>63002.33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  area  year  gross_product\n",
       "0  广东省  2015       72812.55\n",
       "1  江苏省  2015       70116.38\n",
       "2  山东省  2015       63002.33"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas\n",
    "with lite.connect('country_stat.sqlite') as db:\n",
    "    df3 = pandas.read_sql('SELECT * FROM regional_gross_product WHERE year = 2015 ORDER BY gross_product DESC LIMIT 3', con = db)\n",
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>area</th>\n",
       "      <th>avg_gross_product</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>广东省</td>\n",
       "      <td>49403.249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>江苏省</td>\n",
       "      <td>45275.185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>山东省</td>\n",
       "      <td>42471.128</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  area  avg_gross_product\n",
       "0  广东省          49403.249\n",
       "1  江苏省          45275.185\n",
       "2  山东省          42471.128"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas\n",
    "with lite.connect('country_stat.sqlite') as db:\n",
    "    df4 = pandas.read_sql('SELECT area, AVG(gross_product) as avg_gross_product FROM regional_gross_product GROUP BY area HAVING avg_gross_product >= 10000 ORDER BY avg_gross_product DESC LIMIT 3', con = db)\n",
    "df4.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}