<jupyter_start><jupyter_text>Processing zooplankton data to DWC compliant files<jupyter_code>#get coding environment ready
#bring in pandas for manipulating columns, put no limit on the amount of columns in display
import pandas as pd
pd.options.display.max_columns = None

#bring in numpy to manipulate numbers
import numpy as np

#I need random numbers
import random

#I need to manipulate dates
import datetime<jupyter_output><empty_output><jupyter_text>Let start with data: read in existing csv  take a look at the file<jupyter_code>#Make a dataframe, parse dates
raw = pd.read_csv('https://raw.githubusercontent.com/ioos/bio_data_guide/main/datasets/AMBON_zooplankton/data/raw/AMBON2017150.csv', parse_dates=['Date_Time'])

raw.head()


raw.tail()
raw.describe()
#so it's count of 2936

#let's check for non identified observations, just in case
raw["APHIA_ID"].isnull().sum()
raw.dropna(subset=['APHIA_ID'], inplace=True)
raw.tail()
# checked NaNs removed
raw["APHIA_ID"].isnull().sum()
#Ok, doublechecking that it was an ok thing
raw.describe()
#count of 2934, as expected
#raw.head()<jupyter_output><empty_output><jupyter_text>list existing column headers, match to DWC terms<jupyter_code># what are the existing column headers?
raw.columns
# dataframe processing
# make a copy
df = raw.copy()
df.head()

#what I need is class(df[Station]) and levels. In Pandas that's...
#df['Station'].astype('category')
#hmm there are 78, that's, not going to work
df['APHIA_ID'].astype('category')
#hmmm 78

#OK, also, unique()
#df.describe(include='all', datetime_is_numeric=True)<jupyter_output>C:\Users\Mathew.Biddle\programs\Anaconda3\envs\sandbox\lib\site-packages\pandas\io\formats\format.py:1429: FutureWarning: Index.ravel returning ndarray is deprecated; in a future version this will return a view on self.
  for val, m in zip(values.ravel(), mask.ravel())<jupyter_text>Enough looking, let's get the two files as oneZooplankton has two file system going on.because of net size.Let's rowbind the 150 and the 505 datasets together. luckily, their columns already look alike.<jupyter_code>#Rename df to it's netsize

df150 = df

df150.head()

#read in the 505 data

#%ls ../Zooplankton_505

raw2 = pd.read_csv('https://raw.githubusercontent.com/ioos/bio_data_guide/main/datasets/AMBON_zooplankton/data/raw/AMBON2017505.csv', parse_dates=['Date_Time'])

raw2.tail()
#check for NaN
raw2["APHIA_ID"].isnull().sum()
#hooray, 0
#raw2[1200:1206]
raw2.describe()
#1793 observations
#rowbind these two csvs together
#first make the csvs raw copies
df505 = raw2.copy()

df505.head()


df505.describe()
#so now with have df150 and df505, let's concat

df = pd.concat([df150, df505])
df.describe()
#4727 count total
#df[2930:2940]
df['Life_Stage'].unique()
# Yay for one big dataframe

df.reset_index(drop=True, inplace=True)
df.tail()<jupyter_output><empty_output><jupyter_text>EventDate as ISO format<jupyter_code>#let's get the eventDate looking ISO_perfect
#df.head()

list = df['Date_Time']
list
#iso format the thing? from datetime package
test = list[1]
test
#est.isoformat()
test = test.isoformat()
#est
print(test)
test
#Make a list, build it with iso format, add 'Z' for utc timezone

storage = []
for x in list:
    x=x.isoformat()
    x=x+'Z'
    storage.append(x)

    #I have no idea why I can't write over in place in the list, but dumping the output into another list works for me
storage[1:6]
#done
#storage
df['eventDate']=storage
df.head()<jupyter_output><empty_output><jupyter_text>--- eventID and datasetID and OccurenceID for all the tablesSo my understanding is.....One occurenceID for each row as is here, in the untidy, because 2 measurements were taken off a given species from the 'station stop' on the cruise. And then reference that occurenceID in the long tidy measurementorFact table<jupyter_code># added by mmb 2021-10-04
# cast number should be a string not a number, so let's replace NaN with empty and convert number to string as integer.
df['Cast_Number_conv'] = df['Cast_Number'].astype('Int64').astype(str).replace('<NA>','?')


# find where lifeStage is either 'male' or 'female' and return that to another column called 'sex'
df['sex'] = df.loc[ (df['Life_Stage'] == 'male') | (df['Life_Stage'] == 'female'), ['Life_Stage'] ]
df['sex'].replace(np.nan, '?', inplace=True)
#df.loc[ (df['Life_Stage'] == 'male'), ['Life_Stage']] = ''
#df.loc[ (df['Life_Stage'] == 'female'), ['Life_Stage']] = ''
        
