Difference between revisions of "IS428 2016-17 Term1 Assign3 Yang Chengzhen"

From Visual Analytics for Business Intelligence
Jump to navigation Jump to search
(Undo revision 5563 by Czyang.2013 (talk))
 
(31 intermediate revisions by the same user not shown)
Line 20: Line 20:
 
I used tableau to create calculated field to convert X,Y to the corresponding zones:<br>
 
I used tableau to create calculated field to convert X,Y to the corresponding zones:<br>
 
[[File:Cz XY-to-floorZone.png|700x400px]]
 
[[File:Cz XY-to-floorZone.png|700x400px]]
 +
==== Data Transform and Union of Hazium Data ====
 +
There are 4 data sets of Hazium, each represent one sensor in a floor zone. To analyse the pattern, we need to merge those data as shown below:<br>
 +
[[File:CzBefore transform1.png|800x500px]]
 +
==== Data Transform of Building data ====
 +
I used JMP to convert building measurements to one attribute instead of different columns:<br>
 +
[[File:CzData transformation-2.png|800x400px]]
  
 
=Data Visualization and Findings=
 
=Data Visualization and Findings=
 +
Final Dashboard Link:https://public.tableau.com/profile/publish/MA3_YangChengzhen/Story1#!/publish-confirm
 
==Patterns of Employees by Prox Card Data==
 
==Patterns of Employees by Prox Card Data==
 
==== Floor-Zone Analysis ====
 
==== Floor-Zone Analysis ====
Line 27: Line 34:
 
The left graph shows number of people in each zone over times (Aggregated data from fix & Mobile Sensors).
 
The left graph shows number of people in each zone over times (Aggregated data from fix & Mobile Sensors).
 
The right graph shows the position of employees at different time (mobile sensor data only). Size represent number of employees at the point, color represents morning/afternoon.<br>
 
The right graph shows the position of employees at different time (mobile sensor data only). Size represent number of employees at the point, color represents morning/afternoon.<br>
[[File:Floor-zone analysis-F1.png|1000x850px]][[File:Floor-zone analysis-F2.png|1000x850px]][[File:Floor-zone analysis-F3.png|1000x850px]]
+
[[File:Floor-zone analysis-F2.png|1000x850px]]
 
==== Moving Pattern -Per Employee Analysis ====
 
==== Moving Pattern -Per Employee Analysis ====
 
This Dashboard shows the Movement across zones over each day per employee prox card. We can zoom in/out our analysis by using the Floor / Time Filter on the right.
 
This Dashboard shows the Movement across zones over each day per employee prox card. We can zoom in/out our analysis by using the Floor / Time Filter on the right.
Line 33: Line 40:
 
[[File:Moving pattern employee analysis.png|1000x850px]]
 
[[File:Moving pattern employee analysis.png|1000x850px]]
 
=====''' Findings ''' =====
 
