Courses/Computer Science/CPSC 203/CPSC 203 Template/Fall 2009 Teaching Assistants/F09 Thomas Burt/Lab 4

Lab 4

This lab may span into next week. We'll spend any leftover time on Monday talking about the quiz.

• More materials are available here

Overview

• We are going to fabricate, and then analyze some random data about a particular plant.
• The goal is to correlate location and time to various properties of the plants measured.

First, download today's spreadsheet.

Fill in random data

In the "Raw_Data" section, fill in the following:

District of Origin Data

Put this in the first cell of the District column

• =LOOKUP(RAND(),randomdata!$A$4:$B$9)

Use autofill to populate the rest of the column

Measurement Data

Use the following for the Height column

• =ROUND(RAND()*12,0)+35

Use the following for the Number of Seeds column

• =ROUND(RAND()*35,0)+6

Use the following for the Potency column

• =ROUND(RAND()*12,0)+25

Use autofill to populate the rest of the table (get at least about 50 rows)

Analyze Data

We're going to create two pivot tables so that we can do this quickly. Otherwise, we'd have to write out the aggregation function (sum, avg, etc) by hand.

Go to the 'Analysis' tab at the bottom of the spreadsheet

1. Click the 'Insert' menu at the top of the page and click 'PivotTable' on the far left
2. Click the button for the 'Table/Range' field.
3. Go over to the Raw_Data table, and select the entire dataset
4. Ensure a range is referenced (it should look like "Raw_Data!$C$4:$H$68")
5. Hit 'OK' a few times until a blank pivot table pops up.

Select the correct fields to add to the report

1. Click 'Year'
2. Move 'Year' from the "Sum-Values" box to the "Row Labels" box in the bottom right area of the screen
3. Click 'Number of Seeds', 'Height', and 'Potency'
4. For each of these columns, change entry in the "Sum-Values" box to the correct aggregate function
   1. Click the little down arrow next to the "Sum of ..." button.
   2. Select the "Value field settings" menu item
   3. Change "Summarize by" to "Average" and click OK.
5. Also change "Grand Total" to "Average Across All Years" or something that more correctly describes that row

Now, make a similar report that aggregates by district instead of by year

Chart Data

What are some good ways to represent the analysis visually?

You will have to decide for yourself what types of charts best reflect your analysis for the homework assignment. For now, just get some practice by making two or three charts.

Make sure you can justify your decisions. You will need to ensure that meaningful relationship between calculated values is well represented by your charts.

Here's what I came up with:

Note that it would be help to make sure that the vertical axis for each measurement taken lined up (ensure the same scale, range, and placement horizontally)