<jupyter_start><jupyter_text>The USGS Publications Warehouse provides a metadata source for all USGS Series Reports and journal articles authored and edited by USGS staff. It provides an additional source of information for CreativeWork items already in our graph via DOIs we've encountered elsewhere and pulled into that cache. For existing matches, we will be able to potentially pick up abstracts and email identifiers for authors/editors (unique to this internal catalog source). We will also pick up an important internal identifier ("IPDS number") that has to do with the review and publishing workflow and is referenced in project records that we are pulling in from another internal USGS source. This will help us in linking projects to publication products.For the purpose of our USGS graph, I made some decisions in the Pubs Warehouse data building code to filter the Pubs Warehouse records to only those that we can link to authors or editors via email or orcid identifiers to people we already have in our graph via the master source in the ScienceBase Directory. This limits the set of publications to 20K or so, but gives us a reasonable set of information to work against for the types of queries we are exploring in examining current capacity for various kinds of research.Still to do: The Cost Center information in Pubs Warehouse records deserves further investigation, though we may get to the relationship between USGS organizational units and publications via another route as we start to bring in project information. We also get an indirect relationship from orgs to pubs via author affiliations, but that is transient and time steps may be off in the data.<jupyter_code>import isaid_helpers
import pandas as pd
pd.read_csv(isaid_helpers.f_graphable_pw).head()
%%time
with isaid_helpers.graph_driver.session(database=isaid_helpers.graphdb) as session:
    session.run("""
        LOAD CSV WITH HEADERS FROM '%(source_path)s/%(source_file)s' AS row
        WITH row WHERE NOT row.doi IS NULL
            MERGE (w:CreativeWork {doi: row.doi})
            ON MATCH
                SET w.description = row.description,
                w.id_pw = row.id_pw,
                w.id_ipds = row.id_ipds,
            ON CREATE
                SET w.name = row.name,
                w.description = row.description,
                w.year_published = row.year_published,
                w.url = row.url,
                w.id_pw = row.id_pw,
                w.id_ipds = row.id_ipds,
                w.source = row.source

            WITH w, row
                WHERE NOT row.author_emails IS NULL
                    UNWIND split(row.author_emails, ',') AS author_email
                    MATCH (p:Person {email: author_email})
                    MERGE (p)-[a:AUTHOR_OF]->(w)
                        SET a.date_qualifier = row.year_published,
                        a.reference = row.url

            WITH w, row
                WHERE NOT row.author_orcids IS NULL
                    UNWIND split(row.author_orcids, ',') AS author_orcid
                    MATCH (p:Person {orcid: author_orcid})
                    MERGE (p)-[a:AUTHOR_OF]->(w)
                        SET a.date_qualifier = row.year_published,
                        a.reference = row.url

            WITH w, row
                WHERE NOT row.editor_emails IS NULL
                    UNWIND split(row.editor_emails, ',') AS editor_email
                    MATCH (p:Person {email: editor_email})
                    MERGE (p)-[a:EDITOR_OF]->(w)
                        SET a.date_qualifier = row.year_published,
                        a.reference = row.url

            WITH w, row
                WHERE NOT row.editor_orcids IS NULL
                    UNWIND split(row.editor_orcids, ',') AS editor_orcid
                    MATCH (p:Person {orcid: editor_orcid})
                    MERGE (p)-[a:EDITOR_OF]->(w)
                        SET a.date_qualifier = row.year_published,
                        a.reference = row.url
    """ % {
        "source_path": isaid_helpers.local_cache_path,
        "source_file": isaid_helpers.f_graphable_pw
    })
%%time
with isaid_helpers.graph_driver.session(database=isaid_helpers.graphdb) as session:
    session.run("""
        LOAD CSV WITH HEADERS FROM '%(source_path)s/%(source_file)s' AS row
        WITH row WHERE row.doi IS NULL
            MERGE (w:CreativeWork {name: row.name})
            ON MATCH
                SET w.description = row.description,
                w.id_pw = row.id_pw,
                w.id_ipds = row.id_ipds
            ON CREATE
                SET w.name = row.name,
                w.description = row.description,
                w.year_published = row.year_published,
                w.url = row.url,
                w.id_pw = row.id_pw,
                w.id_ipds = row.id_ipds,
                w.source = row.source

            WITH w, row
                WHERE NOT row.author_emails IS NULL
                    UNWIND split(row.author_emails, ',') AS author_email
                    MATCH (p:Person {email: author_email})
                    MERGE (p)-[a:AUTHOR_OF]->(w)
                        SET a.date_qualifier = row.year_published,
                        a.reference = row.url

            WITH w, row
                WHERE NOT row.author_orcids IS NULL
                    UNWIND split(row.author_orcids, ',') AS author_orcid
                    MATCH (p:Person {orcid: author_orcid})
                    MERGE (p)-[a:AUTHOR_OF]->(w)
                        SET a.date_qualifier = row.year_published,
                        a.reference = row.url

            WITH w, row
                WHERE NOT row.editor_emails IS NULL
                    UNWIND split(row.editor_emails, ',') AS editor_email
                    MATCH (p:Person {email: editor_email})
                    MERGE (p)-[a:EDITOR_OF]->(w)
                        SET a.date_qualifier = row.year_published,
                        a.reference = row.url

            WITH w, row
                WHERE NOT row.editor_orcids IS NULL
                    UNWIND split(row.editor_orcids, ',') AS editor_orcid
                    MATCH (p:Person {orcid: editor_orcid})
                    MERGE (p)-[a:EDITOR_OF]->(w)
                        SET a.date_qualifier = row.year_published,
                        a.reference = row.url
    """ % {
        "source_path": isaid_helpers.local_cache_path,
        "source_file": isaid_helpers.f_graphable_pw
    })<jupyter_output>CPU times: user 5.45 ms, sys: 3.17 ms, total: 8.62 ms
Wall time: 4min 22s
{'fraction_non_alphanumeric': 0.072025528035253, 'fraction_numerical': 0.002127336271083422, 'mean_word_length': 1.7996597192683965, 'pattern_counts': {'":': 4, '<': 4, '<?xml version=': 0, '>': 12, 'https://': 0, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '316179', 'license': 'Unlicense', 'n_tokens_mistral': 1862, 'n_tokens_neox': 1718, 'n_words': 586, 'path': 'isaid/iSAID - Build Graph - Pubs Warehouse.ipynb', 'repo_name': 'skybristol/pylinkedcmd', 'size': '6581'}