
from IPython.display import HTML
HTML('''<script>
code_show=true; 
function code_toggle() {
 if (code_show){
 $('div.input').hide();
 } else {
 $('div.input').show();
 }
 code_show = !code_show
} 
$( document ).ready(code_toggle);
</script>
<form action="javascript:code_toggle()"><input type="submit" value="Click here to toggle on/off the raw code."></form>''')


from pathlib import Path
import re
import pandas as pd
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
import sqlite3
import warnings
warnings.filterwarnings("ignore")


%%HTML
<style>
.table1 table{
    width: 80%
}

</style>


# from tqdm import tqdm_notebook
# # Retrieve customs dataset from 2019.
# path = Path('/mnt/data/public/customs/')
# files = []
# for year in ['2019']:
#     files.extend(path.glob('*' + year + '.xlsx'))
# files = [str(file) for file in files]

# # Combine all datasets in a dataframe 
# df_all = pd.DataFrame()
# for file in tqdm_notebook(files):
#     df = pd.read_excel(file)
#     pattern_month = r'(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)'
#     pattern_year = r'\d{4}'
#     month = re.findall(pattern_month, file)
#     year = re.findall(pattern_year, file)
#     df['month'] = month[0]
#     df['year'] = year[0]
#     df_all = df_all.append(df, sort=False)

# df_all.shape


# df_all.to_csv("customs2019.csv.gz", index=False, compression='gzip')


df1 = pd.read_csv('customs2019.csv.gz', compression='gzip',
                  usecols=['HSCODE', 'COUNTRYEXPORT', 'CUSTOMSVALUE',
                           'CURRENCY', 'DUTIABLE_FOREIGN', 'EXCHANGE_RATE',
                           'DUTIABLEVALUEPHP', 'FREIGHT', 'INSURANCE',
                           'PREF_CODE', 'DUTY_PAID', 'VAT_PAID', 
                           'EXCISEADVALOREM', 'DUTIESTAXES',
                           'GOODSDESCRIPTION', 'year'])
df1.head()


df1.describe()


hscode = pd.read_csv('hschapters.csv', names=['HS2', 'HS Description'],
                     header=0, dtype={'HS2': object})
hscode.head()


df = df1[df1["DUTIESTAXES"] <= df1["DUTIABLEVALUEPHP"]].copy()
#df.describe()
df.head()


hs_2digit = df['HSCODE'].astype(str).str[:2].tolist()
df.insert(loc=1, column='HS2', value=hs_2digit)
df = pd.merge(df, hscode, on='HS2')
df.head()


top_tax19 = (df.groupby("COUNTRYEXPORT")[["COUNTRYEXPORT","DUTIESTAXES"]]
             .agg(sum).nlargest(10, columns="DUTIESTAXES").reset_index())
top_tax19 = top_tax19.rename(columns={"DUTIESTAXES" : "TOTAL_DUTIESTAX"})

fig, ax = plt.subplots(figsize=(15, 8), constrained_layout=True)


sns.barplot(x="TOTAL_DUTIESTAX", y="COUNTRYEXPORT", 
            data=top_tax19)
ax.set_title("TOP 10 IMPORT COUNTRIES WITH HIGHEST DUTIES AND TAXES FOR 2019")
ax.set_xlabel("TOTAL DUTIES TAX (in hundred billion PhP)")

ax;


print("Proportion of Duties/Taxes of the TOP 4 Countries (2019): {:.2f}%"
      .format(
          sum(top_tax19.TOTAL_DUTIESTAX[:4]) / sum(df.DUTIESTAXES) * 100))

Proportion of Duties/Taxes of the TOP 4 Countries (2019): 52.39%


top_hs19 = (df.groupby("HS Description")[["DUTIESTAXES"]]
            .agg(sum).nlargest(10, columns="DUTIESTAXES").reset_index())


fig, ax = plt.subplots(figsize=(15, 10), constrained_layout=True)


sns.barplot(y="HS Description", x="DUTIESTAXES", data=top_hs19)
ax.set_title("HS CHAPTERS WITH HIGHEST DUTIES+TAXES (2019)")
ax.set_xlabel("TOTAL DUTIES+TAX (in hundred billion PhP)")

ax;


print("Proportion of the top 4 HS chapters by DUTIESTAXES (2019): ", 
      sum(top_hs19.DUTIESTAXES[:4]) / sum(df.DUTIESTAXES))

Proportion of the top 4 HS chapters by DUTIESTAXES (2019):  0.5811713578375313


