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.
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
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.
total = cheeseCountriesAll.groupby([FLOW])
total[GBPVALUE].aggregate(sum)
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.
cheeseMonthTable = pivot_table(cheeseCountriesAll,
index=[MONTH_CODE,MONTH],
columns = [FLOW],
values = GBPVALUE,
aggfunc=sum
)
cheeseMonthTable
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.
cheeseMonthTable.plot(kind='bar',figsize=(12,8))
Trade by type of cheese¶
Adding in the type of cheese to the columns enable comparison of the imports and exports by category.
cheeseCountriesSub
cheeseTypeMonthTable = pivot_table(cheeseCountriesSub,
index=[MONTH_CODE,MONTH],
columns = [COMMODITY,FLOW],
values = GBPVALUE,
aggfunc=sum
)
cheeseTypeMonthTable
cheeseTypeMonthTable.plot(kind='bar',figsize=(28,15))
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.
cheeseCountriesTable = pivot_table(cheeseCountriesAll,
index=[PARTNER],
columns=[FLOW],
values=GBPVALUE,
aggfunc=sum
)
cheeseCountriesTable
cheeseCountriesTable.plot(kind='bar',figsize=(12,8))
Modifying the list to show only Partners for which the trade value is more than £2.5million will identify major trading partners.
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
cheeseCountriesMainTable.plot(kind='bar',figsize=(12,8))
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.
cheeseCountryAllUSA= cheeseCountriesAll[cheeseCountries[PARTNER]=="United States of America"]
cheeseCountryAllUSATable = pivot_table(cheeseCountryAllUSA,
index=[MONTH_CODE,MONTH],
columns = [COMMODITY,FLOW],
values = GBPVALUE,
aggfunc=sum
)
cheeseCountryAllUSATable
cheeseCountryAllUSATable.plot(figsize=(12,8))
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.
cheeseCountrySubUSA= cheeseCountriesSub[cheeseCountries[PARTNER]=="United States of America"]
cheeseCountrySubUSAExports= cheeseCountriesSub[cheeseCountries[FLOW]=="Exports"]
cheeseCountryUSASubExportsTable = pivot_table(cheeseCountrySubUSAExports,
index=[MONTH_CODE,MONTH],
columns = [COMMODITY,FLOW],
values = GBPVALUE,
aggfunc=sum
)
cheeseCountryUSASubExportsTable
cheeseCountryUSASubExportsTable.plot(figsize=(12,10))
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.