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.
[[File:Midpoints_Mapping.JPG]|500px]

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.

[[File:MappingFixProxData.JPG]|500px][[File:CombinedProxData.JPG]|500px]