3. Automated Data Update

The two Python/Pandas scripts below are called by the corresponding (UN Comtrade or UNIDO) front-end programs which in turn are invoked from the Data Update page. These two scripts are located in the /var/www/wordpress/jordan/prototype/scripts/ folder. They are included here to give an idea of the data structures and manipulation involved in displaying the charts.

The front-end programs are HTML/Javascripts/CSS based and they are to be found at:

• <Wordpress root folder>/jordan/prototype/admin/comtrade_update.html
• <Wordpress root folder>/var/www/wordpress/jordan/prototype/admin/unido_update.html

UN Comtrade

#!/home/huzaifa/anaconda3/bin/python3
#/usr/bin/python3
# Name: update_comtrade.py
# Created By: Huzaifa Zoomkawala
# Last Modified: August 10, 2021
import cgi
import cgitb; cgitb.enable()  # for troubleshooting
import os
import os.path
import datetime
import json
import pandas as pd
import sys
#------------------------------------ Global Variables
A = ['001', '011', '012', '022', '025', '034', '036', '041', '042', '043', '044', '045', '054', '057', '071', '072', '074', '075', '081', '121', '211', '212', '222', '223', '231', '244', '245', '246', '261', '263', '268', '269', '272', '273', '274', '277', '278', '291', '292', '321', '325', '333', '343', '681', '682', '683', '684', '685', '686', '687']
B = ['016', '017', '023', '024', '035', '037', '046', '047', '048', '056', '058', '059', '061', '062', '073', '091', '098', '111', '112', '122', '232', '247', '248', '251', '264', '265', '421', '422', '431']
C = ['281', '282', '283', '284', '285', '286', '287', '288', '289', '322', '334', '335', '342', '344', '345', '411', '511', '514', '515', '516', '522', '523', '524', '531', '532', '551', '592', '621', '625', '629', '633', '634', '635', '641', '661', '662', '663', '664', '667', '689']
D = ['611', '612', '613', '651', '652', '654', '655', '656', '657', '658', '659', '831', '841', '842', '843', '844', '845', '846', '848', '851']
E = ['642', '665', '666', '673', '674', '675', '676', '677', '679', '691', '692', '693', '694', '695', '696', '697', '699', '821', '893', '894', '895', '897', '898', '899']
F = ['713', '781', '782', '783', '784', '785']
G = ['266', '267', '512', '513', '533', '553', '554', '562', '571', '572', '573', '574', '575', '579', '581', '582', '583', '591', '593', '597', '598', '653', '671', '672', '678', '786', '791', '882']
H = ['711', '712', '714', '721', '722', '723', '724', '725', '726', '727', '728', '731', '733', '735', '737', '741', '742', '743', '744', '745', '746', '747', '748', '749', '761', '762', '763', '772', '773', '775', '778', '793', '811', '812', '813', '872', '873', '884', '885']
I = ['751', '752', '759', '764', '771', '774', '776', '891']
J = ['525', '541', '542', '716', '718', '792', '871', '874', '881']
K = ['351', '883', '892', '896', '961', '971']
RB = B+C
LT = D+E
MT = F+G+H
HT = I+J
manuf = RB+LT+MT+HT
MHT = MT+HT
year_col='Year'
code_col='Commodity Code'
value_col='Trade Value (US$)'
aggr_col='Aggregate Level'
countries = {
	'12': 'Algeria',
	'48': 'Bahrain',
	'818': 'Egypt',
	'275': 'Gaza and West Bank',
	'364': 'Iran',
	'368': 'Iraq',
	'376': 'Israel',
	'400': 'Jordan',
	'414': 'Kuwait',
	'422': 'Lebanon',
	'434': 'Libya',
	'504': 'Morocco',
	'512': 'Oman',
	'682': 'Saudi Arabia',
	'760': 'Syria',
	'788': 'Tunisia',
	'784': 'United Arab Emirates',
	'887': 'Yemen'
}
one_dgt = {
	"1": {"name": "Food, Beverages and Tobacco", "codes":['016', '017' '023', '024', '035',
		'037', '046', '047', '048', '056', '058', '059', '061', '062', '073', '091', '098',
		'111', '112', '122', '411', '421', '422', '431']},
	"2": {"name": "Textiles, wearing apparel and leather products", 'codes':['264','265',
		'266', '267', '611', '612', '613', '651', '652', '653', '654', '655', '656', '657',
		'658', '659', '831', '841', '842', '843', '844', '845', '846', '847', '848', '851']},
	"3": {"name": "Wood and Paper products", 'codes':['247', '248', '633', '634', '635',
		'641', '642', '251']},
	"4": {"name": "Metal products", 'codes':['281', '282', '283', '284', '285', '286', '287',
		'288', '289', '671', '672', '673', '674', '675', '676', '677', '678', '679', '678',
		'689', '691', '692', '693', '694', '695', '696', '697', '698', '699']},
	"5": {"name": "Coke, refined petroleum, non-metallic mineral products and rubber",
		'codes':['232', '322', '334', '335', '621', '625', '629', '661', '662', '663', '664',
		'665', '666', '667', '342', '344', '345']},
	"6": {"name": "Machinery, equipment and telecommunications", 'codes':['711', '712', '713',
		'714','716', '718', '721', '722', '723', '724', '725', '726', '727', '728', '731',
		'733', '735', '737', '741', '742', '743', '744', '745', '746', '747', '748', '749',
		'751', '752', '759', '761', '762', '763', '764', '771', '772', '773', '774', '775',
		'776', '778', '871', '872', '873', '874', '881', '882', '883', '884', '885']},
	"7": {"name": "Transport equipment", 'codes':['781', '782', '783', '784', '785', '786',
		'791', '792', '793']},
	"8": {"name": "Chemical and plastic products", 'codes':['512', '513', '525', '533', '511',
		'514', '515', '516', '522', '523', '524', '531', '532', '541', '542', '551', '553',
		'554', '562', '571', '572', '573', '574', '575', '579', '581', '582', '583', '591',
		'592', '593', '597', '598', '893']},
	"9": {"name": "N.E.S. (incl. furniture, recycling and publishing and printing )",
		'codes':['811', '812', '813', '899', '894', '895', '896', '897', '898', '821', '891']}
}
isos = {
	'12': 'DZA',
	'48': 'BHR',
	'818': 'EGY',
	'275': 'PSE',
	'364': 'IRN',
	'368': 'IRQ',
	'376': 'ISR',
	'400': 'JOR',
	'414': 'KWT',
	'422': 'LBN',
	'434': 'LBY',
	'504': 'MAR',
	'512': 'OMN',
	'682': 'SAU',
	'760': 'SYR',
	'788': 'TUN',
	'784': 'ARE',
	'887': 'YEM'
}
fltrd = {
	'682': 'Saudi Arabia',
	'760': 'Syria',
	'788': 'Tunisia',
	'784': 'United Arab Emirates',
	'887': 'Yemen'
}
#--------------------------------- backup
def backup(typ):
	timestamp = str(datetime.datetime.now()).split('.')
	no_ms = timestamp[0] # remove milliseconds
	day, time = no_ms.split(' ')
	if typ == 'comtrade':
		folders = ('dataframe',)
	else:
		folders = ('mht_by_manuf', 'primary_vs_manuf', 'trends_by_sector', 'trends_by_tech')
	out['output'] = []
	destfolder = '../trade/backup/'+day
	try:
		if not os.path.isdir(destfolder):
			os.makedirs(destfolder)
		for folder in folders:
			command = 'zip -r '+destfolder+'/'+folder+'.zip ../trade/'+folder+'/'
			popen = os.popen(command)
			out['output'] += popen.readlines()
			status = popen.close()
			if status != None:
				break
		if status is None:
			out['status'] = 'success'
		else:
			out['status'] = status
	except:
		out['status'] = 'error'
		out['error'] = str(sys.exc_info()[0])
