Difference between revisions of "G1-Group10"

From Geospatial Analytics for Urban Planning
Jump to navigation Jump to search
 
(51 intermediate revisions by 2 users not shown)
Line 14: Line 14:
 
| style="background:none;" width="1%" |  
 
| style="background:none;" width="1%" |  
 
| style="padding:0.2em; font-size:100%; background-color:#1D1D1D;  border-bottom:0px solid #3D9DD7; text-align:center; color:#F5F5F5" width="10%" |  
 
| style="padding:0.2em; font-size:100%; background-color:#1D1D1D;  border-bottom:0px solid #3D9DD7; text-align:center; color:#F5F5F5" width="10%" |  
−
[[Three_horrible_guys_Pizza_Poster|<font color="#F5F5F5" size=2 face="Helvetica"><b>POSTER</b></font>]]
+
[[Three_horrible_guys_Poster|<font color="#F5F5F5" size=2 face="Helvetica"><b>POSTER</b></font>]]
  
 
| style="background:none;" width="1%" | &nbsp;
 
| style="background:none;" width="1%" | &nbsp;
 
| style="padding:0.2em; font-size:100%; background-color:#1D1D1D;  border-bottom:0px solid #3D9DD7; text-align:center; color:#F5F5F5" width="10%" |  
 
| style="padding:0.2em; font-size:100%; background-color:#1D1D1D;  border-bottom:0px solid #3D9DD7; text-align:center; color:#F5F5F5" width="10%" |  
−
[[Three_horrible_guys_Application|<font color="#F5F5F5" size=2 face="Helvetica"><b>APPLICATION</b></font>]]
+
[[Three_horrible_guys_Web_Maps|<font color="#F5F5F5" size=2 face="Helvetica"><b>WEB MAPS</b></font>]]
  
 
| style="background:none;" width="1%" | &nbsp;
 
| style="background:none;" width="1%" | &nbsp;
 
| style="padding:0.2em; font-size:100%; background-color:#1D1D1D;  border-bottom:0px solid #3D9DD7; text-align:center; color:#F5F5F5" width="10%" |  
 
| style="padding:0.2em; font-size:100%; background-color:#1D1D1D;  border-bottom:0px solid #3D9DD7; text-align:center; color:#F5F5F5" width="10%" |  
−
[[Three_horrible_guys_Research Paper|<font color="#F5F5F5" size=2 face="Helvetica"><b>RESEARCH PAPER</b></font>]]
+
[[Three_horrible_guys_Project_Report|<font color="#F5F5F5" size=2 face="Helvetica"><b>REPORT</b></font>]]
 
|}  
 
|}  
 
<!--/Header-->
 
<!--/Header-->
Line 29: Line 29:
 
<br/>
 
<br/>
  
−
==<div style="background: #8b1209; padding: 15px; line-height: 0.3em; text-indent: 15px; font-size:18px; font-family:Helvetica"><font color= #FFFFFF>Introduction</font></div>==
+
==<div style="background: #8b1209; padding: 15px; line-height: 0.3em; text-indent: 15px; font-size:18px; font-family:Helvetica"><font color= #FFFFFF>Introduction & Motivation</font></div>==
 
<div style="font-family:Helvetica;font-size:16px">
 
<div style="font-family:Helvetica;font-size:16px">
 
International Food Chain (IFC) is a leading brand in its sector, with over 18000 outlets worldwide and an ever-growing presence in the global market. In Taiwan alone, IFC has over 240 branches and are constantly expanding.
 
International Food Chain (IFC) is a leading brand in its sector, with over 18000 outlets worldwide and an ever-growing presence in the global market. In Taiwan alone, IFC has over 240 branches and are constantly expanding.
  
−
However, as the franchise grows bigger, so does its challenges. One of the challenges involves the lack of an analysis to efficiently compare the performance of each chain to one another.
+
However, as the franchise grows bigger, so does its challenges. One of the challenges involves the lack of a geographical analysis to efficiently compare the performance of each chain to one another.
  
 
Leveraging on this fact, our group aims to digitalise the data and conduct in-depth analysis on each branch. We hope to track the performance of each chain in relation to Point-Of-Interests surrounding each chain, uncovering and comprehending phenomena, with the aid of spatial data.
 
Leveraging on this fact, our group aims to digitalise the data and conduct in-depth analysis on each branch. We hope to track the performance of each chain in relation to Point-Of-Interests surrounding each chain, uncovering and comprehending phenomena, with the aid of spatial data.
  
−
==<div style="background: #8b1209; padding: 15px; line-height: 0.3em; text-indent: 15px; font-size:18px; font-family:Helvetica"><font color= #FFFFFF>Problem and Motivation</font></div>==
+
This project was made in tandem with: https://wiki.smu.edu.sg/1920t1is428g1/Two_Eyes_One_Pizza
−
<div style="font-family:Helvetica;font-size:16px">
 
−
To provide an analysis that allows for:
 
−
 
 
−
* Digitizing of each chain’s trade and delivery area
 
−
* Business profiling of the company’s outlet to determine Points-Of-Interests that can generate insights such as: Highest earning outlets, relative performance of outlets, outlet’s profile patterns and item sales information.
 
