Difference between revisions of "ANLY482 AY2016-17 T2 Group19 Data"

From Analytics Practicum
Jump to navigation Jump to search
 
(24 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 +
<!--Begin Header-->
 +
 
<!--Logo-->
 
<!--Logo-->
 
<br>
 
<br>
Line 4: Line 6:
  
 
<!--Navigation-->
 
<!--Navigation-->
<!--Navigation inspiration (table form) credits to  https://wiki.smu.edu.sg/anly104/Chocotastic -->
+
<center>  
 
+
{| style="border-collapse: separate; border-spacing: 0; font-size:13px; font-family: Trajan Pro; border-width: 0px; border-radius: 0px; border-style: solid; border-color: #ffffff; padding: 0;  height: 0px;" width="100%"
<center>
 
{| style="border-collapse: separate; border-spacing: 0; font-size:16px; font-family: Century Gothic; border-width: 1px; border-radius: 7px; border-style: solid; border-color: #ffffff; padding: 0;  height: 30px;" width="50%"
 
 
|-
 
|-
  
! style="border-style: none; border-width: 0 1px 1px 0; width: 12%"| [[ANLY482_AY2016-17_T2_Group19|<font color="#000000"><b>OVERVIEW</b></font>]]
+
! style="border-style: none; border-width: 0 1px 1px 0"|  
 
+
[[File:Protege_overview.svg|40px|link= ANLY482_AY2016-17_T2_Group19 ]] &nbsp;
! style="border-style: none; border-width: 0 1px 1px 0; width: 12%;  border-left:3px solid #a4a4a6; background-color:#ffffff"| [[ANLY482_AY2016-17_T2_Group19_Data| <font color="#a1212e"><b>DATA</b></font>]]
+
[[ANLY482_AY2016-17_T2_Group19|<font color="#000000"><b>OVERVIEW</b></font>]]
  
! style="border-style: none; border-width: 0 1px 1px 0; width: 12%; border-left:3px solid #a4a4a6; background-color:#ffffff"| [[ANLY482_AY2016-17_T2_Group19_Methodology|<font color="#000000"><b>METHODOLOGY</b></font>]]
+
! style="border-style: none; border-width: 0 1px 1px 0; border-left:0px solid #a4a4a6; background-color:#ffffff"|  
 +
[[File:Protege_data.svg|40px|link= ANLY482_AY2016-17_T2_Group19_Data ]] &nbsp;
 +
[[ANLY482_AY2016-17_T2_Group19_Data| <font color="#a1212e"><b>DATA</b></font>]]
  
! style="border-style: none; border-width: 0 1px 1px 0; width: 12%; border-left:3px solid #a4a4a6; background-color:#ffffff"| [[ANLY482_AY2016-17_T2_Group19_Analysis| <font color="#000000"><b>ANALYSIS</b></font>]]
+
! style="border-style: none; border-width: 0 1px 1px 0; border-left:0px solid #a4a4a6; background-color:#ffffff"|  
 +
[[File:Protege_Methods.svg|40px|link= ANLY482_AY2016-17_T2_Group19_Methodology ]] &nbsp;
 +
[[ANLY482_AY2016-17_T2_Group19_Methodology|<font color="#000000"><b>METHODOLOGY & ANALYSIS</b></font>]]
  
! style="border-style: none; border-width: 0 1px 1px 0; width: 12%; border-left:3px solid #a4a4a6; background-color:#ffffff"| [[ANLY482_AY2016-17_T2_Group19_Findings| <font color="#000000"><b>FINDINGS</b></font>]]
+
! style="border-style: none; border-width: 0 1px 1px 0; border-left:0px solid #a4a4a6; background-color:#ffffff"|  
 +
[[File:Protegemaster-03.svg|40px|link= ANLY482_AY2016-17_T2_Group19_Findings ]] &nbsp;
 +
[[ANLY482_AY2016-17_T2_Group19_Findings| <font color="#000000"><b>FINDINGS</b></font>]]
  
! style="border-style: none; border-width: 0 1px 1px 0; width: 12%; border-left:3px solid #a4a4a6; background-color:#ffffff"| [[ANLY482_AY2016-17_T2_Group19_Poster| <font color="#000000"><b>POSTER</b></font>]]
+
! style="border-style: none; border-width: 0 1px 1px 0; border-left:0px solid #a4a4a6; background-color:#ffffff"|  
 +
[[File:Protege_poster.svg|40px|link= ANLY482_AY2016-17_T2_Group19_Poster ]] &nbsp;
 +
[[ANLY482_AY2016-17_T2_Group19_Documentation| <font color="#000000"><b>DOCUMENTATION</b></font>]]
  
 +
! style="border-style: none; border-width: 0 1px 1px 0; border-left:0px solid #a4a4a6; background-color:#ffffff"|
 +
&nbsp;
 +
[[ANLY482_AY2016-17_Term_2| <font color="#000000"><b>BACK TO COURSE</b></font>]]
 
|}
 
|}
 
</center>
 
</center>
<div style="background: #4d4d4d; padding: 20px; line-height: 0.1em;  text-indent: 10px; font-size:20px; font-family: Rockwell;  border-radius: 7px; border-bottom:3px solid #ba3749"><font color= #FFF>Data</font></div>
+
<!--End Navigation-->
 +
<!--End Header-->
 +
 
 +
 
 +
<div style="background: #4d4d4d; padding: 20px; line-height: 0.1em;  text-indent: 10px; font-size:20px; font-family: Trajan Pro;  border-radius: 7px; border-bottom:3px solid #ba3749"><font color= #FFF>Data</font></div>
 
<p>
 
<p>
=<div style="background: #4d4d4d; padding: 20px;  line-height: 0.1em;  text-indent: 10px; font-size:20px; font-family: Rockwell;  border-radius: 7px; border-bottom:3px solid #ba3749"><font color= #ffffff>Background</font></div>=
+
=<div style="background: #4d4d4d; padding: 20px;  line-height: 0.1em;  text-indent: 10px; font-size:20px; font-family: Trajan Pro;  border-radius: 7px; border-bottom:3px solid #ba3749"><font color= #ffffff>Background</font></div>=
  
 
<div style="margin:0px; padding: 10px; background: #f2f4f4; font-family: Arial, sans-serif; border-radius: 7px; ">
 
<div style="margin:0px; padding: 10px; background: #f2f4f4; font-family: Arial, sans-serif; border-radius: 7px; ">
Line 35: Line 50:
 
</div><br>
 
</div><br>
  
=<div style="background: #4d4d4d; padding: 20px;  line-height: 0.1em;  text-indent: 10px; font-size:20px; font-family: Rockwell;  border-radius: 7px; border-bottom:3px solid #ba3749"><font color= #ffffff>Data Description</font></div>=
+
=<div style="background: #4d4d4d; padding: 20px;  line-height: 0.1em;  text-indent: 10px; font-size:20px; font-family: Trajan Pro;  border-radius: 7px; border-bottom:3px solid #ba3749"><font color= #ffffff>Data Description & Acknowledgement</font></div>=
 
<div style="margin:0px; padding: 10px; background: #f2f4f4; font-family: Arial, sans-serif; border-radius: 7px; ">
 
<div style="margin:0px; padding: 10px; background: #f2f4f4; font-family: Arial, sans-serif; border-radius: 7px; ">
#insert table format of data set, only if not sensitive
+
'''The raw data was collected from Company Z on the 12th of January 2016 and is described as follows:'''
 +
*Sales data from Pharmaceutical Wholesale Distributions
 +
*Itemised transactions occurring in the years 2014 to 2016
 +
*3 main customer types namely General Practitioners, Specialists and Branded Chain Pharmacies
 +
 
 +
'''Additional Data was requested to further enhance the quality and relevance of the analysis:'''
 +
 
 +
1) Classification list for the medical products sold.
 +
 
 +