# Get top 4 COUNTRYEXPORT Chapters based on DUTIESTAXES.
def top_country(df):
    """Return list of top 4 COUNTRYEXPORT in terms of DUTIESTAXES."""
    top = (df.groupby("COUNTRYEXPORT")[["DUTIESTAXES"]]
           .agg(sum).nlargest(4, "DUTIESTAXES").reset_index())
    return top.COUNTRYEXPORT.to_list()


# Get Top 5 HS for each COUNTRYEXPORT based on DUTIESTAXES.
def country_byhsratio(df, ctry):
    """Return DataFrame of top 5 HS per COUNTRYEXPORT."""
    top = (df[(df['COUNTRYEXPORT'] == ctry)]
           .groupby(["COUNTRYEXPORT", "HS2", "HS Description"])
           [["DUTIABLEVALUEPHP", "DUTIESTAXES"]]
           .agg(np.sum).nlargest(5, "DUTIESTAXES").reset_index())
    top["DUTIESRATIO"] = top["DUTIESTAXES"] / top["DUTIABLEVALUEPHP"]
    return top


# Only transactions with DUTIESTAXES < DUTIABLEVALUEPHP are included
df_valid = df[df['DUTIESTAXES'] < df['DUTIABLEVALUEPHP']]

# Combine the top 5 HS Chapter of the top 4 COUNTRYEXPORT.
hs_country = pd.DataFrame()
for hs in top_country(df_valid):
    df_hs = country_byhsratio(df_valid, hs)
    hs_country = hs_country.append(df_hs, ignore_index=True)
hs_country.head(20)


def sortedgroupedbar(ax, x ,y, groupby, data=None, width=0.8, **kwargs):
    """Sort values per HS chapter."""
    order = np.zeros(len(data))
    df = data.copy()
    for xi in np.unique(df[x].values):
        group = data[df[x] == xi]
        a = group[y].values
        b = sorted(np.arange(len(a)),key=lambda x:a[x],reverse=True)
        c = sorted(np.arange(len(a)),key=lambda x:b[x])
        order[data[x] == xi] = c   
    df["order"] = order
    u, df["ind"] = np.unique(df[x].values, return_inverse=True)
    step = width/len(np.unique(df[groupby].values))
    for xi,grp in df.groupby(groupby):
        ax.bar(grp["ind"]-width/2.+grp["order"]*step+step/2.,
               grp[y],width=step, label=xi, **kwargs)
    ax.legend(title=groupby)
    ax.set_xticks(np.arange(len(u)))
    ax.set_xticklabels(u)
    ax.set_xlabel(x)
    ax.set_ylabel(y)


fig, ax = plt.subplots(2,1,figsize=(8,10),sharex=True)
fig.tight_layout(rect=[0, 0.03, 1, 0.95])
plt.suptitle("DUTIABLE VALUE AND DUTIES/TAXES OF IMPORTS BY HS CHAPTER",
             fontsize=15)

sortedgroupedbar(ax[0], x="COUNTRYEXPORT", y='DUTIABLEVALUEPHP', 
                 groupby="HS Description", data=hs_country)
ax[0].set_xlabel("")
ax[0].set_ylabel("DUTIABLE VALUE (in PHP)", fontsize=14)
ax[0].legend(loc='best')

sortedgroupedbar(ax[1], x="COUNTRYEXPORT", y='DUTIESTAXES', 
                 groupby="HS Description", data=hs_country)
ax[1].set_xlabel("")
ax[1].set_ylabel("DUTIES AND TAXES (in PHP)", fontsize=14)
ax[1].legend(loc='best')
ax[1].set_ylim(0, 1.5e11)

plt.xticks(rotation=30, ha="right");


fig, ax = plt.subplots(figsize=(8, 5.88))
fig.tight_layout(rect=[0, 0.03, 1, 0.95])
plt.suptitle("DUTIES RATIO OF IMPORTS BY HS", fontsize=15)
sortedgroupedbar(ax, x="COUNTRYEXPORT", y='DUTIESRATIO', 
                 groupby="HS Description", data=hs_country)
ax.set_xlabel("COUNTRIES", fontsize=14)
ax.set_ylabel("DUTIES RATIO", fontsize=14)
ax.set_ylim(0, 0.5)
ax.legend(loc='best');


# Get top 4 HS Chapters based on DUTIESTAXES. 
def top_hs_year(df):
    """Return list of top 4 HS in terms of total duties and taxes paid."""
    
    top = (df.groupby(["HS2","HS Description"])[["DUTIESTAXES"]]
           .agg(sum).nlargest(4, "DUTIESTAXES").reset_index())
    return top.HS2.to_list()