−
* Allow for informed business decisions, such as determining locations for new outlet openings with matching POIs of high sales outlets
 
−
* Scalable program to incorporate future data to generate current information (Using data from other cities besides Taiwan)
 
−
* Easy and intuitive tool to quickly view information with regards to all branches
 
  
 
==<div style="background: #8b1209; padding: 15px; line-height: 0.3em; text-indent: 15px; font-size:18px; font-family:Helvetica"><font color= #FFFFFF>Objectives</font></div>==
 
==<div style="background: #8b1209; padding: 15px; line-height: 0.3em; text-indent: 15px; font-size:18px; font-family:Helvetica"><font color= #FFFFFF>Objectives</font></div>==
Line 52: Line 44:
 
This project aims to provide insights into the following:
 
This project aims to provide insights into the following:
  
−
# Outlets with the highest monthly sales
+
# Missing Areas in trade zone
−
# Relative monthly or yearly performance of each outlet
+
# Number of POIs surrounding each store
−
# Each branch geographical information, including the type and number of POI’s in the surroundings
+
# Store performance with regards to sales
−
# Profiling of similar types of branches
+
# Delivery Information
−
# Improvements for poor performing outlets
+
# Population Density
−
# Scalable data processing scripts to incorporate future data
+
# Buffer and proximity
−
 
+
# Nearest Competitors to store
−
==<div style="background: #8b1209; padding: 15px; line-height: 0.3em; text-indent: 15px; font-size:18px; font-family:Helvetica"><font color= #FFFFFF>Background Survey of Related Works (WIP)</font></div>==
+
# Variable importance based on regression analysis
−
<div style="font-family:Helvetica;font-size:11px">
 
−
{| class="wikitable" style="background-color:#ffffff;" width="100%"
 
−
|-
 
−
! style="font-weight: bold;background: #000000;color:#fbfcfd;width: 10%;" | Visualizations
 
−
! style="font-weight: bold;background: #000000;color:#fbfcfd;width: 10%;" | Explaination
 
−
|-
 
−
| [[Image:11.png|300px]]
 
−
<br>
 
−
||
 
−
Data source: https://www.researchgate.net/publication/324949619_Visualization_of_Fast_Food_Restaurant_Location_using_Geographical_Information_System<br>
 
−
 
−
The visualization provides the buffer polygons, as well as representing population density of the area through colour. By comparing the two, we can conclude whether the center of activity is proportional to the population density in a region. It allows us to perform further exploration to see what spatial information significantly affects the level of activity in a city, such as the availability of points-of-interest. This visualization is great as it allows the viewer to clearly see multiple dimensions dealing with spatial data in an elegant way.
 
−
 
 
−
|-
 
−
| [[Image:12.png|300px]]
 
−
<br>
 
−
||
 
−
Data source: https://www.researchgate.net/publication/324949619_Visualization_of_Fast_Food_Restaurant_Location_using_Geographical_Information_System<br>
 
−
The graph on the left shows the distribution of outlets on the geographical map. The right graph describes the outlets grid distribution, result from grid creation and spatial joint operation. From both figures, they can show the potential tendency of whether the outlets are clustered, and with the number of outlets in each grid. We could use them together to justify and adjust the outlet locations.
 
−
|-
 
−
| [[Image:13.png|300px|]]
 
−
<br>
 
−
||
 
−
Data source: https://www.researchgate.net/publication/324949619_Visualization_of_Fast_Food_Restaurant_Location_using_Geographical_Information_System<br>
 
−
This visualization provides a novel way of linking a variable to its geographical location when hovering over the respective area. It would be great in our case, if we were to allow the user to view the corresponding branch through the tooltip, for example profit.
 
−
|-
 
−
| [[Image:14.png|300px]]
 
−
<br>
 
−
||
 
−
Data source: https://www.researchgate.net/publication/324949619_Visualization_of_Fast_Food_Restaurant_Location_using_Geographical_Information_System<br>
 
−
This shows kernel density surface, based on the number of fast food restaurants around Jakarta and distribute them smoothly, so it provides average surface estimation. Kernel density estimation allows us to observe both the centrality and agglomeration of existing outlets. This visualization allows us to view multiple dimensions at a time in an effective manner, through the choice of colour and size.
 
−
|-
 
−
| [[Image:15.png|300px|]]
 
−
<br>
 
−
||
 
−
Data source:
 
−
https://public.tableau.com/profile/mirandali#!/vizhome/Salesforce-SalesPerformance/SalesPerformance<br>
 
−
 
−
This databoard shows the cumulative sales. We could learn from this and display by outlets to compare the performance by having multiple forms of visualization. We really like the fact that certain key summarizations and variables are displayed on the top, and will consider using this in our project.
 
−
|} </div>
 
  
 
==<div style="background: #8b1209; padding: 15px; line-height: 0.3em; text-indent: 15px; font-size:18px; font-family:Helvetica"><font color= #FFFFFF>Tools and Libraries</font></div>==
 
==<div style="background: #8b1209; padding: 15px; line-height: 0.3em; text-indent: 15px; font-size:18px; font-family:Helvetica"><font color= #FFFFFF>Tools and Libraries</font></div>==
Line 105: Line 57:
 
