Home

improving local capacity for coral reef monitoring data interpretation

1. PRIMER 6 Yap Nimpal MPA Fish PHworking AGE E File Edit Select View Analyse PERMANOYA Tools Window Help 0 X D a El A X H4 PL foe hele ae Workspace 2 5 YapMim
2. S EE ssi isa js js dest dala STET TT T T ET fests HE SET x nle aor 65 891 3518 magn 5287 1744 59231 u 16444 38913 D 1953 32513 39599 27 425 27581 24746 18586 32799 44818 O 48 75 3692 41567 40808 27748 43811 82852 32231 24 142 1912 22481 30 466 0 43088 B4811 252957 S10 A A 0 0 21894 13825 27791 12952 44582 29403 32346 38457 31984 3656 27573 21278 484561 29897 25987 14426 24365 45175 34923 31806 62557 26218 53282 180522 pi 44193 0 15853 3113 D 2636 3051 gd 3078 o 19437 d484D4 49971 0 0 33 46 D 087 43418 0 22382 0 0 36057 50368 82 686 S15 ol 11 524 22 544 0 40 064 27 175 al 22 398 0 13335 38 902 37 858 70 915 56917 sp zas ame 40172 34704 38785 52172 29295 83708 20128 20022 54159 57993 33814 20 742 25083 28883 49575 20949 17354 104568 42885 2007 62871 18575 ml 33198 38254 20108 22175 14297 54397 41 914 1589 39486 27206 27 508 45522 31862 B2994 26595 0 58 327 39889 38576 46859 26899 55303 897 14505 45148 27584 3362 41511 31783 42684 48426 16837 B3538 36 09 42158 50046 30 152 38932 S20 15 866 29 956 25404 0 49 549 32 049 n 82580 20 418 3383 59356 42 524 44 752 29 941 33298 65 268 sa OI 15381 55338 82472 43783 M444 37957 45578 2593 H 483681 56 228 31427 3482 22058 51076 S22 44 354 14 884 42838 37588 26133 42745 36 126 50 784 24 704 0 513511 39449 18307 20205 12988 66 256 3199 43049 33495 25395 31 095 4354 23505 70337 16 283 1658 BO
3. 45 Page 27 Insert a new Pivot Table and name it Pivot PNP Fish by Transect 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 Ws Report Filter ES a 4 L 4 Row Labels gt Values Column Labels F 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 Exercise 4 Managing and Using Data Guidebook 46 Page 30 Click anywhere inside the main table 31 Insert a basic column chart the one on the top left of the selection menu a Right click inside the chart and move it into its own spreadsheet b Rename the sheet Pivot Graph PNP fish transect c Confirm 8000 7000 8606 5000 4000 3000 2000 1000 4 MT DNP Fish Natahase Pivot Granh PHP fish transect lt Pivnt PNP Fil 1P Pewee PPH BK DI Di2 DOI DO2 Kil EIZ KOI KO2 LIL Liz LOL LO MI MI2 MO1 NOZ MII NIZ NO1 NOZ B Average of Hipposcarus longiceps E Std Dev of Hipposcarus longiceps2 PivotChart Filter Pane
4. L n er a m a 8 daw Md _ 4 al 3 3 1 m m o am D azna won n n 2 S m UN ERE y Row 26 Col 1 33 Once you have selected the appropriate samples navigate to the Select main menu and scroll down to Highlighted Exercise Managing and Using Data Guidebook 102 Page a Confirm below PRIMER 6 Yap Nimpal MPA Fish PHworking fB File Edit Select View Analyse PERMANOVA Tools Window Help ao x Duk 5 a O Ow ln GA Workspace Ed Yap Nimpal MPA Fis Biomass arables S6 n as 13263 Mae 0 eet sey 0 exe o MA Ex MELO Ega gt y e x 7 a a 0 Ken Ao B Ed A np gt 8 580 SGS i NET in a Li D L EN ill L EN am gt PAE 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 more realistic manner as they existed at the study sites Without the transformation dominant fish such as Ctenochaetus striatus would have a dominant influence
5. Nimpal Gachuug fishdata 10m 10m o 0 0 0 U Q 0 155 5967 Q 709 2205 0 246 4307 0 o 0 Q 637 0112 U 3858 08 4526 74 0 o Q 0 Q 0 o 0 0 2790 589 i Primer Prepare Yap Fish2 SEN 16 Delete extraneous rows and columns a Delete Row 1 b Delete Row 77 Exercise 7 H Scientific Name 0 0 0 443 8639 158 9545 449 9913 132 629 2000000005000 0 E 000500 nm o o o 00 o 2000000000500 842 3138 324 5821 292 8198 5000000500050 631 2274 228 1668 127 7007 0 0 224 1723 20000000060 D 163 4656 0 410 0111 E cen o o o 55 19581 1983 238 200000005055 65 32372 0 0 0 0 390 8646 309 8052 183 4658 0 D D 2000000000005 50505000500 1454 777 2098 196 n 252 9171 1063 511 344 1298 344 1298 1526 806 o ce o o 4 1870 162 4038 709 4521 077 9757 544 2618 31 D coz 100 445 294 7546 152 3426 0 954 0168 1327 592 1466 499 1818 498 337 6664 1877 006 1339 763 Managing and Using Data Guidebook 570 2208 456 2791 359 8519 259 5631 347 7459 237 9478 406 2062 387 3948 1310 312 415 0813 2213 739 3 29 498 1887 781 2202 601 0082 163 718 8561 703 0403 2376 361 2132 761 2207 123 64 5614 248 1385 20 77246 302 168 759 1879 238 0775 490 5276 59 80685 335 0197 633 7058 480 38 225 326 1714 0 0 0 310 2472 cccuuo
6. d Confirm below Exercise 3 Managing and Using Data Guidebook 29 Page Ala m x LA me y PEA UA AS Verdana n gt A lla m EU Normal 8 Normal 9 m i am Ay A B z u E IA A Es SIE tel 0 00 Conditional Format Normal Bad Insert Delete Format Sort amp Find amp E ka A Formatting as Table N N M lt 2 Clear Filter Select Clipboard Font c Alignment fa Number ix styles B5 5 fe 70 1058709641017 Ey REALAS yv Pohnpei MPA Tish transects exercise Microsoft Excel PivotTable Tools mm General p Wrap Text rad Merge amp Center EP A L E C N D E di F i G E 2 EI Average of Biomass g Column Labels lx 4 Row Labels Acanthurus lineatus Acanthurus xanthopterus Caranx melampygus Cephalopholis argus Chlorurus microrhinos Hipposcarus l 5 No 70 10587096 71 70777206 182 9699397 148 4329743 178 70312 11 6 Yes 102 6207832 83 72546783 631 2894633 370 3138073 298 9283737 39 7 blank a RIE es Os Sm x 8 Grand Total 79 16740388 82 23654977 389 8866429 244 9029017 241 6320089 247 38 M4 N Site information Charti PNP fish pivot PNP Fish Database Sheeti Sheet2 999 0 0 004 Ready M a 8 Goto the Insert tab off the main menu of Excel and insert a column chart a Choose the stacked column chart one showing cum
7. 81 Change the zoom drop down menu to Fit 82 Confirm our new look of the two graphs A planci abundances in the CMMI ENS m pactstes E NHoR HnpzT s ts Average A planci dere ity per 100m Darig Grazing urchin abundances inthe Cehil EE Hn pacts tes E prn pact z tes Average urchin density per 100rriz Dark 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 lt appears that when 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 Let s 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 com
8. Lobster all spp IL JOctapus IF Actinopyga mauritiana IE ha 2 34 wn Drag fields between areas below w ReportFilter FA Column Labels Es iu Row Labels Values 15 Click and Drag the Island 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 Exercis e2 Managing and Using Data Guidebook 17 Page b Click on Average c See below for a confirmation of these steps J A SSS C D E F G H I J K L M PivotTable Field List 1 2 Choose fields to add to report 3 Row Labels v Average of Sea Cucumber Total Se 4 Chuuk 4 086956522 Holothuria atra 5 Etal 1 166666667 L Holothuria edulis 6 Kuop 0 6 Holothuria fuscopuntata 7 Losap 0 5 Pearsonothuria graeffi 8 Lukunor 0 Stichopus chloronotus 9 Murilo 0 55 L Stichopus hermanni a Nama 0 Thelenota ananas 11 Nomwin 0 025 Thelonota anax 12 Satawan 0 275 7 Sea Cucumber Total 13 bank _
9. 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 83 Page 74 Confirm 2D Graph 2 A planci abundances in the CNMI Average A planci density per 100m2 After Befare ata During EN Average of Grazing Urchin Total2 7 Average of Grazing Urchin Tatal2 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 CNMI 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
10. 0 013092867 o 013092867 o 022059421 0 017005573 0 DATTOD S 0 013092867 0 01306709 0 01306709 0 01447752 EWN 0 024694091 0 022236967 0 022236967 Biomass g 2 974331519 2 974331519 2974331519 34137520668 3 137520668 3 San 2 955475758 Je pal pim ITA SS 3122492248 3 122492248 34121692957 ATT 2 Ex 2 970682336 as 414531504 19 85271227 14 51182063 64 12765942 8 922740354 ai 92758147 11 18 74352648 9 506013651 ETE 108 581928 61 44898613 39 30595609 6 292609376 UNE 6 6932160019 20 78537612 J iz 0 10 cm 10 20 m v 20 wm 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
11. a Promote the headings to titles 39 Confirm AA m SEdDev of Acanthasterz E of Grazing Urch je of Grazing Urcky of Grazing Urch Time FrarrImpack 5i 1 Count of Acanthaste Bverage of Acanthaste StdDey of Acanthast it of Grazing Llrch ge of Grazing Ure of Grazing Urct 17 0 3341 45 0000 47222 3 2710 After Ma 80 0000 0 1168 0 2666 4 2500 0 2361 4 0000 4 5476 5 0386 Before Ma 159 0000 0 1132 0 4462 e 3052 26 0000 e 833 3 0964 During No 181 0000 0 1133 0 2925 181 0000 D G OF E 0 Fr Notice you have to use the lateral scroll bar on the 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
12. 1331941091 11 36900221 1139603005 T 465197028 359800722 3 574271078 0 539125630 5 204164999 3 789450104 1247914925 10413649503 1143360561 17 60305040 16 37770334 10 50 33351 T172915878 b RRAAR318A Delta Percent Change Detected 20 29 45 34509004 Notice that only a 46 change in coral cover can be detected from this first site with statistical confidence however we desired to detect 30 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 sample delta sd Sig level power alternative test power calculation 4 29 29425 13 52 0 05 0 7 two sided NOTE n is number in each group BugWSI L Lestildgelte l189 135 20 13 92 Power 0 7 Two sample n delta sd 210 level power alternative test power calculation 7 619335 19 13 13 92 0 05 D T two sided NOTE n is number in each group gt Now let s focus on the value for n that was calcu
13. Bl 0 55488 ni D 41508 al D Dn B 5817 D um 1 W n nl n al FN Hl 25281 57622 32617 w a AR uu apo r 7 ni ol Di o ni 18347 0 10535 456 28 n D n 58333 n ni n y n n n n al al D 34413 35985 ol n Bp m al ol W 0 44986 Di ol n 410 01 ol 34413 25858 n ol ol ol al Di ol ol 15895 n o wi 7 JH n 15288 34775 Soas 0 D 18835 n i Hu a n pep its n s24s8 ol Dn DJ H 71886 n al n 0 ri al ol E Rar 0 n n 28852 al T W n D 70304 al n al 1 n ol n 213 a n ni 0 D i aj a 23784 al ni n D al ol n 38585 al n D 22847 a a H 1328 dw ol ol n al n e n n n 12778 n n n 22074 Al n n 0 al B n t 7 Yi al n ses al n n 4802 2237 Bl n n n n ol D 24843 o cin al ul 19892 ol 40388 37295 n n n al o 10987 al ol a nl n pl B AB a5mry 18878 K gt n Dn n n 18383 wm a a a wm sa mn we a m n n n n 114 ol n 26183 B582 2 ni 28112 1 al n 1249 6 ol d n 43278 ol 0 n n 0 13276 23808 al 14855 E Vw D a Hl E il a NW ow D D 14665 4980 53 al ol n ni D n o n al D 631 23 D 38086 80486 18185 59807 n al D 246 8 13880 al n n 25803 Wm 7 4 n 30981 00 99787 33502 BD aal n al aa opl wW al ol EN al D 18347 ol 487 83377 B al nil a a N 0 n 158985 n n nl ddl D 10044 64 756 E n m n n o ol H a EN n 0 aj m 0 28475 24814 Oo 19443 D 28006 0 40 931 n al 1024 2 al ol 22447 B5324 D 15234 28772 al Um Ho al Wm n D 43845 ni a p 0 1448 0
14. 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 we want to appear for all lookup list values on the Metadata sheet These will never change b Your database should now look like below p FU T2 3 T Chuuk REA Invert database Microsoft Excel Hame Insert Pag out Formulas Review Vi Add Ins Acrobat B dh Cut Verdana io lt ea ar Ei Wrap Text General B Td 3 Copy j ES S K zammas RT EISE AT E IIT Egwenen nw OE L Farma Clipboard as Font E Alignment Isi Humber IS G5 d fe A B C F TT 1 Island Reef type Wave exposure Depth Site Transect GDS X GPS y 2 Chuuk Channel Low 3m C 1 1 151 8706333 7 429800667 3 Chuuk Inner Moderate 3m amp C 11 1 4 Chuuk Inner barrier Low 3m C 13 2 amp Etal Outer barrier Sheltered 3m C 14 2 6 Losap Outer barrier Moderate 3m C 13 3 7 Losap Channel High 10m C 14 3 8 Murilo Inner barrier Low 10m C 15 4 O Etal Channel Moderate 10m M 4 4 10 Chuuk Inner Moderate 10m M 9 5 11 20 Fill in your 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 th
15. 2 0000 1 0000 1 0000 0 0000 1 5000 5 0000 4 5000 1 0000 2 5000 0 0000 WYW 2005 0000 2 0000 4 0000 2 0000 0 0000 0 0000 0 0000 0 5000 0 0000 0 0000 0 0000 0 5000 0 0000 0 5000 4 5000 1 0000 12 5000 10 0000 3 5000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 1 0000 1 0000 2 0000 2 0000 1 0000 0 0000 0 0000 Managing and Using Data Guidebook 2006 0000 0 0000 0 5000 0 0000 0 0000 2 5000 2 0000 2 0000 1 0000 0 0000 0 0000 0 0000 0 0000 1 0000 0 5000 0 0000 0 0000 0 5000 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 0000 0 0000 0 0000 0 0000 Year 2007 0000 0 0000 0 0000 0 5000 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 5000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 5000 0 0000 0 0000 0 0000 0 0000 0 0000 1 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 ANNON Year 2008 0000 1 5000 0 5000 0 0000 0 0000 0 0000 0 0000 0 5000 B 0 0000 0 0000 0 0000 0 0000 0 5000 0 5000 0 5000 0 0000 0 0000 0 0000 B 0 0000 1 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 5000 0 0000 0 0000 0 0000 e AA 66 Page Now we can clean this up a bit before starting our graph and statistical analyses 16 Right click on Row 1 a C
16. 825 504714 928 3573877 250 0795945 3943 030976 42 20930013 2 651510778 21 42392402 71 45131273 426 7695095 16379 22135 12003 95724 1768 023349 3199 695914 564 1745507 307 8528241 69 32168018 751 4755651 0 0 255 3678554 5 303021557 0 0 Note This is the layout of the table we want to export for further investigation Note lt 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 7 a Click in cell A1 Exercise 4 Managing and Using Data Guidebook PivotTable Field List Yx E Choose fields to add to report UEM g SampeID Y 7 MPA 4 Reef type Y 4 Replicate v Species Ob 4 Biomass g 10 10 cm 10 20 cm 20 30 cm 30 40 cm 1140 50 cm gt 50 cm Drag f elds between areas below W Report Filter Column Labels Spes dd Row Labels E values _SampeID Sum of Biomas Y _ Defer Layout Update 40 Page Select Values Click OK Confirm below 205 p Paste Special Paste O all C3 Formula
17. Caranx melam Cephalapholug Cheilinus unduchlarurus mici chloruruz sor Ctenochaetus Epinephelus m Epinephelus mro 54 Lg D n n n n n n 5 5172 D D Sa U 6 1114 U U 4 558 U U U 6 0093 U U s3 5 0537 4 8351 U U U U U U 5 962 U 5 7422 4 U U U U U U U U 74788 U 3 9075 SS 6 5656 U U U U 4 0258 U U 6 0309 U U 811 D U U 6 2645 U U U U 6 5791 U U 12 6 4554 U U 5 603 U U U U 5 5558 U U U U U U U 7 7746 U U 513 U We will now proceed and create a similarity matrix A similarity matrix compares each individual sample 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 lytj yall 2 yj yey D is the Bray Curtis distance between two samples or transects in our case 2 represents the summation for all fish species and y1 j 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 biomass by the sum for two consecutive transects This is done for all species and all transects by the computer and we end up with desirable measure of distance between each pair of transects In other words the distance tells us how similar two transects were
18. EJ E A Parupeneus barberinus vs Values B Naso unicornis Average cf Biomass a a Naso lituratus B Monotaxis granoculis B Lutjanus gibbus E Lutjanus fulvus Drag fields between areas below W Report Filter EH Legend Fields B Hipposcarus longiceps B Chlorurus microrhinos B Caranx melampygus B Acanthurus xanthopterus B Acanthurus lineatus C Defer Layout Update Exercise 3 Managing and Using Data Guidebook 35 Page We have learned a great deal from our investigations thus far First for inner reefs MPA s D and K do not seem successful as compared with all other three Second by far the most success seems to be found at MPA L Third although team Pohnpei 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 lt is very interesting to learn that fewer fish may be able to serve as statistically useful indicators for MPA success and these are common 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 dat
19. Echinaster luzonicus 14 Grand Total 2 025 EJAcanthaster plana s T Culcita novaguinea 17 Linckia guildingi 18 Linckia laevigata 19 Seastar Total 20 IL Echinometra 21 22 Drag fields between areas below 23 Y Report Filter E Column Labels 24 W 25 26 27 28 29 a iz Row Labels X Values lt 32 Island E age ofSe Y 33 34 35 36 37 I RHENUM C Defer Layout Update I 4 gt M Brainstorm metadata fields Metadata Chuuk REA Invert Pivot Database build Ready Lets also view the standard deviations to understand how the data was spread among the surveyed transects 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 lets 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 b Click on Column and select the first graph option in the top left oor Y E En Change Save As Switch Select Chart Type Template Ron Column Data Type Data Chart 3 E A Values Chuuk Etal Kuop Losap wo o o
20. PivotChart Filter Pane window click on the drop down menu for Depth and leave only the 3m depth highlighted 25 Confirm below T Format Palriter PDA A a ie di a de cen 7 AAA Fig res Tablet OO l Ze Cear Fiter Select Clipboard S Fort E Alignment zl Number S _ Styles Cells S Editing m Ke a pl Total a Active Fields on the PivotChart 35 Report Filter dl Axis Fields Categories q _ 25 Average of Sea Cucumber Total E 20 Crinoid dE F Bohadschia argus B Total Drag fields between areas below W Report Filter Jj Legend Fields rae NT A a 3 a 3 3 3 3 3 3 3 3 3 3 3 a 3 3 3 3 3 3 3 3 8 C 10 C 11 0 12 0C 13 C 14 C 15 C 16 C 17 C 18 C 19 C 20 E 21 C 22 C 23 C 27 C 28 C 3 C C5 C5 C7 CB C9 Chuuk f i i 4b Hj Metadata Chuuk REA Invert Summary ChuukREA Invert Bot A 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 jkKilometers Not surprising the highest abundances were found on Chuuk s inner reefs adjacent to islands of varying population land use and other physical attributes However many similar inner reefs wer
21. x PivotTable Field List rx Active Fields on the PivotChart El W Report Filter io Axis Fields Categories Sample ID Legend Fields Series Values Values Average of Hipposcarus longiceps 5tdDev of Hippascarus longiceps2 H3 wr Choose fields to add to report Es S Acanthurus xanthopterus Caranx melampygus Cephalopholis argus Chlorurus microrhinos 7 Hipposcarus longiceps Lethrinus harak Lutjanus fulvus Lutjanus gibbus Lutjanus monostigma Monotaxis granoculis Naso lituratus Naso unicornis L Parupeneus barberinus Siganus doliatus Siganus puellus 5iganus vulpinus Drag fields between areas below W Report Filter T Legend Fields Ho Axis Fields Cat X Values Sample ID Average of Hip i _StdDev of Hipp gt Defer Layout Update 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 which 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
22. 0 0 0 0 0 38 0 D 0 ol 0 D 0 M 4 K N Brainstorm metadata fields Metadata Database build Sheet2 Sheet4 2 97 a 53 A AE mi Ready i Average 2 025 Count 571 Sum 1053 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 Dothe same sum function b See below for confirmation Uu eet Home Insert a a ee mn Hn Design m ea 3 El m Text to Remove Data Columns Duplicates Validation Data Tools View Acrobat AT TIAI CL Sort Formulas E Refresh AIL Page Salar Data Review Add Ins fara TZ Show Detail L SL E Hide Detail Group TE subtotal Kd MES gt BALE EH Er Er From From From From Other Existing lel 5 Web Text Sources Connections Get External Data Connections Seastar Total NR qp a r gi 1 gt Linckia c gata E seastar Total 55 connections 4i Sp Properties a Edit Links Clear ka Reapply Ennsolt t What If Analysis X7 Advan ced Sort amp Filter _ Outline E m Ed El
23. 0 0 0 0 0 0 O 545 2556 0 Gachuug Reference 2007 Channel 10m 5 Z 0 0 0 0 0 648 0856 Gachuug Reference 2007 Channel 3m 1 B 0 0 0 0 O 333 8632 1436 281 42 28866 0 Gachuug Reference 2007 Channel 3m 2 4 0 0 0 0 0 0 0 0 0 Gachuug Reference 2007 Channel 3m 3 10 o O 527 1049 574 0373 70 11343 0 Gachuug Reference 2007 Channel 3m 4 11 579 1653 0 0 0 0 143 4671 317 4599 0 0 Gachuug Reference 2007 Channel 3m 5 12 0 0 0 0 0 0 0 0 0 Gachuug Reference 2007 Outer 10m 1 13 0 1128 66 0 0 0 0 0 0 0 Gachuug Reference 2007 Outer 10m 2 14 O 1838 304 0 0 0 0 0 0 0 Gachuug Reference 2007 Outer 10m 3 15 0 690 9934 0 0 0 0 0 0 Gachuug Reference 2007 Outer 10m 4 16 0 1249 616 D D 0 2179 418 U U U Gachuug Reference 2007 Quter 10m 2 17 0 0 0 0 0 O 5219 694 0 0 Gachuug Reference 2007 Outer 3m 1 18 0 0 0 0 0 0 0 0 0 Gachuug Reference 2007 Outer am 2 19 0 0 0 0 0 0 1615 408 0 0 Gachuug Reference 2007 Outer 3m 3 20 0 o 0 0 949 0819 0 0 Gachuug Reference 2007 Outer 3m 4 2 0 0 0 0 0 O 1774 951 0 0 Gachuug Reference 2007 Outer 3m 5 22 0 0 0 0 0 0 0 30 97017 0 Gachuug Reference 2009 Channel 10m 1 23 O 40 93125 o 0 165 8322 0 0 0 Gachuug Reference 2009 Channel 10m 2 24 0 0 0 0 0 0 0 0 0 Gachuug Reference 2009 Channel 10m 3 25 o o 0 o 0 0 0 0 0 Gachuug Reference 2009 Channel 10m 4 26 0 405 4788 O 3389 799 0 0 0 0 0 Gachuug Reference 2009 Channel 10m 5 27 0 0 0 0 0 0 0 0 0 Gachuug Reference 2009 Channel 3m 1 28 o 0 0 0 0 0 0 0
24. 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 D 0000 0 0000 0 0000 0 0000 z OOOO 9 2004 0000 0 0000 0 0000 0 0000 0 0000 0 0000 D 0000 0 5000 0 0000 0 0000 D 0000 6 2005 0000 z ULL 4 0000 z UL 0 0000 0 0000 0 0000 0 5000 0 0000 0 0000 0 0000 006 0000 0 0000 0 5000 0 0000 0 0000 zat z OOOO z OOOO 1 0000 0 0000 0 0000 Managing and Using Data Guidebook o 2007 0000 0 0000 0 0000 0 5000 0 5000 0 0000 0 5000 D 0000 0 0000 0 0000 D 0000 9 006 0000 1 5000 0 5000 0 0000 0 0000 D 0000 0 0000 0 5000 D 0000 0 0000 D 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 ann 2001 0000 2002 0000 2005 0000 2004 0000 005 0000 2006 0000 2007 0000 2008 0000 009 0000 68 Page Create Graph Type apr grat Select the type of graph pau want to create Scatter Plat Line Plot tm i a L Line and Scatter Plot points with vertica Vector Plot bars X data are Area Plot assumed Polar Plot E Vertical Bar Chart Harrzontal Bar Chart Box Plot Pie Chart a Click next For this example we have just a simple bar chart with error bars 25 Choose Simple Error Bars Create Graph Styl
25. 0 Gachuug Reference 2009 Channel 3m 2 29 13980 48 o 0 0 0 0 0 0 0 Gachuug Reference 2009 Channel 3m 3 30 13980 48 0 0 0 0 0 0 0 0 Gachuug Reference 2009 Channel 3m 4 L a1 o 0 0 0 0 0 0 0 0 Gachuug Reference 2009 Channel 3m 5 ae 0 0 0 0 0 0 0 2329 665 0 Gachuug Reference 2009 Outer 3m 1 33 0 o 0 0 0 0 0 0 0 Gachuug Reference 2009 Outer 3m 2 A an g y PES A Ba m E as Gachuud Boforonce Myn tar 3 2 i 4 K N Site metadata Fish lookup table Yap fish pivot Nimpal Gachuug fishdata PRIMER import 9742 4 mnm Ready DOS Exercise 7 Managing and Using Data Guidebook 97 Page 20 Save AND Minimize the Excel file 21 Open the PRIMER E Program a Click on the open 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 AI PRIMER Files pwk pri sid agg ppl p All PRIMER Files pw pri sid aga ppl ppd PRIMER B amp 5 Files pw pri sid aga ppl pp PRIMER 4 Files pmi z sim dis T ext Files bet csv Excel Files x27 eles E s HEN Evi All Files v 22 Navigate to your Excel file Yap Nimpal MPA Fish xlsx and click open 23 Click on the dropdown menu that now appears under Excel worksheet Excel File Wizard Yap Nimpal MPA Fish PHworking xlsx E a Select the Primer import sheet we just made b Make sure Sample data is checked Title Data type 24 Click Nex
26. 10 C 10 1 151 7091167 7 3973 0 0 0 0 8 Chuuk Inner Low 10 C 10 2 151 7091167 7 3973 0 0 0 0 Chuuk Inner Low 10 C 10 3 151 7091167 7 3973 0 U 0 1 O Chuuk Inner Low 10 C 10 4 151 7091167 7 3973 0 0 0 3 11 Chuuk Inner Low 10 C 10 5 151 7091167 7 3973 0 0 0 20 Chuuk Inner Low 3 C 11 2 151 788 7 369016667 0 0 1 0 13 Chuuk Inner Low 3 C 11 1 151 788 7 369016667 0 0 0 1 14 Chuuk Inner Low 3 C 11 3 151 88 7 369016667 0 0 0 0 a Change the name to Clam Total Exercise 2 Managing and Using Data Guidebook 12 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 M Fe his a Table2 fThis Row Tridacna crocea Table2 amp This Row Tridacna spp a a M m 1 sland El B Wave exposure lt B pepin El Em ETE Bos E ETE A ca x EE Y S Clam Total lt Crinoids Bd Lobster a 2 amare Inner Low 3 C 10 151 7091167 7 3973 0 0 U d Ey k Inner Low 3 C 10 s 151 7091167 7 3973 0 0 m ol 0 4 Chuuk Inner Low 3 C 10 3 151 7091167 7 3973 0 0 0 U 0 5 Chuuk Inner Low 3 C 10 4 151 7091167 7 3973 0 U U U 0 6 Chuuk Inner Low 3 C 10 5 15
27. 10332 y ps dl 0 001 O96 Re Loi 8 35835 115 A Sic esp 0 001 gag MP Re Lo o 41250 5160 6 JL c AL al luc IPS uan 1 HP Re Lo jj 16 64046 4002 9 2 bazo 0 001 299 Res abla sl Total heyy ao ale ds 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 142 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 type 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 it actually had the highest F statistic suggesting its greater influence compared with Location This is not surprising either as our initial investigation of the MDS plot suggested this Third we see that MPA status did not consistently predict any variance in fish biomass Again gi
28. 181 0000 is iL Section 1 Data 1 a li Graph Page 1 d 5 oy joi 0 1 E zi iL Section 3 al a Data 2 a a e 3 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 ing fields between areas below a Change the Impact Sites filter to Yes Report Filter E Column Labels b Remove all 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 iu Row Labels Z Values COTS Count of Grazi Impact Sites 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 Sili Count of Acanthastei Average of Acanthasteri StdDew of Acanthaster 6 7 8 After Yes 45 0000 0 1556 0 3341 After 2 Before Yes 42 0000 0 0714 0 2361 Before 3 During Yes 96 0000 1 2917 13052 During 4 5 6 7 8 J 10 E Impact Sites Count of Grazing Average 11 Y
29. 396 0000 1 2917 2 3052 Data 1 gt li Graph Page 1 3 TJ Section 3 5 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 1 25 Rename cell 9 1 from COTS to Time Frame Exercise 6 2 Managing and 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 i File Edit Insert View Format Tools Graph Statistics Transforms Toolbox Pharmacology Window Help FX DS m 4800 ABAD ADO D OF Q One Way ANOVA m an L TimeFrame mpact Sil Count of Acanthastei Average of Acanthasteri StdDev of Acanthaster ym After Yes 45 0000 0 1556 0 3341 After No 80 0000 E ST E Z Before Yes 42 0000 0 0714 0 2361 Before No 159 0000 T ca DE aaa al 3 During Yes 36 0000 1 2917 2 3052 During No
30. 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 View Analyse PERMANOVS Tools Window Help D Gr bl dh Ea E Pohnpe fish MPA PERMANOYA exercise ry FahnperMPA fish PERMAMNLU VA example E Overall Transform HEN log transformed m Hesemblancel Hf Resembl f MDS E Graphi EN Graph2 is Design E E Hesemblancez Bray Curtis similarity Exercise 8 mia 51 52 SJ S4 S S Sr s 59 510 311 512 513 514 515 516 e G Y 0 to TOO 51 az ar OF 65 091 52 87 18 53 24 046 36 92 24 142 29 403 14 426 3r 813 35 15 1714 15 444 32 513 19 556 41 557 1332 MAU 32 346 24 365 15 853 11 524 44716 55 38 231 38 313 39 599 32 739 40 506 22 401 38 457 45 175 31 13 33 45 22 544 40 172 S4 2 425 44 818 2 48 30 466 31 884 34 923 34 04 Managing and Using Data Guidebook 55 Sl a 140 Page 59 Go to the PERMANOVA menu a Select PERMANOVA 60 Under Design Worksheet select Design 61 Under Test a Select Main test You can use 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 works
31. A e Fill Paste DUO PPS lt r ve Im a JL en Conditional Format Neutrz Insert Delete Format Sor amp Find amp i Format Painter l7 A pels s zr Merge amp Center S Sk EE asin as Ari Good VHS or P Clear oe Select lipboar Agni ae A a AAA 35 30 25 20 X Values is Average of Sea Cucumber Total E Average of Sea Cucumber Total l StdDev of Sea Cucumber Total B StdDev of Sea Cucumber Total M 10 Drag fields between areas below V Report Filter Legend Fields Values gt M K K Metadata F Chuuk REA Invert oia x 3 Ready REA Invert Summary Chuuk REA Invert Pivo 0 Jeu eo ay 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 c
32. El E Settings Input Message Error Alert Validation criteria Allow Any value e Clear All Cancel 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 El Ed settings Input Message Error Alert Validation criteria Alla List we v Ignore blank In cell dropdown Source Brainstorm metadata fields 14442 Aga Apply these changes to all other cells with the same settings Clear All OK Cancel 10 Click OK This code logically refers Excel to the sheet named Brainstorm metadata fields and says that 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 a 0 Un d aja b Choose any island name for now c Click in cell A3 do the same Populate cells dow
33. Exercise 5 Managing and Using Data Guidebook 60 Page 12 Go back to R and 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 estimate 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 i R Console Two sanple test power calculation n 4 delta 22 32 f056 sd 10 63 sig leve l 0 05 power D T alternative two sided NOTE n is number in each group gt a e PS IN power 0 7 Two sample test power calculation n 5 370644 delta 15 16 ad 10 63 asig level 0 05 power 0 7 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 EES 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
34. Filter Pane X PivotTable Field List Active Fields on the PivotChart E Choose fields to add to report Report Filter Sample ID MPA ID Replicate I7 MPA Reef type Acanthurus lineatus 4 Legend Fields Series Acanthurus xanthapterus values Caranx nius C ephalopholis argus dees _ Chlorurus microrhinos T Hipposcarus longiceps Lethrinus harak L Lutjanus fulvus Lutjanus gibbus Lutjanus monostigma Average of Total Biomass StdDev of Total Biomass2 IL Monotaxis granoculis Drag fields between areas below E Legend Fields wW Report Filter dtd Axis Fields Cat 2 Values MPA Defer Layout Update 49 Page 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 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 und
35. ID DI1 DI2 20 9DO2 25 LIL 30 s LI2 35 SLO1 M 4 NT Site infnrmatinn Sum of Biomass c B Je pb 1 Yes Inner 32 Yes Inner 23 Yes Inner 34 Yes Inner 35 Yes Inner 1 Yes Inner 32 Yes Inner 33 Yes Inner 34 Yes Inner 35 Yes Inner 531 No Inner 32 No Inner 33 5No Inner 34 No Inner 35 NO Inner 1 No Inner 32 EINO Inner 23 No Inner 34 No Inner 35 NO Inner ji Yes Inner 32 Yes Inner 33 Yes Inner 4 Yes Inner 35 Yes Inner 1 Yes Inner 32 Yes Inner 33 Yes Inner 34 Yes Inner 25 Yes Inner 31 No Inner 32 No Inner 33 NO Inner 34 No Inner PNP fish nivnt chart PNP fish nivat Species 7 Replicate MPA Reef type 7 Acanthurus xanthopteru PNP Fish Datahase G GO GO GO 0 5 61549908 34 68837875 0 8 895905473 17 79181095 0 acaceoeeoeoeoeooooo 1103 600024 0 298 1126228 0 G GOGO GOGO GOGO GO Shedi 51 1681023 Ail Ooooooocococooocoocococoooooooooon uocoooocoocoso 22 28788213 14 39864279 106 9436501 1636 499422 0 38 07614397 89 38812819 49 99871473 22 54191114 0 3344 823399 801 6671508 0 0 0 410 4122884 11 52533634 38 20208402 60 24855971 0 3285 179365 0 1613 431607 4839 145245 934 6812259 0 0 0 1476 796345 1476 796345 200 5011135 0 170 6714083 348 2527314 s Cephalopholis arqus Chlorurus microrhinos Hipposcarus longiceps Leth 0 0 278 5780129 0 219 9682947 20 87648147 0 27 58813905 0 0 803 8615862
36. 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 al m 1 Time Frame mpact Sili Count of Acanthasker Average of Acanthaster Med ee E of Grazing Urch je of Grazing Ureche of Gra After 45 0000 0 0495 45 0000 d 7222 E ad bagage 2 Before 42 0000 42 0000 4 5476 data exam 9 41 Section 1 di 3 During 96 0000 96 0000 2 8533 Data 1 li Graph Page 1 ly iL Section 3 Data 2 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 Guid
37. Yes Inner 0 5 615499 0 0 127 6102 124 7121 U 0 o 0 o 0 0 3 51520 q Yes Inner O 26 62324 0 0 109 32 4 557385 0 0 o 0 o 0 0 4 Yes Inner O 5 615499 o 0 100 3902 U o 29 02364 o 0 o 0 0 5 Yes Inner 0 5 615499 o 0 29 53955 0 0 19 85271 o U 0 0 o 1 No Inner 0 0 0 D 246 7601 56 47521 0 0 64 12 66 0 0 56 27397 0 2 Mo Inner o U o D 29 53955 17 85093 0 0 51 64582 0 o 0 o 3 Mo Inner 26 94958 U 0 259 8023 309 14 5 91 17614 0 0 o 0 261 8648 0 0 4 No Inner 166 4284 0 0 127 607 417 0558 153 2935 0 0 o 0 0 0 o 5 No Inner 35 81142 0 0 D 157 0442 274 2833 0 0 0 0 0 D 4 115446 90 46 76 1 No Inner O 86 50376 0 O 1416 244 0 0 0 0 0 D 295 9627 o pivot chart PNP fish pivot PNP Fish Database Sheetl Sheet2 92 oo Ready l i i 17 Rename this sheet from Sheet 1 to PNP Fish Data by Transect Exercise 4 Managing and Using Data Guidebook 42 Page 18 Delete Row 1 a Highlight the rest of the data Ctrl A 19 Go to the insert tab from Excel s main menu and choose Table 20 Click OK 21 Confirm below lu m O me T EU 1 Yes Inner o 0 U 0 22 28 88213 U 2 Yes Inner 0 U U U 14 39864279 0 3 Yes Inner o 0 U U 106 9436501 278 5780129 4 Yes Inner U U U U 1636 499427 5 Yes Inner o 0 U D U 219 9682947 1 Yes Inner U 5 61549908 0 0 38 07614397 20 87648147 2 Yes Inner o 34 68837875 O D 89 38812819 D 3 Yes Inner U 0 0 0 49 99871473 2 58813905 4 Yes Inn
38. a Right click in cell A1 b Select Paste Special and select Values c Click OK 14 Rename the sheet Primer Prepare Yap Fish Exercise 7 Managing and Using Data Guidebook N Choose fields to add to report IS Site Year Code Site Reef Type Z MPA Status Year Depth m v Transect a Yapese Fish name Number Length a lb Biomass Drag fields between areas below w Report Filter e Column Labels dij Row Labels X Values MPA Status Year Reef Type Depth mi Y Defer Layout Update 94 Page o ORTI Os dd lt pre y Yap Nimpal MPA Fish PHwWworking xIsx Microsoft Excel m X wyn i 2 3 y Home insert Pagelayout Formulas Data Review View Addins Acrobat ag oe i Tr Y e mi A E E Spe gt KE ar A 43 Copy f m E n e UL 7 Im ig Fill Paste B U HE r Ay ll Merge amp Center 8 s Conditional Format Bad Good s S Delete Format Sorta Find amp f Format Painter pe NN oes x x E S 9 lE zl Formatting as Table gt Pt ns e Clear Filter Select Clipboard S Font pem m UN Number E Styles E Cells Edil 3 AAA A A A _ e 1 sum of Biomass 2 Site MPA Statt Year 3 Gachuug Reference 2007 Channel amp 3m 13 Outer im 2009 Channel 28 3m 83 Outer im Ready
39. data file after the log transformation a On the left make active the Datat sheet b Rename to log transformed 30 Confirm P PRIMER 6 log transformed p File Edit Select View Analyse PERMAMOVA Tools Window Help D gt E de Ea Fahnperfish HPEA PERIMANCUNVA exerc lt TN PahnpeiMPA fish PERMANCUNVA e El Ek Overall Transform zl ry log transtormed E E Hesemblancel Sy Reseni HIOmass f ust Graph Graph Now with this sheet active 31 Highlight all the data by clicking in the box above S1 and to the left of Acanthurus lineatus The data sheet should change color 32 Scroll down to S1260 a Click on that row That row should change to a different color Exercise 8 Managing and Using Data Guidebook 133 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 analyses Note Check to ensure that S126 is no longer there 34 Confirm O c E b Es m E Pohnpertish MPA PERMANOVA exerc m Pohnpei MP4fish PERMANOVA e E E Overall Transform SO log transformed E E Resemblancel pH EO e Biomass Acanthurus lir Acanthurus 3 Carans melam cephala Bf Resem 593 0 5 7292 p f MDS1 S84 0 sasa 0 5 Graph 595 0 o F Graph2 S96 0 3 1418 0 S87 0 E 0 598 0 5 599 0 o D 3100 0 0 0 4 5101 0 0 0 5102 0 0 o 5103 0 0 0 510
40. eene 51 Exercise 6 1 An introduction to creating report quality graphs and preparing data for univariate statistical analyses esses 63 Exercise 6 2 Conducting basic univariate statistical analyses and producing informative professional quality graphs to show your trends 74 Section 3 Multiyarrate statistics and crap hin the T6SUltS oes Leestos E nue Ga CDU A 91 Exercise 7 An introduction to multivariate data considerations PRIMER E and DL KM ANOWAR 91 Exercise 8 A multivariate statistical examination of Pohnpei s Marine Protected Areas using PRIMER E and PERMANOVAG 124 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 Pal
41. 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 I1 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 surveyed You can just enter values of your choosing A 1 TEL CTS gt ATTE gt Depth gt ET gt ETT Mas My NN HolothiE2 2 Chuuk Channel Low 3m ES 1 151 8706333 7 429866667 25 3 Chuuk Inner Moderate 3m C 11 1 151 788 7 369016667 33 4 Chuuk Inner barrier Low 3m C 13 2 151 5917333 7 47655 2 5 Etal Outer barner Sheltered 3m C 14 2 151 58505 7 4684 3 6 Losap Outer barrier Moderate 3m C 13 3 151 5917333 7 47655 4 7 Losap Channel High 10m C 14 3 151 58505 7 4684 5 8 Murilo Inner barrier Low 10m C 16 4 151 4476667 7 396533333 6 9 Etal Channel Moderate 10m M 4 4 153 7878667 5 531483333 66 10 Chuuk Inner Moderate 10m M 9 5 153 5405833 5 404633333 54 11 Satawan Outer barrier High 3m C 12 3 151 5751 7 471166667 Exercise 1 Managing and Using Data Guidebook 9 Page 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 Us
42. mb CO NJ Lukunor 10 Murilo 11 Nama 12 Nomwin 13 Satawan 14 blank 15 Grand Total M 4 M Brainstorm metadata fields Ready Chuuk REA invert database PHworking Microsoft Excel PivotChart Tools 4 086956522 1 166666667 0 6 0 5 Metadata Chuuk REA Invert Pivot a Row Labels y Average of Sea Cucumber Total StdDev of Sea Cucumber Total 11 69074969 2 520034665 0 855005545 0 731083277 0 2 074880287 n ELI B Average of Sea Cucumber Total E StdDev of Sea Cucumber Total Let s move the chart to a new sheet for simplicity 20 Right click in the chart and select Move Chart Refresh Data Cut Copy Paste lE Reset to Match Style Font Change Chart Type lr TRER CS Select Data i8 E E Assign Macro f Format Chart Area Exercise 2 Active Fields on the PivotChart J B 7 Report Filter io Axis Fields Categories Island x B EH Legend Fields Series values 4 X Values Average of Sea Cucumber Total StdDev of Sea Cucumber Total O Formulas n no Ni Ad peas smi im teet Ame O n aiu 5 hh d hb bd bd bd b Chart Chart Layouts Chart Styles J Location B ey A gt E F G H Wp PivotChart Filter Pane X PivotTable Field List Choose fields to AM to moves Holothuria a Holothuria edulis _ Holothuria fuscopuntata Pearso
43. of your desired sample transects 79 Next under Analyze a Go to Resemblance and b Create a Bray Curtis similarity matrix 80 While keeping you matrix sheet active a Go to Analyze again and b Select MDS plot 81 Click OK for the default settings 82 From the graph page select Data labels amp symbols a b On the right select Combined name from the dropdown menu C d Exercise 8 Uncheck the Plot box for labels Click OK Confirm your informative plot Transform Log X 1 Resemblance S17 Bray Curtis similarity 2D Stress 0 18 Combined name MOuterYesMl3 x MOuterYesMl4 w MOuterNoMO3 E1 MOuterNoMO4 Managing and Using Data Guidebook 147 Page From this MDS plot it appears there are strong differences between individual sites but also between inside and outside the MPA s This is exactly the situation we thought was occurring from our initial analyses However we have to consider how these MDS plots are made before we wonder why non significant findings were made in our PERMANOVA above where both sites were combined within each MPA status prior to examining for significance 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 described in E
44. on the left hand side a Scroll down to New and choose Worksheet 15 Right click in cell 1 7 and choose Paste 16 Confirm SigmaPlot Data 2 File Edit Insert View Format Tools Graph Statistics Transforms Toolbox Pharmacology Window Help Dart Bll b X Rm Lo c El A G SMS SA EDD P JAE amp One Way ANOVA 1 Data E AU OGEIENOEPDDONS 2 COTS Impact Sites Count of Acanth Average of Acar SEdDev of Acant Mo 3 After Yes 45 0000 0 1556 0 3341 Data 1 4 Before Yes 42 0000 0 0714 0 2361 li Graph Page 1 5 During Yes 96 0000 1 2017 2 3052 EL Section 3 6 Data 2 7 8 9 10 11 E I Exercise 6 2 Managing and Using Data Guidebook 75 Page 17 Right click on row f and delete this 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 SigmaPlot Data 2 File Edit Insert View Format Tools Graph Statistics Transforms Toolbox Pharmacology Window Help arth Sit Be Blo ce es a Ten EE ES S Swim _ Ol 0 D One Way ANOVA os O ES ini TE m 1 Time Frame 2 Impact Sites 3 Count of Acanthaster 4 Average of Acanthasterz 5 StdDev of Acanthaster3 5 After Yes 45 0000 0 1556 0 3341 E ia kans 2 Before Yes 42 0000 0 0714 0 2381 data exam ci n P 3 During Yes
45. 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 PCO plots correspond to PERMANOVA test results 89 Highlight the Resem 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 MOuterYesMl4 93 Click OK y MOuterNoMOS 94 From the graph page select Data labels amp 40 O MOuterNoMO4 symbols a Uncheck the Plot box for labels b On the right select Combined name from the dropdown menu 95 Click OK 96 Confirm your informative plot noting that red O e PCO2 23 3 of total variation circles and black dashed lines were only drawn 0 in here for explanatory purposes described below 20 40 40 20 0 20 40 PCO1 35 9 of total variation Exercise 8 Managing and Using Data Guidebook 150 Page We can see clear similarities between our PCO plot and our MDS plot To better understand why we didn t get significant findings in our PERMANOVA you can envision the two data clouds circled above These represent 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 mul
46. 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 considerations 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 unioariate statistical analyses So far we have been using Excel to generate our visual graphs because of the easy manipulation of data through the PivotT able 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 indivi
47. the Brainstorm metadata 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 Al Sort Filter T Text to Access Web Text Sources Connections Al Edit Links Advanced Column Get External Data Connections Sort amp Filter A1 i fe Island A B C D E F G 1 Island ef type Wave exposure Depth Site Transect GDS X 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 the List Data Validation
48. the samples noted above 77 Go to the Select menu a Select highlighted 78 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 BIOImass Acanthurus lim amp canthuruz ni canthurus tr Caranx melam Cephalopholug Cheilinus undd Chlorurus mic Chlorurus sort Ctenochaetus Epinephelus m Epinephelus m Grouper Hippascarus Why phosus Lutjanus gibby Lutjanus tt S25 U 5 0749 U U U U U 4 6195 4 156 o U U U U U SY U U U U U U U 5 5095 5 518 U 3 0177 U 5 6386 U 3 736 528 U 63327 U U 5 4169 4 1945 U 5 0327 3 324 o U U U U U 529 U 7 241 U U U U 7 3037 U 5 7143 o U U U U U S30 T 1062 U U U U U 6 8617 6 6336 o U U U U 6 0075 561 ERES n n n n n n n 3 8227 n n n n n 5 5061 SB2 U U U U 3 9545 U U U 5 0746 U U 6 2201 U U 6 5396 SB3 o U 2 9997 U U U 3 3788 o U U U U U 564 U o U 5 1371 U U U 5 437 0 U U U U 555 U U U U U U U 4 4177 o U 5 1634 U U U 79 Go to the Analyze menu a Select SIMPER which is short for analyses of similarities Exercise 7 Managing and Using Data Guidebook 121 Page SIMPER Design Measure 2 One way 2 Bray Curtis similarity O Two way crossed C2 Euclidean distance Factor E Site w List only higher contributing variables Cut off percentage 30 80 Under Factor A a Select Site from the drop down menu So we can determine differences can leave the defaul
49. 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 Yap 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 surveys 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 123 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 protect
50. 0 Gachuug Reference 2007 Channel 3m is 511 Gachuug Reference 2007 uter 10m 1 BK 1512 Gachuug Reference 2007 i Outer 10m la 31 31 Gachuug Reference 2007 Outer 10m 3 14 Gachuug Reference 2007 Outer 10m 4 L 81 3 Gachuug Reference 2007 Outer 10m IE Help S16 Gachuug Reference 2007 Outer 3m 1 151 Gachuug Reference 2007 Outer am 2 15181 Gachuug Reference 2007 Outer 3m 3 S19 Gachuug Reference 2007 Outer am A S20 Gachuug Reference 2007 Outer 3m 5 521 Gachuug Reference 2009 Channel 1 m H S22 Gachuug Reference 2008 Channel 1 Dm 2 523 Gachuug Reference 2008 Channel d m la S24 Gachuug Reference 2009 Channel 10m 04 525 Gachuug Reference 2009 Channel 40m 5 5261 Gachuug Reference 2008 Channel Imo 1 1541 achu T Reference 2009 Channel 3m 1 2 5281 Gachuug Reference 2009 Channel 3m 3 529 Gachuug Reference 2009 Channel 3m Ir Sa Gachuug Reference 2009 Channel 3m a 531 Gachuug Reference 2009 E Laia am H TERN Gachuua Reference 2009 Outer 3m ME 29 Click OK Now we are set for our analyses with PRIMER 30 Save your workspace as a new PRIMER file called Yap multivariate fish exercise Exercise 7 Managing and Using Data Guidebook 100 Page 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 f
51. 0 0 0 10 FMKSA04115 1 9 28 2007 12 5 0 25 0 0 0 0 10 60 0 0 0 10 0 0 0 11 FMKSA04115 2 9 28 2007 12 5 0 0 0 0 0 0 7 5 77 5 0 0 25 0 0 0 0 12 FMKSA04115 3 9 28 2007 20 0 5 0 0 0 0 5 55 0 0 0 25 0 25 0 13 FMKSA04115 4 9 28 2007 12 5 0 0 0 0 0 5 17 5 50 0 0 0 125 0 0 0 14 FMKSA04120 1 104 2008 15 0 0 0 0 0 0 25 62 5 0 0 5 75 0 0 0 15 FMKSA04120 2 10 1 2008 10 0 0 0 0 0 0 5 67 5 0 0 0 7 5 0 0 0 16 FMKSA04120 3 10 1 2008 75 0 0 0 0 0 0 25 80 0 0 0 25 0 0 0 17 FMKSA04120 A 10 1 2008 10 U A TA U U U 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 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 U 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 detec
52. 06 PM Data source Data 4 in CHMI data example JNE Group Name N Missing Row 1 45 U Row 2 A U Row 3 B U source of Variation DF Between Groups E Residual 160 Total 152 Mean std Dev SEM 4722 3 271 0 488 4 548 3 039 0 777 2 083 3 096 0 316 T Ms F P 298 05 149 252 11 090 lt 0 001 2422 495 13 458 2720 99 7 The differences in the mean values among the treatment groups are greater than would be expected by chance there is a statistically significant difference P lt l 0011 Power of performed test with alpha 0 050 0 921 All Pairwise Multiple Comparison Procedures Fisher LSD Method Comparisons for factor Comparison Diff of Means L5D alpha U 50 P Diff L5D Row ws Rou a 2 6372 1 202 0 001 Yes Row 1 ws Row 0175 1 553 0 325 Ho Row 2 ws Row 3 2 464 1 339 0 001 Yes Exercise 6 2 Managing and Using Data Guidebook 88 Page Note on this sheet the groups are referred to as How 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 8 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
53. 1 7091167 7 3973 0 0 0 U 0 7 Chuuk Inner Low 10 C 10 1 151 7091167 7 3973 0 U U U U 8 Chuuk Inner Low 10 C 10 2 151 7091167 7 3973 0 0 0 0 0 9 Chuuk Inner Low 10 C 10 3 151 7091167 7 3973 0 U U U 1 10 Chuuk Inner Low 10 C 10 4 151 7091167 7 3973 0 0 0 U 3 11 Chuuk Inner Low 10 C 10 5 151 7091167 7 3973 0 U U U 20 12 Chuuk Inner Low 3 C 11 2 151 788 36901666 0 0 1 1 U 13 Chuuk Inner Low 3 C 11 1 151 788 7369016667 0 0 0 U 1 14 Chuuk Inner Low 3 C 11 3 151 788 36901666 0 0 0 0 0 15 Chuuk Inner Low 3 E 11 4 151 788 7 369016667 0 0 0 U 0 16 Chuuk Inner Low J C 11 5 151 788 7 369016667 0 0 0 U 0 17 Chuuk Inner Low 10 C 11 1 151 788 36901666 0 U U U 0 18 Chuuk Inner Low 10 C 11 2 151 788 3690166D 0 0 0 O 0 19 Chuuk Inner Low 10 C 11 3 151 788 36901666 0 U U U 20 Chuuk Inner Low 10 C 11 4 151 88 36901666 0 0 0 U 0 21 Chuuk Inner Low 10 C 11 5 151 788 7 36901666 0 O 0 U U 22 Chuuk Outer barrier Moderate 3 C 12 1 151 5751 7 471166667 0 0 0 0 0 23 Chuuk Outer barrier Moderate 3 E L2 Z 151 5751 4711bD66 0 U U U U 24 Chuuk Outer barrier Moderate 3 C 12 3 151 5751 7 4 1166667 0 0 0 U 25 Chuuk Outer barrier Moderate 3 5 12 4 151 5751 7 4 1166667 0 0 0 U 1 26 Chuuk Outer barrier Moderate 3 C 12 5 151 5751 47116666 0 D D D O 27 Chuuk Outer barrier Moderate 10 C 12 3 151 5751 7 4 1166667 0 U 1 1 0 28 Chuuk Outer barrier Moderate 10 C 12 1 151 5751 7 471166667 0 D 0 D 5 29 Chuuk Outer barrier Moderate 10 C 12 2 151 5751 7 4 116666
54. 1031 17 36388221 11 96 7930605 T 465137028 T 359800722 9 574271078 0 533125630 5 204164999 9 769450104 12 47914925 18 41364383 11 43368561 17 603056040 16 37770334 10 50783351 T T72915879 6 004463104 3 555525183 Overall HC average 50 52083333 Managing and Using Data Guidebook Overall HC standar deviation 1062544548 58 Page b Confirm Fie Edit View Misc Packages Windows Help l R Console Two sample I delta sd sig level power alternative test power calculation 4 2d ore 10 63 0 05 0 75 two sided NOTE n is number in each group gt pouer t test n 4 zsd 10 53 power 7 Two sample Tl delta ad sig level power alternative test power calculation 4 22 37056 10 63 0 05 D T Lwc S 1 ded 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 delt
55. 198 47542 27776 16482 21465 82781 17 723 36107 53978 34751 59348 pors 3433 53278 23505 22917 56588 48137 4782 34285 34298 63 19 45151 14 524 51 552 71 981 T 22572 34853 2307 23855 o 23 46 27 708 0 0 a 29772 14348 34252 32246 18 337 38 14 5396 38885 58 2 49416 58474 45747 44993 30358 21181 2303 55482 18473 23177 52711 33788 52018 54982 515658 53 228 39685 22839 58181 45D75 55817 5484 39553 51435 23229 18459 43923 43281 1971 59465 87798 43339 59056 25768 28193 35658 12 922 146689 85052 39528 529 42 521 18 407 48 861 52 994 0 39 458 59 408 26 42 63 602 29 38 34 186 23255 10 152 11337 5 9738 25579 38421 32396 37734 48083 0 58243 46333 48522 428588 13562 24772 35251 18825 18593 11719 38843 16 089 13128 45205 35872 31398 53843 30181 38253 42148 14747 53109 58945 44944 51672 33111 44 04 215 13185 25 999 n 34153 31485 0 52086 0 0 37948 40758 28 265 284 21121 54748 435 maa Ta 35847 U 39219 38 345 17248 28898 3128 25502 47519 1757 21105 32135 23178 1395 11476 19856 338932 0 40807 45074 17836 4778 45345 35299 44952 18 162 21 871 13872 23889 ess 404 aresg areo 25813 B5355 39073 30550 42431 20373 22 507 46385 48485 22585 15488 18455 56142 Exercise 8 Managing and Using Data Guidebook 129 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 c
56. 26 10 826 30 841 45 and 561 65 These correspond to both Nimpal and Gachuug samples channel reef types only and 3m depth only 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 30 Confirm below Exercise 7 Managing and Using Data Guidebook 101 Page PRIMER 6 Yap Nimpal MPA Fish PHworking x x B Be Edit Select View Analyse PERMANOVA Tools Window Help A Ose S5 LRR O Og Rie we ES Workspace f Y ap Nimpal MPA Fis Biomass vanables qq AAA A gt MMM AAA AA A N e d o MES seas 28 oaf mms d a aa o n meas 4 oO o A o n e Y 1 NE 7 D AS 2 4577 N n n V rw ps E A SESE A Ao eee SO uiuo Ac NENA 842 31 1870 2 221 3 a1 A a 4038 8 a 0 n 1128 7 Gere e pru er lt 39 8 or mH NS ms a A A a cw e B 33 592 0 BNR O 31 E 2 O 3 ml o em A o ASEN BEEE LANS Mo E E E O 345 18 4ms B n 0 14 B a A e AA A MA A li as S 9 0 39M NUS 3 8 9 8 8 3 8 Te AM 0 ee CAM OL 3 2389 M m X mM 0 Ama m RET MENO 1394 5 12186 w 4 7 148448 D S A 0 0 asta L i mesi RI AA 18242 2 Al 1 2 W
57. 30217 W M oj al n Y p al amp D 42188 aj ol ol Bl qi g54n2 75949 al al n n 40548 ao 27905 ul D n 0 D 28882 13388 48038 al Be are 0 a n n n 88237 m qa ol n H m H 10333 E pl n o pl n ol O ae al al a lt B BT o 38 925 182442 p ao a mii o ai m 598 38 al n n 22817 D 43757 15708 Hi H HN cum n n D 4063 7 n ml n n m al 1552 1207 Ml nl o n n al ol rar n dE n al al o 223688 11848 al ni ni n a E n ooo W n n a ol o B asn pl mme o al H ol ni n Q al H Di R mn ul Bel aa al T 1 al TAM m el Row 7 Cold Exercise 7 Managing and Using Data Guidebook 99 Page We will now view our factors that we wish to use in our analysis 28 In PRIMER Click on Edit menu and scroll down to and select Factors Confirm with screen shot below These are all of the metadata columns in our excel sheet that we moved to the right of our data block Factors Edit Add Label Site MPA Statuz Year Reef Type Depth tmi Trarisect 51 GachuLg Reference 2007 Channel 10m 4 Er 521 Gachuug Reference 2007 Channel 10m 3 33 Gachuug Reference MEA Channel 10m E B 54 Gachuug Reference 2007 Channel 10m 4 55 Gachuug Reference ur Channel 10m a SB Gachuug Reference 2007 Channel 3m 1 S7 Gachuug Reference 2007 Channel 3m dee Kew 501 Gachuug i Reference TH 2007 Channel am 7 3 SS Gachuug Reference 2007 Channel am 4 a S1
58. 37 2 69 32168018 B m 38 3 751 4755651 efes Layout Update M 4b NT Site information PNP fish pivot chart PNP fish pivot PNP Fish Database Shed ii 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 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 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 Oa Wc gt Pohnpei MPA fish transects exercise Microsoft Excel PivotTable Tools mom x i Home Insert Page Layout Formulas Data Review View Add Ins Acrobat Options Design ax B A cut Verdana 110 IARA Wrap Text General E dA Normal 8 Normal 9 s EL pw AC d Autos AT BF a Copy Z m Sel ss i Fill Z s Paste f Format Paintes B z UE 4 S 4 E Merge amp Center lt 5038 Seira bbc Normal Bad gt Insert Delete Fo
59. 4 Exercise 8 Hesemblancel Fiesembll f MDS1 5 Graphi 5 Graph2 Design Resemblancez ff Bray Curtis similarity PERMANDWVAI PERMANDWVAZ PERMANDWVAJ Hesemblance3 if Resem f MDS2 F Graph3 5 Graph i Design3 E PERMANOVAS EE Graph Hesemblance4 S PERHMDISP1 ANOSIM Analysis of Similarities One Way Analysis Resemblance worksheet Mame Reseml Data type Similarity selection 411 Factor Values Factor MPA Factor Groups sample MPA 591 Yes Saz Yes 593 Yes e Yes 385 Yes 3285 Yes ao Yes 2380 Yes nog Yes 2100 Yes al aL al No 3112 No 2113 Ho 2114 No SLLLS No 5116 No 3117 No SLE No 2119 No 5120 Mo Global Test Sample statistic Global Ri 0 537 significance level of sample statistic 0 1 Number of permutations 999 Random sample from 92378 Mumber of permuted statistics greater than or equal to Global E O Dutputs Plot Graphs Managing and Using Data Guidebook 149 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 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 may be due in part to the non parametric ranking procedure Our last
60. 4 0 ui M 5105 7 0 B 5106 0 n 0 3 5107 0 0 M 5108 0 0 0 3 S109 0 o 0 3 S110 o 0 0 3 5111 ol 0 D S112 5 5001 0 0 3 3113 0 D 0 5114 n 0 0 S115 5 7022 o 0 S116 0 0 0 8117 n ol JS 5118 n 0 0 5118 D 0 5120 55883 0 0 SH a 0 0 5122 n o 0 5123 n n 0 5124 0 0 7 3533 5125 LA 0 0 5127 47782 o n 5128 5 0296 o 0 5128 n al 0 5130 o 22921 D S131 0 o 0 lii UE Exercise 8 Managing and Using Data Guidebook 134 Page Now we are ready to design our PERMANOVA analysis This is just like designing any standard ANOVA 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 figure in the introduction above 38 Select 4 factors 39 Click OK PRIMER 6 Design1 z Eie Edit View PERMANOVA Tools Window Help eh 2 Ba k SEN T S Pohnpeitish MPA PERMANOWA exerc IPN D MPA lt TN Pohnpei MP fish PERMANOVA e P Mestedin lFixedirandom HYT m Overall Transform TERESES 7 lt TN log transformed Hesemblancel la Resemi f MDS Graphi Graph ED Design 40 Double click in the first cell below Factor you will notice a drop down menu appears a Select Location 41 ln the cell below Location a Select Reef type 42 Continue down the column sele
61. 42758 100 54 n al ol n0 E 45529 ol n al 14224 n D 432825 n mn TT n al n Hd 88885 of n n n Ql D 1432522 0n 28432 32852 n p al n 53883 0 BOD n n n D i n 35118 D axm 853580 n o Bl nh ml al 20784 n al Ooo 428 39 of 40827 al 0 15285 al a Do 56433 ol 18533 51385 n 30286 0 E R n w nl oO 186545 n n dp cam D A4875 28484 n Bi n ni S n al nl n 2005 n 0 Hl al ol 1581 10177 a al ol 485 BR F n D n BD 56214 n di al al o 65584 30751 o n m a s m Sow T Coll H Note Check to ensure that the factors have all been imported too Exercise 8 Managing and Using Data Guidebook 126 Page 8 Under the Edit menu a Select factors 9 Confirm below Factors Edit Add Combine Rename Reorder Delete Keu Cancel Help Label 91 Sz ed 34 55 Sb af S8 ed S10 511 5121 513 5141 15 5161 ES t Location D D D D D D D D D S D D D D p n 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 at a later stage in this exercise Exercise 8 Managing and Using Data Guidebook 127 Page 10 Click on the Combine box
62. 500 4 26305 83373 50915 25388 RENAJ A ABAE RA O AAA 21501 47622 16 694 57 096 3104 4084 50168 25246 2897802 43128 74054 22828 3554 38753 31148 46385 23954 B581 YTB5d9 28803 42103 65172 58887 432 72 7197 540 HAB T2827 PRI RR SAT 48534 MATS 11490 389 FATS SIA 34554 69458 51 46 13003 22427 10936 99062 36077 10985 12833 49 379 38775 29 279 11516 13518 10954 11922 10 638 24 B5B 18 146 22 059 20 993 1824 20 325 2701 26233 16549 24558 18782 27191 13 359 24 304 20 932 44 ss 2350 45379 19435 38805 31 48 19496 22704 DBI3 14502 20099 45358 24778 42358 21227 45735 39 23865 25537 17 264 14 619 1283 17344 22 831 88272 11881 18564 189271 25861 17312 20185 16461 46 SED 234467 45898 37683 17554 15834 3T8D4 23507 25087 26658 20773 20308 2570 14384 21858 38447 dB ve f gt Now we have a data matrix that compares every possible combination of transects and provides a distance measure of ecological similarity for each comparison Exercise 7 Managing and Using Data Guidebook 107 Page 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 scal
63. 6 499 0 0 100 4545 o 0 0 0 M 5 Yes Inner U 0 0 0 219 9683 0 0 0 0 o 0 O 8 63118 B DI2 1 Yes Inner O 5 615499 0 O 38 07614 20 87648 0 0 0 0 o 20 53927 0 E 2 Yes Inner O 34 68838 0 O 89 38813 0 0 0 o 0 0 0 0 A0 3 Yes Inner 0 0 0 0 49 99871 27 58814 0 0 0 O 20 53478 0 O 5 68841 ETE 4 Yes Inner 0 8 895905 0 0 22 54191 0 0 0 0 0 0 0 0 12 5 Yes Inner D 17 79181 0 51 1681 o 0 0 0 0 0 0 0 0 34 6498 _13 DO1 1 No Inner o U U 0 3344 823 803 8616 o 0 0 0 196 3264 0 0 411 437 14 2 Mo Inner o U 0 0 801 6672 825 5047 0 0 0 0 0 4439 441 D 15 3 Mo Inner 0 U 0 0 O 928 3574 0 0 0 0 0 56 27397 O 17 3249 16 4 No Inner o U 0 0 0 250 0796 0 0 o U 0 91 62068 o de ad 5 Mo Inner o 0 0 0 0 3943 031 0 0 O 261 5679 0 56 27397 869 6052 17 3249 18 DOZ 1 No Inner 0 0 o 0 410 4123 42 2093 0 0 0 0 97 11944 0 0 47 9384 19 2 No Inner 0 0 o 0 11 52534 2 651511 41 63865 0 0 D 12 31149 0 0 3 No Inner o 0 o D 38 20208 21 42392 0 0 o D 36 47698 0 o 4 No Inner 0 0 D 60 24856 71 45131 0 0 0 0 0 0 0 5 No Inner 0 0 o 0 O 426 7695 0 0 o 0 24 62299 0 D 34 6498 i Yes Inner 26 94958 U 0 D 1361 965 162 938 U 0 0 0 o 0 0 2 Yes Inner 40 80056 0 0 0 226 503 20 78538 0 0 o 0 100 0216 0 0 3 Yes Inner 58 94294 U 0 O 208 8142 35 72566 0 0 D O 822 4037 0 0 168 206 4 Yes Inner 26 94958 U 0 O 188 4802 232 2597 0 0 o 0 0 0 O 61 4489 5 Yes Inner 183 026 U 0 O 446 015 0 0 631 3982 D 0 0 0 0 ies Inner o 71 12902 0 41 08601 22 28788 09 32168 0 0 o 0 0 0 O 9 20361 J
64. 7 0 U U U Z 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 NI MEE P NL IE MAT BI TRR L p FEE ET B uc N MA LM 1 4 Ly pe LLL YLIEEILELIMTFI TAL UM ES Get External Data Connections Sort amp Filter Data Tools Outline AC1 i gt fe Sea Cucumber Total a 1 Aca C a SY TTT ST enn na TTT TTS ej 0 0 ol D 0 o O f 0 0 0 0 0 0 0 0 0 0 0 ol D 0 0 0 P 0 0 0 0 0 0 0 0 0 0 o ol 0 0 0 0 0 0 0 0 10 0 o 0 0 0 0 0 0 0 o 0 0 1 0 0 3 0 0 1 0 0 0 ol 6 0 O 0 0 0 0 0 6 0 0 0 0 0 0 ol 8 0 0 0 0 0 0 0 8 0 o 0 0 0 0 ol 3 0 0 0 0 0 0 0 12 0 O 0 0 o 0 0 6 0 0 0 1 O 0 0 11 0 o 0 0 O 0 0 7 0 0 o 1 1 0 0 7 0 O 0 0 0 0 0 4 0 0 O 0 O 0 0 19 0 o 0 0 o 0 ol 0 0 o 1 0 0 0 0 1 0 O 0 0 0 0 ol 0 0 0 O 0 0 0 0 1 0 o 0 0 D 0 ol o 0 0 D o 0 0 o 0 0 o 0 0 0 0 0 D 0 0 0 1 0 0 0 0 0 0 o 0 0 D 0 ol 0 0 0 D 1 0 0 0 0 0 0 0 0 2 9 0 ol 9 0 0 0 3 0 0 0 0 0 0 0 0 4 3 0 0 ol 2 0 0 1 E 0 0 0 0 0 0 0 0 5 0 0 0 o 0 0 D 0 0 0
65. 8 151 5917333 151 58505 151 5917333 151 58505 151 4476667 153 7870667 153 5405833 H GPS y 7 429866667 7 369016667 7 47655 7 4684 7 47655 7 4684 7 396533333 5 531483333 5 204633333 GPS y 7 429866667 7 369016667 7 7655 7 4684 F 47655 46B84 396533333 5 531483333 2 4046333331 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 Island Chuuk Chuuk Chuuk Etal Losap Losap Murilo Etal Chuuk u fe ad A pla o e n j FP 25 Do the same with reef type E 7 429866667 7 369016667 7 47655 7 4684 7 47655 7 4684 396533333 5 531483333 E Reef ty ne E wave exposure perta Elsie anses Desy E Channel Low C 1 1 151 8706333 Inner Moderate m C 11 1 151 788 Inner barrier Low 3m C 13 2 151 5917333 Quter barrier Sheltered 3m C 14 2 151 58505 Outer barrier Moderate 3m c 13 a 151 5917333 Channel High 10m C 14 3 151 58505 Inner barrier Low 10m C 16 4 151 4476667 Channel Moderate 10m M 4 4 153 7878667 Inner Moderate 10m M 9 5 153 5405833 F3 5404633333 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
66. 8 Right click the highlighted cells and select cut Exercise 7 Managing and Using Data Guidebook 96 Page 19 Scroll to the right of your sheet until you get to column AD a Right click in cell AD1 and select Paste b Rename this sheet now to PRIMER import c Scroll back to columns A to F and note they are blank Delete all of these columns d Confirm note you can only see the right side of the database in this screen shot where we moved the metadata s a 9 D MAJ Yap Nimpal MPA Fish PHwWorking xIsx Microsoft Excel m x Home Insert Page Layout Formulas Data Review View Add Ins Acrobat mx c EET B see Calibri Hb 7 lA W ELM cq Wrap Text General P A Normal 2 Ji m E dal a Ar FS em Y Format Painter B Y U 3 sila A gt E 2 raj Merge amp Center gt ballu 4 E ELI S DOM Bad Good mses idc Dine a de dyd Clipboard Fs Font a Alignment EMT Number FI Styles Cells Editing Jl MES TEE EL u VU MW U KU AA EE WT WW A AM AN AO Wi 1 Kyphosus Lutjanus g Lutjanus n Macolor m Monotaxi Plectorhir Scarus fre Scarus glo Scarus sp Site MPA Stat Year Reef Type Depth m Transect NAT o o 0 0 0 67 07081 0 Gachuug Reference 2007 Channel 10m 1 FA 0 0 0 0 0 0 O 234 2396 0 Gachuug Reference 2007 Channel 10m 2 4 0 0 o 0 O 1813 636 0 210 0527 0 Gachuug Reference 2007 Channel 10m 3 3 0 0 0 0 0 0 0 0 0 Gachuug Reference 2007 Channel 10m 4
67. 850591 o 0 A 370 6821 570 2425 582 2677 1736 954 228 5995 104 2013 124 0765 161 0071 56 62435 115 6182 4872 741 6153 532 1021 34 370 8986 5167 187 678 8167 199 8531 189 4521 378 3839 904 048 1551 853 388 1105 48 Page 37 Click anywhere in the chart to activate the Pivot Chart functions 38 Click on the Analyze tab in Excel s main menu then click the Refresh button Notice in your PivotT able 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 14000 ooo 12000 10000 a000 B Average of Total Biomass 6000 B StdDevof Total Biomass2 4000 2000 Yes Yes Mo Mo Yes Yes No Mo Yes Yes Mo Yes Yes Ma Mo Yes Yes Ma Mo DIL DIZ DOI DO2 KI KIZ KOI KOZ2 LIT LIZ LOL LO2 MII Miz MOT MO2 Nil NIZ NO1 NOZ M4 K HT Pivot Graph PNP fish transect Pivot PNP Fish bv Transect PMII Exercise 4 Managing and Using Data Guidebook PivotChart
68. 88 21281 37 015 17 766 22149 21874 45462 22797 18608 16856 18745 194122 15472 17851 3024 26733 20 375 34475 283 20 987 20 781 40 353 21 401 B8 SBS 17 858 15504 17842 18358 187 2206 16 089 3341 20144 14072 31 376 4 026 20748 1748 21 888 67 Er 23 827 21181 23834 24403 1905 21812 21856 38058 26332 19496 35953 27262 26968 23431 27 949 55 S65 30822 16505 21012 215567 16518 24064 1814 15899 23434 169327 19 716 24343 24 055 20636 25 019 Bl 4 Row1 Cold Now we have a data matrix that compares every possible combination of transects and provides a distance measure of ecological similarity for each comparison From this we will again create our multi dimensional scaling plot MDS plot 67 Go to the Analyze menu Exercise 7 Managing and Using Data Guidebook 117 Page 68 Select MDS Mumber of restarts Pa 1 o m Kruskal fit scheme Shepard diagrams 1 J2 Cancel Help L onfiguratian plat a Keep the default settings for our options b Click OK After a bit of processing time PRIMER again produces the 2 dimensional and 3 dimensional plots called Graph1 and Graph2 Let s Just focus on the first 2 dimensional plot 69 Under the Graph menu 70 Select Data labels amp symbols Exercise 7 Managing and Using Data Guidebook 118 Page Graph Options s s O ue ONO amp H leneral H Data labels amp symbols Titles Bubble Co
69. 9883167 7 00615 0 0 28 22 0 22 Chuuk Channel Moderate 30 23 x 151 7931 7 227483333 0 o 27 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 D 0 27 Chuuk Channel Moderate 10 C 23 4 151 7931 7 227483333 D 0 23 1 0 28 Kuop Inner barrier Low 3 C 26 E 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 723 4 151 7931 7 227483333 0 0 20 0 0 31 Kuop Channel Low 10 C 25 3 151 9883167 7 00615 0 o 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 IERI 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 4 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 D 0 17 0 0 Exercise 2 Brainstorm metadata fields _ Metadata Database build Sheet2 Sheet4 151 74865 8 503183333 T Managing and Using Data Guidebook 11 Page You can get a general understanding this way for instance that the atolls hold more large clams grou
70. 999 27 588 o o n o 20 535 D n 5 5084 o D 0 8 2059 0 ol 22542 al 0 ol 0 B ni al ol H o 28187 al azza n s18 n wW EN ol al pl Bl m yi 3455 D 12008 n al EN D 33446 80386 al n al n 418985633 a n 411 44 50835 ESA4S7 lora uM ol n n pa G8BMB aaa n ol n Bl EE 4438 4 n E at AN ie JST ol al ol a D 898835 ni o o al ol 56274 B 173235 19 384 n Id ooo nl n n n n 250 08 n o n al n et Bet n Oo 851898 o B al D n ol 394300 a al 0 261 57 ol 56274 B6961 47325 19384 n Bom n o al ol 4041 42 209 ni al n BH yas ml BI az8935 dl n ol ol ol D 41525 26515 41639 n 0 n 12311 nii n B 78A ol nou Td n E ol 0 38202 21424 Bo ol o H a R 7 n 0 93 349 0 STEM O a H n ED249 T1451 gl n 0 al al n ol D 17956 Br124 ol n n n D 46 77 n al a D 24803 0 n 3488 of seag ivan a al a oo 1382 16284 H ch 0 o o y RP 0 E __ W __ Hh 0801 n nl ni 22685 20785 nl D n m 10009 WW ni n HM m 88843 n D D 20881 35726 n al n n 8224 n n 168 21 al n mea y a D 18848 23228 0 Hir n n a 0 n 61449 al 18303 9 oo al 0 a44502 B 631 4 al 1 o Do o al n ol qj Aaa D 41n86 22288 69322 a d n al nl nl n 9 20136 o gs D 585155 n orm 12471 n n ol al mp n D 38152 38758 n o cB al ol 109 73 4 5574 a D nj al ol n gd uw mm al ol 5B155 0 D 100 39 n ni 29 0124 a B ni 0 D D 36438 E3842 i n 56155 n al 2854 al OD 19853 n El al D ol Bn waal ol al H 0
71. E inal 3 ri oo o M 4 HT Ready Brainstorm metadata fields Exercise 2 Metadata ooooo Database build Sheet2 y Sheet4 24 Moooocoooooooo F F3 i El o 0 D o 0 0 o 0 0 0 o D D o 0 E ET D o EN ice o 0 D D o o 0 0 0 0 D 1 D 0 o o o 0 0 Do 0 D 0 0 0 o 0 0 0 0 0 0 0 0 a 0 0 H 0 D el o 0 0 0 0 0 0 0 o 0 0 pl 0 o U i U o a 1 U mm D o 0 0 0 T o 0 0 o o o o D o 0 o D Wy o o o D z o 0 o 0 Wi o 0 0 U o o o n o 0 0 0 o o 0 a 0 o E o a 0 0 1 2 48 2 0 o 0 0 0 0 o o 0 0 0 o o 0 0 0 a 0 U LU 0 OS DE 335 zb loe o 0 D o o 0 o 0 0 D o U 0 0 o 0 o 0 o on Z Average 0 532051282 Count 1563 ooo Sum gs jeu al 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 Af there is a small box with a diagonal arrow a Click on that box all cells in the database are automatically highlighted Tables Illustrations fe Island 1 1 7 Wave expo 2 I Low 3 Chuuk I
72. IMPROVING LOCAL CAPACITY FOR CORAL REEF MONITORING DATA INTERPRETATION 8H y Date 5 Tra 302280 1561388 7 24 2000 2000 A 302280 1561368 7 24 2000 2000 1 102280 1561368 7 24 2000 2000 00 288 305210 1561109 7 24 2000 2000 0 1561105 PEET 366655 1676452 9 21 2000 2000 ip g T 366655 1676452 9 21 2000 2000 366655 1676452 8 21 2000 2000 a mm tano gae A guidebook with step by step exercises to improve local capacity for data collection storage handling visualization and analysis throughout Micronesia Dr Peter Houk environmen y o Pacific Marine Resources Institute www pacmares com Table of Contents Modu CHON A utes Saa Sod RR RWY HRA HN Y TES YF YF 0 FY YR Ei deen NF Y EKORA i section 1 Database generation manipulation and query investipatiOh oee d ee boe ste A COULD GON ed Nu 1 ERE ESTOS AA VIS RR M C T 1 Exercise 2 Manipulating Managing Working with and Visualizing a Database A A A WAY NR 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 esses 37 section Univariate Statistics amd orapbino H GR A A A 51 Exercise 5 Simple calculations of statistical power for influential dependent variables eee
73. 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 A4 or COTS and HH Row Labels Y Vadues a Choose Field Settings b Check None under subtotals and filters 9 Repeat previous step for Impact Sites 10 Confirm Count of Acan Y Average of Ac Y StdDev of Aca Y Exercise 6 2 Managing and Using Data Guidebog 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 up 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 Let s 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
74. MOZ b No Inner 0 0 32 45807963 200 5011135 U 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 NIL 3 Yes Inner 0 0 0 U 0 0 4 85 NIL 4 Yes Inner 0 0 1560 394942 334 3265379 0 86 NII 5 Yes Inner D 0 907 3720349 301 5813593 87 NI i Yes Inner 9 300308364 0 U U 0 U 88 MI2 2 Yes Inner 117 8858770 0 0 0 65 1 349129 1250 231305 89 NI2 J Yes Inner 414 5347352 D D D 903 7508484 0 90 MI2 4 Yes Inner O D 0 0 518 4117907 0 91 NO1 1 No Inner 0 0 0 0 419 6444894 0 92 NOL 2 No Inner 204 1328923 D D 163 276415 93 NOL 3 No Inner 0 D 0 0 1052 18581 316 1631443 34 NO 4 Mo Inner 26 94957737 388 1945196 0 0 518 4117967 20 78537612 95 NO 5 No Inner 155 5966648 299 2851609 0 0 221 6274269 0 96 MO2 1 No Inner 232 0940318 0 0 0 048 288766 651 9648553 97 NOZ 2 No Inner 132 467928 D 0 U 408 2023468 1743 414176 98 NO2 3 No Inner 11 74445370 0 0 0 780 4099822 152 8830567 99 NO2 4 No Inner 94 75435913 0 0 0 88 33956 93 B5 38311227 100 NO2 5 No Inner 26 94957737 0 U U 164 82 3885 116 0184349 101 102 103 104 M4 K M PP fish pivot chart PNP fih pivot lt PNP Fish Database PNP Fish Data by Transect Sheet2 2 IWL lb gt gt EEE eh AV atad ee UR i u a dl 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 Exercise 4 Managing and Using Data Guidebook
75. MPA fish PERMANOVA example xlsx 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 How labels 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 125 Page 7 Confirm below PRIMER 6 Pohnpei MPA fish PERMAMOVA example PR File Edit Select View Analyse PERMAMOVA Tools Window Help ia E mm x Ema ok D OD 2 O him Ea 50 x d Workspace 0 E i i y m I PohnpeiMPA fish PE Biomass Naso Ituratus Maso unicorni Parupeneus b Siganus doliet Siganus puelllSiciar 4 Ben gd 0 D 0 22 288 0 Di 11844 1454 D 53 855 n n 0 n n n nm O n1 14398 ol mu mH egal Hu nb D n 867 n n I NOD _ 0 al al al 10694 27858 BI aze B4128 n ml o n la n n hum a w nu D 4855 Hn B 40045 al ol ol al ol ni al a ET 0 BI EI 0 n 28875 al E n al aj ni B 88312 0 al E we o l En 5 6155 n D 38076 20878 ol al nl n 20559 n ml n E NE wn o o n see ni n 838 oo 30 wGSUJ9b mn A n dal n a 0 0 o n D 49
76. Name to the Column Labels Add Biomass to the Values e Change the attributes of Biomass to Sum o Exercise 7 Managing and Using Data Guidebook 91 Page 3 Confirm Drag fields between areas below ReportFilter 9 Column Labels did Row Labels X Values 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 Exercise 7 Field Settings Source Mame Site Custom Mame Site Subtotals amp Filters Layout amp Print Subtotals O Automatic Custom Select one or more functions Max Filter E Include new items in manual filter EZ 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 auto generates 7 Right click anywhere in the table a Scroll down and select PivotTable Options 8 On the tab Layout 8 Format a Check the box that says For empty cells show b Puta 0 in the box 9 Onthe tab Totals amp Filters a Uncheck the box grand totals for rows and columns 10 On the tab Display a Check the box Classic Pivot Table layout 11 Confirm these settings and the new table look Pivotlable Options Mame PivotTable3 Layout amp Format Totals amp Filters Display Printing Data Layout Merge
77. TN PaehnperMPA fish PERMANUVA example FAH a Nested n GWEWYR timide Overall Transform mr ation Random lt TN Ing transformed a Lo zl Resemblancel TU SEE rl hes ff Resembll Teg P mos 29 Graph 9 Graph2 is Design MPA Reeftype MPA We are now ready to run our PERMANOVA on the dataset However just like any 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 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 transects from 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 so we need to calculate another similarity matrix from our log transformed data Exercise 8 Managing and Using Data Guidebook 137 Page 49 Highlight the Jog transformed data sheet on the left 50 Go to the Analyse menu a Scroll
78. Total 0 0 520 3882684 564 5055572 41 63864716 29 SLi 1 0 0 3285 179365 16379 22135 847 3206118 30 2 0 0 0 12003 95724 429 7563591 302 i Row Labels 31 3 1103 600024 0 1613 431607 1768 023349 458 8361116 124 32 4 0 0 4839 145245 3199 695914 288 4757995 459 SampleID Y 33 5 298 1126228 0 934 6812259 564 1745507 0 417 M 34 LI1 Total 1401 712646 o 10672 43744 33915 0724 2024 388882 2427 35 SILI2 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 M 4 E M Site information PNP fish pivot chart PNP fish pivot PNP Fish Database Shee mM Ready Just a few more steps and then we will have re created a new database for our needs 9 Click on cell A4 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 TN 4 Sample
79. U U 8 0 o 0 9 U 0 20 53477 998 10 o U o 11 U U 0 1 o U 196 3264321 13 U 0 0 14 o o 0 15 U U 0 16 o 261 56 9145 o 17 U U 97 11944011 18 0 o 12 31149291 19 o 0 36 4 697767 20 0 o 0 21 U o 24 62298581 22 o U o 23 U U 100 0216424 a 20 5392749 oe OG G OG GO a 4439 441088 56 27 396857 91 6206 56 56 2 396857 0 oo GGO ac o ocboborosososocs 869 6052304 B GG ces a IE D os d a Conditional Format a Formatting as Table Fj 418 673 Tu 0 8 63118382 0 0 5 688410915 o 34 64983142 411 4376075 17 32491571 0 17 32491571 47 93848427 0 0 0 34 64983142 0 0 36 Go back to the Pivot Graph PNP fish transect worksheet with our graph Exercise 4 Normal amp Styles ooooooooo 0 50 83456442 o 19 38359574 35 19830214 19 38359574 0 7 287146214 93 34908936 179 5604 0 o 0 Managing and Using Data Guidebook Normal 9 Normal Bad ay TR Imsert Delete Format Sososccel 0 25 18653069 12 00845723 65 45696046 G G GS G G 67 12364006 316 2206001 o 0 EE fr vt Cells Autosum Fill gt cp Clear Ed T 1 L Lutianus globe Lutianus monostia Monotaxis granocuEd naso iran Naso unicorn Parupeneus barberine Siganus dotia B Siganus puel E Siganus vulpin E Total 0 16 98766345 o 0 o 19 09386384 o 57 19708924 o 0 o 86 9187129 o 0 o 81 13722766 124 4389485 o o 101
80. a 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 A Sample FMESA FMEKSA FMEKSA FMEKSA 10 F 11 F 12 F 13 FMKSA 14 FMKSA 15 FMKSA 16 FMKSA 17 FMKSA 18 FMKSA ID Average of HC 04111 04113 04115 04120 imksa0o110 MKSA051101 MKSA08112 MKSA081121 08116 0614 0816 06161 0616 06101 19 FMKSA 20 FMKSA 21 FMKSA 22 FMKSA 23 FMKSA 0819 13113 13115 1312 1319 24 imksa16110 25 FMKSA 26 FMKSA 2f FMKSA 28 FMKSA 161101 16112 161121 16116 29 Grand Total B 63 75 16 25 60 625 70 625 62 5 67 5 61 25 63 15 FO 56 675 71 875 76 615 65 625 63 125 63 75 36 25 36 875 StdDev of HC 13 31841081 17 96988221 11 36 83885 7 465197026 359800722 9 574271078 6 539125636 5 204164999 9 783450104 1247914925 18 41364383 11 43368561 17 60385848 16 37770334 10 50733351 T TT2915879 46 675 36 25 52 5 59 375 60 625 61 25 66 125 50 52083333 5 400617249 12 14066651 3145764346 T TT72815878 10 48312135 Overall HC average Overall HC standar deviation 60 52083333 10 62544548 Delta Percent Change Detected 22 3f 36 96239 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
81. a labels amp symbols i Titles Bubble Lontour Labels Symbol SIZE Plot Plot Iv Font Z I 00 By factor By Factor T Edi Sie el Edt Default Symbol Colour El 40 For Labels a Check the By factor box b From the drop down menu select Year 41 For Symbols a Check the By factor box b From the drop down menu select Site 42 Click ok Exercise 7 Managing and Using Data Guidebook 109 Page 43 Confirm Note Your graph may be rotated differently however the spatial distances between sites should be the same 2009 2009 A y 2009 2007 2009 2007 a 2087 y 2007 2007 v 2007 y 2007 A 2007 2007 T 2007 A Exercise Managing and Using Data Transform Log X 1 Resemblance S17 Bray Curtis similarity 2D Stress 0 22 2009 2009 2009 2009 2009 Guidebook 110 Page Take a moment to reflect what we learn from 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
82. abase 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 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 A C PivotTable Field List 1 2 gt Choose fields to add to report i 3 Sum of Biomass g Column Labels x 4 Row Labels 7 Acanthurus xanthopterus Cephalopholis argus Chlorurus microrhinos Hipposcarus longiceps Lethrinus harak Lutjanus fu
83. 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 Return Click in cell V1 a Name this cell Total Biomass 34 Click in cell V2 a Type the following function zsum Notice Excel automatically includes this as part of your data table and the colors change b Highlight all cells in the 2 7 row with a fish name on top of them Excel should autofill the entire column once you hit Enter Table Tools Sni Number 35 Confirm G DI rY Pohnpei MPA fish transects exercise Microsoft Excel Ely HEN Home Page Layout Formulas Data Review V Acrobat _ My Am moon da Ml Verdana ho A dts Wrap Text General EE Copy s s x J Format Painter B Z Uu i e iiec A 7 iE La i Merge amp Center Clipboard E Font Ta Alignment W2 L fe 5UM Table2 This Acanthurus E 146 1007995 0 83 85516295 3 63 6822231 0 0 64 12765942 o o 5 U U 0 6 o o 0 7
84. achuug Reference 2009 Channel 3m 1 O 432 7805 0 0 0 0 0 1327 592 238 0775 0 14 65502 0 0 0 0 23 Gachuug Reference 2009 Channel 3m 2 0 0 0 o 0 0 O 1466 499 490 5276 o 0 o 0 0 0 24 Gachuug Reference 2009 Channel 3m 3 0 0 0 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 4 oO 259 025 0 0 0 309 8052 0 997 6664 335 0197 0 48 79071 0 O 13980 48 0 26 Gachuug Reference 2009 Channel 3m 5 0 0 0 o 0 183 4658 O 1877 006 633 7658 0 0 0 0 0 0 27 Gachuug Reference 2009 Channel 10m 1 0 158 9545 o 0 0 0 0 100 445 64 75614 0 0 0 0 0 0 28 Gachuug Reference 2009 Channel 10m 2 0 0 0 0 0 0 0 294 7546 248 1385 D 19 44339 0 280 0559 0 40 93125 29 Gachuug Reference 2009 Channel 10m 3 O 1024 215 0 0 224 1723 65 323 72 0 152 3426 26 77248 0 0 0 0 0 0 30 Gachuug Reference 2009 Channel 10m 4 0 1394 48 o 0 o 0 1484 777 0 302 168 o 0 o 0 0 0 31 Gachuug Reference 2009 Channel 10m 2 0 1218 561 0 0 D D 0 954 0168 759 1879 0 0 0 D 0 405 4788 32 Gachuug Reference 2009 Outer 3m 1 2790 589 0 0 0 0 0 2698 196 1339 763 480 38 0 98 37189 0 0 0 0 M 4 K M Fish Innkun tahle Yan fish nivnt NimnalGachuno fishdata Primer Prenare Yan Fish 7 A ii We have one last step before we can import our file into PRIMER E We must distinguish between the metadata and the ecological data PRIMER E requires that all data appear first followed by a blank column then the metadata We will re format accordingly 17 Highlight columns A through F 1
85. and center cells with labels r When in compact Form indent row labels 1 character s Display Fields in report Filter area Down Then Over Report Filter fields per column 0 E Format For error values show For empty cells show R Autotik column widths on update Preserve cell Formatting on update Exercise 7 Managing and Using Data Guidebook 93 Page PivotTable Field List Xx A T D E TN G H i 2 3 Sum of Biomass Scientific Name 7 4 Site x MPA Status E2 Year Reef Type e Depth m Transect y Acanthurus lineatus Acanthurus nigricauda Acanthurus ti 5 amp Gachuug Reference 32007 Channel a3 1 0 a R y 0 449 9912911 7 3 155 5966648 132 6289882 e 4 0 0 3 5 709 220469 0 10 230 1 0 0 LL 2 0 0 12 3 0 0 B 4 0 443 8639399 14 5 0 158 9544664 Quter Sl 3 1 0 0 2 637 0111691 0 AAA 3 0 o 4 3858 567874 0 3 5 4526 739952 0 20 210 1 0 21 2 246 430697 0 32 3 0 0 23 4 0 0 24 5 0 0 25 212009 Channel B3 1 0 432 7804749 28 2 0 0 27 3 0 0 28 4 0 259 0250307 29 5 0 0 30 210 1 0 158 9544664 31 2 0 0 32 3 0 1024 215323 mnr wy Site metadata Fish Innkun tahle Van fish nivot lt Nimnal Garhuiun fishdata lt Sheet 3 T mM We will export this to a new sheet now 12 Click on any cell in the table a Press Cirl A b Right click and select Copy 13 Select Sheet3
86. 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 Marine Protected Areas MPA for conservation purposes Similar to Pohnpei for each MPA that is surveyed there is an ecologically similar reference site Yap s program collects data at two different depths a 3m and 10m 1 Open Excel 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 single transect This structure represents another commonly used format for reporting upon visual census fish data 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 a Name the table Yap fish pivot b Add Site MPA Status Year Reef Type Depth m and Transec all under Row Labels in that order c Add Scientific
87. ators Groups Denominator Den df DILL DI2 1 Res 5 Average Similarity between within groups DIL DIz Dp 32523075 DIA asa EI le Within level D of factor Location Within level Inner of factor Reef type Within level Mo of factor MPA Unique Groups t Fiperrmi perms Exercise 8 Managing and Using Data Guidebook 145 Page A review of these site comparisons reveals that approximately 50 of pair wise comparisons were significant This confirms our thoughts that Site level variation is very high and needs to be accounted for and probably is driving our non significant findings of the differences between MPA status While clearly we expected some site level variation we also expected that in some instances MPA status might have a stronger influence on the fish biomass than reported While not the results we expected it seems that a road for future analyses has been defined In the future we might want to remove the nesting of site within MPA status and draw comparison among all sites from each corresponding reeftype within each MPA locality This is a logical step for Pohnpel s program to consider Here we begin to move in this direction in the final steps of our exercise Just a word of caution at this point 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 len
88. au 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 Micronesia 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 ex
89. auses attributable 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 cucumbers 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 bio
90. ave 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 DI1 c Confirm below ur O y ER im d Eje j Te B Verdana 10 i ia Copy TEN a f Format Painter salik fi ilL Clipboard E Font A2 fe DI1 1 Enim n Replicatild FT E Re 2 DI1 1 Yes In DI1 2 Yes In DI1 3 Yes In 5 Z LL 4 Yes In 6 Bll 5 Yes In 3 DI 1 Yes In a 2 Yes iri 24 Do the same for all other sites 25 Highlight cells A7 A11 a Go to the Fil 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 Ensure that under the heading Reef Type Column D only Inne
91. collaborative project with many contributors Type contributorzs for more information and citation on how to cite R or E packages in publications Type demo for some demos helpfi for on line help or help starti for an HTML browser interface to help Type qi to guit B 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 nz4 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 IF R Console DERK Natural language support but running in an English locale R is a collaborative project with many contributors Type contributors for more information and pitarionr on how tu cite E or E packages in publications Type demo for some demos help for on line help or help start for an HTML browser interface to help Type ail to guit E POWer t testcin 4 320 13 92 power U T Two sample t test power calculation n 4 delta 29 29426 sd 135 92 sig lewvel 0 05 power 0 7 alternative tuo z
92. cting MPA and Site in that order Exercise 8 Managing and Using Data Guidebook 135 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 B valable Include Reef type Location MFA Site Transect 8 Combined name LIE Cancel 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 n 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 he fourth to Random Exercise 8 Managing and Using Data Guidebook 136 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 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 Design1 i File Edit View PERMSNOVA Tools Window Help Cp REIR 6 SA ee Gh Pohnpeiish MPA PERMANUWVA exercise CAL MPA
93. d am 6 Losap Outer barrier Moderate 3m 7 Losap Channel High 10m 8 Murilo Inner barrier Low 10m 9 Etal Channel Moderate 10m 10 Chuuk Inner Moderate Om OG E cro eri e Validation criteria lt 1 Ignore blank P In cell dropdown Source Metadats AgzigA oo Apply these changes to all other cells with Ehe same settings ETIN Note that we reference a different sheet the Metadata sheet now a Click on that sheet and verify that A2 A57 were selected b Populate the database with values of your choosing Exercise 1 Managing and Using Data Guidebook 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 F2 i fe 1 A C D E mE NNNN G Island Reef type Wave exposure Depth Site Transect GDS X GPS y 2 Chuuk Channel Low 3m C 1 11 Chuuk Inner Moderate 3m C 11 1 Chuuk Inner barrier Low 3m ESL3 2 Etal Outer barrier Sheltered 3m C 14 2 Losap Outer barrier Moderate 3m C 13 3 Losap Channel High 10m C 14 3 Murilo Inner barrier Low 10m C 16 4 9 Etal Channel Moderate 10m M 4 4 10 Chuuk Inner Moderate 10m M 9 5 11 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
94. 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 b Click next Data source Data Z in CNMI data example JH B 96 For Data for Mean Group Mame N Missing Mean Sid Dev a Select column 15 or Average of Grazing F a 4 200 6 146 Row 2 159 U 7 016 7 736 Urchin which corresponds to average Pius 181 T 0327 17858 abundances within our Non impact sites i Alan os source of Variation DF a Ms F P 97 For our Size remember this is sample size p stween Groups 2 1526 563 763 281 2092 0125 a Select Count data located in column 14 Residual 417 152134918 364 832 98 For Standard Error la e L 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 1 125 100 Confirm Power of performed test with alpha 0 030 0 231 The po
95. 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 Distances are to F alues are frorn 2 Centroid 2 Permutation C3 Median C3 Tables Output individual deviation values Do pairwise tests UK Lancel 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 138 Page 54 Click OK PRIMER 6 PERMDISP1 S File Edit wiew Tools Window Help lah amp E E Overall Transform lt TN log transformed i lt E Resemblancel Sy Rezembll e usi FN Braphi E Graph2 feo Design E Hesemblancez Sl Bray Curtis similarity FERMANO A PERMANOY AZ FERMANOYAJ E Hesemblance3 Sl Resemi eS ups EN Braph3 E Graph4 is Design FEHMANLU VAS E ANOSIM EN Graph5 P Resemblanced You should now get a PERMDISP results sheet that displays the homogeneity of multivariate dispersions 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 1 9 and that
96. dual 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 will 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 CNMl invert pivot 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 Put a 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 On the Display tab check Classic PivotTable layout option 9 Right click in cell A4 and go to Field Settings a select None under subtotals 10 Repeat Step 9for Cells B4 and C4 Confirm fy el S lt MK gt PivotTable Tools enmi inverts e
97. e m Overall Transform similarity D to 100 lt TN 3mechannel transtormed B Resemblancel ss 157 sa SB S10 S26 S27 528 529 ssa 541 ma 543 S44 sas S61 E Resemi 36 z a fl ups ST PST NE 29 Graphi EB Bej T F Graph2 E 45 782 61 771 47 418 siMPERI rm 80232 70827 58242 55338 B Overall Transform2 2 57971 51 505 58523 67 927 64 976 EHE T mehanneHransformed EPA e 57 087 ES 42808 50 806 38 233 me E nap saa sm TUM MUS ma E E 25258 22 504 25483 53267 38325 49 254 22 534 45 424 38833 39995 39552 8009 5027 53300 471 51273 46 823 ET 83483 58341 61 578 44828 T2784 54885 39987 44487 23996 41 951 S42 80586 50395 54018 53 838 86732 48654 47422 38 201 29 222 47208 81 213 HAS 71485 77 882 53293 BB 560 B6906 4BE97 37826 28 901 45 408 70 863 64 325 Sad 64003 E amp SIT 65 392 46 858 B 592 57448 55255 33 245 25046 54231 65572 57208 79 209 74 835 73 156 76 58 55415 64 715 69 688 48115 39 077 29 874 49 856 76 327 68 293 90 379 82 549 SRI aw 16 632 18992 189508 14 785 23459 34 061 16 3
98. e Graph styles Select the style of graph you want to create Simple Bar gt Grouped Bar Plots data as Y Sirnple Error Bare values with error Grouped Error Bars bars Stacked Bars i a Click next We want the bars in our chart to represent Column Means and let s 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 H nw are the error bars computed Lala Means Ace ip 9 anm The symbol value is the mean of the calumn Error calculation upper Standard Error Error calculation lower Hone Data format Manu Y One 4 column and al least one Y calum Bak J Nee 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 E Dateg Select the column Selected columne 2 0 to plat by clicking E the column in the E p n EU oU worksheet zB BN Rc s NEHME 75 00 1 I L 6 00 EEN 73 00 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 bo
99. e formula Ctrl V a Fill all the way down to G10 Your database should look like below C 1 Island Reef type Wave exposure 2 Chuuk Channel Low 3 Chuuk Inner Moderate 4 Chuuk Inner barrier Low 5 Etal Outer barrier Sheltered 6 Losap Outer barrier Moderate 7 Losap Channel High a Murilo Inner barrier Low Etal Channel Moderate ao Chuuk Inner Moderate 1 Now that we have everything in order we are ready to formalize our database into an Excel list function LOOKUP E10 Metadata SA52 5AS57 Metadatal CS2 5C 57 151 8706333 151 788 151 5917333 151 58505 151 5917333 151 58505 151 4476667 153 7870667 D E F Depth Site Transect GP amp x 3m C1 1 3m C 11 1 3m C 13 2 3m C 14 2 3m C 13 3 10m C 14 3 10m C 16 4 10m M 4 4 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 Island Reef type Wave exposure Chuuk Channel Low Chuuk Inner Moderate Chuuk Inner barrier Low Etal Outer barri Outer barr Create lable Losap Losap Channel Murilo Inner barrie Etal Channel Chuuk Inner 11 12 13 Exercise 1 My table has headers Depth Site 3m C 1 3am C 11 3m C 13 m _ 14 J EC 13 C 14 C 16 M 4 M 9 Inf P WW RM KM Ka K Managing and Using Data Guidebook 153 54058331 Transect GPS xX 151 8706333 151 78
100. e surveyed yet 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 PivotChart Filter Pane X PivotTable Field List YX Active Fields on the PivotChart a Choose fields to ak to Meo Y Report Filter rz Reef type Wave exposure io Axis Fields Categories Depth Y Island v Site Transect E wee GPS x Site GPS y Ml Depth Hippopus Tridacna crocea _ Tridacna spp L_ Clam Total _ Crinoids Average of Sea Cucumber Total Lobster all spp Octopus Actinopyga mauritiana Bohadschia argus Total 30 4 4 Es lra 23 7 B Legend Fields Series Values Drag fields between areas below Y Report Filter i Legend Fields 15 E Total 10 4 i m 0o 4 p i d ss Nn mnm ax 3 3 3 3 icd Axis Fields Cat X Values Island 7 Average of Se i Reef type Y Site Sd Depth X 3 3 3 3 3 3 3 C 13 C 23 C 4 C7 C 10 C 11 C 15 C 18 C 19 H C 5 S 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 ba
101. ea Cucumber Total PivotTable Field List ODE 3 Choose fields to add to report Active Fields on the PivotChart d v Island v Reef type Wave exposure v Depth v Site IL TTransect ers x ers y IL Hippopus Tridacna crocea IL Tridacna spp clam Total I Crinoids DLobster all spp Octopus _ Actinopyga mauritiana 1221 1 2 Drag fields between areas below WV Report Filter i Legend Fields Y Values x Values em id As Fields Cat 0 k Island x Average of Se ho N L Site Y StdDev of Sea Y s 0 3 ds hds ids hd hos hols ho has i hols hals os h shdshdshdshdshds 1d 31d 3103 20 E 7 A Exercise 2 Chuuk Metadata Chuuk REA Invert Summary Chuuk REA Invert Pivot Bii SEKS S H casa cas 01902021 e baec C 3 C 4 ES C 6 c7 C 8 C 9 Managing and Using Data Guidebook Depth Defer Layout Update ua m a 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 Tota box from Values a Click and drag it back up from where you initially grabbed it 24 Back on the
102. eatures 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 PRIMEH 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 and leave it to the user manual to describe the math behind each operation 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 31 Select the samples that we wish to compare 32 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 Sm 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
103. ebook 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 2 a Choose the associated standard errors we just calculated in column 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 After Before During A Data EN Average of Acanthaster EJ Average of 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 abu
104. ed 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 spread out among the numerous independent variables that emerge from their 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 E Ux EA AA c Inner Outer Inner Outer Inner Outer inner Outer Inner Outer reefs reefs d FA Inside Outside Inside Outside MPA MPA MPA MPA ENTER Site 2 Site 1 Site 2 Site 1 N NX Replicate transects n 5 Exercise 8 Managing and Using Data Guidebook 124 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 however it is always best to start our investigations with a b
105. eference 2007 Channel 3m 5 709 2205 o 0 o 0 55 19581 0 0 415 0813 o 0 o 0 579 1653 0 7 Gachuug Reference 2007 Channel 10m 1 0 0 0 0 0 0 O 252 9121 576 2208 326 1714 0 20 78538 0 0 amp Gachuug Reference 2007 Channel 10m 2 0 0 0 0 O 183 4658 0 1063 511 456 2791 0 683 3266 0 0 9 Gachuug Reference 2007 Channel 10m 3 0 o 0 o 0 0 0 344 1298 359 8519 o 0 o 0 0 0 10 Gachuug Reference 2007 Channel 10m 4 O 443 8639 0 0 0 410 0111 0 344 1298 259 5631 0 0 0 0 0 0 11 Gachuug Reference 2007 Channel 10m 5 O 158 9545 0 0 0 O 1526 806 347 7459 510 2472 O 188 3515 0 0 12 Gachuug Reference 2007 Outer 3m 1 0 0 O 524 5821 0 0 0 O 718 8561 0 0 0 0 0 0 13 Gachuug Reference 2007 Outer 3m 2 637 0112 0 0 292 8198 D D 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 301 0 0 0 0 0 0 15 Gachuug Reference 2007 Outer 3m 4 3858 568 0 0 O 228 1668 0 0 O 2132 761 0 0 0 0 0 0 16 Gachuug Reference 2007 Outer 3m 9 4526 74 0 0 0 127 7607 D 0 O 2207 123 0 0 0 0 0 0 17 Gachuug Reference 2007 Outer 10m 1 0 0 O 842 3138 0 0 0 1870 162 2213 739 0 0 0 0 0 0 18 Gachuug Reference 2007 Outer 10m 2 246 4307 0 O 1983 238 0 4038 769 3729 498 0 0 0 0 0 1128 66 19 Gachuug Reference 2007 Outer 10m 3 0 0 0 0 0 0 0 4521 677 1887 781 0 0 1836 304 20 Gachuug Reference 2007 Outer 10m 4 0 0 0 0 173 0042 D 0 9757 544 2202 601 0 0 0 D 0 690 9934 21 Gachuug Reference 2007 Outer 10m 2 0 0 0 O 113 9958 0 0 2616 31 6682 163 0 281 1204 0 0 0 1249 616 22 G
106. er o 8 895905473 U 0 22 54191114 D 5 Yes Inner 17 9181095 U 51 16810232 1 No Inner 0 U U U 3344 823399 803 8615862 2 Mo Inner 0 0 0 D 801 6671508 825 504714 3 No Inner D D D U 928 3573877 4 No Inner 0 0 0 0 0 250 0795945 5 No Inner o 0 o D D 3943 030976 1 No Inner U 0 0 0 410 4122884 42 20930013 2 No Inner 0 U D D 11 52533634 2 651510778 3 No Inner 0 0 0 0 38 20208402 21 42392402 4 No Inner D D U U 60 24855971 7145131273 5 No Inner 0 0 0 0 0 426 7695095 1 Yes Inner 26 94957737 D D D 1361 965315 162 9379989 2 Yes Inner 40 80056086 U Q 0 226 5029675 20 78537612 3 Yes Inner 58 04203881 U 0 0 208 8141772 35 2565636 4 Yes Inner 26 94957 737 0 0 0 188 4801602 232 2596791 5 Yes Inner 183 0260262 D U 0 446 015026 D 1 Yes Inner 71 1290243 0 41 08601422 22 28788213 69 32168018 2 Yes Inner o 5 61549908 U D 127 610225 124 7121333 3 Yes Inner 0 26 02324126 0 0 109 7320328 4 557385076 4 Yes Inner 5 61549908 D D 100 3901652 U 5 Yes Inner 0 5 61549908 U 0 29 53955032 U 1 No Inner o 0 U D 246 7601492 56 47521097 2 Mo Inner U 0 0 29 53955032 17 85092737 3 No Inner 25 94957737 D D 259 8023409 309 1474729 3117613625 4 No Inner 166 4284061 U 0 127 7606938 417 0557612 153 2934902 5 No Inner 35 81142032 D D D 157 0441591 274 2833456 37 ko2 1 No Inner 86 50376278 U 0 1416 244105 U 38 2 No Inner o U U 0 321 4499577 0 l4 4 b M PNP fish nivnt chart PNP fish nivnt PNP Fish Darahase PNP Fish Nata hv Transect Sheet 753 AM il ll You should h
107. erstand statistical consideration of our dataset We are armed with a logical framework and flow to create a report power point lecture grant application 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 10096 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 Sch
108. es 45 0000 4 7222 12 Before Yes 42 0000 4 5476 13 During Yes 96 0000 2 0833 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 0962 a Cut and paste these data under Column 6 35 Promote the column headings to titles a Delete all unnecessary cells left below 36 Confirm m 1 After Yes 45 0000 0 1556 0 3341 45 0000 q Eze 3 2710 After 2 Before Yes 42 0000 0 0714 0 2361 42 0000 4 5476 5 0356 Before 3 During Yes 96 0000 1 2917 2 3082 96 0000 2 00 33 3 0964 During 4 al 6 7 B a Finally we get the last set of data from Excel Exercise 6 2 Managing and Using Data Guidebook Time Frar Impact 5i1 Count of Acanthaste Average of AcanthasteStdDew of Acanthast Ma Ma Ma 80 0000 159 0000 151 0000 0 1155 0 1132 0 1135 0 2660 0 4462 0 2920 StdDew 0 3 2710 5 0356 3 0964 1 Time Frame mpact Sili Count of Acanthastel Awerage of Acanthasteri StdDew of Acanthasterzt of Grazing Urch je of Grazing Urcky of Grazing Urch Time Fran Impact Si1 Count of Acanthaste Awerage of Acanthaste StdDew of Acanthast Ma 80 0000 0 1155 0 2660 Mo 159 0000 0 1132 0 4462 Ma 181 0000 0 1153 0 2920 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
109. gth 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 We have to be careful in using absolute terms when describing findings Our discussion above describes the most probable causes for our findings Graphical interpretations Selection Our last exercise will be to look at and graph specific comparison between elect levels for factar all sites for one of the MPA s This was defined as the logical next step to take by our preliminary analyses However here we will only do this for one amp vailable Include of the MPA s MInnerr eshl MLIuterreshl3 MiInnerresMIz ML uter ezM 4 i Minner oM C11 MOuter oM O3 FOR onto our log transformed sheet linnean MDuteraMD4 75 Click on the Select menu and click Samples MInneri esMI a Check Factor level Hinner eshi b In the drop down menu highlight Combined name and OH SEES did p gniig NOuterresNI4 c Click on the Levels box MInnerMoMUT MInnerMaMNLEZ We will only consider village M and the Outer reefs in both MPA status H uterHaNU3 76 In the Include dialog box select MOuterYesMI3 MOuterYesMI4 MOuterNoMOS MOuterNoMOd Ok Cancel Help 77 Confirm with the image to the right 78 Click OK in all dialog boxes Exercise 8 Managing and Using Data Guidebook 146 Page You should now have a subset
110. heet Test Sums of Squares 2 Main test C Type sequential LN O Pair wise test O Type ll conditional 2 Tune III partial Permutation method O Unrestricted permutation of raw data Do Monte Carlo tests 9 Permutation of residuals under a reduced model Fired effects sum ta zero C3 Permutation of residuals under the full model Use short names Lancel Help Exercise 8 Managing and Using Data Guidebook 141 Page After a bit of computation you should get a PERMANOVA results sheet P PRIMER 6 PERMANOVA1 m File Edit View Tools Window Help D co Bd amp amp a ER Fahnperfish MPA PERMANLUVA exercise cR Pahnpei MPA fish PERMAN DVA example PE RMANO VA E m Overall Transform Permutational MANDA EES log transformed E Besemblance Resemblance worksheet E Resembll Mame Eray Curtis similarity se MD51 Data type Similarity E Graphi Seleccion ilI F Graph2 Transform LaogiE r1 lao Design Resemblance S17 Bray Curtis similarity Hesemblancez Ies Bray Curtis similarity sums of Squares type Type III partial yl PERMANOVAT Fixed effects sum to zero for mixed terms Permutation method Permutation of residuals under a reduced model Humber of permutations 999 Factors Name Abbrev Type Levels Location Lo Random 3 Reef type Re Fixed MPA MP Fixed Site mul Random 3 Z PREMANOVA table of results Unique SOURCE df aja MS Pseudo F P netm perma La 4 a
111. her Scarids including Hipposcarus longiceps Scarus forsteni 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 powerful to aid our understanding Let s continue to look at other reef types and depths 53 Go back to the first main data sheet under the Yap multivariate fish exercise 54 From the select menu select AIP Exercise 7 Managing and Using Data Guidebook 113 Page 55 Go back to the Edif menu and select Factors Let s look at the same channel reefs this time at the 10m depth 56 On your scratch paper record the relevant sites we want to highlight S1 5 821 25 836 40 and S56 60 Note You can deselect the undesired samples by clicking on them and select the new samples noted above 57 From the Select menu choose Highlighted 58 Confirm
112. hoose Delete Rows b Delete rows 1 3 so choose to delete 3 rows starting at row 1 Delete Rows Motebook3 Dat E Delete E abrow 1 a ee Finally let s promote our years to official column titles 17 Right click on column 1 a Choose Column Titles Column and Row Titles p Column Row calumn 1 Delete promoted row Promote row 1 to titles 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 Nexf a Promote 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 17 a Type the word Year 20 In the cell under Year type in 2000 then 20071 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 wm OF c A E c PI Fr Ra e 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 D 0000 2 2001 0000 0 0000 0 0000 0 0000 0 0000 0 0000 1 0000 1 0000 3 s000 1 0000 0 0000 3 2002 0000 0 0000 0 0000
113. ided NOTE n is number in each group gt We can see the results now very clearly We are interested in the value 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 FMKSA04113 FMKSA04115 FMKSA04 120 fmksa08110 FMKSA081101 FMKSA08112 12 FMKSA081121 13 FMKSA08116 FMKSAU814 5 FMKSAD816 FMKSA08161 17 FMKSA0818 FMKSA08181 19 FMKSA0819 20 FMKSA13113 21 FMKSA1311A FMKSADA111 HC 63 75 16 25 60 625 70 625 62 5 67 5 61 25 63 75 70 56 015 71 875 76 875 65 625 63 75 AR ATA StdDev of HC
114. ig picture perspective 1 e highest levels first then work our way down based upon significant findings 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 account for all other predictable variation that is possible to do so The PERMANOVA software allows us to efficiently 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 look at the Data sheet You can see the metadata 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 Click on the sheet For Primer These are the same data arranged for input into 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 used in our previous exercise 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
115. ing Through this process the distances we calculate between each pair of sites are ranked from lowest to highest using an ordinal scale and the resultant plot highlights these relationships specifically how the pairwise relationships all fit into a bigger picture called 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 38 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 af restarts Minimum stress 25 0 01 EE zz n z mns Kruskal fit scheme Shepard diagrams 1 TE Lancel Help l Configuration plot b Keep the default settings for our options c Click OK Exercise 7 Managing and Using Data Guidebook 108 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 39 Under the Graph menu a Select Data labels amp symbols Graph Options UG UU T General Dat
116. ing 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 Q4 d 9 c gt Chuuk REA invert database complete Microsoft Excel Table Tools PTI cy Home Insert Page Layout Formulas Data Review View Add Ins Acrobat Design ax 3 y La Connections A BH WK Clear E gs cs a ar er eL mn 3 Shaw Detail n 2 T Properties H LA amp Reapply ERE ud S dm x tE YS u E Hide Detall From From From From Other Existing Refresh x e l Sort z Text to Remove Data Consolidate What If Group Ungroup Subtotal Access Web Text Source connection Alre Edit Links 1 57 Advanced Columns Duplicates Validation gt Analysis G gt Get External Data Connections Sort amp Filter Data Tools Outline F7 fe A B C D E G H I J K L M N ty J Reef ty
117. ing 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 O lt n3 als 3 Average of Acanthaster Date v Transec SAkino Reef 8 16 2000 iBarcinas Bay 3 7 25 2000 11 EIiBarcinus Bay 1 3 7 26 2000 l4 amp Bird Island 724 2000 17 Boy Scout 16 28 2000 c 20 ZiCoral Gardens 37 26 2000 zi 11 24 2000 23 Coral Ocean Point 9 Slota North 3 7 28 2000 c C Transect 1 2 3 1 2 3 1 2 3 1 2 3 1 2 3 1 2 3 1 2 3 1 2 3 1 2 3 1 2 3 1 2 3 1 0 1 2 Elota South 7 24 2000 34 35 ci Lau 2 1 24 2000 1 36 1 37 0 5 lt Obyan 59 23 2000 0 Mo 4 K Ml CHMT invert nivot Sheet1 Metadata J Exercise 6 1 Managing and Using Data Guidebook 65 Page We have to transfer our data on a year by year basis because Excel has put in O for all empty boxes even if no surveys were conducted lt 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 1 a Choose paste 15 Do this for all years then confirm P3 SigmaPlot Data 1 M AR A WC XE ile K 111 LI ue Lux All Open No
118. ired 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 types 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 that were observed No size data were collected just counts From
119. ists 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 for the development of this guidebook Here we present a step by step data analyses and graphing guidebook that was produced by PMRI and funded by NOAA Pacific Islands Marine Protected Area Community program This guidebook was developed using regional data collected during FY 09 collaborations between PMHI and FSM RMI coral monitoring programs The goal is 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 The guidebook represents a framework for this process The guidebook was produced using four major software platforms Microsoft Excel Access PRIMER E and Sigma Plot These will all be needed to use this guidebook Please provide constructive comments regarding the guidebook to PMRI through their website www pacmares com Introduction Managing and Using Data Guidebook Page 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 of information is typically required or at least des
120. lated for us n 7 62 This means that to accomplish our goals we d need to sample 8 transects 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 a Delete cells E5 E6 and F5 F6 for now because we want to look at all sites combined b In Cell E5 type Overall HC Average c In F5type Overall HC standard deviation d In cell E6 type average B5 B28 this takes the overall average of HC e In cell F6 type average C5 C28 this takes the overall mean deviation of HC f Confirm Data 1 2 3 EN 4 Sample ID Average of HC 5 6 5 FMRKSA04111 JFMKS5A04113 7 JFMKSA04115 8 FMKSADAT120 3 fmksa s110 10 FMK5A051101 11 FMKS5A05112 12 FMK5A051121 13 FMK5A05116 14 FMKS5A0514 15 FMKSA0616 16 FMEKSA 08151 17 FMK5SA0518 18 FMKS5A05181 19 FMKSA0619 20 FMKSA13113 21 FMK35413115 22 IFMRSA1312 Now note the overall standard deviation on your scratch paper 53 75 6 25 60 625 70 625 62 5 b 5 61 25 53 75 FO 1 8 5 6 8 5 65 625 63 125 63 75 36 25 36 675 46 075 10 Return to the R Software a Type power t test n 4 sd 10 63 power 0 7 Exercise 5 gt tdDev of HC 13 3184
121. le 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 Data Format Select the Format of your data eS ee see zaman sm Mean nize skanderd E W En am nes The MSE Format 5 00 6 10 ior places the 5 10 im LO mean sample size and skd dev in separate worksheet columns The informational box tells us that Treatments are significantly different meaning that urchin abundances are significantly different among the time frames 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 comparison 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 A lt 0 001 Suggested Test Description Fisher s LSD Test can be used for pairwise Comparison Type comparisons IE iz 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 45
122. ll Exercise 7 Managing and Using Data Guidebook 114 Page 60 Select Log X 1 from the drop down menu and 61 Click OK You have now created a new species by site datasheet you can see on the left that the current name is Data1 62 Rename this to 10m channel transformed GS Yap multivariate fish exercise ry ap Mimpal MPA Fish PHwaorkina E eg Overall Transform lt TN 3m channel transformed E Hesemblance HD Reseml af Most Graphi Graphz S SIMPER E m Overall Transtarmz m channal transfermed 63 Go to the Analyze menu a Select Resemblance Exercise 7 Overall Transform Logf 1 v Biomass Acanthurus lin Acanthurus ni Acanthurus tr cara D i 0 a 6 0978 5 0749 5 0749 GC G G G G oa Managing and Using Data Guidebook U ecOoocojdcodo 115 Page Resemblance Anallize belwesen Measure 9 Samples 2 Bray Curtis similarity O Variables C3 Euclidean distance O Mare tab Add dummy variable m 64 Under Analyze Between a Select Samples 65 Under Measure a Select Bray Curtis similarity 66 Click OK Exercise 7 Managing and Using Data Guidebook 116 Page TP PRIMER 6 Resern2 n Elle Edit Select view Analyse PERMANOYA Tools Window Help Du E LR K OD Z O DEG K AI gt X rcs rap multivariate fish exercise la rap Mimpal HPA Fish PHwarking Sims DO t
123. lvus Lutj sort an Y 5 SDI 178 0129597 62 31828846 14 12507006 35 15110911 3 MPA 6 1 22 28788213 118 4382485 reef type 7 2 14 39864279 56 50028025 v Replicate 8 3 106 9436501 278 5780129 132 6183517 v Species 4 1 1636 499422 100 4544909 Denn 10 E 219 9682947 Da ii DI2 11 16526571 51 16810232 9 524042763 9 692924103 Elb 12 1 5 61549908 38 07614397 20 87648147 V Biomass g 13 2 34 68837875 89 38812819 0 10 cm 14 3 49 99871473 27 58813905 10 20 cm 15 E 8 895905473 22 54191114 7120 30 cm 16 5 17 79181095 51 16810232 7130 40 cm 17 DO1 376 9536863 204 5707351 140 50 cm 18 1 3344 823399 803 8615862 gt 50 em 19 2 801 6671508 825 504714 20 3 928 3573877 21 4 250 0795945 22 5 3943 030976 Drag fields between areas below 23 DO2 65 04853355 47 04212976 41 63864716 W Report Filter Column Labels 24 1 410 4122884 42 20930013 species 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 RC EL d 31 2 12003 95724 429 7563591 302 9879514 iij Row Labels un 32 3 1103 600024 1613 431607 1768 023349 458 8361116 1248 546398 SampleID SumofBiomas Y 33 4 4839 145245 3199 695914 288 4757995 459 0088779 Replicate v 34 5 298 1126228 934 6812259 564 1745507 417 0352246 35 SLI2 738 3981727 161 2357242 335 7776315 3 36 1 307 8528241
124. ly 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 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 Siganus vulpinus Siganus puellus Siganus doliatus 8 Parupe
125. mass from length estimates 3 Click on the next sheet PNP Fish Database You can see a dataset for Pohnper s 2006 indicator fish monitoring efforts First notice the design of the database is different from the Chuuk HEA 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 down menu 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 gueries 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 COHDBOIMI ANSI transects exercise or any other name of your choosing I MPA fish transects ada d Table Tools 2 x 1 Sample mt MPA Ia Reef type Y Replicate MM Species X De gt y err DIL DIL DI1 DIL 311 _ Sie information PNP Fish Database Sheeti S 0 021061453 04021061453 0 021061453
126. n 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 zd Validation criteria Allow List w Ignore blank RES E In cell dropdown SOURCE Brainstorm metadata Fields 642 8 5 ER i Aala I i i i Wore ral PI eir FLIP M AAA G 11 Clear All Dr 57 L Cancel 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 A C D 1 Island Reef type Wave exposure Depth 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 9 Etal Channel 10 Chuuk Inner Inner Inner barrier Outer barrier Site E F Transect GPSx 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 fe 10m A B C EN NN z F G 1 Island Reef type Wave exposure Depth Site Transect GDS X GPS y 2 Chuuk Channel Low 3m 3 Chuuk Inner Moderate 3m 4 Chuuk Inner barrier Low 3m 5 Etal Quter barrier Sheltere
127. ndances in the CNMI BEN impact sites E Mon impact sites CM C C T mh CI E TE C do pe E C lx CI L qe C NY ae zx 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 defined 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 1 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
128. neus barberinus amp Naso unicornis E Naso lituratus m Monotaxis granoculis B Lutjanus monostigma B Lutjanus gibbus 8 Lutjanus fulvus B Lethrinus harak B Hipposcarus longiceps B Chlorurus microrhinos a Cephalopholis argus Bi Acanthurus xanthopterus Inner M Site information PNP fish pivot chart PNP fish pivot Exercise 3 DI2 PNP Fish Wi PivotChart Filter Pane Active Fields on the PivotChart B W Report Filter i Axis Fields Categories w X Reef type MPA Sample ID Species EXEE PT Legend Fields Series hd Values Average of Biomass ag Managing and Using Data Guidebook PivotTable Field List vx Y Choose fields to add to report 7 Sample ID l v MPA v Reef type 7 Replicate v Species Length Da b v Biomass a 0 10 cm 10 20 cm _ 20 30 cm _ 30 30 cm _ 40 50 cm gt 50 cm Drag fields between areas below W Report Filter Legend Fields Species nen 54 X Values 4 Axis Fields Cat eeftpe gt MPA Sample ID E Average of Bio Y K 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 es
129. nner Low 4 Chuuk Inner Low 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 Worksheef is selected Create Pivot Table a IS Choose the data that vou want to analyze 2 Select a table or range 0 A TablefRange AE eee eels r C 3 Use an external data source ne Choose where vou want Ehe PivotTable report to be placed New Worksheet C3 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 Man To build a report choose fields from the PivotTable Field List Brainstorm metadata fields Metadata Sheet1 Database build Sheet2 Shed P Z PivotTable Field List x 7 Defer Layout Update Choose fields to add to report HE Find i IL Reef type Wave exposure Depth site I Transect ers x ers y Hippopus IL Tridacna crocea IL Tridacna spp clam Total IL rinoids
130. nothuria graeffi _ Stichopus chloronotus _ Stichopus hermanni _ Thelenota ananas L Thelonota anax Sea Cucumber Total _ Echinaster luzonicus Acanthaster planci Culcita novaguinea L_ Linckia guildingi IL JLinckia laevigata Seastar Total JEchinometra L Drag fields between areas below W Report Filter Cj Legend Fields z Values ad Managing and Using Data Guidebook You can see a lot of options here we want to look at simple Column charts 19 Page 21 Select New Sheet and rename the chart Chuuk REA Invert Summary Move Chart Choose where you want the chart to be placed L Lu New sheet Chuuk REA Inverk Summary Ill O Object im Chuuk REA Invert Pivot v A new sheet is created and our desired summary is easily seen and understood Ga ld 3 M Chuuk REA invert database PHworking Microsoft Excel PivotChart Tools 3 Home Insert Page Layout Formulas Data Review View Add Ins Acrobat Design Layout Format Analyze e S Xx ae cm A ud hd hd E Wig 0 n al Eez Change Save As Switch Select HI il la Emm Chart Type Template Row Column Data z E Type Data Chart Layouts Chart Styles v fe El T i z T PivotChart Filter Pane x PivotTable Field List Y X Chart Location Active Fields on the PivotChart B Choose fields to add to report 7 Repo
131. ntour Labels Symbols SIZE Flot Plot qo Bufactor By Factor T e se ea Default Symbol Colour R Keu Cancel 71 For Labels a Check the By factor box b From the drop down menu select Year 72 For Symbols a Check the By factor box b From the drop down menu select Site 73 You should have changed the look of your graph confirm Exercise 7 Managing and Using Data Guidebook 119 Page Transform Log X 1 Resemblance 517 Bray Curtis similarity 2D Stress 0 12 site A Gachuug w Nimpal 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 using the SIMPER analyses again 74 Go back to the 10m channel transformed data sheet Exercise 7 Managing and Using Data Guidebook 120 Page 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 75 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 S21 25 and S56 60 Note those sample labels on your scratch paper and close the factors box 76 On your main sheet a Highlight
132. ogram 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 Finally reducing the number of replicate stations in each MPA main represent one means of saving time and funding to accomplish the other deficiencies 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 151 Page
133. olar search engine Here we will touch upon the subject for our needs of assessing data confidence You should have already installed the software 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 R 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 maa M mA E EE so EA A B G 1 EE SESE da Bo E DE Bo Bo Bo Bn E sce lor BRE TU 2 FMKSA04111 1 9 21 2005 0 0 0 0 0 5 62 5 0 0 5 0 0 0 3 FMKSA04111 2 9 21 2005 7 s 0 0 0 0 25 0 70 0 0 0 0 0 0 4 FMKSA04111 3 9 21 2005 12 5 0 0 0 0 0 5 0 77 5 0 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 0 6 FMKSA04113 1 9 22 2006 12 5 0 0 0 0 0 0 5 72 5 0 0 25 0 0 0 0 7 FMKSA04113 2 9 22 2006 12 5 0 0 0 0 0 0 0 87 5 0 0 0 0 0 0 0 8 FMKSA04113 3 9 22 2006 5 0 0 0 0 0 25 0 92 5 0 0 0 0 0 0 0 9 FMKSA04113 4 9 22 2006 15 0 0 0 0 0 17 5 25 52 5 0 0 0 0
134. omputer 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 Plof c Onthe Symbols side change the factor dropdown menu to Location 19 Click OK 20 Confirm 1 PRIMER 6 Graph1 E File Edit View Graph Tools Window Help D c E amp BE k BDS 7 S Pohnpeifish MPA PERMANOVA exerc gt Pohnpei MPA fish PERMANOVA e E Overall Transform m ES Du x 2D Stress 0 28 fs Reseml lt e Exercise 8 Managing and Using Data Guidebook 130 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 types 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 Go to the Graph main menu and a Select Data Labels amp Symbols 22 On the Symbols side a Change the factor dropdown menu to Reef type 23 Click OK 24 Confirm Transform Log X 1 Rosetnilance S17 Bray Curtis similarity 2D Stress 0 28 Reef type A Inner a w Outer A A A A a A V A A A Y A A d Y A A PU A r A A y
135. on the left a Place Location Reef type MPA and Site in the Include box in that order which follows our experimental design diagram above b Click OK Ordered Selection Select factors Available Include Transect H Location Reet type MPA Site OF Cancel Help You can see your new factor has appeared 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 Exercise 8 Managing and Using Data Guidebook 128 Page b Click OK A new sheet with the log transtormed data should appear 14 Go to the Analyze menu a Create a Bray Curtis similarity matrix by scrolling down to resemblance make sure the analyses is between samples and you use a Bray Curtis similarity method 15 Click OK 16 Confirm PRIMER 6 Resem1 File Edit Select View Analyse PERMANOVA Tools Window Help _ 4s Dag E BM k 6 Y s Pehnpeidish MPA PERN PahnpeicMPA fish PE m Overall T ransfarr T 4 Y 3 E 5 cj
136. 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 Exercise 7 Managing and Using Data Guidebook 103 Page better understand the concepts behind data transformations 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 typically utilize a different transformation as compared with biomass and percent coverage data The transformation selected for use here is widely accepted and commonly employed for biomass and abundance data 34 Goto the Analyze main menu 1 Scroll down to pre treatment 2 Select Transform overall 3 Select Log X 1 from the drop down menu 4 Click OK 5 Confirm Below Transformation Legs You have now created a new species by site datasheet you can see on the left that the current name is Data1 35 Rename this to 32m channel transformed Exercise Managing and Using Data Guidebook 104 Page P PRIMER 6 3m channel transformed m File Edit Select View Analyse PERMANGV4 Tools Window Help O c y o E m Re he T Gh Yap multivariate fish exercise E T T ap Himpal MPA Fish PHwarking E fo Overall Transform DoS Wariables T 3m channel transformed Acanthurus lir Acanthurus ni amp canthurus tr
137. oolbar LOOKUP E2 Metadata A 2 5A 57 Metadata C 2 5C 57 1 Island Reef type Nave exposure Depth Site Transect GPSx GPS y 2 Chuuk Channel LOW 3m Chuuk Inner Moderate 3m c 11 1 Chuuk Inner barrier Low 3m C 13 2 cr l P Kea pr denne 9 Chaltarad Ir FK 1 1 7 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 Go back to our Database build sheet and highlight the first cell you want to populate with the
138. or were not 36 Go to the Analyze menu a Select Resemblance Exercise 7 Managing and Using Data Guidebook 105 Page Resemblance Main Mare Analyse between Measure Samples 2 Bray Curtis similarity C3 Mariables C3 Euclidean distance C3 More Lab Add dummy variable Cancel Note Make sure the analysis is between Samples and were using the Bray Curtis similarity 37 Click OK Exercise 7 Managing and Using Data Guidebook 106 Page H PRIMER 6 Resem1 PI if Elle Edit Select View Analyse PERMANOYA Tools Window Help OSM SS KRK O co kee GA Yap mullivariate fish exercise E PA Y ap NimpaFMPA Fish PHwarking Gaiari na 300 a Llverall Transform simianty O10 190 EET E t C ecd js 52 ES TN E 521 22 523 sa 525 sat EE ES 538 eon Se v2 52343 S 32796 aia 59 407 TD 39 776 55 4838 48578 39784 54352 EM 32H2 41752 0S 33265 19837 so 43304 25398 33931 29894 25547 72432 E28 13 675 10028 1178 1 801 35447 32 481 37 025 324 IT 70180 4993 B1473 575 MEE EN 63 3 S36 31399 40458 2381 46674 37326 59786 75319 34835 M74 585228 etd
139. palMPAFis Biomass Overall Transfarr SEE o acanthurus lij Acanthurus nj Acarthurus tr Caranx melan Cephalopholu Cheilinus und Chlorurus mic Chlorurus sor Ctenochaetus Epinephelus mEpinephelus Grouper Hipposcarus WKyphosus Lutianus gibby Lutianus mondMacalor D D D D D D D 252 91 576 22 32647 D D 20 785 D D D D D D D D 183 47 D 1053 5 455 28 D D D 583 33 D D D 1E ol ol 0 T n JB n 34443 359 85 D o 0 n n 0 n 0 44385 D 0 D 410 01 D 344 13 259 56 D D D D D 0D D MEN 158 95 D D D D 0 1526 8 347 75 510 25 D D 188 35 D D D 8213 158 95 0 D D D D 100 44 64 756 D o D D D D ol D D D D D D D 294 75 248 14 D 19 443 D 280 06 ol 40 931 D 523 1024 2 D ol 22417 65 324 n 152 34 26 772 D D D D D D D D 13845 0 D 0 D 1484 8 0 302 17 0 0 0 D 0 D 0 0 1218 6 0 0 0 0 0 954 02 759 19 D D D D D 405 48 D 33t oe D D D D 25 162 n ol 828 87 546 68 907 66 D D D D D D S37 o 0 n 0 0 n D 572 34 594 25 492 5 D o D D D 1 S38 0 ol 0 n 0 D 521 54 40511 D D D D D D D o D D D D D D n 1822 2 522 58 D D D D ol 405 48 D D D Di D D D n 1170 8 53204 n D D D Di D D ce g n n n n n n n 44 727 0 n n n o 245 18 n D D ol D 51 168 n D D 158 9 D D 501 75 D ol 690 99 D l 0 n 0 n 18 079 0 n n 8364 n n 0 n ol n 0 m Gm 0 D D D 3418 D 0 n 228 75 0 0 0 D 0 D oj See O n 0 0 E 0 n n 0 81 909 oj n 317 43 0 o 0 n We will follow the exact same steps as before 59 Select the Analyze menu a Go to pre treatment b Select Transform overa
140. pare 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 View 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 samp
141. pe E4 Wave exposure lt Depth Site lt Micrsx Micrsy M Hippopus MiTridacna crocea Tridacna spp Bi crinoids lt Lobster all spp 14 octop 2 Kuop Inner barrier Low 3 C 26 5 151 8756 7 1075 D o 85 0 0 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 D 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 D o 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 o 42 D 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 D 0 37 D 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 33 0 0 14 Kuop Inner barrier Low 10 C 26 5 151 8756 7 1075 0 o 31 0 0 15 Chuuk Inner barrier Low 3 6 27 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 o 30 D 0 17 Kuop Channel Low 3 C 25 2 151 9883167 7 00615 0 0 30 0 0 18 Murilo Inner atoll Low 3H 6 4 151 8299 8 55305 D 0 30 D 0 19 Kuop Inner barrier Low 3 C 26 1 151 8756 7 1075 0 0 29 0 0 20 Kuop Inner barrier Low 3 C 26 3 151 8756 7 1075 0 o 29 0 0 21 Kuop Channel Low 10 C 25 4 151
142. pecially 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 4 200601 T 2500 Series Naso unicornis Point Inner No DOLL Value 869 6052304 P M Site information PNP fish pivot chart PNP fish pivot 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 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 You can confirm below for MPA N LE NI2 Mao Wes Inner Ol PNP fih nivot chart BNP fi nynt PND OEE af 3E ERES Siganus vulpinus A Siganus puellus B Siganus doliatus j S f i
143. ped 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 Ato 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 I 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 B C D E F G H I J K M K M Reef type Wave exposure lt Depth M site Mil transect Mers x Ml GPS y lt Hippopus Tridacna crocea Tridacna spp bA Column1 10505583 Lobster a 2 Chuuk Inner Low 3 C 10 1 151 7091167 7 3973 0 0 0 0 3 Chuuk Inner Low 3 C 10 2 151 7091167 7 3973 0 0 0 0 4 Chuuk Inner Low 3 C 10 3 151 091167 7 3973 0 0 0 0 5 Chuuk Inner Low 3 C 10 4 151 7091167 3973 0 0 0 0 6 Chuuk Inner Low 3 C 10 5 151 091167 7 3973 0 0 0 0 7 Chuuk Inner Low
144. r is selected Exercise 4 Managing and Using Data Guidebook 44 Page When finished confirm you completed data table below fy B 20380 m im Inse N Pohnpei MPA fish transects exercise Microsoft Excel Pag out Rewigw View El y n re mu asp 5 X Table Tools A at Design 15 m ox e l mx EW Verdana lio AAA ag egi Wrap Text General B ai Normal 8 Normal 9 a a AGI E AH Ar FF H Copy n 1 y yg Fill 7 en J Format Painter B I Wem lt A A E aj METQE GE CEPR Som Me om EX l a a bo i ak pat EH ay dori n INT P Clear Pe dy Clipboard la Font ai Alignment Tu Humber s Styles Cells Editing A5 v LS fe DI1 Sample ID Replicate MPA Reef type Acanthurus lineatus Acanthurus xanthopterus Caranx melampygus Cephalopholis argus Chlorurus microrhinos Hipposcarus longiceps Lethrinus ham 67 MI2 1 Yes Inner 0 0 0 0 0 0 2077 28 68 MI2 E Inner D U 0 0 U U 69 MI2 3 YES Inner U U 0 0 U U 70 MI2 4 Yes Inner 0 0 0 0 0 0 71 MI2 5 Nes Inner U U U 0 0 0 72 MOI 1 No Inner 0 0 127 7606938 D 138 64336U4 73 MO1 2 No Inner 0 0 0 0 U 0 74 MOT 3 No Inner 0 0 U 0 334 3265379 859 580583 75 MOI 4 No Inner 0 0 0 0 0 1882 099165 76 MOI 5 No Inner 0 0 0 D 1576 025111 D 77 MO2 1 No Inner 0 0 0 32 45807963 200 5011135 U 78 MO2 2 No Inner 0 0 0 0 348 252 7314 0 79 MO2 3 No Inner 0 0 0 32 45807963 122 2 3608 0 80 MO2 4 No Inner 0 0 0 51 16810232 195 678465 81
145. 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 Let s 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 14 Page Note When the warning box appears you can click OK we re well aware of MAA our study design and we simply wish to view and understand the results so we can best move forward A siii idad You have asked For comparisons among levels of a random Factor 73 Confirm the third PERMANOVA sheet Proceed anyway PERMANOVA L oc Genet Permutational MANERA Resemblance worksheet Name Bray Curtis similarity Data type Similarity selection All Transform Log A11 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 Nare Abbrev Type Levels Location Lo Random 5 Reef type Re Fixed Z MPA MP Fixed Z SLEE ai Random 32 PAIE WIESE TESTS Term SiiMPIR amp eiLo Within level D of factor Location Within level Inner of factor Reef type Within level Yes of factor MPA Unique Groups t Pf perm perme DLL DLS a Em 0 043 12 B Denomin
146. rfish 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 eraphs 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 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 Drag fields between areas below 3 Remove all Column Row and Values from the boxes on the lower right SF Report Filter Column Labels 4 Choose COTS and Impact Sites for new row labels in that order a Drag Acanthaster under Values 3 times 5
147. rmat py re wide sot Clipboard Eont la Alignment lu Number EE Styles Cells Editing cs de A Wc H M M P SERERE HER E A B D E E G s PivotTable Field List 1 2 Choose fields to add to report 3 Sum of Biomass q Species Lv i 4 Sample ID Y Replicate Acanthurus xanthopterus Cephalopholis argus Chlorurus microrhinos Hipposcarus longiceps Lethrinus harak Lutjanu oras 5 SDI 1 0 0 22 28788213 0 o ais P 6 2 0 0 14 39864279 0 56 50028025 Reef type 7 3 0 0 106 9436501 278 5780129 0 132 v Replicate 4 ol 0 1636 499422 0 0 100 i 5 0 0 0 219 9682947 0 0 DI1 Total o o 1780 129597 498 5463077 56 50028025 351 DI2 1 5 61549908 0 38 07614397 20 87648147 0 2 34 68837875 0 89 38812819 0 0 Biomass g 3 0 0 49 99871473 27 58813905 0 0 10 cm 4 8 895905473 0 22 54191114 0 0 7 10 20 cm CAM 5 17 79181095 51 16810232 0 0 0 1020 30 cm 16 DI2 Total 66 99159425 51 16810232 200 004898 48 46462052 0 1730 40 cm _17 FDO1 1 0 0 3344 823399 803 8615862 0 40 50 cm 18 2 0 0 801 6671508 825 504714 0 gt 50 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 o o 4146 490549 6750 834258 o Drag fields between areas below 23 2DO2 1 0 0 410 4122884 42 20930013 0 7 Report Filter EF Column Labels 24 2 0 0 11 52533634 2 651510778 41 63864716 Species y 25 3 0 0 38 20208402 21 42392402 0 AO AO LO 26 4 0 0 60 24855971 71 45131273 0 mo 5 0 0 0 426 7695095 0 28 DO2
148. rrespond to 10 transects surveyed 5 inside of the MPA at a 3m depth in 2009 and 5 outside Biomass a arlables Acanthurus lir Acanthurus ni amp canthurus tr Caranx melam Cephalophalu3Cheilinus undi Chlorurus mici Chlorurus sor Ctenochaetus Epinephelus m Epinephelus rm Grouper Hipposcarus h Kyphosus Lutjanus gibh Lutjanus mandghacolor m S28 0 432 78 0 T 0 n D 1327 8 238 08 n 14 555 D D n D D S27 D 0 0 n 0 1466 5 490 53 o 0 il EE 0 ol n 53123 n 390 86 50466 4818 5 58807 n r 0 2468 13980 n n Ell c29 o 259 03 0 0 0 308 81 D 997 67 335 02 0 48 791 0 D 13980 0 0 5301 0 n n 0 183 47 n 1877 633 77 n D D D n D D S81 EE n n 960 09 217 06 485 29 n n 26 772 n n 0 0 326 09 n 205 17 562 0 0 a 58868 0 0 0 D 56423 a 0 3540 3 0 oo 0 B 563 o 0 0 0 51 168 0 0 0 130 29 n D 0 0 0 454 49 0 S54 0 n D 18 078 0 D 19 387 n D D D n D D 565 0 89 217 0 0 173 0 D 142 33 0 D 0 721 27 n 0 0 SIMPER Design Measure 2 One way 9 Bray Curtis similarity E J Two way crossed C Euclidean distance Factor Site List only higher contributing variablez Cut off percentage so Cancel Help 50 Under Factor A a Select Site So we can determine differences can leave the default settings that match our MDS plot generation 51 Click OK 52 Confirm Exercise 7 Managing and Using Data Guidebook 112 Page Note Scroll do
149. rrier Chuuk _ Defer Layout Update K KN Metadata Chuuk REA Invert Summarv Chuuk REA Invert Pivot We can now easily see that sea cucumbers are preferably found on the inner reefs as expected but why is there so 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 i e if we re do the surveys do we have statistical confidence to detect a change especially at the sites where resources are good Exercise 2 Managing and Using Data Guidebook 24 Page 29 On the PivotTable Field List 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 use Z A qr Lue ceu i OR Chuuk REA invert database PHworking Microsoft Excel Tp ou 4A bim pn n gt q d f fis WX Tx Iis n 4 Im 1 Rn r3 A X me sui EC m X n ae Calibri Body 10 A X rwymyn call p Wrap Text Tea d E d 7 Normal Bad A EN 18 A Copy M MJ lr
150. rt Filter EE Holothuria atra eel E Holothuria edulis id Axis Fields Categories Holothuria fuscopuntata Island v Pearsonothuria graeffi L_ Stichopus chloronotus Legend Fields Series 7 stchopus hermanni gen Values Trhelenota ananas Thelonota anax Z Values V Sea Cucumber Total Average of Sea Cucumber Total D Echinaster luzonicus StdDev of Sea Cucumber Total L Acanthaster planc _ Culcita novaguinea Linckia guildingi Linckia laevigata Seastar Total JEchinometra vi B Average of Sea Cucumber Total E StdDev of Sea Cucumber Total Drag fields between areas below W Report Filter EE Legend Fields E Values nf EH Ads rad Ct E Values Island Y Average of Se Y StdDev of Sea Y 0 d T T Chuuk Etal Kuop Losap Lukunor Murilo Nama Nomwin X Satawan blank E _ Defer Layout Update Mab N Ready Metadata Chuuk REA Invert Summary Chuuk REA Invert Pivot 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 sea cucumbers a consequence of the high islands located in Chu
151. s pa Formats e Comments C Validation Operation e None L 7 Add Subtract Skip blanks Right click and select Paste Special Albusing Source theme All except borders C3 Column widths C2 Formulas and number formats Values and number Formats CO Multiply C3 Divide Transpose You should now have a new formatted sheet Confirm below Exercise 4 Managing and Using Data Guidebook 41 Page Pohnpei MPA fish transects exercise Microsoft Excel s s E fn EAN B mn Verdana l0 A amp E Ep Wrap Text General Y Normal 8 Normal 9 s i CN 3 Copy SS SSS a Bl al Fill Base A Formal Painter ie es iei mm een El merge a Center rene e Comddiowel Format Bad Insert Delete Format 5 ro Clipboard Ta Font E Alignment Ta Number Ta Styles Cells A1 a fe Sum of Biomass g B C D z F G H I J K L M N O P Q R Sum of Biamass g Species 2 Sample ID Replicate MPA Reef type Acanthuru Acanthuru Caranx me Cephaloph Chlorurus 1 Hipposcart Lethrinus FLutjanus fi Lutjanus q Lutjanus m Monotaxis Naso litura Naso uniccParupene _ 3 DIM 1 Yes Inner 0 o D 22 28788 0 0 118 4382 146 1008 D 83 85516 0 o 4 Yes Inner 0 0 0 D 14 39864 O 56 50028 D 63 68222 0 0 0 D 418 673 ma 3 Yes Inner 0 0 0 D 106 9437 278 578 0 132 6184 64 12766 0 0 0 o B 4 Yes Inner 0 0 0 O 163
152. t Row labels O Abundance Onentation Biomass Note We did not include a title in our Excel sheet Samples as columns Environmental 2 Samples as rows O LInknown ather 25 n the next dialog box a Uncheck the green mark next to Title Blank 2 Missing value Note We also did not include How labels in our Excel sheet Zero b Uncheck the green mark next to Row labels c Select Samples as rows as our data are aligned in leu Heb rows 26 Select Biomass for the type of data Exercise 7 Managing and Using Data Guidebook 98 Page 27 Click Finish You should have now successfully imported your data into PRIMER maximize the windows and confirm PRIMER 6 Yap Mimpal MPA Fish PHworking PR File Edit Select View Analyse PERMANCOYA Tools window Help 50 Xx Deh n dX Be K C Ogg s o Bam V TY GS workspace ww M Y TN Yap Nimpal MPA Fis Blomass 4 arabes A E Macol aec AD 0 al 0 al ol 0 ol 247 95 0 ol 0 al 0 0 ni Dn 44989 al y Ya mM al Bi 406 21 sw 4 wo wo HU n EN 4558 13283 Em ol a al al n 38739 Oo aoe al w al pl n EE Tow o al al n o p 13103 n 48790 n 0 Bl o ol 70822 a n
153. t If you know any of the above 3 values the simple analyses through R will provide you with the calculation of the fourth 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 Report Filter Column Labels u o sth ota Row Labels gt Values Average ofHC 5tdDev of HC a _ 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 i R Console E version 2 9 1 2009 06 26 Copyright c 009 The R Foundation for Statistical Computing ISBN 3 900051i 07 H Rois free software and comes with ABSOLUTELY HO WARRANTY You are welcome to redistribute it under certain conditions Type licensel or licence for distribution details Natural language support but running in an English locale R is a
154. t settings that match our MDS plot generation and 1 Click OK 82 Confirm ml S anum e d Group Gachuug Group Mimpal Species Ay Abund Ay Abund ee al MEIN Contr d Cum 1 614 S i 1 iy E H LU LU Ln Ln Scars 2p 0 00 Acanthurus nigricauda Chlorurus sordidus Cephalopholus argus Lutjanus gibbus GEOUper A LI H cu P He o 4 J C C c a a Ulis edu si MEIN Hals Ea MEIN m p r 1 O LO Ln re BL CI ca CO CT n e cn lo JO m 0 mn ee 1 ch E L J I l O Pa D C3 Chlorurus microrhinos CO ds Macolor macularis map C m CI om m Ctenochaetus Reus a Vets Hipposcarus longiceps oo 1 Wo pp Dm LI LO Scroll down the text output sheet so we can see the comparison between the two sites The relevant section was again manually highlighted in blue for identification Exercise 7 Managing and Using Data Guidebook 122 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 has data 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 three fish cumulatively accounted for gt 50 of the variance the last column tells
155. tatus 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 but keep 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 143 Page a Change our Test to Pair wise 66 From the drop down menu below select MPA Reef type Location 67 Click OK 68 Confirm the second PERMANOVA results sheet below PATE WIS5E TESTS Term MF Re Lo 1 Within level D of factor Location MaL eau ER 2 lane e b melee lee dne Unique Groups t Pipermi perms Yes No 1 293 0 35 3 Denominators Groups Denominator Den dtf Yes Ho 1 5i1 HP RefLol Average Similarity between within groups Yes No YSS X sm m Na aro ls a ES Within level E of factor Location Within level Inner of factor Reef type Unique Groups t Pi perm perma Wes Ho Oo fa Z 4a 1 3 Here we can see the results of the pair wise comparisons The box above i e the screenshot highlights 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
156. tebooks Notebook3 1L Section 1 Data 1 A 2000 0000 3 0 0000 0 0000 0 0000 0 5000 1 5000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 2 0000 0 0000 3 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 5000 0 5000 0 0000 0 0000 1 0000 0 0000 0 0000 0 0000 1 0000 1 0000 0 5000 0 0000 0 0000 v PPP Exercise 6 1 2001 0000 0 0000 0 0000 0 0000 0 0000 0 0000 1 0000 1 0000 3 5000 1 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 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 e ANNM 2002 0000 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 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 5000 0 0000 0 0000 0 0000 0 0000 0 0000 YW 2003 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 0 0000 2 0000 0 0000 0 0000 0 5000 1 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 0000 0 0000 4 0000 4 5000 9 5000 2 0000 MANANNAN 2004 0000 0 0000 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 5000 0 0000 0 5000 0 0000 0 0000 0 5000 0 5000 0 0000 0 0000 0 0000 0 0000 0 0000
157. tivariate 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 in fact 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 MOuterYesMI3 the green plus signs and 2 MOuterYesMl4 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 from exercises 3 4 and 8 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 in all cases This may be due to the confidence in our 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 pr
158. to the fields box putting it above MPA de PivotChart Filter pane X PivotTable Field List 3x PU x h Active Fields on the PivotChart bl Choose fields to add to report 7 Report Filter sample ID MPA 5000 0 0 9 0 Y OO it Axis Fields Categories 7 Reef type L IR F fre Replicate Reef type Siganus vu Ipin us wA s Species Siganus puellus gt ae i egend Fields Series a Siganus doliatus n 3 Ob l igi S 4000 Parupeneus barberinus species ll Biomass g Naso unicornis X Values he Ed 10 20 cm rr E Naso lituratus Average of Biomass g 20 30 on A Monotaxis granaculis 30 40 cm 3000 B Lutjanus monostigma 40 50 cm 50 cm B Lutjanus gibbus O B Lutjanus fulvus E Lethrinus harak Drag fields between areas below 2000 a Hibrioscaras kitisceps wW Report Filter fal Legend Fields B Chlorurus microrhinos B Cephalopholis argus B Caranx melampygus 1000 E Acanthurusxanthopterus E Acanthurus lineatus In Ho axis Fields Cat E Values Reef type Average of Bio MPA Mm 0 No Yes No Yes s Inner Duter b El C Defer Layout Update K M Site information PNP fish pivot chart PNP fish pivot PNP Fish Ml n Now we can clearly see that inner reef MPA s are protecting a much larger proportion of the biomass as compared with outer reefs Specifical
159. uk 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 b 70 all E WD LM o N o m o Bi StdDev of Sea Cucumber Total B Average of Sea Cucumber Total PivotChart Filter Pane X Y Report Filter i Axis Fields Categories Island Ei Site w Depth S Legend Fields Series Values X Values Average of Sea Cucumber Total StdDev of S
160. ulative data summaries g Exercise 3 Managing and Using Data Guidebook 30 Page Ca did Oe nanna Home Insert Page Layout Formulas Data Review View ld EB Cp 5 doc ey PivotTable Table Picture Clip SHADES SmartArt Line Pie Bar Area Scar 1 Fm 3 D Column Right click in the chart area and move this chart to its own sheet Name the sheet PNP fish pivot chart Confirm below 5000 Illustrations E blank B Siganus vulpinus B Siganus puellus E Siganus doliatus B Parupeneus barberinus B Maso unicornis 3000 B Naso lituratus B Monotaxis granoculis 8 A Lutjanus monostigma A Lutjanus gibbus B Lutjanus fulvus 2000 B Lethrinus harak B Hipposcarus longiceps B Chlorurus microrhinos B Cephalopholis argus B Caranx melampygus 1000 E Acanthurus xanthopterus E Acanthurus lineatus 500 No Yes blank 9 Click on the MPA drop down menu in the PivotChart Filter Pane Exercise 3 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 in
161. uoccUouuUuUrodocuo u ujUUu oocuo bruou 1oo cut 14 65502 Reef Type Depth m Transect Acanthurt Acanthurt Acanthurt Caranx me Cephalopl Cheilinus Chlorurus Chlorurus CtenochaeEpinephel Epinephel Grouper 10m 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 0 Hipposcar Kyphosus Lutjanus g Lutjanus 20 78538 583 3206 0 166 3515 oo ogo oO 8 o no am oa o oa oe oa o tc oooo t e 246 795 0 0 E 0005 DD 4 o i E 0005055055005 oc o o 13980 48 13980 48 0 oo o o 0 o mn o 64 ce 1128 66 1838 304 690 3334 1249 616 oo o o o o 95 Page c Delete column AC Now we have to fill in the missing cells in columns A through E with fill down functions similar to before Do this on your own and confirm the look of your working data table below A B C D E EU cs H J K L M N O p a R 5 T U v 8 1 Site MPA Stat Year Reef Type Depth m Transect Acanthurt Acanthuru Acanthuru Caranx me Cephalopl Cheilinus Chlorurus Chlorurus Ctenochat Epinephel Epinephel Grouper Hipposcar Kyphosus Lutjanus g Lutjanus 2 Gachuug Reference 2007 Channel 3m 1 0 o 0 o 0 0 0 0 247 9478 0 0 0 0 0 0 3 Gachuug Reference 2007 Channel 3m 2 0 449 9913 0 O 127 7607 0 0 0 406 2062 0 0 0 0 0 0 4 Gachuug Reference 2007 Channel 3m 3 155 5967 132 629 D 0 0 0 0 O 387 3948 0 310 7563 0 0 0 Gachuug Reference 2007 Channel 3m 4 0 o 0 o 0 o 0 O 1310 312 O 48 79071 o 0 0 0 6 Gachuug R
162. ven 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 might do better by considering 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 72 kk 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 s
163. 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 Bh Pohnpeifish MPA PERMANDVA exsercise ry FahnperMPA Fsh PERMAMNLPIVA example PERMDISP Distance based test tor homogeneity of multivariate dispersions Resemblance worksheet Mame Reseme Data type Similarity a Wee e ArI Transform Log TL Eesemblance S17 Bray Curtis similarity Group factor Site Humber of permutations 999 Humber of groups 3z Humber of samples 155 DEVIATIONS FROM CENTEOLD pc umm bp 3L aub P permi 0 1 MEANS AND STANDARD ERRORS Group size Average ES CIL 5 41 365 8 88421 DIZ b 39 943 4 5647 sites 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 139 Page PERMANOVA Testing The input for a PERMANOVA test is a similarity matrix such as our Bray Curtis similarity matrix we created that describes how similar each individual transect is to one another 55 On the left hand side of the screen highlight the log transformed sheet 56 Go to the Analyse menu a Select Resemblance make sure you are calculating a Bray Curtis similarity matrix again
164. wer of the performed test 0 431 is below the desired power of 0 600 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 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 municipalities Yap State Federated States of Micronesia Rather than focus upon
165. wn the text output sheet so we can see the comparison between the two sites The relevant section was manually highlighted in blue for identification 3 AT 4 mag Nimpal A imilarity Group Gachuug Group Himpal species hw Abund hw Abund D Contribs 18 Chlorurus sordidus T 29 UU Ei Bue sp D00 Cephalopholus argus Kyphosus Cheilinus undulatus Caranx melampygus Acanthurus nigricauda Hipposcarus longiceps Ceenochdet uS jeece alia ye Wks 1 ee To C3 i Po tal MI CI lO El I cw m C am 0 Ln Oo O e U LT cv of iD in e lt E Groumpner Plectorhinchus lineatus Chlorurus microrhinos Epinephelus merra oO tn m rm E a a co co 1 I oo Ll C doa eat cea LO co LOCI r ta J C 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 5096 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 ot
166. x 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 Plots Axes Graph ANIS Apply ta mm Y _ na n Average COTS ob observed per 10m w 100m2 v Rename Rename E Mc settings tor S scale type E Linear v Range Start Calculation U Constant End Calculation A 14 Data Range Pad 5 Nearest tick Tick Label ls m Si a In the End box change the 14 to a 7 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 E a ao D M LO qe c qe Li EU e LA E a qe C a de gt T 2002 2004 2006 2005 We can summarize that higher than average A planci abundances were evident in the CNMI between 2003 and 2006 We now wish to understand the ecological consequences of high sta
167. xample PHworking xIsx Compatibility Mode Microsoft Excel m x Home Insert Page Layout Formulas Data Review View Add Ins Acrobat Options Design mx a Cut meral _ T kide T Lam c Pu E Autesum iE e c eximie mem ca De RED ier 143 Copy EA TA AA a 2 D y Fill lt Paste gt B 7 U E lt EE E SSI Merge amp Center T gt E E Conditional Format Good Veutral EI Insert Delete Format Sort amp Find s Clipboard S Font ri s Y Number gj Styles ooo mU FAR It Editing Ur E E le amp o D Eust ug S LITERE ER et I ML N E E PivotTable Field List 2 Choose fields to add to report 3 Average of Acanthaster 4 Site Collector 5 SAGU 2 36 5 2001 SiteCode _ Island Yn w Date LS v Year 9 ros 10 25 23 2002 impact Sites Fi Transect A Echinometra 13 Echinothrix 14 Diadema 15 9 17 2003 Grazing Urchin Total 16 Heterocentrotus U 29 28 2005 T JEchinostrenhus of Tripneustes gratiella Linckia 19 EI Akino Reef 2816 2 20 ral 22 35 9 2002 a 23 24 34 2 2003 Drag fields between areas below V Report Filter E Column Labels 28 Alaguan Bay 29 16 2004 29 36 25 30 2002 3T 38 M 4 M CNMLinvert pivot Sheetl Metadata 92 A Ready Exercise 6 1 Managing and Using Data Guidebook Our table now has the population density estimates for coral eating starfish dur
168. xercise 7 Just as an exercise lets run an associated non parametric test of significance now This procedure is called an ANOSIM short for analysis of similarities 83 Highlight your Resem7 similarity matrix associated with this plot 84 Go to the Analyze 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 amp Pohnperfish MPA PERMANDVA exercise T 3 m Pohnpei MP fish PERMANDVA example 123 MPA Test m Overall Transform PERMANOVAT PERMANDVA2 PERMANOVAS a m Resemblance3 if Reseml 4 Design3 PERMANOVAS ANDSIM1 o Grants m Resemblanced f Resem2 amp PERMDISP1 Frequency 0 2 0 1 0 1 0 2 0 3 0 4 0 5 0 6 R Exercise 8 Managing and Using Data Guidebook 148 Page The first graph that appears shows the variation in the permutated H values that were calculated typically a frequency distribution centered around 0 is desired to show that the procedure was successful and that the calculated R statistic is consistent 88 Click on the ANOSIM spreadsheet on top of this graph page a Scroll down to the results for the Global Test Bh Pohnpeifish MPA PERMAN VA exercise HER Pahnpei MPA fish PERMANDVA example E Overall Transfarm CER log transformed ER M 2m
169. y A a A wy A id A A v A wv Exercise 8 Managing and Using Data Guidebook 131 Page In this MDS plot we can start to see where some of the ecological variation exists While not extremely clear we can start to see separation between the two different reef types 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 Now let s 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 Plot the labels 27 Click OK 28 Confirm Transform Log X 1 Resemblance S17 Bray Curtis similarity 2D Stress 0 28 Reef type A Inner y Outer S80 S89 A A S157 set edle asp nor s38 S86 Exercise 8 Managing and Using Data Guidebook 132 Page We can see that our outlier transect is S126 Note that on your scratch paper 29 Move back to our
170. 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 the MPA 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 that calculates the relative contributions of each species in determining the trends that the graph show 44 G0 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 45 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 826 30 and 861 65 Note those sample labels on your scratch paper and close the factors box 46 On the main datasheet highlight only the new samples noted above 47 Go to the Select menu a Select highlighted Exercise 7 Managing and Using Data Guidebook 111 Page 48 Confirm your datasheet below Notice only 10 samples remain these co

improving local capacity for coral reef monitoring data interpretation

Contents

Download Pdf Manuals

Related Search

Related Contents