# Adjust life stage to not have null values but empty cells
df['Life_Stage_conv'] = df['Life_Stage'].replace(' ','?')

# adjust Accepted_Organism_Identification to separate by underscores, replace / with _ as well
df['Accepted_Organism_Identification_conv'] = df['Accepted_Organism_Identification'].str.replace(' ','_').str.replace('/','_')


df[['Life_Stage_conv','Cast_Number_conv','Accepted_Organism_Identification_conv','sex']]
df['sex'].unique()
#let's do two eggs in one nest with the datasetID and occurenceID stuff
#first, datasetID
df['datasetID'] = 'AMBON_Zooplankton_2017'

#occurenceID -- I'll have to tackle that separately
#df['occurrenceID'] =df['datasetID']+'_'+df['Station']+'_'

# mmb 2021-10-04 
# join string columns together as id
# need to convert biomass to a string to join
df['depth_str'] = df['Depth_[m]'].astype(str)
df['date_str'] = df['Date_Time'].dt.strftime('%Y-%m-%dT%H:%M:%S%Z')
df['biomass_str'] = df['Biomass_[mg dw/m3]'].astype(str)

df['occurrenceID'] = df[['datasetID',
                         'Station',
                         'Cast_Number_conv',
                         'Accepted_Organism_Identification_conv',
                         'Life_Stage_conv',
                         'sex',
                         'Type',
                         #'biomass_str',
                         'depth_str',
                         'date_str',
                        ]].agg('_'.join, axis=1)

#then eventID
df['eventID'] = df['datasetID']+'_'+df['Station']+'_'+df['eventDate']
#How many rows are in this dataframe?
#df.describe()
df['occurrenceID'].tail().tolist() # edited mmb 2021-07-23
#4726<jupyter_output><empty_output><jupyter_text>Return a sorted list of the duplicate occurrenceID's.<jupyter_code>print('Number of duplicates found = %s\n' % df[df.duplicated(subset='occurrenceID', keep=False)].shape[0])

print('Duplicate occurrenceIDs:')
sorted(df[df.duplicated(subset='occurrenceID', keep=False)]['occurrenceID'].tolist()) # added by mmb 2021-10-04<jupyter_output>Number of duplicates found = 410

Duplicate occurrenceIDs:<jupyter_text>Show me a duplicate record<jupyter_code>df.loc[df['occurrenceID'] == 'AMBON_Zooplankton_2017_BBL10_?_Calanus_glacialis_marshallae_?_?_TWINRING_150UM_MICROSCOPY_48_2017-08-22T03:47:00'].T<jupyter_output><empty_output><jupyter_text>To resolve the duplicate record, add the biomass (or abundance) value to make `occurrenceID` unique.<jupyter_code>df['occurrenceID'] = df[['datasetID',
                         'Station',
                         'Cast_Number_conv',
                         'Accepted_Organism_Identification_conv',
                         'Life_Stage_conv',
                         'sex',
                         'Type',
                         'biomass_str',
                         'depth_str',
                         'date_str',
                        ]].agg('_'.join, axis=1)<jupyter_output><empty_output><jupyter_text>Check for duplicate IDs.<jupyter_code>print('Number of duplicates found = %s\n' % df[df.duplicated(subset='occurrenceID', keep=False)].shape[0])

print('Duplicate occurrenceIDs:')
sorted(df[df.duplicated(subset='occurrenceID', keep=False)]['occurrenceID'].tolist()) # added by mmb 2021-10-04<jupyter_output>Number of duplicates found = 0

Duplicate occurrenceIDs:<jupyter_text>END of ID generation---<jupyter_code>#OK, rename and then add new columns
#rename columns as necessary

df.rename(columns={'Depth_[m]': 'minimumDepthInMeters',
                   'Life_Stage': 'lifeStage',
                   'Type': 'samplingProtocol',
                   'Longitude_[decimal_degrees_east]': 'decimalLongitude',
                   'Latitude_[decimal _degrees_north]': 'decimalLatitude',
                   'Accepted_Organism_Identification': 'scientificName',
                   'APHIA_ID': 'taxonID' }, inplace=True)

# add new parameters

df['taxonID'] = df['taxonID'].astype(int).astype(str) ## mmb 2021-11-19 fix ID generation from lopping off trailing 0
df['scientificNameID'] = 'urn:lsid:marinespecies.org:taxname:'+df['taxonID']
df['identificationReferences'] = 'WoRMS'
df['basisOfRecord'] = 'HumanObservation' #nospace!
df['occurenceStatus'] = 'present'



df.head()
df.loc[df['occurrenceID'] == 'AMBON_Zooplankton_2017_ML5.4_?_Rotifera_?_?_TWINRING_150UM_MICROSCOPY_0.0001_33_2017-08-20T01:09:00']
df['maximumDepthInMeters'] = df['minimumDepthInMeters'].copy()
# Remove unnecessary columns