The following tools and libraries are used in the digitisation and analysis:
 
The following tools and libraries are used in the digitisation and analysis:
 
*QGIS
 
*QGIS
 +
*Excel
 
*Python
 
*Python
−
*R
 
−
*Tableau
 
  
 
</div>
 
</div>
Line 113: Line 64:
 
==<div style="background: #8b1209; padding: 15px; line-height: 0.3em; text-indent: 15px; font-size:18px; font-family:Helvetica"><font color= #FFFFFF>Datasets</font></div>==
 
==<div style="background: #8b1209; padding: 15px; line-height: 0.3em; text-indent: 15px; font-size:18px; font-family:Helvetica"><font color= #FFFFFF>Datasets</font></div>==
 
<p>
 
<p>
−
These are the datasets we plan to use:
+
Datasets Provided:
 
</p>
 
</p>
 +
 
{| class="wikitable" style="background-color:#FFFFFF;" width="100%"
 
{| class="wikitable" style="background-color:#FFFFFF;" width="100%"
 
|-
 
|-
Line 120: Line 72:
 
! style="font-weight: bold;background: #000000;color:#fbfcfd;" | Rationale
 
! style="font-weight: bold;background: #000000;color:#fbfcfd;" | Rationale
 
|-
 
|-
−
| <center> Administrative Boundaries, Taiwan </center> ||  
+
| <center> Traced Map </center> [[Image: A1.PNG |300px|center]]||
−
* A dataset containing SHP files of the administrative boundaries of taiwan (county, town, village)
+
* The client provided us with powerpoint files of manually drawn trade areas. These maps contain the various zones within the trade area, competitors, nearby stores as well as the drive time between each spots in the main road.
−
* Used as a reference to digitize IFC branch trade areas
+
* The five competitors defined by the client are:
 +
# Dominos
 +
# Napoleon
 +
# Mcdonalds
 +
# Kentucky Fried Chicken
 +
# Mos Burger
 +
 
 +
|-
 +
| <center> Geospatial Data </center>[[Image: A2.png |300px|center]] ||
 +
* The client provided us SHP files that contains information about Counties found in Taiwan
 +
<center>
 +
{| class="wikitable"
 +
|-
 +
! Column !! Description !! Example
 +
|-
 +
| COUNTYID|| Unique ID of each County || “U”
 +
|-
 +
| COUNTYCODE || Unique numerical ID of each County|| 10015
 +
|-
 +
| COUNTYNAME|| Name of the County in Mandarin|| “台北市”
 +
|-
 +
| COUNTYENG|| Name of the County in English|| “Taipei City”
 +
|}
 +
</center>
 +
|-
 +
| <center> Town Area </center> [[Image: A3.png |300px|center]]||
 +
* The client provided us SHP files that contains information about Towns found in Taiwan
 +
 
 +
<center>
 +
{| class="wikitable"
 +
|-
 +
! Column !! Description !! Example
 +
|-
 +
| VILLCODE|| Unique numerical ID of each Village || 65000050041
 +
|-
 +
| VILLNAME|| Name of the Village in Mandarin || “甲仙區”
 +
|-
 +
| VILLENG|| Name of the Village in English|| “Jiasian”
 +
|-
 +
| TOWNID|| Unique ID of each Town|| “K12”
 +
|-
 +
| TOWNCODE|| Unique numerical ID of each town || 10005060
 +
|-
 +
| TOWNNAME|| Name of the Town in Mandarin|| “半線城”
 +
|-
 +
| COUNTYID|| Unique ID of each County|| “U”
 +
|-
 +
| COUNTYCODE || Unique numerical ID of each Countyy|| 10015
 +
|-
 +
| COUNTYNAME|| Name of the County in Mandarin|| “台北市”
 +
|-
 +
| NOTE|| Miscellaneous notes || NIL
 +
|}
 +
</center>
 +
|-
 +
| <center> Village Area </center> [[Image: A4.png |300px|center]]||  
 +
* The client provided us SHP files that contains information about Villages found in Taiwan
 +
<center>
 +
{| class="wikitable"
 +
|-
 +
! Column !! Description !! Example
 +
|-
 +
| TOWNID|| Unique ID of each Town || “K12”
 +
|-
 +
| TOWNCODE || Unique numerical ID of each town || 10005060
 +
|-
 +
| TOWNNAME|| Name of the Town in Mandarin|| “半線城”
 +
|-
 +
| TOWNENG|| Name of the Town in English|| “Bamboo Town”
 +
|-
 +
| COUNTYID|| Unique ID of each County|| “U”
 +
|-
 +
| COUNTYCODE || Unique numerical ID of each County|| 10015
 +
|-
 +
| COUNTYNAME|| Name of the County in Mandarin|| “台北市”
 +
|}
 +
</center>
 +
|-
 +
| <center> Taiwan Stores </center> [[Image: A6.png |500px|center]] ||
 +
* The client provided us a GeoPackage that contains information about each IFC Store
 +
<center>
 +
{| class="wikitable"
 +
|-
 +
! Column !! Description !! Example
 +
|-
 +
| fid|| Unique ID of each Town || “K12”
 +
|-
 +
| Country|| Unique numerical ID of each town || 10005060
 +
|-
 +
| Market|| Name of the Town in Mandarin|| “半線城”
 +
|-
 +
| PH/PHD|| Name of the Town in English|| “Bamboo Town”
 +
|-
 +
| Status|| Unique ID of each County|| “U”
 +
|-
 +
| Milestone|| Unique numerical ID of each County|| 10015
 +
|-
 +
| Local Code|| Name of the County in Mandarin|| “台北市”
 +
|-
 +
| CHAMPS Code|| Name of the Town in English|| “Bamboo Town”
 +
|-
 +
| JDE Code|| Unique ID of each County|| “U”
 +
|-
 +
| Store Name|| Unique numerical ID of each County|| 10015
 +
|-
 +
| Latest Asset Type|| Name of the County in Mandarin|| “台北市”
 +
|-
 +
| Facility Type|| Name of the Town in English|| “Bamboo Town”
 +
|-
 +
| City Location|| Unique ID of each County|| “U”
 +
|-
 +
| Location Type|| Unique numerical ID of each County|| 10015
 +
|-
 +
| Open Date|| Name of the County in Mandarin|| “台北市”
 +
|-
 +
| Close Date|| Unique numerical ID of each County|| 10015
 +
|-
 +
| Corresponding Relo-Open / Relo-Closure Store Name|| Name of the County in Mandarin|| “台北市”
 +
|-
 +
| Corresponding Relo-Open / Relo-Closure Date|| Unique numerical ID of each County|| 10015
 +
|-
 +
| Corresponding Relo-Open / Relo-Closure Asset Type|| Name of the County in Mandarin|| “台北市”
 +
|-
 +
| Store Address|| Address of the store|| B1 & 1F., No. 52-1, Hsin Sheng S. Rd., Sec. 1, Taipei, Taiwan (R.O.C)
 +
|-
 +
| Latitude|| Latitude of the store|| 25.041601
 +
|-
 +
| Longitude|| Longitude of the store|| 121.532475
 +
|-
 +
| Month|| Month of the opening date of the store|| 10
 +
|-
 +
| Quarter|| Quarter of the opening date of the store|| Q4
 +
|-
 +
| Year|| Year of the opening date of the store|| FY1995
 +
|-
 +
| Grouping|| Used to denote which group these stores were assigned to|| G1 Group 10
 +
|-
 +
| Cluster ID|| Used to denote which group these stores were assigned to, in numerical value|| 6
 +
|}
 +
</center>
 +
|-
 +
| <center> POIs </center>[[Image: A6.png |300px|center]] ||
 +
* The client provided us SHP files that contains information about each POI. We used 32 out of the 86 POI SHPs given. They are:
 +
# ATM
 +
# BANK
 +
# BAR OR PUB
 +
# BOOKSTORE
 +
# BOWLING CENTRE
 +
# BUS STATION
 +
# BUSINESS FACILITY
 +
# CINEMA
 +
# CLOTHING STORE
 +
# COFFEE SHOP
 +
# COMMUTER RAIL STATION
 +
# CONSUMER ELECTRONICS STORE
 +
# CONVENIENCE STORE
 +
# DEPARTMENT STORE
 +
# INDUSTRIAL ZONE
 +
# GOVERNMENT OFFICE
 +
# GROCERY STORE
 +
# HIGHER EDUCATION
 +
# HOSPITAL
 +
# HOTEL
 +
# MEDICAL SERVICE
 +
# NIGHTLIFE
 +
# PERFORMING ARTS
 +
# PHARMACY
 +
# RESIDENTIAL AREA/BUILDING
 +
# RESTAURANT
 +
# SCHOOL
 +
# SHOPPING
 +
# SPECIALITY STORE
 +
# SPORTS CENTRE
 +
# SPORTS COMPLEX
 +
# TRAIN STATION
 +
<center>
 +
{| class="wikitable"
 +
|-
 +
! Column !! Description !! Example
 +
|-
 +
| fid || Unique numerical ID of each POI type || 25
 +
|-
 +
| LINK_ID || Unsure || 969985784
 +
|-
 +
| POI_ID || Unique numerical ID for each POI || 1201865541
 +
|-
 +
| SEQ_NUM || Unsure || 1
 +
|-
 +
| FAC_TYPE || Numerical ID for facility type || 9853
 +
|-
 +
|POI_NAME || Name of the POI || “王牙科”
 +
|-
 +
| POI_LANGCD || Unsure || “CHT”
 +
|-
 +
| POI_NMTYPE || Unsure || “B”
 +
|-
 +
| POI_ST_NUM || Unsure || 91
 +
|-
 +
| ST_NUM_FUL || Unsure || 124-1
 +
|-
 +
| ST_NFUL_LC || Unsure || “CHT”
 +
|-
 +
| ST_NAME || Name of ST || “中和路”
 +
|-
 +
| ST_LANGCD || Unsure || “CHT”
 +
|-
 +
| POI_ST_SD || Unsure || “L”
 +
|-
 +
| ACC_TYPE || Unsure || NIL
 +
|-
 +
| PH_NUMBER || Unsure || 3-5281997
 +
|-
 +
| CHAIN_ID || Unsure || 0
 +
|-
 +
| NAT_IMPORT || Unsure || “N”
 +
|-
 +
| PRIVATE || Unsure || “N”
 +
|-
 +
| IN_VICIN || Unsure || “N”
 +
|-
 +
| NUM_PARENT || Unsure || 0
 +
|-
 +
| NUM_CHILD || Unsure || 0
 +
|-
 +
| PERCFRREF || Unsure || 40
 +
|-
 +
| VANCITY_ID || Unsure || 0
 +
|-
 +
| ACT_ADDR || Unsure || NIL
 +
|-
 +
| ACT_LANGCD || Unsure || NIL
 +
|-
 +
| ACT_ST_NAM || Unsure || NIL
 +
|-
 +
| ACT_ADMIN || Unsure || NIL
 +
|-
 +
| ACT_POSTAL || Unsure || NIL
 +
|-
 +
| ENTR_TYPE || Unsure || NIL
 +
|}
 +
</center>
 +
|-
 +
| <center> Competitor POI’s </center> ||
 +
* The client also provided us SHP files that contains information about each individual store from 5 clients. The data has the same attributes as POIs (refer to above), with an addition column:
 +
<center>
 +
{| class="wikitable"
 +
|-
 +
! Column !! Description !! Example
 +
|-
 +
| FOOD_TYPE|| Type of food Competitor sells || “FAST FOOD”
 +
|}
 +
</center>
 +
 
 +
 
 +
|-
 +
| <center> Taiwan Road </center>[[Image: A7.png |300px|center]] ||
 +
* We obtained Taiwan Road SHP files online and managed to get it from mapcruzin.com. This was used in our shortest path analytical task.
 +
* Obtained from https://mapcruzin.com/free-taiwan-country-city-place-gis-shapefiles.htm
 +
 
 +
<center>
 +
{| class="wikitable"
 +
|-
 +
! Column !! Description !! Example
 +
|-
 +
| osm_id || Unique ID of the road  || 25
 +
|-
 +
| name || Name of the road || Alley 43-33, Ln. 361, Jieshou Rd. Sec. 2
 +
|-
 +
| ref || Unsure || 4
 +
|-
 +
| type || The type of road || “primary”
 +
|-
 +
| oneway || One hot encoded, 1 = oneway 0 = not oneway || 1
 +
|-
 +
| bridge || One hot encoded,, 1 = road on bridge 0 = not on bridge || 0
 +
|-
 +
| tunnel || One hot encoded, 1 = road in a tunnel = 0 not in tunnel || 0
 +
|-
 +
| maxspeed || Max allowed speed on road || 90
 +
|}
 +
</center>
 +
 
 +
|-
 +
| <center> Sales Data </center>[[Image: A8.png |300px|center]] ||
 +
* The client gave us a CSV file containing yearly sales information of each region, further broken down into zone
 +
 
 +
<center>
 +
{| class="wikitable"
 +
|-
 +
! Column !! Description !! Example
 +
|-
 +
| Zone || Name of the Zone || “D-05”
 +
|-
 +
| Bills || Numerical value of bills || 666
 +
|-
 +
| Bills % || Percentage of total number of bills || 24.46
 +
|-
 +
| Amount || Total monetary amount of sales || 450182
 +
|-
 +
| Amount % || Percentage of Total monetary amount of sales || 23.88
 +
|-
 +
| Ave Bill || Average monetary amount from sales || 675.95
 +
|-
 +
| Shop Code Sales || String used to denote shop code || “AE”
 +
|}
 +
</center>
 +
 
 +
|-
 +
| <center> Population Data </center> ||
 +
* We obtained population data with regards to Taiwan online. The XLS file contains population Data is from the year 2010 and is in, uncleaned table format XLS. This was used as an addition feature in our analysis.
 +
* Obtained from https://census.dgbas.gov.tw/PHC2010/english/rehome.htm
 +
 
 +
 
 +
<center>
 +
{| class="wikitable"
 +
|-
 +
| Number of resident population: Grand total || Total number of residents, male + female || 23123866
 
|-
 
|-
−
| <center> Branch location of IFC, Taiwan </center> ||  
+
| Number of resident population: Male || Total number of male residents || 11489285
−
* A dataset containing the geographical information of each individual branch.
 
−
* Used as the main target of our project
 
 
|-
 
|-
−
| <center> Point of Interests , Taiwan </center> ||  
+
| Number of resident population: Female || Total number of female residents || 11634581
−
* A dataset containing each individual Point-Of-Interests in Taiwan (e.g. ATMs, Amusement Parks, Banks)
 
−
* Used as features for analysis with regards to each branch
 
 
|-
 
|-
−
| <center> Powerpoint Slides of trade areas for each branch </center> ||  
+
| Total Land Area (km2) || Total monetary amount of sales || 36191.5
−
* A dataset containing each individual hand drawn trade area for each branch in Taiwan
 
−
* Used as features for analysis with regards to each branch
 
 
|-
 
|-
−
| <center> Outlets Monthly Sales Data </center> ||  
+
| Population Density (person/km2) || Percentage of Total monetary amount of sales || 638.9
−
* A dataset containing the monthly sales information of each individual branch
+
|-
−
* Used to study the sales data along with the profile of each branch to generate yielding patterns (e.g. top and bottom performer)
+
| By Country/City || Country/City the row of data belongs to || “Taipei City”
 +
|}
 +
</center>
 
|-
 
|-
 
|}
 
|}
Line 156: Line 420:
 
