eu-28_total.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlalchemy as sa"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn = sa.create_engine(\"mysql+pymysql://CMARCINIAK:ifpri360@localhost/comtrade?charset=utf8mb4\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "output_dir = \"C:/Dropbox (IFPRI)/EU-28_Imports/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "chapters = [\"06\", \"07\", \"08\", \"09\", \"11\", \"12\" ,\"13\", \"14\", \"15\", \"18\", \"20\", \"23\", \"24\", \"33\",\"44\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "years = [str(i) for i in range(1995,2016)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'make_query' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-15-60deab0de77d>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mmake_query\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m1995\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m\"06\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'make_query' is not defined"
     ]
    }
   ],
   "source": [
    "make_query(1995,\"06\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def make_query(year,number):\n",
    "    query = \"SELECT commodity_code,value,netweight_kg FROM comtradehs{0} where trade_flow_code = 1 and commodity_code like '{1}%%';\"\n",
    "    return query.format(str(year),str(number))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\n",
    "for year in years:\n",
    "    data = pd.DataFrame()\n",
    "    for chapter in chapters:\n",
    "        query = make_query(year,chapter)\n",
    "        table = pd.read_sql(query,conn)\n",
    "        table['netweight_MT'] = table.netweight_kg/1000\n",
    "        table['value (000 USD)'] = table.value/1000\n",
    "        total = table.groupby(\"commodity_code\",as_index=False).sum()\n",
    "        total['label'] =\"World Total\"\n",
    "        total['year'] = year\n",
    "        data = pd.concat([data,total])        \n",
    "    data.to_csv(output_dir+\"total/world_total_{0}.csv\".format(year))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "eu28 = [\"Austria\",\n",
    "\"Estonia\",\n",
    "\"Italy\",\n",
    "\"Portugal\",\n",
    "\"Belgium\", \n",
    "\"Finland\", \n",
    "\"Latvia\",\n",
    "\"Romania\", \n",
    "\"Bulgaria\",\n",
    "\"France\",\n",
    "\"Lithuania\",\n",
    "\"Slovakia\",\n",
    "\"Croatia\", \n",
    "\"Germany\",\n",
    "\"Luxembourg\",\n",
    "\"Slovenia\",\n",
    "\"Cyprus\",\n",
    "\"Greece\",\n",
    "\"Malta\",\n",
    "\"Spain\",\n",
    "\"Czech Republic\",\n",
    "\"Hungary\",\n",
    "\"Netherlands\",\n",
    "\"Sweden\",\n",
    "\"Denmark\",\n",
    "\"Ireland\",\n",
    "\"Poland\",\n",
    "\"United Kingdom\",\n",
    "\"Belgium-Luxembourg\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def import_query(year,country,chapter):\n",
    "    query = \"SELECT value,netweight_kg,partner_iso,year,commodity_code from comtradehs{0} where trade_flow_code=1 and reporter='{1}' and commodity_code like '{2}%%'\"\n",
    "    return query.format(year,country,chapter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"SELECT value,netweight_kg,partner_iso,year,commodity_code from comtradehs1995 where trade_flow_code=1 and reporter='Austria' and commodity_code like '06%%'\""
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import_query(\"1995\",eu28[0],\"06\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\n",
    "for year in years:\n",
    "    imports = pd.DataFrame()\n",
    "    for chapter in chapters:\n",
    "        for country in eu28:\n",
    "            query = import_query(year,country,chapter)\n",
    "            table = pd.read_sql(query,conn)\n",
    "            table[\"netweight_MT\"] = table.netweight_kg/1000\n",
    "            table[\"value (000 USD)\"] = table.value/1000\n",
    "            imports = pd.concat([imports,table])\n",
    "            out = imports.groupby([\"partner_iso\",\"commodity_code\",\"year\"],as_index=False).sum()\n",
    "            out['label'] = \"EU-28 Total\"\n",
    "    out.to_csv(output_dir+\"bilateral/eu28_imports_{0}.csv\".format(year))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "a = pd.read_csv(\"eu-28/total/world_total_1995.csv\")\n",
    "b = pd.read_csv(\"eu-28/bilateral/eu28_imports_1995.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "c = pd.concat([a,b])\n",
    "c.to_csv(\"eu_imports_1995.csv\")"
   ]
  },
  {
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}