wiki_names_variants.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "830f5eae",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.insert(0, '/projekti/wikidata')\n",
    "import os\n",
    "from collections import defaultdict, Counter\n",
    "from text_utils import remove_reg, tokenise, get_provenance\n",
    "from itertools import combinations, product\n",
    "import json\n",
    "import numpy as np\n",
    "from tqdm.auto import tqdm\n",
    "from wiki_labels import qid_lab_get, qid_lab\n",
    "import gzip\n",
    "import pickle\n",
    "\n",
    "#print(qid_lab_get(177053, include_alt=True)['mr'])\n",
    "#print(qid_lab_get(191701, include_alt=True)['mba'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "98135616-8a7b-47af-8949-9bed8be368d3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Counter()"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# dic = pickle.load(open('/projekti/mondodb/lists/wiki_freq_dict.pickle', 'rb'))\n",
    "dic['hr_Latn_dae']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e8bd448b-664a-4a13-83a3-88bac998b1a4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'wiki_id': 'per'}"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "named_ent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69522b40-4482-4c8e-b154-806b26f07b28",
   "metadata": {},
   "outputs": [],
   "source": [
    "fo = gzip.open('/projekti/mondodb/lists/wikiname.tsv.gz', 'wt')\n",
    "for l in tqdm(gzip.open('/backup/wikidata/wikiname.jsonl.gz', 'rt'), total=854769):\n",
    "    j = json.loads(l)\n",
    "    wid = int(j['wiki_id'][1:])\n",
    "    labs = qid_lab_get(wid, include_alt=True, return_alt=True)\n",
    "    for lab, langs in labs.items():\n",
    "        for lang in langs:\n",
    "            lang = lang[0]\n",
    "            if '_' in lang:\n",
    "                lang = lang.split('_')[0]\n",
    "            elif '-' in lang:\n",
    "                lang = lang.split('-')[0]\n",
    "            prov = get_provenance(lab, lang, no_countries=True)\n",
    "            t = j['type'].replace('N.', '').lower()\n",
    "            if prov:\n",
    "                fo.write(f'{t}\\t{lab}\\t{prov[0]}\\n')\n",
    "\n",
    "    #w1, w2 = qid_lab_get(w1), qid_lab_get(w2)\n",
    "fo.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b14fecbd",
   "metadata": {},
   "outputs": [],
   "source": [
    "pairs = Counter()\n",
    "for l in tqdm(gzip.open('/backup/wikidata/wikiname.jsonl', 'rt'), total=854769):\n",
    "    j = json.loads(l)\n",
    "    sim = [int(a.replace('WIKI_Q', '').replace('Q', '')) for a in j['variants']+[j['wiki_id']] if 'P' not in a]\n",
    "    for w1, w2 in combinations(sim, 2):\n",
    "        w1, w2 = qid_lab_get(w1), qid_lab_get(w2)\n",
    "        for v1, v2 in product(w1.keys(), w2.keys()):\n",
    "            if v1<v2 and w1[v1].intersection(w2[v2]):\n",
    "                pairs[(v1, v2)] += 1\n",
    "    #break\n",
    "len(pairs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ff9e7ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "import oracledb\n",
    "\n",
    "tls = 'db202201301230_high'\n",
    "tls = 'db202201301230_high'\n",
    "psw = open('/projekti/wikidata/orapsw.txt').read()\n",
    "connection = oracledb.connect(user='mondo', password=psw, dsn=tls, encoding=\"UTF-8\",\n",
    "                                   config_dir='/usr/lib/oracle/21/client64/lib/network/admin/',\n",
    "                              wallet_location='/usr/lib/oracle/21/client64/lib/network/admin/', wallet_password=psw)\n",
    "cur = connection.cursor()\n",
    "sql = 'create table wiki_samaas (w1 varchar2(100), w2 varchar2(100), freq number(7,0))'\n",
    "#NAME2NAME BIBLICAL_TRANS_BIBLE BIBLICAL_NAME_TRANSLATION\n",
    "t = cur.execute(sql)\n",
    "for (n1, n2), v in pairs.items():\n",
    "    cur.execute('insert into wiki_samaas values (:1, :2, :3)', (n1, n2, v))\n",
    "\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f452e216-2a40-4d87-a9ac-9a157908317a",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"alter table name2name add wiki_sameas number (5,0)\")\n",
    "cur.execute(\"\"\"MERGE into name2name nn\n",
    "using (select * from wiki_samaas) B\n",
    "on (B.w1=nn.N1 and B.w2=nn.N2)\n",
    "when MATCHED then update set wiki_sameas = B.freq\n",
    "when not MATCHED then insert (N1, N2, wiki_sameas) values (b.w1, b.w2, b.freq)\n",
    "\"\"\")\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92a12e9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "toks = defaultdict(Counter)\n",
    "for tok, v in tqdm(names.items(), total=len(names)):\n",
    "    for t_prov, cnt in v.items():\n",
    "        src, t, lang, script, cc = t_prov.split('_')\n",
    "        tok = tok.replace('_', ' ')\n",
    "        if len(tok)>0:\n",
    "            toks[f'{tok}_{lang}_{script}'][(src, t)] += cnt\n",
    "            if tok[-1] == '.' and tok.count('.') == 1:\n",
    "                toks[f'{tok[:-1]}_{lang}_{script}'][(src+'.', t)] += cnt\n",
    "toks = dict(toks)\n",
    "toks['dr_en_Latn'], toks['mba_en_Latn']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4406ec7",
   "metadata": {},
   "outputs": [],
   "source": [
    "t2set = defaultdict(set)\n",
    "prov2set = defaultdict(set)\n",
    "\n",
    "for t, c in t2i.items():\n",
    "    t = t.split('_')\n",
    "    t2set['_'.join(t[:3])].add(c)    \n",
    "    prov2set['_'.join(t[1:3])].add('_'.join(t[1:]))\n",
    "t2set = dict(t2set)    \n",
    "i2t = {k: v for v, k in t2i.items()}\n",
    "prov2set = dict(prov2set)    \n",
    "i2t = {k: v for v, k in t2i.items()}\n",
    "t2set['tit_hr_Latn'], prov2set['de_Latn']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2589993",
   "metadata": {},
   "outputs": [],
   "source": [
    "freqs = {}\n",
    "for k, v in tqdm(toks.items(), total=len(toks)):\n",
    "    (tok, provt) = k.split('_', 1) \n",
    "    for (src, tip), cnt in v.items():\n",
    "        if (tok, provt) not in freqs:\n",
    "            freqs[(tok, provt)] = {'fn0': 0, 'fn1': 0, 'fn2': 0, 'ln': 0, 'tit': 0, }\n",
    "        freqs[(tok, provt)][tip] += cnt\n",
    "\n",
    "freqs[('prof', 'hr_Latn')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e2afcb1",
   "metadata": {},
   "outputs": [],
   "source": [
    "freqs2 = {}\n",
    "nema = Counter()\n",
    "for k, v in tqdm(freqs.items(), total=len(freqs)):\n",
    "    tok, provt = k\n",
    "    tips = [k for k in v if v[k]>0]\n",
    "    if provt in prov2set:\n",
    "        for tip in tips:\n",
    "            if tip == 'tit':\n",
    "                allprovs = [provt+'_']\n",
    "            else:\n",
    "                allprovs = [a for a in prov2set[provt] if not a.endswith('_')]\n",
    "            cnt = v[tip]\n",
    "            cnt_all = sum(v.values())\n",
    "            if cnt_all > 0: \n",
    "                for prov in allprovs:\n",
    "                    if f'{tip}_{prov}' in t2i:\n",
    "                        provi = t2i[f'{tip}_{prov}']\n",
    "                        freqs2[(tok, provi)] = (cnt, round(cnt*100 / cnt_all,2))\n",
    "                    else:\n",
    "                        nema[f'{tip}_{prov}'] += 1\n",
    "len(nema), sum(nema.values()), freqs2[('doctor', t2i['tit_en_Latn_'])], freqs2[('doctor', t2i['ln_en_Latn_US'])], freqs2[('prof', t2i['tit_hr_Latn_'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "566f552c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# to Oracle\n",
    "import cx_Oracle\n",
    "tls = 'db202201301230_high'\n",
    "connection = cx_Oracle.connect(user='mondo', password=open('orapsw.txt').read(),dsn=tls,encoding=\"UTF-8\")\n",
    "cur = connection.cursor()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb00b35f",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    cur.execute('drop table mondo.wiki_name_parts purge') \n",
    "except:\n",
    "    pass\n",
    "cur.execute(\"\"\"create table mondo.wiki_name_parts (token varchar2(200), lang varchar2(10), fn0 number(6, 0), fn1 number(6, 0), fn2 number(6, 0),\n",
    "            ln number(6, 0), tit number(6, 0), CONSTRAINT pk_locations PRIMARY KEY (token, lang)) ORGANIZATION INDEX\"\"\")\n",
    "print('loading ...')\n",
    "batch = []\n",
    "br = 0\n",
    "for (tok, lng), cnt in tqdm(freqs.items(), total=len(freqs)):\n",
    "    if len(bytes(tok, 'utf-8')) < 190:\n",
    "        batch.append((tok, lng, cnt['fn0'], cnt['fn1'], cnt['fn2'], cnt['ln'], cnt['tit'] ))\n",
    "    if len(batch)>10_048:\n",
    "        cur.executemany('insert into mondo.wiki_name_parts values (:1, :2, :3, :4, :5, :6, :7) ', batch) \n",
    "        connection.commit()\n",
    "        batch = []\n",
    "    br += 1\n",
    "cur.executemany('insert into mondo.wiki_name_parts values (:1, :2, :3, :4, :5, :6, :7) ', batch) \n",
    "connection.commit()\n",
    "print(br)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f1a45b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute('select count(*) from mondo.wiki_name_parts').fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb00e683",
   "metadata": {},
   "outputs": [],
   "source": [
    "# select m.n, (select t.t from  mondo.TYPEID t where t.id=m.t) t, c from MONDO.NAME_M m where n='dr' and\n",
    "# not exists (select 1 from mondo.TYPEID where id=m.t and regexp_like(t, 'title_.+') )  and\n",
    "# exists (select 1 from mondo.wiki_name_parts w where w.token=m.n) and\n",
    "# not exists (select 1 from mondo.wiki_name_parts w where w.token=m.n and w.PROV=m.T and w.RATIO>5);\n",
    "\n",
    "# AD HOC cleaning \n",
    "\n",
    "cur.execute(\"\"\"\n",
    "delete from MONDO.NAME_M m where\n",
    "not exists (select 1 from mondo.TYPEID where id=m.t and regexp_like(t, 'title_.+') )  and\n",
    "exists (select 1 from mondo.wiki_name_parts w where w.token=m.n) and\n",
    "not exists (select 1 from mondo.wiki_name_parts w where w.token=m.n and w.PROV=m.T and w.RATIO>5)\n",
    "\"\"\")\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d8471f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"select m.n, t.t from mondo.name_m m, mondo.TYPEID t where t.id=m.t and m.n='mr.'\").fetchall()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}