Category based Export of Nepal (2020-21)- Data management using STATA

 

I have used the data from Trade and Export Promotion Centre (TEPC) data portal, for the year 2020-21 (2077-78) as a reference for this article. Although, same code and logic could be used for any country's trade based data given that we are cleaning the data in terms of category (i.e Clothing, Textiles etc). There are 22 categories in which all goods come under. Such is the case because the Harmonised System (HS) Code identification for Category based description  is identical for all countries. I will talk more about this later in the article. 

Firstly, we need to download the data from the TEPC portal. My last article shows halfway work to what we are going to explore in this blog. Hence, I request you to follow my last article till the end of 8 digit HS Code portion(i.e till drop if Country==""). After you run the code mentioned in the earlier article, you should get the data as such;

Now, as per the need of your research or wish, you can drop the variable “Unit” and “Quantity". Here, I will keep it for now. 

The most tricky part is the identification of particular good into a particular category. Example, How do you know which good comes under “Textiles” or “Clothing”? 

WTO has categorised goods into a particular category which is largely identified in terms of HS Code. For simplicity, I am attaching the WTO defined categorisation which is mentioned in this PDF here: https://unctad.org/system/files/official-document/wto2018_en.pdf Open this report and go to page 33. You will see the definition of groups in terms of agricultural products and non-agricultural products and the categories identified in terms of chapter (i.e chap 01), 4 digit (i.e 1601) or 6 digit (i.e 150410). 

Now, it's pertinent to understand what "4 or 6" digit HS Code and "chap" mean in this case. The first two digit basically tells you various chapters of the list under which many products are identified. If you look at the data right after downloading or before cleaning it, you would see the product description in terms of chapters as well. This two digit code further helps in identifying the product category at 4 digit HS Code. And the last two digit code is specific to the products that are exported as per 6 digit HS Code description. 

Nepal uses 8 digit HS Code which you could see in the data as well. Hence, to categorize the product or goods in their respective category, we will have to adjust the category by taking into account every description mentioned in the HS Code nomenclature list of the pdf. 

For that, firstly, you will have to create three variables HS2 denoting chapter, HS4 denoting 4 digit HS Code, HS6 denoting 6 digit HS Code. 

gen HS2=""

gen HS4=""

gen HS6=""

Also,  generate a new variable “Category” and leave it blank for now, so that we can replace the blank space with respective category later. 

gen Category

Now, you will have to divide the variable “HS Code” into three portion. The first two digit should be replaced as observation in the variable “HS2”, first 4 digit in the variable “HS4”, and first 6 digit in the variable “HS6”. 

replace HS2 = substr(HSCode,1,2)

replace HS4 = substr(HSCode,1,4)

replace HS6 = substr(HSCode,1,6)

The data should look like this;

While running the above mentioned code, I incurred some issues related to numeric adjustment of the observation among the variables that had the observation as string. As you can see in the figure above, the variable “HS2” has single digit observation (i.e 2 when it should be 02). In order to get rid of this trouble, you will have to the convert the string variable into numeric form for which you will have to use destring command. 

destring HS2, replace

destring HS4, replace 

destring HS6, replace 

Given that we are looking at "Category" only and not the export in terms of country, we can drop Country

drop Country

Now, the most tedious part begins whereby you will have to manually code every chapter, 4 or 6 digit description mentioned in the pdf. Get ready for some labour work here. Use the command as addressed below;

//Agricultural Products 

replace Category= "Animal Products" if HS2==01|HS2==02|(HS4>=1601 & HS4<=1602)

replace Category= "Dairy Products" if (HS4>=0401 & HS4<=0406) 

replace Category= "Fruits, Vegetables, Plants" if HS2==07|HS2==08|HS2==13|HS2==14|(HS4>=0601 & HS4<=0603)|HS4==1105|HS4==1106|(HS4>=2001 & HS4<=2008)|HS4==1211

replace Category= "Coffee, Tea" if HS2==18|HS4==0901|HS4==0902|HS4==0903|HS4==2101

replace Category="" if HS4==1802

