Working with Large Files with Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Science Tools\n",
    "#### Working with Large Files\n",
    "+ Pandas\n",
    "+ Modin\n",
    "+ Dask\n",
    "+ Vaex\n",
    "+ Other Methods\n",
    "\n",
    "##### Installation\n",
    "+ pip install pandas\n",
    "+ pip install dask\n",
    "+ pip install \"dask[complete]\"\n",
    "+ pip install vaex\n",
    "+ pip install modin"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 1\n",
    "+ Split and read\n",
    "+ csvkit\n",
    "+ Unix split\n",
    "+ CSV splitter\n",
    "+ npm i csv-split\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Using Terminal\n",
    "- Split by 1000 lines\n",
    "    + split -l 1000 bigdata.csv\n",
    "    + for i in *; do mv \"$i\" \"$i.csv\"; done\n",
    "- Split by Bytes\n",
    "    + split -b 50K bigdata.csv\n",
    "- Split by Numeric/Suffix/Digit\n",
    "    + split -d bigdata.csv\n",
    "- Split and use custom name\n",
    "    + split -l 1000 bigdata.csv mysmalldata_\n",
    "- Split with N chunks\n",
    "    + split -n5 bigdata.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 2 No Pkgs\n",
    "+ Using Readlines\n",
    "+ To Have an overview of your file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Exact number of rows: 938640\n",
      "CPU times: user 715 ms, sys: 390 ms, total: 1.1 s\n",
      "Wall time: 2.02 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "with open('big_dataset.csv') as file:\n",
    "    n_rows = len(file.readlines())\n",
    "\n",
    "print ('Exact number of rows: {}'.format(n_rows))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Now we know how many rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 2\n",
    "+ Read and work with some rows or columns\n",
    "+ nrows\n",
    "+ usecols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load Pandas\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Attempt to Read Big Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 5.24 s, sys: 479 ms, total: 5.72 s\n",
      "Wall time: 7.93 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df = pd.read_csv(\"big_dataset.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>cert</th>\n",
       "      <th>NameOfBank</th>\n",
       "      <th>city</th>\n",
       "      <th>State</th>\n",
       "      <th>zip</th>\n",
       "      <th>county</th>\n",
       "      <th>cbsa_metro</th>\n",
       "      <th>MetroArea</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>NetLoansAndLeasesToDeposits</th>\n",
       "      <th>TotalDepositAccountsWithLessThan100k</th>\n",
       "      <th>TotalDepositAccountsWithMoreThan100k</th>\n",
       "      <th>DepositsAccountsLessThan250k</th>\n",
       "      <th>DepositAccountsWithMoreThan250k</th>\n",
       "      <th>TotalDeposits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15426</td>\n",
       "      <td>Almena State Bank</td>\n",
       "      <td>Almena</td>\n",
       "      <td>KS</td>\n",
       "      <td>67622</td>\n",
       "      <td>Norton</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>97</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>51131</td>\n",
       "      <td>19923</td>\n",
       "      <td>71939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>57144</td>\n",
       "      <td>American State Bank &amp; Trust Company</td>\n",
       "      <td>Great Bend</td>\n",
       "      <td>KS</td>\n",
       "      <td>67530</td>\n",
       "      <td>Barton</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>155513</td>\n",
       "      <td>121751</td>\n",
       "      <td>284517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17636</td>\n",
       "      <td>Astra Bank</td>\n",
       "      <td>Scandia</td>\n",
       "      <td>KS</td>\n",
       "      <td>66966</td>\n",
       "      <td>Republic</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>81</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>142692</td>\n",
       "      <td>64803</td>\n",
       "      <td>214791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17769</td>\n",
       "      <td>Bank of Hays</td>\n",
       "      <td>Hays</td>\n",
       "      <td>KS</td>\n",
       "      <td>67601</td>\n",
       "      <td>Ellis</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>84</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>116204</td>\n",
       "      <td>79910</td>\n",
       "      <td>202174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13710</td>\n",
       "      <td>Bank of Palmer</td>\n",
       "      <td>Palmer</td>\n",
       "      <td>KS</td>\n",
       "      <td>66962</td>\n",
       "      <td>Washington</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>62</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25798</td>\n",
       "      <td>7819</td>\n",
       "      <td>37886</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cert                           NameOfBank        city State    zip  \\\n",
       "0  15426                    Almena State Bank      Almena    KS  67622   \n",
       "1  57144  American State Bank & Trust Company  Great Bend    KS  67530   \n",
       "2  17636                           Astra Bank     Scandia    KS  66966   \n",
       "3  17769                         Bank of Hays        Hays    KS  67601   \n",
       "4  13710                       Bank of Palmer      Palmer    KS  66962   \n",
       "\n",
       "       county  cbsa_metro MetroArea     Quarter  NetLoansAndLeasesToDeposits  \\\n",
       "0      Norton           0       NaN  2016-09-30                           97   \n",
       "1      Barton           0       NaN  2016-09-30                           89   \n",
       "2    Republic           0       NaN  2016-09-30                           81   \n",
       "3       Ellis           0       NaN  2016-09-30                           84   \n",
       "4  Washington           0       NaN  2016-09-30                           62   \n",
       "\n",
       "   TotalDepositAccountsWithLessThan100k  TotalDepositAccountsWithMoreThan100k  \\\n",
       "0                                     0                                     0   \n",
       "1                                     0                                     0   \n",
       "2                                     0                                     0   \n",
       "3                                     0                                     0   \n",
       "4                                     0                                     0   \n",
       "\n",
       "   DepositsAccountsLessThan250k  DepositAccountsWithMoreThan250k  \\\n",
       "0                         51131                            19923   \n",
       "1                        155513                           121751   \n",
       "2                        142692                            64803   \n",
       "3                        116204                            79910   \n",
       "4                         25798                             7819   \n",
       "\n",
       "   TotalDeposits  \n",
       "0          71939  \n",
       "1         284517  \n",
       "2         214791  \n",
       "3         202174  \n",
       "4          37886  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reading the First 1000 rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 29.4 ms, sys: 0 ns, total: 29.4 ms\n",
      "Wall time: 88.5 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df1 = pd.read_csv(\"big_dataset.csv\",nrows=1000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Narrative\n",
    "+ Takes less time as compared to reading the entire file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>cert</th>\n",
       "      <th>NameOfBank</th>\n",
       "      <th>city</th>\n",
       "      <th>State</th>\n",
       "      <th>zip</th>\n",
       "      <th>county</th>\n",
       "      <th>cbsa_metro</th>\n",
       "      <th>MetroArea</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>NetLoansAndLeasesToDeposits</th>\n",
       "      <th>TotalDepositAccountsWithLessThan100k</th>\n",
       "      <th>TotalDepositAccountsWithMoreThan100k</th>\n",
       "      <th>DepositsAccountsLessThan250k</th>\n",
       "      <th>DepositAccountsWithMoreThan250k</th>\n",
       "      <th>TotalDeposits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15426</td>\n",
       "      <td>Almena State Bank</td>\n",
       "      <td>Almena</td>\n",
       "      <td>KS</td>\n",
       "      <td>67622</td>\n",
       "      <td>Norton</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>97</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>51131</td>\n",
       "      <td>19923</td>\n",
       "      <td>71939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>57144</td>\n",
       "      <td>American State Bank &amp; Trust Company</td>\n",
       "      <td>Great Bend</td>\n",
       "      <td>KS</td>\n",
       "      <td>67530</td>\n",
       "      <td>Barton</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>155513</td>\n",
       "      <td>121751</td>\n",
       "      <td>284517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17636</td>\n",
       "      <td>Astra Bank</td>\n",
       "      <td>Scandia</td>\n",
       "      <td>KS</td>\n",
       "      <td>66966</td>\n",
       "      <td>Republic</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>81</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>142692</td>\n",
       "      <td>64803</td>\n",
       "      <td>214791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17769</td>\n",
       "      <td>Bank of Hays</td>\n",
       "      <td>Hays</td>\n",
       "      <td>KS</td>\n",
       "      <td>67601</td>\n",
       "      <td>Ellis</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>84</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>116204</td>\n",
       "      <td>79910</td>\n",
       "      <td>202174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13710</td>\n",
       "      <td>Bank of Palmer</td>\n",
       "      <td>Palmer</td>\n",
       "      <td>KS</td>\n",
       "      <td>66962</td>\n",
       "      <td>Washington</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>62</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25798</td>\n",
       "      <td>7819</td>\n",
       "      <td>37886</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cert                           NameOfBank        city State    zip  \\\n",
       "0  15426                    Almena State Bank      Almena    KS  67622   \n",
       "1  57144  American State Bank & Trust Company  Great Bend    KS  67530   \n",
       "2  17636                           Astra Bank     Scandia    KS  66966   \n",
       "3  17769                         Bank of Hays        Hays    KS  67601   \n",
       "4  13710                       Bank of Palmer      Palmer    KS  66962   \n",
       "\n",
       "       county  cbsa_metro  MetroArea     Quarter  NetLoansAndLeasesToDeposits  \\\n",
       "0      Norton           0        NaN  2016-09-30                           97   \n",
       "1      Barton           0        NaN  2016-09-30                           89   \n",
       "2    Republic           0        NaN  2016-09-30                           81   \n",
       "3       Ellis           0        NaN  2016-09-30                           84   \n",
       "4  Washington           0        NaN  2016-09-30                           62   \n",
       "\n",
       "   TotalDepositAccountsWithLessThan100k  TotalDepositAccountsWithMoreThan100k  \\\n",
       "0                                     0                                     0   \n",
       "1                                     0                                     0   \n",
       "2                                     0                                     0   \n",
       "3                                     0                                     0   \n",
       "4                                     0                                     0   \n",
       "\n",
       "   DepositsAccountsLessThan250k  DepositAccountsWithMoreThan250k  \\\n",
       "0                         51131                            19923   \n",
       "1                        155513                           121751   \n",
       "2                        142692                            64803   \n",
       "3                        116204                            79910   \n",
       "4                         25798                             7819   \n",
       "\n",
       "   TotalDeposits  \n",
       "0          71939  \n",
       "1         284517  \n",
       "2         214791  \n",
       "3         202174  \n",
       "4          37886  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1000, 15)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Num of Rows and Columns\n",
    "df1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 15 columns):\n",
      "cert                                    1000 non-null int64\n",
      "NameOfBank                              1000 non-null object\n",
      "city                                    1000 non-null object\n",
      "State                                   1000 non-null object\n",
      "zip                                     1000 non-null int64\n",
      "county                                  1000 non-null object\n",
      "cbsa_metro                              1000 non-null int64\n",
      "MetroArea                               0 non-null float64\n",
      "Quarter                                 1000 non-null object\n",
      "NetLoansAndLeasesToDeposits             1000 non-null int64\n",
      "TotalDepositAccountsWithLessThan100k    1000 non-null int64\n",
      "TotalDepositAccountsWithMoreThan100k    1000 non-null int64\n",
      "DepositsAccountsLessThan250k            1000 non-null int64\n",
      "DepositAccountsWithMoreThan250k         1000 non-null int64\n",
      "TotalDeposits                           1000 non-null int64\n",
      "dtypes: float64(1), int64(9), object(5)\n",
      "memory usage: 406.1 KB\n"
     ]
    }
   ],
   "source": [
    "# Check Memory Usage\n",
    "df1.info(memory_usage=\"deep\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 15 columns):\n",
      "cert                                    1000 non-null int64\n",
      "NameOfBank                              1000 non-null object\n",
      "city                                    1000 non-null object\n",
      "State                                   1000 non-null object\n",
      "zip                                     1000 non-null int64\n",
      "county                                  1000 non-null object\n",
      "cbsa_metro                              1000 non-null int64\n",
      "MetroArea                               0 non-null float64\n",
      "Quarter                                 1000 non-null object\n",
      "NetLoansAndLeasesToDeposits             1000 non-null int64\n",
      "TotalDepositAccountsWithLessThan100k    1000 non-null int64\n",
      "TotalDepositAccountsWithMoreThan100k    1000 non-null int64\n",
      "DepositsAccountsLessThan250k            1000 non-null int64\n",
      "DepositAccountsWithMoreThan250k         1000 non-null int64\n",
      "TotalDeposits                           1000 non-null int64\n",
      "dtypes: float64(1), int64(9), object(5)\n",
      "memory usage: 117.3+ KB\n"
     ]
    }
   ],
   "source": [
    "# Check Memory Usage\n",
    "df1.info(memory_usage=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reading Selected Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = ['cert', 'NameOfBank', 'city', 'State', 'zip', 'county']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3.87 s, sys: 378 ms, total: 4.24 s\n",
      "Wall time: 6.88 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df2 = pd.read_csv(\"big_dataset.csv\",usecols=columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>cert</th>\n",
       "      <th>NameOfBank</th>\n",
       "      <th>city</th>\n",
       "      <th>State</th>\n",
       "      <th>zip</th>\n",
       "      <th>county</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15426</td>\n",
       "      <td>Almena State Bank</td>\n",
       "      <td>Almena</td>\n",
       "      <td>KS</td>\n",
       "      <td>67622</td>\n",
       "      <td>Norton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>57144</td>\n",
       "      <td>American State Bank &amp; Trust Company</td>\n",
       "      <td>Great Bend</td>\n",
       "      <td>KS</td>\n",
       "      <td>67530</td>\n",
       "      <td>Barton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17636</td>\n",
       "      <td>Astra Bank</td>\n",
       "      <td>Scandia</td>\n",
       "      <td>KS</td>\n",
       "      <td>66966</td>\n",
       "      <td>Republic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17769</td>\n",
       "      <td>Bank of Hays</td>\n",
       "      <td>Hays</td>\n",
       "      <td>KS</td>\n",
       "      <td>67601</td>\n",
       "      <td>Ellis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13710</td>\n",
       "      <td>Bank of Palmer</td>\n",
       "      <td>Palmer</td>\n",
       "      <td>KS</td>\n",
       "      <td>66962</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cert                           NameOfBank        city State    zip  \\\n",
       "0  15426                    Almena State Bank      Almena    KS  67622   \n",
       "1  57144  American State Bank & Trust Company  Great Bend    KS  67530   \n",
       "2  17636                           Astra Bank     Scandia    KS  66966   \n",
       "3  17769                         Bank of Hays        Hays    KS  67601   \n",
       "4  13710                       Bank of Palmer      Palmer    KS  66962   \n",
       "\n",
       "       county  \n",
       "0      Norton  \n",
       "1      Barton  \n",
       "2    Republic  \n",
       "3       Ellis  \n",
       "4  Washington  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 3\n",
    "+ Read it in chunks with pandas\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for chunk in pd.read_csv('../input/train.csv', chunksize=chunksize, dtype=dtypes):\n",
    "    filtered = (chunk[(np.where(chunk['is_attributed']==1, True, False))])\n",
    "    df_converted = pd.concat([df_converted, filtered], ignore_index=True, )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 5min 47s, sys: 21.4 s, total: 6min 9s\n",
      "Wall time: 9min 21s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "chunksize = 10 ** 3\n",
    "df_largest_banks = pd.DataFrame()\n",
    "for chunk in pd.read_csv('big_dataset.csv', chunksize=chunksize):\n",
    "    largest_banks = chunk[chunk['TotalDeposits'] > 100000]\n",
    "    df_largest_banks = pd.concat([df_largest_banks,largest_banks])\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>cert</th>\n",
       "      <th>NameOfBank</th>\n",
       "      <th>city</th>\n",
       "      <th>State</th>\n",
       "      <th>zip</th>\n",
       "      <th>county</th>\n",
       "      <th>cbsa_metro</th>\n",
       "      <th>MetroArea</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>NetLoansAndLeasesToDeposits</th>\n",
       "      <th>TotalDepositAccountsWithLessThan100k</th>\n",
       "      <th>TotalDepositAccountsWithMoreThan100k</th>\n",
       "      <th>DepositsAccountsLessThan250k</th>\n",
       "      <th>DepositAccountsWithMoreThan250k</th>\n",
       "      <th>TotalDeposits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>57144</td>\n",
       "      <td>American State Bank &amp; Trust Company</td>\n",
       "      <td>Great Bend</td>\n",
       "      <td>KS</td>\n",
       "      <td>67530</td>\n",
       "      <td>Barton</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>155513</td>\n",
       "      <td>121751</td>\n",
       "      <td>284517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17636</td>\n",
       "      <td>Astra Bank</td>\n",
       "      <td>Scandia</td>\n",
       "      <td>KS</td>\n",
       "      <td>66966</td>\n",
       "      <td>Republic</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>81</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>142692</td>\n",
       "      <td>64803</td>\n",
       "      <td>214791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17769</td>\n",
       "      <td>Bank of Hays</td>\n",
       "      <td>Hays</td>\n",
       "      <td>KS</td>\n",
       "      <td>67601</td>\n",
       "      <td>Ellis</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>84</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>116204</td>\n",
       "      <td>79910</td>\n",
       "      <td>202174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>16436</td>\n",
       "      <td>Centera Bank</td>\n",
       "      <td>Sublette</td>\n",
       "      <td>KS</td>\n",
       "      <td>67877</td>\n",
       "      <td>Haskell</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>61</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>134368</td>\n",
       "      <td>91171</td>\n",
       "      <td>233288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4702</td>\n",
       "      <td>Central National Bank</td>\n",
       "      <td>Junction City</td>\n",
       "      <td>KS</td>\n",
       "      <td>66441</td>\n",
       "      <td>Geary</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>57</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>488747</td>\n",
       "      <td>268501</td>\n",
       "      <td>784018</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cert                           NameOfBank           city State    zip  \\\n",
       "1  57144  American State Bank & Trust Company     Great Bend    KS  67530   \n",
       "2  17636                           Astra Bank        Scandia    KS  66966   \n",
       "3  17769                         Bank of Hays           Hays    KS  67601   \n",
       "8  16436                         Centera Bank       Sublette    KS  67877   \n",
       "9   4702                Central National Bank  Junction City    KS  66441   \n",
       "\n",
       "     county  cbsa_metro MetroArea     Quarter  NetLoansAndLeasesToDeposits  \\\n",
       "1    Barton           0       NaN  2016-09-30                           89   \n",
       "2  Republic           0       NaN  2016-09-30                           81   \n",
       "3     Ellis           0       NaN  2016-09-30                           84   \n",
       "8   Haskell           0       NaN  2016-09-30                           61   \n",
       "9     Geary           0       NaN  2016-09-30                           57   \n",
       "\n",
       "   TotalDepositAccountsWithLessThan100k  TotalDepositAccountsWithMoreThan100k  \\\n",
       "1                                     0                                     0   \n",
       "2                                     0                                     0   \n",
       "3                                     0                                     0   \n",
       "8                                     0                                     0   \n",
       "9                                     0                                     0   \n",
       "\n",
       "   DepositsAccountsLessThan250k  DepositAccountsWithMoreThan250k  \\\n",
       "1                        155513                           121751   \n",
       "2                        142692                            64803   \n",
       "3                        116204                            79910   \n",
       "8                        134368                            91171   \n",
       "9                        488747                           268501   \n",
       "\n",
       "   TotalDeposits  \n",
       "1         284517  \n",
       "2         214791  \n",
       "3         202174  \n",
       "8         233288  \n",
       "9         784018  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_largest_banks.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Narrative\n",
    "+ It works but it takes time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 4\n",
    "+ Using Modin\n",
    "+ Pandas on Ray"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Process STDOUT and STDERR is being redirected to /tmp/ray/session_2019-05-10_15-15-11_5859/logs.\n",
      "Waiting for redis server at 127.0.0.1:29930 to respond...\n",
      "Waiting for redis server at 127.0.0.1:23919 to respond...\n",
      "Starting Redis shard with 10.0 GB max memory.\n",
      "WARNING: The object store is using /tmp instead of /dev/shm because /dev/shm has only 1846906880 bytes available. This may slow down performance! You may be able to free up space by deleting files in /dev/shm or terminating any running plasma_store_server processes. If you are inside a Docker container, you may need to pass an argument with the flag '--shm-size' to 'docker run'.\n",
      "Starting the Plasma object store with 2.0 GB memory using /tmp.\n"
     ]
    }
   ],
   "source": [
    "# Load Pkg\n",
    "import modin.pandas as mpd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 64 ms, sys: 13 ms, total: 77 ms\n",
      "Wall time: 12.4 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_modin = mpd.read_csv(\"big_dataset2.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the entire dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['T',\n",
       " '__abs__',\n",
       " '__add__',\n",
       " '__and__',\n",
       " '__array__',\n",
       " '__array_wrap__',\n",
       " '__bool__',\n",
       " '__class__',\n",
       " '__contains__',\n",
       " '__copy__',\n",
       " '__deepcopy__',\n",
       " '__delattr__',\n",
       " '__delitem__',\n",
       " '__dict__',\n",
       " '__dir__',\n",
       " '__div__',\n",
       " '__doc__',\n",
       " '__eq__',\n",
       " '__finalize__',\n",
       " '__floordiv__',\n",
       " '__format__',\n",
       " '__ge__',\n",
       " '__getattr__',\n",
       " '__getattribute__',\n",
       " '__getitem__',\n",
       " '__getstate__',\n",
       " '__gt__',\n",
       " '__hash__',\n",
       " '__iadd__',\n",
       " '__ifloordiv__',\n",
       " '__imod__',\n",
       " '__imul__',\n",
       " '__init__',\n",
       " '__init_subclass__',\n",
       " '__invert__',\n",
       " '__ipow__',\n",
       " '__isub__',\n",
       " '__iter__',\n",
       " '__itruediv__',\n",
       " '__le__',\n",
       " '__len__',\n",
       " '__lt__',\n",
       " '__mod__',\n",
       " '__module__',\n",
       " '__mul__',\n",
       " '__ne__',\n",
       " '__neg__',\n",
       " '__new__',\n",
       " '__nonzero__',\n",
       " '__or__',\n",
       " '__pow__',\n",
       " '__radd__',\n",
       " '__rdiv__',\n",
       " '__reduce__',\n",
       " '__reduce_ex__',\n",
       " '__repr__',\n",
       " '__rfloordiv__',\n",
       " '__rmod__',\n",
       " '__rmul__',\n",
       " '__round__',\n",
       " '__rpow__',\n",
       " '__rsub__',\n",
       " '__rtruediv__',\n",
       " '__setattr__',\n",
       " '__setitem__',\n",
       " '__setstate__',\n",
       " '__sizeof__',\n",
       " '__str__',\n",
       " '__sub__',\n",
       " '__subclasshook__',\n",
       " '__truediv__',\n",
       " '__unicode__',\n",
       " '__weakref__',\n",
       " '__xor__',\n",
       " '_aggregate',\n",
       " '_build_repr_df',\n",
       " '_create_dataframe_from_compiler',\n",
       " '_default_to_pandas',\n",
       " '_get_columns',\n",
       " '_get_index',\n",
       " '_getitem_array',\n",
       " '_getitem_column',\n",
       " '_getitem_slice',\n",
       " '_query_compiler',\n",
       " '_repr_html_',\n",
       " '_set_axis_name',\n",
       " '_set_columns',\n",
       " '_set_index',\n",
       " '_string_function',\n",
       " '_update_inplace',\n",
       " '_validate_dtypes',\n",
       " '_validate_dtypes_min_max',\n",
       " '_validate_dtypes_sum_prod_mean',\n",
       " '_validate_eval_query',\n",
       " '_validate_other',\n",
       " 'abs',\n",
       " 'add',\n",
       " 'add_prefix',\n",
       " 'add_suffix',\n",
       " 'agg',\n",
       " 'aggregate',\n",
       " 'align',\n",
       " 'all',\n",
       " 'any',\n",
       " 'append',\n",
       " 'apply',\n",
       " 'applymap',\n",
       " 'as_blocks',\n",
       " 'as_matrix',\n",
       " 'asfreq',\n",
       " 'asof',\n",
       " 'assign',\n",
       " 'astype',\n",
       " 'at',\n",
       " 'at_time',\n",
       " 'axes',\n",
       " 'between_time',\n",
       " 'bfill',\n",
       " 'blocks',\n",
       " 'bool',\n",
       " 'boxplot',\n",
       " 'clip',\n",
       " 'clip_lower',\n",
       " 'clip_upper',\n",
       " 'columns',\n",
       " 'combine',\n",
       " 'combine_first',\n",
       " 'compound',\n",
       " 'convert_objects',\n",
       " 'copy',\n",
       " 'corr',\n",
       " 'corrwith',\n",
       " 'count',\n",
       " 'cov',\n",
       " 'cummax',\n",
       " 'cummin',\n",
       " 'cumprod',\n",
       " 'cumsum',\n",
       " 'describe',\n",
       " 'diff',\n",
       " 'div',\n",
       " 'divide',\n",
       " 'dot',\n",
       " 'drop',\n",
       " 'drop_duplicates',\n",
       " 'droplevel',\n",
       " 'dropna',\n",
       " 'dtypes',\n",
       " 'duplicated',\n",
       " 'empty',\n",
       " 'eq',\n",
       " 'equals',\n",
       " 'eval',\n",
       " 'ewm',\n",
       " 'expanding',\n",
       " 'ffill',\n",
       " 'fillna',\n",
       " 'filter',\n",
       " 'first',\n",
       " 'first_valid_index',\n",
       " 'floordiv',\n",
       " 'from_csv',\n",
       " 'from_dict',\n",
       " 'from_items',\n",
       " 'from_records',\n",
       " 'ftypes',\n",
       " 'ge',\n",
       " 'get',\n",
       " 'get_dtype_counts',\n",
       " 'get_ftype_counts',\n",
       " 'get_value',\n",
       " 'get_values',\n",
       " 'groupby',\n",
       " 'gt',\n",
       " 'head',\n",
       " 'hist',\n",
       " 'iat',\n",
       " 'idxmax',\n",
       " 'idxmin',\n",
       " 'iloc',\n",
       " 'index',\n",
       " 'infer_objects',\n",
       " 'info',\n",
       " 'insert',\n",
       " 'interpolate',\n",
       " 'is_copy',\n",
       " 'isin',\n",
       " 'isna',\n",
       " 'isnull',\n",
       " 'items',\n",
       " 'iteritems',\n",
       " 'iterrows',\n",
       " 'itertuples',\n",
       " 'ix',\n",
       " 'join',\n",
       " 'keys',\n",
       " 'kurt',\n",
       " 'kurtosis',\n",
       " 'last',\n",
       " 'last_valid_index',\n",
       " 'le',\n",
       " 'loc',\n",
       " 'lookup',\n",
       " 'lt',\n",
       " 'mad',\n",
       " 'mask',\n",
       " 'max',\n",
       " 'mean',\n",
       " 'median',\n",
       " 'melt',\n",
       " 'memory_usage',\n",
       " 'merge',\n",
       " 'min',\n",
       " 'mod',\n",
       " 'mode',\n",
       " 'mul',\n",
       " 'multiply',\n",
       " 'ndim',\n",
       " 'ne',\n",
       " 'nlargest',\n",
       " 'notna',\n",
       " 'notnull',\n",
       " 'nsmallest',\n",
       " 'nunique',\n",
       " 'pct_change',\n",
       " 'pipe',\n",
       " 'pivot',\n",
       " 'pivot_table',\n",
       " 'plot',\n",
       " 'pop',\n",
       " 'pow',\n",
       " 'prod',\n",
       " 'product',\n",
       " 'quantile',\n",
       " 'query',\n",
       " 'radd',\n",
       " 'rank',\n",
       " 'rdiv',\n",
       " 'reindex',\n",
       " 'reindex_axis',\n",
       " 'reindex_like',\n",
       " 'rename',\n",
       " 'rename_axis',\n",
       " 'reorder_levels',\n",
       " 'replace',\n",
       " 'resample',\n",
       " 'reset_index',\n",
       " 'rfloordiv',\n",
       " 'rmod',\n",
       " 'rmul',\n",
       " 'rolling',\n",
       " 'round',\n",
       " 'rpow',\n",
       " 'rsub',\n",
       " 'rtruediv',\n",
       " 'sample',\n",
       " 'select',\n",
       " 'select_dtypes',\n",
       " 'sem',\n",
       " 'set_axis',\n",
       " 'set_index',\n",
       " 'set_value',\n",
       " 'shape',\n",
       " 'shift',\n",
       " 'size',\n",
       " 'skew',\n",
       " 'slice_shift',\n",
       " 'sort_index',\n",
       " 'sort_values',\n",
       " 'squeeze',\n",
       " 'stack',\n",
       " 'std',\n",
       " 'style',\n",
       " 'sub',\n",
       " 'subtract',\n",
       " 'sum',\n",
       " 'swapaxes',\n",
       " 'swaplevel',\n",
       " 'tail',\n",
       " 'take',\n",
       " 'to_clipboard',\n",
       " 'to_csv',\n",
       " 'to_dense',\n",
       " 'to_dict',\n",
       " 'to_excel',\n",
       " 'to_feather',\n",
       " 'to_gbq',\n",
       " 'to_hdf',\n",
       " 'to_html',\n",
       " 'to_json',\n",
       " 'to_latex',\n",
       " 'to_msgpack',\n",
       " 'to_numpy',\n",
       " 'to_panel',\n",
       " 'to_parquet',\n",
       " 'to_period',\n",
       " 'to_pickle',\n",
       " 'to_records',\n",
       " 'to_sparse',\n",
       " 'to_sql',\n",
       " 'to_stata',\n",
       " 'to_string',\n",
       " 'to_timestamp',\n",
       " 'to_xarray',\n",
       " 'transform',\n",
       " 'transpose',\n",
       " 'truediv',\n",
       " 'truncate',\n",
       " 'tshift',\n",
       " 'tz_convert',\n",
       " 'tz_localize',\n",
       " 'unstack',\n",
       " 'update',\n",
       " 'values',\n",
       " 'var',\n",
       " 'where',\n",
       " 'xs']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dir(df_modin)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>cert</th>\n",
       "      <th>NameOfBank</th>\n",
       "      <th>city</th>\n",
       "      <th>State</th>\n",
       "      <th>zip</th>\n",
       "      <th>county</th>\n",
       "      <th>cbsa_metro</th>\n",
       "      <th>MetroArea</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>NetLoansAndLeasesToDeposits</th>\n",
       "      <th>TotalDepositAccountsWithLessThan100k</th>\n",
       "      <th>TotalDepositAccountsWithMoreThan100k</th>\n",
       "      <th>DepositsAccountsLessThan250k</th>\n",
       "      <th>DepositAccountsWithMoreThan250k</th>\n",
       "      <th>TotalDeposits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15426</td>\n",
       "      <td>Almena State Bank</td>\n",
       "      <td>Almena</td>\n",
       "      <td>KS</td>\n",
       "      <td>67622</td>\n",
       "      <td>Norton</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>97</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>51131</td>\n",
       "      <td>19923</td>\n",
       "      <td>71939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>57144</td>\n",
       "      <td>American State Bank &amp; Trust Company</td>\n",
       "      <td>Great Bend</td>\n",
       "      <td>KS</td>\n",
       "      <td>67530</td>\n",
       "      <td>Barton</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>155513</td>\n",
       "      <td>121751</td>\n",
       "      <td>284517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17636</td>\n",
       "      <td>Astra Bank</td>\n",
       "      <td>Scandia</td>\n",
       "      <td>KS</td>\n",
       "      <td>66966</td>\n",
       "      <td>Republic</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>81</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>142692</td>\n",
       "      <td>64803</td>\n",
       "      <td>214791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17769</td>\n",
       "      <td>Bank of Hays</td>\n",
       "      <td>Hays</td>\n",
       "      <td>KS</td>\n",
       "      <td>67601</td>\n",
       "      <td>Ellis</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>84</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>116204</td>\n",
       "      <td>79910</td>\n",
       "      <td>202174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13710</td>\n",
       "      <td>Bank of Palmer</td>\n",
       "      <td>Palmer</td>\n",
       "      <td>KS</td>\n",
       "      <td>66962</td>\n",
       "      <td>Washington</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>62</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25798</td>\n",
       "      <td>7819</td>\n",
       "      <td>37886</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cert                           NameOfBank        city State    zip  \\\n",
       "0  15426                    Almena State Bank      Almena    KS  67622   \n",
       "1  57144  American State Bank & Trust Company  Great Bend    KS  67530   \n",
       "2  17636                           Astra Bank     Scandia    KS  66966   \n",
       "3  17769                         Bank of Hays        Hays    KS  67601   \n",
       "4  13710                       Bank of Palmer      Palmer    KS  66962   \n",
       "\n",
       "       county  cbsa_metro MetroArea     Quarter  NetLoansAndLeasesToDeposits  \\\n",
       "0      Norton           0       NaN  2016-09-30                           97   \n",
       "1      Barton           0       NaN  2016-09-30                           89   \n",
       "2    Republic           0       NaN  2016-09-30                           81   \n",
       "3       Ellis           0       NaN  2016-09-30                           84   \n",
       "4  Washington           0       NaN  2016-09-30                           62   \n",
       "\n",
       "   TotalDepositAccountsWithLessThan100k  TotalDepositAccountsWithMoreThan100k  \\\n",
       "0                                     0                                     0   \n",
       "1                                     0                                     0   \n",
       "2                                     0                                     0   \n",
       "3                                     0                                     0   \n",
       "4                                     0                                     0   \n",
       "\n",
       "   DepositsAccountsLessThan250k  DepositAccountsWithMoreThan250k  \\\n",
       "0                         51131                            19923   \n",
       "1                        155513                           121751   \n",
       "2                        142692                            64803   \n",
       "3                        116204                            79910   \n",
       "4                         25798                             7819   \n",
       "\n",
       "   TotalDeposits  \n",
       "0          71939  \n",
       "1         284517  \n",
       "2         214791  \n",
       "3         202174  \n",
       "4          37886  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_modin.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "UserWarning: `info` defaulting to pandas implementation.\n",
      "To request implementation, send an email to feature_requests@modin.org.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'modin.pandas.dataframe.DataFrame'>\n",
      "RangeIndex: 938639 entries, 0 to 938638\n",
      "Data columns (total 15 columns):\n",
      "cert                                    938639 non-null int64\n",
      "NameOfBank                              938639 non-null object\n",
      "city                                    938639 non-null object\n",
      "State                                   938639 non-null object\n",
      "zip                                     938639 non-null int64\n",
      "county                                  938639 non-null object\n",
      "cbsa_metro                              938639 non-null int64\n",
      "MetroArea                               532382 non-null object\n",
      "Quarter                                 938639 non-null object\n",
      "NetLoansAndLeasesToDeposits             938639 non-null int64\n",
      "TotalDepositAccountsWithLessThan100k    938639 non-null int64\n",
      "TotalDepositAccountsWithMoreThan100k    938639 non-null int64\n",
      "DepositsAccountsLessThan250k            938639 non-null int64\n",
      "DepositAccountsWithMoreThan250k         938639 non-null int64\n",
      "TotalDeposits                           938639 non-null int64\n",
      "dtypes: int64(9), object(6)\n",
      "memory usage: 107.4+ MB\n"
     ]
    }
   ],
   "source": [
    "df_modin.info(memory_usage=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 5 \n",
    "+ Using Dask\n",
    "+ Dask DataFrame\n",
    "+ Doesn’t load anything until you specifically tell it to do so."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the Pkgs\n",
    "import dask.dataframe as dd\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 70.9 ms, sys: 15 ms, total: 85.9 ms\n",
      "Wall time: 351 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_dask = dd.read_csv(\"big_dataset.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['DepositAccountsWithMoreThan250k',\n",
       " 'DepositsAccountsLessThan250k',\n",
       " 'MetroArea',\n",
       " 'NameOfBank',\n",
       " 'NetLoansAndLeasesToDeposits',\n",
       " 'Quarter',\n",
       " 'State',\n",
       " 'TotalDepositAccountsWithLessThan100k',\n",
       " 'TotalDepositAccountsWithMoreThan100k',\n",
       " 'TotalDeposits',\n",
       " '_HTML_FMT',\n",
       " '__abs__',\n",
       " '__add__',\n",
       " '__and__',\n",
       " '__array__',\n",
       " '__array_ufunc__',\n",
       " '__array_wrap__',\n",
       " '__bool__',\n",
       " '__class__',\n",
       " '__complex__',\n",
       " '__dask_graph__',\n",
       " '__dask_keys__',\n",
       " '__dask_layers__',\n",
       " '__dask_optimize__',\n",
       " '__dask_postcompute__',\n",
       " '__dask_postpersist__',\n",
       " '__dask_scheduler__',\n",
       " '__dask_tokenize__',\n",
       " '__delattr__',\n",
       " '__delitem__',\n",
       " '__dict__',\n",
       " '__dir__',\n",
       " '__doc__',\n",
       " '__eq__',\n",
       " '__float__',\n",
       " '__floordiv__',\n",
       " '__format__',\n",
       " '__ge__',\n",
       " '__getattr__',\n",
       " '__getattribute__',\n",
       " '__getitem__',\n",
       " '__getstate__',\n",
       " '__gt__',\n",
       " '__hash__',\n",
       " '__init__',\n",
       " '__init_subclass__',\n",
       " '__int__',\n",
       " '__invert__',\n",
       " '__le__',\n",
       " '__len__',\n",
       " '__long__',\n",
       " '__lt__',\n",
       " '__mod__',\n",
       " '__module__',\n",
       " '__mul__',\n",
       " '__ne__',\n",
       " '__neg__',\n",
       " '__new__',\n",
       " '__nonzero__',\n",
       " '__or__',\n",
       " '__pow__',\n",
       " '__radd__',\n",
       " '__rand__',\n",
       " '__reduce__',\n",
       " '__reduce_ex__',\n",
       " '__repr__',\n",
       " '__rfloordiv__',\n",
       " '__rmod__',\n",
       " '__rmul__',\n",
       " '__ror__',\n",
       " '__rpow__',\n",
       " '__rsub__',\n",
       " '__rtruediv__',\n",
       " '__rxor__',\n",
       " '__setattr__',\n",
       " '__setitem__',\n",
       " '__setstate__',\n",
       " '__sizeof__',\n",
       " '__slots__',\n",
       " '__str__',\n",
       " '__sub__',\n",
       " '__subclasshook__',\n",
       " '__truediv__',\n",
       " '__weakref__',\n",
       " '__xor__',\n",
       " '_args',\n",
       " '_bind_comparison_method',\n",
       " '_bind_operator',\n",
       " '_bind_operator_method',\n",
       " '_constructor',\n",
       " '_contains_index_name',\n",
       " '_cum_agg',\n",
       " '_elemwise',\n",
       " '_get_binary_operator',\n",
       " '_get_numeric_data',\n",
       " '_get_unary_operator',\n",
       " '_ipython_key_completions_',\n",
       " '_is_column_label_reference',\n",
       " '_is_index_level_reference',\n",
       " '_is_partition_type',\n",
       " '_meta',\n",
       " '_meta_nonempty',\n",
       " '_name',\n",
       " '_partition_type',\n",
       " '_partitions',\n",
       " '_reduction_agg',\n",
       " '_repr_data',\n",
       " '_repr_divisions',\n",
       " '_repr_html_',\n",
       " '_scalarfunc',\n",
       " '_select_columns_or_index',\n",
       " '_token_prefix',\n",
       " '_validate_axis',\n",
       " 'abs',\n",
       " 'add',\n",
       " 'align',\n",
       " 'all',\n",
       " 'any',\n",
       " 'append',\n",
       " 'apply',\n",
       " 'applymap',\n",
       " 'assign',\n",
       " 'astype',\n",
       " 'bfill',\n",
       " 'categorize',\n",
       " 'cbsa_metro',\n",
       " 'cert',\n",
       " 'city',\n",
       " 'clear_divisions',\n",
       " 'clip',\n",
       " 'clip_lower',\n",
       " 'clip_upper',\n",
       " 'columns',\n",
       " 'combine',\n",
       " 'combine_first',\n",
       " 'compute',\n",
       " 'copy',\n",
       " 'corr',\n",
       " 'count',\n",
       " 'county',\n",
       " 'cov',\n",
       " 'cummax',\n",
       " 'cummin',\n",
       " 'cumprod',\n",
       " 'cumsum',\n",
       " 'dask',\n",
       " 'describe',\n",
       " 'diff',\n",
       " 'div',\n",
       " 'divisions',\n",
       " 'drop',\n",
       " 'drop_duplicates',\n",
       " 'dropna',\n",
       " 'dtypes',\n",
       " 'eq',\n",
       " 'eval',\n",
       " 'ffill',\n",
       " 'fillna',\n",
       " 'first',\n",
       " 'floordiv',\n",
       " 'ge',\n",
       " 'get_dtype_counts',\n",
       " 'get_ftype_counts',\n",
       " 'get_partition',\n",
       " 'groupby',\n",
       " 'gt',\n",
       " 'head',\n",
       " 'idxmax',\n",
       " 'idxmin',\n",
       " 'iloc',\n",
       " 'index',\n",
       " 'info',\n",
       " 'isin',\n",
       " 'isna',\n",
       " 'isnull',\n",
       " 'iterrows',\n",
       " 'itertuples',\n",
       " 'join',\n",
       " 'known_divisions',\n",
       " 'last',\n",
       " 'le',\n",
       " 'loc',\n",
       " 'lt',\n",
       " 'map_overlap',\n",
       " 'map_partitions',\n",
       " 'mask',\n",
       " 'max',\n",
       " 'mean',\n",
       " 'memory_usage',\n",
       " 'merge',\n",
       " 'min',\n",
       " 'mod',\n",
       " 'mul',\n",
       " 'ndim',\n",
       " 'ne',\n",
       " 'nlargest',\n",
       " 'notnull',\n",
       " 'npartitions',\n",
       " 'nsmallest',\n",
       " 'nunique_approx',\n",
       " 'partitions',\n",
       " 'persist',\n",
       " 'pipe',\n",
       " 'pivot_table',\n",
       " 'pow',\n",
       " 'prod',\n",
       " 'quantile',\n",
       " 'query',\n",
       " 'radd',\n",
       " 'random_split',\n",
       " 'rdiv',\n",
       " 'reduction',\n",
       " 'rename',\n",
       " 'repartition',\n",
       " 'resample',\n",
       " 'reset_index',\n",
       " 'rfloordiv',\n",
       " 'rmod',\n",
       " 'rmul',\n",
       " 'rolling',\n",
       " 'round',\n",
       " 'rpow',\n",
       " 'rsub',\n",
       " 'rtruediv',\n",
       " 'sample',\n",
       " 'select_dtypes',\n",
       " 'sem',\n",
       " 'set_index',\n",
       " 'shape',\n",
       " 'shift',\n",
       " 'size',\n",
       " 'squeeze',\n",
       " 'std',\n",
       " 'sub',\n",
       " 'sum',\n",
       " 'tail',\n",
       " 'to_bag',\n",
       " 'to_csv',\n",
       " 'to_dask_array',\n",
       " 'to_delayed',\n",
       " 'to_hdf',\n",
       " 'to_html',\n",
       " 'to_json',\n",
       " 'to_parquet',\n",
       " 'to_records',\n",
       " 'to_string',\n",
       " 'to_timestamp',\n",
       " 'truediv',\n",
       " 'values',\n",
       " 'var',\n",
       " 'visualize',\n",
       " 'where',\n",
       " 'zip']"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dir(df_dask)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'dask.dataframe.core.DataFrame'>\n",
      "Columns: 15 entries, cert to TotalDeposits\n",
      "dtypes: object(5), float64(1), int64(9)"
     ]
    }
   ],
   "source": [
    "df_dask.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><strong>Dask DataFrame Structure:</strong></div>\n",
       "<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>cert</th>\n",
       "      <th>zip</th>\n",
       "      <th>cbsa_metro</th>\n",
       "      <th>MetroArea</th>\n",
       "      <th>NetLoansAndLeasesToDeposits</th>\n",
       "      <th>TotalDepositAccountsWithLessThan100k</th>\n",
       "      <th>TotalDepositAccountsWithMoreThan100k</th>\n",
       "      <th>DepositsAccountsLessThan250k</th>\n",
       "      <th>DepositAccountsWithMoreThan250k</th>\n",
       "      <th>TotalDeposits</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>npartitions=1</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>float64</td>\n",
       "      <td>int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>int64</td>\n",
       "      <td>int64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "<div>Dask Name: describe, 102 tasks</div>"
      ],
      "text/plain": [
       "Dask DataFrame Structure:\n",
       "                cert    zip cbsa_metro MetroArea NetLoansAndLeasesToDeposits TotalDepositAccountsWithLessThan100k TotalDepositAccountsWithMoreThan100k DepositsAccountsLessThan250k DepositAccountsWithMoreThan250k TotalDeposits\n",
       "npartitions=1                                                                                                                                                                                                                    \n",
       "               int64  int64      int64   float64                       int64                                int64                                int64                        int64                           int64         int64\n",
       "                 ...    ...        ...       ...                         ...                                  ...                                  ...                          ...                             ...           ...\n",
       "Dask Name: describe, 102 tasks"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Describe\n",
    "df_dask.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Describe The Dataset using compute\n",
    "#df_dask.describe().compute()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 6\n",
    "+ Using Vaex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load Pkgs\n",
    "#import vaex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dv = vaex.from_csv(\"big_dataset.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 7\n",
    "+ Save it as pickle for faster loading\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 5.43 s, sys: 595 ms, total: 6.03 s\n",
      "Wall time: 9.13 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_all = pd.read_csv(\"big_dataset.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save as Pickle\n",
    "df_all.to_pickle(\"newdataset.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reload it and utilize it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 817 ms, sys: 329 ms, total: 1.15 s\n",
      "Wall time: 1.68 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "new_df = pd.read_pickle(\"newdataset.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Improves The Speed\n",
    "+ Pickling Changes it From Raw Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>cert</th>\n",
       "      <th>NameOfBank</th>\n",
       "      <th>city</th>\n",
       "      <th>State</th>\n",
       "      <th>zip</th>\n",
       "      <th>county</th>\n",
       "      <th>cbsa_metro</th>\n",
       "      <th>MetroArea</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>NetLoansAndLeasesToDeposits</th>\n",
       "      <th>TotalDepositAccountsWithLessThan100k</th>\n",
       "      <th>TotalDepositAccountsWithMoreThan100k</th>\n",
       "      <th>DepositsAccountsLessThan250k</th>\n",
       "      <th>DepositAccountsWithMoreThan250k</th>\n",
       "      <th>TotalDeposits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15426</td>\n",
       "      <td>Almena State Bank</td>\n",
       "      <td>Almena</td>\n",
       "      <td>KS</td>\n",
       "      <td>67622</td>\n",
       "      <td>Norton</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>97</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>51131</td>\n",
       "      <td>19923</td>\n",
       "      <td>71939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>57144</td>\n",
       "      <td>American State Bank &amp; Trust Company</td>\n",
       "      <td>Great Bend</td>\n",
       "      <td>KS</td>\n",
       "      <td>67530</td>\n",
       "      <td>Barton</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>155513</td>\n",
       "      <td>121751</td>\n",
       "      <td>284517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17636</td>\n",
       "      <td>Astra Bank</td>\n",
       "      <td>Scandia</td>\n",
       "      <td>KS</td>\n",
       "      <td>66966</td>\n",
       "      <td>Republic</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>81</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>142692</td>\n",
       "      <td>64803</td>\n",
       "      <td>214791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17769</td>\n",
       "      <td>Bank of Hays</td>\n",
       "      <td>Hays</td>\n",
       "      <td>KS</td>\n",
       "      <td>67601</td>\n",
       "      <td>Ellis</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>84</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>116204</td>\n",
       "      <td>79910</td>\n",
       "      <td>202174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13710</td>\n",
       "      <td>Bank of Palmer</td>\n",
       "      <td>Palmer</td>\n",
       "      <td>KS</td>\n",
       "      <td>66962</td>\n",
       "      <td>Washington</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>62</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25798</td>\n",
       "      <td>7819</td>\n",
       "      <td>37886</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cert                           NameOfBank        city State    zip  \\\n",
       "0  15426                    Almena State Bank      Almena    KS  67622   \n",
       "1  57144  American State Bank & Trust Company  Great Bend    KS  67530   \n",
       "2  17636                           Astra Bank     Scandia    KS  66966   \n",
       "3  17769                         Bank of Hays        Hays    KS  67601   \n",
       "4  13710                       Bank of Palmer      Palmer    KS  66962   \n",
       "\n",
       "       county  cbsa_metro MetroArea     Quarter  NetLoansAndLeasesToDeposits  \\\n",
       "0      Norton           0       NaN  2016-09-30                           97   \n",
       "1      Barton           0       NaN  2016-09-30                           89   \n",
       "2    Republic           0       NaN  2016-09-30                           81   \n",
       "3       Ellis           0       NaN  2016-09-30                           84   \n",
       "4  Washington           0       NaN  2016-09-30                           62   \n",
       "\n",
       "   TotalDepositAccountsWithLessThan100k  TotalDepositAccountsWithMoreThan100k  \\\n",
       "0                                     0                                     0   \n",
       "1                                     0                                     0   \n",
       "2                                     0                                     0   \n",
       "3                                     0                                     0   \n",
       "4                                     0                                     0   \n",
       "\n",
       "   DepositsAccountsLessThan250k  DepositAccountsWithMoreThan250k  \\\n",
       "0                         51131                            19923   \n",
       "1                        155513                           121751   \n",
       "2                        142692                            64803   \n",
       "3                        116204                            79910   \n",
       "4                         25798                             7819   \n",
       "\n",
       "   TotalDeposits  \n",
       "0          71939  \n",
       "1         284517  \n",
       "2         214791  \n",
       "3         202174  \n",
       "4          37886  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Method 8 \n",
    "+ Use of GC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "import gc\n",
    "import psutil"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "available RAM: svmem(total=4018290688, available=105082880, percent=97.4, used=3527852032, free=115445760, active=2932015104, inactive=646619136, buffers=9990144, cached=365002752, shared=153759744, slab=146710528)\n"
     ]
    }
   ],
   "source": [
    "print(\"available RAM:\", psutil.virtual_memory())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "available RAM: svmem(total=4018290688, available=997117952, percent=75.2, used=2631544832, free=1004339200, active=2042478592, inactive=647782400, buffers=10694656, cached=371712000, shared=158011392, slab=146751488)\n"
     ]
    }
   ],
   "source": [
    "# Garbage Collection\n",
    "gc.collect()\n",
    "\n",
    "print(\"available RAM:\", psutil.virtual_memory())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 9\n",
    "+ Use Cloud"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#### Thanks For Reading\n",
    "#### Jesus Saves @JCharisTech\n",
    "### Jesse E.Agbe (JCharis)\n",
    "### J-Secur1ty"
   ]
  }
 ],
 "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}