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. 

Russia-Ukraine War and Global Food Security

Right when the post-pandemic recovery was about to take a positive incline, Russian-Ukraine war has added an additional trauma to an already traumatized world economy with a forced supply crunch for multiple food and petroleum products. With numerous economic sanctions, geo-political tensions, supply chain disruptions and sky-high fuel prices beginning to be a new normal currently, the war is having a serious ripple effect. Especially by intensifying threats to global food security given that a large chunk of global food exports comes from Ukraine and Russia. 

Predicaments of War and South Asia

Russia and Ukraine rank among the top three exporters of wheat, barley, maize, and sunflower oil among others. Ukraine alone accounts for world’s one-third of barley export. Russia ranks first, second and third in the global export of nitrogen fertilizer, potassic fertilizers and phosphorous fertilizers respectively. Least developed Countries rely heavily on such fertilizers and food supplies from Ukraine and Russia. Many of the LDCs were already having issues with fertilizers shortages and food supply due to the pandemic and now, with the Ukraine-Russia war propelling their production and supply crunch, matters have been much worse for South Asia Countries. 

 

The export-import scenario of wheat across South Asian countries and Ukraine can be a fresh example. Bangladesh imports almost half of its wheat from Ukraine which is termed to be much cheaper import. A significant other chunk is imported from India. However, in reference to the current food shortage, India has implied a ban on wheat export. Given such situation, with both India and Ukraine out of option for wheat import, Bangladesh is forced to obtain wheat from expensive sellers. India’s stance on wheat export ban is heavily criticized by the WTO and many countries citing that such decisions would further worsen the food market, especially in low-income countries. Likewise, Pakistan imports 39% of its wheat from Ukraine and a significant chunk from Russia as well. Pakistan saw a record production of wheat last year, however, that is still termed to be insignificant to meet the demand and the target production for this year. Also, Fertilizer shortages have been a big concern in Pakistan and Nepal lately. In one of the western regions of Nepal, the farmers out of desperation took over the fertilizer trunk on route sent by the government to a respective body. Sri Lanka, on the other hand, is having the worst possible scenario any nation could have. Sri Lanka is basically in a fuel lockdown and its weak foreign currency and depleted reserves have made it the most vulnerable to food shortage. 

 

Not to forget, 60% of the world’s hungry people live in Asia and the people of South Asian countries include a significant chunk of those hungry people. 

 

Besides, the early prediction by the Food and Agriculture Organization (FAO) highlights that the global trade of food products are expected to decrease in 2022-23, especially food products like cereals, wheat and coarse grain among others. Subsequently, due to such shortages and debacles time and again, food prices and inflation in general have touched the roof which has made the situation even worse. 

Global Food Price Hike

Ever since the Russia-Ukraine war, the price levels for almost every good has touched the sky. When many countries tend to depend on a few countries for important goods on a daily basis, and when the countries upon which many depend are unable to supply the necessary goods, situation like that of today are bound to happen. However, it is alarming to see such a strong ripple effect. With the shortage of fuels and food products that are largely exported by Ukraine and Russia, many countries are forced to look for limited and expensive alternative options to meet the demand. However, given the extreme shortage, food and fertilizer prices have been higher than ever which is sharply noted in the figure below. 

  Source: FAO

Global Food Price Index (FPI) shows a very unsettling scenario if we look at its inclination since the pandemic. The figure above puts an effort to show pre-COVID, during COVID, pre-war and current status of global food prices. Initially, in 2019 the FDI index was at 95.1 on average. The FPI index floated around 95.1-95.5 during the first wave. With the pandemic tightly grasping the world economy, it’s quite positive to see that the food prices were relatively stable which could be largely due to the higher stocks left. However, as the pandemic moved towards the 2^nd wave and that the stocks started to get thinner by the day, the FPI index showed an upward trend. During the 3^rd wave between mid-January 2021 to mid-May 2021, it showed a steep incline. By this time in May, the FPI index had already boomed to 128.1. It’s a stark rise considering the fact that FPI saw an increase of 33 points (34 percent rise) since 2019 average. 

 