replace Category= "Cereals and Preparations" if HS2==19|HS2==10|(HS4>=0407 & HS4<=0410)|(HS4>=1101 & HS4<=1104)|(HS4>=1107 & HS4<=1109)|(HS4>=2102 & HS4<=2106)|HS4==2209

replace Category= "Oilseeds, Fats & Oils" if HS2==15|(HS4>=1201 & HS4<=1208)|(HS4>=2304 & HS4<=2306)|HS4==3823

replace Category="" if HS6==150410|HS6==150420

replace Category= "Sugars and confectionery" if HS2==17

replace Category= "Beverages and tabacco" if HS2==24|HS4==2009|(HS4>=2201 & HS4<=2208)

replace Category= "Cotton" if (HS4>=5201 & HS4<=5203)

replace Category= "Other agricultural products" if (HS4>=0904 & HS4<=0910)|HS2==05|HS4==0604|(HS4>=1209 & HS4<=1210)|(HS4>=1212 & HS4<=1214)|HS4==1802|HS6==230110|HS4==2302|HS4==2303|(HS4>=2307 & HS4<=2309)|(HS6>=290543 & HS6<=290545)|HS4==3301|(HS4>=3501 & HS4<=3505)|HS6==380910|HS6==382460|(HS4>=4101 & HS4<=4103)|HS4==4301|(HS4>=5001 & HS4<=5003)|(HS4>=5101 & HS4<=5103)|HS4==5301|HS4==5302

replace Category="" if HS4==0508| HS6==051191

//Non-agricultural Products 

replace Category= "Fish and fish products" if HS2==03|HS4==0508|HS6==051191|HS6==150410|HS6==150420|HS6==230120|(HS4>=1603 & HS4<=1605)

replace Category= "Minerals and Metals" if (HS4>=2601 & HS4<=2617)|HS4==2620|(HS2>=72 & HS2<=76)|(HS2>=78 & HS2<=83)|HS2==25|(HS4>= 2618 & HS4<= 2619)|HS4==2621|(HS4>= 2701 & HS4<= 2704)|(HS4>= 2706 & HS4<= 2708)|(HS4>= 2711 & HS4<= 2715)|HS2==31|HS4==3403|(HS2>=68 & HS2<= 71)| (HS6>=911310 & HS6<=911320)

replace Category= "" if (HS4>= 7321 & HS4<= 7322)|(HS4>= 8304 & HS4<= 8305)|HS4==6807|(HS6>= 701911 & HS6<= 701919)|(HS6>= 701940 & HS6<= 701959)

replace Category= "Petroleum" if HS4==2709| HS4==2710

replace Category= "Chemicals" if HS4==2705|(HS2>=28 & HS2<=30)|(HS2>=32 & HS2<=34)|HS4==3506|HS4==3507|(HS4>=3601 & HS4<=3604)|(HS2>=37 & HS2<=39)

replace Category="" if (HS6>=290543 & HS6<=290545)|HS6==300590|HS4==3301|HS6==330620|HS4==3403|HS4==3406|HS6==380910|HS4==3823|HS6==382460|(HS6>=392112 & HS6<=392113)|HS6==392190

replace Category= "Wood, paper, etc" if HS2==44|HS2==45|HS2==47|HS2==48|HS2==49|HS6==961900|(HS4>=9401 & HS4<=9404)

replace Category="" if HS6==940490

replace Category= "Textiles" if HS6==300590|HS6==330620|(HS6>=392112 & HS2<=392113)|HS6==392190|HS6==420212|HS6==420222|HS6==420232|HS6==420292|(HS2>=50 & HS2<=60)|HS2==63|HS6==640520|HS6==640610|(HS4>= 6501 & HS4<= 6505)|HS4==6601|(HS6>=701911 & HS6<=701919)|(HS6>=701940 & HS6<=701959)|HS6==870821|HS4==8804|HS6==911390|HS6==940490|HS6==961210

replace Category="" if (HS4>= 5001 & HS4<= 5003)|(HS4>= 5101 & HS4<= 5103)|(HS4>= 5201 & HS4<= 5203)|(HS4>=5301 & HS4<=5302)

