Difference between revisions of "IS428 2016-17 Term1 Assign3 Tan Yong Kiong Alson"
Yktan.2013 (talk | contribs) |
Yktan.2013 (talk | contribs) |
||
(2 intermediate revisions by the same user not shown) | |||
Line 22: | Line 22: | ||
* For the “Employee List.xlsx” worksheet, the prox-id is created with reference to the “proxOut-MC2 “ and “proxMobileOut-MC2” files using the Excel formula: | * For the “Employee List.xlsx” worksheet, the prox-id is created with reference to the “proxOut-MC2 “ and “proxMobileOut-MC2” files using the Excel formula: | ||
=LOWER(LEFT(C2,1)&B2&"001") | =LOWER(LEFT(C2,1)&B2&"001") | ||
− | + | [[File:Assignment3 AlsonTan 3.png|center|Data Cleaning - Employee List]] | |
+ | [[File:Assignment3_AlsonTan_2.png|thumbnail|Data Cleaning - Employee List]] | ||
Secondly, JMP Pro 12 was used to stack the columns together to allow easy interpretation of data by Tableau. | Secondly, JMP Pro 12 was used to stack the columns together to allow easy interpretation of data by Tableau. | ||
* The “bldg-MC2” is imported into JMP Pro 12 | * The “bldg-MC2” is imported into JMP Pro 12 | ||
Line 30: | Line 31: | ||
* In the xlsx file, we create an additional column: “Floor/Zone” to sieve out the Floor and Zone from each label. | * In the xlsx file, we create an additional column: “Floor/Zone” to sieve out the Floor and Zone from each label. | ||
* Using the LEFT function in Excel, we can retrieve the Floor and Zone from each label respectively. | * Using the LEFT function in Excel, we can retrieve the Floor and Zone from each label respectively. | ||
+ | [[File:Assignment3_AlsonTan_1.png|center|Data Cleaning - Employee List]] | ||
* The “Floor/Zone” column is then split into two additional columns are inserted in the Excel which are “Floor” and “Zone”. | * The “Floor/Zone” column is then split into two additional columns are inserted in the Excel which are “Floor” and “Zone”. | ||
== Tools Used == | == Tools Used == | ||
− | + | The following tools are used for this report: | |
+ | # Tableau 10.0 | ||
+ | # Microsoft Excel 2016 | ||
+ | # JMP Pro 12 | ||
== Results == | == Results == | ||
+ | [[File:Assignment3 Alson 11.PNG|800px|center]] | ||
+ | [[File:Assignment3 Alson 12.PNG|800px|center]] | ||
+ | [[File:Assignment3 Alson 13.PNG|800px|center]] | ||
+ | [[File:Assignment3 Alson 14.PNG|800px|center]] | ||
+ | [[File:Assignment3 Alson 15.PNG|800px|center]] | ||
+ | [[File:Assignment3 Alson 16.PNG|800px|center]] | ||
+ | [[File:Assignment3 Alson 17.PNG|800px|center]] | ||
+ | [[File:Assignment3 Alson 18.PNG|800px|center]] | ||
+ | [[File:Assignment3 Alson 19.PNG|800px|center]] | ||
+ | [[File:Assignment3 Alson 20.PNG|800px|center]] | ||
+ | [[File:Assignment3 Alson 21.PNG|800px|center]] | ||
+ | [[File:Assignment3 Alson 22.PNG|800px|center]] |
Latest revision as of 21:10, 27 October 2016
Contents
Data Visualization
The full dashboard on the data visualization can be found here.
Abstract
Problem and Motivation
Due to the kidnapping that happened in GAStech’s old office, the officials decided to shift its operations to a new three-story building. The data given consist of the results from sensors across the building, which can be used to analyse any patterns and anomalies. GAStech’s CEO is also concerned about the side effects that Hazium has on its employees, which may exist within the building. Therefore, four zones are fitted with the Hazium Concentration Sensors to monitor the level and take precautionary measures in the future.
This report aims to find out the patterns and anomalies that exists within the 2 million data points embedded in the dataset. With the fixed and mobile prox card data from employees, we can see how the employees interact in the building during their working hours daily, with Rosie the robot. By realizing the patterns in the building data, it will aid in identifying and explaining the reasons behind them. We also want to find out the anomalies or ususual events that can be gathered from the data, which would help the higher management to be alerted or safety or fraud issues.
Below are list of questions that will be explored:
- What are the typical patterns in the prox card data? What does a typical day look like for GAStech employees?
- Describe up to ten of the most interesting patterns that appear in the building data. Describe what is notable about the pattern and explain its possible significance.
- Describe up to ten notable anomalies or unusual events you see in the data. Prioritize those issues that are most likely to represent a danger or a serious issue for building operations.
- Describe up to five observed relationships between the proximity card data and building data elements. If you find a causal relationship (for example, a building event or condition leading to personnel behavior changes or personnel activity leading to building operations changes), describe your discovered cause and effect, the evidence you found to support it, and your level of confidence in your assessment of the relationship.
Approaches
First, the dataset is cleaned using various techniques and functions in Excel.
- For the “Employee List.xlsx” worksheet, the prox-id is created with reference to the “proxOut-MC2 “ and “proxMobileOut-MC2” files using the Excel formula:
=LOWER(LEFT(C2,1)&B2&"001")
Secondly, JMP Pro 12 was used to stack the columns together to allow easy interpretation of data by Tableau.
- The “bldg-MC2” is imported into JMP Pro 12
- The Stack tool is selected from Tables
- In the query function, we stack all columns except the Date/Time field.
- Once done, save as a separate xlsx file for further data cleaning.
- In the xlsx file, we create an additional column: “Floor/Zone” to sieve out the Floor and Zone from each label.
- Using the LEFT function in Excel, we can retrieve the Floor and Zone from each label respectively.
- The “Floor/Zone” column is then split into two additional columns are inserted in the Excel which are “Floor” and “Zone”.
Tools Used
The following tools are used for this report:
- Tableau 10.0
- Microsoft Excel 2016
- JMP Pro 12