q-add-draft.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "ef9bfee5-b14c-4228-a58c-f4ed99201288",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyscripts.inst_str_id import get_filter, oa_root"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "d473bac9-05da-48e9-8a5a-cd30464edeb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import os\n",
    "from pathlib import Path\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import polars as pl\n",
    "from dotenv import load_dotenv\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "5cf7a4b5-d56a-4256-9c2b-6f70a05ebf14",
   "metadata": {},
   "outputs": [],
   "source": [
    "work_filter = get_filter(\"14/works\")\n",
    "source_filter = get_filter(\"12/sources\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "fe996512-407a-406e-bc8a-659ddb29b2c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "def parse_id(col):\n",
    "    return col.str[22:].astype(np.uint64)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "922c984a-1fb0-44ca-b785-440b7d7e8c19",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_best_q_by_year():\n",
    "    return pl.read_csv(\"s3://tmp-borza-public-cyx/metascience/q-by-year.csv.gz\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0cd9053-a194-4d73-aed6-d824843a0a35",
   "metadata": {},
   "outputs": [],
   "source": [
    "source_base = load_sources()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "07111530-b7d4-41ae-8831-44d71b309abb",
   "metadata": {},
   "outputs": [],
   "source": [
    "qdf = get_best_q_by_year()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "f1cb8378-192a-4479-9b84-fdbcd1a53e9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "sodf = (\n",
    "    pd.read_csv(os.environ[\"OA_ROOT\"] + \"/entity-csvs/sources/ids.csv.gz\")\n",
    "    .assign(id=lambda df: df[\"openalex\"].pipe(parse_id))\n",
    "    .set_index(\"id\")\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "15a6c11b-6b9a-4920-b1ef-27b575d36b76",
   "metadata": {},
   "outputs": [],
   "source": [
    "puby = \"publication_year\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "30406c6e-667b-4037-ae34-8d5d8a681605",
   "metadata": {},
   "outputs": [],
   "source": [
    "_isc = \"issn\"\n",
    "_issns = pd.concat(\n",
    "    [\n",
    "        sodf[_isc].dropna().apply(json.loads).explode().reset_index(),\n",
    "        sodf[\"issn_l\"].dropna().rename(_isc).reset_index(),\n",
    "    ]\n",
    ").drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "f5453d25-3ac8-4361-a02d-267dcfa70668",
   "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>id</th>\n",
       "      <th>issn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>157242733</td>\n",
       "      <td>1876-214X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2765070212</td>\n",
       "      <td>1176-306X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2765070212</td>\n",
       "      <td>2324-3740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2764771425</td>\n",
       "      <td>0045-7736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2736747913</td>\n",
       "      <td>2442-8868</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124339</th>\n",
       "      <td>2764951900</td>\n",
       "      <td>0387-1185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124567</th>\n",
       "      <td>4210232958</td>\n",
       "      <td>2297-2633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128100</th>\n",
       "      <td>4306512670</td>\n",
       "      <td>1025-3076</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132798</th>\n",
       "      <td>4210212177</td>\n",
       "      <td>1767-4603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133258</th>\n",
       "      <td>4220651409</td>\n",
       "      <td>2521-7119</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>210838 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                id       issn\n",
       "0        157242733  1876-214X\n",
       "1       2765070212  1176-306X\n",
       "2       2765070212  2324-3740\n",
       "3       2764771425  0045-7736\n",
       "4       2736747913  2442-8868\n",
       "...            ...        ...\n",
       "124339  2764951900  0387-1185\n",
       "124567  4210232958  2297-2633\n",
       "128100  4306512670  1025-3076\n",
       "132798  4210212177  1767-4603\n",
       "133258  4220651409  2521-7119\n",
       "\n",
       "[210838 rows x 2 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "_issns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "07e43f23-5bf6-49de-a8c2-e5412aca9f9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "q_matched_df = (\n",
    "    get_best_q_by_year()\n",
    "    .select(\n",
    "        [\n",
    "            pl.col(_isc),\n",
    "            pl.col(\"year\").cast(pl.UInt16).alias(puby),\n",
    "            pl.col(\"best_q\").str.slice(1, None).cast(pl.UInt8),\n",
    "        ]\n",
    "    )\n",
    "    .join(pl.from_pandas(_issns).select([\"id\", pl.col(_isc)]), on=_isc)\n",
    "    .drop(_isc)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "88bfc81e-b1fd-41e1-b1ab-f843f12d505c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "247it [07:52,  1.91s/it]\n"
     ]
    }
   ],
   "source": [
    "w_dfs = []\n",
    "for wdf in tqdm(\n",
    "    pd.read_csv(\n",
    "        os.environ[\"OA_ROOT\"] + \"/entity-csvs/works/main.csv.gz\",\n",
    "        chunksize=1_000_000,\n",
    "        usecols=[\"id\", puby],\n",
    "    )\n",
    "):\n",
    "    w_dfs.append(\n",
    "        pl.from_pandas(\n",
    "            wdf.dropna().assign(id=lambda df: df[\"id\"].pipe(parse_id)),\n",
    "            schema_overrides={puby: pl.UInt16},\n",
    "        )\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "5aac1f0d-665a-4c56-adb7-ab81073ad80d",
   "metadata": {},
   "outputs": [],
   "source": [
    "full_ywdf = pl.concat(w_dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "2bfa9c43-7984-4d67-aad3-56881bbc8a65",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "0it [00:00, ?it/s]\n"
     ]
    }
   ],
   "source": [
    "for lodf in tqdm(\n",
    "    pd.read_csv(\n",
    "        os.environ[\"OA_ROOT\"] + \"/entity-csvs/works/locations.csv.gz\",\n",
    "        chunksize=100_000,\n",
    "        usecols=[\"parent_id\", \"source\"],\n",
    "    )\n",
    "):\n",
    "    break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "3015ea17-ede1-4142-aaca-3a841067814a",
   "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>parent_id</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2027675404</td>\n",
       "      <td>4210224251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2027678827</td>\n",
       "      <td>186920367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2027678827</td>\n",
       "      <td>4306525036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2027686561</td>\n",
       "      <td>154037165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2027694283</td>\n",
       "      <td>4210203914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99995</th>\n",
       "      <td>3009967627</td>\n",
       "      <td>4210220205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99996</th>\n",
       "      <td>3009992470</td>\n",
       "      <td>59624048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99997</th>\n",
       "      <td>3009992470</td>\n",
       "      <td>4306525036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99998</th>\n",
       "      <td>3010000965</td>\n",
       "      <td>133005937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99999</th>\n",
       "      <td>3010000965</td>\n",
       "      <td>4306525036</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>89698 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        parent_id      source\n",
       "10     2027675404  4210224251\n",
       "11     2027678827   186920367\n",
       "12     2027678827  4306525036\n",
       "13     2027686561   154037165\n",
       "14     2027694283  4210203914\n",
       "...           ...         ...\n",
       "99995  3009967627  4210220205\n",
       "99996  3009992470    59624048\n",
       "99997  3009992470  4306525036\n",
       "99998  3010000965   133005937\n",
       "99999  3010000965  4306525036\n",
       "\n",
       "[89698 rows x 2 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lodf.dropna().apply(parse_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "ce3cfeef-d595-44f3-997a-54f8ee6bae25",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0          157242733\n",
       "1         2765070212\n",
       "2         2765070212\n",
       "3         2764771425\n",
       "4         2736747913\n",
       "             ...    \n",
       "124339    2764951900\n",
       "124567    4210232958\n",
       "128100    4306512670\n",
       "132798    4210212177\n",
       "133258    4220651409\n",
       "Name: openalex, Length: 210838, dtype: uint64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "_issns[\"openalex\"].pipe(parse_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "95563434-a617-43b8-85a7-603ea0c22ed7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (3_774_225, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>year</th><th>best_q</th><th>openalex</th></tr><tr><td>u16</td><td>u8</td><td>str</td></tr></thead><tbody><tr><td>1950</td><td>3</td><td>&quot;https://openal…</td></tr><tr><td>1950</td><td>3</td><td>&quot;https://openal…</td></tr><tr><td>1950</td><td>1</td><td>&quot;https://openal…</td></tr><tr><td>1950</td><td>1</td><td>&quot;https://openal…</td></tr><tr><td>1950</td><td>4</td><td>&quot;https://openal…</td></tr><tr><td>1950</td><td>4</td><td>&quot;https://openal…</td></tr><tr><td>1950</td><td>1</td><td>&quot;https://openal…</td></tr><tr><td>1950</td><td>1</td><td>&quot;https://openal…</td></tr><tr><td>1950</td><td>1</td><td>&quot;https://openal…</td></tr><tr><td>1950</td><td>1</td><td>&quot;https://openal…</td></tr><tr><td>1950</td><td>4</td><td>&quot;https://openal…</td></tr><tr><td>1950</td><td>3</td><td>&quot;https://openal…</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2024</td><td>4</td><td>&quot;https://openal…</td></tr><tr><td>2024</td><td>4</td><td>&quot;https://openal…</td></tr><tr><td>2024</td><td>3</td><td>&quot;https://openal…</td></tr><tr><td>2024</td><td>3</td><td>&quot;https://openal…</td></tr><tr><td>2024</td><td>3</td><td>&quot;https://openal…</td></tr><tr><td>2024</td><td>3</td><td>&quot;https://openal…</td></tr><tr><td>2024</td><td>3</td><td>&quot;https://openal…</td></tr><tr><td>2024</td><td>3</td><td>&quot;https://openal…</td></tr><tr><td>2024</td><td>4</td><td>&quot;https://openal…</td></tr><tr><td>2024</td><td>4</td><td>&quot;https://openal…</td></tr><tr><td>2024</td><td>3</td><td>&quot;https://openal…</td></tr><tr><td>2024</td><td>3</td><td>&quot;https://openal…</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (3_774_225, 3)\n",
       "┌──────┬────────┬──────────────────────────────────┐\n",
       "│ year ┆ best_q ┆ openalex                         │\n",
       "│ ---  ┆ ---    ┆ ---                              │\n",
       "│ u16  ┆ u8     ┆ str                              │\n",
       "╞══════╪════════╪══════════════════════════════════╡\n",
       "│ 1950 ┆ 3      ┆ https://openalex.org/S168850678  │\n",
       "│ 1950 ┆ 3      ┆ https://openalex.org/S168850678  │\n",
       "│ 1950 ┆ 1      ┆ https://openalex.org/S143903698  │\n",
       "│ 1950 ┆ 1      ┆ https://openalex.org/S143903698  │\n",
       "│ 1950 ┆ 4      ┆ https://openalex.org/S4210205887 │\n",
       "│ …    ┆ …      ┆ …                                │\n",
       "│ 2024 ┆ 3      ┆ https://openalex.org/S4210180931 │\n",
       "│ 2024 ┆ 4      ┆ https://openalex.org/S4210203330 │\n",
       "│ 2024 ┆ 4      ┆ https://openalex.org/S4210203330 │\n",
       "│ 2024 ┆ 3      ┆ https://openalex.org/S4210175667 │\n",
       "│ 2024 ┆ 3      ┆ https://openalex.org/S4210175667 │\n",
       "└──────┴────────┴──────────────────────────────────┘"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q_matched_df"
   ]
  }
 ],
 "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}