search

pfmc-assessments / PacFIN.Utilities

R code to manipulate data from the PacFIN database for assessments
http://pfmc-assessments.github.io/PacFIN.Utilities
Other
7 stars 1 forks source link

Moving to the Comprehensive_BDS_Comm table - Intro and missing ages #31

Closed John-R-Wallace-NOAA closed 2 years ago

John-R-Wallace-NOAA commented 3 years ago

PacFIN.BDS.Extraction() (with a help man page) extracts BDS data from PacFIN's Comprehensive_BDS_Comm table.

The old BDS tables were made wider with extra ages for the same fish, from readers one, two, and three moved to their own columns. To stick with a more raw data format, this is not done here and hence the default for the argument to add columns with legacy names is set to FALSE (there would have to be another table to fully match the old BDS table).

To compare the number of unique fish from the old BDS table to the new Comp_BDS, I tabulated the number of non-missing ages for unique fish [ paste(SAMPLE_NO, CLUSTER_NO, FISH_NO) ] by agency and year.

We are not concerned with a final age since NWFSC uses an aging error matrix, but for other users of PacFIN BDS data, I also looked at the number of final ages vs the number of first reads, both from the Comprehensive_BDS table .

In the Canary rockfish tables below, 'Year is sample year, 'legacyAge' is for ages from the old BDS table, compAgeFinal is the final age from the Comp_BDS table, and comAge is the first read from the Comp_BDS table. Note that the ages from a sample year could have aged at a later time.

For WA, there are more ages of unique fish in 1982 for the old table than in Comp_BDS:

    Year legacyAge compAgeFinal compAge compAge.minus.compFinal Leg.minus.CompAge
 1  1973         0            0       0                       0                 0
 2  1974         0            0       0                       0                 0
 3  1975       534          534     534                       0                 0
 4  1976       287          287     287                       0                 0
 5  1977       295          295     295                       0                 0
 6  1978       249          249     249                       0                 0
 7  1979       797          797     797                       0                 0
 8  1980      1631         1508    1631                     123                 0
 9  1981      1429         1416    1527                     111               -98
 10 1982       891          826     839                      13                52
 11 1983      1230         1360    1428                      68              -198
 12 1984      1191         1049    1191                     142                 0
 13 1985      1647         1647    1647                       0                 0
 14 1986      1195         1048    1195                     147                 0
 15 1987      1244         1240    1244                       4                 0
 16 1988       948          948     948                       0                 0
 17 1989       887          887     887                       0                 0
 18 1990       850          850     850                       0                 0
 19 1991       978          978     978                       0                 0
 20 1992       997          997     997                       0                 0
 21 1993       848          802     848                      46                 0
 22 1994       749          749     749                       0                 0
 23 1995      1100         1100    1100                       0                 0
 24 1996       729          721     729                       8                 0
 25 1997       846          846     846                       0                 0
 26 1998       827          817     827                      10                 0
 27 1999       737          737     737                       0                 0
 28 2000       227          227     227                       0                 0
 29 2001       425          425     425                       0                 0
 30 2002       643          642     643                       1                 0
 31 2003       271          271     271                       0                 0
 32 2004       490          490     490                       0                 0
 33 2005       518          518     518                       0                 0
 34 2006       647          647     647                       0                 0
 35 2007       480          480     480                       0                 0
 36 2008       629          629     629                       0                 0
 37 2009       670          670     670                       0                 0
 38 2010       781          781     781                       0                 0
 39 2011       560          560     560                       0                 0
 40 2012       504          504     504                       0                 0
 41 2013       344          344     344                       0                 0
 42 2014        81           81      81                       0                 0
 43 2015       284          284     284                       0                 0
 44 2016       383          383     383                       0                 0
 45 2017       918          918     918                       0                 0
 46 2018       684          684     684                       0                 0

Here is the WA - 1982 breakdown by month:

 # Old BDS tables
   1   3   4   5   6   7   8   9  12 
  99 149  49  50  49 247  99 100  49 

 # Comp_BDS
   1   3   4   5   6   7   8   9  12 
  99  99  48  50  49 247  99 100  48