While pandemic had a huge effect on food price hike, the Russia-Ukraine war made it much worse. Although FPI was floating in an expensive price range during the pre-war period, it is quite evident through the graph that, right when the war kicked-off, the FPI showed a very steep upward incline within one month period. The FPI index rose to 159.7 from 141.1. That is 18.1 points (12.30 percent rise) which is immensely high considering the fact that FPI index rose by 6.2 points from February 2020 (beginning of pandemic) to November 2020 (end of second wave). To put into perspective, FPI index tripled within one month during the start of the war compared to a 9 month rise from the start of pandemic to the end of second wave. 

Year	Food Price Index	Meat Price Index	Dairy Price Index	Cereals Price Index	Oils Price Index	Sugar Price Index
2019	95.1	100.0	102.8	96.6	83.2	78.6
2020	98.1	95.5	101.8	103.1	99.4	79.5
2021	125.7	107.7	119.1	131.2	164.9	109.3
2022	150.4	117.6	141.7	159.8	221.3	116.6

Source: FAO

The table above shows a year-on-year FDI index along with the index of five daily food product category from 2019-2022 (till May). FPI saw an increase of 24.7 points from 2021 to 2022 May. From the pre covid period till May 2022, FPI saw an increase of 55.3 points (58.15 percent). Given the dire state of food security currently, the FPI index is likely to rise by the end of 2022. Between 2019-2022 May, Oil Price index jumped 138.1 points which is a 166% rise. Likewise, as mentioned earlier as well, Cereals price is expected to further increase in the future with an already rising state of 28.6 points from 2021-2022 May.

 

Policy Recommendation 

The 12^th ministerial Conference held recently on June 12-15 by the World Trade Organization (WTO) has tried to address possible remedies for food insecurity. Restricting countries to impose export ban when traded for commercial humanitarian purpose to the world food programme is a welcome move. However, that can hardly address the current state of dire food security.

 

One of the most important steps would be to work on reducing heavy trade dependency on just a few countries. Its pertinent to look for alternative measures for those commodities that are massively exported on a daily basis like wheat, cereals, and rice among others. Having a wide array of importing option would immediately reduce vulnerability to exogenous shocks like the current war. Given that the LDCs are largely the ones facing the adverse effects of such shocks mostly due to their relatively low-coping mechanisms to shocks, it’s extremely important for LDCs to learn from current scenario and look for alternate options for future food security.

 

Likewise, while it is important to take relevant measures to meet the domestic demand, export ban of food products right when the food security is threatened should not be encouraged and avoided by developing and high-food-producing countries. Moreover, WTO and FAO should bring about measures and policies that help prevent such precarious actions.

 

Initiatives to support the most vulnerable groups is welcome and encouraged. Social incentives and protection programs towards food supply and security should be initiated so that it can help alleviate the effects of war to a certain extent. Importantly, it is even more pertinent to make sure these incentives and social protection programs reach the right audience in a timely manner after the incentives is passed by the relevant body. 

Above all, geo-political conflicts by default hampers food security. 2022 Global Report on Food Crisis underlines Conflict/insecurity to be one of the most important drivers of food crisis across all regions in 2021. For example, for all seven food crises in the Middle East, two out of three food crises in South Asia were caused largely due to conflict/insecurity. Disruptions of food production and daily stable living is reported to have driven many acute food crisis across many regions and such disruptions were propelled by conflict/insecurity. It’s pertinent to avoid such geo-political tensions and conflicts of various means that bring peril to regional food security.  

 

***This article is an edited and extended version of my own article published in Trade, Climate Change and Development Monitor Volume 19, Issue 06, a monthly E-newsletter of SAWTEE. 

Trade-based Data Cleaning through STATA (Nepal Exports--2020/21)

