<jupyter_start><jupyter_text>Aligning Data to Darwin Core - Sampling Event with Measurement or Fact using Python<NAME>November 9, 2020 General information about this notebookThis notebook was created for the IOOS DMAC Code Sprint Biological Data SessionThe data in this notebook were created specifically as an example and meant solely to beillustrative of the process for aligning data to the biological data standard - Darwin Core.These data should not be considered actually occurrences of species and any measurementsare also contrived. This notebook is meant to provide a step by step process for takingoriginal data and aligning it to Darwin CoreThis notebook is a python implementation of the R notebook [IOOS_DMAC_DataToDWC_Notebook_event.R](https://github.com/ioos/bio_data_guide/blob/master/Standardizing%20Marine%20Biological%20Data/datasets/example_script_with_fake_data/IOOS_DMAC_DataToDwC_Notebook_event.R)<jupyter_code>import pandas as pd
import pyworms # pip install git+git://github.com/iobis/pyworms.git
import numpy as np
import uuid
import csv<jupyter_output><empty_output><jupyter_text>Read in the raw data file.<jupyter_code>file = 'data/MadeUpDataForBiologicalDataTraining.csv'
df = pd.read_csv(file, header=[0])<jupyter_output><empty_output><jupyter_text>First we need to to decide if we will provide an occurrence only version of the data ora sampling event with measurement or facts version of the data. Occurrence only is easierto create. It's only one file to produce. However, several pieces of information will beleft out if we choose that option. If we choose to do sampling event with measurement orfact we'll be able to capture all of the data in the file creating a lossless version.Here we decide to use the sampling event option to include as much information as we can.First let's create the eventID and occurrenceID in the original file so that informationcan be reused for all necessary files down the line.<jupyter_code>df['eventID'] = df[['region', 'station', 'transect']].apply(lambda x: '_'.join(x.astype(str)), axis=1)
df['occurrenceID'] = uuid.uuid4()

# uuid for each row
#for index, row in df.iterrows():
#    df.loc[index, 'occurrenceID'] = uuid.uuid4()<jupyter_output><empty_output><jupyter_text>Event file We will need to create three separate files to comply with the sampling event format.We'll start with the event file but we only need to include the columns that are relevantto the event file.<jupyter_code>event = df[['date', 'lat', 'lon', 'region', 'station', 'transect', 'depth', 'bottom type', 'eventID']].copy()<jupyter_output><empty_output><jupyter_text>Next we need to rename any columns of data that match directly to Darwin Core. We knowthis based on our crosswalk spreadsheet CrosswalkToDarwinCore.csv<jupyter_code>event['decimalLatitude'] = event['lat']
event['decimalLongitude'] = event['lon']
event['minimumDepthInMeters'] = event['depth']
event['maximumDepthInMeters'] = event['depth']
event['habitat'] = event['bottom type']
event['island'] = event['region']<jupyter_output><empty_output><jupyter_text>Let's see how it looks:<jupyter_code>event.head()<jupyter_output><empty_output><jupyter_text>We need to convert the date to ISO format<jupyter_code>event['eventDate'] = pd.to_datetime(
                            event['date'],
                            format='%m/%d/%Y',
                            utc=True)<jupyter_output><empty_output><jupyter_text>We will also have to add any missing required fields<jupyter_code>event['basisOfRecord'] = 'HumanObservation'
event['geodeticDatum'] = 'EPSG:4326 WGS84'<jupyter_output><empty_output><jupyter_text>Then we'll remove any columns that we no longer need to clean things up a bit.<jupyter_code>event.drop(
    columns=['date', 'lat', 'lon', 'region', 'station', 'transect', 'depth', 'bottom type'],
    inplace=True)<jupyter_output><empty_output><jupyter_text>We have too many repeating rows of information. We can pare this down using eventID whichis a unique identifier for each sampling event in the data- which is six, three transectsper site.<jupyter_code>event.drop_duplicates(
    subset='eventID',
    inplace=True)