def country_byhsratio(df, hs):
    """Return DataFrame of top 5 countries per HS based on total duties and 
    taxes paid.
    """
    
    top = (df[(df['HS2'] == hs)]
           .groupby(["HS2","HS Description", "COUNTRYEXPORT"])
           [["DUTIABLEVALUEPHP", "DUTIESTAXES"]].agg(np.sum)
           .nlargest(5, "DUTIESTAXES").reset_index())
    top["DUTIESRATIO"] = top["DUTIESTAXES"] / top["DUTIABLEVALUEPHP"]
    return top

# Combine in a daframe the top 5 countries from 2017-2020 per HS Chapter.
country_hs = pd.DataFrame()
for hs in top_hs_year(df):
    df_hs = country_byhsratio(df, hs)
    country_hs = country_hs.append(df_hs, ignore_index=True)
country_hs.head(30)


def sortedgroupedbar(ax, x ,y, groupby, data=None, width=0.8, **kwargs):
    """Sort values per HS chapter."""
    order = np.zeros(len(data))
    df = data.copy()
    for xi in np.unique(df[x].values):
        group = data[df[x] == xi]
        a = group[y].values
        b = sorted(np.arange(len(a)),key=lambda x:a[x],reverse=True)
        c = sorted(np.arange(len(a)),key=lambda x:b[x])
        order[data[x] == xi] = c   
    df["order"] = order
    u, df["ind"] = np.unique(df[x].values, return_inverse=True)
    step = width/len(np.unique(df[groupby].values))
    for xi,grp in df.groupby(groupby):
        ax.bar(grp["ind"]-width/2.+grp["order"]*step+step/2.,
               grp[y],width=step, label=xi, **kwargs)
    ax.legend(title=groupby)
    ax.set_xticks(np.arange(len(u)))
    ax.set_xticklabels(u)
    ax.set_xlabel(x)
    ax.set_ylabel(y)


fig, ax = plt.subplots(2,1,figsize=(8,10),sharex=True)
fig.tight_layout(rect=[0, 0.03, 1, 0.95])
plt.suptitle("DUTIABLE VALUE and DUTIES/TAXES", fontsize=15)

sortedgroupedbar(ax[0], x="HS Description", y='DUTIABLEVALUEPHP', 
                 groupby="COUNTRYEXPORT", data=country_hs)
ax[0].set_xlabel("")
ax[0].set_ylabel("DUTIABLE VALUE (in PHP)", fontsize=14)
ax[0].legend(loc='center left', bbox_to_anchor=(1, 0.65))
ax[0].legend(loc='best')

sortedgroupedbar(ax[1], x="HS Description", y='DUTIESTAXES', 
                 groupby="COUNTRYEXPORT", data=country_hs)
ax[1].set_xlabel("")
ax[1].set_ylabel("DUTIES AND TAXES (in PHP)", fontsize=14)
ax[1].legend(loc='center left', bbox_to_anchor=(1, 0.65))
ax[1].legend(loc='best')
ax[1].set_ylim(0, 1e11)

plt.xticks(rotation=30, ha="right");


fig, ax = plt.subplots(figsize=(8,5))
fig.tight_layout(rect=[0, 0.03, 1, 0.95])
plt.suptitle("DUTIES RATIO", fontsize=15)

sortedgroupedbar(ax, x="HS Description", y='DUTIESRATIO', 
                 groupby="COUNTRYEXPORT", data=country_hs)
ax.set_xlabel("HS CHAPTERS", fontsize=14)
ax.set_ylabel("DUTIES RATIO", fontsize=14)
ax.legend(loc='best')
plt.xticks(rotation=30, ha="right");