NA
 
NA
 
|-
 
|-
−
| <center> Unfamiliarity in R and Python in creation of data processing scripts </center>  
+
| <center> Unfamiliarity in Python integration with QGIS regarding creation of data processing scripts </center>  
 
||  
 
||  
−
* Watching video tutorials about R and Python
+
* Watching video tutorials about Python and QGIS
 
* Independent learning on the design and syntax
 
* Independent learning on the design and syntax
 
* Peer learning and sharing
 
* Peer learning and sharing
−
* Using Datacamp as our mentor
+
 
 
||
 
||
 
We managed to start using the languages quickly and suit our own project needs.
 
We managed to start using the languages quickly and suit our own project needs.
Line 195: Line 459:
 
|}
 
|}
  
−
==<div style="background: #8b1209; padding: 15px; line-height: 0.3em; text-indent: 15px; font-size:18px; font-family:Helvetica"><font color= #FFFFFF>Roles & Milestones (WIP)</font></div>==
+
==<div style="background: #8b1209; padding: 15px; line-height: 0.3em; text-indent: 15px; font-size:18px; font-family:Helvetica"><font color= #FFFFFF>Scope of work</font></div>==
 +
<div style="font-family:Helvetica;font-size:16px">
 +
*Roles
 +
{| class="wikitable" style="background-color:#FFFFFF;" width="100%"
 +
|-
 +
! style="font-weight: bold;background: #000000;color:#fbfcfd;width: 33%;" | Kelvin Chia Sen Wei
 +
! style="font-weight: bold;background: #000000;color:#fbfcfd;width: 33%;" | Linus Cheng Xin Wei
 +
! style="font-weight: bold;background: #000000;color:#fbfcfd;width: 33%;" | Eugene Choy Wen Jie
 +
|-
 +
| <center>
 +
Data Cleaner in Python <br>
 +
Wiki Writer/Editor <br>
 +
Chart Creator <br>
 +
Map Digitizer 1 <br>
 +
Report Writer 1
 +
</center>
 +
||
 +
<center>
 +
Project Manager <br/>
 +
Content Checker<br/>
 +
Poster man <br>
 +
Map Digitizer 2 <br>
 +
Report Writer 2
 +
</center>
 +
 
 +
||
 +
<center>
 +
Data Cleaner in Excel<br/>
 +
QGIS Manager <br>
 +
Map Creator <br>
 +
Map Digitizer 3 <br>
 +
Report Writer 3
 +
</center>
 +
 
 +
|}
 +
 
 +
