CoCalc -- Week 4 Project Michael Wilding.ipynb

Jupyter notebook Week 4 Project Michael Wilding.ipynb

⁴¹ views

Kernel: Python 3

Learn to Code for Data Analysis Week 4 Project

In this project I have been tasked to explore Comtrade data (http://comtrade.un.org/data/) for a commodity of my choice and consider the trade characteristics of the commodity including consideration of the following questions:

Which are the regular exporters, i.e. which countries sell every month to the UK?
Where could the export market be further developed, i.e. which countries import the least? Do the figures look realistic?
What is the total amount of exports to and imports from the bi-lateral trade countries? Hint: pivot tables can have ‘marginal’ values.

My chosen commodity is cheese.

I will download the comtrade data for cheese (and it's sub-categories) and consider the above questions and other features of the data. As part of the project I wish to use learning from previous weeks. As such I intend to consider how to alter the trade value from US$ to GB£, to perform calculations across columns to work out net trade value and other manipulations if appropriate.

In [17]:

import warnings
warnings.simplefilter('ignore', FutureWarning)

from pandas import *
%matplotlib inline

cheeseAll = read_csv('comtrade_cheese_2015_monthly.csv', dtype={'Commodity Code':str})

def cheeseType(code):
    if code == '0406': # Cheese and curd
        return 'All Cheese & Curd'
    if code == '040610': # fresh cheese, unfermented cheese, curd
        return 'Cheese, fresh and unfermented; Curd' 
    if code == '040620': # cheese, grated or powdered
        return 'Cheese, grated'
    if code == '040630': # processed cheese, not grated/powdered
        return 'Cheese, processed'
    if code == '040640': # blue cheese
        return 'Cheese, blue veined'
    if code == '040690': # other
        return 'Cheese, other'
    return 'unknown'

def usdToGBP (currency):
    return currency * 0.681

def monthName (code):
    if code == 201501:
        return 'January'
    if code == 201502:
        return 'February'
    if code == 201503:
        return 'March'
    if code == 201504:
        return 'April'
    if code == 201505:
        return 'May'
    if code == 201506:
        return 'June'
    if code == 201507:
        return 'July'
    if code == 201508:
        return 'August'
    if code == 201509:
        return 'September'
    if code == 201510:
        return 'October'
    if code == 201511:
        return 'November'
    return 'December'

COMMODITY = 'Cheese'
cheeseAll[COMMODITY] = cheeseAll['Commodity Code'].apply(cheeseType)
MONTH_CODE = 'Period'
MONTH = 'Period Desc.'
PARTNER = 'Partner'
FLOW = 'Trade Flow'
VALUE = 'Trade Value (US$)'
GBPVALUE = 'Trade Value (GB£)'
cheeseAll[GBPVALUE] = cheeseAll[VALUE].apply(usdToGBP).round()
headings = [MONTH_CODE, MONTH, PARTNER, FLOW, COMMODITY, GBPVALUE]
cheeseAll = cheeseAll[headings]
cheeseAll

Out[17]:

In [18]:

cheeseCountries = cheeseAll[cheeseAll['Partner'] != 'World'] #This removes the 'world' data from the data set.
cheeseCountriesAll = cheeseCountries[cheeseCountries[COMMODITY] == 'All Cheese & Curd'] #This removes the sub-categories from the data set, leaving just the 'all cheese' figures.
cheeseCountriesSub = cheeseCountries[cheeseCountries[COMMODITY] != 'All Cheese & Curd'] #This removes the 'all cheese' data leaving just the sub-categories.

Total trade flow

To answer the first question, 'how much does the UK export and import and is the balance positive (more exports than imports)?', 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.

In [19]:

total = cheeseCountriesAll.groupby([FLOW])
total[GBPVALUE].aggregate(sum)

Out[19]:

Trade Flow
Exports        461543718
Imports       1338598155
Name: Trade Value (GB£), dtype: float64

This shows that overall in 2015 there was a trade deficit of approximately £1.2 trillion. This can be further broken down by month using a pivot table.

In [20]:

cheeseMonthTable = pivot_table(cheeseCountriesAll,
                            index=[MONTH_CODE,MONTH],
                            columns = [FLOW],
                            values = GBPVALUE,
                            aggfunc=sum
                                    )
cheeseMonthTable

Out[20]:

This table is plotted on a bar chart to show the differences visualy. This chart shows import and export values for all cheese and curd and suggests a peak in exports in October, and a peak in imports in June. It also clearly visualises the trade deficit each month.

In [21]:

cheeseMonthTable.plot(kind='bar',figsize=(12,8))

Out[21]:

<matplotlib.axes._subplots.AxesSubplot at 0x7fc78a8a1f28>

Trade by type of cheese

Adding in the type of cheese to the columns enable comparison of the imports and exports by category.

In [22]:

cheeseCountriesSub

cheeseTypeMonthTable = pivot_table(cheeseCountriesSub,
                            index=[MONTH_CODE,MONTH],
                            columns = [COMMODITY,FLOW],
                            values = GBPVALUE,
                            aggfunc=sum
                                    )
cheeseTypeMonthTable

Out[22]:

In [34]:

cheeseTypeMonthTable.plot(kind='bar',figsize=(28,15))

Out[34]:

<matplotlib.axes._subplots.AxesSubplot at 0x7fc783ec5eb8>

Trade by country

Modifying the code allows visualisation of imports and exports for all cheese by country, and identifies a list of 98 countries. Most seem to be countries to which the UK has exported or imported small amounts of cheese.

In [27]:

cheeseCountriesTable = pivot_table(cheeseCountriesAll,
                            index=[PARTNER],
                            columns=[FLOW],
                            values=GBPVALUE,
                            aggfunc=sum
                                    )
cheeseCountriesTable

Out[27]:

In [29]:

cheeseCountriesTable.plot(kind='bar',figsize=(12,8))

Out[29]:

<matplotlib.axes._subplots.AxesSubplot at 0x7fc788dd5400>

Modifying the list to show only Partners for which the trade value is more than £2.5million will identify major trading partners.

In [55]:

def tradeMoreThan100K(trade):
    return sum(trade[GBPVALUE]) > 2500000

cheeseCountriesAllMain = cheeseCountriesAll.groupby(PARTNER).filter(tradeMoreThan100K).sort(FLOW, ascending=False)
cheeseCountriesMainTable = pivot_table(cheeseCountriesAllMain,
                            index=[PARTNER],
                            columns=[FLOW],
                            values=GBPVALUE,
                            aggfunc=sum
                                    )
cheeseCountriesMainTable

Out[55]:

In [44]:

cheeseCountriesMainTable.plot(kind='bar',figsize=(12,8))

Out[44]:

<matplotlib.axes._subplots.AxesSubplot at 0x7fc7834b8240>

This chart identifies the UK's main trading partners (as defined by a total trade value (import and export) of more than £2.5 million. It shows that largely the UK imports from all countries more than it exports, and that the countries with the highest trade value are Ireland, France, Germany, Italy and Germany respectively. One curiosity is the USA, where the UK exported a large value of cheese without significant imports. This is explored further below.

In [51]:

cheeseCountryAllUSA= cheeseCountriesAll[cheeseCountries[PARTNER]=="United States of America"]
cheeseCountryAllUSATable = pivot_table(cheeseCountryAllUSA,
                            index=[MONTH_CODE,MONTH],
                            columns = [COMMODITY,FLOW],
                            values = GBPVALUE,
                            aggfunc=sum
                                    )
cheeseCountryAllUSATable

Out[51]:

/usr/lib/python3/dist-packages/pandas/core/frame.py:1771: UserWarning: Boolean Series key will be reindexed to match DataFrame index.
  "DataFrame index.", UserWarning)

In [60]:

cheeseCountryAllUSATable.plot(figsize=(12,8))

Out[60]:

<matplotlib.axes._subplots.AxesSubplot at 0x7fc782bf1358>

This shows a peak in exports of all cheese around October, and a second smaller peak in January. This does not, however, give an indication of any pattern in the type of cheese exported.

In [68]:

cheeseCountrySubUSA= cheeseCountriesSub[cheeseCountries[PARTNER]=="United States of America"].reset_index()
cheeseCountrySubUSAExports= cheeseCountriesSub[cheeseCountries[FLOW]=="Exports"].reset_index()
cheeseCountryUSASubExportsTable = pivot_table(cheeseCountrySubUSAExports,
                            index=[MONTH_CODE,MONTH],
                            columns = [COMMODITY,FLOW],
                            values = GBPVALUE,
                            aggfunc=sum
                                    )
cheeseCountryUSASubExportsTable

Out[68]:

/usr/lib/python3/dist-packages/pandas/core/frame.py:1771: UserWarning: Boolean Series key will be reindexed to match DataFrame index.
  "DataFrame index.", UserWarning)

In [67]:

cheeseCountryUSASubExportsTable.plot(figsize=(12,10))

Out[67]:

<matplotlib.axes._subplots.AxesSubplot at 0x7fc782b1f7f0>

This information reveals that the October peak in exports of cheese to the USA is due to the 'Cheese, other' category, and that the likely cause of the January peak is processed cheese.

Conclusion

The UK has a relatively high trade activity in cheese, both imports and exports. Most countries with which the UK trades cheese have both imports and exports, although the UK imports a higher value of cheese than it exports (£1.3 trillion vs £0.4 trillion). The countries with which the UK partakes most trade in cheese are Ireland, France, Germany, Italy and Germany. There was one country whose main trade relationship was exports of UK cheese, the United States of America, who imported UK cheese at a generaly level, with a peak import of 'other cheese' in October 2015.

Learn to Code for Data Analysis Week 4 Project

Total trade flow

Trade by type of cheese

Trade by country

Conclusion

Product

Resources

Company