How to analyze html data from research rooms audit: (in progress)
...
As part of space management and policy enforcement, usage data from the electronic locks is gathered and analyzed. If you haven't already done so, begin by Gathering Lock Audit Data. You will take the data and extract it to create a Pivot Table using Microsoft Excel. How to do this is outlined below.
Helpful Tip: Save your work regularly. Occasionally (often) an error will occur that will not allow you to save your work. If you have been working on this for awhile, you will lose all of your work. Don't do that. The first few times you do this, it will take time so save often. (Save yourself the tears.)
Load date into Microsoft Excel
- Open a new Microsoft Excel sheet
- Click on the “Data” tab
- Select “New Query” then “From File” and “From Folder”. (Note: This image does not reflect the current file path.)
- Click Browse.
...
- Select
...
- This PC < Shared Network Space LEAD < Shared <Library Spaces Planning & Mgmt< Rooms < Research Room Audits
...
- < XXXX Audit Data< Most Recent file
- Click OK, then OK again.
- Another box will appear – just click Load at the bottom.
- Allow the rows to load. Depending on how big the file is, it may take a few seconds. You can watch the progress on the right side of the screen.
Format Data
- On the tool bar at the top of the screen under “Query Tools”, click “Query”
...
- Select Edit on the far left
...
- The far left column is labeled "Content". Click on the box with the downward arrows.
...
- In the pop-up window, select "Table 0" for the door numbers and date/times accessed to appear. Click OK.
- Right click on the Date column. In the drop down menu, select Transform and click “Date Only”. This will remove the time stamps, which will potentially skew the data counts.
- In the Event/Name column, click on the down arrow for the menu.
...
- In the pop-up window, select “Load More” at the bottom of the window to see all the event name options.
...
- You will now have a much longer list. Individually select only the research rooms
...
- . We do not analyze
...
- data from any other entry. Then click "OK".
...
- Delete the Function
...
- and
...
- Source Name
...
- columns by right clicking and selecting “Remove”. The only columns you need to keep are Door, Date, and Event/Name.
- Once you are finished making your edits, click on Close and Load in the upper left hand corner. This will take some time to load so be patient.
...
- In the upper tool bar, under “Table Tools” click Design.
...
- Highlight the three remaining columns and click "Remove Duplicates" under "Table Tools".
...
- There will be a pop-up window. Make sure all three columns are selected (Door, Date, Event/Name)
...
- . Also leave "My data has headers" selected. Click OK. Excel will tell you how many duplicates were removed. Click OK again.
...
- Click the downward arrow in the Event/Name column and
...
- one last time check to make sure only Grad Commons research rooms are selected. Unselect anything that is not a
...
- Grad Commons research room
...
- . Click OK.
Create a Pivot Table
- In the tool bar at the top, click Insert.
...
- Make sure all three columns are highlighted and select "Pivot Table".
- A pop up will appear to select the table or range, which you should’ve already highlighted.
...
- Be sure to chedk the box to
...
- "Add this data to the Data
...
- Model" and click OK.
- On the right will be a box labeled "PivotTable Fields". It will have one large box (Range) and 4 smaller boxes (Filters, Columns, Rows, and Values).
- In "Range", click and drag Event/Name to the "Filters" box.
- This will appear in the spreadsheet
...
- . Click on the downward arrow to open the drop down menu.
- Check the box at the bottom labeled "Select Multiple Items"
...
- . A checked box will appear next to the word "All"
...
- . Click the + next to "All".
- All of the rooms should have a check mark next to them. Uncheck any you do not want to include.
...
- Click OK
- Working with "Range" again, click and drag "Door" to the "Rows" box
- This will now appear:
- Click on the downward arrow next to "Row Labels" to open the drop down menu
- Uncheck "Blank" then click OK
- Again, working with "Range" check the box next to "Date". When you do this, several more items will appear. See the image below. They will also appear in the "Columns" box.
- Hover the cursor over "Date (Year)". A downward arrow will appear to the right. Click on this arrow.
- A pop up window will appear similar to what you worked with in previous steps. Select the appropriate year and make sure all other years are unchecked. Then hit "OK".
- Click and drag "Date (Year)" from "Range" to "Rows" and put it below "Door" in that box. When you do this, it will disappear from the "Columns" box.
- Repeat with "Date (Month)" selecting only the relevant months.
- Uncheck "Date (Quarter)" because you don't need it.
- Hover the cursor over "Date" in the "Range" box and again, click the downward arrow.
- This time, select "Date Filters", then enter the day before the first day of data you want. So if Jan. 2 is the first day of data you want, input Jan. 1.
- Click and drag "Date" to the "Rows" box.
- You don't have to use Date but it can help you see patterns a room's usage is close to the minimum but not quite where it needs to be.
- Hover the cursor over "Date (Year)". A downward arrow will appear to the right. Click on this arrow.
- Now, click and drag Event/Name to the Values box.
- This is what the final settings should look like (if you haven't checked "Date":
- Save your work as "Year-Month-Date Usage" in the Audit Analysis Reports Folder.