Home

improving data collection, storage, handling, visualization

1. deal al ja HE gt z Change Save As Switch Select I ol a IH E Move Chart Type Template Row Column Data Na E Chart Type Data Chart Layouts Chart Styles J Location Chart 3 y a fe i A P aB d C D E E G H ZE PivotChart Filter Pane X PivotTable Field List 1 Active Fields on the PivotChart El Choose fields to add to report 3 Values AAA 4 Row Labels y Average of Sea Cucumber Total StdDev of Sea Cucumber Total nada Holothuria atra 5 Chuuk 4 086956522 11 69074969 R Holothuria edulis 6 Etal 1 166666667 2 520034665 1 Axis Fields Categories Holothuria fuscopuntata 7 Kuop 0 6 0 855005545 Island Pearsonothuria graeffi 8 Losap D 5 0 731083277 jStichopus chloronotus 9 Lukunor 0 0 C Legend Fields Series L Stichopus hermanni 10 Murilo 0 55 2 074880287 Values JThelenota ananas 11 Nama Q Qiz z Thelonota anax 12 Nomwin 0 025 44 X Values Sea Coramber Total 13 Satawan 0 275 14 blank T Average of Sea Cucumber Total C Echinaster luzonicus _ Acanthaster planci 15 Grand Total 2 025 StdDev of Sea Cucumber Total A 16 10 JCulcita novaguinea 17 Linckia guildingi 18 8 Lincia laevigata E Average of Sea Cucumber 19 6 Total seastar Total 20 L Echinometra 21 4 E StdDev of Sea Cucumber 22 2 T Drag fields between areas below 23 d J Y Report Filter Cj Legend Fields 24 0 T i T
2. 19 Go to the insert tab from Excel s main menu and choose Table 20 Click OK 21 Confirm below DI1 i Yes Inner 0 0 0 0 22 28 88213 0 2 Yes Inner 0 0 D 14 39864279 D 3 Yes Inner D D D D 106 9436501 278 5780129 5 4 Yes Inner 0 0 0 0 1636 499422 0 D 5 Yes Inner D D D D D 219 9682947 7 DI2 1 Yes Inner 0 5 61549908 0 38 07614397 20 87648147 B 2 Yes Inner D 34 68837875 D 89 38812819 0 3 Yes Inner 0 0 0 0 49 99871473 27 58813905 10 4 Yes Inner 0 8 895905473 0 0 22 54191114 o 1 o Yes Inner D 17 9181095 51 16810232 0 0 12 DO1 1 No Inner D D D 0 3344 823399 803 8615862 13 2 No Inner 0 0 0 0 801 6671508 825 504714 4 3 No Inner 0 D D D D 928 3573877 15 4 No Inner 0 0 D D D 250 0795945 6 5 No Inner D D D D D 3943 030976 17 DO2 1 No Inner 0 o 0 0 410 4122884 42 20930013 2 No Inner 0 0 D D 11 52533634 2 651510778 41 0386 3 No Inner 0 0 0 38 20208402 21 42392402 4 No Inner D D D D 60 24855971 71 45131273 1 5 No Inner 0 0 0 0 0 426 7695095 22 KI1 1 Yes Inner 26 94957737 0 D D 1361 965315 162 9379989 2 Yes Inner 40 80056086 0 D D 226 5029675 20 8537612 3 Yes Inner 58 94293881 D D D 208 8141772 35 2565636 4 Yes Inner 26 94957737 0 0 0 188 4801602 232 2596791 26 5 Yes Inner 183 0260262 D D D 446 015026 D JKI2 1 Yes Inner 0 71 1290243 D 41 08601422 22 28 88213 69 32168018 2 Yes Inner 0 5 61549908 D D 127 610225 124 7121333 3 Yes Inner 0 26 62324126 0 0 109 7320328 4 557385076 4 Yes Inner
3. 55 On the left hand side of the screen highlight the log transformed sheet 56 Go to the Analyse menu a Select Hesemblance make sure you are calculating a Bray Curtis similarity matrix again 57 Click OK 58 Rename the resultant matrix Bray Curtis similarity See the previous exercise for a more formal definition of what this similarity matrix represents PRIMER 6 Bray Curtis similarity Ib File Edit Select wiew Analyse PERMANCYA Tools Window Help D Gr E de E Bh Pohnpei fish MPA PERMAM VA esercise H T FahnperMPA fish PERMAMNLUVA example m Overall Transformi HEBR log transformed E m Hesemblancel Ss Resembl f MDS E Graphi 9 Graph is Design E E Hesemblancez Bray Curtis similarity Exercise 8 Similant Sa Sa S4 55 sb ar S0 29 510 511 512 ss 514 sis S16 me 4 7 Oto 100 gt cy af ur 55 831 92 07 19 63 24 745 36 92 24 142 28 403 14 425 3r 313 35 15 1714 16 444 32 513 19 556 41 567 19 12 21 017 32 346 24 365 15 853 11 524 44716 38 231 38 313 39 599 32 r 99 40 506 22 401 38 457 45 175 31 13 33 46 22 044 40 172 S4 af 425 44 616 27 140 30 466 31 384 34 3253 34 704 Managing and Using Data Guidebook 55 5I 41 3 138 Page 59 Go to the PERMANOVA menu a Select PERMANOVA 60 Under Design Worksheet select DesignT 61 Under Test a Select Main test You can use
4. Analyse between Measure Samples Bray Curtis similarity CO Variables Euclidean distance C3 More tab Add dumme variable Exercise 7 omass Acanthurus liq 4canthurus ni amp canthuruz tr cara d i T A 6 0978 5 0749 5 0749 O A Al OO O Managing and Using Data Guidebook 0 D Oo OD Oo O 114 Page 69 Under Analyze Between a Select Samples 70 Under Measure a Select Bray Curtis similarity 71 Click OK P PRIMER 6 Resem2 EJ ES fh File Edit Select View Analyse PERMAMOVA Tools Window Help w Ses Sel SLE Ole mls oh Yap multivariate fish exercise EN ed rap Mimpal MPA Fish PH working similarity 0 fo 100 sq Overall Transform 561 Em 3m channel transformed emm Resemblancel Bp Oval Tensions 64 976 MA 38 233 D a 31472 39 833 39 936 39 552 60 09 50 27 53 301 51 273 46 623 83 483 59 341 61 578 44 828 72 784 54 885 38 987 44 467 23 986 41 951 bo ba 1 A El Reseml s o a fe Mos su T Eh ep 5225 A Ga irata EL SB 00 232 70 827 55 242 55 338 Qr ha bo oo oo Qu o I 542 S43 20 901 45 405 FO Db3 64 325 E cn il E E ES 362 a Qu mq R i e cn M gt Row 1 Col 1 Exercise Managing and Using Data Guidebook 115 Page Now we have a data matrix that compares every possible combination of tra
5. JD0 o sss 208 O 0 20 wQn 40 20 amp 08 0 Eje 0 0 0 0 t4 X 0 0 a3 622 0 2012 0 0 0 M8 0 Eum 0 se 0 0 0 0 0 Tzs 2909 0 155 00 00 X00 00 90 82 0 0 90 2988 24 p X 0 0 455 4ms3 0 40 83 09 X 0 X 0 8 23 0 00 0 8823 0 39086 8065 18185 58807 0 0 0 248 13950 0 D 820 0 2590 oo 0 X 0 3081 o arero 33502 o 48791 0 0 13980 amp 0 a G5 0 0 85B 20 0 0 184 0 187 63377 0 0 0 y 0 0 Q0 29 0 1 995 0 0 0 0 20 1X4 amp 75 0 0 0 0 0 0 Q0 A 0 80 0 Hm 4m 290 0 29875 2814 0 18443 0 2006 0 4093 80 92 0 19042 0 0 2217 8534 0 15234 2072 0 0 X 40 0 3 0 2413 24 D 829 0 1 of 13945 0 0 of of 1448 0 sr 0 a 90 90 90 35 o 0 M88 0 0 0 0 0 802 756818 0 0 0 0 0 40549 Q0 3 955 0 0 0 00 4 20 4D 2082 13398 oaa 0 8933720 amp 0 0 0 0 D 932 3937 0 900 X 00 X 00 X060 9 9B 2090 93 0 9 20 9 9D X 0 A3 0 J D0 89 33 294 0 00 X 0 y 0 0 3855 178042 0 0 0 X 0 X 0 320 Q0
6. JTridacna spp Clam Total Crinoids Lobster all spp JOctopus Actinopyga mauritiana Drag fields between areas below Wo Report Filter Column Labels del Row Labels E Values NN Defer Layout Update 15 Click and Drag the sland Box from the Pivot Table Field list on the right and place it down in the Row Labels 16 Click and Drag Sea Cucumber Total box from the Pivot table field list hint you need to scroll down to find it and place it under values 17 Click one time on the Count of Sea Cucumber Total box under values a sub menu should pop up a Click on the Value Field Setting Notice count is selected but we want to examine average values found on each transect Exercise 2 Managing and Using Data Guidebook 17 Page b Click on Average c See below for a confirmation of these steps A 1 2 G 3 Row Labels a Average of Sea Cucumber Total 4 Chuuk 5 Etal 6 Kuop 7 Losap 8 Lukunor 9 Murilo 10 Nama 11 Nomwin 12 Satawan 13 blank 4 086956522 1 166666667 0 6 0 5 0 0 55 0 0 025 0 275 14 Grand Total 38 4 4b pl Ready Brainstorm metadata fields Lets also view the standard deviations to understand how the data was spread among the surveyed transects 2 025 Metadata Chuuk REA Invert Pivot D Database build E Lill a PivotTable Field List Choose fields to add to
7. First we will use a Pivot Table to transform the look of our data table for easier interpretation 1 Insert a Pivot Table call it Kosrae benthic pivot a Place Sample ID under Row labels and HC under Values two times b Change the attributes of one of the HC boxes to Average and the other to StDev c Confirm below Exercise 5 Managing and Using Data Guidebook 52 Page Drag fields between areas below W Report Filter FA Column Labels Values de Row Labels gt Values Average ofHC StdDev of HC Y _ Defer Layout Update Now we have a simple table of each monitoring station with an average and standard deviation of hard coral cover Get out our scratch paper for now and note the sample ID and standard deviation for the first row of data row 5 2 Open the R software Exercise 5 Managing and Using Data Guidebook 53 Page File Edit View Misc Packages Windows Help R Console version 2 9 1 2009 06 26 Copyright cj 2009 The E Foundation for Statistical Computing ISBN 3 200051 07 0 Rois free software and comes with ABSOLUTELY NO WARRANTY You are welcome to redistribute it under certain conditions Type i43csense or iicence tor distribution details Natural language support but running in an English locale RE is a collaborative project with many contributors Type contributors for more information and citationf on how to cite E or ER p
8. IL Tridacna crocea 60 Island 50 Legend Fields Series Values 40 4 _ Tridacna spp IL Clam Total Average of Sea Cucumber Total Crinoids StdDev of Sea Cucumber Total Lobster all spp Octopus _ Actinopyga mauritiana X Values E Average of Sea Cucumber Total E StdDev of Sea Cucumber Total Drag fields between areas below VW Report Filter c Legend Fields X Values nl iz Axis Fields Cat X Values Island M Average of Se Y Site v StdDev ofSea Y l all Ade La 4h M 3 10 3 10 3 10 3 110 3 usta slate lala 110 3 10 3 MNAARARA 110 3 10 3 10 3 0 A 10 3 103 10 C 10 C 11 C 12 C 13 C 14 C 15 C 16 C 17 C 18 C 19 C 20 C 21 C 22 C 23 C 27 C 28 C 3 C 4 C 5 C 6 C 7 C 8 C 9 Chuuk 4 M Metadata Chuuk REA Invert Summary Chuuk REA Invert Pivot Exercise 2 Managing and Using Data Guidebook 21 Page The first thing we notice is that our standard deviations are greatly reduced when examining data at the site level suggesting our survey goals of detecting change at the site level are better approached However they are still higher than desired for many sites We will touch back on that later 23 Remove the StdDev of Sea Cucumber Total box from Values a Click and drag it back up from where you initially grabbed it 24 Back on the PivotChart Fi
9. K MPA site Let s continue because we know the overall trends suggested MPA s were working on the whole 13 Go back to the PivotChart Filter Pane a Go to the drop down menu for SamplelD b Check only the boxes for LIT LI2 LOT and LOZ Now we can easily see the perceived success of this MPA compared with others You can do the same examinations for MPA s M and N 6000 NO1 NO2 NIL Inner H Site information PNP fish nivot chart PNP fish nivat PNP Fishli Exercise 3 You can confirm below for MPA N E Siganus vulpinus Siganus puellus A Siganus doliatus B Parupeneus barberinus E Naso unicornis E Naso lituratus E Monotaxis granoculis A Lutjanus gibbus E Lutjanus fulvus E Hipposcarus longiceps E Chlorurus microrhinos B Caranx melampygus E Acanthurus xanthopterus B Acanthurus lineatus NI2 EE Managing and Using Data Guidebook PivotChart Filter Pane X PivotTable Field List 7 EE Active Fields on the PivotChart E Choose fields to add to report B 7 w Report Filter I JAL dE 3 E F Sample ID E f i i i KI Drag fields between areas below af Report Filter E Legend Fields ILE LIE ss Defer Layout Update Update We have learned a great deal from our investigations thus far First for inner reefs MPA s D and K do not seem success ful as compared with all other three Second b
10. Length Da b Y Biomass g 0 10 cm C 10 20 cm 20 30 cm 30 40 cm 7 40 50 cm C gt 50 cm lt lt Drag fields between areas below W Report Filter 2 Legend Fields Species JI iz Axis Fields Cat X Values Reef type Y MA 0 0 7J Sample ID z Average of Bio C Defer Layout Update 33 Page These results contradict our earlier finding of success for MPA s in general For this MPA we see there appear to be more fish outside the MPA compared with inside Check to agree that these trends are especially pronounced for Chlorurus microrhinos and Naso unicornis at the DO1 site outside the MPA Note You can hover over any part of the data bar and Excel should automatically tell you what fish species each color represents Confirm below if it is not clear 2500 2000 1500 Series Naso unicornis Point Inner No DO1 Value 869 6052304 1000 No gt M Site information PNP fish pivot chart PNP fish pivo Let s look at the next MPA 12 Go back to the PivotChart Filter Pane and a Go to the drop down menu for SamplelD b Check only the boxes for KIT KI2 KOT and KO2 This means that we are going to look at data from the MPA with nickname K now Exercise 3 Managing and Using Data Guidebook 34 Page The results again clearly show no substantial benefits of the
11. Mone 27 For data format select X Many Y Create Graph Data Format Data format How is pour data organized Manu r RNE Man Y w 75000 94000 72000 One column and 2 66000 36000 6 6000 at least one Y 3 11 7000 5 2000 column 1 4000 8 7000 12 0000 13 0000 9000 13 n0n a Click next Now Sigma Plot is asking for the data 28 n the Data for X choose column 11 Year a Bar 1 choose 2000 Exercise 6 1 Managing and Using Data Guidebook 70 Page Create Graph Select Data ES Datel Select the column Selected columns to plat by clicking x 11 Year the calumn in the worksheet Bar 1 1 2000 0000 b Repeat until you reach Bar 10 and have highlighted 2009 c Click Finish The initial look of the graph that is created is relatively unimpressive but this is easy to change 29 In the Zoom box on top a Change the value 50 to 100 30 Click on 2D Graph 2 a Change the title to A planci abundances in CNMP 31 Click on Y Data a Change this to Average COTS observed per 100m 32 Delete X Data and the legend below showing Plot T 33 Double click on the vertical axis numbers Exercise 6 1 Managing and Using Data Guidebook 71 Page Graph Properties Apply to Average COTS observed per 100m2 MEM Settings for Scale type Range Stark Calculation 0 kw g End Calculation Data Range x
12. 151 8299 8 55305 0 0 30 0 0 19 Kuop Inner barrier Low 3 C 26 1 151 8756 7 1075 0 0 29 0 0 20 Kuop Inner barrier Low 36 26 3 151 8756 7 1075 0 0 29 0 0 21 Kuop Channel Low 10 C 25 4 151 9883167 7 00615 0 0 28 22 0 22 Chuuk Channel Moderate a c2 5 151 7931 7 227483333 0 0 ag 0 0 23 Etal Outer atoll barrier Moderate 3 M 13 2 153 5636667 5 571383333 0 0 25 1 0 24 Etal Outer atoll barrier Moderate 10 M 14 5 153 5894167 5 574016667 0 0 25 0 0 25 Chuuk Channel Moderate 10 C 23 1 151 7931 7 227483333 0 0 24 5 0 26 Satawan Inner atoll Low 3 M 9 1 153 5405833 5 404633333 0 0 24 0 0 27 Chuuk Channel Moderate 10 C 23 4 151 7931 7 227483333 0 0 23 1 0 28 Kuop Inner barrier Low 3 C 26 4 151 8756 7 1075 0 0 23 0 0 29 Chuuk Channel Moderate 10 C 23 2 151 7931 7 227483333 0 0 21 5 0 30 Chuuk Channel Moderate 3 C 23 4 151 7931 7 227483333 0 0 20 0 0 31 Kuop Channel Low 10 C 25 3 151 9883167 7 00615 0 0 20 25 0 32 Murilo Inner atoll Low 3H 7 4 151 74865 8 503183333 0 0 20 0 0 33 Etal Outer atoll barrier Moderate 3 M 13 4 153 5636667 5 571383333 0 0 19 1 0 34 Satawan Inner atoll Low 3 M 9 5 153 5405833 5 404633333 5 0 19 0 0 35 Chuuk Channel Moderate 3 25 3 151 7931 7 227483333 0 0 18 0 0 36 Chuuk Channel Moderate 10 C 23 5 151 7931 7 227483333 0 0 18 E 0 37 Kuop Inner barrier Low 10 C 26 4 151 8756 7 1075 0 0 17 0 0 38 Murilo Inner atoll Low 3H 7 5 151 74865 8 503183333 0 0 17 0 0 M 4 gt M Brainstorm metadata fields Metadata Database build Sheet2 Sheet4 2
13. 939 1 8939 of 0 0 25047 0 0 13x57 15 08 0 0 0 0 90 0 90 39 1 1537 0 0 0 0 090 e 0 159592 dtdX 90 0 80 X 0 y A 9Q G3 9 IC 9 9 M 9 E Wae 9 3 d 94 9 9 9 A 0000 08 000 A A 00 A 00 0 393 AA M t m 177 41 35 85 z amples ig w Row 1 Cal 1 We will now set up our workspace for analyses 28 Add our factors for analyses basically our site information from Excel 29 Minimize PRIMER and re open our Excel file Exercise 7 Managing and Using Data Guidebook 99 Page 30 Select the Primer Prepare Yap Fish datasheet a Highlight cells A2 A76 b Right click and select Copy 31 Minimize Excel maximize PRIMER 32 In PRIMER scroll down and select Factors from the Edit a Select Add Name this factor Site b Right click in the first cell under site and select paste c Confirm with screen shot below Site I o cL T z i Combine Rename Reorder Delete Import o A 33 Repeat previous step for MPA Status Year Reef Type and Depth a confirm with the screen shot below MPA Status Year Reet Type Depth Reference Channel am c o Combine Reference Channel 3m u Reference Channel 3m Reference Channel 3m Reorder Reference Channel 3m Reference channel fi Om Delete Reference Channel 1
14. Data Labels amp Symbols 22 On the Symbols side a Change the factor dropdown menu to Reef type 23 Click OK 24 Confirm Hii Log X 1 Resemblance S17 Bray Curtis similarity 2D Stress 0 28 Reef type A Inner a w Outer A A A A P A A Exercise 8 Managing and Using Data Guidebook 129 Page In this MDS plot we can start to see where some of the major ecological variation exists While not extremely clear we can start to see separation between the two different reef tyoes regardless of MPA status This tells us that in order to compare MPA and reference sites it would be a very good idea to first account for reef type which we will do next Before moving on you can change the data symbols to other factors if you like One last note here there is one green triangle in the far right hand of the above plot These seems to be a strong outlier meaning it is very unlike any of the other transects Typically when this occurs there may have been an error in the data collection or entry or this may just be a very unique situation Either way we should remove this outlier point from further analyses as it may bias the outcome To find out the name of the outlier sample 25 Go back to the Graph menu a Select Data Labels amp Symbols 26 On the left click on box for Plof the labels 27 Click OK 28 Confirm Transform Log X 1 Resemblance S17 Bray Curtis similarity 2D Stress 0 28 Re
15. Data iri data example b Click next P 96 For Data for Mean Group Name N Missing Mean Stid Dev l maie or Aver f Grazin Row 8D D 4 250 6 146 a Se Set SEAN 20 erage of Grazing Row 2 159 D 7 016 7 736 Urchin which corresponds to average pue 181 1 g 327 37 258 abundances within our Non impact sites 97 F Size risa jare Source of Variation DF S5 MS F P For our Size remember this is sample size Between Groups 2 1526 563 763 221 2 092 0125 a Select Count data located in column 14 Residual 417 152134918 384 832 98 For Standard Error Total 49 153661 481 a Select column 16 The differences in the mean values among the treatment groups are not great enough to exclude the 99 Click Finish possibility that the difference is due to random sampling variability there is not a statistically significant difference P 0 125 100 Confirm Ere Power of performed test with alpha 0 050 0 231 The power of the performed test 0 231 is below the desired power of 0 800 Less than desired power indicates you are less likely to detect a difference when one actually exists Negative results should be interpreted cautiously Exercise 6 2 Managing and Using Data Guidebook 89 Page The resultant summary sheet informs us that no significant variation was detected We can look under the Source of Variation section and see our P value is much greater than 0 05 typically required for significance Thus there is no
16. Go back to our Database build sheet and highlight the first cell you want to populate with the lookup function for GPS Y or latitude This is cell H2 a Once highlighted enter the appropriate formula lookup E2 Metadata A 2 A 57 Metadata B 2 B 57 Note we do not want and near the E2 because that is dynamic and we want to drag our formula to autofill the cells below However to appear for all lookup list values on the Metadata sheet These will never change b Your database should now look like below C a DI E Chuuk REA invert database Microsoft Excel Home I Page Layout Formulas Data Rewie View Add Ins Acrobat irm M am mun Verdana 7110 p Wrap Text General x El a 43 Copy rio 33 Paste l B Z U A E lt l E El a Merge amp Center 8 292 Conditional Format f Format Painter Os Es Jl js S EH Mero E Formatting as Table Clipboard EF Font E Alignment F Number F G5 E A B C E F G H 1 Island Reef type Wave exposure Depth Site Transect GPSx GPS y 2 Chuuk Channel Low 3m t 1 1 151 8706333 7 42986666 3 Chuuk Inner Moderate 3m c 11 1 _ 4 Chuuk Inner barrier Low 3m C 13 2 5 Etal Outer barrier Sheltered 3m C 14 2l l 6 Losap Outer barrier Moderate 3m C 13 3 7 Losap Channel High 10m c 14 3 8 Murilo Inner barrier Low 10m C 16 4 9 Etal Channel Moderate 10m M 4 4 10 Chuuk Inner Moderate 10m M 9 5 20 Fill in your
17. I Ready Exercise 2 Managing and Using Data Guidebook 11 Page You can get a general understanding this way for instance that the atolls hold more large clams grouped as Tridacna spp as compared with Chuuk And in particular Kuop seems to appear many times at the top of the list 3 Now do the same sorting for the common sea cucumber Holothuria atra Which island consistently holds the greatest abundance of this common sea cucumber Lets return the database back to its original form 4 Click on the Depth a Sort smallest to largest 5 Click on site b Sort A to Z 6 Click on Island c Sort Ato Z You can notice this is exactly how the database looked when we first opened it Now we will add some additive summary columns that will help us to better visualize our results Notice that columns J and K all refer to clams Lets add a column to help summarize the abundance of all clams together 7 Click on column L Crinoids right after the last column with clam names 8 Right click the mouse and select insert Notice a new column appears called Column 1 2 Ichuuk um 4 iC huuk huuk Chuuk Chuuk Chuuk huuk 9 Chuuk O Chuuk C C c BIG Ti C C C I3 c t C a Change the name to Clam Total Chuuk Chuuk huuk huuk Inner Inner Inner Inner Inner Inner Inner Inner Inner Inner
18. P Q R 1 Sum of Bidmass g Species 2 Sample ID Replicate MPA Reef type Acanthuru Acanthuru Caranx me Cephaloph Chlorurus rHipposcart Lethrinus FLutjanus fi Lutjanus g Lutjanus m Monotaxis Naso litura Naso unice Parupene 3 DI1 1 Yes Inner 0 0 0 O 22 28788 0 o 118 4382 146 1008 0 83 85516 0 0 4 2 Yes Inner o 0 0 O 14 39864 O 56 50028 0 63 68222 D D D 0 418 673 5 3 Yes Inner o 0 0 0 106 9437 278 578 o 132 6184 64 12766 0 o D 0 6 4 Yes Inner 0 0 0 O 1636 499 0 0 100 4545 0 D D D 0 A 5 Yes Inner o 0 0 0 0 219 9683 0 o 0 0 0 0 0 8 63118 8 DI2 1 Yes Inner O 5 615499 0 0 38 07614 20 87648 0 o 0 0 0 20 53927 0 2 Yes Inner OD 34 68838 0 O 89 38813 0 0 o 0 0 o D 0 10 3 Yes Inner 0 0 0 O 49 99871 27 58814 0 o 0 0 20 53478 0 0 5 68841 11 4 Yes Inner O 8 895905 0 0 22 54191 0 0 o 0 0 0 D 0 12 5 Yes Inner O 17 79181 0 51 1681 0 0 0 o 0 0 0 0 O 34 6498 13 DO1 1 No Inner 0 0 0 0 3344 823 803 8616 0 o 0 O 196 3264 0 0 411 437 14 2 No Inner 0 0 0 0 801 6672 825 5047 0 o 0 0 0 4439 441 0 15 3 No Inner 0 0 0 0 0 928 3574 o o 0 0 0 56 27397 o 17 3249 16 4 No Inner o 0 0 0 0 250 0796 0 o 0 0 0 91 62068 0 17 5 No Inner o 0 0 0 0 3943 031 0 o D 261 5679 0 56 27397 869 6052 17 3249 18 DO2 1 No Inner o 0 0 0 410 4123 42 2093 0 o 0 o 97 11944 D o 47 9384 19 2 No Inner 0 0 0 0 11 52534 2 651511 41 63865 o 0 0 12 31149 0 0 20 3 No Inner 0 0 0 0 38 20208 21 42392 o o 0 O 36 47098 0 0 l 4 No Inner o 0 0 0 60 24856 71 45131 0 o 0 0 0 D 0 22 5 No Inner
19. Pad 5 Nearest tick a In the End box change the 714 to a f b Click OK take a moment to look at the quality and information presented in just a few easy steps Sigma Plot allows you to export these graphs in raster or vector formats to preserve high resolution images for your reports or grant applications Exercise 6 1 Managing and Using Data Guidebook 72 Page A planci abundances in CNMI C C cC T CI DO qo c qi LA DO e Lr E a a qi C Cu q 1 2002 004 2006 000 We can summarize that higher than average A planci abundances were evident in the CNMI between 2003 2006 We now wish to understand the ecological consequences of high starfish abundances in terms of our other datasets and eventually look at recovery Save your work Keep your files open as they are needed for exercise 6 2 End of Exercise 6 1 Exercise 6 1 Managing and Using Data Guidebook 73 Page Exercise 6 2 Conducting basic univariate statistical analyses and producing informative professional quality graphs to show your trends 1 Go back to our Excel file a Make the main database sheet active Notice column G which is named COTS Click on the drop down menu and notice there are three choices Before During and After These indicate that data were collected before i e from 2000 2003 during 2003 2006 and after 2006 2009 high COTS activity Also notice
20. SamplelD box and drag it below MPA in the Axis Fields box b Go back to the PivotChart Filter Pane and go to the drop down menu for SamplelD c Check only the boxes for DIT DI2 DOT and DOZ This means that we are going to look at data from the MPA with nickname D and the sites surveyed I inside and O outside the MPA The 1 and 2 refer to site replicates within which 5 x 50m transects were surveyed Indeed a nice survey design methods and dataset Confirm below 2500 2000 1500 1000 DO1 M Site information PNP fish nivot chart lt PNP fish nivat PNP Fish Exercise 3 Inner DI1 Siganus vulpinus Siganus puellus Siganus doliatus E Parupeneus barberinus B Naso unicornis Bi Naso lituratus m Monotaxis granoculis E Lutjanus monostigma E Lutjanus gibbus B Lutjanus fulvus B Lethrinus harak E Hipposcarus longiceps B Chlorurus microrhinos m Cephalopholis argus E Acanthurus xanthopterus PivotChart Filter Pane x Active Fields on the PivotChart E Y Report Filter i Axis Fields Categories Reef type MPA Sample ID KE Legend Fields Series Species G Values Average of Biomass g Managing and Using Data Guidebook PivotTable Field List Choose fields to add to report Es M v Sample ID v MPA v Reef type Replicate v Species
21. ea Barcinas Bay 7 25 2000 C 11 SBarcinus Bay 1 7 26 2000 c ca c 14 Bird Island 7 24 2000 MN 20 Coral Gardens 7 25 2000 1 4 ta x c ca cx nm G 23 Coral Ocean Point 311 242000 57 28 2000 31 24 2000 i 38 Obyan 9 23 2000 HAH CHMT invert nivnt Sheet Metadata J P x Fs yy alm CH 1 1 gt af o a jeu jc oci cujas E Exercise 6 1 Managing and Using Data Guidebook 65 Page We have to transfer our data on a year by year basi conducted It s easy to do 12 Right click on Column D a Select Copy 13 Open Sigma Plot a Start a new notebook 14 Right click on Column T a Choose pasie 15 Do this for all years then confirm PA SigmaPlot Data 1 Picasa LAE Laid a Ao AD oe S A ollie 10 Paired t test E E m E n f s because Excel has put in 0 for all empty boxes even if no surveys were Eek ERASE AA A A iss 1 II o2 0 023 0 00 5 0 7 no Es All Open Notebooks E E pl Year Year Year Year Year Year Year Year Year 2000 0000 2001 0000 2002 0000 2003 0000 2004 0000 2005 0000 2006 0000 2007 0000 2008 0000 MA 0 0000 0 0000 0 0000 0 0000 0 0000 2 0000 0 0000 0 0000 1 5000 S D 0 0000 0 0000 0 0000 0 0000 0 0000 4 0000 0 5000 0 0000 0 5000 0 0000 0 0000 0 0000 0 0000 0 0000 2 0000 0 0000 0 500
22. 0 0 0 780 4099822 162 8830567 99 NO2 4 No Inner 94 75435913 0 0 0 88 33956 93 85 38311227 100 NO 5 Mo Inner 265 9405 7737 0 0 0 164 8273885 116 0184349 101 102 103 104 E IM 4 k M PNP fish pivot chart PNP fish pivot PNP Fish Database PNP Fish Data by Transect Sheet We are now ready to begin examining our statistical confidence 26 Click anywhere in the table then press Ctrl A to highlight all the data 27 Insert a new Pivot Table and name it Pivot PNP Fish by Transect Exercise 4 Managing and Using Data Guidebook amp E LE 20096 45 Page a Click and drag the Sample ID box and put it under Row Labels We will first look at one influential fish we found from earlier b Click and drag the Hipposcarus longiceps box and put it under values c Click and drag the exact same box and put it under values d Confirm the look of your Values box below Drag fields between areas below Y Report Filter E Nm Row Labels Values Column Labels Defer Layout Update 28 Left click on the top Sum of Hipposcarus box a Change the attributes field to Average 29 Left click on the bottom Sum of Hipposcarus box a Change the attributes field to StdDev 30 Click anywhere inside the main table Exercise 4 Managing and Using Data Guidebook 46 Page 31 Insert a basic column chart the one on the top left of the selection menu a Rig
23. 0000 0 0000 0 0000 0 0000 0 0000 2 0000 0 0000 0 0000 0 0000 0 0000 1 0000 0 0000 0 0000 0 0000 1 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 1 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 1 0000 0 0000 1 0000 0 0000 0 0000 0 0000 0 5000 0 0000 1 5000 1 0000 0 0000 0 0000 0 0000 1 0000 0 0000 0 0000 0 0000 5 0000 2 0000 0 0000 0 0000 0 0000 1 0000 0 0000 0 0000 4 0000 4 5000 2 0000 0 0000 0 0000 0 5000 0 5000 0 0000 0 0000 4 5000 1 0000 1 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 9 5000 2 5000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 2 0000 0 0000 0 0000 0 0000 0 0000 0 0000 2 B m S B 1111 E E Exercise 6 1 Managing and Using Data Guidebook OW REC RUN AUTO CAP MM SCRL d 66 Page Now we can clean this up a bit before starting our graph and statistical analyses 16 Right click on Row 1 a Choose Delete Rows b Delete rows 1 3 so choose to delete 3 rows starting at row 1 Delete Rows Notebook3 Dat E Delete Finally let s promote our years to official column titles 17 Right click on column 1 a Choose Column Titles Column and Row Titles Column Row calumn mew Tile Promote row to tithes Delete promoted row b Click on the promote button to move the text heading of the first column up Notice on your datasheet that 2000 has been promoted to a column title 18 Click on Next a Prom
24. 101 7850591 904 048 22 0 0 0 0 0 O 1551 853 23 0 0 100 0216424 0 0 0 0 0 388 1105 36 Go back to the Pivot Graph PNP fish transect worksheet with our graph 37 Click anywhere in the chart to activate the Pivot Chart functions Exercise 4 Managing and Using Data Guidebook 48 Page 38 Click on the Analyze tab in Excel s main menu then click the Refresh button Notice in your PivotTable Field List that Total Fish Biomass has been added 39 Remove all active boxes from the Values field by unchecking the green marks next to any active fish name you were previously investigating 40 Click and drag the MPA box and place it under SamplelD 41 Click and drag the Total Fish Biomass box and place it under Values a Do this twice so you have two boxes b Change the attributes of each to Average and StdDev i w i i i S 14000 PivotChart Filter Pane x PivotTable Field List x Active Fields on the PivotChart n Choose fields to add to report Report Filter Sample ID NN i Axis Fields Categories Acanthurus lineatus 10000 pa Legend Fields Series Acanthurus xanthopterus Caranx melampygus Cephalopholis argus E Values Chlorurus microrhinos Hipposcarus longiceps 8000 Lethrinus harak Lutjanus fulvus Lutjanus gibbus i Lutjanus maonostigma E StdDevof Total Biomass2 Monotaxis granoculis Average of Total Biomass StdDev of Tot
25. 14531504 19 85271227 14 51182063 64 12765942 8 922740354 41 92758147 11 52533634 18 74352648 9 506613651 31 84111155 108 581928 61 44898613 39 30595609 6 292609376 2 201222347 106 9436501 69 32168018 20 78537612 0 10 cm um ES b Sort amp Find amp Filter Select gt OTI y Pie Now we are ready to begin our query and investigation Logically we ll start by asking the most general questions and get more specific as we learn 6 Highlight all of the data and again insert a Pivot Table like before a Change the name of the sheet to PNP fish pivot Exercise 3 Managing and Using Data Guidebook 28 Page b Click ok in the dialog box We will first take a look at all MPA s grouped together not yet taking into account statistical sampling concerns like standard deviations and confidence intervals surrounding our data 7 Click and drag the MPA box and put in under Row Labels a Put Species under Column labels b Put Biomass under Values c Left click the Count of Biomass box once and change the field settings to average d Confirm below Exercise 3 Managing and Using Data Guidebook 29 Page Pohnpei MPA fish transects exercise Microsoft Excel PivotTable Tools o x te iN AJ x y co Mom 4 08 p Wrap Text General y Normal 8 Normal 9 m ER Ci indt AY d Fill 3 Copy ste j ae E Me
26. 21 1 0 6 a x 3 155 5966648 al a 0 g 5 709 220469 10 510 1 0 Ex 2 0 12 3 0 13 4 0 14 5 0 Outer LE 1 0 2 637 0111691 l 3 0 4 3858 56 7874 19 5 4526 739952 20 110 1 0 21 2 246 430697 22 3 0 23 4 0 24 5 0 35 32009 Channel 83 1 0 26 2 0 27 3 0 28 4 0 29 5 0 30 110 1 0 31 2 0 Le 3 o Mal Site metadata Fish lonkun tahe Yan fish nivnt Nimna Gachuun fishdata Sheeta 9 3 A mi We will export this to a new sheet now 12 Click on any cell in the table a Press Ctrl A b Right click and select Copy 13 Select Sheet3 a Right click in cell A1 b Select Paste Special and select Values c Click OK Exercise 7 Managing and Using Data Guidebook Transect gt Acanthurus lineatus Acanthurus nigricauda Acanthurus ti 0 449 9912911 132 6289882 20100 201000050002 432 7804 749 0 0 259 0250307 0 158 9544664 0 1024 215323 j FivotTable Field List xi Choose fields to add to report Site Year Code Drag fields between E below Report Filter 4 Column Labels Nun o zd Row Ed Row Labels Values At Defer Layout Update 94 Page 14 Rename the sheet Primer Prepare Yap Fish 15 Confirm ay ki 9 Ox gt Yap Nimpal MPA Fish PHworking Microsoft Excel PivotTable Tools ca Home Insert Page Layout Formulas Data Review View Add Ins Acrobat ons Design i i aa i FH ac DC LL PivotTa
27. 22 872 34 853 2307 23 855 0 23 46 27 709 0 0 0 29 772 ES 14 348 34 252 32246 16 337 38 14 53 96 38 865 58 2 49 416 58 474 45 747 44 993 30358 21181 23 03 55482 18 473 23 177 52 711 33 799 52 018 54 982 51 565 53 226 39 695 22 839 58 181 45 075 55 817 55484 38 553 51 436 23228 18 459 43 923 43 261 1971 59 465 87 798 43 339 59 066 25 768 29 193 35 658 12 922 14 669 8 5052 39 629 29 42 521 16 407 48 861 62 994 0 39 458 59 408 26 42 63 602 29 38 34 186 23 255 10 152 11 337 6 9738 25 579 38 421 32 396 37 734 48083 o 56243 46 333 46 522 42 869 13 562 24772 35 251 16825 18 693 11719 38 643 16 089 13 129 45 205 35 872 31 398 53 843 30 181 38 253 42 146 14 747 53 109 69 946 44 944 51 672 33 111 44 04 215 13 185 25 999 0 34153 31 485 0 52 086 0 o 37 949 40 758 28 265 29 4 21 121 54 745 EJE 135 11 125 19 243 35 847 0 39 219 38 345 17 248 28 698 31 26 25 502 47 619 17 57 21 105 32 135 23 178 13 95 11 476 19 856 33 932 0 40 607 45 074 17 836 47 78 46 345 35 299 44 852 18162 21 871 13 872 23 869 538 40 477 47 959 27 603 25813 8 6355 39 073 30 559 42 431 20 373 22 507 46 385 49 485 22 585 15 498 18 455 56 042 36 37 862 12 834 22 235 42 602 0 35 985 29 245 35 866 20 094 0 44 585 50 078 20 48 24 904 15 185 46 727 S37 35 428 12054 20868 28 296 0 33516 26 842 335 37 57 13325 49 506 384 19 145 23149 14438 41086 38 39 715 13 423 23 268 38 531 0 37 877 31 135 37 67 21 364 0 42 163 51 197 21 497 26 254 15 737 48 734 v
28. 3 9078 n D 9 5455 325 n n D I D 52475 D 7 538 6 4533 D D D D 0 556 n n 6 8681 53848 6 1868 n n 3 324 n n n 0 5 7802 S57 D 0 D 5 5362 D 0 D D 53372 D D 81722 D D S58 D D D D 38545 n D D 48774 ol D d o D 559 D D 0 2 9887 D D 3 0149 D D D 0 D SED D 4 5022 ol D 5 1591 n ol D 4 9651 ol ol n 6 5824 D 54 Go to the Analyze menu and select SIMPEHR which is short for analyses of similarities SIMPER Design Measure One way 2 Brap Curtis similarity Two way crossed Euclidean distance Factor Cut off percentage List only higher contributing varables 55 Under Factor A a Select Site So we can determine differences can leave the default settings that match our MDS plot generation 56 Click OK Exercise 7 Managing and Using Data Guidebook 111 Page 57 Confirm Note Scroll down the text output sheet so we can see the comparison between the two sites The relevant section was manually highlighted in blue for identification T a Y EE t Mart Groups Gachuug Nimpal Lverage dissimilarity a Group Gachuug Group Nimpal Sie iw ibund Lw Abund Contribs Cum 15 SE Hn J Io Chlorurus sordidus SCarus Sp Cephalopholus argus Evphaoasus Cheilinus undulatus Caranx melampygus Lcanthurus nigricauda in 6 00 50 als E Lj Ie NEN In oe c IO pb l tl Mee tu D 3 m i I D D Co n LI e my rn c E n co m G i H 1 a
29. 842 3138 0 0 O 1870 162 2213 739 0 0 0 0 0 0 0 0 0 0 0 0 18 246 4307 0 0 0 O 1983 238 0 4038 769 3729 498 0 0 0 0 0 1128 66 0 0 0 0 0 0 19 0 0 0 0 0 0 0 4521 677 1887 781 0 0 0 0 O 1838 304 0 0 0 0 0 0 20 0 0 0 0 173 0042 0 0 9757 544 2202 601 0 0 0 0 0 690 9934 0 0 0 0 0 0 21 0 0 0 0 113 9958 0 0 2618 31 6682 163 0 281 1204 0 0 O 1249 616 0 0 0 2179 418 0 0 22 0 432 7805 0 0 0 0 0 1327 592 238 0775 O 14 65502 0 0 0 0 0 0 0 0 0 23 0 0 0 0 0 0 0 1466 499 490 5276 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 631 2274 0 390 8646 8046 58 1818 498 59 850685 0 0 0 246 795 13980 48 0 0 0 0 0 0 0 25 0 259 025 0 0 0 309 8052 0 997 6664 335 0197 0 48 79071 0 0 13980 48 0 0 0 0 0 0 0 26 0 0 0 0 0 183 4658 O 1877 006 633 7658 0 0 0 0 0 0 0 0 0 0 0 0 27 O 158 9545 0 0 0 0 0 100 445 64 75614 0 0 0 0 0 0 0 0 0 0 0 30 97017 28 0 0 0 0 0 0 0 294 7546 248 1385 0 19 44339 0 280 0559 0 40 93125 0 0 O 165 8322 0 0 29 O 1024 215 0 O 224 1723 65 32372 0 152 3426 26 77248 0 0 0 0 0 0 0 0 0 0 0 0 30 0 1394 48 0 0 0 O 1484 777 0 302 168 0 0 0 0 0 0 0 0 0 0 0 0 31 O 1218 561 D 0 0 0 0 954 0168 759 1879 0 0 0 0 0 405 4788 0 3389 799 0 0 0 0 32 2790 589 0 0 0 0 0 2698 196 1339 763 480 38 0 98 37189 0 0 0 0 0 0 0 0 0 2329 665 H k Mi Fish lonkun tahle Yan fish nivet NimnalGachuun fishdata Primer Prenare Yan Fish Primer import 47 Hl 20 Save AND Minimize the Excel file Exercise Managing and Using Data Guidebook 97 Page 21 Open the PRIMER E Program a Click on the ope
30. 9 Click on cell 44 on the pivot table a Right click and choose Field Settings b Under Subtotals click none c Click OK to close the dialog box Now we want to re add the MPA and Reef type information in our table 10 Click and drag these boxes and put them under Replicate 11 Click on cell B4 again choose Field Settings a Under Subtotals click none b Click OK to close the dialog box Exercise 4 Managing and Using Data Guidebook 39 Page 12 Click on cell C4 again choose Field Settings a Under Subtotals click none b Click OK to close the dialog box 13 Confirm below A B D 1 PivotTable Field List Choose fields to add to report 3 Sum of Biomass Species LYJ 4 Sample ID Y Replicate MPA_ lReef type 7 Acanthurus xanthopterus Cephalopholis argus Chlorurus microrhinos Hipposcarus longiceps Leth Nem Y 5 DIL 31 Yes Inner 0 0 22 28788213 Y MPA 6 32 SYes Inner 0 0 14 39864279 0 v Reef type Y 7 33 Yes Inner 0 0 106 9436501 278 5780129 v Replicate 8 34 Yes Inner 0 0 1636 499422 0 Y Species 9 35 SYes Inner 0 0 0 219 9682947 Length 10 SDI2 31 Yes Inner 5 61549908 0 38 07614397 20 87648147 Da 11 32 SYes Inner 34 68837875 0 89 38812819 0 Ob 12 33 Yes Inner 0 0 49 99871473 27 58813905 7 Biomass g 13 34 Yes Inner 8 895905473 0 22 54191114 0 0 10 cm 14 35 SYes Inner 17 791810
31. Biomass g 13 3 0 0 49 99871473 27 58813905 0 0 10 cm 14 4 8 895905473 0 22 54191114 0 0 C 10 20 cm 15 OOOO O 5 17 79181095 51 16810232 0 0 0 20 30 cm 16 DI2 Total 66 99159425 51 16810232 200 004898 48 46462052 0 30 40 cm 17 9DO1 1 0 0 3344 823399 803 8615862 0 40 50 cm 18 2 0 0 801 6671508 825 504714 0 1550 cm 19 3 0 0 0 928 3573877 0 20 4 0 0 0 250 0795945 0 21 5 0 0 0 3943 030976 0 22 DO1 Total 0 0 4146 490549 6750 834258 0 Drag fields between areas below 23 9DO2 1 0 0 410 4122884 42 20930013 0 Y Report Filter Cj Column Labels 24 2 0 0 11 52533634 2 651510778 41 63864716 Species 25 3 0 0 38 20208402 21 42392402 0 26 4 0 0 60 24855971 71 45131273 0 27 5 0 0 0 426 7695095 0 28 DO2 Total 0 0 520 3882684 564 5055572 41 63864716 29 aun 1 0 0 3285 179365 16379 22135 847 3206118 30 2 0 0 0 12003 95724 429 7563591 302 31 3 1103 600024 0 1613 431607 1768 023349 458 8361116 124 32 4 0 0 4839 145245 3199 695914 288 4757995 459 33 5 298 1126228 0 934 6812259 564 1745507 0 417 34 LI1 Total 1401 712646 0 10672 43744 33915 0724 2024 388882 2427 35 SLI2 1 0 0 0 307 8528241 0 36 2 0 0 0 69 32168018 0 37 3 0 0 0 751 4755651 0 38 4 0 0 1476 796345 0 6486 221418 144 gt r Site information Ready PNP fish pivot chart PNP fish pivot _PNP Fish Database Shed C Defer Layout Update Eg E LI Just a few more steps and then we will have re created a new database for our needs
32. DO O n mn un Hipposcarus longiceps i I d ocn Ctenochaetus striatus Grouper Plectorhinchus lineatus Chlorurus microrhinog Epinephelus merra Cc ER C O CO B ah 1 C Otium m em rn Co lo cmn Cc DC A a 1 m Di IDO a dau Rh op From this table three columns are most informative The first column has the average biomass from Gachuug the reference site for each fish species The second from Nimpal For now we can disregard the next two columns and focus upon the contribution We are most interested in what species contributed to the majority of the difference found in our MDS plot Notice the first four fish cumulatively accounted for gt 50 of the variance the last column tells us the cumulative variance accounted for So we should logically focus upon these four species The most notable difference are a shift in parrotfish from Chlorurus sordidus very common at the reference site to other Scarids including Hipposcarus longiceps Scarus tricolor S frenatus and others Also there has been an increase in the grouper Cephalopholus argus Now we have a good idea of where change occurred the magnitude of change and what change consisted of This is very powe rful to aid our understanding Let s continue to look at other reef types and depths 58 Go back to the first main data sheet under the Yap multivariate fish exercise 59 From the select menu select AIr 60 Go bac
33. FMKSA04115 3 9 26 2007 20 0 5 0 0 0 0 5 55 0 0 0 25 0 25 13 FMKSAD4115 4 9 28 2007 12 5 0 0 0 0 0 5 17 5 50 0 0 0 12 5 0 0 14 FMKSA04120 1 10 1 2008 15 0 0 0 0 0 0 25 62 5 0 0 5 T 5 0 0 15 FMKSA04120 2 10 1 2008 10 0 0 0 0 0 0 5 67 5 0 0 0 f5 0 0 16 FMKSA04120 3 10 1 2008 15 0 0 0 0 0 0 25 a0 0 0 0 2 5 0 0 17 FMKSAD4120 4 10 1 2008 1 li A 725 i a n 4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 n n You can see a very straightforward datasets with Sample ID Replicate and Date to define each sampling event The remaining codes indicate benthic categories that Kosrae s monitoring program used to collect data These benthic data were collected using four 20m long transect lines and noting the benthic life form at each 0 5m mark on the line Thus there is a total of four replicate transects with 40 benthic data points collected along each For our purposes we will focus on Column L or HC which refers to hard coral cover The numbers below are percent coverages There are four key elements for calculating and understand statistical power 1 standard deviations associated with your measurements 2 required statistical power or confidence 3 number of replicate samples you have used 4 desired absolute value of change you want to be able to detect If you know any of the above 3 values the simple analyses through R will provide you with the calculation of the fourth
34. From the PRIMER main menu a Click on edit and scroll down to factors Note Get some scratch paper and a pencil ready 27 Maximize the Factors dialog box to the entire screen We will first look at the differences in fish assemblages between the Nimpal conservation area and the Gachuug reference site for the Channel reef type and only at 3m We want to record all Labels or sample ID s that pertain to our analyses so we can select them from the main screen Important Confirm on your own that for the analyses defined above we wish to examine sites or Labels S1 S5 S21 S25 S36 S40 and S56 S60 28 Close the Factors window 29 On your main data sheet highlight the rows that pertain to our desired analysis by left clicking on each Exercise 7 Managing and Using Data Guidebook 101 Page 30 Confirm below PRIMER 6 Yap Nimpal MPA Fish PHworking EJ bx m File Edit Select View Analyse PERMANOYA4 Tools Window Help E X Dg ASR Eel POH cs Y ap multivariate fish exercise Hei v Biomass Variables 5 Acanthurus li Acanthurus tr Cephalopholug Cheilinus und Epinephelus mEpinephelus Hippo carus WK yphosus Lutjanus aibbuLutjan a S2 92847 406334625 Gai TA 34i s o O dEB8 wO 1028 NL MEE AO arts 208 2818368822 Fi Oo pu 1870 2 2213 7 0 oo 10044 54758 0 29475 24814 22417 55 324 152 34 2B 72 NEN oo NEN
35. GPS data for all other sites 21 Highlight the G2 and H2 cells together and copy the contents press the Ctrl C Exercise 1 Managing and Using Data Guidebook 7 Page 22 Scroll down to cell G3 and paste the formula Ctrl V a Fill all the way down to G10 Your database should look like below OO sd hi Re Ga E LO G10 A Island Chuuk Chuuk Chuuk Etal Losap Losap Murilo Etal _ 10 Chuuk Now we have everything in order we are ready to formalize our database into an Excel list function p 151 8706333 151 788 151 5917333 151 58505 151 5917333 151 58505 151 4476667 153 7878607 fe LOOKUP E10 Metadata SA52 5A557 Metadatal C 2 5C557 B c D E F mn Reef type Wave exposure Depth Site Transect GPSx Channel Low 3m C 1 1 Inner Moderate 3m C 11 i Inner barrier Low 3m C 13 2 Outer barrier Sheltered 3m C 14 2 Outer barrier Moderate m C 13 3 Channel High 10m C 14 3 Inner barrier Low i m C 16 4 Channel Moderate 10m M 4 4 Inner Moderate 10m M 9 5 23 Highlight all cells where data exists A1 to H10 a Click on the Insert main tab for Excel on the sub menu click on table You should have a dialog box appear as below 11 12 13 Exercise 1 Island Chuuk Chuuk Chuuk Etal Losap Losap Murilo Etal Chuuk Reef type Wave exposure Depth Site Channel Low 3m C i 1 Inner Moderate 3m C 11 1 Inner barrier Low 3m C 13 2 Outer
36. Go to the Analyze menu Notice the options have changed items that were previously available are no longer This is because we are working with an active resemblance matrix as opposed to a species by site dataset a Select MDS Number of restarts A stress an Kruskal fit scheme _ Shepard diagrams OR C2 Configuration plot b Keep the default settings for our options c Click OK Exercise 7 Managing and Using Data Guidebook 107 Page After a bit of processing time PRIMER produces a 2 dimensional and 3 dimensional plot called Graph1 and Graph2 Let s just focus on the first 2 dimensional plot We will change the look of this plot to better understand the findings 44 Under the Graph menu a Select Data labels amp symbols Graph Options General Data labels amp symbols Titles Bubble Contour Labels Symbols SIZE 4 Plot Plot 10 _ By factor By Factor Site vi Default Symbol Colour 45 For Labels a Check the By factor box b From the drop down menu select Year 46 For Symbols a Check the By factor box b From the drop down menu select Site 47 Click ok Exercise 7 Managing and Using Data Guidebook 108 Page 48 Confirm You should have changed the look of your graph Note Your graph
37. IDIL 3 Yes In 5 DI1 4 Yes In 6 DIL i 5 Yes In DI2 1 Yes In 2 J wma In 24 Do the same for all other sites 25 Highlight cells A7 A1 1 a Go to the Fill drop down menu and select down b Keep on doing this until you fill in all blank boxes on the database Tip Another trick you can use from the keyboard is to click on the first cell with the site name press Ctrl C then move down to the blank cell and press Ctrl V This cut and paste works as well When finished confirm you completed data table below Exercise 4 Managing and Using Data Guidebook 44 Page m i ar H T je MIP A fish transects exercise Microsoft Exce m x i C Pohnpei MPA fish transects exercise Microsoft Excel Table Tools me I iip es Formulas Review Wieuy Add Ins mM Design 6 mox e fi Cut Fs c E HH NI m mm E AutoSum 7 Ay de Verdana lio A A gt 47 Wrap Text General PA Normal 8 Normal 9 E X j 2 ES Paste IB U 2 4 A EB m BS ee E Merge amp Cente a t 08 Conditional Format Bad Insert Delete Format gt Sort amp Find amp Wf Format Painter 2 Formatting as Table E v T T c Clear 7 Filter Select Clipboard E Font E Alignment E Number E Cells Editing Samp Replicate MPA Reef type Acanthurus lineatus Acanthurus xanthopterus Caranx melampygus Cephalopholis argus Chlorurus microrhinos Hipposcarus longiceps Lethrinus h iE 67 MI2 1 Ye
38. There is a lot of variation among individual sites even if they are in the same Location Reef Type and MPA status We might not desire to combine data from sites to judge a higher order variable such as MPA status However the experimental design was set up to do this recall Site was selected to be a random factor So we will proceed with our experimental design keeping our knowledge gained in mind 63 Go back to our Bray Curtis similarity sheet We are ready to do a pairwise comparison to learn about the success of MPA status for each location and reef type separately 64 From the PERMANOVA menu 65 Select PERMANOVA Exercise 8 Managing and Using Data Guidebook 141 Page a Change our Tes to Pair wise 66 From the drop down menu below select MPA Reeftype Location 67 Click OK 68 Confirm the second PERMANOVA results sheet below PAIR WISE TESTS Term MF e abge gs Within lewel D of factor Location Within lewel Inner of factor Reef type Unique Groups t Fi perm perms Mes Mo ip SLE ss 3 Denomingtors Groups Denominator Den dt Yes No 1 S51i MP Re Lo Average Similarity between within groups Yea No Yes 5 571 No 7 0459 45 556 Within level E of factor Location Within level Inner of factor Reef type Unique Groups t Pi perm perms Yes No 0 73242 1 3 Here we can see the results of the pair wise comparisons The box above highlights
39. Transform En log transformed a A Hesemblancel Reser Sf mos A 29 Graph AES Graph AE Design 40 Double click in the first cell below Factor you will notice a drop down menu appears a Select Location 41 n the cell below Location a Select Reef type 42 Continue down the column selecting MPA and Site in that order Exercise 8 Managing and Using Data Guidebook 133 Page 43 Double click on the cell next to Reef type under the column Nested in a Nest Reef type in location as we discussed above Selection Select factors nested in Available Include Reef type MPA Site Transect H Combined name 44 Click OK 45 Nest MPA within Reef type as there are sites inside and outside of MPA s for each reef type 46 Finally nest Site within MPA as there are two sites with 5 transects each inside each MPA zone 47 In the next column Fixed random we need to a Make sure the first box is set to Random b The second and third to Fixed c The fourth to Random Exercise 8 Managing and Using Data Guidebook 134 Page Our sampling design dictates whether or not a variable is fixed or random For instance Location could be any village in Pohnpei that decides to establish an MPA so is set to random However Reef type and MPA status are well defined categories that do not change and
40. are not random by nature Finally Site or the exact placement of the sites in each MPA and reference site is also random 48 Confirm PRIMER 6 Design A File Edit View PERMANCOYA Tools Window Help a Wi AX E E 1 Re e v T al Fahnperfish HPA PERMAN VA esercise IPN p MPA E En FahnperMPA Fsh PERMAMLUVA example Eum Mested in Fixed irandam Contraste a A Overall Transform Random HB Jn log transhormed pum Fixer B m Hesemblance B da Resembll dll iis Most i Re 29 Braphl AS Graph Be D Design We are now ready to run our PERMANOVA on the dataset However just like a univariate ANOVA test we need to examine whether or not our variances are homogeneous This will determine if we can continue with our PERMANOVA or we need to utilize a non parametric test i e a rank sum test procedure like an ANOSIM Basically can we use our actual numerical data or do we need to use a derivative of the data such as rank sums PRIMER has a function built in to understand the dispersion of the multivariate data Dispersion can be thought of as statistical variance or how different each replicate measure is to the next For our example we wish to know if the replicate transect conducted at each site all have similar levels of dispersion If they do we can move forward with our PERMANOVA else we d probably choose to move on with the ANOSIM procedure discussed above in Exercise 7 Remember we removed one outlier point
41. differences for different database formats take a moment to reflect Programs like Excel make it relatively easy to switch formats in a short time frame 1 First go back to our PNP fish pivot worksheet 2 Under Row Labels click and drag the boxes for MPA and Reef type out 3 Click and drag Replicate and put it under SamplelD a Under values left click once on Average of Biomass and change the attribute field to Sum b Confirm below Exercise 4 Managing and Using Data Guidebook 37 Page PivotTable Field List X 1 2 Choose fields to add to report 3 Sum of Biomass g Column Labels Ly 4 Row Labels Y Acanthurus xanthopterus Cephalopholis argus Chlorurus microrhinos Hipposcarus longiceps Lethrinus harak Lutjanus fulvus Lutj ro Y 5 sDIH 178 0129597 62 31828846 14 12507006 35 15110911 3 OMPA 6 1 22 28788213 118 4382485 Reef type Y 7 2 14 39864279 56 50028025 Replicate 8 3 106 9436501 278 5780129 132 6183517 v Species ES 4 1 1636 499422 100 4544909 Length 10 5 219 9682947 Da 11 3DI2 11 16526571 51 16810232 9 524042763 9 692924103 Ob 12 1 5 61549908 38 07614397 20 87648147 Y Biomass g 13 2 34 68837875 89 38812819 0 10 cm 14 3 49 99871473 27 58813905 C 10 20 cm 15 4 8 895905473 22 54191114 20 30 cm 16 5 17 79181095 51 16810232 130 40 cm 17 3DO1 376 9536863 204 5707351 40 50 cm 18 1 3344 823399 803 8615862
42. lt lil gt Managing and Using Data Guidebook 127 Page Now we just need to create multi dimensional scaling plot to improve our big picture understanding before moving forward 17 Go to Analyse a Select MDS wait for the computer to process the required calculations Once completed lets change the look of the graph to gain a better perspective for our analyses 18 Go to the Graph menu a Select Data Labels amp Symbols b On the Labels left hand side uncheck the box that says Plot c On the Symbols side change the factor dropdown menu to Location 19 Click OK 20 Confirm PRIMER 6 Graph1 m e File Edit View Graph Tools Window Help o x Oeil EA k puf a GS Pohnpeifish MPA PERMANDVA exerc Transform Log X 1 SFA Pohnpei MPA fish PERMANOVA e l a oe ee amp UR Overall Transform Resemblance S17 Bray Curtis similarity P Datat 2m oe 2D Stress 028 Location f mps1 we Exercise 8 Managing and Using Data Guidebook 128 Page This MDS plot shows similarities between the individual fish transect data from each location but it does not tell us anything about the different reef tyoes MPA status or individual sites yet The intermixing of symbols and colors strongly suggests that there are no strong difference in the overall composition of fish assemblages between the locations Lets look at the reef types 21 Goto the Graph main menu and a Select
43. need to conduct pairwise testing because no overall significant variation was detected This tells us that at sites where no major increases in A planci density were observed the urchin density remained the same We can now be pretty confident in our conclusions that are graphically represented We are completed with this exercise however you can open another existing file to better understand the ecological consequences associated with high A planci densities in the CNMI from 2003 2006 End of Exercise 6 2 Exercise 6 2 Managing and Using Data Guidebook 90 Page Section 3 Multivariate statistics and graphing the results Exercise 7 An introduction to multivariate data considerations PRIMER E and PERMANOVA For this exercise we will again consider fish biomass data collected along replicate transect lines this time from Nimpal and Gachuug localities Yap State Federated States of Micronesia Rather than focus upon any individual species of fish or compare total biomass we will now begin to address the multivariate nature that many ecological datasets have Yap Community Action Program s marine office conducts monitoring at several localities that desire to establish an MPA s for conservation purposes Similar to Pohnpel for each MPA a ecologically similar reference site is established Yap s program collects data at two different depths a 3m and 10m In this exercise we will again focus upon fish data 1 Open Excel
44. on your scratch paper 10 Return to the R Software a Type power t test n 4 sd 10 63 power 0 7 Exercise 5 Managing and Using Data Guidebook 58 Page b Confirm Packages Windows Help File Edit View Misc I R Console Two sample n delta sd sig level bower alternarive test power calculation 4 al raro iD 55 Bl Bla Osis tro sided NOTE n is number in each group DONEE Gs beeline sd 10 63 Dbouer DiTl Two sample n delta sd Si 16ve1l power alternative test power calculation 4 22 37056 10 63 0 05 0 7 two sided NOTE n is number in each group gt We can see that based upon all of the sites Kosrae s team surveyed a 22 change is confidently detected in HC To understanding what our delta value translates into in terms of percent change let s put our delta value in perspective with our coral cover value 11 Go back to Excel a Click in Cell G4 and write the word Delta b Type in our value 22 37 below in Cell G5 c Click in Cell H4 and write Percent Change Detected Exercise 5 Managing and Using Data Guidebook 59 Page d Click in Cell H5 and write the following simple math formula 22 37 60 52 100 This takes our delta value divides it by the mean coverage of coral and tells us what percent change that was detected on average with our sampling design e Confirm J FMK
45. provide guidance for future data analyses you will undertake 74 Click onto our log transformed sheet Selection 75 Click on the Select menu a Check Factor level Select levels for factor b In the drop down menu highlight Combined name and c Click on the Levels box Available Include l l l MInnerresMI1 M uterresMI We will only consider village M and the Outer reefs in both MPA MInnerresMI2 M uterresMI4 status Ml nner obio IO uter oh 3 Minner oMi O z MO uber oho 4 76 In the Include dialog box select MOuterYesMI3 MInnerresMIT MOuterYesMI4 MOuterNoMO3 MOuterNoMO4 NinnerrezHl 77 Confirm on image to the right D 78 Click OK in all dialog boxes Hinner aN a Minner oN 0 Z N uteiNoNO3 Exercise 8 Managing and Using Data Guidebook 144 Page You should now have a subset of your desired sample transects 79 Next under Analyse a Go to Resemblance and b Create a Bray Curtis similarity matrix 80 While keeping you matrix sheet active a Go to Analyse again and b Select MDS plot 81 Click OK for the default settings 82 From the graph page select Data labels symbols a b On the right select Combined name from the dropdown menu C d Exercise 8 Uncheck the Plof box for labels Click OK Confirm your informative plot Transform Log X 1 Resemblance S17 Bray Curtis similarity 2D Stress 0 18 Combined
46. the default settings for the rest The user guide has detailed explanations of each we have selected the most common and general settings for now 62 Click OK PERMANOVA Design worksheet Covariable worksheet Design Term Test Sums of Squares 2 Main test Type sequential 3 Pairwise test C3 Type II conditional 2 Type III partial Mum permutations Permutation method C Unrestricted permutation of raw data Do Monte Carlo tests 2 Permutation of residuals under a reduced model Fixed effects sum to zero Permutation of residuals under the full model Use short names Exercise 8 Managing and Using Data Guidebook 139 Page After a bit of computation you should get a PERMANOVA results sheet P PRIMER 6 PERMANOVA1 E File Edit View Tools Window Help Cah EE n ER Pohnpettish MP4 PERMANOYA exercise zd ry FahnperMPA fish PERH MANDYA example P E RMANOVA E E Overall Transform Permutational MANOVA HEB log transformed fo Pesemblance Resemblance worksheet Ef Resembll Mame Bray Curtis similarity gt MES Data type Similarity E53 Graph Selection 411 F Graph Transform Logq 2 11 feo Design Besemblance 517 Bray Curtis similarity E m Fesemblancec Ea Bray Curtis similarity sums of squares type Type III partial PERMAND NAT Fixed effects sum to zero for mixed terms Permutation method Permutation of residuals under a reduced model Nu
47. wn o EE NEC T NN A52 1x98 soas oj s 133 8 esas tea MIRE ERE E 0 O nsa tan in F ca n Lu E 4A ba Qu Row 56 Col 1 Exercise 7 Managing and Using Data Guidebook 102 Page 38 Go to Select in the main menu and choose Highlighted a Confirm below PRIMER 6 Yap Nimpal MPA Fish PHworking aja ry File Edit Select View Analyse PERMANOYA Tools Window Help zu m x Dac E EIS X84 k 225 eE GA Y ap multivariate fish exercise FA Yap Nimpa MPA Fish PHworking Biomass Variables Acanthurus ir Acenthurus nf Acanthurus tr Caranx melar CephelopholujChelinus nell Chlorurus mic Chlorurus sorjCtenochaetus Epinephelus r Epinephelus rGrouper Hioposcerus Kyphosus Lutjanus gibbuLufjanus m o 0 0 o o o 0 0 247 95 o o D 0 o 0 0 449 99 o 0 127 76 0 0 0 406 21 o o o 0 o 0 1556 132863 o 0 ol 0 o DO 38739 0 310 76 o o 0 o o o 0 o o o D 0 13103 0 48791 0 0 0 o 709 22 0 0 o 0 55195 D 0 41508 o o o o 57947 D o 43278 o o 0 0 o 1327 6 238 08 o 14855 0 0 0 o 0 0 0 0 0 0 0 1466 5 490 53 ny 0 0 0 0 0 0 0 o 631 23 o 390 86 80455 1818 5 58 807 o o 0 246 8 13980 o o 259 03 0 o o 309 81 0 997 67 335 02 o 4879 E o 13980 o o o o o 0 18347 o 1877 633 77 o 0 o o o 0 o o o o o o 0 22688 11949 o o o o 0 o 0 0 0 0 0 o o 0 31973 0 70726 0 0 0 0 o D o o o D D 177 41 35 85 o D D D D D o o o o o 0 ol 268 66
48. 0 0 0000 0 5000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 5000 0 0000 J 1 5000 0 0000 0 0000 0 0000 0 0000 0 0000 2 5000 0 0000 0 0000 p 0 0000 1 0000 0 5000 0 0000 0 0000 0 0000 2 0000 0 5000 0 0000 MEMENTO E 0 0000 1 0000 0 0000 0 0000 0 5000 0 5000 2 0000 0 0000 0 5000 0 0000 3 5000 0 0000 0 0000 0 0000 0 0000 1 0000 0 0000 mre 0 0000 0 0000 1 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 5000 By 0 0000 0 0000 0 0000 2 0000 0 0000 0 0000 0 0000 0 0000 0 0000 2f 0 0000 0 0000 0 0000 0 0000 0 0000 0 5000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 5000 m 2 0000 0 0000 0 5000 0 5000 0 0000 0 5000 1 0000 0 0000 0 5000 0000 0 0000 0 0000 0 0000 1 0000 0 5000 4 5000 0 5000 0 0000 0 5000 0 0000 3 0000 0 0000 0 0000 0 0000 0 0000 1 0000 0 0000 0 0000 0 0000 S 0 0000 0 0000 0 0000 0 0000 0 5000 12 5000 0 0000 0 5000 0 0000 0 0000 E 0 0000 0 0000 0 0000 0 0000 0 0000 10 0000 0 5000 0 0000 0 0000 br 0 0000 0 0000 0 0000 0 0000 0 0000 3 5000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 5000 0 0000 0 0000 0 0000 1 0000 MEMMIUS p 0 0000 0 0000 0 0000 0 0000 0 5000 0 0000 0 5000 0 0000 0 5000 7 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 y 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 5000 s 0 5000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 5000 0 0000 0 5000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0
49. 0 0000 D 0000 D 0000 9 2006 0000 1 5000 0 5000 0 0000 0 0000 0 0000 0 0000 0 5000 0 0000 0 0000 0 0000 10 009 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 5000 0 0000 0 5000 0 0000 11 Year 2000 0000 2001 0000 0020000 2005 0000 2004 0000 2005 0000 2006 0000 2007 0000 2005 0000 2009 0000 68 Page Create Graph Type Graph types Select the type of graph you want to create Scatter Flot Line Plot mes Plats data as r Line and Scatter Plot points with vertical Vector Plot bars data are l 0 assumed Horizontal Bar Chart Box Plot Fie Chart a Click next For this example we have just a simple bar chart with error bars 25 Choose Simple Error Bars Create Graph Style Graph styles Select the style of graph you want to create Simple Bar Grouped Bar Be thet ter es Plots data as Y values with error Grouped Error Bars bars Stacked Bars a Click next We want the bars in our chart to represent Column Means and lets make errors bars that represent Standard Error 26 Choose None for the lower error bars these are redundant a Click next Exercise 6 1 Managing and Using Data Guidebook 69 Page Create Graph Error Bars Symbol values How are the eror bars computed ee ad 9 4000 The symbol value iz 9 6000 the mean of the 6 8000 column Error calculation upper Standard Errar Error calculation lower
50. 0m Reference Channel 1 Om Reference Channel 1 Om Import Reference Channel 10m Reference Outer 3m Reference Outer am OF Reference Outer am Cancel Reference Outer 3m Reference Outer 3m Help Reference Outer Exercise 7 Managing and Using Data Guidebook 100 Page 34 Click OK Very Important Now we are set for our analyses with PRIMER 35 Save your workspace as Yap multivariate fish exercise First a note about PRIMER This is a very powerful and user friendly data visualization and analyses package In this exercise we will cover some of the basic features Each user at this workshop was provided a user manual and example guidebook that accompanies the software As your capacity develops and your datasets emerge and change you can refer to the manual for more examples and suggestions Here we will conduct some of the most basic procedures in PRIMER that shows how easy and powerful a multivariate approach to data analysis can be It should be understood that less care is given to explaining the mathematical calculations that accompany these procedures rather we focus mostly on visualizing and testing patterns The user manual contains easily understandable mathematical summaries of each procedure We will first take a multivariate look at the differences in fish assemblages for shallow coral assemblages inside the Nimpal no take preserve and the Gachuug reference location 36 Select the samples that we wish to compare 37
51. 1 0 0 0 0 0 0 15 Gachuug Reference 2007 Outer 3m 4 3858 08 0 0 0 228 1668 0 0 0 2132 761 0 0 0 0 0 0 16 Gachuug Reference 2007 Outer am 25 4526 74 0 0 0 127 7607 0 0 0 2207 123 0 0 0 0 0 0 17 Gachuug Reference 2007 Outer 10m 1 0 0 O 842 3138 0 0 O 1270 162 2213 739 0 0 0 0 0 0 18 Gachuug Reference 2007 Outer 10m 2 246 1307 0 0 0 0 1983 238 O 4038 69 3729 498 0 0 0 0 0 1128 66 19 Gachuug Reference 2007 Outer 10m 3 0 0 0 0 0 0 O 4521 677 1887 781 0 0 0 0 0 1838 304 20 Gachuug Reference 2007 Outer 10m 4 0 0 0 0 173 0042 0 o 9757 544 2202 601 0 0 0 0 0 690 9934 21 Gachuug Reference 2007 Outer 10m 5 0 0 0 0 113 9958 0 0 2618 31 6682 163 0 281 1204 0 0 O 1249 616 22 Gachuug Reference 2009 Channel 3m 1 0 432 7805 0 0 0 0 0 1327592 238 0775 0 14 65502 0 0 0 0 23 Gachuug Reference 2009 Channel 3m 2 0 0 0 0 0 0 0 1466 499 490 5276 0 0 0 0 0 0 24 Gachuug Reference 2009 Channel 3m 3 0 0 O 631 2274 0 390 8646 8046 58 1818 498 59 80685 0 0 0 246 795 13980 48 0 25 Gachuug Reference 2009 Channel 3m 0 259 025 0 0 0 309 8052 O 997 6604 335 0197 0 48 79071 0 0 13980 48 0 26 Gachuug Reference 2009 Channel 3m 5 0 0 o 0 0 183 4658 0 1877 006 633 7658 0 0 0 o 0 0 27 Gachuug Reference 2009 Channel 10m 1 O 158 9545 0 0 0 0 0 100 445 64 75614 0 0 0 o 0 0 28 Gachuug Reference 2009 Channel 10m 2 0 0 0 0 0 0 0 294 7546 248 1385 oO 19 44339 0 280 0559 0 40 93125 29 Gachuug Reference 2009 Channel 10m 3 0 1024 215 0 2241723 65 32372 0 152 3426 26 77248 0 0 0 0 0 0 30 Gachuug Refer
52. 1 5751 7 471166667 o 0 0 0 0 Chuuk Outer barrier Moderate 10 C 12 3 151 5751 7 4 116666 0 0 1 1 0 8 Chuuk Outer barrier Moderate 10 C 12 1 151 5751 7 471166667 0 0 0 5 Chuuk Outer barrier Moderate 10 C 12 2 151 5751 4 1166667 0 0 O 0 2 Next we will do the same for sea cucumbers Column AB has the name of the last sea cucumber Thelonota anax 10 Click on the next column AC and right click and again insert a Name this Sea Cucumber Total 11 Do the sum function for excel ensure that all sea cucumbers are included columns P through AC Exercise 2 Managing and Using Data Guidebook 13 Page Note Instead of clicking individual cells you can drag the excel cursor across all cells if you like Your spreadsheet should look like below A AES ONES NOSTRE URS NS HQ a Get External Data Connections Sort amp Filter Data Tools Outline Sea Cucumber Total El Y AB AC AD AE AF us chloronotus 7 Stichopus exem m e M v Thelonota anax 7 0 D Sticho Sea Cucumber Total 4 Echinaster luzonicus k Acanthaster planci B EA Culcita novaquinea M T re pa DE c EN co EN co m c m c A a m pu SceaonhowocoeCgqe es Se Se O O A sd x e O M W 00 0 w GOOG O OG EU SM co DN c E c E o A c Si Pc amp euaoocuocuuudoouuuuuludlouudlouldgimuuuuouu uuludluu1uriz umGHHhwioucdd o EE O A O 8 A E A O A SA O A SO AS A SS a O E98 O A O c P dr el E c nc E c cc Ec c
53. 12 22 481 30 466 0 43 098 64 911 25 235 3 0 21 717 0 0 21 894 13 623 27 797 12 952 44 682 29 403 32 346 38 457 31 984 36 56 27 573 21278 48 481 29 897 25 967 14 426 24 365 45 175 34 923 31 806 62 557 26 218 53 282 18 052 0 44 199 0 15 853 31 13 0 62 636 39 061 0 31 719 0 19 497 49 404 49 971 0 0 33 46 O 50671 43 418 0 22382 0 0 38 057 50 368 82 686 0 11 524 22 544 o 40 064 27 175 0 22 398 0 13 335 38 902 37 858 70 915 56 917 S18 37 913 44 716 40 172 34 704 39 765 52 172 29 295 83 708 20 128 20 022 64 159 57 993 33 814 20 742 25 063 29 683 49 575 20 949 17 354 10 456 42 885 20 07 62 671 19 575 0 33 196 38 254 20 108 22175 14 297 54 397 41 914 15 699 39 486 27 206 27 508 45 522 31 862 62 994 26 695 0 58 327 32 883 38 576 46 859 26 899 55 303 17 897 14 505 45 148 27 584 3362 41511 31 783 42 684 48 426 16 637 63 536 38 08 42158 50 046 30 152 38 932 20 15 966 29 956 26414 0 49 549 32 049 0 62 581 20 418 33 83 59 356 42 524 44 752 29 941 33 298 65 268 19 073 15 391 55 838 52 172 43 753 44 476 37 967 45 578 25 93 0 49 361 56 228 31 427 34 82 22 058 51 076 ED 44 354 14 884 42 838 37 588 25 133 42 745 36 126 60 784 24 704 o 51 351 39 449 18 307 20 205 12 988 66 256 23 31 99 43 049 33 496 25 396 31 095 43 54 23 606 70 337 16 283 16 58 60 198 47 542 27 776 16 482 21 465 82 781 4 17723 36407 53 978 34751 59 348 41074 34 33 53278 23 505 22917 56 586 48 137 47 82 34 285 34 298 63 19 45 151 14 524 51 552 71 991 0
54. 18 5 954 02 759413 405 45 25 152 820 87 546 55 307 66 o 522 34 594 25 4926 0 521 54 405 11 1822 2 622 56 405 45 0 1170 9 532 04 o o S81 0 0 n 0 n n 0 n 44 727 n n n n n 245 18 5621 51 158 158 8 501 75 690 99 T S63 13 078 20 364 o 5641 3418 228 75 o 5651 a 81 909 317 43 oO oO We will follow the exact same steps as before 64 Select the Analyze menu Overall Transform a Go to pre treatment b Select Transform overall Transformation 65 Select Log X 1 from the drop down menu and Logles 1 v 66 Click OK Exercise 7 Managing and Using Data Guidebook 113 Page You have now created a new species by site datasheet you can see on the left that the current name is Data1 67 Rename this to 70m channel transformed s Y ap multilariate fish exercise ry ap Mimpal MPA Fish PHwarkina a E Overall Transform Sf 3mechanneHransformed H Resemblancel Sy Resemi 6 a A mos S7 Graphi o Grape sg B siMPER1 E MS Overal Transfarmz zB 1 m channel transt 68 Go to the Analyze menu a Select Resemblance Resemblance Mam More
55. 3 Pohnpei MPA fish transects exercise Microsoft E ke E du E blank B Siganus vulpinus B Siganus puellus E Siganus doliatus B Parupeneus barberinus B Naso unicornis B Naso lituratus B Monotaxis granoculis B Lutjanus monostigma A Lutjanus gibbus B Lutjanus fulvus E Lethrinus harak B Hipposcarus longiceps B Chlorurus microrhinos B Cephalopholis argus E Caranx melampygus E Acanthurus xanthopterus E Acanthurus lineatus Managing and Using Data Guidebook 31 Page a Uncheck the blank box if it happens to be selected if not you don t need to do anything This initial chart seems like positive news on average there is a greater biomass of just about every indicator species inside of the MPA s compared with outside However there is a lot more to consider before coming to that conclusion so we should continue our investigation 10 Click and drag the Reef type box and drag it into the fields box putting it above MPA 6000 5000 4000 3000 MEM l PE o No 1000 Inner gt M Site information PNP fish pivot chart PNP fish pivot Siganus vulpinus Siganus puellus Siganus doliatus Parupeneus barberinus Maso unicornis E Naso lituratus B Monotaxis granoculis E Lutjanus monostigma B Lutjanus gibbus B Lutjanus fulvus E Lethrinus harak E Hipposcarus longiceps B Chlorurus microrhinos E Cephalopholis argus E Caranx melampygus E
56. 4082 7 284 32 127 75 3285 2 853 58 1528 6 1664 5 200 5 0 562 14 536 53 690 41 227 04 613 06 16715 264 64 11561 101 77 5558 4 307 61 564 53 136 33 302 86 455 50 a a a a a vi Row 1 Col 1 Note Check to ensure that the factors have all been imported too Exercise 8 Managing and Using Data Guidebook 124 Page 8 Under the Edit menu a Select factors 9 Confirm below Factors A CL CL Imm 72 0 0 0 0 0 02 2 02 02 0 0 02 0j aja Combine Rename Reorder Delete ke Import Z7 Cancel Help OK Label 31 32 3 54 EE 36 37 35 33 510 3111 3121 S13 514 S15 S16 ES 4 Location Reet type You can see that all of our factors have been automatically imported by PRIMER We are going to use a useful feature in PRIMER and make a new factor that is a combination of several of the others This will be done so we can generate a better graphical interpretation Exercise 8 Managing and Using Data Guidebook 125 Page 10 Click on the Combine box on the left a Place Location Reeftype MPA and Site in the Include box in that order which follows our experimental design diagram above b Click OK Ordered Selection Select factors Avallable Include Transect H You can see your new factor has appea
57. 43626 0 0 o o 0 o 0 221 93 0 0 0 o o o 341 3 0 o 0 o 0 0 s ____ a Ji SL GEW Guta Mias el d E En EL m uL d o 0 0 688 68 0 0 0 J 564 23 ny 0 3540 3 0 0 0 o 0 o 0 51 168 o 0 ny 130 29 o 0 o 0 0 454 49 piso o o 0 o 19 079 o 0 0 19 387 o 0 o D 0 0 E o 89 217 o 0 173 0 0 0 142 33 o 0 0 721 27 0 0 lii Row 15 Col 1 You will notice the color of the cells changes to blue to indicate that selective conditions for samples are in place Note each row represents one individual transect from which observations were made Next we are ready to conduct a basic data transformation a log transformation so that our analyses takes into account the dominant and rare species of fish recorded in a realistic manner Without the transformation dominant fish such as Ctenochaetus striatus would have a greater influence on the multivariate assessment While this species is commonplace to most reefs on Yap our goal is to take the entire assemblage into account You can confer with the user manual to better understand data transformation Also the topic of transforming data has been heavily documented in books and scientific articles The transformation one chooses typically depends upon the type of data that was collected Count data would require a different transformation from biomass data and percent coverage data The transformation selected for use here is widely accepted and commonly employed for bio
58. 61 17 FMKSA0818 65 625 17 60385848 18 FMKSA08181 63 125 16 37770334 19 FMKSA0819 63 75 10 50793351 20 FMKSA13113 3625 7 772815878 24 FMEKRA13115 IA ATE A RRAAR318A Notice that only a 46 change in coral cover can be detected from this first site with statistical confidence however we desired to detect 3096 change in coral cover How many replicate samples would we need to do that lts easy to calculate First recall that the average coral cover for the FMKSA04111 site is 63 75 and 30 of that is easily calculated as 19 13 Exercise 5 Managing and Using Data Guidebook 56 Page So our desired delta value is 19 13 and now we want to find out what number of transects we need to reach our goal 8 Go back to the R software a Type in the following code power t test delta 19 13 sd 13 92 power 0 7 b Press Enter c Confirm I R Console Two s amp le n delta sd Sig level power alternative test power calculation 4 9 29425 13 92 0 05 0 7 tuo sided NOTE n is number in each group power t test delta 1i9 13 8S0713 592 BOUWBF L 7 Two samp le n delta sd ata level power alternative test power calculation 7 61935 19 13 135 92 0 05 Dat two 21ded NOTE n is number in each group gt Now let s focus on the value for n that was calculated for us n 7 62 This means that to accomplish our goals we d need to sample 8 transe
59. 95 51 16810232 0 0 10 20 cm 15 3DO1 31 SNo Inner 0 0 3344 823399 803 8615862 20 30 cm 16 32 No Inner 0 0 801 6671508 825 504714 7130 40 cm 17 33 SNo Inner 0 0 0 928 3573877 140 50 cm 18 34 No Inner 0 0 0 250 0795945 50 cm 19 35 No Inner 0 0 0 3943 030976 20 3DO2 31 SNo Inner 0 0 410 4122884 42 20930013 21 32 No Inner 0 0 11 52533634 2 651510778 22 33 SNo Inner 0 0 38 20208402 21 42392402 Drag fields between areas below 23 34 SNo Inner 0 0 60 24855971 71 45131273 W Report Filter Column Labels 24 25 SNo Inner 0 0 0 426 7695095 Species X 25 SLi 31 Yes Inner 0 0 3285 179365 16379 22135 26 32 sSYes Inner 0 0 0 12003 95724 27 33 SYes Inner 1103 600024 0 1613 431607 1768 023349 28 34 Yes Inner 0 0 4839 145245 3199 695914 29 35 Yes Inner 298 1126228 0 934 6812259 564 1745507 30 SLI2 31 Yes Inner 0 0 0 307 8528241 31 32 SYes Inner 0 0 0 69 32168018 L Rawiah 32 33 SYes Inner 0 0 0 751 4755651 Sample ID V Sum of Biomas Y 33 34 Yes Inner 0 0 1476 796345 0 Replicate z 34 35 Yes Inner 0 0 1476 796345 0 MPA E 35 3101 31 SNo Inner 0 0 200 5011135 255 3678554 Reef type y 36 32 SNo Inner 0 0 0 5 303021557 37 33 SNo Inner 0 0 170 6714083 0 38 4 E Inner 0 0 348 2527314 0 _ Defer Layout Update o M 4 Mi Site information PNP fish nivat chart PNP fish nivot PNP Fish Datahase Sheal illi Note This is the layout of the table we want to export for further investigat
60. Acanthurus xanthopterus E Acanthurus lineatus PivotChart Filter Pane T ox Active Fields on the PivotChart El Y Report Filter Fl MPA v d Legend Fields Series Species Values Average of Biomass g Pivot Table Field List xl Eg Choose fields to add to report M Drag fields between areas below SY Report Filter E Legend Fields IU ES AxisFields Cat X Values Defer Layout Update Jpdate Now we can clearly see that inner reef MPA s are protecting a much larger proportion of the biomass as compared with outer reefs Specifically Caranx melampygus jack and Hipposcarus longiceps parrotfish are two fish that seem to be influential drivers of this trend Are these differences in success based upon reef type due to proximity of human populations that help maintain and enforce the MPA Are they due to natural differences in habitat types whereby outer reefs are harder to access so differences are less Exercise 3 Managing and Using Data Guidebook 32 Page pronounced We must be clear that we can t answer these questions with our existing data but we can continue to learn about patterns so we know how to most efficiently learn about cause Let s focus more on understanding patterns for the inner reefs as they seem most influential 11 On the PivotChart Filter Pane click the drop down menu next to Reef type and leave only inner reefs checked a Click the
61. Bad E ms us m ld a lw Fi SERE B r ud I U ese E E rai Merge 8 Center 7 8 00 Conditional Format Good Neutral la Insert Delete Format Sort amp Find amp Clipboard T4 Font Alignment Ca Number ta Styles Cells Editing 3 ss qe of Acanthaster 4 Site lwipate Transect 36 5 2001 25 23 2002 Echinothrix Diadema Grazing Urchin Total Heterocentrotus JEchinostrephus of Tripneustes gratiella Linckia 39 17 2003 29 28 2005 Akino Reef 28 16 2000 20 Fi EJ 35 9 2002 23 Drag fields between Pu below Hb tS Report Filter ZH Column Labels 24 Year F 25 26 er 28 SAlaquan Bay did Row Labels Values 33 Barcinas Bay a 7 25 2000 3 35 35 25 30 2002 3T Id 4 k Fl Ready CNMI invert pivot Sheeti Exercise 6 1 Managing and Using Data Guidebook Our table now has the population density estimates for coral eating starfish during each year These are the data and proper format required for Sigma Plot to produce our desired graph 11 Click on the dropdown menu for Year in the PivotTable cell D3 a Transfer our data year by year Check only the box next to 2000 first b Confirm A B de D 3 Average of Acanthaster Year x 1 Site Date Transect 2000 3 lAkino Reef 18 16 2000 0
62. Column H Impact Sites The predator starfish were not seen in high abundances at all sites where monitoring was conducted at No indicates that no increase in COTS abundances was evident and Yes means high populations were recorded We will explore another more simplified format for transferring data into Sigma Plot for further graphing and analyses of CNMI s database 2 Go back to our PivotTable sheet 3 Remove all Column Row and Values from the boxes on the lower right 4 Choose COTS and Impact Sites for new row labels in that order a Drag Acanthaster under Values 3 times 5 Left click the first Acanthaster a Select Field Attributes and then select Count 6 Set the second to Average 7 Set the third to StdDev 8 Right click on Cell AF or COTS and Drag fields between areas below W Report Filter 9 Column Labels iud Row Labels E Values b Check None under subtotals and filters ors 07 Count of Acan Y Impact Sites hi Average of Ac Y StdDev of Aca 9 Repeat previous step for Impact Sites 10 Confirm Exercise 6 2 Managing and Using Data Guideboo Defer Layout Update Page We again have data ready for Sigma Plot in a simplified summarized format Sigma Plot can handle raw data or summarized a major benefit for us Take a moment to understand what is on our datasheet The Count function in excel adds u
63. D 5 61549908 D D 100 3901652 D 31 o Yes Inner D 5 61549908 0 29 53955032 0 32 KO1 1 No Inner D D D D 246 7601492 56 47521097 2 No Inner 0 0 0 0 29 53955032 17 85092737 3 No Inner 26 94957737 0 0 259 8023409 309 1474729 91 17613625 4 No Inner 166 4284061 0 D 127 7606938 417 0557012 153 2934902 5 No Inner 35 81142032 D D D 157 0441591 274 2833456 37 KO2 1 No Inner 0 86 50376278 D D 1416 244105 D 38 2 No Inner 0 0 0 0 321 4499577 0 db bl PNP fish nivat chart PNP fish nivat PNP Fish Datahase PNP Fish Data hv Transect Sheet 2 JM m b You should have a new database generated that shows fish abundances by transects now There is only one problem left for each site SamplelD there is only one label with four blank boxes below We need to fill in the blank boxes below each site label Unfortunately there is no automated easy process to do this but Excel has some helpful functions to reduce the time required 22 Highlight cells A2 A6 Exercise 4 Managing and Using Data Guidebook 43 Page 23 Go to the Home tab on Excel s main menu a Click on the drop down box named Fill b Choose the first option Down Notice Excel fills the boxes with the same site label D 1 a us below Page Han seri ay OUT For Mnr A m Verdana 10 as Copy P in wil io us 4f Format Painter Is Clipboard E Font A2 r fe DI1 B C ESO Eten gt LL JL DI1 1 Yes In DI1 2 Yes In
64. IMPROVING DATA COLLECTION STORAGE HANDLING VISUALIZATION AND ANALYSES FOR MICRONESIA S CORAL REEF MONITORING PROGRAMS 37 50 24 65 49 38 4344 4211 43 75 00 3438 5281 6 8 A guidebook with step by step exercises using regional datasets to improve local capacity for data interpretation Dr Peter Houk environmen Pacific Marine Resources Institute WWW pacrmares com Table of Contents oie hae et o ee tet hee A i section 1 Database generation manipulation and query invest O ia 1 EXOrcie TE ShUUIIS TO IATA SO AAA AAA AAA AAA AA AAA AAA 1 Exercise 2 Manipulating Managing Working with and Visualizing a Database sc si NAAA AA AAA save bv RU AAA 11 Exercise 3 Advanced queries into a large multivariate dataset to understand ecological patterns pertinent for management actions 27 Exercise 4 Beyond examining trends Reformatting an existing database to understand statistical aspects of the data sess 37 Section 2 Univariate Statistics amd orapbing he FeSulfs ta A AAA AS dro dic S da 51 Exercise 5 Simple calculations of statistical power Tor influential dependent Ural a 51 Exercise 6 1 An introduction to creating report quality graphs and preparing data for univariate statistical analyses sss 63 Exercise 6 2 Conducting basic univariate statistical analyses and producing informative professional quality graphs to show your
65. Inner Inner Inner B C D E F G H I J K Ed Reeftype____ M4 wave exposure 4 Depth LESER transect Micpsx 71552 Bd Hippopus Bai Tridacna crocea_ Tridacna spp B4 3 C 10 1 151 7001167 7 3973 0 0 0 3 C 10 2 151 7091167 7 3973 0 0 0 3 C 10 3 151 7091167 7 3973 0 0 0 3 C 10 4 151 7001167 7 3973 0 0 0 3 C 10 5 151 7091167 7 3973 0 0 0 10 C 10 1 151 7001167 7 3973 0 0 0 10 C 10 2 151 7091167 7 3973 0 0 0 10 C 10 3 151 7091167 7 3973 0 0 0 10 C 10 4 151 7091167 7 3973 0 0 0 10 C 10 5 151 7091167 7 3973 0 0 0 3 C 11 2 151 788 7 369016667 0 0 1 3 C 11 1 151 788 7 369016667 0 0 0 3C 11 3 151 788 7 369016667 0 0 0 Exercise 2 Low Low Low Low Low Low Low Low Low Low Low Low Low Managing and Using Data Guidebook Columnl M hh sta MESA a Pa SreeacdwWwWwreoocgcnococas t2 Page 9 Now highlight cell L2 Write sum to conduct an automated sum function in excel b Click on the cell 2 place a comma c Click on J2 add another comma d Click on K2 finish with a closed parenthesis e Press enter Excel fills in a sum function for the entire database automatically This column is now the total abundance of all three clam categories and can be used as a summary L3 ble2 amp This E d his e Mi crocea his ee acna spp m emai M ae 1 Toland E CT Wave exposure E El depth JEA Bi Transect Bcs ETEA T AA PT TET EA clam Total_
66. Managing and Using Data Guidebook It is very straightforward how to continue to enter data in this manner one can keep on adding species in the columns to the right of the Excel list Finish exercise 1 save your Excel file for future reference Exercise 1 Managing and Using Data Guidebook 10 Page Exercise 2 Manipulating Managing Working with and Visualizing a Database 1 Open the file Chuuk REA invert database complete a Click on the sheet Database build Here you will find the same database we just built however now it is populated with 520 transects of macroinvertebrate data abundance estimates that were collected during the Chuuk REA Examine the database especially look at the organization The data are sorted by Island Site and Depth You can re sort the data by using the column headers and clicking on the dropdown arrow next to the column heading 2 Click on cell K2 Tridacna spp a Sort from largest to smallest On ld ut id Chuuk REA invert database complete Microsoft Excel Table Tools Home Insert Page Layout Formulas Data Review View Add Ins Acrobat Design o x La Connections A ne K Clear E e m M gt Hcy ace A a 5 Show Detail oet J Fy Properties 24 EA o Reapply Sas 4 Eo x te de SS 22 Hide Detail Fro Fron From From Other Existing Refresh Spee 21 Sort Y Text to Remove Data Consolidate What If Group Ungroup Subtota ccess b Text
67. SA04111 63 75 6 FMKSA04113 76 25 7 FMKSA04115 60 625 8 FMKSA04120 70 625 9 fmksa s110 62 5 10 FMK54061101 67 5 11 FMKSA08112 61 25 12 FMKSA081121 63 75 13 FMK5405116 FO 14 FMIKSAU8 14 56 675 15 FMKSA0816 11 875 16 FMKSA08161 16 815 1f FMKSA0516 65 625 18 FMKSA06161 63 125 19 FMKSA0819 63 75 20 FMKSA13113 36 25 21 FMKSA13115 36 675 22 FMKSA1312 46 675 23 FMKSA1319 36 25 24 limksa16110 52 5 25 FMEKSAT161101 59 375 25 FMKSA16112 60 625 2f FMKSA161121 61 25 28 FMKSA16116 66 125 29 Grand Total 50 52083333 sStdDev of HC 13 31841081 17 96388221 11 96783885 7 465197026 T 359800722 9 574271078 8 539125638 5 204164999 9 783450104 12 47914926 16 41364963 11 43366561 17 60365046 16 377 70334 10 50793351 ff f2015676 5 884453184 9 555525183 131497782 5 40061 249 12 14066657 3 145 64346 FT 12815878 10 48312135 14 335247 173 Overall HC average Overall HC standar deviation 60 52083333 10 62544548 Delta Percent Change Detected 22 37 36 95299 We can see that Kosrae is successfully able to detect a 37 change in HC should one occur with confidence using their sampling design Recall that our goals were to detect a 30 change in HC Exercise 5 Managing and Using Data Guidebook 60 Page 12 Go back to Rand Calculate how many transects would be required to improve our confidence just a bit to attain these goals a Set our delta value to 30 of the average es
68. Sources Connections Ally Edit Links 7 Advanced Columns Duplicates Validation Analysis M Get External Data Connections Sort amp Filter Data Tools Outline F7 fe 3 n r A B G D E G H I J K k M N i Em CEDA AA Depth M site Transect Micpsx Micrsy e Hippopus E e EETA Crinoids MA Lobster all spp MM octop 2 Kuop Inner barrier Low 3 C 26 5 151 8756 7 1075 0 0 85 0 0 d 3 Etal Outer atoll barrier Moderate 10 M 14 1 153 5894167 5 574016667 0 0 70 0 0 4 Kuop Inner barrier Low 10 C 26 1 151 8756 7 1075 0 0 58 0 0 5 Etal Outer atoll barrier Moderate 10 M 14 4 153 5894167 5 574016667 0 0 50 0 0 6 Murilo Inner atoll Low 3 H 6 5 151 8299 8 55305 0 0 47 0 0 7 Etal Outer atoll barrier Moderate 10 M 14 3l 153 5894167 5 574016667 0 0 45 0 0 8 Kuop Inner barrier Low 3 C 26 2 151 8756 7 1075 0 0 42 0 0 9 Etal Outer atoll barrier Moderate 3 M 13 3 153 5636667 5 571383333 0 0 38 0 1 10 Kuop Inner barrier Low 10 C 26 2 151 8756 7 1075 0 0 3 0 0 11 Chuuk Inner barrier Low 10 C 21 2 151 7131833 7 225916667 0 0 36 0 0 12 Kuop Inner barrier Low 10 C 26 3 151 8756 7 1075 0 0 36 0 0 13 Murilo Inner atoll Low 3 H 6 3 151 8299 8 55305 0 0 Hg 0 0 14 Kuop Inner barrier Low 10 C 26 5 151 8756 7 1075 0 0 31 0 0 15 Chuuk Inner barrier Low 2 C2 5 151 9884333 7 357916667 0 0 30 0 0 16 Etal Outer atoll barrier Moderate 10 M 14 2 153 5894167 5 574016667 0 0 30 0 0 17 Kuop Channel Low iq 2 151 9883167 7 00615 0 0 30 0 0 18 Murilo Inner atoll Low 3 H 6
69. Table3 Layout amp Format Totals amp Filters Display Printing Data Layout Merge and center cells with labels When in compact Form indent row labels character s Display Fields in report Filter area Down Then Over Report filter Fields per column o B Format For error values show EE For empty cells show Auto e column widths on update Preserve cell Formatting on update Exercise Managing and Using Data Guidebook 92 Page 6 Repeat for cells A6 A7 A8 and A9 This will condense all of the subtotals that Excel autogenerates 7 Right click anywhere in the table a Scroll down and select PivotTable Options 8 On the tab Layout amp Format a Check the box that says For empty cells show b Puta 0 in the box 9 On the tab Totals Filters a Uncheck the box grand totals for rows and columns 10 On the tab Display a Check the box Classic Pivot Table layout Field Settings Source Mame Site Subtotals amp Filters Layout amp Print Subtotals CO Automatic Select one ar more Functions Filter _ Include new items in manual Filter Exercise 7 Managing and Using Data Guidebook 93 Page 11 Confirm your new table look IF A c D E F G 3 3 ajs um of Biomass Scientific Name 7 4 Site MPA Status _ Year Reef Type Depth m 5 Gachuug Reference 22007 Channel
70. Using Data Guidebook 76 Page 26 Right click on Column 9 a Choose Column Titles b Promote the column headings to titles for all 5 columns columns 9 13 27 Confirm SigmaPlot Data 2 File Edit Insert view Format Tools Graph Statistics Transforms Toolbox Pharmacology Window Help F X ED Rd b BS o ce Em p OE AO 101205 O One Way ANOVA gt es c ES ey ime Frame mpac ION a Cantnascel verage carni raster i eyo caninas Er Ime Fray Mpar L1 ALOUnE a Cancnasce verage carni Traste eyo caninas Tm n 1 Time F E Sili Count of Acanthastel A F Acanthasteri StdDew of Acanthaster 6 7 8 Time Fran Impact Sil Count of Acanthastd A F AcanthastestdDew of Acanthast a B After Yes 45 0000 0 1556 0 3341 After No 80 0000 0 1188 ls idu Hin F Before Yes 42 0000 0 0714 0 2361 Before No 159 0000 D 1132 0 4462 T Oata PxHam eee P 3 During Yes 96 0000 1 2917 2 3052 During No 181 0000 0 1133 0 2928 B Data 1 2i w lis Graph Page 1 3 r3 S i Section 3 6 Data 2 7 cd a a Based upon previous exploration of the data using Excel Pivot Tables it was determined that a simultaneous look at grazing sea urchins was most useful to understand some influential trends We will now place the grazing urchin data alongside the COTS data 28 Go back to Excel Drag fields between areas below a Change the Impact Sites filter to Yes Wf Report Filter 2 Column Labels b Remove a
71. _ Crinoids Lobster al Chuuk Inner Low 3 C 10 1 151 7091167 7 3973 0 0 0 o 0 3 Chuuk Inner Low 3 C 10 2 151 7091167 7 3973 0 0 0 o 0 Chuuk Inner Low 3 C 10 3 151 7091167 7 3973 0 0 0 0 0 Chuuk Inner Low 3 C 10 4 151 7091167 7 3973 0 0 0 0 0 Chuuk Inner Low 3 C 10 5 151 7091167 7 3973 0 0 0 0 0 Chuuk Inner Low 10 C 10 1 151 7091167 7 3973 0 0 O 0 O Chuuk Inner Low 10 C 10 2 151 091167 7 3973 0 0 0 0 0 Chuuk Inner Low 10 C 10 3 151 7091167 7 3973 0 0 O 0 1 Chuuk Inner Low 10 C 10 4 151 7091167 3973 0 0 0 O 3 Chuuk Inner Low 10 C 10 5 151 7091167 7 3973 0 0 0 o 20 2 Chuuk Inner Low 3 11 2 151 788 7 369016667 0 0 1 1 0 Chuuk Inner Low 3 C 11 1 151 788 7 369016667 0 0 0 o 1 Chuuk Inner Low j C 11 3 151 88 7 36901666 0 0 0 O 0 Chuuk Inner Low 3 C 11 4 151 788 7 369016667 0 0 0 o 0 Chuuk Inner Low 3 C 11 5 151 788 7 3690166607 0 0 0 0 0 Chuuk Inner Low 10 C 11 1 151 788 7 369016667 0 0 0 o 0 Chuuk Inner Low 10 C 11 El 151 788 7 3690166607 0 0 0 0 0 l9 Chuuk Inner Low 10 C 11 3 151 788 7 369016667 0 0 O 0 chuuk Inner Low 10 C 11 4 151 788 7 3690166607 0 0 0 0 0 Chuuk Inner Low 10 C 11 5 151 788 7 369016667 0 0 O 0 O 2 Chuuk Outer barrier Moderate 3 C 12 1 151 5751 7 4 1166667 0 0 0 0 O Chuuk Outer barrier Moderate 3 C 12 2 151 5751 7 471166667 0 0 0 0 0 Chuuk Outer barrier Moderate 3 c 12 3 151 5751 7 471166667 0 0 0 0 0 Chuuk Outer barrier Moderate 3 C 12 4 151 5751 4 7116666 0 0 O 0 1 Chuuk Outer barrier Moderate 3 C 12 5 15
72. a Click on that box all cells in the database are automatically highlighted iati Tables Illustrations fe Island Inner Low Chuuk Inner Low P bo MJ EH b Click on the Insert Tab of Excels main menu and c Click on Pivot Table d The table range should match and ensure that New Worksheet is selected Create PivotlTable Choose the data that vau want to analyze e Select a table or range Table Range AE eka ele Use an external data source Choose where vou wank Ehe PivotTable report to be placed 2 New Worksheet Existing Worksheet Location e Click OK Exercise 2 Managing and Using Data Guidebook 16 Page A new sheet see below should be created between Metadata and Database build that is called Sheet 1 You can right click and rename it to Chuuk REA Invert Pivot Click back inside the Pivot table area in the upper left With Pivot Table you can make summary tables and graphs easily and quickly The first thing we will do is take a simple look at sea Cucumber abundances by island To build a report choose fields from the PivotTable Field List ld 4 Fl Brainstorm metadata fields Metadata Sheetl Database build Sheet2 Shed P PivotTable Field List X Choose fields to add to report Island Reef type Wave exposure Depth site Transect eps x _ GPS y Hippopus Tridacna crocea
73. a Open Yap Nimpal MPA Fish Notice the database site metadata and fish species lookup tables that were used to generate the database In this database each row represents one or more fish of the exact same size of a particular species that was observed on a transect For the Pohnpei database recall that each row was one individual fish only here column J indicates how many fish were seen on any transect of the same species and size Make sure you understand that before moving forward We will need to prepare a table that we can import to PRIMER E for further analyses We ll again use Pivot Table features 2 Highlight the data and insert a Pivot Table Drag fields between areas below a Name the table Y ap fish pivot w Report Filter ZH Column Labels b Add Site MPA Status Year Reef Type Depth m Scientific Name and Transect all under Row Labels in that order c Add Scientific Name to the Column Labels d Add Biomass to the Values e Change the attributes of Biomass to Sum E Row Labels gt Values 3 Confirm Sum of Biomass Y Exercise 7 Managing and Using Data G 91 Page eene o Defer Layout Update Now modify the way the table looks for easiest input into PRIMER 4 Right click in cell A5 a Select Field Settings b Under Subtotals select None 5 Confirm Pivot lable Options Mame Pivot
74. a cucumbers a consequence of the high islands located in Chuuk Lagoon providing suspended particulate matter to the lagoon through the deposition of terrestrial organic matter These trends are expected Third the outer islands all have very low abundances in fact at some none were recorded Fourth in all instances the standard deviation is greater than the average This informs us right away that our statistical power to detect change over time in sea cucumber abundances is low for the entire island level However our goals are to understand change at the individual site level So we will see how the data improve our understanding of the distribution of sea cucumbers around Chuuk only Notice you can manipulate the Pivot Table in chart mode with Excel as well as table mode These next steps could be done on the Chuuk REA Invert Summary graph sheet or the Chuuk REA Invert Pivot table sheet Keep on the graph sheet for now 22 Under the PivotChart Filter Pane window click on the drop down menu for Island a Uncheck all islands except for Chuuk b On the PivotTable field list drag Site and Depth below Island c Confirm below E T PivotChart Filter Pane v x PivotTable Field List Y X Active Fields on the PivotChart a Choose fields to add to report EE Y Report Filter Island Y Reef type 124 Axis Fields Categories Wave exposure Depth Site _ Transect Cees x _ GPS y Hippopus
75. ace z Ed Yap NimpalMPA Fis Biomass Variables canthurus lij Acanthurus n Acanthurus triCeranx melmCephslopholujChellinus und Chlorurus mic Chlorurus sor Ctenochaetus Epinephelus mEpinephelus mGrouper Hipposcarus Kyphosus Lutjanus gibbqLutjanus mondMacok gn ec 3 0 5 9 9 689 89 20 20 90 3 P3 9 83 0 0 90 9060 49 gm 0 49 9 0 0 Wm 9 9 09 We 0 9 9 9 9 09 9 155 1 09 0 0 0 20 09 3 3 0 308 0 9 9 9 0 gp o 9 0 o 0 9 0 9 13 0 4m 0 9 0 9 0 G9 mem 0 0 0 0 599 0 0 0 0 0 9 SM 0 9 gg 9 9o O9 O9 0o 9 2s wer E 0 0 255 0 0 9 om o o 9 9 9 iM 0 ss 4mm 0 90 0 59559 9 9 0 Gb o 0 0 o 90 0 0 S413 9B 0 oF oF 9 0 oF 9 co 0 45 0 09 0 MOM 0 343 x 0 0 0 0 0 9 0 gg o ss op o 0 0 0 1958 3S S02 0 0 1935 0 9 O0 gm aj o 90 S945 9 o 9 9 rem 290 9 0 9 9 0 9 gu sm 0o o a o 90 9 o WM o 09 09 9 09 09 0 giu 9 959 0 9B X298 IA O0 Jf00 ey84 X 0 HgH 20 QJD0 A 90 Q0 1 o 3500 0 0 0 201 0 00 20 228 4 0 24A40 20mm 2390 80 X900 8 gi 4567 0 0 0 177 0 0 0 29541 0 0 0 9 0 0 9p em A A AA A AA d dl 3 e e e A i o 2643 0 0 0 0 1832 0 4D88 3795 0 0 0 0 0 2 B Nom s 9 93 9 3 e mz ys 9 9 B 3 M8 4893 zem uU X302 Xd 0 3
76. ackages in publications Type demo for some demos helpi for on line help or help startii for an HTML browser interface to help Type qfi to quit E gt You should have a R Console dialog box that is ready to accept code to process your queries The package for standard statistical power calculations comes pre loaded in R 3 Insert the code you learned about from the website power t test n 4 sd 13 92 power 0 7 We are required to provide 3 of the fours items listed above remember So we know our sampling originated from n 4 transects our sd 13 92 from the excel sheet and our desired power level or probability will be 70 or 0 7 4 Press Enter 5 Confirm with screen shot below Exercise 5 Managing and Using Data Guidebook 54 Page I R Console Natural language support but running in an English locale R is a collaborative project with many contributors Type contributorzs for more information and citationi on how to cite ER or E packages in publications Type demo j for some demos help 1 for on line help or help start for an HTML browser interface to help Type qii to guit HR gt power t test n 2 sd 13 9z power 0 7 Two S8mp Le test power calculation n 4 delta 29 29420 su 13 92 aig lewwel 0 05 power 0 7 alternative Ewo sided NOTE n is number in each group gt We can see the results now very clearly We are interested in the valu
77. al Biomass E Average of Total Biomass 6000 cl fields between areas below w Report Filter 3 Legend Fields Legend Fields 4000 2000 NM id Axis Fields Cat Axis Fields Cat Yes Yes No Mo Yes Yes No Mo Yes Yes No Mo Yes Yes No Mo Yes Yes No Ma Sample ID Average of To StdDev of Tot Y DIl Diz DO1 DO2 KM KI2 KOT KO2 LII LI LO1 LO2 Mil Miz MO1 MO2 Nil NI2 NO1 NO2 Tal rot ean PHP th travesti aan T LUE M Ire We can clearly see that we have improved our confidence interval surrounding our data by utilizing the new field sum of fish biomass In many instances the standard deviation appears to be less then 50 of the mean and appropriate for the calculation of statistical Exercise 4 Managing and Using Data Guidebook 49 Page power However this trend is not universal and our conclusion would be to also examine the multivariate properties of these datasets Both statistical power and multivariate data analyses are approached in a later exercise In just a short period of time we have successfully identified island wide trends associated with Pohnpei s MPA network We subsequently identified which MPA s seem to be most successful Finally we re formatted our data to understand statistical consideration of our dataset We are armed with a logical framework and flow to create a report power point lecture grant app
78. ames at the impact sites We need to know which time frames are different from each other because there are three So the dialog box asks us logically to choose a comparisons of individual means Exercise 6 2 Managing and Using Data Guidebook 87 Page 92 Select Fisher LSD from the drop down menu Multiple Comparison Options Select Factors to Compare Treatments are significantly different Factor A P Factor amp lt 0 001 Suggested Test Description Fisher s LSD Test can be used for pairwise Comparison Type comparisons IE rz included for the sake of m completeness The Holm Sidak test is All Pairwise preferred over Fisher s LSD test This is one popular post hoc comparison of means test used in ecology 93 Click Finish 94 Confirm the statistical testing results sheet below One Way Analysis of Variance Monday June 28 2010 12 43 06 PM Data source Data Z in CHMI data example JHE Group Name W Missing Mean id Dev SEM Row 1 45 0 4 722 3 211 0 48 Row 2 42 0 4 548 3 039 0 777 Row 3 a 0 2 083 3 056 0 316 Source of Variation DF an MS F P Between Groups E 298 303 149 252 11 090 0 001 Residual 120 2422 403 13 458 Total 182 2720 997 The differences in the mean values among the treatment groups are greater than would be expected by chance there is a statistically agruficant difference P lt 0 0017 Power of performed test with alpha 0 050 0 991 All Pairwise Multiple Compari
79. amp Filters tab a Uncheck the boxes for Show grand totals both of them 6 Click on the Display tab a Check the box that says Classic Pivot Table layout 7 Click OK to close the dialog box 8 Confirm below Exercise 4 Managing and Using Data Guidebook 38 Page Qn kl Qu M Pohnpei MPA fish transects exercise Microsoft Excel PivotTable Tools mx Home Insert Page Layout Formulas Data Review View Add Ins Acrobat Options Design OQ nx pos Verdana 7110 A A B E Wrap Text General z Ri Normal 8 Normal 9 e m ER iu Liu AT 4a Copy o t ig Fill rome B Z DEO A E S cm d de ere cores gt 8 ote o sd 28 Conational remet Normal Bed ica Ca Clipboard Ta Font Ta Alignment F Number fa Styles Cells Editing c8 y d A PivotTable Field List Y X 1 2 Choose fields to add to report 3 Sum of Biomass q Species 4 Sample ID Replicate Acanthurus xanthopterus Cephalopholis argus Chlorurus microrhinos Hipposcarus longiceps Lethrinus harak _Lutjanu gp Y 5 SDI 0 0 22 28788213 0 0 118 OMPA 6 0 0 14 39864279 0 56 50028025 Reef type Y 7 0 0 106 9436501 278 5780129 0 132 v Replicate 8 ol 0 1636 499422 0 0 100 v Speces 9 0 0 0 219 9682947 0 Length 10 DIi Total 0 0 1780 129597 498 5463077 56 50028025 351 5 Da 11 SDI2 1 5 61549908 0 38 07614397 20 87648147 0 Clb 12 2 34 68837875 0 89 38812819 0 0 Y
80. ample our sample unit is one individual transect remember to others based upon the differing biomass of fish in each species category Again there are many mathematical formulas that researchers have derived to do this we ll use a very common one for ecological studies called a Bray Curtis similarity measure Here is what it looks like D y1 y2 2 ytj yal 2 yj y2j D is the Bray Curtis distance between two samples or transects in our case 2 represents the summation for all fish species and y1j and y2j represent fish biomass from two different transects It is simple to understand that the ecological distance is calculated by dividing the difference between fish species abundances by the sum for two consecutive transects This is done for all species and all transects by the computer and we end up with a desirable measure of distance between each transect In other terms the distance tells us how similar two transects were or were not 41 Go to the Analyze menu a Select Resemblance Resemblance Mam More Analyse between Measure 2 Samples 5 Bray Curtis similarity C2 Variables 3 Euclidean distance C3 More tab Add dummy variable po Exercise 7 Managing and Using Data Guidebook 105 Page Note Make sure the analysis is between Samples and were using the Bray Curtis similarity 42 Click OK P 1 DRIMER 6 Hacer PRIMER 6 Res
81. at calculates the relative contributions of each species in determining the trends that the graph show 49 Go back to the 3m channel transformed data sheet We need to make further selections from our data What we want to know is how and why the fish biomass are different between these reefs in 2009 only because in 2007 they were still similar Basically we d like to know what change occurred 50 In the Edit menu then select Factors Notice only our subset of sites appears For our next examination we wish to look at only 2009 data corresponding to samples 821 9825 and 856 860 Note those sample labels on your scratch paper and close the factors box 51 Click on the samples noted above 52 Go to the Select menu a select highlighted Exercise 7 Managing and Using Data Guidebook 110 Page 53 Confirm your datasheet below Notice only 10 samples remain these correspond to 10 transects surveyed 5 inside of the MPA at a 3m depth in 2009 and 5 outside DIOmass ariables Acanthurus lil amp canthuruz ni Acanthurus tr cCaranx m lamCephalopholug Cheilinus undd Chlorurus mici Chlorurus sor Ctenochaetus Epinephelus m Epinephelus my Grouper Hipposcarus lhKyphosus S24 D 6 0725 D D D 71919 5 4768 2 7508 D o D 322 n D of ol ol ol ol 7 2913 6 1975 D D D d 0 523 D 0 D 5 4492 D 5 9709 8 9931 7 5063 41077 D D n 5 5126 9 5455 S24 D 5 5608 D Y D 5 7392 D B S064 58172 D
82. barri am C 14 2 Outer barril SL MEI idt dl c 12 3 Channel ll Where is the data For your table C 14 3 Inner barrie C 16 4 Channel M 4 4 Inner My table has headers M 9 5 Managing and Using Data Guidebook LOOKUP E10 Metadata SAS2 54557 Metadata S BS2 5B557 153 54058331l Transect GPS X 151 8 06333 151 788 151 5917333 151 58505 151 5917333 151 58505 151 4476667 153 7878667 153 5405833 H GPS y 42986600 369016607 4 655 7 4684 4 655 7 4684 396533333 5 531483333 95 04633333 GPS y 7 429866667 369016606 7 7655 7 4684 7 7655 7 4684 395533333 5 531483333 24046333334 a Page b Make sure the box for My table has headers is checked and click OK 24 Click in cell A11 a Select any island of you like from the drop down menu A B 1 Island Ed Reef type Bl Wave exposure Ed Depth Edisite Editransect EcrsxEicesy 2 Chuuk Channel Low 3m E T 1 151 8706333 7 420866667 3 Chuuk Inner Moderate im C 11 1 151 788 7 36901660607 4 Chuuk Inner barrier Low 3m C 13 2 151 5917333 7 47655 5 Etal Outer barier Sheltered 3m C 14 2 151 58505 7 4684 6 Losap Outer barrier Moderate 3m C 13 3 151 5917333 7 47655 Losap Channel High 10m C 14 3 151 58505 7 4084 amp Murilo Inner barrier Low 10m C 16 4 151 4476667 7 396533333 9 Etal Channel Moderate 10m M 4 4 153 7878667 5 531483333 10 Chuuk Inner Moderate 10m M 9 5 153 5405833 5 404633333 ad A 25 Do the sam
83. bels we do not have either of these in our Excel file a Select Samples as rows for the Orientation b Select Biomass for the data type c Click Finish Exercise 8 Managing and Using Data Guidebook 123 Page 7 Confirm below PRIMER 6 Pohnpei MPA fish PERMANOVA example MEIE EO File Edit Select View Analyse PERMAMOVA Tools Window Help X D Wa t SOE k 2 DG 5 Elle GS Workspace samples Caranx mela Cephalopholis Chlorurus mici Hipposcarus WLethrinus haraLutjanus tulvulLutianus gibb Lutjanus mondMonotaxis gra Naso lituratus Siganus doliat Siganus puelll Sigan l A canthurus li A canthurus X 0 234b 0 A Mi 0 ES 0 0 0 0 9 WEB 0 85 0 382 0 0 0 0 MWBE 0 O We N Sj wa ess 0 0 Y 9 0 39 9 0 9 0 9 oes 0 0 9 0 19 0 0 8E 0 8 3i mb 0 0 9 0 09 23599 0 9 0 9 sms 0 0 0 9 0 90 9 9 0 18 INICIE 0 0 S 0 285 0 0 8 0 n mea 8 0 3 9 9 Q 9 90 9 3 AX 0 9 9 0 8 9 0 09 0 3 9 120 9 0 o 1 er TEO asta ara a 5 6155 34 600 8 8358 17 732 51 168 410 41 11 525 30 202 60 249 41 538 26 95 1362 226 5 208 81 188 48 446 02 22 200 127 61 108 73 100 38 29 54 427 58 40 801 631 4 1 129 5 6155 26 623 5 6155 5 6155 41 056 69 322 124 71 4 5374 29 024 13 853 100 54 142 24 316 16 455 29 432 35 950 05 13 252 351 18
84. ble Name Active Field 92 Expand Entire Field G UP Selection AJ E ES i j i j i a PivotTable3 Depth mj Ungroup Z ire Fi pa Sort Refresh Change Data Clear 4f Options Oy Field Settings A EF Group Field Ad PivotTable PivotTable Active Field Group Sort Data 4 A B C D 1 Fa 3 Sum of Biomass 4 Site y MPA Status Year y Reef Type Depth m 5 cGachuug Reference 2007 Channel 310m 6 a Ey 9 10 23m 11 E rd 13 14 l 1 15 Outer 110m 16 17 18 19 20 23m 21 22 23 24 25 22009 Channel 310m 26 21 28 23 30 33m 31 32 M 4 b M Site metadata Fish lookup table Yap fish pivot_ NimpalGachuug fishdata Primer Prepare Yap FiskHl Ready 16 Delete extraneous rows and columns a Delete Row 7 b Delete Row 77 c Delete column AC Exercise 7 Managing and Using Data Guidebook ue E E PivotChart Formulas OLAP X tools w N e Un gt w Nbe Un b amp w MJ E oO G0 Ne 0 hw Ne Un DUN 155 5966648 0 709 220469 0 246 430697 0 0 0 0 637 0111691 0 3858 567874 4526 739952 OG O O O O O O O 443 8639399 158 9544664 0 449 9912911 132 6289882 co Na AAA AAA MAR 1024 215323 1394 48005 1218 561304 432 7804749 0 0 Drag fields between areas below Y Report Filter 95 Page Now we have to fill in the missing cells in columns A through E with fill down functions similar to before D
85. cc mc c mc Ec Ecc c Ec n eOuacHmdouudoocuuduuludcgcddcurmnuuludzliuoluologuacsuurm uuuuudccd eal mmm Emm CE C NC fel fel el Ue el Ue AS eel Ee H Poo M gt M Brainstorm metadata fields Metadata Database build Sheet Sheet4 2 me uuuuuuuuuiiaalMsssss Ready Average 2 0025 Count521 Sum 1053 Sococososcocsoeocsoecococococococcoacocacaoolel 12 Repeat process for a Seastars Columns AD through AH contain names of seastars b Grazing urchins Columns AJ and AK contain grazing urchins 13 Repeat process for edible shells too Note Here you can just click in the cell AP1 and type Edible Shell Total Exercise 2 Managing and Using Data Guidebook 14 Page a Do the same sum function b See below for confirmation Home Insert Page Layout Formulas Data Review View Add Ins Acrobat Design A Es Connections A dk Clear E Es HE ZE z Show Detail i he 1 Properties X Reapply S L Ll 5 Hide Detail From From From From Other Existing Refresh eee pein rll ee Text to Remove Data Consolidate What tf Group Ungroup Subtotal Access Web Text Sources Connections All Edit Links 2 Advanced Columns Duplicates Validation Analysis v Get External Data Connections Sort amp Filter Data Tools Qutline All Y fe Seastar Total AG AH Al A AK AL AM AN AO AP Lin uildi
86. cts or basically double the amount of work Kosrae had done But let s think bigger picture We can see that several surveys were already completed and perhaps we d like to know on average how did the surveys do at accomplishing their statistical confidence goals Exercise 5 Managing and Using Data Guidebook 57 Page 9 Go back to Excel Delete cells E5 E6 and F5 F6 for now because we want to look at all sites combined In Cell E5 type Overall HC Average In F5 type Overall HC standard deviation In cell E6 type average B5 B28 this takes the overall average of HC In cell F6 type average C5 C28 this takes the overall mean deviation of HC Confirm FMESA FMESA FMEKSA FMEKSA FMESA FMESA FMESA FMESA FMEKSA FMKSA 04111 04113 04115 04120 imksa06110 081101 08112 081121 06116 0614 0616 FMKSA 06161 FIMKSA0818 FMEKSA 06107 FMKSA0819 FMEKSA FMKSA FMESA 13113 13115 1312 StdDev of HC 13 31341031 1 96388221 11 96 83885 5 455197029 5 f 359800722 9 5742 71076 8 539125538 T5 5204164999 36 25 36 875 46 875 3 709450104 12 4 314329 18 41364983 11 43366561 17 60305046 16 3 770334 10 50 33351 f I 2815878 5 65644653154 3 555525189 Overall HC average Overall HC standar deviation 50 52083333 10 62544546 Now note the overall standard deviation
87. derations of the datasets presented below End of Exercise 5 Exercise 5 Managing and Using Data Guidebook 62 Page Exercise 6 1 An introduction to creating report quality graphs and preparing data for univariate statistical analyses So far we have been using Excel to generate our visual graphs because of the easy manipulation of data through the PivotTable and PivotChart functions However once we have completed our initial investigations and have decided upon the influential trends and what graphs best show them we often desire to create professional publication quality graphs for our grant applications and reports In this exercise the Sigma Plot software is introduced This software platform is one easy approach that many research scientists use to generate professional figures and conduct basic accompanying statistical analyses We will make a series of graphs that correspond to investigation of coral reef monitoring trends that have emerged in the Commonwealth of the Northern Mariana Islands CNMI 1 Open Excel a Open the file cnmi inverts example These are macroinvertebrate count data that were collected along 50m x 4m belt transects over the past 9 years Each row corresponds to one individual transect Look at the database and the corresponding metadata sheet to understand how these data are arranged Note that columns G and H will be explained later in this exercise they pertain to our preliminary findings that we wi
88. down menu s and lookup functions were created 4 Do this by clicking in cells A2 across and understand how each function works The formula for fish biomass comes from published studies and each species coefficients comes from a website called FishBase a global initiative to improve our understanding and science surrounding fish and fisheries www fishbase org We are going to be manipulating this database to understand trends in MPA success In the case of any master database no data queries or graphing should be conducted using the same file as the original database Exercise 3 Managing and Using Data Guidebook 27 Page 5 First do a save as and name the file Pohnpei MPA fish transects exercise or any other name of your choosing Pohnpei MPA fish transects exercise Microsoft Excel Table Tools i a Inne 3 Hipposcarus longiceps Yes mer 3 Inner 3 Hipposcarus longiceps Yes eS 0 021061453 0 021061453 0 021061453 0 013092867 0 013092867 0 022959421 0 024694091 0 017005573 0 017005573 0 013092867 0 01306709 0 01306709 0 01306709 0 01447752 0 01447752 0 024694091 0 022236967 0 022236967 v Biomass q 2 974331519 2 974331519 2 974331519 3 137520668 3 137520668 3 02223458 2 955475758 3 042260034 3 042260034 3 137520668 3 122492248 3 122492248 3 122492248 3 121692957 3 121692957 2 955475758 2 970682336 2 970682336 34
89. e bottom of the screen now as your data exceeds a typical screen view In the screen Shot above scrolling to the right was needed The highlighted cells show the last data we just brought over The last step before proceeding to making graphs is to transform our standard deviations to standard errors that are commonly used for graphical representations of our datasets and understanding statistical significance Recall that the Standard Error is simply the standard deviation divided by the square root of the sample size In each instance where a StdDev column of data is present we will change these to StdErr We have to do this manually as Excel does not have a Standard Error function customized for our needs 40 Scroll to StdDev of Acanthaster Column 5 associated with the Yes impact sites Recall that the Count column indicates our sample size so we need to divide the value in StdDev cell by the square root of the value in the Count Do this with a calculator a fresh spreadsheet in Excel or other means of your choosing Exercise 6 2 Managing and Using Data Guidebook 79 Page 41 When completed replace the contents of Column 5 with the standard errors you calculated a Change the name of the column from StdDev to StdErr 42 Confirm for the first set of data below im nm 1 Time Frame mpact 5Sili Count of Acankhasker Average of Acanthaster Md tle HET re Eden Itof Grazing LIrch je of Grazing Urcky
90. e for delta or level of change successfully detected because we set the values for the rest The results suggest that given our sample size and standard deviation we are able to confidently detect a 30 change in coral cover with statistical significance 6 Write the delta value 29 29 on your scratch paper 7 Go back to Excel To understanding what our delta value translates into in terms of percent change lets put our delta value in perspective with our coral cover value a Click in Cell E4 and type the word Delta Exercise 5 Managing and Using Data Guidebook 55 Page b Type in our value 29 29 below in Cell E5 c Click in Cell F4 and type Percent Change Detected d Click in Cell F5 and type the following simple math formula 29 29 63 75 100 This takes our delta value divides it by the total coverage of coral and tells us what percent change we can successfully account for with our sampling design e Confirm a 3 4 Sample ID i otdDev of HC Delta Percent Change Detected 5 FMKSA04111 63 75 13 91941091 29 29 45 94509804 6 FMKSA04113 76 25 17 96988221 7 FMKSA04115 60 625 11 96783885 8 FMKSA04120 70 625 7 465197028 9 fmksa08110 62 5 7 359800722 10 FMKSA081101 67 5 9 574271078 11 FMKSA08112 61 25 8 539125638 12 FMKSA081121 63 75 5 204164999 13 FMKSA08116 70 9 789450104 1 FMKSA0814 56 875 12 47914928 15 FMKSA0816 71 875 1841364983 16 FMKSA08161 76 875 11433685
91. e package R on your computer if you have not do so now R is a computer language and interface program that allows any user to create their own code or instructions for data analysis and user interface A great book to describe H and provide you with plenty of examples is The R Book MJ Crawley 2007 John Wiley amp Sons Inc Here we will only use one simple feature of H to generate statistical power estimates You can navigate to http sekhon berkeley edu stats html power t test html to understand the code or package that we will use We will again use Excel as a basis for our inquiries Open the file Kosrae benthic data example Exercise 5 Managing and Using Data Guidebook 51 Page B O ire M A 1 Eee A pica sueno E dc Bo R I m Inc Bra E gt T MSP JB Boo Bo E ls E ha E ho E or ram 2 FMKSA04111 1 9 21 2005 0 0 0 0 0 5 62 5 0 0 5 0 0 3 FMKSA04111 2 9 21 2005 TE 7 0 0 0 0 25 0 T 0 0 0 0 0 gt 4 FMKSA04111 3 3 21 2005 125 0 0 0 0 0 5 0 T 5 0 0 0 0 0 0 5 FMKSA04111 4 9 21 2005 17 5 0 0 0 0 0 5 5 45 0 0 0 0 0 0 5 FMKSAD4113 1 9 22 2006 125 0 0 0 0 0 0 5 72 5 0 0 25 0 0 0 f FMKSA04113 2 9 22 2006 12 5 0 0 0 0 0 0 0 87 5 0 0 0 0 0 0 8 FMKSA0A113 3 9 22 2006 5 0 0 0 0 0 25 0 925 0 0 0 0 0 0 9 FMKSA04113 4 9 22 2006 15 0 0 0 0 0 17 5 2b 52 5 0 0 0 0 0 0 10 FMKS404115 1 9 26 2007 125 0 25 0 0 0 0 10 60 0 0 0 10 0 0 11 FMKSA04115 2 9 26 2007 125 0 0 0 0 0 0 1 5 7s 0 0 25 0 0 0 12
92. e with reef type wave exposure depth site and transect name Note GPS data is automatically entered for you This is because of our lookup table Time to enter our ecological survey data of the macroinvertebrate abundances There are two approaches commonly used The first is especially relevant for count data that has been collected without individual sizes such as counting the numbers of sea cucumbers but not measuring the length of each one 26 Highlight cell 12 a Type in the name of one common sea cucumber Holothuria atra then push enter Note excel automatically extends your list to include column I 27 Enter numbers of sea cucumbers encountered for each transect ren rat You can just enter values of your choosing A mc 1 Island E reef type El wave exposure EA Depth El site mrT DITNENERNN CT OT 2 Chuuk Channel Low C 1 1 151 8706333 7 429866667 25 3 Chuuk Inner Moderate C 11 1 151 788 7 369016667 33 4 Chuuk Inner barrier Low 3m C 13 2 151 5917333 7 47655 5 Etal Outer barrier Sheltered 3m C 14 2 151 58505 7 4684 6 Losap Outer barrier Moderate 3m c 13 3 151 5917333 7 47655 7 Losap Channel High 10m c 14 3 151 58505 7 4684 8 Murilo Inner barrier Low 10m C 16 4 151 4476667 7 396533333 9 Etal Channel Moderate 10m M 4 4 153 7878667 5 531483333 10 Chuuk Inner Moderate 10m M 9 5 153 5405833 5 404633333 A 11 Satawan Outer barrier High 3m C 12 3 151 5751 7 471166667 Exercise 1
93. ed prior to examining the data or apriori This is fine because were interested in examining the cascading impacts to the grazing urchins and eventually graph affinities with coral reef recovery 62 Go back to our Sigma Plot data sheet Data 2 63 Go to the Graph main menu from Sigma Plot a Select Create Graph 64 Choose Vertical Bar Chart a Click next 65 Select Grouped Error Bars a Click next 66 For Symbol values a Make sure Worksheet Columns is selected in the drop down menu b Click next 67 For data format choose X Many Y a Click Next Now Sigma Plot is again ready for our data 68 For our X data choose the first column Time Frame 69 For Set 17 a Choose Average of Grazing Urchins values associated with Yes impact sites This is column 7 70 For Error 1 a Choose the associated standard errors we just calculated in column 8 Now were ready to enter a second set of data 71 For Set 2 a Choose Average of Grazing Urchins values associated with No impact sites This is column 15 72 For Error 2 a Choose the associated standard errors we just calculated in column 16 73 Click Finish Exercise 6 2 Managing and Using Data Guidebook e3 Page 74 Confirm 2D Graph 2 A planci abundances in the CNMI Average A planci density per 100m After Beara ara During ENS Average of Grazing Urchin Total2 EJ Average of Graz
94. ef type a A Inner y Outer S66 S68 S109 s 2 A 535 S128 v amp E A s25 q X3 S10 41 e S44 S108 A yd US S157 Aste SERS Spe Rea TOR EZ S126 y AT e 9 i qu A S90 QUT E og 4 43847 A ISR A ORD S86 y WESEL S A S57 S1 E SOO vw 3 193 SA S87 i os A O12 S156 192 A wy 45128 y 4 M S40 S158 Y v Exercise 8 Managing and Using Data Guidebook 130 Page We can see that our outlier transect is S126 Note that on your scratch paper 29 Move back to our data file after the log transformation a On the left make active the Data sheet b Rename to log transformed 30 Confirm P PRIMER 6 log transformed m File Edit Select wiew Analyse PERMAMOVA c Tools Window Help Do gt bl Es E ER Fahnperfish MPA PERMANUNVA eser E ff FahnperMPA fish PERMANLUSA e fo Overall Transform Alamass ea zl log transformed Resemblancel Reseml a f MDs 9 Graph 9 Graph2 Now with this sheet active 31 Highlight all the data by clicking in the box above Sf and to the left of Acanthurus lineatus The data sheet should change color 32 Scroll down to S126 a Click on that row That row should change to a different color Exercise 8 Managing and Using Data Guidebook 131 Page 33 Go to the Select menu on top a Scroll down to Highlighted Now you have a new datasheet with the outlier data removed ready for further analy
95. elds on the PivotChart B Choose fields to add to report Holothuria atra _Holothuria edulis Holothuria fuscopuntata _ Pearsonothuria graeffi _ Stichopus chloronotus Stichopus hermanni EIrheenota ananas Thelonota anax v Sea Cucumber Total E Echinaster luzonicus E Acanthaster planci Culcita novaguinea Linckia guildingi E Linckia laevigata Seastar Total Echinometra Report Filter ij Axis Fields Categories Island Ed Legend Fields Series 10 Values E Values Average of Sea Cucumber Total StdDev of Sea Cucumber Total E Average of Sea Cucumber Total W StdDev of Sea Cucumber Total Drag fields between areas below Y Report Filter Cj Legend Fields Y Values z dE Axis Fields Cat Island Z i o Tia o A Chuuk Etal Kuop Losap Lukunor Murilo Nama Nomwin Satawan blank T 2b zx C Defer Layout Update 44 gt M Metadata Chuuk REA Invert Summa Chuuk REA Invert Pivot NAT All Ready Exercise 2 Managing and Using Data Guidebook 20 Page Now we can take a moment to reflect upon what the data is telling us First on average there was no site surveyed in Chuuk that had more than 4 sea cucumbers per 5 minute swim a very low value compared with other REA reports conducted in similar habitats and depths Second Chuuk has the greatest abundance of se
96. em1 ajaja A File Edit Select View Analyse PERMANOYA Tools Window Help Bx Dow E AR XR A DIES DIEN T cs Yr ap multreariate Fish exercise Hr Yap Mimpal MPA Fish PHworking VOID 2s Overall Transform similarity 0 io 100 sf j foe fs fem s ss s ss fs ss dsw se wolf Reser ss 52 343 53 39136 39 776 54 352 19 837 72 432 37 025 61 473 633 65 228 33 462 22 828 1872 51 46 10 638 44 4 gt Exercise Managing and Using Data Guidebook 106 Page Now we have a data matrix that compares every possible combination of transects and provides a distance measure of ecological similarity for each comparison Note that we could have used many different similarity indices besides the Bray Curtis you can learn about these and when they are appropriate from your user manual From here we want to visualize our findings PRIMER again has many options for the user to consider We will use the most common visualization method called Multi dimensional scaling Through this process the distances we calculate between each pair of sites are all overlaid in the same multi dimensional space The computer then reduces the dimension of the resultant plot down to two or three while preserving as much of the structure in the data as possible It is best understood through an example and the math behind this can be found in the user manual 43
97. ence 2009 Channel 10m 4 0 1394 48 0 0 0 0 1484 777 0 302 168 0 0 0 0 0 0 31 Gachuug Reference 2009 Channel 10m 2 0 1218 561 0 0 O 954 0168 759 1879 0 0 0 0 0 405 4788 32 Gachuug Reference 2009 Outer 3m 1 2790 589 0 0 0 0 0 2698 196 1339 763 480 38 O 98 37189 0 0 0 0 M 4 gt M Fish Innkun tahle Yan fish nivat Nimnal Gachuun fishdata Primer Prenare Yan Fish 7 4 i We have one last step before we can import our file into PRIMER E We must remove the metadata from the ecological data It is important from this point forward to not change the order or appearance of the data until we successfully import into PRIMER E 17 Highlight all of the fish biomass data Cell G1 and all the way to cell AB76 18 Right click the highlighted cells and select copy 19 Create a new Excel Sheet Exercise 7 Managing and Using Data Guidebook 96 Page a Paste these data inside b Rename this sheet PRIMER import c Confirm WEIR Yap Nimpal MPA Fish PHworking Microsoft Excel Home Insert Page Layout Formulas Data Review View Add Ins Acrobat F y Cut H Eu mh PEN X Autosum a Calibri gt Aa ail 9 icd Text General Y Normal 2 ijs iu mi Ea AY T opy v I ill ae BZ U I U EEE y z tad Merge amp Center MEL gt 58 28 Conditional Format Bad Insert Delete Format f Sort amp Find amp F Format Painter o gt A gt ESSE yq EE Formatting as Table T r A Clear Fi
98. ercise 7 o 9 0749 o 6 9327 TOM T 1062 o D 2 A oa 0 coijojojocojcocjo jcoc io cQ ojlojjojijcojlcojojo jo zcGB 5 4169 0 0 0 3 3545 ado 0 1301 Managing and Using Data Guidebook o o 4 6195 5 5835 5 0327 o 6 0617 o D 2 A O 4 186 5 518 3 324 5 7143 6 6356 3 22r 5 0746 3 3795 S437 44177 cQuJgoejojojojojojajojzcoc Acanthurus lir Acanthurus ni amp canthurus tr Caranz melam Cephalophalu3 Cheilinus unduchlorurus mici Chlorurus son Ctenochaetus Epinephelus m Epinephelus my Grouper o 3 0177 OO O O O O O O OO JO A AO D 6 2201 9 7634 Hipposcarus likyphosu 5 6386 O O O AAG O Lutjanus gibby Lutjanus tt cC ojlojojicoijcojoojojo zc3B 0 3 Fab 0 0 6 0075 5 5061 6 5396 119 Page 84 Go to the Analyze menu a Select SIMPER which is short for analyses of similarities SIMPER Design Measure One way 2 Brap Curtis similarity Two way crossed Euclidean distance Factor List only higher cantributing variables Cut off percentage Factor B p q cite 85 Under Factor A a Select Site from the drop down menu SO we can determine differences can leave the default settings that match our MDS plot generation and 86 Click OK 87 Confirm Bs m ak E E Mara Groups Gechuug amp Nimpal Average dissimilarity 7 Group Gachuug Group Nimpal species Ay Abund v bund av Diss Diss SD Con
99. ew main menu a Scroll down to Toolbars and make sure Statistics is highlighted You should see a Statistics toolbar appear it has a yellow light bulb icon and a drop down menu next to it 85 In the drop down menu a Scroll down to One Way ANOVA b Click on the magic wand Icon next to the drop down menu The first step is to define our data format c Select Mean Size Standard Error to match our data 86 Click next Exercise 6 2 Managing and Using Data Guidebook 86 Page Now we are asked for our dataset First we will test whether or not grazing urchin abundances differed at the Impact sites during the different time periods 87 When asked for our Data for Mean a Choose column 7 or Average of Grazing Urchin which corresponds to average abundances within our impact sites 88 When asked for our Size remember this is sample size a Select our Count data located in column 6 89 When asked for Standard Error a Choose column 8 90 Click Finish 91 Confirm One Way ANOVA Data Format Daka Format Select the Format of your data I Ske Mean 3 SEM Mean Size Standard E n qm aer The MSE Format 5 00 8 10 na places the 5 00 400 100 mean sample size and std dew in separate worksheet columns The informational box tells us that Treatments are significantly different meaning that urchin abundances are significantly different among the time fr
100. f information is typically required or at least desired and all of that information resides with multiple people or agencies However Micronesia s coral monitoring programs are often limited in personnel and capacity so learning to do the best possible job with the resources at hand is a logical outcome There is no one right way to format a database several different approaches can lead to similar outcomes However some basic rules do apply When deciding upon how to format a database the first question that one should ask is what is being measured and what is the unit of replication Two examples below will show different approaches Example 1 Macroinvertebrate data collection from Chuuk Rapid Ecological Surveys Rapid ecological assessments were conducted in Chuuk during August September 2008 In this example we will build a desirable Microsoft Excel database to store the quantitative macroinvertebrate estimates that were made 1 Open Excel 2 Select and open the file Chuuk REA invert database from the example file directory on your computer 3 Click on the first sheet Brainstorm metadata fields to understand the nature of data collection Data were collected from 8 islands among a variety of reef tyoes and wave exposures At each site surveyed two depths were examined for macroinvertebrate abundances along 5 transects A transect consisted of a 5 minute timed swim The observer recorded all conspicuous macroinvertebrates tha
101. greater influence compared with Location alone This is not surprising either as our initial investigation of the MDS plot also suggested this Third we see that MPA status did not consistently predict any variance in fish biomass Again given what we found out in Exercise 3 and 4 where we noted that some MPA s were indeed successful and others not as much this is expected especially when looking at all of them together Finally and notable there was a significant amount of variance explained by Site This means that in the majority of instances sites within specific Reef types and within a specific MPA status can be quite different Thus it would be incorrect to lump the data from the two sites together to judge MPA status Rather we should consider each site independently Below the table of results you can find descriptions of the numerical models that best fit our dataset Last the final Estimates of components of variation table shows us what the relative influence of each variable is similar to estimates of individual ANOVA sum of Square means Individually you can see the greatest components of variation exist at the Site and Reef type level again supporting our initial MDS plot analysis The user manual can help to understand these computational terms better here we highlight the bottom line findings and follow logical steps suggested by our sequential analysis So what do we know
102. gt 50 cm 19 2 801 6671508 825 504714 20 3 928 3573877 21 4 250 0795945 22 5 3943 030976 Drag fields between areas below 23 8DO2 65 04853355 47 04212976 41 63864716 Ww Report Filter Column Labels 24 1 410 4122884 42 20930013 Ganin 25 2 11 52533634 2 651510778 41 63864716 26 3 38 20208402 21 42392402 27 4 60 24855971 71 45131273 28 5 426 7695095 29 SLI 280 3425293 260 3033523 639 9070265 289 1984117 303 4473064 30 1 3285 179365 16379 22135 847 3206118 31 2 12003 95724 429 7563591 302 9879514 LER ro wu 32 3 1103 600024 1613 431607 1768 023349 458 8361116 1248 546398 Sample ID y Sum of Biomas Y 33 4 4839 145245 3199 695914 288 4757995 459 0088779 Replicate x 34 5 298 1126228 934 6812259 564 1745507 417 0352246 35 LI2 738 3981727 161 2357242 335 7776315 3 36 1 307 8528241 37 2 69 32168018 38 3 751 4755651 C Defer Layout Update PNP fish pivot chart PNP fish pivot PNP Fish Database Shee illl Now we have a spreadsheet with each replicate transect as a row and a total amount of fish biomass recorded on each transect hence the sum instead of average function This is exactly what we need to examine transect level replication total sums of biomass for each species along each transect M4 Site information 4 Right click anywhere in the table and go to Pivot Table Options a Make sure the Layout amp Formart tab is selected and put a 0 in the box next to For empty cells show 5 Click on the Totals
103. h we will do in a later exercise 32 Repeat steps 26b 30 and look at Naso unicornis another influential fish we examined before Does the situation differ Try a few other fish as well Discuss conclusions Exercise 4 Managing and Using Data Guidebook 47 Page Clearly at the individual species level there is too much variation in the data to be able to detect significant change over time with statistical confidence However we shouldn t worry fish assemblage data are naturally multivariate in nature That is there are many species that make up the total biomass on any given transect and perhaps we should try to account for all of them simultaneously rather than individually one by one In a later exercise we will analyze the multivariate properties of fish assemblage data Here we will attempt a couple last steps to see if we can utilize some properties of the univariate fish dataset 33 Click in cell W1 a Name this cell Total Biomass 34 Click in cell W2 a Type the following function sum b Highlight all cells in the 27 row with a fish name on top of them Excel should autofill the entire column once you hit Enter 35 OD Notice Excel automatically includes this as part of your data table and the colors change a nao DIT Pohnpei MPA fish transects exercise Microsoft Excel Table Tools Ea l aril inet j E i MU whe Ag m_ mo mE A de ma emm ho A a m e W
104. he same for the Site column but change the source as follows Data Validation settings Input Message Error Alert Validation criteria Allow deg In cell dropdown Source Metadata 442 0457 Es Apply these changes to all other cells with the same settings Note that we reference a different sheet the Metadata sheet now a Click on that sheet and verify why A2 A57 were selected b Populate the database with values of your choosing Exercise 1 Managing and Using Data Guidebook GPS y 5 Page 16 Now for the next column Transect we can again do the list function or we can simply write the numbers 1 5 You choose and populate the cells 1 al a CO J un A Ll a 10 11 F2 A Island Chuuk Chuuk Chuuk Etal Losap Losap Murilo Etal Chuuk B Reef type Channel Inner Inner barrier Outer barrier Outer barrier Channel Inner barrier Channel Inner fx 1 Wave exposure Low Moderate Low Sheltered Moderate Hi g h Low Moderate Moderate D Depth im im im im im 10m 10m 10m 10m Site C 1 C 11 13 C 14 C 13 C 14 C 16 M 4 M 9 Transect GPSx GPS y Ul b J4 amp UJ UJ MJ oe Now for the fields GPS X and GPS Y we will use a lookup function because there are too many numbers in the GPS coordinates to try and process through a dropdown list 17 Click on cell G2 a Type the following in the function t
105. his entire row a Rename cell 1 1 from COTS to Time Frame The values below indicate our time frame of observation 18 Right click on Column 1 a Choose Column Titles 19 Promote the column headings to titles for all 5 columns 20 Confirm SigmaP lot Data 2 File Edit Insert View Format Tools Graph Statistics Transforms Toolbox Pharmacology Window Help 014480120 LITERE SL ES S 3D 00 _ Ol Eg 2 One Way ANOVA CO ES mi i m 1 Time Frame 2 Impact Sites 3 Count of Acanthaster 4 Ayerage of Acanthasterz 5 StdDev of Acanthaster3 6 After Yes 45 0000 0 1556 0 3341 E d 2 Before Yes 42 0000 0 0714 0 2361 n id 3 During Yes 96 0000 1 2917 2 3052 Data 1 gt li Graph Page 1 z E TJ Section 3 b Data 2 7 A Now we need to import the data from the sites where COTS abundances showed no increases over the disturbance years 21 In Excel change your Impact Sites filter to No 22 Copy the entire table 23 Go back to Sigma Plot leave columns 6 7 and 8 blank for later use b Right click on the first cell in Column 9 and select Paste c Highlight only the cells you want to include in your data starting with COTS in the upper left and 0 2928 in the lower left corresponding to cells 9 2 and 13 5 24 Cut Ctrl X the highlighted data a Paste them one row up cell 9 f 25 Rename cell 9 1 from COTS to Time Frame Exercise 6 2 Managing and
106. ht click inside the chart and move it into its own spreadsheet b Rename the sheet Pivot Graph PNP fish transect c Confirm P j PivotChart Filter Pane X PivotTable Field List X 8000 Active Fields on the PivotChart a Choose fields to add to report 24 Report Filter Acanthurus xanthopterus 7000 Caranx melampygus i Axis Fields Categories Cephalopholis argus Sample ID Chlorurus microrhinos y Hipposcarus longiceps 6000 Legend Fields Series Lethrinus harak Values 2 Values Lutjanus fulvus Lutjanus gibbus 5000 Average of Hipposcarus longiceps Monotaxis granoculis Naso lituratus Naso unicornis StdDev of Hipposcarus longiceps2 Lutjanus monostigma O dl Parupeneus barberinus Siganus doliatus i Siganus puellus E StdDevof Hipposcarus longiceps2 Siganus vulpinus 4000 B Average ofHipposcaruslongiceps ss fields between areas m VF Report Filter E Legend Fields E Values vi E Values xl 3000 2000 Axis Fields Cat E Values 1000 DI1 Dl DO1 DO Kil Kl2 KO1 KO2 LIL Li LOL LOZ Mill Miz M01 MOZ Nil NI2 NO1 NO2 Defer Layout Update PNP Fish Datahase Pivot Granh PNP fish transect Pivot PNP Fill iil We can clearly see that the standard deviation surrounding these parrotfish estimates for each site is higher than desired and there is no need to proceed with calculations of statistical power whic
107. ing Urchin Total2 Notice the second graph was created directly on top of our existing graph We will first re arrange our charts 75 From the zoom drop down menu select 50 a Drag the chart we just created to the bottom of the sheet b Drag the Acanthaster graph to the top Note Arrange them neatly Now let s clean up our grazing urchin chart Exercise 6 2 Managing and Using Data Guidebook 84 Page 76 Rename the title to Grazing urchin abundances in the CNMP 77 Rename the Y Data to Average urchin density per 100m2 78 Delete X Data 79 In the Legend a Double click the text next to the black box and rename it to Impact sites b Double click the text next to the grey box and rename it to Non impact sites 80 Drag the legend anywhere inside the graph Note You can remove the upper line associated with the graph and the one on the right too if you like just for appearance 81 Change the zoom drop down menu to Fit 82 Confirm our new look of the two graphs A planci abundances in the CMMI BEBE mpact te E Hei mpact z tes Average A planci dereity per 100m2 Darke Grazing urchin abundances inthe CMM Average urchin density per TOi Darke Exercise 6 2 Managing and Using Data Guidebook 85 Page Consider these very interesting results At the impact sites where COTS abundances were high we have noted what seems to be a significant decline in grazing urchins It appears that whe
108. ion Note It s a good idea to save your work at this point 14 Check the drop down menus for SamplelD Replicate MPA and Reef type these are cells A4 B4 C4 and D4 Note Make sure no filters are on and all boxes have a green check mark 15 Click anywhere inside the pivot table a Press the Ctrl A buttons on the keyboard to select all the data b Right click again and select copy 16 Click the Excel worksheet named Sheet 1 a Click in cell Af Exercise 4 Managing and Using Data Guidebook 40 Page Right click and select Paste Special Select Values Click OK Confirm below e295 Paste Special Paste C all C2 All using Source theme 2 Formulas All except borders C2 Column widths Formats Formulas and number Formats Comments Values and number Formats Validation Operation 2 None Multiply C3 Add Divide C3 Subtract Skip blanks Transpose You should now have a new formatted sheet Confirm below Exercise 4 Managing and Using Data Guidebook 41 Page 41 Pohnpei MPA fish transects exercise Microsoft Excel General E E Normal 8 Normal 9 l En p Wi 2 Pe lit 33 Copy maig jg Fill gt or F Format Painter o 28 EE oe Bad P ird is dn di cp Clear Clipboard T4 Number Ta Styles Cells Sum of Biomass 8 MIA E C D E lo K M N o
109. k to the Edif menu and select Factors Let s look at the same channel reefs this time at the 10m depth Exercise 7 Managing and Using Data Guidebook 112 Page 61 On your scratch paper record the relevant sites we want to highlight S6 S70 S26 S30 S41 S45 and S61 S65 Note You can deselect the undesired samples by clicking on them and select the new samples noted above 62 From the Select menu select Highlighted 63 Confirm es ap multiariate fish exercise E Fy r ap Mimpal MPA Fish PHwoarking E E Overall Transform SIDES Variables E T 3m channel transtormed e Eissemblanest Acanthurus lir amp canthurus ni amp canthurus tr Caranx melamCephalopholugCheilinus undy Chlorurus mici Chlorurus son Ctenachaetus Epinephelus mEpinephelus m Srouper Hipposcarus I amp vphasus Lutjanus aibbuLutjanus ir Ef Resem S5 252 91 576 22 326 17 20 785 B m MOST S7 183 47 1063 5 456 25 653 33 o D e Graph 587 a 344 13 359 85 o 0 Em Graph 591 443 56 410 01 344 13 259 55 o siMPERI 510 n 158 95 0 n 0 0 n 1526 8 347 75 510 25 188 35 5261 158 35 100 44 64 756 0 oO 294 75 248 14 0 19 443 250 06 40 331 1024 2 224 17 65 324 152 34 2B 772 1394 5 1484 8 302 17 o 0 12
110. lication or other type of summary that may be necessary 42 Save your file for future reference then you can close it End of Exercise 4 Exercise 4 Managing and Using Data Guidebook 50 Page Section 2 Univariate Statistics and graphing the results Exercise 5 Simple calculations of statistical power for influential dependent variables Statistical power is defined as a probability 0 to 100 that data we collect will be able to detect a desired level of change in the abundance or density of coral fish or invertebrates in question If we take just a few measurements our standard deviation will be high and our power Will be low However when do we know enough is enough so we can balance our logistical and financial constraints with our data needs Obviously 0 power is not desirable but 100 is equally unattainable unless sampling effort is increased beyond realistic levels Studies agree that power should be 70 or higher for detecting a relative 20 30 change in the resource abundance in question coral fish sea cucumbers etc Here we will conduct some very basic power calculations using the free software R http www r project org Of course the topic of statistical power is well developed in the scientific literature and references are easily attainable from the Google Scholar search engine Here we will touch upon the subject for our needs of assessing data confidence You should have already installed the softwar
111. ll Acanthaster boxes from under the Values 29 Drag the Grazing Urchin Total box under Values three times a Change the attributes of the first Grazing Urchin Total box to Count b Change the second to Average c Change the third to StdDev 30 Confirm iud Row Labels Values COTS Impact Sites Count of Grazi Average of Gr StdDev of Gra Defer Layout Update Exercise 6 2 Managing and Using Data Guidebook 77 Page 31 Copy the relevant data cells in Excel 32 Return to Sigma Plot a Paste these data cells below our existing tables choose cell 6 10 33 Confirm m 1 Time Frame mpact 5ili Count of Acanthastel Average of Acanthasteri StdDew of Acanthaster 6 T 8 Time Fran Impact 5i 1 Count of Acanthaste Awerage of Acanthaste SEdDev of Acanthast 1 After Yes 45 0000 0 1556 0 3341 After Ma 80 0000 0 1188 2668 2 Before Yes 4 0000 0 0714 0 2561 Before Mo 159 0000 0 1132 0 4462 3 During Yes 96 0000 1 2917 2 3052 During Ma 181 0000 0 1133 0 2925 4 5 6 T g J 10 D i Impact Sites Count of Grazing Average StdDey o 11 After Yes 45 0000 4 222 3 2710 12 Before Yes 42 0000 4 5476 5 0556 13 During Yes 96 0000 2 02833 3 09864 14 Notice the first two columns are the same and already are presented in columns 1 and 2 34 Highlight just the data from Count of Grazing to the number 3 0964 a Cut and paste these data under Column 6 35 Promote the c
112. ll go through CNMI s program has experienced several years of higher than average Acanthaster planci abundances and associated coral damage We will use the collected data to understand what has happened and what potential consequences and management actions are 2 Make a PivotTable a Rename the new worksheet CNMI invert pivof 3 Drag Year under Column Labels a Scroll down to Acanthaster and drag it under Values b Left click on it and change the Value field setting to Average 4 Drag Site Date and Transect under the Row Labels in that order 5 Right click anywhere in the PivotTable and a Select Pivot Table Options 6 On the Layout amp Format tab put a check next to the box For empty cells show a Puta 0 zero in the space Exercise 6 1 Managing and Using Data Guidebook 63 Page 7 On the Totals and Filters tab uncheck Show grand totals for columns and rows 8 Right click in cell A4 and go to Field Settings a select None under subtotals 9 Repeat Step 8 for Cells B4 and C4 10 Confirm Ox vel PivotTable Tools cnmi inverts example PHworking xlsx Compatibility Mode Microsoft Excel m x Li sag SS Home Insert Fage Layout Formulas Data Review View Add Ins Acrobat Options Design x E j Cut a mmm E AutoSum Arial 1 cua A A i Ep Wrap Text General 57 Normal
113. lter Select Clipboard a Font F Alignment Number Styles Cells Editing gt B Al f Aemuuslneatus OOOO n v E T Acanthur JAca nthurt Acanthuri Caranx me Cephalopl Cheilinus Chlorurus Chlorurus Ctenocha Epinephel Epinephel Grouper Hipposcar Kyphosus Lutjanus g Lutjanus n Macolor m Monotaxi Plectorhir Scarus fre Scarus glo Scarus sp 2 0 0 0 0 0 0 0 0 247 9478 0 0 0 0 0 0 0 0 0 0 648 0856 0 0 449 9913 0 O 127 7607 0 0 0 406 2062 0 0 0 0 0 0 0 0 0 333 8632 1430 281 42 28806 155 5967 132 629 0 0 0 0 0 0 387 3948 0 310 7563 0 0 0 0 0 0 0 0 0 0 5 0 0 0 0 0 0 0 0 1310 312 O 48 79071 0 0 0 0 0 0 0 527 1049 574 0373 70 11343 6 709 2205 0 0 0 0 55 19581 0 0 415 0813 0 0 0 0 579 1653 0 0 0 0 143 4671 317 4599 0 0 0 0 0 0 0 0 252 9121 576 2208 326 1714 0 O 20 78538 0 0 0 0 0 0 0 67 07081 8 0 0 0 0 O 183 4658 0 1063 511 456 2791 0 0 0 683 3266 0 0 0 0 0 0 0 234 2396 9 0 0 0 0 0 0 0 344 1298 359 8519 0 0 0 0 0 0 0 0 O 1813 636 0 210 0527 10 0 443 8639 0 0 0 410 0111 0 344 1298 259 5631 0 0 0 0 0 0 0 0 0 0 0 0 11 O 158 9545 0 0 0 0 0 1526 806 347 7459 510 2472 0 O 188 3515 0 0 0 0 0 0 0 545 2556 12 0 0 0 524 5821 0 0 0 0 718 8561 0 0 0 0 0 0 0 0 0 0 5219 694 0 13 637 0112 0 0 292 8198 0 0 0 0 703 0403 0 0 0 0 0 0 0 0 0 0 0 0 14 0 0 0 0 0 0 0 0 2378 361 0 0 0 0 0 0 0 0 0 O 1615 408 0 15 3858 568 0 0 0 228 1668 0 0 0 2132 761 0 0 0 0 0 0 0 0 0 0 949 0819 0 16 4526 74 0 0 0 127 7607 0 0 0 2207 123 0 0 0 0 0 0 0 0 0 O 1774 951 0 17 0 0 0
114. lter Pane window click on the drop down menu for Depth and leave only the 3m depth highlighted 25 Confirm below IP scm sm EA TARA Formatting as Table m ll 7 amp Clear 7 Filter Select Clipboard Ta Font ES Alignment Number Styles Cells Editing i gt Fe IT PivotChart Filter Pane X PivotTable Field List Total E Active Fields on the PivotChart _ 35 Report Filter i Axis Fields Categories l o 25 HH Legend Fields Series Bean Tridacna crocea Values Average of Sea Cucumber Total ges 20 Crinoid Lobster all spp JOctopus Actinopyga mauritiana loas i Bohadschia argus mE MES B l yo MES l ad 5 l 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 C 10 C 11 C 12 C 13 C 14 C 15 C 16 C 17 Cc 18 C 12 C 20 C 21 C 22 0 23 C 27 C 28 C3 C 4 C 5 C 6 C 7 C8 cs Chuuk AE o A Metadata Chuuk REA Invert Summary Chuuk REA Invert PO 2 ady Exercise 2 Managing and Using Data Guidebook 22 Page Three sites seem to stand out as holding relatively high abundances of sea cucumbers for Chuuk these are C 5 C 15 and C 11 Look at the map below to understand where those sites are 0 20 40 mmm O Kiometers Not surprising the highest abundances were found on Chuuk s inner reefs adjacent to i
115. mass and abundance data Exercise 7 Managing and Using Data Guidebook 103 Page 39 Select the Analyze menu 1 Go to pre treatment 2 Select Transform overall 3 Select Log X 1 from the drop down menu 4 Click OK 5 Confirm Below Overall Transform Transfarmatian Logl 1 he You have now created a new species by site datasheet you can see on the left that the current name is Data1 40 Rename this to 3m channel transformed P PRIMER 6 3m channel transformed p File Edit Select View Analyse PERMAMOVvA4 Tools Window Help D c iS ls BS Bl O uos BET eh T ap mullivarate fFish exercise gm T ap Mimpal MPA Fish PHwarking a Overall Transform SUE variables n 3m channal transfarmed Acanthurus li Acanthurus nij Acanthurus tr Caranx melam Cephalopholug Cheilinus undy Chlorurus micichloruruz ore Ctenochaetus Epinephelus m Epinephelus mira zi bL i 0 D D D D 0 D 5 5172 D 0 52 0 61114 o o 4 858 o o 0 6 0093 0 o s3 5 0537 4 8951 al of 0 0 o 0 5 962 D 5 7422 q ol ol Dl ol n p 0 ol 71788 ol 3 9078 S5 6 5656 0 o o of 4 0288 0 D 6 0309 D o 511 D 0 0 625 o D ol 0 65791 Bi 0 512 B4584 o D 5 683 o o o D 6 5568 D 0 513 l i o EI JE n 0 O TTMBS ol n Exercise 7 Managing and Using Data Guidebook 104 Page We will now proceed and create a similarity matrix A similarity matrix compares each individual s
116. may be rotated differently however the spatial distances between sites should be the same 2009 2009 A 2999 2009 2007 A 2087 v 2007 2007 2007 200 2007 be 2007 5997 2007 A y 4 Exercise Managing and Using Data Transform Log X 1 Resemblance S17 Bray Curtis similarity 2D Stress 0 22 Site A Gachuug w Nimpal 2009 v 2009 Y 2009 Y 2009 2009 Y Guidebook 109 Page Take a moment to reflect what we learn by this graph First in the upper right corner we see 2D Stress 0 22 This tells us how successful our MDS plot has maintained the actual ecological distances between each transect while transforming the output into only 2 dimensions The user manual provides references to research that suggest that values of 0 25 or below are typically considered sufficient and reliable So we have successfully portrayed our data into 2 dimensions and don t need to look at the 3 dimension graph unless your interested Most notably however the graph tells us that for inner channel sites 3m fish biomass data have changed for the Nimpal site between 2007 and 2009 This is not true for all transects but for many the trend holds However for Gachuug the fish biomass did not change So we have indication that change occurred only at Nimpal but we need to understand what the change is Next we will calculate the contribution of each species of fish to our detected trends PRIMER has a built in analyses th
117. mber of permutations 999 Factors Name Abbrev Type Levels Location Lo Random 5 Reef type Fe Fixed MP 1 MP Fixed ite Si Random Ja PERMANOVA table of results Unique SOuUrce df mm MS Pseudo F Piperrz perms Lo 4 41328 10332 B poe TEL 0 001 gug Re Lol 3 35835 11945 SLE RS n O 001 ggg MP Re Lo o 41206 5160 05 Loa mg ds al een 296 31 MP Re fLo 11 16 64046 4002 9 2 63526 0 001 999 Res ea ley v mee alm mela 150 4 Total log Sto DES The resultant data sheet starts with a summary of what you did to your dataset and what type of factors you have and how they were nested Next and most notable you will find the PERMANOVA table of results You can read this just like you would read an ANOVA table output To better understand the calculations and logic refer to the user manual Exercise 8 Managing and Using Data Guidebook 140 Page Here we See that Location was a significant predictor of the fish assemblages suggesting significantly different assemblages exist within each village location when looking at all transects together regardless of reef tyoe or MPA status This is somewhat surprising considering our initial look at the MDS plot did not reveal easily interpretable differences Nonetheless the formal test of significance is our most thorough evidence Second once location is accounted for we also see that Reef type is a significant predictor of fish assemblages and the higher F Statistic suggests its
118. mn 13 53 Click Finish 54 When the graph appears change the zoom from 50 to 100 in the drop down menu on top of the screen 55 Confirm 2D Graph 1 Before During A Data EN Average of Acanthaster ET Average af Acanthaster2 Exercise 6 2 Managing and Using Data Guidebook 81 Page Now some quick changes to our graph appearance 56 Change the title to A Planci abundances in the CNMP 57 Change the Y Data to Average A Planci density per 100m2 58 Delete X Data 59 In the legend box a Double click the text next to the black box and rename it Impact sites b Double click the text next to the grey box and rename it Non impact sites 60 Move the legend anywhere inside the graph Note You can remove the upper line associated with the graph and the one on the right too if you like just for appearance 61 Confirm our new look A planci abundances in the CNMI EN impact sites E Nor impact sites CM C a L NE ME E un ch Eu n Z a aL L e C e LL AE gt L Before Exercise 6 2 Managing and Using Data Guidebook 82 Page Now we have a very informative graph that is clearly showing a major increase in COTS abundances during the disturbance years at the sites we consider to be impacted as compared with all others Note we can t run a formal statistical analyses on these data because our groupings impact or no impact were not defin
119. n file icon b Click the arrow to open the drop down menu next to Files of type c Set this to Excel Files of type All PRIMER Files pk pri sid aga ppl pw All PRIMER Files pwik prz gids aga ppl ppd E PRIMER 6 amp 5 Files pak ori sid aga ppl pp PRIMER 4 Files prod z sim dis Excel Files I lex sb lem 22 Select your Excel file and click open 23 Click on the dropdown menu for Excel worksheet a Select Primer import Excel File Wizard Yap Nimpal MPA Fish PHworking xlsx b Make sure Sample data is checked 24 Click Next Title Data type Row labels Abundance Note We did not include a title in our Excel sheet area 9 Biomass C3 Samples az columns 3 Environmental 25 n the next dialog box 9 Samples as rows Unknown ather a Uncheck the green mark next to Title Blank Note We also did not include Row labels in our Excel sheet Missing value C Zero b Uncheck the green mark next to Row labels c Select Samples as rows as our data are aligned in 26 Select Biomass for the type of data 27 Click Finish Exercise 7 Managing and Using Data Guidebook 98 Page You should have now successfully imported your data into PRIMER Maximize the windows and confirm PRIMER 6 Yap Nimpal MPA Fish PHworking re File Edit Select View Analyse PERMANCY4 Tools Window Help Bm x D B Sia ft Bk 22551 T Worksp
120. n the COTS abundances grew the urchin abundances declined Strong evidence comes from the fact that the trend was only noted at the impact sites We know how important grazing urchins are for reefs to recover so the findings are clearly influential What we don t know is how the declines in urchins occurred Are Acanthaster superior to the grazing urchins and able to take all of the good hiding spots in the reef leaving the urchins open for predation Was there a direct competitive interaction We don t know the answers to these questions but the trend we do know Is of great concern Lets see if these findings are indeed significant Sigma Plot has a number of built in statistical testing procedures We will use a straightforward ANOVA test to examine if there were differences in urchin densities between the timeframes at both the Impact and Non impact sites ANOVA tests compare the distributions of the samples and require us to input means standard errors and sample sizes for each set of measurements This guidebook assumes you have basic statistical knowledge however any introductory statistics book can serve as a guide to better understand the procedures available in Sigma Plot There is also a well developed Help menu with lots of additional information 83 Click back on our Data 2 sheet First we will analyze if urchins densities from the impact sites were significantly different during each time frame 84 Under the Vi
121. name MOuterYesMI3 x MOuterYesMI4 Y MOuterNoMO3 a MOuterNoMO4 Managing and Using Data Guidebook 145 Page From this MDS plot it appears there are strong differences between individual sites but also between inside and outside the MPA s However we have to consider how these MDS plots are made before we wonder why non significant findings were made in our PERMANOVA above MDS plots use non parametric rank ordering of the inter site differences Meaning rather than using the actual distances reported in the Bray Curtis similarity matrix between any two sites they simply rank the inter site differences and capture the relative spread in two dimensional space The non parametrical statistical test associated with this is the ANOSIM described in Exercise 7 Just as an exercise lets run this test now 83 Highlight your Resem similarity matrix associated with this plot 84 Go to the Analyse menu and a Scroll down to the ANOSIM 85 Select a one way design a Set Factor A to MPA This will let us evaluate significant differences between all sites located within MPA s and all sites located outside of MPA s 86 Click OK 87 Confirm MPA Test verall Transfor PERMANOVAT PERMANOVA2 PERMANOVAS Design3 PERMANOVAS Frequency esem f PERMDISP1 0 2 0 1 0 0 1 0 2 0 3 0 4 0 5 0 6 R lt p Exercise 8 Managing and Using Data Guidebook 146 Page The first graph that appears sho
122. ngi EM Linckia laevigata MM Seastar Total M Echinometra MM Echinostrphus Graz A Petes ieee REDI EDD I i Al g 0 0 D 0 0 0 0 0 0 0 o 0 o 0 0 0 0 0 20 o 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 o 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 o 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 g 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 o 0 0 0 0 o 0 0 0 g 0 0 0 2 2 0 0 0 0 o 0 0 0 0 0 0 0 0 0 g 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 g 0 0 0 0 0 0 0 0 0 o 0 D 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 g 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 o 0 0 0 0 0 o 0 0 0 0 0 0 0 0 0 0 0 0 0 o 0 0 0 0 0 0 0 2 2 g 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 g 0 0 0 0 0 0 0 0 0 o 0 D 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 g 0 0 D 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 o 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 o 0 0 0 0 0 o 0 0 0 0 0 0 0 0 0 0 0 0 0 o 0 0 0 0 1 1 0 2 IM 4b M Brainstorm metadata fields Metadata Database build Sheet Sheet4 7 D Ready Average 0 532051282 Count 1563 Sum 830 EE E LU Exercise 2 Managing and Using Data Guidebook 15 Page That ends our basic database manipulation you can review the steps and logically think of other ways to do similar things Now we will begin to visualize the dataset using Excel s Pivot Table In order to set up a Pivot Table you first need to highlight the cells that define the table 14 To the upper left of cell A1 there is a small box with a diagonal arrow
123. nsects and provides a distance measure of ecological similarity for each comparison From this we will again create our multi dimensional scaling plot MDS plot 72 Go to the Analyze menu Note Notice the options have changed items that were previously available are no longer This is because we are working with an active resemblance matrix as opposed to a species by site dataset 73 Select MDS Humber of restarts Minimum stress Kruskal fit scheme Shepard diagrams 0 1 O2 Configuration plot a Keep the default settings for our options b Click OK After a bit of processing time PRIMER produces a 2 dimensional and 3 dimensional plot called Graph1 and Graph2 Let s just focus on the first 2 dimensional plot We will change the look of this plot to better understand the findings Exercise 7 Managing and Using Data Guidebook 116 Page 74 Under the Graph menu 75 Select Data labels amp symbols Graph Options General Titles Bubble Contour Labels Symbols By factor Default Symbol Colour 0 76 For Labels a Check the By factor box b From the drop down menu select Year 77 For Symbols a Check the By factor box b From the drop down menu select Site Exercise 7 Managing and Using Data Guidebook 117 Page 78 You should have changed the look of your graph confirm Note Your graph may be rotated diffe
124. o 0 0 0 0 426 7695 0 o 0 O 24 62299 0 O 34 6498 23 KI1 1 es Inner 26 94958 0 0 O 1361 965 162 938 0 o 0 0 0 0 0 24 J Yes Inner 40 80056 0 0 0 226 503 20 78538 0 o 0 0 100 0216 0 0 25 j Yes Inner 58 94294 0 0 O 208 8142 35 72566 0 o 0 0 822 4037 0 0 168 206 _ 26 4 Yes Inner 20 94958 0 0 0 188 4802 232 2597 0 o 0 0 0 0 0 61 4489 2f 5 Yes Inner 183 026 0 0 O 446 015 0 0 631 3982 0 0 0 0 0 2B KI2 1 es Inner 0 71 12902 O 41 08601 22 28788 69 32168 o o 0 o D D o 9 20361 29 J Yes Inner 0 5 615499 0 0 127 6102 124 7121 0 o 0 o D D 0 3 51520 30 j Yes Inner O 26 62324 0 0 109 732 4 557385 0 o 0 o 0 0 0 31 4 Yes Inner 0 5 615499 0 0 100 3902 0 o 29 02364 0 o D D 0 32 5 Yes Inner 0 5 615499 0 0 29 53955 0 0 19 852 71 0 0 0 0 0 _33 KO 1 No Inner o 0 0 O 246 76001 56 47521 o 0 64 12766 o D 56 27397 0 34 2 Mo Inner o 0 0 0 29 53955 17 85093 0 0 51 64582 0 0 0 0 35 3i No Inner 26 94958 0 0 259 8023 309 1475 91 17614 0 o 0 o 261 8648 0 0 4 No Inner 166 4284 0 0 127 7607 417 0558 153 2935 0 o 0 o 0 0 0 JF 5 No Inner 35 81142 0 0 0 157 0442 274 2833 0 o 0 0 0 0 4 115446 90 4676 38 KO2 1 No Inner 0 86 50376 0 D 1416 244 0 0 o 0 0 0 295 9627 0 bisitak pivot chart PNP Fish Database Sheetl Sheet 7 4 o N Ready i 17 Rename this sheet from Sheet f to PNP Fish Data by Transect Exercise 4 Managing and Using Data Guidebook 42 Page 18 Delete Row T a Highlight the rest of the data Ctrl A
125. o this on your own and confirm the look of your working data table below A B C D E IAE 6 H I J K L M N o P a R s T U v B Site MPA Statt Year Reef Type Depth m Transect Acanthurt Acanthurt Acanthurt Caranx me Cephalopl Cheilinus Chlorurus Chlorurus Ctenochat Epinephel EpinephelGrouper Hipposcar Kyphosus Lutjanus g Lutjanus Gachuug Reference 2007 Channel 3m 1 0 0 o 0 o 0 0 0 247 9478 0 o 0 o 0 0 Gachuug Reference 2007 Channel 3m 2 0 449 9913 0 0 127 7607 0 0 0 406 2062 0 0 o 0 0 0 4 Gachuug Reference 2007 Channel 3m al 155 5967 132 629 0 0 0 0 0 0 387 3948 0 310 7563 0 0 0 0 Gachuug Reference 2007 Channel 3m 4 0 0 o 0 0 0 0 0 1310 312 0 48 79071 o o 0 0 Gachuug Reference 2007 Channel 3m 5 709 2205 0 0 0 0 55 19581 o 0 415 0813 0 o 0 0 579 1653 0 Gachuug Reterence 2007 Channel 10m 1 0 0 0 0 0 0 0 252 9121 576 2208 326 1714 0 0 20 78538 D 0 Gachuug Reference 2007 Channel 10m 2 0 0 0 0 O 183 4658 O 1063 511 456 2791 0 0 O 683 3266 0 0 Gachuug Reference 2007 Channel 10m 3 0 0 o 0 0 0 0 344 1298 359 8519 0 0 o 0 0 0 Gachuug Reference 2007 Channel 10m 4 0 443 8039 0 410 0111 O 344 1298 259 5631 0 0 0 0 0 0 11 Gachuug Reference 2007 Channel 10m 2 0 158 9545 0 0 0 0 0 1526 806 347 7459 510 2472 0 O 188 3515 0 0 12 Gachuug Reference 2007 Outer 3m 1 0 0 O 524 5821 0 0 0 0 718 8561 0 0 0 0 0 0 13 Gachuug Reference 2007 Outer 3m 2 637 0112 0 O 292 8198 0 0 0 0 703 0403 0 0 0 0 0 0 14 Gachuug Reference 2007 Outer 3m 3 0 0 0 0 0 0 0 0 2378 30
126. of Gra 1 After 45 0000 0 0498 45 0000 4 fee Al Open Notebooks 2 Before 42 0000 0 0364 42 0000 4 5476 e ica 3 During 96 0000 F285 96 0000 2 0833 Data 1 eee li Graph Page 1 PEE E Section 3 Sa Data 2 So a ee mnm 43 Do this for all three other instances where standard deviations existed so that we have only standard errors showing on our datasheet We are now ready to create informational professional graphs and associated testing using Sigma Plot Note Save your work 44 Go to the Graph main menu from Sigma Plot and a Select Create Graph b Choose Vertical Bar Chart c Click next 45 Select Grouped Error Bars a Click next 46 For Symbol values a Make sure Worksheet Columns is selected in the drop down menu b Click next 47 For data format choose X Many Y a Click Next Now Sigma Plot is ready for our data Exercise 6 2 Managing and Using Data Guidebook 80 Page 48 For our X data a Choose the first column Time Frame 49 For Set 1 a Choose Average of Acanthaster values associated with Yes impact sites This is column 4 50 For Error 1 a Choose the associated standard errors we just calculated in column 5 Now were ready to enter a second set of data 51 For Set 2 a Choose Average of Acanthaster values associated with No impact sites This is column 12 52 For Error 27 a Choose the associated standard errors we just calculated in colu
127. olumn headings to titles a Delete all unnecessary cells left below 36 Confirm rm 1 Time Frame mpact Sili Count of Acanthaster Average of Acanthasteri StdDew of Acanthasterz E of Grazing Urch je of Grazing Ureche of Grazing Urch Time Frarr Impact Si1 Count of Acanthaste Average of Acanthaske SEdDev of Acanthast After Yes 45 0000 0 1556 0 3341 45 0000 4 7222 3 2710 After Ma 80 0000 0 1188 0 2665 2 Before Yes 42 0000 0 0714 0 2361 42 0000 4 5476 5 0386 Before Mo 159 0000 0 1132 0 4462 3 During Yes 96 0000 12917 2 4052 96 0000 2 0833 3 0964 During No 181 0000 0 1133 0 2925 4 5 6 T a 9 Finally we get the last set of data from Excel Exercise 6 2 Managing and Using Data Guidebook 78 Page 37 Go back to our PivotTable a Set the Impact Sites filter to No 38 Copy and paste these data into Sigma Plot all the way at the end of our existing table e into columns 14 15 and 16 a Promote the headings to titles 39 Confirm M JJ m StdDev of Acanthaster t of Grazing Urch je of Grazing Urcty of Grazing Urch Time Fran Impact Si1 Count of Acanthaste Average of AcanthasteStdDev of Acanthast it of Grazing LIrc 3e of Grazing Ure sv of Grazing Urct 17 0 3341 45 00000 q 7222 3 2710 After Ma 80 0000 0 1188 0 2668 0 2361 42 0000 4 5476 5 0386 Before Ma 159 0000 0 1132 0 4462 23052 96 0000 2 0053 3 0964 During Wo 181 0000 0 1133 0 2925 D Oh A R M e Notice you have to use the lateral scroll bar on th
128. onesia have begun to address these issues under the framework of the Micronesia Challenge In June 2008 the newly formed MC Measures Working Group identified the need to develop an appropriate framework to assist monitoring programs in each of the jurisdictions to track their progress both locally and regionally in effectively managing their resources for sustainable use Spawning from the goals set forth by the MC Measures Working Group collaborations between the Pacific Marine Resources Institute PMRI Dr Peter Houk and jurisdictional monitoring programs were conducted to evaluate the status of existing datasets in 2009 This effort initiated positive continued collaborations for enhanced scientific oversight of monitoring activities with numerous regional partners and scientists Building upon a scientific foundation to match management goals with monitoring activities key recommendations were made to initiate a standardized monitoring approach for the MC and beyond Houk 2009 These designs and methods were tested in each jurisdiction and shown to address many pressing management concerns with adequate statistical considerations Since 2009 data has been collected using the updated techniques and now exists However these data are not being thoroughly examined and reported on because generally the scientific expertise needed to digest the collected data for management has not yet been well developed within local programs This forms the basis f
129. oolbar SUM A Island chuuk Chuuk huuk Cal X v fe LOOKUP E2 Metadata 5A52 54 57 Metadata C 2 50 57 E Reef type Channel Inner C Wave exposure Low Moderate Inner barrier Low Mwitar harrinar Choaltarcad D Depth im im im Ire eV Site Transect GPSx GPS y 11 0 2 30957 C 11 1 C 13 2 f WA The lookup function first asks for the reference cell value upon which the lookup will occur in this case it is cell E2 or the site Next you have to provide a list of all possible sites for excel to look up in this case the list is found on the Metadata sheet columns B and C are the X and Y coordinates i e lat and long for each site Now you do this for the GPS Y or latitude coordinate Exercise 1 Managing and Using Data Guidebook 6 Page Note It is important to note that the in the cell formula means for Excel to keep the exact cells when conducting the functions Without them the references for the lookup values would change when we cut and paste into cells below to automatically populate our database 18 Get a scratch paper out click on the Metadata sheet and note which cells that contain GPS Y coordinates you are interested in and the site names associated a The relevant information to write are the site names that will be looked up A2 A57 on the Metadata sheet and the valued you want inserted GPS Y B2 B57 19
130. or the current proposal Here we proposed to conduct hands on training workshop using a step by step data analyses and graphing guidebook recently funded by NOAA PIMPAC and currently under development This guidebook is being developed using regional data collected during FY 09 collaborations between PMRI and FSM RMI coral monitoring programs This proposal aims to bring key users of datasets from each FSM and RMI jurisdiction together for a hands on workshop to evaluate their data and learn how to efficiently visualize and when appropriate test for significance Additionally this proposal would teach participants how to utilize the developing Micronesian Challenge database inputting and extracting datasets to quickly understand current trends The guidebook is being produced using four major software platforms Microsoft Excel Access PRIMER E and Sigma Plot Here the budget describes costs for technical and logistical preparations necessary for the workshop one software package and limited travel for participants It is noted that remaining software and travel budget required will come from the FSM monitoring grant and other funding sources recently awarded Introduction Managing and Using Data Guidebook ilPage Section 1 Database generation manipulation and query investigation Exercise 1 Establishing a database The initial establishment of a database can often seem like a daunting task for us Consider that a wealth o
131. ote the names for columns 2 10 b Close the dialog box Exercise 6 1 Managing and Using Data Guidebook 67 Page We will now make headers to define our different years of data 19 Click on the first cell under Column 1f a Type the word Year 20 In the cell under Year type in 2000 then 200T in the cell under that a Continue until 2009 21 Promote Year to a column title as we just did before 22 Confirm n 1 2000 0000 DO c Cf E 0d PR E ce Now we are ready to create a simple bar chart 23 Go to the Graph main menu on the top 0 0000 0 0000 0 0000 0 5000 1 5000 0 0000 0 0000 0 0000 0 0000 0 0000 2 2001 0000 0 0000 0 0000 0 0000 0 0000 0 0000 1 0000 1 0000 3 0000 1 0000 0 0000 3 2002 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 5000 0 0000 0 0000 0 0000 0 0000 a Scroll down to Create Graph 24 Choose Vertical Bar Chart Exercise 6 1 4 005 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 z ULL 3 24 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 5000 0 0000 0 0000 0 0000 6 2005 0000 z ULL 4 0000 z DD 0 0000 0 0000 0 0000 0 5000 0 0000 0 0000 0 0000 006 0000 0 0000 0 5000 D 0000 0 0000 zal z UD z DD 1 0000 0 0000 0 0000 Managing and Using Data Guidebook amp 2D 0000 D 0000 D 0000 0 5000 0 5000 D 0000 0 5000 D 0000
132. ount for all other predictable variation that is possible to do so The PERMANOVA software allows us to easily do this for our multivariate dataset First we need to import our data from excel as we did in the previous exercise 1 Open the Pohnpei MPA fish PERMANOVA example file Take a look at both worksheets First the Data sheet You can see the meta data columns follow the diagram above starting with Location and ending with Transect After these information data you can see each indicator fish species and the biomass 2 Open the sheet For Primer These are the same data arranged in a simple way for PRIMER to import the numerical data and the explanatory factors You can see the fish abundance data appear first but as you scroll to the right you eventually come to a blank row then the informational data This is the format that is required by PRIMER Numerical data followed by a blank then categorical data 3 Close Excel and Open PRIMER 4 Select Open from the menu a Select Excel under the dropdown menu for files of type b Navigate to Pohnpei MPA fish PERMANOVA example xIsx c Click Open Note that PERMANOVA stands for permutation multivariate ANOVA 5 In the next menu box a Click the dropdown menu and choose the excel worksheet titled For Primer b Select Sample data as the data type c Click next 6 Uncheck the two green marks next to Title and Row la
133. p s monitoring program Also there has been an increase in the grouper Cephalopholus argus We could continue to do this for the Outer reefs too but for our purposes we can conclude the exercise now We conclude that substantial changes appear to have occurred between 2007 and 2009 for the Channel monitoring sites associated with Nimpal MPA and Gachuug reference area In a later exercise we will test whether or not these changes were statistically significant using a multivariate nested ANOVA approach This exercise was intended to improve our ability to visualize and comprehend our data initially Often we d like to have immediate insight into potential trends regardless of statistical significance soon after our surve ys are conducted This exercise represents one means at gaining quick insight into multivariate patterns in our collected ecological datasets End of Exercise 7 Exercise 7 Managing and Using Data Guidebook 121 Page Exercise 8 A multivariate statistical examination of Pohnpei s Marine Protected Areas using PRIMER E and PERMANOVA For this exercise we will refer back to the Pohnpei marine protected area fish biomass data we began to explore in exercise 3 and 4 We will be looking these data from a multivariate perspective in order to understand the status of each MPA More formally we will examine how the variance in the fish dataset is soread out among the numerous independent variables that emerge from thei
134. p all cases where data was collected regardless of the value i e regardless of how many COTS we saw on the transect line Excel gives a value of 1 for every data entry Thus the Count is our sample size n or total number of transects that were surveyed in each category The average and standard deviation are self explanatory Lets filter our data and begin to transfer to Sigma Plot Lets first consider only the Impact Sites where increase COTS abundances were noted 11 Click on the drop down menu next to Impact Sites a Check only the Yes box 12 Highlight all of the cells in our Pivot Table a Copy the data 13 Open or return Sigma Plot 14 Right click on Section 1 in the panel on the left hand side a Scroll down to New and choose Worksheet 15 Right click in cell 1 1 and choose Paste 16 Confirm SigmaP lot Data 2 File Edit Insert wiew Format Tools Graph Statistics Transforms Toolbox Pharmacology Window Help Dice X Bao c EG BAO ERE DAD 6f AO ET il One Way ANOVA 1 Data E All Open Notebooks 2 COTS Impact Sites Count of Acanth Average of Acar StdDew of Acant 5 ia 3 After Yes 45 0000 0 1556 0 3341 Data 1 4 Before Yes 42 0000 0 0714 0 2561 li Graph Page 1 5 During Yes 96 0000 1 2917 2 3052 S A Section 3 6 Data 2 7 a a 10 11 12 moma Exercise 6 2 Managing and Using Data Guidebook 75 Page 17 Right click on row 7 and delete t
135. pem A T 1 gt Y Values 25 0 QR OQ d dO uS GG x 26 SUO Q9 Q9 FF SK SOS 27 m CUNG M Tau v 28 29 30 um 31 444 Axis Fields Cat X Values 32 Island Y Average of Se 33 StdDev of Sea Y 34 Hu 36 37 38 a DO Defer Layout Update M 4 gt M Brainstorm metadata fields Metadata Chuuk REA Invert Pivot Ready Let s move the chart to a new sheet for simplicity 20 Right click in the chart and select Move Chart Refresh Data Cut 23 Copy Paste Font 3 4 53 a g Reset to Match Style A dl Change Chart Type ES Select Data Assign Macro Format Chart Area Exercise 2 Managing and Using Data Guidebook 19 Page 21 Select New Sheet and rename the chart Chuuk REA Invert Summary Move Chart EJ Choose where you want Ehe chart to be placed du new sect OObject in Chuuk REA Invert Pivot v A new sheet is created and our desired summary is easily seen and understood Da WHI c Chuuk REA invert database PHworking Microsoft Excel PivotChart Tools Ca Home Insert Page Layout Formulas Data Review View Add Ins Acrobat Design Layout Format Analyze m x E ae mu Change Save As Switch Select E Ea I li i i Move Chart Type Template Row Column Data Chart Location 4 Type Data Chart Layouts Chart Styles Y Q Fe 14 T PivotChart Filter Pane X PivotTable Field List Active Fi
136. persions These results can be interpreted like and ANOVA F statistic The key results are located under the Deviations from Centroid header Here you can see that our F statistic is relatively low and that we have 32 total sites meaning there are 31 degrees of freedom for the test The last item displays the P value P perm 0 1 suggesting that no significant differences in multivariate dispersions exist between all of the sites Bs Pohnpeifish MPA PERMANDVA exercise T FahnperMPA fish PERMANCUVA example uA Y PERMDISP Distance based test for homogeneity of multivariate dispersions Resemblance worksheet Mame Resemz Data type Simllarity SAA Transform Logriz rlli Resemblance S17 Bray Curtis similarity Group factor Site Number of permutations 999 Number of groups 3z Humber of samples 156 DEVIATIONS FROM CENTEOID F 1 9465 di 31 dz 1246 P perm 0 1 MEANS AND STANDARD ERRORES Group Size Average GE DIL 5 41 365 8 8841 DIz AE ae Below this you can find the average dispersion value for each site For our purposes the non significant value means that we can proceed as planned with our PERMANOVA using the parametric dataset Exercise 8 Managing and Using Data Guidebook 137 Page PERMANOVA Testing The input for a PERMANOVA test is a similarity matrix such as the Bray Curtis similarity matrix we already created that describes how similar each individual transect is to one another
137. r monitoring program design To this end we will also test for statistical significance providing a guide for future work with datasets of your choosing It will help to examine a diagram of the survey design used There were five villages that have established MPA s within them noted as D K N M and L Each MPA encompasses both inner lagoon and outer reef sampling sites For each reef type two sampling sites were set up inside and outside the MPA Finally at each sampling site there were 5 transects surveyed MPA D MPA K MPA N MPA M MPA L IPN IN NA FS FN Inner Outer Inner Outer Inner Outer inner Outer Inner Outer reefs reefs Inside Outside Inside Outside MPA MPA MPA MPA Site 2 Site 1 Site 2 Site 1 P4 3 Fi Pi 73 N Replicate transects n 5 Exercise 8 Managing and Using Data Guidebook 122 Page This type of experimental design is defined as nested Reef type is nested within village location MPA status is nested within reef type and sites are nested within each MPA status Using this design we can examine all MPA s together or individually howe ver it is always best to start our investigations with a big picture perspective i e highest levels first then work our way down We are interested in determining what nesting level or levels explain significant proportions of the variation in the fish biomass data obviously we are most interested in learning about MPA status but we wish to acc
138. r monitoring data the amount of time since the MPA was established or the level of compliance with the no take fishing policy For the monitoring program these results suggest potential changes to their sampling design and or methodologies It seems very important to maintain the same data collector when conducting fish surveys Similar there may be a desire to expand data collection efforts to include all food fish rather than the select indicator species A last note is that there are several ways in which these data could have been analyzed using PERMANOVA We might want to consider further tests at the individual site level rather than grouping the sites to determine whether or not MPA are successful Clearly this would be a positive next step but entails somewhat of a revision of the ecological sampling plan These are all terrific points for monitoring programs to discuss with each other and with scientific advisors End of Exercise 8 Exercise 8 Managing and Using Data Guidebook 149 Page
139. rap Text General T F EY Normal 8 Normal 9 c a x AutoSum 3 Copy m Fill ds 7 Format Painter Biju ME SEY aa A 22 E sE le amp Center 9 Sao jo E silia and pere Norma Ban a d TENE zs cp Clear 7 Clipboard E Font E Alignment F Number F Styles Cells Ed W2 Fx E his Acanthurus Siganus a 1 Lutjanus gibbuka MIRA E Monotaxis granocuks Ef Naso tituratull eR Y EPT Y E iganus doian bl prRT pM Total BE Eg 146 1007995 0 83 85516295 o 0 0 0 of 370 68211 3 63 06822231 0 0 0 o 418 XE 16 98 66345 570 2425 4 64 12765942 0 O 0 0 0 0 0 O 582 2677 5 0 0 0 O 0 0 0 0 O 1736 954 6 0 0 O 0 0 8 63118382 0 0 O 228 5095 T 0 0 0 20 5392 7497 0 0 0 0 19 09386384 104 2013 8 0 0 0 o o 0 0 oO 124 0765 9 0 0 20 53477998 0 0 5 688410915 0 0 57 19708924 161 0071 10 0 0 O 0 0 0 0 25 18653069 0 56 62435 11 0 0 o 0 o 34 64083142 12 00845723 0 115 6182 12 196 3264321 0 o 411 4376075 50 83456442 65 45696046 0 4872 741 13 0 0 0 4439 441088 o 0 0 0 86 9187129 6153 532 14 0 0 O 56 2 396857 0 17 32491571 19 38359574 0 0 1021 34 15 0 0 o 91 6206 56 0 0 35 19830214 0 O 376 8986 16 0 261 5679145 0 56 2 396857 869 6052304 17 32491571 19 383595 4 0 0 5167 187 17 0 0 97 11944011 0 47 93848427 0 0 81 13722766 678 8167 18 0 0 12 31149291 0 0 0 7287146214 0 124 4389485 199 8531 19 0 0 36 47697767 0 0 93 34908936 0 0 189 4521 20 0 0 0 0 0 0 179 5604 6 7 12364006 O 378 3839 21 0 0 24 62208581 0 o 34 64083142 316 2206001
140. red 11 Using the rename box on the left a Rename this factor to Combined name b Click OK Note No changes will be saved unless you click on Ok In order to help us set up our PERMANOVA design let s first gain a big picture perspective of the data set To do this we will create a multi dimensional scaling plot similar to the last exercise 12 Go to the Analyze menu a Select pre treatment b Select transform overall 13 In the dropdown menu a Select Log x 1 b Click OK Exercise 8 Managing and Using Data Guidebook t26 Page A new sheet with the log transformed data should appear 14 Go to the Analyze menu a Create a Bray Curtis similarity matrix b Select resemblance make sure the analyses is between samples and you use a Bray Curtis similarity method 15 Click OK 16 Confirm PRIMER 6 Resem1 fA File Edit Select view D a Ed n X ES Pohnper fish MP4 PERW FA Pohnpei MPA fish PE E Overall Transforr Datal fo Resemb fa Res lt m gt Exercise 8 Analyse PERMANOYA Tools Window Help 0 X BOE 8 2 20 ul Similarity 0 to 100 st H2 js js ss X s s X ECOS E SSI st X SI SOCIO s4 js SECO STA EN S EJES 65 891 35 16 52 87 17 14 59 231 E o 16 444 39913 0 19 63 32 513 39 599 27 425 27 581 24 746 19 556 32 799 44 818 0 48 75 36 92 41 567 40 806 27 748 43811 62 852 32 231 NN 24142 19
141. rently however the spatial distances between sites should be the same Transform Log X 1 Resemblance 517 Bray Curtis similarity 2D Stress 0 12 Notice we have very similar trends compared with our 3m depth analyses earlier Next we will calculate the contribution of each species of fish to our detected trends PRIMER has a built in analyses that calculates the relative contributions of each species in determining the trends that the graph show Exercise Managing and Using Data Guidebook 118 Page 79 Go back to the 70m channel transformed data sheet We need to make further selections from our data What we want to Know is how and why the fish biomass are different between these reefs in 2009 only because in 2007 they were still similar Basically we d like to know what change occurred 80 Select the Edif menu a Select Factors Notice only our subset of sites appears For our next examination we wish to look at only 2009 data corresponding to samples S26 S30 and S61 S65 Note those sample labels on your scratch paper and close the factors box 81 On your main sheet a Highlight the samples noted above 82 Go to the Select menu a Select highlighted 83 Confirm your datasheet below Notice only 10 samples remain these correspond to 10 transects surveyed 5 inside of the MPA at a 10m depth in 2009 and 5 outside Biomass S26 Sa S20 Sa S30 551 SEZ S63 S64 S65 Ex
142. report Holothuria atra Holothuria edulis Holothuria fuscopuntata Pearsonothuria graeffi Stichopus chloronotus Stichopus hermanni Thelenota ananas Thelonota anax Sea Cucumber Total _ Echinaster luzonicus Acanthaster planci Culcita novaguinea Linckia guildingi Linckia laevigata Seastar Total Echinometra Drag fields between areas below Y Report Filter Column Labels iz Row Labels Island Z Average of Se v C Defer Layout Update 20 020080 o 18 Click and drag the Sea Cucumber Total box from on top below the existing Average of Sea Cucumber in Values a Click the new Count of Sea Cucumber Total box and again choose Value Field Setting b Scroll down on the pop up menu and choose StdDev Now you have averages and standard deviations side by side let view this graphically Exercise 2 Managing and Using Data Guidebook 18 Page 19 Click on any cell in the Pivot Table the new data table on the upper left of the sheet a Click on the Insert main menu tab in Excel You can see a lot of options here we want to look at simple Column charts b Click on Column and select the first graph option in the top left A x gt DEA i s TRA 2 mox fy E Chuuk REA invert database PHworking Microsoft Excel PivotChart Tools m m 3 EM E ES nu l ame E ER mz Q 5 x
143. rge amp Center ac 9 4 Conditional Format Bad mser Delete nares j Sort amp Find amp J Format Painter E e Bhd Mero 8 2 Formatting as Table lt 2 Clear 7 Filter Select Clipboard Ta Number la Styles EF X PivotTable Field List 1 2 Choose fields to add to report 3 Average of Biomass g Column Labels 4 Row Labels Acanthurus lineatus Acanthurus xanthopterus Caranx melampygus Cephalopholis argus Chlorurus microrhinos Hippo No 70 105870961 71 70777206 182 9699397 148 4329743 178 70312 6 Yes 102 6207832 83 72546783 631 2894633 370 3138073 298 9283737 blank Grand Total 79 16740388 82 23654977 389 8866429 244 9029017 241 6320089 NON A A A AA AAA Ap sll ell lalola bl 38 4 4 M PNP fish pivot PNP Fish Database Sheet Sheet2 Pm Ready 8 Go to the Insert tab off the main menu of Excel and insert a column chart a Choose the stacked column chart one showing cumulative data summaries Exercise 3 Managing and Using Data Guidebook 30 Page po 909 Home i Insert eon PivotTable Table b Right click in the chart area and move this chart to its own sheet c Name the sheet PNP fish pivot chart 41 Page Layout ure Clip Shapes SmartArt seen f 70 10587 d Confirm below 5000 4500 3500 3000 2500 2000 1500 1000 500 9 Click on the MPA drop down menu in the PivotChart Filter Pane Exercise
144. rm 5i MF Re La Within level D of factor Location Within lewel aa of factor Reef type Within lewel Yes of factor Mrs Unique Groups t Piperrmn perms Dril Mie al ails mal 0 043 126 Denominators Groups Denominator Den dt DLL Mie a a Arerage Similarity between within groups DIL DIZ Te gd DIA cel apo ELA Within level D of factor Location We Inner of factor RHeef type Within level No of factor MPI Unique Groups t Piperrm perms Exercise 8 Managing and Using Data Guidebook 143 Page A review of these site comparisons reveals that almost all pair wise comparisons are significant This confirms our thoughts that Site level variation is the greatest and even stronger than MPA status While clearly we expected some site level variation we also expected that some of the MPA s might have a stronger influence on the fish biomass We have to be careful how we interpret these results We can think of several reasons as to why our results came about ranging from data collector variance i e different people collecting data from different locations number and choice of indicator species number length and width of transects quality of the data collection and most importantly the length of time any particular site has been an established and enforced MPA Graphical interpretations Our last exercise here will be to produce an informative MDS summary plot for one MPA to highlight our findings and
145. s Inner 0 0 0 0 0 0 2077 23 68 MIZ 2 Yes Inner 0 0 0 0 0 0 69 MI2 3 Yes Inner 0 0 0 0 0 0 70 MI2 4 Yes Inner 0 0 0 0 0 0 71 MI2 5 Yes Inner 0 0 0 0 0 72 M01 1 No Inner 0 0 0 127 606938 0 138 6433604 73 MO1 2 No Inner 0 0 0 0 0 0 74 MO1 3 No Inner 0 0 0 0 334 3265379 859 580583 75 MO1 4 No Inner 0 0 0 0 0 1882 099165 76 MO1 5 No Inner 0 0 0 0 1576 025111 0 77 MO2 1 No Inner 0 0 0 32 45807963 200 5011135 0 78 MOZ2 2 No Inner 0 0 0 0 348 252 7314 0 79 MO2 3 No Inner 0 0 0 32 45807963 122 2 36087 0 80 MO2 4 No Inner 0 0 51 16810232 0 195 678465 81 MO2 5 No Inner 0 0 0 32 45807963 200 5011135 0 82 MIL 1 Yes Inner 0 0 0 0 573 045497 316 1631443 83 NIL 2 Yes Inner 0 0 0 0 507 2108781 69 32168018 84 NI a Yes Inner 0 0 0 0 0 0 85 NT 4 Yes Inner 0 0 1560 394942 0 334 3265379 0 86 NI1 5 Yes Inner 0 0 0 0 907 3720349 301 5813593 87 NI2 1 Yes Inner 9 300308364 0 0 o 0 o 88 MI2 2 Yes Inner 117 8858776 0 0 0 65 1 349129 1250 231305 89 NI J Yes Inner 414 5347352 0 0 0 903 7508484 0 90 MI2 4 Yes Inner 0 0 0 0 518 4117967 o 91 NO1 1 No Inner 0 0 0 419 6444894 0 92 NO1 2 No Inner 0 204 1328923 0 0 163 2 6415 0 93 NO1 3 Mo Inner 0 0 0 0 1052 18581 316 1631443 94 NO1 4 Mo Inner 26 94957737 388 1945196 0 o 518 4117967 20 78537612 95 NO1 5 No Inner 155 5966648 299 2851609 0 0 221 62 74269 0 96 NO2 1 No Inner 232 0940318 0 0 0 948 288766 651 9648553 97 NOZ 2 Mo Inner 132 467928 0 0 0 408 2023468 1743 414176 98 NQ2 3 No Inner 11 74445376
146. s down to A10 Exercise 1 Managing and Using Data Guidebook 3 Page 12 Do the same for the next column Reef type a Click on the column B on top of Reef type b Type the below code into the empty Source box Data Validation Settings Input Message Error Alert Walidation criteria w E i E In cell Source Brainstorm metadata Fields 14642 B 5 ER 13 Again verify yourself why cells B2 B5 are chosen by examining the Brainstorm metadata fields Populate cells B2 B10 with values of your choosing Inner A e C D E F 1 Island Reef type Wave exposure Depth Site Transect GPSx 2 Chuuk Channel 3 Chuuk Inner 4 Chuuk Inner barrier 5 Etal Outer barrier 6 Losap Outer barrier 7 Losap Channel 8 Murilo Inner barrier Etal Channel Inner Inner barrier Outer barrier Exercise 1 Managing and Using Data Guidebook 4 Page 14 Do the same for the next column Wave exposure and Depth a Fill in columns with values of your choosing D10 fe 10m A B C D E F G 1 Island Reef type Wave exposure Depth Site Transect GPS x 2 Chuuk Channel Low 3m 3 Chuuk Inner Moderate 3m 4 Chuuk Inner barrier Low 3m 5 Etal Outer barrier Sheltered 3m 6 Losap Outer barrier Moderate 3m F Losap Channel High 10m 8 Murilo Inner barrier Low 10m 9 Etal Channel Moderate 10m 10 Chuuk Inner Moderate 10m v 15 Now do t
147. s for Chuuk seems best focused upon inner reefs People in charge of continued monitoring programs might design or re design annual ecological surveys accordingly It seems less appropriate to randomly survey all reef habitats in Chuuk however like any dataset the REA data doesn t tell the whole story For example the outer reef flats were not surveyed and typically hold high sea cucumber populations but usually of only a few species End of Exercise 2 Exercise 2 Managing and Using Data Guidebook 26 Page Exercise 3 Advanced queries into a large multivariate dataset to understand ecological patterns pertinent for management actions 1 Open the file Pohnpei MPA fish transects Notice there are two sheets that are populated with data and site information 2 Click on the sheet Site information This sheet contains a list of all MPA monitoring locations for Pohnpei s program MPA status reef type indicator fish species and two coefficients that are used to estimate biomass from length estimates 3 Click on the next sheet PNP Fish Database You can see a dataset for Pohnpei s 2006 indicator fish monitoring efforts First notice the design of the database is different from the Chuuk REA database Here each row represents one individual fish on any particular transect at any particular site With the Chuuk REA data each row represented one transect Take time to notice the column headings and how the drop
148. ses Note Check to ensure that S126 is no longer there 34 Confirm b Ea ER ry Pohnpei MP4 fish PERMANOVA e E m Overall Transform B lag transfarmed E m Hesemblancel Ef Reseml f MDS E Graphi F Graph2 iii Exercise 8 Alamass ee Acanthurus liq Scanthurus 4 Caranx melam Cephalo o 5 7282 0 489521 o o o 31419 o al o n o n ol o o n o n o n o n o o o D o n o o o n ra E ol o 5 5091 0 o 0 o n 5 7022 0 D 0 ol o o n o o 5 5883 0 ol D o o o n o n n 47782 n 6 0296 o ol ei o 2 2921 o o m ESSI CU ca cQuoejojojojojojojoajojoajojoj oajojojojojojojajaujo do ojojojo o 0 o 7 3533 a 3 SI a a 0 Managing and Using Data Guidebook 132 Page Now we are ready to design our PERMANOVA analysis 35 Go to the PERMANOVA menu 36 Select Create PERMANOVA design 37 Title this PNP MPA Recall from our diagram above we have four factors 1 Location 2 Reef type 3 MPA status and 4 Sites If you can t recall this see the introduction above 38 Select 4 factors 39 Click OK PRIMER 6 Design1 cy File Edit View PERMANOVA Tools Window Help Da ESR BA APO El PAF IPA Factor Nestedin Fixediranciom Contrasts Bo EI 505 0 o O 00 0 00 A Fed 000000 00 js PahnperFish h PA PERMANLCUVA exerc a En Pohnpel MPA fish PERMANOYA e a A Overall
149. significance using ANOSIM Without getting into details provided there the guidance suggests that any R statistic above 0 5 can be considered statistically significant Thus the ANOSIM detects significant differences between MPA status that the PERMANOVA did not We should again understand this is due to the non parametric ranking procedure Our last procedure here will be to prepare a PCO plot rather than a MDS plot PCO or Principal Coordinate Ordination is a parametrical approach to produce informative plots such as MDS using the actual values of the Bray Curtis similarity matrix 89 Highlight the ResemT similarity matrix used Transform Log X 1 with our ANOSIM 90 Click on the PERMANOVA menu 60 Combined name 91 Scroll down to PCO MOuterYesMI3 92 Keep all default settings x MOuterYesMI4 93 Click OK Y MOuterNoMO3 94 From the graph page select Data labels amp T O MOuterNoMO4 symbols a Uncheck the Plof box for labels b On the right select Combined name from the dropdown menu 95 Click OK 96 Confirm your informative plot NI co PCO2 23 3 of total variation 0 20 40 40 20 0 20 40 PCO1 35 9 of total variation Exercise 8 Managing and Using Data Guidebook 148 Page We can see clear similarities between our PCO plot and our MDS plot To better understand why we didn t get significant find ings in our PERMANOVA you can envision the two data clouds circled above These represen
150. slands of varying population land use and other physical attributes However many similar inner reefs were surveyed and why do the abundances vary among them Let s look closer Exercise 2 Managing and Using Data Guidebook 23 Page 26 From the PivotTable Field List a Click and drag the Reef type box below the Island but on top of the Site and Depth boxes b Confirm below 35 4 30 25 20 15 10 M Total a y 3 3 3 Metadata Chuuk RFA Invert Summary 3 3 3 Chuuk RFA Invert Pivatii E E 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 C 13 C 23 C 4 C 7 C 10 C 11 C 15 C 18 C 19 C 28 C 5 C 6 C 9 C 14 C 17 C 20 C 21 C 27 C 3 C 12 C 16 C 22 C 8 Channel Inner Inner barrier Outer barrier Chuuk Bi Total PivotChart Filter Pane Active Fields on the PivotChart a Report Filter i Axis Fields Categories Tas Island eN Reef type y Site Ly Depth Fi 3 Legend Fields Series Values Average of Sea Cucumber Total PivotTable Field List X Choose fields to add to report v Reef type 4 Wave exposure v Depth Y Site JTransect ers x ePs y A Hippopus JTridacna crocea Tridacna spp Clam Total Crinoids Lobster all spp Octopus Actinopyga mauritiana argus
151. so we need to calculate another similarity matrix from our log transformed data Exercise 8 Managing and Using Data Guidebook 135 Page 49 Highlight the log transformed data sheet on the left 50 Go to the Analyse menu a Scroll down to Resemblance b Create a Bray Curtis similarity matrix 51 Go to the PERMANOVA menu 52 Scroll down to PERMDISPERSE The following dialog box should appear PERMDISP Group factor Num permutations Distances are to P values are from 2 Centroid Permutation Median C3 Tables Output individual deviation values Do pairwise tests 53 Change the group factor to Site Meaning that we want to understand the variance at the site level within which five replicate transects of data were collected You can refer back to the diagram at the top if you don t understand why we are choosing Site Exercise 8 Managing and Using Data Guidebook 196 Pa39ge 54 Click OK P PRIMER 6 PERMDISP1 m File Edit View Tools Window Help Ca Adsl E E Overall Transform log transformed E E Resemblance Df Rezembl f most E Graphi 9 Graph2 is Design E E Hesemblancez f Bray Curtis similarity FERMANO PERMANOYAL PERMANOYAS 2 E Hesemblance3 Df Resemi f ups E Graph3 9 Graph feo Design PERMANOYAS ANOSIM1 9 Graphs BA Recemblanced You should now get a PERMDISP results sheet that displays the homogeneity of multivariate dis
152. son Procedures Fisher LOD Method Comparisons for factor Comparison Diff of Means L 5D alpha 0 50 P Diff gt LSD Row 1 ws Row 3 2 639 1 308 0 001 Yes Row 1 ws Row 2 0 173 1 533 0 825 Ho Row Z vs Row 3 2 464 1 330 0 001 Yes Exercise 6 2 Managing and Using Data Guidebook 88 Page Note on this sheet the groups are referred to as Row 1 2 and 3 From our main data sheet we know that Row 1 represents the time frame after the COTS disturbances Row 2 is before and Row 3 is during General data summaries that we selected for input first appear under Group Name Then under Source of Variation we have our ANOVA table showing significant differences between the groups but we don t yet know which ones just that variation exists Finally under Comparison we see individual pairwise testing results Pairwise testing shows that Row 1 is unique and significantly different from all others and Row s 2 and 3 are the same Translated urchin densities significantly declined during the years where A planci abundances were high but seem to have rebounded We will now look at the Non impact sites where we hypothesize that no change in urchin densities would have occurred 95 Click on the magic wand icon next to the drop down menu Again the first step is to define our data format a Select Mean Size Standard Error to One Way Analysis of Variance Monday June 22 2010 4 19 14 P match our data ee mr ata source
153. t the two datasets we wish to detect significant differences in The average radius of these circles represents the component of variation or basically the variance in the multivariate data This is the dashed black line above with a CV next to it These can be interpreted like standard deviation bars on our graphs If the black dash line is longer that the mean distance between the two center points of our circle the PERMANOVA will most likely show a non significant result Also different from the MDS plot you can see numerical values on the X and Y axes The last clear message we can learn through our visualization of the PCO plot is that it seems the MPA status is having an impact on the fish assemblages here However you can see the high inter site variation between the two sites in the MPA boundary 1 MOuterYesMIS the green plus signs and 2 MOuterYesMI4 the blue X It appears both sites individually would be significantly different from the reference sites but when combining the data from these sites to test for MPA effectiveness too much variation is introduced Clearly our summary provides a wealth of information to inform the monitoring and management programs of Pohnpei Some clear suggestions to the management community are that the MPA s have a mixed success and trends vary within each village Even at sites where current improvements are noted the results are not yet significant This may be due to the confidence in ou
154. t were observed No size data were collected just counts From the Brainstorm metad ata fields sheet you can get an idea of the sampling scheme This is a logical first step in creating a database to generate a brainstorm or relevant metadata fields sheet that breaks down how sampling was conducted 4 Click on the next tab Metadata Here a list of all sites surveyed was populated while surveys were being conducted Location information exists as well as site characteristics Armed with this information laid out in this manner were ready to begin building our database Exercise 1 Managing and Using Data Guidebook 1 Page 5 Click on the next tab Database build Notice that the metadata headings from the earlier sheets are copied over already We need to populate them and reduce the chance of data entry error 6 Click on the Data tab on the main menu of excel up top 7 Click on the entire A column above the word Island see below From From From From Other Existing Refresh m Sort Filter Fo Text to Access Web Text Sources Connections All Edit Links 4 Advanced Column Get External Data Connections sort amp Filter Island C D F G Wave exposure Depth Site Transect GPSx 8 Now under the excel sub menu Data Tools click on Data Validation a The menu below should pop up Click on the Settings tab b Then for validation criteria scroll down until you find
155. the List Data Validation Settings Input Message Error Alert Im Validation criteria allow Any value Whole number Decimal List Date Time Text length Custom L Exercise 1 Managing and Using Data Guidebook 2 Page 9 Now you should see a Source field open up where you want to provide the selective criteria for entering data into these cells i e the island names where data was collected from a Type in exactly what is seen below Data Validation Settings Input Message Error Alert Validation criteria Allow Jose Ber o M w In call dropdown Source Brainstorm metadata Fields A 2 445 Es Apply these changes to all other cells with the same settings 10 Click OK This code logically refers Excel to the sheet named Brainstorm metadata fields and says the all possible islands where data were collected from are located in cells A2 A9 Verify that for yourself Note When doing this it is very desirable to have scratch paper for taking informal notes to assist you with entering source codes and functions into excel 11 Click in cell A2 and notice there is a dropdown arrow on the right hand side a Click the drop down arrow and notice the list of islands appears where data were collected from B Island Reef type ea a un 4s Ga fd i b Choose any island name for now c Click in cell A3 do the same Populate cell
156. the first pair wise comparison from inside of Village D on the Inner Reefs and we can see that a non significant t statistic and P value emerged We can continue to scroll down and view each of the pair wise results however none are significant We have a good idea as to why this is and that is due to the Site level variation that exists Lets confirm this 69 Go back to your Bray Curtis similarity sheet 70 Go to the PERMANOVA menu a Select PERMANOVA 71 Select pair wise a This time in the drop down box select Sites 72 Click OK Exercise 8 Managing and Using Data Guidebook 142 Page Note When the warning box appears you can click OK were well aware of MATE our study design and we simply wish to view and understand the results so we can best move forward A HERG You have asked For comparisons among levels of a random Factor 73 Confirm the third PERMANOVA sheet Proceed anyway PERMANOVA 9k Permutational MANERA Resemblance worksheet Name Bray Curtis similarity Data type Similarity selection All Transform Log 2 1 Resemblance S17 Bray Curtis similarity Sums of squares type Type III partial Fixed effects sum to zero for mixed terms Permutation method Permutation of residuals under a reduced model Number of permutations 999 Factors Name Abbrev Type Levels Location Lo Random 5 Reef type Re Fixed MPA MP Fixed E Sae Exin Random JA PATR WISE TESTS Te
157. timate of coral cover or 30 of 60 52 or 18 16 b Type the following power t test delta 18 16 sd 10 63 power 0 7 c Confirm File Edit View Misc Packages Windows Help IM R Console Two sample test power calculation n 4 delta 22 23 fO56 Bd 10 63 Sig level 0 05 power E alternative two sided NOTE n is number in each group eepower t test delta q15 16 ad 10 63 power 0 7 Two sample test power calculation n 5 370844 delta 15 16 sd 10 63 aig level 0 05 power Us alternative two sided NOTE n is number in each group You can see that with just a bit more effort 5 transect Kosrae could successfully meet the goals we laid out We are finished with the current exercise Note that we can easily substitute fish counts abundances biomass algae coverage or whatever our key ecological metric is within any survey This exercise was intended to provide you an example to follow for making future calculations on your own Exercise 5 Managing and Using Data Guidebook 61 Page Also keep in mind that many ecological datasets are multivariate in nature and statistical power by definition only accounts for one variable Typically monitoring programs select one key variable such as coral coverage or other abundant benthic organisms to examine The results will indicate whether or not your level of replication is sufficient generally This is usually a good start prior to moving into multivariate consi
158. trends 74 Section 3 Multivariate statistics and graphing he Tes att 91 Exercise 7 An introduction to multivariate data considerations PRIMER E and PERMANOVAA4s i 0c ccc ceccesccncccsccnsccsccsccsccuscesccnscesscuscesenseeecs 91 Exercise 8 A multivariate statistical examination of Pohnpei s Marine Protected Areas using PRIMER E and PERMANOVAG 122 Introduction Statistically sound science is required to assess the status of regional and local management efforts ranging from community based marine protected areas to expansive regional networks defined by the Micronesian Challenge Despite having common goals of protecting their resources for future generations jurisdictions throughout Micronesia strongly differ in their approach used to monitor coral reefs and thus in the information that is available for managers to act upon This is in part due to unequal funding and capacity distributed throughout the region As of 2009 monitoring throughout Micronesia ranged from reef check surveys conducted by governmental and recreational divers in Kosrae to seven year programs supporting multiple trained biologists in the Commonwealth of the Northern Mariana Islands and Palau Accordingly the questions being answered statistical power to detect change and the precision of the data differ considerably Houk and van Woesik 2006 Houk 2009 Waddell and Clarke 2008 Recently the 5 political jurisdictions of Micr
159. tribs Cum SCAarus sp 0 00 Tals sies E 5 96 al 5423 2 qum O 00 e e 0 00 Tan 7m n 5 Da Acanthurus nigricauda Chlorurus aordidus Cephalopholus argus Lutjanus gibbus iE meus Chlorurus microrhinos Hacolor macularis IO p Wi in a PRP e ou m om 0 a Hm im uu LO ome e HS LB ci Di iD cm P3 DO cn Co D HH lo res Ctenochaetus striatus 5 08 CI oO E Ek Hipposcarus longiceps mm scroll down the text output sheet so we can see the comparison between the two sites The relevant section was manually highlighted in blue for identification Exercise 7 Managing and Using Data Guidebook 120 Page From this table three columns are most informative The first column has the average biomass from Gachuug the reference site for each fish species The second from Nimpal For now we continue to disregard the next two columns and focus upon the contribution We are most interested in what species contributed to the majority of the difference found in our MDS plot Notice the first four fish cumulatively accounted for gt 50 of the variance the last column tells us the cumulative variance accounted for So we should logically focus upon these three species The most notable difference again is a shift in parrotfish from Chlorurus sordidus very common at the reference site to other Scarids including a mixture of other species of parrotfish besides the common ones as noted by Ya
160. tyles Cells T nan y Chuuk REA invert database PHworking Microsoft Excel PivotChart Tools o x e L gt f Adad 28 esi IN B Average of Sea Cucumber Total B StdDev of Sea Cucumber Total iu P Values Exercise 2 Managing and Using Data Guidebook 25 Page We can also look at the 10m depth and find similar patterns however abundances typically decrease with depth can you find the site with an exception to this pattern In two out of three of the sites where sea cucumbers were most abundant our standard deviation is less than half of our average or mean While any coral reef manager would like lower standard deviation bars this is a satisfactory situation How do our findings translate to future next steps and potential management actions First one commonly applied rule for management is to protect the locations where good resources exist It would be insightful to understand why C 5 C 15 and C 11 hold high resource abundances The two probable causes atiributable to patterns are 1 differing natural environments or 2 human harvesting trends This is where scientists and monitoring teams present findings to communities and knowledgeable individuals to learn and plan for management accordingly If we can understand what conditions lead to high sea cucumber populations than we can identify and prioritize management actions that should be efficient Second long term monitoring focused on sea cucumber
161. v Drag fields between areas below VW Report Filter A Legend Fields ixl Axis Fields Cat X Values Island v Average of Se Y Reef type v Site Za Depth C Defer Layout Update We can now easily see that sea cucumbers are preferably found on the inner reefs as expected but why is there soo much variation among inner reef sites 27 Go to the drop down menu for the Reef type filter pane check only inner Nine sites are left on our graph again you can refer to the map above to understand which inner reefs have the highest abundances of sea cucumber resources Our final step here will be to investigate the quality of our data collection 1 e if we re do the surveys do we have statistical confidence to detect a change especially at the sites were resources are good Exercise 2 Managing and Using Data Guidebook 24 Page 28 On the PivotTable Field List a Click and drag the Sea Cucumber Total again down to the Values box below Average of Sea Cucumber Total b Left click the box one time click on Value Field Settings c Set to StdDev d Confirm below f Cut r1 zi air wives Calibri Body gt 10 fA a gt Wrap Text Text z g Normal Bad a Eu ES E Seis AF aC e zi 3 Fill Formatting as Table T lt 2 Clear 7 Filter Select Clipboard La Ta Alignment IP Number E S
162. ws the variation in the permutated R values that were calculated typically a frequency distribution centered around 0 is desired to show that the procedure was successful 88 Click on the ANOSIM spreadsheet on top of this graph page a Scroll down to the results for the Global Test GS Pohnpei fish MPA PERMAN VA exercise E T Pohnper hMP4 fish PER MANDYA example E y Overall Transform ER log transformed E E Reszemblancel Ef Resembll f most 5 Grapht F Graphe i Design E E Hesemblancez E Bray Curtis similarity PERMAN OAT FERMAMDUVA PERMANOVAS E m Hesemblance3 Exercise 8 75 Graph E m Hesemblance4 E Resem2 5 PERMDISP1 ANOSIM Analysis of similarities One Way Analysis Resemblance worksheet Name Reseml Data type Similarity mpm MILI Factor Values Factor MPA Factor Groups Sample MPA EE Yes TAG Yes EA Yes DSi Yes 58965 Yes 596 Yes SE Yes SSE Yes mas MES 3100 Yes SLL Ho SE Ho ale Na ERE No S115 Ho 5116 Ho 2117 No 3113 Ho 5119 Ho 5120 Na Global Test Sample statistic Global Ri 0 537 significance level of sample statistic 0 1 Humber of permutations 222 Random sample from 92375 Humber of permuted statistics greater than or equal to Global E O ntputs Plot Graphs Managing and Using Data Guidebook 147 Page You can see our H statistic is 0 537 and P value is 0 1 or 0 001 The guidance materials in the PRIMER book describes how to interpret
163. y far the most success seems to be found at MPA L Third although team Pohnpel monitors 16 indicator fish trends are most influentially delineated by only a few fish Namely these are Chlorurus microrhinos Hipposcarus longiceps Caranx melampygus Naso unicornis and maybe one or two others This is understandable because these are relatively large fish that make up a high proportion of Pohnper s fish market catch It in very interesting to learn that fe wer fish may be able to serve as statistically useful indicators for MPA success and these are common with with local names that are well known End of Exercise 3 save the file and keep it open This same file will be used for Exercise 4 Exercise 3 Managing and Using Data Guidebook 36 Page Exercise 4 Beyond examining trends Reformatting an existing database to understand statistical aspects of the data While we have successfully visualized trends regarding fish assemblages from Pohnpei s MPA dataset in Exercise 3 will now take a look at the statistical confidence of these data as we have yet to view any error bars that describe consistencies among transects and sites Because the original database was generated by placing each individual fish measurement in its own row with lots of metadata we will need to re format the dataset to generate summaries at the transect level Recall the transect is our unit of replication within each site It is good to understand the functional

improving data collection, storage, handling, visualization

Contents

Download Pdf Manuals

Related Search

Related Contents