Difference between revisions of "IS428 2016-17 Term1 Assign3 Chua Shan Yong James"
| Line 19: | Line 19: | ||
'''Issue 3 (proxOut-MC2.csv without x,y coordinates):'''<p> In order to plot the points against the maps given, the X,Y coordinates is needed in order to be plotted as a point on the map. For proxOut-MC2.csv, only the floor and zones are available without the X,Y coordinates. To get the X,Y coordinates of a zone, i carried out the following steps to determine it. | '''Issue 3 (proxOut-MC2.csv without x,y coordinates):'''<p> In order to plot the points against the maps given, the X,Y coordinates is needed in order to be plotted as a point on the map. For proxOut-MC2.csv, only the floor and zones are available without the X,Y coordinates. To get the X,Y coordinates of a zone, i carried out the following steps to determine it. | ||
<br/><br/> | <br/><br/> | ||
| − | 1. As the jpeg file of all the prox zones of each floor (VAST_ProxZones_F1.jpg, VAST_ProxZones_F2, VAST_ProxZones_F3.jpg) comes with white spaces and also unnecessary texts, i edited each of the pictures by cropping them in paint to keep only the map. | + | 1. As the jpeg file of all the prox zones of each floor (VAST_ProxZones_F1.jpg, VAST_ProxZones_F2.jpg, VAST_ProxZones_F3.jpg) comes with white spaces and also unnecessary texts, i edited each of the pictures by cropping them in paint to keep only the map. |
[[File:VAST ProxZones F1 edited.jpg|300px|center]] | [[File:VAST ProxZones F1 edited.jpg|300px|center]] | ||
| Line 70: | Line 70: | ||
[[File:Building vlookup.png|600px|center]] | [[File:Building vlookup.png|600px|center]] | ||
| + | </p> | ||
| + | |||
| + | ==Approach== | ||
| + | ===Prox Card Data=== | ||
| + | <p>I changed the X and Y to longitude and latitude respectively. | ||
| + | |||
| + | [[File:Lat long.png|300px|center]] | ||
| + | |||
| + | I then insert background images (VAST_ProxZones_F1.jpg, VAST_ProxZones_F2.jpg, VAST_ProxZones_F3.jpg), set the X and Y of each image to be (0,189) and (0, 111) and to show when respective floors are selected. | ||
| + | [[File:Background image coordinates.JPG|500px|center]] | ||
| + | |||
| + | The results are as follows: | ||
| + | [[File:Resulted map.jpg|600px|center]] | ||
| + | |||
| + | I used the distinct count of prox id as the size mark on the map so that it can be easily identified which area has the most number of employees visiting it. I also used the department of the employees as the colour mark to segregate out the different departments. To provide a more detailed view of each day at each floor/zone, i used the timestamp, floor and zones as filter for the user to use. | ||
| + | |||
| + | [[File:Prox data sheet.JPG|600px|center]] | ||
| + | |||
| + | I also created another sheet to display the employees' first name, last name, department and also the type of sensor that has captured their prox ids. This sheet is to be used in the dashboard when the user can see the list of employee details by using the map attributes in the previous sheet as the filter. | ||
| + | |||
| + | [[File:Prox data sheet2.JPG|600px|center]] | ||
| + | |||
| + | </p> | ||
| + | |||
| + | ===Building Data=== | ||
| + | <p>Similarly to the prox data, i changed the X,Y to longitude and latitude respectively. I also inserted the background images (VAST_EnergyZones_F1.jpg, VAST_EnergyZones_F2.jpg, VAST_EnergyZones_F3.jpg) to plot the points accordingly on the respective map. | ||
| + | |||
| + | I included all the elements average values in the detail of each point so that a user can have a summary of all the elements' average values when it hovers over a point of a respective floor and zone on the map. Similarly to the prox data, i included the timestamp, floor and zones as the filter. | ||
| + | |||
| + | [[File:Building data sheet.JPG|600px|center]] | ||
| + | |||
| + | ===Web-based interactive system for prox card data=== | ||
| + | |||
| + | ===Web-based interactive system for builidng data=== | ||
</p> | </p> | ||
==Task== | ==Task== | ||
| − | + | ===Q1 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. | + | # Employees in the engineering department can be seen more in floor 2. |
| − | + | # Employees in the administration department can be seen more in floor 3. | |
| − | + | # Floor 1 has lesser employees as compared to floor 2 and 3 as there are lesser offices in floor 1. | |
| + | # Only administration employees comes back to work on weekends. This is evident on 5th June, 11th June and 12th June data as only administration employee can be seen. | ||
| + | # Floor 1 zone 6 are only visited by executive and facilities department personnel as it is a conference room that probably only executive personnel can use and facilities personnel for maintenance. | ||
| + | |||
| + | ===Q2 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.=== | ||
| + | ===Q3 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.=== | ||
| + | ===Q4 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.=== | ||
Revision as of 15:41, 23 October 2016
Contents
- 1 Problem & Motivation
- 2 Data Preparation
- 3 Approach
- 4 Task
- 4.1 Q1 What are the typical patterns in the prox card data? What does a typical day look like for GAStech employees?
- 4.2 Q2 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.
- 4.3 Q3 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.
- 4.4 Q4 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.
Problem & Motivation
After the successful resolution of the 2014 kidnapping at GAStech’s Abila, Kronos office, GAStech officials determined that Abila offices needed a significant upgrade. At the end of 2015, the growing company moved into a new, state-of-the-art three-story building near their previous location. Even though the employee morale rose somewhat with the excitement of the new building, there are still a few disgruntled employees in the company.
The new office is built to the highest energy efficiency standard, but as with any new building, there are still several HVAC issues to work out. The building is divided into several HVAC (heating, ventilation, and air conditioning) zones. Each zone is instrumented with sensors that report building temperatures, heating and cooling system status values, and concentration levels of various chemicals such as carbon dioxide (abbreviated CO2) and hazium (abbreviated Haz), a recently discovered and possibly dangerous chemical. CEO Sten Sanjorge Jr. has read about hazium and requested that these sensors be included. However, they are very new and very expensive, so GAStech can afford only a small number of sensors.
With their move into the new building, GAStech also introduced new security procedures, which staff members are not necessarily adopting consistently. Staff members are now required to wear proximity (prox) cards while in the building. The building is instrumented with passive prox card readers that cover individual building zones. The prox card zones do not generally correspond with the HVAC zones. When a prox card passes into a new zone, it is detected and recorded. Most, but not all, areas are still open to staff members even if they forget their prox cards. People are somewhat careless with their prox cards, but some diligent staff members will go to the security desk and pick up a new prox card if their old one is mislaid. As part of the deal to entice GAStech to move into this new building, the builders included a free robotic mail delivery system. This robot, nicknamed Rosie, travels the halls periodically, moving between floors in a specially designed chute. Rosie is equipped with a mobile prox sensor, which identifies the prox cards in the areas she travels through.
Data Preparation
Prox card data
Issue 1 (Employee List Prox ID):
As both the proxMobileOut-MC2.csv and proxOut-MC2.csv are reading prox id of the employee, there is a need to retrieve and extract out the prox id of each employee to match the different files. By looking through the prox ids in proxMobileOut-MC2.csv and proxOut-MC2.csv, i identified that the pattern of the prox id is the first letter of the first name followed by the last name of the employee all in small caps.
Therefore, in the employee list file, i added a column with the formula =LOWER(CONCATENATE(MID(C10,1,1),B10)) in excel to generate each employee's prox id.
Issue 2 (proxMobileOut-MC2.csv and proxOut-MC2.csv Prox ID):
As both the proxMobileOut-MC2.csv and proxOut-MC2.csv prox ids are with a 3 digit number at the back that denotes the count of the card number(employees may make new card each time it loses one), i created a column in both the files to remove the last 3 characters of the prox id - =LEFT(C2,LEN(C2)-3)
Issue 3 (proxOut-MC2.csv without x,y coordinates):
In order to plot the points against the maps given, the X,Y coordinates is needed in order to be plotted as a point on the map. For proxOut-MC2.csv, only the floor and zones are available without the X,Y coordinates. To get the X,Y coordinates of a zone, i carried out the following steps to determine it.
1. As the jpeg file of all the prox zones of each floor (VAST_ProxZones_F1.jpg, VAST_ProxZones_F2.jpg, VAST_ProxZones_F3.jpg) comes with white spaces and also unnecessary texts, i edited each of the pictures by cropping them in paint to keep only the map.
2. I used tableau and display the jpeg as a background image and set X to be 0 to 189 and Y to be 0 to 111. To determine each zones' coordinates, i estimate the centre point of each zone and annotate it in tableau to retrieve and record down the coordinates of each zone. I did this step for all 3 floors and all zones.
3. With the coordinates for each floor/zone recorded, i used it as a lookup table in excel. In the proxOut-MC2.csv file, i added a floor/zone column by concatenating the floor and zone value to be the lookup value to match the lookup table's floor/zone. I added both the X and Y column and used the vlookup function in excel to get the X and Y coordinates of each floor and zone - =VLOOKUP(F2,Sheet1!$A$2:$C$22,2,FALSE)
Issue 4 (one single file for all the prox data):
As the proxMobileOut-MC2 and proxOut-MC2.csv are in 2 separate files, i consolidated them together into one file to use it in tableau with the following attributes: timestamp, type, prox-id, floor, x, y and new prox-id.
Building data
Issue 1 (building data combines all elements of every floor and zone):
I want to segregate the elements by extracting each of the floor and zone in the cell. I did the following steps to transform the data.
1. I segregated the building data into 3 different files by each floor.
2. Using JMP Pro, i stacked the columns to get each element value for each timestamp. I did this for all 3 floors and got the following transformation.
3. With the transformed information of each floor, i used excel to get the floor and zone of each record. I used the excel function substitute to retrieve the floor and zone - =SUBSTITUTE(C3,": Lights Power", "") followed by using the text to column wizard to get the floor number and zone number respectively.
4. For the hazium concentration files, i sorted each floor's data and inserted the hazium concentration to the specific zone. Giving 0 to all the other zones that do not have hazium concentration readings.
Issue 2 (building data without x,y coordinates):
Similarly to proxOut-MC2.csv, the builidng data only has the floor and zone. I carried out the same steps as i did for the prox zones.
- Processing VAST_EnergyZones_F1.jpg, VAST_EnergyZones_F2.jpg, VAST_EnergyZones_F3.jpg by removing white spaces.
- Open these images in tableau as background image, estimate zone's centre point and annotate to note down the points.
- use the coordinates as a lookup table and get the X,Y coordinates of each floor/zone.
Approach
Prox Card Data
I changed the X and Y to longitude and latitude respectively.
I then insert background images (VAST_ProxZones_F1.jpg, VAST_ProxZones_F2.jpg, VAST_ProxZones_F3.jpg), set the X and Y of each image to be (0,189) and (0, 111) and to show when respective floors are selected.
The results are as follows:
I used the distinct count of prox id as the size mark on the map so that it can be easily identified which area has the most number of employees visiting it. I also used the department of the employees as the colour mark to segregate out the different departments. To provide a more detailed view of each day at each floor/zone, i used the timestamp, floor and zones as filter for the user to use.
I also created another sheet to display the employees' first name, last name, department and also the type of sensor that has captured their prox ids. This sheet is to be used in the dashboard when the user can see the list of employee details by using the map attributes in the previous sheet as the filter.
Building Data
Similarly to the prox data, i changed the X,Y to longitude and latitude respectively. I also inserted the background images (VAST_EnergyZones_F1.jpg, VAST_EnergyZones_F2.jpg, VAST_EnergyZones_F3.jpg) to plot the points accordingly on the respective map. I included all the elements average values in the detail of each point so that a user can have a summary of all the elements' average values when it hovers over a point of a respective floor and zone on the map. Similarly to the prox data, i included the timestamp, floor and zones as the filter.
Web-based interactive system for prox card data
Web-based interactive system for builidng data
Task
Q1 What are the typical patterns in the prox card data? What does a typical day look like for GAStech employees?
- Employees in the engineering department can be seen more in floor 2.
- Employees in the administration department can be seen more in floor 3.
- Floor 1 has lesser employees as compared to floor 2 and 3 as there are lesser offices in floor 1.
- Only administration employees comes back to work on weekends. This is evident on 5th June, 11th June and 12th June data as only administration employee can be seen.
- Floor 1 zone 6 are only visited by executive and facilities department personnel as it is a conference room that probably only executive personnel can use and facilities personnel for maintenance.
