<jupyter_start><jupyter_text>In order to write URL values into the ScienceBase Directory, we first need to establish the connection to ScienceBase Identifiers. Email address is our known identifier between the two systems, profile pages/inventory and ScienceBase Directory, but it is a pretty imperfect and not necessarily persistent identifier. @usgs.gov email addresses have been recycled over time. I don't know if that is still a practice, but I wouldn't be surprised. Part of the purpose for the ScienceBase Directory was to maintain some persistence over time where it would not ever delete a person record once created, providing a sustainable record of people and their scientific contributions forever.(Note: This whole exercise points to the importance of persistent identifiers throughout these kinds of information infrastructures, the need for systems to be aware of different identifier schemes, and ideally for those systems to be responsive to (e.g., able to be keyed on) multiple valid persistent identifiers for the same identities where possible.)This notebook runs a workflow for establishing the connection from the email address identifiers in the staff profile inventory and ScienceBase identities. It queries the ScienceBase Directory API for an email address, retrieves the full person document when found, checks to see if a current URL value is on board, and sets things up for update by stashing the person document that will be updated and the API end point/identifier for that identity. We have to retrieve the person document separate from the search result (meaning two API calls for every email address found) because the search result doesn't give us the full data structure we need to send back to the ScienceBase API in the update operation.In the process of working through these data, I found a surprising number of email addresses currently in the USGS profile system that cannot be found by email address in ScienceBase. It's not surprising that they are in the USGS profile system as, presumably, those should also be permanent records that remain on our system, providing a record of what a staff member "looked like" while they were with the USGS even after they've left. It is surprising that they don't seem to have a record in the ScienceBase Directory, as that was set up to retain person records for all time and has been operating since about 2010. In the workflow, I split out those documents where I will be able to make an update with profile page URLs and those that I cannot find in ScienceBase. I also split off the handful of records from the USGS profiles system where there is already a URL in the ScienceBase item, assuming that someone might have entered a preferred URL into their ScienceBase record.<jupyter_code>import requests
import pickle
import pylinkedcmd
import numpy as np
from joblib import Parallel, delayed
import tqdm
from sqlite_utils import Database
import json
import ast
sb = pylinkedcmd.pylinkedcmd.Sciencebase()

db = Database("usgs_profiles.db")
def get_source_data(source_type="db", batch_records=False, batch_size=500):
    if source_type == "db":
        usgs_staff = list()
        for r in [i for i in db["missing_sb_person"].rows]:
            new_record = dict()
            for k,v in r.items():
                try:
                    new_record[k] = json.loads(v)
                except:
                    new_record[k] = v
            usgs_staff.append(new_record)
    else:
        infile = open("usgs_staff_profiles.pkl", "rb")
        usgs_staff = pickle.load(infile)
        infile.close()
        
    if batch_records:
        num_batches = round(len(usgs_staff)/batch_size)
        
        if num_batches > 1:
            return np.array_split(usgs_staff, num_batches)
    
    return [usgs_staff]

usgs_staff = get_source_data()<jupyter_output><empty_output><jupyter_text>There are all kinds of issues trying to work with the ScienceBase REST APIs related to both the immediate ScienceBase infrastructure and to the network in between. Running many operations serially has to be done with a delay due to a policy that kicks processes off after too many sequential requests. Running operations in parallel can also work but with the same issue. Either option still results in HTTP connection pool problems after a while. Because of this, I chose to run this in parallel but with batches of about 500 records, which seem to work most of the time. (But still a major pain in the ass!)<jupyter_code>existing_url = list()

def sb_check_accumulator(person):
    sb_person = sb.lookup_sb_person_by_email(person["email"][0])

    if sb_person is None:
        no_sb_person.append(person)
    else:
        if "url" in sb_person.keys():
            existing_url.append((person["profile"], sb_person["url"]))
        else:
            sb_person["url"] = person["profile"][0]
            update_records.append(sb_person)
update_records = list()
no_sb_person = list()