df = df.drop(columns=['Cruise', 'Station', 'Bottom_Depth_[m]', 'Date_Time','Cast_Number'])

#but not the measurements!
df.head()

#taxonid needs to not have trailing .0

#mmb - don't think this is necessary either.
# taxons = df[['taxonID']].astype('string', errors='ignore')
# t=taxons['taxonID'].convert_dtypes()
# t=t.str.strip('.0')
# t
# df['taxonID']=t
# df.head()

# mmb 2021-11-19 fixing taxonIDs above
df['kingdom']='Animalia'
#finally!
#nope it's scientificNameID that needs this

# mmb - Think this was not necessary.
# sciids = df[['scientificNameID']].astype('string', errors='ignore')
# s = sciids['scientificNameID'].convert_dtypes()
# s=s.str.strip('.0')
# df['scientificNameID']=s
# df.head()
#double checking
df.columns
df['lifeStage'].unique()
df['lifeStage'].replace(to_replace='male', value=None, inplace= True, method='pad')
df['lifeStage'].replace(to_replace='female', value=None, inplace= True, method='pad')

df['lifeStage'].unique()<jupyter_output><empty_output><jupyter_text>Now for table 1 - event table Event table needs:Event Table  eventID == 'eventID'   EventDate== 'eventDate'  Depth == 'minimumDepthInMeters' , 'maximumDepthInMeters'decimal Lat== 'decimalLatitude'  decimal Long == 'decimalLongitude'  ~~basisofRecord== 'basisOfRecord'  ~~geodeticDatum == 'WGS84'  countryCode == 'US'  coordinateUncertaintyInMeters ==~~'NA'~~ Nope, if it's blank do not include.  Without repeating or duplicate events. So, filtered by unique. IN PANDAS that's...<jupyter_code>eventdf = df[['eventID', 'eventDate', 'maximumDepthInMeters','minimumDepthInMeters' , 'decimalLatitude', 'decimalLongitude']]
eventdf.head()
eventdf['eventID'].describe()

#and, that's that? Check!
#add columns of data needed
eventdf['geodeticDatum'] = 'WGS84'
eventdf['countryCode'] = 'US'
#eventdf['coordinateUncertaintyInMeters'] ='NA'

eventdf.head()
#The event table needs to only hold those 135 unique events, so....
#eventdf.head()
eventdf = eventdf.drop_duplicates(subset=['eventID'])

eventdf['eventID'].describe()

#win<jupyter_output><empty_output><jupyter_text>Now for table 2 - OccurenceOccurence table needs- Occurence/organism quantity table with the following fields, minimum    - ~~eventDate    - ~~eventID    - ~~dec lat    - ~~dec long~~    - kingdom    - scientificName     - basisofRecord (according to controlled vocab, probably HumanObservation)    - occurenceID    - datasetid (a shortname for the OPT occurence table ( more at  https://tools.gbif.org/dwca-validator/extension.do?id=dwc:event )    - organismQuantity (as applicable)    - organismQuantityType (as applicable)    - lifeStage (if present)    - occurrenceRemarks (if present)    - occurrenceStatus = present    - identificationReferenceThe tricky part here is the occurrence ID. Each occurence ID should be unique to each observation in the occurence table. It's recommended out of eventID and TAXON id. Or just make a UUID type deal.It's also, a unique occurence observation but NOT a unique measurement. SO, for abundance and biomass, they're off the same occurence.So I'm not tidy-ing the dataset yet, in case I need to brute-force something by row.<jupyter_code>#build the dataframe

occdf = df[['datasetID','eventID','occurrenceID', 'kingdom', 'scientificName', 'scientificNameID', 'taxonID','identificationReferences', 
            'lifeStage','basisOfRecord', 'samplingProtocol']]

occdf.describe().T


occdf.info()

#where are the messy values in scinameid and taxon id?
n = occdf['scientificNameID'] == 'urn:lsid:marinespecies.org:taxname:nan'
occdf[n]
#add columns still needed
#occdf.insert(0,'ocurrenceStatus', 'present')
occdf['ocurrenceStatus'] = 'present'
occdf.head()
occdf.loc[occdf['occurrenceID'] == 'AMBON_Zooplankton_2017_ML1.3_57_Parasagitta_elegans_?_?_BONGO_505UM_MICROSCOPY_0.0643_29_2017-08-17T20:46:00']<jupyter_output><empty_output><jupyter_text>Make the measurementorfact table long and tidySo the occurence table is now, bereft of actual specimen information? it references out to this table.(it could hold one measurement of the information, but in this particular case study, it made more sense not to)What I need is melt, so in pandas that's....melt. ^_^So, Abundance, and biomass, stacked on top of eachother, as 'organismQuantity' and 'organismQuantitytype' containing 'biomass' or abundance dry weight.MoFTable needs:- MeasurementOrFact table for additional attributes/variables, But ONLY those in the [OBIS mof guide](https://tools.gbif.org/dwca-validator/extension.do?id=dwc:MeasurementOrFact)    - eventID    - occurenceID    - measurementType    - measurementTypeID    - measurementValue    - measurementUnit    - measurementUnitID<jupyter_code>#OK, let's make this a tidy dataset of as much of the information as possible, THEN manipulate it into a MOF dataframe. So I have a tidy dataset if I need it.
#DataFrame.melt(id_vars=None, value_vars=None, var_name=None, value_name='value', col_level=None, ignore_index=True)


