Exercise 12.0.0.

This exercise relates to all of Chapter 12 (community ecology), and is primarily aimed at helping you to prepare data and assemble it in a form that allows you to carry out further investigation. This follows on from an earlier exercise in Section 3.2.7, where you used an Excel Pivot Table.

• Introduction
• The exercise data
• Make a Pivot Table
• Make species lists
  • All species
  • Species by site
  • Presence-absence by site
  • Abundance-based lists
  • Constancy tables

I am text block. Click edit button to change this text. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

It is important that your data are arranged and set out in a manner that allows you to carry out the analyses that you require. In general, a scientific recording layout is a good starting point (see Section 2.2). In the scientific recording layout (a.k.a. biological recording layout) you have a column for each variable (e.g. site name, species name, abundance). For most purposes this is the most “robust” way to record your data, as you can use the data most flexibly. For community analyses however, you’ll generally want to have the data arranged by site and species, with the rows being site names and the columns the species (with the body of the table being the abundance).

This exercise shows you how to take a dataset that is in recording layout and convert to community layout. Get the datafile for this exercise: Preparing and managing community data exercise.xlsx.

The exercise data are in the file Preparing and managing community data exercise.xlsx. There are several worksheets, with the Data tab being the main data. The other worksheets contain a note about the Domin scale (an ordinal scale of plant abundance), a completed Pivot Table and a dataset ready for export as CSV.

The data show the abundance of some terrestrial plant species at ten sites in Shropshire. These data are part of a series of NVC surveys carried out by student groups. Here are the first few rows of the dataset:

The first few rows of the example dataset.

The data are as follows:

• Site = Site names as a simple label.
• Species = Plant species names.
• Const = Frequency of occurrence (a value from 0-5).
• Cover = The maximum abundance in Domin scale (a value from 1-10) from the 5 quadrats at each site.
• Imp = A combination of Const and Cover (Const * Cover ÷ 5) to give an abundance score.

These data are set out in scientific recording layout, that is, each column is a single variable and each row is a “record”. This gives the most flexibility and maximises the usefulness of the data. However, for community analysis we’ll need the data in community layout like so:

The example data laid out as a community dataset. Each column is a separate sample.

In this case the columns are the sites. It is easy to transpose the data, either in Excel or later on if you have exported the data to CSV.

Although this is the “ideal” layout for the purpose of community analysis there are other ways to manage the data, which you’ll see as you work through the exercise. The manipulations are illustrated using Excel 2013 (Windows) but you can follow along with most versions of Excel as well as Open Office or Libre Office.

The starting point for your data arrangement and management is a Pivot Table. This will allow you to arrange and rearrange your data in many (useful) ways. You can also use a Pivot Table to get summary information and to make exploratory graphs.

In Excel 2013 the Pivot Table button is in the Insert ribbon menu. In other spreadsheets you may find the appropriate function in the Data menu. Start by making a new blank Pivot Table in a fresh worksheet:

1. Click once anywhere in the block of data; there is no need to highlight or select any data.
2. Click the Insert > Pivot Table
3. The Create Pivot Table dialogue box should open and you’ll see that the data are automatically selected. The default is to place the new pivot table in a new worksheet so you can simply click OK.

You should now see the new (empty) pivot table and the Pivot Table Fields dialogue box. In most versions of Excel you drag the items you want from the top (the list of variables) to the appropriate section at the bottom of the dialogue window (in some spreadsheets you drag items directly to the pivot table).

Now you are ready to build your dataset. There are many options and in the following exercise you’ll get to see several ways to present the data.

There are various simple lists you can make.

A good starting point would be to make a list of all the species present across the survey sites.

1. Click on the pivot table (currently empty) to ensure that the Pivot Table Field dialogue box opens.
2. Now drag the Species field from the list at the top to the area labelled Rows at the bottom of the dialogue box.

Now you have a simple overall species check-list. This is useful as a data validation tool, as you can look through it for spelling mistakes (two entries that are similar, one will probably be a mistake).

It would also be useful to have a list of the species at each site. There are two ways you could try:

• List all sites with their species
• List sites and their species sequentially

Listing all the sites seems the most obvious.

