# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---
# ## Project 4: Exploring the RSA's machinery imports and exports
# 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 SA's imports and exports of milk and cream in 2015:
#
# - How much does SA export and import and is the balance positive (more exports than imports)? 
# - Which are the main trading partners, i.e. from/to which countries does the SA import/export the most?
# - Which are the regular customers, i.e. which countries buy machinery from the SA every month?
# - Which countries does SA both import from and export to?
# + activity=false
import warnings
warnings.simplefilter('ignore', FutureWarning)
from pandas import *
# %matplotlib inline
# -
# ## Getting and preparing the data
#
# The data is obtained from the [United Nations Comtrade](http://comtrade.un.org/data/) website, by selecting the following configuration:
#
# - Type of Product: goods
# - Frequency: monthly 
# - Periods: January to May of 2015
# - Reporter: United Kingdom
# - Partners: all
# - Flows: imports and exports
# - HS (as reported) commodity codes: 0401 (Milk and cream, neither concentrated nor sweetened) and 0402 (Milk and cream, concentrated or sweetened)
#
# 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.
commodity= 'comtrade_machinery_sa_jan_may_15.csv'
# The data can also be downloaded directly from Comtrade using the "View API Call" URL, modified in two ways:
#
# - `max=500` is increased to `max=5000` to make sure all data is loaded,
# - `&fmt=csv` is added at the end to obtain the data in CSV format.
# +
# 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'
# -
# On reading in the data, the commodity code has to be read as a string, to not lose the leading zero.
# + activity=false
machinery = read_csv(commodity, dtype={'Commodity Code':str})
machinery.head()

# -
# # The data only covers the last five months of 2020. 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.
# + activity=false
def machineType(code):
    if code == '731412': # neither concentrated nor sweetened
        return 'stainless steel'
    if code == '731412': # concentrated or sweetened
        return 'non stainless steel' 
    return 'unknown'
COMMODITY = 'steel machinery'
machinery[COMMODITY] = machinery['Commodity Code'].apply(machineType)
MONTH = 'Period'
PARTNER = 'Partner'
FLOW = 'Trade Flow'
VALUE = 'Trade Value (US$)'
headings = [MONTH, PARTNER, FLOW, COMMODITY, VALUE]
machinery = machinery[headings]
machinery.head()
# -
# 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.
# + activity=false
machinery = machinery[machinery[PARTNER] != 'World']
machinery.head()
# -
# ## 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.
# + activity=false
grouped = machinery.groupby([FLOW])
grouped[VALUE].aggregate(sum)
# -
# This shows a trade surplus of over 5 million dollars.
# ## Main trade partners
#
# 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.
# + activity=false
imports = machinery[machinery[FLOW] == 'Imports']
grouped = imports.groupby([PARTNER])
print('SA imports from', len(grouped), 'countries.')
print('The 5 biggest exporters to SA are:')
totalImports = grouped[VALUE].aggregate(sum).sort_values(inplace=False,ascending=False)
totalImports.head()
# -
# The export values can be plotted as a bar chart, making differences between countries easier to see.
totalImports.head(10).plot(kind='barh')
# + activity=false
exports = machinery[machinery[FLOW] == 'Exports']
grouped = exports.groupby([PARTNER])
print('SA exports to', len(grouped), 'countries.')
print('The 5 biggest importers from SA are:')
grouped[VALUE].aggregate(sum).sort_values(ascending=False,inplace=False).head()

# -
# ## Regular importers
#
# Given that there are two commodities, the third question, 'Which are the regular customers, i.e. which countries buy machinery from SA 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.
# + activity=false
def buysEveryMonth(group):
    return len(group) == 10
grouped = exports.groupby([PARTNER])
regular = grouped.filter(buysEveryMonth)
regular[(regular[MONTH] == 201501) & (regular[COMMODITY] == 'processed')]
# -
# Just over 75% of the total UK exports are due to these regular customers.
# + activity=false
regular[VALUE].sum() / exports[VALUE].sum()
# -
# ## Bi-directional trade
#
# To address the fourth question, 
# '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. 
# + activity=false
countries = pivot_table(machinery, index=[PARTNER], columns=[FLOW], 
                        values=VALUE, aggfunc=sum)
countries.head()
# -
# Removing the rows with a missing value will result in only those countries with bi-directional trade flow with SA.
# + activity=false
countries.dropna()
# -
# ## Conclusions
#
# The machinery trade of SA from January to May 2019 was analysed in terms of which countries SA mostly depends on for income (exports) and goods (imports). Over the period, SA had a trade surplus of over 5 million US dollars.
#
# United Kingdom are the main importing partner and Swaziland is the main exporting partner. 
#
# SA exported to 17 countries during the period, but only imported from 13 countries.
#
# There are no regulars or monthly exports to a certain country. This is probably because the product at hand is not a need like food.
#
# SA has no bi-directional trade (i.e. both exports and imports). It imports mostly from other continents, which do not import from SA as much as African countries.
#
#
# ## Now repeat the analysis with your own data gotten from Comtrade website