Last active
January 21, 2018 10:47
-
-
Save lightondust/94f53adc907a56b7861d8adcf816af68 to your computer and use it in GitHub Desktop.
jupyter_sql.ipynb
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "cells": [ | |
| { | |
| "metadata": {}, | |
| "cell_type": "markdown", | |
| "source": "gist:94f53adc907a56b7861d8adcf816af68" | |
| }, | |
| { | |
| "metadata": {}, | |
| "cell_type": "markdown", | |
| "source": "# magic commandでsqlできるようにする" | |
| }, | |
| { | |
| "metadata": {}, | |
| "cell_type": "markdown", | |
| "source": "必要なパッケージは \n```pip** install ipython-sql``` \nでインストール。mysqlを使う場合はpymysqlもインストールすること。" | |
| }, | |
| { | |
| "metadata": { | |
| "heading_collapsed": true | |
| }, | |
| "cell_type": "markdown", | |
| "source": "# autopandas有効" | |
| }, | |
| { | |
| "metadata": { | |
| "hidden": true | |
| }, | |
| "cell_type": "markdown", | |
| "source": "設定：" | |
| }, | |
| { | |
| "metadata": { | |
| "hidden": true, | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "%config SqlMagic.autopandas = True\n%config SqlMagic.feedback = False\n%load_ext sql", | |
| "execution_count": 17, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": "The sql extension is already loaded. To reload it, use:\n %reload_ext sql\n" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "hidden": true | |
| }, | |
| "cell_type": "markdown", | |
| "source": "接続（[sql種類]：//[ユーザー名]：[パスワード]@[接続先]/...）" | |
| }, | |
| { | |
| "metadata": { | |
| "hidden": true, | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "%sql mysql+pymysql://root@localhost/stock?charset=utf8mb4", | |
| "execution_count": 20, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/plain": "'Connected: root@stock'" | |
| }, | |
| "execution_count": 20, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "hidden": true | |
| }, | |
| "cell_type": "markdown", | |
| "source": "クエリーしてみる" | |
| }, | |
| { | |
| "metadata": { | |
| "hidden": true, | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "%%sql\n\nselect * from price limit 10;", | |
| "execution_count": 19, | |
| "outputs": [ | |
| { | |
| "ename": "ValueError", | |
| "evalue": "The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().", | |
| "output_type": "error", | |
| "traceback": [ | |
| "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", | |
| "\u001b[0;31mValueError\u001b[0m Traceback (most recent call last)", | |
| "\u001b[0;32m<ipython-input-19-c9ac116ded6d>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mget_ipython\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun_cell_magic\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'sql'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m''\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'\\nselect * from price limit 10;'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m", | |
| "\u001b[0;32m/usr/local/lib/python3.6/site-packages/IPython/core/interactiveshell.py\u001b[0m in \u001b[0;36mrun_cell_magic\u001b[0;34m(self, magic_name, line, cell)\u001b[0m\n\u001b[1;32m 2129\u001b[0m \u001b[0mmagic_arg_s\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mvar_expand\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mline\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstack_depth\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 2130\u001b[0m \u001b[0;32mwith\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbuiltin_trap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2131\u001b[0;31m \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmagic_arg_s\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcell\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 2132\u001b[0m \u001b[0;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 2133\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n", | |
| "\u001b[0;32m<decorator-gen-126>\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n", | |
| "\u001b[0;32m/usr/local/lib/python3.6/site-packages/IPython/core/magic.py\u001b[0m in \u001b[0;36m<lambda>\u001b[0;34m(f, *a, **k)\u001b[0m\n\u001b[1;32m 185\u001b[0m \u001b[0;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 186\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 187\u001b[0;31m \u001b[0mcall\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 188\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 189\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", | |
| "\u001b[0;32m<decorator-gen-125>\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n", | |
| "\u001b[0;32m/usr/local/lib/python3.6/site-packages/IPython/core/magic.py\u001b[0m in \u001b[0;36m<lambda>\u001b[0;34m(f, *a, **k)\u001b[0m\n\u001b[1;32m 185\u001b[0m \u001b[0;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 186\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 187\u001b[0;31m \u001b[0mcall\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 188\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 189\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", | |
| "\u001b[0;32m/usr/local/lib/python3.6/site-packages/sql/magic.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n\u001b[1;32m 87\u001b[0m \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparsed\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'sql'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0muser_ns\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 88\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 89\u001b[0;31m \u001b[0;32mif\u001b[0m \u001b[0mresult\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0;34m~\u001b[0m\u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresult\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstr\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumn_local_vars\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 90\u001b[0m \u001b[0;31m#Instead of returning values, set variables directly in the\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 91\u001b[0m \u001b[0;31m#users namespace. Variable names given by column names\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", | |
| "\u001b[0;32m/usr/local/lib64/python3.6/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36m__nonzero__\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m 1119\u001b[0m raise ValueError(\"The truth value of a {0} is ambiguous. \"\n\u001b[1;32m 1120\u001b[0m \u001b[0;34m\"Use a.empty, a.bool(), a.item(), a.any() or a.all().\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1121\u001b[0;31m .format(self.__class__.__name__))\n\u001b[0m\u001b[1;32m 1122\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 1123\u001b[0m \u001b[0m__bool__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m__nonzero__\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", | |
| "\u001b[0;31mValueError\u001b[0m: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()." | |
| ] | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "hidden": true | |
| }, | |
| "cell_type": "markdown", | |
| "source": "あっちゃ〜。\n\nこれはバグらしい： \nhttps://github.com/catherinedevlin/ipython-sql/issues/67\n\n新しいバージョンだと直っているとか。 \nautopandasにすると実行結果をpandasに渡してデータ整形してくれるが、そこで問題が起きた模様。 \nアップデートすべきだが、ここはとりあえず、autopandasを無効にしてやってみる。" | |
| }, | |
| { | |
| "metadata": {}, | |
| "cell_type": "markdown", | |
| "source": "# autopandas無効" | |
| }, | |
| { | |
| "metadata": {}, | |
| "cell_type": "markdown", | |
| "source": "autopandasを無効にして" | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "%config SqlMagic.autopandas = False\n%config SqlMagic.feedback = False\n%load_ext sql", | |
| "execution_count": 21, | |
| "outputs": [ | |
| { | |
| "name": "stdout", | |
| "output_type": "stream", | |
| "text": "The sql extension is already loaded. To reload it, use:\n %reload_ext sql\n" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": {}, | |
| "cell_type": "markdown", | |
| "source": "接続" | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "%sql mysql+pymysql://root@localhost/stock?charset=utf8mb4", | |
| "execution_count": 22, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/plain": "'Connected: root@stock'" | |
| }, | |
| "execution_count": 22, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": {}, | |
| "cell_type": "markdown", | |
| "source": "Query投げる" | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "%%sql\n\nselect * from price limit 10;", | |
| "execution_count": 23, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/html": "<table>\n <tr>\n <th>id</th>\n <th>code</th>\n <th>open</th>\n <th>last</th>\n <th>high</th>\n <th>low</th>\n <th>per</th>\n <th>date</th>\n </tr>\n <tr>\n <td>13012017/11/02</td>\n <td>1301</td>\n <td>None</td>\n <td>3700.0</td>\n <td>None</td>\n <td>None</td>\n <td>17.37</td>\n <td>2017-11-02</td>\n </tr>\n <tr>\n <td>13012017/11/06</td>\n <td>1301</td>\n <td>None</td>\n <td>3850.0</td>\n <td>None</td>\n <td>None</td>\n <td>18.07</td>\n <td>2017-11-06</td>\n </tr>\n <tr>\n <td>13012017/11/10</td>\n <td>1301</td>\n <td>None</td>\n <td>3620.0</td>\n <td>None</td>\n <td>None</td>\n <td>16.99</td>\n <td>2017-11-10</td>\n </tr>\n <tr>\n <td>13012017/11/14</td>\n <td>1301</td>\n <td>None</td>\n <td>3540.0</td>\n <td>None</td>\n <td>None</td>\n <td>16.61</td>\n <td>2017-11-14</td>\n </tr>\n <tr>\n <td>13012017/11/15</td>\n <td>1301</td>\n <td>None</td>\n <td>3545.0</td>\n <td>None</td>\n <td>None</td>\n <td>16.64</td>\n <td>2017-11-15</td>\n </tr>\n <tr>\n <td>13012017/11/16</td>\n <td>1301</td>\n <td>None</td>\n <td>3675.0</td>\n <td>None</td>\n <td>None</td>\n <td>17.25</td>\n <td>2017-11-16</td>\n </tr>\n <tr>\n <td>13012017/11/22</td>\n <td>1301</td>\n <td>None</td>\n <td>3645.0</td>\n <td>None</td>\n <td>None</td>\n <td>17.11</td>\n <td>2017-11-22</td>\n </tr>\n <tr>\n <td>13012017/11/6</td>\n <td>1301</td>\n <td>None</td>\n <td>3850.0</td>\n <td>None</td>\n <td>None</td>\n <td>18.07</td>\n <td>2017-11-06</td>\n </tr>\n <tr>\n <td>13012017/11/7</td>\n <td>1301</td>\n <td>None</td>\n <td>3710.0</td>\n <td>None</td>\n <td>None</td>\n <td>17.41</td>\n <td>2017-11-07</td>\n </tr>\n <tr>\n <td>13012017/11/8</td>\n <td>1301</td>\n <td>None</td>\n <td>3625.0</td>\n <td>None</td>\n <td>None</td>\n <td>17.01</td>\n <td>2017-11-08</td>\n </tr>\n</table>", | |
| "text/plain": "[('13012017/11/02', 1301, None, 3700.0, None, None, 17.37, datetime.date(2017, 11, 2)),\n ('13012017/11/06', 1301, None, 3850.0, None, None, 18.07, datetime.date(2017, 11, 6)),\n ('13012017/11/10', 1301, None, 3620.0, None, None, 16.99, datetime.date(2017, 11, 10)),\n ('13012017/11/14', 1301, None, 3540.0, None, None, 16.61, datetime.date(2017, 11, 14)),\n ('13012017/11/15', 1301, None, 3545.0, None, None, 16.64, datetime.date(2017, 11, 15)),\n ('13012017/11/16', 1301, None, 3675.0, None, None, 17.25, datetime.date(2017, 11, 16)),\n ('13012017/11/22', 1301, None, 3645.0, None, None, 17.11, datetime.date(2017, 11, 22)),\n ('13012017/11/6', 1301, None, 3850.0, None, None, 18.07, datetime.date(2017, 11, 6)),\n ('13012017/11/7', 1301, None, 3710.0, None, None, 17.41, datetime.date(2017, 11, 7)),\n ('13012017/11/8', 1301, None, 3625.0, None, None, 17.01, datetime.date(2017, 11, 8))]" | |
| }, | |
| "execution_count": 23, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": {}, | |
| "cell_type": "markdown", | |
| "source": "取得結果を変数に渡して処理することもできる" | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "res = %sql select * from price where date='20180105';", | |
| "execution_count": 24, | |
| "outputs": [] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "type(res)", | |
| "execution_count": 25, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/plain": "sql.run.ResultSet" | |
| }, | |
| "execution_count": 25, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": {}, | |
| "cell_type": "markdown", | |
| "source": "値は何やらsql用のオブジェクトになっている模様" | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "len(res)", | |
| "execution_count": 26, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/plain": "3162" | |
| }, | |
| "execution_count": 26, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "res[1:3]", | |
| "execution_count": 27, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/plain": "[('13322018/01/05', 1332, None, 590.0, None, None, 12.28, datetime.date(2018, 1, 5)),\n ('13522018/01/05', 1352, None, 1195.0, None, None, 14.17, datetime.date(2018, 1, 5))]" | |
| }, | |
| "execution_count": 27, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "type(res[1])", | |
| "execution_count": 28, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/plain": "sqlalchemy.engine.result.RowProxy" | |
| }, | |
| "execution_count": 28, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": {}, | |
| "cell_type": "markdown", | |
| "source": "かなりリストっぽい" | |
| }, | |
| { | |
| "metadata": {}, | |
| "cell_type": "markdown", | |
| "source": "## 結果をpandasに渡してみる" | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "import pandas as pd", | |
| "execution_count": 29, | |
| "outputs": [] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "res_frame = pd.DataFrame(res)", | |
| "execution_count": 30, | |
| "outputs": [] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "len(res_frame)", | |
| "execution_count": 31, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/plain": "3162" | |
| }, | |
| "execution_count": 31, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": {}, | |
| "cell_type": "markdown", | |
| "source": "DataFrameにすると「.head()」で最初のデータだけを表示させることができる" | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "res_frame.head()", | |
| "execution_count": 33, | |
| "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 <th>5</th>\n <th>6</th>\n <th>7</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>13012018/01/05</td>\n <td>1301</td>\n <td>None</td>\n <td>4340.0</td>\n <td>None</td>\n <td>None</td>\n <td>20.37</td>\n <td>2018-01-05</td>\n </tr>\n <tr>\n <th>1</th>\n <td>13322018/01/05</td>\n <td>1332</td>\n <td>None</td>\n <td>590.0</td>\n <td>None</td>\n <td>None</td>\n <td>12.28</td>\n <td>2018-01-05</td>\n </tr>\n <tr>\n <th>2</th>\n <td>13522018/01/05</td>\n <td>1352</td>\n <td>None</td>\n <td>1195.0</td>\n <td>None</td>\n <td>None</td>\n <td>14.17</td>\n <td>2018-01-05</td>\n </tr>\n <tr>\n <th>3</th>\n <td>13762018/01/05</td>\n <td>1376</td>\n <td>None</td>\n <td>1769.0</td>\n <td>None</td>\n <td>None</td>\n <td>13.94</td>\n <td>2018-01-05</td>\n </tr>\n <tr>\n <th>4</th>\n <td>13772018/01/05</td>\n <td>1377</td>\n <td>None</td>\n <td>4030.0</td>\n <td>None</td>\n <td>None</td>\n <td>29.66</td>\n <td>2018-01-05</td>\n </tr>\n </tbody>\n</table>\n</div>", | |
| "text/plain": " 0 1 2 3 4 5 6 7\n0 13012018/01/05 1301 None 4340.0 None None 20.37 2018-01-05\n1 13322018/01/05 1332 None 590.0 None None 12.28 2018-01-05\n2 13522018/01/05 1352 None 1195.0 None None 14.17 2018-01-05\n3 13762018/01/05 1376 None 1769.0 None None 13.94 2018-01-05\n4 13772018/01/05 1377 None 4030.0 None None 29.66 2018-01-05" | |
| }, | |
| "execution_count": 33, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": {}, | |
| "cell_type": "markdown", | |
| "source": "カラム名がなくなっている〜\n\nカラム名を見てみる：" | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "res_frame.columns", | |
| "execution_count": 34, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/plain": "RangeIndex(start=0, stop=8, step=1)" | |
| }, | |
| "execution_count": 34, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": {}, | |
| "cell_type": "markdown", | |
| "source": "やっぱりなくなっている。。。\n\nカラム名を手で渡してみる：" | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "data = %sql select code, name from stock_info;", | |
| "execution_count": 35, | |
| "outputs": [] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "data_frame = pd.DataFrame(data, columns=[\"code\",\"name\"])", | |
| "execution_count": 36, | |
| "outputs": [] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "data_frame.head()", | |
| "execution_count": 37, | |
| "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>code</th>\n <th>name</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>1301</td>\n <td>極洋</td>\n </tr>\n <tr>\n <th>1</th>\n <td>1332</td>\n <td>日本水産</td>\n </tr>\n <tr>\n <th>2</th>\n <td>1334</td>\n <td>マルハニチロホールディングス</td>\n </tr>\n <tr>\n <th>3</th>\n <td>1352</td>\n <td>ホウスイ</td>\n </tr>\n <tr>\n <th>4</th>\n <td>1376</td>\n <td>カネコ種苗</td>\n </tr>\n </tbody>\n</table>\n</div>", | |
| "text/plain": " code name\n0 1301 極洋\n1 1332 日本水産\n2 1334 マルハニチロホールディングス\n3 1352 ホウスイ\n4 1376 カネコ種苗" | |
| }, | |
| "execution_count": 37, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": {}, | |
| "cell_type": "markdown", | |
| "source": "describeで統計量をまとめてくれる" | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "data_frame.describe()", | |
| "execution_count": 41, | |
| "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>code</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>count</th>\n <td>3610.000000</td>\n </tr>\n <tr>\n <th>mean</th>\n <td>5896.647922</td>\n </tr>\n <tr>\n <th>std</th>\n <td>2474.945915</td>\n </tr>\n <tr>\n <th>min</th>\n <td>1301.000000</td>\n </tr>\n <tr>\n <th>25%</th>\n <td>3710.250000</td>\n </tr>\n <tr>\n <th>50%</th>\n <td>6277.500000</td>\n </tr>\n <tr>\n <th>75%</th>\n <td>7985.750000</td>\n </tr>\n <tr>\n <th>max</th>\n <td>9997.000000</td>\n </tr>\n </tbody>\n</table>\n</div>", | |
| "text/plain": " code\ncount 3610.000000\nmean 5896.647922\nstd 2474.945915\nmin 1301.000000\n25% 3710.250000\n50% 6277.500000\n75% 7985.750000\nmax 9997.000000" | |
| }, | |
| "execution_count": 41, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "res_frame.describe()", | |
| "execution_count": 42, | |
| "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>1</th>\n <th>3</th>\n <th>6</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>count</th>\n <td>3162.000000</td>\n <td>3162.000000</td>\n <td>3139.000000</td>\n </tr>\n <tr>\n <th>mean</th>\n <td>5885.274194</td>\n <td>2285.654491</td>\n <td>23.833122</td>\n </tr>\n <tr>\n <th>std</th>\n <td>2459.487036</td>\n <td>3025.831763</td>\n <td>71.453577</td>\n </tr>\n <tr>\n <th>min</th>\n <td>1301.000000</td>\n <td>12.000000</td>\n <td>-872.300000</td>\n </tr>\n <tr>\n <th>25%</th>\n <td>3741.250000</td>\n <td>787.500000</td>\n <td>10.925000</td>\n </tr>\n <tr>\n <th>50%</th>\n <td>6270.000000</td>\n <td>1579.000000</td>\n <td>17.310000</td>\n </tr>\n <tr>\n <th>75%</th>\n <td>7965.750000</td>\n <td>2845.750000</td>\n <td>27.605000</td>\n </tr>\n <tr>\n <th>max</th>\n <td>9997.000000</td>\n <td>66020.000000</td>\n <td>929.080000</td>\n </tr>\n </tbody>\n</table>\n</div>", | |
| "text/plain": " 1 3 6\ncount 3162.000000 3162.000000 3139.000000\nmean 5885.274194 2285.654491 23.833122\nstd 2459.487036 3025.831763 71.453577\nmin 1301.000000 12.000000 -872.300000\n25% 3741.250000 787.500000 10.925000\n50% 6270.000000 1579.000000 17.310000\n75% 7965.750000 2845.750000 27.605000\nmax 9997.000000 66020.000000 929.080000" | |
| }, | |
| "execution_count": 42, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": {}, | |
| "cell_type": "markdown", | |
| "source": "DataFrame.valuesでnumpy表記にできる" | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "data_values=data_frame.values", | |
| "execution_count": 38, | |
| "outputs": [] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "type(data_values)", | |
| "execution_count": 39, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/plain": "numpy.ndarray" | |
| }, | |
| "execution_count": 39, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "data_values[1]", | |
| "execution_count": 40, | |
| "outputs": [ | |
| { | |
| "data": { | |
| "text/plain": "array([1332, '日本水産'], dtype=object)" | |
| }, | |
| "execution_count": 40, | |
| "metadata": {}, | |
| "output_type": "execute_result" | |
| } | |
| ] | |
| }, | |
| { | |
| "metadata": { | |
| "trusted": true | |
| }, | |
| "cell_type": "code", | |
| "source": "", | |
| "execution_count": null, | |
| "outputs": [] | |
| } | |
| ], | |
| "metadata": { | |
| "gist": { | |
| "id": "", | |
| "data": { | |
| "description": "jupyter_sql.ipynb", | |
| "public": true | |
| } | |
| }, | |
| "kernelspec": { | |
| "name": "python3", | |
| "display_name": "Python 3", | |
| "language": "python" | |
| }, | |
| "language_info": { | |
| "name": "python", | |
| "version": "3.6.2", | |
| "mimetype": "text/x-python", | |
| "codemirror_mode": { | |
| "name": "ipython", | |
| "version": 3 | |
| }, | |
| "pygments_lexer": "ipython3", | |
| "nbconvert_exporter": "python", | |
| "file_extension": ".py" | |
| }, | |
| "toc": { | |
| "nav_menu": {}, | |
| "number_sections": true, | |
| "sideBar": false, | |
| "skip_h1_title": false, | |
| "toc_cell": false, | |
| "toc_position": { | |
| "height": "365px", | |
| "left": "1146px", | |
| "right": "82px", | |
| "top": "110px", | |
| "width": "212px" | |
| }, | |
| "toc_section_display": "block", | |
| "toc_window_display": true | |
| } | |
| }, | |
| "nbformat": 4, | |
| "nbformat_minor": 2 | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment