02.A. Data Exploration and Feature Engineering - Cash Out & Transfer.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from IPython.display import clear_output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"/Users/Abhirup/Desktop/Projects/HSBC DS Assignment/PS_20174392719_1491204439457_log.csv\")\n",
    "df.sort_values(by=['step'])\n",
    "df = df.rename(columns={'oldbalanceOrg':'oldBalanceOrig', 'newbalanceOrig':'newBalanceOrig', \\\n",
    "                        'oldbalanceDest':'oldBalanceDest', 'newbalanceDest':'newBalanceDest'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>step</th>\n",
       "      <th>type</th>\n",
       "      <th>amount</th>\n",
       "      <th>nameOrig</th>\n",
       "      <th>oldBalanceOrig</th>\n",
       "      <th>newBalanceOrig</th>\n",
       "      <th>nameDest</th>\n",
       "      <th>oldBalanceDest</th>\n",
       "      <th>newBalanceDest</th>\n",
       "      <th>isFraud</th>\n",
       "      <th>isFlaggedFraud</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>TRANSFER</td>\n",
       "      <td>181.00</td>\n",
       "      <td>C1305486145</td>\n",
       "      <td>181.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C553264065</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>CASH_OUT</td>\n",
       "      <td>181.00</td>\n",
       "      <td>C840083671</td>\n",
       "      <td>181.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C38997010</td>\n",
       "      <td>21182.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>CASH_OUT</td>\n",
       "      <td>229133.94</td>\n",
       "      <td>C905080434</td>\n",
       "      <td>15325.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C476402209</td>\n",
       "      <td>5083.0</td>\n",
       "      <td>51513.44</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>TRANSFER</td>\n",
       "      <td>215310.30</td>\n",
       "      <td>C1670993182</td>\n",
       "      <td>705.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C1100439041</td>\n",
       "      <td>22425.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>TRANSFER</td>\n",
       "      <td>311685.89</td>\n",
       "      <td>C1984094095</td>\n",
       "      <td>10835.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>C932583850</td>\n",
       "      <td>6267.0</td>\n",
       "      <td>2719172.89</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   step      type     amount     nameOrig  oldBalanceOrig  newBalanceOrig  \\\n",
       "0     1  TRANSFER     181.00  C1305486145           181.0             0.0   \n",
       "1     1  CASH_OUT     181.00   C840083671           181.0             0.0   \n",
       "2     1  CASH_OUT  229133.94   C905080434         15325.0             0.0   \n",
       "3     1  TRANSFER  215310.30  C1670993182           705.0             0.0   \n",
       "4     1  TRANSFER  311685.89  C1984094095         10835.0             0.0   \n",
       "\n",
       "      nameDest  oldBalanceDest  newBalanceDest  isFraud  isFlaggedFraud  \n",
       "0   C553264065             0.0            0.00        1               0  \n",
       "1    C38997010         21182.0            0.00        1               0  \n",
       "2   C476402209          5083.0        51513.44        0               0  \n",
       "3  C1100439041         22425.0            0.00        0               0  \n",
       "4   C932583850          6267.0      2719172.89        0               0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cot=df[df['type'].isin(['CASH_OUT','TRANSFER'])]\n",
    "df_cot=df_cot.reset_index(drop=True)\n",
    "df_cot.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2770409, 11)\n",
      "743\n"
     ]
    }
   ],
   "source": [
    "print(df_cot.shape)\n",
    "print(len(df_cot['step'].drop_duplicates()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0.257, 186.5]     990974\n",
       "(186.5, 372.0]    1317246\n",
       "(372.0, 557.5]     380545\n",
       "(557.5, 743.0]      81644\n",
       "Name: step, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cot['step'].value_counts(bins=4).sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0     990974\n",
       "2.0    1317246\n",
       "3.0     380559\n",
       "4.0      81630\n",
       "Name: week, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Since Steps are hours, make bins of observations to weeks\n",
    "for i in range(4):\n",
    "    df_cot.loc[(df_cot['step']>186*i) & (df_cot['step']<=186*(i+1)),'week'] =i+1\n",
    "df_cot['week'].value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2770409"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_cot)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Customers Transacting both in Week 1 and Week 2\n",
      "628\n",
      "\n",
      "Customers Transacting both in Week 1 and Week 3\n",
      "178\n",
      "\n",
      "Customers Transacting both in Week 2 and Week 3\n",
      "210\n",
      "\n",
      "Customers Transacting both in Week 2 and Week 4\n",
      "48\n",
      "\n",
      "Customers Transacting both in Week 3 and Week 4\n",
      "10\n",
      "\n",
      "Customers Transacting both in Week 3 and Week 5\n",
      "0\n"
     ]
    }
   ],
   "source": [
    "for i in range(1,4):\n",
    "    print(\"\\nCustomers Transacting both in Week %s and Week %s\"%(i,i+1))\n",
    "    print(pd.merge(df_cot.loc[df_cot['week']==i,'nameOrig'],df_cot.loc[df_cot['week']==i+1,'nameOrig'],on='nameOrig')\n",
    "      ['nameOrig'].nunique())\n",
    "    print(\"\\nCustomers Transacting both in Week %s and Week %s\"%(i,i+2))\n",
    "    print(pd.merge(df_cot.loc[df_cot['week']==i,'nameOrig'],df_cot.loc[df_cot['week']==i+2,'nameOrig'],on='nameOrig')\n",
    "      ['nameOrig'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Current Progress: 7.76 %\n"
     ]
    }
   ],
   "source": [
    "df_cot_f=pd.DataFrame()\n",
    "for i in range(990974,len(df_cot)):\n",
    "    \n",
    "    clear_output(wait=True)\n",
    "    \n",
    "    name=df_cot.iloc[i,3]\n",
    "    step=df_cot.iloc[i,0]\n",
    "    df_t1=df_cot.loc[(df_cot['step']>=step-186)&(df_cot['step']<step)&(df_cot['nameOrig']==name)\n",
    "                    ,['step','nameOrig','amount']].groupby('nameOrig',as_index=False)['amount'].sum()\n",
    "    df_t2=df_cot.loc[(df_cot['step']>=step-186)&(df_cot['step']<step)&(df_cot['nameOrig']==name)\n",
    "                     ,['step','nameOrig','amount']].groupby('nameOrig',as_index=False)['amount'].count()\n",
    "    \n",
    "    df_t3=df_cot.iloc[[i]]\n",
    "    \n",
    "    if df_t1.empty==True:\n",
    "        df_t3['$TransL1W']=0\n",
    "        df_t3['NumTransL1W']=0\n",
    "        df_cot_f=df_cot_f.append(df_t3)\n",
    "    elif df_t1.empty==False:\n",
    "        df_t1=df_t1.rename(columns={'amount':'$TransL1W'})\n",
    "        df_t2=df_t2.rename(columns={'amount':'NumTransL1W'})\n",
    "        df_t=pd.merge(df_t1,df_t2,on='nameOrig')\n",
    "        df_t3=pd.merge(df_t3,df_t,how='left',on='nameOrig')\n",
    "        df_cot_f=df_cot_f.append(df_t3)\n",
    "        \n",
    "    print(\"Current Progress:\",np.round((i-990973)/(len(df_cot)-990973)*100,2),'%')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cot_f.to_csv('/Users/Abhirup/Desktop/Projects/HSBC DS Assignment/df_cot_f.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}