aggregate_data_by_addresses.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime, timedelta"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load csv-file with txn and transform format 3 fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "myfile = 'transactions_2.csv'\n",
    "transactions = pd.read_csv(myfile, sep=';', header=0)\n",
    "transactions.loc[:,'blockTimestamp'] = pd.to_datetime(transactions['blockTimestamp'])\n",
    "transactions.loc[:,'gas'] = transactions['gas'].map(lambda x: int(x,16))\n",
    "transactions.loc[:,'gasPrice'] = transactions['gasPrice'].map(lambda x: int(x,16))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aggregate of data for addresses \"from\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.5/dist-packages/ipykernel_launcher.py:5: FutureWarning: using a dict on a Series for aggregation\n",
      "is deprecated and will be removed in a future version\n",
      "  \"\"\"\n",
      "/usr/local/lib/python3.5/dist-packages/ipykernel_launcher.py:10: FutureWarning: using a dict on a Series for aggregation\n",
      "is deprecated and will be removed in a future version\n",
      "  # Remove the CWD from sys.path while we load stuff.\n",
      "/usr/local/lib/python3.5/dist-packages/ipykernel_launcher.py:15: FutureWarning: using a dict on a Series for aggregation\n",
      "is deprecated and will be removed in a future version\n",
      "  from ipykernel import kernelapp as app\n",
      "/usr/local/lib/python3.5/dist-packages/ipykernel_launcher.py:21: FutureWarning: using a dict on a Series for aggregation\n",
      "is deprecated and will be removed in a future version\n"
     ]
    }
   ],
   "source": [
    "address_from_blockTimestamp = transactions.groupby('from')['blockTimestamp'].agg({'count':np.count_nonzero, \n",
    "                                                                                  'delta_timestamp': lambda x: \n",
    "                                                                                       (np.max(x) - np.min(x)).total_seconds(),\n",
    "                                                                                 'min_timestamp':np.min,\n",
    "                                                                                 'max_timestamp':np.max}).reset_index()\n",
    "\n",
    "address_from_value = transactions.groupby('from')['value'].agg({'count_value_non_zero':np.count_nonzero, \n",
    "                                                                'mean_value':np.mean, \n",
    "                                                                'max_value':np.max, \n",
    "                                                                'sum_value':np.sum}).reset_index()\n",
    "\n",
    "address_from_gas = transactions.groupby('from')['gas'].agg({'count_non_zero_gas':np.count_nonzero, \n",
    "                                                            'min_gas':np.min, 'mean_gas':np.mean, \n",
    "                                                            'max_gas':np.max, \n",
    "                                                            'sum_gas':np.sum}).reset_index()\n",
    "\n",
    "address_from_gasPrice = transactions.groupby('from')['gasPrice'].agg({'count_non_zero_gasPrise':np.count_nonzero, \n",
    "                                                                      'min_gasPrice':np.min, \n",
    "                                                                      'mean_gasPrice':np.mean, \n",
    "                                                                      'max_gasPrice':np.max, \n",
    "                                                                      'sum_gasPrice':np.sum}).reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aggregate of data for addresses \"to\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.5/dist-packages/ipykernel_launcher.py:2: FutureWarning: using a dict on a Series for aggregation\n",
      "is deprecated and will be removed in a future version\n",
      "  \n",
      "/usr/local/lib/python3.5/dist-packages/ipykernel_launcher.py:8: FutureWarning: using a dict on a Series for aggregation\n",
      "is deprecated and will be removed in a future version\n",
      "  \n",
      "/usr/local/lib/python3.5/dist-packages/ipykernel_launcher.py:14: FutureWarning: using a dict on a Series for aggregation\n",
      "is deprecated and will be removed in a future version\n",
      "  \n",
      "/usr/local/lib/python3.5/dist-packages/ipykernel_launcher.py:20: FutureWarning: using a dict on a Series for aggregation\n",
      "is deprecated and will be removed in a future version\n",
      "/usr/local/lib/python3.5/dist-packages/ipykernel_launcher.py:22: FutureWarning: using a dict on a Series for aggregation\n",
      "is deprecated and will be removed in a future version\n"
     ]
    }
   ],
   "source": [
    "address_to_blockTimestamp = transactions.groupby('to')['blockTimestamp'].agg({'count':np.count_nonzero, \n",
    "                                                                              'delta_timestamp': lambda x: \n",
    "                                                                                       (np.max(x) - np.min(x)).total_seconds(),\n",
    "                                                                              'min_timestamp':np.min,\n",
    "                                                                              'max_timestamp':np.max}).reset_index()\n",
    "\n",
    "address_to_value = transactions.groupby('to')['value'].agg({'count_value_non_zero':np.count_nonzero, \n",
    "                                                            'mean_value':np.mean, \n",
    "                                                            'max_value':np.max, \n",
    "                                                            'sum_value':np.sum}).reset_index()\n",
    "\n",
    "address_to_gas = transactions.groupby('to')['gas'].agg({'count_non_zero_gas':np.count_nonzero, \n",
    "                                                        'min_gas':np.min, \n",
    "                                                        'mean_gas':np.mean, \n",
    "                                                        'max_gas':np.max, \n",
    "                                                        'sum_gas':np.sum}).reset_index()\n",
    "\n",
    "address_to_gasPrice = transactions.groupby('to')['gasPrice'].agg({'count_non_zero_gasPrise':np.count_nonzero, \n",
    "                                                                  'min_gasPrice':np.min, \n",
    "                                                                  'mean_gasPrice':np.mean, \n",
    "                                                                  'max_gasPrice':np.max, \n",
    "                                                                  'sum_gasPrice':np.sum}).reset_index()\n",
    "\n",
    "address_is_contract = transactions.groupby('to')['input'].agg({'is_contract': lambda x: \n",
    "                                                               False if len(np.unique(x))>1 \n",
    "                                                                         or np.unique(x)=='0x' else True}).reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merging aggregated DataFrames to one.  <br>\n",
    "You may uncoment rows for <b>outer-join</b>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "address_from = pd.merge(pd.merge(pd.merge(address_from_blockTimestamp, \n",
    "                                              address_from_value,\n",
    "                                              on='from'),\n",
    "                                     address_from_gas,\n",
    "                                     on='from'),\n",
    "                             address_from_gasPrice,\n",
    "                             on='from')\n",
    "\n",
    "address_to = pd.merge(pd.merge(pd.merge(pd.merge(address_to_blockTimestamp, \n",
    "                                                      address_to_value,\n",
    "                                                      on='to'),\n",
    "                                             address_is_contract,\n",
    "                                             on='to'),\n",
    "                                     address_to_gas,\n",
    "                                     on='to'),\n",
    "                             address_to_gasPrice,\n",
    "                             on='to')\n",
    "\n",
    "addresses_analysis = pd.merge(address_from, \n",
    "                                 address_to,\n",
    "#                                  how='outer', \n",
    "                                 left_on='from', \n",
    "                                 right_on='to', \n",
    "                                 suffixes=('_from', '_to'))\n",
    "\n",
    "# addresses_to_analysis.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculating of address field"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def address_function(x):\n",
    "    return x['from'] if  x['from']==x['from'] else x['to']\n",
    "\n",
    "addresses_analysis.loc[:,'address'] = addresses_analysis.apply(address_function, axis=1)\n",
    "addresses_analysis.drop(['from','to'], inplace=True, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "addresses_analysis.to_csv('addresses_analysis.csv')\n",
    "# address_to.to_csv('addresses_to.csv')\n",
    "# address_from.to_csv('addresses_from.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'address_to_blockTimestamp' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-5-14b165ac4925>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0maddress_to_blockTimestamp\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'address_to_blockTimestamp' is not defined"
     ]
    }
   ],
   "source": [
    "address_to_blockTimestamp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}