#------------------------------------- fetchComtradeAPIData((r='400', rg='2', cc='TOTAL')
# Use Comtrade API (https://comtrade.un.org/data/doc/api/#DataRequests) to fetch data directly (via URL) to a DataFrame
#
# Export all 3 digit codes - https://comtrade.un.org/api/get?r=400&px=S3&ps=ALL&p=0&rg=2&cc=AG3&fmt=CSV&type=C&freq=A
# Export Total - https://comtrade.un.org/api/get?r=400&px=S3&ps=ALL&p=0&rg=2&cc=TOTAL&fmt=CSV&type=C&freq=A
# Re-export all 3 digit codes - https://comtrade.un.org/api/get?r=400&px=S3&ps=ALL&p=0&rg=3&cc=AG3&fmt=CSV&type=C&freq=A
# Re-export Total - https://comtrade.un.org/api/get?r=400&px=S3&ps=ALL&p=0&rg=3&cc=TOTAL&fmt=CSV&type=C&freq=A
#
# r: reporting area - https://comtrade.un.org/Data/cache/reporterAreas.json
# rg: trade regime (2: exports, 3: re-exports)
# cc: classification code (AG3: 3 digit SITC Rev 3, TOTAL: total)
def fetchComtradeAPIData(r='400', rg='2', cc='TOTAL'):
	url = 'https://comtrade.un.org/api/get?r='+r+'&px=S3&ps=ALL&p=0&rg='+rg+'&cc='+cc+'&fmt=CSV&type=C&freq=A'
	#print(url)
	try:
		df = pd.read_csv(url, dtype={code_col: str})
		df[year_col]=df[year_col].astype(str)
		return df
	except:
		out['status'] = 'error'
		out['error'] = str(sys.exc_info()[0])
		return None
#------------------------------------- saveIfOk
def saveIfOk(sizes, df, country_title, filename):
	if df is not None:
		read_size = df.shape[0]
		exp_size = sizes[country_title][filename]
		if (read_size == 1) and (exp_size > 1):
			out['status'] = 'error'
			out['error'] = filename+' for '+country_title+' should not be empty'
			return False
		else:
			toCSV(df, '../trade/dataframe/'+country_title+'/'+filename)
			return True
	else:
		return False # no need to set out['status'] as reaching this point means it has been already set by fetchComtradeAPIData
#------------------------------------- fetchExportData(country)
def fetchExportData(country):
	#global df_exports_tot, df_exports_3_dgt, df_reexports_tot, df_reexports_3_dgt
	sizes = json.load(open('dataframe_sizes.json'))
	out['status'] = 'success'
	if country != 'none':
		country_title = countries[country]
		if (not os.path.isdir('../trade/dataframe/'+country_title)):
			os.makedirs('../trade/dataframe/'+country_title)
		df_exports_tot = fetchComtradeAPIData(country, '2')
		if saveIfOk(sizes, df_exports_tot, country_title, 'exports_tot.csv'):
			df_exports_3_dgt = fetchComtradeAPIData(country, '2', 'AG3')
			if saveIfOk(sizes, df_exports_3_dgt, country_title, 'exports_3_dgt.csv'):
				df_reexports_tot = fetchComtradeAPIData(country, '3')
				if saveIfOk(sizes, df_reexports_tot, country_title, 'reexports_tot.csv'):
					df_reexports_3_dgt = fetchComtradeAPIData(country, '3', 'AG3')
					saveIfOk(sizes, df_reexports_3_dgt, country_title, 'reexports_3_dgt.csv')
#------------------------------------- fetchImportData(country)
def fetchImportData(country):
	#global df_imports_tot, df_imports_3_dgt, df_reimports_tot, df_reimports_3_dgt
	sizes = json.load(open('dataframe_sizes.json'))
	out['status'] = 'success'
	if country != 'none':
		country_title = countries[country]
		if (not os.path.isdir('../trade/dataframe/'+country_title)):
			os.makedirs('../trade/dataframe/'+country_title)
		df_imports_tot = fetchComtradeAPIData(country, '1')
		if saveIfOk(sizes, df_imports_tot, country_title, 'imports_tot.csv'):
			df_imports_3_dgt = fetchComtradeAPIData(country, '1', 'AG3')
			if saveIfOk(sizes, df_imports_3_dgt, country_title, 'imports_3_dgt.csv'):
				df_reimports_tot = fetchComtradeAPIData(country, '4')
				if saveIfOk(sizes, df_reimports_tot, country_title, 'reimports_tot.csv'):
					df_reimports_3_dgt = fetchComtradeAPIData(country, '4', 'AG3')
					saveIfOk(sizes, df_reimports_3_dgt, country_title, 'reimports_3_dgt.csv')
#------------------------------------- getFltrd(df_3_dgt, fltr_on)
def getFltrd(df_3_dgt, fltr_on):
	df_fltr = df_3_dgt[df_3_dgt[code_col].isin(fltr_on)]
	return df_fltr[[year_col, value_col]].groupby(year_col).sum()
#------------------------------------- getPct(df_tot, df_3_dgt, fltr_on, tot)
def getPct(df_tot, df_3_dgt, fltr_on, tot):
	df_fltr_nmrtr = df_3_dgt[df_3_dgt[code_col].isin(fltr_on)]
	# TOTAL
	if tot == 'TOTAL':
		df_fltr_denom = df_tot
	else:
		df_fltr_denom = df_3_dgt[df_3_dgt[code_col].isin(tot)]
	global df_nmrtr, df_denom
	df_nmrtr = df_fltr_nmrtr[[year_col, value_col]].groupby(year_col).sum()
	df_denom = df_fltr_denom[[year_col, value_col]].groupby(year_col).sum()
	# group and sum
	df_pct = df_nmrtr/df_denom * 100
	return df_nmrtr, df_denom, df_pct
################################ FIGURE 1: Manufactured Goods against Primary Goods ########################################
#------------------------------------- getFigure1Data(country)
def getFigure1Data(country):
	global df_exports_tot, df_exports_3_dgt, df_reexports_tot, df_reexports_3_dgt
	folder = '../trade/dataframe/'+countries[country]+'/'
	df_exports_tot = pd.read_csv(folder+'exports_tot.csv')
	df_exports_3_dgt = pd.read_csv(folder+'exports_3_dgt.csv', dtype={code_col: str})
	df_reexports_tot = pd.read_csv(folder+'reexports_tot.csv')
	df_reexports_3_dgt = pd.read_csv(folder+'reexports_3_dgt.csv', dtype={code_col: str})
#------------------------------------- computeFigure1All(fltr_on, tot)
def computeFigure1All(fltr_on, tot):
	df_nmrtr_exports, df_denom_exports, df_pct_exports = getPct(df_exports_tot, df_exports_3_dgt, fltr_on, tot)
	if df_reexports_tot.shape[0] > 1:
		df_nmrtr_reexports, df_denom_reexports, df_pct_reexports = getPct(df_reexports_tot, df_reexports_3_dgt, fltr_on, tot)
		df_pct_netexports = df_nmrtr_exports.subtract(df_nmrtr_reexports, fill_value=0)/df_denom_exports.subtract(df_denom_reexports, fill_value=0) * 100
	else:
		df_pct_reexports = None
		df_pct_netexports = df_pct_exports
	return df_pct_exports, df_pct_reexports, df_pct_netexports