==<div style="background: #8b1209; padding: 15px; line-height: 0.3em; text-indent: 15px; font-size:18px; font-family:Helvetica"><font color= #FFFFFF>Project Schedule</font></div>==
 +
 
 +
Project Gantt chart:
  
−
*Project Timeline
+
[[File:Ganttgantt.png|1200px|none]]
−
[[Image: Tl.png |600px|center]]
 
  
 
==<div style="background: #8b1209; padding: 15px; line-height: 0.3em; text-indent: 15px; font-size:18px; font-family:Helvetica"><font color= #FFFFFF>References</font></div>==
 
==<div style="background: #8b1209; padding: 15px; line-height: 0.3em; text-indent: 15px; font-size:18px; font-family:Helvetica"><font color= #FFFFFF>References</font></div>==

Latest revision as of 02:10, 22 November 2019

3hg.png

Back to Project Home

 

PROPOSAL

 

DATA TRANSFORMATION

 

POSTER

 

WEB MAPS

 

REPORT


Introduction & Motivation

International Food Chain (IFC) is a leading brand in its sector, with over 18000 outlets worldwide and an ever-growing presence in the global market. In Taiwan alone, IFC has over 240 branches and are constantly expanding.

However, as the franchise grows bigger, so does its challenges. One of the challenges involves the lack of a geographical analysis to efficiently compare the performance of each chain to one another.

