005_add_wikidata_country.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "7e3d6ec9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "892a339c",
   "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>university</th>\n",
       "      <th>year</th>\n",
       "      <th>rank_display</th>\n",
       "      <th>score</th>\n",
       "      <th>link</th>\n",
       "      <th>country</th>\n",
       "      <th>city</th>\n",
       "      <th>region</th>\n",
       "      <th>logo</th>\n",
       "      <th>type</th>\n",
       "      <th>research_output</th>\n",
       "      <th>student_faculty_ratio</th>\n",
       "      <th>international_students</th>\n",
       "      <th>size</th>\n",
       "      <th>faculty_count</th>\n",
       "      <th>wikidata_entity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Massachusetts Institute of Technology (MIT)</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>https://www.topuniversities.com/universities/m...</td>\n",
       "      <td>United States</td>\n",
       "      <td>Cambridge</td>\n",
       "      <td>North America</td>\n",
       "      <td>https://www.topuniversities.com/sites/default/...</td>\n",
       "      <td>Private</td>\n",
       "      <td>Very High</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3,730</td>\n",
       "      <td>M</td>\n",
       "      <td>3,065</td>\n",
       "      <td>http://www.wikidata.org/entity/Q49108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Stanford University</td>\n",
       "      <td>2017</td>\n",
       "      <td>2</td>\n",
       "      <td>98.7</td>\n",
       "      <td>https://www.topuniversities.com/universities/s...</td>\n",
       "      <td>United States</td>\n",
       "      <td>Stanford</td>\n",
       "      <td>North America</td>\n",
       "      <td>https://www.topuniversities.com/sites/default/...</td>\n",
       "      <td>Private</td>\n",
       "      <td>Very High</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3,879</td>\n",
       "      <td>L</td>\n",
       "      <td>4,725</td>\n",
       "      <td>http://www.wikidata.org/entity/Q41506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Harvard University</td>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>98.3</td>\n",
       "      <td>https://www.topuniversities.com/universities/h...</td>\n",
       "      <td>United States</td>\n",
       "      <td>Cambridge</td>\n",
       "      <td>North America</td>\n",
       "      <td>https://www.topuniversities.com/sites/default/...</td>\n",
       "      <td>Private</td>\n",
       "      <td>Very High</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5,877</td>\n",
       "      <td>L</td>\n",
       "      <td>4,646</td>\n",
       "      <td>http://www.wikidata.org/entity/Q13371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>University of Cambridge</td>\n",
       "      <td>2017</td>\n",
       "      <td>4</td>\n",
       "      <td>97.2</td>\n",
       "      <td>https://www.topuniversities.com/universities/u...</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>Cambridge</td>\n",
       "      <td>Europe</td>\n",
       "      <td>https://www.topuniversities.com/sites/default/...</td>\n",
       "      <td>Public</td>\n",
       "      <td>Very high</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7,925</td>\n",
       "      <td>L</td>\n",
       "      <td>5,800</td>\n",
       "      <td>http://www.wikidata.org/entity/Q35794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California Institute of Technology (Caltech)</td>\n",
       "      <td>2017</td>\n",
       "      <td>5</td>\n",
       "      <td>96.9</td>\n",
       "      <td>https://www.topuniversities.com/universities/c...</td>\n",
       "      <td>United States</td>\n",
       "      <td>Pasadena</td>\n",
       "      <td>North America</td>\n",
       "      <td>https://www.topuniversities.com/sites/default/...</td>\n",
       "      <td>Private</td>\n",
       "      <td>Very High</td>\n",
       "      <td>2.0</td>\n",
       "      <td>692</td>\n",
       "      <td>S</td>\n",
       "      <td>968</td>\n",
       "      <td>http://www.wikidata.org/entity/Q161562</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     university  year rank_display  score  \\\n",
       "0  Massachusetts Institute of Technology (MIT)   2017            1  100.0   \n",
       "1                           Stanford University  2017            2   98.7   \n",
       "2                            Harvard University  2017            3   98.3   \n",
       "3                       University of Cambridge  2017            4   97.2   \n",
       "4  California Institute of Technology (Caltech)  2017            5   96.9   \n",
       "\n",
       "                                                link         country  \\\n",
       "0  https://www.topuniversities.com/universities/m...   United States   \n",
       "1  https://www.topuniversities.com/universities/s...   United States   \n",
       "2  https://www.topuniversities.com/universities/h...   United States   \n",
       "3  https://www.topuniversities.com/universities/u...  United Kingdom   \n",
       "4  https://www.topuniversities.com/universities/c...   United States   \n",
       "\n",
       "        city         region  \\\n",
       "0  Cambridge  North America   \n",
       "1   Stanford  North America   \n",
       "2  Cambridge  North America   \n",
       "3  Cambridge         Europe   \n",
       "4   Pasadena  North America   \n",
       "\n",
       "                                                logo     type research_output  \\\n",
       "0  https://www.topuniversities.com/sites/default/...  Private       Very High   \n",
       "1  https://www.topuniversities.com/sites/default/...  Private       Very High   \n",
       "2  https://www.topuniversities.com/sites/default/...  Private       Very High   \n",
       "3  https://www.topuniversities.com/sites/default/...   Public       Very high   \n",
       "4  https://www.topuniversities.com/sites/default/...  Private       Very High   \n",
       "\n",
       "   student_faculty_ratio international_students size faculty_count  \\\n",
       "0                    4.0                  3,730    M         3,065   \n",
       "1                    3.0                  3,879    L         4,725   \n",
       "2                    5.0                  5,877    L         4,646   \n",
       "3                    4.0                  7,925    L         5,800   \n",
       "4                    2.0                    692    S           968   \n",
       "\n",
       "                          wikidata_entity  \n",
       "0   http://www.wikidata.org/entity/Q49108  \n",
       "1   http://www.wikidata.org/entity/Q41506  \n",
       "2   http://www.wikidata.org/entity/Q13371  \n",
       "3   http://www.wikidata.org/entity/Q35794  \n",
       "4  http://www.wikidata.org/entity/Q161562  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_005 = pd.read_csv(\"semweb-data-005.csv\")\n",
    "df_005[\"country\"] = df_005[\"country\"].apply(str)\n",
    "df_005[\"city\"] = df_005[\"city\"].apply(str)\n",
    "df_005.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "id": "31b6357f",
   "metadata": {},
   "outputs": [],
   "source": [
    "country_conv = {\n",
    "    \"China (Mainland)\": \"http://www.wikidata.org/entity/Q148\",\n",
    "    \"Hong Kong SAR\": \"http://www.wikidata.org/entity/Q8646\",\n",
    "    \"Iran, Islamic Republic of\": \"http://www.wikidata.org/entity/Q794\",\n",
    "    \"Macau SAR\": \"http://www.wikidata.org/entity/Q14773\",\n",
    "    \"Palestinian Territory, Occupied\": \"http://www.wikidata.org/entity/Q219060\",\n",
    "    \"Puerto Rico\": \"http://www.wikidata.org/entity/Q1183\",\n",
    "}\n",
    "\n",
    "def get_country_entity(x):\n",
    "    if x in list(wikidata_countries[\"countryLabel\"]):\n",
    "        return wikidata_countries.loc[wikidata_countries['countryLabel'] == x][\"country\"].values[0]\n",
    "    \n",
    "    ccs = None\n",
    "    for index, row in wikidata_countries.iterrows():\n",
    "        if x in row[\"countryAltLabelList\"]:\n",
    "            return row[\"country\"]\n",
    "    \n",
    "    if x in country_conv.keys():\n",
    "        return country_conv[x]\n",
    "    \n",
    "    return None\n",
    "    \n",
    "# print(get_country_entity(\"United Kingdom\"))\n",
    "# print(get_country_entity(\"United States\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "id": "717da180",
   "metadata": {},
   "outputs": [],
   "source": [
    "# add country entity\n",
    "df_005[\"wikidata_country\"] = df_005[\"country\"].apply(get_country_entity)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "bd8906bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "for c in np.unique(df_005[df_005['wikidata_country'].isnull()][\"country\"]):\n",
    "    print(c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "201b8e8a",
   "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>university</th>\n",
       "      <th>year</th>\n",
       "      <th>rank_display</th>\n",
       "      <th>score</th>\n",
       "      <th>link</th>\n",
       "      <th>country</th>\n",
       "      <th>city</th>\n",
       "      <th>region</th>\n",
       "      <th>logo</th>\n",
       "      <th>type</th>\n",
       "      <th>research_output</th>\n",
       "      <th>student_faculty_ratio</th>\n",
       "      <th>international_students</th>\n",
       "      <th>size</th>\n",
       "      <th>faculty_count</th>\n",
       "      <th>wikidata_entity</th>\n",
       "      <th>wikidata_country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [university, year, rank_display, score, link, country, city, region, logo, type, research_output, student_faculty_ratio, international_students, size, faculty_count, wikidata_entity, wikidata_country]\n",
       "Index: []"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_005[df_005['wikidata_country'].isnull()].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "6044f8f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# export\n",
    "df_005.to_csv(\"semweb-data-006.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "01ddce1b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.9.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}