This blog is a result of something that I recently worked on while exploring Trade data of Nepal (exports) for the year 2017/18 (2074/75 BS) to 2020/21 (2077/78 BS). You can download the data from the data portal available at the Trade and Export Promotion Centre (TEPC) website. For folks who wish to use this data to see the HS Code in 8 digit or 6 digit along with total export amount and Country, this article might be of good use to those who are using STATA to clean the data. Hence, I thought I might as well put it in my blog.

Once you download the data from the trade portal, let’s say for the year 2077/78. Generally it will look somewhat like this: 

Here, you can see that the data includes many information that might be of great use to you and some might be redundant as well. When you want the data only in terms of 8 digit or in 6 digit terms, you will have to get rid of a lot of variables like “SN”  and many observations in the variable “HS Code”. For example rows with information related to “chapter” and “4 digit HS Code”. Also, you would want to create a separate column for country and shift the information related to country from the current column as you can see in the figure that the information related to “Chapter/Commodity/Country” are all kept in the same Column. It’s upto you if you want to drop the variable “Unit” and “Quantity” as per the need of your analysis.

I will try to put as clear a description as possible and would welcome any suggestion regarding the STATA coding part that can help me do the same code in a much smarter way, if I may say so. 

Here it is:

Firstly, it would be better if you would set the directory. In my case I had the following directory: 

cd "/Users/Desktop/TEPC"

Then, you will have to import the excel file into STATA.

  import excel “destination of the file in your computer”

drop SN

For simplicity, I will change the variable name from “Chapter/Commodity/Country” as just “Commodity” given that we will only keep information related to commodity in this column and keep information related to country in the newly generated column.

   gen Country=""

   replace Country=Commodity if HSCode==""

   replace Commodity="" if HSCode==""

Now,  in order to only keep the variable “HS Code” with 8 digit, we will have to generate a new column that drops any other observation in HS Code with anything other than 8 digit which is to say that observation with information related to “Chapters” and “4 digit HS Code” will be dropped leaving just the observation with 8 digit.

gen HS_length=length(HSCode)

  replace HSCode="" if HS_length != 8    (!= denotes “not equal to”)

After running the above mentioned command, the data will look something like this in the STATA .dta file, 

Now, what we will do is, replace the observation with a blank space in the variable “HS Code” and “Commodity” with whatever information that we have above that blank space. For example, the first observation of Commodity variable is “Butter”. Here, we are going to replace “Butter” in the blank space below “Butter” till another observation with a some information appears which in this case is “Dairy spreads”. Use the same logic with the variable “HS Code”.

 

We will have to run a loop for this case. Run the command as mentioned below:

Set trace on

    forvalues row=1/`=_N' {

    replace Commodity =Commodity[_n-1] if HSCode==""

   replace HSCode = HSCode[_n-1] if HSCode==""

   replace Unit = Unit[_n-1] if Unit==""

 }

  set trace off

The data will look something like this after you run the above mentioned command;

Now,  we need to get rid of total values of observation in order to keep the data with respect to the exports to country. We need to do this to make sure we don’t have double counting of total values as well. We will drop any observation of the Variable “HS_length” with observation “8” and any blank observation of the variable “Country”.

  drop if HS_length==8

drop HS_length

    drop if Country == ""

Now, we can have the data as per our need with total values of goods exported by Nepal to different countries. The data file should look something like this;

Again, It is up to you if you want to keep units and quantity. Help yourself.

If you want to analyse the data in 6 digit HS Code since 6 digit is used internationally as a common HS Code format.

For that, we need to replace the same 8 digit HS Code to 6 digit (first 6 numbers). Simultaneously, we need to drop duplicates as the total export value in amounts (Values Rs) will be added multiple times in the same row.  Hence it is pertinent to avoid double counting.

   replace (HSCode) = substr(HSCode,1,6)

   bys HSCode: egen total_75=sum(ValuesRs)

  duplicates drop HSCode, force

   save "name of the file", replace.

 

***END***