<jupyter_start><jupyter_text>North coast kelp data conversionSince Laura only shared with me an MS Access DB file (.mdb), I'll have to start by extracting the data from there.Resources:- [Wiki for pyodbc](https://github.com/mkleehammer/pyodbc/wiki)<jupyter_code>## Imports

import pandas as pd
import numpy as np
import csv, pyodbc
import pickle
import datetime
## Ensure my general functions for the MPA data integration project can be imported, and import them

import sys
sys.path.insert(0, "C:\\Users\\dianalg\\PycharmProjects\\PythonScripts\\MPA data integration")

import WoRMS # functions for querying WoRMS REST API<jupyter_output><empty_output><jupyter_text>Connect to db and retrieve dataNote that (as described in the Wiki) the original database filename did not work because it included underscores. I renamed 'Abalone_DiveSurveys_EH_06242020.mdb' to 'AbaloneDiveSurveys-06242020.mdb' to fix this problem<jupyter_code>## Check available access driver

[x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]
## Function for connecting to db

def get_data_from_db(tbl_name):
    """Connects to .mdb file and extracts data from tbl_name. Saves data in a .csv file and column names in a .txt file."""
    
    # Connect
    conn_str = (
        r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
        r'DBQ=C:\Users\dianalg\PycharmProjects\PythonScripts\MPA data integration\North coast kelp\AbaloneDiveSurveys-06242020.mdb;'
        )
    cnxn = pyodbc.connect(conn_str)
    crsr = cnxn.cursor()
    
    # Get column names
    cols = [row.column_name for row in crsr.columns(table=tbl_name)]
    
    # Get rows
    SQL = 'SELECT * FROM ' + tbl_name + ';'
    rows = crsr.execute(SQL).fetchall()
    
    # Close connection
    crsr.close()
    cnxn.close()
    
    # Save data
    with open(tbl_name + '.csv', 'w') as file:
        csv_writer = csv.writer(file) 
        csv_writer.writerows(rows)
        
    # Save column names
    with open(tbl_name + '_cols.data', 'wb') as file:
        pickle.dump(cols, file)<jupyter_output><empty_output><jupyter_text>**Note** that the table names in this database are:- Paste Errors- Switchboard Items- Tbl_Diver_Lookup- Tbl_Format_Lookup- Tbl_New_size- Tbl_SppSeen- Tbl_Survey_Lookup- tblCounts- tblHabitat- tblNewGrowth- tblQuadrat- tblSite- TblSpecies- tblSubstrate- tblSurvey- tblSwimGroups<jupyter_code>## Extract data from .mdb file

tables = ['tblSite', 'tblSurvey', 'tblCounts', 'tblSpecies']

for table in tables:
    get_data_from_db(table)<jupyter_output><empty_output><jupyter_text>Load data<jupyter_code>## Function to load data

def load_table(tbl_name):
    """Takes tbl_name (a string) and loads saved data from that table."""
    
    # Get filenames
    col_name = tbl_name + '_cols.data'
    data_name = tbl_name + '.csv'
    
    # Retrieve column names
    with open(col_name, 'rb') as file:
        cols = pickle.load(file)
        
    # Load data
    data = pd.read_csv(data_name, header=None, names=cols, encoding='ANSI')
    return(data)
## Load data

site = load_table('tblSite')
survey = load_table('tblSurvey')
count = load_table('tblCounts')
species = load_table('tblSpecies')
## Tidy survey data

# Remove columns that are all nan; there are no rows that are all nan
survey.drop(columns=survey.columns[survey.isna().all()], inplace=True)

# Can probably drop these columns: DIVER, Orientation, Buddy, RANGE, TIDEHEIGHT, SLAT, SLONG, ELAT, ELONG< SLAT_old, SLONG_old, ELAT_old, ELONG_old, Format, DISTANCE, COMMENTS
survey.drop(columns=['DIVER (LEFT FOR TRANSECT)', 'Orientation', 'Buddy (RIGHT FOR TRANSECT)', 'RANGE', 'TIDEHEIGHT', 'SLAT', 'SLONG', 'ELAT', 'ELONG', 'SLAT_old', 'SLONG_old',
                     'ELAT_old', 'ELONG_old', 'Format', 'DISTANCE', 'COMMENTS'], inplace=True)

# We are only interested in the emergent survey type at this stage
survey = survey[survey['TYPE'] == 'EMERGENT']

