Pandas/Persistance/Stop Persisting Pandas to CSV.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Stop persising pandas data frames to csv\n",
    "In this notebook we will explore advantage and disadvanteges of persisting the pandas dataframe to CSV (`df.to_csv`) and compare with the other options which exists. We will look at:\n",
    "* speed of saving\n",
    "* speed of loading\n",
    "* size of the persisted file\n",
    "* preservation of data types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:35:47.693409Z",
     "start_time": "2020-08-19T21:35:46.938659Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\v.dekanovsky\\Anaconda3\\lib\\site-packages\\xarray\\core\\merge.py:10: FutureWarning: The Panel class is removed from pandas. Accessing it from the top-level namespace will also be removed in the next version\n",
      "  PANDAS_TYPES = (pd.Series, pd.DataFrame, pd.Panel)\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import time\n",
    "from pathlib import Path\n",
    "from sqlalchemy import create_engine\n",
    "import plotly.express as pe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T20:54:46.998343Z",
     "start_time": "2020-08-19T20:54:46.993356Z"
    }
   },
   "source": [
    "## Support functions\n",
    "* `statistics` - calculates the data frame with information about \n",
    " * data type preservation, how many % of columns remained original type after reading\n",
    " * compression/size - how big is the file in % of csv\n",
    " * write_time - how long does it take to write this format as % of csv writing time\n",
    " * read_time - how long does it take to read this format as % of csv reading time\n",
    "* `repeat` - which measures average processing time, we do 7 repetitions\n",
    "* `performance_test` - which inputs the defined dict of tests and runs the repetition and statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:35:47.700390Z",
     "start_time": "2020-08-19T21:35:47.694405Z"
    }
   },
   "outputs": [],
   "source": [
    "def statistics(df: pd.DataFrame, df_loaded: pd.DataFrame, new_file: str, \n",
    "               tp: str, benchmark: dict, out: dict, orig_size: int) -> pd.DataFrame:\n",
    "    \"\"\"Generate statistics based on performance test results\"\"\"\n",
    "    \n",
    "    comparison_df = pd.DataFrame({\"orig\": df.dtypes, \"new\": df_loaded.dtypes})\n",
    "    comparison_df[\"same\"] = comparison_df[\"orig\"]==comparison_df[\"new\"]\n",
    "    #mismatched_types = comparison_df[comparison_df[\"same\"]==False].index.to_list()\n",
    "    \n",
    "    new_size = Path(new_file).stat().st_size\n",
    "    # we don't measure size of the SQL tables in the notebook\n",
    "    if tp == \"SQL\":\n",
    "        new_size = 0\n",
    "    \n",
    "    return pd.DataFrame({\n",
    "    \"dtype_preservation\": comparison_df[\"same\"].sum()/comparison_df[\"same\"].count(),\n",
    "    \"compression\": new_size/orig_size,\n",
    "    \"write_time\": out[\"write\"]/benchmark[\"write\"],\n",
    "    \"read_time\": out[\"read\"]/benchmark[\"read\"],\n",
    "    #\"mismatched_types\": mismatched_types\n",
    "    }, \n",
    "        index=[tp]), comparison_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:35:47.708399Z",
     "start_time": "2020-08-19T21:35:47.702385Z"
    }
   },
   "outputs": [],
   "source": [
    "def repeat(f: dict, repetitions: int, df: pd.DataFrame, file: str) -> dict:\n",
    "    \"\"\" Perform an operation specified by input dict number of repetition times on the data frame\"\"\"\n",
    "    \n",
    "    writes_elapsed = []\n",
    "    reads_elapsed = []\n",
    "    for r in range(repetitions):\n",
    "        \n",
    "        # write\n",
    "        start_time = time.time()\n",
    "        \n",
    "        # first parameter is self (the dataframe), second the path and then **kwargs\n",
    "        f[\"write_function\"](df, file, **f[\"write_params\"])\n",
    "        write_elapsed = time.time() - start_time\n",
    "        writes_elapsed.append(write_elapsed)\n",
    "        \n",
    "        # read\n",
    "        start_time = time.time()\n",
    "        df_loaded = f[\"read_function\"](file, **f[\"read_params\"])\n",
    "        read_elapsed = time.time() - start_time\n",
    "        reads_elapsed.append(read_elapsed)\n",
    "    \n",
    "    return {\"write\": sum(writes_elapsed)/len(writes_elapsed),\n",
    "           \"read\": sum(reads_elapsed)/len(reads_elapsed),\n",
    "           \"df_loaded\": df_loaded}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:35:47.717344Z",
     "start_time": "2020-08-19T21:35:47.709365Z"
    }
   },
   "outputs": [],
   "source": [
    "def performance_test(exporting_types: dict, df: pd.DataFrame, \n",
    "                     out_file: str=\"out\", repetitions: int = 7) -> pd.DataFrame:\n",
    "    \n",
    "    \"\"\"Run performance test for predefined dict of operations\"\"\"\n",
    "\n",
    "    results = []\n",
    "    for k,v in exporting_types.items():\n",
    "\n",
    "        # create a file name\n",
    "        new_file = out_file + v[\"extension\"]\n",
    "        \n",
    "        try:\n",
    "            # repeat the writing and reading several times\n",
    "            out = repeat(v, repetitions, df, new_file)\n",
    "\n",
    "            # CSV is the first one and it's set as benchmark for reading and writing times\n",
    "            if v[\"type\"] == \"CSV\":\n",
    "                benchmark = out\n",
    "                df.to_csv(\"benchmark.csv\", index=False)\n",
    "                orig_size = Path(\"benchmark.csv\").stat().st_size\n",
    "\n",
    "            # process the results - dtypes_preservation, compression, write and read_time\n",
    "            results.append(statistics(df, out[\"df_loaded\"], new_file, v[\"type\"], benchmark, out, orig_size)[0])\n",
    "        \n",
    "        except Exception as e:\n",
    "            print(f\"{k} failed - {e}\")\n",
    "\n",
    "    return pd.concat(results)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Test Data\n",
    "Before we can run any test, we need some data to test on. We will create a random dataset including number of different types, random words (clusters of letters) and senetences formed based on these words and random dates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:35:47.729318Z",
     "start_time": "2020-08-19T21:35:47.719340Z"
    }
   },
   "outputs": [],
   "source": [
    "import random\n",
    "import string\n",
    "import datetime\n",
    "\n",
    "def get_random_string(length: int) -> str:\n",
    "    \"\"\"Generated random string up to the specific lenght\"\"\"\n",
    "    \n",
    "    letters = string.ascii_letters\n",
    "    result_str = ''.join([random.choice(letters) for i in range(random.randint(3,length))])\n",
    "    return result_str\n",
    "\n",
    "def get_random_sentence(words: int=5) -> str:\n",
    "    \"\"\"Generate random sentence - list of random works\"\"\"\n",
    "    \n",
    "    words = []\n",
    "    for i in range(5):\n",
    "        words.append(get_random_string(10)[:random.randint(0,10)])\n",
    "    return \" \".join(words)\n",
    "\n",
    "def random_date(start: str, end: str, format: str) -> str:\n",
    "    \"\"\"Generate random date of specified format\"\"\"\n",
    "    \n",
    "    earliest = datetime.datetime.strptime(start, format)\n",
    "    latest  = datetime.datetime.strptime(end, format)\n",
    "    delta = latest - earliest\n",
    "    int_delta = (delta.days * 24 * 60 * 60) + delta.seconds    \n",
    "    random_second = random.randrange(int_delta)\n",
    "    return (earliest + datetime.timedelta(seconds = random_second)).strftime(format)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We use these function and random library to generate list of list containing random values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:35:47.740283Z",
     "start_time": "2020-08-19T21:35:47.731308Z"
    }
   },
   "outputs": [],
   "source": [
    "# https://numpy.org/doc/stable/user/basics.types.html\n",
    "def generate_random_data(size: int) -> list:\n",
    "    \"\"\"Generates list of list containing random data of various formats\"\"\"\n",
    "    data = []\n",
    "    for i in range(size):\n",
    "        data.append(\n",
    "            [random.randint(-127,127),  # int8\n",
    "             random.randint(-32768,32767),  # int16\n",
    "             random.randint(-2147483648,2147483647),  # int32\n",
    "             random.randint(-9223372036854775808 ,9223372036854775807),  # int64\n",
    "             random.randint(0,255),  # uint8\n",
    "             round(random.uniform(0,10000),2),\n",
    "             round(random.uniform(0,1000000),2),\n",
    "             get_random_string(10),\n",
    "             get_random_sentence(5),\n",
    "             random.choice([get_random_string(10) for i in range(25)]),\n",
    "             random_date(\"1900-01-01\",\"2020-05-01\",\"%Y-%m-%d\"),\n",
    "             random_date(\"1900-01-01T00:00:00\",\"2020-05-01T23:59:59\",\"%Y-%m-%dT%H:%M:%S\"),\n",
    "             random.choice([True,False])])\n",
    "        \n",
    "    return data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These values are concatenated into the dataframe. Some persisting methods doesn't work well with some data types, mainly with `timedelta` and `datetime` with timezone so we can optionally remove them. We also measure benchmark size of the csv file created based on this data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:35:47.751295Z",
     "start_time": "2020-08-19T21:35:47.741280Z"
    }
   },
   "outputs": [],
   "source": [
    "def create_random_df(size: int, drop_timedelta: bool=False, drop_timezone: bool=False):\n",
    "    \"\"\"Generates dataframe with random values\"\"\"\n",
    "    data = generate_random_data(size)\n",
    "    column_names = [\"Int8\", \"Int16\", \"Int32\", \"Int64\", \"UInt8\", \"Float32\", \"Float64\", \n",
    "                    \"String\", \"Sentence\", \"Category\", \"Date\", \"DateTime\", \"Bool\"]\n",
    "    df = pd.DataFrame(data, columns=column_names)\n",
    "    df[\"Int8\"] = df[\"Int8\"].astype(\"int8\")\n",
    "    df[\"Int16\"] = df[\"Int16\"].astype(\"int16\")\n",
    "    df[\"Int32\"] = df[\"Int32\"].astype(\"int32\")\n",
    "    df[\"UInt8\"] = df[\"UInt8\"].astype(\"uint8\")\n",
    "    df[\"Float32\"] = df[\"Float32\"].astype(\"float32\")\n",
    "    df[\"Category\"] = df[\"Category\"].astype(\"category\")\n",
    "    df[\"Date\"] = pd.to_datetime(df[\"Date\"], format=\"%Y-%m-%d\")\n",
    "    df[\"DateTime\"] = pd.to_datetime(df[\"DateTime\"], format=\"%Y-%m-%dT%H:%M:%S\")\n",
    "    if not drop_timedelta:\n",
    "        df[\"TimeDelta\"] = df[\"DateTime\"]-df[\"Date\"]\n",
    "    if not drop_timezone:\n",
    "        df[\"DateTime+Zone\"] = df[\"DateTime+Zone\"].dt.tz_localize('Europe/Vienna')\n",
    "\n",
    "    # store for size comparison\n",
    "    df.to_csv(\"benchmark.csv\", index=False)\n",
    "    orig_size = Path(\"benchmark.csv\").stat().st_size\n",
    "\n",
    "    return df, orig_size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:36:05.552215Z",
     "start_time": "2020-08-19T21:35:47.753248Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 50000 entries, 0 to 49999\n",
      "Data columns (total 13 columns):\n",
      "Int8        50000 non-null int8\n",
      "Int16       50000 non-null int16\n",
      "Int32       50000 non-null int32\n",
      "Int64       50000 non-null int64\n",
      "UInt8       50000 non-null uint8\n",
      "Float32     50000 non-null float32\n",
      "Float64     50000 non-null float64\n",
      "String      50000 non-null object\n",
      "Sentence    50000 non-null object\n",
      "Category    50000 non-null category\n",
      "Date        50000 non-null datetime64[ns]\n",
      "DateTime    50000 non-null datetime64[ns]\n",
      "Bool        50000 non-null bool\n",
      "dtypes: bool(1), category(1), datetime64[ns](2), float32(1), float64(1), int16(1), int32(1), int64(1), int8(1), object(2), uint8(1)\n",
      "memory usage: 4.7+ MB\n"
     ]
    }
   ],
   "source": [
    "dataset_size = 50000\n",
    "df, orig_size = create_random_df(dataset_size, True, True)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Run the peformance test\n",
    "We define each test case by several parameters:\n",
    "* type: type of persistance (used as descriptor of the result)\n",
    "* extension: extension which is used for the file persisted on the disk\n",
    "* write_function: which function is used to persist/write the dataframe\n",
    "* write_params: parameters of this function, e.g. compression type or engine used\n",
    "* read_function: fuction to read the persisted file\n",
    "* read_parameters: parameters of the read function "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:36:05.616065Z",
     "start_time": "2020-08-19T21:36:05.555220Z"
    }
   },
   "outputs": [],
   "source": [
    "# define the types of transformation to use\n",
    "exporting_types = {\n",
    "    \"csv\": {\n",
    "        \"type\": \"CSV\",\n",
    "        \"extension\": \".csv\",\n",
    "        \"write_function\": pd.DataFrame.to_csv,\n",
    "        \"write_params\": {\"index\": False},\n",
    "        \"read_function\": pd.read_csv,\n",
    "        \"read_params\": {}\n",
    "    },\n",
    "    \"csv_zip\": {\n",
    "        \"type\": \"CSV zip\",\n",
    "        \"extension\": \".zip\",\n",
    "        \"write_function\": pd.DataFrame.to_csv,\n",
    "        \"write_params\": {\"index\": False, \"compression\": \"zip\"},\n",
    "        \"read_function\": pd.read_csv,\n",
    "        \"read_params\": {\"compression\": \"zip\"}\n",
    "    },\n",
    "    \"picklea\": {\n",
    "        \"type\": \"Pickle bz2\",\n",
    "        \"extension\": \".pkl.bz2\",\n",
    "        \"write_function\": pd.DataFrame.to_pickle,\n",
    "        \"write_params\": {\"compression\": \"bz2\"},\n",
    "        \"read_function\": pd.read_pickle,\n",
    "        \"read_params\": {\"compression\": \"bz2\"}\n",
    "    },\n",
    "    \"pickleb\": {\n",
    "        \"type\": \"Pickle gzip\",\n",
    "        \"extension\": \".pkl.gzip\",\n",
    "        \"write_function\": pd.DataFrame.to_pickle,\n",
    "        \"write_params\": {\"compression\": \"gzip\"},\n",
    "        \"read_function\": pd.read_pickle,\n",
    "        \"read_params\": {\"compression\": \"gzip\"}\n",
    "    },\n",
    "    \"picklec\": {\n",
    "        \"type\": \"Pickle zip\",\n",
    "        \"extension\": \".pkl.zip\",\n",
    "        \"write_function\": pd.DataFrame.to_pickle,\n",
    "        \"write_params\": {\"compression\": \"zip\"},\n",
    "        \"read_function\": pd.read_pickle,\n",
    "        \"read_params\": {\"compression\": \"zip\"}\n",
    "    },\n",
    "    \"pickled\": {\n",
    "        \"type\": \"Pickle infer\",\n",
    "        \"extension\": \".pkl\",\n",
    "        \"write_function\": pd.DataFrame.to_pickle,\n",
    "        \"write_params\": {},\n",
    "        \"read_function\": pd.read_pickle,\n",
    "        \"read_params\": {}\n",
    "    },\n",
    "    \"picklee\": {\n",
    "        \"type\": \"Pickle xz\",\n",
    "        \"extension\": \".pkl.xz\",\n",
    "        \"write_function\": pd.DataFrame.to_pickle,\n",
    "        \"write_params\": {\"compression\": \"xz\"},\n",
    "        \"read_function\": pd.read_pickle,\n",
    "        \"read_params\": {\"compression\": \"xz\"}\n",
    "    },\n",
    "    \"parquet_pyarrow\": {\n",
    "        \"type\": \"Parquet via PyArrow\",\n",
    "        \"extension\": \".parquet.gzip\",\n",
    "        \"write_function\": pd.DataFrame.to_parquet,\n",
    "        \"write_params\": {},\n",
    "        \"read_function\": pd.read_parquet,\n",
    "        \"read_params\": {}\n",
    "    },\n",
    "    \"parquet_fastparquet\": {\n",
    "        \"type\": \"Parquet via fastparquet\",\n",
    "        \"extension\": \".parquet.gzip\",\n",
    "        \"write_function\": pd.DataFrame.to_parquet,\n",
    "        \"write_params\": {\"engine\":\"fastparquet\",\"compression\":\"GZIP\"},\n",
    "        \"read_function\": pd.read_parquet,\n",
    "        \"read_params\": {\"engine\":\"fastparquet\"}\n",
    "    },    \n",
    "    \"Hdf5f\": {\n",
    "        \"type\": \"Hdf5 fixed\",\n",
    "        \"extension\": \".h5\",\n",
    "        \"write_function\": pd.DataFrame.to_hdf,\n",
    "        \"write_params\": {\"key\":\"df\", \"format\":\"fixed\"},\n",
    "        \"read_function\": pd.read_hdf,\n",
    "        \"read_params\": {\"key\":\"df\"}\n",
    "    },\n",
    "    \"Hdf5t\": {\n",
    "        \"type\": \"Hdf5 table\",\n",
    "        \"extension\": \".h5\",\n",
    "        \"write_function\": pd.DataFrame.to_hdf,\n",
    "        \"write_params\": {\"key\":\"df\", \"format\":\"table\"},\n",
    "        \"read_function\": pd.read_hdf,\n",
    "        \"read_params\": {\"key\":\"df\"}\n",
    "    },\n",
    "    \"Hdf5c\": {\n",
    "        \"type\": \"Hdf5 compressed\",\n",
    "        \"extension\": \".h5\",\n",
    "        \"write_function\": pd.DataFrame.to_hdf,\n",
    "        \"write_params\": {\"key\":\"df\", \"format\":\"table\", \"complevel\": 9, \"complib\": \"bzip2\"},\n",
    "        \"read_function\": pd.read_hdf,\n",
    "        \"read_params\": {\"key\":\"df\"}\n",
    "    },\n",
    "    \"Excel\": {\n",
    "        \"type\": \"Excel\",\n",
    "        \"extension\": \".xlsx\",\n",
    "        \"write_function\": pd.DataFrame.to_excel,\n",
    "        \"write_params\": {\"index\":False},\n",
    "        \"read_function\": pd.read_excel,\n",
    "        \"read_params\": {}\n",
    "    },\n",
    "    # to_sql and read_sql have too much different syntax\n",
    "    #\"SQL\": {\n",
    "    #    \"type\": \"SQL\",\n",
    "    #    \"extension\": \"\",\n",
    "    #    \"write_function\": pd.DataFrame.to_sql,\n",
    "    #    \"write_params\": {\"name\": \"pandas\", \"con\": engine},\n",
    "    #    \"read_function\": pd.read_sql,\n",
    "    #    \"read_params\": {\"name\": \"pandas\", \"con\": engine}\n",
    "    #},\n",
    "    # feather file gets blocked by ArrowIO after the first run\n",
    "    \"Feather\": {\n",
    "        \"type\": \"Feather\",\n",
    "        \"extension\": \".f\",\n",
    "        \"write_function\": pd.DataFrame.to_feather,\n",
    "        \"write_params\": {},\n",
    "        \"read_function\": pd.read_feather,\n",
    "        \"read_params\": {}\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:40:49.823076Z",
     "start_time": "2020-08-19T21:36:05.618028Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hdf5f failed - Cannot store a category dtype in a HDF5 dataset that uses format=\"fixed\". Use format=\"table\".\n",
      "Feather failed - Failed to open local file 'out.f', error: The requested operation cannot be performed on a file with a user-mapped section open.\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<style  type=\"text/css\" >\n",
       "</style><table id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6\" ><thead>    <tr>        <th class=\"blank level0\" ></th>        <th class=\"col_heading level0 col0\" >dtype_preservation</th>        <th class=\"col_heading level0 col1\" >compression</th>        <th class=\"col_heading level0 col2\" >write_time</th>        <th class=\"col_heading level0 col3\" >read_time</th>    </tr></thead><tbody>\n",
       "                <tr>\n",
       "                        <th id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6level0_row0\" class=\"row_heading level0 row0\" >CSV</th>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row0_col0\" class=\"data row0 col0\" >38.46%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row0_col1\" class=\"data row0 col1\" >100.00%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row0_col2\" class=\"data row0 col2\" >100.00%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row0_col3\" class=\"data row0 col3\" >100.00%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6level0_row1\" class=\"row_heading level0 row1\" >CSV zip</th>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row1_col0\" class=\"data row1 col0\" >38.46%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row1_col1\" class=\"data row1 col1\" >56.10%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row1_col2\" class=\"data row1 col2\" >156.66%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row1_col3\" class=\"data row1 col3\" >115.82%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6level0_row2\" class=\"row_heading level0 row2\" >Pickle bz2</th>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row2_col0\" class=\"data row2 col0\" >100.00%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row2_col1\" class=\"data row2 col1\" >48.13%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row2_col2\" class=\"data row2 col2\" >70.25%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row2_col3\" class=\"data row2 col3\" >160.75%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6level0_row3\" class=\"row_heading level0 row3\" >Pickle gzip</th>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row3_col0\" class=\"data row3 col0\" >100.00%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row3_col1\" class=\"data row3 col1\" >48.56%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row3_col2\" class=\"data row3 col2\" >98.65%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row3_col3\" class=\"data row3 col3\" >29.75%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6level0_row4\" class=\"row_heading level0 row4\" >Pickle zip</th>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row4_col0\" class=\"data row4 col0\" >100.00%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row4_col1\" class=\"data row4 col1\" >48.56%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row4_col2\" class=\"data row4 col2\" >54.61%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row4_col3\" class=\"data row4 col3\" >27.60%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6level0_row5\" class=\"row_heading level0 row5\" >Pickle infer</th>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row5_col0\" class=\"data row5 col0\" >100.00%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row5_col1\" class=\"data row5 col1\" >63.92%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row5_col2\" class=\"data row5 col2\" >5.57%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row5_col3\" class=\"data row5 col3\" >12.52%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6level0_row6\" class=\"row_heading level0 row6\" >Pickle xz</th>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row6_col0\" class=\"data row6 col0\" >100.00%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row6_col1\" class=\"data row6 col1\" >44.20%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row6_col2\" class=\"data row6 col2\" >316.52%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row6_col3\" class=\"data row6 col3\" >191.97%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6level0_row7\" class=\"row_heading level0 row7\" >Parquet via PyArrow</th>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row7_col0\" class=\"data row7 col0\" >100.00%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row7_col1\" class=\"data row7 col1\" >69.35%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row7_col2\" class=\"data row7 col2\" >12.54%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row7_col3\" class=\"data row7 col3\" >25.60%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6level0_row8\" class=\"row_heading level0 row8\" >Parquet via fastparquet</th>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row8_col0\" class=\"data row8 col0\" >100.00%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row8_col1\" class=\"data row8 col1\" >48.44%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row8_col2\" class=\"data row8 col2\" >85.80%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row8_col3\" class=\"data row8 col3\" >90.82%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6level0_row9\" class=\"row_heading level0 row9\" >Hdf5 table</th>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row9_col0\" class=\"data row9 col0\" >100.00%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row9_col1\" class=\"data row9 col1\" >3629.55%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row9_col2\" class=\"data row9 col2\" >36.17%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row9_col3\" class=\"data row9 col3\" >136.83%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6level0_row10\" class=\"row_heading level0 row10\" >Hdf5 compressed</th>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row10_col0\" class=\"data row10 col0\" >100.00%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row10_col1\" class=\"data row10 col1\" >3629.31%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row10_col2\" class=\"data row10 col2\" >146.95%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row10_col3\" class=\"data row10 col3\" >258.00%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6level0_row11\" class=\"row_heading level0 row11\" >Excel</th>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row11_col0\" class=\"data row11 col0\" >53.85%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row11_col1\" class=\"data row11 col1\" >90.38%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row11_col2\" class=\"data row11 col2\" >1985.06%</td>\n",
       "                        <td id=\"T_a6049670_e264_11ea_a75a_50e08598ebb6row11_col3\" class=\"data row11 col3\" >4200.80%</td>\n",
       "            </tr>\n",
       "    </tbody></table>"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x1f834d16d08>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# performance test\n",
    "performance_df = performance_test(exporting_types, df)\n",
    "\n",
    "# results\n",
    "performance_df.style.format(\"{:.2%}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:14:29.890245Z",
     "start_time": "2020-08-19T21:14:29.884264Z"
    }
   },
   "source": [
    "## Display the results\n",
    "Image is worth many words so we display the results using interactive Plotly.Express chart. Plotly.Express allow to create these chart in few lines of code. Click on the items in the legend to hide/reveal particular persistance option from the graph. Click and select an area in the chart to zoom."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T22:21:46.819411Z",
     "start_time": "2020-08-19T22:21:46.726533Z"
    }
   },
   "outputs": [],
   "source": [
    "fig = pe.bar(performance_df.T, \n",
    "             barmode='group', text=\"value\")\n",
    "fig.update_traces(texttempmlate='%{text:.2%}', textposition='auto')\n",
    "fig.update_layout(title=f\"Statistics for {dataset_size} records - performance test 7 repetititons\", \n",
    "                  yaxis={\"type\": \"log\", \"title\": \"value % (log scale)\"})\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:16:30.666294Z",
     "start_time": "2020-08-19T21:16:30.659314Z"
    }
   },
   "source": [
    "Writing and reading speed and memory use in the process was previous explored by Ilia Zaitsev:  https://towardsdatascience.com/the-best-format-to-save-pandas-data-414dca023e0d"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Manual Runs\n",
    "If you struggle to follow the function wrapped in the dictiory you can run each function separately in the code below. I also highlight which data types are not persisted. \n",
    "## To csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:40:50.622659Z",
     "start_time": "2020-08-19T21:40:50.618670Z"
    }
   },
   "outputs": [],
   "source": [
    "out_file = \"out\"\n",
    "repetitions = 7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:40:58.845438Z",
     "start_time": "2020-08-19T21:40:50.624655Z"
    }
   },
   "outputs": [],
   "source": [
    "type = \"csv\"\n",
    "extension = \".csv\"\n",
    "\n",
    "new_file = out_file + extension\n",
    "writes_elapsed, reads_elapsed = [], []\n",
    "for r in range(repetitions):\n",
    "    start_time = time.time()\n",
    "    df.to_csv(new_file, index=False)\n",
    "    write_elapsed = time.time() - start_time\n",
    "    writes_elapsed.append(write_elapsed)\n",
    "    \n",
    "    start_time = time.time()\n",
    "    df_loaded = pd.read_csv(new_file)\n",
    "    #df_loaded = pd.read_csv(new_file, parse_dates=[\"period\",\"accepted\"], infer_datetime_format=True)\n",
    "    read_elapsed = time.time() - start_time\n",
    "    reads_elapsed.append(read_elapsed)\n",
    "    \n",
    "out = {\"read\": sum(reads_elapsed)/len(reads_elapsed),\n",
    "      \"write\": sum(writes_elapsed)/len(writes_elapsed)}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:47:10.534788Z",
     "start_time": "2020-08-19T21:47:09.973933Z"
    }
   },
   "outputs": [],
   "source": [
    "# set up csv as your benchmark\n",
    "df.to_csv(\"benchmark.csv\", index=False)\n",
    "orig_size =  Path(\"benchmark.csv\").stat().st_size\n",
    "benchmark = out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:47:11.533238Z",
     "start_time": "2020-08-19T21:47:11.512294Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style  type=\"text/css\" >\n",
       "    #T_89894e92_e265_11ea_8bff_50e08598ebb6row0_col2 {\n",
       "            color:  red;\n",
       "        }    #T_89894e92_e265_11ea_8bff_50e08598ebb6row1_col2 {\n",
       "            color:  red;\n",
       "        }    #T_89894e92_e265_11ea_8bff_50e08598ebb6row2_col2 {\n",
       "            color:  red;\n",
       "        }    #T_89894e92_e265_11ea_8bff_50e08598ebb6row4_col2 {\n",
       "            color:  red;\n",
       "        }    #T_89894e92_e265_11ea_8bff_50e08598ebb6row5_col2 {\n",
       "            color:  red;\n",
       "        }    #T_89894e92_e265_11ea_8bff_50e08598ebb6row9_col2 {\n",
       "            color:  red;\n",
       "        }    #T_89894e92_e265_11ea_8bff_50e08598ebb6row10_col2 {\n",
       "            color:  red;\n",
       "        }    #T_89894e92_e265_11ea_8bff_50e08598ebb6row11_col2 {\n",
       "            color:  red;\n",
       "        }</style><table id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6\" ><thead>    <tr>        <th class=\"blank level0\" ></th>        <th class=\"col_heading level0 col0\" >orig</th>        <th class=\"col_heading level0 col1\" >new</th>        <th class=\"col_heading level0 col2\" >same</th>    </tr></thead><tbody>\n",
       "                <tr>\n",
       "                        <th id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6level0_row0\" class=\"row_heading level0 row0\" >Int8</th>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row0_col0\" class=\"data row0 col0\" >int8</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row0_col1\" class=\"data row0 col1\" >int64</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row0_col2\" class=\"data row0 col2\" >False</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6level0_row1\" class=\"row_heading level0 row1\" >Int16</th>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row1_col0\" class=\"data row1 col0\" >int16</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row1_col1\" class=\"data row1 col1\" >int64</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row1_col2\" class=\"data row1 col2\" >False</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6level0_row2\" class=\"row_heading level0 row2\" >Int32</th>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row2_col0\" class=\"data row2 col0\" >int32</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row2_col1\" class=\"data row2 col1\" >int64</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row2_col2\" class=\"data row2 col2\" >False</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6level0_row3\" class=\"row_heading level0 row3\" >Int64</th>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row3_col0\" class=\"data row3 col0\" >int64</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row3_col1\" class=\"data row3 col1\" >int64</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row3_col2\" class=\"data row3 col2\" >True</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6level0_row4\" class=\"row_heading level0 row4\" >UInt8</th>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row4_col0\" class=\"data row4 col0\" >uint8</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row4_col1\" class=\"data row4 col1\" >int64</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row4_col2\" class=\"data row4 col2\" >False</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6level0_row5\" class=\"row_heading level0 row5\" >Float32</th>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row5_col0\" class=\"data row5 col0\" >float32</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row5_col1\" class=\"data row5 col1\" >float64</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row5_col2\" class=\"data row5 col2\" >False</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6level0_row6\" class=\"row_heading level0 row6\" >Float64</th>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row6_col0\" class=\"data row6 col0\" >float64</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row6_col1\" class=\"data row6 col1\" >float64</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row6_col2\" class=\"data row6 col2\" >True</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6level0_row7\" class=\"row_heading level0 row7\" >String</th>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row7_col0\" class=\"data row7 col0\" >object</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row7_col1\" class=\"data row7 col1\" >object</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row7_col2\" class=\"data row7 col2\" >True</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6level0_row8\" class=\"row_heading level0 row8\" >Sentence</th>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row8_col0\" class=\"data row8 col0\" >object</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row8_col1\" class=\"data row8 col1\" >object</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row8_col2\" class=\"data row8 col2\" >True</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6level0_row9\" class=\"row_heading level0 row9\" >Category</th>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row9_col0\" class=\"data row9 col0\" >category</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row9_col1\" class=\"data row9 col1\" >object</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row9_col2\" class=\"data row9 col2\" >False</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6level0_row10\" class=\"row_heading level0 row10\" >Date</th>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row10_col0\" class=\"data row10 col0\" >datetime64[ns]</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row10_col1\" class=\"data row10 col1\" >object</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row10_col2\" class=\"data row10 col2\" >False</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6level0_row11\" class=\"row_heading level0 row11\" >DateTime</th>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row11_col0\" class=\"data row11 col0\" >datetime64[ns]</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row11_col1\" class=\"data row11 col1\" >object</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row11_col2\" class=\"data row11 col2\" >False</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6level0_row12\" class=\"row_heading level0 row12\" >Bool</th>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row12_col0\" class=\"data row12 col0\" >bool</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row12_col1\" class=\"data row12 col1\" >bool</td>\n",
       "                        <td id=\"T_89894e92_e265_11ea_8bff_50e08598ebb6row12_col2\" class=\"data row12 col2\" >True</td>\n",
       "            </tr>\n",
       "    </tbody></table>"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x1f83f94af08>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statistics(df, df_loaded, new_file, type, benchmark, out=out, orig_size=orig_size)[1]\\\n",
    "    .style.applymap(lambda x: \"color: red\" if isinstance(x,bool) and x==False else \"black\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:47:14.068353Z",
     "start_time": "2020-08-19T21:47:14.055387Z"
    }
   },
   "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>dtype_preservation</th>\n",
       "      <th>compression</th>\n",
       "      <th>write_time</th>\n",
       "      <th>read_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>csv</td>\n",
       "      <td>0.384615</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     dtype_preservation  compression  write_time  read_time\n",
       "csv            0.384615          1.0         1.0        1.0"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statistics(df, df_loaded, new_file, type, benchmark, out=out, orig_size=orig_size)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:47:16.030766Z",
     "start_time": "2020-08-19T21:47:15.694666Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 50000 entries, 0 to 49999\n",
      "Data columns (total 13 columns):\n",
      "Int8        50000 non-null int8\n",
      "Int16       50000 non-null int16\n",
      "Int32       50000 non-null int64\n",
      "Int64       50000 non-null int64\n",
      "UInt8       50000 non-null int64\n",
      "Float32     50000 non-null float64\n",
      "Float64     50000 non-null float64\n",
      "String      50000 non-null object\n",
      "Sentence    50000 non-null object\n",
      "Category    50000 non-null category\n",
      "Date        50000 non-null datetime64[ns]\n",
      "DateTime    50000 non-null datetime64[ns]\n",
      "Bool        50000 non-null bool\n",
      "dtypes: bool(1), category(1), datetime64[ns](2), float64(2), int16(1), int64(3), int8(1), object(2)\n",
      "memory usage: 5.4+ MB\n"
     ]
    }
   ],
   "source": [
    "# to keep the data types while reading csv you must specify them using `dtype` parameter\n",
    "df_loaded = pd.read_csv(new_file,\n",
    "                       dtype={\"Int8\": \"int8\",\n",
    "                             \"Int16\": \"int16\",\n",
    "                             \"Uint8\": \"uint8\",\n",
    "                             \"Category\": \"category\"},\n",
    "                       parse_dates=[\"Date\",\"DateTime\"])\n",
    "df_loaded.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-04T19:00:43.982637Z",
     "start_time": "2020-08-04T19:00:43.978648Z"
    }
   },
   "source": [
    "## To Pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:55:53.204446Z",
     "start_time": "2020-08-19T21:55:53.113723Z"
    }
   },
   "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>dtype_preservation</th>\n",
       "      <th>compression</th>\n",
       "      <th>write_time</th>\n",
       "      <th>read_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Pickle</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.639237</td>\n",
       "      <td>0.047124</td>\n",
       "      <td>0.108429</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        dtype_preservation  compression  write_time  read_time\n",
       "Pickle                 1.0     0.639237    0.047124   0.108429"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type = \"Pickle\"\n",
    "extension = \".plk\"\n",
    "new_file = out_file + extension\n",
    "\n",
    "# write to pickle\n",
    "start_time = time.time()\n",
    "df.to_pickle(new_file)\n",
    "write_elapsed = time.time() - start_time\n",
    "\n",
    "# reading the pickled file\n",
    "start_time = time.time()\n",
    "df_loaded = pd.read_pickle(new_file)\n",
    "read_elapsed = time.time() - start_time\n",
    "    \n",
    "out = {\"read\": read_elapsed,\n",
    "      \"write\": write_elapsed}\n",
    "\n",
    "statistics(df, df_loaded, new_file, type, benchmark, out=out, orig_size=orig_size)[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## To Parquet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:56:02.788394Z",
     "start_time": "2020-08-19T21:56:02.630781Z"
    }
   },
   "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>orig</th>\n",
       "      <th>new</th>\n",
       "      <th>same</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Int8</td>\n",
       "      <td>int8</td>\n",
       "      <td>int8</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Int16</td>\n",
       "      <td>int16</td>\n",
       "      <td>int16</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Int32</td>\n",
       "      <td>int32</td>\n",
       "      <td>int32</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>UInt8</td>\n",
       "      <td>uint8</td>\n",
       "      <td>uint8</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Float32</td>\n",
       "      <td>float32</td>\n",
       "      <td>float32</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>String</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Sentence</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Category</td>\n",
       "      <td>category</td>\n",
       "      <td>category</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Date</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>DateTime</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    orig             new  same\n",
       "Int8                int8            int8  True\n",
       "Int16              int16           int16  True\n",
       "Int32              int32           int32  True\n",
       "Int64              int64           int64  True\n",
       "UInt8              uint8           uint8  True\n",
       "Float32          float32         float32  True\n",
       "Float64          float64         float64  True\n",
       "String            object          object  True\n",
       "Sentence          object          object  True\n",
       "Category        category        category  True\n",
       "Date      datetime64[ns]  datetime64[ns]  True\n",
       "DateTime  datetime64[ns]  datetime64[ns]  True\n",
       "Bool                bool            bool  True"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type = \"Parquet\"\n",
    "extension = \".parquet.gzip\"\n",
    "new_file = out_file + extension\n",
    "#df.to_parquet(new_file, engine=\"fastparquet\", compression=\"gzip\")\n",
    "df.to_parquet(new_file)\n",
    "df_loaded = pd.read_parquet(new_file)\n",
    "#results.append()\n",
    "statistics(df, df_loaded, new_file, type, benchmark, out={\"read\": 0, \"write\": 0},orig_size=orig_size)[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## To Hdf5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:56:06.854793Z",
     "start_time": "2020-08-19T21:56:06.383523Z"
    }
   },
   "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>orig</th>\n",
       "      <th>new</th>\n",
       "      <th>same</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Int8</td>\n",
       "      <td>int8</td>\n",
       "      <td>int8</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Int16</td>\n",
       "      <td>int16</td>\n",
       "      <td>int16</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Int32</td>\n",
       "      <td>int32</td>\n",
       "      <td>int32</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>UInt8</td>\n",
       "      <td>uint8</td>\n",
       "      <td>uint8</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Float32</td>\n",
       "      <td>float32</td>\n",
       "      <td>float32</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>String</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Sentence</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Category</td>\n",
       "      <td>category</td>\n",
       "      <td>category</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Date</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>DateTime</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    orig             new  same\n",
       "Int8                int8            int8  True\n",
       "Int16              int16           int16  True\n",
       "Int32              int32           int32  True\n",
       "Int64              int64           int64  True\n",
       "UInt8              uint8           uint8  True\n",
       "Float32          float32         float32  True\n",
       "Float64          float64         float64  True\n",
       "String            object          object  True\n",
       "Sentence          object          object  True\n",
       "Category        category        category  True\n",
       "Date      datetime64[ns]  datetime64[ns]  True\n",
       "DateTime  datetime64[ns]  datetime64[ns]  True\n",
       "Bool                bool            bool  True"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type = \"Hdf5\"\n",
    "extension = \".h5\"\n",
    "new_file = out_file + extension\n",
    "df.to_hdf(new_file, key='df', format=\"table\")\n",
    "df_loaded = pd.read_hdf(new_file, key='df')\n",
    "#results.append(statistics(df, df_loaded, new_file, type))\n",
    "statistics(df, df_loaded, new_file, type, benchmark, out={\"read\": 0, \"write\": 0}, orig_size=orig_size)[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## To Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T21:59:39.613382Z",
     "start_time": "2020-08-19T21:56:09.502944Z"
    }
   },
   "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>orig</th>\n",
       "      <th>new</th>\n",
       "      <th>same</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Int8</td>\n",
       "      <td>int8</td>\n",
       "      <td>int64</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Int16</td>\n",
       "      <td>int16</td>\n",
       "      <td>int64</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Int32</td>\n",
       "      <td>int32</td>\n",
       "      <td>int64</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>UInt8</td>\n",
       "      <td>uint8</td>\n",
       "      <td>int64</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Float32</td>\n",
       "      <td>float32</td>\n",
       "      <td>float64</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>String</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Sentence</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Category</td>\n",
       "      <td>category</td>\n",
       "      <td>object</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Date</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>DateTime</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    orig             new   same\n",
       "Int8                int8           int64  False\n",
       "Int16              int16           int64  False\n",
       "Int32              int32           int64  False\n",
       "Int64              int64           int64   True\n",
       "UInt8              uint8           int64  False\n",
       "Float32          float32         float64  False\n",
       "Float64          float64         float64   True\n",
       "String            object          object   True\n",
       "Sentence          object          object   True\n",
       "Category        category          object  False\n",
       "Date      datetime64[ns]  datetime64[ns]   True\n",
       "DateTime  datetime64[ns]  datetime64[ns]   True\n",
       "Bool                bool            bool   True"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type = \"Excel\"\n",
    "extension = \".xlsx\"\n",
    "new_file = out_file + extension\n",
    "\n",
    "writes_elapsed, reads_elapsed = [], []\n",
    "\n",
    "for r in range(repetitions):\n",
    "    start_time = time.time()\n",
    "    df.to_excel(new_file, index=False)\n",
    "    write_elapsed = time.time() - start_time\n",
    "    writes_elapsed.append(write_elapsed)\n",
    "    \n",
    "    start_time = time.time()\n",
    "    df_loaded = pd.read_excel(new_file)\n",
    "    read_elapsed = time.time() - start_time\n",
    "    reads_elapsed.append(read_elapsed)\n",
    "    \n",
    "out = {\"read\": sum(reads_elapsed)/len(reads_elapsed),\n",
    "      \"write\": sum(writes_elapsed)/len(writes_elapsed)}\n",
    "\n",
    "statistics(df, df_loaded, new_file, type, benchmark, out, orig_size=orig_size)[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T22:06:18.958138Z",
     "start_time": "2020-08-19T22:06:18.947167Z"
    }
   },
   "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>dtype_preservation</th>\n",
       "      <th>compression</th>\n",
       "      <th>write_time</th>\n",
       "      <th>read_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Excel</td>\n",
       "      <td>0.538462</td>\n",
       "      <td>0.903782</td>\n",
       "      <td>19.885719</td>\n",
       "      <td>50.167168</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       dtype_preservation  compression  write_time  read_time\n",
       "Excel            0.538462     0.903782   19.885719  50.167168"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statistics(df, df_loaded, new_file, type, benchmark, out, orig_size=orig_size)[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-04T19:34:22.062804Z",
     "start_time": "2020-08-04T19:34:22.032884Z"
    }
   },
   "source": [
    "## To Feather"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T22:06:23.575046Z",
     "start_time": "2020-08-19T22:06:23.476310Z"
    }
   },
   "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>orig</th>\n",
       "      <th>new</th>\n",
       "      <th>same</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Int8</td>\n",
       "      <td>int8</td>\n",
       "      <td>int8</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Int16</td>\n",
       "      <td>int16</td>\n",
       "      <td>int16</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Int32</td>\n",
       "      <td>int32</td>\n",
       "      <td>int32</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>UInt8</td>\n",
       "      <td>uint8</td>\n",
       "      <td>uint8</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Float32</td>\n",
       "      <td>float32</td>\n",
       "      <td>float32</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>String</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Sentence</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Category</td>\n",
       "      <td>category</td>\n",
       "      <td>category</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Date</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>DateTime</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    orig             new  same\n",
       "Int8                int8            int8  True\n",
       "Int16              int16           int16  True\n",
       "Int32              int32           int32  True\n",
       "Int64              int64           int64  True\n",
       "UInt8              uint8           uint8  True\n",
       "Float32          float32         float32  True\n",
       "Float64          float64         float64  True\n",
       "String            object          object  True\n",
       "Sentence          object          object  True\n",
       "Category        category        category  True\n",
       "Date      datetime64[ns]  datetime64[ns]  True\n",
       "DateTime  datetime64[ns]  datetime64[ns]  True\n",
       "Bool                bool            bool  True"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type = \"Feather\"\n",
    "extension = \".f\"\n",
    "new_file = out_file + extension\n",
    "start_time = time.time()\n",
    "df.to_feather(new_file)\n",
    "write_elapsed = time.time() - start_time\n",
    "start_time = time.time()\n",
    "df_loaded = pd.read_feather(new_file)\n",
    "read_elapsed = time.time() - start_time\n",
    "\n",
    "out = {\"read\": read_elapsed,\n",
    "      \"write\": write_elapsed}\n",
    "\n",
    "statistics(df, df_loaded, new_file, type, benchmark, out, orig_size=orig_size)[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T22:06:28.103453Z",
     "start_time": "2020-08-19T22:06:28.085501Z"
    }
   },
   "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>dtype_preservation</th>\n",
       "      <th>compression</th>\n",
       "      <th>write_time</th>\n",
       "      <th>read_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Feather</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.653213</td>\n",
       "      <td>0.036684</td>\n",
       "      <td>0.180578</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         dtype_preservation  compression  write_time  read_time\n",
       "Feather                 1.0     0.653213    0.036684   0.180578"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statistics(df, df_loaded, new_file, type, benchmark, out, orig_size=orig_size)[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## To Sql\n",
    "SQL was not integrated in the original test because the `.to_sql` function is so specific that it's hard to parametrize it like the others. Using `SQLAlchemy` you must \n",
    "* set up the DB engine\n",
    "* establish the connection\n",
    "* run the SQL command"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T22:06:50.191831Z",
     "start_time": "2020-08-19T22:06:50.162297Z"
    }
   },
   "outputs": [],
   "source": [
    "engine = create_engine(\n",
    "    'mssql+pyodbc://vaclav:VGF1H054ui@localhost/Vaclavs?driver=ODBC+Driver+13+for+SQL+server',\n",
    "    isolation_level=\"REPEATABLE READ\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T22:06:51.233860Z",
     "start_time": "2020-08-19T22:06:51.187984Z"
    }
   },
   "outputs": [],
   "source": [
    "connection = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T22:08:05.265881Z",
     "start_time": "2020-08-19T22:08:01.896642Z"
    }
   },
   "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>orig</th>\n",
       "      <th>new</th>\n",
       "      <th>same</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>bool</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Category</td>\n",
       "      <td>category</td>\n",
       "      <td>object</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Date</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>DateTime</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>datetime64[ns]</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Float32</td>\n",
       "      <td>float32</td>\n",
       "      <td>float64</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Int16</td>\n",
       "      <td>int16</td>\n",
       "      <td>int64</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Int32</td>\n",
       "      <td>int32</td>\n",
       "      <td>int64</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Int8</td>\n",
       "      <td>int8</td>\n",
       "      <td>int64</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Sentence</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>String</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>UInt8</td>\n",
       "      <td>uint8</td>\n",
       "      <td>int64</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>index</td>\n",
       "      <td>NaN</td>\n",
       "      <td>int64</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    orig             new   same\n",
       "Bool                bool            bool   True\n",
       "Category        category          object  False\n",
       "Date      datetime64[ns]  datetime64[ns]   True\n",
       "DateTime  datetime64[ns]  datetime64[ns]   True\n",
       "Float32          float32         float64  False\n",
       "Float64          float64         float64   True\n",
       "Int16              int16           int64  False\n",
       "Int32              int32           int64  False\n",
       "Int64              int64           int64   True\n",
       "Int8                int8           int64  False\n",
       "Sentence          object          object   True\n",
       "String            object          object   True\n",
       "UInt8              uint8           int64  False\n",
       "index                NaN           int64  False"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type = \"SQL\"\n",
    "\n",
    "# drop the test table if already exists\n",
    "sql = 'DROP TABLE [Vaclavs].[dbo].[test]'\n",
    "connection.execute(sql)\n",
    "\n",
    "writes_elapsed, reads_elapsed = [], []\n",
    "start_time = time.time()\n",
    "df.to_sql(name=\"test\", con=connection)\n",
    "write_elapsed = time.time() - start_time\n",
    "    \n",
    "start_time = time.time()\n",
    "df_loaded = pd.read_sql_table(table_name=\"test\", con=connection)\n",
    "read_elapsed = time.time() - start_time\n",
    "\n",
    "\n",
    "out = {\"read\": read_elapsed,\n",
    "      \"write\": write_elapsed}\n",
    "statistics(df, df_loaded, new_file, type, benchmark, out, orig_size)[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T22:08:07.232791Z",
     "start_time": "2020-08-19T22:08:07.222808Z"
    }
   },
   "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>dtype_preservation</th>\n",
       "      <th>compression</th>\n",
       "      <th>write_time</th>\n",
       "      <th>read_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>SQL</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.783496</td>\n",
       "      <td>3.131933</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     dtype_preservation  compression  write_time  read_time\n",
       "SQL                 0.5          0.0    2.783496   3.131933"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# compression value is \n",
    "statistics(df, df_loaded, new_file, type, benchmark, out, orig_size)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T22:08:00.279341Z",
     "start_time": "2020-08-19T22:08:00.274356Z"
    }
   },
   "outputs": [],
   "source": [
    "# Speed up SQL processing\n",
    "# https://medium.com/analytics-vidhya/speed-up-bulk-inserts-to-sql-db-using-pandas-and-python-61707ae41990\n",
    "\n",
    "from sqlalchemy import event\n",
    "@event.listens_for(engine, \"before_cursor_execute\")\n",
    "def receive_before_cursor_execute(\n",
    "       conn, cursor, statement, params, context, executemany\n",
    "        ):\n",
    "            if executemany:\n",
    "                cursor.fast_executemany = True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rerun the SQL test after this hack to see that the `write_time` and `read_time` have improved"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Try on your own dataset\n",
    "We will use the SEC quarterly data dump, the list of files, because it contains all types of columns we would like to persist:\n",
    "* datetime - `accepted`, `filed`, `period`\n",
    "* category - `form`\n",
    "* float - `ein`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T22:09:00.976867Z",
     "start_time": "2020-08-19T22:09:00.971880Z"
    }
   },
   "outputs": [],
   "source": [
    "folder = r\"..\\Stocks\\Data_Sec\\2020Q1\"\n",
    "file_name = \"sub.txt\"\n",
    "file = os.path.join(folder, file_name)\n",
    "out_file = \"out\"\n",
    "repetitions = 7  # how many times read/write to measure the speed\n",
    "results = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T22:09:01.905147Z",
     "start_time": "2020-08-19T22:09:01.792451Z"
    }
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(file, sep=\"\\t\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T22:09:02.817338Z",
     "start_time": "2020-08-19T22:09:02.588858Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "adsh                object\n",
       "cik                  int64\n",
       "sic                float64\n",
       "ein                float64\n",
       "form              category\n",
       "period      datetime64[ns]\n",
       "fy                   int64\n",
       "accepted    datetime64[ns]\n",
       "dtype: object"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"accepted\"] = pd.to_datetime(df[\"accepted\"])\n",
    "df[\"filed\"] = pd.to_datetime(df[\"filed\"], format=\"%Y%m%d\")\n",
    "df[\"period\"] = pd.to_datetime(df[\"period\"], format=\"%Y%m%d\")\n",
    "df[\"form\"] = df[\"form\"].astype('category')\n",
    "df[\"ein\"] = df[\"ein\"].astype(\"float\")\n",
    "df = df[[\"adsh\",\"cik\",\"sic\",\"ein\",\"form\",\"period\",\"fy\",\"accepted\"]]\n",
    "df.to_csv(\"benchmark.csv\", index=False)\n",
    "orig_size = Path(\"benchmark.csv\").stat().st_size\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T22:09:03.449152Z",
     "start_time": "2020-08-19T22:09:03.442167Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(13560, 8)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T22:09:57.739667Z",
     "start_time": "2020-08-19T22:09:04.810617Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hdf5f failed - Cannot store a category dtype in a HDF5 dataset that uses format=\"fixed\". Use format=\"table\".\n",
      "Feather failed - Failed to open local file 'out.f', error: The requested operation cannot be performed on a file with a user-mapped section open.\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<style  type=\"text/css\" >\n",
       "</style><table id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6\" ><thead>    <tr>        <th class=\"blank level0\" ></th>        <th class=\"col_heading level0 col0\" >dtype_preservation</th>        <th class=\"col_heading level0 col1\" >compression</th>        <th class=\"col_heading level0 col2\" >write_time</th>        <th class=\"col_heading level0 col3\" >read_time</th>    </tr></thead><tbody>\n",
       "                <tr>\n",
       "                        <th id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6level0_row0\" class=\"row_heading level0 row0\" >CSV</th>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row0_col0\" class=\"data row0 col0\" >62.50%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row0_col1\" class=\"data row0 col1\" >100.00%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row0_col2\" class=\"data row0 col2\" >100.00%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row0_col3\" class=\"data row0 col3\" >100.00%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6level0_row1\" class=\"row_heading level0 row1\" >CSV zip</th>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row1_col0\" class=\"data row1 col0\" >62.50%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row1_col1\" class=\"data row1 col1\" >19.43%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row1_col2\" class=\"data row1 col2\" >144.29%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row1_col3\" class=\"data row1 col3\" >124.48%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6level0_row2\" class=\"row_heading level0 row2\" >Pickle bz2</th>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row2_col0\" class=\"data row2 col0\" >100.00%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row2_col1\" class=\"data row2 col1\" >16.53%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row2_col2\" class=\"data row2 col2\" >83.55%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row2_col3\" class=\"data row2 col3\" >149.15%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6level0_row3\" class=\"row_heading level0 row3\" >Pickle gzip</th>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row3_col0\" class=\"data row3 col0\" >100.00%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row3_col1\" class=\"data row3 col1\" >17.81%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row3_col2\" class=\"data row3 col2\" >253.54%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row3_col3\" class=\"data row3 col3\" >35.10%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6level0_row4\" class=\"row_heading level0 row4\" >Pickle zip</th>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row4_col0\" class=\"data row4 col0\" >100.00%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row4_col1\" class=\"data row4 col1\" >18.26%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row4_col2\" class=\"data row4 col2\" >37.57%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row4_col3\" class=\"data row4 col3\" >32.55%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6level0_row5\" class=\"row_heading level0 row5\" >Pickle infer</th>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row5_col0\" class=\"data row5 col0\" >100.00%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row5_col1\" class=\"data row5 col1\" >74.43%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row5_col2\" class=\"data row5 col2\" >3.44%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row5_col3\" class=\"data row5 col3\" >13.59%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6level0_row6\" class=\"row_heading level0 row6\" >Pickle xz</th>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row6_col0\" class=\"data row6 col0\" >100.00%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row6_col1\" class=\"data row6 col1\" >13.37%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row6_col2\" class=\"data row6 col2\" >453.18%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row6_col3\" class=\"data row6 col3\" >122.39%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6level0_row7\" class=\"row_heading level0 row7\" >Parquet via PyArrow</th>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row7_col0\" class=\"data row7 col0\" >100.00%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row7_col1\" class=\"data row7 col1\" >25.81%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row7_col2\" class=\"data row7 col2\" >9.90%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row7_col3\" class=\"data row7 col3\" >25.16%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6level0_row8\" class=\"row_heading level0 row8\" >Parquet via fastparquet</th>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row8_col0\" class=\"data row8 col0\" >100.00%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row8_col1\" class=\"data row8 col1\" >17.24%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row8_col2\" class=\"data row8 col2\" >40.12%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row8_col3\" class=\"data row8 col3\" >930.19%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6level0_row9\" class=\"row_heading level0 row9\" >Hdf5 table</th>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row9_col0\" class=\"data row9 col0\" >100.00%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row9_col1\" class=\"data row9 col1\" >20579.48%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row9_col2\" class=\"data row9 col2\" >43.13%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row9_col3\" class=\"data row9 col3\" >164.58%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6level0_row10\" class=\"row_heading level0 row10\" >Hdf5 compressed</th>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row10_col0\" class=\"data row10 col0\" >100.00%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row10_col1\" class=\"data row10 col1\" >20579.69%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row10_col2\" class=\"data row10 col2\" >88.26%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row10_col3\" class=\"data row10 col3\" >249.10%</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6level0_row11\" class=\"row_heading level0 row11\" >Excel</th>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row11_col0\" class=\"data row11 col0\" >75.00%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row11_col1\" class=\"data row11 col1\" >53.88%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row11_col2\" class=\"data row11 col2\" >2193.44%</td>\n",
       "                        <td id=\"T_b7dbb4ec_e268_11ea_9664_50e08598ebb6row11_col3\" class=\"data row11 col3\" >6185.17%</td>\n",
       "            </tr>\n",
       "    </tbody></table>"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x1f8354580c8>"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# performance test\n",
    "performance_df = performance_test(exporting_types, df)\n",
    "\n",
    "# results\n",
    "performance_df.style.format(\"{:.2%}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T22:10:10.188318Z",
     "start_time": "2020-08-19T22:10:09.950614Z"
    }
   },
   "outputs": [],
   "source": [
    "# display the graph with the results\n",
    "fig = pe.bar(performance_df.T, barmode='group', text=\"value\")\n",
    "    \n",
    "fig.update_traces(texttemplate='%{text:.2%}', textposition='auto')\n",
    "fig.update_layout(title=f\"Statistics for {dataset_size} records - performance test 7 repetititons\", \n",
    "                  yaxis={\"type\": \"log\", \"title\": \"value % (log scale)\"})\n",
    "fig.update_layout(title=f\"Statistics for SEC Quarterly data dump of shape {df.shape}\")\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-08-19T22:09:57.807487Z",
     "start_time": "2020-08-19T22:09:18.991Z"
    }
   },
   "outputs": [],
   "source": [
    "As you can see, the results on this dataset are similar to our test dataset"
   ]
  }
 ],
 "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.7.4"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}