Leveraging on this fact, our group aims to digitalise the data and conduct in-depth analysis on each branch. We hope to track the performance of each chain in relation to Point-Of-Interests surrounding each chain, uncovering and comprehending phenomena, with the aid of spatial data.

This project was made in tandem with: https://wiki.smu.edu.sg/1920t1is428g1/Two_Eyes_One_Pizza

Objectives

This project aims to provide insights into the following:

  1. Missing Areas in trade zone
  2. Number of POIs surrounding each store
  3. Store performance with regards to sales
  4. Delivery Information
  5. Population Density
  6. Buffer and proximity
  7. Nearest Competitors to store
  8. Variable importance based on regression analysis

Tools and Libraries

The following tools and libraries are used in the digitisation and analysis:

  • QGIS
  • Excel
  • Python

Datasets

Datasets Provided:

Dataset Rationale
Traced Map
A1.PNG
  • The client provided us with powerpoint files of manually drawn trade areas. These maps contain the various zones within the trade area, competitors, nearby stores as well as the drive time between each spots in the main road.
  • The five competitors defined by the client are:
  1. Dominos
  2. Napoleon
  3. Mcdonalds
  4. Kentucky Fried Chicken
  5. Mos Burger
Geospatial Data
A2.png
  • The client provided us SHP files that contains information about Counties found in Taiwan
