{ "nbformat": 4, "nbformat_minor": 0, "metadata": { "colab": { "provenance": [] }, "kernelspec": { "name": "python3", "display_name": "Python 3" }, "language_info": { "name": "python" } }, "cells": [ { "cell_type": "code", "source": [ "pip install pandas requests openpyxl\n" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "yRlC9z-C7mz_", "outputId": "ef41f76d-cec2-450c-b3a3-54d41ab6d988" }, "execution_count": 11, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "Requirement already satisfied: pandas in /usr/local/lib/python3.10/dist-packages (2.2.2)\n", "Requirement already satisfied: requests in /usr/local/lib/python3.10/dist-packages (2.32.3)\n", "Requirement already satisfied: openpyxl in /usr/local/lib/python3.10/dist-packages (3.1.5)\n", "Requirement already satisfied: numpy>=1.22.4 in /usr/local/lib/python3.10/dist-packages (from pandas) (1.26.4)\n", "Requirement already satisfied: python-dateutil>=2.8.2 in /usr/local/lib/python3.10/dist-packages (from pandas) (2.8.2)\n", "Requirement already satisfied: pytz>=2020.1 in /usr/local/lib/python3.10/dist-packages (from pandas) (2024.2)\n", "Requirement already satisfied: tzdata>=2022.7 in /usr/local/lib/python3.10/dist-packages (from pandas) (2024.2)\n", "Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.10/dist-packages (from requests) (3.4.0)\n", "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.10/dist-packages (from requests) (3.10)\n", "Requirement already satisfied: urllib3<3,>=1.21.1 in /usr/local/lib/python3.10/dist-packages (from requests) (2.2.3)\n", "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.10/dist-packages (from requests) (2024.8.30)\n", "Requirement already satisfied: et-xmlfile in /usr/local/lib/python3.10/dist-packages (from openpyxl) (2.0.0)\n", "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.10/dist-packages (from python-dateutil>=2.8.2->pandas) (1.16.0)\n" ] } ] }, { "cell_type": "code", "source": [ "import pandas as pd\n", "import requests\n", "import json\n", "\n", "# Function to query OpenAlex API based on a person's name\n", "def query_openalex(first_name, last_name):\n", " base_url = \"https://api.openalex.org/authors\"\n", " params = {\n", " \"search\": f\"{first_name} {last_name}\",\n", " \"per_page\": 1 # Limit the number of results to 1\n", " }\n", " response = requests.get(base_url, params=params)\n", "\n", " if response.status_code == 200:\n", " data = response.json()\n", " # Check if 'count' key exists in 'meta' and if it's greater than 0\n", " if 'count' in data['meta'] and data['meta']['count'] > 0:\n", " # If OpenAlex returns results, return the first one\n", " author = data['results'][0]\n", " return {\n", " 'OpenAlexID': author['id'],\n", " 'Affiliation': author['affiliation'] if 'affiliation' in author else None,\n", " 'AffiliationID': author['affiliation_id'] if 'affiliation_id' in author else None\n", " }\n", " else:\n", " return None # No match found\n", " else:\n", " print(f\"Error querying OpenAlex for {first_name} {last_name}: {response.status_code}\")\n", " return None\n", "\n", "# Load the data from the provided Excel file\n", "df = pd.read_excel(\"ays_faculty.xlsx\")\n", "\n", "# Prepare lists to store matched and unmatched results\n", "matched = []\n", "unmatched = []\n", "\n", "# Loop through the faculty names and query OpenAlex\n", "for index, row in df.iterrows():\n", " first_name = row['First Name']\n", " last_name = row['Last Name']\n", "\n", " # Query OpenAlex API\n", " result = query_openalex(first_name, last_name)\n", "\n", " if result:\n", " # If a match is found, append the OpenAlex data to the row and add to matched list\n", " row['OpenAlexIdentifier'] = result['OpenAlexID']\n", " row['Affiliation'] = result['Affiliation']\n", " row['AffiliationID'] = result['AffiliationID']\n", " matched.append(row)\n", " else:\n", " # If no match is found, add to unmatched list\n", " unmatched.append(row)\n", "\n", "# Convert the matched and unmatched lists to DataFrames\n", "matched_df = pd.DataFrame(matched)\n", "unmatched_df = pd.DataFrame(unmatched)\n", "\n", "# Save the results to CSV or JSON as needed\n", "matched_df.to_csv('matched_faculty.csv', index=False)\n", "unmatched_df.to_csv('unmatched_faculty.csv', index=False)\n", "\n", "# Optionally, save the data as JSON\n", "matched_df.to_json('matched_faculty.json', orient='records', lines=True)\n", "unmatched_df.to_json('unmatched_faculty.json', orient='records', lines=True)\n", "\n", "print(\"Process completed. Matched and unmatched faculty saved.\")\n" ], "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "4EmZLrX-78Q6", "outputId": "da62b722-9c1e-488d-bc5b-c1db80c68ac4" }, "execution_count": 14, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "Process completed. Matched and unmatched faculty saved.\n" ] } ] }, { "cell_type": "code", "source": [ "from google.colab import drive\n", "\n", "# This will unmount the drive if it was previously mounted\n", "drive.flush_and_unmount()\n", "print('All changes made in this colab session should now be visible in Drive.')\n", "\n", "# Remount the drive\n", "drive.mount('/content/drive')" ], "metadata": { "id": "KzIuLFW4uXpb", "colab": { "base_uri": "https://localhost:8080/" }, "outputId": "37a5b38e-c517-4d99-c34e-7ed670b296fc" }, "execution_count": 16, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "Drive not mounted, so nothing to flush and unmount.\n", "All changes made in this colab session should now be visible in Drive.\n", "Mounted at /content/drive\n" ] } ] } ] }