2) Customers with ‘CGP’ as customer type to be mapped to the URA’s Master Plan 2014 Subzone Boundary list.
 +
 
 +
'''Other sources of data include:'''
 +
 
 +
1) oneMap (Development of R script to geocode customer postal codes)
 +
 
 +
2) Data@gov (Singapore Subzone SHP File and Master plan 2014 subzone boundaries)
 +
 
 
</div>
 
</div>
  
=<div style="background: #4d4d4d; padding: 20px;  line-height: 0.1em;  text-indent: 10px; font-size:20px; font-family: Rockwell;  border-radius: 7px; border-bottom:3px solid #ba3749"><font color= #ffffff>Purpose of the model</font></div>=
+
=<div style="background: #4d4d4d; padding: 20px;  line-height: 0.1em;  text-indent: 10px; font-size:20px; font-family: Trajan Pro;  border-radius: 7px; border-bottom:3px solid #ba3749"><font color= #ffffff> Data Cleaning and Preparation </font></div>=
 
<div style="margin:0px; padding: 10px; background: #f2f4f4; font-family: Arial, sans-serif; border-radius: 7px;">
 
<div style="margin:0px; padding: 10px; background: #f2f4f4; font-family: Arial, sans-serif; border-radius: 7px;">
Given the context of this data set...<br><br> Through these model...
 
