WBRef USGS Laboratories.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The USGS web presents a not fully complete listing of USGS Laboratories as another aspect of the USGS organizational structure. Similar to the \"observatories\" concept, there is a more strict internal definition of a lab as it relates to the Quality Management System, but the public-facing web presentation is useful in the context of organizing entities into the GeoKB. This notebook uses a similar web scraping routine to pull useful information on the labs.\n",
    "\n",
    "In contrast to the state/territory listing with USGS Regions and Science Centers, the state listing for labs does not really apply as an \"operating area\" concept. Rather, it is the state that a lab is located in, so we use the \"located in the administrative territorial entity\" property in this case."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import pandas as pd\n",
    "from wbmaker import WikibaseConnection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "geokb = WikibaseConnection(\"GEOKB_CLOUD\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "usgs_base_url = \"https://www.usgs.gov\"\n",
    "usgs_lab_url = \"https://www.usgs.gov/science/laboratories\""
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Labs from web listing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def scrape_usgs_labs():\n",
    "    labs = []\n",
    "\n",
    "    r_labs = requests.get(usgs_lab_url)\n",
    "    if r_labs.status_code == 200:\n",
    "        soup_labs = BeautifulSoup(r_labs.content, 'html.parser')\n",
    "        labs_table = soup_labs.find('table')\n",
    "        table_body = labs_table.find('tbody')\n",
    "        for row in table_body.find_all('tr'):\n",
    "            columns = row.find_all('td')\n",
    "            labs.append({\n",
    "                \"label\": columns[0].text.strip(),\n",
    "                \"reference_url\": f\"{usgs_base_url}{columns[0].find('a')['href']}\",\n",
    "                \"sc_label\": columns[1].text.strip() if columns[1].text.strip() != '' else None,\n",
    "                \"sc_url\": f\"{usgs_base_url}{columns[1].find('a')['href']}\" if columns[1].text.strip() != '' else None,\n",
    "                \"states_territories\": columns[2].text.strip()\n",
    "            })\n",
    "\n",
    "        df_labs = pd.DataFrame(labs)\n",
    "\n",
    "        df_labs[\"acronym\"] = df_labs[\"label\"].str.extract(r'\\((.*?)\\)')\n",
    "        df_labs[\"label\"] = df_labs.apply(lambda x: x.label.replace(f\"({x.acronym})\", \"\").strip() if isinstance(x.acronym, str) else x.label, axis=1)\n",
    "\n",
    "        return df_labs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_labs = scrape_usgs_labs()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# GeoKB References for Linking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "q_geokb_orgs = \"PREFIX%20wd%3A%20%3Chttps%3A%2F%2Fgeokb.wikibase.cloud%2Fentity%2F%3E%0APREFIX%20wdt%3A%20%3Chttps%3A%2F%2Fgeokb.wikibase.cloud%2Fprop%2Fdirect%2F%3E%0A%0ASELECT%20%3Fitem%20%3FitemLabel%20%3Fitem_alt_label%20%3Furl%20%3Finstance_ofLabel%0AWHERE%20%7B%0A%20%20%3Fitem%20wdt%3AP62*%20wd%3AQ44210%20.%0A%20%20%3Fitem%20wdt%3AP1%20%3Finstance_of%20.%0A%20%20OPTIONAL%20%7B%0A%20%20%20%20%3Fitem%20skos%3AaltLabel%20%3Fitem_alt_label%20.%0A%20%20%20%20FILTER%20(lang(%3Fitem_alt_label)%3D'en')%0A%20%20%7D%0A%20%20OPTIONAL%20%7B%0A%20%20%20%20%3Fitem%20wdt%3AP31%20%3Furl%20.%0A%20%20%7D%0A%20%20SERVICE%20wikibase%3Alabel%20%7B%20bd%3AserviceParam%20wikibase%3Alanguage%20%22en%22%20.%20%7D%0A%7D\"\n",
    "df_geokb_orgs = geokb.wb_ref_data(query=q_geokb_orgs)\n",
    "df_geokb_orgs[\"qid\"] = df_geokb_orgs[\"item\"].apply(lambda x: x.split(\"/\")[-1])\n",
    "\n",
    "q_states_territories = \"PREFIX%20wdt%3A%20%3Chttps%3A%2F%2Fgeokb.wikibase.cloud%2Fprop%2Fdirect%2F%3E%0A%0ASELECT%20%3Fitem%20%3FitemLabel%20%3Ffips_alpha%0AWHERE%20%7B%0A%20%20%3Fitem%20wdt%3AP13%20%3Ffips_alpha%20.%20%0A%20%20SERVICE%20wikibase%3Alabel%20%7B%20bd%3AserviceParam%20wikibase%3Alanguage%20%22en%22%20.%20%7D%0A%7D\"\n",
    "df_states_territories = geokb.wb_ref_data(query=q_states_territories)\n",
    "df_states_territories[\"qid\"] = df_states_territories[\"item\"].apply(lambda x: x.split(\"/\")[-1])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "org_name_lookup = pd.concat([\n",
    "    df_geokb_orgs[df_geokb_orgs[\"item_alt_label\"].notnull()][[\"qid\",\"item_alt_label\"]].rename(columns={\"item_alt_label\": \"label\"}),\n",
    "    df_geokb_orgs[[\"qid\",\"itemLabel\"]].rename(columns={\"itemLabel\": \"label\"})\n",
    "])\n",
    "\n",
    "org_url_lookup = df_geokb_orgs[df_geokb_orgs[\"url\"].notnull()][[\"qid\",\"url\"]]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prep Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_labs_to_geokb = pd.merge(\n",
    "    left=df_labs,\n",
    "    right=org_name_lookup,\n",
    "    how=\"left\",\n",
    "    on=\"label\"\n",
    ")\n",
    "\n",
    "df_labs_to_geokb = pd.merge(\n",
    "    left=df_labs_to_geokb,\n",
    "    right=org_url_lookup.rename(columns={'qid': 'sc_qid', 'url': 'sc_url'}),\n",
    "    how=\"left\",\n",
    "    on=\"sc_url\"\n",
    ")\n",
    "\n",
    "df_labs_to_geokb = pd.merge(\n",
    "    left=df_labs_to_geokb,\n",
    "    right=df_states_territories[[\"qid\",\"itemLabel\"]].rename(columns={'qid': 'loc_qid', 'itemLabel': 'states_territories'}),\n",
    "    how=\"left\",\n",
    "    on=\"states_territories\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Qualifiers @632310 _Qualifiers__qualifiers={'P110': [<Snak @ced390 _Snak__snaktype=<WikibaseSnakType.KNOWN_VALUE: 'value'> _Snak__property_number='P110' _Snak__hash=None _Snak__datavalue={'value': {'time': '+2023-01-01T00:00:00Z', 'before': 0, 'after': 0, 'precision': 9, 'timezone': 0, 'calendarmodel': 'http://www.wikidata.org/entity/Q1985727'}, 'type': 'time'} _Snak__datatype='time'>]}>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lab_source_refs = geokb.models.References()\n",
    "lab_source_refs.add(\n",
    "    geokb.datatypes.URL(\n",
    "        prop_nr=geokb.prop_lookup['reference URL'],\n",
    "        value=usgs_lab_url\n",
    "    )\n",
    ")\n",
    "\n",
    "now_quals = geokb.models.Qualifiers()\n",
    "now_quals.add(\n",
    "    geokb.datatypes.Time(\n",
    "        prop_nr=geokb.prop_lookup['point in time'],\n",
    "        time='+2023-01-01T00:00:00Z',\n",
    "        precision=geokb.date_precision.YEAR\n",
    "    )\n",
    ")\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preview"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>label</th>\n",
       "      <th>reference_url</th>\n",
       "      <th>sc_label</th>\n",
       "      <th>sc_url</th>\n",
       "      <th>states_territories</th>\n",
       "      <th>acronym</th>\n",
       "      <th>qid</th>\n",
       "      <th>sc_qid</th>\n",
       "      <th>loc_qid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Algal and Other Environmental Toxins Laboratory</td>\n",
       "      <td>https://www.usgs.gov/programs/environmental-he...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Kansas</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Q269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Aquatic Experimental Laboratory</td>\n",
       "      <td>https://www.usgs.gov/centers/fort-collins-scie...</td>\n",
       "      <td>Fort Collins Science Center</td>\n",
       "      <td>https://www.usgs.gov/centers/fort</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>AXL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Q44213</td>\n",
       "      <td>Q256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Behavioral Toxicology Laboratory</td>\n",
       "      <td>https://www.usgs.gov/programs/environmental-he...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Q244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bioactive Chemicals Research Laboratory</td>\n",
       "      <td>https://www.usgs.gov/programs/environmental-he...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Q256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bird Banding Laboratory</td>\n",
       "      <td>https://www.usgs.gov/labs/bird-banding-laboratory</td>\n",
       "      <td>Patuxent Wildlife Research Center</td>\n",
       "      <td>https://www.usgs.gov/centers/pwrc</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Q50879</td>\n",
       "      <td>Q270</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             label  \\\n",
       "0  Algal and Other Environmental Toxins Laboratory   \n",
       "1                  Aquatic Experimental Laboratory   \n",
       "2                 Behavioral Toxicology Laboratory   \n",
       "3          Bioactive Chemicals Research Laboratory   \n",
       "4                          Bird Banding Laboratory   \n",
       "\n",
       "                                       reference_url  \\\n",
       "0  https://www.usgs.gov/programs/environmental-he...   \n",
       "1  https://www.usgs.gov/centers/fort-collins-scie...   \n",
       "2  https://www.usgs.gov/programs/environmental-he...   \n",
       "3  https://www.usgs.gov/programs/environmental-he...   \n",
       "4  https://www.usgs.gov/labs/bird-banding-laboratory   \n",
       "\n",
       "                            sc_label                             sc_url  \\\n",
       "0                               None                               None   \n",
       "1        Fort Collins Science Center  https://www.usgs.gov/centers/fort   \n",
       "2                               None                               None   \n",
       "3                               None                               None   \n",
       "4  Patuxent Wildlife Research Center  https://www.usgs.gov/centers/pwrc   \n",
       "\n",
       "  states_territories acronym  qid  sc_qid loc_qid  \n",
       "0             Kansas     NaN  NaN     NaN    Q269  \n",
       "1           Colorado     AXL  NaN  Q44213    Q256  \n",
       "2           Missouri     NaN  NaN     NaN    Q244  \n",
       "3           Colorado     NaN  NaN     NaN    Q256  \n",
       "4           Maryland     NaN  NaN  Q50879    Q270  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_labs_to_geokb.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Commit Lab Claims and Build New Items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "labs_added = {\n",
    "    \"Algal and Other Environmental Toxins Laboratory\": \"Q50881\",\n",
    "    \"Aquatic Experimental Laboratory\": \"Q50882\",\n",
    "    \"Behavioral Toxicology Laboratory\": \"Q50883\",\n",
    "    \"Bioactive Chemicals Research Laboratory\": \"Q50884\",\n",
    "    \"Bird Banding Laboratory\": \"Q50885\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Brine Research Instrumentation and Experimental\n",
      "\t\t\t Laboratory Q50886\n",
      "Calcareous Laboratory Q50887\n",
      "Carbonate Aquifer Characterization Laboratory Q50888\n",
      "Chemistry Laboratory Q50889\n",
      "Columbia River Research Laboratory Q50890\n",
      "Coral Microbial Ecology Laboratory Q50891\n",
      "Denver Microbeam Laboratory Q50892\n",
      "Diagnostic Microbiology Laboratory Q50893\n",
      "Diagnostic Parasitology Laboratory Q50894\n",
      "Diagnostic Virology Laboratory Q50895\n",
      "Earth Systems Biogeochemistry Laboratory Q50896\n",
      "Eastern Energy and Environmental Laboratory Q50897\n",
      "Environmental and Public Health Microbiology Laboratory Q50898\n",
      "Environmental Chemistry Laboratory Q50899\n",
      "Fish Health Program Q50900\n",
      "Functional and Molecular Bioassay Laboratory Q50901\n",
      "High-Content Screening Laboratory Q50902\n",
      "Hydrologic Instrumentation Facility Q44352\n",
      "Integrated Water Chemistry Assessment Laboratory Q50903\n",
      "Integrated Water Chemistry Assessment Laboratory Q50904\n",
      "Kentucky Sediment Laboratory Q50905\n",
      "Klamath Falls Field Station Q50906\n",
      "Laboratory for Infectious Disease and the Environment Q44380\n",
      "Low-Temperature Calibration Facility Q50907\n",
      "Luminescence Dating Laboratory Q50908\n",
      "Macrofossil and Sediment Processing Laboratory Q50909\n",
      "Marrowstone Marine Field Station Q50910\n",
      "Mercury Research Laboratory Q44370\n",
      "Michigan Bacteriological Research Laboratory Q50911\n",
      "Microbial Biogeochemistry Laboratory Q50912\n",
      "Mineralogy and Microscopy Laboratory Q50913\n",
      "Molecular Ecology Laboratory Q50914\n",
      "National Water Quality Laboratory Q44337\n",
      "New Mexico Water Science Center Sediment Laboratory Q50915\n",
      "Ohio Water Microbiology Laboratory Q50916\n",
      "Organic Chemistry Research Laboratory Q50917\n",
      "Organic Geochemistry Laboratory Q50918\n",
      "Organic Geochemistry Research Laboratory Q50919\n",
      "Organic Matter Research Laboratory Q50920\n",
      "Petroleum Geochemistry Research Laboratory Q50921\n",
      "Photomicroscopy and Flow Cytometry Laboratory Q50922\n",
      "Plant-Soil-Environment Laboratory Q50923\n",
      "Pollen Laboratory Q50924\n",
      "Quaternary Paleobotany Laboratory Q50925\n",
      "Reston Biogeochemical Processes in Groundwater\n",
      "\t\t\tLaboratory Q50926\n",
      "Reston Electron Microbeam Laboratory Q50927\n",
      "Reston Microbial Laboratory Q50928\n",
      "Reston Stable Isotope Laboratory Q44350\n",
      "Soil and Low-Ionic-Strength Water Quality Laboratory Q50929\n",
      "Soils and Sediment Laboratory Q50930\n",
      "Spectroscopy Lab Q50931\n",
      "St. Petersburg Carbon Analytical Laboratory Q50932\n",
      "Terrestrial Ecosystems Laboratory Q50933\n",
      "WHCMSC Sediment Laboratory Q50934\n"
     ]
    }
   ],
   "source": [
    "for index, row in df_labs_to_geokb[~df_labs_to_geokb.label.isin(labs_added.keys())].iterrows():\n",
    "    if isinstance(row.qid, str):\n",
    "        item = geokb.wbi.item.get(row.qid)\n",
    "    else:\n",
    "        item = geokb.wbi.item.new()\n",
    "\n",
    "    item.labels.set('en', row.label)\n",
    "    item.descriptions.set('en', f'a USGS laboratory located in {row.states_territories}')\n",
    "\n",
    "    if isinstance(row.acronym, str):\n",
    "        item.aliases.set('en', row.acronym)\n",
    "\n",
    "    item.claims.add(\n",
    "        claims=geokb.datatypes.Item(\n",
    "            prop_nr=geokb.prop_lookup['instance of'],\n",
    "            value=\"Q50880\",\n",
    "            qualifiers=now_quals,\n",
    "            references=lab_source_refs\n",
    "        ),\n",
    "        action_if_exists=geokb.action_if_exists.REPLACE_ALL\n",
    "    )\n",
    "\n",
    "    item.claims.add(\n",
    "        claims=geokb.datatypes.URL(\n",
    "            prop_nr=geokb.prop_lookup['reference URL'],\n",
    "            value=row.reference_url,\n",
    "            qualifiers=now_quals,\n",
    "            references=lab_source_refs\n",
    "        ),\n",
    "        action_if_exists=geokb.action_if_exists.REPLACE_ALL\n",
    "    )\n",
    "\n",
    "    if isinstance(row.sc_qid, str):\n",
    "        item.claims.add(\n",
    "            claims=geokb.datatypes.Item(\n",
    "                prop_nr=geokb.prop_lookup['part of'],\n",
    "                value=row[\"sc_qid\"],\n",
    "                qualifiers=now_quals,\n",
    "                references=lab_source_refs\n",
    "            ),\n",
    "            action_if_exists=geokb.action_if_exists.REPLACE_ALL\n",
    "        )\n",
    "            \n",
    "    if isinstance(row.loc_qid, str):\n",
    "        item.claims.add(\n",
    "            claims=geokb.datatypes.Item(\n",
    "                prop_nr=geokb.prop_lookup['located in the administrative territorial entity'],\n",
    "                value=row.loc_qid,\n",
    "                qualifiers=now_quals,\n",
    "                references=lab_source_refs\n",
    "            ),\n",
    "            action_if_exists=geokb.action_if_exists.REPLACE_ALL\n",
    "        )\n",
    "\n",
    "    response = item.write(\n",
    "        summary=\"Built out USGS lab claims from USGS lab listing\"\n",
    "    )\n",
    "    labs_added[row.label] = response.id\n",
    "    print(row.label, response.id)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Science Center \"has part\" Claims"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sc_qid</th>\n",
       "      <th>lab_qid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Q44213</td>\n",
       "      <td>[Q50882]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Q44227</td>\n",
       "      <td>[Q50917]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Q44230</td>\n",
       "      <td>[Q50934]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Q44241</td>\n",
       "      <td>[Q50929]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Q44243</td>\n",
       "      <td>[Q50918]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Q44244</td>\n",
       "      <td>[Q50886, Q50897, Q50926, Q50927, Q50928]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Q44247</td>\n",
       "      <td>[Q50891, Q50932]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Q44250</td>\n",
       "      <td>[Q50923, Q50933]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Q44258</td>\n",
       "      <td>[Q50887, Q50909, Q50924]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Q44284</td>\n",
       "      <td>[Q50890, Q50900, Q50906, Q50910]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Q44285</td>\n",
       "      <td>[Q50888]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Q44286</td>\n",
       "      <td>[Q50915]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Q44289</td>\n",
       "      <td>[Q50921]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Q44315</td>\n",
       "      <td>[Q50896, Q50907, Q50908, Q50913, Q50925, Q50930]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Q44326</td>\n",
       "      <td>[Q44370]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Q44331</td>\n",
       "      <td>[Q50889, Q50893, Q50894, Q50895]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Q44345</td>\n",
       "      <td>[Q50905, Q50916]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Q50860</td>\n",
       "      <td>[Q50904]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Q50879</td>\n",
       "      <td>[Q50885]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    sc_qid                                           lab_qid\n",
       "0   Q44213                                          [Q50882]\n",
       "1   Q44227                                          [Q50917]\n",
       "2   Q44230                                          [Q50934]\n",
       "3   Q44241                                          [Q50929]\n",
       "4   Q44243                                          [Q50918]\n",
       "5   Q44244          [Q50886, Q50897, Q50926, Q50927, Q50928]\n",
       "6   Q44247                                  [Q50891, Q50932]\n",
       "7   Q44250                                  [Q50923, Q50933]\n",
       "8   Q44258                          [Q50887, Q50909, Q50924]\n",
       "9   Q44284                  [Q50890, Q50900, Q50906, Q50910]\n",
       "10  Q44285                                          [Q50888]\n",
       "11  Q44286                                          [Q50915]\n",
       "12  Q44289                                          [Q50921]\n",
       "13  Q44315  [Q50896, Q50907, Q50908, Q50913, Q50925, Q50930]\n",
       "14  Q44326                                          [Q44370]\n",
       "15  Q44331                  [Q50889, Q50893, Q50894, Q50895]\n",
       "16  Q44345                                  [Q50905, Q50916]\n",
       "17  Q50860                                          [Q50904]\n",
       "18  Q50879                                          [Q50885]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_center_labs = pd.merge(\n",
    "    left=df_labs_to_geokb[df_labs_to_geokb.sc_qid.notnull()][[\"label\",\"sc_qid\"]].drop_duplicates(),\n",
    "    right=pd.DataFrame(list(labs_added.items()), columns=[\"label\",\"lab_qid\"]),\n",
    "    how=\"left\",\n",
    "    on=\"label\"\n",
    ").drop([\"label\"], axis=1).groupby(\"sc_qid\", as_index=False).agg(list)\n",
    "\n",
    "df_center_labs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in df_center_labs.iterrows():\n",
    "    item = geokb.wbi.item.get(row[\"sc_qid\"])\n",
    "    \n",
    "    has_part_claims = []\n",
    "    for lab_qid in row[\"lab_qid\"]:\n",
    "        has_part_claims.append(\n",
    "            geokb.datatypes.Item(\n",
    "                prop_nr=geokb.prop_lookup['has part'],\n",
    "                value=lab_qid,\n",
    "                qualifiers=now_quals,\n",
    "                references=lab_source_refs\n",
    "            )\n",
    "        )\n",
    "\n",
    "    item.claims.add(\n",
    "        claims=has_part_claims,\n",
    "        action_if_exists=geokb.action_if_exists.REPLACE_ALL\n",
    "    )\n",
    "\n",
    "    response = item.write(\n",
    "        summary=\"Added labs as has part claims to Science Center\"\n",
    "    )\n",
    "    print(response.id)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "geokb",
   "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.11.0"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}