{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Project 4: Exploring the UK's milk imports and exports\n", "\n", "by Tony Hirst and Michel Wermelinger, 19 November 2015, 28 March 2016, 18 October 2017, minor edit 20 December 2017\n", "\n", "This is the project notebook for Part 4 of The Open University's _Learn to Code for Data Analysis_ course." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "A country's economy depends, sometimes heavily, on its exports and imports. The United Nations Comtrade database provides data on global trade. It will be used to analyse the UK's imports and exports of milk and cream in 2015:\n", "\n", "- How much does the UK export and import and is the balance positive (more exports than imports)? \n", "- Which are the main trading partners, i.e. from/to which countries does the UK import/export the most?\n", "- Which are the regular customers, i.e. which countries buy milk from the UK every month?\n", "- Which countries does the UK both import from and export to?" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "activity": false, "collapsed": true }, "outputs": [], "source": [ "import warnings\n", "warnings.simplefilter('ignore', FutureWarning)\n", "\n", "from pandas import *\n", "%matplotlib inline" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Getting and preparing the data\n", "\n", "The data is obtained from the [United Nations Comtrade](http://comtrade.un.org/data/) website, by selecting the following configuration:\n", "\n", "- Type of Product: goods\n", "- Frequency: monthly \n", "- Periods: January to May of 2015\n", "- Reporter: United Kingdom\n", "- Partners: all\n", "- Flows: imports and exports\n", "- HS (as reported) commodity codes: 0401 (Milk and cream, neither concentrated nor sweetened) and 0402 (Milk and cream, concentrated or sweetened)\n", "\n", "Clicking on 'Preview' results in a message that the data exceeds 500 rows. Data was downloaded using the *Download CSV* button and the download file renamed appropriately." ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": true }, "outputs": [], "source": [ "LOCATION = 'comtrade_milk_uk_jan_jul_15.csv'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The data can also be downloaded directly from Comtrade using the \"View API Call\" URL, modified in two ways:\n", "\n", "- `max=500` is increased to `max=5000` to make sure all data is loaded,\n", "- `&fmt=csv` is added at the end to obtain the data in CSV format." ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": true }, "outputs": [], "source": [ "# LOCATION = 'http://comtrade.un.org/api/get?max=5000&type=C&freq=M&px=HS&ps=201505%2C201504%2C201503%2C201502%2C201501&r=826&p=all&rg=1%2C2&cc=0401%2C0402&fmt=csv'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "On reading in the data, the commodity code has to be read as a string, to not lose the leading zero." ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "activity": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ClassificationYearPeriodPeriod Desc.Aggregate LevelIs Leaf CodeTrade Flow CodeTrade FlowReporter CodeReporter...QtyAlt Qty Unit CodeAlt Qty UnitAlt QtyNetweight (kg)Gross weight (kg)Trade Value (US$)CIF Trade Value (US$)FOB Trade Value (US$)Flag
635HS2015201505May 2015402Exports826United Kingdom...NaNNaNNaNNaN2213NaN37883NaNNaN0
636HS2015201505May 2015402Exports826United Kingdom...NaNNaNNaNNaN1588NaN5676NaNNaN0
\n", "

2 rows × 35 columns

\n", "
" ], "text/plain": [ " Classification Year Period Period Desc. Aggregate Level Is Leaf Code \\\n", "635 HS 2015 201505 May 2015 4 0 \n", "636 HS 2015 201505 May 2015 4 0 \n", "\n", " Trade Flow Code Trade Flow Reporter Code Reporter ... Qty \\\n", "635 2 Exports 826 United Kingdom ... NaN \n", "636 2 Exports 826 United Kingdom ... NaN \n", "\n", " Alt Qty Unit Code Alt Qty Unit Alt Qty Netweight (kg) \\\n", "635 NaN NaN NaN 2213 \n", "636 NaN NaN NaN 1588 \n", "\n", " Gross weight (kg) Trade Value (US$) CIF Trade Value (US$) \\\n", "635 NaN 37883 NaN \n", "636 NaN 5676 NaN \n", "\n", " FOB Trade Value (US$) Flag \n", "635 NaN 0 \n", "636 NaN 0 \n", "\n", "[2 rows x 35 columns]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "milk = read_csv(LOCATION, dtype={'Commodity Code':str})\n", "milk.tail(2)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The data only covers the first five months of 2015. Most columns are irrelevant for this analysis, or contain always the same value, like the year and reporter columns. The commodity code is transformed into a short but descriptive text and only the relevant columns are selected." ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "activity": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PeriodPartnerTrade FlowMilk and creamTrade Value (US$)
0201501WorldImportsunprocessed14104602
1201501WorldExportsunprocessed26259792
2201501AustraliaExportsunprocessed50331
3201501AustriaExportsunprocessed360
4201501BelgiumImportsunprocessed1424271
\n", "
" ], "text/plain": [ " Period Partner Trade Flow Milk and cream Trade Value (US$)\n", "0 201501 World Imports unprocessed 14104602\n", "1 201501 World Exports unprocessed 26259792\n", "2 201501 Australia Exports unprocessed 50331\n", "3 201501 Austria Exports unprocessed 360\n", "4 201501 Belgium Imports unprocessed 1424271" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def milkType(code):\n", " if code == '0401': # neither concentrated nor sweetened\n", " return 'unprocessed'\n", " if code == '0402': # concentrated or sweetened\n", " return 'processed' \n", " return 'unknown'\n", "\n", "COMMODITY = 'Milk and cream'\n", "milk[COMMODITY] = milk['Commodity Code'].apply(milkType)\n", "MONTH = 'Period'\n", "PARTNER = 'Partner'\n", "FLOW = 'Trade Flow'\n", "VALUE = 'Trade Value (US$)'\n", "headings = [MONTH, PARTNER, FLOW, COMMODITY, VALUE]\n", "milk = milk[headings]\n", "milk.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The data contains the total imports and exports per month, under the 'World' partner. Those rows are removed to keep only the per-country data." ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "activity": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PeriodPartnerTrade FlowMilk and creamTrade Value (US$)
2201501AustraliaExportsunprocessed50331
3201501AustriaExportsunprocessed360
4201501BelgiumImportsunprocessed1424271
5201501BelgiumExportsunprocessed996031
6201501BulgariaExportsunprocessed191
\n", "
" ], "text/plain": [ " Period Partner Trade Flow Milk and cream Trade Value (US$)\n", "2 201501 Australia Exports unprocessed 50331\n", "3 201501 Austria Exports unprocessed 360\n", "4 201501 Belgium Imports unprocessed 1424271\n", "5 201501 Belgium Exports unprocessed 996031\n", "6 201501 Bulgaria Exports unprocessed 191" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "milk = milk[milk[PARTNER] != 'World']\n", "milk.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Total trade flow\n", "\n", "To answer the first question, 'how much does the UK export and import and is the balance positive (more exports than imports)?',\n", "the dataframe is split into two groups: exports from the UK and imports into the UK. The trade values within each group are summed up to get the total trading." ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "activity": false }, "outputs": [ { "data": { "text/plain": [ "Trade Flow\n", "Exports 265029661\n", "Imports 156483978\n", "Name: Trade Value (US$), dtype: int64" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "grouped = milk.groupby([FLOW])\n", "grouped[VALUE].aggregate(sum)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This shows a trade surplus of over 100 million dollars." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Main trade partners\n", "\n", "To address the second question, 'Which are the main trading partners, i.e. from/to which countries does the UK import/export the most?', the dataframe is split by country instead, and then each group aggregated for the total trade value. This is done separately for imports and exports. The result is sorted in descending order so that the main partners are at the top." ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "activity": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The UK imports from 21 countries.\n", "The 5 biggest exporters to the UK are:\n" ] }, { "data": { "text/plain": [ "Partner\n", "Ireland 46263897\n", "France 28314091\n", "Germany 21899123\n", "Netherlands 17658912\n", "Belgium 14325697\n", "Name: Trade Value (US$), dtype: int64" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "imports = milk[milk[FLOW] == 'Imports']\n", "grouped = imports.groupby([PARTNER])\n", "print('The UK imports from', len(grouped), 'countries.')\n", "print('The 5 biggest exporters to the UK are:')\n", "totalImports = grouped[VALUE].aggregate(sum).sort_values(inplace=False,ascending=False)\n", "totalImports.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The export values can be plotted as a bar chart, making differences between countries easier to see." ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" }, { "data": { "image/png": "iVBORw0KGgoAAAANSUhEUgAAAbIAAAEJCAYAAADxfqKqAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAHctJREFUeJzt3XuUHWWd7vHvQ4AEiDQeEzg9gUWjtFw0EkiI3MSAHtSR\nERFmgQOzoiBRFwrODDqZcdYMHmZEx6WGIxeJgFwOoOIFuZwDAQRBDJBuCGkC6Hi4CPGSYZRAuATo\nPOePqpZNpzu9O+m9d9fO81mrV9d+662q394L+slbVfst2SYiIqKqNmt1ARERERsjQRYREZWWIIuI\niEpLkEVERKUlyCIiotISZBERUWkJsoiIqLQEWUREVFqCLCIiKm3zVhewKZgyZYq7urpaXUZERKX0\n9vY+ZXvqSP0SZE3Q1dVFT09Pq8uIiKgUSY/X0y+nFiMiotISZBERUWkJsoiIqLQEWUREVFpu9miC\nvhWr6Jp//ZDrHvvS+5tcTUREe8mILCIiKm1cB5mkfklLJT0g6VpJ27Wwlp+36tgRETG8cR1kwAu2\nZ9h+K/AH4ORWFWL7gFYdOyIihjfeg6zWYmAagApfKUdqfZKOKdvnSPqppO9J+qWkL0k6TtI9Zb83\nlf3+QtLdku6TdLOkHcr20yVdJOk2SY9IOmXg4JJWl78nS7pF0r3lPo9o+icRERF/UombPSRNAN4F\nXFg2fQiYAewFTAGWSLq9XLcXsAfFCO4R4ALbsyWdCnwa+AzwM2A/25b0MeBzwN+V2+8OHAK8DviF\npPNsv1xTzovAkbafkTQFuEvSNbY9qOZ5wDyACduOOMNKRERsoPEeZFtJWgp0Ab3ATWX7QcCVtvuB\n30v6KbAv8AywxPZvAST9P2BRuU0fRUAB7Ah8V1InsCXwaM0xr7e9BlgjaSWwA/BkzXoBX5R0MLCW\nYpS4A/C72sJtLwQWAkzs7H5NyEVExNgZ76cWX7A9A9iZInAGrpFpPdusqVleW/N6La8G9zeAs21P\nBz4OTBpm+37WDfvjgKnAzLK23w/aPiIimmi8BxkAtlcBpwCnSdoCuB04RtIESVOBg4F7RrHLDmBF\nuTx3lOV0ACttvyzpEIqQjYiIFqlEkAHYvg+4HzgW+BGwrHz9E+Bztn+3ns0HOx24StIdwFOjLOVy\nYJakHorR2cOj3D4iIsaQBt2jEA0wsbPbnXMXDLkuM3tERAxNUq/tWSP1G+83e7SF6dM66ElgRUQ0\nRGVOLUZERAwlQRYREZWWIIuIiEpLkEVERKUlyCIiotISZBERUWkJsoiIqLQEWUREVFqCLCIiKi1B\nFhERlZYpqpqgb8UquuZfP+rtMg9jRMTIKjkik/R5ScslLZO0VNLbx3Dfq8dqXxER0XiVG5FJ2h84\nHNjH9hpJUygeuhkREZugKo7IOoGnbK8BsP0UsKOkHwJIOkLSC5K2lDRJ0iNl+5sk3SCpV9IdknYv\n23eRtFjSEkln1B5I0mfL9mWSvlC2dUl6SNK3ylHhIklbNfMDiIiIV1UxyBYBO0n6paRzJb0TuBfY\nu1z/DuABYF/g7cDdZftC4NO2ZwKnAeeW7WcB59neF/jTwzklHQZ0A7OBGcBMSQeXq7uBc2y/BXga\nOKoh7zQiIkZUuVOLtldLmkkRWIcA3wXmA7+StAdF8HwNOBiYANwhaTJwAMVToQd2NbH8fSCvBtFl\nwJfL5cPKn/vK15MpAuzXwKO2l5btvUDX4DolzQPmAUzYdupGveeIiBhe5YIMwHY/cBtwm6Q+YC5w\nB/A+4GXgZuBiiiA7jWLk+bTtGcPtcog2AWfaPv81jVIXsKamqR9Y59Si7YUUo0AmdnbnMdwREQ1S\nuVOLknaT1F3TNAN4HLgd+Ayw2PZ/Am8AdgeW234GeFTSX5b7kKS9yu3vBI4tl4+r2e+NwAnlaA5J\n0yRt36j3FRERG6aKI7LJwDckbQe8AvyK4hTec8AOFIEGsAxYaXtgNHQccJ6kfwK2AL4D3A+cClwh\n6VTgBwMHsb2oPFW5uDwduRo4nmIEFhER44Re/TsfjTKxs9udcxeMert8IToiNmWSem3PGqlfFUdk\nlTN9Wgc9CaWIiIao3DWyiIiIWgmyiIiotARZRERUWoIsIiIqLUEWERGVliCLiIhKS5BFRESlJcgi\nIqLSEmQREVFpCbKIiKi0TFHVBH0rVtE1//pWl5G5GyOiLWVEFhERldb2QSbp85KWS1omaamkt2/A\nPj4gaX4j6ouIiI3T1qcWJe0PHA7sY3uNpCnAlqPdj+1rgGvGur6IiNh47T4i6wSesr0GwPZTtn8j\n6TFJX5Z0T/mzK4Ckv5B0t6T7JN0saYey/SOSzi6XL5b0vyT9XNIjko5u2buLiIi2D7JFwE6Sfinp\nXEnvrFn3jO3ZwNnAwFMvfwbsZ3tviidIf26Y/XYCB1GM9r7UmNIjIqIebX1q0fZqSTOBdwCHAN+t\nudZ1Zc3vr5fLO5Z9OilOQT46zK6vtr0WeHBg1DaYpHnAPIAJ207d6PcSERFDa/cRGbb7bd9m+1+A\nTwFHDayq7Vb+/gZwtu3pwMeBScPsdk3NsoY57kLbs2zPmrB1x4a/gYiIWK+2DjJJu0nqrmmaATxe\nLh9T83txudwBrCiX5za+woiI2FhtfWoRmAx8Q9J2wCvAryhO9x0OTJR0N0WYf7jsfzpwlaQVwF3A\nLk2vOCIiRkW2R+7VZiQ9Bsyy/VQzjjexs9udcxeM3LHBMrNHRFSJpF7bs0bq1+4jsnFh+rQOehIi\nERENsUkGme2uVtcQERFjo61v9oiIiPaXIIuIiEpLkEVERKUlyCIiotISZBERUWkJsoiIqLQEWURE\nVFqCLCIiKi1BFhERlbZJzuzRbH0rVtE1//pWl5G5FiOiLWVEFhERldY2QSapX9JSSQ9IukrS1iP0\nXz1Gx+2S9MBY7CsiIkavbYIMeMH2DNtvBV4CPtHqgiIiovHaKchq3QHsCiDpb8tR2gOSPjO4o6TJ\nkm6RdK+kPklHlO1dkh6S9C1JyyUtkrRVuW6mpPslLQZObuYbi4iI12q7IJO0OfA+oE/STOCjwNuB\n/YCTJO09aJMXgSNt7wMcAnxVksp13cA5tt8CPA0cVbZ/GzjF9v7rqWOepB5JPf3PrxqrtxcREYO0\nU5BtJWkp0AP8GrgQOAj4ke3nbK8Gfgi8Y9B2Ar4oaRlwMzAN2KFc96jtpeVyL9AlqQPYzvZPy/bL\nhirG9kLbs2zPmrB1xxi9xYiIGKydbr9/wfaM2oaakdX6HAdMBWbaflnSY8Ckct2amn79wFYUweeN\nLzciIsZCO43IhnI78EFJW0vaBjiS4vpZrQ5gZRlihwA7r2+Htp8GVkk6qGw6bqyLjoiI+rXTiGwd\ntu+VdDFwT9l0ge37BnW7HLhWUg+wFHi4jl1/FLhI0vPAjWNVb0REjJ7snCVrtImd3e6cu6DVZWRm\nj4ioFEm9tmeN1K+tR2TjxfRpHfQkRCIiGqLdr5FFRESbS5BFRESlJcgiIqLSEmQREVFpCbKIiKi0\nBFlERFRagiwiIiotQRYREZWWIIuIiEpLkEVERKWNOEWVpAnAJbaPb0I9balvxSq65l/f6jI2SZlf\nMqL9jTgis90PTJW0ZRPqiYiIGJV6Jw1+DLhT0jXAcwONtr/WiKIk9QN9wBbAK8AlwALbaxtxvNGS\ntNr25FbXERER9QfZb8qfzYDXNa6cP/nT054lbQ9cQfEAzH9pwrGHVT5xup6nTkdERJPUFWS2vwAg\naRvbz43UfyzZXilpHrBE0ukUYfolYA4wETjH9vmS5gCnA08BbwV6geNtW9JjFGF4CMUobx5wJrAr\n8BXb35Q0Gfgx8Pqyzz/Z/rGkLuD/ArcC+wMfHKhN0hTgWuBfbeciWEREC9R116Kk/SU9CDxUvt5L\n0rkNrayG7Ucoat0eOBFYZXtfYF/gJEm7lF33Bj4D7Am8ETiwZjdP2N4fuAO4GDga2A/4n+X6F4Ej\nbe9DEXhfLUdgALsBl9re2/bjAJJ2AK4H/nmoEJM0T1KPpJ7+51eNxccQERFDqPfU4gLgPcA1ALbv\nl3Rww6oa2kCoHAa8TdLR5esOoBt4CbjH9pMAkpYCXcDPyn7XlL/7gMm2nwWelfSipO0orv19sXxf\na4FpwA7lNo/bvqumli2AW4CTbf90qGJtLwQWQvGE6A1+1xERsV51PyHa9hOvDlAA6B/7coYm6Y3l\n8VZSBNqnbd84qM8cYM2g+mrf38C6tYP6rS37HQdMBWbafrk8HTmp7DP4dOorFKcu3wMMGWQREdEc\n9X4h+glJBwCWtKWk0yhPMzaapKnAN4GzbRu4EfikpC3K9W+WtM0YHKoDWFmG2CHAzuvpa+AEYHdJ\n88fg2BERsYHqHZF9AjiL4nTbk8Ai4ORGFQVsVZ4aHLj9/jJg4Fb/CyhOGd5bXsP6T2puwNgIlwPX\nSuoBlgIPr6+z7X5Jx5bbPGO7adcMIyLiVSoGOdFIEzu73Tl3QavL2CRlZo+I6pLUa3vWSP3qGpGV\np/dOohgJ/Wkb2ydsaIGbkunTOujJH9SIiIao99TijyluW7+ZJt7kERERMZJ6g2xr23/f0EoiIiI2\nQL13LV4n6c8bWklERMQGqDfITqUIsxckPSPpWUnPNLKwiIiIetQ712IzJgqOiIgYtXrnWrylnraI\niIhmW++ITNIkYGtgiqTX8+p8h9sCf9bg2iIiIkY00qnFj1PMJv9nFHMLDgTZM8A5DawrIiKiLusN\nMttnSTob+EfbZzSppoiIiLqNeI3Mdj+QW+8jImJcqvcL0YskHQX80JmccdT6Vqyia34eID0eZO7F\niPZT7/fI/ha4CljTqu+RSeqXtFTS/ZLuLR8rM9I2q+voc4GkPcemyoiIaLYqfY/sBdszACS9BzgT\neOfG7tT2xzZ2HxER0Tr1jsiQ9HpJsyUdPPDTyMJGsC3wx5raPitpiaRlkr4wuLOkzSSdK2m5pOsk\n/R9JR5frbpM0q1xeXbPN0ZIuLpcvlnSepFslPSLpnZIukvTQQJ+IiGiNeh/j8jGKaap2pHjo5H7A\nYuDQxpW2joGHbU4COgeOLekwoBuYTfH1gGskHWz79pptP0TxCJrpwPYUT7e+aJTHf315zA8A1wIH\nAh8DlkiaYXvpBr6viIjYCKOZa3Ff4HHbhwB7UzyZuZlesD3D9u7Ae4FLyydEH1b+3AfcC+xOEWy1\nDgKusr3W9u+AWzfg+NeWN7r0Ab+33Wd7LbCcIiRfQ9I8ST2SevqfX7UBh4uIiHrUe9fii7ZflISk\nibYflrRbQytbD9uLJU0BplKMws60ff56NtF61r1m1zXLkwatW1P+XluzPPB6nc/R9kJgIRRPiK7z\n+BERMUr1jsielLQdcDVwk6QfA79pXFnrJ2l3YALwX8CNwAmSJpfrpknaftAmPwOOKq+V7QDMGWbX\nv5e0h6TNgCMbU31ERIyleu9aHPijfrqkW4EO4IaGVTW0gWtkUIyw5pZf1l4kaQ9gcXGmkdXA8cDK\nmm1/ALwLeAD4JXA3MNT5vvnAdcATZd/JDXgfERExhrS+7zeXkwZ/AtiV4trQhbZfaVJtY0rSZNur\nJb0BuAc4sLxe1nATO7vdOXdBMw4VI8gXoiOqQ1Kv7Vkj9RtpRHYJ8DJwB/A+YE+KGz+q6Lry9OiW\nwBnNCjGA6dM66Mkf0IiIhhgpyPa0PR1A0oUUI5lKsj2n1TVERMTYG+lmj5cHFqp6SjEiItrbSCOy\nvWrmVBTFDRfPlMu2vW1Dq4uIiBjBSM8jm9CsQiIiIjZE3XMtRkREjEcJsoiIqLQEWUREVFqCLCIi\nKi1BFhERlZYgi4iISqv3MS6xEfpWrKJr/vWtLiMaLPM4RrRGRmQREVFpDQsySZb01ZrXp0k6fYRt\n5kg6oOb1xZKO3sg6HisfwrnRJK0ei/1ERMTYaeSIbA3woVGGyBzggJE61UOFjDgjItpcI//QvwIs\nBP5m8ApJUyX9QNKS8udASV0Uzz77G0lLJb2j7H6wpJ9LeqR2dCbps+W2yyR9oWzrkvSQpHOBe4Gd\nBh33akm9kpZLmlfTvlrSv0m6X9Jd5VOkkbSLpMXlcc6o6d8p6fayzgdqao2IiCZr9IjlHOA4SR2D\n2s8Cvm57X+Ao4ALbjwHfLNtn2L6j7NsJHAQcDnwJQNJhQDcwG5gBzJR0cNl/N+BS23vbfnzQcU+w\nPROYBZxSPmQTYBvgLtt7AbcDJ9XUeV5ZZ+3zy/4KuNH2DGAvYCkREdESDb1r0fYzki4FTgFeqFn1\nbmBPSQOvt5X0umF2c7XttcCDAyMl4LDy577y9WSKYPs18Ljtu4bZ1ymSjiyXdyq3+S/gJeC6sr0X\n+B/l8oEUQQtwGfDlcnkJcJGkLcr61gmycsQ3D2DCtlOHKSciIjZWM26/X0Bxmu/bNW2bAfvbrg03\naoKt1praLjW/z7R9/qDtu4DnhtqJpDkUAbq/7ecl3QZMKle/bNvlcj+v/VzMILZvL0eA7wcuk/QV\n25cO6rOQ4tQqEzu719lHRESMjYbfDGH7D8D3gBNrmhcBnxp4IWlGufgsMNzIrNaNwAmSJpfbT5O0\n/QjbdAB/LENsd2C/Oo5zJ3BsuXxcTb07Ayttfwu4ENinjn1FREQDNOuuvq8CtXcvngLMKm/UeJDi\nJg+Aa4EjB93ssQ7bi4ArgMWS+oDvM3IA3gBsLmkZcAYw3OnHWqcCJ0taQhGEA+YASyXdR3Hq8aw6\n9hUREQ2gV8+oRaNM7Ox259wFrS4jGiwze0SMLUm9tmeN1C9TVDXB9Gkd9OSPXEREQ+QLwxERUWkJ\nsoiIqLQEWUREVFqCLCIiKi1BFhERlZYgi4iISkuQRUREpSXIIiKi0hJkERFRaQmyiIiotExR1QR9\nK1bRNf/6VpcRbSjzO0ZkRBYRERU3boNM0g6SrpD0iKReSYtrnu4cEREBjNMgU/Go6KuB222/0fZM\nigdc7ljn9hMaWV9ERIwf4zLIgEOBl2x/c6DB9uO2vyFpgqSvSFpSPpjz4wCS5ki6VdIVQJ+kLkkP\nS7pA0gOSLpf0bkl3SvoPSbPL7WZL+rmk+8rfu5XtH5H0Q0k3lP3/vWw/UdLXB+qSdJKkrzXzw4mI\niFeN1yB7C3DvMOtOBFbZ3hfYFzhJ0i7lutnA523vWb7eleLpzW8Ddgf+CjgIOA34x7LPw8DBtvcG\n/hn4Ys2xZgDHANOBYyTtBHwH+ICkLco+HwW+PbhISfMk9Ujq6X9+1ajefERE1K8Sdy1KOocigF4C\nHgfeJunocnUH0F2uu8f2ozWbPmq7r9zHcuAW25bUB3TVbH+JpG7AwBY1299ie1W5/YPAzrafkPQT\n4HBJDwFbDByjlu2FwEIonhC90R9CREQMabwG2XLgqIEXtk+WNAXoAX4NfNr2jbUbSJoDPDdoP2tq\nltfWvF7Lq+/9DOBW20dK6gJuG2b7/pptLqAY0T3MEKOxiIhonvF6avEnwCRJn6xp27r8fSPwyYFT\ne5LeLGmbjThWB7CiXP5IPRvYvhvYieJU5ZUbceyIiNhI4zLIbBv4IPBOSY9Kuge4BPh7itHQg8C9\nkh4AzmfjRpb/Dpwp6U5gNHc7fg+40/YfN+LYERGxkVRkRoyWpOuAr9u+ZaS+Ezu73Tl3QROqik1N\nZvaIdiap1/askfqN12tk45ak7YB7gPvrCTGA6dM66MkfnIiIhkiQjZLtp4E3t7qOiIgojMtrZBER\nEfVKkEVERKUlyCIiotISZBERUWkJsoiIqLQEWUREVFqCLCIiKi1BFhERlZYgi4iISsvMHk3Qt2IV\nXfOvb3UZES2XuSGjETIii4iISmuLEZmkfqD2Kc0ftP1Yi8qJiIgmaosgA16wPWO4lZI2t/1KMwuK\niIjmaNtTi5I+IukqSdcCiyRNlnSLpHsl9Uk6ouzXJekhSd+StFzSIklblet2lXSzpPvL7d5Utn9W\n0hJJyyR9oYVvMyJik9cuQbaVpKXlz49q2vcH5to+FHgRONL2PsAhwFclqezXDZxj+y3A08BRZfvl\nZftewAHAbyUdVvafDcwAZko6eHBBkuZJ6pHU0//8qrF/xxERAbT/qcWbbP+hXBbwxTJ01gLTgB3K\ndY/aXlou9wJdkl4HTLP9IwDbLwKUQXYYcF/ZfzJFsN1ee2DbC4GFUDwheuPfYkREDKVdgmw4z9Us\nHwdMBWbaflnSY8Ckct2amn79wFYUwTcUAWfaPn+Ma42IiA3QLqcW69EBrCxD7BBg5/V1tv0M8KSk\nDwJImihpa+BG4ARJk8v2aZK2b3DtERExjE0pyC4HZknqoRidPVzHNn8NnCJpGfBz4L/bXgRcASyW\n1Ad8H3hdg2qOiIgRyM7lm0ab2NntzrkLWl1GRMtlZo8YDUm9tmeN1K/dr5GNC9OnddCT/4EjIhpi\nUzq1GBERbShBFhERlZYgi4iISkuQRUREpSXIIiKi0hJkERFRaQmyiIiotARZRERUWoIsIiIqLTN7\nNEHfilV0zb++1WVERDRVs6Yky4gsIiIqrfJBJmn1BmzzmKQprTp+RESMncoH2VAkTWh1DRER0Rxt\nE2SS5ki6VdIVQF/ZdrykeyQtlXT+UAEn6WpJvZKWS5pX075a0r9Jul/SXZJ2KNt3kbRY0hJJZzTt\nDUZExJDaJshKs4HP295T0h7AMcCBtmcA/RQP1BzsBNszgVkUD9F8Q9m+DXCX7b2A24GTyvazgPNs\n7wv8roHvJSIi6tBuQXaP7UfL5XcBM4ElkpaWr984xDanSLofuAvYCegu218CriuXe4GucvlA4Mpy\n+bLhCpE0T1KPpJ7+51dt4NuJiIiRtNvt98/VLAu4xPY/DNdZ0hzg3cD+tp+XdBswqVz9sl99fHY/\nr/2sRnystu2FwEIonhBd7xuIiIjRabcRWa1bgKMlbQ8g6b9J2nlQnw7gj2WI7Q7sV8d+7wSOLZeH\nOlUZERFN1LZBZvtB4J+ARZKWATcBnYO63QBsXq4/g+L04khOBU6WtIQiCCMiooX06tmzaJSJnd3u\nnLug1WVERDTVxs7sIanX9qyR+rXbNbJxafq0DnqaNFVLRMSmpm1PLUZExKYhQRYREZWWIIuIiEpL\nkEVERKUlyCIiotJy+30TSHoW+EWr6xhnpgBPtbqIcSafybrymaxrU/pMdrY9daROuf2+OX5Rz3ch\nNiWSevKZvFY+k3XlM1lXPpN15dRiRERUWoIsIiIqLUHWHAtbXcA4lM9kXflM1pXPZF35TAbJzR4R\nEVFpGZFFRESlJcgaTNJ7Jf1C0q8kzW91Pa0m6SJJKyU90OpaxgtJO0m6VdJDkpZLOrXVNbWapEmS\n7pF0f/mZfKHVNY0HkiZIuk/SdSP33nQkyBpI0gTgHOB9wJ7AhyXt2dqqWu5i4L2tLmKceQX4O9t7\nUDzc9eT8d8Ia4FDbewEzgPdKqufBt+3uVOChVhcx3iTIGms28Cvbj9h+CfgOcESLa2op27cDf2h1\nHeOJ7d/avrdcfpbiD9W01lbVWi6sLl9uUf5s0hf0Je0IvB+4oNW1jDcJssaaBjxR8/pJNvE/ULF+\nkrqAvYG7W1tJ65Wn0ZYCK4GbbG/qn8kC4HPA2lYXMt4kyBpLQ7Rt0v+qjOFJmgz8APiM7WdaXU+r\n2e63PQPYEZgt6a2trqlVJB0OrLTd2+paxqMEWWM9CexU83pH4DctqiXGMUlbUITY5bZ/2Op6xhPb\nTwO3sWlfWz0Q+ICkxyguURwq6X+3tqTxI0HWWEuAbkm7SNoSOBa4psU1xTgjScCFwEO2v9bqesYD\nSVMlbVcubwW8G3i4tVW1ju1/sL2j7S6KvyM/sX18i8saNxJkDWT7FeBTwI0UF/C/Z3t5a6tqLUlX\nAouB3SQ9KenEVtc0DhwI/DXFv7KXlj9/3uqiWqwTuFXSMop/EN5kO7ecx5Ays0dERFRaRmQREVFp\nCbKIiKi0BFlERFRagiwiIiotQRYREWNqNJODS/p6zd26v5T09KiPl7sWIyJiLEk6GFgNXGq77hlZ\nJH0a2Nv2CaM5XkZkERExpoaaHFzSmyTdIKlX0h2Sdh9i0w8DV472eJtvYJ0RERGjsRD4hO3/kPR2\n4Fzg0IGVknYGdgF+MtodJ8giIqKhygmxDwCuKmZkA2DioG7HAt+33T/a/SfIIiKi0TYDni6fZjCc\nY4GTN3TnERERDVM+luhRSX8JxUTZkvYaWC9pN+D1FPOwjlqCLCIixtQwk4MfB5wo6X5gOXBEzSYf\nBr7jDbyNPrffR0REpWVEFhERlZYgi4iISkuQRUREpSXIIiKi0hJkERFRaQmyiIiotARZRERUWoIs\nIiIq7f8D3EAdEK5H6HUAAAAASUVORK5CYII=\n", "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "totalImports.head(10).plot(kind='barh')" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "activity": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The UK exports to 107 countries.\n", "The 5 biggest importers from the UK are:\n" ] }, { "data": { "text/plain": [ "Partner\n", "Ireland 128155891\n", "Netherlands 18018603\n", "China 14111163\n", "Germany 10828464\n", "China, Hong Kong SAR 9482458\n", "Name: Trade Value (US$), dtype: int64" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "exports = milk[milk[FLOW] == 'Exports']\n", "grouped = exports.groupby([PARTNER])\n", "print('The UK exports to', len(grouped), 'countries.')\n", "print('The 5 biggest importers from the UK are:')\n", "grouped[VALUE].aggregate(sum).sort_values(ascending=False,inplace=False).head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Regular importers\n", "\n", "Given that there are two commodities, the third question, 'Which are the regular customers, i.e. which countries buy milk from the UK every month?', is meant in the sense that a regular customer imports both commodities every month. This means that if the exports dataframe is grouped by country, each group has exactly ten rows (two commodities bought each of the five months). To see the countries, only the first month of one commodity has to be listed, as by definition it's the same countries every month and for the other commodity." ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "activity": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PeriodPartnerTrade FlowMilk and creamTrade Value (US$)
268201501BelgiumExportsprocessed142610
271201501ChinaExportsprocessed1013141
275201501CyprusExportsprocessed6625
280201501DenmarkExportsprocessed107883
284201501FranceExportsprocessed178262
287201501GermanyExportsprocessed2588380
292201501China, Hong Kong SARExportsprocessed1110086
294201501HungaryExportsprocessed2119
298201501IrelandExportsprocessed4020981
300201501ItalyExportsprocessed23267
311201501MaltaExportsprocessed1403
314201501NetherlandsExportsprocessed3788929
317201501PolandExportsprocessed10145
319201501PortugalExportsprocessed3165
327201501SpainExportsprocessed10107
329201501SwedenExportsprocessed1739
332201501United Arab EmiratesExportsprocessed417480
\n", "
" ], "text/plain": [ " Period Partner Trade Flow Milk and cream Trade Value (US$)\n", "268 201501 Belgium Exports processed 142610\n", "271 201501 China Exports processed 1013141\n", "275 201501 Cyprus Exports processed 6625\n", "280 201501 Denmark Exports processed 107883\n", "284 201501 France Exports processed 178262\n", "287 201501 Germany Exports processed 2588380\n", "292 201501 China, Hong Kong SAR Exports processed 1110086\n", "294 201501 Hungary Exports processed 2119\n", "298 201501 Ireland Exports processed 4020981\n", "300 201501 Italy Exports processed 23267\n", "311 201501 Malta Exports processed 1403\n", "314 201501 Netherlands Exports processed 3788929\n", "317 201501 Poland Exports processed 10145\n", "319 201501 Portugal Exports processed 3165\n", "327 201501 Spain Exports processed 10107\n", "329 201501 Sweden Exports processed 1739\n", "332 201501 United Arab Emirates Exports processed 417480" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def buysEveryMonth(group):\n", " return len(group) == 10\n", "\n", "grouped = exports.groupby([PARTNER])\n", "regular = grouped.filter(buysEveryMonth)\n", "regular[(regular[MONTH] == 201501) & (regular[COMMODITY] == 'processed')]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Just over 75% of the total UK exports are due to these regular customers." ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "activity": false }, "outputs": [ { "data": { "text/plain": [ "0.7583526396315317" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "regular[VALUE].sum() / exports[VALUE].sum()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Bi-directional trade\n", "\n", "To address the fourth question, \n", "'Which countries does the UK both import from and export to?', a pivot table is used to list the total export and import value for each country. " ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "activity": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Trade FlowExportsImports
Partner
Algeria8087885.0NaN
Angola2416129.0NaN
Antigua and Barbuda22218.0NaN
Areas, nes51205.0NaN
Australia81644.0NaN
\n", "
" ], "text/plain": [ "Trade Flow Exports Imports\n", "Partner \n", "Algeria 8087885.0 NaN\n", "Angola 2416129.0 NaN\n", "Antigua and Barbuda 22218.0 NaN\n", "Areas, nes 51205.0 NaN\n", "Australia 81644.0 NaN" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "countries = pivot_table(milk, index=[PARTNER], columns=[FLOW], \n", " values=VALUE, aggfunc=sum)\n", "countries.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Removing the rows with a missing value will result in only those countries with bi-directional trade flow with the UK." ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "activity": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Trade FlowExportsImports
Partner
Austria56279.0780.0
Belgium7209121.014325697.0
Czech Rep.2590.0486889.0
Denmark335068.013681759.0
France9381762.028314091.0
Germany10828464.021899123.0
Hungary71378.04762.0
Ireland128155891.046263897.0
Italy173689.0321635.0
Latvia567.0870.0
Lithuania1172.0318407.0
Netherlands18018603.017658912.0
Poland106659.04933917.0
Portugal25855.0308654.0
Romania36.0975996.0
Slovakia65.039990.0
Spain1229172.04910615.0
Sweden34972.01962413.0
United Arab Emirates1590593.027225.0
United States of America548910.046044.0
\n", "
" ], "text/plain": [ "Trade Flow Exports Imports\n", "Partner \n", "Austria 56279.0 780.0\n", "Belgium 7209121.0 14325697.0\n", "Czech Rep. 2590.0 486889.0\n", "Denmark 335068.0 13681759.0\n", "France 9381762.0 28314091.0\n", "Germany 10828464.0 21899123.0\n", "Hungary 71378.0 4762.0\n", "Ireland 128155891.0 46263897.0\n", "Italy 173689.0 321635.0\n", "Latvia 567.0 870.0\n", "Lithuania 1172.0 318407.0\n", "Netherlands 18018603.0 17658912.0\n", "Poland 106659.0 4933917.0\n", "Portugal 25855.0 308654.0\n", "Romania 36.0 975996.0\n", "Slovakia 65.0 39990.0\n", "Spain 1229172.0 4910615.0\n", "Sweden 34972.0 1962413.0\n", "United Arab Emirates 1590593.0 27225.0\n", "United States of America 548910.0 46044.0" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "countries.dropna()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Conclusions\n", "\n", "The milk and cream trade of the UK from January to May 2015 was analysed in terms of which countries the UK mostly depends on for income (exports) and goods (imports). Over the period, the UK had a trade surplus of over 100 million US dollars.\n", "\n", "Ireland is the main partner, but it imported from the UK almost the triple in value than it exported to the UK. \n", "\n", "The UK exported to over 100 countries during the period, but only imported from 21 countries, the main ones (top five by trade value) being geographically close. China and Hong Kong are the main importers that are not also main exporters. \n", "\n", "The UK is heavily dependent on its regular customers, the 16 countries that buy all types of milk and cream every month. They contribute three quarters of the total export value.\n", "\n", "The UK has bi-directional trade (i.e. both exports and imports) with 20 countries, although for some the trade value (in US dollars) is suspiciously low, which raises questions about the data's accuracy." ] } ], "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.6.2" } }, "nbformat": 4, "nbformat_minor": 1 }