=====''' Findings ''' =====
# For floor 1, Most people appears at F1-Z7, F1-Z2 and F1-Z8 in the morning and F1-Z7 in the afternoon. The zones mentioned above are offices and meeting rooms.
+
{| class="wikitable"
# F1-Z1 and F1-Z4 generally have the same pattern: there are 3 peak period per day: around 7-8 AM, 11-2PM and 5PM. These time period can be categorized as: come to work, lunch break and leave office.
+
|-
# For floor2, people are evenly spread among all the offices in the afternoon. In the morning, there are noticable large group of people stay at F2-Z6(Meeting & Training) room. This may indicate employees normally gather in the morning for meeting and back to their office in the afternoon.
+
! style="width: 60%;" | Finding
# F2-Z5 and F2-Z7 generally have the same pattern: there are 2 peak period per day: around 9 AM and 2PM.  
+
! style="width: 40%;;" | Supporting graph
# For floor3, people are evenly spread among all the offices.
+
|-
# F3-Z1, F3-Z2 and F3-Z3 generally have the same pattern: there are 2 peak period per day: around 9 AM and 2-3PM.
+
| 1. For floor 1, Most people appears at F1-Z7, F1-Z2 and F1-Z8 in the morning and F1-Z7 in the afternoon. The zones mentioned above are offices and meeting rooms. || [[File:Floor-zone analysis-F1.png|200x130px|framed]]
# There are 2 different types of employees by working hour:  8AM-5PM; 4PM-12AM (detailes can be found in dashboard)
+
|-
 +
| 2.F1-Z1 and F1-Z4 (Area with no offices) generally have the same pattern: there are 3 peak period per day: around 7-8 AM, 11-2PM and 5PM. These time period can be categorized as: come to work, lunch break and leave office. || [[File:Floor-zone analysis-F1.png|200x130px|framed]]
 +
|-
 +
| 3. For floor2, people are evenly spread among all the offices in the afternoon. In the morning, there are noticable large group of people stay at F2-Z6(Meeting & Training) room. This may indicate employees normally gather in the morning for meeting and back to their office in the afternoon. || [[File:Floor-zone analysis-F2.png|200x130px|framed]]
 +
|-
 +
| 4.F2-Z5 and F2-Z7 generally have the same pattern: there are 2 peak period per day: around 9 AM and 2PM. This can be the regular meeting time since there are meeting rooms in both zones.  || [[File:Floor-zone analysis-F2.png|200x130px|framed]]
 +
|-
 +
| 5.For floor3, people are evenly spread among all the offices across each day. || [[File:Floor-zone analysis-F3.png|200x130px|framed]]
 +
|-
 +
| 6.F3-Z1, F3-Z2 and F3-Z3 generally have the same pattern: there are 2 peak period per day: around 9 AM and 2-3PM. || [[File:Floor-zone analysis-F3.png|200x130px|framed]]
 +
|-
 +
| 7. There are 3 different clusters of employees by working hour:  8AM-5PM , 12AM to 8AM and 4PM-12AM  ||
 +
<gallery>
 +
File:Czworkhour1.png|8AM-5PM
 +
File:Czworkhour2.png|4PM-12AM  
 +
File:Czworkhour3.png|12AM to 8AM
 +
</gallery>
 +
|}
  
 
==Notable pattern in Building data==
 
==Notable pattern in Building data==
 +
==== Building data analysis dashboard ====
 +
This dashboard takes the building data, projects the value of each measurement overtime. The right graph shows the Hazium value by Floor-zone.
 +
[[File:Cz Buildingdata.png|1000x850px]]
 +
==== Findings ====
 +
 +
{| class="wikitable sortable"
 +
|-
 +
! Finding !! Supporting Garph
 +
|-
 +
| 1.Hazium value has several high points for all 3 floors: June 3rd, June 7th, and June 11st||
 +
[[File:Czevi1.png|200x160px|framed]]
 +
|-
 +
| 2. Level 3 has highest Equipment Power consumption while Floor1 has highest light power consumption  || [[File:Czevi4.png|200x100px|framed]]
 +
|-
 +
| 3. Supply fan power for level 3 is higher in weekend than weekdays || [[File:Czevi3.png|200x160px|framed]]
 +
|-
 +
| 4. While water heater gas rate remains high in every week day 8AM to 12AM and 0 in other times, Water heater setpoint and temperature does not change over days. || [[File:Czevi6.png|200x160px|framed]]
 +
|-
 +
| 5.For supply side measurements, supply side inlet temperatures has a regular low-high-low pattern on each week days 12pm to next day 12pm, while on the weekends it stays at high value level. Supply side mass flow rate and Supply side outlet temperature value does not vary from weekdayto weekends.    || [[File:Czevi7.png|200x160px|framed]]
 +
|-
 +
| 6.Both Equipment and Light power has really regular consumption patterns over the 2 weeks period (higher value on weekdays and lower in the weekends), we can infer there is no obvious abnormality of equipment utilization.||
 +
<gallery>
 +
File:Czevi2.png|Equipment power
 +
File:Czevi5.png|Light power
 +
</gallery>
 +
|}
 +
 
==Notable anomalies or unusual events==
 
