Where do we get our stuff come from? Who buys the things we make? It is no surprise that almost all material goods found in Singapore originated from somewhere else outside the tiny island-nation. With one of the highest trade-to-GDP ratio in the world, Singapore's economy lives and dies by trade. In this post, I have visualized the history of Singapore's merchandise trade with her trading partners, and how Singapore's relationship with them has changed over the years.
- Hover over the data points to see individual country trade statistics with Singapore
- Built with javascript (D3.js), python
Singapore's Merchandise Trade
By Country/Region 1976-2018
Data source: Singapore Department of Statistics (DOS)
The countries that are buying our stuff and the countries that are selling us stuff have changed significantly in the last 40 years. Since the 70s, the United States and Malaysia have been Singapore's largest trading partners, together with Japan in third place. This started to change in the early 2000s when China rapidly overtook Japan and Indonesia to get to third place (press pause at 2004q2). Shortly after, China will go on to overtake United States (2008q1), and subsequently Malaysia (2014q1) to take the place of Singapore's largest trading partner (in merchandise).
It is worth noting that this visualization only represents Singapore's trade in goods, and does not include the country's trade in services, which has over the years, contributed a growing share towards overall trade and GDP. In 2017, Singapore's merchandise trade was approximately twice the size of her services trade (merchandise: $967b, services: $463b)
Another caveat is in order. The merchandise trade visualized here also includes re-exports. Re-exports are goods that were imported and subsequently exported without a change in its state. This is no small amount in Singapore's case, taking up about a quarter of total merchandise trade in 2017.
The rest of the post below shows how the visualization was created using python and javascript. Code can be found at https://gitlab.com/royhung_/merchandise-trade.
Data Cleaning - merch_trade_clean.py
Using outputFile.xlsx downloaded from singstat.gov.sg, the goal is to convert this data into a json format suitable for consumption by D3. At the same time, this data is aggregated from monthly to quarterly data.
import pandas as pd import numpy as np import json df_imp = pd.read_excel("outputFile.xlsx", sheet_name="T1", skiprows=5, thousands=",") #Merchandise import data df_exp = pd.read_excel("outputFile.xlsx", sheet_name="T2", skiprows=5, thousands=",") #Merchandise export data df_imp = df_imp.iloc[:120] df_exp = df_exp.iloc[:92] # Replace 'na' strings to -90000 df_imp.replace("na", 0, inplace=True) df_exp.replace("na", 0, inplace=True) df_imp.replace("-", 0, inplace=True) df_exp.replace("-", 0, inplace=True) #Remove '(Thousand Dollars)' from country names df_imp['Variables'] = df_imp['Variables'].apply(lambda x: x.replace('(Thousand Dollars)', '')) df_exp['Variables'] = df_exp['Variables'].apply(lambda x: x.replace('(Thousand Dollars)', '')) #Remove leading spaces from country names df_imp['Variables'] = df_imp['Variables'].apply(lambda x: x.replace(' ', '')) df_exp['Variables'] = df_exp['Variables'].apply(lambda x: x.replace(' ', '')) #Drop first 7 rows - Not countries df_imp = df_imp[7:] df_exp = df_exp[7:] #Convert columns to float values df_imp[df_imp.columns[1:]] = df_imp[df_imp.columns[1:]].apply(pd.to_numeric, downcast="float", axis=1) df_exp[df_exp.columns[1:]] = df_exp[df_exp.columns[1:]].apply(pd.to_numeric, downcast="float", axis=1)
def generate_quarter_col(df, last_quarter=4): ''' Aggregates month columns and generates quarter columns (i.e., 1976q1, 2018q2, etc) last_quarter parameter takes in the ending quarter of series (1,2,3,4) ''' quarters = [ ['Jan', 'Feb', 'Mar'], ['Apr', 'May', 'Jun'], ['Jul', 'Aug', 'Sep'], ['Oct', 'Nov', 'Dec'] ] years = list(range(1976, 2019)) for y in years: if y == years[-1]: quarters = quarters[:last_quarter] for i, q in enumerate(quarters, 1): col_name = str(y) + 'q' + str(i) df[col_name] = df[str(y) + ' ' + q[0]] + df[str(y) + ' ' + q[1]] + df[str(y) + ' ' + q[2]] #print(col_name, ' column ', 'created')
#Generate columns for quarter aggregates generate_quarter_col(df_exp) generate_quarter_col(df_imp)
Prepare dictionary to map each country to respective region.
region_map = { "Belgium":"Europe","Denmark":"Europe","France":"Europe","Germany, Federal Republic Of":"Europe","Greece":"Europe","Ireland":"Europe","Italy":"Europe","Luxembourg":"Europe","Netherlands":"Europe","United Kingdom":"Europe","Portugal":"Europe","Spain":"Europe","Austria":"Europe","Finland":"Europe","Norway":"Europe","Sweden":"Europe","Switzerland":"Europe","Liechtenstein":"Europe","Malta":"Europe","Germany, Democratic Republic Of":"Europe","Hungary":"Europe","Poland":"Europe","Estonia":"Europe","Latvia":"Europe","Lithuania":"Europe","Slovenia":"Europe","Czech Republic":"Europe","Slovak Republic (Slovakia)":"Europe","Brunei Darussalam":"Asia","Indonesia":"Asia","Malaysia":"Asia","Philippines":"Asia","Thailand":"Asia","Myanmar":"Asia","Cambodia":"Asia","Laos People's Democratic Republic":"Asia","Vietnam, Socialist Republic Of":"Asia","Japan":"Asia","Hong Kong":"Asia","Korea, Republic Of":"Asia","Taiwan":"Asia","Macau":"Asia","China People's Republic":"Asia","Korea, Democratic People's Republic Of":"Asia","Afghanistan":"Asia","Bangladesh":"Asia","India":"Asia","Maldives, Republic Of":"Asia","Nepal":"Asia","Pakistan":"Asia","Sri Lanka":"Asia","Bahrain":"Asia","Cyprus":"Asia","Iran (Islamic Republic Of)":"Asia","Israel":"Asia","Jordan":"Asia","Kuwait":"Asia","Lebanon":"Asia","Oman":"Asia","Qatar":"Asia","Saudi Arabia":"Asia","Syrian Arab Republic":"Asia","United Arab Emirates":"Asia","Yemen":"Asia","Yemen Democratic":"Asia","Canada":"Americas","Puerto Rico":"Americas","United States":"Americas","Argentina":"Americas","Brazil":"Americas","Chile":"Americas","Colombia":"Americas","Ecuador":"Americas","Mexico":"Americas","Paraguay":"Americas","Peru":"Americas","Uruguay":"Americas","Venezuela":"Americas","Netherlands Antilles":"Americas","Panama":"Americas","Bahamas":"Americas","Bermuda":"Americas","French Guiana":"Americas","Grenada":"Americas","Guatemala":"Americas","Honduras":"Americas","Jamaica":"Americas","St Vincent & The Grenadines":"Americas","Trinidad & Tobago":"Americas","Anguilla":"Americas","Other Countries In America":"Americas","Australia":"Oceania","Fiji":"Oceania","Nauru":"Oceania","New Caledonia":"Oceania","New Zealand":"Oceania","Papua New Guinea":"Oceania","Cocos (Keeling) Islands":"Oceania","French Southern Territories":"Oceania","Norfolk Island":"Oceania","Cook Islands":"Oceania","French Polynesia":"Oceania","Guam":"Oceania","Kiribati":"Oceania","Niue":"Oceania","Solomon Islands":"Oceania","Tuvalu":"Oceania","Wallis & Fatuna Islands":"Oceania","Micronesia":"Oceania","Palau":"Oceania","South Sudan":"Oceania","Other Countries In Oceania":"Oceania","Commonwealth Of Independent States":"Other" }
#Drop month columns and keep quarterly data df_imp.drop(month_columns, axis=1, inplace=True) df_exp.drop(month_columns, axis=1, inplace=True) #Convert to dictionary to build json file import_dict = df_imp.set_index(['Variables']).T.to_dict(orient='index') export_dict = df_exp.set_index(['Variables']).T.to_dict(orient='index')
#Build the json file json_data = [] quarters = sorted(import_dict.keys()) countries = df_imp['Variables'] for q in quarters: json_entry = {} json_entry['quarter'] = q countries_data = [] for country in countries: imp = import_dict[q].get(country, 0) exp = export_dict[q].get(country, 0) total = imp + exp countries_data.append({ 'country':country, 'region':region_map[country], 'import':imp, 'export':exp, 'total':total }) json_entry['countries'] = countries_data json_data.append(json_entry) with open("merch_trade_data.json", "w") as f: f.write(json.dumps(json_data))
Setup - index.html
Setup HTML template for visualization
<!DOCTYPE html> <html> <head> <title>Singapore Merchandise Trade</title> <script src="https://d3js.org/d3.v4.min.js"></script> </head> <body> <div id="graph" style="width:800px;text-align:center;"> <div id="chart-area"></div> <input type="range" min="0" max="171" value="1" class="slider" id="rangeSlider" style="width:80%;"> <br> <button id="play" onclick="pausePlay()" value=1 >Pause</button> <button id="reset" onclick="reset()">Reset</button> </div> <script src="main.js"></script> </body> </html>
Visualization - main.js
Setup chart area and prepare axes, legends, labels, etc.
var div_width = document.getElementById('chart-area').offsetWidth var intended_width = 825 var intended_height = 625 var margin = { left:50 * div_width/intended_width, right:100 * div_width/intended_width, top:40 * div_width/intended_width, bottom:150* div_width/intended_width } var width = div_width - margin.left - margin.right //825 - margin.left - margin.right var height = intended_height * width / intended_width - margin.top - margin.bottom var svg = d3.select("#chart-area").append("svg") .attr('viewBox','0 0 '+Math.min( width + margin.left + margin.right,height + margin.top + margin.bottom)+' '+Math.min(width + margin.left + margin.right,height + margin.top + margin.bottom)) .attr('preserveAspectRatio','xMinYMin') .attr("width", width + margin.left + margin.right) .attr("height", height + margin.top + margin.bottom) .on("click", pausePlay); var g = svg.append("g") .attr("transform", "translate(" + margin.left + ", " + margin.top + ")") var gBorder = svg.append("rect") .attr("width", width + margin.left + margin.right) .attr("height", height + margin.top + margin.bottom) .attr("x", 0) .attr("y", 0) .style("fill", "none") .style("stroke", "#708090") var xScale = d3.scaleLinear() .domain([0, 20000000]) //Max import value derived from data cleaning - 17254772.0 .range([0, width]) var yScale = d3.scaleLinear() .domain([0, 20000000]) //Max export value derived from data cleaning - 19636632.0 .range([height , 0]) var zScale = d3.scaleLinear() //Scale for radius of circles .domain([0,40000000]) .range([0, 65 *div_width/intended_width]) var regions = ['Europe', 'Asia', 'Americas', 'Oceania', 'Others'] var colorPalette = ['#66c2a5', '#fc8d62', '#8da0cb', '#e78ac3'] var colorScale = d3.scaleOrdinal(d3.schemePastel2) .domain(regions) .range(colorPalette) // X Axis var xAxisGroup = g.append("g") .attr("class", "x-axis") .attr("transform", "translate(" + margin.left + "," + (height+margin.top)+ ")") .style("font-size", 12 * div_width/intended_width) var xAxisCall = d3.axisBottom(xScale) .ticks(5).tickFormat(function (d) { // Divide ticks by 1000 and format with comma // To give ticks in millions return d3.format(",")(d/1000) }) xAxisGroup.call(xAxisCall) xAxisGroup.selectAll("path").style("stroke", "#708090"); xAxisGroup.selectAll("line").style("stroke", "#708090"); // Y Axis var yAxisGroup = g.append("g") .attr("class", "y-axis") .attr("transform", "translate(" + margin.left + "," + margin.top + ")") .style("font-size", 12 * div_width/intended_width) var yAxisCall = d3.axisLeft(yScale) .ticks(5).tickFormat(function (d) { // Divide ticks by 1000 and format with comma // To give ticks in millions return d3.format(",")(d/1000) }) yAxisGroup.call(yAxisCall) yAxisGroup.selectAll("path").style("stroke", "#708090"); yAxisGroup.selectAll("line").style("stroke", "#708090"); // x Axis Labels g.append("text") .attr("y", height + 2*margin.top) .attr("x", (width + margin.right + margin.left)/2) .style("text-anchor", "middle") .style("font-family", "Arial") .style("font-weight", "bold") .style("fill", "#333333") .style("font-size", 16 * div_width/intended_width) .text("Merchandise Imports (SGD, millions)") // y Axis Labels g.append("text") .attr("transform", "rotate(-90)") .attr("y", -margin.left/3) .attr("x", -(height+margin.top)/2) .style("text-anchor", "middle") .style("font-family", "Arial") .style("font-weight", "bold") .style("fill", "#333333") .style("font-size", 16 * div_width/intended_width) .text("Merchandise Exports (SGD, millions)") // Net exporter label g.append("text") .attr("y", height/3) .attr("x", width/3) .attr("transform", "translate(" + margin.left + ", " + margin.top + ")") .style("text-anchor", "middle") .style("font-family", "Arial") .style("font-size", 32 * div_width/intended_width) .style("fill", "#d3d3d3") .text("Net Exporter") // Net Importer label g.append("text") .attr("y", height - height/3) .attr("x", width - width/3) .attr("transform", "translate(" + margin.left + ", " + margin.top + ")") .style("text-anchor", "middle") .style("font-family", "Arial") .style("font-size", 32 * div_width/intended_width) .style("fill", "#d3d3d3") .text("Net Importer") // 45 degree line separating net exporters / net importers var xyLine = g.append("line") .attr("x1", 0) .attr("y1", height ) .attr("x2", width -20) .attr("y2", 20) .attr("stroke-width", 1) .attr("stroke", "#708090") .attr("transform", "translate(" + margin.left + ", " + margin.top + ")"); // Prepare legend var legend = g.append("g") .attr("class", "legend") .attr("transform", "translate(" + 0.5*margin.left+ "," + (height + margin.top + 0.4*margin.bottom) +")") for (i=0; i < 4; i++) { legend.append("circle") .attr("cx", (i+1)*width*0.8/4 ) .attr("cy", 10 ) .attr("r", 20/4 * div_width/intended_width) .attr("fill", colorPalette[i]) legend.append("text") .attr("x", (i+1)*width*0.8/4 + 20/4* div_width/intended_width + width*0.01) .attr("y", 10+ 20/4 * div_width/intended_width) .attr("fill", colorPalette[i]) .style("font-family", "Arial") .style("font-size", 16 * div_width/intended_width) .text(regions[i]) }
Prepare update() function and other helper functions to update data according to the corresponding quarter during animation.
function update(i, data) { var circles = g.selectAll(".circleData").data(data[i].countries) circles.exit().remove() circles.enter().append("circle") .attr("class", "circleData") .merge(circles) .transition() .attr("r", function(d) { return zScale(d.total)}) .attr("cx", function(d) { return xScale(+d.import + 1)} ) .attr("cy", function(d) { return yScale(+d.export + 1)}) .attr("transform", "translate(" + margin.left + "," + margin.top + ")") .attr("fill", function(d) { return colorScale(d.region); }) circles.on("mouseover", mouseoverEvent) .on("mouseout", mouseoutEvent) document.getElementById("rangeSlider").value=i // If mouseover data is shown, update data on mouseover during animation pointLabel = document.getElementsByClassName("pointLabel") if (pointLabel.length != 0) { hoverCountry = pointLabel[0].innerHTML hoverCountryData = data[i].countries.filter(function(d) { return d.country == hoverCountry }) //Update total trade, import and export data of hovered over country d3.selectAll(".pointLabelData").text( "Total Trade: $" + d3.format(".3n")(hoverCountryData[0].total/1000000) + "bn | " + "Import: $" + d3.format(".3n")(hoverCountryData[0].import/1000000) + "bn | " + "Export: $" + d3.format(".3n")(hoverCountryData[0].export/1000000) + "bn" ) } } function mouseoverEvent(d) { d3.select(this) .style("fill", d3.rgb(this.getAttribute("fill")).brighter()) .style("stroke", "#d3d3d3") text_x = xScale(+d.import +1) text_y = yScale(+d.export + 1) // Country Text g.append("text") .attr("class", "pointLabel") .attr("x", 0 ) .attr("y", 0 ) .style("font-family", "Arial") .style("fill", "#333333") .style("font-size", 32 * div_width/intended_width) .text(d.country) .attr("transform", "translate(" + (margin.left + 20 * div_width/intended_width) + ",0)") // Total Trade Text g.append("text") .attr("class", "pointLabelData") .attr("x", 0 ) .attr("y", 25 * div_width/intended_width ) .style("font-family", "Arial") .style("fill", "#333333") .style("font-size", 16 * div_width/intended_width) .text( "Total Trade: $" + d3.format(".3n")(d.total/1000000) + "bn | " + "Import: $" + d3.format(".3n")(d.import/1000000) + "bn | " + "Export: $" + d3.format(".3n")(d.export/1000000) + "bn" ) .attr("transform", "translate(" + (margin.left+20 * div_width/intended_width) + ",0)") } function mouseoutEvent(d) { d3.select(this) .style("fill", function(d) { return colorScale(d.region); }) .style("stroke", "black") d3.selectAll(".pointLabel").remove() d3.selectAll(".pointLabelData").remove() } function reset() { time = 0; if (play == false) { play = true; } } function pausePlay() { if (play ==true ) { document.getElementById("play").innerHTML = "Play" } else { document.getElementById("play").innerHTML = "Pause" } play = !play; }
Lastly, the following code block calls the prepared json file and renders the data to the chart area.
var time = 0; var play = true; d3.json("./merch_trade_data.json", function(data) { console.log(data) i = 171 var circles = g.selectAll("circle").data(data[i].countries).enter() .append("circle") .attr("class", "circleData") .attr("r", function(d) { return zScale(d.total)}) .attr("cx", function(d) { return xScale(+d.import + 1)} ) .attr("cy", function(d) { return yScale(+d.export + 1)}) .attr("transform", "translate(" + margin.left + "," + margin.top + ")") .attr("fill", function(d) { return colorScale(d.region); }) .attr("stroke", "#333333") .attr("stroke-width", "0.8") .on("mouseover", mouseoverEvent) .on("mouseout", mouseoutEvent) var quarter_label = g.append("text") .attr("id", "quarter_label") .style("font-family", "Arial") .attr("font-size", 35 * div_width/intended_width) .attr("fill", "#333333") .attr("x", width - 2*margin.left) .attr("y", height) .text(data[i].quarter) var interval = setInterval(function(){ if (play === true) { update(time, data) quarter_label.text(data[time].quarter) time = (time < 171) ? +time + 1 : 0 } }, 100); //rangeSlider function d3.select("#rangeSlider").on("input", function() { //Always pause when slider is touched play = false; document.getElementById("play").innerHTML = "Play" time = this.value; update(time, data); quarter_label.text(data[time].quarter) }) })