event.head(6)<jupyter_output><empty_output><jupyter_text>Finally we write out the event file<jupyter_code>event.to_csv(
    'MadeUpData_event_frompy.csv',
    header=True,
    index=False,
    date_format='%Y-%m-%d')<jupyter_output><empty_output><jupyter_text>Occurrence fileNext we need to create the occurrence file. We start by creating the dataframe.<jupyter_code>occurrence = df[['scientific name', 'eventID', 'occurrenceID', 'percent cover']].copy()<jupyter_output><empty_output><jupyter_text>Then we'll rename the columns that align directly with Darwin Core.<jupyter_code>occurrence['scientificName'] = occurrence['scientific name']<jupyter_output><empty_output><jupyter_text>Finally we'll add required information that's missing.<jupyter_code>occurrence['occurrenceStatus'] = np.where(occurrence['percent cover'] == 0, 'absent', 'present')<jupyter_output><empty_output><jupyter_text>Taxonomic Name MatchingA requirement for OBIS is that all scientific names match to the World Register ofMarine Species (WoRMS) and a scientificNameID is included. A scientificNameID lookslike this "urn:lsid:marinespecies.org:taxname:275730" with the last digits afterthe colon being the WoRMS aphia ID. We'll need to go out to WoRMS to grab thisinformation.Create a lookup table of unique scientific names<jupyter_code>lut_worms = pd.DataFrame(
    columns=['scientificName'],
    data=occurrence['scientificName'].unique())<jupyter_output><empty_output><jupyter_text>Add the columns that we can grab information from WoRMS including the required scientificNameID.<jupyter_code>headers = ['acceptedname', 'acceptedID', 'scientificNameID', 'kingdom', 'phylum',
           'class', 'order', 'family', 'genus', 'scientificNameAuthorship', 'taxonRank']

for head in headers:
    lut_worms[head] = ''<jupyter_output><empty_output><jupyter_text>Taxonomic lookup using the library [pyworms](https://github.com/iobis/pyworms)<jupyter_code>for index, row in lut_worms.iterrows():
    print('Searching for scientific name = %s' % row['scientificName'])
    resp = pyworms.aphiaRecordsByMatchNames(row['scientificName'])[0][0]
    lut_worms.loc[index, 'acceptedname'] = resp['valid_name']
    lut_worms.loc[index, 'acceptedID'] = resp['valid_AphiaID']
    lut_worms.loc[index, 'scientificNameID'] = resp['lsid']
    lut_worms.loc[index, 'kingdom'] = resp['kingdom']
    lut_worms.loc[index, 'phylum'] = resp['phylum']
    lut_worms.loc[index, 'class'] = resp['class']
    lut_worms.loc[index, 'order'] = resp['order']
    lut_worms.loc[index, 'family'] = resp['family']
    lut_worms.loc[index, 'genus'] = resp['genus']
    lut_worms.loc[index, 'scientificNameAuthorship'] = resp['authority']
    lut_worms.loc[index, 'taxonRank'] = resp['rank']<jupyter_output>Searching for scientific name = Acropora cervicornis
Searching for scientific name = Madracis auretenra
Searching for scientific name = Mussa angulosa
Searching for scientific name = Siderastrea radians<jupyter_text>Merge the lookup table of unique scientific names back with the occurrence data.<jupyter_code>occurrence = pd.merge(occurrence, lut_worms, how='left', on='scientificName')<jupyter_output><empty_output><jupyter_text>We're going to remove any unnecessary columns to clean up the file<jupyter_code>occurrence.drop(
    columns=['scientific name', 'percent cover'],
    inplace=True)<jupyter_output><empty_output><jupyter_text>Quick look at what we have before we write out the file<jupyter_code>occurrence.head()<jupyter_output><empty_output><jupyter_text>Write out the file.<jupyter_code># sort the columns on scientificName
occurrence.sort_values('scientificName', inplace=True)
# reorganize column order to be consistent with R example:
columns = ["scientificName","eventID","occurrenceID","occurrenceStatus","acceptedname","acceptedID",
           "scientificNameID","kingdom","phylum","class","order","family","genus","scientificNameAuthorship",
           "taxonRank"]

