Lab exercise: Size Spectrum Analysis

File: d:\B173-2010\size_spectrum.wpd

Date: November 22, 2010

Rationale

As discussed in lecture, size-spectrum analysis takes the opposite approach to those models which demand more and more information to analyze a fishery.

In essence, a size spectrum analysis ignores taxonomy, and lumps individuals according to their size. A plot is then constructed of frequency versus size. Such a plot should resemble a trophic pyramid tipped over on its side, i.e., we should see a negative slope in frequency as size (often measured by weight) increases.

Furthermore, the steepness of the slope can be used as an indication of how hard we are fishing, i.e., a steeper slope indicates loss of the largest individuals.

1. What are the "pros" of size-spectrum analysis relative to a multispecies yield-
per-recruit model?

2. What are the "cons" of size-spectrum analysis relative to a multispecies yield-
per-recruit model?

Doing a Size-Spectrum Analysis

Actually doing a size-spectrum analysis could be very tedious if it weren't for certain powerful tools available in Excel (and other spreadsheets). These include the ability to Sort a column of data and what is known as the Frequency (or Bin) function.

The raw data for a size-spectrum analysis will be a long list of sizes (typically weights) of individual fish caught.

Sorting: The first thing you might want to do is to sort the data into ascending order. Use the Sort menu item to do so: Highlight the data to be sorted, then choose Data, then Sort and follow the menus. [Note: It isn't actually necessary to sort the data for the Frequency analysis shown below, but sorted data makes it far easier to check your results]

Frequencies: It would be pointless to plot the frequency of the raw data because each fish would have a unique weight, e.g., one might be 10g and another 11g, so we really want to lump values into bins, e.g., 1-10, 11-20, etc.

To do this, we use the Frequency function. In theory, the frequency function uses two lists of numbers. The first list is the raw data to be processed. The second list is a list of "bins". In our case, we are going to use the bins "10", "20", "30", etc up to "100". The idea is that Excel processes the first list and tallies the number of items in that list that fit in each bin. An item is said to fit in a bin if it is larger than the last bin label, but smaller than or equal to the current bin label. So, for example, if our bins are "10", "20" and "30", and our data list consists of the numbers 6, 10, 14 and 25, they would be put in bins as follows:

Bin	Item

10	6, 10

20	14

30	25

Excel doesn't tell you which items are in which bins, it merely returns the tally or frequency of items in the bin, so in this case it would be

Bin	Frequency

10	2

20	1

30	1

The function that does this looks like this:

{=FREQUENCY(A$10:A$50,B$10:B$20)}

but there is a bit of a trick to this function. The FREQUENCY function is the first "array" function that you have encountered, denoted by the '{' and '}' symbols around the formula. There is a large group of Array functions that allow higher level analysis for all sorts of things. The trick to using array functions is that you can't just enter them by typing, rather you need to do a slight modification, which must be done a very particular way. .

So, here is what you do. WARNING: EXCEL IS REALLY FUSSY about how you do this. You must do it EXACTLY as I say or it won't work.

Enter the bin labels in column B. I will assume that your first bin label goes in cell B10 and your last one is in B20. Notice that I entered the word "over" in cell B21. This is to hold any values that are above the maximum bin. Do NOT include the "over" in the range of bins, i.e., notice that the formula below says B$20, not B$21. .

I also assume that your data is in A10..A57. In cell C10, type

=FREQUENCY(A$10:A$57,B$10:B$20)

i.e., DO NOT enter the curly braces and pay attention to the : and , symbols.

Once you have entered this, hit the Enter key.

**Alternatively, you could use the Insert Function menu choices to achieve the same thing. FREQUENCY is in the Statistical function set. The first range (A$10:A$57) is the raw data. The second range (B$10:B$20) are the bins into which the raw data is going to be placed.

Now, position the cursor back over cell C10 by clicking on cell C10. Now highlight C10 and the 10 cells below it, ie., you should now have cells C10 through C21 highlighted.

Move the cursor back up to the edit window, click once, and then hit Ctrl-Shift-Enter all together. This will put the little curly braces around the function and activate it as an array function.

Notice that the FREQUENCY function (like most functions in Excel) is continuously active. By that I mean that if you change the data (e.g., you realize that you made a mistake in entering some data points so you correct the entries), the FREQUENCY function will update the results in the bins instantly. [This is not true of SORT, by the way].

Note: While you can edit the data, you cannot edit the formula – Excel highly resists this, so if you make a mistake, you are best to start again and retype the correct formula in C10.

What is the "over" bin for? If there are any values which exceed the range of the bins, i.e., in this case, have a value equal or higher than 110, they go into the "over" bin. There really shouldn't be any values in there so if there are, you didn't do something correctly. Try changing a value in the data to see if you can get a value in the "over" bin.

3. Do a size-spectrum analysis for the following data. This data could easily be (though it isn't actually) the kind of data one might get from fishing at Putah creek, a local water body. After putting the data in bins, plot a regression of Frequency versus bin weight for each year and compare the slopes. From this analysis, does it appear that the population has been fished harder in 2005 or in 2004?

2004 weights caught:

21 31 31 4 12 14 27 23 23 41 43 44 51 52 61 100 76 76 62 17 5 6 2 2 2 7 8 8 8 18 19 11 11 16 15 30 30 22 22 56 45 31 34 36 35 45 46 53

2005 weights caught:

27 27 20 20 20 2 2 5 6 34 37 40 45 46 47 50 51 35 36 17 16 10 10 2 4 8 7 9 8 10 30 28 14 15 39 38 48 53 19 19 18 23 26 29 54

-- END