#------------------------------------- saveFigure1Files
def saveFigure1Files(country):
	iso = isos[country]
	getFigure1Data(country)
	out['status'] = 'success'
	df_pct_exports_prmry_vs_tot, df_pct_reexports_prmry_vs_tot, df_pct_netexports_prmry_vs_tot =
		computeFigure1All(A, 'TOTAL')
	df_pct_exports_manuf_vs_tot, df_pct_reexports_manuf_vs_tot, df_pct_netexports_manuf_vs_tot =
		computeFigure1All(manuf, 'TOTAL')
	dest='../trade/primary_vs_manuf'
	folder = dest+'/'+iso
	if (not os.path.isdir(folder)):
		os.makedirs(folder)
	toCSV(df_pct_exports_prmry_vs_tot, folder+'/pct_exports_prmry_vs_tot.csv', True) # index=True
	toCSV(df_pct_exports_manuf_vs_tot, folder+'/pct_exports_manuf_vs_tot.csv', True)
	if (df_pct_reexports_prmry_vs_tot is not None) and (df_pct_reexports_prmry_vs_tot.shape[0] > 1):
		toCSV(df_pct_reexports_prmry_vs_tot, folder+'/pct_reexports_prmry_vs_tot.csv', True)
		toCSV(df_pct_netexports_prmry_vs_tot, folder+'/pct_netexports_prmry_vs_tot.csv', True)
		toCSV(df_pct_reexports_manuf_vs_tot, folder+'/pct_reexports_manuf_vs_tot.csv', True)
		toCSV(df_pct_netexports_manuf_vs_tot, folder+'/pct_netexports_manuf_vs_tot.csv', True)
################################ Figure 2 ######################################
#------------------------------------- getFigure2Data(country)
def getFigure2Data(country):
	global df_exports_tot, df_exports_3_dgt, df_reexports_tot, df_reexports_3_dgt
	folder = '../trade/dataframe/'+countries[country]+'/'
	df_exports_tot = pd.read_csv(folder+'exports_tot.csv')
	df_exports_3_dgt = pd.read_csv(folder+'exports_3_dgt.csv', dtype={code_col: str})
	df_reexports_tot = pd.read_csv(folder+'reexports_tot.csv')
	df_reexports_3_dgt = pd.read_csv(folder+'reexports_3_dgt.csv', dtype={code_col: str})
#------------------------------------- computeFigure2
def computeFigure2(fltr_on, tot):
	df_nmrtr_exports, df_denom_exports, df_pct_exports = getPct(df_exports_tot, df_exports_3_dgt, fltr_on, tot)
	if df_reexports_tot.shape[0] > 1:
		df_nmrtr_reexports, df_denom_reexports, df_pct_reexports = getPct(df_reexports_tot, df_reexports_3_dgt, fltr_on, tot)
		df_pct_netexports = df_nmrtr_exports.subtract(df_nmrtr_reexports, fill_value=0)/df_denom_exports.subtract(df_denom_reexports, fill_value=0) * 100
	else:
		df_pct_reexports = None
		df_pct_netexports = df_pct_exports
	return df_pct_exports, df_pct_reexports, df_pct_netexports
#------------------------------------- computeFigure2Group
def computeFigure2Group(fltrd):
	figure2 = {}
	# First get the data sets for each country, then compute the division (MHT/manuf) and finally rename their "Trade Value (US$)" columns to country name
	for country, iso in fltrd.items():
		#print(country, iso)
		getFigure2Data(country)
		df_exports_mht_vs_manuf, df_reexports_mht_vs_manuf, df_netexports_mht_vs_manuf =
			computeFigure2(MHT, manuf)
		df_exports_mht_vs_manuf.rename(columns={"Trade Value (US$)": fltrd[country]}, inplace=True)
		if isinstance(df_reexports_mht_vs_manuf, pd.DataFrame) and (df_reexports_mht_vs_manuf.shape[0]>1):
			df_reexports_mht_vs_manuf.rename(columns={"Trade Value (US$)": fltrd[country]}, inplace=True)
			df_netexports_mht_vs_manuf.rename(columns={"Trade Value (US$)": fltrd[country]}, inplace=True)
		else:
			df_reexports_mht_vs_manuf = None
			df_netexports_mht_vs_manuf = df_exports_mht_vs_manuf
		figure2[country] = {
			'df_exports_mht_vs_manuf': df_exports_mht_vs_manuf,
			'df_reexports_mht_vs_manuf': df_reexports_mht_vs_manuf,
			'df_netexports_mht_vs_manuf': df_netexports_mht_vs_manuf
		}
	# Now combine the datasets for each country for chart
	keys = list(figure2.keys())
	keys.reverse
	key = keys.pop()
	df_exports_mht_vs_manuf = figure2[key]['df_exports_mht_vs_manuf']
	df_reexports_mht_vs_manuf = figure2[key]['df_reexports_mht_vs_manuf']
	df_netexports_mht_vs_manuf = figure2[key]['df_netexports_mht_vs_manuf']
	for key in keys:
		df_exports_mht_vs_manuf = df_exports_mht_vs_manuf.join(figure2[key]['df_exports_mht_vs_manuf'], how='outer')
		df_netexports_mht_vs_manuf = df_netexports_mht_vs_manuf.join(figure2[key]['df_netexports_mht_vs_manuf'], how='outer')
		df_reexports_mht_vs_manuf = joinDataframes(df_reexports_mht_vs_manuf, figure2[key]['df_reexports_mht_vs_manuf'])
		#if isinstance(figure2[key]['df_reexports_mht_vs_manuf'], pd.DataFrame) and
		#	(figure2[key]['df_reexports_mht_vs_manuf'].shape[0]>1):
		#	df_reexports_mht_vs_manuf = df_reexports_mht_vs_manuf.
		#		join(figure2[key]['df_reexports_mht_vs_manuf'], how='outer')
	return df_exports_mht_vs_manuf, df_reexports_mht_vs_manuf, df_netexports_mht_vs_manuf
#------------------------------------- joinDataFrames(df1, df2)
def joinDataframes(df1, df2):
	df1_ok = isinstance(df1, pd.DataFrame) and (df1.shape[0] > 1)
	df2_ok = isinstance(df2, pd.DataFrame) and (df2.shape[0] > 1)
	if df1_ok and df2_ok:
		return df1.join(df2, how='outer')
	elif df1_ok:
		return df1
	elif df2_ok:
		return df2
	else:
		return None
#------------------------------------- saveFigure2Files
def saveFigure2Files():
	out['status'] = 'success'
	df_exports_mht_vs_manuf, df_reexports_mht_vs_manuf, df_netexports_mht_vs_manuf = computeFigure2Group(isos)
	folder='../trade/mht_by_manuf'
	if (not os.path.isdir(folder)):
		os.makedirs(folder)
	toCSV(df_exports_mht_vs_manuf.T, folder+'/exports_mht_vs_manuf.csv', 'iso')
	toCSV(df_reexports_mht_vs_manuf.T, folder+'/reexports_mht_vs_manuf.csv', 'iso')
	toCSV(df_netexports_mht_vs_manuf.T, folder+'/netexports_mht_vs_manuf.csv', 'iso')
################################ Figure 3: #####################################
#------------------------------------- getFigure3Data(country)
def getFigure3Data(country):
	global df_exports_3_dgt, df_reexports_3_dgt
	folder = '../trade/dataframe/'+countries[country]+'/'
	out['status'] = 'success'
	df_exports_3_dgt = pd.read_csv(folder+'exports_3_dgt.csv', dtype={code_col: str})
	df_reexports_3_dgt = pd.read_csv(folder+'reexports_3_dgt.csv', dtype={code_col: str})
