Balance of supply use and final demand and how to transform relative Industry by Industry table to absolute units [M.EUR]

exiobase_balance.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Paths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ixi_p = 'data/CREEA_database/version2.2.2_20141118/mrIOT_IxI_fpa_coefficient_version2.2.2/mrIot_version2.2.2.txt'\n",
    "sup_p = 'data/CREEA_database/version2.2.2_20141118/mrSUT_version2.2.2/mrSupply_version2.2.2.txt'\n",
    "use_p = 'data/CREEA_database/version2.2.2_20141118/mrSUT_version2.2.2/mrUse_version2.2.2.txt'\n",
    "fid_p = 'data/CREEA_database/version2.2.2_20141118/mrSUT_version2.2.2/mrFinalDemand_version2.2.2.txt'\n",
    "adv_p = 'data/CREEA_database/version2.2.2_20141118/mrSUT_version2.2.2/mrFactorInputs_version2.2.2.txt'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Industry by Industry (ixi): (7824, 7824)\n",
      "Supply (sup): (9600, 7824)\n",
      "Use (use): (9600, 7824)\n",
      "Final demand (fid): (9600, 336)\n",
      "Added value (adv): (19, 7824)\n",
      "CPU times: user 1min 25s, sys: 2.49 s, total: 1min 27s\n",
      "Wall time: 1min 27s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "ixi = pd.read_csv(ixi_p, sep='\\t', header=[0,1], index_col=[0,1,2])\n",
    "print('Industry by Industry (ixi): {}'.format(ixi.shape))\n",
    "\n",
    "sup = pd.read_csv(sup_p, sep='\\t', header=[0,1], index_col=[0,1,2])\n",
    "print('Supply (sup): {}'.format(sup.shape))\n",
    "\n",
    "use = pd.read_csv(use_p, sep='\\t', header=[0,1], index_col=[0,1,2])\n",
    "print('Use (use): {}'.format(use.shape))\n",
    "\n",
    "fid = pd.read_csv(fid_p, sep='\\t', header=[0,1], index_col=[0,1,2])\n",
    "print('Final demand (fid): {}'.format(fid.shape))\n",
    "\n",
    "adv = pd.read_csv(adv_p, sep='\\t', header=[0,1], index_col=[0,1])\n",
    "print('Added value (adv): {}'.format(adv.shape))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Balance\n",
    "$ supply = use + {final\\ demand} $"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "78017337.583 39991033.9473 38039346.3525\n"
     ]
    }
   ],
   "source": [
    "total_sup = sup.sum().sum()\n",
    "total_use = use.sum().sum()\n",
    "total_fid = fid.sum().sum()\n",
    "print(total_sup, total_use, total_fid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13042.716796457767"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "balance = total_use + total_fid - total_sup\n",
    "balance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.00016717715831539878"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "balance/total_sup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "balance is only very slightly off"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transform IOT from relative [M.EUR/M.EUR] to absolute [M.EUR]\n",
    "multiply the columns of th IxI table with the respective column total of the  supply table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ixi_abs = ixi.mul(sup.sum(axis=0))\n",
    "ixi_abs.index = ixi_abs.index.droplevel(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "39991031.947253019"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ixi_abs.sum().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "39991033.947260596"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_use"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-2.0000075772404671"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ixi_abs.sum().sum() - total_use"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "the total trade volume of the ixi table with absolute values now matches the total use"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda env:magit]",
   "language": "python",
   "name": "conda-env-magit-py"
  },
  "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": 0
}