Column Description Example
COUNTYID Unique ID of each County “U”
COUNTYCODE Unique numerical ID of each County 10015
COUNTYNAME Name of the County in Mandarin “台北市”
COUNTYENG Name of the County in English “Taipei City”
Town Area
A3.png
  • The client provided us SHP files that contains information about Towns found in Taiwan
Column Description Example
VILLCODE Unique numerical ID of each Village 65000050041
VILLNAME Name of the Village in Mandarin “甲仙區”
VILLENG Name of the Village in English “Jiasian”
TOWNID Unique ID of each Town “K12”
TOWNCODE Unique numerical ID of each town 10005060
TOWNNAME Name of the Town in Mandarin “半線城”
COUNTYID Unique ID of each County “U”
COUNTYCODE Unique numerical ID of each Countyy 10015
COUNTYNAME Name of the County in Mandarin “台北市”
NOTE Miscellaneous notes NIL
Village Area
A4.png
  • The client provided us SHP files that contains information about Villages found in Taiwan
Column Description Example
TOWNID Unique ID of each Town “K12”
TOWNCODE Unique numerical ID of each town 10005060
TOWNNAME Name of the Town in Mandarin “半線城”
TOWNENG Name of the Town in English “Bamboo Town”
COUNTYID Unique ID of each County “U”
COUNTYCODE Unique numerical ID of each County 10015
COUNTYNAME Name of the County in Mandarin “台北市”
Taiwan Stores
A6.png
  • The client provided us a GeoPackage that contains information about each IFC Store
Column Description Example
fid Unique ID of each Town “K12”
Country Unique numerical ID of each town 10005060
Market Name of the Town in Mandarin “半線城”
PH/PHD Name of the Town in English “Bamboo Town”
Status Unique ID of each County “U”
Milestone Unique numerical ID of each County 10015
Local Code Name of the County in Mandarin “台北市”
CHAMPS Code Name of the Town in English “Bamboo Town”
JDE Code Unique ID of each County “U”
Store Name Unique numerical ID of each County 10015
Latest Asset Type Name of the County in Mandarin “台北市”
Facility Type Name of the Town in English “Bamboo Town”
City Location Unique ID of each County “U”
Location Type Unique numerical ID of each County 10015
Open Date Name of the County in Mandarin “台北市”
Close Date Unique numerical ID of each County 10015
Corresponding Relo-Open / Relo-Closure Store Name Name of the County in Mandarin “台北市”
Corresponding Relo-Open / Relo-Closure Date Unique numerical ID of each County 10015
Corresponding Relo-Open / Relo-Closure Asset Type Name of the County in Mandarin “台北市”
Store Address Address of the store B1 & 1F., No. 52-1, Hsin Sheng S. Rd., Sec. 1, Taipei, Taiwan (R.O.C)
Latitude Latitude of the store 25.041601
Longitude Longitude of the store 121.532475
Month Month of the opening date of the store 10
Quarter Quarter of the opening date of the store Q4
Year Year of the opening date of the store FY1995
Grouping Used to denote which group these stores were assigned to G1 Group 10
Cluster ID Used to denote which group these stores were assigned to, in numerical value 6
POIs
A6.png
  • The client provided us SHP files that contains information about each POI. We used 32 out of the 86 POI SHPs given. They are:
  1. ATM
  2. BANK
  3. BAR OR PUB
  4. BOOKSTORE
  5. BOWLING CENTRE
  6. BUS STATION
  7. BUSINESS FACILITY
  8. CINEMA
  9. CLOTHING STORE
  10. COFFEE SHOP
  11. COMMUTER RAIL STATION
  12. CONSUMER ELECTRONICS STORE
  13. CONVENIENCE STORE
  14. DEPARTMENT STORE
  15. INDUSTRIAL ZONE
  16. GOVERNMENT OFFICE
  17. GROCERY STORE
  18. HIGHER EDUCATION
  19. HOSPITAL
  20. HOTEL
  21. MEDICAL SERVICE
  22. NIGHTLIFE
  23. PERFORMING ARTS
  24. PHARMACY
  25. RESIDENTIAL AREA/BUILDING
  26. RESTAURANT
  27. SCHOOL
  28. SHOPPING
  29. SPECIALITY STORE
  30. SPORTS CENTRE
  31. SPORTS COMPLEX
  32. TRAIN STATION