#------------------------------------- computeFigure3Group(fltrd=countries)
def computeFigure3Group(fltrd=countries):
	figure3 = {}
	categories = {'RB': RB, 'LT': LT, 'MT': MT, 'HT': HT}
	for country in fltrd:
		figure3[country]={ 'exports': {}, 'reexports': {}, 'netexports': {}}
		getFigure3Data(country)
		for key, category in categories.items():
			figure3[country]['exports'][key] = getFltrd(df_exports_3_dgt, category)
			if df_reexports_3_dgt.shape[0] > 1:
				figure3[country]['reexports'][key] = getFltrd(df_reexports_3_dgt, category)
				figure3[country]['netexports'][key] = getFltrd(df_exports_3_dgt, category).
					subtract(getFltrd(df_reexports_3_dgt, category))
			else:
				figure3[country]['reexports'][key] = None
				figure3[country]['netexports'][key] = getFltrd(df_exports_3_dgt, category)
	return figure3
#------------------------------------- saveFigure3Files
def saveFigure3Files():
	figure3 = computeFigure3Group(isos)
	tech_sctrs = ['RB', 'LT', 'MT', 'HT']
	out['status'] = 'success'
	for country, datahash in figure3.items():
		iso = isos[country]
		#print(iso)
		for typ in datahash.keys():
			if datahash[typ]['RB'] is not None:
				#print(typ)
				dest='../trade/trends_by_tech'
				folder = dest+'/'+iso+'/'+typ
				if (not os.path.isdir(folder)):
					os.makedirs(folder)
				for tech_sctr in tech_sctrs:
					toCSV(datahash[typ][tech_sctr], folder+'/'+tech_sctr+'.csv', True)
					#datahash[typ][tech_sctr].to_csv(folder+'/'+tech_sctr+'.csv')
################################ Figure 4: #####################################
#------------------------------------- getFigure4Data(country)
def getFigure4Data(country):
	global df_exports_3_dgt, df_reexports_3_dgt, df_imports_3_dgt, df_reimports_3_dgt
	folder = '../trade/dataframe/'+countries[country]+'/'
	df_exports_3_dgt = pd.read_csv(folder+'exports_3_dgt.csv', dtype={code_col: str})
	df_reexports_3_dgt = pd.read_csv(folder+'reexports_3_dgt.csv', dtype={code_col: str})
	df_imports_3_dgt = pd.read_csv(folder+'imports_3_dgt.csv', dtype={code_col: str})
	df_reimports_3_dgt = pd.read_csv(folder+'reimports_3_dgt.csv', dtype={code_col: str})
#------------------------------------- computeFigure4Group(fltrd=countries)
def computeFigure4Group(fltrd=countries):
	figure4 = {}
	categories = {}
	for category in one_dgt.keys():
		categories[category] = one_dgt[category]['codes']
	for country in fltrd:
		figure4[country]={ 'exports': {}, 'reexports': {}, 'netexports': {},
			'imports': {}, 'reimports': {}, 'netimports': {}}
		getFigure4Data(country)
		for key, category in categories.items():
			figure4[country]['exports'][key] = getFltrd(df_exports_3_dgt, category)
			if df_reexports_3_dgt.shape[0] > 1:
				figure4[country]['reexports'][key] = getFltrd(df_reexports_3_dgt, category)
				figure4[country]['netexports'][key] = getFltrd(df_exports_3_dgt, category).
					subtract(getFltrd(df_reexports_3_dgt, category))
			else:
				figure4[country]['reexports'][key] = None
				figure4[country]['netexports'][key] = getFltrd(df_exports_3_dgt, category)
			figure4[country]['imports'][key] = getFltrd(df_imports_3_dgt, category)
			if df_reimports_3_dgt.shape[0] > 1:
				figure4[country]['reimports'][key] = getFltrd(df_reimports_3_dgt, category)
				figure4[country]['netimports'][key] = getFltrd(df_imports_3_dgt, category).
					subtract(getFltrd(df_reimports_3_dgt, category))
			else:
				figure4[country]['reimports'][key] = None
				figure4[country]['netimports'][key] = getFltrd(df_imports_3_dgt, category)
	return figure4
#------------------------------------- saveFigure4Files
def saveFigure4Files():
	out['status'] = 'success'
	figure4 = computeFigure4Group(isos)
	one_dgt_sctrs = list(map(str, range(1,10)))
	for country, datahash in figure4.items():
		iso = isos[country]
		#print(iso)
		for typ in datahash.keys():
			if datahash[typ]['1'] is not None:
				#print(typ)
				dest='../trade/trends_by_sector'
				folder = dest+'/'+iso+'/'+typ
				if (not os.path.isdir(folder)):
					os.makedirs(folder)
				for sctr in one_dgt_sctrs:
					toCSV(datahash[typ][sctr], folder+'/'+sctr+'.csv', True)
					#datahash[typ][sctr].to_csv(folder+'/'+sctr+'.csv')
#------------------------------------- toCSV
def toCSV(df, fil, index=False):
	if isinstance(df, pd.DataFrame):
		checkAndRemove(fil)
		if type(index) is bool:
			df.to_csv(fil, index=index)
		else: # if index is not True/False it is the index_label
			df.to_csv(fil, index_label=index)
#------------------------------------- checkAndRemove(fil)
def checkAndRemove(fil):
	if os.path.exists(fil):
		os.remove(fil)
#------------------------------------- saveStatus()
def saveStatus():
	status = {}
	status['status'] = form.getvalue('status', 'none')
	status['date'] = str(datetime.datetime.today()).split(' ')[0]
	if not status['status'] in ('none', 'success'):
		status['country'] = form.getvalue('country', 'none')
	try:
		json.dump(status, open('comtrade_status.json','w'))
	except:
		out['status'] = 'error'
		out['error'] = str(sys.exc_info()[0])
#--------------------------------- MAIN
print("Content-type: text/json")
print()
form = cgi.FieldStorage(keep_blank_values=True)
action = form.getvalue('action', 'none')
#folder = form.getvalue('folder','../instance')
out= { 'action': action }
if action == 'backup_comtrade':
	backup('comtrade')
elif action == 'backup_chartdata':
	backup('chartdata')
elif action == 'fetch_exports':
	country = form.getvalue('country', 'none')
	out['country'] = country
	fetchExportData(country)
elif action == 'fetch_imports':
	country = form.getvalue('country', 'none')
	out['country'] = country
	fetchImportData(country)
elif action == 'fig1':
	country = form.getvalue('country', 'none')
	out['country'] = country
	saveFigure1Files(country)
elif action == 'fig2':
	saveFigure2Files()
elif action == 'fig3':
	saveFigure3Files()
elif action == 'fig4':
	saveFigure4Files()
elif action == 'save_status':
	saveStatus()
else:
	out['error'] = "No valid action specified: "+action
print(json.dumps(out, indent=3))

UNIDO

