IS428 2016-17 Term1 Assign3 Bong Jun Hao

Data Exploration/Cleanup

Employees.xlsx

By itself, the dataset provided was not enough to provide any insights, except for to identify the department and floor/zone of a particular employee. Issue: It is mentioned that an employee could possible hold multiple proxID as they would pick up additional prox card(s) if they have misplaced their old one. This is not reflected in the employees file provided.

Solution: Upon further investigation of the proxOut file, it can be seen that the proxID of an employee is based on the combination of the first character of an employee's First Name followed by his or her entire Last Name as well as a 3 number digit code that is in running order from 001 onwards.

An example would be Anda Ribera, where Anda is the first name and Ribera is the last name. Hence he would have the id Aribera001 and any subsequent id issued would be aribera002, aribera003 so on and so forth. As such, an additional column is added to the employees.xlsx file based on the proxOut file to see what are the various ids that each employee holds.

Mobile and Fixed Proximity Data

Issue: The problem with the mobile and fixed proximity data is that ProxOut (Fixed) shows the floor and zone of each area whereas for the mobile ProxOut, it shows the X and Y coordinates in replacement of the zone. Hence without a common set of values as well as mappings, it is not possible to connect the fixed and mobile proximity sensor on the map to give us an indication of the location of the employee.

Solution: The solution is to find a way to be able to map out the areas on the proximity zones images provided and give a X and Y coordinate mapping which can be then added to the mobile Proxout data file. I referred to Gwendoline's work where she used this online tool, from Bryant Howell to draw out custom polygons on the map provided and to get the centre point X and Y coordinates of these zones.

The online tools is available at this link: https://github.com/bryantbhowell/tableau-map-pack

Following Gwendoline's idea, i cropped out the map images provided so as to remove white spaces and to show only the map area only as well. Other points to note when plotting the polygons are:
1) For zones that encompasses a large area of the map such as Zone 1 of Floor 1 which refers to all the corridors and white spaces in that area, I only plotted the polygon of the corridor.
2) For zones that includes multiple areas such as Zone 4 of Floor 1, only one area is drawn, as it refers to all the staircases and stairwells and it makes sense to just map the X and Y coordinate to one staircase as it is assumed that it is not important to track the exact staircase location for each employee who are at it.

With the polygon mappings of the cropped image, i then proceeded to calculate the center point of the coordinate using Excel. What i did was to use the formula: "=MEDIAN(MIN(C$2:C$5),MAX(C$2:C$5))" to find the exact center X and Y value for each polygon. OffCenteredMidPointsFloor2.JPG

This was repeated for floor 2 and 3. However, due to floor 2 and 3 having irregular shapes, some of the mid points were off and had to be manually changed.

The mid point mappings for each floor and zone is then combined as seen.

• NOTE* I realized i made a mistake when initially counting the midpoints as i've sent the range of X and Y to 3085 and 1019, without rescaling it to the required dimensions of 189 (X) and 111 (Y). I have since resize the map images and redone/readjust the plotting and calculation of the mid points according.

With this mapping we then proceed to combine the Proximity Data together. As the mobile proximity data already has the x and y coordinates mapped, only the fixed proximity sensor will be mapped, using a V-lookup function. Prior to that function, a column called identifier which combines the floor and zone is created so that the correct x and y coordinates can be determined from the mapping table. This is then combined with the mobile proxOut data.

Building Data

For the building data that was provided, i split it into various segments, namely: 1. The building with all its unique attributes alone. 2. By floor when it contains all the attributes of a building floor without attributes from zones. 3. Lastly, all the elements which contains both floor and zone attributes are grouped together.

After splitting the data up, the data were reshaped for easier processing in Tableau using the Tableau plug in.

Hazium Concentrations

For the hazium dataset, it was processed similarly to that of the building data. It is slightly different in the sense that instead of splitting them up, i proceeded to join all the datasets together and perform a data reshaping after that.

Task 1

This is based on the proximity data for by combining both mobile and fixed promixity sensors data and linking it to the employeeId and proximityId to see if there is any patterns within departments or movement patterns.

The typical patterns in the proximity card data set is that: 1. From 12am - 6am, the only data collected by the fixed sensor is from the level 1 fixed proximity sensor, indicating that is it quite likely that no employees are moving around level 2 and 3 between this period of time. 2. Outside of the time frame between 7am-5pm, there is no movement of any staff from the security department.

A typical day of an employee of GASTech would be: 1. Employees reach the office from 7am onwards. 2. By 9am most employees are already in their office. 3. Lunch time is about 12 noon - 1 where we see quite a bit of people at the Deli after which there is movement around the offices. 4. Employees tend to end work/knock off at 5pm with a few exceptions who end work from 4pm onwards. 5. Employees from the IT and Engineering department tend to stay and work till much later than the others, going home at about 11-12 midnight.