# View
print(survey.shape)
survey.head()<jupyter_output>(455, 16)<jupyter_text>**Note** that by filtering for TYPE = 'EMERGENT', we are discarding the majority of the data (all but 455 records out of ~ 3400). **Is this really the right thing to do?**Just for documentation, this eliminates the following values under SURVEY:- 'Transect - 30m (Rapid Emergent)'- 'Transect - 20mx2m (Emergent)'- nan- 'ARM'- 'Transect - 30mx2m (Emergent)'- 'Swim'- 'Transect - 5m (Invasive)'- 'Transect - 20mx5m (Emergent)'- 'Transect - 16mx2m (Emergent)'- 'Transect - 1/4m'- 'Transect - 1/4M'- 'Transect - running transect'- 'Transect - Running Transect'Only 'Transect - 30m (Emergent)' is left. **Also note** that there are some weird values in the COUNT column of the count data, including:- 'RG18-C3-'- 'P' ConvertThere is very minimal metadata for this data set, so I'm not sure what a lot of the columns mean. I'm just going to try to work through a basic conversion process, and see where questions and problems arise. **I'm going to start by assuming that this data set can be summarized by an occurrence file only**, containing: eventID, eventDate, datasetID, locality, localityRemarks (if needed), countryCode, decimalLat, decimalLon, coordinateUncertaintyInMeters, occurrenceID, scienficName, scientificNameID, taxonID, nameAccordingTo, occurrenceStatus, basisOfRecord, identificationQualifier (if needed), occurrenceRemarks (if needed), individualCount (or organismQuantity and organismQuantityType), minimumDepthInMeters, maximumDepthInMeters, samplingProtocol and samplingEffort. eventIDAs with other similar surveys, it seems reasonable to assume that each transect is an event. **I don't know what the broader study organization is for this, i.e. number of sites, inside/outside MPAs, number of transects per site, what was looked for, what was measured.** The metadata states that the Survey_Num field is composed of the site location, year and site ID. Transect numbers should only be assigned for rapid assessment surveys, which were filtered out because they're not type EMERGENT. That said, each record in the survey table has a unique Survey_Num, so hopefully that can be used as an eventID. **For now I'm going to assume that the Survey_Num format is the site ID + year + transect number. Note** that there are a number of Survey_Nums that do not fit this format, though, including:- 'PA71C-01'- 'PA86dfg-13'<jupyter_code>## eventID - as with other similar surveys, I'll assume the event is a transect

occ = pd.DataFrame({'eventID':survey['Survey_Num']})
occ.head()
## eventDate

occ['eventDate'] = survey['DATE']

# format
eventDate = [datetime.datetime.strptime(dt, '%Y-%m-%d %H:%M:%S').date().isoformat() for dt in occ['eventDate']]
occ['eventDate'] = eventDate
occ.head()<jupyter_output><empty_output><jupyter_text>There are 71 unique survey dates in this data set. **Note** that the earliest of these is 1971-09-01 and the latest of these is 2001-08-22. **This makes no sense, seeing as Laura was talking about having entered the 2017 data over the summer. It must be the 'EMERGENT' filtering step. But I'll let it be for now.**<jupyter_code>## datasetID

occ['datasetID'] = 'North coast kelp emergent transects'
occ.head()
## locality

occ['SiteID'] = survey['SiteID']
occ = occ.merge(site, how='left', on='SiteID')
occ.rename(columns={'SITE':'locality'}, inplace=True)

## ------ TODO: Drop SiteID, but for now leave it in in case I need to merge on it again.

print(occ.shape)
occ.head()<jupyter_output>(455, 5)<jupyter_text>**Note** that for most of the kelp forest survey data sets, there's a localityRemarks column indicating whether each site was inside or outside an MPA. I don't seem to have this information here, though.<jupyter_code>## countryCode

occ['countryCode'] = 'US'
occ.head()<jupyter_output><empty_output><jupyter_text>**The site table doesn't contain lat/lon information, and the survey table seems to contain weird forms of it.** I received the following information from Laura in June:| Site           | ID  | Latitude  | Longitude  ||----------------|-----|-----------|------------|| Albion Bay     | AB  | 39 22.900 | 123 77.881 || Caspar Cove    | CC  | 39 36.556 | 123 82.362 || Fort Ross      | FR  | 38 50.797 | 123 23.851 || Ocean Cove     | OC  | 38 55.363 | 123 30.688 || Point Arena    | PA  | 38 54.783 | 123 42.835 || Point Cabrillo | PC  | 39 20.752 | 123 49.628 || Russian Gulch  | RG  | 39 32.687 | 123 80.782 || Salt Point     | SP  | 38 33.972 | 123 20.172 || Sea Ranch      | SER | 38 42.468 | 123 27.302 || Timber Cove    | TC  | 38 53.476 | 123 28.192 || Todds Point    | TP  | 38 43.195 | 123 81.780 || Van Damme      | VD  | 39 16.044 | 123 47.662 |**Note** that something is clearly wrong here. I assume these coordinates are in degrees + decimal minutes, but several of the decimal minute entries are > 60. This shouldn't be.<jupyter_code>## Create a digital copy of the table Laura sent

# Define columns
site_name = ['Albion Bay', 'Caspar Cove', 'Fort Ross', 'Ocean Cove', 'Point Arena', 'Point Cabrillo', 'Russian Gulch', 'Salt Point', 'Sea Ranch', 'Timber Cove', 'Todds Point', 'Van Damme']
site_id = ['AB', 'CC', 'FR', 'OC', 'PA', 'PC', 'RG', 'SP', 'SER', 'TC', 'TP', 'VD']
lat_deg = [39, 39, 38, 38, 38, 39, 39, 38, 38, 38, 38, 39]
lat_min = [22.900, 36.556, 50.797, 55.363, 54.783, 20.752, 32.687, 33.972, 42.468, 53.476, 43.195, 16.044]
lon_deg = [123]*12
lon_min = [77.881, 82.362, 23.851, 30.688, 42.835, 49.628, 80.782, 20.172, 27.302, 28.192, 81.780, 47.662]

# Create df
site_lat_lon = pd.DataFrame({'Site':site_name,
                            'ID':site_id,
                            'Lat_deg':lat_deg,
                            'Lat_min':lat_min,
                            'Lon_deg':lon_deg,
                            'Lon_min':lon_min})

# Convert lat, lons to decimal degrees
site_lat_lon['Lat_dd'] = site_lat_lon['Lat_deg'] + site_lat_lon['Lat_min']/60
site_lat_lon['Lon_dd'] = site_lat_lon['Lon_deg'] + site_lat_lon['Lon_min']/60

# Merge with site table to populate what few lat, lons we know at this time. **Note that based on the prior text box, some of these are wrong.**
site = site.merge(site_lat_lon, how='left', left_on='SiteID', right_on='ID')
site.head()
## decimalLatitude, decimalLongitude

occ = occ.merge(site[['SiteID', 'Lat_dd', 'Lon_dd']], how='left', on='SiteID')
occ.rename(columns={'Lat_dd':'decimalLatitude', 'Lon_dd':'decimalLongitude'}, inplace=True)
print(occ.shape)
occ.head()<jupyter_output>(455, 8)<jupyter_text>**Note** that once merged, the only lat, lons that are missing are from Bodega Marine Life Refuge.<jupyter_code>## coordinateUncertaintyInMeters

occ['coordinateUncertaintyInMeters'] = 250
occ.head()<jupyter_output><empty_output><jupyter_text>**Is 250 m reasonable for coordinateUncertaintyInMeters?**<jupyter_code>## occurrenceID

# Merge with counts
occ = occ.merge(count[['Survey_Num', 'SpeciesID', 'COUNT']], how='left', left_on='eventID', right_on='Survey_Num')

# Create occurrenceID
occ['occurrenceID'] = occ.groupby('eventID').cumcount()+1
occ['occurrenceID'] = occ['eventID'] + '_occ' + occ['occurrenceID'].astype(str)

# Drop redundant columns
occ.drop(columns=['Survey_Num'], inplace=True)

print(occ.shape)
occ.head()
## scientificName

# Merge to get species names from codes
occ = occ.merge(species[['SpeciesID', 'SPECIES', 'Scientific']], how='left', on='SpeciesID')

# Rename columns
occ.rename(columns={'SPECIES':'vernacularName',
                   'Scientific':'scientificName'}, inplace=True)

# Drop
occ.drop(columns=['SpeciesID'], inplace=True)

print(occ.shape)
occ.head()
## Get unique names

names = occ['scientificName'].unique()
names<jupyter_output><empty_output><jupyter_text>**There are a number of changes that need to be made to scientific names before they can go through WoRMS.**1. A number of abalone species don't have the genus written out    - H. Rufescens, H. walallensis, H. kamtschatkana --> Haliotis2. A number of urchin species don't have the genus written out    - S. franciscanus and S. purpuratus --> Strongylocentrotus3. A number of scientific names are simply "general"    - These seem to correspond to vernacular names: Cancer sp., Solaster species    **In addition,** misspellings:- Othasterias koehleri should be Orthasterias koehleri<jupyter_code>## Update names and occ based on the above observations

# Update occ
occ.replace({'H. Rufescens':'Haliotis rufescens',
            'H. walallensis':'Haliotis walallensis',
            'H. kamtschatkana':'Haliotis kamtschatkana',
            'S. franciscanus':'Strongylocentrotus franciscanus',
            'S. purpuratus':'Strongylocentrotus purpuratus',
            'Othasterias koehleri':'Orthasterias koehleri'}, inplace=True)
occ.loc[occ['vernacularName'] == 'Cancer sp.', 'scientificName'] = 'Cancer sp.'
occ.loc[occ['vernacularName'] == 'Solaster species', 'scientificName'] = 'Solaster species'

# Get unique names again
names = occ['scientificName'].unique()
names
## Run through WoRMS

name_id_dict, name_name_dict, name_taxid_dict, name_class_dict = WoRMS.run_get_worms_from_scientific_name(names, verbose_flag=True)
## Add scientific name-related columns

occ['scientificNameID'] = occ['scientificName']
occ['scientificNameID'].replace(name_id_dict, inplace=True)

occ['taxonID'] = occ['scientificName']
occ['taxonID'].replace(name_taxid_dict, inplace=True)
occ.head()
## Since there are no identificationQualifiers so far, swap out scientificName too

occ['scientificName'].replace(name_name_dict, inplace=True)
occ.head()
## Add other name-related columns

occ['nameAccordingTo'] = 'WoRMS'
occ['occurrenceStatus'] = 'present'
occ['basisOfRecord'] = 'HumanObservation'

occ.head()
## Get count information in the right place

# Set organismQuantity
occ['organismQuantity'] = occ['COUNT'].copy()

# Drop COUNT column
occ.drop(columns=['COUNT'], inplace=True)

# Add organismQuantityType
occ['organismQuantityType'] = 'density in number of individuals per 60m2'
occ.head()<jupyter_output><empty_output><jupyter_text>**Note** that I should check the organismQuantityType values.**Also note** that organismQuantity is currently a string. I think this is because there were weird values in the original data set. These have been eliminated during the EMERGENT filtering step, but may return once that filter's been broadened, which I think it needs to be. **Also, 274 rows in the count table have COUNT = NaN.** I'm not sure if this is meaningful, or if it's an error. Because of the merge, these rows also appear in occ, and I'm going to leave them for now. But I'll have to make organismQuantity a float for now rather than an int.<jupyter_code>## Update presence/absence status

# Ensure organismQuantity is an int
occ['organismQuantity'] = pd.to_numeric(occ['organismQuantity'])

# Change 0 counts to absent
occ.loc[occ['organismQuantity'] == 0, 'occurrenceStatus'] = 'absent'
occ.head()
## Depth

occ = occ.merge(survey[['Survey_Num', 'Min_DEPTH', 'Max_Depth']], how='left', left_on='eventID', right_on='Survey_Num')
occ.drop(columns=['Survey_Num'], inplace=True)
occ.rename(columns={'Min_DEPTH':'minimumDepthInMeters',
           'Max_Depth':'maximumDepthInMeters'}, inplace=True)
print(occ.shape)
occ.head()<jupyter_output>(3440, 21)<jupyter_text>**Note** that when maximumDepthInMeters is not NaN, it is equal to minimumDepthInMeters. These values look like they're in feet, but **I should check this.** Also, **when was average depth recorded**, and how should I include it?<jupyter_code>## samplingProtocol, samplingEffort

occ['samplingProtocol'] = 'band transect'
occ['samplingEffort'] = '10-15 minutes per transect'
occ.head()<jupyter_output><empty_output><jupyter_text>**Check samplingProtocol and samplingEffort.**<jupyter_code>## Save

occ.to_csv('occ_20201028.csv', index=False, na_rep='NaN')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12564151302033835, 'fraction_numerical': 0.029778876005829055, 'mean_word_length': 4.629101283880171, 'pattern_counts': {'":': 0, '<': 54, '<?xml version=': 0, '>': 56, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '212075', 'license': 'MIT', 'n_tokens_mistral': 5646, 'n_tokens_neox': 5032, 'n_words': 1651, 'path': 'North coast kelp/north_coast_kelp_conversion.ipynb', 'repo_name': 'CeNCOOS/MPA_data_integration', 'size': '15783'}