occurrence.to_csv(
    "MadeUpData_Occurrence_frompy.csv",
    header=True,
    index=False,
    quoting=csv.QUOTE_ALL,
    columns=columns)<jupyter_output><empty_output><jupyter_text>All done with occurrence!  Measurement Or FactThe last file we need to create is the measurement or fact file. For this we need tocombine all of the measurements or facts that we want to include making sure to includeIDs from the BODC NERC vocabulary where possible. Temperature<jupyter_code>temperature = df[['eventID', 'temperature', 'date']].copy()
temperature['occurrenceID'] = ''
temperature['measurementType'] = 'temperature'
temperature['measurementTypeID'] = 'http://vocab.nerc.ac.uk/collection/P25/current/WTEMP/'
temperature['measurementValue'] = temperature['temperature']
temperature['measurementUnit'] = 'Celsius'
temperature['measurementUnitID'] = 'http://vocab.nerc.ac.uk/collection/P06/current/UPAA/'
temperature['measurementAccuracy'] = 3
temperature['measurementDeterminedDate'] = pd.to_datetime(temperature['date'],
                                                          format='%m/%d/%Y',
                                                          utc=True)
temperature['measurementMethod'] = ''
temperature.drop(
    columns=['temperature', 'date'],
    inplace=True)<jupyter_output><empty_output><jupyter_text>Rugosity<jupyter_code>rugosity = df[['eventID', 'rugosity', 'date']].copy()
rugosity['occurrenceID'] = ''
rugosity['measurementType'] = 'rugosity'
rugosity['measurementTypeID'] = ''
rugosity['measurementValue'] = rugosity['rugosity'].map('{:,.6f}'.format)
rugosity['measurementUnit'] = ''
rugosity['measurementUnitID'] = ''
rugosity['measurementAccuracy'] = ''
rugosity['measurementDeterminedDate'] = pd.to_datetime(rugosity['date'],
                                                       format='%m/%d/%Y',
                                                       utc=True)
rugosity['measurementMethod'] = ''
rugosity.drop(
    columns=['rugosity', 'date'],
    inplace=True)<jupyter_output><empty_output><jupyter_text>Percent cover<jupyter_code>percent_cover = df[['eventID', 'occurrenceID', 'percent cover', 'date']].copy()
percent_cover['measurementType'] = 'Percent Cover'
percent_cover['measurementTypeID'] = 'http://vocab.nerc.ac.uk/collection/P01/current/SDBIOL10/'
percent_cover['measurementValue'] = percent_cover['percent cover']
percent_cover['measurementUnit'] = 'Percent/100m^2'
percent_cover['measurementUnitID'] = ''
percent_cover['measurementAccuracy'] = 5
percent_cover['measurementDeterminedDate'] = pd.to_datetime(percent_cover['date'],
                                                          format='%m/%d/%Y',
                                                          utc=True)
percent_cover['measurementMethod'] = ''
percent_cover.drop(
    columns=['percent cover', 'date'],
    inplace=True)<jupyter_output><empty_output><jupyter_text>Concatenate all measurements or facts together.<jupyter_code>measurementorfact = pd.concat([temperature, rugosity, percent_cover])<jupyter_output><empty_output><jupyter_text>Let's check to see what it looks like<jupyter_code>measurementorfact.head(50)<jupyter_output><empty_output><jupyter_text>Write measurement or fact file<jupyter_code>measurementorfact.to_csv('MadeUpDate_mof_frompy.csv',
                         index=False,
                         header=True,
                         date_format='%Y-%m-%d')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11613351877607789, 'fraction_numerical': 0.004172461752433936, 'mean_word_length': 5.398776418242492, 'pattern_counts': {'":': 0, '<': 109, '<?xml version=': 0, '>': 109, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '27', 'dir': 'jupyter-structured-clean-dedup', 'id': '491607', 'license': 'MIT', 'n_tokens_mistral': 3617, 'n_tokens_neox': 3420, 'n_words': 861, 'path': 'datasets/example_script_with_fake_data/IOOS DMAC DataToDwC_Notebook_event.ipynb', 'repo_name': 'ioos/bio_data_scripts', 'size': '11509'}