==Notable anomalies or unusual events==
# '''Problem1:'''Some of the employees seem to change prox card frequently (e.g. G.Florez changed 5 security cards over 5 days). The lost cards may cause potential problems (e.g. P.young was tracked of using 2 cards on the same day appearing at different zones, which may be caused by someone else using one of the card)
+
{| class="wikitable  sortable"
<gallery>
+
|-
 +
! Priority!! Problem !! Supporting graph
 +
|-
 +
| 1 || Some of the employees seem to change prox card frequently (e.g. G.Florez changed 5 security cards over 5 days). The lost cards may cause potential problems (e.g. P.young was tracked of using 2 cards on the same day appearing at different zones, which may be caused by someone else using one of the card)|| <gallery>
 
File:CzProblem1.png|Employee G.florez
 
File:CzProblem1.png|Employee G.florez
 
File:CzProblem-employee-1.png|Employee P.young
 
File:CzProblem-employee-1.png|Employee P.young
 
</gallery>
 
</gallery>
 +
|-
 +
| 2|| High CO2 return outlet concentration is found among multiple building measures from June 7th 12AM to June 9th around 9 AM.There are 2 major breakpoints, one at 7th morning and another at 8th morning Serveral measures shows extreme high value as compare to other times. After expanding the inspection level to floor-zone, we can tell the problem is majorly caused by floor2-zone1 to floor 2-zone14, since it has the highest value increase. The CO2 concentration level change caused several other measure's increase such as Reheat Coil Power, VAV Damper position and Thermostat_Temp :<br>|| <gallery>
 +
File:Return_outlet_co2_consumption.png|Return_outlet_co2_consumption floor level
 +
File:Cz-floor-zone level.png|Return_outlet_co2_consumption floor zone level
 +
File:Reheat_Coil_pwer.png|Reheat Coil Power
 +
File:Thermostat_Temp.png|Thermostat_Temp
 +
File:VAV Damper position.png|VAV Damper position
 +
</gallery>
 +
|-
 +
| 1 ||There are 3 period which has relatively high hazium concentration level :June 3rd, June 7th(not very obivious for floor1), and June 11st. On 11st June, Hazium concentration level is extremely high among all the floor zones. ||
 +
[[File:Czevi1.png|200x160px|framed]]
 +
|-
 +
| 2 || The Supply Fan outlet Temperature for Floor3 on June 7th morning and June 8th afternoon around 3pm is unusually high. This also caused the supply fan power to reach a high value during the period||
 +
<gallery>
 +
File:Czevi8.png|Supply Fan outlet Temperature
 +
File:Czevi9.png|Supply Fan power
 +
</gallery>
 +
|-
 +
| 3 || The Air Loop inlet mass flow rate for Floor3 from  June 4th 5pm to June 6th 5AM is  unusually high , this should not be the case since it is weekend period and both floor 1 and 2 has very low value. ||
 +
[[File:Czevi10.png|200x160px|framed]]
 +
 +
|}
 +
  
 
==Observed relationships between the proximity card data and building data elements==
 
==Observed relationships between the proximity card data and building data elements==
 +
===== Buiding Measures Vs Employee Patterns dashboard =====
 +
This Dashboard aims to find the relationship among various building measures and employee moving pattern. The graph on the right shows the 4 selected measurement which shows relatively bvious relationship with number of employees. Change the filter to see any measurement's pattern vs employee numbers overtime
 +
[[File:Dash3.png|1000x800px]]
 +
==== Findings ====
  
=Visualization Software=
+
{| class="wikitable"
 
+
|-
 +
! Finding!! Supporting Graph !! Confidence Level
 +
|-
 +
| There is a positive correlation between Water heater gas rate and number of employees in the building. The more employee, the higher gas rate.  || [[File:Czevi12.png|200x160px|framed]] || High
 +
|-
 +
| There is a negative correlation between supply side inlet temperature and number of employees in the building. The more employee, the lower supply side inlet temperature. || [[File:Czevi13.png|200x160px|framed]] || High
 +
|-
 +
| There is a positive correlation between Supply Fan Power and number of employees in the building. The more employee, higher the fan power. || [[File:Czevi14.png|200x160px|framed]] || Medium
 +
|-
 +
| There is a positive correlation between Total Electricity Demand Power and number of employees in the building. The more employee, lower the demand power. || [[File:Czevi15.png|200x160px|framed]] || Low
  
 +
|}
  
 +
=Visualization Software=
 +
* Microsoft Excel for data cleaning
 +
* JMP for data Transformation
 +
* Tableau for data visualization
  
 
=Final Outcome=
 
=Final Outcome=
 +
The Final visualization of this project can be accessed at : https://public.tableau.com/profile/publish/MA3_YangChengzhen/Story1#!/publish-confirm
  
 
+
=Reflection of the effectiveness of tools=
=Conclusion=
+
* Time format for Tableau: I found it hard to do the formatting / extracting of time from date in tableau.  If your initial time format in excel is "9:00:00 AM", Tableau cannot convert it to a time  measure automatically.  Initially i followed this tutorial here only to get the time part from the timestamp:http://kb.tableau.com/articles/issue/extract-time-from-date-and-time-field But after evaluating the effectiveness,  I feel adding another Time column in excel manually may be faster :(.
 +