1. Click the Pivot Table to open the Pivot Table Field dialogue box.
2. Drag the Site field item from the top to the Rows area at the bottom. Make sure that the Site item is above the Species item (you can drag to rearrange them anytime).

Now you have a compact table showing the species lists for all the sampling sites:

Species lists per sample site.

Try dragging the Site field so that it is underneath the Species field in the Rows area. You now get a list of which sites each species was present in.

You might also want to display a single site, rather than all of them. There are two simple options:

• Filter the Site field to display one (or more) of the sites.
• Move the Site field to the Filters area (sometimes called Report Filter), where you can apply the filter.

The first option allows you to keep the species lists separate per site.

1. Click the pivot table to activate the Pivot Table Field dialogue box.
2. Move the mouse over the Site field at the top of the Pivot Table list. You’ll see a triangle at the right end of the highlight, click that to open a sort & filter dialogue box.
3. Click the box(es) beside the items you want to view or not. A ticked box will be displayed and an empty box will be not displayed!
4. Click OK when you are done to apply the filter. Note that you’ll see a funnel icon by the Site field in the list area.

The second option allows you to combine lists across sites.

1. If you applied a filter earlier clear it now. Click the Pivot Table then the triangle in the Site field to open the sort & filter dialogue.
2. Click the Clear Filter from “Site” button to remove the filter.
3. Now drag the Site field from the Row area at the bottom to the Filter area.
4. Now you see the top of the pivot table read Site and to the right a label (All) and a grey triangle.
5. Click the triangle to open the filter dialogue.
6. If you click a single item, you’ll display the species for that site. If you click several sites, you’ll display a list encompassing all those sites.

This approach could be useful if you have “grouped” samples, perhaps from different habitats.

Simple species lists are useful but for calculations of species richness or similarity you’ll need to make a presence-absence dataset. That is one where each species has a 1 for presence or 0 for absence.

This is easily done with the pivot table.

1. If you have filters applied they will usually be cleared if you move a field to a new location. Alternatively, you can remove the fields and start afresh (drag the fields out of the lower part of the Pivot Table Field dialogue box or un-tick the fields in the list at the top).
2. Drag the Species field to the Rows area.
3. Drag the Sites field to the Columns area.
4. Now you have a table arranged in a sensible fashion but no data! Drag the Species field from the list at the top to the Values area at the bottom (of the Pivot Table Field dialogue box).

Now you’ll have a table where the presence of species is shown by a 1. The absences are blank spaces. At the moment you’ll also have row and column totals.

• Row totals show the frequency of each species across the samples.
• Column totals show the species richness for each site.

If you need to carry out analysis, then you may well wish to remove the totals and also to replace the blank cells with 0. To do this you’ll need to use the Pivot Table Tools menus.

1. Click once in the pivot table to activate the Pivot Table Tools ribbon menus. In Excel 2013 there are two: Analyze and Design. In older versions there were three menus but you should not have too much difficulty finding the right buttons.
2. Remove the totals using the Design Click Grand Totals > Off for Rows and Columns.
3. To replace blanks with 0 you need the Analyze Click Pivot Table > Options to open the Pivot Table Options dialogue box.
4. On the Layout & Format tab (this should be in view by default) enter a 0 in the box labelled For empty cells show. The tick-box beside the label needs to be ticked but this is usually already activated.
5. Now click OK to apply the changes.

Now your data are in a form that could be exported as CSV, although you’d need to remove the first three rows from the final CSV.

If you need to have the sites as rows, then you can simply rearrange the fields between the Rows and Columns areas. If you are going to use R then there is no need to do this as you can easily transpose data using the t() command.

Since these data are accompanied by abundance data it is most likely that you’ll want to use the abundance instead of presence-absence. In the dataset the Imp variable is a measure of abundance derived from the frequency and maximum (Domin) cover. Each sample site is a combination of 5 quadrats but the individual quadrat data is not available.

To make an abundance-based community dataset you simply rearrange the pivot table:

1. Click once on the pivot table.
2. Now rearrange the fields so that Species are in the Rows area.
3. Make sure the Site field is in the Columns area.
4. Remove the item in the Values area; just drag it away and it’ll disappear.
5. Drag the Imp field from the list at the top to the Values area at the bottom.

The Values item should read Sum of Imp. Since there is only one value per species/site combination this is fine. If the field does not show Sum, then click on it and use Value Field Settings to alter the summary to Sum.