#!/home/huzaifa/anaconda3/bin/python3
#/usr/bin/python3
# Name: update_unido.py
# Created By: Huzaifa Zoomkawala
# Last Modified: September 4, 2021
import cgi
import cgitb; cgitb.enable()  # for troubleshooting
import datetime
import os
import os.path
import time
import json
import pandas as pd
import shutil
import sys
#------------------------------------ Global Variables
colset = {
	'indstat': {'Table Code', 'Country Code', 'Year', 'ISIC', 'ISIC Combination', 'Value', 'Table Definition Code', 'Source Code', 'Unit'},
	'mva': {'Variable Code', 'Country Code', 'Year', 'Value', 'Unit'}
}
varcols = { 'indstat': 'Table Code', 'mva': 'Variable Code' }
varset = {
	'indstat': {'01', '04', '20', '31'},
	'mva': {'GdpCud', 'MvaCud', 'GdpCod', 'MvaCod', 'Pop'}
}
units = { '#', '$'}
minsize = { 'indstat2' : 50e3, 'indstat4': 100e3, 'indstat4_4': 48e3, 'mva': 3e3 }
countries = {
	'indstat': {'048', '368', '422', '504', '414', '012', '434', '760', '792', '682',
	'275', '512', '887', '784', '400', '788', '376', '818', '364', '634'},
	'mva': {'048', '368', '422', '504', '414', '012', '434', '760', '792', 'WOR',
		'682', '275', '512', '887', '784', '400', '788', '376', '818', '364', '634'}
}
isic_set = {
	'indstat2': {'22', '26', '25', '33', '19', '36', '29', '34', '35', '15', '20',
	'30', '16', '37', 'D', '28', '23', '27', '18', '31', '32', '24', '21', '17'},
	'indstat4_4': {'2811', '2520', '3319', '1702', '1062', '1075', '1313', '2310',
	'3091', '1061', '2652', '2750', '239', '2030', '2660', '2815', '2610', '2910',
	'1623', '1104', '2591', '2431', '321', '1511', '2393', '1071', '243', '2930',
	'1512', '162', '251', '2211', '2816', '3012', '309', '2821', '1920', '301',
	'2100', '259', '2822', '1010', '1073', '3020', '1311', '2731', '2710', '273',
	'2732', '1391', '1811', '2012', '3290', '1080', '3311', '3211', '2513', '2511',
	'281', '2029', '106', '3212', '1520', '3250', '3312', '3313', '3092', '1709',
	'2817', '1050', '2512', '2640', '1910', '2396', '170', '2219', '1102', '181',
	'1072', '1393', '1200', '1410', '131', '139', '3011', '1622', '3100', '2825',
	'2392', '2733', '1101', '282', '110', '2021', '2395', '1392', '1040', '2829',
	'2399', '3030', '1621', '2630', '1312', '3230', '331', '2818', '2013', '151',
	'2599', '221', '201', '2819', '1430', '3320', '2011', '1610', '2432', '2790',
	'2420', 'C', '1820', '2220', '1394', '3220', '2826', '1103', '1812', '2391',
	'2920', '3315', '1020', '2022', '1079', '2410', '3240', '2720', '3040', '3099',
	'1420', '3314', '2620', '2813', '1629', '2812', '107', '1701', '2651', '2823',
	'2592', '2593', '2824', '2023', '1030', '265', '2680', '202', '1074', '1399',
	'2394', '2740', '2670', '2814'},
	'indstat4': {'2694', '1553', '3430', '2924', '2811', '1532', '2520', '3190',
	'2925', '2310', '2610', '2330', '2413', '2919', '1511', '2930', '1512', '251',
	'3693', '2211', '242', '2926', 'D', '1920', '3511', '1911', '2109', '1730',
	'1810', '2731', '191', '2913', '2710', '273', '2732', '3520', '1711', '3610',
	'3311', '3140', '3120', '359', '2511', '3591', '3699', '281', '292', '210',
	'1549', '1721', '2029', '1722', '1552', '1520', '2320', '2693', '3312', '3313',
	'2102', '3530', '241', '3210', '3599', '2691', '2915', '369', '2219', '2922',
	'153', '2912', '2101', '3130', '2230', '2222', '2927', '2421', '1729', '1531',
	'1543', '3420', '3692', '2010', '3710', '2021', '2411', '2412', '2929', '3694',
	'222', '2892', '3230', '331', '151', '1912', '1600', '221', '2914', '172',
	'3320', '2692', '2696', '3512', '3150', '1820', '1723', '2921', '2891',
	'1514', '3220', '2422', '2429', '3720', '2695', '3410', '1533', '1712', '2423',
	'3000', '1541', '3592', '2022', '2893', '2430', '2923', '2424', '3691', '2221',
	'2519', '2720', '269', '1551', '351', '3330', '1554', '2212', '2813', '2899',
	'155', '171', '2812', '1542', '154', '2023', '1513', '291', '202', '2699',
	'3110', '289', '2911', '1544', '2213'}
}
# for convertTech and related routines
isic_indstat = dict(
	indstat2 = dict(
		lt = ['15', '16', '17', '18', '19', '20', '21', '22', '36', '37'],
		ml = ['23', '25', '26', '27', '28'],
		mht = ['24', '29', '30', '31', '32', '33', '34'],
		lt_incl = [],
		ml_incl = [],
		mht_incl = [],
		lt_excl = [],
		ml_excl = [],
		mht_excl = []
	),
	indstat4 = dict(
		lt = ['15', '16', '17', '18', '19', '20', '21', '22', '36', '37'],
		ml = ['23', '25', '26', '27', '28'],
		mht = ['24', '29', '30', '31', '32', '33', '34', '35'],
		lt_incl = [],
		ml_incl = ['351'],
		mht_incl = [],
		lt_excl = [],
		ml_excl = [],
		mht_excl = ['351']
	),
	indstat4_4 = dict(
		lt = ['10', '11', '12', '13', '14', '15', '16', '17', '18', '25', '31'],
		ml = ['22', '23', '24', '32', '33'],
		mht = ['20', '21', '26', '27', '28', '29', '30'],
		lt_incl = [],
		ml_incl = ['301'],
		mht_incl = ['252', '325'],
		lt_excl = ['252'],
		ml_excl = ['325'],
		mht_excl = ['301']
	)
)
#------------------------------------ checkDB
def checkDB(dbtype, df):
	out['status'] = 'error'
	out['error'] = 'unspecified'
	if dbtype.startswith('indstat'):
		refcols = colset['indstat']
		varcol = varcols['indstat']
		varlist = varset['indstat']
		refcountries = countries['indstat']
	else:
		refcols = colset['mva']
		varcol = varcols['mva']
		varlist = varset['mva']
		refcountries = countries['mva']
	cols = set(df.columns)
	if refcols.issubset(cols):
		if minsize[dbtype] < df.shape[0]: df_countries = set(df['Country Code']) if dbtype == 'indstat4_4': countries_cond = df_countries.issubset(refcountries) else: countries_cond = (df_countries == refcountries) if countries_cond: df_vars = set(df[varcol]) if df_vars == varlist: df_units = set(df['Unit']) if df_units == units: if dbtype == 'mva': out['status'] = 'success' else: df_isics = set(df['ISIC']) refisics = isic_set[dbtype] len_common = len(refisics.intersection(df_isics)) len_all = len(refisics) pct_intersect = round(len_common/len_all * 100) if pct_intersect > 85:
								out['status'] = 'success'
							else:
								out['error'] = 'ISIC codes in data file do not match those for '+dbtype
					else:
						out['error'] =	'Unit for data file is not correct. Make sure US Dollar is chosen instead of Local Currency'
				else:
					out['error'] =	'Variables in data file are not chosen according to specification for '+dbtype
			else:
				if dbtype == 'mva':
					out['error'] = 'Countries in data set should only be from MENA region or the World (as a region)'
				else:
					out['error'] = 'Countries in data set should only be from MENA region'
		else:
			out['error'] =	f'Too few rows in data file: Expected minimum {minsize[dbtype]:,.0f} and found {df.shape[0]:,.0f}'
	else:
		out['error'] = 'Columns in data file not correct: please check specification for '+dbtype
