examples-csv.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Engineering in Python with databolt  - Quickly Load Any Type of CSV (d6tlib/d6tstack)\n",
    "\n",
    "Vendors often send large datasets in multiple files. Often there are missing and misaligned columns between files that have to be manually cleaned. With DataBolt File Stack you can easily stack them together into one consistent dataset.\n",
    "\n",
    "Features include:\n",
    "* Quickly check column consistency across multiple files\n",
    "* Fix added/missing columns\n",
    "* Fix renamed columns\n",
    "* Out of core functionality to process large files\n",
    "* Export to pandas, CSV, SQL, parquet\n",
    "    * Fast export to postgres and mysql with out of core support\n",
    "    \n",
    "In this workbook we will demonstrate the usage of the d6tstack library."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import importlib\n",
    "import pandas as pd\n",
    "import glob\n",
    "\n",
    "import d6tstack.combine_csv as d6tc\n",
    "import d6tstack"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get sample data\n",
    "\n",
    "We've created some dummy sample data which you can download. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import urllib.request\n",
    "cfg_fname_sample = 'test-data.zip'\n",
    "urllib.request.urlretrieve(\"https://github.com/d6t/d6tstack/raw/master/\"+cfg_fname_sample, cfg_fname_sample)\n",
    "import zipfile\n",
    "zip_ref = zipfile.ZipFile(cfg_fname_sample, 'r')\n",
    "zip_ref.extractall('.')\n",
    "zip_ref.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Use Case: Checking Column Consistency\n",
    "\n",
    "Let's say you receive a bunch of csv files you want to ingest them, say for example into pandas, dask, pyspark, database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['test-data/input/test-data-input-csv-clean-mar.csv', 'test-data/input/test-data-input-csv-clean-feb.csv', 'test-data/input/test-data-input-csv-clean-jan.csv']\n"
     ]
    }
   ],
   "source": [
    "cfg_fnames = list(glob.glob('test-data/input/test-data-input-csv-clean-*.csv'))\n",
    "print(cfg_fnames)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check column consistency across all files\n",
    "\n",
    "Even if you think the files have a consistent column layout, it worthwhile using `d6tstack` to assert that that is actually the case. It's very quick to do even with very many large files!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sniffing columns ok\n"
     ]
    }
   ],
   "source": [
    "# get previews\n",
    "c = d6tc.CombinerCSV(cfg_fnames) # all_strings=True makes reading faster\n",
    "col_sniff = c.sniff_columns()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "all columns equal? True\n",
      "\n",
      "which columns are present in which files?\n",
      "\n",
      "                                                   date  sales  cost  profit\n",
      "file_path                                                                   \n",
      "test-data/input/test-data-input-csv-clean-feb.csv  True   True  True    True\n",
      "test-data/input/test-data-input-csv-clean-jan.csv  True   True  True    True\n",
      "test-data/input/test-data-input-csv-clean-mar.csv  True   True  True    True\n",
      "\n",
      "in what order do columns appear in the files?\n",
      "\n",
      "   date  sales  cost  profit\n",
      "0     0      1     2       3\n",
      "1     0      1     2       3\n",
      "2     0      1     2       3\n"
     ]
    }
   ],
   "source": [
    "print('all columns equal?', c.is_all_equal())\n",
    "print('')\n",
    "print('which columns are present in which files?')\n",
    "print('')\n",
    "print(c.is_column_present())\n",
    "print('')\n",
    "print('in what order do columns appear in the files?')\n",
    "print('')\n",
    "print(col_sniff['df_columns_order'].reset_index(drop=True))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preview Combined Data\n",
    "\n",
    "You can see a preview of what the combined data from all files will look like."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "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>date</th>\n",
       "      <th>sales</th>\n",
       "      <th>cost</th>\n",
       "      <th>profit</th>\n",
       "      <th>filepath</th>\n",
       "      <th>filename</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-clean-feb.csv</td>\n",
       "      <td>test-data-input-csv-clean-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-02-02</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-clean-feb.csv</td>\n",
       "      <td>test-data-input-csv-clean-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-02-03</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-clean-feb.csv</td>\n",
       "      <td>test-data-input-csv-clean-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "      <td>test-data/input/test-data-input-csv-clean-jan.csv</td>\n",
       "      <td>test-data-input-csv-clean-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-01-02</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "      <td>test-data/input/test-data-input-csv-clean-jan.csv</td>\n",
       "      <td>test-data-input-csv-clean-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2011-01-03</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "      <td>test-data/input/test-data-input-csv-clean-jan.csv</td>\n",
       "      <td>test-data-input-csv-clean-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2011-03-01</td>\n",
       "      <td>300</td>\n",
       "      <td>-100</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data/input/test-data-input-csv-clean-mar.csv</td>\n",
       "      <td>test-data-input-csv-clean-mar.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2011-03-02</td>\n",
       "      <td>300</td>\n",
       "      <td>-100</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data/input/test-data-input-csv-clean-mar.csv</td>\n",
       "      <td>test-data-input-csv-clean-mar.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2011-03-03</td>\n",
       "      <td>300</td>\n",
       "      <td>-100</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data/input/test-data-input-csv-clean-mar.csv</td>\n",
       "      <td>test-data-input-csv-clean-mar.csv</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  sales  cost  profit                                           filepath                           filename\n",
       "0  2011-02-01    200   -90     110  test-data/input/test-data-input-csv-clean-feb.csv  test-data-input-csv-clean-feb.csv\n",
       "1  2011-02-02    200   -90     110  test-data/input/test-data-input-csv-clean-feb.csv  test-data-input-csv-clean-feb.csv\n",
       "2  2011-02-03    200   -90     110  test-data/input/test-data-input-csv-clean-feb.csv  test-data-input-csv-clean-feb.csv\n",
       "3  2011-01-01    100   -80      20  test-data/input/test-data-input-csv-clean-jan.csv  test-data-input-csv-clean-jan.csv\n",
       "4  2011-01-02    100   -80      20  test-data/input/test-data-input-csv-clean-jan.csv  test-data-input-csv-clean-jan.csv\n",
       "5  2011-01-03    100   -80      20  test-data/input/test-data-input-csv-clean-jan.csv  test-data-input-csv-clean-jan.csv\n",
       "6  2011-03-01    300  -100     200  test-data/input/test-data-input-csv-clean-mar.csv  test-data-input-csv-clean-mar.csv\n",
       "7  2011-03-02    300  -100     200  test-data/input/test-data-input-csv-clean-mar.csv  test-data-input-csv-clean-mar.csv\n",
       "8  2011-03-03    300  -100     200  test-data/input/test-data-input-csv-clean-mar.csv  test-data-input-csv-clean-mar.csv"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.combine_preview()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read All Files to Pandas\n",
    "\n",
    "You can quickly load the combined data into a pandas dataframe with a single command. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "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>date</th>\n",
       "      <th>sales</th>\n",
       "      <th>cost</th>\n",
       "      <th>profit</th>\n",
       "      <th>filepath</th>\n",
       "      <th>filename</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-clean-feb.csv</td>\n",
       "      <td>test-data-input-csv-clean-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-02-02</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-clean-feb.csv</td>\n",
       "      <td>test-data-input-csv-clean-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-02-03</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-clean-feb.csv</td>\n",
       "      <td>test-data-input-csv-clean-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-02-04</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-clean-feb.csv</td>\n",
       "      <td>test-data-input-csv-clean-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-02-05</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-clean-feb.csv</td>\n",
       "      <td>test-data-input-csv-clean-feb.csv</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  sales  cost  profit                                           filepath                           filename\n",
       "0  2011-02-01    200   -90     110  test-data/input/test-data-input-csv-clean-feb.csv  test-data-input-csv-clean-feb.csv\n",
       "1  2011-02-02    200   -90     110  test-data/input/test-data-input-csv-clean-feb.csv  test-data-input-csv-clean-feb.csv\n",
       "2  2011-02-03    200   -90     110  test-data/input/test-data-input-csv-clean-feb.csv  test-data-input-csv-clean-feb.csv\n",
       "3  2011-02-04    200   -90     110  test-data/input/test-data-input-csv-clean-feb.csv  test-data-input-csv-clean-feb.csv\n",
       "4  2011-02-05    200   -90     110  test-data/input/test-data-input-csv-clean-feb.csv  test-data-input-csv-clean-feb.csv"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.to_pandas().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Use Case: Identifying and fixing inconsistent columns\n",
    "\n",
    "The first case was clean: all files had the same columns. It happens very frequently that the data schema changes over time with columns being added or deleted over time. Let's look at a case where an extra columns got added."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['test-data/input/test-data-input-csv-colmismatch-mar.csv', 'test-data/input/test-data-input-csv-colmismatch-feb.csv', 'test-data/input/test-data-input-csv-colmismatch-jan.csv']\n"
     ]
    }
   ],
   "source": [
    "cfg_fnames = list(glob.glob('test-data/input/test-data-input-csv-colmismatch-*.csv'))\n",
    "print(cfg_fnames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sniffing columns ok\n"
     ]
    }
   ],
   "source": [
    "# get previews\n",
    "c = d6tc.CombinerCSV(cfg_fnames) # all_strings=True makes reading faster\n",
    "col_sniff = c.sniff_columns()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "all columns equal? False\n",
      "\n",
      "which columns are unique? ['profit2']\n",
      "\n",
      "which files have unique columns?\n",
      "\n",
      "                                                    profit2\n",
      "file_path                                                  \n",
      "test-data/input/test-data-input-csv-colmismatch...    False\n",
      "test-data/input/test-data-input-csv-colmismatch...    False\n",
      "test-data/input/test-data-input-csv-colmismatch...     True\n"
     ]
    }
   ],
   "source": [
    "print('all columns equal?', c.is_all_equal())\n",
    "print('')\n",
    "print('which columns are unique?', col_sniff['columns_unique'])\n",
    "print('')\n",
    "print('which files have unique columns?')\n",
    "print('')\n",
    "print(c.is_column_present_unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "scrolled": true
   },
   "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>date</th>\n",
       "      <th>sales</th>\n",
       "      <th>cost</th>\n",
       "      <th>profit</th>\n",
       "      <th>profit2</th>\n",
       "      <th>filepath</th>\n",
       "      <th>filename</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>NaN</td>\n",
       "      <td>test-data/input/test-data-input-csv-colmismatc...</td>\n",
       "      <td>test-data-input-csv-colmismatch-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-02-02</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>NaN</td>\n",
       "      <td>test-data/input/test-data-input-csv-colmismatc...</td>\n",
       "      <td>test-data-input-csv-colmismatch-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-02-03</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>NaN</td>\n",
       "      <td>test-data/input/test-data-input-csv-colmismatc...</td>\n",
       "      <td>test-data-input-csv-colmismatch-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-02-04</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>NaN</td>\n",
       "      <td>test-data/input/test-data-input-csv-colmismatc...</td>\n",
       "      <td>test-data-input-csv-colmismatch-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-02-05</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>NaN</td>\n",
       "      <td>test-data/input/test-data-input-csv-colmismatc...</td>\n",
       "      <td>test-data-input-csv-colmismatch-feb.csv</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  sales  cost  profit  profit2                                           filepath                                 filename\n",
       "0  2011-02-01    200   -90     110      NaN  test-data/input/test-data-input-csv-colmismatc...  test-data-input-csv-colmismatch-feb.csv\n",
       "1  2011-02-02    200   -90     110      NaN  test-data/input/test-data-input-csv-colmismatc...  test-data-input-csv-colmismatch-feb.csv\n",
       "2  2011-02-03    200   -90     110      NaN  test-data/input/test-data-input-csv-colmismatc...  test-data-input-csv-colmismatch-feb.csv\n",
       "3  2011-02-04    200   -90     110      NaN  test-data/input/test-data-input-csv-colmismatc...  test-data-input-csv-colmismatch-feb.csv\n",
       "4  2011-02-05    200   -90     110      NaN  test-data/input/test-data-input-csv-colmismatc...  test-data-input-csv-colmismatch-feb.csv"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.to_pandas().head() # keep all columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sniffing columns ok\n"
     ]
    },
    {
     "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>date</th>\n",
       "      <th>sales</th>\n",
       "      <th>cost</th>\n",
       "      <th>profit</th>\n",
       "      <th>filepath</th>\n",
       "      <th>filename</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-colmismatc...</td>\n",
       "      <td>test-data-input-csv-colmismatch-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-02-02</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-colmismatc...</td>\n",
       "      <td>test-data-input-csv-colmismatch-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-02-03</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-colmismatc...</td>\n",
       "      <td>test-data-input-csv-colmismatch-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-02-04</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-colmismatc...</td>\n",
       "      <td>test-data-input-csv-colmismatch-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-02-05</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-colmismatc...</td>\n",
       "      <td>test-data-input-csv-colmismatch-feb.csv</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  sales  cost  profit                                           filepath                                 filename\n",
       "0  2011-02-01    200   -90     110  test-data/input/test-data-input-csv-colmismatc...  test-data-input-csv-colmismatch-feb.csv\n",
       "1  2011-02-02    200   -90     110  test-data/input/test-data-input-csv-colmismatc...  test-data-input-csv-colmismatch-feb.csv\n",
       "2  2011-02-03    200   -90     110  test-data/input/test-data-input-csv-colmismatc...  test-data-input-csv-colmismatch-feb.csv\n",
       "3  2011-02-04    200   -90     110  test-data/input/test-data-input-csv-colmismatc...  test-data-input-csv-colmismatch-feb.csv\n",
       "4  2011-02-05    200   -90     110  test-data/input/test-data-input-csv-colmismatc...  test-data-input-csv-colmismatch-feb.csv"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d6tc.CombinerCSV(cfg_fnames, columns_select_common=True).to_pandas().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Use Case: align renamed columns. Select subset of columns\n",
    "\n",
    "Say a column has been renamed and now the data doesn't line up with the data from the old column name. You can easily fix such a situation by using `CombinerCSVAdvanced` which allows you to rename columns and automatically lines up the data. It also allows you to just load data from a subset of columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sniffing columns ok\n",
      "                                                    revenue  sales\n",
      "file_path                                                         \n",
      "test-data/input/test-data-input-csv-renamed-feb...    False   True\n",
      "test-data/input/test-data-input-csv-renamed-jan...    False   True\n",
      "test-data/input/test-data-input-csv-renamed-mar...     True  False\n"
     ]
    }
   ],
   "source": [
    "cfg_fnames = list(glob.glob('test-data/input/test-data-input-csv-renamed-*.csv'))\n",
    "c = d6tc.CombinerCSV(cfg_fnames)\n",
    "print(c.is_column_present_unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The column `sales` got renamed to `revenue` in the March file, this would causes problems when reading the files. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sniffing columns ok\n"
     ]
    },
    {
     "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>filename</th>\n",
       "      <th>revenue</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>test-data-input-csv-renamed-feb.csv</td>\n",
       "      <td>NaN</td>\n",
       "      <td>200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>test-data-input-csv-renamed-feb.csv</td>\n",
       "      <td>NaN</td>\n",
       "      <td>200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>test-data-input-csv-renamed-feb.csv</td>\n",
       "      <td>NaN</td>\n",
       "      <td>200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>test-data-input-csv-renamed-jan.csv</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>test-data-input-csv-renamed-jan.csv</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>test-data-input-csv-renamed-jan.csv</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>test-data-input-csv-renamed-mar.csv</td>\n",
       "      <td>300.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>test-data-input-csv-renamed-mar.csv</td>\n",
       "      <td>300.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>test-data-input-csv-renamed-mar.csv</td>\n",
       "      <td>300.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              filename  revenue  sales\n",
       "0  test-data-input-csv-renamed-feb.csv      NaN  200.0\n",
       "1  test-data-input-csv-renamed-feb.csv      NaN  200.0\n",
       "2  test-data-input-csv-renamed-feb.csv      NaN  200.0\n",
       "3  test-data-input-csv-renamed-jan.csv      NaN  100.0\n",
       "4  test-data-input-csv-renamed-jan.csv      NaN  100.0\n",
       "5  test-data-input-csv-renamed-jan.csv      NaN  100.0\n",
       "6  test-data-input-csv-renamed-mar.csv    300.0    NaN\n",
       "7  test-data-input-csv-renamed-mar.csv    300.0    NaN\n",
       "8  test-data-input-csv-renamed-mar.csv    300.0    NaN"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "col_sniff = c.sniff_columns()\n",
    "c.combine_preview()[['filename']+col_sniff['columns_unique']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can pass the columns you want to rename to `columns_rename` and it will rename and align those columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# only select particular columns\n",
    "cfg_col_sel = ['date','sales','cost','profit'] # don't select profit2\n",
    "# rename colums\n",
    "cfg_col_rename = {'sales':'revenue'} # rename all instances of sales to revenue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sniffing columns ok\n"
     ]
    },
    {
     "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>date</th>\n",
       "      <th>revenue</th>\n",
       "      <th>cost</th>\n",
       "      <th>profit</th>\n",
       "      <th>filepath</th>\n",
       "      <th>filename</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-renamed-fe...</td>\n",
       "      <td>test-data-input-csv-renamed-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-02-02</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-renamed-fe...</td>\n",
       "      <td>test-data-input-csv-renamed-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-02-03</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-renamed-fe...</td>\n",
       "      <td>test-data-input-csv-renamed-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "      <td>test-data/input/test-data-input-csv-renamed-ja...</td>\n",
       "      <td>test-data-input-csv-renamed-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-01-02</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "      <td>test-data/input/test-data-input-csv-renamed-ja...</td>\n",
       "      <td>test-data-input-csv-renamed-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2011-01-03</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "      <td>test-data/input/test-data-input-csv-renamed-ja...</td>\n",
       "      <td>test-data-input-csv-renamed-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2011-03-01</td>\n",
       "      <td>300</td>\n",
       "      <td>-100</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data/input/test-data-input-csv-renamed-ma...</td>\n",
       "      <td>test-data-input-csv-renamed-mar.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2011-03-02</td>\n",
       "      <td>300</td>\n",
       "      <td>-100</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data/input/test-data-input-csv-renamed-ma...</td>\n",
       "      <td>test-data-input-csv-renamed-mar.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2011-03-03</td>\n",
       "      <td>300</td>\n",
       "      <td>-100</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data/input/test-data-input-csv-renamed-ma...</td>\n",
       "      <td>test-data-input-csv-renamed-mar.csv</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  revenue  cost  profit                                           filepath                             filename\n",
       "0  2011-02-01      200   -90     110  test-data/input/test-data-input-csv-renamed-fe...  test-data-input-csv-renamed-feb.csv\n",
       "1  2011-02-02      200   -90     110  test-data/input/test-data-input-csv-renamed-fe...  test-data-input-csv-renamed-feb.csv\n",
       "2  2011-02-03      200   -90     110  test-data/input/test-data-input-csv-renamed-fe...  test-data-input-csv-renamed-feb.csv\n",
       "3  2011-01-01      100   -80      20  test-data/input/test-data-input-csv-renamed-ja...  test-data-input-csv-renamed-jan.csv\n",
       "4  2011-01-02      100   -80      20  test-data/input/test-data-input-csv-renamed-ja...  test-data-input-csv-renamed-jan.csv\n",
       "5  2011-01-03      100   -80      20  test-data/input/test-data-input-csv-renamed-ja...  test-data-input-csv-renamed-jan.csv\n",
       "6  2011-03-01      300  -100     200  test-data/input/test-data-input-csv-renamed-ma...  test-data-input-csv-renamed-mar.csv\n",
       "7  2011-03-02      300  -100     200  test-data/input/test-data-input-csv-renamed-ma...  test-data-input-csv-renamed-mar.csv\n",
       "8  2011-03-03      300  -100     200  test-data/input/test-data-input-csv-renamed-ma...  test-data-input-csv-renamed-mar.csv"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c = d6tc.CombinerCSV(cfg_fnames, columns_rename = cfg_col_rename, columns_select = cfg_col_sel) \n",
    "c.combine_preview() \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Case: Identify change in column order\n",
    "\n",
    "If you read your files into a database this will be a real problem because it look like the files are all the same whereas in fact they have changes. This is because programs like dask or sql loaders assume the column order is the same. With `d6tstack` you can easily identify and fix such a case."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['test-data/input/test-data-input-csv-reorder-jan.csv', 'test-data/input/test-data-input-csv-reorder-mar.csv', 'test-data/input/test-data-input-csv-reorder-feb.csv']\n"
     ]
    }
   ],
   "source": [
    "cfg_fnames = list(glob.glob('test-data/input/test-data-input-csv-reorder-*.csv'))\n",
    "print(cfg_fnames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sniffing columns ok\n"
     ]
    }
   ],
   "source": [
    "# get previews\n",
    "c = d6tc.CombinerCSV(cfg_fnames) # all_strings=True makes reading faster\n",
    "col_sniff = c.sniff_columns()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we can see that all columns are not equal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "all columns equal? False\n",
      "\n",
      "in what order do columns appear in the files?\n",
      "\n",
      "   date  sales  cost  profit\n",
      "0     0      1     2       3\n",
      "1     0      1     2       3\n",
      "2     0      1     3       2\n"
     ]
    }
   ],
   "source": [
    "print('all columns equal?', col_sniff['is_all_equal'])\n",
    "print('')\n",
    "print('in what order do columns appear in the files?')\n",
    "print('')\n",
    "print(col_sniff['df_columns_order'].reset_index(drop=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "scrolled": true
   },
   "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>date</th>\n",
       "      <th>sales</th>\n",
       "      <th>cost</th>\n",
       "      <th>profit</th>\n",
       "      <th>filepath</th>\n",
       "      <th>filename</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-reorder-fe...</td>\n",
       "      <td>test-data-input-csv-reorder-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-02-02</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-reorder-fe...</td>\n",
       "      <td>test-data-input-csv-reorder-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-02-03</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "      <td>test-data/input/test-data-input-csv-reorder-fe...</td>\n",
       "      <td>test-data-input-csv-reorder-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "      <td>test-data/input/test-data-input-csv-reorder-ja...</td>\n",
       "      <td>test-data-input-csv-reorder-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-01-02</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "      <td>test-data/input/test-data-input-csv-reorder-ja...</td>\n",
       "      <td>test-data-input-csv-reorder-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2011-01-03</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "      <td>test-data/input/test-data-input-csv-reorder-ja...</td>\n",
       "      <td>test-data-input-csv-reorder-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2011-03-01</td>\n",
       "      <td>300</td>\n",
       "      <td>-100</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data/input/test-data-input-csv-reorder-ma...</td>\n",
       "      <td>test-data-input-csv-reorder-mar.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2011-03-02</td>\n",
       "      <td>300</td>\n",
       "      <td>-100</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data/input/test-data-input-csv-reorder-ma...</td>\n",
       "      <td>test-data-input-csv-reorder-mar.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2011-03-03</td>\n",
       "      <td>300</td>\n",
       "      <td>-100</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data/input/test-data-input-csv-reorder-ma...</td>\n",
       "      <td>test-data-input-csv-reorder-mar.csv</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  sales  cost  profit                                           filepath                             filename\n",
       "0  2011-02-01    200   -90     110  test-data/input/test-data-input-csv-reorder-fe...  test-data-input-csv-reorder-feb.csv\n",
       "1  2011-02-02    200   -90     110  test-data/input/test-data-input-csv-reorder-fe...  test-data-input-csv-reorder-feb.csv\n",
       "2  2011-02-03    200   -90     110  test-data/input/test-data-input-csv-reorder-fe...  test-data-input-csv-reorder-feb.csv\n",
       "3  2011-01-01    100   -80      20  test-data/input/test-data-input-csv-reorder-ja...  test-data-input-csv-reorder-jan.csv\n",
       "4  2011-01-02    100   -80      20  test-data/input/test-data-input-csv-reorder-ja...  test-data-input-csv-reorder-jan.csv\n",
       "5  2011-01-03    100   -80      20  test-data/input/test-data-input-csv-reorder-ja...  test-data-input-csv-reorder-jan.csv\n",
       "6  2011-03-01    300  -100     200  test-data/input/test-data-input-csv-reorder-ma...  test-data-input-csv-reorder-mar.csv\n",
       "7  2011-03-02    300  -100     200  test-data/input/test-data-input-csv-reorder-ma...  test-data-input-csv-reorder-mar.csv\n",
       "8  2011-03-03    300  -100     200  test-data/input/test-data-input-csv-reorder-ma...  test-data-input-csv-reorder-mar.csv"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.combine_preview() # automatically puts it in the right order"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Customize separator and pass pd.read_csv() params\n",
    "\n",
    "You can pass additional parameters such as separators and any params for `pd.read_csv()` to the combiner."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sniffing columns ok\n",
      "{'files_columns': {'test-data/input/test-data-input-csv-reorder-feb.csv': ['date', 'sales', 'cost', 'profit'], 'test-data/input/test-data-input-csv-reorder-jan.csv': ['date', 'sales', 'cost', 'profit'], 'test-data/input/test-data-input-csv-reorder-mar.csv': ['date', 'sales', 'profit', 'cost']}, 'columns_all': ['date', 'sales', 'cost', 'profit'], 'columns_common': ['date', 'sales', 'cost', 'profit'], 'columns_unique': [], 'is_all_equal': False, 'df_columns_present':                                                     date  sales  cost  profit\n",
      "file_path                                                                    \n",
      "test-data/input/test-data-input-csv-reorder-feb...  True   True  True    True\n",
      "test-data/input/test-data-input-csv-reorder-jan...  True   True  True    True\n",
      "test-data/input/test-data-input-csv-reorder-mar...  True   True  True    True, 'df_columns_order':                                                     date  sales  cost  profit\n",
      "test-data/input/test-data-input-csv-reorder-feb...     0      1     2       3\n",
      "test-data/input/test-data-input-csv-reorder-jan...     0      1     2       3\n",
      "test-data/input/test-data-input-csv-reorder-mar...     0      1     3       2}\n"
     ]
    }
   ],
   "source": [
    "c = d6tc.CombinerCSV(cfg_fnames, sep=',', read_csv_params={'header': None})\n",
    "col_sniff = c.sniff_columns()\n",
    "print(col_sniff)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CSV out of core functionality\n",
    "\n",
    "If your files are large you don't want to read them all in memory and then save. Instead you can write directly to the output file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'test-data/output/test.csv'"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.to_csv_combine('test-data/output/test.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Auto Detect pd.read_csv() settings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "### Detect CSV settings across a single file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'delim': ',', 'skiprows': 0, 'has_header': True, 'header': 0}\n"
     ]
    }
   ],
   "source": [
    "cfg_sniff = d6tstack.sniffer.sniff_settings_csv([cfg_fnames[0]])\n",
    "print(cfg_sniff)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Detect CSV settings across multiple files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'delim': ',', 'skiprows': 0, 'has_header': True, 'header': 0}\n"
     ]
    }
   ],
   "source": [
    "# finds common csv across all files\n",
    "cfg_sniff = d6tstack.sniffer.sniff_settings_csv(cfg_fnames)\n",
    "print(cfg_sniff)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}