Comment on page
Exercise. Working with data
- Spreadsheet software (e.g. Excel, Libre Office, Google Sheets)
We will use another example dataset to play with data using spreadsheets.
The data is released under the Creative Commons CCZero licence. The original files published by Newcastle have been modified into one single file for this exercise.
One way of approaching this is to group the data by branch in a pivot table. Then display a sum of issues for each branch.
From the topics we've discussed in human data, this is using Grouping, then Pivoting, and then Roll up (using the Sum of values)
- 2.On the Excel menu select Insert > Pivot table to launch a new Pivot table
- 3.Accept the default options and click OK
- 4.In the Pivot table selections, drag the 'Library' field into the 'Rows' section
- 5.Drag the 'Issues' field into the 'Values' section
- 6.Excel may calculate a 'Count of issues' for each library. This is actually the number of rows of data for that library. Instead, we want the SUM of the issues (each value added together). In the pivot table selections click on 'Count of issues' and select 'Value field settings'. Select 'Sum' and click OK.
- 7.To sort the table, click the drop down on the pivot table labelled 'Row labels'. Click 'More sort options' and select 'Sum of issues'.
We could have used alternatives to Sum. For example, average would show which library had the highest average (mean) issues.
- 2.Import the data. Select File > Import > Upload. Drag the file newcastle_usage.csv to be uploaded, or use the file selection tool.
- 3.When prompted, select to replace the existing spreadsheet and click Import.
- 4.Select from the menu Data > Pivot table.
- 5.On the Pivot menu for the Rows option add the field Library.
- 6.On the Values option add the field Issues (ensure it says 'Summarise by SUM')
- 7.On the Rows options change 'Sort by: Library' to 'Sort by: SUM of issues'.
You should finish with a table like the following (top 3 shown only).
The library with the most issues is City. The total sum of issues is 2,949,062.
Work in groups, and share knowledge, to answer the following questions of the data. Brief guidance is given for each questions, and possible answers provided on a separate sheet. Before diving in, spend some time looking at the data and considering how it may need to be manipulated to reach the answer.
The key here is to group the data by Year. How do we do that when the field is provided in year AND month (e.g. '2008-04')?
Similar task to above, you will need to separate out the month (e.g. '04') from the year and month field ('2008-04'). How will we decide which month is the busiest, when we have data for multiple years?
- We can't rely on data being in the form that we need it
- We can apply key data skills to practical applications with library data