For OR, the Comp_BDS has equal or more ages of unique fish:

    Year legacyAge compAgeFinal compAge compAge.minus.compFinal Leg.minus.CompAge
 1  1973        51           51      51                       0                 0
 2  1974       359          359     359                       0                 0
 3  1975         0            0       0                       0                 0
 4  1976         0            0       0                       0                 0
 5  1977       444          444     444                       0                 0
 6  1978         0          664     664                       0              -664
 7  1979       396          396     396                       0                 0
 8  1980         0          878     878                       0              -878
 9  1981       603          603     603                       0                 0
 10 1982      1142         1358    1358                       0              -216
 11 1983      2041         2724    2724                       0              -683
 12 1984      1257         1856    1856                       0              -599
 13 1985      1053         1204    1204                       0              -151
 14 1986       607          807     807                       0              -200
 15 1987      1448         1448    1448                       0                 0
 16 1988       899          899     899                       0                 0
 17 1989      1094         1094    1094                       0                 0
 18 1990       998          998     998                       0                 0
 19 1991       850          850     850                       0                 0
 20 1992      1280         1280    1280                       0                 0
 21 1993      1110         1110    1110                       0                 0
 22 1994       200          200     200                       0                 0
 23 1995       794          794     794                       0                 0
 24 1996      1093         1093    1093                       0                 0
 25 1997      1554         1554    1554                       0                 0
 26 1998      1641         1641    1641                       0                 0
 27 1999      1516         1516    1516                       0                 0
 28 2000       491          507     507                       0               -16
 29 2001       772          772     772                       0                 0
 30 2002      1008         1009    1009                       0                -1
 31 2003       241          248     248                       0                -7
 32 2004       333          336     336                       0                -3
 33 2005       342          343     343                       0                -1
 34 2006       240          240     240                       0                 0
 35 2007       108          110     110                       0                -2
 36 2008       195          195     195                       0                 0
 37 2009       485          489     489                       0                -4
 38 2010       340          340     340                       0                 0
 39 2011       390          390     390                       0                 0
 40 2012       493          493     493                       0                 0
 41 2013      1153         1153    1153                       0                 0
 42 2014      1319         1319    1319                       0                 0
 43 2015      1571         1571    1571                       0                 0
 44 2016         0            0       0                       0                 0
 45 2017         0            0       0                       0                 0
 46 2018         0            0       0                       0                 0

CA is all good for Canary (but see Dover below):

    Year legacyAge compAgeFinal compAge compAge.minus.compFinal Leg.minus.CompAge
 1  1973         0            0       0                       0                 0
 2  1974         0            0       0                       0                 0
 3  1975         0            0       0                       0                 0
 4  1976         0            0       0                       0                 0
 5  1977         0            0       0                       0                 0
 6  1978         0            0       0                       0                 0
 7  1979         0            0       0                       0                 0
 8  1980       263          263     263                       0                 0
 9  1981       166          166     166                       0                 0
 10 1982       222          222     222                       0                 0
 11 1983       402          402     402                       0                 0
 12 1984       364          364     364                       0                 0
 13 1985       430          430     430                       0                 0
 14 1986         0            0       0                       0                 0
 15 1987         1            1       1                       0                 0
 16 1988         0            0       0                       0                 0
 17 1989         0            0       0                       0                 0
 18 1990         0            0       0                       0                 0
 19 1991         0            0       0                       0                 0
 20 1992         0            0       0                       0                 0
 21 1993         0            0       0                       0                 0
 22 1994         0            0       0                       0                 0
 23 1995         0            0       0                       0                 0
 24 1996         0            0       0                       0                 0
 25 1997         0            0       0                       0                 0
 26 1998         0            0       0                       0                 0
 27 1999         0            0       0                       0                 0
 28 2000         0            0       0                       0                 0
 29 2001        28           28      28                       0                 0
 30 2002        98           98      98                       0                 0
 31 2003        44           44      44                       0                 0
 32 2004        12           12      12                       0                 0
 33 2005        54           54      54                       0                 0
 34 2006        31           31      31                       0                 0
 35 2007        21           21      21                       0                 0
 36 2008         0            0       0                       0                 0
 37 2009        21           21      21                       0                 0
 38 2010         0            0       0                       0                 0
 39 2011         0            0       0                       0                 0
 40 2012         0            0       0                       0                 0
 41 2013         0            0       0                       0                 0
 42 2014         0            0       0                       0                 0
 43 2015         0            0       0                       0                 0
 44 2016         0            0       0                       0                 0
 45 2017         0            0       0                       0                 0
 46 2018         0            0       0                       0                 0