Variable Name	Definition	Data Type
HSCODE	Harmonized Commodity Description and Coding System. Internationally-standardized system of numbers and names that describes the type of good that is shipped.	Integer
COUNTRYEXPORT	Country of export of the goods	String
GROSSMASSKGS	Mass of the goods including packaging	Float
NETMASSKG	Mass of the imported good itself without any packaging	Float
CUSTOMSVALUE (in Foreign Currency)	Amount paid or payable for the imported goods	Float
CURRENCY	Currency of the import transaction (CUSTOMSVALUE)	String
DUTIABLE_FOREIGN (in Foreign Currency)	Dutiable import value upon which the import duty rate will be levied `CUSTOMSVALUE + INSURANCE + FREIGHT`	Float
EXCHANGE_RATE	Exchange rate used from the foreign currency unit (CURRENCY) to Philippine peso	Float
FREIGHT (in Foreign Currency)	Cost of shipping of imported goods	Float
INSURANCE (in Foreign Currency)	Cost of insurance of the goods	Float
ARRASTRE (in Foreign Currency)	Cost of handling the cargo	Float
PREF_CODE	Preferential trade agreement used for the transaction. Reduced customs duty rates may apply on importrf goods from a country with which PH has a trade agreement.	String
DUTY_PAID (in PHP)	Customs duties paid for the imported goods	Integer
VATBASE (in PHP)	VATable value of goods upon which the VAT rate of 12% will be levied	Integer
VAT_PAID (in PHP)	Value-added taxes paid on the transaction, in Philippine peso	Integer
EXCISEADVALOREM (in PHP)	Excise and ad valorem taxes on the goods	Float
DUTIESTAXES	Total duties and taxes paid `DUTY_PAID + VAT_PAID + EXCISEADVALOREM`	Integer
GOODSDESCRIPTION	Description of the imported goods	String

	HSCODE	COUNTRYEXPORT	CUSTOMSVALUE	CURRENCY	DUTIABLE_FOREIGN	EXCHANGE_RATE	DUTIABLEVALUEPHP	FREIGHT	INSURANCE	PREF_CODE	VAT_PAID	DUTIESTAXES	GOODSDESCRIPTION	year
0	73011000000	CHINA	216000.00	USD	229438.89	51.033	11708955.00	13364.00	74.89	ACFTA	1421907	1421907	SHEET PILE Z SHAPE 770343.58.5*12000 TOLE	2019
1	38244000000	JAPAN	46200.00	USD	150304.00	51.033	7670464.00	103180.00	924.00	JPEPA	1068958	1068958	7,700MT CHEMICAL GYPSUM IN BULK FOR USE AS R	2019
2	84313990000	CHINA	1730190.00	USD	1831866.89	50.826	93106466.55	67073.09	34603.80	ACFTA	11182987	11182987	PAN CONVEYOR BELT CONVEYOR COMPONENT	2019
3	73089099000	CHINA	752933.08	USD	797180.18	50.826	40517479.83	29188.44	15058.66	ACFTA	4870064	4870064	GRID BRIDGE PROCESS AIR DUCT STORAGE	2019
4	84283990000	CHINA	202038.82	USD	213911.90	50.826	10872286.23	7832.30	4040.78	ACFTA	1307109	1307109	CHAIN CONVEYOR	2019

	HSCODE	CUSTOMSVALUE	DUTIABLE_FOREIGN	EXCHANGE_RATE	DUTIABLEVALUEPHP	FREIGHT	INSURANCE	DUTY_PAID	VAT_PAID	EXCISEADVALOREM	DUTIESTAXES	year
count	3.794776e+06	3.794776e+06	3.794776e+06	3.794776e+06	3.794776e+06	3.794776e+06	3.794776e+06	3.794776e+06	3.794776e+06	3.794776e+06	3.794776e+06	3794776.0
mean	6.923999e+10	9.210593e+04	9.573394e+04	5.153954e+01	2.883647e+06	2.779397e+03	7.778899e+02	3.567569e+04	3.351352e+05	3.074327e+04	1.604089e+05	2019.0
std	2.331668e+10	4.389686e+07	4.395284e+07	4.024134e+00	2.303371e+09	2.067272e+06	4.715231e+04	3.298769e+06	2.767941e+08	4.686981e+06	3.256353e+06	0.0
min	1.012100e+09	1.000000e-02	1.000000e-02	4.000000e-03	5.100000e-01	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	2019.0
25%	4.821109e+10	3.182400e+02	3.632900e+02	5.110700e+01	1.868900e+04	7.830000e+00	2.420000e+00	0.000000e+00	2.063000e+03	0.000000e+00	0.000000e+00	2019.0
50%	8.414903e+10	2.050000e+03	2.237560e+03	5.200100e+01	1.145765e+05	5.509000e+01	2.660000e+01	2.710000e+02	1.284200e+04	0.000000e+00	2.300000e+02	2019.0
75%	8.536700e+10	1.242024e+04	1.326853e+04	5.233900e+01	6.727522e+05	3.014500e+02	1.793300e+02	6.792000e+03	7.241500e+04	0.000000e+00	2.835800e+04	2019.0
max	9.706000e+10	8.533109e+10	8.533109e+10	6.965200e+01	4.481760e+12	4.019379e+09	7.940000e+07	6.374059e+09	5.384834e+11	8.496222e+09	6.919788e+08	2019.0

	HS2	HS Description
