async_citation_report.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "HDlTvBLHJ0Vp"
      },
      "outputs": [],
      "source": [
        "%pip install python-dotenv asyncio_throttle xmltodict aiohttp"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "uiiPgFrOBFX8"
      },
      "outputs": [],
      "source": [
        "import aiohttp\n",
        "import asyncio\n",
        "from asyncio_throttle import Throttler\n",
        "import pandas as pd\n",
        "import os\n",
        "import urllib.parse\n",
        "import xmltodict\n",
        "import requests"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "T9H5XVJeCQC0"
      },
      "outputs": [],
      "source": [
        "%load_ext dotenv\n",
        "%dotenv keys.env"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "xVhcYd22B3yk"
      },
      "outputs": [],
      "source": [
        "api_key = os.getenv(\"bibKey\")\n",
        "headers = {\n",
        "    \"Authorization\": f\"apikey {api_key}\",\n",
        "    \"Accept\": \"application/json\",\n",
        "    'limit' : '100',\n",
        "}\n",
        "base = \"https://api-na.hosted.exlibrisgroup.com/almaws/v1/bibs/\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "PsFdGJwn149F"
      },
      "outputs": [],
      "source": [
        "course_key = os.getenv(\"courseKey\")\n",
        "\n",
        "course_headers = {\n",
        "    \"Authorization\": f\"apikey {course_key}\",\n",
        "    \"Accept\": \"application/json\",\n",
        "}"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "N65-JZioKELA"
      },
      "outputs": [],
      "source": [
        "def build_db():\n",
        "  analytics_key= os.getenv(\"analyticsKey\")\n",
        "  path_encoded = \"%2Fshared%2FMiraCosta%20College%2001CACCL_MIRACOSTA%2Fcitation%20ids\"\n",
        "  path = urllib.parse.unquote(path_encoded)\n",
        "  analytics_url = 'https://api-na.hosted.exlibrisgroup.com/almaws/v1/analytics/reports'\n",
        "\n",
        "  analytics_headers = {\n",
        "    \"path\" : path,\n",
        "    \"apiKey\": analytics_key,\n",
        "    'limit' : 1000\n",
        "  }\n",
        "  y=0\n",
        "  flag = 'false'\n",
        "\n",
        "  try:\n",
        "    while y < 1:\n",
        "  # Make the initial request\n",
        "      analytics_response = requests.get(analytics_url, params=analytics_headers)\n",
        "      data_dict = xmltodict.parse(analytics_response.content)\n",
        "      db_list.append([row for row in data_dict['report']['QueryResult']['ResultXml']['rowset']['Row']])\n",
        "      \n",
        "  # Exchange the report path for redemption token. Pass on subsequent runs.\n",
        "      try:\n",
        "        del analytics_headers[\"path\"]\n",
        "        analytics_headers['token'] = data_dict['report']['QueryResult']['ResumptionToken']   \n",
        "      except KeyError:\n",
        "        pass\n",
        "\n",
        "  # Build dictionary of column names to map onto dataframe.\n",
        "      if flag == 'false':\n",
        "        for i in data_dict['report']['QueryResult']['ResultXml']['rowset']['xsd:schema']['xsd:complexType']['xsd:sequence']['xsd:element']:\n",
        "          column_number = i['@name'] \n",
        "          column_name = i['@saw-sql:columnHeading']\n",
        "          columns[column_number] = column_name\n",
        "        flag = 'true'\n",
        "\n",
        "  # Break out of the loop if the 'IsFinished' element returns 'true'\n",
        "\n",
        "      if data_dict['report']['QueryResult']['IsFinished'] == 'true':\n",
        "        y+=1\n",
        "        break\n",
        "\n",
        "  except KeyError as error:\n",
        "    print(f'Key Error: {error}')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "LTonwsBSKGqi"
      },
      "outputs": [],
      "source": [
        "def shape_db(db_list):\n",
        "\n",
        "# Separate out rows containing data (type: dict) \n",
        "# from empty rows (type: str)\n",
        "\n",
        "  cleaned_db = []\n",
        "\n",
        "  for i in db_list:\n",
        "    for j in i:  \n",
        "      if type(j) == dict:\n",
        "        cleaned_db.append(j)\n",
        "        continue\n",
        "      else:\n",
        "        pass\n",
        "\n",
        "  return cleaned_db"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "d4LtswNTrVhF"
      },
      "outputs": [],
      "source": [
        "# Function builds out the dictionary that becomes a row in the final dataframe. Contains physical item information.\n",
        "def get_phys_item(\n",
        "    i,\n",
        "):  \n",
        "    b = {\n",
        "        \"title\": i[\"bib_data\"][\"title\"],\n",
        "        \"Barcode\": i[\"item_data\"][\"barcode\"],\n",
        "        \"call number\": i[\"holding_data\"][\"call_number\"],\n",
        "        \"permanent location\": i[\"item_data\"][\"location\"][\"desc\"],\n",
        "        \"edition\": i[\"bib_data\"][\"complete_edition\"],\n",
        "    }\n",
        "    \n",
        "    try:\n",
        "        b['isbn'] = i[\"bib_data\"][\"isbn\"]\n",
        "    except KeyError:\n",
        "        b['isbn'] = 'none'\n",
        "    try:\n",
        "        b['author'] = i[\"bib_data\"][\"author\"]\n",
        "    except KeyError:\n",
        "        b['author'] = 'none'\n",
        "    try:\n",
        "        b[\"due back date\"] = i[\"holding_data\"][\"due_back_date\"]\n",
        "    except KeyError:\n",
        "        b[\"due back date\"] = \"none\"\n",
        "    try:\n",
        "        b[\"in temp location\"] = i[\"holding_data\"][\"in_temp_location\"]\n",
        "    except KeyError:\n",
        "        b[\"in temp location\"] = \"none\"\n",
        "    try:\n",
        "        b[\"temp location\"] = i[\"holding_data\"][\"temp_location\"][\"value\"]\n",
        "    except KeyError:\n",
        "        b[\"temp location\"] = \"none\"\n",
        "    try:\n",
        "        b[\"temp policy\"] = i[\"holding_data\"][\"temp_policy\"][\"value\"]\n",
        "    except KeyError:\n",
        "        b[\"temp policy\"] = \"none\"\n",
        "    try:\n",
        "        b[\"temp call number\"] = i[\"holding_data\"][\"temp_call_number\"]\n",
        "    except KeyError:\n",
        "        b[\"temp call number\"] = \"none\"\n",
        "\n",
        "    return b"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "cfsTWEwKErwf"
      },
      "outputs": [],
      "source": [
        "# Creates the URL endpoints needed to make citation requests.\n",
        "def build_url(df):  \n",
        "\n",
        "    a = []\n",
        "\n",
        "    baseURL = \"https://api-na.hosted.exlibrisgroup.com/almaws/v1/courses/\"\n",
        "\n",
        "    for index, row in df.iterrows():\n",
        "        course_id, citation_id, reading_list_id = (\n",
        "            row[\"Course ID\"],\n",
        "            row[\"Citation Id\"],\n",
        "            row[\"Reading List Id\"],\n",
        "        )\n",
        "\n",
        "        url = (\n",
        "            baseURL\n",
        "            + course_id\n",
        "            + \"/reading-lists/\"\n",
        "            + reading_list_id\n",
        "            + \"/citations/\"\n",
        "            + citation_id\n",
        "        )\n",
        "\n",
        "        a.append(url)\n",
        "\n",
        "    return a"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "AkRYnv-RTqNx"
      },
      "outputs": [],
      "source": [
        "# Async handler for making bibs (phys item) api calls. Works together with get_bibs()\n",
        "async def get_bibs_list(\n",
        "    df, headers\n",
        "):  \n",
        "    throttler = Throttler(rate_limit=25)\n",
        "    async with aiohttp.ClientSession() as client:\n",
        "\n",
        "        awaitables = [\n",
        "            get_bibs(client=client, throttler=throttler, headers=headers, mms=i)\n",
        "            for i in df[\"MMS Id\"]\n",
        "        ]\n",
        "\n",
        "        itemList = await asyncio.gather(*awaitables)\n",
        "\n",
        "    return itemList"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "uzfQoKjKVekN"
      },
      "outputs": [],
      "source": [
        "# Works with get_bibs_list() to batch collection item information.\n",
        "async def get_bibs(\n",
        "    client, throttler, headers, mms\n",
        "):  \n",
        "    items = []\n",
        "    async with throttler:\n",
        "        try:\n",
        "            resp = None\n",
        "            url = base + mms + \"/holdings/ALL/items\"\n",
        "            async with client.get(url, headers=headers) as session:\n",
        "                if session.status != 200:\n",
        "                    resp = await session.text()\n",
        "                    session.raise_for_status()\n",
        "                resp = await session.json()\n",
        "                if (\n",
        "                    resp[\"total_record_count\"] == 0\n",
        "                ):  # Checks to see how many items in the record. If none, adds the MMS ID of the record to the list so we can make citation calls on those MMS Ids\n",
        "                    items.append(mms)\n",
        "                else:\n",
        "                    items.append(resp)\n",
        "        except Exception as e:\n",
        "            print(f\"{e} for {mms}\")\n",
        "\n",
        "    return items"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "A9o5gky_uE4s"
      },
      "outputs": [],
      "source": [
        "# Used with get_ebook_list() to make batch citation calls to get ebook information.\n",
        "async def get_ebook(\n",
        "    client, throttler, headers, mms, df\n",
        "):  \n",
        "    ebooks = []\n",
        "    async with throttler:\n",
        "        try:\n",
        "            resp = None\n",
        "            courseInfo = df.loc[\n",
        "                df[\"MMS Id\"] == mms\n",
        "            ]  # Uses the MMS ID to pull the table row, then uses values from that row to apply course information (code, instructor, end date)\n",
        "            url = str(courseInfo.url.values[0])\n",
        "            async with client.get(url, headers=headers) as session:\n",
        "                if session.status != 200:\n",
        "                    resp = await session.text()\n",
        "                    session.raise_for_status()\n",
        "                resp = await session.json()\n",
        "                b = {\n",
        "                    \"title\": resp[\"metadata\"][\n",
        "                        \"title\"\n",
        "                    ],  # Build out the citation information as a dictionary, appends it into the ebooks list.\n",
        "                    \"Barcode\": \"none - ebook\",\n",
        "                    \"call number\": \"none\",\n",
        "                    \"permanent location\": \"none\",\n",
        "                    \"due back date\": \"none\",\n",
        "                    \"in temp location\": \"none\",\n",
        "                    \"temp location\": \"none\",\n",
        "                    \"temp policy\": \"none\",\n",
        "                    \"temp call number\": \"none\",\n",
        "                    \"course code\": courseInfo[\"Course Code\"].values[0],\n",
        "                    \"course end date\": courseInfo[\"Current Course End Date\"].values[0],\n",
        "                    \"instructor\": courseInfo[\"Course Instructor\"].values[0],\n",
        "                }\n",
        "\n",
        "                try:\n",
        "                    b['isbn'] = resp[\"metadata\"][\"isbn\"]\n",
        "                except KeyError:\n",
        "                    b['isbn'] = 'none'\n",
        "                try:\n",
        "                    b['edition'] = resp[\"metadata\"][\"edition\"]\n",
        "                except KeyError:\n",
        "                    b['edition'] = 'none'\n",
        "                try:\n",
        "                    b['author'] = resp[\"metadata\"][\"author\"]\n",
        "                except KeyError:\n",
        "                    b['author'] = 'none'\n",
        "                ebooks.append(b)\n",
        "        except Exception as e:\n",
        "            print(f\"{e} for {mms}\")\n",
        "\n",
        "    return ebooks"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "5s5pbgfmtPNK"
      },
      "outputs": [],
      "source": [
        "# Works with get_ebook() to make batch courses calls to retrieve citation information. Uses course_headers dict and course key.\n",
        "async def get_ebook_list(\n",
        "    elec_items, course_headers\n",
        "):  \n",
        "    throttler = Throttler(rate_limit=25)\n",
        "    async with aiohttp.ClientSession() as client:\n",
        "\n",
        "        awaitables = [\n",
        "            get_ebook(\n",
        "                client=client, throttler=throttler, headers=course_headers, mms=i, df=df\n",
        "            )\n",
        "            for i in elec_items\n",
        "        ]\n",
        "\n",
        "        itemList = await asyncio.gather(*awaitables)\n",
        "\n",
        "    return itemList"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "4XmkBb5PvXy2"
      },
      "outputs": [],
      "source": [
        "# Simple string formatter to remove special characters from the MMS ids.\n",
        "def formatter(\n",
        "    str,\n",
        "):  \n",
        "    str1 = str.replace(\"[\", \"\")\n",
        "    str2 = str1.replace(\"]\", \"\")\n",
        "    str3 = str2.replace(\"'\", \"\")\n",
        "\n",
        "    return str3"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "JKntS0l2WAhr"
      },
      "outputs": [],
      "source": [
        "# Build out each row for a physical item. Uses MMS ID to find the appropriate row \n",
        "# containing static course information (instructor, code, end date) and applies that to each item.\n",
        "def get_physical_citations(\n",
        "    p_list, df, cit_list\n",
        "):  \n",
        "    for i in p_list:\n",
        "        mms = i[0][\"item\"][0][\"bib_data\"][\"mms_id\"]\n",
        "        courseInfo = df[df[\"MMS Id\"].str.contains(mms)]    # return rows where mms matches\n",
        "        \n",
        "        for j in i[0][\"item\"]: \n",
        "            for index, row in courseInfo.iterrows():\n",
        "              a = get_phys_item(j)\n",
        "              a[\"course code\"] = row[\"Course Code\"]\n",
        "              a[\"course end date\"] = row[\"Current Course End Date\"]\n",
        "              a[\"instructor\"] = row[\"Course Instructor\"]\n",
        "        \n",
        "              cit_list.append(a)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "um74yWWsWrjE"
      },
      "outputs": [],
      "source": [
        "# Separates out the MMS IDs for citation calls from the item records of physical item calls.\n",
        "def build_lists(\n",
        "    itemlist, phys_items, elec_items\n",
        "):  \n",
        "    for i in itemlist:\n",
        "        try:\n",
        "            if i[0][\"total_record_count\"] > 0:\n",
        "                phys_items.append(i)\n",
        "        except TypeError as error:\n",
        "            elec_items.append(i)\n",
        "        except IndexError:\n",
        "            pass"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "zdEj4Ph03AlZ"
      },
      "outputs": [],
      "source": [
        "# Extracts out the returned citation records from the async ebooks function, then loads those into the final list.\n",
        "def load_ebooks(\n",
        "    ebooks, citationItems\n",
        "):  \n",
        "    for i in ebooks:\n",
        "        for j in i:\n",
        "            citationItems.append(j)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "kBboVw3cJvYx"
      },
      "outputs": [],
      "source": [
        "# Strip the Z from the due back date being passed from Alma.\n",
        "def strip_date(a):\n",
        "    try:\n",
        "        return a.strip(\"Z\")\n",
        "    except AttributeError:\n",
        "        pass"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "jQghjwsX2arc"
      },
      "outputs": [],
      "source": [
        "# Initialize empty lists we'll use.\n",
        "phys_items, temp_elec_items, citationItems, elec_items, db_list = (\n",
        "    [] for i in range(5)\n",
        ") \n",
        "\n",
        "columns = {}"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "CgxnRlVsLUYj"
      },
      "outputs": [],
      "source": [
        "# Retrieve report data\n",
        "build_db()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "2obyyc6NLdjM"
      },
      "outputs": [],
      "source": [
        "# Remove rows containing no data\n",
        "db_rows = shape_db(\n",
        "    db_list\n",
        ")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "lKx-rngrLfh8"
      },
      "outputs": [],
      "source": [
        "# Create dataframe from report data\n",
        "df = pd.DataFrame(\n",
        "    db_rows\n",
        ")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "DLPihBJELhLM"
      },
      "outputs": [],
      "source": [
        "# Map report column names onto dataframe\n",
        "df = df.rename(\n",
        "    columns=columns\n",
        ")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ociv9IzrYedo"
      },
      "outputs": [],
      "source": [
        "# Build out our endpoint urls for making course calls (for citation information we use for ebooks)\n",
        "df[\"url\"] = build_url(\n",
        "    df\n",
        ")  "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "MmDuErvvYk7A"
      },
      "outputs": [],
      "source": [
        "# Make bibs calls to get physical item information.\n",
        "itemlist = await get_bibs_list(\n",
        "    df, headers\n",
        ")  "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "XROoDs07W1h8"
      },
      "outputs": [],
      "source": [
        "# Separates out physical item information (phys_items), and MMS ids to pull citation information (temp_elec_items)\n",
        "build_lists(\n",
        "    itemlist, phys_items, temp_elec_items\n",
        ")  "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Z2GULm2ZyccG"
      },
      "outputs": [],
      "source": [
        "# format the mms IDs\n",
        "e = [formatter(str(i)) for i in temp_elec_items]  "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "vJYYJgTfl0bZ"
      },
      "outputs": [],
      "source": [
        "# Remove duplicate MMS id's\n",
        "elec_items = [*set(e)]  "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "5jJVXRE82GEF"
      },
      "outputs": [],
      "source": [
        "# Make course api calls to retrieve citation information\n",
        "ebooks = await get_ebook_list(\n",
        "    elec_items, course_headers\n",
        ")  "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "BMFJR-GL3Pax"
      },
      "outputs": [],
      "source": [
        "# Extract out citation information and adds it to the final list.\n",
        "load_ebooks(\n",
        "    ebooks, citationItems\n",
        ")  "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "gtH6ENy8WcrU"
      },
      "outputs": [],
      "source": [
        "# Extract relevent physical item information from the records we received, then adds them to the citationItems list.\n",
        "get_physical_citations(\n",
        "    phys_items, df, citationItems\n",
        ")  "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "oGZ4bk-sOrDo"
      },
      "outputs": [],
      "source": [
        "# Generate a dataframe from the collection of physical / electronic item dictionaries.\n",
        "c = pd.DataFrame.from_records(\n",
        "    citationItems\n",
        ")  "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "mB8F4sK-qbgM"
      },
      "outputs": [],
      "source": [
        "# Drop duplicate rows while keeping the last instance.\n",
        "d = c.drop_duplicates(\n",
        "    subset = ['course code', 'Barcode'],\n",
        "    keep= 'last').reset_index(drop=True) "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "eQpSoPRmJvYz"
      },
      "outputs": [],
      "source": [
        "# Format date columns to match\n",
        "d['due back date'] = [strip_date(s) for s in d['due back date']]\n",
        "d['course end date'] = pd.to_datetime(d['course end date'], infer_datetime_format=True)\n",
        "d['course end date'] = d['course end date'].dt.date"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "PY023xuQ7Qub"
      },
      "outputs": [],
      "source": [
        "# Choose the columns we want in the final report, and export it as xlsx.\n",
        "columns = [\n",
        "    \"course code\",\n",
        "    \"instructor\",\n",
        "    \"title\",\n",
        "    \"author\",\n",
        "    \"edition\",\n",
        "    \"call number\",\n",
        "    \"isbn\",\n",
        "    \"Barcode\",  \n",
        "    \"permanent location\",\n",
        "    \"temp location\",\n",
        "    \"in temp location\",\n",
        "    \"temp call number\",\n",
        "    \"temp policy\",\n",
        "    \"due back date\",\n",
        "    \"course end date\",\n",
        "]\n",
        "d = d[columns]\n",
        "d = d.sort_values(by=[\"course code\"])\n",
        "d.to_excel(\"cts.xlsx\", index=False)"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3.10.4 64-bit",
      "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.10.4"
    },
    "vscode": {
      "interpreter": {
        "hash": "369f2c481f4da34e4445cda3fffd2e751bd1c4d706f27375911949ba6bb62e1c"
      }
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}