examples-pyspark.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# d6tstack with pyspark\n",
    "\n",
    "Pyspark is a great library for out-of-core computing. But if input files are not properly organized it quickly breaks. For example:\n",
    "\n",
    "1) if columns are different between files: [unlike dask](https://github.com/d6t/d6tstack/blob/master/examples-dask.ipynb) pyspark actually handles that\n",
    "\n",
    "2) if column order is rearranged between files it will read data, but into the wrong columns and you won't notice it\n",
    "\n",
    "3) if columns are named between files, you'll have to manually fix the situation\n",
    "\n",
    "Pyspark can't easily handle those scenarios. With d6tstack you can easily fix the situation with just a few lines of code!\n",
    "\n",
    "For more instructions, examples and documentation see https://github.com/d6t/d6tstack"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import findspark\n",
    "findspark.init(r'E:\\progs.install\\spark-2.2.0-bin-hadoop2.7')\n",
    "\n",
    "import pyspark\n",
    "sc = pyspark.SparkContext(appName=\"myAppName\")\n",
    "from pyspark.sql import SQLContext\n",
    "sqlc = SQLContext(sc)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Base Case: Columns are same between all files\n",
    "As a base case, we have input files which have consistent input columns and thus can be easily read in dask."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>cost</th>\n",
       "      <th>date</th>\n",
       "      <th>profit</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-01</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-02</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-03</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-04</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-05</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-06</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-07</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-08</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-09</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-10</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-02</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-03</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-04</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-05</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-06</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-07</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-08</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-09</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-10</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-02</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-03</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-04</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-05</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-06</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-07</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-08</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-09</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-10</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cost        date profit sales\n",
       "0   -100  2011-03-01    200   300\n",
       "1   -100  2011-03-02    200   300\n",
       "2   -100  2011-03-03    200   300\n",
       "3   -100  2011-03-04    200   300\n",
       "4   -100  2011-03-05    200   300\n",
       "5   -100  2011-03-06    200   300\n",
       "6   -100  2011-03-07    200   300\n",
       "7   -100  2011-03-08    200   300\n",
       "8   -100  2011-03-09    200   300\n",
       "9   -100  2011-03-10    200   300\n",
       "10   -90  2011-02-01    110   200\n",
       "11   -90  2011-02-02    110   200\n",
       "12   -90  2011-02-03    110   200\n",
       "13   -90  2011-02-04    110   200\n",
       "14   -90  2011-02-05    110   200\n",
       "15   -90  2011-02-06    110   200\n",
       "16   -90  2011-02-07    110   200\n",
       "17   -90  2011-02-08    110   200\n",
       "18   -90  2011-02-09    110   200\n",
       "19   -90  2011-02-10    110   200\n",
       "20   -80  2011-01-01     20   100\n",
       "21   -80  2011-01-02     20   100\n",
       "22   -80  2011-01-03     20   100\n",
       "23   -80  2011-01-04     20   100\n",
       "24   -80  2011-01-05     20   100\n",
       "25   -80  2011-01-06     20   100\n",
       "26   -80  2011-01-07     20   100\n",
       "27   -80  2011-01-08     20   100\n",
       "28   -80  2011-01-09     20   100\n",
       "29   -80  2011-01-10     20   100"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sdf = sqlc.read.csv('test-data/input/test-data-input-csv-clean-*.csv', inferSchema=False, header=True)\n",
    "sdf.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem Case 1: Columns are different between files\n",
    "That worked well. But what happens if your input files have inconsistent columns across files? Say for example one file has a new column that the other files don't have.\n",
    "\n",
    "[unlike dask](https://github.com/d6t/d6tstack/blob/master/examples-dask.ipynb) pyspark actually handles that. The new column got correctly added."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>cost</th>\n",
       "      <th>date</th>\n",
       "      <th>profit</th>\n",
       "      <th>sales</th>\n",
       "      <th>profit2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-01</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-02</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-03</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-04</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-05</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-06</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-07</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-08</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-09</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>-100</td>\n",
       "      <td>2011-03-10</td>\n",
       "      <td>200</td>\n",
       "      <td>300</td>\n",
       "      <td>400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-02</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-03</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-04</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-05</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-06</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-07</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-08</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-09</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>-90</td>\n",
       "      <td>2011-02-10</td>\n",
       "      <td>110</td>\n",
       "      <td>200</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-02</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-03</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-04</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-05</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-06</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-07</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-08</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-09</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>-80</td>\n",
       "      <td>2011-01-10</td>\n",
       "      <td>20</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cost        date profit sales profit2\n",
       "0   -100  2011-03-01    200   300     400\n",
       "1   -100  2011-03-02    200   300     400\n",
       "2   -100  2011-03-03    200   300     400\n",
       "3   -100  2011-03-04    200   300     400\n",
       "4   -100  2011-03-05    200   300     400\n",
       "5   -100  2011-03-06    200   300     400\n",
       "6   -100  2011-03-07    200   300     400\n",
       "7   -100  2011-03-08    200   300     400\n",
       "8   -100  2011-03-09    200   300     400\n",
       "9   -100  2011-03-10    200   300     400\n",
       "10   -90  2011-02-01    110   200    None\n",
       "11   -90  2011-02-02    110   200    None\n",
       "12   -90  2011-02-03    110   200    None\n",
       "13   -90  2011-02-04    110   200    None\n",
       "14   -90  2011-02-05    110   200    None\n",
       "15   -90  2011-02-06    110   200    None\n",
       "16   -90  2011-02-07    110   200    None\n",
       "17   -90  2011-02-08    110   200    None\n",
       "18   -90  2011-02-09    110   200    None\n",
       "19   -90  2011-02-10    110   200    None\n",
       "20   -80  2011-01-01     20   100    None\n",
       "21   -80  2011-01-02     20   100    None\n",
       "22   -80  2011-01-03     20   100    None\n",
       "23   -80  2011-01-04     20   100    None\n",
       "24   -80  2011-01-05     20   100    None\n",
       "25   -80  2011-01-06     20   100    None\n",
       "26   -80  2011-01-07     20   100    None\n",
       "27   -80  2011-01-08     20   100    None\n",
       "28   -80  2011-01-09     20   100    None\n",
       "29   -80  2011-01-10     20   100    None"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sdf = sqlc.read.csv('test-data/input/test-data-input-csv-colmismatch-*.csv', inferSchema=False, header=True)\n",
    "sdf.toPandas()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem Case 2: Columns are reordered between files\n",
    "This is a sneaky case. The columns are the same but the order is different! Pyspark will read everything just fine without a warning but your data is totally messed up! You don't even notice it! You'll start using the data and at some point notice something weird is going on!\n",
    "\n",
    "In the example below, the \"profit\" column contains data from the \"cost\" column!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "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>profit</th>\n",
       "      <th>cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-03-01</td>\n",
       "      <td>300</td>\n",
       "      <td>200</td>\n",
       "      <td>-100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-03-02</td>\n",
       "      <td>300</td>\n",
       "      <td>200</td>\n",
       "      <td>-100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-03-03</td>\n",
       "      <td>300</td>\n",
       "      <td>200</td>\n",
       "      <td>-100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-03-04</td>\n",
       "      <td>300</td>\n",
       "      <td>200</td>\n",
       "      <td>-100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-03-05</td>\n",
       "      <td>300</td>\n",
       "      <td>200</td>\n",
       "      <td>-100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2011-03-06</td>\n",
       "      <td>300</td>\n",
       "      <td>200</td>\n",
       "      <td>-100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2011-03-07</td>\n",
       "      <td>300</td>\n",
       "      <td>200</td>\n",
       "      <td>-100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2011-03-08</td>\n",
       "      <td>300</td>\n",
       "      <td>200</td>\n",
       "      <td>-100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2011-03-09</td>\n",
       "      <td>300</td>\n",
       "      <td>200</td>\n",
       "      <td>-100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2011-03-10</td>\n",
       "      <td>300</td>\n",
       "      <td>200</td>\n",
       "      <td>-100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2011-02-02</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2011-02-03</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2011-02-04</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2011-02-05</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2011-02-06</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2011-02-07</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2011-02-08</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2011-02-09</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2011-02-10</td>\n",
       "      <td>200</td>\n",
       "      <td>-90</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2011-01-02</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2011-01-03</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2011-01-04</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2011-01-05</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2011-01-06</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2011-01-07</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2011-01-08</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2011-01-09</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2011-01-10</td>\n",
       "      <td>100</td>\n",
       "      <td>-80</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          date sales profit  cost\n",
       "0   2011-03-01   300    200  -100\n",
       "1   2011-03-02   300    200  -100\n",
       "2   2011-03-03   300    200  -100\n",
       "3   2011-03-04   300    200  -100\n",
       "4   2011-03-05   300    200  -100\n",
       "5   2011-03-06   300    200  -100\n",
       "6   2011-03-07   300    200  -100\n",
       "7   2011-03-08   300    200  -100\n",
       "8   2011-03-09   300    200  -100\n",
       "9   2011-03-10   300    200  -100\n",
       "10  2011-02-01   200    -90   110\n",
       "11  2011-02-02   200    -90   110\n",
       "12  2011-02-03   200    -90   110\n",
       "13  2011-02-04   200    -90   110\n",
       "14  2011-02-05   200    -90   110\n",
       "15  2011-02-06   200    -90   110\n",
       "16  2011-02-07   200    -90   110\n",
       "17  2011-02-08   200    -90   110\n",
       "18  2011-02-09   200    -90   110\n",
       "19  2011-02-10   200    -90   110\n",
       "20  2011-01-01   100    -80    20\n",
       "21  2011-01-02   100    -80    20\n",
       "22  2011-01-03   100    -80    20\n",
       "23  2011-01-04   100    -80    20\n",
       "24  2011-01-05   100    -80    20\n",
       "25  2011-01-06   100    -80    20\n",
       "26  2011-01-07   100    -80    20\n",
       "27  2011-01-08   100    -80    20\n",
       "28  2011-01-09   100    -80    20\n",
       "29  2011-01-10   100    -80    20"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sdf = sqlc.read.csv('test-data/input/test-data-input-csv-reorder-*.csv', inferSchema=False, header=True)\n",
    "sdf.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fixing the problem with d6stack\n",
    "After a while you'll get to the root of the problem, and then you can either manually process those files or use d6tstack to easily check for such a situation and fix it with a few lines of code - no manual processing required. Let's take a look!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/anaconda3/lib/python3.6/importlib/_bootstrap.py:219: RuntimeWarning: numpy.dtype size changed, may indicate binary incompatibility. Expected 96, got 88\n",
      "  return f(*args, **kwds)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sniffing columns ok\n",
      "all columns equal? False\n",
      "\n",
      "in what order do columns appear in the files?\n",
      "\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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   date  sales  cost  profit\n",
       "0     0      1     2       3\n",
       "1     0      1     2       3\n",
       "2     0      1     3       2"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import glob\n",
    "import d6tstack.combine_csv\n",
    "\n",
    "cfg_fnames = list(glob.glob('test-data/input/test-data-input-csv-reorder-*.csv'))\n",
    "c = d6tstack.combine_csv.CombinerCSV(cfg_fnames, all_strings=True)\n",
    "\n",
    "# check columns\n",
    "col_sniff = c.sniff_columns()\n",
    "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",
    "col_sniff['df_columns_order'].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, just a useful check before loading data into dask you can see that the columns don't line up. It's very fast to run because it only reads the headers, there's NO reason for you NOT to do it from a QA perspective.\n",
    "\n",
    "Same as above, the fix is the same few lines of code with d6stack."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# out-of-core combining\n",
    "c.to_csv_align(output_dir='test-data/output/')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>profit</th>\n",
       "      <th>date</th>\n",
       "      <th>cost</th>\n",
       "      <th>sales</th>\n",
       "      <th>filename</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>110</td>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>-90</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data-input-csv-reorder-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>110</td>\n",
       "      <td>2011-02-02</td>\n",
       "      <td>-90</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data-input-csv-reorder-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>110</td>\n",
       "      <td>2011-02-03</td>\n",
       "      <td>-90</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data-input-csv-reorder-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>110</td>\n",
       "      <td>2011-02-04</td>\n",
       "      <td>-90</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data-input-csv-reorder-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>110</td>\n",
       "      <td>2011-02-05</td>\n",
       "      <td>-90</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data-input-csv-reorder-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>110</td>\n",
       "      <td>2011-02-06</td>\n",
       "      <td>-90</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data-input-csv-reorder-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>110</td>\n",
       "      <td>2011-02-07</td>\n",
       "      <td>-90</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data-input-csv-reorder-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>110</td>\n",
       "      <td>2011-02-08</td>\n",
       "      <td>-90</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data-input-csv-reorder-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>110</td>\n",
       "      <td>2011-02-09</td>\n",
       "      <td>-90</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data-input-csv-reorder-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>110</td>\n",
       "      <td>2011-02-10</td>\n",
       "      <td>-90</td>\n",
       "      <td>200</td>\n",
       "      <td>test-data-input-csv-reorder-feb.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>20</td>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>-80</td>\n",
       "      <td>100</td>\n",
       "      <td>test-data-input-csv-reorder-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>20</td>\n",
       "      <td>2011-01-02</td>\n",
       "      <td>-80</td>\n",
       "      <td>100</td>\n",
       "      <td>test-data-input-csv-reorder-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>20</td>\n",
       "      <td>2011-01-03</td>\n",
       "      <td>-80</td>\n",
       "      <td>100</td>\n",
       "      <td>test-data-input-csv-reorder-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>20</td>\n",
       "      <td>2011-01-04</td>\n",
       "      <td>-80</td>\n",
       "      <td>100</td>\n",
       "      <td>test-data-input-csv-reorder-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>20</td>\n",
       "      <td>2011-01-05</td>\n",
       "      <td>-80</td>\n",
       "      <td>100</td>\n",
       "      <td>test-data-input-csv-reorder-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>20</td>\n",
       "      <td>2011-01-06</td>\n",
       "      <td>-80</td>\n",
       "      <td>100</td>\n",
       "      <td>test-data-input-csv-reorder-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>20</td>\n",
       "      <td>2011-01-07</td>\n",
       "      <td>-80</td>\n",
       "      <td>100</td>\n",
       "      <td>test-data-input-csv-reorder-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>20</td>\n",
       "      <td>2011-01-08</td>\n",
       "      <td>-80</td>\n",
       "      <td>100</td>\n",
       "      <td>test-data-input-csv-reorder-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>20</td>\n",
       "      <td>2011-01-09</td>\n",
       "      <td>-80</td>\n",
       "      <td>100</td>\n",
       "      <td>test-data-input-csv-reorder-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>2011-01-10</td>\n",
       "      <td>-80</td>\n",
       "      <td>100</td>\n",
       "      <td>test-data-input-csv-reorder-jan.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>200</td>\n",
       "      <td>2011-03-01</td>\n",
       "      <td>-100</td>\n",
       "      <td>300</td>\n",
       "      <td>test-data-input-csv-reorder-mar.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>200</td>\n",
       "      <td>2011-03-02</td>\n",
       "      <td>-100</td>\n",
       "      <td>300</td>\n",
       "      <td>test-data-input-csv-reorder-mar.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>200</td>\n",
       "      <td>2011-03-03</td>\n",
       "      <td>-100</td>\n",
       "      <td>300</td>\n",
       "      <td>test-data-input-csv-reorder-mar.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>200</td>\n",
       "      <td>2011-03-04</td>\n",
       "      <td>-100</td>\n",
       "      <td>300</td>\n",
       "      <td>test-data-input-csv-reorder-mar.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>200</td>\n",
       "      <td>2011-03-05</td>\n",
       "      <td>-100</td>\n",
       "      <td>300</td>\n",
       "      <td>test-data-input-csv-reorder-mar.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>200</td>\n",
       "      <td>2011-03-06</td>\n",
       "      <td>-100</td>\n",
       "      <td>300</td>\n",
       "      <td>test-data-input-csv-reorder-mar.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>200</td>\n",
       "      <td>2011-03-07</td>\n",
       "      <td>-100</td>\n",
       "      <td>300</td>\n",
       "      <td>test-data-input-csv-reorder-mar.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>200</td>\n",
       "      <td>2011-03-08</td>\n",
       "      <td>-100</td>\n",
       "      <td>300</td>\n",
       "      <td>test-data-input-csv-reorder-mar.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>200</td>\n",
       "      <td>2011-03-09</td>\n",
       "      <td>-100</td>\n",
       "      <td>300</td>\n",
       "      <td>test-data-input-csv-reorder-mar.csv</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>200</td>\n",
       "      <td>2011-03-10</td>\n",
       "      <td>-100</td>\n",
       "      <td>300</td>\n",
       "      <td>test-data-input-csv-reorder-mar.csv</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   profit        date  cost sales                             filename\n",
       "0     110  2011-02-01   -90   200  test-data-input-csv-reorder-feb.csv\n",
       "1     110  2011-02-02   -90   200  test-data-input-csv-reorder-feb.csv\n",
       "2     110  2011-02-03   -90   200  test-data-input-csv-reorder-feb.csv\n",
       "3     110  2011-02-04   -90   200  test-data-input-csv-reorder-feb.csv\n",
       "4     110  2011-02-05   -90   200  test-data-input-csv-reorder-feb.csv\n",
       "5     110  2011-02-06   -90   200  test-data-input-csv-reorder-feb.csv\n",
       "6     110  2011-02-07   -90   200  test-data-input-csv-reorder-feb.csv\n",
       "7     110  2011-02-08   -90   200  test-data-input-csv-reorder-feb.csv\n",
       "8     110  2011-02-09   -90   200  test-data-input-csv-reorder-feb.csv\n",
       "9     110  2011-02-10   -90   200  test-data-input-csv-reorder-feb.csv\n",
       "10     20  2011-01-01   -80   100  test-data-input-csv-reorder-jan.csv\n",
       "11     20  2011-01-02   -80   100  test-data-input-csv-reorder-jan.csv\n",
       "12     20  2011-01-03   -80   100  test-data-input-csv-reorder-jan.csv\n",
       "13     20  2011-01-04   -80   100  test-data-input-csv-reorder-jan.csv\n",
       "14     20  2011-01-05   -80   100  test-data-input-csv-reorder-jan.csv\n",
       "15     20  2011-01-06   -80   100  test-data-input-csv-reorder-jan.csv\n",
       "16     20  2011-01-07   -80   100  test-data-input-csv-reorder-jan.csv\n",
       "17     20  2011-01-08   -80   100  test-data-input-csv-reorder-jan.csv\n",
       "18     20  2011-01-09   -80   100  test-data-input-csv-reorder-jan.csv\n",
       "19     20  2011-01-10   -80   100  test-data-input-csv-reorder-jan.csv\n",
       "20    200  2011-03-01  -100   300  test-data-input-csv-reorder-mar.csv\n",
       "21    200  2011-03-02  -100   300  test-data-input-csv-reorder-mar.csv\n",
       "22    200  2011-03-03  -100   300  test-data-input-csv-reorder-mar.csv\n",
       "23    200  2011-03-04  -100   300  test-data-input-csv-reorder-mar.csv\n",
       "24    200  2011-03-05  -100   300  test-data-input-csv-reorder-mar.csv\n",
       "25    200  2011-03-06  -100   300  test-data-input-csv-reorder-mar.csv\n",
       "26    200  2011-03-07  -100   300  test-data-input-csv-reorder-mar.csv\n",
       "27    200  2011-03-08  -100   300  test-data-input-csv-reorder-mar.csv\n",
       "28    200  2011-03-09  -100   300  test-data-input-csv-reorder-mar.csv\n",
       "29    200  2011-03-10  -100   300  test-data-input-csv-reorder-mar.csv"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sdf = sqlc.read.csv('test-data/output/d6tstack-test-data-input-csv-colmismatch-*.csv', inferSchema=False, header=True)\n",
    "sdf.toPandas()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem Case 3: Columns are renamed between files\n",
    "In this case a column gets renamed between files so you have two columns with partial NaNs that should really be the same column. You would have to manually inspect which columns this applies to and then manually edit them looking for NaNs.\n",
    "\n",
    "Instead you can use d6tstack to make your input files consistent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# see examples-csv.ipynb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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
}