Sample Code/Day_09_SampleCode.ipynb

{"nbformat":4,"nbformat_minor":0,"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.8"},"colab":{"name":"pandas_io.ipynb","provenance":[],"collapsed_sections":[]}},"cells":[{"cell_type":"markdown","metadata":{"id":"APCGOGRZit3t"},"source":["範例目標:<br>\r\n","1. 讀寫csv\r\n","2. 讀寫excel\r\n","3. 讀寫json\r\n","4. 讀寫SQL資料庫\r\n"]},{"cell_type":"markdown","metadata":{"id":"24oDpwO3jFIS"},"source":["範例重點:<br>\r\n","1. 讀寫不同檔案格式會有不同程式可以達到csv(read_csv、to_csv)、excel(read_excel、to_excel)、json(read_json、to_json)、SQL資料庫(io.sql.read_sql、to_sql)\r\n","2. 讀取進來後都是DataFrame的型態"]},{"cell_type":"code","metadata":{"id":"9sPIu6cjiv1i"},"source":["import pandas as pd"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"EsI8xBztwMYF","outputId":"3cb6158c-49fb-47f9-9563-cd45d26150ae"},"source":["!pip install openpyxl\n","!pip install XLRD"],"execution_count":null,"outputs":[{"output_type":"stream","text":["Defaulting to user installation because normal site-packages is not writeable\n","Collecting openpyxl\n","  Downloading openpyxl-3.0.5-py2.py3-none-any.whl (242 kB)\n","\u001b[K     |████████████████████████████████| 242 kB 3.7 MB/s eta 0:00:01\n","\u001b[?25hCollecting jdcal\n","  Downloading jdcal-1.4.1-py2.py3-none-any.whl (9.5 kB)\n","Collecting et-xmlfile\n","  Downloading et_xmlfile-1.0.1.tar.gz (8.4 kB)\n","Building wheels for collected packages: et-xmlfile\n","  Building wheel for et-xmlfile (setup.py) ... \u001b[?25ldone\n","\u001b[?25h  Created wheel for et-xmlfile: filename=et_xmlfile-1.0.1-py3-none-any.whl size=8917 sha256=7584453fbec4cd81222e7dd792129bc80a7a7442380ac5707e7babee9f0e939f\n","  Stored in directory: /home/hong/.cache/pip/wheels/ef/92/e0/956c00decf3091294b8800d9104f6169a1adacfaafd122ed42\n","Successfully built et-xmlfile\n","Installing collected packages: jdcal, et-xmlfile, openpyxl\n","Successfully installed et-xmlfile-1.0.1 jdcal-1.4.1 openpyxl-3.0.5\n","\u001b[33mWARNING: You are using pip version 20.2.3; however, version 20.2.4 is available.\n","You should consider upgrading via the '/usr/bin/python3 -m pip install --upgrade pip' command.\u001b[0m\n","Defaulting to user installation because normal site-packages is not writeable\n","Requirement already satisfied: XLRD in /home/hong/.local/lib/python3.6/site-packages (1.2.0)\n","\u001b[33mWARNING: You are using pip version 20.2.3; however, version 20.2.4 is available.\n","You should consider upgrading via the '/usr/bin/python3 -m pip install --upgrade pip' command.\u001b[0m\n"],"name":"stdout"}]},{"cell_type":"code","metadata":{"id":"4L18MEmBwMYH","outputId":"e4cee03c-e11d-4bc6-dd21-d6453c470691"},"source":["iris_data = pd.read_csv('iris.csv')\n","iris_data"],"execution_count":null,"outputs":[{"output_type":"execute_result","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>sepal length</th>\n","      <th>sepal width</th>\n","      <th>petal length</th>\n","      <th>petal width</th>\n","      <th>target</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>5.1</td>\n","      <td>3.5</td>\n","      <td>1.4</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>4.9</td>\n","      <td>3.0</td>\n","      <td>1.4</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>4.7</td>\n","      <td>3.2</td>\n","      <td>1.3</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>4.6</td>\n","      <td>3.1</td>\n","      <td>1.5</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>5.0</td>\n","      <td>3.6</td>\n","      <td>1.4</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>145</th>\n","      <td>6.7</td>\n","      <td>3.0</td>\n","      <td>5.2</td>\n","      <td>2.3</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>146</th>\n","      <td>6.3</td>\n","      <td>2.5</td>\n","      <td>5.0</td>\n","      <td>1.9</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>147</th>\n","      <td>6.5</td>\n","      <td>3.0</td>\n","      <td>5.2</td>\n","      <td>2.0</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>148</th>\n","      <td>6.2</td>\n","      <td>3.4</td>\n","      <td>5.4</td>\n","      <td>2.3</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>149</th>\n","      <td>5.9</td>\n","      <td>3.0</td>\n","      <td>5.1</td>\n","      <td>1.8</td>\n","      <td>2</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>150 rows × 5 columns</p>\n","</div>"],"text/plain":["     sepal length  sepal width  petal length  petal width  target\n","0             5.1          3.5           1.4          0.2       0\n","1             4.9          3.0           1.4          0.2       0\n","2             4.7          3.2           1.3          0.2       0\n","3             4.6          3.1           1.5          0.2       0\n","4             5.0          3.6           1.4          0.2       0\n","..            ...          ...           ...          ...     ...\n","145           6.7          3.0           5.2          2.3       2\n","146           6.3          2.5           5.0          1.9       2\n","147           6.5          3.0           5.2          2.0       2\n","148           6.2          3.4           5.4          2.3       2\n","149           5.9          3.0           5.1          1.8       2\n","\n","[150 rows x 5 columns]"]},"metadata":{"tags":[]},"execution_count":2}]},{"cell_type":"code","metadata":{"id":"0gFFN9BLwMYI","outputId":"307f932c-c771-45c1-fdc0-76147963630c"},"source":["iris_data = pd.read_csv('iris.csv',usecols=['petal length','petal width','target'])\n","iris_data"],"execution_count":null,"outputs":[{"output_type":"execute_result","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>petal length</th>\n","      <th>petal width</th>\n","      <th>target</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>1.4</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>1.4</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>1.3</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>1.5</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>1.4</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>145</th>\n","      <td>5.2</td>\n","      <td>2.3</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>146</th>\n","      <td>5.0</td>\n","      <td>1.9</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>147</th>\n","      <td>5.2</td>\n","      <td>2.0</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>148</th>\n","      <td>5.4</td>\n","      <td>2.3</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>149</th>\n","      <td>5.1</td>\n","      <td>1.8</td>\n","      <td>2</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>150 rows × 3 columns</p>\n","</div>"],"text/plain":["     petal length  petal width  target\n","0             1.4          0.2       0\n","1             1.4          0.2       0\n","2             1.3          0.2       0\n","3             1.5          0.2       0\n","4             1.4          0.2       0\n","..            ...          ...     ...\n","145           5.2          2.3       2\n","146           5.0          1.9       2\n","147           5.2          2.0       2\n","148           5.4          2.3       2\n","149           5.1          1.8       2\n","\n","[150 rows x 3 columns]"]},"metadata":{"tags":[]},"execution_count":3}]},{"cell_type":"code","metadata":{"id":"Ra41PMcYwMYJ","outputId":"bdd98892-25da-436d-de2c-feea763aa583"},"source":["iris_data = pd.read_csv('iris.csv',header=0,names=['featrue1','featrue2','featrue3','featrue4','target'])\n","iris_data"],"execution_count":null,"outputs":[{"output_type":"execute_result","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>featrue1</th>\n","      <th>featrue2</th>\n","      <th>featrue3</th>\n","      <th>featrue4</th>\n","      <th>target</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>5.1</td>\n","      <td>3.5</td>\n","      <td>1.4</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>4.9</td>\n","      <td>3.0</td>\n","      <td>1.4</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>4.7</td>\n","      <td>3.2</td>\n","      <td>1.3</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>4.6</td>\n","      <td>3.1</td>\n","      <td>1.5</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>5.0</td>\n","      <td>3.6</td>\n","      <td>1.4</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>145</th>\n","      <td>6.7</td>\n","      <td>3.0</td>\n","      <td>5.2</td>\n","      <td>2.3</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>146</th>\n","      <td>6.3</td>\n","      <td>2.5</td>\n","      <td>5.0</td>\n","      <td>1.9</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>147</th>\n","      <td>6.5</td>\n","      <td>3.0</td>\n","      <td>5.2</td>\n","      <td>2.0</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>148</th>\n","      <td>6.2</td>\n","      <td>3.4</td>\n","      <td>5.4</td>\n","      <td>2.3</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>149</th>\n","      <td>5.9</td>\n","      <td>3.0</td>\n","      <td>5.1</td>\n","      <td>1.8</td>\n","      <td>2</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>150 rows × 5 columns</p>\n","</div>"],"text/plain":["     featrue1  featrue2  featrue3  featrue4  target\n","0         5.1       3.5       1.4       0.2       0\n","1         4.9       3.0       1.4       0.2       0\n","2         4.7       3.2       1.3       0.2       0\n","3         4.6       3.1       1.5       0.2       0\n","4         5.0       3.6       1.4       0.2       0\n","..        ...       ...       ...       ...     ...\n","145       6.7       3.0       5.2       2.3       2\n","146       6.3       2.5       5.0       1.9       2\n","147       6.5       3.0       5.2       2.0       2\n","148       6.2       3.4       5.4       2.3       2\n","149       5.9       3.0       5.1       1.8       2\n","\n","[150 rows x 5 columns]"]},"metadata":{"tags":[]},"execution_count":4}]},{"cell_type":"code","metadata":{"id":"LPfpvXj9wMYJ"},"source":["iris_data.to_csv('my_iris.csv')"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"scrolled":true,"id":"8YQul365wMYK","outputId":"3f845864-b862-49f1-9235-8b6cd3f403a5"},"source":["data = pd.read_excel('data.xls')\n","data"],"execution_count":null,"outputs":[{"output_type":"execute_result","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>sepal length</th>\n","      <th>sepal width</th>\n","      <th>petal length</th>\n","      <th>petal width</th>\n","      <th>target</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>5.1</td>\n","      <td>3.5</td>\n","      <td>1.4</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>4.9</td>\n","      <td>3.0</td>\n","      <td>1.4</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>4.7</td>\n","      <td>3.2</td>\n","      <td>1.3</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>4.6</td>\n","      <td>3.1</td>\n","      <td>1.5</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>5.0</td>\n","      <td>3.6</td>\n","      <td>1.4</td>\n","      <td>0.2</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>145</th>\n","      <td>6.7</td>\n","      <td>3.0</td>\n","      <td>5.2</td>\n","      <td>2.3</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>146</th>\n","      <td>6.3</td>\n","      <td>2.5</td>\n","      <td>5.0</td>\n","      <td>1.9</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>147</th>\n","      <td>6.5</td>\n","      <td>3.0</td>\n","      <td>5.2</td>\n","      <td>2.0</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>148</th>\n","      <td>6.2</td>\n","      <td>3.4</td>\n","      <td>5.4</td>\n","      <td>2.3</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>149</th>\n","      <td>5.9</td>\n","      <td>3.0</td>\n","      <td>5.1</td>\n","      <td>1.8</td>\n","      <td>2</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>150 rows × 5 columns</p>\n","</div>"],"text/plain":["     sepal length  sepal width  petal length  petal width  target\n","0             5.1          3.5           1.4          0.2       0\n","1             4.9          3.0           1.4          0.2       0\n","2             4.7          3.2           1.3          0.2       0\n","3             4.6          3.1           1.5          0.2       0\n","4             5.0          3.6           1.4          0.2       0\n","..            ...          ...           ...          ...     ...\n","145           6.7          3.0           5.2          2.3       2\n","146           6.3          2.5           5.0          1.9       2\n","147           6.5          3.0           5.2          2.0       2\n","148           6.2          3.4           5.4          2.3       2\n","149           5.9          3.0           5.1          1.8       2\n","\n","[150 rows x 5 columns]"]},"metadata":{"tags":[]},"execution_count":26}]},{"cell_type":"code","metadata":{"id":"0mVs29stwMYK","outputId":"1cee0108-bde8-4313-bc44-64dd1ab7b07d"},"source":["boston_data = pd.read_excel('data.xls',sheet_name='boston')\n","boston_data"],"execution_count":null,"outputs":[{"output_type":"execute_result","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>CRIM</th>\n","      <th>ZN</th>\n","      <th>INDUS</th>\n","      <th>CHAS</th>\n","      <th>NOX</th>\n","      <th>RM</th>\n","      <th>AGE</th>\n","      <th>DIS</th>\n","      <th>RAD</th>\n","      <th>TAX</th>\n","      <th>PTRATIO</th>\n","      <th>B</th>\n","      <th>LSTAT</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>0.00632</td>\n","      <td>18.0</td>\n","      <td>2.31</td>\n","      <td>0</td>\n","      <td>0.538</td>\n","      <td>6.575</td>\n","      <td>65.2</td>\n","      <td>4.0900</td>\n","      <td>1</td>\n","      <td>296</td>\n","      <td>15.3</td>\n","      <td>396.90</td>\n","      <td>4.98</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>0.02731</td>\n","      <td>0.0</td>\n","      <td>7.07</td>\n","      <td>0</td>\n","      <td>0.469</td>\n","      <td>6.421</td>\n","      <td>78.9</td>\n","      <td>4.9671</td>\n","      <td>2</td>\n","      <td>242</td>\n","      <td>17.8</td>\n","      <td>396.90</td>\n","      <td>9.14</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>0.02729</td>\n","      <td>0.0</td>\n","      <td>7.07</td>\n","      <td>0</td>\n","      <td>0.469</td>\n","      <td>7.185</td>\n","      <td>61.1</td>\n","      <td>4.9671</td>\n","      <td>2</td>\n","      <td>242</td>\n","      <td>17.8</td>\n","      <td>392.83</td>\n","      <td>4.03</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>0.03237</td>\n","      <td>0.0</td>\n","      <td>2.18</td>\n","      <td>0</td>\n","      <td>0.458</td>\n","      <td>6.998</td>\n","      <td>45.8</td>\n","      <td>6.0622</td>\n","      <td>3</td>\n","      <td>222</td>\n","      <td>18.7</td>\n","      <td>394.63</td>\n","      <td>2.94</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>0.06905</td>\n","      <td>0.0</td>\n","      <td>2.18</td>\n","      <td>0</td>\n","      <td>0.458</td>\n","      <td>7.147</td>\n","      <td>54.2</td>\n","      <td>6.0622</td>\n","      <td>3</td>\n","      <td>222</td>\n","      <td>18.7</td>\n","      <td>396.90</td>\n","      <td>5.33</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","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>501</th>\n","      <td>0.06263</td>\n","      <td>0.0</td>\n","      <td>11.93</td>\n","      <td>0</td>\n","      <td>0.573</td>\n","      <td>6.593</td>\n","      <td>69.1</td>\n","      <td>2.4786</td>\n","      <td>1</td>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>391.99</td>\n","      <td>9.67</td>\n","    </tr>\n","    <tr>\n","      <th>502</th>\n","      <td>0.04527</td>\n","      <td>0.0</td>\n","      <td>11.93</td>\n","      <td>0</td>\n","      <td>0.573</td>\n","      <td>6.120</td>\n","      <td>76.7</td>\n","      <td>2.2875</td>\n","      <td>1</td>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>396.90</td>\n","      <td>9.08</td>\n","    </tr>\n","    <tr>\n","      <th>503</th>\n","      <td>0.06076</td>\n","      <td>0.0</td>\n","      <td>11.93</td>\n","      <td>0</td>\n","      <td>0.573</td>\n","      <td>6.976</td>\n","      <td>91.0</td>\n","      <td>2.1675</td>\n","      <td>1</td>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>396.90</td>\n","      <td>5.64</td>\n","    </tr>\n","    <tr>\n","      <th>504</th>\n","      <td>0.10959</td>\n","      <td>0.0</td>\n","      <td>11.93</td>\n","      <td>0</td>\n","      <td>0.573</td>\n","      <td>6.794</td>\n","      <td>89.3</td>\n","      <td>2.3889</td>\n","      <td>1</td>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>393.45</td>\n","      <td>6.48</td>\n","    </tr>\n","    <tr>\n","      <th>505</th>\n","      <td>0.04741</td>\n","      <td>0.0</td>\n","      <td>11.93</td>\n","      <td>0</td>\n","      <td>0.573</td>\n","      <td>6.030</td>\n","      <td>80.8</td>\n","      <td>2.5050</td>\n","      <td>1</td>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>396.90</td>\n","      <td>7.88</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>506 rows × 13 columns</p>\n","</div>"],"text/plain":["        CRIM    ZN  INDUS  CHAS    NOX     RM   AGE     DIS  RAD  TAX  \\\n","0    0.00632  18.0   2.31     0  0.538  6.575  65.2  4.0900    1  296   \n","1    0.02731   0.0   7.07     0  0.469  6.421  78.9  4.9671    2  242   \n","2    0.02729   0.0   7.07     0  0.469  7.185  61.1  4.9671    2  242   \n","3    0.03237   0.0   2.18     0  0.458  6.998  45.8  6.0622    3  222   \n","4    0.06905   0.0   2.18     0  0.458  7.147  54.2  6.0622    3  222   \n","..       ...   ...    ...   ...    ...    ...   ...     ...  ...  ...   \n","501  0.06263   0.0  11.93     0  0.573  6.593  69.1  2.4786    1  273   \n","502  0.04527   0.0  11.93     0  0.573  6.120  76.7  2.2875    1  273   \n","503  0.06076   0.0  11.93     0  0.573  6.976  91.0  2.1675    1  273   \n","504  0.10959   0.0  11.93     0  0.573  6.794  89.3  2.3889    1  273   \n","505  0.04741   0.0  11.93     0  0.573  6.030  80.8  2.5050    1  273   \n","\n","     PTRATIO       B  LSTAT  \n","0       15.3  396.90   4.98  \n","1       17.8  396.90   9.14  \n","2       17.8  392.83   4.03  \n","3       18.7  394.63   2.94  \n","4       18.7  396.90   5.33  \n","..       ...     ...    ...  \n","501     21.0  391.99   9.67  \n","502     21.0  396.90   9.08  \n","503     21.0  396.90   5.64  \n","504     21.0  393.45   6.48  \n","505     21.0  396.90   7.88  \n","\n","[506 rows x 13 columns]"]},"metadata":{"tags":[]},"execution_count":29}]},{"cell_type":"code","metadata":{"id":"LUruwgqNwMYL","outputId":"e7866192-b33d-40cf-d06d-36e72c8e471e"},"source":["boston_data = pd.read_excel('data.xls',sheet_name='boston',header=0 \\\n","                            ,usecols=['TAX','PTRATIO','B','LSTAT'])\n","boston_data"],"execution_count":null,"outputs":[{"output_type":"execute_result","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>TAX</th>\n","      <th>PTRATIO</th>\n","      <th>B</th>\n","      <th>LSTAT</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>296</td>\n","      <td>15.3</td>\n","      <td>396.90</td>\n","      <td>4.98</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>242</td>\n","      <td>17.8</td>\n","      <td>396.90</td>\n","      <td>9.14</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>242</td>\n","      <td>17.8</td>\n","      <td>392.83</td>\n","      <td>4.03</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>222</td>\n","      <td>18.7</td>\n","      <td>394.63</td>\n","      <td>2.94</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>222</td>\n","      <td>18.7</td>\n","      <td>396.90</td>\n","      <td>5.33</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>501</th>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>391.99</td>\n","      <td>9.67</td>\n","    </tr>\n","    <tr>\n","      <th>502</th>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>396.90</td>\n","      <td>9.08</td>\n","    </tr>\n","    <tr>\n","      <th>503</th>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>396.90</td>\n","      <td>5.64</td>\n","    </tr>\n","    <tr>\n","      <th>504</th>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>393.45</td>\n","      <td>6.48</td>\n","    </tr>\n","    <tr>\n","      <th>505</th>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>396.90</td>\n","      <td>7.88</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>506 rows × 4 columns</p>\n","</div>"],"text/plain":["     TAX  PTRATIO       B  LSTAT\n","0    296     15.3  396.90   4.98\n","1    242     17.8  396.90   9.14\n","2    242     17.8  392.83   4.03\n","3    222     18.7  394.63   2.94\n","4    222     18.7  396.90   5.33\n","..   ...      ...     ...    ...\n","501  273     21.0  391.99   9.67\n","502  273     21.0  396.90   9.08\n","503  273     21.0  396.90   5.64\n","504  273     21.0  393.45   6.48\n","505  273     21.0  396.90   7.88\n","\n","[506 rows x 4 columns]"]},"metadata":{"tags":[]},"execution_count":30}]},{"cell_type":"code","metadata":{"id":"CFPFsFVHwMYL"},"source":["boston_data.to_excel('my_boston.xlsx',sheet_name='boston')"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"KXqR-jB-wMYM","outputId":"3c342ac0-ab48-4df3-cbdd-b25052f696f8"},"source":["boston_data[:5]"],"execution_count":null,"outputs":[{"output_type":"execute_result","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>TAX</th>\n","      <th>PTRATIO</th>\n","      <th>B</th>\n","      <th>LSTAT</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>296</td>\n","      <td>15.3</td>\n","      <td>396.90</td>\n","      <td>4.98</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>242</td>\n","      <td>17.8</td>\n","      <td>396.90</td>\n","      <td>9.14</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>242</td>\n","      <td>17.8</td>\n","      <td>392.83</td>\n","      <td>4.03</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>222</td>\n","      <td>18.7</td>\n","      <td>394.63</td>\n","      <td>2.94</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>222</td>\n","      <td>18.7</td>\n","      <td>396.90</td>\n","      <td>5.33</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["   TAX  PTRATIO       B  LSTAT\n","0  296     15.3  396.90   4.98\n","1  242     17.8  396.90   9.14\n","2  242     17.8  392.83   4.03\n","3  222     18.7  394.63   2.94\n","4  222     18.7  396.90   5.33"]},"metadata":{"tags":[]},"execution_count":57}]},{"cell_type":"code","metadata":{"scrolled":true,"id":"EbwRp-P-wMYM"},"source":["import json"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"LOow-XtdwMYN","outputId":"52edbec3-fe68-45dd-b894-d27d6dbcdea4"},"source":["boston_data = pd.read_json('boston.json')\n","boston_data"],"execution_count":null,"outputs":[{"output_type":"execute_result","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>TAX</th>\n","      <th>PTRATIO</th>\n","      <th>B</th>\n","      <th>LSTAT</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>296</td>\n","      <td>15.3</td>\n","      <td>396.90</td>\n","      <td>4.98</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>242</td>\n","      <td>17.8</td>\n","      <td>396.90</td>\n","      <td>9.14</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>242</td>\n","      <td>17.8</td>\n","      <td>392.83</td>\n","      <td>4.03</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>222</td>\n","      <td>18.7</td>\n","      <td>394.63</td>\n","      <td>2.94</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>222</td>\n","      <td>18.7</td>\n","      <td>396.90</td>\n","      <td>5.33</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>501</th>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>391.99</td>\n","      <td>9.67</td>\n","    </tr>\n","    <tr>\n","      <th>502</th>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>396.90</td>\n","      <td>9.08</td>\n","    </tr>\n","    <tr>\n","      <th>503</th>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>396.90</td>\n","      <td>5.64</td>\n","    </tr>\n","    <tr>\n","      <th>504</th>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>393.45</td>\n","      <td>6.48</td>\n","    </tr>\n","    <tr>\n","      <th>505</th>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>396.90</td>\n","      <td>7.88</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>506 rows × 4 columns</p>\n","</div>"],"text/plain":["     TAX  PTRATIO       B  LSTAT\n","0    296     15.3  396.90   4.98\n","1    242     17.8  396.90   9.14\n","2    242     17.8  392.83   4.03\n","3    222     18.7  394.63   2.94\n","4    222     18.7  396.90   5.33\n","..   ...      ...     ...    ...\n","501  273     21.0  391.99   9.67\n","502  273     21.0  396.90   9.08\n","503  273     21.0  396.90   5.64\n","504  273     21.0  393.45   6.48\n","505  273     21.0  396.90   7.88\n","\n","[506 rows x 4 columns]"]},"metadata":{"tags":[]},"execution_count":70}]},{"cell_type":"code","metadata":{"id":"h46SH8PmwMYN"},"source":["boston_data.to_json('boston.json')"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"HSMabG9PwMYO"},"source":["import sqlite3"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"l_4VAStkwMYP"},"source":["boston_data = pd.read_excel('data.xls',sheet_name='boston',header=0 \\\n","                            ,usecols=['TAX','PTRATIO','B','LSTAT'])\n","connection = sqlite3.connect('sql_db.sqlite')\n","boston_data.to_sql('boston', connection, if_exists='replace')\n","connection.commit()\n","connection.close()"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"-MBFLaEAwMYQ","outputId":"57b1c3dd-3f3a-40a6-a244-812f3fc469cb"},"source":["connection = sqlite3.connect('sql_db.sqlite')\n","boston_data_sql = pd.io.sql.read_sql(\"select * from boston\",connection)\n","connection.close()\n","boston_data_sql"],"execution_count":null,"outputs":[{"output_type":"execute_result","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>TAX</th>\n","      <th>PTRATIO</th>\n","      <th>B</th>\n","      <th>LSTAT</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>0</td>\n","      <td>296</td>\n","      <td>15.3</td>\n","      <td>396.90</td>\n","      <td>4.98</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>1</td>\n","      <td>242</td>\n","      <td>17.8</td>\n","      <td>396.90</td>\n","      <td>9.14</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>2</td>\n","      <td>242</td>\n","      <td>17.8</td>\n","      <td>392.83</td>\n","      <td>4.03</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>3</td>\n","      <td>222</td>\n","      <td>18.7</td>\n","      <td>394.63</td>\n","      <td>2.94</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>4</td>\n","      <td>222</td>\n","      <td>18.7</td>\n","      <td>396.90</td>\n","      <td>5.33</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>501</th>\n","      <td>501</td>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>391.99</td>\n","      <td>9.67</td>\n","    </tr>\n","    <tr>\n","      <th>502</th>\n","      <td>502</td>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>396.90</td>\n","      <td>9.08</td>\n","    </tr>\n","    <tr>\n","      <th>503</th>\n","      <td>503</td>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>396.90</td>\n","      <td>5.64</td>\n","    </tr>\n","    <tr>\n","      <th>504</th>\n","      <td>504</td>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>393.45</td>\n","      <td>6.48</td>\n","    </tr>\n","    <tr>\n","      <th>505</th>\n","      <td>505</td>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>396.90</td>\n","      <td>7.88</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>506 rows × 5 columns</p>\n","</div>"],"text/plain":["     index  TAX  PTRATIO       B  LSTAT\n","0        0  296     15.3  396.90   4.98\n","1        1  242     17.8  396.90   9.14\n","2        2  242     17.8  392.83   4.03\n","3        3  222     18.7  394.63   2.94\n","4        4  222     18.7  396.90   5.33\n","..     ...  ...      ...     ...    ...\n","501    501  273     21.0  391.99   9.67\n","502    502  273     21.0  396.90   9.08\n","503    503  273     21.0  396.90   5.64\n","504    504  273     21.0  393.45   6.48\n","505    505  273     21.0  396.90   7.88\n","\n","[506 rows x 5 columns]"]},"metadata":{"tags":[]},"execution_count":83}]},{"cell_type":"code","metadata":{"id":"zzFmqe1LwMYR","outputId":"94d0ad0b-7b18-4a66-8786-a5a482d6eb30"},"source":["boston_data_sql"],"execution_count":null,"outputs":[{"output_type":"execute_result","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>TAX</th>\n","      <th>PTRATIO</th>\n","      <th>B</th>\n","      <th>LSTAT</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>0</td>\n","      <td>296</td>\n","      <td>15.3</td>\n","      <td>396.90</td>\n","      <td>4.98</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>1</td>\n","      <td>242</td>\n","      <td>17.8</td>\n","      <td>396.90</td>\n","      <td>9.14</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>2</td>\n","      <td>242</td>\n","      <td>17.8</td>\n","      <td>392.83</td>\n","      <td>4.03</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>3</td>\n","      <td>222</td>\n","      <td>18.7</td>\n","      <td>394.63</td>\n","      <td>2.94</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>4</td>\n","      <td>222</td>\n","      <td>18.7</td>\n","      <td>396.90</td>\n","      <td>5.33</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>501</th>\n","      <td>501</td>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>391.99</td>\n","      <td>9.67</td>\n","    </tr>\n","    <tr>\n","      <th>502</th>\n","      <td>502</td>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>396.90</td>\n","      <td>9.08</td>\n","    </tr>\n","    <tr>\n","      <th>503</th>\n","      <td>503</td>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>396.90</td>\n","      <td>5.64</td>\n","    </tr>\n","    <tr>\n","      <th>504</th>\n","      <td>504</td>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>393.45</td>\n","      <td>6.48</td>\n","    </tr>\n","    <tr>\n","      <th>505</th>\n","      <td>505</td>\n","      <td>273</td>\n","      <td>21.0</td>\n","      <td>396.90</td>\n","      <td>7.88</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>506 rows × 5 columns</p>\n","</div>"],"text/plain":["     index  TAX  PTRATIO       B  LSTAT\n","0        0  296     15.3  396.90   4.98\n","1        1  242     17.8  396.90   9.14\n","2        2  242     17.8  392.83   4.03\n","3        3  222     18.7  394.63   2.94\n","4        4  222     18.7  396.90   5.33\n","..     ...  ...      ...     ...    ...\n","501    501  273     21.0  391.99   9.67\n","502    502  273     21.0  396.90   9.08\n","503    503  273     21.0  396.90   5.64\n","504    504  273     21.0  393.45   6.48\n","505    505  273     21.0  396.90   7.88\n","\n","[506 rows x 5 columns]"]},"metadata":{"tags":[]},"execution_count":79}]}]}