01_balances_reconstruction_and_validation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "from myutils.utilities import *\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.float_format', '{:.3f}'.format)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 930161 entries, 0 to 930160\n",
      "Data columns (total 11 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_xdai       930161 non-null  object  \n",
      " 4   target_xdai       930161 non-null  object  \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  object  \n",
      " 9   source            930161 non-null  string  \n",
      " 10  target            930161 non-null  string  \n",
      "dtypes: category(2), float64(1), int64(1), object(3), string(4)\n",
      "memory usage: 65.6+ 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/final_data/txns.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.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/final_data/users.csv'\n",
    "users = pd.read_csv(users_fn,  # assuming 'user_fn' is your file name\n",
    "                      sep=\",\",\n",
    "                      header=0,\n",
    "                      dtype={'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",
    "                            'crid': 'int'}) \n",
    "\n",
    "                            \n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [id, start, final_bal, gender, area_name, area_type, held_roles, business_type, old_POA_blockchain_address, xDAI_blockchain_address, ovol_in, ovol_out, otxns_in, otxns_out, ounique_in, ounique_out, svol_in, svol_out, stxns_in, stxns_out, sunique_in, sunique_out, crid]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 23 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.loc[users.final_bal<0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "PRECISION = 3 # number of decimal places to round"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "check if the data is clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Index([], dtype='object'), Index(['final_bal'], dtype='object'))"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "txns.columns[txns.isna().any()],users.columns[users.isna().any()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "rename columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "txns.drop(columns=['token_name','token_address','date'],inplace=True)\n",
    "txns.rename(columns={\"transfer_subtype\": \"type\",'timeset': 'date'},inplace=True)\n",
    "users.rename(columns={\"held_roles\": \"role\",\n",
    "                          \"business_type\": \"business\",\n",
    "                          \"old_POA_blockchain_address\": \"poa\",\n",
    "                          \"xDAI_blockchain_address\": \"xdai\",},inplace=True)\n",
    "\n",
    "# users.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create 'Net cash flow' and 'Initial balance' column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_out = users.svol_out + users.ovol_out\n",
    "all_in = users.svol_in + users.ovol_in\n",
    "\n",
    "net = all_in - all_out\n",
    "\n",
    "users['net'] = net\n",
    "# users['final_bal'] = users['final_bal'].fillna(0)\n",
    "users['final_bal'] = users['final_bal'].fillna(abs(users['net']))\n",
    "\n",
    "users['initial_bal'] = users['final_bal'] - net\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([], dtype='object')"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.columns[users.isna().any()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['net', 'initial_bal'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "# Select columns with numeric types\n",
    "numeric_cols = users.select_dtypes(include='number')\n",
    "\n",
    "# Check which columns contain any values less than 0\n",
    "columns_with_negative_values = numeric_cols.columns[(numeric_cols < 0).any(axis=0)]\n",
    "\n",
    "print(columns_with_negative_values)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "notebookRunGroups": {
     "groupValue": "2"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'PRECISION' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[10], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m users[users\u001b[38;5;241m.\u001b[39mselect_dtypes(include\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mfloat\u001b[39m\u001b[38;5;124m'\u001b[39m)\u001b[38;5;241m.\u001b[39mcolumns] \u001b[38;5;241m=\u001b[39m \u001b[43musers\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mselect_dtypes\u001b[49m\u001b[43m(\u001b[49m\u001b[43minclude\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mfloat\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mapply\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m      2\u001b[0m \u001b[43m    \u001b[49m\u001b[38;5;28;43;01mlambda\u001b[39;49;00m\u001b[43m \u001b[49m\u001b[43mx\u001b[49m\u001b[43m:\u001b[49m\u001b[43m \u001b[49m\u001b[43mx\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mround\u001b[49m\u001b[43m(\u001b[49m\u001b[43mPRECISION\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m      3\u001b[0m \u001b[43m)\u001b[49m\n\u001b[1;32m      5\u001b[0m users\u001b[38;5;241m.\u001b[39minitial_bal\u001b[38;5;241m.\u001b[39mvalue_counts()\n",
      "File \u001b[0;32m~/tesi/sarafu/.venv/lib/python3.10/site-packages/pandas/core/frame.py:10374\u001b[0m, in \u001b[0;36mDataFrame.apply\u001b[0;34m(self, func, axis, raw, result_type, args, by_row, engine, engine_kwargs, **kwargs)\u001b[0m\n\u001b[1;32m  10360\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mpandas\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mcore\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mapply\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m frame_apply\n\u001b[1;32m  10362\u001b[0m op \u001b[38;5;241m=\u001b[39m frame_apply(\n\u001b[1;32m  10363\u001b[0m     \u001b[38;5;28mself\u001b[39m,\n\u001b[1;32m  10364\u001b[0m     func\u001b[38;5;241m=\u001b[39mfunc,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m  10372\u001b[0m     kwargs\u001b[38;5;241m=\u001b[39mkwargs,\n\u001b[1;32m  10373\u001b[0m )\n\u001b[0;32m> 10374\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mop\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mapply\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\u001b[38;5;241m.\u001b[39m__finalize__(\u001b[38;5;28mself\u001b[39m, method\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mapply\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "File \u001b[0;32m~/tesi/sarafu/.venv/lib/python3.10/site-packages/pandas/core/apply.py:916\u001b[0m, in \u001b[0;36mFrameApply.apply\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    913\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mraw:\n\u001b[1;32m    914\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mapply_raw(engine\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine, engine_kwargs\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine_kwargs)\n\u001b[0;32m--> 916\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mapply_standard\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/tesi/sarafu/.venv/lib/python3.10/site-packages/pandas/core/apply.py:1063\u001b[0m, in \u001b[0;36mFrameApply.apply_standard\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1061\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mapply_standard\u001b[39m(\u001b[38;5;28mself\u001b[39m):\n\u001b[1;32m   1062\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mpython\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[0;32m-> 1063\u001b[0m         results, res_index \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mapply_series_generator\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1064\u001b[0m     \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m   1065\u001b[0m         results, res_index \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mapply_series_numba()\n",
      "File \u001b[0;32m~/tesi/sarafu/.venv/lib/python3.10/site-packages/pandas/core/apply.py:1081\u001b[0m, in \u001b[0;36mFrameApply.apply_series_generator\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1078\u001b[0m \u001b[38;5;28;01mwith\u001b[39;00m option_context(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mmode.chained_assignment\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;28;01mNone\u001b[39;00m):\n\u001b[1;32m   1079\u001b[0m     \u001b[38;5;28;01mfor\u001b[39;00m i, v \u001b[38;5;129;01min\u001b[39;00m \u001b[38;5;28menumerate\u001b[39m(series_gen):\n\u001b[1;32m   1080\u001b[0m         \u001b[38;5;66;03m# ignore SettingWithCopy here in case the user mutates\u001b[39;00m\n\u001b[0;32m-> 1081\u001b[0m         results[i] \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mfunc\u001b[49m\u001b[43m(\u001b[49m\u001b[43mv\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1082\u001b[0m         \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(results[i], ABCSeries):\n\u001b[1;32m   1083\u001b[0m             \u001b[38;5;66;03m# If we have a view on v, we need to make a copy because\u001b[39;00m\n\u001b[1;32m   1084\u001b[0m             \u001b[38;5;66;03m#  series_generator will swap out the underlying data\u001b[39;00m\n\u001b[1;32m   1085\u001b[0m             results[i] \u001b[38;5;241m=\u001b[39m results[i]\u001b[38;5;241m.\u001b[39mcopy(deep\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mFalse\u001b[39;00m)\n",
      "Cell \u001b[0;32mIn[10], line 2\u001b[0m, in \u001b[0;36m<lambda>\u001b[0;34m(x)\u001b[0m\n\u001b[1;32m      1\u001b[0m users[users\u001b[38;5;241m.\u001b[39mselect_dtypes(include\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mfloat\u001b[39m\u001b[38;5;124m'\u001b[39m)\u001b[38;5;241m.\u001b[39mcolumns] \u001b[38;5;241m=\u001b[39m users\u001b[38;5;241m.\u001b[39mselect_dtypes(include\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mfloat\u001b[39m\u001b[38;5;124m'\u001b[39m)\u001b[38;5;241m.\u001b[39mapply(\n\u001b[0;32m----> 2\u001b[0m     \u001b[38;5;28;01mlambda\u001b[39;00m x: x\u001b[38;5;241m.\u001b[39mround(\u001b[43mPRECISION\u001b[49m)\n\u001b[1;32m      3\u001b[0m )\n\u001b[1;32m      5\u001b[0m users\u001b[38;5;241m.\u001b[39minitial_bal\u001b[38;5;241m.\u001b[39mvalue_counts()\n",
      "\u001b[0;31mNameError\u001b[0m: name 'PRECISION' is not defined"
     ]
    }
   ],
   "source": [
    "users[users.select_dtypes(include='float').columns] = users.select_dtypes(include='float').apply(\n",
    "    lambda x: x.round(PRECISION)\n",
    ")\n",
    "\n",
    "users.initial_bal.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "mask = (users['final_bal'] < 0) | (users['initial_bal'] < 0)\n",
    "users.loc[mask, ['final_bal', 'initial_bal']] = users.loc[mask, ['final_bal', 'initial_bal']].apply(\n",
    "    lambda row: row - row.min(), axis=1\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'PRECISION' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[12], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m users[users\u001b[38;5;241m.\u001b[39mselect_dtypes(include\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mfloat\u001b[39m\u001b[38;5;124m'\u001b[39m)\u001b[38;5;241m.\u001b[39mcolumns] \u001b[38;5;241m=\u001b[39m \u001b[43musers\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mselect_dtypes\u001b[49m\u001b[43m(\u001b[49m\u001b[43minclude\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mfloat\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mapply\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m      2\u001b[0m \u001b[43m    \u001b[49m\u001b[38;5;28;43;01mlambda\u001b[39;49;00m\u001b[43m \u001b[49m\u001b[43mx\u001b[49m\u001b[43m:\u001b[49m\u001b[43m \u001b[49m\u001b[43mx\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mround\u001b[49m\u001b[43m(\u001b[49m\u001b[43mPRECISION\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m      3\u001b[0m \u001b[43m)\u001b[49m\n\u001b[1;32m      5\u001b[0m users\u001b[38;5;241m.\u001b[39minitial_bal\u001b[38;5;241m.\u001b[39mvalue_counts()\n",
      "File \u001b[0;32m~/tesi/sarafu/.venv/lib/python3.10/site-packages/pandas/core/frame.py:10374\u001b[0m, in \u001b[0;36mDataFrame.apply\u001b[0;34m(self, func, axis, raw, result_type, args, by_row, engine, engine_kwargs, **kwargs)\u001b[0m\n\u001b[1;32m  10360\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mpandas\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mcore\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mapply\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m frame_apply\n\u001b[1;32m  10362\u001b[0m op \u001b[38;5;241m=\u001b[39m frame_apply(\n\u001b[1;32m  10363\u001b[0m     \u001b[38;5;28mself\u001b[39m,\n\u001b[1;32m  10364\u001b[0m     func\u001b[38;5;241m=\u001b[39mfunc,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m  10372\u001b[0m     kwargs\u001b[38;5;241m=\u001b[39mkwargs,\n\u001b[1;32m  10373\u001b[0m )\n\u001b[0;32m> 10374\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mop\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mapply\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\u001b[38;5;241m.\u001b[39m__finalize__(\u001b[38;5;28mself\u001b[39m, method\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mapply\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "File \u001b[0;32m~/tesi/sarafu/.venv/lib/python3.10/site-packages/pandas/core/apply.py:916\u001b[0m, in \u001b[0;36mFrameApply.apply\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    913\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mraw:\n\u001b[1;32m    914\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mapply_raw(engine\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine, engine_kwargs\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine_kwargs)\n\u001b[0;32m--> 916\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mapply_standard\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/tesi/sarafu/.venv/lib/python3.10/site-packages/pandas/core/apply.py:1063\u001b[0m, in \u001b[0;36mFrameApply.apply_standard\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1061\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mapply_standard\u001b[39m(\u001b[38;5;28mself\u001b[39m):\n\u001b[1;32m   1062\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mpython\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[0;32m-> 1063\u001b[0m         results, res_index \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mapply_series_generator\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1064\u001b[0m     \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m   1065\u001b[0m         results, res_index \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mapply_series_numba()\n",
      "File \u001b[0;32m~/tesi/sarafu/.venv/lib/python3.10/site-packages/pandas/core/apply.py:1081\u001b[0m, in \u001b[0;36mFrameApply.apply_series_generator\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1078\u001b[0m \u001b[38;5;28;01mwith\u001b[39;00m option_context(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mmode.chained_assignment\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;28;01mNone\u001b[39;00m):\n\u001b[1;32m   1079\u001b[0m     \u001b[38;5;28;01mfor\u001b[39;00m i, v \u001b[38;5;129;01min\u001b[39;00m \u001b[38;5;28menumerate\u001b[39m(series_gen):\n\u001b[1;32m   1080\u001b[0m         \u001b[38;5;66;03m# ignore SettingWithCopy here in case the user mutates\u001b[39;00m\n\u001b[0;32m-> 1081\u001b[0m         results[i] \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mfunc\u001b[49m\u001b[43m(\u001b[49m\u001b[43mv\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1082\u001b[0m         \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(results[i], ABCSeries):\n\u001b[1;32m   1083\u001b[0m             \u001b[38;5;66;03m# If we have a view on v, we need to make a copy because\u001b[39;00m\n\u001b[1;32m   1084\u001b[0m             \u001b[38;5;66;03m#  series_generator will swap out the underlying data\u001b[39;00m\n\u001b[1;32m   1085\u001b[0m             results[i] \u001b[38;5;241m=\u001b[39m results[i]\u001b[38;5;241m.\u001b[39mcopy(deep\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mFalse\u001b[39;00m)\n",
      "Cell \u001b[0;32mIn[12], line 2\u001b[0m, in \u001b[0;36m<lambda>\u001b[0;34m(x)\u001b[0m\n\u001b[1;32m      1\u001b[0m users[users\u001b[38;5;241m.\u001b[39mselect_dtypes(include\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mfloat\u001b[39m\u001b[38;5;124m'\u001b[39m)\u001b[38;5;241m.\u001b[39mcolumns] \u001b[38;5;241m=\u001b[39m users\u001b[38;5;241m.\u001b[39mselect_dtypes(include\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mfloat\u001b[39m\u001b[38;5;124m'\u001b[39m)\u001b[38;5;241m.\u001b[39mapply(\n\u001b[0;32m----> 2\u001b[0m     \u001b[38;5;28;01mlambda\u001b[39;00m x: x\u001b[38;5;241m.\u001b[39mround(\u001b[43mPRECISION\u001b[49m)\n\u001b[1;32m      3\u001b[0m )\n\u001b[1;32m      5\u001b[0m users\u001b[38;5;241m.\u001b[39minitial_bal\u001b[38;5;241m.\u001b[39mvalue_counts()\n",
      "\u001b[0;31mNameError\u001b[0m: name 'PRECISION' is not defined"
     ]
    }
   ],
   "source": [
    "users[users.select_dtypes(include='float').columns] = users.select_dtypes(include='float').apply(\n",
    "    lambda x: x.round(PRECISION)\n",
    ")\n",
    "\n",
    "users.initial_bal.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "txns['source_role'] = txns['source_xdai'].map(users.set_index('xdai')['role'])\n",
    "txns['target_role'] = txns['target_xdai'].map(users.set_index('xdai')['role'])\n",
    "\n",
    "\n",
    "txns['target_role'] = txns['target_role'].fillna('UnKnown')\n",
    "txns['source_role'] = txns['source_role'].fillna('UnKnown')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "first round of balance integration, backward "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "tx_with_bal = txns.copy(deep=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "previous_balance = {f'{address}': final_balance for address, final_balance in zip(users['crid'], users['final_bal'])}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "previous_balance = {f'{address}': final_balance for address, final_balance in zip(users['crid'], users['final_bal'])}\n",
    "\n",
    "# Sort the DataFrame by date from newest to oldest\n",
    "# tx_with_bal = all_tx.copy(deep=True)\n",
    "tx_with_bal.sort_values(by='date', ascending=False, inplace=True)\n",
    "\n",
    "# Iterate over the sorted DataFrame\n",
    "source_bal_pre = []\n",
    "target_bal_pre = []\n",
    "source_bal_post = []\n",
    "target_bal_post = []\n",
    "\n",
    "for item in tx_with_bal.itertuples(index=True, name='Row'):\n",
    "    # Set post-transaction balances\n",
    "    source_bal_post.append(previous_balance[item.source])\n",
    "    target_bal_post.append(previous_balance[item.target])\n",
    "\n",
    "    # Calculate pre-transaction balances\n",
    "    source_bal_pre.append(previous_balance[item.source] + item.weight)\n",
    "    target_bal_pre.append(previous_balance[item.target] - item.weight)\n",
    "\n",
    "    # Update the balances in the dictionary\n",
    "    previous_balance[item.source] += item.weight\n",
    "    previous_balance[item.target] -= item.weight\n",
    "\n",
    "# Assign the computed lists back to the DataFrame\n",
    "tx_with_bal['source_bal_pre'] = source_bal_pre\n",
    "tx_with_bal['source_bal_post'] = source_bal_post\n",
    "tx_with_bal['target_bal_pre'] = target_bal_pre\n",
    "tx_with_bal['target_bal_post'] = target_bal_post\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "tx_with_bal=tx_with_bal[['id', 'date', 'type', \n",
    "                                 'source_xdai', 'target_xdai', \n",
    "                                 'weight',\n",
    "                                 'source_bal_pre','source_bal_post', \n",
    "                                 'target_bal_pre', 'target_bal_post', \n",
    "                                 'source', 'target', \n",
    "                                 'source_role', 'target_role'\n",
    "       ]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "right_weight\n",
       "True    930161\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tx_with_bal['right_weight'] = np.isclose(tx_with_bal['weight'], abs(tx_with_bal['source_bal_pre'] - tx_with_bal['source_bal_post']),atol=1e-3)\n",
    "tx_with_bal.right_weight.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "tx_with_bal.sort_values(by='date', ascending=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>date</th>\n",
       "      <th>type</th>\n",
       "      <th>source_xdai</th>\n",
       "      <th>target_xdai</th>\n",
       "      <th>weight</th>\n",
       "      <th>source_bal_pre</th>\n",
       "      <th>source_bal_post</th>\n",
       "      <th>target_bal_pre</th>\n",
       "      <th>target_bal_post</th>\n",
       "      <th>source</th>\n",
       "      <th>target</th>\n",
       "      <th>source_role</th>\n",
       "      <th>target_role</th>\n",
       "      <th>right_weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</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>-10330585.919000</td>\n",
       "      <td>-10348585.919000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>18000.000000</td>\n",
       "      <td>8356</td>\n",
       "      <td>2</td>\n",
       "      <td>ADMIN</td>\n",
       "      <td>TOKEN_AGENT</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</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.661000</td>\n",
       "      <td>-10348585.919000</td>\n",
       "      <td>-10357633.580000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>9047.661000</td>\n",
       "      <td>8356</td>\n",
       "      <td>0</td>\n",
       "      <td>ADMIN</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</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.726000</td>\n",
       "      <td>-10357633.580000</td>\n",
       "      <td>-10383012.306000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>25378.726000</td>\n",
       "      <td>8356</td>\n",
       "      <td>1</td>\n",
       "      <td>ADMIN</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</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.933000</td>\n",
       "      <td>-10383012.306000</td>\n",
       "      <td>-10387508.239000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4495.933000</td>\n",
       "      <td>8356</td>\n",
       "      <td>3</td>\n",
       "      <td>ADMIN</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</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>43724689.390003</td>\n",
       "      <td>43724289.390003</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>400.000000</td>\n",
       "      <td>8357</td>\n",
       "      <td>8358</td>\n",
       "      <td>ADMIN</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>True</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>930156</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>95.000000</td>\n",
       "      <td>85.000000</td>\n",
       "      <td>200.800000</td>\n",
       "      <td>210.800000</td>\n",
       "      <td>45861</td>\n",
       "      <td>47568</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>930157</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>216.000000</td>\n",
       "      <td>206.000000</td>\n",
       "      <td>213.000000</td>\n",
       "      <td>223.000000</td>\n",
       "      <td>54370</td>\n",
       "      <td>44268</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>930158</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>254.000000</td>\n",
       "      <td>239.000000</td>\n",
       "      <td>246.200000</td>\n",
       "      <td>261.200000</td>\n",
       "      <td>43619</td>\n",
       "      <td>42955</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>930159</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>50.000000</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>400.000000</td>\n",
       "      <td>410.000000</td>\n",
       "      <td>54891</td>\n",
       "      <td>51284</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>930160</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>116.960000</td>\n",
       "      <td>96.960000</td>\n",
       "      <td>346.420000</td>\n",
       "      <td>366.420000</td>\n",
       "      <td>45396</td>\n",
       "      <td>45275</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>930161 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             id                        date          type  \\\n",
       "0             1  2020-01-25 19:13:17.731529  DISBURSEMENT   \n",
       "1             2  2020-01-25 19:13:19.056070  DISBURSEMENT   \n",
       "2             3  2020-01-25 19:13:20.288346  DISBURSEMENT   \n",
       "3             4  2020-01-25 19:13:21.478850  DISBURSEMENT   \n",
       "4             5  2020-01-26 07:48:43.042684  DISBURSEMENT   \n",
       "...         ...                         ...           ...   \n",
       "930156  1035657  2021-06-15 04:46:20.990469      STANDARD   \n",
       "930157  1035658  2021-06-15 04:47:58.072894      STANDARD   \n",
       "930158  1035659  2021-06-15 04:57:59.343271      STANDARD   \n",
       "930159  1035660  2021-06-15 04:59:54.897303      STANDARD   \n",
       "930160  1035661  2021-06-15 05:00:30.887568      STANDARD   \n",
       "\n",
       "                                       source_xdai  \\\n",
       "0       0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F   \n",
       "1       0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F   \n",
       "2       0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F   \n",
       "3       0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F   \n",
       "4       0xBDB3Bc887C3b70586BC25D04d89eC802b897fC5F   \n",
       "...                                            ...   \n",
       "930156  0x8581cd31b9a2FD9EB86fAA8c231c6E4167A47749   \n",
       "930157  0x08637fE1D2D0F8FBdb2D3C2b8629aaD5E549A4b8   \n",
       "930158  0xafaFcc50bDB16eA7A6ecc69DEA267cc619E353d1   \n",
       "930159  0xD6bcBcAfbE21F5Afe5B78EC71E5afBE2811608ad   \n",
       "930160  0x5aCA7747A43Eacfdca0A4C9D97826155dD4a432b   \n",
       "\n",
       "                                       target_xdai       weight  \\\n",
       "0       0x245fc81fe385450Dc0f4787668e47c903C00b0A1 18000.000000   \n",
       "1       0xC1697C1326fD192438515fE2F7E4cCb0C705C5d2  9047.661000   \n",
       "2       0xBAB77A20a757e8438DfaBF01D5F36DD12d862B31 25378.726000   \n",
       "3       0xD95954e3fCd2f09A6Be5931D24f731eFa63BF435  4495.933000   \n",
       "4       0x4AB73CfaC1732a9DcD74BdB4C9605f21832D7C72   400.000000   \n",
       "...                                            ...          ...   \n",
       "930156  0x11C959A35453A793e05a19361782f3e13845708b    10.000000   \n",
       "930157  0x1CB6Ee7062C6Cd7CA8F0d71A84D7980b852E1648    10.000000   \n",
       "930158  0xB88Cbf217F5b39762603aD7a6E0c86Bc0Bda1bb9    15.000000   \n",
       "930159  0xC0bB86A110c331f569f9e81CB4f387cfBF12E4B4    10.000000   \n",
       "930160  0xe813C88FeC150A79d6b0c2512E0B71f71F5Fd829    20.000000   \n",
       "\n",
       "         source_bal_pre  source_bal_post  target_bal_pre  target_bal_post  \\\n",
       "0      -10330585.919000 -10348585.919000        0.000000     18000.000000   \n",
       "1      -10348585.919000 -10357633.580000        0.000000      9047.661000   \n",
       "2      -10357633.580000 -10383012.306000        0.000000     25378.726000   \n",
       "3      -10383012.306000 -10387508.239000        0.000000      4495.933000   \n",
       "4       43724689.390003  43724289.390003        0.000000       400.000000   \n",
       "...                 ...              ...             ...              ...   \n",
       "930156        95.000000        85.000000      200.800000       210.800000   \n",
       "930157       216.000000       206.000000      213.000000       223.000000   \n",
       "930158       254.000000       239.000000      246.200000       261.200000   \n",
       "930159        50.000000        40.000000      400.000000       410.000000   \n",
       "930160       116.960000        96.960000      346.420000       366.420000   \n",
       "\n",
       "       source target  source_role  target_role  right_weight  \n",
       "0        8356      2        ADMIN  TOKEN_AGENT          True  \n",
       "1        8356      0        ADMIN  BENEFICIARY          True  \n",
       "2        8356      1        ADMIN  BENEFICIARY          True  \n",
       "3        8356      3        ADMIN  BENEFICIARY          True  \n",
       "4        8357   8358        ADMIN  BENEFICIARY          True  \n",
       "...       ...    ...          ...          ...           ...  \n",
       "930156  45861  47568  BENEFICIARY  BENEFICIARY          True  \n",
       "930157  54370  44268  BENEFICIARY  BENEFICIARY          True  \n",
       "930158  43619  42955  BENEFICIARY  BENEFICIARY          True  \n",
       "930159  54891  51284  BENEFICIARY  BENEFICIARY          True  \n",
       "930160  45396  45275  BENEFICIARY  BENEFICIARY          True  \n",
       "\n",
       "[930161 rows x 15 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "THRESHOLD = 10**(-PRECISION)\n",
    "\n",
    "float_cols = tx_with_bal.select_dtypes(include='float').columns\n",
    "\n",
    "# Apply the lambda function to float columns\n",
    "tx_with_bal[float_cols] = tx_with_bal[float_cols].apply(\n",
    "    lambda x: x.apply(lambda y: 0 if abs(y) < THRESHOLD else y)\n",
    ")\n",
    "tx_with_bal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "balances_pre = pd.DataFrame({\n",
    "    'date': tx_with_bal['date'].repeat(2).values,  # Repeat each time twice\n",
    "    'balance': tx_with_bal[['source_bal_pre', 'target_bal_pre']].values.flatten(),\n",
    "    'xdai': tx_with_bal[['source_xdai', 'target_xdai']].values.flatten(),  # Stack feature1 and feature2 in an alternating pattern\n",
    "    'type': tx_with_bal['type'].repeat(2).values\n",
    "})\n",
    "\n",
    "balances_post = pd.DataFrame({\n",
    "    'date': tx_with_bal['date'].repeat(2).values,  # Repeat each time twice\n",
    "    'balance': tx_with_bal[['source_bal_post', 'target_bal_post']].values.flatten(),\n",
    "    'xdai': tx_with_bal[['source_xdai', 'target_xdai']].values.flatten(),  # Stack feature1 and feature2 in an alternating pattern\n",
    "    'type': tx_with_bal['type'].repeat(2).values\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>date</th>\n",
       "      <th>balance</th>\n",
       "      <th>xdai</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-25 19:13:17.731529</td>\n",
       "      <td>-10348585.919000</td>\n",
       "      <td>0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F</td>\n",
       "      <td>DISBURSEMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-25 19:13:17.731529</td>\n",
       "      <td>18000.000000</td>\n",
       "      <td>0x245fc81fe385450Dc0f4787668e47c903C00b0A1</td>\n",
       "      <td>DISBURSEMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-25 19:13:19.056070</td>\n",
       "      <td>-10357633.580000</td>\n",
       "      <td>0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F</td>\n",
       "      <td>DISBURSEMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-25 19:13:19.056070</td>\n",
       "      <td>9047.661000</td>\n",
       "      <td>0xC1697C1326fD192438515fE2F7E4cCb0C705C5d2</td>\n",
       "      <td>DISBURSEMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-25 19:13:20.288346</td>\n",
       "      <td>-10383012.306000</td>\n",
       "      <td>0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F</td>\n",
       "      <td>DISBURSEMENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1860317</th>\n",
       "      <td>2021-06-15 04:57:59.343271</td>\n",
       "      <td>261.200000</td>\n",
       "      <td>0xB88Cbf217F5b39762603aD7a6E0c86Bc0Bda1bb9</td>\n",
       "      <td>STANDARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1860318</th>\n",
       "      <td>2021-06-15 04:59:54.897303</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>0xD6bcBcAfbE21F5Afe5B78EC71E5afBE2811608ad</td>\n",
       "      <td>STANDARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1860319</th>\n",
       "      <td>2021-06-15 04:59:54.897303</td>\n",
       "      <td>410.000000</td>\n",
       "      <td>0xC0bB86A110c331f569f9e81CB4f387cfBF12E4B4</td>\n",
       "      <td>STANDARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1860320</th>\n",
       "      <td>2021-06-15 05:00:30.887568</td>\n",
       "      <td>96.960000</td>\n",
       "      <td>0x5aCA7747A43Eacfdca0A4C9D97826155dD4a432b</td>\n",
       "      <td>STANDARD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1860321</th>\n",
       "      <td>2021-06-15 05:00:30.887568</td>\n",
       "      <td>366.420000</td>\n",
       "      <td>0xe813C88FeC150A79d6b0c2512E0B71f71F5Fd829</td>\n",
       "      <td>STANDARD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1860322 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               date          balance  \\\n",
       "0        2020-01-25 19:13:17.731529 -10348585.919000   \n",
       "1        2020-01-25 19:13:17.731529     18000.000000   \n",
       "2        2020-01-25 19:13:19.056070 -10357633.580000   \n",
       "3        2020-01-25 19:13:19.056070      9047.661000   \n",
       "4        2020-01-25 19:13:20.288346 -10383012.306000   \n",
       "...                             ...              ...   \n",
       "1860317  2021-06-15 04:57:59.343271       261.200000   \n",
       "1860318  2021-06-15 04:59:54.897303        40.000000   \n",
       "1860319  2021-06-15 04:59:54.897303       410.000000   \n",
       "1860320  2021-06-15 05:00:30.887568        96.960000   \n",
       "1860321  2021-06-15 05:00:30.887568       366.420000   \n",
       "\n",
       "                                               xdai          type  \n",
       "0        0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F  DISBURSEMENT  \n",
       "1        0x245fc81fe385450Dc0f4787668e47c903C00b0A1  DISBURSEMENT  \n",
       "2        0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F  DISBURSEMENT  \n",
       "3        0xC1697C1326fD192438515fE2F7E4cCb0C705C5d2  DISBURSEMENT  \n",
       "4        0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F  DISBURSEMENT  \n",
       "...                                             ...           ...  \n",
       "1860317  0xB88Cbf217F5b39762603aD7a6E0c86Bc0Bda1bb9      STANDARD  \n",
       "1860318  0xD6bcBcAfbE21F5Afe5B78EC71E5afBE2811608ad      STANDARD  \n",
       "1860319  0xC0bB86A110c331f569f9e81CB4f387cfBF12E4B4      STANDARD  \n",
       "1860320  0x5aCA7747A43Eacfdca0A4C9D97826155dD4a432b      STANDARD  \n",
       "1860321  0xe813C88FeC150A79d6b0c2512E0B71f71F5Fd829      STANDARD  \n",
       "\n",
       "[1860322 rows x 4 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "balances_pre.balance = balances_pre.balance.apply(lambda x: 0 if abs(x) < THRESHOLD  else x)\n",
    "balances_post.balance = balances_post.balance.apply(lambda x: 0 if abs(x) < THRESHOLD else x)\n",
    "balances_post"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "balance   \n",
       "50.000000     33496\n",
       "400.000000     8315\n",
       "400.000000      891\n",
       "50.000000       797\n",
       "50.000000       423\n",
       "              ...  \n",
       "159.946856        1\n",
       "160.000000        1\n",
       "160.027879        1\n",
       "160.228874        1\n",
       "158.871468        1\n",
       "Name: count, Length: 2913, dtype: int64"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ser = balances_post.groupby('xdai').first('balance').value_counts()\n",
    "ser "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "balances_pre = balances_pre.merge(\n",
    "    users[['xdai', 'role', 'area_type', 'area_name','svol_in', 'svol_out','stxns_in', 'stxns_out', 'sunique_in', 'sunique_out','initial_bal','final_bal','net','otxns_in', 'otxns_out','crid']],  # Ensure the right columns are selected\n",
    "    on='xdai',  # Use 'xdai' as the merge key in users\n",
    "    how='left'\n",
    ")\n",
    "balances_post = balances_post.merge(\n",
    "    users[['xdai', 'role', 'area_type', 'area_name','svol_in', 'svol_out','stxns_in', 'stxns_out', 'sunique_in', 'sunique_out','initial_bal','final_bal','net','otxns_in', 'otxns_out','crid']],  # Ensure the right columns are selected\n",
    "    on='xdai',  # Use 'xdai' as the merge key in users\n",
    "    how='left'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "FIRST round of validation "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On transactions sizes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "right_weight\n",
       "True    930161\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tx_with_bal['right_weight'] = np.isclose(tx_with_bal['weight'], abs(tx_with_bal['source_bal_pre'] - tx_with_bal['source_bal_post']),atol=1e-3)\n",
    "tx_with_bal.right_weight.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On reconstructed vs calculated initial conditions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of users: 54970\n",
      "Number of validated balances: 54967\n",
      "Number of non-matching balances: 3\n"
     ]
    }
   ],
   "source": [
    "first = balances_pre.groupby('xdai').first()\n",
    "\n",
    "first['validated'] = np.isclose(first.balance, first.initial_bal,atol = 1e-3)\n",
    "\n",
    "first[first.validated == True]\n",
    "\n",
    "# Optional: You can also count how many rows didn't validate\n",
    "print(f'Total number of users: {len(first)}')\n",
    "print(f\"Number of validated balances: {len(first[first.validated == True])}\")\n",
    "print(f\"Number of non-matching balances: {len(first[first.validated == False])}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>date</th>\n",
       "      <th>balance</th>\n",
       "      <th>type</th>\n",
       "      <th>role</th>\n",
       "      <th>area_type</th>\n",
       "      <th>area_name</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>initial_bal</th>\n",
       "      <th>final_bal</th>\n",
       "      <th>net</th>\n",
       "      <th>otxns_in</th>\n",
       "      <th>otxns_out</th>\n",
       "      <th>crid</th>\n",
       "      <th>validated</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xdai</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",
       "      <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>0x9EE844A0cf2DD827604FC68Facb6c0259aF24CAe</th>\n",
       "      <td>2021-06-14 21:16:02.142453</td>\n",
       "      <td>-50.000000</td>\n",
       "      <td>DISBURSEMENT</td>\n",
       "      <td>BENEFICIARY</td>\n",
       "      <td>other</td>\n",
       "      <td>other</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>54998</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0xBDB3Bc887C3b70586BC25D04d89eC802b897fC5F</th>\n",
       "      <td>2020-01-26 07:48:43.042684</td>\n",
       "      <td>43724689.392073</td>\n",
       "      <td>DISBURSEMENT</td>\n",
       "      <td>ADMIN</td>\n",
       "      <td>other</td>\n",
       "      <td>other</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>27939762.420000</td>\n",
       "      <td>13969881.210000</td>\n",
       "      <td>-13969881.210000</td>\n",
       "      <td>0</td>\n",
       "      <td>219126</td>\n",
       "      <td>8357</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F</th>\n",
       "      <td>2020-01-25 19:13:17.731529</td>\n",
       "      <td>-10330585.934101</td>\n",
       "      <td>DISBURSEMENT</td>\n",
       "      <td>ADMIN</td>\n",
       "      <td>other</td>\n",
       "      <td>other</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11730.000000</td>\n",
       "      <td>9000.000000</td>\n",
       "      <td>-2730.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>8356</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                  date  \\\n",
       "xdai                                                                     \n",
       "0x9EE844A0cf2DD827604FC68Facb6c0259aF24CAe  2021-06-14 21:16:02.142453   \n",
       "0xBDB3Bc887C3b70586BC25D04d89eC802b897fC5F  2020-01-26 07:48:43.042684   \n",
       "0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F  2020-01-25 19:13:17.731529   \n",
       "\n",
       "                                                    balance          type  \\\n",
       "xdai                                                                        \n",
       "0x9EE844A0cf2DD827604FC68Facb6c0259aF24CAe       -50.000000  DISBURSEMENT   \n",
       "0xBDB3Bc887C3b70586BC25D04d89eC802b897fC5F  43724689.392073  DISBURSEMENT   \n",
       "0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F -10330585.934101  DISBURSEMENT   \n",
       "\n",
       "                                                   role area_type area_name  \\\n",
       "xdai                                                                          \n",
       "0x9EE844A0cf2DD827604FC68Facb6c0259aF24CAe  BENEFICIARY     other     other   \n",
       "0xBDB3Bc887C3b70586BC25D04d89eC802b897fC5F        ADMIN     other     other   \n",
       "0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F        ADMIN     other     other   \n",
       "\n",
       "                                            svol_in  svol_out  stxns_in  \\\n",
       "xdai                                                                      \n",
       "0x9EE844A0cf2DD827604FC68Facb6c0259aF24CAe 0.000000 20.000000         0   \n",
       "0xBDB3Bc887C3b70586BC25D04d89eC802b897fC5F 0.000000  0.000000         0   \n",
       "0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F 0.000000  0.000000         0   \n",
       "\n",
       "                                            stxns_out  sunique_in  \\\n",
       "xdai                                                                \n",
       "0x9EE844A0cf2DD827604FC68Facb6c0259aF24CAe          1           0   \n",
       "0xBDB3Bc887C3b70586BC25D04d89eC802b897fC5F          0           0   \n",
       "0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F          0           0   \n",
       "\n",
       "                                            sunique_out     initial_bal  \\\n",
       "xdai                                                                      \n",
       "0x9EE844A0cf2DD827604FC68Facb6c0259aF24CAe            1        0.000000   \n",
       "0xBDB3Bc887C3b70586BC25D04d89eC802b897fC5F            0 27939762.420000   \n",
       "0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F            0    11730.000000   \n",
       "\n",
       "                                                 final_bal              net  \\\n",
       "xdai                                                                          \n",
       "0x9EE844A0cf2DD827604FC68Facb6c0259aF24CAe       30.000000        30.000000   \n",
       "0xBDB3Bc887C3b70586BC25D04d89eC802b897fC5F 13969881.210000 -13969881.210000   \n",
       "0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F     9000.000000     -2730.000000   \n",
       "\n",
       "                                            otxns_in  otxns_out   crid  \\\n",
       "xdai                                                                     \n",
       "0x9EE844A0cf2DD827604FC68Facb6c0259aF24CAe         1          0  54998   \n",
       "0xBDB3Bc887C3b70586BC25D04d89eC802b897fC5F         0     219126   8357   \n",
       "0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F         0          8   8356   \n",
       "\n",
       "                                            validated  \n",
       "xdai                                                   \n",
       "0x9EE844A0cf2DD827604FC68Facb6c0259aF24CAe      False  \n",
       "0xBDB3Bc887C3b70586BC25D04d89eC802b897fC5F      False  \n",
       "0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F      False  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first[first.validated == False]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looking for negative balances"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "neg_crid = list(set(tx_with_bal.loc[tx_with_bal.source_bal_pre < 0].source.unique()) | set(tx_with_bal.loc[tx_with_bal.source_bal_post < 0].source.unique()) | set(tx_with_bal.loc[tx_with_bal.target_bal_pre < 0].target.unique()) | set(tx_with_bal.loc[tx_with_bal.target_bal_post < 0].target.unique()))\n",
    "negs_xdai = list(set(tx_with_bal.loc[tx_with_bal.source_bal_pre < 0].source_xdai.unique()) | set(tx_with_bal.loc[tx_with_bal.source_bal_post < 0].source_xdai.unique()) | set(tx_with_bal.loc[tx_with_bal.target_bal_pre < 0].target_xdai.unique()) | set(tx_with_bal.loc[tx_with_bal.target_bal_post < 0].target_xdai.unique()))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2953"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(neg_crid)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Second round of integration for users with negative balance"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Assuming that accounts can't go below zero, we shift those balances by the minimum thay have ever reached "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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>crid</th>\n",
       "      <th>balance_x</th>\n",
       "      <th>balance_y</th>\n",
       "      <th>min_bal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>-161.567424</td>\n",
       "      <td>-161.567424</td>\n",
       "      <td>-161.567424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>-0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>-0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>-0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>24</td>\n",
       "      <td>-0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2948</th>\n",
       "      <td>48874</td>\n",
       "      <td>-33.000000</td>\n",
       "      <td>-33.000000</td>\n",
       "      <td>-33.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2949</th>\n",
       "      <td>50038</td>\n",
       "      <td>-10.000000</td>\n",
       "      <td>-10.000000</td>\n",
       "      <td>-10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2950</th>\n",
       "      <td>51306</td>\n",
       "      <td>-140.000000</td>\n",
       "      <td>-140.000000</td>\n",
       "      <td>-140.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2951</th>\n",
       "      <td>52530</td>\n",
       "      <td>-0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2952</th>\n",
       "      <td>54998</td>\n",
       "      <td>-50.000000</td>\n",
       "      <td>-20.000000</td>\n",
       "      <td>-50.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2953 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       crid   balance_x   balance_y     min_bal\n",
       "0         3 -161.567424 -161.567424 -161.567424\n",
       "1         6   -0.000000         NaN   -0.000000\n",
       "2         7   -0.000000         NaN   -0.000000\n",
       "3         8   -0.000000         NaN   -0.000000\n",
       "4        24   -0.000000         NaN   -0.000000\n",
       "...     ...         ...         ...         ...\n",
       "2948  48874  -33.000000  -33.000000  -33.000000\n",
       "2949  50038  -10.000000  -10.000000  -10.000000\n",
       "2950  51306 -140.000000 -140.000000 -140.000000\n",
       "2951  52530   -0.000000         NaN   -0.000000\n",
       "2952  54998  -50.000000  -20.000000  -50.000000\n",
       "\n",
       "[2953 rows x 4 columns]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_pre = balances_pre.groupby('crid').balance.min()\n",
    "grouped_pre = grouped_pre[grouped_pre.values<0]\n",
    "grouped_post = balances_post.groupby('crid').balance.min()\n",
    "grouped_post = grouped_post[grouped_post.values<0]\n",
    "merged = pd.merge(grouped_pre,grouped_post,on='crid',how='outer')\n",
    "\n",
    "merged['min_bal'] = merged[['balance_x','balance_y']].min(axis=1)\n",
    "\n",
    "merged.reset_index(inplace=True)\n",
    "merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "users = users.merge(merged[['crid','min_bal']], on='crid', how='left')\n",
    "users['min_bal'] = users['min_bal'].fillna(0)\n",
    "users['final_bal'] = users['final_bal'] - users.min_bal\n",
    "users['initial_bal'] = users['initial_bal'] - users.min_bal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "tx_with_bal= txns.copy(deep=True)\n",
    "previous_balance = {f'{address}': final_balance for address, final_balance in zip(users['crid'], users['final_bal'])}\n",
    "# Sort the DataFrame by date from newest to oldest\n",
    "# tx_with_bal= txns.copy(deep=True)\n",
    "tx_with_bal.sort_values(by='date', ascending=False, inplace=True)\n",
    "\n",
    "# Iterate over the sorted DataFrame\n",
    "source_bal_pre = []\n",
    "target_bal_pre = []\n",
    "source_bal_post = []\n",
    "target_bal_post = []\n",
    "\n",
    "for item in tx_with_bal.itertuples(index=True, name='Row'):\n",
    "    # Set post-transaction balances\n",
    "    source_bal_post.append(previous_balance[item.source])\n",
    "    target_bal_post.append(previous_balance[item.target])\n",
    "\n",
    "    # Calculate pre-transaction balances\n",
    "    source_bal_pre.append(previous_balance[item.source] + item.weight)\n",
    "    target_bal_pre.append(previous_balance[item.target] - item.weight)\n",
    "\n",
    "    # Update the balances in the dictionary\n",
    "    previous_balance[item.source] += item.weight\n",
    "    previous_balance[item.target] -= item.weight\n",
    "\n",
    "# Assign the computed lists back to the DataFrame\n",
    "tx_with_bal['source_bal_pre'] = source_bal_pre\n",
    "tx_with_bal['source_bal_post'] = source_bal_post\n",
    "tx_with_bal['target_bal_pre'] = target_bal_pre\n",
    "tx_with_bal['target_bal_post'] = target_bal_post\n",
    "\n",
    "tx_with_bal=tx_with_bal[['id', 'date', 'type', \n",
    "                                 'source_xdai', 'target_xdai', \n",
    "                                 'weight',\n",
    "                                 'source_bal_pre','source_bal_post', \n",
    "                                 'target_bal_pre', 'target_bal_post', \n",
    "                                 'source', 'target', \n",
    "                                 'source_role', 'target_role'\n",
    "       ]]\n",
    "pd.set_option('display.float_format', '{:.6f}'.format)\n",
    "tx_with_bal.sort_values(by='date', ascending=True, inplace=True)\n",
    "\n",
    "threshold = 1e-3\n",
    "\n",
    "# Apply the lambda function to all numeric columns\n",
    "tx_with_bal[tx_with_bal.select_dtypes(include='number').columns] = tx_with_bal.select_dtypes(include='number').apply(\n",
    "    lambda x: x.apply(lambda y: 0 if abs(y) < threshold else y)\n",
    ")# tx_with_bal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "right_weight\n",
       "True    930161\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tx_with_bal['right_weight'] = np.isclose(tx_with_bal['weight'], abs(tx_with_bal['source_bal_pre'] - tx_with_bal['source_bal_post']),atol=1e-3)\n",
    "tx_with_bal.right_weight.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "balances_pre = pd.DataFrame({\n",
    "    'date': tx_with_bal['date'].repeat(2).values,  # Repeat each time twice\n",
    "    'balance': tx_with_bal[['source_bal_pre', 'target_bal_pre']].values.flatten(),\n",
    "    'xdai': tx_with_bal[['source_xdai', 'target_xdai']].values.flatten(),  # Stack feature1 and feature2 in an alternating pattern\n",
    "    'type': tx_with_bal['type'].repeat(2).values\n",
    "})\n",
    "\n",
    "balances_post = pd.DataFrame({\n",
    "    'date': tx_with_bal['date'].repeat(2).values,  # Repeat each time twice\n",
    "    'balance': tx_with_bal[['source_bal_post', 'target_bal_post']].values.flatten(),\n",
    "    'xdai': tx_with_bal[['source_xdai', 'target_xdai']].values.flatten(),  # Stack feature1 and feature2 in an alternating pattern\n",
    "    'type': tx_with_bal['type'].repeat(2).values\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "balances_pre.balance = balances_pre.balance.apply(lambda x: 0 if abs(x) < 1e-3 else x)\n",
    "balances_post.balance = balances_post.balance.apply(lambda x: 0 if abs(x) < 1e-3 else x)\n",
    "# balances_post"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "balance   \n",
       "50.000000     34159\n",
       "400.000000     8340\n",
       "400.000000      901\n",
       "50.000000       842\n",
       "60.000000       409\n",
       "              ...  \n",
       "0.658059          1\n",
       "0.778245          1\n",
       "0.779390          1\n",
       "0.806952          1\n",
       "0.889802          1\n",
       "Name: count, Length: 2879, dtype: int64"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "balances_post.groupby('xdai').first('balance').value_counts()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "balances_pre = balances_pre.merge(\n",
    "    users[['xdai', 'role', 'area_type', 'area_name','svol_in', 'svol_out','stxns_in', 'stxns_out', 'sunique_in', 'sunique_out','initial_bal','final_bal','net','otxns_in', 'otxns_out','crid']],  # Ensure the right columns are selected\n",
    "    on='xdai',  # Use 'xdai' as the merge key in users\n",
    "    how='left'\n",
    ")\n",
    "balances_post = balances_post.merge(\n",
    "    users[['xdai', 'role', 'area_type', 'area_name','svol_in', 'svol_out','stxns_in', 'stxns_out', 'sunique_in', 'sunique_out','initial_bal','final_bal','net','otxns_in', 'otxns_out','crid']],  # Ensure the right columns are selected\n",
    "    on='xdai',  # Use 'xdai' as the merge key in users\n",
    "    how='left'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of users: 54970\n",
      "Number of validated balances: 54967\n",
      "Number of non-matching balances: 3\n"
     ]
    }
   ],
   "source": [
    "first = balances_pre.groupby('xdai').first()\n",
    "\n",
    "first['validated'] = np.isclose(first.balance, first.initial_bal,atol = 1e-3)\n",
    "\n",
    "first[first.validated == True]\n",
    "\n",
    "# Optional: You can also count how many rows didn't validate\n",
    "print(f'Total number of users: {len(first)}')\n",
    "print(f\"Number of validated balances: {len(first[first.validated == True])}\")\n",
    "print(f\"Number of non-matching balances: {len(first[first.validated == False])}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "not_validated = first[first.validated == False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "xdai\n",
       "0x9EE844A0cf2DD827604FC68Facb6c0259aF24CAe    54998\n",
       "0xBDB3Bc887C3b70586BC25D04d89eC802b897fC5F     8357\n",
       "0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F     8356\n",
       "Name: crid, dtype: int64"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "not_validated.crid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "negs = list(set(tx_with_bal.loc[tx_with_bal.source_bal_pre < 0].source.unique()) | set(tx_with_bal.loc[tx_with_bal.source_bal_post < 0].source.unique()) | set(tx_with_bal.loc[tx_with_bal.target_bal_pre < 0].target.unique()) | set(tx_with_bal.loc[tx_with_bal.target_bal_post < 0].target.unique()))\n",
    "\n",
    "negs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate spending and earning rates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "tx_with_bal['frac_out'] = (tx_with_bal['source_bal_pre'] / tx_with_bal.weight)**(-1)\n",
    "tx_with_bal['frac_in'] = (tx_with_bal['target_bal_pre'] / tx_with_bal.weight)**(-1)\n",
    "tx_with_bal['frac_out_post'] = (tx_with_bal['source_bal_post'] / tx_with_bal.weight)**(-1)\n",
    "tx_with_bal['frac_in_post'] = (tx_with_bal['target_bal_post'] / tx_with_bal.weight)**(-1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json \n",
    "\n",
    "dtype_mapping = {\n",
    "    'int64': 'int',\n",
    "    'float64': 'float',\n",
    "    'object': 'string',\n",
    "    'category': 'category'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': 'int',\n",
       " 'date': 'string',\n",
       " 'type': 'category',\n",
       " 'source_xdai': 'string',\n",
       " 'target_xdai': 'string',\n",
       " 'weight': 'float',\n",
       " 'source_bal_pre': 'float',\n",
       " 'source_bal_post': 'float',\n",
       " 'target_bal_pre': 'float',\n",
       " 'target_bal_post': 'float',\n",
       " 'source': 'string',\n",
       " 'target': 'string',\n",
       " 'source_role': 'category',\n",
       " 'target_role': 'category',\n",
       " 'right_weight': 'bool',\n",
       " 'frac_out': 'float',\n",
       " 'frac_in': 'float',\n",
       " 'frac_out_post': 'float',\n",
       " 'frac_in_post': 'float'}"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dtype_dict = {col: dtype_mapping.get(str(tx_with_bal[col].dtype), str(tx_with_bal[col].dtype)) for col in tx_with_bal.columns}\n",
    "\n",
    "\n",
    "dtype_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'date': 'string',\n",
       " 'balance': 'float',\n",
       " 'xdai': 'string',\n",
       " 'type': 'category',\n",
       " 'role': 'category',\n",
       " 'area_type': 'category',\n",
       " 'area_name': 'string',\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",
       " 'initial_bal': 'float',\n",
       " 'final_bal': 'float',\n",
       " 'net': 'float',\n",
       " 'otxns_in': 'int',\n",
       " 'otxns_out': 'int',\n",
       " 'crid': 'int'}"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dtype_dict = {col: dtype_mapping.get(str(balances_pre[col].dtype), str(balances_pre[col].dtype)) for col in balances_pre.columns}\n",
    "\n",
    "\n",
    "dtype_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': 'int',\n",
       " 'start': 'string',\n",
       " 'final_bal': 'float',\n",
       " 'gender': 'category',\n",
       " 'area_name': 'string',\n",
       " 'area_type': 'category',\n",
       " 'role': 'category',\n",
       " 'business': 'category',\n",
       " 'poa': 'string',\n",
       " 'xdai': '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',\n",
       " 'net': 'float',\n",
       " 'initial_bal': 'float',\n",
       " 'min_bal': 'float'}"
      ]
     },
     "execution_count": 89,
     "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",
    "\n",
    "dtype_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "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>role</th>\n",
       "      <th>business</th>\n",
       "      <th>poa</th>\n",
       "      <th>xdai</th>\n",
       "      <th>...</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",
       "      <th>net</th>\n",
       "      <th>initial_bal</th>\n",
       "      <th>min_bal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>2018-10-23 14:10:27</td>\n",
       "      <td>43389.850000</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>317816.300000</td>\n",
       "      <td>339096.000000</td>\n",
       "      <td>339</td>\n",
       "      <td>312</td>\n",
       "      <td>211</td>\n",
       "      <td>157</td>\n",
       "      <td>3</td>\n",
       "      <td>43228.282576</td>\n",
       "      <td>161.567424</td>\n",
       "      <td>-161.567424</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                start    final_bal gender       area_name area_type  \\\n",
       "3   5  2018-10-23 14:10:27 43389.850000   Male  Mukuru Nairobi     urban   \n",
       "\n",
       "          role business                                         poa  \\\n",
       "3  BENEFICIARY   labour  0x3700dd6c99f396c48e6658102ab2e18dc776d243   \n",
       "\n",
       "                                         xdai  ...       svol_in  \\\n",
       "3  0xD95954e3fCd2f09A6Be5931D24f731eFa63BF435  ... 317816.300000   \n",
       "\n",
       "       svol_out  stxns_in  stxns_out  sunique_in  sunique_out  crid  \\\n",
       "3 339096.000000       339        312         211          157     3   \n",
       "\n",
       "           net  initial_bal     min_bal  \n",
       "3 43228.282576   161.567424 -161.567424  \n",
       "\n",
       "[1 rows x 26 columns]"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users[users.crid == 3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "tx_with_bal.to_csv('/home/claudio/tesi/sarafu/final_data/00_tx_with_bal_bwd_allpositivebalances.csv')\n",
    "balances_post.to_csv('/home/claudio/tesi/sarafu/final_data/01_balances_post_bwd_allpositivebalances.csv')\n",
    "balances_pre.to_csv('/home/claudio/tesi/sarafu/final_data/02_balances_pre_bwd_allpositivebalances.csv')\n",
    "users.to_csv('/home/claudio/tesi/sarafu/final_data/03_users_adjusted_allpositivebalances.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'xdai,date,balance,type,role,area_type,area_name,svol_in,svol_out,stxns_in,stxns_out,sunique_in,sunique_out,initial_bal,final_bal,net,otxns_in,otxns_out,crid,validated\\n0x9EE844A0cf2DD827604FC68Facb6c0259aF24CAe,2021-06-14 21:16:02.142453,0.0,DISBURSEMENT,BENEFICIARY,other,other,0.0,20.0,0,1,0,1,50.0,80.0,30.0,1,0,54998,False\\n0xBDB3Bc887C3b70586BC25D04d89eC802b897fC5F,2020-01-26 07:48:43.042684,43724689.39207255,DISBURSEMENT,ADMIN,other,other,0.0,0.0,0,0,0,0,27939762.42,13969881.21,-13969881.21,0,219126,8357,False\\n0xEDA5C9B75Fdb3B9bdAB987A704632280Cf93084F,2020-01-25 19:13:17.731529,4721876.586896978,DISBURSEMENT,ADMIN,other,other,0.0,0.0,0,0,0,0,15064192.520999946,15061462.520999946,-2730.0,0,8,8356,False\\n'"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "not_validated.to_csv()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}