WBRef Other Place Names.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I need to come back and revisit this one to smooth out the methodology for pulling reference place names into the graph. This was a brief deal I needed to add in first-level administrative units (states, provinces, territories) for Canada and Mexico because we have enough of those references in the Pubs Warehouse metadata to make it worthwhile. I had used different methods and sources than I used here for U.S. States and Territories, and I need to run back through and clean up a few things."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pickle\n",
    "import pandas as pd\n",
    "from wbmaker import WikibaseConnection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "geokb = WikibaseConnection('GEOKB_CLOUD')\n",
    "pw_dump = pd.DataFrame(pickle.load(open('data/pw_usgs_reports_dump.pickle', 'rb')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "geokb_states = geokb.url_sparql_query(\n",
    "    sparql_url=\"https://geokb.wikibase.cloud/query/sparql?query=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%0AWHERE%20%7B%0A%20%20%3Fitem%20wdt%3AP1%20%3Fclasses%20.%0A%20%20VALUES%20%3Fclasses%20%7B%20wd%3AQ229%20wd%3AQ25363%20%7D%0A%20%20SERVICE%20wikibase%3Alabel%20%7B%20bd%3AserviceParam%20wikibase%3Alanguage%20%22en%22%20.%20%7D%0A%7D\",\n",
    "    output_format=\"dataframe\"\n",
    ")\n",
    "\n",
    "geokb_states['object'] = geokb_states['item'].apply(lambda x: x.split('/')[-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "wd_3166_2 = geokb.url_sparql_query(\n",
    "    sparql_url=\"https://query.wikidata.org/sparql?query=SELECT%20%3Fitem%20%3FitemLabel%20%3FcountryLabel%20%3Fiso_code%20%3Finstance_ofLabel%0AWHERE%20%7B%0A%20%20%3Fitem%20wdt%3AP300%20%3Fiso_code%20.%0A%20%20%3Fitem%20wdt%3AP17%20%3Fcountry%20.%0A%20%20VALUES%20%3Fcountry%20%7B%20wd%3AQ96%20wd%3AQ16%20%7D%0A%20%20%3Fitem%20wdt%3AP31%20%3Finstance_of%20.%0A%20%20VALUES%20%3Finstance_of%20%7B%20wd%3AQ11828004%20wd%3AQ9357527%20wd%3AQ15149663%20%7D%0A%20%20SERVICE%20wikibase%3Alabel%20%7B%20bd%3AserviceParam%20wikibase%3Alanguage%20%22%5BAUTO_LANGUAGE%5D%2Cen%22.%20%7D%0A%7D\",\n",
    "    output_format=\"dataframe\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "geonames_props = [\n",
    "    \"geonameId\",\n",
    "    \"countryCode\",\n",
    "    \"name\",\n",
    "    \"toponymName\",\n",
    "    \"adminCodes1\"\n",
    "]\n",
    "\n",
    "geonames_ids = {\n",
    "    \"CA\": \"6251999\",\n",
    "    \"MX\": \"3996063\"\n",
    "}\n",
    "\n",
    "username = 'skybristol'\n",
    "\n",
    "country_dfs = []\n",
    "for geonames_id in geonames_ids.values():\n",
    "    url = f'http://api.geonames.org/childrenJSON?geonameId={geonames_id}&username={username}'\n",
    "    response = requests.get(url)\n",
    "    country_dfs.append(pd.DataFrame(response.json()['geonames'])[geonames_props])\n",
    "\n",
    "geonames_states = pd.concat(country_dfs)\n",
    "geonames_states[\"iso_code\"] = geonames_states.apply(lambda x: '-'.join([x['countryCode'], x['adminCodes1']['ISO3166_2']]), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "geokb_class_mapping = {\n",
    "    \"province of Canada\": \"Q138360\",\n",
    "    \"territory of Canada\": \"Q138361\",\n",
    "    \"state of Mexico\": \"Q138362\"\n",
    "}\n",
    "\n",
    "geokb_country_mapping = {\n",
    "    \"CA\": \"Q157\",\n",
    "    \"MX\": \"Q177\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "state_items = pd.merge(\n",
    "    left=geonames_states.drop(columns=['adminCodes1']),\n",
    "    right=wd_3166_2[['iso_code','item','instance_ofLabel']],\n",
    "    how=\"inner\",\n",
    "    on=\"iso_code\"\n",
    ")\n",
    "\n",
    "state_items['country_qid'] = state_items['countryCode'].apply(lambda x: geokb_country_mapping[x])\n",
    "state_items['instance_of_qid'] = state_items['instance_ofLabel'].apply(lambda x: geokb_class_mapping[x])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Q138363 Alberta\n",
      "Q138364 British Columbia\n",
      "Q138365 Manitoba\n",
      "Q138366 New Brunswick\n",
      "Q138367 Newfoundland and Labrador\n",
      "Q138368 Northwest Territories\n",
      "Q138369 Nova Scotia\n",
      "Q138370 Nunavut\n",
      "Q138371 Ontario\n",
      "Q138372 Prince Edward Island\n",
      "Q138373 Quebec\n",
      "Q138374 Saskatchewan\n",
      "Q138375 Yukon\n",
      "Q138376 Aguascalientes\n",
      "Q138377 Baja California\n",
      "Q138378 Baja California Sur\n",
      "Q138379 Campeche\n",
      "Q138380 Chiapas\n",
      "Q138381 Chihuahua\n",
      "Q138382 Coahuila\n",
      "Q138383 Colima\n",
      "Q138384 Durango\n",
      "Q138385 Guanajuato\n",
      "Q138386 Guerrero\n",
      "Q138387 Hidalgo\n",
      "Q138388 Jalisco\n",
      "Q138389 Michoacán\n",
      "Q138390 Morelos\n",
      "Q138391 México\n",
      "Q138392 Nayarit\n",
      "Q138393 Nuevo León\n",
      "Q138394 Oaxaca\n",
      "Q138395 Puebla\n",
      "Q138396 Querétaro\n",
      "Q138397 Quintana Roo\n",
      "Q138398 San Luis Potosí\n",
      "Q138399 Sinaloa\n",
      "Q138400 Sonora\n",
      "Q138401 Tabasco\n",
      "Q138402 Tamaulipas\n",
      "Q138403 Tlaxcala\n",
      "Q138404 Veracruz\n",
      "Q138405 Yucatán\n",
      "Q138406 Zacatecas\n"
     ]
    }
   ],
   "source": [
    "for index, row in state_items.iterrows():\n",
    "    item = geokb.wbi.item.new()\n",
    "\n",
    "    item.labels.set('en', row['name'])\n",
    "    item.descriptions.set('en', row['instance_ofLabel'])\n",
    "    aliases = [i for i in row['toponymName'].split('/') if i != row['name']]\n",
    "    aliases.append(f\"{row['name']}, {row['countryCode']}\")\n",
    "    if aliases:\n",
    "        item.aliases.set('en', aliases)\n",
    "\n",
    "    item.claims.add(\n",
    "        geokb.datatypes.Item(\n",
    "            prop_nr=geokb.prop_lookup['instance of'],\n",
    "            value=row['instance_of_qid']\n",
    "        )\n",
    "    )\n",
    "\n",
    "    item.claims.add(\n",
    "        geokb.datatypes.Item(\n",
    "            prop_nr=geokb.prop_lookup['country'],\n",
    "            value=row['country_qid']\n",
    "        )\n",
    "    )\n",
    "\n",
    "    item.claims.add(\n",
    "        geokb.datatypes.ExternalID(\n",
    "            prop_nr=geokb.prop_lookup['ISO 3166-2 code'],\n",
    "            value=row['iso_code']\n",
    "        )\n",
    "    )\n",
    "\n",
    "    item.claims.add(\n",
    "        geokb.datatypes.URL(\n",
    "            prop_nr=geokb.prop_lookup['same as'],\n",
    "            value=row['item']\n",
    "        )\n",
    "    )\n",
    "\n",
    "    response = item.write(\n",
    "        summary=\"Added first-level administrative unit for country from geonames and wikidata source material\"\n",
    "    )\n",
    "    print(response.id, row['name'])\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}