You can arrange the Site and Species fields in different ways, just as you did when making simple lists. Try it out and see what you get. The most useful arrangement for community analysis is where you have Sites as columns and Species as rows. If you need to have the sites as rows, then you can simply rearrange the fields between the Rows and Columns areas. If you are going to use R then there is no need to do this as you can easily transpose data using the t() command once you have the data in R.

A constancy table is used to display the survey results of a British NVC plant survey. For each site/sample the species are listed. The abundance data are given as a pair of values:

• The constancy – that is the frequency of occurrence (1-5, absences are not shown).
• The cover – that is the maximum abundance from the 5 sample quadrats) in domin scale (1-10).

Even if you are not reporting an NVC survey the process will help to show you how flexible the pivot table can be.

1. Start by removing any previous data from the Pivot Table.
2. Drag the Species field to the Rows area.
3. Drag the Site field to the Columns area.
4. Now drag the Const field to the Values area. You now have a table showing the frequency of each species by site.
5. Now drag the Cover field to the Values area; drop it underneath the Sum of Const item that’s already there.

Now you have a table showing the Constancy (Frequency) and Cover (max abundance) for each species and site. This is not the most “readable” of tables, so try some modifications.

Try dragging the Site item in the Columns area so that it is underneath the ∑Values item. Now you have two tables side by side, one showing constancy, the other cover.

Move the Site item again, this time place it above the Species item in the Rows area. Now you have a much more useable table, with the sites being shown atop one another.

With a bit of tweaking you can recreate the original data layout!

1. Click once on the pivot table to activate the Pivot Table Tools
2. Click the Design > Report Layout button and select Show in Tabular Form.
3. Now click the Report Layout button again and select Repeat All Item Labels.
4. Click the Design > Subtotals button and select Do Not Show Subtotals.

Obviously it is a little silly to recreate the original dataset; this just goes to show how you can arrange and rearrange the data easily using a pivot table.

There is a lot more to Pivot Tables (as well as general data management) in my book Managing Data Using Excel!

Exercise 11.1.

This exercise is concerned with how to produce and chart some diagnostics for regression models (Sections 11.1 and 11.2). The dataset used to illustrate these notes is available as a file: regression diagnostic.csv.

• Introduction
• Fitted values
  • In R
  • In Excel
    • Use Analysis ToolPak
• Residual values
  • In R
  • In Excel
    • Use Analysis ToolPak
• Standardized residuals
  • In R
  • In Excel
• Diagnostic plots
  • Residuals vs. Fitted values
    • In R
    • In Excel
  • Distribution plot of residuals
    • In R
    • In Excel

When you only have two variables (a predictor and a single response) you can use a regular scatter plot to show the relationship. Even if the relationship is logarithmic or polynomial you can represent the situation, as long as there is only one predictor variable.

When you have two or more predictor variables it becomes hard to represent the situation graphically. You can try a 3D plot but they are rarely successful. Generally, you’ll stick to plotting the “most important” predictor variable and display the model as a standard regression table.

However, it can be helpful show some diagnostics from your regression model as graphics. These notes show you how you can produce some simple regression diagnostics and present them graphically.

The data used for these notes can be downloaded as a CSV file: regression diagnostic.csv. The data are the same as shown in the book (mayfly regression) but reproduced here to make it easier to follow the notes.

When you make a regression model you are trying to find the “best” mathematical fit between the variables. In a simple form the relationship will be:

y = mx + c

In this case there is a single predictor variable, x. The m is the slope and c is the intercept. Both m and c are known as coefficients. You might have a logarithmic or polynomial relationship but essentially the form of the relationship is the same:

y = m.log(x) + c
y = ax + bx^2 + c

In multiple regression you have more than one predictor and each predictor has a coefficient (like a slope), but the general form is the same:

y = ax + bz + c

Where a and b are coefficients, x and z are predictor variables and c is an intercept. You can add more and more variables:

y = m1.x1 + m2.x2 + m3.x3 + ... + c

Note that each predictor variable has a unique coefficient.

Once you have your “equation” you can use the observed values of the predictors and of the coefficients in your equation to produce new y-values. These values are “fitted” to the regression model. Think of them as being idealised values or “expected” values.