#--------------------------------- saveAsCSV
def saveAsCSV(dbtype, df):
	df_country = pd.read_csv('country.csv', dtype={'numeric': str})
	df_country.set_index('numeric', inplace=True)
	out['status'] == 'success'
	df_with_iso = df.merge(df_country, left_on='Country Code', right_on=df_country.index, how='left')
	if dbtype == 'mva':
		df_mva = df_with_iso[['Year', 'iso', 'Variable Code', 'Value']]
		df_mva.rename(columns={'Year': 'year', 'Variable Code': 'var', 'Value': 'value'}, inplace=True)
		# recode 'var/Variable Code'
		df_mva.loc[df_mva['var']=='GdpCud', 'var']='gdp'
		df_mva.loc[df_mva['var']=='MvaCud', 'var']='mva'
		df_mva.loc[df_mva['var']=='GdpCod', 'var']='gdpcons'
		df_mva.loc[df_mva['var']=='MvaCod', 'var']='mvacons'
		df_mva.loc[df_mva['var']=='Pop', 'var']='pop'
		df_mva.to_csv(datafolder+'/mva.csv', index=False)
	else: # indstat2 or indstat4 or indstat4_4
		if dbtype == 'indstat2':
			isic = 'isic2'
		else:
			isic = 'isic4'
		df_indstat = df_with_iso[['Year', 'iso', 'Table Code', 'ISIC', 'Value']]
		df_indstat.rename(columns={'Year': 'year', 'Table Code': 'var', 'ISIC': isic, 'Value': 'value'}, inplace=True)
		# recode 'var/Variable Code'
		df_indstat.loc[df_indstat['var']=='01','var']='est'
		df_indstat.loc[df_indstat['var']=='04','var']='emp'
		df_indstat.loc[df_indstat['var']=='20','var']='va'
		df_indstat.loc[df_indstat['var']=='31','var']='fem'
		df_indstat.to_csv(datafolder+'/'+dbtype+'.csv', index=False)
#--------------------------------- listFiles
def listFiles():
	out['files'] = { fil.split('.csv')[0]: time.strftime('%m/%d/%Y', time.gmtime(os.path.getmtime(datafolder+'/'+fil)))
		for fil in filter(lambda x: x.endswith("csv"), os.listdir(datafolder)) }
	out['status'] = 'success'
#--------------------------------- get_specifics (for convertTech)
def get_specifics(indstat, group):
	if indstat == '2':
		df_indstat = df_indstat2
		overall_group = 'D'
		isic_col = 'isic2'
		isic_maxlen = 2
	elif indstat == '4':
		df_indstat = df_indstat4
		overall_group = 'D'
		isic_col = 'isic4'
		isic_maxlen = 4
	else: # indstat == '4_4':
		df_indstat = df_indstat4_4
		overall_group = 'C'
		isic_col = 'isic4'
		isic_maxlen = 4
	if group == 'overall':
		cond = df_indstat[isic_col] == overall_group
		cond_add = None
	else: # group == 'lt' or 'mt' or 'mht':
		cond = df_indstat[isic_col].str[:2].isin(isic_indstat['indstat'+indstat][group]) &
			   (df_indstat[isic_col].str.len() == isic_maxlen) &
			   ~df_indstat[isic_col].str[:3].isin(isic_indstat['indstat'+indstat][group+'_excl'])
		cond_add = df_indstat[isic_col].isin(isic_indstat['indstat'+indstat][group+'_incl'])
	return df_indstat, cond, cond_add, isic_col
#--------------------------------- calc_shr_add_indemp (for ConvertTech)
### additional (included) ISICs
def calc_shr_add_indemp(df_indstat, cond_add, isic_col, ind='fem'):
	df_add_ind = df_indstat[(df_indstat['iso'] != 'WLD') & (df_indstat['var'] == ind) & cond_add]
	df_add_emp = df_indstat[(df_indstat['iso'] != 'WLD') & (df_indstat['var'] == 'emp') & cond_add]
	df_add_ind.rename(columns = {isic_col:'isic'}, inplace = True)
	df_add_emp.rename(columns = {isic_col:'isic'}, inplace = True)
	df_add_ind = df_add_ind[['year', 'iso', 'isic', 'value']].set_index(['year', 'iso', 'isic'])
	df_add_emp = df_add_emp[['year', 'iso', 'isic', 'value']].set_index(['year', 'iso', 'isic'])
	return df_add_ind, df_add_emp
#----------------------------------- makeTSCopy
def makeTSCopy(folder, filename):
	timestamp = str(datetime.datetime.now()).split('.')
	no_ms = timestamp[0] # remove milliseconds
	day, time = no_ms.split(' ')
	minute = ':'.join(time.split(':')[:2])
	dest = folder+'/backup/'+day+' '+minute
	if not os.path.isdir(dest):
		os.makedirs(dest)
	if os.path.isfile(folder+'/'+filename):
		shutil.copyfile(folder+'/'+filename, dest+'/'+filename)
#--------------------------------- calc_shr_indemp (for ConvertTech)
def calc_shr_indemp(indstat, group, ind='fem', pct=1):
	df_indstat, cond, cond_add, isic_col = get_specifics(indstat, group)
	df_ind = df_indstat[(df_indstat['iso'] != 'WLD') & (df_indstat['var'] == ind) & cond]
	df_emp = df_indstat[(df_indstat['iso'] != 'WLD') & (df_indstat['var'] == 'emp') & cond]
	if group == 'overall':
		fields_include = ['year', 'iso', 'value']
		fields_index = ['year', 'iso']
		df_ind = df_ind[fields_include].set_index(fields_index)
		df_emp = df_emp[fields_include].set_index(fields_index)
	else:
		df_ind['isic'] = df_ind[isic_col].str[:2]
		df_ind = df_ind.groupby(['year', 'iso', 'isic']).sum()
		df_emp['isic'] = df_emp[isic_col].str[:2]
		df_emp = df_emp.groupby(['year', 'iso', 'isic']).sum()
	df_shr_indemp = ((df_ind/df_emp) * pct).dropna()
	if group != 'overall':
		df_add_ind, df_add_emp = calc_shr_add_indemp(df_indstat, cond_add, isic_col, ind)
		if df_add_ind.shape[0] > 0:
			df_shr_indemp = df_shr_indemp.append(((df_add_ind/df_add_emp) * pct).dropna())
			df_ind = df_ind.append(df_add_ind)
			df_emp = df_emp.append(df_add_emp)
		# Add group total
		df_shr_indemp_tot = (df_ind.groupby(['year', 'iso']).sum()/df_emp.groupby(['year', 'iso']).sum()*pct).dropna()
		df_shr_indemp_tot['isic'] = group
		df_shr_indemp_tot.reset_index(inplace=True)
		df_shr_indemp_tot.set_index(['year','iso', 'isic'], inplace=True)
		df_shr_indemp = df_shr_indemp.append(df_shr_indemp_tot)
	destfolder = tblnfolder+'/indstat'+indstat
	if not os.path.isdir(destfolder):
		os.makedirs(destfolder)
	filename = ind+'_emp_'+group+'.csv'
	makeTSCopy(destfolder, filename)
	df_shr_indemp.to_csv(destfolder+'/'+filename)
	#return df_shr_indemp
#--------------------------------- convertTech
def convertTech():
	indstats = ('2', '4', '4_4')
	groups = ('overall', 'lt', 'ml', 'mht')
	for ind in ('va', 'fem'):
		if ind == 'va':
			pct = 1
		else:
			pct = 100
		for indstat in indstats:
			for group in groups:
				calc_shr_indemp(indstat, group, ind, pct)