tidydf = df.melt(id_vars=['datasetID','occurrenceID','eventID','eventDate','samplingProtocol','decimalLongitude','decimalLatitude','maximumDepthInMeters', 'minimumDepthInMeters','scientificName','lifeStage','taxonID','scientificNameID','identificationReferences','basisOfRecord'],
        value_vars=['Abundance_[#/m3]', 'Biomass_[mg dw/m3]'])

tidydf.head()
tidydf.describe()
tidydf['occurrenceID'].describe()
# I will take that, let's rename 'variable ' and 'value'
tidydf.rename(columns={'variable': 'measurementType',
                   'value': 'measurementValue'}, inplace= True)
tidydf.head()

#tidydf.columns

tidydf.describe()
tidydf['occurrenceID'].describe()
tidydf.columns
#MOF table needs columns dropped, added, renamed etc
mofdf = tidydf.drop(columns=['datasetID','eventDate','samplingProtocol','decimalLongitude','decimalLatitude','maximumDepthInMeters', 'minimumDepthInMeters','scientificName','lifeStage','taxonID','scientificNameID','identificationReferences','basisOfRecord',])

#add static values from DWC vocabs etc
mofdf['measurementUnit'] = 'Number per m3'
mofdf['measurementUnitID'] = 'http://vocab.nerc.ac.uk/collection/P06/current/UPMM/'
#mofdf['measurementTypeID'] = '' #Do not include things that are blank values
mofdf.head()
mofdf['measurementTypeID'] = ''
#for abundance mofdf['measurementTypeID'] = 'http://vocab.nerc.ac.uk/collection/S06/current/S0600002/'
#for biomass mofdf['measurementTypeID'] = 'http://vocab.nerc.ac.uk/collection/S06/current/S0600086/'

mofdf.loc[mofdf['measurementType'] == 'Abundance_[#/m3]', 'measurementTypeID']= 'http://vocab.nerc.ac.uk/collection/S06/current/S0600002/'
mofdf.loc[mofdf['measurementType'] == 'Biomass_[mg dw/m3]', 'measurementTypeID']= 'http://vocab.nerc.ac.uk/collection/S06/current/S0600086/'
mofdf[2100:2110]<jupyter_output><empty_output><jupyter_text>Check scientificNames<jupyter_code>import pyworms

# Testing pyworms package on known invalid aphia
resp = pyworms.aphiaRecordByAphiaID('10544')
if resp is None:
    print("invalid AphiaID")<jupyter_output>invalid AphiaID<jupyter_text>Now lets check all of the taxonIDs against the aphiaID service:*If the next cell doesn't print a message then all taxonIDs are valid.*<jupyter_code>import pyworms

for taxon in occdf['taxonID']:
    resp = pyworms.aphiaRecordByAphiaID(taxon)
    if resp is None:
        print("%s not valid!" % taxon)<jupyter_output><empty_output><jupyter_text>write out dataframe to new csv file<jupyter_code>#Event Table
eventdf.to_csv('data/processed/AMBON2017_zooplankton_Event_DWC_mb.csv', index=False)
#occurence table
occdf.to_csv('data/processed/AMBON2017_zooplankton_Occurrence_DWC_mb.csv', index=False)
#MoF table
mofdf.to_csv('data/processed/AMBON2017_zooplankton_MoF_DWC_mb.csv', index=False)
!ls data/processed<jupyter_output>AMBON2017_zooplankton_Event_DWC_mb.csv
AMBON2017_zooplankton_MoF_DWC_mb.csv
AMBON2017_zooplankton_Occurrence_DWC_mb.csv
{'fraction_non_alphanumeric': 0.1333492937514963, 'fraction_numerical': 0.02160641608810151, 'mean_word_length': 5.152061855670103, 'pattern_counts': {'":': 0, '<': 65, '<?xml version=': 0, '>': 65, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '285630', 'license': 'MIT', 'n_tokens_mistral': 5992, 'n_tokens_neox': 5480, 'n_words': 1335, 'path': 'datasets/AMBON_zooplankton/2017zooplanton_to_dwc(2)_mmb.ipynb', 'repo_name': 'ioos/bio_data_scripts', 'size': '16708'}