Note that you should not call these fitted values “predicted” values because they are based on your observed predictor variable values. If you insert predictor values that you did not observe into the equation, then you can call the resulting y-values predicted y-values. The values you insert to the equation could lie within the max-min range of observed predictor (interpolation) or be outside (extrapolation). In general, it is “safer” to go with interpolation than with extrapolation.

Fitted values are easy to compute in R. You can get them from the result of a lm() command in two ways:

model$fitted.values
fitted(model)

In both cases model is the result of a lm() command.

names(mf)
[1] "Length" "Speed" "Algae" "NO3" "BOD"
mf.lm = lm(Length ~ BOD + Algae, data = mf)

Then you can get the fitted values:

fitted(mf.lm)
1        2        3        4        5        6        7        8
16.71301 17.70924 19.40969 21.65981 21.79722 20.93840 21.88309 19.12458
9       10       11       12       13       14       15       16
17.22829 16.42101 19.50246 20.23417 21.14452 20.23417 17.79511 16.72331
17       18       19       20       21       22       23       24
17.57183 19.29977 22.31250 14.66902 16.47254 24.35649 22.90681 22.52893
25
22.36403

Once you have the values you can use them like any other numeric variable (the vector has a names attribute).

To get fitted values in Excel you’ll need to calculate the coefficients and plug the values into the spreadsheet to generate them:

1. Arrange your data so that the predictor values are next to one another.
2. Use the LINEST function to determine the coefficients:
   1. Highlight one block of cells in a row, you need one cell per coefficient.
   2. Type =LINEST and start the formula, inside the () you need
   3. The y-values, the x-values, 1, 0. Note that the final 0 suppresses additional regression stats, you only get the coefficients.
   4. Enter the formula as an array using Control+Enter
3. The results of LINEST show the coefficients backwards! The intercept is last. The first coefficient shows the last of the predictor variables.
4. Use the coefficient values and the observed predictors to make your predicted values.

Once you have the coefficients it is fairly easy to create the fitted values but you will need to “fix” the references to the coefficients using $ in the cell reference(s). Then you can copy/paste the formula down the column to fill in the fitted values for all the rows of data.

Residual values are the difference between the fitted values and the actually observed values for your response variable. Think of the fitted values as being the “ideal” or “expected” values based on your regression equation. The residual values are (usually) not ideal and differ from the “perfect fit”.

So, the residuals are a way to see how bad the fit is between the ideal values based on your regression model and the real situation. In general, you want to see that the residuals are normally distributed, at least this is what you want when doing regular linear regression (and curvilinear). In diagnostic terms it is the normal distribution of the residuals that is the really important thing, not the distribution of the original data (although usually you do not get the former without the latter).

Residual values are easy to compute using R; you get them from the result of a lm() command:

model$residuals
resid(model)

Simply make your regression model result then away you go:

mf.lm = lm(Length ~ BOD + Algae, data = mf)

fitted(mf.lm)
1        2        3        4        5        6        7        8
16.71301 17.70924 19.40969 21.65981 21.79722 20.93840 21.88309 19.12458
9       10       11       12       13       14       15       16
17.22829 16.42101 19.50246 20.23417 21.14452 20.23417 17.79511 16.72331
17       18       19       20       21       22       23       24
17.57183 19.29977 22.31250 14.66902 16.47254 24.35649 22.90681 22.52893
25
22.36403

The result of resid() is a numerical vector (with a names attribute), which you can use in various ways (as you will see later).

Residuals are simply the difference between the observed and expected values of your response variable. You can use the regression equation for your model to generate the fitted values. Once you have those you simply subtract the fitted value from the observed:

Observed – Fitted = Residual

Note that you always carry out the calculation this way around by convention.

The Analysis ToolPak can compute the residuals for you easily. You simply tick the box labelled Residuals in the Regression dialogue box. The process is exactly the same as when calculating the fitted values.

The residuals are placed alongside the fitted values (which are labelled Predicted), and are labelled Residuals.

Standardized residuals are residuals that have been rescaled. The way they are scaled is such that each residual value has unit standard deviation. The standardization process takes into account how much “influence” a datum might have on the regression (something called leverage).

It is generally preferable to use the standardized residuals.