* Data extraction: Unlike JMP, it is more difficult for you to export the data you edited via the software. For tableau, if you want to export the data source you have modified, you can only extract as a .tde file or do a view data-> copy paste manually.(https://community.tableau.com/thread/117150)
  
 
=Comment=
 
=Comment=

Latest revision as of 08:59, 24 October 2016


Description

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.
This project aims to identify the patterns and problems arose after moving to the new office building for GASTech. This is achieved by using GASTech operational data sets, including:

  • Proximity sensor data for each of the prox zone regions
  • Proximity sensor data from Rosie the mobile robot
  • HVAC sensor readings and status information from each of the building’s HVAC zones
  • Hazium readings from four sensors

Data Preparation

Create HH:MM:SS Field from timestamp

To analyse the moving pattern over each day, we need to extract time from the date since tableau does not support auto extract well.
Cz Hhmmss.png

Convert Server room to the corresponding Zone

For ProxoutMC2.csv, there are 29 records with zone name "Server Room" instead of a numeric number of majority. After checking the floor map, I found server room belongs to Floor3 Zone4, so I converted these values as following:
Cz ServerRoom.png

Covert X Y in proxMobileOut-MC2.csv to corresponding Zone

For Mobile Prox data, the position of the point is indicated by X,Y , while for fixed prox data, the position is indicated by zone. In order to analyse the movement together, we need to align the 2 data sets. I used tableau to create calculated field to convert X,Y to the corresponding zones:
Cz XY-to-floorZone.png

Data Transform and Union of Hazium Data

There are 4 data sets of Hazium, each represent one sensor in a floor zone. To analyse the pattern, we need to merge those data as shown below:
CzBefore transform1.png

Data Transform of Building data

I used JMP to convert building measurements to one attribute instead of different columns:
CzData transformation-2.png

Data Visualization and Findings

Final Dashboard Link:https://public.tableau.com/profile/publish/MA3_YangChengzhen/Story1#!/publish-confirm

Patterns of Employees by Prox Card Data

Floor-Zone Analysis

This Dashboard analyses the moving pattern of employees among different floor zones. The left graph shows number of people in each zone over times (Aggregated data from fix & Mobile Sensors). The right graph shows the position of employees at different time (mobile sensor data only). Size represent number of employees at the point, color represents morning/afternoon.
Floor-zone analysis-F2.png

Moving Pattern -Per Employee Analysis

This Dashboard shows the Movement across zones over each day per employee prox card. We can zoom in/out our analysis by using the Floor / Time Filter on the right.
Moving pattern employee analysis.png

Findings
Finding Supporting graph
1. For floor 1, Most people appears at F1-Z7, F1-Z2 and F1-Z8 in the morning and F1-Z7 in the afternoon. The zones mentioned above are offices and meeting rooms.
Floor-zone analysis-F1.png
2.F1-Z1 and F1-Z4 (Area with no offices) generally have the same pattern: there are 3 peak period per day: around 7-8 AM, 11-2PM and 5PM. These time period can be categorized as: come to work, lunch break and leave office.
Floor-zone analysis-F1.png
3. For floor2, people are evenly spread among all the offices in the afternoon. In the morning, there are noticable large group of people stay at F2-Z6(Meeting & Training) room. This may indicate employees normally gather in the morning for meeting and back to their office in the afternoon.
Floor-zone analysis-F2.png
4.F2-Z5 and F2-Z7 generally have the same pattern: there are 2 peak period per day: around 9 AM and 2PM. This can be the regular meeting time since there are meeting rooms in both zones.
Floor-zone analysis-F2.png
5.For floor3, people are evenly spread among all the offices across each day.
Floor-zone analysis-F3.png
6.F3-Z1, F3-Z2 and F3-Z3 generally have the same pattern: there are 2 peak period per day: around 9 AM and 2-3PM.
Floor-zone analysis-F3.png
7. There are 3 different clusters of employees by working hour: 8AM-5PM , 12AM to 8AM and 4PM-12AM

Notable pattern in Building data

Building data analysis dashboard

This dashboard takes the building data, projects the value of each measurement overtime. The right graph shows the Hazium value by Floor-zone. Cz Buildingdata.png

Findings

Finding Supporting Garph
1.Hazium value has several high points for all 3 floors: June 3rd, June 7th, and June 11st
Czevi1.png
2. Level 3 has highest Equipment Power consumption while Floor1 has highest light power consumption
Czevi4.png
3. Supply fan power for level 3 is higher in weekend than weekdays
Czevi3.png
4. While water heater gas rate remains high in every week day 8AM to 12AM and 0 in other times, Water heater setpoint and temperature does not change over days.
Czevi6.png
5.For supply side measurements, supply side inlet temperatures has a regular low-high-low pattern on each week days 12pm to next day 12pm, while on the weekends it stays at high value level. Supply side mass flow rate and Supply side outlet temperature value does not vary from weekdayto weekends.
Czevi7.png
6.Both Equipment and Light power has really regular consumption patterns over the 2 weeks period (higher value on weekdays and lower in the weekends), we can infer there is no obvious abnormality of equipment utilization.

Notable anomalies or unusual events

Priority Problem Supporting graph
1 Some of the employees seem to change prox card frequently (e.g. G.Florez changed 5 security cards over 5 days). The lost cards may cause potential problems (e.g. P.young was tracked of using 2 cards on the same day appearing at different zones, which may be caused by someone else using one of the card)
2 High CO2 return outlet concentration is found among multiple building measures from June 7th 12AM to June 9th around 9 AM.There are 2 major breakpoints, one at 7th morning and another at 8th morning Serveral measures shows extreme high value as compare to other times. After expanding the inspection level to floor-zone, we can tell the problem is majorly caused by floor2-zone1 to floor 2-zone14, since it has the highest value increase. The CO2 concentration level change caused several other measure's increase such as Reheat Coil Power, VAV Damper position and Thermostat_Temp :
1 There are 3 period which has relatively high hazium concentration level :June 3rd, June 7th(not very obivious for floor1), and June 11st. On 11st June, Hazium concentration level is extremely high among all the floor zones.
Czevi1.png
2 The Supply Fan outlet Temperature for Floor3 on June 7th morning and June 8th afternoon around 3pm is unusually high. This also caused the supply fan power to reach a high value during the period
3 The Air Loop inlet mass flow rate for Floor3 from June 4th 5pm to June 6th 5AM is unusually high , this should not be the case since it is weekend period and both floor 1 and 2 has very low value.
Czevi10.png


Observed relationships between the proximity card data and building data elements

Buiding Measures Vs Employee Patterns dashboard

This Dashboard aims to find the relationship among various building measures and employee moving pattern. The graph on the right shows the 4 selected measurement which shows relatively bvious relationship with number of employees. Change the filter to see any measurement's pattern vs employee numbers overtime Dash3.png

Findings

Finding Supporting Graph Confidence Level
There is a positive correlation between Water heater gas rate and number of employees in the building. The more employee, the higher gas rate.
Czevi12.png
High
There is a negative correlation between supply side inlet temperature and number of employees in the building. The more employee, the lower supply side inlet temperature.
Czevi13.png
High
There is a positive correlation between Supply Fan Power and number of employees in the building. The more employee, higher the fan power.
Czevi14.png
Medium
There is a positive correlation between Total Electricity Demand Power and number of employees in the building. The more employee, lower the demand power.
Czevi15.png
Low

Visualization Software

  • Microsoft Excel for data cleaning
  • JMP for data Transformation
  • Tableau for data visualization

Final Outcome

The Final visualization of this project can be accessed at : https://public.tableau.com/profile/publish/MA3_YangChengzhen/Story1#!/publish-confirm

Reflection of the effectiveness of tools

  • Time format for Tableau: I found it hard to do the formatting / extracting of time from date in tableau. If your initial time format in excel is "9:00:00 AM", Tableau cannot convert it to a time measure automatically. Initially i followed this tutorial here only to get the time part from the timestamp:http://kb.tableau.com/articles/issue/extract-time-from-date-and-time-field But after evaluating the effectiveness, I feel adding another Time column in excel manually may be faster :(.
  • Data extraction: Unlike JMP, it is more difficult for you to export the data you edited via the software. For tableau, if you want to export the data source you have modified, you can only extract as a .tde file or do a view data-> copy paste manually.(https://community.tableau.com/thread/117150)

Comment