examples/Example 6 - Types Read Write CSV SQL JSON HTML.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook you will get information about **Lemuras** data types and how to read and write **Table** objects in and from **CSV, SQL, JSON** and **HTML** formats."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sample data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Sample</b>, 5 columns, 6 rows.<br>\n",
       "<table>\n",
       "<tr><th>'type'</th><th>'size'</th><th>'weight'</th><th>'when'</th><th>'tel'</th></tr>\n",
       "<tr><td>'A'</td><td>1</td><td>12</td><td>2018-11-29 13:40:08.116768</td><td>'+79360360193'</td></tr>\n",
       "<tr><td>'B'</td><td>4</td><td>12</td><td>2018-10-30 13:40:08.116775</td><td>84505505151</td></tr>\n",
       "<tr><td>'A'</td><td>3</td><td>10</td><td>2018-10-15 13:40:08.116776</td><td>'+31415926535'</td></tr>\n",
       "<tr><td>'B'</td><td>6</td><td>14</td><td>2018-01-03 13:40:08.116777</td><td>0</td></tr>\n",
       "<tr><td>'A'</td><td>4</td><td>15</td><td>2018-05-07 13:40:08.116778</td><td>None</td></tr>\n",
       "<tr><td>'A'</td><td>2</td><td>11</td><td>2018-05-24 13:40:08.116779</td><td>''</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "- Table object, title: \"Sample\", 5 columns, 6 rows.\n",
       "'type' 'size' 'weight' 'when' 'tel'\n",
       "'A' 1 12 2018-11-29 13:40:08.116768 '+79360360193'\n",
       "'B' 4 12 2018-10-30 13:40:08.116775 84505505151\n",
       "'A' 3 10 2018-10-15 13:40:08.116776 '+31415926535'\n",
       "'B' 6 14 2018-01-03 13:40:08.116777 0\n",
       "'A' 4 15 2018-05-07 13:40:08.116778 None\n",
       "'A' 2 11 2018-05-24 13:40:08.116779 ''"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from lemuras import Table\n",
    "from datetime import datetime, timedelta\n",
    "\n",
    "def mkdt(ds):\n",
    "    return datetime.now() - timedelta(days=ds)\n",
    "\n",
    "cols = ['type', 'size', 'weight', 'when', 'tel']\n",
    "rows = [\n",
    "  ['A', 1, 12, mkdt(3), '+79360360193'],\n",
    "  ['B', 4, 12, mkdt(33), 84505505151],\n",
    "  ['A', 3, 10, mkdt(48), '+31415926535'],\n",
    "  ['B', 6, 14, mkdt(333), 0],\n",
    "  ['A', 4, 15, mkdt(209), None],\n",
    "  ['A', 2, 11, mkdt(192), ''],\n",
    "]\n",
    "df1 = Table(cols, rows, 'Sample')\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lemuras Table object consists of native Python lists, so, it can contain any objects that support **str** and **repr**. However, there is advanced built-in support for handling such types:\n",
    "\n",
    "- **int** – identifier **`i`**.\n",
    "- **float** – identifier **`f`**.\n",
    "- **str** – identifier **`s`**.\n",
    "- **date** – identifier **`d`**.\n",
    "- **datetime** – identifier **`t`**.\n",
    "\n",
    "Also, you can meet the identifier **`m`** which means that a column has multiple or mixed types."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The **`.get_type()`** method of **Column** object return tuple with the type identifier and maximum needed symbols length:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('i', 2)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1['weight'].get_type()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The **`.find_types()`** method of **Table** object return new **Table** with types of each column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Types</b>, 3 columns, 5 rows.<br>\n",
       "<table>\n",
       "<tr><th>'Column'</th><th>'Type'</th><th>'Symbols'</th></tr>\n",
       "<tr><td>'type'</td><td>'s'</td><td>1</td></tr>\n",
       "<tr><td>'size'</td><td>'i'</td><td>1</td></tr>\n",
       "<tr><td>'weight'</td><td>'i'</td><td>2</td></tr>\n",
       "<tr><td>'when'</td><td>'t'</td><td>26</td></tr>\n",
       "<tr><td>'tel'</td><td>'m'</td><td>12</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "- Table object, title: \"Types\", 3 columns, 5 rows.\n",
       "'Column' 'Type' 'Symbols'\n",
       "'type' 's' 1\n",
       "'size' 'i' 1\n",
       "'weight' 'i' 2\n",
       "'when' 't' 26\n",
       "'tel' 'm' 12"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.find_types()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Save CSV"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use **`.to_csv`** method to save the data as comma-separated-values. And if you specify an argument, it will be used as a filename for saving the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'type,size,weight,when,tel\\r\\nA,1,12,2018-11-29 13:40:08.116768,+79360360193\\r\\nB,4,12,2018-10-30 13:40:08.116775,84505505151\\r\\nA,3,10,2018-10-15 13:40:08.116776,+31415926535\\r\\nB,6,14,2018-01-03 13:40:08.116777,0\\r\\nA,4,15,2018-05-07 13:40:08.116778,\\r\\nA,2,11,2018-05-24 13:40:08.116779,\\r\\n'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "txt = df1.to_csv()\n",
    "txt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load CSV"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use **`.from_csv`** class method to create a Table object with given CSV data.\n",
    "\n",
    "If the argument **`inline`** is True, then the first argument is considered as CSV text itself. Otherwise, it is considered as a name of CSV file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>SomeData</b>, 5 columns, 6 rows.<br>\n",
       "<table>\n",
       "<tr><th>'type'</th><th>'size'</th><th>'weight'</th><th>'when'</th><th>'tel'</th></tr>\n",
       "<tr><td>'A'</td><td>1</td><td>12</td><td>2018-11-29 13:40:08</td><td>79360360193</td></tr>\n",
       "<tr><td>'B'</td><td>4</td><td>12</td><td>2018-10-30 13:40:08</td><td>84505505151</td></tr>\n",
       "<tr><td>'A'</td><td>3</td><td>10</td><td>2018-10-15 13:40:08</td><td>31415926535</td></tr>\n",
       "<tr><td>'B'</td><td>6</td><td>14</td><td>2018-01-03 13:40:08</td><td>0</td></tr>\n",
       "<tr><td>'A'</td><td>4</td><td>15</td><td>2018-05-07 13:40:08</td><td>''</td></tr>\n",
       "<tr><td>'A'</td><td>2</td><td>11</td><td>2018-05-24 13:40:08</td><td>''</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "- Table object, title: \"SomeData\", 5 columns, 6 rows.\n",
       "'type' 'size' 'weight' 'when' 'tel'\n",
       "'A' 1 12 2018-11-29 13:40:08 79360360193\n",
       "'B' 4 12 2018-10-30 13:40:08 84505505151\n",
       "'A' 3 10 2018-10-15 13:40:08 31415926535\n",
       "'B' 6 14 2018-01-03 13:40:08 0\n",
       "'A' 4 15 2018-05-07 13:40:08 ''\n",
       "'A' 2 11 2018-05-24 13:40:08 ''"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = Table.from_csv(txt, inline=True, title='SomeData')\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The types detected are the same as before serialization:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Types</b>, 3 columns, 5 rows.<br>\n",
       "<table>\n",
       "<tr><th>'Column'</th><th>'Type'</th><th>'Symbols'</th></tr>\n",
       "<tr><td>'type'</td><td>'s'</td><td>1</td></tr>\n",
       "<tr><td>'size'</td><td>'i'</td><td>1</td></tr>\n",
       "<tr><td>'weight'</td><td>'i'</td><td>2</td></tr>\n",
       "<tr><td>'when'</td><td>'t'</td><td>19</td></tr>\n",
       "<tr><td>'tel'</td><td>'m'</td><td>11</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "- Table object, title: \"Types\", 3 columns, 5 rows.\n",
       "'Column' 'Type' 'Symbols'\n",
       "'type' 's' 1\n",
       "'size' 'i' 1\n",
       "'weight' 'i' 2\n",
       "'when' 't' 19\n",
       "'tel' 'm' 11"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.find_types()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also, you can specify a value to replace **None** values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>OtherData</b>, 5 columns, 6 rows.<br>\n",
       "<table>\n",
       "<tr><th>'type'</th><th>'size'</th><th>'weight'</th><th>'when'</th><th>'tel'</th></tr>\n",
       "<tr><td>'A'</td><td>1</td><td>12</td><td>2018-11-29 13:40:08</td><td>79360360193</td></tr>\n",
       "<tr><td>'B'</td><td>4</td><td>12</td><td>2018-10-30 13:40:08</td><td>84505505151</td></tr>\n",
       "<tr><td>'A'</td><td>3</td><td>10</td><td>2018-10-15 13:40:08</td><td>31415926535</td></tr>\n",
       "<tr><td>'B'</td><td>6</td><td>14</td><td>2018-01-03 13:40:08</td><td>0</td></tr>\n",
       "<tr><td>'A'</td><td>4</td><td>15</td><td>2018-05-07 13:40:08</td><td>''</td></tr>\n",
       "<tr><td>'A'</td><td>2</td><td>11</td><td>2018-05-24 13:40:08</td><td>''</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "- Table object, title: \"OtherData\", 5 columns, 6 rows.\n",
       "'type' 'size' 'weight' 'when' 'tel'\n",
       "'A' 1 12 2018-11-29 13:40:08 79360360193\n",
       "'B' 4 12 2018-10-30 13:40:08 84505505151\n",
       "'A' 3 10 2018-10-15 13:40:08 31415926535\n",
       "'B' 6 14 2018-01-03 13:40:08 0\n",
       "'A' 4 15 2018-05-07 13:40:08 ''\n",
       "'A' 2 11 2018-05-24 13:40:08 ''"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = Table.from_csv(txt, inline=True, empty=0, title='OtherData')\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or, you can disable preprocessing of the data to leave the values as strings:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>from CSV</b>, 5 columns, 6 rows.<br>\n",
       "<table>\n",
       "<tr><th>'type'</th><th>'size'</th><th>'weight'</th><th>'when'</th><th>'tel'</th></tr>\n",
       "<tr><td>'A'</td><td>'1'</td><td>'12'</td><td>'2018-11-29 13:40:08.116768'</td><td>'+79360360193'</td></tr>\n",
       "<tr><td>'B'</td><td>'4'</td><td>'12'</td><td>'2018-10-30 13:40:08.116775'</td><td>'84505505151'</td></tr>\n",
       "<tr><td>'A'</td><td>'3'</td><td>'10'</td><td>'2018-10-15 13:40:08.116776'</td><td>'+31415926535'</td></tr>\n",
       "<tr><td>'B'</td><td>'6'</td><td>'14'</td><td>'2018-01-03 13:40:08.116777'</td><td>'0'</td></tr>\n",
       "<tr><td>'A'</td><td>'4'</td><td>'15'</td><td>'2018-05-07 13:40:08.116778'</td><td>''</td></tr>\n",
       "<tr><td>'A'</td><td>'2'</td><td>'11'</td><td>'2018-05-24 13:40:08.116779'</td><td>''</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "- Table object, title: \"from CSV\", 5 columns, 6 rows.\n",
       "'type' 'size' 'weight' 'when' 'tel'\n",
       "'A' '1' '12' '2018-11-29 13:40:08.116768' '+79360360193'\n",
       "'B' '4' '12' '2018-10-30 13:40:08.116775' '84505505151'\n",
       "'A' '3' '10' '2018-10-15 13:40:08.116776' '+31415926535'\n",
       "'B' '6' '14' '2018-01-03 13:40:08.116777' '0'\n",
       "'A' '4' '15' '2018-05-07 13:40:08.116778' ''\n",
       "'A' '2' '11' '2018-05-24 13:40:08.116779' ''"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = Table.from_csv(txt, inline=True, preprocess=False)\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Save SQL creation code & values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using Lemuras, you can work with SQL! You can generate tables creation code for SQL. It uses automatic detection of columns types that was described earlier."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATE TABLE `Sample` (\n",
      "  `type` varchar(1),\n",
      "  `size` int(1),\n",
      "  `weight` int(1),\n",
      "  `when` datetime,\n",
      "  `tel` varchar(12)\n",
      ") ;\n"
     ]
    }
   ],
   "source": [
    "sql_cr = df1.to_sql_create()\n",
    "print(sql_cr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And get the code to fill the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INSERT INTO `Sample` VALUES ('A',1,12,'2018-11-29 13:40:08.116768','+79360360193'), ('B',4,12,'2018-10-30 13:40:08.116775','84505505151'), ('A',3,10,'2018-10-15 13:40:08.116776','+31415926535'), ('B',6,14,'2018-01-03 13:40:08.116777','0'), ('A',4,15,'2018-05-07 13:40:08.116778','None'), ('A',2,11,'2018-05-24 13:40:08.116779','');\n"
     ]
    }
   ],
   "source": [
    "sql_vals = df1.to_sql_values()\n",
    "print(sql_vals)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load SQL creation code & values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Firstly, load the table declaration to retrieve the structure:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Sample</b>, 5 columns, 0 rows.<br>\n",
       "<table>\n",
       "<tr><th>'type'</th><th>'size'</th><th>'weight'</th><th>'when'</th><th>'tel'</th></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "- Table object, title: \"Sample\", 5 columns, 0 rows.\n",
       "'type' 'size' 'weight' 'when' 'tel'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = Table.from_sql_create(sql_cr)\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then, supply the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Sample</b>, 5 columns, 6 rows.<br>\n",
       "<table>\n",
       "<tr><th>'type'</th><th>'size'</th><th>'weight'</th><th>'when'</th><th>'tel'</th></tr>\n",
       "<tr><td>'A'</td><td>1</td><td>12</td><td>2018-11-29 13:40:08</td><td>79360360193</td></tr>\n",
       "<tr><td>'B'</td><td>4</td><td>12</td><td>2018-10-30 13:40:08</td><td>84505505151</td></tr>\n",
       "<tr><td>'A'</td><td>3</td><td>10</td><td>2018-10-15 13:40:08</td><td>31415926535</td></tr>\n",
       "<tr><td>'B'</td><td>6</td><td>14</td><td>2018-01-03 13:40:08</td><td>0</td></tr>\n",
       "<tr><td>'A'</td><td>4</td><td>15</td><td>2018-05-07 13:40:08</td><td>''</td></tr>\n",
       "<tr><td>'A'</td><td>2</td><td>11</td><td>2018-05-24 13:40:08</td><td>''</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "- Table object, title: \"Sample\", 5 columns, 6 rows.\n",
       "'type' 'size' 'weight' 'when' 'tel'\n",
       "'A' 1 12 2018-11-29 13:40:08 79360360193\n",
       "'B' 4 12 2018-10-30 13:40:08 84505505151\n",
       "'A' 3 10 2018-10-15 13:40:08 31415926535\n",
       "'B' 6 14 2018-01-03 13:40:08 0\n",
       "'A' 4 15 2018-05-07 13:40:08 ''\n",
       "'A' 2 11 2018-05-24 13:40:08 ''"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.add_sql_values(sql_vals)\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load SQL query result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In addition, you can create Table object using query result string:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Goods</b>, 5 columns, 8 rows.<br>\n",
       "<table>\n",
       "<tr><th>'id'</th><th>'name_rus'</th><th>'q1'</th><th>'q2'</th><th>'q3'</th></tr>\n",
       "<tr><td>1205</td><td>'Джем клубничный'</td><td>3286</td><td>10</td><td>14</td></tr>\n",
       "<tr><td>1306</td><td>'Мед'</td><td>800</td><td>19</td><td>19</td></tr>\n",
       "<tr><td>1110</td><td>'Блины'</td><td>5140</td><td>18</td><td>3</td></tr>\n",
       "<tr><td>2805</td><td>'Бургер'</td><td>18067</td><td>90817</td><td>61933</td></tr>\n",
       "<tr><th>...</th><th>...</th><th>...</th><th>...</th><th>...</th></tr></table>"
      ],
      "text/plain": [
       "- Table object, title: \"Goods\", 5 columns, 8 rows.\n",
       "'id' 'name_rus' 'q1' 'q2' 'q3'\n",
       "1205 'Джем клубничный' 3286 10 14\n",
       "1306 'Мед' 800 19 19\n",
       "1110 'Блины' 5140 18 3\n",
       "2805 'Бургер' 18067 90817 61933\n",
       ". . ."
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_result = \"\"\"+------+------------------+--------+--------+--------+\n",
    "| id   | name_rus                         | q1     | q2     | q3     |\n",
    "+------+----------------------------------+--------+--------+--------+\n",
    "| 1205 | Джем клубничный                  |   3286 |     10 |     14 |\n",
    "| 1306 | Мед                              |    800 |     19 |     19 |\n",
    "| 1110 | Блины                            |   5140 |     18 |      3 |\n",
    "| 2805 | Бургер                           |  18067 |  90817 |  61933 |\n",
    "| 2604 | Пирожок                          |  47150 | 215139 | 170291 |\n",
    "| 4446 | Чизкейк                          |   6856 |  17665 |  12808 |\n",
    "| 4248 | Маффин с яблоком и корицей       |   1765 |   4176 |   2385 |\n",
    "| 4753 | Вафельный рожок                  |   2158 |  16577 |  11725 |\n",
    "+------+----------------------------------+--------+--------+--------+\"\"\"\n",
    "\n",
    "df2 = Table.from_sql_result(sql_result, title='Goods')\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Warning!** Note that the string must start with `+--` symbols, without any leading whitespace symbols! It will be simplified in the future."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Save JSON"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can save a Table object to JSON string with rows as lists (by default) and set **`pretty`** to get more readable text:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "  \"columns\": [\n",
      "    \"type\", \"size\", \"weight\", \"when\", \"tel\"\n",
      "  ], \n",
      "  \"rows\": [\n",
      "    [\n",
      "      \"A\", 1, 12, \"2018-11-29 13:40:08.116768\", \"+79360360193\"\n",
      "    ], [\n",
      "      \"B\", 4, 12, \"2018-10-30 13:40:08.116775\", 84505505151\n",
      "    ], [\n",
      "      \"A\", 3, 10, \"2018-10-15 13:40:08.116776\", \"+31415926535\"\n",
      "    ], [\n",
      "      \"B\", 6, 14, \"2018-01-03 13:40:08.116777\", 0\n",
      "    ], [\n",
      "      \"A\", 4, 15, \"2018-05-07 13:40:08.116778\", \"None\"\n",
      "    ], [\n",
      "      \"A\", 2, 11, \"2018-05-24 13:40:08.116779\", \"\"\n",
      "    ]\n",
      "  ], \n",
      "  \"title\": \"Sample\"\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "s = df1.to_json(pretty=True)\n",
    "print(s)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or you can save rows as objects (though it is much less compact):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "  \"columns\": [\n",
      "    \"type\", \"size\", \"weight\", \"when\", \"tel\"\n",
      "  ], \n",
      "  \"rows\": [\n",
      "    {\n",
      "      \"type\": \"A\", \"size\": 1, \"weight\": 12, \"when\": \"2018-11-29 13:40:08.116768\", \"tel\": \"+79360360193\"\n",
      "    }, {\n",
      "      \"type\": \"B\", \"size\": 4, \"weight\": 12, \"when\": \"2018-10-30 13:40:08.116775\", \"tel\": 84505505151\n",
      "    }, {\n",
      "      \"type\": \"A\", \"size\": 3, \"weight\": 10, \"when\": \"2018-10-15 13:40:08.116776\", \"tel\": \"+31415926535\"\n",
      "    }, {\n",
      "      \"type\": \"B\", \"size\": 6, \"weight\": 14, \"when\": \"2018-01-03 13:40:08.116777\", \"tel\": 0\n",
      "    }, {\n",
      "      \"type\": \"A\", \"size\": 4, \"weight\": 15, \"when\": \"2018-05-07 13:40:08.116778\", \"tel\": \"None\"\n",
      "    }, {\n",
      "      \"type\": \"A\", \"size\": 2, \"weight\": 11, \"when\": \"2018-05-24 13:40:08.116779\", \"tel\": \"\"\n",
      "    }\n",
      "  ], \n",
      "  \"title\": \"Sample\"\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "s = df1.to_json(as_dict=True, pretty=True)\n",
    "print(s)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load JSON"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can load a JSON string with one of two mentioned formats (but **title** is optional):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Sample</b>, 5 columns, 6 rows.<br>\n",
       "<table>\n",
       "<tr><th>'type'</th><th>'size'</th><th>'weight'</th><th>'when'</th><th>'tel'</th></tr>\n",
       "<tr><td>'A'</td><td>1</td><td>12</td><td>2018-11-29 13:40:08</td><td>79360360193</td></tr>\n",
       "<tr><td>'B'</td><td>4</td><td>12</td><td>2018-10-30 13:40:08</td><td>84505505151</td></tr>\n",
       "<tr><td>'A'</td><td>3</td><td>10</td><td>2018-10-15 13:40:08</td><td>31415926535</td></tr>\n",
       "<tr><td>'B'</td><td>6</td><td>14</td><td>2018-01-03 13:40:08</td><td>0</td></tr>\n",
       "<tr><td>'A'</td><td>4</td><td>15</td><td>2018-05-07 13:40:08</td><td>''</td></tr>\n",
       "<tr><td>'A'</td><td>2</td><td>11</td><td>2018-05-24 13:40:08</td><td>''</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "- Table object, title: \"Sample\", 5 columns, 6 rows.\n",
       "'type' 'size' 'weight' 'when' 'tel'\n",
       "'A' 1 12 2018-11-29 13:40:08 79360360193\n",
       "'B' 4 12 2018-10-30 13:40:08 84505505151\n",
       "'A' 3 10 2018-10-15 13:40:08 31415926535\n",
       "'B' 6 14 2018-01-03 13:40:08 0\n",
       "'A' 4 15 2018-05-07 13:40:08 ''\n",
       "'A' 2 11 2018-05-24 13:40:08 ''"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = Table.from_json(s)\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Save HTML"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To save data as an HTML table use **`.html()`** instance method. To turn off default cutting of rows and columns disable *`cut`* optional parameter:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"<table>\\n<tr><th>'type'</th><th>'size'</th><th>'weight'</th><th>'when'</th><th>'tel'</th></tr>\\n<tr><td>'A'</td><td>1</td><td>12</td><td>2018-11-29 13:40:08.116768</td><td>'+79360360193'</td></tr>\\n<tr><td>'B'</td><td>4</td><td>12</td><td>2018-10-30 13:40:08.116775</td><td>84505505151</td></tr>\\n<tr><td>'A'</td><td>3</td><td>10</td><td>2018-10-15 13:40:08.116776</td><td>'+31415926535'</td></tr>\\n<tr><td>'B'</td><td>6</td><td>14</td><td>2018-01-03 13:40:08.116777</td><td>0</td></tr>\\n<tr><td>'A'</td><td>4</td><td>15</td><td>2018-05-07 13:40:08.116778</td><td>None</td></tr>\\n<tr><td>'A'</td><td>2</td><td>11</td><td>2018-05-24 13:40:08.116779</td><td>''</td></tr>\\n</table>\""
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.html(cut=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By the way, Table objects output for these Jupyter Notebooks is implemented using this method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Sample</b>, 5 columns, 6 rows.<br>\n",
       "<table>\n",
       "<tr><th>'type'</th><th>'size'</th><th>'weight'</th><th>'when'</th><th>'tel'</th></tr>\n",
       "<tr><td>'A'</td><td>1</td><td>12</td><td>2018-11-29 13:40:08.116768</td><td>'+79360360193'</td></tr>\n",
       "<tr><td>'B'</td><td>4</td><td>12</td><td>2018-10-30 13:40:08.116775</td><td>84505505151</td></tr>\n",
       "<tr><td>'A'</td><td>3</td><td>10</td><td>2018-10-15 13:40:08.116776</td><td>'+31415926535'</td></tr>\n",
       "<tr><td>'B'</td><td>6</td><td>14</td><td>2018-01-03 13:40:08.116777</td><td>0</td></tr>\n",
       "<tr><td>'A'</td><td>4</td><td>15</td><td>2018-05-07 13:40:08.116778</td><td>None</td></tr>\n",
       "<tr><td>'A'</td><td>2</td><td>11</td><td>2018-05-24 13:40:08.116779</td><td>''</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "- Table object, title: \"Sample\", 5 columns, 6 rows.\n",
       "'type' 'size' 'weight' 'when' 'tel'\n",
       "'A' 1 12 2018-11-29 13:40:08.116768 '+79360360193'\n",
       "'B' 4 12 2018-10-30 13:40:08.116775 84505505151\n",
       "'A' 3 10 2018-10-15 13:40:08.116776 '+31415926535'\n",
       "'B' 6 14 2018-01-03 13:40:08.116777 0\n",
       "'A' 4 15 2018-05-07 13:40:08.116778 None\n",
       "'A' 2 11 2018-05-24 13:40:08.116779 ''"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load HTML"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lemurs v1.1.7 brings us an opportunity to parse HTML tables! This is quite simple:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Numbers</b>, 2 columns, 3 rows.<br>\n",
       "<table>\n",
       "<tr><th>'Name'</th><th>'Value'</th></tr>\n",
       "<tr><td>'Pi'</td><td>3.1415926535</td></tr>\n",
       "<tr><td>'Euler'</td><td>2.7182818284</td></tr>\n",
       "<tr><td>'Phi'</td><td>1.6180339887</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "- Table object, title: \"Numbers\", 2 columns, 3 rows.\n",
       "'Name' 'Value'\n",
       "'Pi' 3.1415926535\n",
       "'Euler' 2.7182818284\n",
       "'Phi' 1.6180339887"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "html_table = \"\"\"\n",
    "<table>\n",
    "    <thead><tr>\n",
    "        <th>Name</th><th>Value</th>\n",
    "    </tr></thead>\n",
    "    <tbody><tr class=\"odd\">\n",
    "        <td>Pi</td><td>3.1415926535</td>\n",
    "    </tr><tr class=\"even\">\n",
    "        <td>Euler</td><td>2.7182818284</td>\n",
    "    </tr><tr class=\"odd\">\n",
    "        <td>Phi</td><td>1.6180339887</td>\n",
    "    </tr></tbody>\n",
    "</table>\"\"\"\n",
    "\n",
    "df2 = Table.from_html(html_table, title='Numbers')\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that to use this method you must have [BeautifulSoup 4 module installed](https://www.crummy.com/software/BeautifulSoup/bs4/doc/#installing-beautiful-soup)."
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}