You can compute the standardized residuals using the rstandard() command:

rstandard(mf.lm)
1           2           3           4           5           6
1.89433577  1.85925744  1.53830120  0.77572446 -0.45212719 -0.52806189
7           8           9          10          11          12
-1.70410540 -1.82866111 -1.29005538 -1.41946426  0.85656348  0.43789841
13          14          15          16          17          18
-0.09180616 -0.13389770  0.71314102  0.75324789 -0.32958291 -0.16844397
19          20          21          22          23          24
-0.76996058 -1.02235369 -0.27307270  0.39957434  0.63390452  0.27061395
25
-0.20911178

The result is a numeric vector (with a names attribute).

You cannot calculate the standardized residuals in Excel without some tedious computations! The Analysis ToolPak claims to calculate standardized residuals but what you end up with is the residuals divided by the standard deviation of the residuals. This is only an approximation to the “proper” standardization.

There are several type of plot you might use as diagnostic tools. The ones I’ll show here are:

• Residuals vs. Fitted values.
• Normal distribution plot of standardized residuals.

You can make these plots easily using R. In Excel you can make some worthwhile attempts but you can only use approximately standardized residuals.

You can calculate the fitted and residuals easily in R or Excel. A plot of the residuals against the fitted values is a simple way to produce a useful diagnostic tool.

Ideally you would like not to see any kind of pattern. In the example there are some points identified as possibly having undue influence but the lowess line runs aimlessly along the middle.

You can produce a similar plot using standardized residuals and regular plot commands:

plot(fitted(mf.lm), rstandard(mf.lm))
abline(h = 0, lty = 3)
lines(lowess(fitted(mf.lm), rstandard(mf.lm)), col = "red")

It is fairly easy to plot the regular residuals against the fitted values using Excel. Once you have calculated the values you can simply make a scatter plot. However, it is not so easy to add a locally weighted polynomial. There are add-ins you might try but a simple workaround is to use a moving average trendline. To incorporate this, you need to sort the fitted values in ascending numerical order.

1. First of all, you need to calculate the fitted values and the residuals. You can do this using the Analysis ToolPak.
2. If you used regular commands to compute the values, then:
   1. Copy them to the clipboard.
   2. Paste Special the values only to a new location.
3. If you used the Analysis ToolPak you do not need to do anything.
4. Highlight the values (include their headings) and sort them:
   1. Click the Home > Sort & Filter button.
   2. Click Custom Sort.
   3. Use the dropdown on the left to sort by the Fitted values.
   4. Make sure you use Smallest to Largest.
   5. Click OK to apply the sort.
5. Now you have the fitted values sorted in ascending numerical order.

Make a scatter plot by highlighting the fitted and residual values data (and the column headings). You want to ensure that the fitted values are in the first column (Excel will expect this).

Ideally you want the residuals (standardized) to be normally distributed. In R you can calculate the standardized residuals and plot a histogram or QQ plot to show the distribution. In Excel you can only approximate the standardized residuals, which you can plot as a histogram or a QQ plot (with a bit of work).

Once you have the residuals in Excel (either by direct calculation or via the Analysis ToolPak) you are well on your way to making a normal distribution plot. If you want the standardized residuals you will have to put up with Excel’s approximation. You can work out the approximate values using:

Approx Std.Resid = Residual / Stdev(Residual)

Or let the Analysis ToolPak calculate this for you.

You can then make a histogram of the values. You’ll have to select appropriate bins and use the FREQUENCY function to work out the frequencies. Alternatively, you can let the Analysis ToolPak work out frequencies for you via the Histogram routine (which will make the chart too).

It is possible to produce a QQ plot, which requires a few steps:

1. Make a column of “ranks”, which is a simple list of “observation number”, values from 1 to the number of replicates (rows) in your dataset.
2. Now determine the cumulative proportion for the rank values. You need a formula resembling: =(rank-0.5)/count(rank1:rankn) where rank1:rankn is your column of rank values and rank is the rank you are working out the proportion of. You will need to “fix” the cell references for rank1:rankn using $.
3. Copy and paste the formula down the column. You should see ascending values rising towards a value of 1 (you’ll probably end up with 0.98).
4. In the next column you need to calculate a z-score. Use NORMSINV to work this out using the proportions you just calculated. You should end up with a series of values starting at about -2 and rising through 0 to around +2.
5. In the next column copy your standardized residual values.
6. Sort the residuals from smallest to largest.
7. Highlight the z-score and sorted residuals data.
8. Insert a Scatter plot.

