leakysearch_kernel.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5",
    "collapsed": true
   },
   "source": [
    "## Giba's Property\n",
    "\n",
    "- https://www.kaggle.com/titericz/the-property-by-giba (kernel)\n",
    "- https://www.kaggle.com/c/santander-value-prediction-challenge/discussion/61329 (post)\n",
    "\n",
    "#### This kernel is just to extend giba's result in a *stupid* brute-force way\n",
    "### The updated part is based on [S D's comment](https://www.kaggle.com/johnfarrell/giba-s-property-extended-result/notebook#358945)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "224ce4a65b5b0b0f795d726ae603354d3f193149",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import os\n",
    "import gc\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "from tqdm import tqdm\n",
    "# DATA_DIR = '../input/'\n",
    "\n",
    "hex2dec = lambda x: int(x, 16)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
    "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train = pd.read_csv('train.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.iloc[1383, :]['e176a204a']\n",
    "# train[train == 1563411.76].count(axis=0).sort_values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "print(\"To String...\")\n",
    "X = train[train.columns.drop(['ID','target'])] # only get \"X\" vectort\n",
    "\n",
    "print(\"rounding...\")\n",
    "for i in X.columns:\n",
    "    X.loc[:, i] = np.round(X[i] , decimals=3)\n",
    "    \n",
    "tmp = X[X.columns[0]].apply(str)\n",
    "tmp[tmp=='0.0'] = ''\n",
    "CV = pd.DataFrame()\n",
    "CV['features'] = tmp\n",
    "\n",
    "for feat in X.columns[1:]:\n",
    "    tmp = X[feat].apply(str)\n",
    "    tmp[ tmp=='0.0' ] = ''\n",
    "    CV['features'] = CV['features'] + tmp + ' '\n",
    "\n",
    "gc.collect()\n",
    "print(CV)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "from sklearn.feature_extraction.text import CountVectorizer, TfidfVectorizer\n",
    "\n",
    "rd = TfidfVectorizer(lowercase=True, ngram_range=(1, 1), max_df=0.99, min_df=2, use_idf=False)\n",
    "words = rd.fit_transform(CV['features'])\n",
    "\n",
    "print(words.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "words"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "_uuid": "84489969a56ef225b9aea7d33e28823ea8991a05",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# cols = [\n",
    "#     \"f190486d6\",\"58e2e02e6\",\"eeb9cd3aa\",\"9fd594eec\",\"6eef030c1\",\"15ace8c9f\",\n",
    "#     \"fb0f5dbfe\",\"58e056e12\",\"20aa07010\",\"024c577b9\",\"d6bb78916\",\n",
    "#     \"b43a7cfd5\",\"58232a6fb\"\n",
    "# ]\n",
    "\n",
    "cols = ['f190486d6', '58e2e02e6', 'eeb9cd3aa', '9fd594eec', '6eef030c1', '15ace8c9f', \n",
    "        'fb0f5dbfe', '58e056e12', '20aa07010', '024c577b9', 'd6bb78916', 'b43a7cfd5', \n",
    "        '58232a6fb', '1702b5bf0', '324921c7b', '62e59a501', '2ec5b290f', '241f0f867', \n",
    "        'fb49e4212', '66ace2992', 'f74e8f13d', '5c6487af1', '963a49cdc', '26fc93eb7', \n",
    "        '1931ccfdd', '703885424', '70feb1494', '491b9ee45', '23310aa6f', 'e176a204a', \n",
    "        '6619d81fc', '1db387535', \n",
    "        'fc99f9426',\n",
    "        '91f701ba2', '0572565c2', '190db8488', 'adb64ff71', 'c47340d97', 'c5a231d81', '0ff32eb98'\n",
    "       ]\n",
    "rows = [1757, 3809, 511, 3798, 625, 3303, 4095, 1283, 4209, 1696, 3511, 816, 245, 1383, 2071, 3492, 378, 2971, 2366, 4414, 2790, 3979, 193, 1189, 3516, 810, 4443, 3697, 235, 1382, 4384, 3418, 4396, 921, 3176, 650]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "aefbbe2a606ef017ca1eab28bf6f5a63b5742ae8"
   },
   "source": [
    "### The original result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "_uuid": "0b43133942747c28d646ccc83adee1565f8fb31e",
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "original shape (36, 42)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>target</th>\n",
       "      <th>f190486d6</th>\n",
       "      <th>58e2e02e6</th>\n",
       "      <th>eeb9cd3aa</th>\n",
       "      <th>9fd594eec</th>\n",
       "      <th>6eef030c1</th>\n",
       "      <th>15ace8c9f</th>\n",
       "      <th>fb0f5dbfe</th>\n",
       "      <th>58e056e12</th>\n",
       "      <th>...</th>\n",
       "      <th>6619d81fc</th>\n",
       "      <th>1db387535</th>\n",
       "      <th>fc99f9426</th>\n",
       "      <th>91f701ba2</th>\n",
       "      <th>0572565c2</th>\n",
       "      <th>190db8488</th>\n",
       "      <th>adb64ff71</th>\n",
       "      <th>c47340d97</th>\n",
       "      <th>c5a231d81</th>\n",
       "      <th>0ff32eb98</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>0b132f2c6</td>\n",
       "      <td>500000.00</td>\n",
       "      <td>60000.00</td>\n",
       "      <td>1359000.00</td>\n",
       "      <td>5450500.00</td>\n",
       "      <td>280000.00</td>\n",
       "      <td>2352551.72</td>\n",
       "      <td>160000.00</td>\n",
       "      <td>3513333.34</td>\n",
       "      <td>310000.00</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>235</th>\n",
       "      <td>0e1920aa8</td>\n",
       "      <td>406000.00</td>\n",
       "      <td>528666.66</td>\n",
       "      <td>307000.00</td>\n",
       "      <td>814800.00</td>\n",
       "      <td>1878571.42</td>\n",
       "      <td>500000.00</td>\n",
       "      <td>12000000.00</td>\n",
       "      <td>60000.00</td>\n",
       "      <td>1359000.00</td>\n",
       "      <td>...</td>\n",
       "      <td>1015000.00</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>0e8c830c4</td>\n",
       "      <td>1477600.00</td>\n",
       "      <td>75000.00</td>\n",
       "      <td>3147200.00</td>\n",
       "      <td>466461.54</td>\n",
       "      <td>1600000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>440000.00</td>\n",
       "      <td>3076666.66</td>\n",
       "      <td>550000.00</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>378</th>\n",
       "      <td>16a02e67a</td>\n",
       "      <td>2352551.72</td>\n",
       "      <td>3513333.34</td>\n",
       "      <td>310000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1477600.00</td>\n",
       "      <td>1586888.88</td>\n",
       "      <td>75000.00</td>\n",
       "      <td>3147200.00</td>\n",
       "      <td>466461.54</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>511</th>\n",
       "      <td>1df3ca92e</td>\n",
       "      <td>296444.44</td>\n",
       "      <td>115636.36</td>\n",
       "      <td>540000.00</td>\n",
       "      <td>1015000.00</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>625</th>\n",
       "      <td>24204cd10</td>\n",
       "      <td>550000.00</td>\n",
       "      <td>296444.44</td>\n",
       "      <td>834800.00</td>\n",
       "      <td>115636.36</td>\n",
       "      <td>540000.00</td>\n",
       "      <td>1015000.00</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650</th>\n",
       "      <td>2514ad945</td>\n",
       "      <td>380000.00</td>\n",
       "      <td>1304800.00</td>\n",
       "      <td>267428.58</td>\n",
       "      <td>60666.66</td>\n",
       "      <td>400000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>448666.66</td>\n",
       "      <td>406000.00</td>\n",
       "      <td>609200.00</td>\n",
       "      <td>...</td>\n",
       "      <td>440000.00</td>\n",
       "      <td>3076666.66</td>\n",
       "      <td>550000.00</td>\n",
       "      <td>247166.66</td>\n",
       "      <td>296444.44</td>\n",
       "      <td>834800.00</td>\n",
       "      <td>115636.36</td>\n",
       "      <td>540000.00</td>\n",
       "      <td>1015000.00</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>810</th>\n",
       "      <td>2e57ec99f</td>\n",
       "      <td>307000.00</td>\n",
       "      <td>1878571.42</td>\n",
       "      <td>500000.00</td>\n",
       "      <td>12000000.00</td>\n",
       "      <td>60000.00</td>\n",
       "      <td>1359000.00</td>\n",
       "      <td>5450500.00</td>\n",
       "      <td>280000.00</td>\n",
       "      <td>2352551.72</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>816</th>\n",
       "      <td>2e8ee92a7</td>\n",
       "      <td>1586888.88</td>\n",
       "      <td>3147200.00</td>\n",
       "      <td>466461.54</td>\n",
       "      <td>1600000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>440000.00</td>\n",
       "      <td>3076666.66</td>\n",
       "      <td>550000.00</td>\n",
       "      <td>247166.66</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>921</th>\n",
       "      <td>35dcc0108</td>\n",
       "      <td>1304800.00</td>\n",
       "      <td>60666.66</td>\n",
       "      <td>400000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>448666.66</td>\n",
       "      <td>406000.00</td>\n",
       "      <td>609200.00</td>\n",
       "      <td>528666.66</td>\n",
       "      <td>307000.00</td>\n",
       "      <td>...</td>\n",
       "      <td>550000.00</td>\n",
       "      <td>247166.66</td>\n",
       "      <td>296444.44</td>\n",
       "      <td>834800.00</td>\n",
       "      <td>115636.36</td>\n",
       "      <td>540000.00</td>\n",
       "      <td>1015000.00</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1189</th>\n",
       "      <td>448efbb28</td>\n",
       "      <td>1878571.42</td>\n",
       "      <td>12000000.00</td>\n",
       "      <td>60000.00</td>\n",
       "      <td>1359000.00</td>\n",
       "      <td>5450500.00</td>\n",
       "      <td>280000.00</td>\n",
       "      <td>2352551.72</td>\n",
       "      <td>160000.00</td>\n",
       "      <td>3513333.34</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1283</th>\n",
       "      <td>4a5425356</td>\n",
       "      <td>1600000.00</td>\n",
       "      <td>440000.00</td>\n",
       "      <td>3076666.66</td>\n",
       "      <td>550000.00</td>\n",
       "      <td>247166.66</td>\n",
       "      <td>296444.44</td>\n",
       "      <td>834800.00</td>\n",
       "      <td>115636.36</td>\n",
       "      <td>540000.00</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1382</th>\n",
       "      <td>500d02a95</td>\n",
       "      <td>448666.66</td>\n",
       "      <td>609200.00</td>\n",
       "      <td>528666.66</td>\n",
       "      <td>307000.00</td>\n",
       "      <td>814800.00</td>\n",
       "      <td>1878571.42</td>\n",
       "      <td>500000.00</td>\n",
       "      <td>12000000.00</td>\n",
       "      <td>60000.00</td>\n",
       "      <td>...</td>\n",
       "      <td>540000.00</td>\n",
       "      <td>1015000.00</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1383</th>\n",
       "      <td>502cdea84</td>\n",
       "      <td>310000.00</td>\n",
       "      <td>1477600.00</td>\n",
       "      <td>1586888.88</td>\n",
       "      <td>75000.00</td>\n",
       "      <td>3147200.00</td>\n",
       "      <td>466461.54</td>\n",
       "      <td>1600000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>440000.00</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1696</th>\n",
       "      <td>625e88875</td>\n",
       "      <td>3147200.00</td>\n",
       "      <td>1600000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>440000.00</td>\n",
       "      <td>3076666.66</td>\n",
       "      <td>550000.00</td>\n",
       "      <td>247166.66</td>\n",
       "      <td>296444.44</td>\n",
       "      <td>834800.00</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1757</th>\n",
       "      <td>6726fff18</td>\n",
       "      <td>115636.36</td>\n",
       "      <td>1015000.00</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2071</th>\n",
       "      <td>7862786dc</td>\n",
       "      <td>3513333.34</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1477600.00</td>\n",
       "      <td>1586888.88</td>\n",
       "      <td>75000.00</td>\n",
       "      <td>3147200.00</td>\n",
       "      <td>466461.54</td>\n",
       "      <td>1600000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2366</th>\n",
       "      <td>8adafbb52</td>\n",
       "      <td>5450500.00</td>\n",
       "      <td>2352551.72</td>\n",
       "      <td>160000.00</td>\n",
       "      <td>3513333.34</td>\n",
       "      <td>310000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1477600.00</td>\n",
       "      <td>1586888.88</td>\n",
       "      <td>75000.00</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2790</th>\n",
       "      <td>a36b78ff7</td>\n",
       "      <td>60000.00</td>\n",
       "      <td>5450500.00</td>\n",
       "      <td>280000.00</td>\n",
       "      <td>2352551.72</td>\n",
       "      <td>160000.00</td>\n",
       "      <td>3513333.34</td>\n",
       "      <td>310000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1477600.00</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2971</th>\n",
       "      <td>ad960f947</td>\n",
       "      <td>280000.00</td>\n",
       "      <td>160000.00</td>\n",
       "      <td>3513333.34</td>\n",
       "      <td>310000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1477600.00</td>\n",
       "      <td>1586888.88</td>\n",
       "      <td>75000.00</td>\n",
       "      <td>3147200.00</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3176</th>\n",
       "      <td>b9713bb06</td>\n",
       "      <td>8120000.00</td>\n",
       "      <td>267428.58</td>\n",
       "      <td>60666.66</td>\n",
       "      <td>400000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>448666.66</td>\n",
       "      <td>406000.00</td>\n",
       "      <td>609200.00</td>\n",
       "      <td>528666.66</td>\n",
       "      <td>...</td>\n",
       "      <td>3076666.66</td>\n",
       "      <td>550000.00</td>\n",
       "      <td>247166.66</td>\n",
       "      <td>296444.44</td>\n",
       "      <td>834800.00</td>\n",
       "      <td>115636.36</td>\n",
       "      <td>540000.00</td>\n",
       "      <td>1015000.00</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3303</th>\n",
       "      <td>bf6c2d1ef</td>\n",
       "      <td>3076666.66</td>\n",
       "      <td>247166.66</td>\n",
       "      <td>296444.44</td>\n",
       "      <td>834800.00</td>\n",
       "      <td>115636.36</td>\n",
       "      <td>540000.00</td>\n",
       "      <td>1015000.00</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3418</th>\n",
       "      <td>c5e859554</td>\n",
       "      <td>60666.66</td>\n",
       "      <td>0.00</td>\n",
       "      <td>448666.66</td>\n",
       "      <td>406000.00</td>\n",
       "      <td>609200.00</td>\n",
       "      <td>528666.66</td>\n",
       "      <td>307000.00</td>\n",
       "      <td>814800.00</td>\n",
       "      <td>1878571.42</td>\n",
       "      <td>...</td>\n",
       "      <td>296444.44</td>\n",
       "      <td>834800.00</td>\n",
       "      <td>115636.36</td>\n",
       "      <td>540000.00</td>\n",
       "      <td>1015000.00</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3492</th>\n",
       "      <td>c95732596</td>\n",
       "      <td>160000.00</td>\n",
       "      <td>310000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1477600.00</td>\n",
       "      <td>1586888.88</td>\n",
       "      <td>75000.00</td>\n",
       "      <td>3147200.00</td>\n",
       "      <td>466461.54</td>\n",
       "      <td>1600000.00</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3511</th>\n",
       "      <td>ca6cab485</td>\n",
       "      <td>75000.00</td>\n",
       "      <td>466461.54</td>\n",
       "      <td>1600000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>440000.00</td>\n",
       "      <td>3076666.66</td>\n",
       "      <td>550000.00</td>\n",
       "      <td>247166.66</td>\n",
       "      <td>296444.44</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3516</th>\n",
       "      <td>ca98b17ca</td>\n",
       "      <td>814800.00</td>\n",
       "      <td>500000.00</td>\n",
       "      <td>12000000.00</td>\n",
       "      <td>60000.00</td>\n",
       "      <td>1359000.00</td>\n",
       "      <td>5450500.00</td>\n",
       "      <td>280000.00</td>\n",
       "      <td>2352551.72</td>\n",
       "      <td>160000.00</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3697</th>\n",
       "      <td>d4546ed8f</td>\n",
       "      <td>609200.00</td>\n",
       "      <td>307000.00</td>\n",
       "      <td>814800.00</td>\n",
       "      <td>1878571.42</td>\n",
       "      <td>500000.00</td>\n",
       "      <td>12000000.00</td>\n",
       "      <td>60000.00</td>\n",
       "      <td>1359000.00</td>\n",
       "      <td>5450500.00</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3798</th>\n",
       "      <td>d8e48b069</td>\n",
       "      <td>247166.66</td>\n",
       "      <td>834800.00</td>\n",
       "      <td>115636.36</td>\n",
       "      <td>540000.00</td>\n",
       "      <td>1015000.00</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3809</th>\n",
       "      <td>d94655f86</td>\n",
       "      <td>834800.00</td>\n",
       "      <td>540000.00</td>\n",
       "      <td>1015000.00</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3979</th>\n",
       "      <td>e42aae1b8</td>\n",
       "      <td>12000000.00</td>\n",
       "      <td>1359000.00</td>\n",
       "      <td>5450500.00</td>\n",
       "      <td>280000.00</td>\n",
       "      <td>2352551.72</td>\n",
       "      <td>160000.00</td>\n",
       "      <td>3513333.34</td>\n",
       "      <td>310000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4095</th>\n",
       "      <td>eacc7ab9e</td>\n",
       "      <td>440000.00</td>\n",
       "      <td>550000.00</td>\n",
       "      <td>247166.66</td>\n",
       "      <td>296444.44</td>\n",
       "      <td>834800.00</td>\n",
       "      <td>115636.36</td>\n",
       "      <td>540000.00</td>\n",
       "      <td>1015000.00</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4209</th>\n",
       "      <td>f0a57697d</td>\n",
       "      <td>466461.54</td>\n",
       "      <td>0.00</td>\n",
       "      <td>440000.00</td>\n",
       "      <td>3076666.66</td>\n",
       "      <td>550000.00</td>\n",
       "      <td>247166.66</td>\n",
       "      <td>296444.44</td>\n",
       "      <td>834800.00</td>\n",
       "      <td>115636.36</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4384</th>\n",
       "      <td>fae83c142</td>\n",
       "      <td>400000.00</td>\n",
       "      <td>448666.66</td>\n",
       "      <td>406000.00</td>\n",
       "      <td>609200.00</td>\n",
       "      <td>528666.66</td>\n",
       "      <td>307000.00</td>\n",
       "      <td>814800.00</td>\n",
       "      <td>1878571.42</td>\n",
       "      <td>500000.00</td>\n",
       "      <td>...</td>\n",
       "      <td>834800.00</td>\n",
       "      <td>115636.36</td>\n",
       "      <td>540000.00</td>\n",
       "      <td>1015000.00</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4396</th>\n",
       "      <td>fba64a995</td>\n",
       "      <td>267428.58</td>\n",
       "      <td>400000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>448666.66</td>\n",
       "      <td>406000.00</td>\n",
       "      <td>609200.00</td>\n",
       "      <td>528666.66</td>\n",
       "      <td>307000.00</td>\n",
       "      <td>814800.00</td>\n",
       "      <td>...</td>\n",
       "      <td>247166.66</td>\n",
       "      <td>296444.44</td>\n",
       "      <td>834800.00</td>\n",
       "      <td>115636.36</td>\n",
       "      <td>540000.00</td>\n",
       "      <td>1015000.00</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4414</th>\n",
       "      <td>fd0c7cfc2</td>\n",
       "      <td>1359000.00</td>\n",
       "      <td>280000.00</td>\n",
       "      <td>2352551.72</td>\n",
       "      <td>160000.00</td>\n",
       "      <td>3513333.34</td>\n",
       "      <td>310000.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1477600.00</td>\n",
       "      <td>1586888.88</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4443</th>\n",
       "      <td>fef33cb02</td>\n",
       "      <td>528666.66</td>\n",
       "      <td>814800.00</td>\n",
       "      <td>1878571.42</td>\n",
       "      <td>500000.00</td>\n",
       "      <td>12000000.00</td>\n",
       "      <td>60000.00</td>\n",
       "      <td>1359000.00</td>\n",
       "      <td>5450500.00</td>\n",
       "      <td>280000.00</td>\n",
       "      <td>...</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "      <td>1563411.76</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>36 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID       target    f190486d6    58e2e02e6    eeb9cd3aa  \\\n",
       "193   0b132f2c6    500000.00     60000.00   1359000.00   5450500.00   \n",
       "235   0e1920aa8    406000.00    528666.66    307000.00    814800.00   \n",
       "245   0e8c830c4   1477600.00     75000.00   3147200.00    466461.54   \n",
       "378   16a02e67a   2352551.72   3513333.34    310000.00         0.00   \n",
       "511   1df3ca92e    296444.44    115636.36    540000.00   1015000.00   \n",
       "625   24204cd10    550000.00    296444.44    834800.00    115636.36   \n",
       "650   2514ad945    380000.00   1304800.00    267428.58     60666.66   \n",
       "810   2e57ec99f    307000.00   1878571.42    500000.00  12000000.00   \n",
       "816   2e8ee92a7   1586888.88   3147200.00    466461.54   1600000.00   \n",
       "921   35dcc0108   1304800.00     60666.66    400000.00         0.00   \n",
       "1189  448efbb28   1878571.42  12000000.00     60000.00   1359000.00   \n",
       "1283  4a5425356   1600000.00    440000.00   3076666.66    550000.00   \n",
       "1382  500d02a95    448666.66    609200.00    528666.66    307000.00   \n",
       "1383  502cdea84    310000.00   1477600.00   1586888.88     75000.00   \n",
       "1696  625e88875   3147200.00   1600000.00         0.00    440000.00   \n",
       "1757  6726fff18    115636.36   1015000.00   1563411.76   1563411.76   \n",
       "2071  7862786dc   3513333.34         0.00   1477600.00   1586888.88   \n",
       "2366  8adafbb52   5450500.00   2352551.72    160000.00   3513333.34   \n",
       "2790  a36b78ff7     60000.00   5450500.00    280000.00   2352551.72   \n",
       "2971  ad960f947    280000.00    160000.00   3513333.34    310000.00   \n",
       "3176  b9713bb06   8120000.00    267428.58     60666.66    400000.00   \n",
       "3303  bf6c2d1ef   3076666.66    247166.66    296444.44    834800.00   \n",
       "3418  c5e859554     60666.66         0.00    448666.66    406000.00   \n",
       "3492  c95732596    160000.00    310000.00         0.00   1477600.00   \n",
       "3511  ca6cab485     75000.00    466461.54   1600000.00         0.00   \n",
       "3516  ca98b17ca    814800.00    500000.00  12000000.00     60000.00   \n",
       "3697  d4546ed8f    609200.00    307000.00    814800.00   1878571.42   \n",
       "3798  d8e48b069    247166.66    834800.00    115636.36    540000.00   \n",
       "3809  d94655f86    834800.00    540000.00   1015000.00   1563411.76   \n",
       "3979  e42aae1b8  12000000.00   1359000.00   5450500.00    280000.00   \n",
       "4095  eacc7ab9e    440000.00    550000.00    247166.66    296444.44   \n",
       "4209  f0a57697d    466461.54         0.00    440000.00   3076666.66   \n",
       "4384  fae83c142    400000.00    448666.66    406000.00    609200.00   \n",
       "4396  fba64a995    267428.58    400000.00         0.00    448666.66   \n",
       "4414  fd0c7cfc2   1359000.00    280000.00   2352551.72    160000.00   \n",
       "4443  fef33cb02    528666.66    814800.00   1878571.42    500000.00   \n",
       "\n",
       "        9fd594eec    6eef030c1    15ace8c9f    fb0f5dbfe   58e056e12  \\\n",
       "193     280000.00   2352551.72    160000.00   3513333.34   310000.00   \n",
       "235    1878571.42    500000.00  12000000.00     60000.00  1359000.00   \n",
       "245    1600000.00         0.00    440000.00   3076666.66   550000.00   \n",
       "378    1477600.00   1586888.88     75000.00   3147200.00   466461.54   \n",
       "511    1563411.76   1563411.76   1563411.76   1563411.76  1563411.76   \n",
       "625     540000.00   1015000.00   1563411.76   1563411.76  1563411.76   \n",
       "650     400000.00         0.00    448666.66    406000.00   609200.00   \n",
       "810      60000.00   1359000.00   5450500.00    280000.00  2352551.72   \n",
       "816          0.00    440000.00   3076666.66    550000.00   247166.66   \n",
       "921     448666.66    406000.00    609200.00    528666.66   307000.00   \n",
       "1189   5450500.00    280000.00   2352551.72    160000.00  3513333.34   \n",
       "1283    247166.66    296444.44    834800.00    115636.36   540000.00   \n",
       "1382    814800.00   1878571.42    500000.00  12000000.00    60000.00   \n",
       "1383   3147200.00    466461.54   1600000.00         0.00   440000.00   \n",
       "1696   3076666.66    550000.00    247166.66    296444.44   834800.00   \n",
       "1757   1563411.76   1563411.76   1563411.76   1563411.76  1563411.76   \n",
       "2071     75000.00   3147200.00    466461.54   1600000.00        0.00   \n",
       "2366    310000.00         0.00   1477600.00   1586888.88    75000.00   \n",
       "2790    160000.00   3513333.34    310000.00         0.00  1477600.00   \n",
       "2971         0.00   1477600.00   1586888.88     75000.00  3147200.00   \n",
       "3176         0.00    448666.66    406000.00    609200.00   528666.66   \n",
       "3303    115636.36    540000.00   1015000.00   1563411.76  1563411.76   \n",
       "3418    609200.00    528666.66    307000.00    814800.00  1878571.42   \n",
       "3492   1586888.88     75000.00   3147200.00    466461.54  1600000.00   \n",
       "3511    440000.00   3076666.66    550000.00    247166.66   296444.44   \n",
       "3516   1359000.00   5450500.00    280000.00   2352551.72   160000.00   \n",
       "3697    500000.00  12000000.00     60000.00   1359000.00  5450500.00   \n",
       "3798   1015000.00   1563411.76   1563411.76   1563411.76  1563411.76   \n",
       "3809   1563411.76   1563411.76   1563411.76   1563411.76  1563411.76   \n",
       "3979   2352551.72    160000.00   3513333.34    310000.00        0.00   \n",
       "4095    834800.00    115636.36    540000.00   1015000.00  1563411.76   \n",
       "4209    550000.00    247166.66    296444.44    834800.00   115636.36   \n",
       "4384    528666.66    307000.00    814800.00   1878571.42   500000.00   \n",
       "4396    406000.00    609200.00    528666.66    307000.00   814800.00   \n",
       "4414   3513333.34    310000.00         0.00   1477600.00  1586888.88   \n",
       "4443  12000000.00     60000.00   1359000.00   5450500.00   280000.00   \n",
       "\n",
       "         ...       6619d81fc   1db387535   fc99f9426   91f701ba2   0572565c2  \\\n",
       "193      ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "235      ...      1015000.00  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "245      ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "378      ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "511      ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "625      ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "650      ...       440000.00  3076666.66   550000.00   247166.66   296444.44   \n",
       "810      ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "816      ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "921      ...       550000.00   247166.66   296444.44   834800.00   115636.36   \n",
       "1189     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "1283     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "1382     ...       540000.00  1015000.00  1563411.76  1563411.76  1563411.76   \n",
       "1383     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "1696     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "1757     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "2071     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "2366     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "2790     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "2971     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "3176     ...      3076666.66   550000.00   247166.66   296444.44   834800.00   \n",
       "3303     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "3418     ...       296444.44   834800.00   115636.36   540000.00  1015000.00   \n",
       "3492     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "3511     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "3516     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "3697     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "3798     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "3809     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "3979     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "4095     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "4209     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "4384     ...       834800.00   115636.36   540000.00  1015000.00  1563411.76   \n",
       "4396     ...       247166.66   296444.44   834800.00   115636.36   540000.00   \n",
       "4414     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "4443     ...      1563411.76  1563411.76  1563411.76  1563411.76  1563411.76   \n",
       "\n",
       "       190db8488   adb64ff71   c47340d97   c5a231d81   0ff32eb98  \n",
       "193   1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "235   1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "245   1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "378   1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "511   1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "625   1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "650    834800.00   115636.36   540000.00  1015000.00  1563411.76  \n",
       "810   1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "816   1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "921    540000.00  1015000.00  1563411.76  1563411.76  1563411.76  \n",
       "1189  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "1283  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "1382  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "1383  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "1696  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "1757  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "2071  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "2366  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "2790  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "2971  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "3176   115636.36   540000.00  1015000.00  1563411.76  1563411.76  \n",
       "3303  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "3418  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "3492  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "3511  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "3516  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "3697  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "3798  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "3809  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "3979  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "4095  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "4209  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "4384  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "4396  1015000.00  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "4414  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "4443  1563411.76  1563411.76  1563411.76  1563411.76  1563411.76  \n",
       "\n",
       "[36 rows x 42 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tmp = train.loc[rows, [\"ID\",\"target\"]+cols]\n",
    "print('original shape', tmp.shape)\n",
    "tmp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp = train.loc[rows, :]\n",
    "tmp = tmp.drop('ID', axis=1)\n",
    "# tmp.set_index('ID', inplace=True)\n",
    "# tmp = tmp.T\n",
    "# tmp.sort_values(rows)\n",
    "# tmp.to_csv('order_this.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array([64], dtype=int64),)"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "u, c = np.unique(tmp.iloc[0,:].values, return_counts=True)\n",
    "arg = np.argsort(c)\n",
    "np.where(u[arg] == 1563411.76)\n",
    "# u[arg].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "metadata": {},
   "outputs": [],
   "source": [
    "def element_wise_comparison(i1, i2, thresh=3):\n",
    "    i1 = np.array(i1)\n",
    "    i2 = np.array(i2)\n",
    "    dif = i1 - i2\n",
    "    count = np.count_nonzero(dif)\n",
    "    if count <= thresh:\n",
    "        return True\n",
    "    else:\n",
    "        return False\n",
    "        \n",
    "def find_filler_value(df, idx):\n",
    "    u, c = np.unique(df.iloc[0,:].values, return_counts=True)\n",
    "    arg = np.argsort(c)\n",
    "    return u[arg][-idx]\n",
    "\n",
    "def find_col_order_by_filler(df, filler, nb=40):\n",
    "    counts = []\n",
    "    for c in df.columns:\n",
    "        v = df[c].values - filler\n",
    "        counts.append(np.count_nonzero(v))\n",
    "    counts = np.array(counts)\n",
    "    args = np.argsort(counts)\n",
    "    stop = np.where(counts[args] == len(df.index))\n",
    "    return df.iloc[:, args[:np.squeeze(stop)[0]][::-1]]\n",
    "\n",
    "def find_leading_col(raw_df, sorted_df):\n",
    "    curr_lead = sorted_df.apply(tuple, axis=0).to_frame().rename(columns={0: 'key'}).iloc[0, 0][1:]\n",
    "    cols_to_match = raw_df.apply(tuple, axis=0).to_frame().rename(columns={0: 'key'})\n",
    "    for c in cols_to_match.index:\n",
    "        if element_wise_comparison(cols_to_match.loc[c, 'key'][:-1], curr_lead):\n",
    "            print('Leading col found: {}'.format(c))\n",
    "            return pd.concat([raw_df[c], sorted_df], axis=1)\n",
    "    print('No lead found')\n",
    "    \n",
    "def find_n_cols_groups(df, n_groups=40):\n",
    "    for i in range(n_groups):\n",
    "        filler = find_filler_value(tmp, i)\n",
    "        df_view = find_col_order_by_filler(tmp, filler)\n",
    "        df_view = find_leading_col(tmp, df_view)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 286,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Leading col found: 6619d81fc\n",
      "Leading col found: 703885424\n",
      "Leading col found: da5c36eee\n",
      "Leading col found: 47665e3ce\n",
      "Leading col found: 48df886f9\n",
      "Leading col found: 2cff4bf0c\n",
      "Leading col found: d36ded502\n",
      "Leading col found: a3e023f65\n",
      "Leading col found: ccc7609f4\n",
      "Leading col found: 48b839509\n",
      "Leading col found: 87ffda550\n",
      "Leading col found: 920a04ee2\n",
      "Leading col found: 476d95ef1\n",
      "Leading col found: 5030aed26\n",
      "Leading col found: 81de0d45e\n",
      "Leading col found: 1904ce2ac\n",
      "Leading col found: ced6a7e91\n",
      "Leading col found: 1af4d24fa\n",
      "Leading col found: 48df886f9\n",
      "Leading col found: 1d9078f84\n",
      "Leading col found: f190486d6\n",
      "Leading col found: f3cf9341c\n",
      "Leading col found: c928b4b74\n",
      "Leading col found: 51c141e64\n",
      "Leading col found: 86cefbcc0\n",
      "Leading col found: 4302b67ec\n",
      "Leading col found: 63be1f619\n",
      "Leading col found: 53aa182a2\n",
      "Leading col found: 9e4765450\n",
      "Leading col found: fb387ea33\n",
      "Leading col found: 9126049d8\n",
      "Leading col found: b6daeae32\n",
      "Leading col found: fec5644cf\n",
      "Leading col found: 0f8d7b98e\n",
      "Leading col found: a1cd7b681\n",
      "Leading col found: 5b465f819\n",
      "Leading col found: df838756c\n",
      "Leading col found: c13ee1dc9\n",
      "Leading col found: 8f6514df0\n",
      "Leading col found: e20edfcb8\n",
      "Leading col found: 266525925\n",
      "Leading col found: 0d7692145\n",
      "Leading col found: 2d60e2f7a\n",
      "Leading col found: 7f72c937f\n",
      "Leading col found: 48df886f9\n",
      "Leading col found: 91de54e0a\n",
      "Leading col found: 86cefbcc0\n",
      "Leading col found: 48df886f9\n",
      "Leading col found: 48df886f9\n",
      "Leading col found: 4dcf81d65\n",
      "Leading col found: 1e6306c7c\n",
      "Leading col found: d50798d34\n",
      "Leading col found: b77bc4dac\n",
      "Leading col found: 6df033973\n",
      "Leading col found: fec5eaf1a\n",
      "Leading col found: 09184c121\n",
      "Leading col found: 0256b6714\n",
      "Leading col found: 3f02111a8\n",
      "Leading col found: 85dcc913d\n",
      "Leading col found: 207871d87\n",
      "Leading col found: 8e8736fc8\n",
      "Leading col found: 7ab926448\n",
      "Leading col found: e8b599c85\n",
      "Leading col found: 15ace8c9f\n",
      "Leading col found: 62071f7bc\n",
      "Leading col found: 66146c12d\n",
      "Leading col found: 547d3135b\n",
      "Leading col found: caa9883f6\n",
      "Leading col found: 90b0ed912\n",
      "Leading col found: 58ed8fb53\n",
      "Leading col found: fe33df1c4\n",
      "Leading col found: dd84964c8\n",
      "Leading col found: 8b5c0fb4e\n",
      "Leading col found: 9fe78f046\n",
      "Leading col found: d19110e37\n",
      "Leading col found: f49ff3269\n",
      "Leading col found: 48420ad48\n",
      "Leading col found: 600ea672f\n",
      "No lead found\n",
      "Leading col found: 3d655b0ed\n",
      "Leading col found: 8367dfc36\n",
      "Leading col found: cc93bdf83\n",
      "Leading col found: 74507e97f\n",
      "Leading col found: dbfa2b77f\n",
      "Leading col found: 6619d81fc\n"
     ]
    },
    {
     "ename": "IndexError",
     "evalue": "index -85 is out of bounds for axis 0 with size 84",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mIndexError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-286-4e1f2cd25406>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[1;31m# df_view = find_col_order_by_filler(tmp, filler)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;31m# find_leading_col(tmp, df_view)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 4\u001b[1;33m \u001b[0mfind_n_cols_groups\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtmp\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;36m100\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m<ipython-input-284-49f37b7c2f0f>\u001b[0m in \u001b[0;36mfind_n_cols_groups\u001b[1;34m(df, n_groups)\u001b[0m\n\u001b[0;32m     35\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0mfind_n_cols_groups\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mn_groups\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m40\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     36\u001b[0m     \u001b[1;32mfor\u001b[0m \u001b[0mi\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mrange\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mn_groups\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 37\u001b[1;33m         \u001b[0mfiller\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfind_filler_value\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtmp\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mi\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     38\u001b[0m         \u001b[0mdf_view\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfind_col_order_by_filler\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtmp\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfiller\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     39\u001b[0m         \u001b[0mdf_view\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfind_leading_col\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtmp\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdf_view\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<ipython-input-284-49f37b7c2f0f>\u001b[0m in \u001b[0;36mfind_filler_value\u001b[1;34m(df, idx)\u001b[0m\n\u001b[0;32m     12\u001b[0m     \u001b[0mu\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mc\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munique\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0miloc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mreturn_counts\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     13\u001b[0m     \u001b[0marg\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0margsort\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mc\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 14\u001b[1;33m     \u001b[1;32mreturn\u001b[0m \u001b[0mu\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m-\u001b[0m\u001b[0midx\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     15\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     16\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0mfind_col_order_by_filler\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfiller\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mnb\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m40\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mIndexError\u001b[0m: index -85 is out of bounds for axis 0 with size 84"
     ]
    }
   ],
   "source": [
    "# filler = find_filler_value(tmp, 20)\n",
    "# df_view = find_col_order_by_filler(tmp, filler)\n",
    "# find_leading_col(tmp, df_view)\n",
    "find_n_cols_groups(tmp, 100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "bc3c29d7b03a6a8088edcbe1c0572be5fd56ba56"
   },
   "source": [
    "### Search in a brute-force way"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "808f00527f9534a959d8f9fdee8a4f32f882ea18",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def bf_search(df_new, df_cand):\n",
    "    cnt = 0\n",
    "    head_curr = df_new.values[1:, 0]\n",
    "    tail_curr = df_new.values[:-1, -1]\n",
    "    while True:\n",
    "        for c in df_cand.columns:\n",
    "            if c in df_new:\n",
    "                continue\n",
    "            elif np.all(\n",
    "                df_cand[c].iloc[:-1].values==head_curr\n",
    "            ) and len(df_cand[c].unique())>1:\n",
    "                df_new.insert(0, c, df_cand[c].values)\n",
    "                head_curr = df_new.values[1:, 0]\n",
    "                print(c, 'found head!', 'new shape', df_new.shape)\n",
    "                cnt += 1\n",
    "                break\n",
    "            elif np.all(\n",
    "                df_cand[c].iloc[1:].values==tail_curr\n",
    "            ) and len(df_cand[c].unique())>1:\n",
    "                df_new[c] = df_cand[c].values\n",
    "                tail_curr = df_new.values[:-1, -1]\n",
    "                print(c, 'found tail!', 'new shape', df_new.shape)\n",
    "                cnt += 1\n",
    "                break\n",
    "            else:\n",
    "                continue\n",
    "        if cnt==0:\n",
    "            break\n",
    "        else:\n",
    "            cnt = 0\n",
    "            continue\n",
    "    return df_new\n",
    "\n",
    "\n",
    "def row_bf_search(df_new):\n",
    "    df_new = df_new.T.copy()\n",
    "    df_cand_row = train[df_new.index].T.copy()\n",
    "    print('Row searching ...')\n",
    "    df_new = bf_search(df_new, df_cand_row)\n",
    "    df_new = df_new.T.copy()\n",
    "    return df_new\n",
    "def column_bf_search(df_new):\n",
    "    df_cand_col = train.loc[df_new.index, :]\n",
    "    df_cand_col = df_cand_col.iloc[:, 2:]\n",
    "    print('Column searching ...')\n",
    "    df_new = bf_search(df_new, df_cand_col)\n",
    "    return df_new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_new = column_bf_search(df_new)\n",
    "df_new = row_bf_search(df_new)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_new"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "c3606fe7f58cb9e7563630dcc051bf7f6fde883f"
   },
   "source": [
    "## There's a strange long tail of number ***1563411.76*** pointed out by [S D](https://www.kaggle.com/johnfarrell/giba-s-property-extended-result/comments#358945)\n",
    "## Thanks [S D](https://www.kaggle.com/sdoria) for this new idea!\n",
    "## Let's check it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "7f7aff2a9f91e1afa813a7932f8b622a5465a867",
    "collapsed": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "for i, c in enumerate(df_new.columns):\n",
    "    print(\n",
    "        'No.', i, 'Column Name', c, \n",
    "        'subset count',\n",
    "        (df_new[c].values==1563411.76).sum(), \n",
    "        'train count',\n",
    "        (train[c].values==1563411.76).sum()\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "30efb919a57d62ef9ba4388575c2bfcf8b9e73d9",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "res_cnt = dict((c, (train[c].values==1563411.76).sum()) for c in train.columns[2:])\n",
    "res_cnt = pd.DataFrame.from_dict(res_cnt, orient='index', columns=['strange_number_cnt'])\n",
    "res_cnt = res_cnt.sort_values('strange_number_cnt', 0, False)\n",
    "res_cnt.head(50).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "8f411ac7f4de747423c20fb86f057b18ca5ec8f6",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "res_cnt.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "744f2c1a6127f88fb4d126cad178d532d20ad8aa"
   },
   "source": [
    "## Sad... there are *2* columns with the same count 30!\n",
    "- 91f701ba2\n",
    "- fc99f9426\n",
    "\n",
    "## What about the row-wise?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "fa7ceab104ade0417d088051886d92d8065e16be",
    "collapsed": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "for i, c in enumerate(df_new.T.columns):\n",
    "    print(\n",
    "        'No.', i, 'Row Name', c, \n",
    "        'subset count',\n",
    "        (df_new.T[c].values==1563411.76).sum(), \n",
    "        'train count',\n",
    "        (train.T[c].values==1563411.76).sum()\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "2cf61852c9cecad5662dc73b98070594780e23a4",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp = train.iloc[:, 2:].values\n",
    "res_t_cnt = dict((idx, (tmp[i, :]==1563411.76).sum()) for i,idx in enumerate(train.index))\n",
    "res_t_cnt = pd.DataFrame.from_dict(res_t_cnt, orient='index', columns=['strange_number_cnt'])\n",
    "res_t_cnt = res_t_cnt.sort_values('strange_number_cnt', 0, False)\n",
    "res_t_cnt.head(50).T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "c643f93aeaef03ec37ed1d064e2f20ea94c24c33"
   },
   "source": [
    "## Hmm, seems no same values. OK, we start from rows!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "3f3a91a7083bb2391f09f1ea862ba162fc8e8fb0",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "head_row_indexes = res_t_cnt[res_t_cnt['strange_number_cnt']>24].index.tolist()\n",
    "head_row_indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "ef08ad91c449ee6c191e264c1705cda6f26f49ed",
    "collapsed": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "mask = res_t_cnt['strange_number_cnt']>0 \n",
    "mask&=res_t_cnt['strange_number_cnt']<8\n",
    "tail_row_indexes = res_t_cnt.loc[mask].index.tolist()\n",
    "tail_row_indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "51eb81819211349641b13320ae09c6863d09e292",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pd.concat([\n",
    "    train.loc[head_row_indexes, ['target']+df_new.columns.tolist()], \n",
    "    train.loc[df_new.index, ['target']+df_new.columns.tolist()],\n",
    "    train.loc[tail_row_indexes, ['target']+df_new.columns.tolist()], \n",
    "])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "67b2c9fe4c935700ccf36f285de1258ac8caf847"
   },
   "source": [
    "## Good! This is our new df_new and we got to search once more!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "2e82261c8f8d74f4acd2b36d2309b2fc908451b0",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_new = pd.concat([\n",
    "    train.loc[head_row_indexes, df_new.columns.tolist()], \n",
    "    train.loc[df_new.index, df_new.columns.tolist()],\n",
    "    train.loc[tail_row_indexes, df_new.columns.tolist()], \n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "838577c1b0d5fffdcfdeb49fa932b19567519cdc",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_new = column_bf_search(df_new)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "050ab916e42970b22452d27cc962d45addeed080",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_new = row_bf_search(df_new)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "e5617bd5b16d0761f45e32a53dbac2f3717cf356"
   },
   "source": [
    "## No good... But wait! Let's check it!\n",
    "### Here, we still can't identify the true order of the columns with the same count 30\n",
    "### Just for example take a look at 'fc99f9426','91f701ba2' at first\n",
    "### We add 'fc99f9426','91f701ba2' and the first 6 columns with count 31~36"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "6888e6d0946d3fa4d9871353c073483589b07342",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "res_cnt[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "fa2ccddedeca94c5a10fbc7f6f3ef8b155567aca",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train.loc[df_new.index, ['target']+df_new.columns.tolist()+['fc99f9426','91f701ba2'] + res_cnt.index.values[:6].tolist()[::-1]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "75127d72c71d0a3f20b2279e61cede9469a0f457"
   },
   "source": [
    "## Hmm, seems something is wrong\n",
    "### at column: ['1db387535', 'fc99f9426','91f701ba2']\n",
    "### from ~last 10 rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "e2622d3c3ad8ce22bba526c9a5aa401f7a5abfec",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train.loc[df_new.index[-10:], df_new.columns.tolist()[-2:]+['fc99f9426', '91f701ba2'] + res_cnt.index.values[:6].tolist()[::-1]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "aac2c7911b8b7e57ece668372e9479baa5ea83d6"
   },
   "source": [
    "### Check the table above\n",
    "### Seems 540000.00 disappeared from column '1db387535'\n",
    "###      And 1015000.00\t disappeared from column 'fc99f9426'\n",
    "### Thus the time series is becoming not *serious* from here...\n",
    "### Another question, what about the order of 'fc99f9426','91f701ba2'? Which's first?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "d1f60abbba782a3b1fbb3e788b1e0dea6e663091",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train.loc[df_new.index[:8], ['f190486d6', '58e2e02e6']].T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "7c5f1fcacdf35d7d1a67ced3a26cd68eb53b1b50",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train.loc[df_new.index[-2:], df_new.columns.tolist()[-2:]+['fc99f9426', '91f701ba2']+res_cnt.index.values[:6].tolist()[::-1]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "512356dfa6ebadefd8423122c381a89f24c6e867"
   },
   "source": [
    "### From row values (in reverse order), it seems 'fc99f9426', '91f701ba2' is consistent with other columns \n",
    "### Thus, in our limited analysis, the new df_new is"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "3c47549cdb10d1d5108050fa4d6b7d517fa62975",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_new_new = train.loc[df_new.index, df_new.columns.tolist()+['fc99f9426','91f701ba2'] + res_cnt.index.values[:6].tolist()[::-1]]\n",
    "df_new_new.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "7223184371f292a47e9de95d91b8625dfc5ba708",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print(f'Row indexes({df_new_new.shape[0]})\\n', df_new_new.index.values.tolist())\n",
    "print(f'Column indexes({df_new_new.shape[1]})\\n', df_new_new.columns.values.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "f6713de5e1713e0155d17a3cf3803866d390dec4",
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train.loc[df_new_new.index, ['ID', 'target']+df_new_new.columns.tolist()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "_uuid": "84a6c5e1e4d6d1b8d73b531c25b46abf9acbc31f"
   },
   "source": [
    "### For the final result consist many uncertains and hypothesis\n",
    "### Be careful of using this !!!\n",
    "### Any comments and advices are welcome !!!\n",
    "### Please point out if there's something wrong!!!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "2c37adc49983d313dcc5612e7e8eede25a8f3d0c",
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "459129a819e4e7ddc0354ed7b4116b8dd65d4425",
    "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}