Data_Cleaning_Feature_Creation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "fatal-crowd",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime as dt\n",
    "import seaborn as sns\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "import re\n",
    "from collections import Counter"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "smaller-visitor",
   "metadata": {},
   "source": [
    "# Load Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "irish-discrimination",
   "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>Recnum</th>\n",
       "      <th>Cardnum</th>\n",
       "      <th>Date</th>\n",
       "      <th>Merchnum</th>\n",
       "      <th>Merch description</th>\n",
       "      <th>Merch state</th>\n",
       "      <th>Merch zip</th>\n",
       "      <th>Transtype</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Fraud</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5142190439</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>5509006296254</td>\n",
       "      <td>FEDEX SHP 12/23/09 AB#</td>\n",
       "      <td>TN</td>\n",
       "      <td>38118.0</td>\n",
       "      <td>P</td>\n",
       "      <td>3.62</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>5142183973</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>61003026333</td>\n",
       "      <td>SERVICE MERCHANDISE #81</td>\n",
       "      <td>MA</td>\n",
       "      <td>1803.0</td>\n",
       "      <td>P</td>\n",
       "      <td>31.42</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>5142131721</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>4503082993600</td>\n",
       "      <td>OFFICE DEPOT #191</td>\n",
       "      <td>MD</td>\n",
       "      <td>20706.0</td>\n",
       "      <td>P</td>\n",
       "      <td>178.49</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>5142148452</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>5509006296254</td>\n",
       "      <td>FEDEX SHP 12/28/09 AB#</td>\n",
       "      <td>TN</td>\n",
       "      <td>38118.0</td>\n",
       "      <td>P</td>\n",
       "      <td>3.62</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>5142190439</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>5509006296254</td>\n",
       "      <td>FEDEX SHP 12/23/09 AB#</td>\n",
       "      <td>TN</td>\n",
       "      <td>38118.0</td>\n",
       "      <td>P</td>\n",
       "      <td>3.62</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Recnum     Cardnum       Date       Merchnum        Merch description  \\\n",
       "0       1  5142190439 2010-01-01  5509006296254   FEDEX SHP 12/23/09 AB#   \n",
       "1       2  5142183973 2010-01-01    61003026333  SERVICE MERCHANDISE #81   \n",
       "2       3  5142131721 2010-01-01  4503082993600        OFFICE DEPOT #191   \n",
       "3       4  5142148452 2010-01-01  5509006296254   FEDEX SHP 12/28/09 AB#   \n",
       "4       5  5142190439 2010-01-01  5509006296254   FEDEX SHP 12/23/09 AB#   \n",
       "\n",
       "  Merch state  Merch zip Transtype  Amount  Fraud  \n",
       "0          TN    38118.0         P    3.62      0  \n",
       "1          MA     1803.0         P   31.42      0  \n",
       "2          MD    20706.0         P  178.49      0  \n",
       "3          TN    38118.0         P    3.62      0  \n",
       "4          TN    38118.0         P    3.62      0  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_excel('card transactions.xlsx')\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "special-summary",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Recnum                  0\n",
       "Cardnum                 0\n",
       "Date                    0\n",
       "Merchnum             3375\n",
       "Merch description       0\n",
       "Merch state          1195\n",
       "Merch zip            4656\n",
       "Transtype               0\n",
       "Amount                  0\n",
       "Fraud                   0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "molecular-manchester",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 96753 entries, 0 to 96752\n",
      "Data columns (total 10 columns):\n",
      " #   Column             Non-Null Count  Dtype         \n",
      "---  ------             --------------  -----         \n",
      " 0   Recnum             96753 non-null  int64         \n",
      " 1   Cardnum            96753 non-null  int64         \n",
      " 2   Date               96753 non-null  datetime64[ns]\n",
      " 3   Merchnum           93378 non-null  object        \n",
      " 4   Merch description  96753 non-null  object        \n",
      " 5   Merch state        95558 non-null  object        \n",
      " 6   Merch zip          92097 non-null  float64       \n",
      " 7   Transtype          96753 non-null  object        \n",
      " 8   Amount             96753 non-null  float64       \n",
      " 9   Fraud              96753 non-null  int64         \n",
      "dtypes: datetime64[ns](1), float64(2), int64(3), object(4)\n",
      "memory usage: 7.4+ MB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "unauthorized-certification",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Recnum 0\n",
      "Cardnum 0\n",
      "Date 0\n",
      "Merchnum 231\n",
      "Merch description 0\n",
      "Merch state 0\n",
      "Merch zip 0\n",
      "Transtype 0\n",
      "Amount 0\n",
      "Fraud 95694\n"
     ]
    }
   ],
   "source": [
    "for col in data.columns:\n",
    "    print(col,len(data.loc[data[col]=='0'])+len(data.loc[data[col]==0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "alpine-arbor",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    95694\n",
       "1     1059\n",
       "Name: Fraud, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.Fraud.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "stainless-registrar",
   "metadata": {},
   "source": [
    "# Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "adopted-episode",
   "metadata": {},
   "source": [
    "## Fill missing values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "hollywood-australia",
   "metadata": {},
   "source": [
    "According to data quality checking, three variables -- `merchnum`, `merch.state` and `merch.zip`\n",
    "are not 100% populated, with missing values and 0s. We assigned values in these fields \n",
    "based on other different fields.\n",
    "\n",
    "- Merchnum: based on merch description (mode)\n",
    "- Merch state: based on merch zip -> merch description (mode) \n",
    "- Merch zip: based on merch num -> merch description (mode)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "offshore-object",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Recnum               0\n",
       "Cardnum              0\n",
       "Date                 0\n",
       "Merchnum             0\n",
       "Merch description    0\n",
       "Merch state          0\n",
       "Merch zip            0\n",
       "Transtype            0\n",
       "Amount               0\n",
       "Fraud                0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.fillna('Unknown',inplace=True)\n",
    "data.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cleared-breakfast",
   "metadata": {},
   "source": [
    "### Merchnum"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "nominated-prime",
   "metadata": {},
   "source": [
    "Fill missing merchnum based on Merch description (mode)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "textile-black",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "930"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_merchnum = data.loc[(data.Merchnum=='Unknown')|(data.Merchnum=='0')]\n",
    "len(missing_merchnum['Merch description'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "copyrighted-companion",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3606"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# before filling\n",
    "len(missing_merchnum)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "mexican-convenience",
   "metadata": {},
   "outputs": [],
   "source": [
    "def majorityElement(nums):\n",
    "        \"\"\"\n",
    "        :type nums: List[int]\n",
    "        :rtype: int\n",
    "        \"\"\"\n",
    "        \n",
    "        if not nums:\n",
    "            return 'Unknown'\n",
    "    \n",
    "        count = {}\n",
    "        for num in nums:                             # count the number of times every num occurrs\n",
    "            if num in count:\n",
    "                count[num] += 1\n",
    "            else:\n",
    "                count[num] = 1\n",
    "        \n",
    "        return {v: k for k, v in count.items()}[max(count.values())] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "bored-usage",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1111'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "majorityElement(['1','1111','1111','22'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ruled-elephant",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CONVENIENCE CHECK FEE(%) \t Unknown\n",
      "GRAINGER #973 \t 06-3666654612\n",
      "AUTOMATED OFFICE PRODU \t 06-3666935222\n",
      "ROLL CALL NEWSPAPER \t 600020014041\n",
      "MONTGOMERY COLLEGE-PHONE \t Unknown\n",
      "ARGENT CHEMICAL LABS \t 900009018624\n",
      "CHEMUNEX \t 06-3666157897\n",
      "RETAIL DEBIT ADJUSTMENT \t Unknown\n",
      "TEKMAR COMPANY \t 08-0069481648\n",
      "OPERON TECHNOLOGIES INC \t 991801244335\n",
      "RETAIL CREDIT ADJUSTMENT \t Unknown\n",
      "CHARLESTON CO PARKING OP0 \t Unknown\n",
      "PACKAGE PLACE  THE \t Unknown\n",
      "CUBIX CORPORATION \t Unknown\n",
      "SIGNAL GRAPHICS PRINTING \t Unknown\n",
      "GRAINGER #932 \t 06-3666477676\n",
      "SUNSET LABORATORY \t 08-0904990330\n",
      "AUTO PARTS MACHINE SHOP \t 900009097097\n",
      "THE AMERICAN PHYSIOLOGICA \t 600000642706\n",
      "C & M OFFICE EQUIPMENT \t Unknown\n",
      "HACH COMPANY \t 08-3500900433\n",
      "MOLECULAR PROBES INC. \t 900009010706\n",
      "OSU BOOKSTORES INC \t 08-0966777330\n",
      "LEGASYS INTERNATIONAL INC \t 08-3500601097\n",
      "TOMMY'S TRAILERS \t Unknown\n",
      "CONSOLIDATED FREIGHTWAYS \t 08-9902558331\n",
      "JF CONTRACTING \t 05-0500900356\n",
      "Z WORLD/RABBIT SEMICONDUC \t Unknown\n",
      "IMPAC/TRI-COUNTY/FREED \t Unknown\n",
      "GRAINGER #507 \t 06-3666051760\n",
      "CONSOLIDATED PLASTICS CO \t 900009066514\n",
      "REPROGRPHC TECHNLGIES INC \t Unknown\n",
      "STP SPECIALITY TECH \t Unknown\n",
      "VANGARD INTERNAITONAL \t Unknown\n",
      "GOLDEN SOFTWARE INC \t 08-3500094845\n",
      "KEYSAN CATALOG \t 05-0509106367\n",
      "GRAINGER #935 \t 06-0006692427\n",
      "LES SCHWAB TIRES   10029 \t 08-0006099335\n",
      "BLACKWELL SCIENCE \t Unknown\n",
      "CDN ISOTOPES INC \t Unknown\n",
      "GRAINGER #624 \t 06-3666057931\n",
      "BURLINGTON AIR EXPRESS \t 606000204287\n",
      "HOSPITAL SHARED SERVIC \t 06-6000099975\n",
      "RED WING SHOES 884 \t 600020079453\n",
      "INTERACTIVE SOFTWARE S \t Unknown\n",
      "H R WILLIAMS MILL SUPP \t Unknown\n",
      "ELSEVIER SCIENCE BV \t Unknown\n",
      "COLORADO GARDEN SHOW \t Unknown\n",
      "GRAINGER #268 \t 06-3666055266\n",
      "MEMOSUN INC \t 09-3666688703\n",
      "JERRY CROUSES TIRE/AUTO \t 607995135336\n",
      "PEARSON EDUCATION CANADA \t Unknown\n",
      "PONTOTOC AREA VO-TECH \t Unknown\n",
      "NATIONAL BAG COMPANY \t Unknown\n",
      "SHIMADZU SCIENTIFIC \t 08-0069493922\n",
      "RIVER NETWORK \t 08-9907716331\n",
      "GRAINGER #637 \t 06-3666057882\n",
      "FTMYR OOM CASHIER CAGE \t Unknown\n",
      "ESTUARINE RESEARCH FDRTN \t Unknown\n",
      "HOLLYWOOD AUTO SUPPLY \t 08-0619326337\n",
      "GRAINGER #931 \t 06-3666913976\n",
      "GRAINGER #505 \t 06-3666051134\n",
      "REED BUSINESS PUBLISHI \t Unknown\n",
      "INDUSTRIAL CHEMICALS INC. \t 8000917948\n",
      "SANDERSON SAFETY SUPPLY \t 800008008558\n",
      "MAYER ELECTRIC SUPPLY CO \t 8000924096\n",
      "APPARATUS SALES CORPORATI \t 900009056632\n",
      "X U-PARK #1281 \t Unknown\n",
      "PAYNE CNTY ASSESSOR'S OF0 \t Unknown\n",
      "X AMERICAN AIR001 \t Unknown\n",
      "PAYMENT ADJUSTMENT \t Unknown\n",
      "INSYNC SOFTWARE INC. \t Unknown\n",
      "PROXTRONICS INC \t Unknown\n",
      "CAMPDEN PUBLISHING LTD \t Unknown\n",
      "OUP JOURNALS \t Unknown\n",
      "CONTROLCO 01 \t Unknown\n",
      "TRI STATE SUPPLY INC \t 06-3666819105\n",
      "WILDCO OR WILDLIFE \t Unknown\n",
      "WELDING ENGINEERING SUP \t Unknown\n",
      "GRAINGER #933 \t 06-3666888433\n",
      "ST. PETER HOSPITAL \t Unknown\n",
      "MARK IT CO, THE \t 08-9908435335\n",
      "GOC-NRC CISTI /CNRC \t Unknown\n",
      "MEACON CORPORATION \t Unknown\n",
      "NORDAN SMITH WELDING \t Unknown\n",
      "LEARNED ASSOCIATES INC \t Unknown\n",
      "TODAYS PHOTO \t Unknown\n",
      "IKON OFFICE SOLUTIONS \t 9420966051835\n",
      "CREDIT DEBIT ADJUSTMENT \t Unknown\n",
      "GSA/CUST SUPPLY CTR 87 \t 9900000027006\n",
      "CRENLO \t Unknown\n",
      "HOWARD COOPER INC. \t 900009091137\n",
      "DICK BLICK800 447 1892 \t 6906000154305\n",
      "SEELYE PLASTICS INC \t 08-0009492287\n",
      "MARINE TECHNOLOGY \t Unknown\n",
      "GRAINGER #046 \t 06-0006692593\n",
      "DOBBS OPTICIANS INC \t 900009091409\n",
      "CAPITAL PRESS \t Unknown\n",
      "MARCEL DEKKER BOOK PUB \t 06-3666883431\n",
      "SILVER PLATTER INFO INC \t 06-3666841035\n",
      "ENVIRONMENTAL SIGN SYS \t Unknown\n",
      "AIRMETRICS \t Unknown\n",
      "JOAN CAROL DISPLAYS \t Unknown\n",
      "APPLIED IND TECH   744 \t 900009001511\n",
      "COLE INDUSTRIAL INC \t 08-0996103338\n",
      "SECURITY FENCE GROUP INC \t Unknown\n",
      "AMERICAN LASER INC \t Unknown\n",
      "INTERNATIONAL INSTITUT \t Unknown\n",
      "NORTHWEST RIVER SUPPLY \t Unknown\n",
      "GRAINGER #753 \t Unknown\n",
      "SEA-BIRD ELECTRONICS INC \t 08-0996224339\n",
      "J L DARLING CORP \t 900009097713\n",
      "EXERCISE EQUIPMENT   #90 \t Unknown\n",
      "BATTELLE PNNL \t 08-9906989334\n",
      "VIKING OFFICE PRODUCTS \t 09-0090700063\n",
      "RADAR, INC         #01 \t Unknown\n",
      "GRAINGER #732 \t Unknown\n",
      "NRCC-CISTI \t Unknown\n",
      "COMPUTER STORE OF CORVLS \t 08-0616075333\n",
      "MCLOUGHLIN MULTIMEDIA PUB \t 9006005990615\n",
      "WAVELAND PRESS INC \t Unknown\n",
      "FTMYR OOM 5 STAR CATER \t Unknown\n",
      "ARIZONA VALVE & FITTING C \t 08-3500012132\n",
      "INTL ORACLE USERS GRP \t Unknown\n",
      "RIVIERA HOTEL AND CASINO \t 4944900944994\n",
      "SGJ TECHNOLOGIES  INC. \t Unknown\n",
      "CRUCIAL TECH./MICRON \t 08-0980456333\n",
      "GUNN APPLIANCE REPAIR \t Unknown\n",
      "EQUIFAX INFO SERVIC \t 465614140337\n",
      "LANOVATION \t Unknown\n",
      "SEAARK MARINE INC \t Unknown\n",
      "OSCAR FABER LTD \t Unknown\n",
      "WATERLOO HYDROGEOLOGIC \t Unknown\n",
      "GENE PTACEK  AND  SON FIR \t Unknown\n",
      "E.S.I. ENVIRONMENTAL \t Unknown\n",
      "ATOMIC ENERGY AGENCY \t Unknown\n",
      "ARGENS \t 08-3500938753\n",
      "ATM CASH ADVANCE FEE \t Unknown\n",
      "STANDARD GRAPHICS MID AT \t Unknown\n",
      "ACE GLASS INCORPORATED \t 08-0036004235\n",
      "RESIDUA LTD \t Unknown\n",
      "SHENAV HADRAHOT \t Unknown\n",
      "COMPUTER WHOLESALE \t Unknown\n",
      "SHIRLEY JONES & ASSOCIATE \t Unknown\n",
      "KRUYSMAN/REDWELD \t Unknown\n",
      "GRAINGER #930 \t 06-3666632920\n",
      "GUILD INC \t Unknown\n",
      "SPECTRIS TECHNOLOGIES INC \t Unknown\n",
      "MONITORING TECHNOLOGIE \t Unknown\n",
      "THE HERALD NEWS \t Unknown\n",
      "OSMONICS INC. \t 08-0086002257\n",
      "COMPUTER ALLEY INC \t Unknown\n",
      "TEXAS WORKFORCE COMMISIO0 \t Unknown\n",
      "COMMERCIAL FILTER SALES \t Unknown\n",
      "WITTICHEN SUPPLY \t 8000924602\n",
      "PRODUCTION II INC \t Unknown\n",
      "GRAINGER #328 \t Unknown\n",
      "CANYON ELECTRIC \t Unknown\n",
      "NETCON INC \t 08-3500015496\n",
      "THE INTERNATIONAL ALLIANC \t Unknown\n",
      "BERNAN ASSOCIATES \t Unknown\n",
      "BIO-OREGON INC \t 08-9960715339\n",
      "C.R.C.P.D. \t Unknown\n",
      "TAC OFFICE PRODUCTS \t 800008001714\n",
      "NAT'L POLLUTION PREVEN \t 600660001663\n",
      "PASSE COMPANY \t 08-3500911114\n",
      "MUSTANG SURVIVAL INC \t Unknown\n",
      "SOCRATES COMPUTER SYST \t 06-3666857392\n",
      "FULTON PUBLISHING \t Unknown\n",
      "SHARP ELECT CORP GOV DIR \t 465909215334\n",
      "ALLTECH SUPERIOR \t Unknown\n",
      "INFOCUS \t Unknown\n",
      "IARC PRESS VAD  2184997 \t Unknown\n",
      "LECO CORP \t 08-3500681441\n",
      "ARTFUL FRAMING \t Unknown\n",
      "FEDERAL LOCK & SAFE \t Unknown\n",
      "X HERITAGE CONSER \t Unknown\n",
      "M W HOTT CO INC \t 900009061182\n",
      "KEITHLEY INSTRUMENTS INC \t 900009003054\n",
      "BAUMGARTEN CO OF WASHINGT \t 06-3666459915\n",
      "MITTLER SUPPLY INC \t Unknown\n",
      "GRAPHPAD SOFTWARE INC \t Unknown\n",
      "LASER ONE \t Unknown\n",
      "COLORADO MOUNTAIN CLUB  0 \t Unknown\n",
      "PROVANTAGE CORP \t 900009066062\n",
      "ISCO INC - E DIV \t 08-2200065764\n",
      "ALTA ANALYTICAL LAB \t Unknown\n",
      "PIKES PEAK NURSERIES \t Unknown\n",
      "MOBILE CYCLE CENTER \t Unknown\n",
      "ONYX GRAPHICS CORP. \t Unknown\n",
      "DATA CONTROL INC \t 06-3660324924\n",
      "TEKTRONIX MBD PARTS 09 \t Unknown\n",
      "NORTHWEST ECONOMETRICS \t Unknown\n",
      "DATA RECOVERY LAB \t Unknown\n",
      "ENVIROMENTAL MUTAGEN SOCT \t Unknown\n",
      "FRED MEYER TK      SJF \t Unknown\n",
      "S.C. DEPT.OF REVENUE DMR0 \t Unknown\n",
      "BECKER'S HARDWARE \t 08-3100069531\n",
      "GRAINGER #236 \t 06-3666055741\n",
      "ARROW SYSTEMS KONTROLS I0 \t Unknown\n",
      "TRANSDUCTION LABORATORIES \t Unknown\n",
      "WORLD PRECISION INST \t 8000954334\n",
      "THE CONFERENCE BOARD \t 06-3666883468\n",
      "VILLAGE LOCK & KEY \t Unknown\n",
      "TEXAS WORKFORCE COMMISSI0 \t Unknown\n",
      "DATAPRINT CORPORATION \t 08-3508253372\n",
      "HUMMINGBIRD COMMUNICATION \t Unknown\n",
      "GRAINGER #250 \t 06-3666055727\n",
      "CASCADE COMPUTER MAINT \t Unknown\n",
      "ANS & ZEBRA MUSSEL \t Unknown\n",
      "KNOWLEDGE ALLIANCE \t Unknown\n",
      "GRAINGER #925 \t Unknown\n",
      "ADA IRON & WELDING LLC \t Unknown\n",
      "KAUFMAN CONTAINER \t 900009003496\n",
      "ALARM CENTER  INC. \t Unknown\n",
      "BREDE EXPOSITION SERVICES \t 08-0002815224\n",
      "NEUTRAL POSTURE \t Unknown\n",
      "ADVANCED SEC & CONTROL \t Unknown\n",
      "ENVIRONMENTAL CONCERN  IN \t Unknown\n",
      "SMITH-ROOT INC \t 08-3509793750\n",
      "MYHRE'S EXPLORATION PDCT \t Unknown\n",
      "APOLLO AUDIO VISUAL \t 06-3666818297\n",
      "RED WING SHOE STORE 158 \t 600660066409\n",
      "BAILEY NURSERIES, INC \t Unknown\n",
      "LANGEVIN LEARN SERV(US)IN \t Unknown\n",
      "JW PHOTO LAB INC \t Unknown\n",
      "LEWAN & ASSOCIATES \t 08-3500676669\n",
      "GRAINGER #638 \t Unknown\n",
      "EDMA MARKETING LTD \t Unknown\n",
      "HIGH COUNTRY NEWS \t 06-3666968605\n",
      "NATL MICROGRAPHICS \t Unknown\n",
      "KITSAP MARINA \t 900009085118\n",
      "CONVENTION SERVICES NW \t Unknown\n",
      "NEW HERMES \t 8000934611\n",
      "AOL*ONLINE SERVICE 0200 \t 3433000017263\n",
      "A ARVIN TONER STORE \t Unknown\n",
      "A & M FIRE & SAFETY INC \t Unknown\n",
      "SCANDINAVIAN1175745333760 \t Unknown\n",
      "NOVA RESEARCH COMPANY \t 5600000067354\n",
      "SCANDINAVIAN1175745333761 \t Unknown\n",
      "DEBIT PURCHASE BALANCE \t Unknown\n",
      "MIKETRONICS INC \t Unknown\n",
      "TWIN CITIES DIGITAL INC \t Unknown\n",
      "CARTER CO. CLERK        0 \t Unknown\n",
      "MEDIA CYBERNETICS INC \t Unknown\n",
      "GRAINGER #147 \t Unknown\n",
      "KEYPOINT SRVICES INTERNL \t 08-0905698335\n",
      "AMER SOCIETY FOR QUALITY \t 08-0002801008\n",
      "SHARONVILLE ELECTRIC SPLY \t Unknown\n",
      "PUBLIC DATA \t Unknown\n",
      "CASH & CARRY UG. \t 09-3666145263\n",
      "BUY.COM \t 8834000695412\n",
      "US WEST COMM FED SERV \t 06-3666943596\n",
      "CPN INTERNATIONAL \t Unknown\n",
      "THORNTON PLASTICS \t 900009087623\n",
      "RESEARCH GENETICS \t 8000993091\n",
      "ALLIANCE FOR THE CHESA \t Unknown\n",
      "AFFINITY BIOREAGENTS INC \t 08-3500605701\n",
      "BRITISH CROP PROTECTIO \t Unknown\n",
      "X FIRSTIER BANCO/ \t Unknown\n",
      "X OFFICE DEPOT #1 \t Unknown\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "IDAHO PUBLIC TV FOUNDTN \t 08-0509624337\n",
      "WILLIAM L PRATT         0 \t Unknown\n",
      "SOUTHERN MICRO \t 8000912138\n",
      "BROOKLYN THERMOMETER \t 08-0069492615\n",
      "X THE CHEESECAKE \t Unknown\n",
      "X HOMEWOOD SUITES \t Unknown\n",
      "X FRANKLIN COVEY \t Unknown\n",
      "X SNYDERS WILLOW \t Unknown\n",
      "WATER RESOURCES PUBLIC \t Unknown\n",
      "X UNITED AIR    016 \t Unknown\n",
      "X DENVER ITL AIRP \t Unknown\n",
      "CHMIA                   0 \t Unknown\n",
      "AMAZON.COM  *SUPERSTOR \t 4353000719908\n",
      "CARPENTER BROS HDWE DO IT \t 08-3100066227\n",
      "COUNTRY SURVEY SUPPLY \t Unknown\n",
      "FASTSIGNS \t 2722000873087\n",
      "GRAINGER #423 \t 06-3666051986\n",
      "ALASKA CLEAN SEAS \t Unknown\n",
      "BOXWARE INC \t Unknown\n",
      "RETAIL DEBIT  ADJUSTMENT \t Unknown\n",
      "TSM INC \t Unknown\n",
      "POSTMASTER              0 \t Unknown\n",
      "WIRELESS COMMUNICATION \t 36443735\n",
      "EDIFRANCE       4227647 \t Unknown\n",
      "CAPITAL PRESS           0 \t Unknown\n",
      "A S P R S \t 600020018966\n",
      "ENVIRONMENTAL RESOURCE \t 06-0060735515\n",
      "AMER. INDIAN LAW TRAININ0 \t Unknown\n",
      "GRAINGER #568 \t Unknown\n",
      "ASSOC OF STATE WETLND MGR \t Unknown\n",
      "PLOTTER SUPPLY \t 06-3666709587\n",
      "THE MISSOULIAN \t 08-0005244834\n",
      "NATIONAL MAILING SYSTEM \t 06-3666154636\n",
      "NEW SOCIETY PUBLISHERS \t Unknown\n",
      "ARIS \t Unknown\n",
      "POSITANO'S \t 06-3666138052\n",
      "PHOTOWRIGHT LABS \t Unknown\n",
      "SIVERTSON GALLERY \t Unknown\n",
      "AUTOMATION COMPONENTS \t Unknown\n",
      "NAUTICAL SOFTWARE INC \t Unknown\n",
      "IKON OFC SOLUTIONS INC \t Unknown\n",
      "AMERICAN LUNG ASSOC \t 938908519220\n",
      "TEXAS STATE DIRECTORY   0 \t Unknown\n",
      "PC *PC CONNECTION \t 4063000739258\n",
      "GRAINGER #640 \t Unknown\n",
      "TIERRA MADRE ENVIR COR \t Unknown\n",
      "INDUSTRIAL PAPER SHREDDER \t Unknown\n",
      "FAXPRESS INC \t Unknown\n",
      "ID DEPT OF EDUCATION    0 \t Unknown\n",
      "WQT*D SNORE 1OF1 \t 3043000732914\n",
      "QUILTED JOURNEY \t Unknown\n",
      "BMJ \t Unknown\n",
      "GRAINGER #128 \t 06-3666055636\n",
      "ADVANCED LOCK & SAFE INC \t 08-3500678234\n",
      "PARAMOUNT SUPPLY CO INC \t Unknown\n",
      "GRAINGER #234 \t 06-3666055790\n",
      "GRAINGER #937 \t 06-0004269242\n",
      "ANDERSON'S SPRT GDS  004 \t 08-0610789331\n",
      "FLETCHER CHICAGO INC \t 06-3660343808\n",
      "BARBARA                 0 \t Unknown\n",
      "LANGEVIN LEARNING SERVICE \t Unknown\n",
      "STRATO REPROGRAPHIX \t Unknown\n",
      "GLOBAL ENGINEERING DOCUME \t 08-3500942443\n",
      "NCSL \t Unknown\n",
      "GENIGRAPHICS \t 8000860744\n",
      "J.SEIGEL FOOTWEAR INC. \t Unknown\n",
      "GRAINGER #233 \t Unknown\n",
      "OR ADMIN RULES UNIT     0 \t Unknown\n",
      "ASIS \t Unknown\n",
      "CREDIT PURCHASE BALANCE \t Unknown\n",
      "RTM COMMUNICATIONS \t Unknown\n",
      "NATIONAL DEFENSE INDUSTRI \t 06-3666852920\n",
      "CREATIVE TRAINING TECHINQ \t Unknown\n",
      "CPANS/PNNIS 2000 AIR    0 \t Unknown\n",
      "INNOVATIVE SOFTWARE LTD \t Unknown\n",
      "WA DEPT OF FISH AND WILD0 \t Unknown\n",
      "NEW ORLEANS PUBLIC      0 \t Unknown\n",
      "AMERICAN OVERSEAS BOOK \t 06-3666020573\n",
      "AK DEPT OF EDUC & EARLY 0 \t Unknown\n",
      "PGH INTL AIRPORT   Q3J \t Unknown\n",
      "SOFTWARE HOUSE INTERNATL \t 89200686316\n",
      "RETAIL DEIBT  ADJUSTMENT \t Unknown\n",
      "COSTO WHOLESALE         0 \t Unknown\n",
      "ON TIME DEPOT REPAIR INC0 \t Unknown\n",
      "AMER METEOROLOGICAL SOCTY \t 06-3666845810\n",
      "AFCEA INTERNATIONAL \t Unknown\n",
      "M  AND  M AUTO CENTER INC \t Unknown\n",
      "HARBIN'S INC. \t Unknown\n",
      "FEDERAL CARTRIDGE COMPANY \t 08-0007760361\n",
      "RETAIL MARKETING SVCS \t 8000803987\n",
      "GRAINGER #330 \t Unknown\n",
      "STAFFORD ENTERPRISES \t Unknown\n",
      "LEGIS CNSL ORS     00412 \t Unknown\n",
      "TRULOGIC SYSTEMS \t Unknown\n",
      "PREGON ORRICE OF ENERGY 0 \t Unknown\n",
      "COLORADO SCHOOL OF MINES \t 06-3660392418\n",
      "800.COM \t Unknown\n",
      "LASER WORLD \t Unknown\n",
      "DIRECTION ON MICROSOFT \t Unknown\n",
      "WASHINGTON PROF SYSTEMS \t Unknown\n",
      "WKU CASHIER'S OFFICE \t Unknown\n",
      "TECHNIPORT INC \t Unknown\n",
      "ADOLPHUS HOTEL (THE) \t 08-0080703339\n",
      "SOUTH BRANCH MARINA MAIN \t 06-3508298980\n",
      "CYBER PASS INC. \t Unknown\n",
      "THE NATL ASSOC ENV PROFS \t Unknown\n",
      "GTCO CORP \t Unknown\n",
      "NEW ORLEANS PUBLIC LIBRA0 \t Unknown\n",
      "TEKTRONIX-CPID \t 08-3508220490\n",
      "HYDROTECH INC \t Unknown\n",
      "GEONICS LIMITED \t Unknown\n",
      "CITY UNIVERSITY         0 \t Unknown\n",
      "COMPUTATIONAL HYDRAULICS \t Unknown\n",
      "J HISTOCHEM CYTOCHEM \t Unknown\n",
      "SEATTLE PUBLIC LIBRARY  0 \t Unknown\n",
      "MICRON GOVERNMENT SYS INC \t 08-3500849290\n",
      "EYRE BUS SERVICE INC \t Unknown\n",
      "THE CLERK,US DIST COURT 0 \t Unknown\n",
      "SHO-BAN NEWS            0 \t Unknown\n",
      "SHIP IT \t Unknown\n",
      "TEXAS WORKFACE COMMISSIO0 \t Unknown\n",
      "ST.LAWRENCE RIVER INST. \t Unknown\n",
      "ACCUSERV \t Unknown\n",
      "GRAINGER #900 \t Unknown\n",
      "RED WING SHOES 824 \t Unknown\n",
      "JOTO PAPER INC \t Unknown\n",
      "PURCHASE DEBIT \t Unknown\n",
      "MARK IT COMPANY         0 \t Unknown\n",
      "GRAINGER #645 \t 06-3666057873\n",
      "SCOTT RICE OF COLO DNVR \t 900009043280\n",
      "SHOES BY SHOES \t Unknown\n",
      "UPS                     0 \t Unknown\n",
      "BERGMAN PHOTOGRAPHIC SVCS \t Unknown\n",
      "CMMERCIAL OFFICE SYSTEMS \t Unknown\n",
      "ALL IN ONE SUPPLY \t 430800009670\n",
      "JONES COLLEGE CONNECTION \t Unknown\n",
      "TECH RENTALS \t 8000941124\n",
      "ENERGY MINES & RESOURCES \t Unknown\n",
      "RED WING SHOES 897 \t Unknown\n",
      "TIME EQUIPMENT COMPANY  0 \t Unknown\n",
      "EVENTS 4 U              0 \t Unknown\n",
      "THE PESTICIDES TRUST \t Unknown\n",
      "TEXAS WORKFORCE CONM    0 \t Unknown\n",
      "PAPER WAREHOUSE #101 \t 08-3500601377\n",
      "HINKLE METALS \t 8000952971\n",
      "GRAINGER #598 \t Unknown\n",
      "MASTER KEY & LOCK \t Unknown\n",
      "CU-ENVIRONALYSIS 2000 \t Unknown\n",
      "NELSON  AND  SONS  INC. \t Unknown\n",
      "MWI*MICRO WAREHOUSE \t 9918000409955\n",
      "Elsevier Science/Invoice \t Unknown\n",
      "GRAINGER #216 \t 06-3666055154\n",
      "GRAINGER #217 \t 06-3666055119\n",
      "CACHE RIVER PRESS \t Unknown\n",
      "LIBERTY MOUNTAIN SPORTS \t 08-9902905338\n",
      "ROYAL SOCIETY OF CHEMI \t Unknown\n",
      "MINERVA'S \t Unknown\n",
      "PROFESSIONAL LATINO ASSN0 \t Unknown\n",
      "UNISTRUT ALABAMA INC \t Unknown\n",
      "FRED MEYER GD      122 \t Unknown\n",
      "TRIATHLON MAPPING CORP  0 \t Unknown\n",
      "GOLDEN RULE PLASTICS \t 08-0069491474\n",
      "SEFAR AMERICA \t 08-0069490845\n",
      "FRANCIS AUDIO VISUAL \t Unknown\n",
      "MARK-IT COMPANY         0 \t Unknown\n",
      "WA STATE RECYCLING ASS \t Unknown\n",
      "ASMS                    0 \t Unknown\n",
      "PROTLAND STATE UNIVERSIT0 \t Unknown\n",
      "GRAINGER #238 \t Unknown\n",
      "SMI (DISTRIBUTION) \t Unknown\n",
      "ATLANTA FEDERAL CENTER \t 8000809392\n",
      "NATL CONFERENCE OF ST LE0 \t Unknown\n",
      "JCH WIRE & CABLE \t 08-0969383335\n",
      "SOCIETY FOR IN VITRO B \t Unknown\n",
      "WEBTRENDS CORP \t Unknown\n",
      "MEAD LUMBER DO-IT CENTER \t 08-3100006681\n",
      "CONFEDERATED TRIBES OF S0 \t Unknown\n",
      "ALLISON-ERWIN COMPANY 2 \t Unknown\n",
      "CINCINNATI BUSINESS SUPPL \t 900009061645\n",
      "GRAINGER #152 \t 06-3666055833\n",
      "HYATT REGENCY CRYSTAL CI0 \t Unknown\n",
      "REPROGRAPHICS TECH. \t 600660003425\n",
      "CONGREX HOLLAND BV \t Unknown\n",
      "BALTIMORE SOUND ENGINEER \t Unknown\n",
      "GLENS FALLS NWSPAPER \t Unknown\n",
      "I A G L R \t Unknown\n",
      "THE BRITISH LIBRARY \t Unknown\n",
      "GAZETTE NEWSPAPERS \t Unknown\n",
      "CONFEDERIED TRIBES OF TH0 \t Unknown\n",
      "AIA SEATTLE             0 \t Unknown\n",
      "RJ LEE INSTRUMENTS LTD \t Unknown\n",
      "CONFEDERATED TRIBES OF T0 \t Unknown\n",
      "INN AT SPANISH HEAD \t Unknown\n",
      "ALL SEATING CORP \t Unknown\n",
      "IHS ENVIRONMENTAL INFORMA \t Unknown\n",
      "EARTH AND  SPIRIT COUNCI0 \t Unknown\n",
      "LEHNERT'S OFFICE SUPPLY \t 4012021849721\n",
      "TRANSFER FRAUD PURCHASE B \t Unknown\n",
      "GLEN PRICE \t Unknown\n",
      "ACE-KAUFFMAN STAMP & S \t 06-0060738311\n",
      "MTS SYSTEMS CORPORATIO \t Unknown\n",
      "CONFEDERATED TRIBES     0 \t Unknown\n",
      "PENNANGALAN DREAMS \t 8457057083814\n",
      "COTTON ON CLOTHING \t Unknown\n",
      "FOOTWORKS CINCY \t Unknown\n",
      "BON SECOUR FISHERIES \t 8000972444\n",
      "TEKTRONIX-CPID CREDIT \t 08-3508220482\n",
      "CENTER STAGE \t Unknown\n",
      "COLOREL BLINDS BROADWAY \t Unknown\n",
      "BUSCA, INC. \t 3910061200892\n",
      "CELLULAR ONE           8 \t Unknown\n",
      "JIM JOHNSON PHOTOGRAPHY \t 5384006666848\n",
      "GOC-CCOHS \t Unknown\n",
      "ACCURATE REPORTING SERVIC \t Unknown\n",
      "BOISE BLUE ART SUPPLY \t Unknown\n",
      "BUCHANAN AIR INC \t Unknown\n",
      "STAR CANYON #5800 \t Unknown\n",
      "IBILL  *www.ibillCS.com \t 8008100680006\n",
      "SENSORS & SOFTWARE INC \t Unknown\n",
      "OSU OFFICE OF INTENAT'L \t Unknown\n",
      "SECRETARY OF STATE      0 \t Unknown\n",
      "ASIAN RESOURCE CENTER   0 \t Unknown\n",
      "MATKA-EKSPERTIT OY/PO \t Unknown\n",
      "PALM GEAR HQ \t Unknown\n",
      "TRANSFER VALID INDIVIDUAL \t Unknown\n",
      "8886538103 PREFBUYERRE \t Unknown\n",
      "WEISCO INC \t Unknown\n",
      "MAARACHOT YEUL LTD \t Unknown\n",
      "SINGELTON'S RV SALVAGE  0 \t Unknown\n",
      "PORTLAND GROUP \t 08-9908386333\n",
      "J WILEY &SONS LD DOLLA \t Unknown\n",
      "GRAINGER PARTS OPR \t 06-3660362210\n",
      "JAMESTOWN AREA LABOR MGT \t Unknown\n",
      "SEMINARS INTERNATIONAL \t Unknown\n",
      "GANDER MOUNTAIN \t 08-0004742062\n",
      "OLF CONFERENCE          0 \t Unknown\n",
      "CITY OF DALLAS PUBLIC LI0 \t Unknown\n",
      "UMEA UNIVERSITY ENVIROME0 \t Unknown\n",
      "MEDICAL RESEARCH C \t Unknown\n",
      "SHEL/DON REPRODUCTIONS \t 08-0004001625\n",
      "FORD VIDEO \t Unknown\n",
      "CLERK'S SALARY FUND     0 \t Unknown\n",
      "MENARDS 3054 \t 08-3500824938\n",
      "EVENTS UNLIMITED \t Unknown\n",
      "ANDRUS CENTER FOR PUBLIC \t Unknown\n",
      "GLOBAL COMPUTER SUPPLY \t 8915600680068\n",
      "ENV SERVICES INC. \t 08-3508726566\n",
      "PROMEGA CORPORATION \t 08-3508358817\n",
      "BALABAN SCIENCE SERVICE \t Unknown\n",
      "NM SECR OF STATE        0 \t Unknown\n",
      "CFC                     0 \t Unknown\n",
      "MAJIC ENTERPRISE, INC   0 \t Unknown\n",
      "STI CO INDUSTRIES \t Unknown\n",
      "INTERNATIONAL PAPERFLOW \t Unknown\n",
      "EXECUTIVE PRESENTATION \t Unknown\n",
      "MENARDS 3074 \t Unknown\n",
      "US DOC                  0 \t Unknown\n",
      "TECHNOLOGY SERVICES \t Unknown\n",
      "CABLE NET \t 6177800009748\n",
      "GRAINGER #648 \t Unknown\n",
      "TEKTRONIX INC       0-91 \t Unknown\n",
      "ELSEVIER SCIENCE LTD \t Unknown\n",
      "COMMAND TECHNOLOGY CORPOR \t Unknown\n",
      "QUESTER TANGENT CORP \t Unknown\n",
      "MINING JOURNAL LTD/DOL \t Unknown\n",
      "RJBTELCOM WWW.FSX2.COM \t 8317600900099\n",
      "COSTCO WHOLESALE        0 \t Unknown\n",
      "MUTHEN & MUTHEN \t Unknown\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "POWER TRANS PRDCT INC \t Unknown\n",
      "BOOT HEEL \t Unknown\n",
      "MANAGEMENT COMMUNICATION \t Unknown\n",
      "WERTZ BROTHERS \t Unknown\n",
      "GENOX CORPORATION \t Unknown\n",
      "SYSTEMS DESIGN CORP \t Unknown\n",
      "B R E LTD \t Unknown\n",
      "MENARDS 3109 \t 08-3500824264\n",
      "APPLIED ERGONOMICS INC \t Unknown\n",
      "CHROMATOGRAPHY TECHNOLOGY \t 08-0069488248\n",
      "SATLANTIC INC \t Unknown\n",
      "FAMILYGENTIX LTD \t Unknown\n",
      "ARGUS PACIFIC           0 \t Unknown\n",
      "UC REGENTS              0 \t Unknown\n",
      "NATIONAL OFFICE SYSTEM \t Unknown\n",
      "NORTHWEST TROPHY MO TO \t Unknown\n",
      "MONTGOMERY SEED \t Unknown\n",
      "GRAINGER #597 \t Unknown\n",
      "THE MAP STORE \t Unknown\n",
      "MBS WIRELESS            0 \t Unknown\n",
      "SOCIETY FOR DERMATOLOGY \t Unknown\n",
      "SPOKESMAN REVIEW \t Unknown\n",
      "TRIPOD DATA SYSTEMS INC \t 08-0906349339\n",
      "DALLAS PISTOL CLUB INC  0 \t Unknown\n",
      "ROSES CONTRACTING AND CON \t Unknown\n",
      "SPRI SHOP/PIC LIB \t Unknown\n",
      "GOC-NRCC CISTI CNRC \t Unknown\n",
      "OCDE PUBLICATIO 4219303 \t Unknown\n",
      "BELTWAY EXPRESS COMMUNICA \t Unknown\n",
      "PROTECH BOOKS \t 4620009463157\n",
      "TOTAL BUILDING SERVICE \t Unknown\n",
      "INST OF TERR ECOLOGY \t Unknown\n",
      "BERNEY, INC. \t Unknown\n",
      "GOC-NRC-INMS \t Unknown\n",
      "WWW LAPTOP OUTLET COM \t Unknown\n",
      "LA DOTD                 0 \t Unknown\n",
      "KING COUNTY LAW LIBRARY 0 \t Unknown\n",
      "CMHC PUBLICATIONS \t Unknown\n",
      "DISTRIBUTION LIVRES UN \t Unknown\n",
      "NC DMV                  0 \t Unknown\n",
      "ALUMON LAB LTD \t Unknown\n",
      "LIGHT INC \t Unknown\n",
      "UNIVERSITY PRESS \t Unknown\n",
      "NEVADA LEARNING SERIES IN \t Unknown\n",
      "UVA CONFERENCE OFFICE \t Unknown\n",
      "EXIT O DIGITAL SYSTEMS \t Unknown\n",
      "AGRICULTURE FOOD & RUR \t Unknown\n",
      "ADVANCED ELECTRONICS \t Unknown\n",
      "THE TRANS.RESEARCH BRD \t 600020014619\n",
      "NELSON CHIPMAN QUIGLYAND0 \t Unknown\n",
      "IEA COAL RESEARCH \t Unknown\n",
      "CTY OF SEATTLE          0 \t Unknown\n",
      "PC HELP \t Unknown\n",
      "THE SKANNER             0 \t Unknown\n",
      "APPLIED IND TECH  0239 \t 900009001871\n",
      "MERCHANDISE MART PROP IN0 \t Unknown\n",
      "REGULATORY COMPLIANCE   0 \t Unknown\n",
      "MICHIGAN MUNICIPAL LEAGUE \t Unknown\n",
      "CANADIAN CENTRE FOR P2 \t Unknown\n",
      "ALPHA COMMUNICATIONS \t Unknown\n",
      "CENTRL WASH UNIV        0 \t Unknown\n",
      "INTERSOL \t Unknown\n",
      "FEEDBACK VIDEO \t Unknown\n",
      "BRECHBUHLER SCALES \t Unknown\n",
      "ANDY ROSANE             0 \t Unknown\n",
      "FORCE THREE \t Unknown\n",
      "HERON PUBLISHING \t Unknown\n",
      "S.C. DMV                0 \t Unknown\n",
      "CARBONIC PRODUCTS \t Unknown\n",
      "ISSX \t Unknown\n",
      "KING CO LAW LIBRARY     0 \t Unknown\n",
      "STRATEGIC COMPUTERS \t 3910060300860\n",
      "FUTURE PUBLISH LTD \t Unknown\n",
      "M LEE SMITH PUBLISHERS \t 810091562\n",
      "HP ELEMEN SCHOOL        0 \t Unknown\n",
      "SEQUITER SOFTWARE INC \t Unknown\n",
      "C & N MANUFACTURING \t Unknown\n",
      "WORDSUCCESS LLC \t Unknown\n",
      "QUEBEC 2000 \t Unknown\n",
      "ADVANCED BATTERY SYSTEMS \t Unknown\n",
      "NOTARIAL ARCHIVES RESRCH0 \t Unknown\n",
      "ICI DULUX PAINT 02624 \t Unknown\n",
      "UNIVERSITY OF WASHINGTON0 \t Unknown\n",
      "MINNESOTA CHROMATOGRAPHY0 \t Unknown\n",
      "WIRELESS COMMUNICATIONS \t 8124806660662\n",
      "OHIO RIVER BASIN CONSORT0 \t Unknown\n",
      "WTEC #2 \t Unknown\n",
      "FOUNDATION WATER RESEA \t Unknown\n",
      "21ST CENTURY EXPO. GROUP \t Unknown\n",
      "SHERIFFS OPNS CTR       0 \t Unknown\n",
      "INTERMEXICO \t Unknown\n",
      "AMTRAK      5547105905114 \t 1647605305664\n",
      "THE MURAWSKI GROUP \t Unknown\n",
      "GULF BREEZE TIRE CENTRE \t Unknown\n",
      "AGENT FEE   8908111473613 \t 08-0030000911\n",
      "EAST BATON ROUGE CLERK O0 \t Unknown\n",
      "US PS                   0 \t Unknown\n",
      "BRENDLE INC \t Unknown\n",
      "WATERLOO MAPLE INC \t Unknown\n",
      "WESCOR INC \t Unknown\n",
      "SOUTHEASTERN SIGHT/SOUND \t 3910067700981\n",
      "RSEWEB.COM 8774054986 \t 8317600900067\n",
      "AHMA DIRECTORIES \t Unknown\n",
      "RED WING SHOES 845 \t Unknown\n",
      "STRAUSS PHOTO TECH SVC \t Unknown\n",
      "DAY WIRELESS SYSTEMS \t Unknown\n",
      "DIVISION OF MOTOR       0 \t Unknown\n",
      "THE HERALD NEWS INC \t Unknown\n",
      "WHO'S WHO/PUBLIC SECTOR \t Unknown\n",
      "AIR & WASTE MGMT ASSOC \t 490900829225\n",
      "NATIONAL OFFICE SYSTEMS \t Unknown\n",
      "FSHRSCI*LAB SUPPLIES \t 4094000906634\n",
      "INDUSTRIAL WELDING SUPPLY \t 400004051522\n",
      "ZDU.COM WEB PURCHASES \t Unknown\n",
      "ONVIA.COM \t 2308000913878\n",
      "CONVENIENCE CHECK #     0 \t Unknown\n",
      "INTL CRITICAL INCIDENT ST \t Unknown\n",
      "NORTH CAROLINA A  AND  T \t Unknown\n",
      "RED WING SHOE STORE 187 \t 600000652870\n",
      "ST ANDREW'S HOUSE       0 \t Unknown\n",
      "INTELLIGENT BATTERIES \t Unknown\n",
      "GREAT LAKES COMPUTER SRCE \t Unknown\n",
      "LUKE HEATING AND A C \t Unknown\n",
      "TYPEWRITER SERVICEMAN \t Unknown\n",
      "WWW.SOFT-SHOP.COM \t Unknown\n",
      "OFFICE OF ADMIN RULES \t Unknown\n",
      "COMMERICAL COPY SERV \t Unknown\n",
      "DUDEN ENTERPRISES INC \t Unknown\n",
      "BAY AREA RESTORATION C \t Unknown\n",
      "F B A \t Unknown\n",
      "RITTENHOUSE BOOK DIST INC \t 6066565824\n",
      "WILDLIFE MANAGEMENT IN \t Unknown\n",
      "AUDIO VISUAL SERVICES \t 3910065100000\n",
      "WOODS BY THE CREEK      0 \t Unknown\n",
      "UNITED SOLAR TINTING INC \t Unknown\n",
      "SAFETY EQUIPMENT COMPANY \t 967616940333\n",
      "FLAIR MANAGEMENT        0 \t Unknown\n",
      "LANCE OVIATT            0 \t Unknown\n",
      "LITRONIC INDUSTRIES \t Unknown\n",
      "BELLINGHAM PUBLIC       0 \t Unknown\n",
      "SKIFTELECOM \t Unknown\n",
      "U S POST MASTER         0 \t Unknown\n",
      "RENT-RITE \t Unknown\n",
      "RE LEGETTE CO           0 \t Unknown\n",
      "MULTNOMAH COUNTY RECORDE0 \t Unknown\n",
      "GEM DUGOUT              0 \t Unknown\n",
      "WWW.WEB.NET/ GREENTEA \t Unknown\n",
      "WASHINGTON SECRETARY OF 0 \t Unknown\n",
      "LINCOLN AMRICAN RED CROSS \t Unknown\n",
      "J. SPARGO  AND  ASSOCIATE \t Unknown\n",
      "LA TABAGIE #2804 \t Unknown\n",
      "KUCK  AND  ASSOCIATES INC \t Unknown\n",
      "HATTIESBURG AMERICAN \t Unknown\n",
      "JAN DISIGNS             0 \t Unknown\n",
      "BEACON OCCUPATIONAL     0 \t Unknown\n",
      "FAYETTEVILLE STATE UNI \t Unknown\n",
      "Q R S LTD \t Unknown\n",
      "AMERICAN RED CROSS \t 6887894786897\n",
      "ENVRNMNTL LAW EDUCATN CTR \t 3910061800655\n",
      "COMPONENTSOURCE \t Unknown\n",
      "BANCTEC/DELL REPAIR \t 6029900935299\n",
      "H.A.P.C.O.A. \t 6177800006024\n",
      "DAVIS OIL COMPANY \t Unknown\n",
      "CAB INTERNATIONAL \t Unknown\n",
      "TANGIPAHOA CLERKOFCOURT 0 \t Unknown\n",
      "LIFE SCIENCE PRODUCTS INC \t Unknown\n",
      "ECOPOINT/DIESELNET INC \t Unknown\n",
      "DIVISION OF MOTOR VEHICL0 \t Unknown\n",
      "OVATION DIGITAL PRODUCT. \t Unknown\n",
      "PUGET SOUND COACH LINES 0 \t Unknown\n",
      "X ATM CASH ADVANC \t Unknown\n",
      "LAKE'S ENVIRONMENTAL \t Unknown\n",
      "CITIZENS ENVIRONMENTAL \t Unknown\n",
      "SUPERIOR OFFICE INSTALLA0 \t Unknown\n",
      "CHARITON VALLEY RC & D  0 \t Unknown\n",
      "STRATO REPROGRAPHIX INC. \t Unknown\n",
      "IAM                     0 \t Unknown\n",
      "DIAGNOSTIC SYSTEMS INC \t 3910065406803\n",
      "TOWN HALL               0 \t Unknown\n",
      "TELE-COMMUNICATIONS     0 \t Unknown\n",
      "WHATCOM SECURITY AGENCY 0 \t Unknown\n",
      "SEATTLE AQUARIUM        0 \t Unknown\n",
      "FOLKEHELSA              0 \t Unknown\n",
      "MARK BENTLEY            0 \t Unknown\n",
      "SHOWCASE INC \t Unknown\n",
      "FRANKLIN PLASTICS       0 \t Unknown\n",
      "U S POSTMASTER          0 \t Unknown\n",
      "ECOS                    0 \t Unknown\n",
      "COSTCO                  0 \t Unknown\n",
      "FIRSTCLASS SYSTEMS \t Unknown\n",
      "TOTEM MARINA            0 \t Unknown\n",
      "GUMBALL/CELLWEST .COM \t Unknown\n",
      "THE DAILY NEWS PUBLI \t Unknown\n",
      "SUPERIOR OFFICE INSTALL 0 \t Unknown\n",
      "JOHN WILEY & SONS \t Unknown\n",
      "CONTINENTAL EXPOSITION \t 810082075\n",
      "AMERICAN MOBILE SHREDDIN0 \t Unknown\n",
      "BROADVIEW PRESS LTD \t Unknown\n",
      "CADDO PURISH TAX ASSESSO0 \t Unknown\n",
      "SCHC                    0 \t Unknown\n",
      "U S DEPARTMENT OF COMMER0 \t Unknown\n",
      "ACE SUPPLY INC \t Unknown\n",
      "APPLIED IND TECH  0783 \t Unknown\n",
      "MEDIA EDGE COMMUNICATIONS \t Unknown\n",
      "EARL OWEN COMPANY       0 \t Unknown\n",
      "NC ST UNIV              0 \t Unknown\n",
      "US POSTMANSTER          0 \t Unknown\n",
      "WILLIAM CRIDDLE PHD     0 \t Unknown\n",
      "CLERK OF COASTS         0 \t Unknown\n",
      "US POSTAL SERVICE       0 \t Unknown\n",
      "KOMOTV                  0 \t Unknown\n",
      "ALASKA NATIVE HEALTH BOA0 \t Unknown\n",
      "AMERICAN COLLEGE TOXICOLY \t Unknown\n",
      "DUKE MED CTR BOOKSTORE \t Unknown\n",
      "PORTLAND PRESS \t Unknown\n",
      "INNOTRAC CORPORATION \t 8000803961\n",
      "FEDERAL EXPRESS         0 \t Unknown\n",
      "REFINING PROCESS SERVICE0 \t Unknown\n",
      "P AND M LOCKSMITH SERVICE \t Unknown\n",
      "WESTERN SUN             0 \t Unknown\n",
      "WESTERN S U N           0 \t Unknown\n",
      "KLUWER ACADEMIC \t Unknown\n",
      "HIGGINS & ASSOCIATIES   0 \t Unknown\n",
      "EZ PASS                 0 \t Unknown\n",
      "ALASKA NATIVE HEALTH    0 \t Unknown\n",
      "FOOTHILL CARDIOLOGY INC 0 \t Unknown\n",
      "ALASKA NATIVE HLTH BOARD0 \t Unknown\n",
      "BECKMAN COULTER CDA INC \t Unknown\n",
      "TEUFEL NURSERY  INC. \t Unknown\n",
      "SHOOTERS MERCANTILE \t Unknown\n",
      "A1 CAMPUS ELECTRONICS \t 900009091153\n",
      "NORTHWEST COURT REPORTER0 \t Unknown\n",
      "BARBARA KROHN&ASSOCIATES0 \t Unknown\n",
      "NATIONAL GEORGRAPHIC TV 0 \t Unknown\n",
      "SAN ANTONIO FOAM \t 8000861308\n",
      "DELTA NEWS              0 \t Unknown\n",
      "CLERK OF DISTRICT COURT 0 \t Unknown\n",
      "OREGON DPT OF EDUCATION 0 \t Unknown\n",
      "DELTA NEWS JOURNAL      0 \t Unknown\n",
      "NATIONAL GEOGRAPHIC TV L0 \t Unknown\n",
      "AMERICAN IMAGE DISPLAYS \t Unknown\n",
      "RICE INDUSTRIAL SUPPLY IN \t Unknown\n",
      "KRACKELER SCIENTIFIC \t 900009045549\n",
      "NATIONAL TONER TECHNOLOGY \t 08-3500080222\n",
      "DENVER INSTRUMENT CO \t 08-0000183860\n",
      "FM \t Unknown\n",
      "MANUGISTICS INC \t Unknown\n",
      "N.C. D M V              0 \t Unknown\n",
      "TEKTRONIX TRSRY SVC 99 \t Unknown\n",
      "TROUT UNLIMITED CANADA \t Unknown\n",
      "X VERTERE \t Unknown\n",
      "XFR F/VILLARREAL 13400883 \t Unknown\n",
      "XFR F/VILLARREAL 12637808 \t Unknown\n",
      "XFR TO RICE 120013402434 \t Unknown\n",
      "FREEMAN DECORATING LTD \t Unknown\n",
      "XFR F/SHABAZZ 86000001693 \t Unknown\n",
      "XFR TO SHABAZZ 0011143493 \t Unknown\n",
      "MTX INC \t Unknown\n",
      "TRUDEAN CRYSTAL \t Unknown\n",
      "XFR TO VILLARREAL 1340088 \t Unknown\n",
      "XFR F/RICE 120012319530 \t Unknown\n",
      "IDAHO CAMERA CO-ORCHARD \t 08-0500067336\n",
      "ST GERMAINS GLASS CO \t 08-3500017301\n",
      "O U P JOURNALS \t Unknown\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "IMPAC/TRI-COUNTY O.P. \t Unknown\n",
      "H.H. BARNUM COMPANY \t Unknown\n",
      "X EMBASSY SUITES \t Unknown\n",
      "FAMILIAN NW            9 \t Unknown\n",
      "X COMFORT INN-W \t Unknown\n",
      "X THE WINDSOR \t Unknown\n",
      "X PGH INTL AIRPOR \t Unknown\n",
      "THE SOLUTIONS GROUP \t Unknown\n",
      "X REVERSAL OF CR BAL REF \t Unknown\n",
      "HILL MANUFACTURING CO INC \t 4590640000581\n",
      "SOWA & NICHOLAS PRINTING \t Unknown\n",
      "LASER REGISTRATION INC. \t Unknown\n",
      "COSTEO WHOLESALE        0 \t Unknown\n",
      "US POSTMASTER           0 \t Unknown\n",
      "THE CAPITAL CONNECTION \t 08-0069498759\n",
      "AMERICAN METEOROLOGICAL S \t Unknown\n",
      "ELECTRON MICROSCOPY SC \t 08-0009499552\n",
      "THE PIPET PEOPLE \t Unknown\n",
      "SMALL BALANCE WRITE-OFF \t Unknown\n",
      "EXERCISE EQUIPMNT CTR #8 \t Unknown\n",
      "RADIO SHACK   00140731 \t 4800000640786\n",
      "DETECT SERVICES \t Unknown\n",
      "RADIO SHACK   00186049 \t Unknown\n",
      "PLANNER PADS CO \t Unknown\n",
      "WOOD WISE INTERIORS \t Unknown\n",
      "ED HI VAC 800-848-9800 \t 490901587222\n",
      "GELSIM SOFTWARE \t Unknown\n",
      "COUNTRY INN ANNAPOLIS \t 08-3500051559\n",
      "CRC LIMITED \t Unknown\n",
      "CC *PC MALL \t 8684000655290\n",
      "ENVIRONMENTAL SERVICES AS \t Unknown\n",
      "OSU COMPUTER BASE \t 997674936331\n",
      "PROFESS OFC ENTERPRISES \t 2603800006005\n",
      "HEWLETT PACKARD CO 2 \t Unknown\n",
      "QMAX SYSTEMS INC \t Unknown\n",
      "TROXLER ELECTRONIC LABORA \t Unknown\n",
      "GRAINGER #523 \t Unknown\n",
      "ACC RENTAL CENTER \t Unknown\n",
      "NEHA \t 06-3666707394\n",
      "IMPAC/TRI-COUNTY O P \t Unknown\n",
      "SCI*SCOPE COMMUNICATE \t 9764000715901\n",
      "AMERICAN PHYSIOL SOCIETY \t Unknown\n",
      "COMPU CAL \t Unknown\n",
      "PRODUCTIVITY PRESS \t 800008008563\n",
      "E LICHT COMPANY \t 08-3500865771\n",
      "SIENCO INSTRUMENT SERVICE \t Unknown\n",
      "I.A.C.P.                0 \t Unknown\n",
      "DEBIT ADJUSTEMNT \t Unknown\n",
      "BULBMAN INC \t 08-0968645332\n",
      "PETCON INC \t Unknown\n",
      "IACP                    0 \t Unknown\n",
      "GO AMERICA COMMUNICATIONS \t 06-3666151795\n",
      "CUTAWAY VIDEO CLIPS \t 841200006SDG6\n",
      "GRAINGER #632 \t Unknown\n",
      "AOAC INTERNATIONAL \t 253050503505\n",
      "RANDOL INTL LTD \t Unknown\n",
      "AMERICAN CHEMICAL SCTY \t 965610606332\n",
      "MCCARUS ADVERTISING \t Unknown\n",
      "BOISE PUBLIC SCHOOLS EDUC \t Unknown\n",
      "INT'L BUS. DIRECTORIES \t Unknown\n",
      "CRUACHEM INC \t 08-0069496693\n",
      "AMER WATER WORKS ASSN FIN \t 08-3500999175\n",
      "TECHNICAL GLASS PRODUCTS \t Unknown\n",
      "PRO PHOTO SUPPLY INC \t Unknown\n",
      "SHERATON HOTELS \t 8060633569800\n",
      "ROUTLEDGE SPON PUB \t Unknown\n",
      "R L DEPPMANN CO #2 \t Unknown\n",
      "FISHER SCI ATL \t 602608969534\n",
      "KARGER S AG \t Unknown\n",
      "NAT ACCTS #951 \t 8500035600006\n",
      "ADON EQUIPMENT CO INC. \t Unknown\n",
      "APPLIED IND TECH  0105 \t Unknown\n",
      "BIO-RAD LABORATORIES INC. \t 7995900690069\n",
      "DULUTH LAWN & SPORT \t Unknown\n",
      "DOUGLAS D. LEE 0D LTD \t Unknown\n",
      "U Z ENGINEERED PRODUCTS \t Unknown\n",
      "THE ANNISTON STAR \t 8000675787\n",
      "MENDELSON COMMERCIAL #145 \t Unknown\n",
      "LANE REGION AIR POLLUT \t Unknown\n",
      "FTS SYSTEMS INC \t Unknown\n",
      "GUESS RETAIL STORE #505 \t 4105550553366\n",
      "TNO MILIUE-ENERGIE EN PRO \t Unknown\n",
      "TAYLOR & FRANCIS \t 89200469295\n",
      "N.S. GEOGRAPHIC INFORMATI \t Unknown\n",
      "UNITED PARCEL SERVICE   0 \t Unknown\n",
      "COMMUNICATION SYSTEMS \t Unknown\n",
      "SPECTRUM PETROGRAPHICS \t Unknown\n",
      "SINCLAIR COLLBURSAR \t 900009065579\n",
      "GRAINGER #320 \t Unknown\n",
      "US POSMASTER            0 \t Unknown\n",
      "THE TRENTONIAN \t Unknown\n",
      "AGILENT \t 465094661332\n",
      "CUMMINS NORTHWEST INC  8 \t Unknown\n",
      "DEPT OF GEOLOGY    08007 \t 08-0000803337\n",
      "SCOTTS LOCK  AND  KEY \t Unknown\n",
      "MOBIL         01261635 \t 100006916185\n",
      "MOBIL         05833488 \t 100005288422\n",
      "MOBIL         01485895 \t 100006425235\n",
      "MOBIL         07627227 \t 100007197997\n",
      "IOTECH INC \t 900009003662\n",
      "CLACKAMAS COMM COLL-CSHR \t 08-9610346333\n",
      "CID \t Unknown\n",
      "MOBIL         01572874 \t 100006579274\n",
      "BELLEVUE COMM COLLEGE #1 \t Unknown\n",
      "B C E \t Unknown\n",
      "CATCHWORD LTD \t Unknown\n",
      "SURE SHRED \t Unknown\n",
      "CELLULAR ONE           7 \t Unknown\n",
      "POWERON COMPUTER SERVICES \t Unknown\n",
      "NORTHERN HYDRAULICS \t 3108000265773\n",
      "JC SENTRY          S6D \t Unknown\n",
      "OFFICE DEPOT #1082 \t 4503082476300\n",
      "STAPLES #941 \t 9108234610000\n",
      "VISCAL INC \t 900009049280\n",
      "FRANKLIN COVEY COMPANY \t 2094330000009\n",
      "A & A TOPPER SALES \t 08-3508727220\n",
      "SNAP SHOTS 23 \t Unknown\n",
      "ABS GRP INC/GOVT INST DIV \t 6692800687085\n",
      "GREEN TEACHER \t Unknown\n",
      "TDA INC \t Unknown\n",
      "JELINEK CORK LIMITED \t Unknown\n",
      "TOMORROW MAGAZINE \t Unknown\n",
      "GRAINGER #143 \t Unknown\n",
      "LIFT TRUCK CORP \t Unknown\n",
      "AM RED CROSS-MID VLY CHP \t Unknown\n",
      "OCEAN VOICE INT'L INC \t Unknown\n",
      "HOLIDAY INNS HTL & ST HS \t 8060639567660\n",
      "NATIONAL GEOGRAPHIC INC 0 \t Unknown\n",
      "SINCLAIR COLLBKSTR \t 900009065578\n",
      "SOUTHERN RHODE ISLAND NEW \t Unknown\n",
      "EXIT 0 DIGITAL SYSTEMS \t Unknown\n",
      "SPEEDHOOK SPECIALISTS INC \t Unknown\n",
      "METRO MEDIA NEWS MONITOR0 \t Unknown\n",
      "COMPRESSOR PARTS & SERVIC \t Unknown\n",
      "DISCOVERY COMMUNICATIONS0 \t Unknown\n",
      "OREGON ENVIRONMENTAL COUN \t Unknown\n",
      "SABINE PARISH CLERK     0 \t Unknown\n"
     ]
    }
   ],
   "source": [
    "# print to see\n",
    "# replace with the majority element\n",
    "for des in missing_merchnum['Merch description'].unique():\n",
    "    arr = data.loc[(data['Merch description']==des)&(data.Merchnum!='Unknown')&(data.Merchnum!='0')]['Merchnum'].tolist()\n",
    "    mode = majorityElement(arr)\n",
    "    print(des,'\\t',mode)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "reserved-opinion",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 27.4 s, sys: 50.3 ms, total: 27.5 s\n",
      "Wall time: 27.5 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# replace with the majority element\n",
    "for des in missing_merchnum['Merch description'].unique():\n",
    "    arr = data.loc[(data['Merch description']==des)&(data.Merchnum!='Unknown')&(data.Merchnum!='0')]['Merchnum'].tolist()\n",
    "    mode = majorityElement(arr)\n",
    "    data.loc[(data['Merch description']==des)&(data.Merchnum=='Unknown'),'Merchnum']= mode\n",
    "    data.loc[(data['Merch description']==des)&(data.Merchnum=='0'),'Merchnum']= mode\n",
    "    #     print(des,'\\t',mode)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "nearby-treatment",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2449"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# after filling\n",
    "len(data.loc[(data.Merchnum=='Unknown')|(data.Merchnum=='0')])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "neither-interaction",
   "metadata": {},
   "source": [
    "### Merch state"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "adverse-cruise",
   "metadata": {},
   "source": [
    "Fill missing merch state first \n",
    "\n",
    "1) based on `merch zip` (mode), last \n",
    "\n",
    "2) based on `merch description` (mode)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "australian-controversy",
   "metadata": {},
   "source": [
    "Fill missing merch state based on Merch description and Merchnum."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "assigned-vision",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "TN     12035\n",
       "VA      7872\n",
       "CA      6817\n",
       "IL      6508\n",
       "MD      5398\n",
       "       ...  \n",
       "387        1\n",
       "654        1\n",
       "924        1\n",
       "415        1\n",
       "314        1\n",
       "Name: Merch state, Length: 228, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['Merch state'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "aging-bradford",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1195"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# before filling\n",
    "sum(data['Merch state']=='Unknown')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "medieval-israeli",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unknown \t VA\n",
      "926.0 \t Unknown\n",
      "929.0 \t Unknown\n",
      "1400.0 \t Unknown\n",
      "65132.0 \t Unknown\n",
      "86899.0 \t Unknown\n",
      "23080.0 \t Unknown\n",
      "60528.0 \t Unknown\n",
      "934.0 \t Unknown\n",
      "902.0 \t Unknown\n",
      "738.0 \t Unknown\n",
      "90805.0 \t Unknown\n",
      "76302.0 \t Unknown\n",
      "9.0 \t CA\n",
      "914.0 \t Unknown\n",
      "6.0 \t VA\n",
      "95461.0 \t Unknown\n",
      "50823.0 \t Unknown\n",
      "2.0 \t MD\n",
      "48700.0 \t Unknown\n",
      "680.0 \t Unknown\n",
      "1.0 \t PA\n",
      "681.0 \t Unknown\n",
      "623.0 \t Unknown\n",
      "726.0 \t Unknown\n",
      "936.0 \t Unknown\n",
      "12108.0 \t Unknown\n",
      "791.0 \t Unknown\n",
      "907.0 \t Unknown\n",
      "922.0 \t Unknown\n",
      "920.0 \t Unknown\n",
      "3.0 \t GA\n",
      "801.0 \t Unknown\n",
      "8.0 \t CO\n",
      "31040.0 \t Unknown\n",
      "38117.0 \t TN\n",
      "41160.0 \t Unknown\n"
     ]
    }
   ],
   "source": [
    "# print to check\n",
    "missing_merchstate = data.loc[data['Merch state']=='Unknown']\n",
    "for zipcode in missing_merchstate['Merch zip'].unique():\n",
    "    arr = data.loc[(data['Merch zip']==zipcode)&(data['Merch state']!='Unknown')]['Merch state'].tolist()\n",
    "    mode = majorityElement(arr)\n",
    "    print(zipcode,'\\t',mode)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "broken-presentation",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 578 ms, sys: 4.39 ms, total: 582 ms\n",
      "Wall time: 582 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# replace\n",
    "for zipcode in missing_merchstate['Merch zip'].unique():\n",
    "    arr = data.loc[(data['Merch zip']==zipcode)\n",
    "                    &(data['Merch state']!='Unknown')]['Merch state'].tolist()\n",
    "    mode = majorityElement(arr)\n",
    "    data.loc[(data['Merch zip']==zipcode)&(data['Merch state']=='Unknown'),'Merch state']= mode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "bearing-request",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "67"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# after filling based on merch state\n",
    "sum(data['Merch state']=='Unknown')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "otherwise-probe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2.53 s, sys: 6.24 ms, total: 2.54 s\n",
      "Wall time: 2.54 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# replace with description mode\n",
    "for des in missing_merchstate['Merch description'].unique():\n",
    "    arr = data.loc[(data['Merch description']==des)&(data['Merch state']!='Unknown')]['Merch state'].tolist()\n",
    "    mode = majorityElement(arr)\n",
    "    data.loc[(data['Merch description']==des)&(data['Merch state']=='Unknown'),'Merch state']= mode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "popular-definition",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "66"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# after filling based on merch description\n",
    "sum(data['Merch state']=='Unknown')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "divine-yesterday",
   "metadata": {},
   "source": [
    "### Merch zip"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "stopped-rochester",
   "metadata": {},
   "source": [
    "Fill missing merch zip \n",
    "\n",
    "1) based on merch num (mode)\n",
    "\n",
    "2) then based on merch description (mode)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "former-insulin",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4656"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# before filling\n",
    "sum(data['Merch zip']=='Unknown')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "extensive-trauma",
   "metadata": {},
   "outputs": [],
   "source": [
    "missing_merchzip = data.loc[data['Merch zip']=='Unknown']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "mediterranean-business",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5000006000095 \t Unknown\n",
      "8053478940091 \t 26003.0\n",
      "Unknown \t 21045.0\n",
      "2000049710067 \t Unknown\n",
      "6700046420068 \t 60604.0\n",
      "6005030600003 \t 45750.0\n",
      "4900000004673 \t 60134.0\n",
      "8849060070074 \t 27603.0\n",
      "1700000096481 \t Unknown\n",
      "6100020004006 \t 20401.0\n",
      "9900020006406 \t 22202.0\n",
      "6176269 \t Unknown\n",
      "9900020008506 \t Unknown\n",
      "5090345469809 \t 2852.0\n",
      "674906173338 \t Unknown\n",
      "7300020006306 \t 64131.0\n",
      "679960185332 \t Unknown\n",
      "3000003830064 \t Unknown\n",
      "6100020006106 \t 20402.0\n",
      "938909498221 \t Unknown\n",
      "4900000004985 \t Unknown\n",
      "490908141220 \t Unknown\n",
      "7284500097374 \t 64120.0\n",
      "900020008906 \t Unknown\n",
      "6700047350063 \t Unknown\n",
      "6100020008906 \t 20402.0\n",
      "2094330000009 \t 84119.0\n",
      "36987734 \t Unknown\n",
      "490908191229 \t Unknown\n",
      "138900042336 \t Unknown\n",
      "9900000037306 \t 30268.0\n",
      "3000000000062 \t 74004.0\n",
      "9900000036906 \t 22150.0\n",
      "5847050676706 \t 48375.0\n",
      "4700050550066 \t Unknown\n",
      "9900000034106 \t 19107.0\n",
      "9900000029606 \t 60482.0\n",
      "3100072100069 \t 10007.0\n",
      "465905123335 \t Unknown\n",
      "9900000031606 \t 95203.0\n",
      "9900000030406 \t 98001.0\n",
      "9900000024706 \t 76115.0\n",
      "4700020002606 \t 60604.0\n",
      "8692066781517 \t 60069.0\n",
      "3500003580804 \t Unknown\n",
      "965905099338 \t Unknown\n",
      "674966041338 \t Unknown\n",
      "9264600069917 \t 98133.0\n",
      "9900000027006 \t 80225.0\n",
      "6600020064006 \t Unknown\n",
      "6518665000576 \t 30336.0\n",
      "674903752337 \t Unknown\n",
      "2200018570064 \t Unknown\n",
      "7700019650061 \t Unknown\n",
      "500020081506 \t 80225.0\n",
      "500020002406 \t 57198.0\n",
      "2500020480867 \t Unknown\n",
      "9006005990615 \t Unknown\n",
      "9900020004806 \t 40511.0\n",
      "465614140337 \t 30309.0\n",
      "4500000000062 \t Unknown\n",
      "9341993300893 \t 10952.0\n",
      "R7071969 \t Unknown\n",
      "674969404338 \t Unknown\n",
      "8098605474995 \t 7030.0\n",
      "991900855333 \t Unknown\n",
      "9573149 \t Unknown\n",
      "7700072262081 \t Unknown\n",
      "3500000400092 \t Unknown\n",
      "3600000093145 \t Unknown\n",
      "2600019500069 \t Unknown\n",
      "8098060904788 \t 8648.0\n",
      "8900000000759 \t Unknown\n",
      "9227662806889 \t 93117.0\n",
      "481657967580 \t Unknown\n",
      "808960981331 \t Unknown\n",
      "8600000065643 \t Unknown\n",
      "465909215334 \t 7430.0\n",
      "8100020098806 \t Unknown\n",
      "700020006006 \t 20507.0\n",
      "60040698001 \t 73439.0\n",
      "2000085120061 \t Unknown\n",
      "8900000006686 \t 89109.0\n",
      "5859063805001 \t 64111.0\n",
      "604908627339 \t Unknown\n",
      "3900015550064 \t 30096.0\n",
      "7593860068556 \t 20020.0\n",
      "3100072200062 \t Unknown\n",
      "975960095221 \t Unknown\n",
      "9529000603145 \t 95492.0\n",
      "975903317229 \t Unknown\n",
      "990903450339 \t Unknown\n",
      "2200049170062 \t Unknown\n",
      "7539060000669 \t 96049.0\n",
      "990966429336 \t Unknown\n",
      "4000340609139 \t Unknown\n",
      "3000012330063 \t Unknown\n",
      "2200000007972 \t 64151.0\n",
      "604999408330 \t Unknown\n",
      "2000021670061 \t Unknown\n",
      "95890066 \t Unknown\n",
      "4300004400015 \t Unknown\n",
      "500020009906 \t Unknown\n",
      "4000356664805 \t Unknown\n",
      "7700065630060 \t Unknown\n",
      "8098605756118 \t 8551.0\n",
      "3200050240061 \t Unknown\n",
      "4800000613120 \t 48108.0\n",
      "6064704564 \t Unknown\n",
      "7700068460064 \t Unknown\n",
      "2094890001708 \t 84606.0\n",
      "3910067700981 \t 27603.0\n",
      "3900061400069 \t Unknown\n",
      "4800000690226 \t 20024.0\n",
      "90680069332 \t Unknown\n",
      "3910061200892 \t 14888.0\n",
      "76521/0376521 \t Unknown\n",
      "2600020006506 \t Unknown\n",
      "3500029123272 \t Unknown\n",
      "125900528338 \t Unknown\n",
      "977099454333 \t Unknown\n",
      "806679096339 \t Unknown\n",
      "9289194800075 \t 74361.0\n",
      "9608009606576 \t 27606.0\n",
      "2000074060062 \t Unknown\n",
      "138906791336 \t Unknown\n",
      "8425880087231 \t Unknown\n",
      "965904571333 \t Unknown\n",
      "938966966224 \t Unknown\n",
      "679966068337 \t Unknown\n",
      "720306361800 \t 2139.0\n",
      "8457033078615 \t Unknown\n",
      "991908148335 \t Unknown\n",
      "7700008760063 \t Unknown\n",
      "3600000036095 \t 1890.0\n",
      "935994818228 \t Unknown\n",
      "3200080280069 \t Unknown\n",
      "991904480331 \t Unknown\n",
      "4700046960064 \t Unknown\n",
      "679070007662 \t 20852.0\n",
      "7700049580064 \t Unknown\n",
      "604991715332 \t Unknown\n",
      "6822005250896 \t 90501.0\n",
      "6173325459 \t 99999.0\n",
      "3900025520067 \t Unknown\n",
      "2000076250060 \t Unknown\n",
      "9100000008932 \t 83704.0\n",
      "6009390680 \t Unknown\n",
      "8600090482109 \t Unknown\n",
      "5859060705064 \t 64105.0\n",
      "89200686316 \t 8873.0\n",
      "6006193633 \t Unknown\n",
      "4900000000979 \t 45219.0\n",
      "4400000716007 \t Unknown\n",
      "9006005910301 \t Unknown\n",
      "806960929334 \t Unknown\n",
      "9010574 \t Unknown\n",
      "997674930332 \t 97339.0\n",
      "8500066364265 \t 20151.0\n",
      "2094890001832 \t 84606.0\n",
      "89200497406 \t Unknown\n",
      "60076796684 \t 81601.0\n",
      "4700088760068 \t Unknown\n",
      "3000087700060 \t Unknown\n",
      "490963480226 \t 19111.0\n",
      "7300020004806 \t 22150.0\n",
      "490963580222 \t 19111.0\n",
      "4269000010976 \t 48106.0\n",
      "1918400693306 \t Unknown\n",
      "6822060156008 \t 92626.0\n",
      "4000080659799 \t Unknown\n",
      "6000099435 \t Unknown\n",
      "4518446813 \t Unknown\n",
      "6009085209 \t Unknown\n",
      "4000350821625 \t Unknown\n",
      "4006065565427 \t Unknown\n",
      "3910060406333 \t 21045.0\n",
      "8089334849839 \t 25362.0\n",
      "3000011310064 \t Unknown\n",
      "7593980000921 \t 27502.0\n",
      "8849060082221 \t 94063.0\n",
      "4274210775951 \t Unknown\n",
      "935946286222 \t Unknown\n",
      "499 \t Unknown\n",
      "3500000005166 \t Unknown\n",
      "9264000458639 \t 8837.0\n",
      "604991094330 \t Unknown\n",
      "6822092900353 \t 91343.0\n",
      "9918000409955 \t 8701.0\n",
      "3600000006071 \t 22301.0\n",
      "5847050050621 \t 48104.0\n",
      "9100020062406 \t 98104.0\n",
      "8098605472612 \t 7470.0\n",
      "6100020002606 \t 30309.0\n",
      "9227662060352 \t 93103.0\n",
      "935994481227 \t Unknown\n",
      "3900052730065 \t Unknown\n",
      "4006064898919 \t Unknown\n",
      "6497600064127 \t 80228.0\n",
      "4112000600958 \t 1760.0\n",
      "5859807765059 \t 66067.0\n",
      "22900642339 \t Unknown\n",
      "9600078130062 \t Unknown\n",
      "806900539332 \t Unknown\n",
      "8200000478088 \t 30601.0\n",
      "9933834930914 \t 48108.0\n",
      "674900174335 \t Unknown\n",
      "4400000753006 \t Unknown\n",
      "9264600000636 \t 63379.0\n",
      "9608007000647 \t 27514.0\n",
      "69178349945 0 \t Unknown\n",
      "4700054290064 \t Unknown\n",
      "4600020003906 \t Unknown\n",
      "7129011009306 \t Unknown\n",
      "5000009791 \t 79764.0\n",
      "7700091370069 \t Unknown\n",
      "5310062 \t Unknown\n",
      "9341690500608 \t 14623.0\n",
      "500020082606 \t 80225.0\n",
      "622905012335 \t Unknown\n",
      "8457057083814 \t Unknown\n",
      "6700077680066 \t Unknown\n",
      "29966/0129966 \t Unknown\n",
      "3500048158152 \t Unknown\n",
      "4000316607957 \t Unknown\n",
      "8849090000502 \t 92704.0\n",
      "4400051891648 \t Unknown\n",
      "674963329336 \t Unknown\n",
      "8100066400009 \t 52627.0\n",
      "1295060865028 \t 92618.0\n",
      "7593960001651 \t 55343.0\n",
      "3900037800061 \t 30308.0\n",
      "938090191224 \t Unknown\n",
      "253644704958 \t Unknown\n",
      "2644006024739 \t 20018.0\n",
      "604967514331 \t Unknown\n",
      "5859060123987 \t 64106.0\n",
      "910095506228 \t Unknown\n",
      "2200030730065 \t Unknown\n",
      "4400000757005 \t Unknown\n",
      "9006005805955 \t Unknown\n",
      "679070003488 \t 20785.0\n",
      "6378043 \t Unknown\n",
      "5090345464048 \t 2920.0\n",
      "2644006050005 \t 30097.0\n",
      "4500000281882 \t 76133.0\n",
      "9006005990206 \t Unknown\n",
      "8317600900099 \t 91372.0\n",
      "809982136339 \t Unknown\n",
      "700020052506 \t Unknown\n",
      "4000080053708 \t Unknown\n",
      "6000020050106 \t 20737.0\n",
      "5666020606134 \t 21758.0\n",
      "965904491337 \t Unknown\n",
      "490908741222 \t Unknown\n",
      "2900006986708 \t 34429.0\n",
      "607098900334 \t Unknown\n",
      "4900000004579 \t Unknown\n",
      "809946579333 \t 60610.0\n",
      "7700001010061 \t Unknown\n",
      "4500000291508 \t Unknown\n",
      "481657965376 \t Unknown\n",
      "6198070019188 \t 94804.0\n",
      "4000370367704 \t Unknown\n",
      "9813400001283 \t 85210.0\n",
      "7000370266278 \t Unknown\n",
      "6000807935 \t Unknown\n",
      "3000081650878 \t Unknown\n",
      "2600000061438 \t Unknown\n",
      "938907114220 \t Unknown\n",
      "9482685069165 \t 30318.0\n",
      "8100000009935 \t 60561.0\n",
      "47680062 \t Unknown\n",
      "9231662000626 \t 55441.0\n",
      "679908343339 \t Unknown\n",
      "9100000008689 \t 36109.0\n",
      "29253 \t Unknown\n",
      "900091851872 \t 30303.0\n",
      "3910065406803 \t 77598.0\n",
      "6170680898 \t 6897.0\n",
      "57720067 \t Unknown\n",
      "604906260336 \t Unknown\n",
      "4700091060067 \t 64015.0\n",
      "4006065513673 \t Unknown\n",
      "8350066 \t Unknown\n",
      "607990574338 \t Unknown\n",
      "5510000714782 \t Unknown\n",
      "8200000418933 \t 99352.0\n",
      "2000030660061 \t 28202.0\n",
      "7284720084825 \t 69162.0\n",
      "4600020043206 \t 30303.0\n",
      "7700000093145 \t 48197.0\n",
      "72458/0972458 \t Unknown\n",
      "9977060437000 \t 97330.0\n",
      "801999831337 \t Unknown\n",
      "7284720074769 \t 69162.0\n",
      "8200000856163 \t Unknown\n",
      "1647605305664 \t Unknown\n",
      "7534660089443 \t 84040.0\n",
      "100020961606 \t Unknown\n",
      "8100544800098 \t 2881.0\n",
      "5090344900929 \t 6512.0\n",
      "604995399331 \t Unknown\n",
      "4094000906634 \t 30024.0\n",
      "8800000007214 \t Unknown\n",
      "9600048650060 \t 48076.0\n",
      "9608009606764 \t 27513.0\n",
      "9600048640066 \t Unknown\n",
      "2200000007047 \t 8075.0\n",
      "481657988404 \t Unknown\n",
      "938908519220 \t 98121.0\n",
      "679076000647 \t 20705.0\n",
      "4240004409 \t Unknown\n",
      "6008934090 \t Unknown\n",
      "8500000085703 \t Unknown\n",
      "6100020080506 \t 20402.0\n",
      "9065066094500 \t 89121.0\n",
      "674901440335 \t Unknown\n",
      "5104065008106 \t 74063.0\n",
      "50002834602 \t Unknown\n",
      "100003321894 \t 77061.0\n",
      "9690523044618 \t 27502.0\n",
      "991908745332 \t Unknown\n",
      "3900053570060 \t Unknown\n",
      "3900087480064 \t Unknown\n",
      "3000038270069 \t Unknown\n",
      "6763 \t 30606.0\n",
      "9800067890066 \t Unknown\n",
      "9800015940069 \t Unknown\n",
      "6894264931242 \t 92506.0\n",
      "2200000007616 \t 80239.0\n",
      "679070007049 \t 20878.0\n",
      "621995115332 \t Unknown\n",
      "604900454333 \t Unknown\n",
      "808907343333 \t Unknown\n",
      "938903890227 \t Unknown\n",
      "5859970005350 \t 27713.0\n",
      "806968303333 \t Unknown\n",
      "679070001122 \t 21076.0\n",
      "8098605736374 \t 8837.0\n",
      "3600000097120 \t 48150.0\n",
      "4000080664034 \t Unknown\n",
      "60060605063 \t Unknown\n",
      "9400020005606 \t Unknown\n",
      "3100029000063 \t Unknown\n",
      "800022334003 \t Unknown\n",
      "5829006600466 \t 77380.0\n",
      "991908226336 \t Unknown\n",
      "5816000005611 \t 65109.0\n",
      "5859970005254 \t 74820.0\n",
      "9341803300689 \t 14850.0\n",
      "8000005864667 \t 98310.0\n",
      "679076000817 \t 21204.0\n",
      "7000000064063 \t Unknown\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6023678800627 \t Unknown\n",
      "3200040720067 \t Unknown\n",
      "6198070079140 \t 55344.0\n",
      "500020087806 \t Unknown\n",
      "3900037930063 \t Unknown\n",
      "6700017150060 \t Unknown\n",
      "679964780338 \t Unknown\n",
      "8900000004013 \t 98146.0\n",
      "9608004900903 \t 27703.0\n",
      "8098605560888 \t 45342.0\n",
      "9264000850687 \t 20147.0\n",
      "993960663224 \t Unknown\n",
      "6064135131 \t Unknown\n",
      "2200000002891 \t 97330.0\n",
      "6500080605309 \t 48084.0\n",
      "6700057350061 \t Unknown\n",
      "4700026530060 \t Unknown\n",
      "497188 \t Unknown\n",
      "7189060662870 \t 66603.0\n",
      "9600029510069 \t Unknown\n",
      "80675082 \t Unknown\n",
      "999966220339 \t Unknown\n",
      "6105600086977 \t 60025.0\n",
      "4274216553998 \t Unknown\n",
      "42763/0542763 \t Unknown\n",
      "1017691368905 \t Unknown\n",
      "800901922333 \t Unknown\n",
      "4109024004266 \t Unknown\n",
      "5740601106819 \t 23455.0\n",
      "4700063260063 \t Unknown\n",
      "34706/0834706 \t Unknown\n",
      "9608008000802 \t 27511.0\n",
      "604990447333 \t Unknown\n",
      "25906793334 \t Unknown\n",
      "8700000106860 \t Unknown\n",
      "601909990337 \t Unknown\n",
      "5800000091301 \t Unknown\n",
      "4673856331 \t 1810.0\n",
      "3910062600337 \t Unknown\n",
      "9813403909102 \t Unknown\n",
      "95581 \t 30308.0\n",
      "30631678 \t Unknown\n",
      "8100540009697 \t 3801.0\n",
      "700000007380 \t Unknown\n",
      "7700066290061 \t Unknown\n",
      "3900056430063 \t Unknown\n",
      "5800000063306 \t Unknown\n",
      "490901587222 \t 1887.0\n",
      "5800000093300 \t Unknown\n",
      "8684000655290 \t 90503.0\n",
      "5800000092170 \t Unknown\n",
      "5800000095487 \t Unknown\n",
      "481626968213 \t Unknown\n",
      "6000668451 \t 2822.0\n",
      "316801632 \t Unknown\n",
      "2603800006005 \t 22304.0\n",
      "5666020600024 \t 20003.0\n",
      "8500066303130 \t 20191.0\n",
      "9764000715901 \t 1532.0\n",
      "8849060087424 \t 2721.0\n",
      "2644006060269 \t 22151.0\n",
      "2200000004917 \t 89109.0\n",
      "7700020330068 \t Unknown\n",
      "6700061560062 \t Unknown\n",
      "9933834990905 \t 48116.0\n",
      "4600000000068 \t 19036.0\n",
      "253050503505 \t 20877.0\n",
      "965610606332 \t 20036.0\n",
      "991800083332 \t Unknown\n",
      "3500000000371 \t 55438.0\n",
      "7080800554000 \t 99645.0\n",
      "7400000008529 \t Unknown\n",
      "8500066307074 \t 22182.0\n",
      "678660009480 \t 17603.0\n",
      "604962159332 \t Unknown\n",
      "8500035600006 \t 30346.0\n",
      "3574900900732 \t 17606.0\n",
      "490900551224 \t Unknown\n",
      "300060300095 \t Unknown\n",
      "5859060105946 \t 64141.0\n",
      "4500000731143 \t Unknown\n",
      "604966610333 \t 68901.0\n",
      "89200469295 \t 19007.0\n",
      "7593980006526 \t Unknown\n",
      "1700000007877 \t Unknown\n",
      "490908225224 \t Unknown\n",
      "55899803 \t 60607.0\n",
      "456906760332 \t Unknown\n",
      "100006916185 \t Unknown\n",
      "100005288422 \t Unknown\n",
      "100006425235 \t Unknown\n",
      "60082085667 \t 32561.0\n",
      "100007197997 \t Unknown\n",
      "4227 \t Unknown\n",
      "100006579274 \t Unknown\n",
      "7700064260064 \t Unknown\n",
      "4600000000864 \t 30324.0\n",
      "2631600606301 \t 19123.0\n",
      "9341013368650 \t 14063.0\n",
      "2200000004514 \t 89119.0\n",
      "6518606055947 \t 30329.0\n",
      "604905195333 \t Unknown\n",
      "500000406062 \t Unknown\n",
      "3500000009711 \t 22202.0\n",
      "6692800687085 \t 20850.0\n",
      "9700000909101 \t Unknown\n",
      "2200000003097 \t 25401.0\n",
      "4269066000000 \t 27628.0\n",
      "CPU times: user 4.77 s, sys: 138 ms, total: 4.91 s\n",
      "Wall time: 4.92 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# print to check\n",
    "for num in missing_merchzip['Merchnum'].unique():\n",
    "    arr = data.loc[(data['Merchnum']==num)&(data['Merch zip']!='Unknown')]['Merch zip'].tolist()\n",
    "    mode = majorityElement(arr)\n",
    "    print(num,'\\t',mode)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "peaceful-plate",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 8.02 s, sys: 16.9 ms, total: 8.04 s\n",
      "Wall time: 8.04 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# replace based on merchnum\n",
    "for num in missing_merchzip['Merchnum'].unique():\n",
    "    arr = data.loc[(data['Merchnum']==num)&(data['Merch zip']!='Unknown')]['Merch zip'].tolist()\n",
    "    mode = majorityElement(arr)\n",
    "    data.loc[(data['Merchnum']==num)&(data['Merch zip']=='Unknown'),'Merch zip']= mode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "chubby-channel",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1102"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# after filling based on merchnum\n",
    "sum(data['Merch zip']=='Unknown')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "raised-sympathy",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 14.8 s, sys: 43.6 ms, total: 14.9 s\n",
      "Wall time: 14.9 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# replace based on merch description\n",
    "for des in missing_merchzip['Merch description'].unique():\n",
    "    arr = data.loc[(data['Merch description']==des)&(data['Merch zip']!='Unknown')]['Merch zip'].tolist()\n",
    "    mode = majorityElement(arr)\n",
    "    data.loc[(data['Merch description']==des)&(data['Merch zip']=='Unknown'),'Merch zip']= mode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "legal-progress",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1074"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# after filling based on merch description\n",
    "sum(data['Merch zip']=='Unknown')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "devoted-picnic",
   "metadata": {},
   "source": [
    "## Fill the left blank with 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "latest-delight",
   "metadata": {},
   "outputs": [],
   "source": [
    "zip_not_null = data.loc[data['Merch zip']!='Unknown']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "touched-revelation",
   "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>Recnum</th>\n",
       "      <th>Cardnum</th>\n",
       "      <th>Date</th>\n",
       "      <th>Merchnum</th>\n",
       "      <th>Merch description</th>\n",
       "      <th>Merch state</th>\n",
       "      <th>Merch zip</th>\n",
       "      <th>Transtype</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Fraud</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19799</th>\n",
       "      <td>19800</td>\n",
       "      <td>5142123782</td>\n",
       "      <td>2010-03-17</td>\n",
       "      <td>6173325459</td>\n",
       "      <td>KRUG FURNITURE INC</td>\n",
       "      <td>CA</td>\n",
       "      <td>99999.0</td>\n",
       "      <td>P</td>\n",
       "      <td>939.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31793</th>\n",
       "      <td>31794</td>\n",
       "      <td>5142263776</td>\n",
       "      <td>2010-05-01</td>\n",
       "      <td>6173325459</td>\n",
       "      <td>KRUG FURNITURE INC</td>\n",
       "      <td>CA</td>\n",
       "      <td>99999.0</td>\n",
       "      <td>P</td>\n",
       "      <td>602.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66533</th>\n",
       "      <td>66534</td>\n",
       "      <td>5142143029</td>\n",
       "      <td>2010-08-24</td>\n",
       "      <td>6173325459</td>\n",
       "      <td>KRUG FURNITURE INC</td>\n",
       "      <td>ON</td>\n",
       "      <td>99999.0</td>\n",
       "      <td>P</td>\n",
       "      <td>1258.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79242</th>\n",
       "      <td>79243</td>\n",
       "      <td>5142134426</td>\n",
       "      <td>2010-10-01</td>\n",
       "      <td>6173325459</td>\n",
       "      <td>KRUG FURNITURE INC</td>\n",
       "      <td>CA</td>\n",
       "      <td>99999.0</td>\n",
       "      <td>P</td>\n",
       "      <td>556.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Recnum     Cardnum       Date    Merchnum   Merch description  \\\n",
       "19799   19800  5142123782 2010-03-17  6173325459  KRUG FURNITURE INC   \n",
       "31793   31794  5142263776 2010-05-01  6173325459  KRUG FURNITURE INC   \n",
       "66533   66534  5142143029 2010-08-24  6173325459  KRUG FURNITURE INC   \n",
       "79242   79243  5142134426 2010-10-01  6173325459  KRUG FURNITURE INC   \n",
       "\n",
       "      Merch state Merch zip Transtype  Amount  Fraud  \n",
       "19799          CA   99999.0         P   939.0      0  \n",
       "31793          CA   99999.0         P   602.0      0  \n",
       "66533          ON   99999.0         P  1258.0      0  \n",
       "79242          CA   99999.0         P   556.0      0  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_not_null.loc[zip_not_null['Merch zip']==99999]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "settled-sugar",
   "metadata": {},
   "outputs": [],
   "source": [
    "after_fill_left_0 = zip_not_null['Merch zip'].astype('int').apply(lambda x: '{0:0>5}'.format(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "great-resistance",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "95679"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = set(after_fill_left_0.index)\n",
    "len(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "generous-sacrifice",
   "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>Recnum</th>\n",
       "      <th>Cardnum</th>\n",
       "      <th>Date</th>\n",
       "      <th>Merchnum</th>\n",
       "      <th>Merch description</th>\n",
       "      <th>Merch state</th>\n",
       "      <th>Merch zip</th>\n",
       "      <th>Transtype</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Fraud</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5142190439</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>5509006296254</td>\n",
       "      <td>FEDEX SHP 12/23/09 AB#</td>\n",
       "      <td>TN</td>\n",
       "      <td>38118</td>\n",
       "      <td>P</td>\n",
       "      <td>3.62</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>5142183973</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>61003026333</td>\n",
       "      <td>SERVICE MERCHANDISE #81</td>\n",
       "      <td>MA</td>\n",
       "      <td>01803</td>\n",
       "      <td>P</td>\n",
       "      <td>31.42</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>5142131721</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>4503082993600</td>\n",
       "      <td>OFFICE DEPOT #191</td>\n",
       "      <td>MD</td>\n",
       "      <td>20706</td>\n",
       "      <td>P</td>\n",
       "      <td>178.49</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Recnum     Cardnum       Date       Merchnum        Merch description  \\\n",
       "0       1  5142190439 2010-01-01  5509006296254   FEDEX SHP 12/23/09 AB#   \n",
       "1       2  5142183973 2010-01-01    61003026333  SERVICE MERCHANDISE #81   \n",
       "2       3  5142131721 2010-01-01  4503082993600        OFFICE DEPOT #191   \n",
       "\n",
       "  Merch state Merch zip Transtype  Amount  Fraud  \n",
       "0          TN     38118         P    3.62      0  \n",
       "1          MA     01803         P   31.42      0  \n",
       "2          MD     20706         P  178.49      0  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['Merch zip'] = [after_fill_left_0[i] if i in after_fill_left_0.index else 'unknown' for i in range(len(data))]\n",
    "data.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "forty-retailer",
   "metadata": {},
   "source": [
    "## Drop all transtype except those equal to P"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "given-magnitude",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "P    96398\n",
       "Name: Transtype, dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = data.loc[data['Transtype']=='P']\n",
    "data.Transtype.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "foreign-egypt",
   "metadata": {},
   "source": [
    "## Remove transaction amout outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "tamil-header",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:xlabel='Amount'>"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAWAAAAEGCAYAAABbzE8LAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuNCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8QVMy6AAAACXBIWXMAAAsTAAALEwEAmpwYAAALrElEQVR4nO3dUYyl5V3H8d8fBlRSkya7JGKlrDZUK5K2dEOoGLMYSDa9QRPUkkY2SjQ2ESREEvVC412vuAA1BivpYqqmWm1LU6YFAlqrLV0akFJagtDGjSTAVtoiprrs48V5gemwu3OWnTn/M/D5JJM9c847532feXa/+8579jxbY4wAsHindB8AwOuVAAM0EWCAJgIM0ESAAZqsnMjGO3fuHLt27dqiQwF4bbr//vufGWOcuf7+Ewrwrl27cuDAgc07KoDXgar6xtHudwkCoIkAAzQRYIAmAgzQRIABmggwQBMBBmgiwABNBBigiQADNBFggCYCDNBEgAGaCDBAEwEGaCLAAE0EGKCJAAM0EWCAJgsJ8M0335ybb755EbsC2DYWEuDV1dWsrq4uYlcA24ZLEABNBBigiQADNBFggCYCDNBEgAGaCDBAEwEGaCLAAE0EGKCJAAM0EWCAJgIM0ESAAZoIMEATAQZoIsAATQQYoIkAAzQRYIAmAgzQRIABmggwQBMBBmgiwABNBBigiQADNBFggCYCDNBEgAGaCDBAEwEGaCLAAE0EGKCJAAM0EWCAJgIM0ESAAZoIMEATAQZoIsAATQQYoMnKInby/PPPL2I3ANvKQgI8xljEbgC2FZcgAJoIMEATAQZoIsAATQQYoIkAAzQRYIAmAgzQRIABmggwQBMBBmgiwABNBBigiQADNBFggCYCDNBEgAGaCDBAEwEGaCLAAE0EGKCJAAM0EWCAJgIM0ESAAZoIMEATAQZoIsAATQQYoIkAAzQRYIAmAgzQRIABmggwQBMBBmgiwABNBBigiQADNFlogPfs2fPSx+23355rr702hw4dWuQhAJyQQ4cObVmr2s6Ab7zxxjz00EO57bbbug4BYEP79+/fslZteYD37Nlz1PvHGBljZHV11VkwsJQOHTqU1dXVLWtV+zXgF154wVkwsJT279+fI0eOJNmaVm0Y4Kr6jao6UFUHnn766U3deZIcPnw4d95556Y/L8DJuuuuu3L48OEkW9OqDQM8xrhljLF7jLH7zDPP3NSdJ8nKykouu+yyTX9egJN16aWXZmVlJcnWtKr9EsSpp56aq666qvswAF5h3759OeWUWSa3olVbHuB77733qPdXVaoqe/fuzY4dO7b6MABO2I4dO7J3794ta1XbGfD111+f888/39kvsNT27du3Za2qMcbcG+/evXscOHDghHfy4j9FO9bZMMBrWVXdP8bYvf7+9mvAAK9XAgzQRIABmggwQBMBBmgiwABNBBigiQADNBFggCYCDNBEgAGaCDBAEwEGaCLAAE0EGKCJAAM0EWCAJgIM0ESAAZoIMEATAQZoIsAATQQYoIkAAzQRYIAmAgzQRIABmggwQBMBBmgiwABNBBigiQADNBFggCYCDNBEgAGaCDBAEwEGaCLAAE1WFrGTqlrEbgC2lYUE+IwzzljEbgC2FZcgAJoIMEATAQZoIsAATQQYoIkAAzQRYIAmAgzQRIABmggwQBMBBmgiwABNBBigiQADNBFggCYCDNBEgAGaCDBAEwEGaCLAAE0EGKCJAAM0EWCAJgIM0ESAAZoIMEATAQZoIsAATQQYoIkAAzQRYIAmAgzQRIABmggwQBMBBmgiwABNBBigiQADNBFggCYCDNBEgAGarCxiJ3v37l3EbgC2lYUE+JprrlnEbgC2FZcgAJoIMEATAQZoIsAATQQYoIkAAzQRYIAmAgzQRIABmggwQBMBBmgiwABNBBigiQADNBFggCYCDNBEgAGaCDBAEwEGaCLAAE1qjDH/xlVPJ/nGq9zXziTPvMqvXRbGsByMYTkYw/zOGWOcuf7OEwrwyaiqA2OM3QvZ2RYxhuVgDMvBGE6eSxAATQQYoMkiA3zLAve1VYxhORjDcjCGk7Swa8AAfC+XIACaCDBAk00PcFXtraqvVdVjVfW7R3m8quqm6fF/q6oLNvsYTtYcY9hTVd+qqgemjz/oOM5jqapbq+qpqvryMR7fDnOw0RiWeg6SpKrOrqp7quqRqnq4qn77KNss9VzMOYalnouq+v6quq+qHpzG8EdH2aZnHsYYm/aR5NQk/57kx5KcnuTBJD+5bpv3JLkjSSW5KMkXNvMYFjSGPUk+2X2sxxnDzya5IMmXj/H4Us/BnGNY6jmYjvGsJBdMt38wyaPb8M/DPGNY6rmYvrdvmG6fluQLSS5ahnnY7DPgC5M8NsZ4fIzxv0n+Jsnl67a5PMltY+bzSd5YVWdt8nGcjHnGsNTGGP+U5JvH2WTZ52CeMSy9McaTY4wvTbe/k+SRJG9at9lSz8WcY1hq0/f2uenT06aP9f/6oGUeNjvAb0ryH2s+P5hXTtY823Sa9/jePf1Ic0dVnbeYQ9s0yz4H89o2c1BVu5K8M7Ozr7W2zVwcZwzJks9FVZ1aVQ8keSrJnWOMpZiHlU1+vjrKfev/pplnm07zHN+XMntv93NV9Z4kH0ty7lYf2CZa9jmYx7aZg6p6Q5KPJrlujPHt9Q8f5UuWbi42GMPSz8UY44Uk76iqNyb5h6r6qTHG2tcXWuZhs8+ADyY5e83nP5LkP1/FNp02PL4xxrdf/JFmjPGpJKdV1c7FHeJJW/Y52NB2mYOqOi2zcH14jPH3R9lk6ediozFsl7lIkjHGs0nuTbJ33UMt87DZAf5iknOr6ker6vQk703yiXXbfCLJVdOrjhcl+dYY48lNPo6TseEYquqHqqqm2xdm9n08tPAjffWWfQ42tB3mYDq+v0jyyBjjxmNsttRzMc8Yln0uqurM6cw3VfUDSS5N8tV1m7XMw6ZeghhjHK6q30ry6cz+NcGtY4yHq+o3p8f/LMmnMnvF8bEkzyf51c08hpM15xiuSPL+qjqc5H+SvHdML6Uug6r668xemd5ZVQeT/GFmLzxsizlI5hrDUs/B5OIkv5Lkoen6Y5L8fpI3J9tmLuYZw7LPxVlJ9lfVqZn95fCRMcYnl6FL3ooM0MQ74QCaCDBAEwEGaCLAAE0EGOAYaoNFoY6y/S9V1VemRX/+aqPtBZh2VfULVTWq6icaj+G6qjqja/8srQ/llW/aOKqqOjfJ7yW5eIxxXpLrNvoaAWYZXJnknzN700uX65IIMN/jaItCVdVbqmq1qu6vqs+uOXH49SR/Msb4r+lrn9ro+QWYVtMaAxcnuTpTgGu2vuw/VtVHqurRqvpAVb2vZmu6PlRVb5m2O6eq7p7Wb727qt483f+hqrpizT6eW/O891bV31XVV6vqw9M7n65N8sNJ7qmqexb8LWD7uSXJNWOMdyX5nSR/Ot3/1iRvrarPVdXnq2rDM+fNXowHTtTPJ1kdYzxaVd+slxfCfnuSt2V29vF4kg+OMS6s2YLg12R2xvrHmS0huL+qfi3JTdPzHc87k5yX2fv8P5fZj4s3VdX1SS4ZYzyzqaPjNWU6YfjpJH87vfs6Sb5v+nUls0WI9mS2lsRnp0V/nj3W8zkDptuVma25nOnXK6fbX5zWov1uZgvkf2a6/6Eku6bb707y4gsdf5nkZ+bY331jjINjjCNJHljzXDCPU5I8O8Z4x5qPt02PHUzy8THG/40xnkjytWywKpwA06aqdiT5uSQfrKqvJ7khyS9ntjTgd9dsemTN50dy7J/cXnxf/eFMv7enRWJOX7PN2ud94TjPBa8wLcX5RFX9YvLSf2X09unhjyW5ZLp/Z2aXJB4/3vMJMJ2uyOwSwjljjF1jjLOTPJH5zmST5F/y8gt378vshbwk+XqSd023L8+0iM8GvpPZf7kDL5kWhfrXJD9eVQer6urMfq9dXVUPJnk4L/+POZ9OcqiqvpLkniQ3jDGOuyqcv/3pdGWSD6y776NJ3p/ZZYeNXJvk1qq6IcnTeXkFqz9P8vGqui/J3Un+e47nuiXJHVX15BjjknkOnte+McaVx3joFS+wTSvAXT99zMVqaABNXIIAaCLAAE0EGKCJAAM0EWCAJgIM0ESAAZr8P/9mxs3QxvGJAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# find the outliers\n",
    "sns.boxplot(data['Amount'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "spatial-fence",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:xlabel='Amount'>"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAW4AAAEGCAYAAABFBX+4AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuNCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8QVMy6AAAACXBIWXMAAAsTAAALEwEAmpwYAAAQc0lEQVR4nO3df2xd5X3H8c/XvrZjY7blVwMzSQ7ZjbVYJVvB6loxbWkFW2xQ2CT+IOsWa4CChJQEkECgXBZDImD7IyxJERuhCLIS2m1dtVCtawMlSNOkps5KgKqEGuZoJIiE1CtNwprYfvbHPb45tuOb6x/3x/fe90uy7rnPec7znO+9ycfH59wfFkIQAMCPunLvAABgaghuAHCG4AYAZwhuAHCG4AYAZ1JT6bxgwYIQRVGRdgUAqtOhQ4c+DiEsnK3xphTcURSpr69vtuYGgJpgZkdnczxOlQCAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAM1P6zsnp2rVrl15//XVJUltbm9LptDZs2FCKqQGg6pQkuPv7+3Xy41NSfUonBz8pxZQAULVKEtySpPqUhlvml2w6AKhWnOMGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGdKEtzHjh2TRoYntO/atUu7du0qxS4AQNVIlWKSTz/9VAphQnt/f38ppgeAqsKpEgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGdS5Zz88OHDkqRVq1aVczempaWlRWfPnlVra6tOnz49Zl1TU5OeeuoppdNp9fX16f7771cIQfPmzdPg4KAWLVqkjz76SOvXr9fu3bt15513avfu3Vq8eLGamppkZrmxRkZGVFdXp5GRETU2Nmrr1q2SpEwmIzPT7bffnlveunWr9uzZoy1btmhwcFAbNmzQyMiIFi1apBMnTmjJkiV6/PHHNX/+fJ06dUqPPPKItmzZIkm55cnWbdy4UTt37sz1SUr2Hxwc1KZNm7Rjxw7NnTtXDz/8sEII2rZt24TtCjU6/saNG/XYY4/p/fffV2Njo6Io0gMPPKAnnnhCx48f16OPPqrnnntOIQTdcccd2rx5s86dO6fGxsbc8zF+fyerpdB6C61pOtugMlTic2chhII7d3Z2hr6+vilPctNNN+n0mbMavnyRJOm6ZYu0Y8cOl4FdqCiK9Pzzz+vmm2+eEOwzccsttyiEoH379knSmF8cra2tOnPmjNasWaPDhw9rYGDgotvfe++92r59u15++WWtWbNGIYTc8mTrli5dqqNHj+b6JCX7j84bRZFWrlyZ28/ReadjdPylS5dOqCmKolzb+Mci+biPPh/j93eyWgqtt9CaprMNKsNsPHdmdiiE0Dlb+1S24B492q5m9913n7Zv3z6rYzY0NCiEoKGhobx9zp8/P+m6p59+WnfffXfuaFSSzp07l/tLIbkuhDBmrKamJu3duzd35HHq1CmtXbtW586dmzBvKpXK7WdjY6NeeumlKR+xJMefqWeffVZz587NjZevllH5+oxfV0gNhW6DyjBbz91sB3fJz3HX/d8n6u/vL/W0ZfHkk0/O+pjnz5/PG9qjffKt27Ztm0ZGRnL3R/sPDw9PWDd+ruHhYe3Zsyd3/4UXXhjTPym57fnz58dsV6jk+DO1bdu2MePlq2VUvj7j101mOtugMlTqc3fJ4Daz9WbWZ2Z9J0+eLMU+VY2p/DVTSgMDA7lQDSHk9nNoaGjSdaOGhoa0f//+3P1XXnnlkr9IRsdKbleoQscvxMDAwJjxCqklX5/x6yYznW1QGSr1ubtkcIcQngkhdIYQOhcuXDjjCUfm/FruIlG1S15krCRRFCmVyl6XNrPcfqZSqUnXjUqlUrrxxhtz92+44YZc/3zMbMx2hSp0/EJEUTRmvEJqyddn/LrJTGcbVIZKfe54OWARFeMiVENDwyWDrKGhIe+6TCajurq63P3R/vX19RPWjZ+rvr5e69aty93v6ekZ0z8puW1DQ8OY7QqVHH+mMpnMmPHy1TIqX5/x6yYznW1QGSr1uStbcB84cKBcU5dEFEVas2aNWltbZ3Xc7u5udXd35+4nx29tbZWZqbu7W1EUTbp9Op3W6tWrZWbq6urKLa9evXrCuq6uLpmZoijK9UlenJk/f36uf3LeKIrG7GdXV9e0Luokx79YTcm28Y/F+H7pdHrMePlqKaTe8esKqaHQbVAZKvW544h7mlpaWiRNDAgp+0qETCYjSert7c2dbpg3b57MTFdccYXMTHfddZfq6uq0fv16mZmWLFmi5cuXq729PfeTTqdztx0dHVq3bp16enq0YsUKdXR0qLe3V3PmzFFzc7N6e3t1zTXXaN26dcpkMmpublZTU5OWLFmiOXPmqL29PXfE0NPTk+ubXJ5sXSaTGdMnKdk/k8nosssuyx3ddnR0aMWKFTM6Uknuw7JlyyRlX6XS3t6uTCajdDqtlpYW9fb25ubr7e3NvSY++XyM3998cxVS71RrqJQjNhSuEp+7sr6Oe9OmTZKkHTt2THlMAPDC/csBAQAzQ3ADgDMENwA4Q3ADgDMENwA4Q3ADgDMENwA4Q3ADgDMENwA4Q3ADgDMENwA4Q3ADgDMENwA4Q3ADgDMENwA4Q3ADgDMENwA4Q3ADgDMENwA4Q3ADgDMENwA4Q3ADgDMENwA4Q3ADgDMENwA4Q3ADgDMENwA4Q3ADgDMENwA4Q3ADgDMENwA4Q3ADgDMENwA4Q3ADgDMENwA4Q3ADgDMENwA4Q3ADgDOpUkzS3Nys02c/ndCeTqdLMT0AVJWSBHdbW5tODv5iQvuGDRtKMT0AVBVOlQCAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADhDcAOAMwQ3ADiTKtlMw0OqP3tKkklaVLJpAaDalCS40+m0jh07Jklqa2tTOp0uxbQAUJUshFBw587OztDX11fE3QGA6mNmh0IInbM1Hue4AcAZghsAnCG4AcAZghsAnCG4AcAZghsAnCG4AcAZghsAnCG4AcAZghsAnCG4AcAZghsAnCG4AcAZghsAnCG4AcAZghsAnCG4AcAZghsAnCG4AcAZghsAnJnSlwWb2UlJR6c51wJJH09zW+9quXaJ+mu5/lquXbpQ/9IQwsLZGnRKwT2jicz6ZvNbjj2p5dol6q/l+mu5dql49XOqBACcIbgBwJlSBvczJZyr0tRy7RL113L9tVy7VKT6S3aOGwAwOzhVAgDOENwA4EzRg9vMVpvZETPrN7MHiz1fMZnZc2Z2wszeTrTNM7P9Zvaz+HZuYt1Dcd1HzOyPE+3Xmdlb8bqdZmZxe5OZfTNu/6GZRSUtMA8zW2xmr5nZT83sJ2a2KW6v+vrNbI6ZHTSzw3Htj8TtVV97kpnVm9mPzew78f2aqd/MBuL9fsPM+uK28tUfQijaj6R6Se9JWiapUdJhSR3FnLPI9fyBpGslvZ1o+xtJD8bLD0r663i5I663SdLV8eNQH687KOmLkkzSdyV1xe13S/q7ePk2Sd8sd82JOq+UdG28fLmkd+Maq77+eD9b4+UGST+U9IVaqH3c43CfpL2SvlNL//bjfRqQtGBcW9nqL3axX5T0vcT9hyQ9VO4nYYY1RRob3EckXRkvXynpyMVqlfS9+PG4UtI7ifa1kv4+2SdeTin7jisrd82TPA7/KunGWqtfUouk/5L0e7VUu6SrJL0q6cu6ENy1VP+AJgZ32eov9qmSNkn/k7j/QdxWTRaFED6UpPj2M3H7ZLW3xcvj28dsE0IYkvQLSfOLtufTFP8Z9zlljzxrov74NMEbkk5I2h9CqJnaY38r6QFJI4m2Wqo/SPq+mR0ys/VxW9nqT82olEuzi7TVyusPJ6s932NS8Y+XmbVK+pake0IIn8Sn6C7a9SJtbusPIQxL+l0z+w1J3zazz+bpXlW1m9nNkk6EEA6Z2apCNrlIm9v6Y9eHEI6b2Wck7Tezd/L0LXr9xT7i/kDS4sT9qyQdL/KcpfaRmV0pSfHtibh9sto/iJfHt4/ZxsxSkn5d0s+LtudTZGYNyob2iyGEf4mba6Z+SQoh/K+kA5JWq3Zqv17SGjMbkPQNSV82s6+rdupXCOF4fHtC0rclfV5lrL/Ywf0jScvN7Goza1T2pPu+Is9Zavsk9cTLPcqe+x1tvy2+Wny1pOWSDsZ/Uv3SzL4QX1FeN26b0bFulfSDEJ/0Krd4X78m6achhO2JVVVfv5ktjI+0ZWbNkm6Q9I5qoHZJCiE8FEK4KoQQKft/+AchhD9XjdRvZpeZ2eWjy5L+SNLbKmf9JTip363sKxDek7S53BcZZljLS5I+lHRe2d+Qdyh7HupVST+Lb+cl+m+O6z6i+Opx3N4ZP/HvSfqqLryDdY6kf5LUr+zV52Xlrjmxz7+v7J9ub0p6I/7proX6Ja2U9OO49rcl/VXcXvW1X+SxWKULFydron5lXxV3OP75yWiOlbN+3vIOAM7wzkkAcIbgBgBnCG4AcIbgBgBnCG4AcIbgRtmZ2Z+aWTCz3y7jPtxjZi3lmh+YCoIblWCtpP9Q9s0d5XKPsh8gBVQ8ghtlFX/2yfXKvpnptrhtlZm9bmb/aGbvmtkTZvYVy34m9ltm9ltxv6Vm9qqZvRnfLonbnzezWxNznE6Me8DM/tnM3jGzFy1ro6TflPSamb1W4ocAmDKCG+X2J5L+PYTwrqSfm9m1cfvvSNok6RpJfyGpPYTweUnPStoQ9/mqpD0hhJWSXpS0s4D5Pqfs0XWHsu+Iuz6EsFPZz4z4UgjhS7NRFFBMBDfKba2yH1yk+HZtvPyjEMKHIYRfKfv24O/H7W8p+5noUvYzjvfGy/+g7NvyL+VgCOGDEMKIsm/bj/J3BypPsT/WFZiUmc1X9oP5P2tmQdlvTAqS/k3SrxJdRxL3RzT5v9vRz28YUnxQEn+YT2OiT3Lc4TxjARWLI26U063KnupYGkKIQgiLJf23CjtylqT/1IULml9R9gKnlP22kuvi5VuU/bqxS/mlsl/JBlQ8ghvltFbZzzZO+pakPytw+42S/tLM3lT2PPimuH23pD80s4PKfsXYmQLGekbSd7k4CQ/4dEAAcIYjbgBwhuAGAGcIbgBwhuAGAGcIbgBwhuAGAGcIbgBw5v8BxIQKdkzmOwEAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# find the outliers\n",
    "df_remove_outlier = data.loc[data['Amount']<3000000]\n",
    "sns.boxplot(df_remove_outlier['Amount'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "awful-break",
   "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>Recnum</th>\n",
       "      <th>Cardnum</th>\n",
       "      <th>Date</th>\n",
       "      <th>Merchnum</th>\n",
       "      <th>Merch description</th>\n",
       "      <th>Merch state</th>\n",
       "      <th>Merch zip</th>\n",
       "      <th>Transtype</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Fraud</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>52714</th>\n",
       "      <td>52715</td>\n",
       "      <td>5142189135</td>\n",
       "      <td>2010-07-13</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>INTERMEXICO</td>\n",
       "      <td>VA</td>\n",
       "      <td>21045</td>\n",
       "      <td>P</td>\n",
       "      <td>3102045.53</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Recnum     Cardnum       Date Merchnum Merch description Merch state  \\\n",
       "52714   52715  5142189135 2010-07-13  Unknown       INTERMEXICO          VA   \n",
       "\n",
       "      Merch zip Transtype      Amount  Fraud  \n",
       "52714     21045         P  3102045.53      0  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the 1 outliers is not fraud so remove it\n",
    "data.loc[data['Amount']>3000000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "tight-forum",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove 1 outlier\n",
    "data = data.loc[data['Amount']<3000000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "light-moldova",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(96397, 10)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "regulation-medicaid",
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_csv('after cleaning.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "italian-kitty",
   "metadata": {},
   "source": [
    "# Feature Creation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "stretch-portrait",
   "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>Recnum</th>\n",
       "      <th>Cardnum</th>\n",
       "      <th>Date</th>\n",
       "      <th>Merchnum</th>\n",
       "      <th>Merch description</th>\n",
       "      <th>Merch state</th>\n",
       "      <th>Merch zip</th>\n",
       "      <th>Transtype</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Fraud</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5142190439</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>5509006296254</td>\n",
       "      <td>FEDEX SHP 12/23/09 AB#</td>\n",
       "      <td>TN</td>\n",
       "      <td>38118</td>\n",
       "      <td>P</td>\n",
       "      <td>3.62</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>5142183973</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>61003026333</td>\n",
       "      <td>SERVICE MERCHANDISE #81</td>\n",
       "      <td>MA</td>\n",
       "      <td>01803</td>\n",
       "      <td>P</td>\n",
       "      <td>31.42</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>5142131721</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>4503082993600</td>\n",
       "      <td>OFFICE DEPOT #191</td>\n",
       "      <td>MD</td>\n",
       "      <td>20706</td>\n",
       "      <td>P</td>\n",
       "      <td>178.49</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>5142148452</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>5509006296254</td>\n",
       "      <td>FEDEX SHP 12/28/09 AB#</td>\n",
       "      <td>TN</td>\n",
       "      <td>38118</td>\n",
       "      <td>P</td>\n",
       "      <td>3.62</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>5142190439</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>5509006296254</td>\n",
       "      <td>FEDEX SHP 12/23/09 AB#</td>\n",
       "      <td>TN</td>\n",
       "      <td>38118</td>\n",
       "      <td>P</td>\n",
       "      <td>3.62</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Recnum     Cardnum       Date       Merchnum        Merch description  \\\n",
       "0       1  5142190439 2010-01-01  5509006296254   FEDEX SHP 12/23/09 AB#   \n",
       "1       2  5142183973 2010-01-01    61003026333  SERVICE MERCHANDISE #81   \n",
       "2       3  5142131721 2010-01-01  4503082993600        OFFICE DEPOT #191   \n",
       "3       4  5142148452 2010-01-01  5509006296254   FEDEX SHP 12/28/09 AB#   \n",
       "4       5  5142190439 2010-01-01  5509006296254   FEDEX SHP 12/23/09 AB#   \n",
       "\n",
       "  Merch state Merch zip Transtype  Amount  Fraud  \n",
       "0          TN     38118         P    3.62      0  \n",
       "1          MA     01803         P   31.42      0  \n",
       "2          MD     20706         P  178.49      0  \n",
       "3          TN     38118         P    3.62      0  \n",
       "4          TN     38118         P    3.62      0  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv('after cleaning.csv',parse_dates=['Date'])\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "present-oklahoma",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 96397 entries, 0 to 96396\n",
      "Data columns (total 10 columns):\n",
      " #   Column             Non-Null Count  Dtype         \n",
      "---  ------             --------------  -----         \n",
      " 0   Recnum             96397 non-null  int64         \n",
      " 1   Cardnum            96397 non-null  int64         \n",
      " 2   Date               96397 non-null  datetime64[ns]\n",
      " 3   Merchnum           96397 non-null  object        \n",
      " 4   Merch description  96397 non-null  object        \n",
      " 5   Merch state        96397 non-null  object        \n",
      " 6   Merch zip          96397 non-null  object        \n",
      " 7   Transtype          96397 non-null  object        \n",
      " 8   Amount             96397 non-null  float64       \n",
      " 9   Fraud              96397 non-null  int64         \n",
      "dtypes: datetime64[ns](1), float64(1), int64(3), object(5)\n",
      "memory usage: 7.4+ MB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cosmetic-plymouth",
   "metadata": {},
   "source": [
    "## Create entities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "arabic-bahamas",
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Cardnum'] = data['Cardnum'].astype('str')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "experimental-accused",
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Merch'] = data['Merchnum']+data['Merch description']+data['Merch state']+data['Merch zip']\n",
    "data['state_zip'] = data['Merch state']+data['Merch zip']\n",
    "data['card_merch'] = data['Cardnum']+data['Merchnum']\n",
    "data['card_description'] = data['Cardnum']+data['Merch description']\n",
    "data['card_state'] = data['Cardnum']+data['Merch state']\n",
    "data['card_zip'] = data['Cardnum']+data['Merch zip']\n",
    "data['merch_state'] = data['Merchnum']+data['Merch state']\n",
    "data['merch_zip'] = data['Merchnum']+data['Merch zip']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "fifteen-spiritual",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Recnum', 'Cardnum', 'Date', 'Merchnum', 'Merch description',\n",
       "       'Merch state', 'Merch zip', 'Transtype', 'Amount', 'Fraud', 'Merch',\n",
       "       'state_zip', 'card_merch', 'card_description', 'card_state', 'card_zip',\n",
       "       'merch_state', 'merch_zip'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "revolutionary-motel",
   "metadata": {},
   "outputs": [],
   "source": [
    "attributes = ['Cardnum', 'Merchnum', 'Merch description','Merch zip', 'Merch',\n",
    "       'state_zip', 'card_merch', 'card_description', 'card_state', 'card_zip',\n",
    "       'merch_state', 'merch_zip']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "active-machinery",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 96397 entries, 0 to 96396\n",
      "Data columns (total 18 columns):\n",
      " #   Column             Non-Null Count  Dtype         \n",
      "---  ------             --------------  -----         \n",
      " 0   Recnum             96397 non-null  int64         \n",
      " 1   Cardnum            96397 non-null  object        \n",
      " 2   Date               96397 non-null  datetime64[ns]\n",
      " 3   Merchnum           96397 non-null  object        \n",
      " 4   Merch description  96397 non-null  object        \n",
      " 5   Merch state        96397 non-null  object        \n",
      " 6   Merch zip          96397 non-null  object        \n",
      " 7   Transtype          96397 non-null  object        \n",
      " 8   Amount             96397 non-null  float64       \n",
      " 9   Fraud              96397 non-null  int64         \n",
      " 10  Merch              96397 non-null  object        \n",
      " 11  state_zip          96397 non-null  object        \n",
      " 12  card_merch         96397 non-null  object        \n",
      " 13  card_description   96397 non-null  object        \n",
      " 14  card_state         96397 non-null  object        \n",
      " 15  card_zip           96397 non-null  object        \n",
      " 16  merch_state        96397 non-null  object        \n",
      " 17  merch_zip          96397 non-null  object        \n",
      "dtypes: datetime64[ns](1), float64(1), int64(2), object(14)\n",
      "memory usage: 13.2+ MB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "portuguese-adelaide",
   "metadata": {},
   "source": [
    "## Create relevant variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "decreased-anthropology",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_copy = data.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "changing-sweden",
   "metadata": {},
   "outputs": [],
   "source": [
    "aggs = ['avg','max','med','total']\n",
    "time_period = [0,1,3,7,14,30]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "blind-cotton",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "amount for Cardnum is done.\n",
      "frequency for Cardnum is done.\n",
      "day_since for Cardnum is done.\n",
      "velocity change for Cardnum is done\n",
      "amount for Merchnum is done.\n",
      "frequency for Merchnum is done.\n",
      "day_since for Merchnum is done.\n",
      "velocity change for Merchnum is done\n",
      "amount for Merch description is done.\n",
      "frequency for Merch description is done.\n",
      "day_since for Merch description is done.\n",
      "velocity change for Merch description is done\n",
      "amount for Merch zip is done.\n",
      "frequency for Merch zip is done.\n",
      "day_since for Merch zip is done.\n",
      "velocity change for Merch zip is done\n",
      "amount for Merch is done.\n",
      "frequency for Merch is done.\n",
      "day_since for Merch is done.\n",
      "velocity change for Merch is done\n",
      "amount for state_zip is done.\n",
      "frequency for state_zip is done.\n",
      "day_since for state_zip is done.\n",
      "velocity change for state_zip is done\n",
      "amount for card_merch is done.\n",
      "frequency for card_merch is done.\n",
      "day_since for card_merch is done.\n",
      "velocity change for card_merch is done\n",
      "amount for card_description is done.\n",
      "frequency for card_description is done.\n",
      "day_since for card_description is done.\n",
      "velocity change for card_description is done\n",
      "amount for card_state is done.\n",
      "frequency for card_state is done.\n",
      "day_since for card_state is done.\n",
      "velocity change for card_state is done\n",
      "amount for card_zip is done.\n",
      "frequency for card_zip is done.\n",
      "day_since for card_zip is done.\n",
      "velocity change for card_zip is done\n",
      "amount for merch_state is done.\n",
      "frequency for merch_state is done.\n",
      "day_since for merch_state is done.\n",
      "velocity change for merch_state is done\n",
      "amount for merch_zip is done.\n",
      "frequency for merch_zip is done.\n",
      "day_since for merch_zip is done.\n",
      "velocity change for merch_zip is done\n",
      "CPU times: user 23min 20s, sys: 19min 8s, total: 42min 28s\n",
      "Wall time: 43min 46s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "final = data.copy()\n",
    "for col in attributes:\n",
    "    df = data.copy()\n",
    "    df = df[['Recnum', 'Date', 'Amount', col]]\n",
    "    temp = pd.merge(df,df, on=col, suffixes=['_l', '_r'])\n",
    "    temp_t = temp[temp.Date_l >= temp.Date_r]\n",
    "    for t in time_period:\n",
    "        # create frequent variables\n",
    "        temp_1 = temp_t.copy()\n",
    "        temp_1 = temp_1[temp_1.Date_r >= (temp_1.Date_l - dt.timedelta(t))]\n",
    "        col_name = f'{col}_count_{t}'\n",
    "        mapper = temp_1.groupby([col, 'Date_l'])['Recnum_r'].nunique()\n",
    "        mapper = mapper.reset_index().rename(columns={'Recnum_r': col_name, 'Date_l': 'Date'})\n",
    "        final = pd.merge(final, mapper, on=[col, 'Date'])\n",
    "        # create amount variables\n",
    "        for agg in aggs:\n",
    "            col_name = f'{agg}_Amount_{col}_{t}'\n",
    "            if agg == 'avg':\n",
    "                mapper = temp_1.groupby([col, 'Date_l'])['Amount_r'].mean()\n",
    "            elif agg == 'max':\n",
    "                mapper = temp_1.groupby([col, 'Date_l'])['Amount_r'].max()\n",
    "            elif agg == 'med':\n",
    "                mapper = temp_1.groupby([col, 'Date_l'])['Amount_r'].median()\n",
    "            elif agg == 'total':\n",
    "                mapper = temp_1.groupby([col, 'Date_l'])['Amount_r'].sum()\n",
    "            mapper = mapper.reset_index().rename(columns={'Amount_r': col_name, 'Date_l': 'Date'})\n",
    "            final = pd.merge(final, mapper, on=[col, 'Date'])\n",
    "            new_col_name = f'actual_by_{agg}_Amount_{col}_{t}'\n",
    "            final[new_col_name] = final['Amount'] / final[col_name]\n",
    "    print(f'amount for {col} is done.')\n",
    "    print(f'frequency for {col} is done.')\n",
    "    \n",
    "    # create day since variables\n",
    "    day_since_df = temp[temp.Recnum_l > temp.Recnum_r][['Recnum_l', 'Date_l', 'Date_r']]\\\n",
    "                   .groupby('Recnum_l')[['Date_l', 'Date_r']].last()\n",
    "    mapper = (day_since_df.Date_l - day_since_df.Date_r).dt.days\n",
    "    final[col + '_day_since'] = final.Recnum.map(mapper)\n",
    "    final[col + '_day_since'].fillna(365, inplace = True)\n",
    "    print(f'day_since for {col} is done.')\n",
    "    \n",
    "    # create velocity change variables\n",
    "    for d in ['0', '1']:\n",
    "        for dd in ['3', '7', '14', '30']:\n",
    "            final[col + '_count_' + d + '_by_' + dd]\\\n",
    "                = final[col + '_count_' + d] / (final[col + '_count_' + dd] / float(dd))\n",
    "            final[col + '_amount_' + d + '_by_' + dd]\\\n",
    "                = final['total' + '_Amount_' + col + '_' + d] / (final['total' + '_Amount_' + col + '_' + dd]/float(dd))\n",
    "    print(f'velocity change for {col} is done')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "distributed-stable",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_copy = final.copy()\n",
    "final.to_csv('after 5 feature creation.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "united-laser",
   "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>Recnum</th>\n",
       "      <th>Cardnum</th>\n",
       "      <th>Date</th>\n",
       "      <th>Merchnum</th>\n",
       "      <th>Merch description</th>\n",
       "      <th>Merch state</th>\n",
       "      <th>Merch zip</th>\n",
       "      <th>Transtype</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Fraud</th>\n",
       "      <th>...</th>\n",
       "      <th>merch_zip_count_0_by_30</th>\n",
       "      <th>merch_zip_amount_0_by_30</th>\n",
       "      <th>merch_zip_count_1_by_3</th>\n",
       "      <th>merch_zip_amount_1_by_3</th>\n",
       "      <th>merch_zip_count_1_by_7</th>\n",
       "      <th>merch_zip_amount_1_by_7</th>\n",
       "      <th>merch_zip_count_1_by_14</th>\n",
       "      <th>merch_zip_amount_1_by_14</th>\n",
       "      <th>merch_zip_count_1_by_30</th>\n",
       "      <th>merch_zip_amount_1_by_30</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17231</th>\n",
       "      <td>17142</td>\n",
       "      <td>5142238608</td>\n",
       "      <td>2010-03-09</td>\n",
       "      <td>9104075140000</td>\n",
       "      <td>STAPLES #756</td>\n",
       "      <td>PA</td>\n",
       "      <td>19115</td>\n",
       "      <td>P</td>\n",
       "      <td>196.7</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>12.606819</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.75</td>\n",
       "      <td>3.227235</td>\n",
       "      <td>3.5</td>\n",
       "      <td>6.454471</td>\n",
       "      <td>5.0</td>\n",
       "      <td>12.606819</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 870 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Recnum     Cardnum       Date       Merchnum Merch description  \\\n",
       "17231   17142  5142238608 2010-03-09  9104075140000      STAPLES #756   \n",
       "\n",
       "      Merch state Merch zip Transtype  Amount  Fraud  ...  \\\n",
       "17231          PA     19115         P   196.7      0  ...   \n",
       "\n",
       "      merch_zip_count_0_by_30 merch_zip_amount_0_by_30 merch_zip_count_1_by_3  \\\n",
       "17231                     5.0                12.606819                    3.0   \n",
       "\n",
       "      merch_zip_amount_1_by_3 merch_zip_count_1_by_7 merch_zip_amount_1_by_7  \\\n",
       "17231                     3.0                   1.75                3.227235   \n",
       "\n",
       "      merch_zip_count_1_by_14 merch_zip_amount_1_by_14  \\\n",
       "17231                     3.5                 6.454471   \n",
       "\n",
       "       merch_zip_count_1_by_30  merch_zip_amount_1_by_30  \n",
       "17231                      5.0                 12.606819  \n",
       "\n",
       "[1 rows x 870 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "modified-device",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(96397, 871)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_copy.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "animated-begin",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(96397, 18)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "gentle-attraction",
   "metadata": {},
   "source": [
    "### Risk Tables Variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "brown-connectivity",
   "metadata": {},
   "outputs": [],
   "source": [
    "final=final_copy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "meaning-grant",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 13.9 s, sys: 14 s, total: 27.9 s\n",
      "Wall time: 29 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# split tt and oot (out-of-time data)\n",
    "final = final.sort_values('Recnum')\n",
    "final['dow'] = final['Date'].dt.dayofweek\n",
    "final = final.merge(final[['Recnum','Fraud']], on='Recnum')\n",
    "train_test = final[final['Date']<'2010-11-01']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "appropriate-medication",
   "metadata": {},
   "outputs": [],
   "source": [
    "train_test.drop(columns = ['Fraud_y'])\n",
    "train_test = train_test.rename(columns = {'Fraud_x':'Fraud'})\n",
    "# do statistical smoothing\n",
    "c = 4; nmid = 20; y_avg = train_test['Fraud'].mean()\n",
    "y_dow = train_test.groupby('dow')['Fraud'].mean()\n",
    "num = train_test.groupby('dow').size()\n",
    "y_dow_smooth = y_avg + (y_dow-y_avg)/(1+np.exp(-(num-nmid)/c))\n",
    "final['dow_risk'] = final['dow'].map(y_dow_smooth)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "everyday-bouquet",
   "metadata": {},
   "outputs": [],
   "source": [
    "final.drop(columns = ['Fraud_y'],inplace=True)\n",
    "final = final.rename(columns = {'Fraud_x':'Fraud'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "flush-haven",
   "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>Recnum</th>\n",
       "      <th>Cardnum</th>\n",
       "      <th>Date</th>\n",
       "      <th>Merchnum</th>\n",
       "      <th>Merch description</th>\n",
       "      <th>Merch state</th>\n",
       "      <th>Merch zip</th>\n",
       "      <th>Transtype</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Fraud</th>\n",
       "      <th>...</th>\n",
       "      <th>merch_zip_count_1_by_3</th>\n",
       "      <th>merch_zip_amount_1_by_3</th>\n",
       "      <th>merch_zip_count_1_by_7</th>\n",
       "      <th>merch_zip_amount_1_by_7</th>\n",
       "      <th>merch_zip_count_1_by_14</th>\n",
       "      <th>merch_zip_amount_1_by_14</th>\n",
       "      <th>merch_zip_count_1_by_30</th>\n",
       "      <th>merch_zip_amount_1_by_30</th>\n",
       "      <th>dow</th>\n",
       "      <th>dow_risk</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5142190439</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>5509006296254</td>\n",
       "      <td>FEDEX SHP 12/23/09 AB#</td>\n",
       "      <td>TN</td>\n",
       "      <td>38118</td>\n",
       "      <td>P</td>\n",
       "      <td>3.62</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>5142183973</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>61003026333</td>\n",
       "      <td>SERVICE MERCHANDISE #81</td>\n",
       "      <td>MA</td>\n",
       "      <td>01803</td>\n",
       "      <td>P</td>\n",
       "      <td>31.42</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>5142131721</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>4503082993600</td>\n",
       "      <td>OFFICE DEPOT #191</td>\n",
       "      <td>MD</td>\n",
       "      <td>20706</td>\n",
       "      <td>P</td>\n",
       "      <td>178.49</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>5142148452</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>5509006296254</td>\n",
       "      <td>FEDEX SHP 12/28/09 AB#</td>\n",
       "      <td>TN</td>\n",
       "      <td>38118</td>\n",
       "      <td>P</td>\n",
       "      <td>3.62</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>5142190439</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>5509006296254</td>\n",
       "      <td>FEDEX SHP 12/23/09 AB#</td>\n",
       "      <td>TN</td>\n",
       "      <td>38118</td>\n",
       "      <td>P</td>\n",
       "      <td>3.62</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</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",
       "      <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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96392</th>\n",
       "      <td>96749</td>\n",
       "      <td>5142276053</td>\n",
       "      <td>2010-12-31</td>\n",
       "      <td>3500000006160</td>\n",
       "      <td>BEST BUY      00001610</td>\n",
       "      <td>KY</td>\n",
       "      <td>41042</td>\n",
       "      <td>P</td>\n",
       "      <td>84.79</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96393</th>\n",
       "      <td>96750</td>\n",
       "      <td>5142225701</td>\n",
       "      <td>2010-12-31</td>\n",
       "      <td>8090710030950</td>\n",
       "      <td>MARKUS OFFICE SUPPLIES</td>\n",
       "      <td>OH</td>\n",
       "      <td>45248</td>\n",
       "      <td>P</td>\n",
       "      <td>118.75</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>2.800000</td>\n",
       "      <td>1.025981</td>\n",
       "      <td>4.285714</td>\n",
       "      <td>1.299964</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96394</th>\n",
       "      <td>96751</td>\n",
       "      <td>5142226486</td>\n",
       "      <td>2010-12-31</td>\n",
       "      <td>4503057341100</td>\n",
       "      <td>TECH PAC, INC</td>\n",
       "      <td>OH</td>\n",
       "      <td>45150</td>\n",
       "      <td>P</td>\n",
       "      <td>363.56</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>4.666667</td>\n",
       "      <td>6.281401</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>12.108333</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>8.268009</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96395</th>\n",
       "      <td>96752</td>\n",
       "      <td>5142244619</td>\n",
       "      <td>2010-12-31</td>\n",
       "      <td>8834000695412</td>\n",
       "      <td>BUY.COM</td>\n",
       "      <td>CA</td>\n",
       "      <td>92656</td>\n",
       "      <td>P</td>\n",
       "      <td>2202.03</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>4.666667</td>\n",
       "      <td>6.649026</td>\n",
       "      <td>4.307692</td>\n",
       "      <td>8.604703</td>\n",
       "      <td>2.608696</td>\n",
       "      <td>5.858854</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96396</th>\n",
       "      <td>96753</td>\n",
       "      <td>5142243247</td>\n",
       "      <td>2010-12-31</td>\n",
       "      <td>9108347680006</td>\n",
       "      <td>STAPLES NATIONAL #471</td>\n",
       "      <td>NJ</td>\n",
       "      <td>07606</td>\n",
       "      <td>P</td>\n",
       "      <td>554.64</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.305237</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>5.378886</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>8.823417</td>\n",
       "      <td>2.307692</td>\n",
       "      <td>12.025150</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>96397 rows × 872 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Recnum     Cardnum       Date       Merchnum        Merch description  \\\n",
       "0           1  5142190439 2010-01-01  5509006296254   FEDEX SHP 12/23/09 AB#   \n",
       "1           2  5142183973 2010-01-01    61003026333  SERVICE MERCHANDISE #81   \n",
       "2           3  5142131721 2010-01-01  4503082993600        OFFICE DEPOT #191   \n",
       "3           4  5142148452 2010-01-01  5509006296254   FEDEX SHP 12/28/09 AB#   \n",
       "4           5  5142190439 2010-01-01  5509006296254   FEDEX SHP 12/23/09 AB#   \n",
       "...       ...         ...        ...            ...                      ...   \n",
       "96392   96749  5142276053 2010-12-31  3500000006160   BEST BUY      00001610   \n",
       "96393   96750  5142225701 2010-12-31  8090710030950   MARKUS OFFICE SUPPLIES   \n",
       "96394   96751  5142226486 2010-12-31  4503057341100            TECH PAC, INC   \n",
       "96395   96752  5142244619 2010-12-31  8834000695412                  BUY.COM   \n",
       "96396   96753  5142243247 2010-12-31  9108347680006    STAPLES NATIONAL #471   \n",
       "\n",
       "      Merch state Merch zip Transtype   Amount  Fraud  ...  \\\n",
       "0              TN     38118         P     3.62      0  ...   \n",
       "1              MA     01803         P    31.42      0  ...   \n",
       "2              MD     20706         P   178.49      0  ...   \n",
       "3              TN     38118         P     3.62      0  ...   \n",
       "4              TN     38118         P     3.62      0  ...   \n",
       "...           ...       ...       ...      ...    ...  ...   \n",
       "96392          KY     41042         P    84.79      0  ...   \n",
       "96393          OH     45248         P   118.75      0  ...   \n",
       "96394          OH     45150         P   363.56      0  ...   \n",
       "96395          CA     92656         P  2202.03      0  ...   \n",
       "96396          NJ     07606         P   554.64      0  ...   \n",
       "\n",
       "      merch_zip_count_1_by_3 merch_zip_amount_1_by_3 merch_zip_count_1_by_7  \\\n",
       "0                        3.0                3.000000               7.000000   \n",
       "1                        3.0                3.000000               7.000000   \n",
       "2                        3.0                3.000000               7.000000   \n",
       "3                        3.0                3.000000               7.000000   \n",
       "4                        3.0                3.000000               7.000000   \n",
       "...                      ...                     ...                    ...   \n",
       "96392                    3.0                3.000000               7.000000   \n",
       "96393                    3.0                3.000000               7.000000   \n",
       "96394                    3.0                3.000000               4.666667   \n",
       "96395                    3.0                3.000000               4.666667   \n",
       "96396                    1.0                2.305237               2.333333   \n",
       "\n",
       "      merch_zip_amount_1_by_7 merch_zip_count_1_by_14  \\\n",
       "0                    7.000000               14.000000   \n",
       "1                    7.000000               14.000000   \n",
       "2                    7.000000               14.000000   \n",
       "3                    7.000000               14.000000   \n",
       "4                    7.000000               14.000000   \n",
       "...                       ...                     ...   \n",
       "96392                7.000000               14.000000   \n",
       "96393                7.000000                2.800000   \n",
       "96394                6.281401                8.000000   \n",
       "96395                6.649026                4.307692   \n",
       "96396                5.378886                2.333333   \n",
       "\n",
       "      merch_zip_amount_1_by_14 merch_zip_count_1_by_30  \\\n",
       "0                    14.000000               30.000000   \n",
       "1                    14.000000               30.000000   \n",
       "2                    14.000000               30.000000   \n",
       "3                    14.000000               30.000000   \n",
       "4                    14.000000               30.000000   \n",
       "...                        ...                     ...   \n",
       "96392                14.000000               30.000000   \n",
       "96393                 1.025981                4.285714   \n",
       "96394                12.108333                6.000000   \n",
       "96395                 8.604703                2.608696   \n",
       "96396                 8.823417                2.307692   \n",
       "\n",
       "      merch_zip_amount_1_by_30  dow  dow_risk  \n",
       "0                    30.000000    4  0.025994  \n",
       "1                    30.000000    4  0.025994  \n",
       "2                    30.000000    4  0.025994  \n",
       "3                    30.000000    4  0.025994  \n",
       "4                    30.000000    4  0.025994  \n",
       "...                        ...  ...       ...  \n",
       "96392                30.000000    4  0.025994  \n",
       "96393                 1.299964    4  0.025994  \n",
       "96394                 8.268009    4  0.025994  \n",
       "96395                 5.858854    4  0.025994  \n",
       "96396                12.025150    4  0.025994  \n",
       "\n",
       "[96397 rows x 872 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "macro-celtic",
   "metadata": {},
   "source": [
    "### Benford's Law Variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "alternative-potter",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove all transactions from Fedex\n",
    "remove_fedex = final[~final['Merch description'].str.contains('FEDEX')]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "computational-missile",
   "metadata": {},
   "source": [
    "#### By Cardnum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "id": "western-going",
   "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>Cardnum</th>\n",
       "      <th>Distribution</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5142110002</td>\n",
       "      <td>[150.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5142110081</td>\n",
       "      <td>[495.9, 636.2, 636.2, 495.9]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5142110313</td>\n",
       "      <td>[144.0, 44.0, 100.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5142110402</td>\n",
       "      <td>[506.5, 20.81, 30.26, 437.76, 54.0, 101.5, 30....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5142110434</td>\n",
       "      <td>[1551.64]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1635</th>\n",
       "      <td>5142310397</td>\n",
       "      <td>[357.94]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1636</th>\n",
       "      <td>5142310525</td>\n",
       "      <td>[318.11, 97.41, 187.69, 563.07]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1637</th>\n",
       "      <td>5142310598</td>\n",
       "      <td>[10.81, 103.5]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1638</th>\n",
       "      <td>5142310768</td>\n",
       "      <td>[100.0, 92.0, 100.0, 558.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1639</th>\n",
       "      <td>5142847398</td>\n",
       "      <td>[504.6, 712.58, 226.98, 776.48, 798.04, 956.44...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1640 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Cardnum                                       Distribution\n",
       "0     5142110002                                            [150.0]\n",
       "1     5142110081                       [495.9, 636.2, 636.2, 495.9]\n",
       "2     5142110313                               [144.0, 44.0, 100.0]\n",
       "3     5142110402  [506.5, 20.81, 30.26, 437.76, 54.0, 101.5, 30....\n",
       "4     5142110434                                          [1551.64]\n",
       "...          ...                                                ...\n",
       "1635  5142310397                                           [357.94]\n",
       "1636  5142310525                    [318.11, 97.41, 187.69, 563.07]\n",
       "1637  5142310598                                     [10.81, 103.5]\n",
       "1638  5142310768                        [100.0, 92.0, 100.0, 558.0]\n",
       "1639  5142847398  [504.6, 712.58, 226.98, 776.48, 798.04, 956.44...\n",
       "\n",
       "[1640 rows x 2 columns]"
      ]
     },
     "execution_count": 253,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cardnum_dist1 = remove_fedex.groupby('Cardnum').agg({'Amount':list}).reset_index().rename(columns = {'Amount':'Distribution'})\n",
    "cardnum_dist1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "id": "developed-decline",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['12', '0.1', '12', '0.0001', '23', '0.222', '0.2', '0.0002']"
      ]
     },
     "execution_count": 177,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# test\n",
    "text = '12, 0.1, 12, 33,0.0001, 23, 0.222,0.2,0.0002'\n",
    "re.findall(r'([12][0-9]*|0.[0]{0,}[12][0-9]*)',text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "id": "hazardous-spirituality",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 246 ms, sys: 3 ms, total: 249 ms\n",
      "Wall time: 249 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "cnt_low = []\n",
    "cnt_high = []\n",
    "for d in cardnum_dist1['Distribution']:\n",
    "    cnt = Counter()\n",
    "    for value in d:\n",
    "        if re.match(r'([12][0-9]*|0.[0]{0,}[12][0-9]*)',str(value)):\n",
    "            cnt['low']+=1\n",
    "        if re.match(r'([3-9][0-9]*|0.[0]{0,}[3-9][0-9]*)',str(value)):\n",
    "            cnt['high']+=1\n",
    "\n",
    "    cnt_low.append(cnt['low'])\n",
    "    cnt_high.append(cnt['high'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "id": "expanded-master",
   "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>Cardnum</th>\n",
       "      <th>Distribution</th>\n",
       "      <th>n_low</th>\n",
       "      <th>n_high</th>\n",
       "      <th>R</th>\n",
       "      <th>1/R</th>\n",
       "      <th>U</th>\n",
       "      <th>n</th>\n",
       "      <th>t</th>\n",
       "      <th>U*</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1177</th>\n",
       "      <td>5142253356</td>\n",
       "      <td>[27.41, 12.5, 19.95, 420.0, 19.95, 33.86, 20.1...</td>\n",
       "      <td>61</td>\n",
       "      <td>5</td>\n",
       "      <td>13.371200</td>\n",
       "      <td>0.074788</td>\n",
       "      <td>13.371200</td>\n",
       "      <td>66</td>\n",
       "      <td>51</td>\n",
       "      <td>13.371199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1541</th>\n",
       "      <td>5142299705</td>\n",
       "      <td>[475.0, 1921.0, 1385.0, 2051.25, 2295.0, 2006....</td>\n",
       "      <td>25</td>\n",
       "      <td>3</td>\n",
       "      <td>9.133333</td>\n",
       "      <td>0.109489</td>\n",
       "      <td>9.133333</td>\n",
       "      <td>28</td>\n",
       "      <td>13</td>\n",
       "      <td>9.027976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>725</th>\n",
       "      <td>5142197563</td>\n",
       "      <td>[30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30....</td>\n",
       "      <td>15</td>\n",
       "      <td>134</td>\n",
       "      <td>0.122687</td>\n",
       "      <td>8.150852</td>\n",
       "      <td>8.150852</td>\n",
       "      <td>149</td>\n",
       "      <td>134</td>\n",
       "      <td>8.150852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>697</th>\n",
       "      <td>5142194617</td>\n",
       "      <td>[22.5, 650.0, 480.0, 650.0, 650.0, 480.0, 650....</td>\n",
       "      <td>5</td>\n",
       "      <td>33</td>\n",
       "      <td>0.166061</td>\n",
       "      <td>6.021898</td>\n",
       "      <td>6.021898</td>\n",
       "      <td>38</td>\n",
       "      <td>23</td>\n",
       "      <td>6.019548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1449</th>\n",
       "      <td>5142288241</td>\n",
       "      <td>[98.5, 8.36, 79.99, 1617.91, 333.3, 30.3, 30.3...</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>0.084308</td>\n",
       "      <td>11.861314</td>\n",
       "      <td>11.861314</td>\n",
       "      <td>14</td>\n",
       "      <td>-1</td>\n",
       "      <td>5.533836</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",
       "    <tr>\n",
       "      <th>1478</th>\n",
       "      <td>5142291501</td>\n",
       "      <td>[336.0]</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>-14</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>947</th>\n",
       "      <td>5142225132</td>\n",
       "      <td>[627.75]</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>-14</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>941</th>\n",
       "      <td>5142224649</td>\n",
       "      <td>[95.59, 95.59]</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2</td>\n",
       "      <td>-13</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>938</th>\n",
       "      <td>5142224315</td>\n",
       "      <td>[112.93]</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>-14</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5142110002</td>\n",
       "      <td>[150.0]</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>-14</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1640 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Cardnum                                       Distribution  n_low  \\\n",
       "1177  5142253356  [27.41, 12.5, 19.95, 420.0, 19.95, 33.86, 20.1...     61   \n",
       "1541  5142299705  [475.0, 1921.0, 1385.0, 2051.25, 2295.0, 2006....     25   \n",
       "725   5142197563  [30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30....     15   \n",
       "697   5142194617  [22.5, 650.0, 480.0, 650.0, 650.0, 480.0, 650....      5   \n",
       "1449  5142288241  [98.5, 8.36, 79.99, 1617.91, 333.3, 30.3, 30.3...      1   \n",
       "...          ...                                                ...    ...   \n",
       "1478  5142291501                                            [336.0]      0   \n",
       "947   5142225132                                           [627.75]      0   \n",
       "941   5142224649                                     [95.59, 95.59]      0   \n",
       "938   5142224315                                           [112.93]      1   \n",
       "0     5142110002                                            [150.0]      1   \n",
       "\n",
       "      n_high          R        1/R          U    n    t         U*  \n",
       "1177       5  13.371200   0.074788  13.371200   66   51  13.371199  \n",
       "1541       3   9.133333   0.109489   9.133333   28   13   9.027976  \n",
       "725      134   0.122687   8.150852   8.150852  149  134   8.150852  \n",
       "697       33   0.166061   6.021898   6.021898   38   23   6.019548  \n",
       "1449      13   0.084308  11.861314  11.861314   14   -1   5.533836  \n",
       "...      ...        ...        ...        ...  ...  ...        ...  \n",
       "1478       1   1.000000   1.000000   1.000000    1  -14   1.000000  \n",
       "947        1   1.000000   1.000000   1.000000    1  -14   1.000000  \n",
       "941        2   1.000000   1.000000   1.000000    2  -13   1.000000  \n",
       "938        0   1.000000   1.000000   1.000000    1  -14   1.000000  \n",
       "0          0   1.000000   1.000000   1.000000    1  -14   1.000000  \n",
       "\n",
       "[1640 rows x 10 columns]"
      ]
     },
     "execution_count": 255,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "n_mid = 15\n",
    "c = 3\n",
    "cardnum_dist1['n_low'] = cnt_low\n",
    "cardnum_dist1['n_high'] = cnt_high\n",
    "cardnum_dist1['R'] = np.where((cardnum_dist1['n_high']!=0)&(cardnum_dist1['n_low']!=0), \n",
    "                             1.096 * cardnum_dist1['n_low'] / cardnum_dist1['n_high'],\n",
    "                             1\n",
    "                            )\n",
    "cardnum_dist1['1/R'] = 1/cardnum_dist1['R'] \n",
    "cardnum_dist1['U'] = np.where(cardnum_dist1['R']>=cardnum_dist1['1/R'],cardnum_dist1['R'],cardnum_dist1['1/R'])\n",
    "cardnum_dist1['n'] = cardnum_dist1['Distribution'].map(len)\n",
    "cardnum_dist1['t'] = cardnum_dist1['n'] - n_mid\n",
    "cardnum_dist1['U*'] = 1+(cardnum_dist1['U']-1)/(1+np.exp(-(cardnum_dist1['n']-n_mid)/c))\n",
    "cardnum_dist1.sort_values('U*',ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "id": "noble-appreciation",
   "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>Cardnum</th>\n",
       "      <th>Date</th>\n",
       "      <th>Distribution</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5142110002</td>\n",
       "      <td>2010-10-12</td>\n",
       "      <td>[150.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5142110081</td>\n",
       "      <td>2010-03-08</td>\n",
       "      <td>[495.9, 636.2]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5142110081</td>\n",
       "      <td>2010-11-26</td>\n",
       "      <td>[636.2]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5142110081</td>\n",
       "      <td>2010-12-27</td>\n",
       "      <td>[495.9]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5142110313</td>\n",
       "      <td>2010-10-07</td>\n",
       "      <td>[144.0, 44.0, 100.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58417</th>\n",
       "      <td>5142847398</td>\n",
       "      <td>2010-03-21</td>\n",
       "      <td>[199.14]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58418</th>\n",
       "      <td>5142847398</td>\n",
       "      <td>2010-03-22</td>\n",
       "      <td>[78.23]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58419</th>\n",
       "      <td>5142847398</td>\n",
       "      <td>2010-03-24</td>\n",
       "      <td>[440.06]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58420</th>\n",
       "      <td>5142847398</td>\n",
       "      <td>2010-03-28</td>\n",
       "      <td>[288.82]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58421</th>\n",
       "      <td>5142847398</td>\n",
       "      <td>2010-03-29</td>\n",
       "      <td>[736.55]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>58422 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Cardnum       Date          Distribution\n",
       "0      5142110002 2010-10-12               [150.0]\n",
       "1      5142110081 2010-03-08        [495.9, 636.2]\n",
       "2      5142110081 2010-11-26               [636.2]\n",
       "3      5142110081 2010-12-27               [495.9]\n",
       "4      5142110313 2010-10-07  [144.0, 44.0, 100.0]\n",
       "...           ...        ...                   ...\n",
       "58417  5142847398 2010-03-21              [199.14]\n",
       "58418  5142847398 2010-03-22               [78.23]\n",
       "58419  5142847398 2010-03-24              [440.06]\n",
       "58420  5142847398 2010-03-28              [288.82]\n",
       "58421  5142847398 2010-03-29              [736.55]\n",
       "\n",
       "[58422 rows x 3 columns]"
      ]
     },
     "execution_count": 256,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# use previous data\n",
    "cardnum_dist2 = remove_fedex.groupby(['Cardnum','Date']).agg({'Amount':list}).reset_index().rename(columns = {'Amount':'Distribution'})\n",
    "cardnum_dist2 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "id": "breeding-document",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 424 µs, sys: 1e+03 ns, total: 425 µs\n",
      "Wall time: 433 µs\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.generic.SeriesGroupBy object at 0x7fee362d6610>"
      ]
     },
     "execution_count": 250,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "cardnum_dist2.groupby(['Cardnum','Date'])['Distribution']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "olive-google",
   "metadata": {},
   "source": [
    "#### By Merchnum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "id": "structural-resource",
   "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>Merchnum</th>\n",
       "      <th>Distribution</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>003100006NOT6</td>\n",
       "      <td>[59.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>004740006ABC6</td>\n",
       "      <td>[21.75]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>005590006PNB6</td>\n",
       "      <td>[858.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>014430619  14</td>\n",
       "      <td>[235.06]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>014938913  51</td>\n",
       "      <td>[310.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13078</th>\n",
       "      <td>DU49038320006</td>\n",
       "      <td>[64.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13079</th>\n",
       "      <td>JCPENNE9 CO</td>\n",
       "      <td>[49.99, 20.99, 72.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13080</th>\n",
       "      <td>PENNE9 CO #05</td>\n",
       "      <td>[85.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13081</th>\n",
       "      <td>PENNE9 CO #68</td>\n",
       "      <td>[42.38]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13082</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>[266.0, 320.0, 970.0, 82.59, 17.59, 19.69, 17....</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13083 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Merchnum                                       Distribution\n",
       "0      003100006NOT6                                             [59.0]\n",
       "1      004740006ABC6                                            [21.75]\n",
       "2      005590006PNB6                                            [858.0]\n",
       "3      014430619  14                                           [235.06]\n",
       "4      014938913  51                                            [310.0]\n",
       "...              ...                                                ...\n",
       "13078  DU49038320006                                             [64.0]\n",
       "13079    JCPENNE9 CO                               [49.99, 20.99, 72.0]\n",
       "13080  PENNE9 CO #05                                             [85.0]\n",
       "13081  PENNE9 CO #68                                            [42.38]\n",
       "13082        Unknown  [266.0, 320.0, 970.0, 82.59, 17.59, 19.69, 17....\n",
       "\n",
       "[13083 rows x 2 columns]"
      ]
     },
     "execution_count": 251,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merchnum_dist1 = remove_fedex.groupby('Merchnum').agg({'Amount':list}).reset_index().rename(columns = {'Amount':'Distribution'})\n",
    "merchnum_dist1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "id": "suitable-journey",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 256 ms, sys: 3.24 ms, total: 259 ms\n",
      "Wall time: 258 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "cnt_low = []\n",
    "cnt_high = []\n",
    "for d in merchnum_dist1['Distribution']:\n",
    "    cnt = Counter()\n",
    "    for value in d:\n",
    "        if re.match(r'([12][0-9]*|0.[0]{0,}[12][0-9]*)',str(value)):\n",
    "            cnt['low']+=1\n",
    "        if re.match(r'([3-9][0-9]*|0.[0]{0,}[3-9][0-9]*)',str(value)):\n",
    "            cnt['high']+=1\n",
    "\n",
    "    cnt_low.append(cnt['low'])\n",
    "    cnt_high.append(cnt['high'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "id": "acting-seminar",
   "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>Merchnum</th>\n",
       "      <th>Distribution</th>\n",
       "      <th>n_low</th>\n",
       "      <th>n_high</th>\n",
       "      <th>R</th>\n",
       "      <th>1/R</th>\n",
       "      <th>U</th>\n",
       "      <th>n</th>\n",
       "      <th>t</th>\n",
       "      <th>U*</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9819</th>\n",
       "      <td>8078200641472</td>\n",
       "      <td>[1.95, 1.95, 1.95, 1.95, 1.95, 1.95, 1.95, 1.9...</td>\n",
       "      <td>59</td>\n",
       "      <td>1</td>\n",
       "      <td>64.664000</td>\n",
       "      <td>0.015465</td>\n",
       "      <td>64.664000</td>\n",
       "      <td>60</td>\n",
       "      <td>45</td>\n",
       "      <td>64.663981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9913</th>\n",
       "      <td>808998385332</td>\n",
       "      <td>[370.0, 370.0, 370.0, 370.0, 370.0, 370.0, 370...</td>\n",
       "      <td>1</td>\n",
       "      <td>36</td>\n",
       "      <td>0.030444</td>\n",
       "      <td>32.846715</td>\n",
       "      <td>32.846715</td>\n",
       "      <td>37</td>\n",
       "      <td>22</td>\n",
       "      <td>32.825921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4979</th>\n",
       "      <td>55158027</td>\n",
       "      <td>[100.0, 100.0, 100.0, 100.0, 100.0, 100.0, 100...</td>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "      <td>29.592000</td>\n",
       "      <td>0.033793</td>\n",
       "      <td>29.592000</td>\n",
       "      <td>28</td>\n",
       "      <td>13</td>\n",
       "      <td>29.221627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2602</th>\n",
       "      <td>3910694900001</td>\n",
       "      <td>[295.0, 295.0, 295.0, 295.0, 295.0, 295.0, 295...</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>27.400000</td>\n",
       "      <td>0.036496</td>\n",
       "      <td>27.400000</td>\n",
       "      <td>26</td>\n",
       "      <td>11</td>\n",
       "      <td>26.741995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10661</th>\n",
       "      <td>8889817332</td>\n",
       "      <td>[19.95, 19.95, 19.95, 19.95, 19.95, 19.95, 19....</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>26.304000</td>\n",
       "      <td>0.038017</td>\n",
       "      <td>26.304000</td>\n",
       "      <td>25</td>\n",
       "      <td>10</td>\n",
       "      <td>25.432399</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",
       "    <tr>\n",
       "      <th>5295</th>\n",
       "      <td>5803300639804</td>\n",
       "      <td>[490.05, 641.61, 781.14, 815.91]</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>-11</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5297</th>\n",
       "      <td>5803301844192</td>\n",
       "      <td>[852.27]</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>-14</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5298</th>\n",
       "      <td>5803301844883</td>\n",
       "      <td>[2257.0, 100.0, 2336.0]</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3</td>\n",
       "      <td>-12</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5300</th>\n",
       "      <td>5803302500373</td>\n",
       "      <td>[79.96]</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>-14</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6541</th>\n",
       "      <td>6070099010067</td>\n",
       "      <td>[182.08]</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>-14</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13083 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Merchnum                                       Distribution  \\\n",
       "9819   8078200641472  [1.95, 1.95, 1.95, 1.95, 1.95, 1.95, 1.95, 1.9...   \n",
       "9913    808998385332  [370.0, 370.0, 370.0, 370.0, 370.0, 370.0, 370...   \n",
       "4979        55158027  [100.0, 100.0, 100.0, 100.0, 100.0, 100.0, 100...   \n",
       "2602   3910694900001  [295.0, 295.0, 295.0, 295.0, 295.0, 295.0, 295...   \n",
       "10661     8889817332  [19.95, 19.95, 19.95, 19.95, 19.95, 19.95, 19....   \n",
       "...              ...                                                ...   \n",
       "5295   5803300639804                   [490.05, 641.61, 781.14, 815.91]   \n",
       "5297   5803301844192                                           [852.27]   \n",
       "5298   5803301844883                            [2257.0, 100.0, 2336.0]   \n",
       "5300   5803302500373                                            [79.96]   \n",
       "6541   6070099010067                                           [182.08]   \n",
       "\n",
       "       n_low  n_high          R        1/R          U   n   t         U*  \n",
       "9819      59       1  64.664000   0.015465  64.664000  60  45  64.663981  \n",
       "9913       1      36   0.030444  32.846715  32.846715  37  22  32.825921  \n",
       "4979      27       1  29.592000   0.033793  29.592000  28  13  29.221627  \n",
       "2602      25       1  27.400000   0.036496  27.400000  26  11  26.741995  \n",
       "10661     24       1  26.304000   0.038017  26.304000  25  10  25.432399  \n",
       "...      ...     ...        ...        ...        ...  ..  ..        ...  \n",
       "5295       0       4   1.000000   1.000000   1.000000   4 -11   1.000000  \n",
       "5297       0       1   1.000000   1.000000   1.000000   1 -14   1.000000  \n",
       "5298       3       0   1.000000   1.000000   1.000000   3 -12   1.000000  \n",
       "5300       0       1   1.000000   1.000000   1.000000   1 -14   1.000000  \n",
       "6541       1       0   1.000000   1.000000   1.000000   1 -14   1.000000  \n",
       "\n",
       "[13083 rows x 10 columns]"
      ]
     },
     "execution_count": 259,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "n_mid = 15\n",
    "c = 3\n",
    "merchnum_dist1['n_low'] = cnt_low\n",
    "merchnum_dist1['n_high'] = cnt_high\n",
    "merchnum_dist1['R'] = np.where((merchnum_dist1['n_high']!=0)&(merchnum_dist1['n_low']!=0), \n",
    "                             1.096 * merchnum_dist1['n_low'] / merchnum_dist1['n_high'],\n",
    "                             1\n",
    "                            )\n",
    "merchnum_dist1['1/R'] = 1/merchnum_dist1['R'] \n",
    "merchnum_dist1['U'] = np.where(merchnum_dist1['R']>=merchnum_dist1['1/R'],merchnum_dist1['R'],merchnum_dist1['1/R'])\n",
    "merchnum_dist1['n'] = merchnum_dist1['Distribution'].map(len)\n",
    "merchnum_dist1['t'] = merchnum_dist1['n'] - n_mid\n",
    "merchnum_dist1['U*'] = 1+(merchnum_dist1['U']-1)/(1+np.exp(-(merchnum_dist1['n']-n_mid)/c))\n",
    "merchnum_dist1.sort_values('U*',ascending = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "allied-chemical",
   "metadata": {},
   "source": [
    "#### Combine data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "id": "minimal-bunch",
   "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>Recnum</th>\n",
       "      <th>Cardnum</th>\n",
       "      <th>Date</th>\n",
       "      <th>Merchnum</th>\n",
       "      <th>Merch description</th>\n",
       "      <th>Merch state</th>\n",
       "      <th>Merch zip</th>\n",
       "      <th>Transtype</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Fraud</th>\n",
       "      <th>...</th>\n",
       "      <th>merch_zip_count_1_by_3</th>\n",
       "      <th>merch_zip_amount_1_by_3</th>\n",
       "      <th>merch_zip_count_1_by_7</th>\n",
       "      <th>merch_zip_amount_1_by_7</th>\n",
       "      <th>merch_zip_count_1_by_14</th>\n",
       "      <th>merch_zip_amount_1_by_14</th>\n",
       "      <th>merch_zip_count_1_by_30</th>\n",
       "      <th>merch_zip_amount_1_by_30</th>\n",
       "      <th>dow</th>\n",
       "      <th>dow_risk</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5142190439</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>5509006296254</td>\n",
       "      <td>FEDEX SHP 12/23/09 AB#</td>\n",
       "      <td>TN</td>\n",
       "      <td>38118</td>\n",
       "      <td>P</td>\n",
       "      <td>3.62</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>5142183973</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>61003026333</td>\n",
       "      <td>SERVICE MERCHANDISE #81</td>\n",
       "      <td>MA</td>\n",
       "      <td>01803</td>\n",
       "      <td>P</td>\n",
       "      <td>31.42</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>5142131721</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>4503082993600</td>\n",
       "      <td>OFFICE DEPOT #191</td>\n",
       "      <td>MD</td>\n",
       "      <td>20706</td>\n",
       "      <td>P</td>\n",
       "      <td>178.49</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>5142148452</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>5509006296254</td>\n",
       "      <td>FEDEX SHP 12/28/09 AB#</td>\n",
       "      <td>TN</td>\n",
       "      <td>38118</td>\n",
       "      <td>P</td>\n",
       "      <td>3.62</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>5142190439</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>5509006296254</td>\n",
       "      <td>FEDEX SHP 12/23/09 AB#</td>\n",
       "      <td>TN</td>\n",
       "      <td>38118</td>\n",
       "      <td>P</td>\n",
       "      <td>3.62</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</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",
       "      <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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96392</th>\n",
       "      <td>96749</td>\n",
       "      <td>5142276053</td>\n",
       "      <td>2010-12-31</td>\n",
       "      <td>3500000006160</td>\n",
       "      <td>BEST BUY      00001610</td>\n",
       "      <td>KY</td>\n",
       "      <td>41042</td>\n",
       "      <td>P</td>\n",
       "      <td>84.79</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96393</th>\n",
       "      <td>96750</td>\n",
       "      <td>5142225701</td>\n",
       "      <td>2010-12-31</td>\n",
       "      <td>8090710030950</td>\n",
       "      <td>MARKUS OFFICE SUPPLIES</td>\n",
       "      <td>OH</td>\n",
       "      <td>45248</td>\n",
       "      <td>P</td>\n",
       "      <td>118.75</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>2.800000</td>\n",
       "      <td>1.025981</td>\n",
       "      <td>4.285714</td>\n",
       "      <td>1.299964</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96394</th>\n",
       "      <td>96751</td>\n",
       "      <td>5142226486</td>\n",
       "      <td>2010-12-31</td>\n",
       "      <td>4503057341100</td>\n",
       "      <td>TECH PAC, INC</td>\n",
       "      <td>OH</td>\n",
       "      <td>45150</td>\n",
       "      <td>P</td>\n",
       "      <td>363.56</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>4.666667</td>\n",
       "      <td>6.281401</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>12.108333</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>8.268009</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96395</th>\n",
       "      <td>96752</td>\n",
       "      <td>5142244619</td>\n",
       "      <td>2010-12-31</td>\n",
       "      <td>8834000695412</td>\n",
       "      <td>BUY.COM</td>\n",
       "      <td>CA</td>\n",
       "      <td>92656</td>\n",
       "      <td>P</td>\n",
       "      <td>2202.03</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>4.666667</td>\n",
       "      <td>6.649026</td>\n",
       "      <td>4.307692</td>\n",
       "      <td>8.604703</td>\n",
       "      <td>2.608696</td>\n",
       "      <td>5.858854</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96396</th>\n",
       "      <td>96753</td>\n",
       "      <td>5142243247</td>\n",
       "      <td>2010-12-31</td>\n",
       "      <td>9108347680006</td>\n",
       "      <td>STAPLES NATIONAL #471</td>\n",
       "      <td>NJ</td>\n",
       "      <td>07606</td>\n",
       "      <td>P</td>\n",
       "      <td>554.64</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.305237</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>5.378886</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>8.823417</td>\n",
       "      <td>2.307692</td>\n",
       "      <td>12.025150</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>96397 rows × 872 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Recnum     Cardnum       Date       Merchnum        Merch description  \\\n",
       "0           1  5142190439 2010-01-01  5509006296254   FEDEX SHP 12/23/09 AB#   \n",
       "1           2  5142183973 2010-01-01    61003026333  SERVICE MERCHANDISE #81   \n",
       "2           3  5142131721 2010-01-01  4503082993600        OFFICE DEPOT #191   \n",
       "3           4  5142148452 2010-01-01  5509006296254   FEDEX SHP 12/28/09 AB#   \n",
       "4           5  5142190439 2010-01-01  5509006296254   FEDEX SHP 12/23/09 AB#   \n",
       "...       ...         ...        ...            ...                      ...   \n",
       "96392   96749  5142276053 2010-12-31  3500000006160   BEST BUY      00001610   \n",
       "96393   96750  5142225701 2010-12-31  8090710030950   MARKUS OFFICE SUPPLIES   \n",
       "96394   96751  5142226486 2010-12-31  4503057341100            TECH PAC, INC   \n",
       "96395   96752  5142244619 2010-12-31  8834000695412                  BUY.COM   \n",
       "96396   96753  5142243247 2010-12-31  9108347680006    STAPLES NATIONAL #471   \n",
       "\n",
       "      Merch state Merch zip Transtype   Amount  Fraud  ...  \\\n",
       "0              TN     38118         P     3.62      0  ...   \n",
       "1              MA     01803         P    31.42      0  ...   \n",
       "2              MD     20706         P   178.49      0  ...   \n",
       "3              TN     38118         P     3.62      0  ...   \n",
       "4              TN     38118         P     3.62      0  ...   \n",
       "...           ...       ...       ...      ...    ...  ...   \n",
       "96392          KY     41042         P    84.79      0  ...   \n",
       "96393          OH     45248         P   118.75      0  ...   \n",
       "96394          OH     45150         P   363.56      0  ...   \n",
       "96395          CA     92656         P  2202.03      0  ...   \n",
       "96396          NJ     07606         P   554.64      0  ...   \n",
       "\n",
       "      merch_zip_count_1_by_3 merch_zip_amount_1_by_3 merch_zip_count_1_by_7  \\\n",
       "0                        3.0                3.000000               7.000000   \n",
       "1                        3.0                3.000000               7.000000   \n",
       "2                        3.0                3.000000               7.000000   \n",
       "3                        3.0                3.000000               7.000000   \n",
       "4                        3.0                3.000000               7.000000   \n",
       "...                      ...                     ...                    ...   \n",
       "96392                    3.0                3.000000               7.000000   \n",
       "96393                    3.0                3.000000               7.000000   \n",
       "96394                    3.0                3.000000               4.666667   \n",
       "96395                    3.0                3.000000               4.666667   \n",
       "96396                    1.0                2.305237               2.333333   \n",
       "\n",
       "      merch_zip_amount_1_by_7 merch_zip_count_1_by_14  \\\n",
       "0                    7.000000               14.000000   \n",
       "1                    7.000000               14.000000   \n",
       "2                    7.000000               14.000000   \n",
       "3                    7.000000               14.000000   \n",
       "4                    7.000000               14.000000   \n",
       "...                       ...                     ...   \n",
       "96392                7.000000               14.000000   \n",
       "96393                7.000000                2.800000   \n",
       "96394                6.281401                8.000000   \n",
       "96395                6.649026                4.307692   \n",
       "96396                5.378886                2.333333   \n",
       "\n",
       "      merch_zip_amount_1_by_14 merch_zip_count_1_by_30  \\\n",
       "0                    14.000000               30.000000   \n",
       "1                    14.000000               30.000000   \n",
       "2                    14.000000               30.000000   \n",
       "3                    14.000000               30.000000   \n",
       "4                    14.000000               30.000000   \n",
       "...                        ...                     ...   \n",
       "96392                14.000000               30.000000   \n",
       "96393                 1.025981                4.285714   \n",
       "96394                12.108333                6.000000   \n",
       "96395                 8.604703                2.608696   \n",
       "96396                 8.823417                2.307692   \n",
       "\n",
       "      merch_zip_amount_1_by_30  dow  dow_risk  \n",
       "0                    30.000000    4  0.025994  \n",
       "1                    30.000000    4  0.025994  \n",
       "2                    30.000000    4  0.025994  \n",
       "3                    30.000000    4  0.025994  \n",
       "4                    30.000000    4  0.025994  \n",
       "...                        ...  ...       ...  \n",
       "96392                30.000000    4  0.025994  \n",
       "96393                 1.299964    4  0.025994  \n",
       "96394                 8.268009    4  0.025994  \n",
       "96395                 5.858854    4  0.025994  \n",
       "96396                12.025150    4  0.025994  \n",
       "\n",
       "[96397 rows x 872 columns]"
      ]
     },
     "execution_count": 264,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_2 = final.copy()\n",
    "final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 267,
   "id": "olive-conversation",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 13.1 s, sys: 12.8 s, total: 25.9 s\n",
      "Wall time: 26 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "final = final.merge(merchnum_dist1[['Merchnum','U*']],how = 'left',on='Merchnum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 270,
   "id": "false-highlight",
   "metadata": {},
   "outputs": [],
   "source": [
    "final.rename(columns = {'U*':'U*_by_merchnum'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 273,
   "id": "strategic-perth",
   "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>Recnum</th>\n",
       "      <th>Cardnum</th>\n",
       "      <th>Date</th>\n",
       "      <th>Merchnum</th>\n",
       "      <th>Merch description</th>\n",
       "      <th>Merch state</th>\n",
       "      <th>Merch zip</th>\n",
       "      <th>Transtype</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Fraud</th>\n",
       "      <th>...</th>\n",
       "      <th>merch_zip_count_1_by_7</th>\n",
       "      <th>merch_zip_amount_1_by_7</th>\n",
       "      <th>merch_zip_count_1_by_14</th>\n",
       "      <th>merch_zip_amount_1_by_14</th>\n",
       "      <th>merch_zip_count_1_by_30</th>\n",
       "      <th>merch_zip_amount_1_by_30</th>\n",
       "      <th>dow</th>\n",
       "      <th>dow_risk</th>\n",
       "      <th>U*_by_merchnum</th>\n",
       "      <th>U*_by_cardnum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5142190439</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>5509006296254</td>\n",
       "      <td>FEDEX SHP 12/23/09 AB#</td>\n",
       "      <td>TN</td>\n",
       "      <td>38118</td>\n",
       "      <td>P</td>\n",
       "      <td>3.62</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.178008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>5142183973</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>61003026333</td>\n",
       "      <td>SERVICE MERCHANDISE #81</td>\n",
       "      <td>MA</td>\n",
       "      <td>01803</td>\n",
       "      <td>P</td>\n",
       "      <td>31.42</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.604857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>5142131721</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>4503082993600</td>\n",
       "      <td>OFFICE DEPOT #191</td>\n",
       "      <td>MD</td>\n",
       "      <td>20706</td>\n",
       "      <td>P</td>\n",
       "      <td>178.49</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "      <td>1.025818</td>\n",
       "      <td>2.368143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>5142148452</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>5509006296254</td>\n",
       "      <td>FEDEX SHP 12/28/09 AB#</td>\n",
       "      <td>TN</td>\n",
       "      <td>38118</td>\n",
       "      <td>P</td>\n",
       "      <td>3.62</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.044105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>5142190439</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>5509006296254</td>\n",
       "      <td>FEDEX SHP 12/23/09 AB#</td>\n",
       "      <td>TN</td>\n",
       "      <td>38118</td>\n",
       "      <td>P</td>\n",
       "      <td>3.62</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.025994</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.178008</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 874 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Recnum     Cardnum       Date       Merchnum        Merch description  \\\n",
       "0       1  5142190439 2010-01-01  5509006296254   FEDEX SHP 12/23/09 AB#   \n",
       "1       2  5142183973 2010-01-01    61003026333  SERVICE MERCHANDISE #81   \n",
       "2       3  5142131721 2010-01-01  4503082993600        OFFICE DEPOT #191   \n",
       "3       4  5142148452 2010-01-01  5509006296254   FEDEX SHP 12/28/09 AB#   \n",
       "4       5  5142190439 2010-01-01  5509006296254   FEDEX SHP 12/23/09 AB#   \n",
       "\n",
       "  Merch state Merch zip Transtype  Amount  Fraud  ... merch_zip_count_1_by_7  \\\n",
       "0          TN     38118         P    3.62      0  ...                    7.0   \n",
       "1          MA     01803         P   31.42      0  ...                    7.0   \n",
       "2          MD     20706         P  178.49      0  ...                    7.0   \n",
       "3          TN     38118         P    3.62      0  ...                    7.0   \n",
       "4          TN     38118         P    3.62      0  ...                    7.0   \n",
       "\n",
       "  merch_zip_amount_1_by_7 merch_zip_count_1_by_14 merch_zip_amount_1_by_14  \\\n",
       "0                     7.0                    14.0                     14.0   \n",
       "1                     7.0                    14.0                     14.0   \n",
       "2                     7.0                    14.0                     14.0   \n",
       "3                     7.0                    14.0                     14.0   \n",
       "4                     7.0                    14.0                     14.0   \n",
       "\n",
       "  merch_zip_count_1_by_30 merch_zip_amount_1_by_30 dow  dow_risk  \\\n",
       "0                    30.0                     30.0   4  0.025994   \n",
       "1                    30.0                     30.0   4  0.025994   \n",
       "2                    30.0                     30.0   4  0.025994   \n",
       "3                    30.0                     30.0   4  0.025994   \n",
       "4                    30.0                     30.0   4  0.025994   \n",
       "\n",
       "   U*_by_merchnum  U*_by_cardnum  \n",
       "0             NaN       2.178008  \n",
       "1        1.000000       1.604857  \n",
       "2        1.025818       2.368143  \n",
       "3             NaN       1.044105  \n",
       "4             NaN       2.178008  \n",
       "\n",
       "[5 rows x 874 columns]"
      ]
     },
     "execution_count": 273,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final = final.merge(cardnum_dist1[['Cardnum','U*']],how = 'left',on='Cardnum').\\\n",
    "        rename(columns = {'U*':'U*_by_cardnum'})\n",
    "final.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "id": "unlike-thought",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 402 ms, sys: 5.58 ms, total: 408 ms\n",
      "Wall time: 407 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# fill na in U* with the default value——the bad rate for the entire population.\n",
    "cnt_low = []\n",
    "cnt_high = []\n",
    "for d in final['Amount']:\n",
    "    cnt = Counter()\n",
    "    if re.match(r'([12][0-9]*|0.[0]{0,}[12][0-9]*)',str(d)):\n",
    "        cnt['low']+=1\n",
    "    if re.match(r'([3-9][0-9]*|0.[0]{0,}[3-9][0-9]*)',str(d)):\n",
    "        cnt['high']+=1\n",
    "\n",
    "    cnt_low.append(cnt['low'])\n",
    "    cnt_high.append(cnt['high'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "id": "convertible-advocate",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.1434914571701709"
      ]
     },
     "execution_count": 288,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "n_mid = 15\n",
    "c = 3\n",
    "n = len(final)\n",
    "R = 1.096*sum(cnt_low)/sum(cnt_high)\n",
    "U = max(R,1/R)\n",
    "U_all = 1+(U-1)/(1+np.exp(-(n-n_mid)/c))\n",
    "U_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 290,
   "id": "measured-metro",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Recnum                      0\n",
       "Cardnum                     0\n",
       "Date                        0\n",
       "Merchnum                    0\n",
       "Merch description           0\n",
       "                           ..\n",
       "merch_zip_amount_1_by_30    0\n",
       "dow                         0\n",
       "dow_risk                    0\n",
       "U*_by_merchnum              0\n",
       "U*_by_cardnum               0\n",
       "Length: 874, dtype: int64"
      ]
     },
     "execution_count": 290,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final['U*_by_merchnum'].fillna(U_all,inplace=True)\n",
    "final['U*_by_cardnum'].fillna(U_all,inplace=True)\n",
    "final.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "id": "declared-myanmar",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 54.4 s, sys: 657 ms, total: 55.1 s\n",
      "Wall time: 55.2 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "final.to_csv('after_feature_creation.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "russian-wound",
   "metadata": {},
   "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.8.2"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "288px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}