Exercise 10.2.b.

These notes are concerned with the Kruskal-Wallis test (Section 10.2). The notes show you how to adjust your test statistic when you have tied values, and so tied ranks.

• Introduction
• Calculating the adjustment factor
• Applying the correction to H
• Example Data
  • Example Ranks
  • Original H value
  • Tied ranks
  • Final H adjustment

The Kruskal-Wallis test is appropriate when you have non-parametric data and one predictor variable (Section 10.2). It is analogous to a one-way ANOVA but uses ranks of items in various groups to determine the likely significance.

When you have tied values, you will get tied ranks. In these circumstances you should apply a correction to your calculated test statistic. The notes show you how this can be done. The calculations are simple but in Excel it can be difficult to get the process “automated”. In R the kruskal.test() command computes the adjustment for you.

In order to correct for tied ranks you first need to know which values are tied. Then you need to know how many ties there are for each rank value. Finally, you’ll need to know how many replicates there are in the dataset.

Once you’ve ascertained these things you can use the following formula to work out a correction, or adjustment, factor:

In the formula t is the number of ties for each rank value. For each value of t, you evaluate the t-cubed minus t part. This is then summed for all the tied values (values without ties can also be evaluated but 1³ – 1 = 0). Once you have the numerator you work out the denominator using n, the number of replicates in the original dataset.

The final value of D is then 1 – your fraction.

Once you have the value of D, the correction factor, you can use it to adjust the original Kruakal-Wallis test statistic (H).

Formula for adjustment of the Kruskal-Wallis statistic in the case of tied ranks.

The correction is simple: H/D.

You then use the Hadj value in place of the original to determine the final test significance (using critical values tables – see exercise 10.2a).

Have a look at the correction in action using the following example:

Example data. Three non-parametric samples for use in Kruskal-Wallis test.

Here there are three samples and you can see that there are tied values, so you know there will be tied ranks.

The first step is to evaluate the ranks. Each value is replaced by its rank in the overall dataset.

Sample data shown as ranks instead of original values. Samples are combined for ranking.

The Kruskal-Wallis test looks at the sum of the ranks from each sample. If the ∑rank is different between samples there is a good chance that differences are statistically significant. If the ∑rank are close, then differences are less likely to be significant.

In this example the rank sums are:

Rank sums for three samples.

You can now calculate the Kruskal-Wallis test statistic, H.

Once you have the rank sums you can compute the Kruskal-Wallis test statistic:

Formula for calculating the Kruskal-Wallis statistic for non-parametric samples.

The Kruskal–Wallis formula looks pretty horrendous but actually it is not that bad. The numbers 12 and 3 are constants. Uppercase N is the total number of observations. The R refers to the ranks of the observations in each sample and n is the number of observations per sample.

In the example the final value of the Kruskal-Wallis statistic works out to be H = 6.403.

When you paste the ranks you only want the numbers and don’t want the formula (which would give an error).

Once you have the column of ranks you can simply re-arrange them in ascending order (use the Home > Sort & Filter button or the Data > Sort button). In the following table the second column shows the sorted ranks (you don’t need to make a second column, I have done this to show the effect).

Rearranging ranked data to make calculation of tied ranks easier.

Once you have the ranks in order it is easy to work out the number of repeats by inspection. You can simply fill in the values as you work down the column. In the preceding table the 3rd column shows the tied rank repeats. So, for example the rank 1.5 is repeated 2 times. The rank 9.5 has 4 repeats.

The final column shows the T³ – T values. In other words, you take the number of repeats and cube it, then subtract the number of repeats.

Once you have these values you can sum them to get an overall value, in this case 102.

Once you have your final sum of T³ -3 values you can work out the value of D using the formula given earlier. You need to know the total number of replicates in the original dataset (in this case 17).

The final value of D works out at: 0.9792.

The adjusted H value is then H/D = 6.403 / 0.9792 = 6.540.

You can now use the adjusted H-value to compare to critical value tables (see exercise 10.2a) to see if your result is statistically significant.