</div>
 
  
=<div style="background: #4d4d4d; padding: 20px;  line-height: 0.1em;  text-indent: 10px; font-size:20px; font-family: Rockwell;  border-radius: 7px; border-bottom:3px solid #ba3749"><font color= #ffffff> Data Exploration </font></div>=
+
'''DATA STAGING'''
 +
 
 +
Preliminary data exploration was done in Jmp from SAS prior to data preparation to better understand the structure of the dataset and additional data that would be required. Following which, the bulk of the data preparation was done in R following discussion with our supervisor and the client on continuity post-project. The following log is a record of steps taken to transform, clean and supplement the data for further exploratory data analysis.
 +
 
 +
Verifications were also carried out with Company Z with regards to anomalies such as negative or zero entries which were corrective in nature and hence were not removed where analysis were done mainly on the aggregation of numbers resulting in the correction of numericals.
  
<div style="background: #4d4d4d; padding: 10px;  line-height: 0.1em;  text-indent: 10px; font-size:20px; font-family: Rockwell;  border-radius: 5px; border-bottom:2px solid #ba3749"><font color= #ffffff> Categorical Data </font></div>
+
[[File:Tablestage.png|center]]
  
<div style="margin:0px; padding: 10px; background: #f2f4f4; font-family: Arial, sans-serif; border-radius: 7px; ">
+
[[File:Flowchartstage.png|center]]
<b>-Insert title of variable-</b><br>
 
-Insert Description of variable and comment on the preparatory work to be done-  <br>
 
#insert image if any
 
</div><br>
 
  
<div style="background: #4d4d4d; padding: 10px;  line-height: 0.1em;  text-indent: 10px; font-size:20px; font-family: Rockwell;  border-radius: 5px; border-bottom:2px solid #ba3749"><font color= #ffffff> Continuous Data </font></div>
+
Refer to the '''FINDINGS''' tab for the results of exploratory data analysis.
  
 +
</div>
 +
=<div style="background: #4d4d4d; padding: 20px;  line-height: 0.1em;  text-indent: 10px; font-size:20px; font-family: Trajan Pro;  border-radius: 7px; border-bottom:3px solid #ba3749"><font color= #ffffff> Data Preparation for Interactive Visual Analytical Dashboard </font></div>=
 
<div style="margin:0px; padding: 10px; background: #f2f4f4; font-family: Arial, sans-serif; border-radius: 7px;">
 
<div style="margin:0px; padding: 10px; background: #f2f4f4; font-family: Arial, sans-serif; border-radius: 7px;">
<b> -Insert title of variable-</b><br>
+
Data preparation was separated into '''2 main phases''' based on the frequency for the need of re-launching.
-Insert Description of variable and comment on the preparatory work to be done-
 
#insert image if any
 
</div><br>
 
  
<div style="background: #4d4d4d; padding: 10px;  line-height: 0.1em;  text-indent: 10px; font-size:20px; font-family: Rockwell;  border-radius: 5px; border-bottom:2px solid #ba3749"><font color= #ffffff> Multicollinearity </font></div>
+
The '''first phase''' is carried out in the coredataprep.R script where raw data is taken in and prepared for further processing. In this implementation of the IVAD, raw data received was in the form of transactional data retrieved using SQL from the sponsor’s ERP system. As such, data was in the form of relations and multiple table definitions. The main processes in this phase include: Data aggregation, batch geocoding, point in polygon analysis.
<div style="margin:0px; padding: 10px; background: #f2f4f4; font-family: Arial, sans-serif; border-radius: 7px;">
+
Data was firstly prepared by aggregating the 3 datasets (over 3 years) into a single dataset and a date format column was generated.
-Discuss any overlapping collinearity <br>
+
Batch geocoding and point in polygon analysis is carried out on customer postal codes. Batch geocoding was carried out using a simple R script and coordinates were then tagged to the appropriate CRS and point in polygon analysis was done to group the points by subzones marked out in the MP14. As this process is highly computationally intensive, this process runs in the coredataprep script which is to be run on the occasion where new customers are added. The key output of this script is a coredata file which serves as a master working copy of all relevant information.
#insert image if any
+
At the current stage of development, this script takes in data in the form of excel spreadsheets, however, at point of enterprise integration, it is highly plausible for tweaks to be made to the script such that it would be able to directly query the system using SQL.
</div><br><br>
 
  
=<div style="background: #4d4d4d; padding: 20px;  line-height: 0.1em;  text-indent: 10px; font-size:20px; font-family: Rockwell;  border-radius: 7px; border-bottom:3px solid #ba3749"><font color= #ffffff> Data Cleaning and Preparation </font></div>=
+
The '''second phase''' of data preparation includes less computationally intensive processes that must be run more often are carried out. Such processes include the preparation of data tables for plotting in the app. Generation of tree map coordinates also occurs in this script so as to reduce the resource load at the app stage. By pre-calculating the coordinates, considerable reductions on loading speeds where achieved. The majority of data tables that are to be used for visualisations are generated in this phased and output as an environment image file (.Rdata) that allows for the rapid loading of objects into the shiny app.  
 +