Parallel(n_jobs=10, prefer="threads")(
    delayed(sb_check_accumulator)
    (
        i
    ) for i in tqdm.tqdm(usgs_staff[0])
)<jupyter_output><empty_output><jupyter_text>Because of all the problems with finishing out the batches and the fact that I am having to split up this whole thing into discrete tasks, I chose to stash the person documents that I'm going to update and the profile records where I could not establish a connection to ScienceBase into a temporary database for later processing. I output a look at the difference in URL between the profile system and ScienceBase for informational purposes only as I decided not to do anything with these at the moment.In looking through those, I do see a couple of deviations where SB Directory items have a different URL, but a number of those are an older reference to where the USGS profile pages used to live. Those should redirect.<jupyter_code>db["missing_sb_person"].upsert_all(no_sb_person, pk="identifier")

update_record_cache = [
    {
        "person_id": i["link"]["href"],
        "person_doc": json.dumps(i)
    } for i in update_records
]

db["update_set"].upsert_all(update_record_cache, pk="person_id")

display(existing_url)<jupyter_output><empty_output><jupyter_text>There are a surprising number (256) of USGS profile page records where I cannot find a valid match to the ScienceBase Directory. In looking through these, there are some interesting issues:* The profile system contains a number of "bogus" accounts that appear to be in the system for testing purposes (e.g., https://usgs.gov/staff-profiles/simple-sample). This seems sloppy to me as I pulled these directly from the public USGS web where, presumably, we should only be putting our best foot forward in terms of real information.* There are a handful of non usgs.gov emails, which is not necessarily surprising. Quite a number of USGS scientists hold faculty positions with universities and identify more with their university identity even if they also have a USGS address. This is only a problem when we don't have a facility for linking between those identities, which we do kind of lack at the moment. However, I was not able to find any of these in the ScienceBase Directory by other properties, anyway.* For the handful of records with email addresses that came up missing from ScienceBase, I checked our internal directory access point and was still unable to find these records. Those that I checked, did not have a fleshed out public profile page. I suspect that these were auto-created with some internal process at some point when those people were on board as staff. It's likely that the records could and probably should be expunged from the USGS profile system for cleanup purposes at least.* There are some other interesting dynamics like [this one](https://www.usgs.gov/staff-profiles/texas-information) where the profile is based on a "non-person" email identity (<EMAIL>) but is actually for a person currently in a particular role. Interestingly enough, the dynamic listing of products on the profile page links to 6 records that are tied to that email identity. This creates an interesting corner case that we may or may not want to deal with. With everything checked and matched, we can run our updates by first grabbing everything back out of the temporary database cache, loading the JSON content back to Python for sciencebasepy, and then sending our updates. I put a function in the pylinkedcmd class for ScienceBase to facilitate this. It prompts for credentials, and permissions to edit anything in the ScienceBase Directory are required. I have those permissions, but most people will not be able to commit actual updates.I show the total number of records to be updated and a sample for reference. I kind of doubt this will actually complete all the way through without running into a timeout somewhere, but I thought I'd give it a shot. The update is carried out as a sequential process of requests that leverages a utility session object from sciencebaseby to handle authenticated access as there is nothing built into the Python API specific to Directory as yet.<jupyter_code>update_person_docs = [
    json.loads(i["person_doc"]) for i in
    db["update_set"].rows
]
len(update_person_docs)
update_person_docs[0]
sb.update_person(update_records)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0464891561090831, 'fraction_numerical': 0.002254670388662229, 'mean_word_length': 4.310718358038769, 'pattern_counts': {'":': 3, '<': 18, '<?xml version=': 0, '>': 19, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '139408', 'license': 'Unlicense', 'n_tokens_mistral': 2283, 'n_tokens_neox': 2211, 'n_words': 1290, 'path': 'examples/Staff profile URLs to ScienceBase.ipynb', 'repo_name': 'brandonserna/pylinkedcmd', 'size': '9333'}