Column Description Example
fid Unique numerical ID of each POI type 25
LINK_ID Unsure 969985784
POI_ID Unique numerical ID for each POI 1201865541
SEQ_NUM Unsure 1
FAC_TYPE Numerical ID for facility type 9853
POI_NAME Name of the POI “王牙科”
POI_LANGCD Unsure “CHT”
POI_NMTYPE Unsure “B”
POI_ST_NUM Unsure 91
ST_NUM_FUL Unsure 124-1
ST_NFUL_LC Unsure “CHT”
ST_NAME Name of ST “中和路”
ST_LANGCD Unsure “CHT”
POI_ST_SD Unsure “L”
ACC_TYPE Unsure NIL
PH_NUMBER Unsure 3-5281997
CHAIN_ID Unsure 0
NAT_IMPORT Unsure “N”
PRIVATE Unsure “N”
IN_VICIN Unsure “N”
NUM_PARENT Unsure 0
NUM_CHILD Unsure 0
PERCFRREF Unsure 40
VANCITY_ID Unsure 0
ACT_ADDR Unsure NIL
ACT_LANGCD Unsure NIL
ACT_ST_NAM Unsure NIL
ACT_ADMIN Unsure NIL
ACT_POSTAL Unsure NIL
ENTR_TYPE Unsure NIL
Competitor POI’s
  • The client also provided us SHP files that contains information about each individual store from 5 clients. The data has the same attributes as POIs (refer to above), with an addition column:
Column Description Example
FOOD_TYPE Type of food Competitor sells “FAST FOOD”


Taiwan Road
A7.png
Column Description Example
osm_id Unique ID of the road 25
name Name of the road Alley 43-33, Ln. 361, Jieshou Rd. Sec. 2
ref Unsure 4
type The type of road “primary”
oneway One hot encoded, 1 = oneway 0 = not oneway 1
bridge One hot encoded,, 1 = road on bridge 0 = not on bridge 0
tunnel One hot encoded, 1 = road in a tunnel = 0 not in tunnel 0
maxspeed Max allowed speed on road 90
Sales Data
A8.png
  • The client gave us a CSV file containing yearly sales information of each region, further broken down into zone
Column Description Example
Zone Name of the Zone “D-05”
Bills Numerical value of bills 666
Bills % Percentage of total number of bills 24.46
Amount Total monetary amount of sales 450182
Amount % Percentage of Total monetary amount of sales 23.88
Ave Bill Average monetary amount from sales 675.95
Shop Code Sales String used to denote shop code “AE”
Population Data
  • We obtained population data with regards to Taiwan online. The XLS file contains population Data is from the year 2010 and is in, uncleaned table format XLS. This was used as an addition feature in our analysis.
  • Obtained from https://census.dgbas.gov.tw/PHC2010/english/rehome.htm


Number of resident population: Grand total Total number of residents, male + female 23123866
Number of resident population: Male Total number of male residents 11489285
Number of resident population: Female Total number of female residents 11634581
Total Land Area (km2) Total monetary amount of sales 36191.5
Population Density (person/km2) Percentage of Total monetary amount of sales 638.9
By Country/City Country/City the row of data belongs to “Taipei City”

Foreseen Technical Challenges

We encountered the following technical challenges throughout the course of the project. We have indicated our proposed solutions, and the outcomes of the solutions.

Key Technical Challenges Proposed Solution Outcome
Data is already pre-aggregated to display monthly sales
  • The dataset is given directly to us from IFC, and we are unable to change it. Thus, We shall utilize and do our best with the available data.

NA

Unfamiliarity in Python integration with QGIS regarding creation of data processing scripts
  • Watching video tutorials about Python and QGIS
  • Independent learning on the design and syntax
  • Peer learning and sharing

We managed to start using the languages quickly and suit our own project needs. Each of us work on different parts such as setting up, designing, logic and deployment. This speeds up our project progress.

Data Cleaning & Transformation Proposed Solution
  • Having a systematic process while working together in order to maximise efficiency e.g. taking turns to clean, transform and perform checks on the data to ensure accuracy

The adopted process was having clear instructions issued to each member in the team, along with maintaining constant communication with each other. In the event that the dataset is deemed too dirty to be usable, it was dropped along with sourcing for new data that would be a suitable replacement.

Lack of geospatial knowledge to understand the dataset initially
  • Attend SMT201 class to learn more, as well as reading up on resources given by Prof Kam to gain further contextual knowledge

NA

Digitising of trade areas from powerpoint slide to QGIS
  • The process is manual and we had to put in a lot of effort to convert the drawn polygon to data points in QGIS.

The data points can better allow us to generate insights on the profile of each outlet via its trade area.

Integrating Relevant Data from Multiple Sources Proposed Solution
  • Working together to decide on what data to extract or eliminate

NA

Scope of work

  • Roles
Kelvin Chia Sen Wei Linus Cheng Xin Wei Eugene Choy Wen Jie

Data Cleaner in Python
Wiki Writer/Editor
Chart Creator
Map Digitizer 1
Report Writer 1

Project Manager
Content Checker
Poster man
Map Digitizer 2
Report Writer 2

Data Cleaner in Excel
QGIS Manager
Map Creator
Map Digitizer 3
Report Writer 3

Project Schedule

Project Gantt chart:

Ganttgantt.png

References

Comments

Feel free to leave comments / suggestions!


Retrieved from ‘https://wiki.smu.edu.sg/1920t1smt201/index.php?title=G1-Group10&oldid=11614’