00_filling_and_validation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import sys\n",
    "# sys.path.insert(0, '/home/claudio/tesi/coding')\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "from myutils.utilities import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# txns = 930161\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 930161 entries, 2020-01-25 19:13:17.731529 to 2021-06-15 05:00:30.887568\n",
      "Data columns (total 9 columns):\n",
      " #   Column            Non-Null Count   Dtype         \n",
      "---  ------            --------------   -----         \n",
      " 0   id                930161 non-null  int64         \n",
      " 1   timeset           930161 non-null  string        \n",
      " 2   transfer_subtype  930161 non-null  category      \n",
      " 3   source            930159 non-null  string        \n",
      " 4   target            930159 non-null  string        \n",
      " 5   weight            930161 non-null  float64       \n",
      " 6   token_name        930161 non-null  category      \n",
      " 7   token_address     930161 non-null  string        \n",
      " 8   date              930161 non-null  datetime64[ns]\n",
      "dtypes: category(2), datetime64[ns](1), float64(1), int64(1), string(4)\n",
      "memory usage: 58.5 MB\n"
     ]
    }
   ],
   "source": [
    "# txns_fn = '/home/claudio/tesi/sarafu/Sarafu2021_UKdb_submission/sarafu_xDAI/sarafu_txns_20200125-20210615.csv'\n",
    "txns_fn = '/home/claudio/tesi/sarafu/Sarafu2021_UKdb_submission(1)/sarafu_xDAI/sarafu_txns_20200125-20210615.csv'\n",
    "txns = pd.read_csv(txns_fn,sep=\",\",header=0,dtype={\"id\": \"int\",\n",
    "                                                 \"timeset\": \"string\",\n",
    "                                                 \"transfer_subtype\": \"category\",\n",
    "                                                 'source': \"string\",\n",
    "                                                 'target': \"string\",\n",
    "                                                 'weight': \"float\",\n",
    "                                                 'token_name': \"category\",\n",
    "                                                 'token_address': \"string\",\n",
    "                                                 })\n",
    "\n",
    "txns['date'] = pd.to_datetime(txns.timeset)\n",
    "txns = txns.sort_values(by='date')\n",
    "txns = txns.set_index(txns.date)\n",
    "txns.drop_duplicates(keep = 'first', inplace=True)\n",
    "# del txns.index.name\n",
    "# txns = txns.reset_index()\n",
    "print('# txns =',len(txns))\n",
    "#\n",
    "txns.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# users_fn = '/home/claudio/tesi/sarafu/Sarafu2021_UKdb_submission/sarafu_xDAI/sarafu_users_20210615.csv'\n",
    "users_fn = '/home/claudio/tesi/sarafu/Sarafu2021_UKdb_submission(1)/sarafu_xDAI/sarafu_users_20210615.csv'\n",
    "users = pd.read_csv(users_fn,  # assuming 'user_fn' is your file name\n",
    "                      sep=\",\",\n",
    "                      header=0,\n",
    "                      dtype={\n",
    "                          \"id\": \"int\",\n",
    "                          \"start\": \"string\",\n",
    "                          \"final_bal\": \"float\",\n",
    "                          \"gender\": \"category\",\n",
    "                          \"area_name\": \"string\",\n",
    "                          \"area_type\": \"category\",\n",
    "                          \"held_roles\": \"category\",\n",
    "                          \"business_type\": \"category\",\n",
    "                          \"old_POA_blockchain_address\": \"string\",\n",
    "                          \"xDAI_blockchain_address\": \"string\",\n",
    "                          \"ovol_in\": \"float\",\n",
    "                          \"ovol_out\": \"float\",\n",
    "                          \"otxns_in\": \"int\",\n",
    "                          \"otxns_out\": \"int\",\n",
    "                          \"ounique_in\": \"int\",\n",
    "                          \"ounique_out\": \"int\",\n",
    "                          \"svol_in\": \"float\",\n",
    "                          \"svol_out\": \"float\",\n",
    "                          \"stxns_in\": \"int\",\n",
    "                          \"stxns_out\": \"int\",\n",
    "                          \"sunique_in\": \"int\",\n",
    "                          \"sunique_out\": \"int\"\n",
    "                      })\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   id                start     final_bal  gender       area_name  area_type  \\\n",
      "0   3  2018-10-21 14:20:57     57.470892    Male    Misc Nairobi      urban   \n",
      "1   4  2018-10-21 15:38:30  10853.966002    Male          Kilifi  periurban   \n",
      "2   2  2018-10-23 09:09:58     80.870000  Female          Kilifi  periurban   \n",
      "3   5  2018-10-23 14:10:27  43228.282576    Male  Mukuru Nairobi      urban   \n",
      "4   9  2018-10-24 08:17:32    595.319801    Male    Misc Nairobi      urban   \n",
      "\n",
      "    held_roles business_type                  old_POA_blockchain_address  \\\n",
      "0  BENEFICIARY        system  0x3cc06c32c88aba379c4bbd4c9ebda585d7574bbf   \n",
      "1  BENEFICIARY          food  0xbe9e9a4b6bddec171bced854615b861479933e44   \n",
      "2  TOKEN_AGENT        system  0xe6d39ad7c6be64673ec18af4a2eb02f2bea88e15   \n",
      "3  BENEFICIARY        labour  0x3700dd6c99f396c48e6658102ab2e18dc776d243   \n",
      "4  BENEFICIARY          food  0xf87ea990cc4a20cfeb1c806ff7f3e486e06ca786   \n",
      "\n",
      "                      xDAI_blockchain_address  ...  otxns_in  otxns_out  \\\n",
      "0  0xC1697C1326fD192438515fE2F7E4cCb0C705C5d2  ...         4          6   \n",
      "1  0xBAB77A20a757e8438DfaBF01D5F36DD12d862B31  ...        33          8   \n",
      "2  0x245fc81fe385450Dc0f4787668e47c903C00b0A1  ...       275         38   \n",
      "3  0xD95954e3fCd2f09A6Be5931D24f731eFa63BF435  ...        43          6   \n",
      "4  0xeFad5486432a5b4F2b31B13520b7C1393e87f531  ...         3          8   \n",
      "\n",
      "   ounique_in  ounique_out   svol_in  svol_out  stxns_in  stxns_out  \\\n",
      "0           2            1       0.0    9007.0         0          1   \n",
      "1           2            1   76850.1  108627.0       121        103   \n",
      "2         125            1       0.0   16766.0         0          2   \n",
      "3           5            1  317816.3  339096.0       339        312   \n",
      "4           2            1     330.0       0.0         8          0   \n",
      "\n",
      "   sunique_in  sunique_out  \n",
      "0           0            1  \n",
      "1          56           47  \n",
      "2           0            2  \n",
      "3         211          157  \n",
      "4           8            0  \n",
      "\n",
      "[5 rows x 22 columns]\n"
     ]
    }
   ],
   "source": [
    "# Remove duplicates by keeping the first occurrence\n",
    "users.drop_duplicates(subset='xDAI_blockchain_address', keep='first', inplace=True)\n",
    "\n",
    "# Check your work\n",
    "print(users.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No duplicates found. Safe to proceed with setting index.\n"
     ]
    }
   ],
   "source": [
    "# Step 1: Check for duplicates\n",
    "if users['xDAI_blockchain_address'].duplicated().any():\n",
    "    print(\"Duplicates found in xDAI_blockchain_address. You need to resolve these.\")\n",
    "    # Display duplicates for review\n",
    "    display(users[users['xDAI_blockchain_address'].duplicated(keep=False)])\n",
    "else:\n",
    "    print(\"No duplicates found. Safe to proceed with setting index.\")\n",
    "\n",
    "# Step 2: Assuming you have resolved duplicates, you might have code like:\n",
    "# users = <your code to handle duplicates>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 54976 entries, 0 to 54998\n",
      "Data columns (total 22 columns):\n",
      " #   Column                      Non-Null Count  Dtype   \n",
      "---  ------                      --------------  -----   \n",
      " 0   id                          54976 non-null  int64   \n",
      " 1   start                       54976 non-null  string  \n",
      " 2   final_bal                   54967 non-null  float64 \n",
      " 3   gender                      54976 non-null  category\n",
      " 4   area_name                   54976 non-null  string  \n",
      " 5   area_type                   54976 non-null  category\n",
      " 6   held_roles                  54883 non-null  category\n",
      " 7   business_type               54976 non-null  category\n",
      " 8   old_POA_blockchain_address  8354 non-null   string  \n",
      " 9   xDAI_blockchain_address     54975 non-null  string  \n",
      " 10  ovol_in                     54976 non-null  float64 \n",
      " 11  ovol_out                    54976 non-null  float64 \n",
      " 12  otxns_in                    54976 non-null  int64   \n",
      " 13  otxns_out                   54976 non-null  int64   \n",
      " 14  ounique_in                  54976 non-null  int64   \n",
      " 15  ounique_out                 54976 non-null  int64   \n",
      " 16  svol_in                     54976 non-null  float64 \n",
      " 17  svol_out                    54976 non-null  float64 \n",
      " 18  stxns_in                    54976 non-null  int64   \n",
      " 19  stxns_out                   54976 non-null  int64   \n",
      " 20  sunique_in                  54976 non-null  int64   \n",
      " 21  sunique_out                 54976 non-null  int64   \n",
      "dtypes: category(4), float64(5), int64(9), string(4)\n",
      "memory usage: 8.2 MB\n"
     ]
    }
   ],
   "source": [
    "users.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "formats = ['%Y-%m-%d %H:%M:%S.%f', '%Y-%m-%d %H:%M:%S', '%Y-%m-%d']\n",
    "for fmt in formats:\n",
    "    users.start = pd.to_datetime(users.start, format=fmt, errors='coerce').fillna(users.start)\n",
    "users = users.sort_values(by='start')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 54976 entries, 0 to 54998\n",
      "Data columns (total 22 columns):\n",
      " #   Column                      Non-Null Count  Dtype         \n",
      "---  ------                      --------------  -----         \n",
      " 0   id                          54976 non-null  int64         \n",
      " 1   start                       54976 non-null  datetime64[ns]\n",
      " 2   final_bal                   54967 non-null  float64       \n",
      " 3   gender                      54976 non-null  category      \n",
      " 4   area_name                   54976 non-null  string        \n",
      " 5   area_type                   54976 non-null  category      \n",
      " 6   held_roles                  54883 non-null  category      \n",
      " 7   business_type               54976 non-null  category      \n",
      " 8   old_POA_blockchain_address  8354 non-null   string        \n",
      " 9   xDAI_blockchain_address     54975 non-null  string        \n",
      " 10  ovol_in                     54976 non-null  float64       \n",
      " 11  ovol_out                    54976 non-null  float64       \n",
      " 12  otxns_in                    54976 non-null  int64         \n",
      " 13  otxns_out                   54976 non-null  int64         \n",
      " 14  ounique_in                  54976 non-null  int64         \n",
      " 15  ounique_out                 54976 non-null  int64         \n",
      " 16  svol_in                     54976 non-null  float64       \n",
      " 17  svol_out                    54976 non-null  float64       \n",
      " 18  stxns_in                    54976 non-null  int64         \n",
      " 19  stxns_out                   54976 non-null  int64         \n",
      " 20  sunique_in                  54976 non-null  int64         \n",
      " 21  sunique_out                 54976 non-null  int64         \n",
      "dtypes: category(4), datetime64[ns](1), float64(5), int64(9), string(3)\n",
      "memory usage: 8.2 MB\n"
     ]
    }
   ],
   "source": [
    "users.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Corrections on 'Area Name' category"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "area_name\n",
       "Kinango Kwale          23671\n",
       "Mukuru Nairobi         10108\n",
       "other                   6773\n",
       "Kisauni Mombasa         6119\n",
       "Misc Nairobi            5260\n",
       "Misc Mombasa             966\n",
       "Kilifi                   916\n",
       "Nyanza                   641\n",
       "Misc Rural Counties      303\n",
       "Turkana                  219\n",
       "Name: count, dtype: Int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.area_name.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "area_name\n",
       "Kinango Kwale          23671\n",
       "Mukuru Nairobi         10108\n",
       "other                   6773\n",
       "Kisauni Mombasa         6119\n",
       "Misc Nairobi            5260\n",
       "Misc Mombasa             966\n",
       "Kilifi                   916\n",
       "Nyanza                   641\n",
       "Misc Rural Counties      303\n",
       "Turkana                  219\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1. Kitui is a rural county. Since only 13 users are from Kitui, we decided to add them to other rural counties.\n",
    "users_geo_areas = []\n",
    "for i in users.index:\n",
    "    area_name = users.area_name.loc[i]\n",
    "    if area_name == 'Kitui':  \n",
    "        users_geo_areas.append('Misc Rural Counties')\n",
    "    if area_name != 'Kitui':\n",
    "        users_geo_areas.append(area_name)\n",
    "#\n",
    "users['area_name'] = users_geo_areas\n",
    "#\n",
    "# 2. The name of Kakuma is substituted with the name of its county (Turkana).\n",
    "users_geo_areas = []\n",
    "for i in users.index:\n",
    "    area_name = users.area_name.loc[i]\n",
    "    if area_name == 'Kakuma':  \n",
    "        users_geo_areas.append('Turkana')\n",
    "    if area_name != 'Kakuma':\n",
    "        users_geo_areas.append(area_name)\n",
    "#\n",
    "users['area_name'] = users_geo_areas\n",
    "#\n",
    "users.area_name.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Corrections on 'Gender' category"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "gender\n",
       "Unknown    23354\n",
       "Male       18209\n",
       "Female     13389\n",
       "Other         24\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.gender.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "held_roles\n",
       "BENEFICIARY      54667\n",
       "GROUP_ACCOUNT      211\n",
       "ADMIN                2\n",
       "TOKEN_AGENT          2\n",
       "VENDOR               1\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.held_roles.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "area_type\n",
       "rural        24834\n",
       "urban        22453\n",
       "other         6773\n",
       "periurban      916\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.area_type.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "business_type\n",
       "labour         18544\n",
       "food           12030\n",
       "farming         9678\n",
       "shop            5607\n",
       "fuel/energy     2880\n",
       "transport       1699\n",
       "other           1696\n",
       "water           1085\n",
       "education        740\n",
       "health           321\n",
       "environment      312\n",
       "savings          273\n",
       "government        55\n",
       "faith             39\n",
       "system            17\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.business_type.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fix missing other values in users dataframe "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['final_bal', 'held_roles', 'old_POA_blockchain_address',\n",
       "       'xDAI_blockchain_address'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.columns[users.isna().any()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "users = users.sort_values(by='start')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>id</th>\n",
       "      <th>start</th>\n",
       "      <th>final_bal</th>\n",
       "      <th>gender</th>\n",
       "      <th>area_name</th>\n",
       "      <th>area_type</th>\n",
       "      <th>held_roles</th>\n",
       "      <th>business_type</th>\n",
       "      <th>old_POA_blockchain_address</th>\n",
       "      <th>xDAI_blockchain_address</th>\n",
       "      <th>...</th>\n",
       "      <th>otxns_out</th>\n",
       "      <th>ounique_in</th>\n",
       "      <th>ounique_out</th>\n",
       "      <th>svol_in</th>\n",
       "      <th>svol_out</th>\n",
       "      <th>stxns_in</th>\n",
       "      <th>stxns_out</th>\n",
       "      <th>sunique_in</th>\n",
       "      <th>sunique_out</th>\n",
       "      <th>crid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>2018-10-21 14:20:57.000000</td>\n",
       "      <td>57.470892</td>\n",
       "      <td>Male</td>\n",
       "      <td>Misc Nairobi</td>\n",
       "      <td>urban</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>system</td>\n",
       "      <td>0x3cc06c32c88aba379c4bbd4c9ebda585d7574bbf</td>\n",
       "      <td>0xC1697C1326fD192438515fE2F7E4cCb0C705C5d2</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9007.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>2018-10-21 15:38:30.000000</td>\n",
       "      <td>10853.966002</td>\n",
       "      <td>Male</td>\n",
       "      <td>Kilifi</td>\n",
       "      <td>periurban</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>food</td>\n",
       "      <td>0xbe9e9a4b6bddec171bced854615b861479933e44</td>\n",
       "      <td>0xBAB77A20a757e8438DfaBF01D5F36DD12d862B31</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>76850.1</td>\n",
       "      <td>108627.0</td>\n",
       "      <td>121</td>\n",
       "      <td>103</td>\n",
       "      <td>56</td>\n",
       "      <td>47</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-10-23 09:09:58.000000</td>\n",
       "      <td>80.870000</td>\n",
       "      <td>Female</td>\n",
       "      <td>Kilifi</td>\n",
       "      <td>periurban</td>\n",
       "      <td>TOKEN_AGENT</td>\n",
       "      <td>system</td>\n",
       "      <td>0xe6d39ad7c6be64673ec18af4a2eb02f2bea88e15</td>\n",
       "      <td>0x245fc81fe385450Dc0f4787668e47c903C00b0A1</td>\n",
       "      <td>...</td>\n",
       "      <td>38</td>\n",
       "      <td>125</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16766.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>2018-10-23 14:10:27.000000</td>\n",
       "      <td>43228.282576</td>\n",
       "      <td>Male</td>\n",
       "      <td>Mukuru Nairobi</td>\n",
       "      <td>urban</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>labour</td>\n",
       "      <td>0x3700dd6c99f396c48e6658102ab2e18dc776d243</td>\n",
       "      <td>0xD95954e3fCd2f09A6Be5931D24f731eFa63BF435</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>317816.3</td>\n",
       "      <td>339096.0</td>\n",
       "      <td>339</td>\n",
       "      <td>312</td>\n",
       "      <td>211</td>\n",
       "      <td>157</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>2018-10-24 08:17:32.000000</td>\n",
       "      <td>595.319801</td>\n",
       "      <td>Male</td>\n",
       "      <td>Misc Nairobi</td>\n",
       "      <td>urban</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>food</td>\n",
       "      <td>0xf87ea990cc4a20cfeb1c806ff7f3e486e06ca786</td>\n",
       "      <td>0xeFad5486432a5b4F2b31B13520b7C1393e87f531</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>330.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>4</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>54994</th>\n",
       "      <td>61450</td>\n",
       "      <td>2021-06-14 17:27:47.267723</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>other</td>\n",
       "      <td>other</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>labour</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0xDe6a42F43947DB9583AC4Bc12665CCA819ADac39</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>54994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54995</th>\n",
       "      <td>61452</td>\n",
       "      <td>2021-06-14 17:34:17.144344</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>Male</td>\n",
       "      <td>other</td>\n",
       "      <td>other</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>farming</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0x6940a2CCE6cFdF7A6d2A6E4dCe08A6eAdb5bF107</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>54995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54996</th>\n",
       "      <td>61453</td>\n",
       "      <td>2021-06-14 18:08:54.760767</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>other</td>\n",
       "      <td>other</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>labour</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0x7A310578dF8FB71A1896f99495c5162a14b3F558</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>54996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54997</th>\n",
       "      <td>61456</td>\n",
       "      <td>2021-06-14 18:57:40.790519</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>other</td>\n",
       "      <td>other</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>labour</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0x05795cd7c61796B1C73F7b8DdF2E153AEC1bAC0f</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>54997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54998</th>\n",
       "      <td>61457</td>\n",
       "      <td>2021-06-14 21:14:54.285784</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>other</td>\n",
       "      <td>other</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>labour</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0x9EE844A0cf2DD827604FC68Facb6c0259aF24CAe</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>54998</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>54976 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                      start     final_bal   gender  \\\n",
       "0          3 2018-10-21 14:20:57.000000     57.470892     Male   \n",
       "1          4 2018-10-21 15:38:30.000000  10853.966002     Male   \n",
       "2          2 2018-10-23 09:09:58.000000     80.870000   Female   \n",
       "3          5 2018-10-23 14:10:27.000000  43228.282576     Male   \n",
       "4          9 2018-10-24 08:17:32.000000    595.319801     Male   \n",
       "...      ...                        ...           ...      ...   \n",
       "54994  61450 2021-06-14 17:27:47.267723      0.000000  Unknown   \n",
       "54995  61452 2021-06-14 17:34:17.144344     50.000000     Male   \n",
       "54996  61453 2021-06-14 18:08:54.760767     30.000000  Unknown   \n",
       "54997  61456 2021-06-14 18:57:40.790519     50.000000  Unknown   \n",
       "54998  61457 2021-06-14 21:14:54.285784     30.000000  Unknown   \n",
       "\n",
       "            area_name  area_type   held_roles business_type  \\\n",
       "0        Misc Nairobi      urban  BENEFICIARY        system   \n",
       "1              Kilifi  periurban  BENEFICIARY          food   \n",
       "2              Kilifi  periurban  TOKEN_AGENT        system   \n",
       "3      Mukuru Nairobi      urban  BENEFICIARY        labour   \n",
       "4        Misc Nairobi      urban  BENEFICIARY          food   \n",
       "...               ...        ...          ...           ...   \n",
       "54994           other      other  BENEFICIARY        labour   \n",
       "54995           other      other  BENEFICIARY       farming   \n",
       "54996           other      other  BENEFICIARY        labour   \n",
       "54997           other      other  BENEFICIARY        labour   \n",
       "54998           other      other  BENEFICIARY        labour   \n",
       "\n",
       "                       old_POA_blockchain_address  \\\n",
       "0      0x3cc06c32c88aba379c4bbd4c9ebda585d7574bbf   \n",
       "1      0xbe9e9a4b6bddec171bced854615b861479933e44   \n",
       "2      0xe6d39ad7c6be64673ec18af4a2eb02f2bea88e15   \n",
       "3      0x3700dd6c99f396c48e6658102ab2e18dc776d243   \n",
       "4      0xf87ea990cc4a20cfeb1c806ff7f3e486e06ca786   \n",
       "...                                           ...   \n",
       "54994                                        <NA>   \n",
       "54995                                        <NA>   \n",
       "54996                                        <NA>   \n",
       "54997                                        <NA>   \n",
       "54998                                        <NA>   \n",
       "\n",
       "                          xDAI_blockchain_address  ...  otxns_out  ounique_in  \\\n",
       "0      0xC1697C1326fD192438515fE2F7E4cCb0C705C5d2  ...          6           2   \n",
       "1      0xBAB77A20a757e8438DfaBF01D5F36DD12d862B31  ...          8           2   \n",
       "2      0x245fc81fe385450Dc0f4787668e47c903C00b0A1  ...         38         125   \n",
       "3      0xD95954e3fCd2f09A6Be5931D24f731eFa63BF435  ...          6           5   \n",
       "4      0xeFad5486432a5b4F2b31B13520b7C1393e87f531  ...          8           2   \n",
       "...                                           ...  ...        ...         ...   \n",
       "54994  0xDe6a42F43947DB9583AC4Bc12665CCA819ADac39  ...          0           1   \n",
       "54995  0x6940a2CCE6cFdF7A6d2A6E4dCe08A6eAdb5bF107  ...          0           1   \n",
       "54996  0x7A310578dF8FB71A1896f99495c5162a14b3F558  ...          0           1   \n",
       "54997  0x05795cd7c61796B1C73F7b8DdF2E153AEC1bAC0f  ...          0           1   \n",
       "54998  0x9EE844A0cf2DD827604FC68Facb6c0259aF24CAe  ...          0           1   \n",
       "\n",
       "       ounique_out   svol_in  svol_out  stxns_in  stxns_out  sunique_in  \\\n",
       "0                1       0.0    9007.0         0          1           0   \n",
       "1                1   76850.1  108627.0       121        103          56   \n",
       "2                1       0.0   16766.0         0          2           0   \n",
       "3                1  317816.3  339096.0       339        312         211   \n",
       "4                1     330.0       0.0         8          0           8   \n",
       "...            ...       ...       ...       ...        ...         ...   \n",
       "54994            0       0.0      50.0         0          1           0   \n",
       "54995            0       0.0       0.0         0          0           0   \n",
       "54996            0       0.0      20.0         0          1           0   \n",
       "54997            0       0.0       0.0         0          0           0   \n",
       "54998            0       0.0      20.0         0          1           0   \n",
       "\n",
       "       sunique_out   crid  \n",
       "0                1      0  \n",
       "1               47      1  \n",
       "2                2      2  \n",
       "3              157      3  \n",
       "4                0      4  \n",
       "...            ...    ...  \n",
       "54994            1  54994  \n",
       "54995            0  54995  \n",
       "54996            1  54996  \n",
       "54997            0  54997  \n",
       "54998            1  54998  \n",
       "\n",
       "[54976 rows x 23 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add a CReation date IDentifier (crid)\n",
    "\n",
    "l = []\n",
    "\n",
    "for item in users.itertuples(index=True, name='Row'):\n",
    "    l.append(item.Index)\n",
    "\n",
    "users['crid'] = l\n",
    "\n",
    "users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['final_bal', 'held_roles', 'old_POA_blockchain_address',\n",
       "       'xDAI_blockchain_address'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.columns[users.isna().any()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter the rows with missing 'old_POA_blockchain_address'\n",
    "missing_poa_indices = users[users['old_POA_blockchain_address'].isna()].index\n",
    "\n",
    "# Assign personalized values to those rows\n",
    "users.loc[missing_poa_indices, 'old_POA_blockchain_address'] = [\n",
    "    f'Nopoa_{i+1}' for i in range(len(missing_poa_indices))\n",
    "]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "np.False_"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.old_POA_blockchain_address.isna().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter the rows with missing 'xDAI_blockchain_address'\n",
    "missing_xdai_indices = users[users['xDAI_blockchain_address'].isna()].index\n",
    "\n",
    "# Assign personalized values to those rows\n",
    "users.loc[missing_xdai_indices, 'xDAI_blockchain_address'] = [\n",
    "    f'Noxdai_{i+1}' for i in range(len(missing_xdai_indices))\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "np.False_"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.xDAI_blockchain_address.isna().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "no_role = []\n",
    "no_role.append(users.loc[users['held_roles'].isna()])\n",
    "\n",
    "# Step 1: Add 'UnKnown' as a new category for the 'held_roles' column\n",
    "users['held_roles'] = users['held_roles'].cat.add_categories(['UnKnown'])\n",
    "\n",
    "# Step 2: Now safely assign 'UnKnown' to NaN values in the 'held_roles' column\n",
    "users.loc[users['held_roles'].isna(), 'held_roles'] = 'UnKnown'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "np.False_"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.xDAI_blockchain_address.isna().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['final_bal'], dtype='object')"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.columns[users.isna().any()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['source', 'target'], dtype='object')"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "txns.columns[txns.isna().any()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>id</th>\n",
       "      <th>start</th>\n",
       "      <th>final_bal</th>\n",
       "      <th>gender</th>\n",
       "      <th>area_name</th>\n",
       "      <th>area_type</th>\n",
       "      <th>held_roles</th>\n",
       "      <th>business_type</th>\n",
       "      <th>old_POA_blockchain_address</th>\n",
       "      <th>xDAI_blockchain_address</th>\n",
       "      <th>...</th>\n",
       "      <th>otxns_out</th>\n",
       "      <th>ounique_in</th>\n",
       "      <th>ounique_out</th>\n",
       "      <th>svol_in</th>\n",
       "      <th>svol_out</th>\n",
       "      <th>stxns_in</th>\n",
       "      <th>stxns_out</th>\n",
       "      <th>sunique_in</th>\n",
       "      <th>sunique_out</th>\n",
       "      <th>crid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>21712</th>\n",
       "      <td>22140</td>\n",
       "      <td>2020-05-21 05:34:28.666021</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Kinango Kwale</td>\n",
       "      <td>rural</td>\n",
       "      <td>UnKnown</td>\n",
       "      <td>other</td>\n",
       "      <td>Nopoa_13346</td>\n",
       "      <td>Noxdai_1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>21712</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                      start  final_bal   gender      area_name  \\\n",
       "21712  22140 2020-05-21 05:34:28.666021        0.0  Unknown  Kinango Kwale   \n",
       "\n",
       "      area_type held_roles business_type old_POA_blockchain_address  \\\n",
       "21712     rural    UnKnown         other                Nopoa_13346   \n",
       "\n",
       "      xDAI_blockchain_address  ...  otxns_out  ounique_in  ounique_out  \\\n",
       "21712                Noxdai_1  ...          1           0            1   \n",
       "\n",
       "       svol_in  svol_out  stxns_in  stxns_out  sunique_in  sunique_out   crid  \n",
       "21712      5.0       0.0         1          0           1            0  21712  \n",
       "\n",
       "[1 rows x 23 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.loc[users['xDAI_blockchain_address'].str.startswith('Noxdai')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Noxdai_1'"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.loc[21712].xDAI_blockchain_address"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>id</th>\n",
       "      <th>timeset</th>\n",
       "      <th>transfer_subtype</th>\n",
       "      <th>source</th>\n",
       "      <th>target</th>\n",
       "      <th>weight</th>\n",
       "      <th>token_name</th>\n",
       "      <th>token_address</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-05-24 18:19:57.480009</th>\n",
       "      <td>250207</td>\n",
       "      <td>2020-05-24 18:19:57.480009</td>\n",
       "      <td>STANDARD</td>\n",
       "      <td>0x889F9c52c6E996da730601593823D7a9F90959bc</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Sarafu</td>\n",
       "      <td>0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4</td>\n",
       "      <td>2020-05-24 18:19:57.480009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-07-26 06:01:28.022752</th>\n",
       "      <td>413319</td>\n",
       "      <td>2020-07-26 06:01:28.022752</td>\n",
       "      <td>STANDARD</td>\n",
       "      <td>0x489F432512525f51E2f5A9aa42B9C571AC7fc467</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>30.0</td>\n",
       "      <td>Sarafu</td>\n",
       "      <td>0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4</td>\n",
       "      <td>2020-07-26 06:01:28.022752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-20 07:47:30.111970</th>\n",
       "      <td>587512</td>\n",
       "      <td>2020-10-20 07:47:30.111970</td>\n",
       "      <td>RECLAMATION</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F</td>\n",
       "      <td>30.0</td>\n",
       "      <td>Sarafu</td>\n",
       "      <td>0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4</td>\n",
       "      <td>2020-10-20 07:47:30.111970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-10-20 09:54:14.821358</th>\n",
       "      <td>590434</td>\n",
       "      <td>2020-10-20 09:54:14.821358</td>\n",
       "      <td>RECLAMATION</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Sarafu</td>\n",
       "      <td>0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4</td>\n",
       "      <td>2020-10-20 09:54:14.821358</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                id                     timeset  \\\n",
       "date                                                             \n",
       "2020-05-24 18:19:57.480009  250207  2020-05-24 18:19:57.480009   \n",
       "2020-07-26 06:01:28.022752  413319  2020-07-26 06:01:28.022752   \n",
       "2020-10-20 07:47:30.111970  587512  2020-10-20 07:47:30.111970   \n",
       "2020-10-20 09:54:14.821358  590434  2020-10-20 09:54:14.821358   \n",
       "\n",
       "                           transfer_subtype  \\\n",
       "date                                          \n",
       "2020-05-24 18:19:57.480009         STANDARD   \n",
       "2020-07-26 06:01:28.022752         STANDARD   \n",
       "2020-10-20 07:47:30.111970      RECLAMATION   \n",
       "2020-10-20 09:54:14.821358      RECLAMATION   \n",
       "\n",
       "                                                                source  \\\n",
       "date                                                                     \n",
       "2020-05-24 18:19:57.480009  0x889F9c52c6E996da730601593823D7a9F90959bc   \n",
       "2020-07-26 06:01:28.022752  0x489F432512525f51E2f5A9aa42B9C571AC7fc467   \n",
       "2020-10-20 07:47:30.111970                                        <NA>   \n",
       "2020-10-20 09:54:14.821358                                        <NA>   \n",
       "\n",
       "                                                                target  \\\n",
       "date                                                                     \n",
       "2020-05-24 18:19:57.480009                                        <NA>   \n",
       "2020-07-26 06:01:28.022752                                        <NA>   \n",
       "2020-10-20 07:47:30.111970  0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F   \n",
       "2020-10-20 09:54:14.821358  0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F   \n",
       "\n",
       "                            weight token_name  \\\n",
       "date                                            \n",
       "2020-05-24 18:19:57.480009     5.0     Sarafu   \n",
       "2020-07-26 06:01:28.022752    30.0     Sarafu   \n",
       "2020-10-20 07:47:30.111970    30.0     Sarafu   \n",
       "2020-10-20 09:54:14.821358     5.0     Sarafu   \n",
       "\n",
       "                                                         token_address  \\\n",
       "date                                                                     \n",
       "2020-05-24 18:19:57.480009  0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4   \n",
       "2020-07-26 06:01:28.022752  0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4   \n",
       "2020-10-20 07:47:30.111970  0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4   \n",
       "2020-10-20 09:54:14.821358  0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4   \n",
       "\n",
       "                                                 date  \n",
       "date                                                   \n",
       "2020-05-24 18:19:57.480009 2020-05-24 18:19:57.480009  \n",
       "2020-07-26 06:01:28.022752 2020-07-26 06:01:28.022752  \n",
       "2020-10-20 07:47:30.111970 2020-10-20 07:47:30.111970  \n",
       "2020-10-20 09:54:14.821358 2020-10-20 09:54:14.821358  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "txns.loc[txns.target.isna()|txns.source.isna()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's just one account not having a xdai address"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>timeset</th>\n",
       "      <th>transfer_subtype</th>\n",
       "      <th>source</th>\n",
       "      <th>target</th>\n",
       "      <th>weight</th>\n",
       "      <th>token_name</th>\n",
       "      <th>token_address</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [id, timeset, transfer_subtype, source, target, weight, token_name, token_address, date]\n",
       "Index: []"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "no_xdai_user = users.loc[21712]\n",
    "fake_address = no_xdai_user.xDAI_blockchain_address\n",
    "\n",
    "txns['target'] = txns['target'].fillna(fake_address)\n",
    "txns['source'] = txns['source'].fillna(fake_address)\n",
    "\n",
    "txns[txns.isna().any(axis=1)] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add crid info to transaction dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "address_to_crid = dict(zip(users.xDAI_blockchain_address, users.crid))\n",
    "\n",
    "txns = txns.copy(deep=True)\n",
    "\n",
    "txns.rename(columns={'source':'source_xdai', 'target':'target_xdai'}, inplace=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>id</th>\n",
       "      <th>timeset</th>\n",
       "      <th>transfer_subtype</th>\n",
       "      <th>source_xdai</th>\n",
       "      <th>target_xdai</th>\n",
       "      <th>weight</th>\n",
       "      <th>token_name</th>\n",
       "      <th>token_address</th>\n",
       "      <th>date</th>\n",
       "      <th>source</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-25 19:13:17.731529</th>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-25 19:13:17.731529</td>\n",
       "      <td>DISBURSEMENT</td>\n",
       "      <td>0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F</td>\n",
       "      <td>0x245fc81fe385450Dc0f4787668e47c903C00b0A1</td>\n",
       "      <td>18000.000000</td>\n",
       "      <td>Sarafu</td>\n",
       "      <td>0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4</td>\n",
       "      <td>2020-01-25 19:13:17.731529</td>\n",
       "      <td>8356</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-25 19:13:19.056070</th>\n",
       "      <td>2</td>\n",
       "      <td>2020-01-25 19:13:19.056070</td>\n",
       "      <td>DISBURSEMENT</td>\n",
       "      <td>0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F</td>\n",
       "      <td>0xC1697C1326fD192438515fE2F7E4cCb0C705C5d2</td>\n",
       "      <td>9047.660892</td>\n",
       "      <td>Sarafu</td>\n",
       "      <td>0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4</td>\n",
       "      <td>2020-01-25 19:13:19.056070</td>\n",
       "      <td>8356</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-25 19:13:20.288346</th>\n",
       "      <td>3</td>\n",
       "      <td>2020-01-25 19:13:20.288346</td>\n",
       "      <td>DISBURSEMENT</td>\n",
       "      <td>0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F</td>\n",
       "      <td>0xBAB77A20a757e8438DfaBF01D5F36DD12d862B31</td>\n",
       "      <td>25378.726002</td>\n",
       "      <td>Sarafu</td>\n",
       "      <td>0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4</td>\n",
       "      <td>2020-01-25 19:13:20.288346</td>\n",
       "      <td>8356</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-25 19:13:21.478850</th>\n",
       "      <td>4</td>\n",
       "      <td>2020-01-25 19:13:21.478850</td>\n",
       "      <td>DISBURSEMENT</td>\n",
       "      <td>0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F</td>\n",
       "      <td>0xD95954e3fCd2f09A6Be5931D24f731eFa63BF435</td>\n",
       "      <td>4495.932576</td>\n",
       "      <td>Sarafu</td>\n",
       "      <td>0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4</td>\n",
       "      <td>2020-01-25 19:13:21.478850</td>\n",
       "      <td>8356</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-26 07:48:43.042684</th>\n",
       "      <td>5</td>\n",
       "      <td>2020-01-26 07:48:43.042684</td>\n",
       "      <td>DISBURSEMENT</td>\n",
       "      <td>0xBDB3Bc887C3b70586BC25D04d89eC802b897fC5F</td>\n",
       "      <td>0x4AB73CfaC1732a9DcD74BdB4C9605f21832D7C72</td>\n",
       "      <td>400.000000</td>\n",
       "      <td>Sarafu</td>\n",
       "      <td>0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4</td>\n",
       "      <td>2020-01-26 07:48:43.042684</td>\n",
       "      <td>8357</td>\n",
       "      <td>8358</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-15 04:46:20.990469</th>\n",
       "      <td>1035657</td>\n",
       "      <td>2021-06-15 04:46:20.990469</td>\n",
       "      <td>STANDARD</td>\n",
       "      <td>0x8581cd31b9a2FD9EB86fAA8c231c6E4167A47749</td>\n",
       "      <td>0x11C959A35453A793e05a19361782f3e13845708b</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>Sarafu</td>\n",
       "      <td>0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4</td>\n",
       "      <td>2021-06-15 04:46:20.990469</td>\n",
       "      <td>45861</td>\n",
       "      <td>47568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-15 04:47:58.072894</th>\n",
       "      <td>1035658</td>\n",
       "      <td>2021-06-15 04:47:58.072894</td>\n",
       "      <td>STANDARD</td>\n",
       "      <td>0x08637fE1D2D0F8FBdb2D3C2b8629aaD5E549A4b8</td>\n",
       "      <td>0x1CB6Ee7062C6Cd7CA8F0d71A84D7980b852E1648</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>Sarafu</td>\n",
       "      <td>0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4</td>\n",
       "      <td>2021-06-15 04:47:58.072894</td>\n",
       "      <td>54370</td>\n",
       "      <td>44268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-15 04:57:59.343271</th>\n",
       "      <td>1035659</td>\n",
       "      <td>2021-06-15 04:57:59.343271</td>\n",
       "      <td>STANDARD</td>\n",
       "      <td>0xafaFcc50bDB16eA7A6ecc69DEA267cc619E353d1</td>\n",
       "      <td>0xB88Cbf217F5b39762603aD7a6E0c86Bc0Bda1bb9</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>Sarafu</td>\n",
       "      <td>0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4</td>\n",
       "      <td>2021-06-15 04:57:59.343271</td>\n",
       "      <td>43619</td>\n",
       "      <td>42955</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-15 04:59:54.897303</th>\n",
       "      <td>1035660</td>\n",
       "      <td>2021-06-15 04:59:54.897303</td>\n",
       "      <td>STANDARD</td>\n",
       "      <td>0xD6bcBcAfbE21F5Afe5B78EC71E5afBE2811608ad</td>\n",
       "      <td>0xC0bB86A110c331f569f9e81CB4f387cfBF12E4B4</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>Sarafu</td>\n",
       "      <td>0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4</td>\n",
       "      <td>2021-06-15 04:59:54.897303</td>\n",
       "      <td>54891</td>\n",
       "      <td>51284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-15 05:00:30.887568</th>\n",
       "      <td>1035661</td>\n",
       "      <td>2021-06-15 05:00:30.887568</td>\n",
       "      <td>STANDARD</td>\n",
       "      <td>0x5aCA7747A43Eacfdca0A4C9D97826155dD4a432b</td>\n",
       "      <td>0xe813C88FeC150A79d6b0c2512E0B71f71F5Fd829</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>Sarafu</td>\n",
       "      <td>0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4</td>\n",
       "      <td>2021-06-15 05:00:30.887568</td>\n",
       "      <td>45396</td>\n",
       "      <td>45275</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>930161 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 id                     timeset  \\\n",
       "date                                                              \n",
       "2020-01-25 19:13:17.731529        1  2020-01-25 19:13:17.731529   \n",
       "2020-01-25 19:13:19.056070        2  2020-01-25 19:13:19.056070   \n",
       "2020-01-25 19:13:20.288346        3  2020-01-25 19:13:20.288346   \n",
       "2020-01-25 19:13:21.478850        4  2020-01-25 19:13:21.478850   \n",
       "2020-01-26 07:48:43.042684        5  2020-01-26 07:48:43.042684   \n",
       "...                             ...                         ...   \n",
       "2021-06-15 04:46:20.990469  1035657  2021-06-15 04:46:20.990469   \n",
       "2021-06-15 04:47:58.072894  1035658  2021-06-15 04:47:58.072894   \n",
       "2021-06-15 04:57:59.343271  1035659  2021-06-15 04:57:59.343271   \n",
       "2021-06-15 04:59:54.897303  1035660  2021-06-15 04:59:54.897303   \n",
       "2021-06-15 05:00:30.887568  1035661  2021-06-15 05:00:30.887568   \n",
       "\n",
       "                           transfer_subtype  \\\n",
       "date                                          \n",
       "2020-01-25 19:13:17.731529     DISBURSEMENT   \n",
       "2020-01-25 19:13:19.056070     DISBURSEMENT   \n",
       "2020-01-25 19:13:20.288346     DISBURSEMENT   \n",
       "2020-01-25 19:13:21.478850     DISBURSEMENT   \n",
       "2020-01-26 07:48:43.042684     DISBURSEMENT   \n",
       "...                                     ...   \n",
       "2021-06-15 04:46:20.990469         STANDARD   \n",
       "2021-06-15 04:47:58.072894         STANDARD   \n",
       "2021-06-15 04:57:59.343271         STANDARD   \n",
       "2021-06-15 04:59:54.897303         STANDARD   \n",
       "2021-06-15 05:00:30.887568         STANDARD   \n",
       "\n",
       "                                                           source_xdai  \\\n",
       "date                                                                     \n",
       "2020-01-25 19:13:17.731529  0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F   \n",
       "2020-01-25 19:13:19.056070  0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F   \n",
       "2020-01-25 19:13:20.288346  0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F   \n",
       "2020-01-25 19:13:21.478850  0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F   \n",
       "2020-01-26 07:48:43.042684  0xBDB3Bc887C3b70586BC25D04d89eC802b897fC5F   \n",
       "...                                                                ...   \n",
       "2021-06-15 04:46:20.990469  0x8581cd31b9a2FD9EB86fAA8c231c6E4167A47749   \n",
       "2021-06-15 04:47:58.072894  0x08637fE1D2D0F8FBdb2D3C2b8629aaD5E549A4b8   \n",
       "2021-06-15 04:57:59.343271  0xafaFcc50bDB16eA7A6ecc69DEA267cc619E353d1   \n",
       "2021-06-15 04:59:54.897303  0xD6bcBcAfbE21F5Afe5B78EC71E5afBE2811608ad   \n",
       "2021-06-15 05:00:30.887568  0x5aCA7747A43Eacfdca0A4C9D97826155dD4a432b   \n",
       "\n",
       "                                                           target_xdai  \\\n",
       "date                                                                     \n",
       "2020-01-25 19:13:17.731529  0x245fc81fe385450Dc0f4787668e47c903C00b0A1   \n",
       "2020-01-25 19:13:19.056070  0xC1697C1326fD192438515fE2F7E4cCb0C705C5d2   \n",
       "2020-01-25 19:13:20.288346  0xBAB77A20a757e8438DfaBF01D5F36DD12d862B31   \n",
       "2020-01-25 19:13:21.478850  0xD95954e3fCd2f09A6Be5931D24f731eFa63BF435   \n",
       "2020-01-26 07:48:43.042684  0x4AB73CfaC1732a9DcD74BdB4C9605f21832D7C72   \n",
       "...                                                                ...   \n",
       "2021-06-15 04:46:20.990469  0x11C959A35453A793e05a19361782f3e13845708b   \n",
       "2021-06-15 04:47:58.072894  0x1CB6Ee7062C6Cd7CA8F0d71A84D7980b852E1648   \n",
       "2021-06-15 04:57:59.343271  0xB88Cbf217F5b39762603aD7a6E0c86Bc0Bda1bb9   \n",
       "2021-06-15 04:59:54.897303  0xC0bB86A110c331f569f9e81CB4f387cfBF12E4B4   \n",
       "2021-06-15 05:00:30.887568  0xe813C88FeC150A79d6b0c2512E0B71f71F5Fd829   \n",
       "\n",
       "                                  weight token_name  \\\n",
       "date                                                  \n",
       "2020-01-25 19:13:17.731529  18000.000000     Sarafu   \n",
       "2020-01-25 19:13:19.056070   9047.660892     Sarafu   \n",
       "2020-01-25 19:13:20.288346  25378.726002     Sarafu   \n",
       "2020-01-25 19:13:21.478850   4495.932576     Sarafu   \n",
       "2020-01-26 07:48:43.042684    400.000000     Sarafu   \n",
       "...                                  ...        ...   \n",
       "2021-06-15 04:46:20.990469     10.000000     Sarafu   \n",
       "2021-06-15 04:47:58.072894     10.000000     Sarafu   \n",
       "2021-06-15 04:57:59.343271     15.000000     Sarafu   \n",
       "2021-06-15 04:59:54.897303     10.000000     Sarafu   \n",
       "2021-06-15 05:00:30.887568     20.000000     Sarafu   \n",
       "\n",
       "                                                         token_address  \\\n",
       "date                                                                     \n",
       "2020-01-25 19:13:17.731529  0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4   \n",
       "2020-01-25 19:13:19.056070  0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4   \n",
       "2020-01-25 19:13:20.288346  0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4   \n",
       "2020-01-25 19:13:21.478850  0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4   \n",
       "2020-01-26 07:48:43.042684  0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4   \n",
       "...                                                                ...   \n",
       "2021-06-15 04:46:20.990469  0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4   \n",
       "2021-06-15 04:47:58.072894  0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4   \n",
       "2021-06-15 04:57:59.343271  0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4   \n",
       "2021-06-15 04:59:54.897303  0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4   \n",
       "2021-06-15 05:00:30.887568  0x0Fd6e8F2320C90e9D4b3A5bd888c4D556d20AbD4   \n",
       "\n",
       "                                                 date  source  target  \n",
       "date                                                                   \n",
       "2020-01-25 19:13:17.731529 2020-01-25 19:13:17.731529    8356       2  \n",
       "2020-01-25 19:13:19.056070 2020-01-25 19:13:19.056070    8356       0  \n",
       "2020-01-25 19:13:20.288346 2020-01-25 19:13:20.288346    8356       1  \n",
       "2020-01-25 19:13:21.478850 2020-01-25 19:13:21.478850    8356       3  \n",
       "2020-01-26 07:48:43.042684 2020-01-26 07:48:43.042684    8357    8358  \n",
       "...                                               ...     ...     ...  \n",
       "2021-06-15 04:46:20.990469 2021-06-15 04:46:20.990469   45861   47568  \n",
       "2021-06-15 04:47:58.072894 2021-06-15 04:47:58.072894   54370   44268  \n",
       "2021-06-15 04:57:59.343271 2021-06-15 04:57:59.343271   43619   42955  \n",
       "2021-06-15 04:59:54.897303 2021-06-15 04:59:54.897303   54891   51284  \n",
       "2021-06-15 05:00:30.887568 2021-06-15 05:00:30.887568   45396   45275  \n",
       "\n",
       "[930161 rows x 11 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "txns['source'] = txns['source_xdai'].map(address_to_crid)\n",
    "\n",
    "txns['target'] = txns['target_xdai'].map(address_to_crid)\n",
    "\n",
    "txns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# # Apply the lambda function to all numeric columns\n",
    "# tx_with_bal_bwd[tx_with_bal_bwd.select_dtypes(include='number').columns] = tx_with_bal_bwd.select_dtypes(include='number').apply(\n",
    "#     lambda x: x.apply(lambda y: 0 if abs(y) < threshold else y)\n",
    "# )# tx_with_bal_bwd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-3"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "int(np.log10(txns.weight.min()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-25 19:13:17.731529</th>\n",
       "      <td>18000.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-25 19:13:19.056070</th>\n",
       "      <td>9047.661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-25 19:13:20.288346</th>\n",
       "      <td>25378.726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-25 19:13:21.478850</th>\n",
       "      <td>4495.933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-26 07:48:43.042684</th>\n",
       "      <td>400.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-15 04:46:20.990469</th>\n",
       "      <td>10.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-15 04:47:58.072894</th>\n",
       "      <td>10.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-15 04:57:59.343271</th>\n",
       "      <td>15.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-15 04:59:54.897303</th>\n",
       "      <td>10.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-15 05:00:30.887568</th>\n",
       "      <td>20.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>930161 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               weight\n",
       "date                                 \n",
       "2020-01-25 19:13:17.731529  18000.000\n",
       "2020-01-25 19:13:19.056070   9047.661\n",
       "2020-01-25 19:13:20.288346  25378.726\n",
       "2020-01-25 19:13:21.478850   4495.933\n",
       "2020-01-26 07:48:43.042684    400.000\n",
       "...                               ...\n",
       "2021-06-15 04:46:20.990469     10.000\n",
       "2021-06-15 04:47:58.072894     10.000\n",
       "2021-06-15 04:57:59.343271     15.000\n",
       "2021-06-15 04:59:54.897303     10.000\n",
       "2021-06-15 05:00:30.887568     20.000\n",
       "\n",
       "[930161 rows x 1 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "txns[txns.select_dtypes(include='float').columns] = txns.select_dtypes(include='float').apply(\n",
    "    lambda x: x.round(-int(np.log10(x.min())))\n",
    ")\n",
    "\n",
    "txns[txns.select_dtypes(include='float').columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>final_bal</th>\n",
       "      <th>ovol_in</th>\n",
       "      <th>ovol_out</th>\n",
       "      <th>svol_in</th>\n",
       "      <th>svol_out</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>57.471</td>\n",
       "      <td>9163.661</td>\n",
       "      <td>99.19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9007.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10853.966</td>\n",
       "      <td>87114.786</td>\n",
       "      <td>44483.92</td>\n",
       "      <td>76850.1</td>\n",
       "      <td>108627.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>80.870</td>\n",
       "      <td>6190197.950</td>\n",
       "      <td>6173351.08</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16766.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>43228.283</td>\n",
       "      <td>64873.233</td>\n",
       "      <td>365.25</td>\n",
       "      <td>317816.3</td>\n",
       "      <td>339096.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>595.320</td>\n",
       "      <td>723.690</td>\n",
       "      <td>458.37</td>\n",
       "      <td>330.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54994</th>\n",
       "      <td>0.000</td>\n",
       "      <td>50.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54995</th>\n",
       "      <td>50.000</td>\n",
       "      <td>50.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54996</th>\n",
       "      <td>30.000</td>\n",
       "      <td>50.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54997</th>\n",
       "      <td>50.000</td>\n",
       "      <td>50.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54998</th>\n",
       "      <td>30.000</td>\n",
       "      <td>50.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>54976 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       final_bal      ovol_in    ovol_out   svol_in  svol_out\n",
       "0         57.471     9163.661       99.19       0.0    9007.0\n",
       "1      10853.966    87114.786    44483.92   76850.1  108627.0\n",
       "2         80.870  6190197.950  6173351.08       0.0   16766.0\n",
       "3      43228.283    64873.233      365.25  317816.3  339096.0\n",
       "4        595.320      723.690      458.37     330.0       0.0\n",
       "...          ...          ...         ...       ...       ...\n",
       "54994      0.000       50.000        0.00       0.0      50.0\n",
       "54995     50.000       50.000        0.00       0.0       0.0\n",
       "54996     30.000       50.000        0.00       0.0      20.0\n",
       "54997     50.000       50.000        0.00       0.0       0.0\n",
       "54998     30.000       50.000        0.00       0.0      20.0\n",
       "\n",
       "[54976 rows x 5 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PRECISION = 3 #int(-np.log10(all_tx.weight.min()))\n",
    "\n",
    "users[users.select_dtypes(include='float').columns] = users.select_dtypes(include='float').apply(\n",
    "    lambda x: x.round(PRECISION)\n",
    ")\n",
    "\n",
    "users[users.select_dtypes(include='float').columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>final_bal</th>\n",
       "      <th>ovol_in</th>\n",
       "      <th>ovol_out</th>\n",
       "      <th>svol_in</th>\n",
       "      <th>svol_out</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>57.470892</td>\n",
       "      <td>9.163661e+03</td>\n",
       "      <td>99.19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9007.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10853.966002</td>\n",
       "      <td>8.711479e+04</td>\n",
       "      <td>44483.92</td>\n",
       "      <td>76850.1</td>\n",
       "      <td>108627.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>80.870000</td>\n",
       "      <td>6.190198e+06</td>\n",
       "      <td>6173351.08</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16766.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>43228.282576</td>\n",
       "      <td>6.487323e+04</td>\n",
       "      <td>365.25</td>\n",
       "      <td>317816.3</td>\n",
       "      <td>339096.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>595.319801</td>\n",
       "      <td>7.236898e+02</td>\n",
       "      <td>458.37</td>\n",
       "      <td>330.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54994</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.000000e+01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54995</th>\n",
       "      <td>50.000000</td>\n",
       "      <td>5.000000e+01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54996</th>\n",
       "      <td>30.000000</td>\n",
       "      <td>5.000000e+01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54997</th>\n",
       "      <td>50.000000</td>\n",
       "      <td>5.000000e+01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54998</th>\n",
       "      <td>30.000000</td>\n",
       "      <td>5.000000e+01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>54976 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          final_bal       ovol_in    ovol_out   svol_in  svol_out\n",
       "0         57.470892  9.163661e+03       99.19       0.0    9007.0\n",
       "1      10853.966002  8.711479e+04    44483.92   76850.1  108627.0\n",
       "2         80.870000  6.190198e+06  6173351.08       0.0   16766.0\n",
       "3      43228.282576  6.487323e+04      365.25  317816.3  339096.0\n",
       "4        595.319801  7.236898e+02      458.37     330.0       0.0\n",
       "...             ...           ...         ...       ...       ...\n",
       "54994      0.000000  5.000000e+01        0.00       0.0      50.0\n",
       "54995     50.000000  5.000000e+01        0.00       0.0       0.0\n",
       "54996     30.000000  5.000000e+01        0.00       0.0      20.0\n",
       "54997     50.000000  5.000000e+01        0.00       0.0       0.0\n",
       "54998     30.000000  5.000000e+01        0.00       0.0      20.0\n",
       "\n",
       "[54976 rows x 5 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users[users.select_dtypes(include='float').columns]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fixing a the problem related to the opening date formatting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       2018-10-21 14:20:57.000000\n",
       "1       2018-10-21 15:38:30.000000\n",
       "2       2018-10-23 09:09:58.000000\n",
       "3       2018-10-23 14:10:27.000000\n",
       "4       2018-10-24 08:17:32.000000\n",
       "                   ...            \n",
       "54994   2021-06-14 17:27:47.267723\n",
       "54995   2021-06-14 17:34:17.144344\n",
       "54996   2021-06-14 18:08:54.760767\n",
       "54997   2021-06-14 18:57:40.790519\n",
       "54998   2021-06-14 21:14:54.285784\n",
       "Name: start, Length: 54976, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.start"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now they should be ready to be cooked"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "txns.to_csv('/home/claudio/tesi/sarafu/final_data/txns.csv',index=False)\n",
    "users.to_csv('/home/claudio/tesi/sarafu/final_data/users.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': 'int',\n",
       " 'timeset': 'string',\n",
       " 'transfer_subtype': 'category',\n",
       " 'source_xdai': 'string',\n",
       " 'target_xdai': 'string',\n",
       " 'weight': 'float',\n",
       " 'token_name': 'category',\n",
       " 'token_address': 'string',\n",
       " 'date': 'datetime64[ns]',\n",
       " 'source': 'int',\n",
       " 'target': 'int'}"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dtype_mapping = {\n",
    "    'int64': 'int',\n",
    "    'float64': 'float',\n",
    "    'object': 'string',\n",
    "    'category': 'category'\n",
    "}\n",
    "\n",
    "dtype_dict = {col: dtype_mapping.get(str(txns[col].dtype), str(txns[col].dtype)) for col in txns.columns}\n",
    "\n",
    "dtype_dict\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': 'int',\n",
       " 'start': 'datetime64[ns]',\n",
       " 'final_bal': 'float',\n",
       " 'gender': 'category',\n",
       " 'area_name': 'string',\n",
       " 'area_type': 'category',\n",
       " 'held_roles': 'category',\n",
       " 'business_type': 'category',\n",
       " 'old_POA_blockchain_address': 'string',\n",
       " 'xDAI_blockchain_address': 'string',\n",
       " 'ovol_in': 'float',\n",
       " 'ovol_out': 'float',\n",
       " 'otxns_in': 'int',\n",
       " 'otxns_out': 'int',\n",
       " 'ounique_in': 'int',\n",
       " 'ounique_out': 'int',\n",
       " 'svol_in': 'float',\n",
       " 'svol_out': 'float',\n",
       " 'stxns_in': 'int',\n",
       " 'stxns_out': 'int',\n",
       " 'sunique_in': 'int',\n",
       " 'sunique_out': 'int',\n",
       " 'crid': 'int'}"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dtype_dict = {col: dtype_mapping.get(str(users[col].dtype), str(users[col].dtype)) for col in users.columns}\n",
    "\n",
    "dtype_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# txns.to_csv('/home/claudio/tesi/sarafu/Sarafu2021_UKdb_submission(1)/sarafu_xDAI/sarafu_txns_20200125-20210615_clean.csv',index=False)\n",
    "# crid_txns.to_csv('/home/claudio/tesi/sarafu/Sarafu2021_UKdb_submission(1)/sarafu_xDAI/sarafu_txns_20200125-20210615_clean_crid.csv',index=False)\n",
    "# sorted_users.to_csv('/home/claudio/tesi/sarafu/Sarafu2021_UKdb_submission(1)/sarafu_xDAI/sarafu_users_20210615_clean.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sorted_users[sorted_users.xDAI_blockchain_address.isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sorted_users[sorted_users.crid == 45396].final_bal,sorted_users[sorted_users.crid == 45275].final_bal"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}