#--------------------------------- MVA_MVACONS (convertNoTech)
def MVA_MVACONS(destfolder):
	for var in ['mva', 'mvacons']:
		df_mva_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == var)]
		filename = var+'.csv'
		makeTSCopy(destfolder, filename)
		df_mva_fltrd[['year', 'iso', 'value']].to_csv(destfolder+'/'+filename, index=None)
#--------------------------------- MVA_GrowthRate (convertNoTech)
def MVA_GrowthRate(destfolder):
	df_mva_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == 'mvacons')]
	df_mva_fltrd = df_mva_fltrd[['year', 'iso', 'value']].set_index(['year', 'iso'])
	df_growth = (df_mva_fltrd / df_mva_fltrd.shift() * 100).dropna()
	df_growth.reset_index(inplace=True)
	min_year = min(df_growth['year'])
	df_growth_shift = df_growth[df_growth['year']!=min_year]
	filename = 'mva_growth.csv'
	makeTSCopy(destfolder, filename)
	df_growth_shift.to_csv(destfolder+'/'+filename, index=False)
#--------------------------------- GDP_GrowthRate (convertNoTech)
def GDP_GrowthRate(destfolder):
	df_gdp_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == 'gdpcons')]
	df_gdp_fltrd = df_gdp_fltrd[['year', 'iso', 'value']].set_index(['year', 'iso'])
	df_growth = (df_gdp_fltrd / df_gdp_fltrd.shift() * 100).dropna()
	df_growth.reset_index(inplace=True)
	min_year = min(df_growth['year'])
	df_growth_shift = df_growth[df_growth['year']!=min_year]
	filename = 'gdp_growth.csv'
	makeTSCopy(destfolder, filename)
	df_growth_shift.to_csv(destfolder+'/'+filename, index=False)
#--------------------------------- Pop_GrowthRate (convertNoTech)
def Pop_GrowthRate(destfolder):
	df_pop_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == 'pop')]
	df_pop_fltrd = df_pop_fltrd[['year', 'iso', 'value']].set_index(['year', 'iso'])
	df_growth = (df_pop_fltrd / df_pop_fltrd.shift() * 100).dropna()
	df_growth.reset_index(inplace=True)
	min_year = min(df_growth['year'])
	df_growth_shift = df_growth[df_growth['year']!=min_year]
	filename = 'pop_growth.csv'
	makeTSCopy(destfolder, filename)
	df_growth_shift.to_csv(destfolder+'/'+filename, index=False)
#--------------------------------- ShareofMVAinGDP (convertNoTech)
def ShareofMVAinGDP(destfolder):
	df_mva_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == 'mva')]
	df_gdp_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == 'gdp')]
	df_mva_fltrd = df_mva_fltrd[['year', 'iso', 'value']].set_index(['year', 'iso'])
	df_gdp_fltrd = df_gdp_fltrd[['year', 'iso', 'value']].set_index(['year', 'iso'])
	df_shr_mva_gdp = df_mva_fltrd/df_gdp_fltrd * 100
	filename = 'share_mva_gdp.csv'
	makeTSCopy(destfolder, filename)
	df_shr_mva_gdp.to_csv(destfolder+'/'+filename)
#--------------------------------- MVAperCapita (convertNoTech)
def MVAperCapita(destfolder):
	df_mva_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == 'mva')]
	df_pop_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == 'pop')]
	df_mva_fltrd = df_mva_fltrd[['year', 'iso', 'value']].set_index(['year', 'iso'])
	df_pop_fltrd = df_pop_fltrd[['year', 'iso', 'value']].set_index(['year', 'iso'])
	df_mva_pcap = df_mva_fltrd/df_pop_fltrd
	filename = 'mva_pcap.csv'
	makeTSCopy(destfolder, filename)
	df_mva_pcap.to_csv(destfolder+'/'+filename)
#--------------------------------- MVACONSperCapita (convertNoTech)
def MVACONSperCapita(destfolder):
	df_mva_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == 'mvacons')]
	df_pop_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == 'pop')]
	df_mva_fltrd = df_mva_fltrd[['year', 'iso', 'value']].set_index(['year', 'iso'])
	df_pop_fltrd = df_pop_fltrd[['year', 'iso', 'value']].set_index(['year', 'iso'])
	df_mva_pcap = df_mva_fltrd/df_pop_fltrd
	filename = 'mvacons_pcap.csv'
	makeTSCopy(destfolder, filename)
	df_mva_pcap.to_csv(destfolder+'/'+filename)
#--------------------------------- GDPperCapita (convertNoTech)
def GDPperCapita(destfolder):
	df_gdp_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == 'gdp')]
	df_pop_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == 'pop')]
	df_gdp_fltrd = df_gdp_fltrd[['year', 'iso', 'value']].set_index(['year', 'iso'])
	df_pop_fltrd = df_pop_fltrd[['year', 'iso', 'value']].set_index(['year', 'iso'])
	df_gdp_pcap = df_gdp_fltrd/df_pop_fltrd
	filename = 'gdp_pcap.csv'
	makeTSCopy(destfolder, filename)
	df_gdp_pcap.to_csv(destfolder+'/'+filename)
#--------------------------------- GDPCONSperCapita (convertNoTech)
def GDPCONSperCapita(destfolder):
	df_gdp_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == 'gdpcons')]
	df_pop_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == 'pop')]
	df_gdp_fltrd = df_gdp_fltrd[['year', 'iso', 'value']].set_index(['year', 'iso'])
	df_pop_fltrd = df_pop_fltrd[['year', 'iso', 'value']].set_index(['year', 'iso'])
	df_gdp_pcap = df_gdp_fltrd/df_pop_fltrd
	filename = 'gdpcons_pcap.csv'
	makeTSCopy(destfolder, filename)
	df_gdp_pcap.to_csv(destfolder+'/'+filename)
#--------------------------------- ShareWorldMVA (convertNoTech)
def ShareWorldMVA(destfolder):
	df_mva_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == 'mva')]
	df_mva_wld = df_mva[(df_mva['iso'] == 'WLD') & (df_mva['var'] == 'mva')]
	df_shr_mva_wld = pd.DataFrame(columns = ['year', 'iso', 'value'])
	df_mva_wld = df_mva_wld[['year', 'value']].set_index(['year'])
	isos = df_mva_fltrd.iso.unique()
	for iso in isos:
		df_mva_iso = df_mva_fltrd[df_mva_fltrd.iso == iso][['year', 'value']].set_index(['year'])
		df_share_wld_iso = df_mva_iso/df_mva_wld * 100
		df_share_wld_iso['iso'] = iso
		df_share_wld_iso.reset_index(inplace=True)
		df_shr_mva_wld = df_shr_mva_wld.append(df_share_wld_iso)
	filename = 'mva_share_world.csv'
	makeTSCopy(destfolder, filename)
	df_shr_mva_wld.to_csv(destfolder+'/'+filename, index=False)
