IS428 2016-17 Term1 Assign3 Chua Shan Yong James

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.

1. Processing VAST_EnergyZones_F1.jpg, VAST_EnergyZones_F2.jpg, VAST_EnergyZones_F3.jpg by removing white spaces.
2. Open these images in tableau as background image, estimate zone's centre point and annotate to note down the points.
3. 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.

Task

Q1 What are the typical patterns in the prox card data? What does a typical day look like for GAStech employees?

1. Employees in the engineering department can be seen more in floor 2.
2. Employees in the administration department can be seen more in floor 3.
3. Floor 1 has lesser employees as compared to floor 2 and 3 as there are lesser offices in floor 1.
4. 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.
5. 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.

1. The average reheat coil power for floor 1 on 11th June increases significantly high as compared to the other days.
2. Average equipment power on floor 3 is the highest among all as there are servers on that level.
3. The hazium concentration is generally low in all floors.
4. The average light power are consistent throughout and only falls during the weekends.
5. The return outlet co2 concentration increases on 5th june and decreases only on the 9th June for floor 1.
6. The return outlet co2 concentration starts to fall on the 9th june for floor 2.
7. The return outlet co2 concentration starts to fall on the 9th june for floor 3.
8. The average reheat coil power for floor 3 on 4th June increases significantly high as compared to the other days.
9. The average reheat coil power for floor 3 on 7th June decreases by about 1400 as compared to 5th June.
10. The average reheat coil power for floor 2 on the 13th June decreases significantly high from 12th June from 1624 to 402

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.

1. Significant increase in Avg. REHEAT COIL Power on floor 1 from 9th June onwards.
2. Decrease in Avg. REHEAT COIL Power on floor 1 only starts on 12th June onwards.
3. There is an increasing trend in Avg. Lights Power from 12th June onwards.
4. Decreasing trend in Avg. RETURN OUTLET CO2 Concentration on floor 1 from 8th June onwards.
5. Increasing trend in Avg. RETURN OUTLET CO2 Concentration on floor 2 from 5th June onwards.
6. Increasing trend in Avg. REHEAT COIL Power on floor 2 from 10th June onwards.
7. Decreasing trend in Avg. REHEAT COIL Power on floor 2 from 12th June onwards.
8. The Avg. RETURN OUTLET CO2 Concentration on floor 2 fluctuates throughout the weeks.
9. The Avg. REHEAT COIL Power on floor 3 fluctuates throughout the weeks.
10. Decreasing trend in Avg. REHEAT COIL Power on floor 3 from 12th June onwards.