Difference between revisions of "ANLY482 AY2016-17 T2 Group13 - Project Findings"
Line 135: | Line 135: | ||
<u>Dimension Table for Sectors</u><br> | <u>Dimension Table for Sectors</u><br> | ||
A separate dimension table was created to individually assign HS codes to the sectors. | A separate dimension table was created to individually assign HS codes to the sectors. | ||
− | [[File:.png | + | |
+ | [[File:2.2_Raw_HS.png|500px]] | ||
+ | |||
Raw data file for the HS codes only includes section, commodity code and commodity. | Raw data file for the HS codes only includes section, commodity code and commodity. | ||
− | [[File:.png | + | |
+ | [[File:2.2_Clean_HS.png|500px]] | ||
Cleaned HS data includes sector and sector category. This data was based on the important sectors of our sponsor. Sector category includes information of what our sponsor considered more important commodities or less important commodities. Then, the sectors and sector categories were assigned to the commodity based on approval by our sponsor. | Cleaned HS data includes sector and sector category. This data was based on the important sectors of our sponsor. Sector category includes information of what our sponsor considered more important commodities or less important commodities. Then, the sectors and sector categories were assigned to the commodity based on approval by our sponsor. | ||
− | [[File:.png | + | [[File:2.2_vlookup_HS.png|500px]] |
+ | |||
The “vlookup” function was used to combine the import data and commodity data based on commodity variable to add Section, Sector and Sector Category. | The “vlookup” function was used to combine the import data and commodity data based on commodity variable to add Section, Sector and Sector Category. | ||
+ | |||
<u>Dimension Table for Countries</u><br> | <u>Dimension Table for Countries</u><br> | ||
− | [[File:.png | + | [[File:2.2_Raw_Geog_Data.png|500px]] |
+ | |||
The initial geographic data found on the UN website only included name of the country (Partner) and its respective country code. | The initial geographic data found on the UN website only included name of the country (Partner) and its respective country code. | ||
We referred to the [https://unstats.un.org/unsd/methods/m49/m49regin.htm United Nations Statistics Division] to assign countries to geographic regions, continents and political organisations. | We referred to the [https://unstats.un.org/unsd/methods/m49/m49regin.htm United Nations Statistics Division] to assign countries to geographic regions, continents and political organisations. | ||
− | [[File:.png | + | [[File:2.2_Clean_Geog_Data.png|500px]] |
+ | |||
The information on the UN website was used to manually add in Continent and Region. Economic Unions for particular countries were then assigned to their respective countries. | The information on the UN website was used to manually add in Continent and Region. Economic Unions for particular countries were then assigned to their respective countries. | ||
− | [[File:.png | + | |
+ | [[File:2.2_vlookup_Geog.png|500px]] | ||
+ | |||
The “vlookup” function was used to combine the import data and geographic data based on Partner variable to add Continent, Region and Economic Union. | The “vlookup” function was used to combine the import data and geographic data based on Partner variable to add Continent, Region and Economic Union. | ||
+ | |||
<div style="background: #dce6f9; line-height: 0.3em; font-family:Century Gothic; border-left: #003464 solid 15px;"><div style="border-left: #FFFFFF solid 5px; padding:15px;font-size:15px;"><font color= "#000000"><strong>4. Calculated Fields</strong></font></div></div> | <div style="background: #dce6f9; line-height: 0.3em; font-family:Century Gothic; border-left: #003464 solid 15px;"><div style="border-left: #FFFFFF solid 5px; padding:15px;font-size:15px;"><font color= "#000000"><strong>4. Calculated Fields</strong></font></div></div> | ||
− | <u> | + | <u>Conversion of “Period” to date variable</u><br> |
− | [[File:.png | + | [[File:2.2_Tableau_DateParse.png|500px]] |
“Dateparse” function was used to convert “Period” into a date variable in Tableau. | “Dateparse” function was used to convert “Period” into a date variable in Tableau. | ||
Line 174: | Line 184: | ||
<u>Calculation of “Market share of UK imports relative to the world”</u><br> | <u>Calculation of “Market share of UK imports relative to the world”</u><br> | ||
This value determines the market share of UK imports relative to the world, and is used in the initial UK overview treemap. The equation used is as follows: | This value determines the market share of UK imports relative to the world, and is used in the initial UK overview treemap. The equation used is as follows: | ||
− | [[File:. | + | [[File:2.2_Calculation_of_Market_Share_of_UK_Imports_Relative_to_World.PNG|100px]] |
Latest revision as of 15:36, 20 February 2017
Before embarking on an Exploratory Data Analysis (EDA, the data had to be transformed for further analysis. Viewing the data based on 2D HS codes alone was insufficient because aggregated categories included too many possible unrelated items in the same category. These were the data cleaning steps that were attempted:
All the .csv files were placed in a folder to ensure that concatenation of csv files would only be among datasets that we will be using for the project. Then the “copy *.csv” function was used to combine all the csv folders together though leaving the original folders intact.
R was also used to combine folders together. This was done by loading the .csv files into variables. After which, the “rbind” function was used to combine the datasets together.
Many of the columns in the raw data file did not have values in them. Due to this, we decided to remove the columns in Microsoft Excel because they would not have been useful to our analysis.
Above is the data set after removing the unnecessary columns. Variables containing weight appeared in 4d and 6d data and were not as apparent for 2d data. Due to this, we decided to leave the variables for the dataset.
We received the names for 18 sectors that the DIT focuses on, listed below.
s/n | High Value Campaign | High Priority Volume | Low Priority Volume | Unclassified |
---|---|---|---|---|
1 | Aerospace | Retail/Consumer | Advanced Manufacturing (Excluding aerospace and automotive) | Others - Raw Materials |
2 | Food and Beverage | Education | Automotive | Others - Manufacturing |
3 | Infrastructure (Water and Environment) | Energy | Bio-economy (Agri-tech) | Others |
4 | Infrastructure (Rail) | Financial and Professional Business Services | Bio-economy (Chemicals) | Financial and Professional Business Services - Others |
5 | Technology | Healthcare | Sports | |
6 | Food and Beverage | Life Sciences | ||
7 | Infrastructure (Airports) |
Dimension Table for Sectors
A separate dimension table was created to individually assign HS codes to the sectors.
Raw data file for the HS codes only includes section, commodity code and commodity.
Cleaned HS data includes sector and sector category. This data was based on the important sectors of our sponsor. Sector category includes information of what our sponsor considered more important commodities or less important commodities. Then, the sectors and sector categories were assigned to the commodity based on approval by our sponsor.
The “vlookup” function was used to combine the import data and commodity data based on commodity variable to add Section, Sector and Sector Category.
The initial geographic data found on the UN website only included name of the country (Partner) and its respective country code.
We referred to the United Nations Statistics Division to assign countries to geographic regions, continents and political organisations.
The information on the UN website was used to manually add in Continent and Region. Economic Unions for particular countries were then assigned to their respective countries.
The “vlookup” function was used to combine the import data and geographic data based on Partner variable to add Continent, Region and Economic Union.
Conversion of “Period” to date variable
“Dateparse” function was used to convert “Period” into a date variable in Tableau.
Calculation of “Benchmarks”
For the UK Benchmark, this value was used as the UK reference line in the bullet chart as a comparison to commodities within the selected country.
For the Average Benchmark, this value was used in the UK reference line in the bullet chart as a comparison to countries within the selected sector/commodity.
Calculation of “Market share of UK imports relative to the world”
This value determines the market share of UK imports relative to the world, and is used in the initial UK overview treemap. The equation used is as follows: