<jupyter_start><jupyter_text>Filter T0 invasive species from the nameserver data<jupyter_code>%matplotlib inline
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
plt.style.use('seaborn-notebook')<jupyter_output><empty_output><jupyter_text>Making a subselection of the invasive species, as defined by the [t0-values in the aggregated checklist](https://github.com/inbo/alien-species-checklist/tree/master/data/processed), from the  verified set of matched names as defined by the [INBO nameserver](https://github.com/inbo/data-publication/tree/master/datasets/name-server/data/processed). As such, this subset defines the set of identifiers to query on the INBO database:  Get verified nameserver names Reading in the recorder verified names:<jupyter_code>recorder_names = pd.read_csv('../../name-server/data/processed/verified_recommended_nameserver_names.tsv', delimiter='\t')
recorder_names.head()<jupyter_output><empty_output><jupyter_text>Get t0 alien species checklist names Reading in the species checklist:<jupyter_code>alien_species_checklist = pd.read_csv('https://raw.githubusercontent.com/inbo/alien-species-checklist/master/data/processed/aggregated-checklist.tsv', delimiter='\t')
alien_species_checklist['datasetName'].unique()<jupyter_output><empty_output><jupyter_text>All rows for which the t0 is part of the `datasetName` should be taken into account:<jupyter_code>t0_alien_species_checklist = alien_species_checklist.loc[alien_species_checklist['datasetName'].str.contains('t0'), :]
t0_alien_species_checklist.head()
len(t0_alien_species_checklist)<jupyter_output><empty_output><jupyter_text>Derive nameserver identifiers corresponding to alien species What we want, is a list of `gbifapi_acceptedKey` from the t0 set of invasive species and check for each acceptedKey the corresponding identifiers of the nameserver.<jupyter_code>recorder_names[['nbn_recommendedTaxonVersionKey', 'gbifapi_acceptedKey']].dtypes
nameserver_identifiers_for_t0 = pd.merge(t0_alien_species_checklist[['gbifapi_acceptedScientificName', 'gbifapi_acceptedKey']], 
                                         recorder_names[['nbn_recommendedTaxonVersionKey', 'gbifapi_acceptedKey']], 
                                         how='left', 
                                         left_on="gbifapi_acceptedKey", 
                                         right_on="gbifapi_acceptedKey")
nameserver_identifiers_for_t0.head()<jupyter_output><empty_output><jupyter_text>This is what will be needed to:* setup the query to get data out of recorder (INBO dbae)* provide datasets coming from recorder again with the corresponding `acceptedKey` of GBIF<jupyter_code>nameserver_identifiers_for_t0.to_csv('../settings/nameserver_species_identifiers_for_t0.tsv', 
                                     index=False, sep='\t', na_rep='')<jupyter_output><empty_output><jupyter_text>Create a query from the template by filling in the identifiers of the species and surveys<jupyter_code># Read the INBO_identifiers
species = pd.read_csv('../settings/nameserver_species_identifiers_for_t0.tsv', delimiter='\t')
identifier_string = "','".join(nameserver_identifiers_for_t0['nbn_recommendedTaxonVersionKey'].dropna().tolist())
identifier_string = "'" + identifier_string + "'"
surveys = pd.read_csv('../settings/nameserver_survey_identifiers_for_t0s.tsv', delimiter='\t')
survey_string = ','.join(surveys['survey_id'].dropna().tolist())
with open('../settings/dwc_occurrence_template.sql') as query:
    template_query = query.read()
with open('dwc_occurrence.sql', 'w') as query:
    query.write(template_query.format(identifier_string, survey_string))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10310119695321002, 'fraction_numerical': 0.004624591947769314, 'mean_word_length': 5.54270462633452, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 9, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '212261', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 1181, 'n_tokens_neox': 1119, 'n_words': 226, 'path': 'datasets/invasive-other-occurrences/src/create_invasive_nameserver_identifiers.ipynb', 'repo_name': 'LifeWatchINBO/open-data-publication', 'size': '3676'}