#--------------------------------- ShareWorldGDP (convertNoTech)
def ShareWorldGDP(destfolder):
	df_gdp_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == 'gdp')]
	df_gdp_wld = df_mva[(df_mva['iso'] == 'WLD') & (df_mva['var'] == 'gdp')]
	df_shr_gdp_wld = pd.DataFrame(columns = ['year', 'iso', 'value'])
	df_gdp_wld = df_gdp_wld[['year', 'value']].set_index(['year'])
	isos = df_gdp_fltrd.iso.unique()
	for iso in isos:
		df_gdp_iso = df_gdp_fltrd[df_gdp_fltrd.iso == iso][['year', 'value']].set_index(['year'])
		df_share_wld_iso = df_gdp_iso/df_gdp_wld * 100
		df_share_wld_iso['iso'] = iso
		df_share_wld_iso.reset_index(inplace=True)
		df_shr_gdp_wld = df_shr_gdp_wld.append(df_share_wld_iso)
	filename = 'gdp_share_world.csv'
	makeTSCopy(destfolder, filename)
	df_shr_gdp_wld.to_csv(destfolder+'/'+filename, index=False)
#--------------------------------- ShareWorldPop (convertNoTech)
def ShareWorldPop(destfolder):
	df_pop_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == 'pop')]
	df_pop_wld = df_mva[(df_mva['iso'] == 'WLD') & (df_mva['var'] == 'pop')]
	df_shr_pop_wld = pd.DataFrame(columns = ['year', 'iso', 'value'])
	df_pop_wld = df_pop_wld[['year', 'value']].set_index(['year'])
	isos = df_pop_fltrd.iso.unique()
	for iso in isos:
		df_pop_iso = df_pop_fltrd[df_pop_fltrd.iso == iso][['year', 'value']].set_index(['year'])
		df_share_wld_iso = df_pop_iso/df_pop_wld * 100
		df_share_wld_iso['iso'] = iso
		df_share_wld_iso.reset_index(inplace=True)
		df_shr_pop_wld = df_shr_pop_wld.append(df_share_wld_iso)
	filename = 'pop_share_world.csv'
	makeTSCopy(destfolder, filename)
	df_shr_pop_wld.to_csv(destfolder+'/'+filename, index=False)
#--------------------------------- MVA_MVACONSperEmp (convertNoTech)
def MVA_MVACONSperEmp(tblnfolder):
	for var in ['mva', 'mvacons']:
		filename = var+'_emp.csv'
		## indstat2
		df_mva_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == var)]
		df_emp_indstat2 = df_indstat2[(df_indstat2['iso'] != 'WLD') & (df_indstat2['var'] == 'emp') &
									  (df_indstat2['isic2'] == 'D')]
		df_emp_indstat2=df_emp_indstat2[['year', 'iso', 'value']].set_index(['year', 'iso'])
		df_mva_fltrd = df_mva_fltrd[['year', 'iso', 'value']].set_index(['year', 'iso'])
		destfolder = tblnfolder+'/indstat2'
		makeTSCopy(destfolder, filename)
		(df_mva_fltrd/df_emp_indstat2).to_csv(destfolder+'/'+filename)
		## indstat4
		df_mva_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == var)]
		df_emp_indstat4 = df_indstat4[(df_indstat4['iso'] != 'WLD') & (df_indstat4['var'] == 'emp') &
									  (df_indstat4['isic4'] == 'D')]
		df_emp_indstat4=df_emp_indstat4[['year', 'iso', 'value']].set_index(['year', 'iso'])
		df_mva_fltrd = df_mva_fltrd[['year', 'iso', 'value']].set_index(['year', 'iso'])
		destfolder = tblnfolder+'/indstat4'
		makeTSCopy(destfolder, filename)
		(df_mva_fltrd/df_emp_indstat4).to_csv(destfolder+'/'+filename)
		## indstat4_4
		df_mva_fltrd = df_mva[(df_mva['iso'] != 'WLD') & (df_mva['var'] == var)]
		df_emp_indstat4_4 = df_indstat4_4[(df_indstat4_4['iso'] != 'WLD') & (df_indstat4_4['var'] == 'emp') &
									  (df_indstat4_4['isic4'] == 'C')]
		df_emp_indstat4_4=df_emp_indstat4_4[['year', 'iso', 'value']].set_index(['year', 'iso'])
		df_mva_fltrd = df_mva_fltrd[['year', 'iso', 'value']].set_index(['year', 'iso'])
		destfolder = tblnfolder+'/indstat4'
		makeTSCopy(destfolder, filename)
		(df_mva_fltrd/df_emp_indstat4_4).to_csv(destfolder+'/'+filename)
#--------------------------------- convertNoTech
def convertNoTech():
	destfolder = tblnfolder+'/mva'
	MVA_MVACONS(destfolder)
	MVA_GrowthRate(destfolder)
	GDP_GrowthRate(destfolder)
	Pop_GrowthRate(destfolder)
	ShareofMVAinGDP(destfolder)
	MVAperCapita(destfolder)
	MVACONSperCapita(destfolder)
	GDPperCapita(destfolder)
	GDPCONSperCapita(destfolder)
	ShareWorldMVA(destfolder)
	ShareWorldGDP(destfolder)
	ShareWorldPop(destfolder)
	MVA_MVACONSperEmp(tblnfolder)
#--------------------------------- transferToDB
def transferToDB():
	command = 'cd '+datafolder+';mysql -ujordanio -pjordaniopwd jordanio < import.sql'
	popen = os.popen(command)
	status = popen.close()
	if status is None:
		out['status'] = 'success'
	else:
		out['status'] = 'error'
		out['error'] = status
#--------------------------------- MAIN
print("Content-type: text/json")
print()
form = cgi.FieldStorage(keep_blank_values=True)
action = form.getvalue('action', 'none')
out= { 'action': action }
datafolder = '../production/data'
tblnfolder = '../production/tabulation'
if action == 'import':
	fileitem = form['datafile']
	try:
		df = pd.read_excel(fileitem.file, dtype={'Table Code': str, 'Country Code': str}, na_values=['...'])
		dbtype = form.getvalue('db_type')
		checkDB(dbtype, df)
		if out['status'] != 'error':
			saveAsCSV(dbtype, df)
	except:
		out['status'] = 'error'
		out['error'] = str(sys.exc_info()[0])
elif action == 'listfiles':
	listFiles()
elif action == 'convert':
	out['status'] = 'success'
	try:
		df_mva = pd.read_csv(datafolder+'/'+'mva.csv')
		df_indstat2 = pd.read_csv(datafolder+'/'+'indstat2.csv')
		df_indstat4 = pd.read_csv(datafolder+'/'+'indstat4.csv')
		df_indstat4_4 = pd.read_csv(datafolder+'/'+'indstat4_4.csv')
		convertTech()
		convertNoTech()
		transferToDB()
	except:
		out['status'] = 'error'
		out['error'] = str(sys.exc_info()[0])
else:
	out['error'] = "No valid action specified: "+action
print(json.dumps(out, indent=3))

The above Python scripts create files in certain folders. If the Observatory application is being initialized from scratch on a new host, those folders (and their descendants) need to have write permissions set as follows:

chmod -R 777 /var/www/wordpress/jordan/prototype/production
chmod -R 777 /var/www/wordpress/jordan/prototype/scripts
chmod -R 777 /var/www/wordpress/jordan/prototype/trade

The two Python/Pandas scripts below are called by the corresponding
(UN Comtrade or UNIDO) front-end programs which in turn are invoked from
the Data Update page.
These two scripts are located in the <Wordpress root folder>/jordan/prototype/scripts/ folder:

 

• <Wordpress root folder>/jordan/prototype/scripts/update_comtrade.py
• <Wordpress root folder>/jordan/prototype/scripts/update_unido.py

The front-end programs that call these scripts are HTML/Javascripts/CSS based and they are to be found at:

• <Wordpress root folder>/jordan/prototype/admin/comtrade_update.html
• <Wordpress root folder>/jordan/prototype/admin/unido_update.html