replace Category="Clothing" if HS2==61|HS2==62

replace Category= "Leather, footwear,etc" if HS2==40|HS2==41|(HS4>=4201 & HS4<=4205)|(HS4>=4302 & HS4<=4304)|HS2==64|HS4==9605

replace Category= "" if (HS4>=4101 & HS4<=4103)|HS6==420212|HS6==420222|HS6==420232|HS6==420292|HS6==640520|HS6==640610

replace Category="Non-electrical Machinery" if (HS4>=7321 & HS4<=7322)|HS2==84|HS6==850860|HS6==852842|HS6==852852|HS6==852862|HS4==8608|HS4==8709

replace Category="" if (HS6>=846721 & HS6<=846729)

replace Category="Electrical Machinery" if (HS6>=846721 & HS6<=846729)|HS2==85|HS6==852352

replace Category="" if HS6== 850860|HS6==852842|HS6==852852|HS6==852862|(HS4>=8519 & HS4<=8523)

replace Category="Transport equipment" if HS2==86|(HS4>=8701 & HS4<=8708)|(HS4>=8711 &

HS4<=8714)|HS4==8716|(HS4>=8801 & HS4<=8803)|HS2==89

replace Category="" if HS4==8608|HS6==870821

replace Category= "Manufactures, not elsewhere specified" if HS4==2716|HS4==3406|(HS4>=3605 & HS4<=3606)|HS4==4206|HS2==46|(HS4>=6506 & HS4<=6507)|(HS4>=6602 & HS4<=6603)|HS2==67|HS4==6807|(HS4>=8304 & HS4<=8305)|(HS4>=8519 & HS4<=8523)|HS4==8710|HS4==8715|HS4==8805|(HS2>=90 & HS2<=93)|(HS4>=9405 & HS4<=9406)|HS2==95|HS2==96|HS2==97

replace Category="" if HS6==852352|HS4==9113|HS4==9605|HS6==961210|HS6==961900

*double counting HSCodes re-entry

replace Category= "Other agricultural products" if HS4==1802|HS4==3301|(HS6>=290543 & HS6<=290545)|HS6==380910|HS6==382460|(HS4>= 5001 & HS4<= 5003)|(HS4>= 5101 & HS4<= 5103)|HS4==5301|HS4==5302|(HS4>=4101 & HS4<=4103)

replace Category= "Leather, footwear,etc" if HS4==4102|HS4==9605

replace Category= "Minerals and Metals" if HS4==3403|HS4==9113

replace Category= "Oilseeds, Fats & Oils" if HS4==3823

replace Category= "Cotton" if (HS4>=5201 & HS4<=5203)

replace Category= "Textiles" if HS6==420212|HS6==420222|HS6==420232|HS6==420292|HS6==640520|HS6==640610|HS6==961210|HS6==870821

replace Category= "Non-electrical Machinery" if HS6==850860|HS6==852842|HS6==852852|HS6==852862|HS4==8608

replace Category = "Electrical Machinery" if HS6==852352

replace Category= "Wood, paper, etc" if HS6==961900

As you can see in the figure above, the variable “Category” has its respective category identification as per the HS Code identification of groups mentioned in the World Tariff Profiles. 

Now, given that we have the categorisation of the goods into different category, we don't need variables HS2, HS4 and HS6. Hence, drop them. Again, you can drop Unit and Quantity as per your need. Here, I will drop them.

drop HS2 HS4 HS6 Unit Quantity

Now, if you want to analyse the data in terms of only "Category", you will have to drop the variable Commodity and HS Code as well and also drop duplicates (in relation to Category).

bys Category: egen total =sum(ValuesRs)

duplicates drop Category, force

drop ValuesRs

The data should look like this;

Now you can export the data into an excel file. Use this command;

export excel “file name", firstrow(variables) replace 

I made the graph through Excel and it looked like this. A large chunk of Nepal’s total exports consisted of Textiles and Oilseeds, Fats & Olis. NRS billions 58.7 and 38.9 respectively, worth of goods were exported among these two category only.