0	01	Live animals
1	02	Meat
2	03	Fish and crustaceans
3	04	Dairy produce
4	05	Products of animal origin

	HSCODE	COUNTRYEXPORT	CUSTOMSVALUE	CURRENCY	DUTIABLE_FOREIGN	EXCHANGE_RATE	DUTIABLEVALUEPHP	FREIGHT	INSURANCE	PREF_CODE	VAT_PAID	DUTIESTAXES	GOODSDESCRIPTION	year
0	73011000000	CHINA	216000.00	USD	229438.89	51.033	11708955.00	13364.00	74.89	ACFTA	1421907	1421907	SHEET PILE Z SHAPE 770343.58.5*12000 TOLE	2019
1	38244000000	JAPAN	46200.00	USD	150304.00	51.033	7670464.00	103180.00	924.00	JPEPA	1068958	1068958	7,700MT CHEMICAL GYPSUM IN BULK FOR USE AS R	2019
2	84313990000	CHINA	1730190.00	USD	1831866.89	50.826	93106466.55	67073.09	34603.80	ACFTA	11182987	11182987	PAN CONVEYOR BELT CONVEYOR COMPONENT	2019
3	73089099000	CHINA	752933.08	USD	797180.18	50.826	40517479.83	29188.44	15058.66	ACFTA	4870064	4870064	GRID BRIDGE PROCESS AIR DUCT STORAGE	2019
4	84283990000	CHINA	202038.82	USD	213911.90	50.826	10872286.23	7832.30	4040.78	ACFTA	1307109	1307109	CHAIN CONVEYOR	2019

1. Executive Summary ¶

2. Introduction¶

3. Data Processing and Cleaning ¶

3.1 Consolidate 2019 Excel Files into one CSV File¶

3.2 Get HS Chapter Description¶

3.3 Check Column Values¶

3.4 Exclude rows with DUTIESTAXES higher than DUTIABLEVALUEPHP¶

3.5 Add new columns¶

4. Exploratory Data Analysis¶

TOP COUNTRIES IN TERMS OF DUTIES AND TAXES PAID IN 2019¶

TOP HS CHAPTERS BASED ON DUTIES AND TAXES¶

4.1 TOP COUNTRIES PER HS CHAPTER BASED ON DUTIES AND TAXES¶

4.2 TOP HS CHAPTERS PER COUNTRY BASED ON DUTIES AND TAXES¶

CONCLUSION AND RECOMMENDATION¶

ACKNOWLEDGEMENT¶

REFERENCES¶

	COUNTRYEXPORT	HS2	HS Description	DUTIABLEVALUEPHP	DUTIESTAXES	DUTIESRATIO
0	CHINA	27	Mineral fuels, oils and products	1.834516e+11	56712039199	0.309139
1	CHINA	85	Electrical machinery & equipment	3.015716e+11	17056282171	0.056558
2	CHINA	84	Machinery and mechanical appliances	1.614548e+11	13014022974	0.080605
3	CHINA	72	Iron and steel	8.783957e+10	10144562047	0.115490
4	CHINA	87	Vehicles other than railway	5.303461e+10	8910859976	0.168020
5	KOREA, REPUBLIC OF	27	Mineral fuels, oils and products	1.161549e+11	40856744268	0.351744
6	KOREA, REPUBLIC OF	87	Vehicles other than railway	1.733356e+10	3639701810	0.209980
7	KOREA, REPUBLIC OF	84	Machinery and mechanical appliances	3.275525e+10	1728752553	0.052778
8	KOREA, REPUBLIC OF	72	Iron and steel	1.030839e+10	902009081	0.087502
9	KOREA, REPUBLIC OF	85	Electrical machinery & equipment	1.773141e+11	665029889	0.003751
10	INDONESIA	87	Vehicles other than railway	1.227132e+11	19715697058	0.160665
11	INDONESIA	27	Mineral fuels, oils and products	9.885390e+10	14931488591	0.151046
12	INDONESIA	21	Miscellaneous edible preparations	2.717923e+10	4815157875	0.177163
13	INDONESIA	15	Animal or vegetable fats	1.561514e+10	1424888572	0.091250
14	INDONESIA	85	Electrical machinery & equipment	1.262453e+10	1297879268	0.102806
15	SINGAPORE	27	Mineral fuels, oils and products	5.324746e+10	22537265074	0.423255
16	SINGAPORE	85	Electrical machinery & equipment	1.367361e+11	3979989326	0.029107
17	SINGAPORE	84	Machinery and mechanical appliances	6.097404e+10	3695984519	0.060616
18	SINGAPORE	33	Essential oils and resinoids	1.501894e+10	2042028906	0.135964
19	SINGAPORE	21	Miscellaneous edible preparations	1.475983e+10	1843636979	0.124909