Check new species data integration in public data

EmilyMarkowitz-NOAA commented 1 year ago

I took the latest worms and itis files created by @SarahFriedman-NOAA and integrated them into the newest version of the FOSS data. They are currently uploading to Oracle, which usually takes a day or two.

[x] @SarahFriedman-NOAA, can you review how I combined the worms and itis files, applied them to the public data, and the output file in oracle RACEBASE_FOSS.FOSS_CPUE_ZEROFILLED (and/or RACEBASE_FOSS.JOIN_*) and let me know if I have used the files correctly/if any changes need to be made?
[x] @Lewis-Barnett-NOAA, do you want to take a look and make sure this meets the needs for the west coast data join package?
[x] @SarahFriedman-NOAA If you like, I can post or give you the code to post your latest (recognizing beta) versions of these itis and worms tables to the GAP_PRODUCTS schema along with the rest of the OLD_* tables. The code for uploading to oracle from R would look something like this:

# hopefully a temporary location for these functions
source("https://raw.githubusercontent.com/afsc-gap-products/metadata/main/code/functions_oracle.R") 

# establish oracle connection 
# you will need the GAP_PRODUCTS user/pass, which I can send you separately
channel <- oracle_connect()

# establish what files you want to upload to oracle. 
# The names of the files will be used as the files on oracle
# In this example, tables are saved in a parent directory as taxon_itis and taxon_worms, so switch those out with whatever name you like
file_paths <- data.frame(file_path = c("./taxon_itis.csv", "./taxon_worms.csv"), 
                         table_metadata = Sys.Date()) # develop a more sophisticated table metadata as you see fit, though GAP_PRODUCTS.METADATA_TABLE may be helpful. 

# upload tables to oracle
oracle_upload <- function(
    file_paths = file_paths, 
    # metadata_column = ..., # possibly borrowed from GAP_PRODUCTS.METADATA_TABLE, but a dummy table is default
    channel = channel,
    schema = "GAP_PRODUCTS")

SarahFriedman-NOAA commented 1 year ago

Thanks for working on this, Em! I see that the scientific_name column on the CPUE table only has the genus -- is this correct?

Happy to add the code and tables for the taxonomy update to other, more accessible locations. I'll work on that this week.

EmilyMarkowitz-NOAA commented 1 year ago

Of course! That's weird - maybe you got it at an odd stage of upload? Or just happened to see some taxon listed that were only ID'ed to the genus level?

Can you send me a screenshot of what you saw?

Either way: Here is what I see when I open the RACEBASE_FOSS.JOIN_FOSS_CPUE_CATCH table in Oracle. _Note that the RACEBASE_FOSS.FOSS_CPUE_ZEROFILLED is still uploading, so the one currently on oracle is out of date. When The new table has uploaded, it should have the same data column_:

Lewis-Barnett-NOAA commented 1 year ago

Yeah, I noticed the same issue with that column in the old version, so we'll just have to wait for the upload to complete.

On Mon, Feb 13, 2023 at 5:48 PM Em Markowitz (NOAA) < @.***> wrote:

Of course! That's weird - maybe you got it at an odd stage of upload? Or just happened to see some taxon listed that were only ID'ed to the genus level?

Can you send me a screenshot of what you saw?

Either way: Here is what I see when I open the RACEBASE_FOSS.JOIN_FOSS_CPUE_CATCH table in Oracle. Note that the RACEBASE_FOSS.FOSS_CPUE_ZEROFILLED is still uploading, so the one currently on oracle is out of date. When The new table has uploaded, it should have the same data column:

[image: image] https://user-images.githubusercontent.com/75965120/218616369-9d3193a0-e8b8-4810-ae7f-4397b451d6b8.png

— Reply to this email directly, view it on GitHub https://github.com/afsc-gap-products/gap_public_data/issues/11#issuecomment-1428985117, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAKMJP6FXEWOV5EG6FYMJTLWXLP7VANCNFSM6AAAAAAU2QFDYM . You are receiving this because you were mentioned.Message ID: @.***>

-- Lewis Barnett, PhD (he/him/his) Research Fish Biologist

NOAA Fisheries, Alaska Fisheries Science Center 7600 Sand Point Way NE, Bldg 4 Seattle, Washington 98115 Google Voice: (206) 526-4111

SarahFriedman-NOAA commented 1 year ago

Ok so the scientific names are correct now, but for some species that aren't found in both databases the ID for the database they are found in is printed under both the ITIS and WORMS column (e.g. Abyssocladia flagrum is only found in WORMS, but has an ITIS code printed that is actually the WORMS code).

Oh, and feel free to post these tables to GAP_PRODUCTS!

EmilyMarkowitz-NOAA commented 1 year ago

Ah, yes, great catch! Looks like I can just use the latest table you sent me since it has both ITIS and WoRMS codes in it. modified the code and am running it now. I'll let you know when the new output tables are up, likely early next week.

And sounds great! Consider it done :)

afsc-gap-products / gap_public_data

Check new species data integration in public data #11