ISOTOPE tables fixes

[kaijagahm]

a. Zoops should probably have a Z, not a W

• are Zoops and Water tables actually identical? (EDIT: yes, they are, by dplyr::all_equal())
• Trace samples forward to results: same or different? (EDIT: can't do this; only one result for each isotopeID)
• Hypothesis: either SQL script references the wrong table (EDIT: not this, checked SQL.), or one of the tables got overwritten. Hopefully if the latter, can dig back in time and find the original zoop samples table. (EDIT: dug back to earliest database version, and the ISZ table looks the same: always has W's instead of Z's.)
• Note that the very earliest ISOTOPE_RESULTS table does include references to isotopeID's with Z's in them. So this confusion has been around since the beginning.
• (See CTS comment below, 2023-04-27, for partial resolution).

b. NA batchIDs

• either add NA to ISOTOPE_BATCHES or find batchID's
• Can probably recreate batchID's after cross-check with ISZ (requires resolving item a.)
• If not, can probably create batchID based on date, location

~~c. Should B-B102 actually be B-0102?

• Yes, but confirm that e.g. 102 is missing otherwise...~~ (fixed)

d isotopeID’s P-0493 through P-0512 are in IR but not in ISPOC. Add to ISPOC? Last one in ISPOC is indeed P-0492, so seems like not all the data got entered.

• Found original data (https://app.box.com/file/691038192281), and it includes even more data that didn’t get entered.
• Makes me wonder whether these tables need to get updated more generally.
• Only need to update through 2013: latest runDate from ISOTOPE_RESULTS is 2013-04-20.
• @joneslabND: Add some notes here about the different analysis machines and your understanding of the state of the data, following what we talked about at the 3/16 meeting.

e. ISOTOPE_SAMPLES_FISH - Are the fish supposed to match up to FISH_INFO? If so, can just remove some of the digits on the fish number. If not, need to note this. - Try removing 0’s; then match back to FISH_INFO. Should have a 1 in the removed or mortality columns.

• (EDIT: fixed! All fish now show up in FISH_INFO, and have a 1 in removed.)

[kaijagahm]

Another two issues: f. ISOTOPE_SAMPLES_BENTHIC_INVERTS has many "unknown"s in the metadataID column. There are also many rows (including many of these unknowns) where the metadataID column doesn't match the metadataID portion of the sampleID. It's not clear to me whether this is a SAMPLES or a DATA table--i.e. should I be running a metadataID check at all? Even if no (if it's a data table), then we should still address the "unknown" values. A lot of the other ISOTOPE_SAMPLES tables don't even have a metadataID column. Why does this one?

g. ISOTOPE_SAMPLES_METHANE has 42 rows where the depthBottom in the sampleID is really different from the depthBottom in the depthBottom column. This does not seem to be a case of swapping depthBottom and depthTop--something else is going on.

h. The formatting of the batchID column in ISOTOPE_SAMPLES_FISH (and ISOTOPE_SAMPLES_BENTHIC_INVERTS) is weird: multiple batches, comma-separated. Consider pivoting to long format? The way it is, we can't run a foreign key check with ISOTOPE_BATCHES. Make sure to check the other ISOTOPE_SAMPLES* tables for this formatting: I stopped after ISBI and ISF.

[kaijagahm]

The structure we have here is kind of weird--the ISOTOPE_SAMPLES tables link to the other SAMPLES tables (or in the case of ISOTOPE_SAMPLES_FISH, to the FISH_INFO table). Do we like this 2-layered approach, or should we cut out the middlemen somehow?

But, on the other hand, the columns required in the ISOTOPE_SAMPLES tables might be different than the ones in SAMPLES or FISH_SAMPLES, so I understand why it was done this way.

[kaijagahm]

Will want to update the dbschema to show these tables in a clearer way once these problems are sorted out.

[ctsolomon]

In ISOTOPE_RESULTS, for fish samples (isotopeID F-xxxx) and possibly others: rows giving H isotope results show an isotope ratio of 0.00 in the d13C and d15N columns. This should presumably be an NA.

[ctsolomon]

Re issue (a) in Kaija's original post: I am now part of the way towards resolving this. Mike McGuigan and Stuart found original isotope log files from 2011-2013. I have gone through these to pull out all the zoop isotope sample info. I have the original files and a script that manipulates them to assemble a replacement version of ISOTOPE_SAMPLES_ZOOPS. This is in my files at 'Database\Issues and fixes\GH 133 - ISOTOPE tables fixes'. It needs some additional work to finalize but it's a good start.