{ "cells": [ { "cell_type": "markdown", "id": "88d72ce1", "metadata": {}, "source": [ "#### Check pictures in SPA och söka i SPA med folk kopplade till IFK Göteborg som saknar bild i WD P18\n", "* [This notebook](https://github.com/salgo60/spa2Commons/blob/main/Notebook/SPA%20IFKDB.ipynb) *\n", "* IFK Göteborg\n", " * [ifkdb.se](https://ifkdb.se)\n", " * Wikidata [property P11905](https://www.wikidata.org/wiki/Property:P11905)\n", " * GITHUB Issue [#12](https://github.com/salgo60/ifkdb/issues/12)\n", " * API SPA https://portrattarkiv.se/endpoints/search.php\n", " * [SPARQL söker fram gubbar i WD födda innan 1920](https://w.wiki/7Ayd)" ] }, { "cell_type": "code", "execution_count": 1, "id": "def08e15", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Last run: 2023-08-07 13:43:30.655973\n" ] } ], "source": [ "from datetime import datetime\n", "start_time = datetime.now()\n", "print(\"Last run: \", start_time)" ] }, { "cell_type": "code", "execution_count": 2, "id": "7d09d161", "metadata": {}, "outputs": [], "source": [ "import urllib3, json\n", "import pandas as pd \n", "http = urllib3.PoolManager() \n", "\n", "url= \"https://portrattarkiv.se/endpoints/search.php\" \n" ] }, { "cell_type": "code", "execution_count": 3, "id": "392f36f1", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "RangeIndex: 11 entries, 0 to 10\n", "Data columns (total 4 columns):\n", " # Column Non-Null Count Dtype \n", "--- ------ -------------- ----- \n", " 0 search 11 non-null object\n", " 1 item 11 non-null object\n", " 2 itemLabel 11 non-null object\n", " 3 year 11 non-null object\n", "dtypes: object(4)\n", "memory usage: 480.0+ bytes\n" ] } ], "source": [ "\n", "\n", "import sys\n", "from SPARQLWrapper import SPARQLWrapper, JSON\n", "\n", "endpoint_url = \"https://query.wikidata.org/sparql\"\n", "\n", "# IFKDB personer födda innan 1920 utan bild - SPA innehåller mest gamla personer\n", "queryIFKDB = \"\"\"SELECT (CONCAT (?itemLabel,\" \",str(year(?birthDate))) AS ?search) ?item ?itemLabel \n", " (str(year(?birthDate)) AS ?year)\n", " WHERE {\n", " ?item wdt:P11905 ?ifkdb. \n", " minus{?item wdt:P19 ?img} # saknar bild\n", " ?item wdt:P569 ?birthDate. \n", " FILTER(xsd:integer(year(?birthDate)) < 1920)\n", " SERVICE wikibase:label { bd:serviceParam wikibase:language \"sv\", \"en\". }\n", "} order by ?itemLabel \"\"\"\n", "\n", "\n", " \n", "def get_sparql_dataframe(endpoint_url, query):\n", " \"\"\"\n", " Helper function to convert SPARQL results into a Pandas data frame.\n", " \"\"\"\n", " user_agent = \"salgo60/%s.%s\" % (sys.version_info[0], sys.version_info[1])\n", " \n", " sparql = SPARQLWrapper(endpoint_url, agent=user_agent)\n", " sparql.setQuery(query)\n", " sparql.setReturnFormat(JSON)\n", " result = sparql.query()\n", "\n", " processed_results = json.load(result.response)\n", " cols = processed_results['head']['vars']\n", "\n", " out = []\n", " for row in processed_results['results']['bindings']:\n", " item = []\n", " for c in cols:\n", " item.append(row.get(c, {}).get('value'))\n", " out.append(item)\n", "\n", " return pd.DataFrame(out, columns=cols)\n", "\n", "#SPAdf = get_sparql_dataframe(endpoint_url, query)\n", "#SPAdf = get_sparql_dataframe(endpoint_url, queryNoPicNorra1890)\n", "SPAdf = get_sparql_dataframe(endpoint_url, queryIFKDB)\n", "\n", "SPAdf.info() " ] }, { "cell_type": "code", "execution_count": 4, "id": "50675928", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(11, 4)" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SPAdf.shape" ] }, { "cell_type": "code", "execution_count": 5, "id": "f9790064", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
searchitemitemLabelyear
0Douglas Krook 1897http://www.wikidata.org/entity/Q1252363Douglas Krook1897
1Elis Berndtsson 1902http://www.wikidata.org/entity/Q121160724Elis Berndtsson1902
2Erik Eiserman 1892http://www.wikidata.org/entity/Q121160732Erik Eiserman1892
3Gustaf Hermansson 1900http://www.wikidata.org/entity/Q121160746Gustaf Hermansson1900
4Gösta Bohman 1911http://www.wikidata.org/entity/Q121160725Gösta Bohman1911
5Göte Andersson 1919http://www.wikidata.org/entity/Q121160713Göte Andersson1919
6Helge Andersson 1899http://www.wikidata.org/entity/Q121160714Helge Andersson1899
7John Andersson 1914http://www.wikidata.org/entity/Q121160715John Andersson1914
8John Säwström 1886http://www.wikidata.org/entity/Q121052611John Säwström1886
9Olof Hjärn 1903http://www.wikidata.org/entity/Q121160747Olof Hjärn1903
10Vallentin Hultén 1911http://www.wikidata.org/entity/Q121160750Vallentin Hultén1911
\n", "
" ], "text/plain": [ " search item \\\n", "0 Douglas Krook 1897 http://www.wikidata.org/entity/Q1252363 \n", "1 Elis Berndtsson 1902 http://www.wikidata.org/entity/Q121160724 \n", "2 Erik Eiserman 1892 http://www.wikidata.org/entity/Q121160732 \n", "3 Gustaf Hermansson 1900 http://www.wikidata.org/entity/Q121160746 \n", "4 Gösta Bohman 1911 http://www.wikidata.org/entity/Q121160725 \n", "5 Göte Andersson 1919 http://www.wikidata.org/entity/Q121160713 \n", "6 Helge Andersson 1899 http://www.wikidata.org/entity/Q121160714 \n", "7 John Andersson 1914 http://www.wikidata.org/entity/Q121160715 \n", "8 John Säwström 1886 http://www.wikidata.org/entity/Q121052611 \n", "9 Olof Hjärn 1903 http://www.wikidata.org/entity/Q121160747 \n", "10 Vallentin Hultén 1911 http://www.wikidata.org/entity/Q121160750 \n", "\n", " itemLabel year \n", "0 Douglas Krook 1897 \n", "1 Elis Berndtsson 1902 \n", "2 Erik Eiserman 1892 \n", "3 Gustaf Hermansson 1900 \n", "4 Gösta Bohman 1911 \n", "5 Göte Andersson 1919 \n", "6 Helge Andersson 1899 \n", "7 John Andersson 1914 \n", "8 John Säwström 1886 \n", "9 Olof Hjärn 1903 \n", "10 Vallentin Hultén 1911 " ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ " SPAdf" ] }, { "cell_type": "code", "execution_count": 6, "id": "84099f4c", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", " Douglas Krook 1897 http://www.wikidata.org/entity/Q1252363\n", "\t\t 20.313782 Karl Arnold Douglas Larsson - 1897 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAACiN4A \t\n", "\t\t 19.261509 Albert (Abbe) Ragnar Krook - 1897 https://portrattarkiv.se/details/0piq-fX0h5AAAAAAAABkjw \t\n", "\t\t 19.255459 Douglas Olsson - 1897 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAACet2g \t\n", "\t\t 18.22902 Douglas Bondeman - 1897 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAAA2JNg \t\n", "\t\t 18.155508 Douglas Wallhäll - 1897 https://portrattarkiv.se/details/ciLMGSqrYjAAAAAAAABicA \t\n", "\n", " Elis Berndtsson 1902 http://www.wikidata.org/entity/Q121160724\n", "\t\t 20.571924 Erland Aloys Ingvar Berndtsson - 1902 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAACiLnw \t\n", "\t\t 19.767786 Ellen Nilsson-Berndtsson - 1902 https://portrattarkiv.se/details/wbbNj8LLWMAAAAAAAAAoEA \t\n", "\t\t 18.01412 Carl Berndtsson - 1902 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAABVsXw \t\n", "\t\t 17.49529 Elis Nestor - 1902 https://portrattarkiv.se/details/wbbNj8LLWMAAAAAAAABfvA \t\n", "\t\t 17.31288 Elis Larsson - 1902 https://portrattarkiv.se/details/rkIpfGqHXrAAAAAAAAAWjA \t\n", "\n", " Erik Eiserman 1892 http://www.wikidata.org/entity/Q121160732\n", "\t\t 12.96698 Erik Reichenberg - 1892 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAABMClQ \t\n", "\t\t 12.910355 Erik Akmar - 1892 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAAA2VFw \t\n", "\t\t 12.852482 Erik Weis - 1892 https://portrattarkiv.se/details/ciLMGSqrYjAAAAAAAAAiIA \t\n", "\t\t 12.829615 Erik Engberg - 1892 https://portrattarkiv.se/details/TR3yO_GV8eAAAAAAAAA13A \t\n", "\t\t 12.806978 Erik Renström - 1892 https://portrattarkiv.se/details/9Y5iReTPHdAAAAAAAABCxQ \t\n", "\n", " Gustaf Hermansson 1900 http://www.wikidata.org/entity/Q121160746\n", "\t\t 19.042025 Kurt Hermansson - 1900 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAACZjsA \t\n", "\t\t 18.20328 Tage Hermansson - 1900 https://portrattarkiv.se/details/j6S8rJvx9lAAAAAAAABQXw \t\n", "\t\t 17.341541 Gustav Hermansson - 1900 https://portrattarkiv.se/details/j6S8rJvx9lAAAAAAAACWlw \t\n", "\t\t 17.121572 Helge Hermansson - 1900 https://portrattarkiv.se/details/j6S8rJvx9lAAAAAAAABQpw \t\n", "\t\t 17.114563 Anders Hermansson - 1900 https://portrattarkiv.se/details/j6S8rJvx9lAAAAAAAABRaA \t\n", "\n", " Gösta Bohman 1911 http://www.wikidata.org/entity/Q121160725\n", "\t\t 23.806688 Gösta Bohman - 1911 https://portrattarkiv.se/details/4e1scVvZ-EAAAAAAAAAQ5A \t\n", "\t\t 14.541103 Gösta Johansson - 1911 https://portrattarkiv.se/details/Di4a42b6X_AAAAAAAACSmA \t\n", "\t\t 14.473673 Gösta Morberg - 1911 https://portrattarkiv.se/details/I_2wtd5VefAAAAAAAABDng \t\n", "\t\t 14.352381 Gösta Fall - 1911 https://portrattarkiv.se/details/IQojCnw0WmAAAAAAAAAWwQ \t\n", "\t\t 14.129609 Gösta Morberg - 1911 https://portrattarkiv.se/details/I_2wtd5VefAAAAAAAABDnw \t\n", "\n", " Göte Andersson 1919 http://www.wikidata.org/entity/Q121160713\n", "\t\t 21.547554 Göte Andersson - 1919 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAABVyQQ \t\n", "\t\t 20.95187 Göte Andersson - 1919 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAABVTJA \t\n", "\t\t 20.342884 Göte Andersson - 1919 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAAA5NIA \t\n", "\t\t 18.98707 Göte Andersson - 1919 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAAA5NIQ \t\n", "\t\t 18.29131 Göte Andersson - 1919 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAABGTIw \t\n", "\n", " Helge Andersson 1899 http://www.wikidata.org/entity/Q121160714\n", "\t\t 18.763737 Karl Andersson - 1899 https://portrattarkiv.se/details/lzSYNK3ifoMAAAAAAAY-Hg \t\n", "\t\t 18.60417 Helge Andersson - 1899 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAAA5YWg \t\n", "\t\t 18.57062 Helge Andersson - 1899 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAAA5YXA \t\n", "\t\t 18.36661 Helge Andersson - 1899 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAAA5YWw \t\n", "\t\t 17.768845 Helge Karlsson - 1899 https://portrattarkiv.se/details/seuNpYu6DKAAAAAAAABN7A \t\n", "\n", " John Andersson 1914 http://www.wikidata.org/entity/Q121160715\n", "\t\t 16.870964 John Andersson - 1914 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAAA5Oyg \t\n", "\t\t 16.133652 John Andersson - 1914 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAAA5Oyw \t\n", "\t\t 15.980896 John Tjäder - 1914 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAABzoOQ \t\n", "\t\t 15.449126 John Nilsson - 1914 https://portrattarkiv.se/details/wbbNj8LLWMAAAAAAAAB_nA \t\n", "\t\t 14.804291 Maria Andersson - 1914 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAAA4-9w \t\n", "\n", " John Säwström 1886 http://www.wikidata.org/entity/Q121052611\n", "\t\t 14.925089 John Holmberg - 1886 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAABe3DA \t\n", "\t\t 14.837259 John Nilsson - 1886 https://portrattarkiv.se/details/wbbNj8LLWMAAAAAAAAAV9g \t\n", "\t\t 14.750305 John Lundberg - 1886 https://portrattarkiv.se/details/rkIpfGqHXrAAAAAAAACMSA \t\n", "\t\t 14.71521 John Nordlander - 1886 https://portrattarkiv.se/details/wbbNj8LLWMAAAAAAAAA91A \t\n", "\t\t 14.693815 John Johansson - 1886 https://portrattarkiv.se/details/Di4a42b6X_AAAAAAAAAnxQ \t\n", "\n", " Olof Hjärn 1903 http://www.wikidata.org/entity/Q121160747\n", "\t\t 13.323799 Olof Lignell - 1903 https://portrattarkiv.se/details/rkIpfGqHXrAAAAAAAAA2AQ \t\n", "\t\t 13.184191 Olof Olsson - 1903 https://portrattarkiv.se/details/vfWYdYT6EoAAAAAAAAAZ9w \t\n", "\t\t 12.992161 Carl-Olof Lindblom - 1903 https://portrattarkiv.se/details/rkIpfGqHXrAAAAAAAABH8Q \t\n", "\t\t 12.977806 Olof Widestrand - 1903 https://portrattarkiv.se/details/ciLMGSqrYjAAAAAAAABAtQ \t\n", "\t\t 12.959324 Olof Ekström - 1903 https://portrattarkiv.se/details/TR3yO_GV8eAAAAAAAABffQ \t\n", "\n", " Vallentin Hultén 1911 http://www.wikidata.org/entity/Q121160750\n", "\t\t 20.69121 Vivi-Anne Hultén - 1911 https://portrattarkiv.se/details/j6S8rJvx9lAAAAAAAAC3rQ \t\n", "\t\t 20.69121 Vivi-Anne Hultén - 1911 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAABO32A \t\n", "\t\t 20.670612 Gösta Hultén - 1911 https://portrattarkiv.se/details/seuNpYu6DKAAAAAAAAAK2A \t\n", "\t\t 20.37493 Vivi-Anne Hultén - 1911 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAACY1Aw \t\n", "\t\t 20.285803 Gösta Hultén - 1911 https://portrattarkiv.se/details/sj9PGLAlnmUAAAAAABVCiA \t\n" ] } ], "source": [ "SPAdetail = \"https://portrattarkiv.se/details/\"\n", "for index, row in SPAdf.iterrows():\n", " print(\"\\n\",row[\"search\"],row[\"item\"])\n", " encoded_body = json.dumps({\n", " \"limit\": \"5\",\n", " \"from\": \"0\",\n", " \"birthyear\":row[\"year\"],\n", " \"all\":row[\"search\"]\n", " })\n", " \n", " r = http.request('POST', url,\n", " headers={'Content-Type': 'application/json'},\n", " body=encoded_body)\n", "\n", " if r.status != 200:\n", " print(r.status)\n", " continue\n", " \n", " data = json.loads(r.data.decode('utf-8'),) \n", " urls = []\n", "\n", "\n", " for h in data[\"hits\"][\"hits\"]:\n", " id = h[\"_id\"]\n", " #print(h)\n", " source = h[\"_source\"]\n", " try:\n", " # urlPicture = urlbasePic + id\n", " # urls.append(urlPicture)\n", " score = h[\"_score\"]\n", " FirstName = source[\"FirstName\"]\n", " LastName = source[\"LastName\"]\n", " BirthYear = source[\"BirthYear\"]\n", " print(\"\\t\\t\",score,FirstName, \" \", LastName, \" - \", BirthYear,SPAdetail+id, \"\\t\", )\n", " except:\n", " print(\"Error\")\n" ] }, { "cell_type": "code", "execution_count": null, "id": "ccb654b5", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "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.13" } }, "nbformat": 4, "nbformat_minor": 5 }