transfer_theData.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "465183fa",
   "metadata": {},
   "source": [
    "### Dataset Preparation and basic analysis\n",
    "The given dataset is in txt format. For convenience lets tranfer the dataset into csv file and do some basic analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "316eb235",
   "metadata": {},
   "outputs": [],
   "source": [
    "#import required libraries\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2a1bdebe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "l =  25\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gT</th>\n",
       "      <th>sId</th>\n",
       "      <th>rId</th>\n",
       "      <th>sAcc</th>\n",
       "      <th>rAcc</th>\n",
       "      <th>TranAmount</th>\n",
       "      <th>TranType</th>\n",
       "      <th>TranStatus</th>\n",
       "      <th>sBalbefore</th>\n",
       "      <th>sBalAfter</th>\n",
       "      <th>...</th>\n",
       "      <th>sf4</th>\n",
       "      <th>sTD</th>\n",
       "      <th>rTD</th>\n",
       "      <th>sAccID</th>\n",
       "      <th>ef1</th>\n",
       "      <th>ef2</th>\n",
       "      <th>NoDescription</th>\n",
       "      <th>TranTS</th>\n",
       "      <th>sType</th>\n",
       "      <th>rType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>N-RegDep</td>\n",
       "      <td>PN_Ret2</td>\n",
       "      <td>PN_EU_0_261</td>\n",
       "      <td>RAcc2</td>\n",
       "      <td>EUAcc0_261</td>\n",
       "      <td>131926.49</td>\n",
       "      <td>Dt</td>\n",
       "      <td>SU</td>\n",
       "      <td>1.000000e+09</td>\n",
       "      <td>9.998681e+08</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1/6/2011 0:11:22</td>\n",
       "      <td>1/6/2011 0:11:22</td>\n",
       "      <td>RAcc2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dp201161.01111</td>\n",
       "      <td>1/6/2011 0:11:22</td>\n",
       "      <td>RET</td>\n",
       "      <td>EU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>N_Reg_RC</td>\n",
       "      <td>PN_EU_1_502</td>\n",
       "      <td>operator</td>\n",
       "      <td>EUAcc1_502</td>\n",
       "      <td>A0</td>\n",
       "      <td>2054.46</td>\n",
       "      <td>ArRC</td>\n",
       "      <td>SU</td>\n",
       "      <td>1.000000e+08</td>\n",
       "      <td>9.999795e+07</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1/6/2011 0:16:8</td>\n",
       "      <td>1/6/2011 0:16:8</td>\n",
       "      <td>EUAcc1_502</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rc201161.01616</td>\n",
       "      <td>1/6/2011 0:16:8</td>\n",
       "      <td>EU</td>\n",
       "      <td>operator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>N-RegDep</td>\n",
       "      <td>PN_Ret2</td>\n",
       "      <td>PN_EU_1_362</td>\n",
       "      <td>RAcc2</td>\n",
       "      <td>EUAcc1_362</td>\n",
       "      <td>182548.74</td>\n",
       "      <td>Dt</td>\n",
       "      <td>SU</td>\n",
       "      <td>9.998681e+08</td>\n",
       "      <td>9.996855e+08</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1/6/2011 0:36:47</td>\n",
       "      <td>1/6/2011 0:36:47</td>\n",
       "      <td>RAcc2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dp201161.03636</td>\n",
       "      <td>1/6/2011 0:36:47</td>\n",
       "      <td>RET</td>\n",
       "      <td>EU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>N_Reg_RC</td>\n",
       "      <td>PN_EU_1_26</td>\n",
       "      <td>operator</td>\n",
       "      <td>EUAcc1_26</td>\n",
       "      <td>A0</td>\n",
       "      <td>5507.28</td>\n",
       "      <td>ArRC</td>\n",
       "      <td>SU</td>\n",
       "      <td>1.000000e+08</td>\n",
       "      <td>9.999449e+07</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1/6/2011 0:36:59</td>\n",
       "      <td>1/6/2011 0:36:59</td>\n",
       "      <td>EUAcc1_26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rc201161.03636</td>\n",
       "      <td>1/6/2011 0:36:59</td>\n",
       "      <td>EU</td>\n",
       "      <td>operator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>N_Reg_RC</td>\n",
       "      <td>PN_EU_1_18</td>\n",
       "      <td>operator</td>\n",
       "      <td>EUAcc1_18</td>\n",
       "      <td>A0</td>\n",
       "      <td>2102.60</td>\n",
       "      <td>ArRC</td>\n",
       "      <td>SU</td>\n",
       "      <td>1.000000e+08</td>\n",
       "      <td>9.999790e+07</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1/6/2011 1:1:26</td>\n",
       "      <td>1/6/2011 1:1:26</td>\n",
       "      <td>EUAcc1_18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rc201161.111</td>\n",
       "      <td>1/6/2011 1:1:26</td>\n",
       "      <td>EU</td>\n",
       "      <td>operator</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>54217</th>\n",
       "      <td>N_Reg_RC</td>\n",
       "      <td>PN_EU_0_116</td>\n",
       "      <td>operator</td>\n",
       "      <td>EUAcc0_116</td>\n",
       "      <td>A0</td>\n",
       "      <td>524.70</td>\n",
       "      <td>ArRC</td>\n",
       "      <td>SU</td>\n",
       "      <td>9.999828e+07</td>\n",
       "      <td>9.999775e+07</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30/9/2011 23:30:0</td>\n",
       "      <td>30/9/2011 23:30:0</td>\n",
       "      <td>EUAcc0_116</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rc2011930.233030</td>\n",
       "      <td>30/9/2011 23:30:0</td>\n",
       "      <td>EU</td>\n",
       "      <td>operator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54218</th>\n",
       "      <td>N_Reg_RC</td>\n",
       "      <td>PN_EU_0_1256</td>\n",
       "      <td>operator</td>\n",
       "      <td>EUAcc0_1256</td>\n",
       "      <td>A0</td>\n",
       "      <td>2106.86</td>\n",
       "      <td>ArRC</td>\n",
       "      <td>SU</td>\n",
       "      <td>9.998486e+07</td>\n",
       "      <td>9.998276e+07</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30/9/2011 23:32:19</td>\n",
       "      <td>30/9/2011 23:32:19</td>\n",
       "      <td>EUAcc0_1256</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Rc2011930.233232</td>\n",
       "      <td>30/9/2011 23:32:19</td>\n",
       "      <td>EU</td>\n",
       "      <td>operator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54219</th>\n",
       "      <td>N-RegDep</td>\n",
       "      <td>PN_Ret6</td>\n",
       "      <td>PN_EU_1_143</td>\n",
       "      <td>RAcc6</td>\n",
       "      <td>EUAcc1_143</td>\n",
       "      <td>213240.93</td>\n",
       "      <td>Dt</td>\n",
       "      <td>SU</td>\n",
       "      <td>8.785127e+08</td>\n",
       "      <td>8.782994e+08</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30/9/2011 23:33:23</td>\n",
       "      <td>30/9/2011 23:33:23</td>\n",
       "      <td>RAcc6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dp2011930.233333</td>\n",
       "      <td>30/9/2011 23:33:23</td>\n",
       "      <td>RET</td>\n",
       "      <td>EU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54220</th>\n",
       "      <td>N-RegC2C</td>\n",
       "      <td>PN_EU_1_197</td>\n",
       "      <td>PN_EU_2_85</td>\n",
       "      <td>EUAcc1_197</td>\n",
       "      <td>EUAcc2_85</td>\n",
       "      <td>199431.78</td>\n",
       "      <td>Ind</td>\n",
       "      <td>SU</td>\n",
       "      <td>9.755577e+07</td>\n",
       "      <td>9.735435e+07</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30/9/2011 23:38:5</td>\n",
       "      <td>30/9/2011 23:38:5</td>\n",
       "      <td>EUAcc1_197</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C2C2011930.233838</td>\n",
       "      <td>30/9/2011 23:38:5</td>\n",
       "      <td>EU</td>\n",
       "      <td>EU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54221</th>\n",
       "      <td>N-RegDep</td>\n",
       "      <td>PN_Ret5</td>\n",
       "      <td>PN_EU_1_360</td>\n",
       "      <td>RAcc5</td>\n",
       "      <td>EUAcc1_360</td>\n",
       "      <td>87364.76</td>\n",
       "      <td>Dt</td>\n",
       "      <td>SU</td>\n",
       "      <td>9.066821e+08</td>\n",
       "      <td>9.065947e+08</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30/9/2011 23:49:1</td>\n",
       "      <td>30/9/2011 23:49:1</td>\n",
       "      <td>RAcc5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dp2011930.234949</td>\n",
       "      <td>30/9/2011 23:49:1</td>\n",
       "      <td>RET</td>\n",
       "      <td>EU</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>54222 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              gT            sId          rId         sAcc        rAcc  \\\n",
       "0      N-RegDep         PN_Ret2  PN_EU_0_261        RAcc2  EUAcc0_261   \n",
       "1      N_Reg_RC     PN_EU_1_502     operator   EUAcc1_502          A0   \n",
       "2      N-RegDep         PN_Ret2  PN_EU_1_362        RAcc2  EUAcc1_362   \n",
       "3      N_Reg_RC      PN_EU_1_26     operator    EUAcc1_26          A0   \n",
       "4      N_Reg_RC      PN_EU_1_18     operator    EUAcc1_18          A0   \n",
       "...          ...            ...          ...          ...         ...   \n",
       "54217  N_Reg_RC     PN_EU_0_116     operator   EUAcc0_116          A0   \n",
       "54218  N_Reg_RC    PN_EU_0_1256     operator  EUAcc0_1256          A0   \n",
       "54219  N-RegDep         PN_Ret6  PN_EU_1_143        RAcc6  EUAcc1_143   \n",
       "54220  N-RegC2C     PN_EU_1_197   PN_EU_2_85   EUAcc1_197   EUAcc2_85   \n",
       "54221  N-RegDep         PN_Ret5  PN_EU_1_360        RAcc5  EUAcc1_360   \n",
       "\n",
       "       TranAmount TranType TranStatus    sBalbefore     sBalAfter  ...  sf4  \\\n",
       "0       131926.49       Dt         SU  1.000000e+09  9.998681e+08  ...  NaN   \n",
       "1         2054.46     ArRC         SU  1.000000e+08  9.999795e+07  ...  NaN   \n",
       "2       182548.74       Dt         SU  9.998681e+08  9.996855e+08  ...  NaN   \n",
       "3         5507.28     ArRC         SU  1.000000e+08  9.999449e+07  ...  NaN   \n",
       "4         2102.60     ArRC         SU  1.000000e+08  9.999790e+07  ...  NaN   \n",
       "...           ...      ...        ...           ...           ...  ...  ...   \n",
       "54217      524.70     ArRC         SU  9.999828e+07  9.999775e+07  ...  NaN   \n",
       "54218     2106.86     ArRC         SU  9.998486e+07  9.998276e+07  ...  NaN   \n",
       "54219   213240.93       Dt         SU  8.785127e+08  8.782994e+08  ...  NaN   \n",
       "54220   199431.78      Ind         SU  9.755577e+07  9.735435e+07  ...  NaN   \n",
       "54221    87364.76       Dt         SU  9.066821e+08  9.065947e+08  ...  NaN   \n",
       "\n",
       "                      sTD                 rTD       sAccID  ef1  ef2  \\\n",
       "0        1/6/2011 0:11:22    1/6/2011 0:11:22        RAcc2  NaN  NaN   \n",
       "1         1/6/2011 0:16:8     1/6/2011 0:16:8   EUAcc1_502  NaN  NaN   \n",
       "2        1/6/2011 0:36:47    1/6/2011 0:36:47        RAcc2  NaN  NaN   \n",
       "3        1/6/2011 0:36:59    1/6/2011 0:36:59    EUAcc1_26  NaN  NaN   \n",
       "4         1/6/2011 1:1:26     1/6/2011 1:1:26    EUAcc1_18  NaN  NaN   \n",
       "...                   ...                 ...          ...  ...  ...   \n",
       "54217   30/9/2011 23:30:0   30/9/2011 23:30:0   EUAcc0_116  NaN  NaN   \n",
       "54218  30/9/2011 23:32:19  30/9/2011 23:32:19  EUAcc0_1256  NaN  NaN   \n",
       "54219  30/9/2011 23:33:23  30/9/2011 23:33:23        RAcc6  NaN  NaN   \n",
       "54220   30/9/2011 23:38:5   30/9/2011 23:38:5   EUAcc1_197  NaN  NaN   \n",
       "54221   30/9/2011 23:49:1   30/9/2011 23:49:1        RAcc5  NaN  NaN   \n",
       "\n",
       "           NoDescription              TranTS sType     rType  \n",
       "0         Dp201161.01111    1/6/2011 0:11:22   RET        EU  \n",
       "1         Rc201161.01616     1/6/2011 0:16:8    EU  operator  \n",
       "2         Dp201161.03636    1/6/2011 0:36:47   RET        EU  \n",
       "3         Rc201161.03636    1/6/2011 0:36:59    EU  operator  \n",
       "4           Rc201161.111     1/6/2011 1:1:26    EU  operator  \n",
       "...                  ...                 ...   ...       ...  \n",
       "54217   Rc2011930.233030   30/9/2011 23:30:0    EU  operator  \n",
       "54218   Rc2011930.233232  30/9/2011 23:32:19    EU  operator  \n",
       "54219   Dp2011930.233333  30/9/2011 23:33:23   RET        EU  \n",
       "54220  C2C2011930.233838   30/9/2011 23:38:5    EU        EU  \n",
       "54221   Dp2011930.234949   30/9/2011 23:49:1   RET        EU  \n",
       "\n",
       "[54222 rows x 25 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#define the column names as described in the dataset description \n",
    "# there was no header in the dataset, but fields names were provided in the task. So name the fileds manually.\n",
    "columnNames=['gT','sId','rId','sAcc','rAcc','TranAmount','TranType','TranStatus','sBalbefore','sBalAfter',\n",
    "   \n",
    "            'rBalBefore','rBalAfter','sf1','sf2','sf3','sf4','sTD','rTD','sAccID','ef1','ef2','NoDescription','TranTS','sType','rType']\n",
    "\n",
    "print('l = ', len(columnNames))\n",
    "    \n",
    "#read the dataset in txt format\n",
    "df = pd.read_csv('logsDataset.txt', sep=\"|\", header=None,names=columnNames)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "27229f99",
   "metadata": {},
   "outputs": [],
   "source": [
    "#export the created dataset as csv\n",
    "df.to_csv('TransactionSamples.csv',index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.6 ('base')",
   "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.6"
  },
  "vscode": {
   "interpreter": {
    "hash": "1448b48b023bcc9c3d4a79e814720a10ca6d4244f75e0f7ce4af58f96ba2b7d3"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}