Other non-R data preparation steps include the identification and reprojection of the MP14 shape files as well as the generation of subzone centre points in the form of shape files. These 2 processes were carried out in Qgis.
  
<div style="margin:0px; padding: 10px; background: #f2f4f4; font-family: Arial, sans-serif; border-radius: 7px;">
 
<b> -insert variable that has been cleaned- </b><br>
 
-discuss the treatment and why the treatment-
 
 
</div><br>
 
</div><br>

Latest revision as of 00:32, 24 April 2017



Protegelogo-01.svg

Protege overview.svg   OVERVIEW

Protege data.svg   DATA

Protege Methods.svg   METHODOLOGY & ANALYSIS

Protegemaster-03.svg   FINDINGS

Protege poster.svg   DOCUMENTATION

  BACK TO COURSE


Data

Background

In the pharmaceutical industry, it has historically been a challenge to manage hundreds of hospitals and clinics with sizeable differences in the types of drugs, disposable items and the sheer volume in respective purchase orders

While not exclusive to this industry alone, managerial decision making processes have historically relied heavily on transactional raw data and managerial experience

The data set that was used contains sales data from a medium-sized pharmaceutical company with customer base ranging from over the counter pharmacies to clinics around Singapore.


Data Description & Acknowledgement

The raw data was collected from Company Z on the 12th of January 2016 and is described as follows:

  • Sales data from Pharmaceutical Wholesale Distributions
  • Itemised transactions occurring in the years 2014 to 2016
  • 3 main customer types namely General Practitioners, Specialists and Branded Chain Pharmacies

Additional Data was requested to further enhance the quality and relevance of the analysis:

1) Classification list for the medical products sold.

2) Customers with ‘CGP’ as customer type to be mapped to the URA’s Master Plan 2014 Subzone Boundary list.

Other sources of data include:

1) oneMap (Development of R script to geocode customer postal codes)

2) Data@gov (Singapore Subzone SHP File and Master plan 2014 subzone boundaries)

Data Cleaning and Preparation

DATA STAGING

Preliminary data exploration was done in Jmp from SAS prior to data preparation to better understand the structure of the dataset and additional data that would be required. Following which, the bulk of the data preparation was done in R following discussion with our supervisor and the client on continuity post-project. The following log is a record of steps taken to transform, clean and supplement the data for further exploratory data analysis.

Verifications were also carried out with Company Z with regards to anomalies such as negative or zero entries which were corrective in nature and hence were not removed where analysis were done mainly on the aggregation of numbers resulting in the correction of numericals.

Tablestage.png
Flowchartstage.png

Refer to the FINDINGS tab for the results of exploratory data analysis.

Data Preparation for Interactive Visual Analytical Dashboard

Data preparation was separated into 2 main phases based on the frequency for the need of re-launching.

The first phase is carried out in the coredataprep.R script where raw data is taken in and prepared for further processing. In this implementation of the IVAD, raw data received was in the form of transactional data retrieved using SQL from the sponsor’s ERP system. As such, data was in the form of relations and multiple table definitions. The main processes in this phase include: Data aggregation, batch geocoding, point in polygon analysis. Data was firstly prepared by aggregating the 3 datasets (over 3 years) into a single dataset and a date format column was generated. Batch geocoding and point in polygon analysis is carried out on customer postal codes. Batch geocoding was carried out using a simple R script and coordinates were then tagged to the appropriate CRS and point in polygon analysis was done to group the points by subzones marked out in the MP14. As this process is highly computationally intensive, this process runs in the coredataprep script which is to be run on the occasion where new customers are added. The key output of this script is a coredata file which serves as a master working copy of all relevant information. At the current stage of development, this script takes in data in the form of excel spreadsheets, however, at point of enterprise integration, it is highly plausible for tweaks to be made to the script such that it would be able to directly query the system using SQL.

The second phase of data preparation includes less computationally intensive processes that must be run more often are carried out. Such processes include the preparation of data tables for plotting in the app. Generation of tree map coordinates also occurs in this script so as to reduce the resource load at the app stage. By pre-calculating the coordinates, considerable reductions on loading speeds where achieved. The majority of data tables that are to be used for visualisations are generated in this phased and output as an environment image file (.Rdata) that allows for the rapid loading of objects into the shiny app. Other non-R data preparation steps include the identification and reprojection of the MP14 shape files as well as the generation of subzone centre points in the form of shape files. These 2 processes were carried out in Qgis.