Besides Canary, I have looked at the ages for Dover, Sablefish, Darkblotched, and Yellowtail. Of those looked at, only Dover in CA has a major issue, with all the ages before 1990 only being in the old BDS tables and not the Comp_BDS:

    Year legacyAge compAgeFinal compAge compAge.minus.compFinal Leg.minus.CompAge
 1  1973         0            0       0                       0                 0
 2  1974         0            0       0                       0                 0
 3  1975         0            0       0                       0                 0
 4  1976         0            0       0                       0                 0
 5  1977         0            0       0                       0                 0
 6  1978         0            0       0                       0                 0
 7  1979       925            0       0                       0               925
 8  1980         0            0       0                       0                 0
 9  1981         0            0       0                       0                 0
 10 1982         0            0       0                       0                 0
 11 1983       939            0       0                       0               939
 12 1984         0            0       0                       0                 0
 13 1985         0            0       0                       0                 0
 14 1986      1863            0       0                       0              1863
 15 1987      1975            0       0                       0              1975
 16 1988      1772            0       0                       0              1772
 17 1989      2620            0       0                       0              2620
 18 1990       484          859     859                       0              -375
 19 1991      1241         1241    1241                       0                 0
 20 1992      1562         1562    1562                       0                 0
 21 1993      1105         1105    1105                       0                 0
 22 1994      1083         1083    1083                       0                 0
 23 1995      1353         1353    1353                       0                 0
 24 1996      1649         1649    1649                       0                 0
 25 1997      1742         1742    1742                       0                 0
 26 1998      1979         1979    1979                       0                 0
 27 1999      1779         1779    1779                       0                 0
 28 2000      2483         2483    2483                       0                 0
 29 2001      2118         2118    2118                       0                 0
 30 2002      2396         2396    2396                       0                 0
 31 2003      2178         2178    2178                       0                 0
 32 2004       388          388     388                       0                 0
 33 2005         0            0       0                       0                 0
 34 2006       423          423     423                       0                 0
 35 2007       841          841     841                       0                 0
 36 2008       269          269     269                       0                 0
 37 2009       324          324     324                       0                 0
 38 2010         0            0       0                       0                 0
 39 2011         0            0       0                       0                 0
 40 2012         0            0       0                       0                 0
 41 2013         0            0       0                       0                 0
 42 2014         0            0       0                       0                 0
 43 2015         0            0       0                       0                 0
 44 2016         0            0       0                       0                 0
 45 2017         0            0       0                       0                 0
 46 2018         0            0       0                       0                 0

There is a mixture in 1990 and here is the breakdown by month:

 # Old BDS tables
   4   5   6 
 151 164 169 

 # Comp_BDS
   1   2   3   4   5   6 
 100  75 200 151 164 169

At least the issue for Dover in CA is an all year or month issue. A major mixture within a month would be more of a problem as it appears that the sample number in the Comp_BDS has been changed from the old BDS tables. (Minor issues were 30 missing ages in Comp_BDS for Sablefish in 2010 and 49 ages missing in Comp_BDS in 1987 for Yellowtail, both of those were in OR.)

Looking at the surface reads and break and burn ages on the same fish in WA in the early 80's, we see that no final age is given. Below are two examples. Because of this issue, Canary Comp_BDS has around 600 more unique fish single ages from the first reader than in final ages.

       BDS_ID SAMPLE_ID SAMPLE_YEAR AGENCY_CODE  SAMPLE_NUMBER FISH_SEQUENCE_NUMBER AGE_SEQUENCE_NUMBER AGE_METHOD_CODE AGE_IN_YEARS FINAL_FISH_AGE_IN_YEARS
 21644 1001602  63325863        1980           W 20804101260004                    4                   1               S           19                      NA
 21645 1001603  63325863        1980           W 20804101260004                    4                   2               B           26                      NA

        BDS_ID SAMPLE_ID SAMPLE_YEAR AGENCY_CODE  SAMPLE_NUMBER FISH_SEQUENCE_NUMBER AGE_SEQUENCE_NUMBER AGE_METHOD_CODE AGE_IN_YEARS FINAL_FISH_AGE_IN_YEARS
 21646 1001733  63325863        1980           W 20804101260004                    5                   1               S           20                      NA
 21647 1001734  63325863        1980           W 20804101260004                    5                   2               B           41                      NA

Since break and burn is accepted to be the superior method, the B&B final age could be filled in and the surface final age left as NA. With both left as NA, the final ages have fewer ages for WA.