IS428 2016-17 Term1 Assign3 Margot Marie T Stelleman

Abstract

At the end of 2015, Abila, division of GAStech officials moved in new offices, near the old ones. It helps to rise the mood of employees but even some are still unhappy about this change. This new location has been designed and built to meet the highest energy efficiency standard but some HVAC issues are still there and need to be worked out. Hazium a possibly new dangerous chemical that forces the CEO to want new and very expensive sensors in each HVAC zones which divide the new offices. Sensors are able to inform about hazium concentration levels as well as the CO2 level but also temperatures, heating and cooling system status values for example. Because these sensors are really expensive, the company can afford only a small number of them. So not HVAC zones will have Hazium sensors. There are also new security procedures but the staff members are still not used and not very constant in their utilizations and respect of the rules. They must wear a prox card while in the building. These cards can be read by passive prox card readers that are each in a different zone of the building. The zones are not necessary the same as the HVAC ones. How does it work from now?

• Each time a prox card enter a zone it is detected and recorded.
• Most of the zones are still open to people not having their cards.
• People are careless but a new prox card can be obtained at the security desk if the old one is mislaid.

There is also a free robotic mail delivery system, named Rosie. She moves periodically, able to move between floors. She has a mobile prox sensor identifying the prox card in the area where she is.

Motivation

I’m a visual analytics expert who’s been hired to help the company understand its operations data. After I had well understand the situation and my differents tasks by rewriting the project task as an abstract and summary, the next step is to preapre the data to finally analyze them and deliver some patterns.

Data preparation and cleaning

Before the data could give us patterns, stories and been used, we need to prepare them and clean them.

Creating a new id column to link Employee list, proxMobileOut-MC2 and proxOut-MC2 files

To answer the first question, we’ll need information about employees and prox card data. The files we will use are the Employee list file on Excel and the proxMobileOut-MC2 and proxOut-MC2 files. The information related to employees in these two last files is the prox-id which is not appearing in the Employee list. Since we want to interpret information from all the three files together we need to create it in the Employee list so they can be linked. When we look carefully at the name of the employees we understand fast that the prox-id is constructed as followed: (all in lower case) the first letter of the first name + the entire last name + “00x” where x is the number of cards that the employee asked since he can ask for a new one when he lost it. This last part with the numbers is complicated to standardize so in each file there will be a column “id” with in lower case the first letter of the first name + the entire last name. For example, in the employee list, Bramar Mat will be “mbramar” and construct with this formula (in French): =MINUSCULE(CONCATENER(STXT(C2;1;1);B2)) where Minuscule is for lower case, stxt to take the fist letter of the first name, and concatener to add to the first letter the last name. In the prox files, a new column id is created by removing the 3 last characters of the column prox-id, using the formula: =GAUCHE(C2;NBCAR(C2)-3). But attention, the work is not finished. Some people have to last name like “Mies Haber” so we need to check and remove the blank space in the new id or when a hyphen, remove the second last name. There is some exceptions as Sanjorge Jr. Sten which will be jsanjorge and not ssanjorge.

Creating coordinates for ProxOut-MC2

For the question 1, we would like to know the typical day of an employee. For that we are going to use the prox data, to know at what time is going to start his day or to know what he is doing during the day regarding how is moving in the building. In the ProxOut-MC2, we are able to know the floor and the zone but to have the visualization as its best we want to use a map, since that we need to have the coordinates of the zone to be able to put it on the map. The other file, ProxMobileOut-MC2 has already the coordinates. So we want to know the coordinates of our different zones, Tableau can help us to do it once we have import and can display the maps of each floor. In data visualization section, this is explained. Once this step is done, we can use the annotation function to know the coordinates of a point. (http://onlinehelp.tableau.com/current/pro/desktop/en-us/help.htm#annotations_annotations_add.html ) For each zone of each floor I just select a point inside more or less in the middle of the zone and annotate then report the coordinates in an excel file. Sometimes some zones are split and/or are not a regular rectangle but it is always the same type of rooms such as for the zones 4 which are the elevators or stairs. Each zone has a number but at floor 3 one zone has no number but is called “Server Room”, we also need to assign her coordinates. The final step is to join the ProxOut-MC2 file with the coordinates of each zone that I put in an excel file. I have used JMP to do it since Excel does not allow it to do so and not as easy. Both tables are joined using Floor=Floor and Zone=Zone. Basically we had 29763 lines, after joining, we have 29513 lines left. Server room could be not recognized and needed to be change as a number the zone is then now at floor 3 known as zone 9. After this change, we still have some lines missing since we have in total 29542 lines. I took a lot of time to know which ones and why they were missing but it is still a mystery. Every zone since to have data so it has been take as assumption that it could not matter since the number of data is very high.

Organizing the building data

When we open the building data file, we are literally lost. There is too much and the data are not organized easily for us to understand and access the information fast. Visualization will definitely help the user to see and search in an effective way. But to have an effective visualization, the data must be organized in a different way on Excel, first. Three major changes need to be done. 1. Split the data in three different major categories: Some data are for the entire building, others are for one particular zone of a particular floor and the rest is for an entire particular floor. We can split the data in three different sheets according to the space that is taking the measure for. We will have then one sheet for the building, one for the entire floors and one for all the data by zone for a floor. 2. Changing tables organization for filtering the concentration measurements: if the file is left like that each measurement is a title of a column. Since that the user won’t be able to use interactively the data. We need to drop each column to the dashboard for them to appear. BUT if the measurements are in one and only column call “measurement”, then the user will be able to select them interactively as they could select the floor or the zone for example. To be able to do that, I put my split file in JMP and using the function STACK allow me to put my labels in a column with each appropriate time and value. It has been done for each split part of the big original file. 3. Creating columns for floor and zone for filtering by localization: In prox-card data, we have for each row a particular floor column and a particular zone column. It allows us to filter and to get into the data, be more precise and accurate AND allow good interactivity. We want to use the same principle for the concentration measurements. For the new floor sheet/file, on JMP we use in Columns>Utilities>Text in Columns to separate the new “measurement” column once using “_” as separator and next “ ” as second time, we delete the column we don’t need and rename the one we need. But because we separate the column with a blank space some we will need to be recombine to form again the name of the measurement. Similar process is done for the zone by floor file.

===== Computing the Hazium concetration file

=====

All the hazium files have been merged in one in excel and then the similar process for organizing the data than for building data has been followed.