find_isu-affiliated_orcids.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "26201707",
   "metadata": {},
   "source": [
    "Import needed libraries and functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e93e46dd-3c47-4c54-a21f-3719927c49bd",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import json\n",
    "from urllib.parse import quote_plus\n",
    "\n",
    "from openpyxl import Workbook\n",
    "import requests"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8a93813",
   "metadata": {},
   "source": [
    "Set up basics needed for searching the ORCID API and retrieve ORCID records. The headers include the access token we need to access the API. These headers will be used by a couple different functions that use the API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "bcbf0570-69a5-42bc-9ad4-e2b09395127c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "headers = {\n",
    "        \"Accept\": \"application/orcid+json\",\n",
    "        \"Authorization type and Access token\": \"Bearer d4c15699-6230-4f17-98f3-c0ac28566ed6\"\n",
    "    }"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9aef26bd",
   "metadata": {},
   "source": [
    "Set up the funtion to search ORCID's API. We'll grab people who's current or past affiliation matches Iowa State University or Iowa State, or who has an @iastate.edu or @mail.iastate.edu email address. If there are more than 1,000 results (there should be,) `search_orcid` will make multiple requests, retrieving the results in blocks of 1,000. Once it has retreived all matching results, `search_orcid` will return a list of all matches. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c0a2ad5c-4843-4d3c-b394-cf3913e2c9d9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def search_orcid(headers):\n",
    "    url = \"https://pub.orcid.org/v3.0/search/?q=\"\n",
    "    instution_name = '(\"Iowa State\" OR \"Iowa State University\")'\n",
    "    query = quote_plus(f\"email:(*@iastate.edu OR *@mail.iastate.edu) OR current-institution-affiliation-name:{instution_name} OR past-institution-affiliation-name:{instution_name}\")\n",
    "    start = 0\n",
    "    step = 1000\n",
    "    search = url + query\n",
    "    \n",
    "    r = requests.get(search, headers=headers)\n",
    "    \n",
    "    results = r.json()[\"result\"]\n",
    "    total_found = int(r.json()[\"num-found\"])\n",
    "\n",
    "    if len(results) == total_found:\n",
    "        return results\n",
    "\n",
    "    while len(results) < total_found:\n",
    "        start += step\n",
    "        r = requests.get(search + f\"&start={start}&rows={step}\", headers=headers)\n",
    "        results.extend(r.json()[\"result\"])\n",
    "\n",
    "    return results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0a3026e",
   "metadata": {},
   "source": [
    "Now we search for matching persons. This search should take only a few seconds to complete."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8c35cbcf-e2da-4d7b-879c-74b067da4d73",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "isu_affiliated_orcids = search_orcid(headers)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e12e8e87",
   "metadata": {},
   "source": [
    "Each matching result contains a dictionary with the ORCID as a \"path,\" that is the raw ORCID and as a URI. We want the \"path\" version for retrieving the full record. The list comprehension below creates a list of the ORCID paths."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c4941f93-93cb-4e53-8a2f-93b85695c251",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "orcids = [o[\"orcid-identifier\"][\"path\"] for o in isu_affiliated_orcids]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c581324",
   "metadata": {},
   "source": [
    "Define the function to retrieve full records for each ISU-affliated person."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d8644297-8098-45eb-b16a-0fc0eedb9bf0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def get_full_records(orcids, headers):\n",
    "    url = \"https://pub.orcid.org/v3.0/\"\n",
    "    records = []\n",
    "    for o in orcids:\n",
    "        r = requests.get(f\"{url}{o}/record\", headers=headers)\n",
    "        try:\n",
    "            records.append(r.json())\n",
    "        # Sometimes ORCID will encounter an error and send an HTML page\n",
    "        # rather than doing the logical thing of sending the error\n",
    "        # message as JSON. The code below is a work-around for that flaw.\n",
    "        except json.JSONDecodeError:\n",
    "            while True:\n",
    "                try:\n",
    "                    r = requests.get(f\"{url}{o}/record\", headers=headers)\n",
    "                    records.append(r.json())\n",
    "                    break\n",
    "                except json.JSONDecodeError:\n",
    "                    print(f\"Retrying {o}\")\n",
    "                    continue\n",
    "\n",
    "    return records"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bccaaddf",
   "metadata": {},
   "source": [
    "Now, we run it. This will take a more than an hour, since we're making a seperate request for several thousand ORCIDs. This would be a good time to take a break, then work on something else for a little bit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ce8e3616-741c-4ce7-b407-14e553daa8ed",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "records = get_full_records(orcids, headers)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d62684b",
   "metadata": {},
   "source": [
    "It may be wise to save a copy of the ORCID records. In case we need to come back and reprocess them at some point it can save the wait of requesting them all again. `save_orcid_records` will save the JSON retrieved from the API. Replace the file_name argument passed to `save_orcid_records` to the name you want to use if you want to save your records. This may take a few seconds, since there are quite a few records.\n",
    "\n",
    "**Don't forget to change the name of the file if you don't want to overwrite earlier copies.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3146b25b-60ae-40c0-b040-92e4927c8e09",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def save_orcid_records(records, file_name, indent_level=2):\n",
    "    with open(file_name, \"w\", encoding=\"utf8\") as fh:\n",
    "        json.dump(records, fh, indent=indent_level)\n",
    "\n",
    "save_orcid_records(records, \"full_orcid_records.json\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f4585d7",
   "metadata": {},
   "source": [
    "Define functions to extract the desired fields from the ORCID records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "3a96f9b2-0ecb-4dfe-8f9e-eab33cb80ff8",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def get_orcid_id_as_link(record):\n",
    "    orcid_url = record.get(\"orcid-identifier\").get(\"uri\")\n",
    "    orcid = record.get(\"orcid-identifier\").get(\"path\")\n",
    "\n",
    "    return f'=HYPERLINK(\"{orcid_url}\", \"{orcid}\")'\n",
    "\n",
    "\n",
    "def get_first_name(record):\n",
    "    \"\"\"Helper function for get_affiliation.\"\"\"\n",
    "\n",
    "    # If a person's name isn't public, ORCID doesn't return an\n",
    "    # empty dictionary that would keep it's data structure consistent.\n",
    "    # It will just truncate the name data structure with `null`. To\n",
    "    # keep this inconsitency from crashing this function, we need\n",
    "    # to wrap this in try/except clauses.\n",
    "    try:\n",
    "        value = record.get(\"person\").get(\"name\").get(\"given-names\").get(\"value\")\n",
    "    except AttributeError:\n",
    "        value = \"\"\n",
    "\n",
    "    return value\n",
    "\n",
    "\n",
    "def get_last_name(record):\n",
    "    \"\"\"Helper function for get_affiliation.\"\"\"\n",
    "    # If a person's name isn't public, ORCID doesn't return an\n",
    "    # empty dictionary that would keep it's data structure consistent.\n",
    "    # It will just truncate the name data structure with `null`. To\n",
    "    # keep this inconsitency from crashing this function, we need\n",
    "    # to wrap this in try/except clauses.\n",
    "    try:\n",
    "        value = record.get(\"person\").get(\"name\").get(\"family-name\").get(\"value\")\n",
    "    except AttributeError:\n",
    "        value = \"\"\n",
    "\n",
    "    return value\n",
    "\n",
    "\n",
    "def get_email(record):\n",
    "    \"\"\"Helper function for get_affiliation.\"\"\"\n",
    "    # Not entirely clear why it was necessary to nest emails\n",
    "    # so deeply.\n",
    "    email_list = record.get(\"person\").get(\"emails\").get(\"email\")\n",
    "    emails = []\n",
    "    for email in email_list:\n",
    "        if \"iastate.edu\" in email.get(\"email\"):\n",
    "            emails.append(email[\"email\"])\n",
    "\n",
    "    return \"; \".join(emails)\n",
    "\n",
    "\n",
    "def convert_date_string(date_string):\n",
    "    \"\"\"Helper function for get_affiliation_end_date.\"\"\"\n",
    "    return int(date_string) if date_string is not None else 0\n",
    "\n",
    "\n",
    "def get_most_recent(summaries, kind):\n",
    "    \"\"\"Helper function for get_affiliation.\"\"\"\n",
    "    for summary in summaries:\n",
    "        institution = summary.get(\"summaries\")[0].get(f\"{kind}-summary\").get(\"organization\").get(\"name\")\n",
    "        if institution == \"Iowa State University\":\n",
    "            return summary\n",
    "\n",
    "    return None\n",
    "\n",
    "\n",
    "def get_affiliation_end_date(affiliation):\n",
    "    \"\"\"Helper function for get_affiliation_details.\"\"\"\n",
    "    end_date = affiliation.get(\"end-date\")\n",
    "    if end_date is None:\n",
    "        return 0, 0, 0\n",
    "\n",
    "    # Everything is wrapped in try/except blocks to handle ORCID's\n",
    "    # inconsistent data structure that replaces `{\"value\": \"1998\"}`\n",
    "    # with `null` when no value has been provided. In a coherent\n",
    "    # structure, the `null` would be at the value-level, not at\n",
    "    # the year-, month-, day-level, or there'd be no unnecessary\n",
    "    # value dictionary to begin with.\n",
    "    try:\n",
    "        year = convert_date_string(end_date.get(\"year\").get(\"value\"))\n",
    "    except AttributeError:\n",
    "        year = 0\n",
    "\n",
    "    try:\n",
    "        month = convert_date_string(end_date.get(\"month\").get(\"value\"))\n",
    "    except AttributeError:\n",
    "        month = 0\n",
    "\n",
    "    try:\n",
    "        day = convert_date_string(end_date.get(\"day\").get(\"value\"))\n",
    "    except AttributeError:\n",
    "        day = 0\n",
    "\n",
    "    return year, month, day\n",
    "\n",
    "\n",
    "def get_affiliation_details(affiliation):\n",
    "    \"\"\"Helper function for get_affiliation.\"\"\"\n",
    "    # If the end date is None, then they're current or didn't update\n",
    "    # their profile after leaving ISU. If an end date exists, they\n",
    "    # are not current.\n",
    "    current = \"yes\" if not(bool(affiliation.get(\"end-date\"))) else \"no\"\n",
    "    department = affiliation.get(\"department-name\") if affiliation.get(\"department-name\") is not None else \"\"\n",
    "    title = affiliation.get(\"role-title\") if affiliation.get(\"role-title\") is not None else \"\"\n",
    "    end_date = get_affiliation_end_date(affiliation)\n",
    "\n",
    "    return current, department, title, end_date\n",
    "\n",
    "\n",
    "def get_affiliation(record):\n",
    "    \"\"\"Helper function for get_fields. Finds most recent\n",
    "    ISU affiliation in a person's educational and employment\n",
    "    affiliations. Returns department, title, and currentness.\"\"\"\n",
    "    try:\n",
    "        education_history = record.get(\n",
    "            \"activities-summary\"\n",
    "        ).get(\n",
    "            \"educations\"\n",
    "        ).get(\n",
    "            \"affiliation-group\"\n",
    "        )\n",
    "\n",
    "        most_recent_isu_education = get_most_recent(education_history, \"education\")\n",
    "    except IndexError:\n",
    "        most_recent_isu_education = None\n",
    "\n",
    "    try:\n",
    "        employment_history = record.get(\n",
    "            \"activities-summary\"\n",
    "        ).get(\n",
    "            \"employments\"\n",
    "        ).get(\n",
    "            \"affiliation-group\"\n",
    "        )\n",
    "\n",
    "        most_recent_isu_employment = get_most_recent(employment_history, \"employment\")\n",
    "    except IndexError:\n",
    "        most_recent_isu_employment = None\n",
    "\n",
    "\n",
    "    if most_recent_isu_education is not None:\n",
    "        ed_current, ed_department, ed_title, ed_end = get_affiliation_details(most_recent_isu_education[\"summaries\"][0][\"education-summary\"])\n",
    "\n",
    "        # If they are currently a student at ISU, assume that is their primary role\n",
    "        # and that any possible employment at ISU is a student job\n",
    "        if ed_current:\n",
    "            return ed_current, ed_department, ed_title\n",
    "\n",
    "    if most_recent_isu_employment is not None:\n",
    "        em_current, em_department, em_title, em_end = get_affiliation_details(most_recent_isu_employment[\"summaries\"][0][\"employment-summary\"])\n",
    "\n",
    "        # If they're currently employed, but not a student, that makes\n",
    "        # identifying their most recent affiliation with ISU pretty easy\n",
    "        # to identify\n",
    "        if em_current:\n",
    "            return em_current, em_department, em_title\n",
    "\n",
    "\n",
    "    if None not in (most_recent_isu_education, most_recent_isu_employment):\n",
    "        # If they were a student at ISU more recently than they\n",
    "        # were employed here, return their education afiliation\n",
    "        if ed_end > em_end:\n",
    "            return ed_current, ed_department, ed_title\n",
    "\n",
    "        return em_current, em_department, em_title\n",
    "\n",
    "    if most_recent_isu_education is None and most_recent_isu_employment is not None:\n",
    "        return em_current, em_department, em_title\n",
    "\n",
    "    if most_recent_isu_education is None and most_recent_isu_employment is not None:\n",
    "        return ed_current, ed_department, ed_title\n",
    "\n",
    "    return \"\", \"\", \"\"\n",
    "\n",
    "\n",
    "def get_fields(record):\n",
    "    \"\"\"Main data-extraction function\"\"\"\n",
    "    orcid = get_orcid_id_as_link(record)\n",
    "    first_name = get_first_name(record)\n",
    "    last_name = get_last_name(record)\n",
    "    email = get_email(record)\n",
    "    current, department, title = get_affiliation(record)\n",
    "\n",
    "    return orcid, first_name, last_name, email, title, current, department\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03d66b89",
   "metadata": {},
   "source": [
    "Gather up the data we need to create the Iowa State University-affiliated ORCIDs spreadsheet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f22514a8",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "rows = []\n",
    "for record in records:\n",
    "    rows.append(get_fields(record))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4db5643c",
   "metadata": {},
   "source": [
    "Add a couple functions that will help sort the rows alphabetically."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "fb061647",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def get_last_name_for_sort(row):\n",
    "    return row[2]\n",
    "\n",
    "def get_first_name_for_sort(row):\n",
    "    return row[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "974fb420",
   "metadata": {},
   "source": [
    "Sort the rows of data so they are in alphabetical order by last name and subordered by first name, so \"Aaron Smith\" appears before \"Barbara Smith.\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "328d8fa2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "rows = sorted(sorted(rows, key=get_first_name_for_sort), key=get_last_name_for_sort)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb0a92e3",
   "metadata": {},
   "source": [
    "Set up functions to save the ORCID report. You can save the report as an Excel file or a TSV."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "29115201",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def save_as_excel(rows, outfile):\n",
    "    headers = [\"ORCID iD\", \"First name\", \"Last name\", \"Email\", \"Affiliation\", \"still at ISU\", \"department\"]\n",
    "    wb = Workbook()\n",
    "    ws = wb.active\n",
    "\n",
    "    ws.append(headers)\n",
    "    for i, row in enumerate(rows, 2):\n",
    "        ws.append(row)\n",
    "        ws.cell(row=i, column=1).style=\"Hyperlink\"\n",
    "\n",
    "    wb.save(outfile)\n",
    "\n",
    "\n",
    "def save_as_tsv(rows, outfile):\n",
    "    headers = [\"ORCID iD\", \"First name\", \"Last name\", \"Email\", \"Affiliation\", \"still at ISU\", \"department\"]\n",
    "    with open(outfile, \"w\", encoding=\"utf8\") as fh:\n",
    "        fh.write(\"\\t\".join(headers))\n",
    "        fh.write(\"\\n\")\n",
    "        for row in rows:\n",
    "            fh.write(\"\\t\".join(row))\n",
    "            fh.write(\"\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac37c63d",
   "metadata": {},
   "source": [
    "Save the report. Remember to rename the file if you want."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7ae0adaa",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "save_as_excel(rows, \"orcid_report.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e08091f",
   "metadata": {},
   "source": [
    "Congratulations, you're done!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "603d87f9",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.2"
  },
  "vscode": {
   "interpreter": {
    "hash": "b022ac6ba4b982fd9df6d87ba34e07ef2d940f3ab24872c493ca2101454dd7b5"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}