wrangle_abstract_dataset.ipynb

{
 "metadata": {
  "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.8.1-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python38164bitd84bd39789ae4619b6b50c356f0457fc",
   "display_name": "Python 3.8.1 64-bit"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "UNIV_COLUMNS = ['대학코드', '국가', '대학명', '파견구분', '협정형태', 'NUM_VISITS', \"BERT_SUM\", \"RNN_SUM\", 'COLLEGE_LINK']\n",
    "ABSTRACT_REVIEWS_COLUMNS = ['대학코드', '제목', '학과', '과정', '년도', 'BERT_SCORE', 'RNN_SCORE', 'REVIEW_LINK']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import all modules\n",
    "import os\n",
    "from os import fdopen, remove\n",
    "import glob\n",
    "from tempfile import mkstemp\n",
    "import shutil\n",
    "from shutil import move, copymode\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Wrangle dataset and make abstract review dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define path for sentiment labeled dataset\n",
    "# abstract: 짧은 후기 제목만 있는 텍스트 데이터셋\n",
    "# specific: 문단으로 구성된 텍스트 데이터셋\n",
    "\n",
    "abstract_sentiment = \"./data_sentiment/abstract\"\n",
    "specific_sentiment = \"./data_sentiment/specific\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "['./data_sentiment/abstract/730     _review_abstract_sentiment.csv',\n './data_sentiment/abstract/AR000004_review_abstract_sentiment.csv',\n './data_sentiment/abstract/AT000001_review_abstract_sentiment.csv',\n './data_sentiment/abstract/AT000002_review_abstract_sentiment.csv',\n './data_sentiment/abstract/AT000003_review_abstract_sentiment.csv']"
     },
     "metadata": {},
     "execution_count": 4
    }
   ],
   "source": [
    "# fetch all yonsei exchange review text datasets for each foreign universities\n",
    "abstract_yonsei_reviews = glob.glob(f\"{abstract_sentiment}/*.csv\")\n",
    "abstract_yonsei_reviews.sort()\n",
    "abstract_yonsei_reviews[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "CN000022_review_abstract_sentiment\nCN000022\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "    Unnamed: 0  No             제목      학과  과정    년도  \\\n13          13   5          북경대학교  중어중문학과  학부  2015   \n3            3  15     북경대학교 교환생활  중어중문학과  학부  2018   \n1            1  17      베이징대 교환생활  중어중문학과  학부  2019   \n14          14   4          북경대학교  중어중문학과  학부  2015   \n12          12   6  북경대학교에서의 한 학기    경영학과  학부  2016   \n\n                                             href  BERT_SCORE  RNN_SCORE  \n13  /partner/expReport.asp?id=12122&page=2&bgbn=R           1   0.659739  \n3   /partner/expReport.asp?id=15513&page=1&bgbn=R           0   0.602057  \n1   /partner/expReport.asp?id=16194&page=1&bgbn=R           0   0.484792  \n14  /partner/expReport.asp?id=12075&page=2&bgbn=R           1   0.659739  \n12  /partner/expReport.asp?id=13391&page=2&bgbn=R           1   0.750406  ",
      "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>Unnamed: 0</th>\n      <th>No</th>\n      <th>제목</th>\n      <th>학과</th>\n      <th>과정</th>\n      <th>년도</th>\n      <th>href</th>\n      <th>BERT_SCORE</th>\n      <th>RNN_SCORE</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>13</th>\n      <td>13</td>\n      <td>5</td>\n      <td>북경대학교</td>\n      <td>중어중문학과</td>\n      <td>학부</td>\n      <td>2015</td>\n      <td>/partner/expReport.asp?id=12122&amp;page=2&amp;bgbn=R</td>\n      <td>1</td>\n      <td>0.659739</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>3</td>\n      <td>15</td>\n      <td>북경대학교 교환생활</td>\n      <td>중어중문학과</td>\n      <td>학부</td>\n      <td>2018</td>\n      <td>/partner/expReport.asp?id=15513&amp;page=1&amp;bgbn=R</td>\n      <td>0</td>\n      <td>0.602057</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>17</td>\n      <td>베이징대 교환생활</td>\n      <td>중어중문학과</td>\n      <td>학부</td>\n      <td>2019</td>\n      <td>/partner/expReport.asp?id=16194&amp;page=1&amp;bgbn=R</td>\n      <td>0</td>\n      <td>0.484792</td>\n    </tr>\n    <tr>\n      <th>14</th>\n      <td>14</td>\n      <td>4</td>\n      <td>북경대학교</td>\n      <td>중어중문학과</td>\n      <td>학부</td>\n      <td>2015</td>\n      <td>/partner/expReport.asp?id=12075&amp;page=2&amp;bgbn=R</td>\n      <td>1</td>\n      <td>0.659739</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>12</td>\n      <td>6</td>\n      <td>북경대학교에서의 한 학기</td>\n      <td>경영학과</td>\n      <td>학부</td>\n      <td>2016</td>\n      <td>/partner/expReport.asp?id=13391&amp;page=2&amp;bgbn=R</td>\n      <td>1</td>\n      <td>0.750406</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "source": [
    "# get sample dataset\n",
    "sample_file = abstract_yonsei_reviews[70]\n",
    "\n",
    "# extract file name\n",
    "file_name = sample_file.split(\"/\")[-1]\n",
    "file_name_without_ext = file_name.split(\".\")[0]\n",
    "print(file_name_without_ext)\n",
    "\n",
    "# extract university code\n",
    "univ_code_sample = file_name_without_ext.split(\"_\")[0]\n",
    "print(univ_code_sample)\n",
    "\n",
    "# read sample dataset\n",
    "df_abstract = pd.read_csv(sample_file, encoding=\"utf-8\")\n",
    "df_abstract.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "       대학코드                    제목        학과  과정    년도  BERT_SCORE  RNN_SCORE  \\\n0  CN000022  PKU Exchange Program  언더우드국제대학  학부  2019           0   0.515649   \n1  CN000022             베이징대 교환생활    중어중문학과  학부  2019           0   0.484792   \n2  CN000022            북경대학교 교환학생    정치외교학과  학부  2019           0   0.664221   \n3  CN000022            북경대학교 교환생활    중어중문학과  학부  2018           0   0.602057   \n4  CN000022    북경대학교에서의 한 학기 교환생활      경영학과  학부  2018           1   0.697339   \n\n                                            href  \n0  /partner/expReport.asp?id=16451&page=1&bgbn=R  \n1  /partner/expReport.asp?id=16194&page=1&bgbn=R  \n2  /partner/expReport.asp?id=15979&page=1&bgbn=R  \n3  /partner/expReport.asp?id=15513&page=1&bgbn=R  \n4  /partner/expReport.asp?id=15209&page=1&bgbn=R  ",
      "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>대학코드</th>\n      <th>제목</th>\n      <th>학과</th>\n      <th>과정</th>\n      <th>년도</th>\n      <th>BERT_SCORE</th>\n      <th>RNN_SCORE</th>\n      <th>href</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>CN000022</td>\n      <td>PKU Exchange Program</td>\n      <td>언더우드국제대학</td>\n      <td>학부</td>\n      <td>2019</td>\n      <td>0</td>\n      <td>0.515649</td>\n      <td>/partner/expReport.asp?id=16451&amp;page=1&amp;bgbn=R</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>CN000022</td>\n      <td>베이징대 교환생활</td>\n      <td>중어중문학과</td>\n      <td>학부</td>\n      <td>2019</td>\n      <td>0</td>\n      <td>0.484792</td>\n      <td>/partner/expReport.asp?id=16194&amp;page=1&amp;bgbn=R</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>CN000022</td>\n      <td>북경대학교 교환학생</td>\n      <td>정치외교학과</td>\n      <td>학부</td>\n      <td>2019</td>\n      <td>0</td>\n      <td>0.664221</td>\n      <td>/partner/expReport.asp?id=15979&amp;page=1&amp;bgbn=R</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>CN000022</td>\n      <td>북경대학교 교환생활</td>\n      <td>중어중문학과</td>\n      <td>학부</td>\n      <td>2018</td>\n      <td>0</td>\n      <td>0.602057</td>\n      <td>/partner/expReport.asp?id=15513&amp;page=1&amp;bgbn=R</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>CN000022</td>\n      <td>북경대학교에서의 한 학기 교환생활</td>\n      <td>경영학과</td>\n      <td>학부</td>\n      <td>2018</td>\n      <td>1</td>\n      <td>0.697339</td>\n      <td>/partner/expReport.asp?id=15209&amp;page=1&amp;bgbn=R</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "source": [
    "df_abstract_review = df_abstract[[\"제목\", \"학과\", \"과정\", \"년도\", \"BERT_SCORE\", \"RNN_SCORE\", \"href\"]]\n",
    "df_abstract_review[\"대학코드\"] = univ_code_sample\n",
    "_abstract_review = df_abstract[[\"제목\", \"학과\", \"과정\", \"년도\", \"BERT_SCORE\", \"RNN_SCORE\", \"href\"]]\n",
    "df_abstract_review[\"대학코드\"] = univ_code_sample\n",
    "df_abstract_review = df_abstract_review[[\"대학코드\",\"제목\", \"학과\", \"과정\", \"년도\", \"BERT_SCORE\", \"RNN_SCORE\", \"href\"]]\n",
    "df_abstract_review.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "['대학코드', '제목', '학과', '과정', '년도', 'BERT_SCORE', 'RNN_SCORE', 'href']"
     },
     "metadata": {},
     "execution_count": 7
    }
   ],
   "source": [
    "df_abstract_review.columns.to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_abstract_review_df(UNIV_CODE):\n",
    "    # reading data source csv file, importing as dataframe\n",
    "    file_path = f\"./data_sentiment/abstract/{UNIV_CODE}_review_abstract_sentiment.csv\"\n",
    "    df_abstract = pd.read_csv(file_path, encoding=\"utf-8\")\n",
    "\n",
    "    # select columns from dataframe, create new dataframe\n",
    "    df_abstract_review = df_abstract[[\"제목\", \"학과\", \"과정\", \"년도\", \"BERT_SCORE\", \"RNN_SCORE\", \"href\"]]\n",
    "\n",
    "    # rename columns into review_link, make complete url\n",
    "    df_abstract_review.rename(columns = {'href':'REVIEW_LINK'}, inplace = True)\n",
    "    df_abstract_review[\"REVIEW_LINK\"] = \"http:https://oia.yonsei.ac.kr\" + df_abstract_review[\"REVIEW_LINK\"].astype(str)\n",
    "    \n",
    "    df_abstract_review[\"대학코드\"] = UNIV_CODE\n",
    "    df_abstract_review = df_abstract_review[[\"대학코드\",\"제목\", \"학과\", \"과정\", \"년도\", \"BERT_SCORE\", \"RNN_SCORE\", \"REVIEW_LINK\"]]\n",
    "    return df_abstract_review"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "470\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[       대학코드                제목               학과  과정    년도  BERT_SCORE  \\\n 0  730       리투아니아에서 행복했던 4개월  사회복지학과/언론홍보영상학부  학부  2019           1   \n \n    RNN_SCORE                                        REVIEW_LINK  \n 0   0.752021  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  ,\n        대학코드                    제목    학과  과정      년도  BERT_SCORE  RNN_SCORE  \\\n 0  AR000004  아르헨티나 교환학생(어학연수프로그램)  건축학과  학부  2013-1           0    0.39967   \n \n                                          REVIEW_LINK  \n 0  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  ,\n         대학코드                                        제목        학과  과정  \\\n 0   AT000001                       쿠프슈타인에서 노후 50년 땡겨쓰기      경영학과  학부   \n 1   AT000001                           평화로운 쿠프에서의 한 학기        경제  학부   \n 2   AT000001                   소중한 경험, 잊지 못할 기억, 쿠프슈타인   언론홍보영상학  학부   \n 3   AT000001                        언제나 마음속의 고향인 쿠프슈타인    중어중문학과  학부   \n 4   AT000001                    자연이 아름다운 쿠프슈타인에서의 한 학기    정치외교학과  학부   \n 5   AT000001                       여유로웠던 쿠프슈타인에서의 한 학기      행정학과  학부   \n 6   AT000001                             쿠프슈타인에서의 한 학기      경제학부  학부   \n 7   AT000001                     여유롭고 소중했던 쿠프슈타인에서의 시간      경제학부  학부   \n 8   AT000001                            마냥 아름다웠던 쿠프슈타인  언론홍보영상학부  학부   \n 9   AT000001      교환생활이 벌써 끝났다는게 믿기지 않아요. 다시 돌아가고 싶네요.    정치외교학과  학부   \n 10  AT000001                                유럽에서의 한 학기      경제학과  학부   \n 11  AT000001               한 여름밤의 꿈 같았던, 오스트리아에서의 한 학기      경제학부  학부   \n 12  AT000001                                 여유로웠던 한학기      경제학과  학부   \n 13  AT000001                              언제나 아련한 교환생활     응용통계학  학부   \n 14  AT000001                    포근한 추억이 된 쿠프슈타인에서의 한학기      경영학과  학부   \n 15  AT000001                             Kufstein에서의 삶      경영학과  학부   \n 16  AT000001                   대학생활 중 최고의 선택이었던 쿠프슈타인!    영어영문학과  학부   \n 17  AT000001           대학 생활 중 최고의 선택이었던 쿠프슈타인에서의 한 학기      경제학과  학부   \n 18  AT000001                            쿠프에서의 즐거웠던 5개월      경영학과  학부   \n 19  AT000001                특별한 모멘텀이 되어줬던 쿠프슈타인에서의 6개월      경제학과  학부   \n 20  AT000001                              쿠프슈타인에서의 한학기      정치외교  학부   \n 21  AT000001                                 쿠프에서의 한학기      경영학과  학부   \n 22  AT000001                            너무나 행복했던 쿠프슈타인      행정학과  학부   \n 23  AT000001                           쿠프슈타인에서의 느긋한 한때      행정학과  학부   \n 24  AT000001                     최고의 경험. 쿠프슈타인에서의 한학기.      경제학과  학부   \n 25  AT000001                     자연이 아름다운 쿠프슈타인에서의 한학기      경영학과  학부   \n 26  AT000001                            노을이 아름다운 그 곳..    사회복지학과  학부   \n 27  AT000001                   그리울 수 밖에 없는 곳, Kufstein      심리학과  학부   \n 28  AT000001               삶의 활력소가 되어준 Kufstein에서의 한학기      경영학과  학부   \n 29  AT000001                             kufstein에서의1년      경영학과  학부   \n 30  AT000001                       알프스의 향기가 있는 아름다운 곳.      경영학과  학부   \n 31  AT000001                           2009년 겨울의 쿠프슈타인      경영대학  학부   \n 32  AT000001                          알프스에서의 꿈같던 유럽생활~      경영학과  학부   \n 33  AT000001             평생 잊을 수 없는 추억을 만든 곳, Kufstein      경영학과  학부   \n 34  AT000001                           마음의 고향 Kufstein      경제학과  학부   \n 35  AT000001                  잊지못할 Kufstein에서의 교환학생 경험      경영학과  학부   \n 36  AT000001          평생 안고 갈 추억들을 쌓아 준 Kufstein에서의 1년      경영학과  학부   \n 37  AT000001                         FHS Kustein에서의 겨울      경영학과  학부   \n 38  AT000001               Keiser 산맥 으로 둘러 쌓인 Kufstein      경영학과  학부   \n 39  AT000001  Life in kufstein, Tirol, Austria, Europe      경영학과  학부   \n \n            년도  BERT_SCORE  RNN_SCORE  \\\n 0   2017-2018           0   0.416185   \n 1   2017ㅡ2018           1   0.816743   \n 2        2017           1   0.854657   \n 3        2017           1   0.834800   \n 4        2017           1   0.870668   \n 5        2017           1   0.816663   \n 6        2016           1   0.731432   \n 7        2016           1   0.852968   \n 8        2016           1   0.892930   \n 9        2016           1   0.946470   \n 10       2015           1   0.674954   \n 11       2016           1   0.804807   \n 12       2015           1   0.742174   \n 13       2014           1   0.904622   \n 14       2015           1   0.886201   \n 15       2014           1   0.871341   \n 16       2014           1   0.945168   \n 17       2014           1   0.936024   \n 18       2014           1   0.862567   \n 19  2013-2014           1   0.841384   \n 20  2013-2014           1   0.731432   \n 21       2013           1   0.731432   \n 22       2013           1   0.959038   \n 23       2012           1   0.575551   \n 24       2012           1   0.949966   \n 25  2011-2012           1   0.870668   \n 26       2011           1   0.926623   \n 27       2011           1   0.810447   \n 28  2010-2011           1   0.922544   \n 29       2010           0   0.731432   \n 30       2010           1   0.918543   \n 31       2009           1   0.684230   \n 32       2009           1   0.761829   \n 33       2008           1   0.970384   \n 34       2008           1   0.647233   \n 35       2006           0   0.910061   \n 36  2005-2006           1   0.718975   \n 37       2006           1   0.730913   \n 38  2005-2006           0   0.273242   \n 39       2006           1   0.521203   \n \n                                           REVIEW_LINK  \n 0   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 1   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 2   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 3   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 4   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 5   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 6   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 7   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 8   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 9   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 10  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 11  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 12  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 13  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 14  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 15  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 16  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 17  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 18  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 19  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 20  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 21  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 22  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 23  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 24  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 25  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 26  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 27  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 28  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 29  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 30  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 31  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 32  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 33  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 34  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 35  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 36  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 37  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 38  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n 39  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  ]"
     },
     "metadata": {},
     "execution_count": 9
    }
   ],
   "source": [
    "# listing up dataframes to concat\n",
    "list_df = []\n",
    "for csv_file in abstract_yonsei_reviews:\n",
    "    # extract file name\n",
    "    file_name = csv_file.split(\"/\")[-1]\n",
    "    file_name_without_ext = file_name.split(\".\")[0]\n",
    "\n",
    "    # extract university code\n",
    "    univ_code = file_name_without_ext.split(\"_\")[0]\n",
    "\n",
    "    # make dataframe based on university code\n",
    "    df_temp = make_abstract_review_df(univ_code)\n",
    "    list_df.append(df_temp)\n",
    "\n",
    "print(len(list_df))\n",
    "list_df[:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "11183\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "       대학코드                       제목               학과  과정         년도  \\\n0  730              리투아니아에서 행복했던 4개월  사회복지학과/언론홍보영상학부  학부       2019   \n0  AR000004     아르헨티나 교환학생(어학연수프로그램)             건축학과  학부     2013-1   \n0  AT000001      쿠프슈타인에서 노후 50년 땡겨쓰기             경영학과  학부  2017-2018   \n1  AT000001          평화로운 쿠프에서의 한 학기               경제  학부  2017ㅡ2018   \n2  AT000001  소중한 경험, 잊지 못할 기억, 쿠프슈타인          언론홍보영상학  학부       2017   \n3  AT000001       언제나 마음속의 고향인 쿠프슈타인           중어중문학과  학부       2017   \n4  AT000001   자연이 아름다운 쿠프슈타인에서의 한 학기           정치외교학과  학부       2017   \n5  AT000001      여유로웠던 쿠프슈타인에서의 한 학기             행정학과  학부       2017   \n6  AT000001            쿠프슈타인에서의 한 학기             경제학부  학부       2016   \n7  AT000001    여유롭고 소중했던 쿠프슈타인에서의 시간             경제학부  학부       2016   \n\n   BERT_SCORE  RNN_SCORE                                        REVIEW_LINK  \n0           1   0.752021  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n0           0   0.399670  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n0           0   0.416185  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n1           1   0.816743  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n2           1   0.854657  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n3           1   0.834800  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n4           1   0.870668  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n5           1   0.816663  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n6           1   0.731432  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n7           1   0.852968  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  ",
      "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>대학코드</th>\n      <th>제목</th>\n      <th>학과</th>\n      <th>과정</th>\n      <th>년도</th>\n      <th>BERT_SCORE</th>\n      <th>RNN_SCORE</th>\n      <th>REVIEW_LINK</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>730</td>\n      <td>리투아니아에서 행복했던 4개월</td>\n      <td>사회복지학과/언론홍보영상학부</td>\n      <td>학부</td>\n      <td>2019</td>\n      <td>1</td>\n      <td>0.752021</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>0</th>\n      <td>AR000004</td>\n      <td>아르헨티나 교환학생(어학연수프로그램)</td>\n      <td>건축학과</td>\n      <td>학부</td>\n      <td>2013-1</td>\n      <td>0</td>\n      <td>0.399670</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>0</th>\n      <td>AT000001</td>\n      <td>쿠프슈타인에서 노후 50년 땡겨쓰기</td>\n      <td>경영학과</td>\n      <td>학부</td>\n      <td>2017-2018</td>\n      <td>0</td>\n      <td>0.416185</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>AT000001</td>\n      <td>평화로운 쿠프에서의 한 학기</td>\n      <td>경제</td>\n      <td>학부</td>\n      <td>2017ㅡ2018</td>\n      <td>1</td>\n      <td>0.816743</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>AT000001</td>\n      <td>소중한 경험, 잊지 못할 기억, 쿠프슈타인</td>\n      <td>언론홍보영상학</td>\n      <td>학부</td>\n      <td>2017</td>\n      <td>1</td>\n      <td>0.854657</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>AT000001</td>\n      <td>언제나 마음속의 고향인 쿠프슈타인</td>\n      <td>중어중문학과</td>\n      <td>학부</td>\n      <td>2017</td>\n      <td>1</td>\n      <td>0.834800</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>AT000001</td>\n      <td>자연이 아름다운 쿠프슈타인에서의 한 학기</td>\n      <td>정치외교학과</td>\n      <td>학부</td>\n      <td>2017</td>\n      <td>1</td>\n      <td>0.870668</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>AT000001</td>\n      <td>여유로웠던 쿠프슈타인에서의 한 학기</td>\n      <td>행정학과</td>\n      <td>학부</td>\n      <td>2017</td>\n      <td>1</td>\n      <td>0.816663</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>AT000001</td>\n      <td>쿠프슈타인에서의 한 학기</td>\n      <td>경제학부</td>\n      <td>학부</td>\n      <td>2016</td>\n      <td>1</td>\n      <td>0.731432</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>AT000001</td>\n      <td>여유롭고 소중했던 쿠프슈타인에서의 시간</td>\n      <td>경제학부</td>\n      <td>학부</td>\n      <td>2016</td>\n      <td>1</td>\n      <td>0.852968</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 10
    }
   ],
   "source": [
    "# concat dataframes in the list\n",
    "df_abstract_wrangle = pd.concat(list_df)\n",
    "print(len(df_abstract_wrangle.index))\n",
    "df_abstract_wrangle.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "(11183, 8)\n"
    }
   ],
   "source": [
    "# check for duplicate rows\n",
    "df_abstract_wrangle = df_abstract_wrangle.drop_duplicates()\n",
    "print(df_abstract_wrangle.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# export wrangled df abstract concat dataframe\n",
    "df_abstract_wrangle.to_csv(\"./data_wrangled/df_abstract_wrangle.csv\",encoding=\"utf-8\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Wrangle Foreign Exchange University Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def yield_BERT_SUM(UNIV_CODE):\n",
    "    file_path = f\"./data_sentiment/abstract/{UNIV_CODE}_review_abstract_sentiment.csv\"\n",
    "    dataframe_input = pd.read_csv(file_path, encoding=\"utf-8\")\n",
    "    bert_sum = dataframe_input[\"BERT_SCORE\"].sum()\n",
    "    return bert_sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def yield_RNN_SUM(UNIV_CODE):\n",
    "    file_path = f\"./data_sentiment/abstract/{UNIV_CODE}_review_abstract_sentiment.csv\"\n",
    "    dataframe_input = pd.read_csv(file_path, encoding=\"utf-8\")\n",
    "    rnn_sum = dataframe_input[\"RNN_SCORE\"].sum()\n",
    "    return rnn_sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "def yield_no_of_students(UNIV_CODE):\n",
    "    dataframe_input = make_abstract_review_df(UNIV_CODE)\n",
    "    no_of_students = len(dataframe_input.index)\n",
    "    return no_of_students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "65\n49.14911192655563\n"
    }
   ],
   "source": [
    "# University of Copenhagen Satisfaction Level\n",
    "print(yield_BERT_SUM(\"DK000003\"))\n",
    "print(yield_RNN_SUM(\"DK000003\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "41\n43.059615552425385\n"
    }
   ],
   "source": [
    "# University of Hongkong Satisfaction Level\n",
    "print(yield_BERT_SUM(\"CN000016\"))\n",
    "print(yield_RNN_SUM(\"CN000016\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "     level_0  index   No                                                대학명  \\\n433      433     40   41                   University of Ulster-Jordanstown   \n420      420     27   28                                 University of Kent   \n118      118      4    5  Turku School of Economics and Business Adminis...   \n715      715    279  280                   Virginia Commonwealth University   \n27        27      0    1   FH Kufstein Tirol University of Applied Sciences   \n\n                 국가         파견구분      협정형태  \\\n433  UNITED KINGDOM         교환학생      ISEP   \n420  UNITED KINGDOM  방문학생SAP(정규)       SAF   \n118         FINLAND         교환학생      ISEP   \n715   UNITED STATES         교환학생  교환대학ISEP   \n27          AUSTRIA         교환학생      교환대학   \n\n                                             href visitation  \n433  /partner/expReport.asp?ucode=GB000023&bgbn=A       None  \n420  /partner/expReport.asp?ucode=GB000013&bgbn=A       None  \n118  /partner/expReport.asp?ucode=FI000006&bgbn=A       None  \n715  /partner/expReport.asp?ucode=US000258&bgbn=A     Exists  \n27   /partner/expReport.asp?ucode=AT000001&bgbn=A     Exists  ",
      "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>level_0</th>\n      <th>index</th>\n      <th>No</th>\n      <th>대학명</th>\n      <th>국가</th>\n      <th>파견구분</th>\n      <th>협정형태</th>\n      <th>href</th>\n      <th>visitation</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>433</th>\n      <td>433</td>\n      <td>40</td>\n      <td>41</td>\n      <td>University of Ulster-Jordanstown</td>\n      <td>UNITED KINGDOM</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>/partner/expReport.asp?ucode=GB000023&amp;bgbn=A</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>420</th>\n      <td>420</td>\n      <td>27</td>\n      <td>28</td>\n      <td>University of Kent</td>\n      <td>UNITED KINGDOM</td>\n      <td>방문학생SAP(정규)</td>\n      <td>SAF</td>\n      <td>/partner/expReport.asp?ucode=GB000013&amp;bgbn=A</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>118</th>\n      <td>118</td>\n      <td>4</td>\n      <td>5</td>\n      <td>Turku School of Economics and Business Adminis...</td>\n      <td>FINLAND</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>/partner/expReport.asp?ucode=FI000006&amp;bgbn=A</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>715</th>\n      <td>715</td>\n      <td>279</td>\n      <td>280</td>\n      <td>Virginia Commonwealth University</td>\n      <td>UNITED STATES</td>\n      <td>교환학생</td>\n      <td>교환대학ISEP</td>\n      <td>/partner/expReport.asp?ucode=US000258&amp;bgbn=A</td>\n      <td>Exists</td>\n    </tr>\n    <tr>\n      <th>27</th>\n      <td>27</td>\n      <td>0</td>\n      <td>1</td>\n      <td>FH Kufstein Tirol University of Applied Sciences</td>\n      <td>AUSTRIA</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>/partner/expReport.asp?ucode=AT000001&amp;bgbn=A</td>\n      <td>Exists</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 18
    }
   ],
   "source": [
    "# read university dataframe\n",
    "df_univ = pd.read_csv(\"./data/univ_db_full.csv\", encoding=\"utf-8\")\n",
    "df_univ.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   level_0  index  No                              대학명           국가  파견구분  \\\n0        0      0   1                 Kabul University  AFGHANISTAN  교환학생   \n1        1      0   1          Universidad Blas Pascal    ARGENTINA  교환학생   \n2        2      1   2  Universidad Catolica de Cordoba    ARGENTINA  교환학생   \n3        3      2   3           Universidad de Palermo    ARGENTINA  교환학생   \n4        4      3   4         Universidad del Salvador    ARGENTINA  교환학생   \n\n   협정형태                                          href visitation      대학코드  \n0  교환대학  /partner/expReport.asp?ucode=AF000001&bgbn=A       None  AF000001  \n1  ISEP  /partner/expReport.asp?ucode=AR000001&bgbn=A       None  AR000001  \n2  ISEP  /partner/expReport.asp?ucode=AR000002&bgbn=A       None  AR000002  \n3  ISEP  /partner/expReport.asp?ucode=AR000003&bgbn=A       None  AR000003  \n4  ISEP  /partner/expReport.asp?ucode=AR000004&bgbn=A     Exists  AR000004  ",
      "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>level_0</th>\n      <th>index</th>\n      <th>No</th>\n      <th>대학명</th>\n      <th>국가</th>\n      <th>파견구분</th>\n      <th>협정형태</th>\n      <th>href</th>\n      <th>visitation</th>\n      <th>대학코드</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>Kabul University</td>\n      <td>AFGHANISTAN</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>/partner/expReport.asp?ucode=AF000001&amp;bgbn=A</td>\n      <td>None</td>\n      <td>AF000001</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>0</td>\n      <td>1</td>\n      <td>Universidad Blas Pascal</td>\n      <td>ARGENTINA</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>/partner/expReport.asp?ucode=AR000001&amp;bgbn=A</td>\n      <td>None</td>\n      <td>AR000001</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>1</td>\n      <td>2</td>\n      <td>Universidad Catolica de Cordoba</td>\n      <td>ARGENTINA</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>/partner/expReport.asp?ucode=AR000002&amp;bgbn=A</td>\n      <td>None</td>\n      <td>AR000002</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>3</td>\n      <td>2</td>\n      <td>3</td>\n      <td>Universidad de Palermo</td>\n      <td>ARGENTINA</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>/partner/expReport.asp?ucode=AR000003&amp;bgbn=A</td>\n      <td>None</td>\n      <td>AR000003</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>4</td>\n      <td>3</td>\n      <td>4</td>\n      <td>Universidad del Salvador</td>\n      <td>ARGENTINA</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>/partner/expReport.asp?ucode=AR000004&amp;bgbn=A</td>\n      <td>Exists</td>\n      <td>AR000004</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 19
    }
   ],
   "source": [
    "# make column for university code\n",
    "series_query = df_univ[\"href\"].str.split(\"=\", expand=True)[1]\n",
    "series_univ_code = series_query.str.split(\"&\", expand=True)[0]\n",
    "df_univ[\"대학코드\"] = series_univ_code\n",
    "df_univ.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "       대학코드           국가                              대학명  파견구분      협정형태  \\\n0  AF000001  AFGHANISTAN                 Kabul University  교환학생      교환대학   \n1  AR000001    ARGENTINA          Universidad Blas Pascal  교환학생      ISEP   \n2  AR000002    ARGENTINA  Universidad Catolica de Cordoba  교환학생      ISEP   \n3  AR000003    ARGENTINA           Universidad de Palermo  교환학생      ISEP   \n4  AR000004    ARGENTINA         Universidad del Salvador  교환학생      ISEP   \n5  AR000005    ARGENTINA       University of Buenos Aires  교환학생      교환대학   \n6  AU000019    AUSTRALIA   Australian National University  교환학생      교환대학   \n7  AU000001    AUSTRALIA                  Bond University  교환학생      교환대학   \n8  AU000002    AUSTRALIA                Curtin University  교환학생  교환대학ISEP   \n9  AU000003    AUSTRALIA  Curtin University of Technology  교환학생       NaN   \n\n  visitation                                          href  \n0       None  /partner/expReport.asp?ucode=AF000001&bgbn=A  \n1       None  /partner/expReport.asp?ucode=AR000001&bgbn=A  \n2       None  /partner/expReport.asp?ucode=AR000002&bgbn=A  \n3       None  /partner/expReport.asp?ucode=AR000003&bgbn=A  \n4     Exists  /partner/expReport.asp?ucode=AR000004&bgbn=A  \n5       None  /partner/expReport.asp?ucode=AR000005&bgbn=A  \n6     Exists  /partner/expReport.asp?ucode=AU000019&bgbn=A  \n7     Exists  /partner/expReport.asp?ucode=AU000001&bgbn=A  \n8     Exists  /partner/expReport.asp?ucode=AU000002&bgbn=A  \n9     Exists  /partner/expReport.asp?ucode=AU000003&bgbn=A  ",
      "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>대학코드</th>\n      <th>국가</th>\n      <th>대학명</th>\n      <th>파견구분</th>\n      <th>협정형태</th>\n      <th>visitation</th>\n      <th>href</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>AF000001</td>\n      <td>AFGHANISTAN</td>\n      <td>Kabul University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>None</td>\n      <td>/partner/expReport.asp?ucode=AF000001&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>AR000001</td>\n      <td>ARGENTINA</td>\n      <td>Universidad Blas Pascal</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>None</td>\n      <td>/partner/expReport.asp?ucode=AR000001&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>AR000002</td>\n      <td>ARGENTINA</td>\n      <td>Universidad Catolica de Cordoba</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>None</td>\n      <td>/partner/expReport.asp?ucode=AR000002&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>AR000003</td>\n      <td>ARGENTINA</td>\n      <td>Universidad de Palermo</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>None</td>\n      <td>/partner/expReport.asp?ucode=AR000003&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>AR000004</td>\n      <td>ARGENTINA</td>\n      <td>Universidad del Salvador</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>Exists</td>\n      <td>/partner/expReport.asp?ucode=AR000004&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>AR000005</td>\n      <td>ARGENTINA</td>\n      <td>University of Buenos Aires</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>None</td>\n      <td>/partner/expReport.asp?ucode=AR000005&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>AU000019</td>\n      <td>AUSTRALIA</td>\n      <td>Australian National University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>Exists</td>\n      <td>/partner/expReport.asp?ucode=AU000019&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>AU000001</td>\n      <td>AUSTRALIA</td>\n      <td>Bond University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>Exists</td>\n      <td>/partner/expReport.asp?ucode=AU000001&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>AU000002</td>\n      <td>AUSTRALIA</td>\n      <td>Curtin University</td>\n      <td>교환학생</td>\n      <td>교환대학ISEP</td>\n      <td>Exists</td>\n      <td>/partner/expReport.asp?ucode=AU000002&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>AU000003</td>\n      <td>AUSTRALIA</td>\n      <td>Curtin University of Technology</td>\n      <td>교환학생</td>\n      <td>NaN</td>\n      <td>Exists</td>\n      <td>/partner/expReport.asp?ucode=AU000003&amp;bgbn=A</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 20
    }
   ],
   "source": [
    "# reorder columns, delete unnecessary columns\n",
    "df = df_univ[[\"대학코드\", \"국가\", \"대학명\", \"파견구분\", \"협정형태\", \"visitation\", \"href\"]]\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "       대학코드         국가                              대학명  파견구분      협정형태  \\\n4  AR000004  ARGENTINA         Universidad del Salvador  교환학생      ISEP   \n6  AU000019  AUSTRALIA   Australian National University  교환학생      교환대학   \n7  AU000001  AUSTRALIA                  Bond University  교환학생      교환대학   \n8  AU000002  AUSTRALIA                Curtin University  교환학생  교환대학ISEP   \n9  AU000003  AUSTRALIA  Curtin University of Technology  교환학생       NaN   \n\n  visitation                                          href  \n4     Exists  /partner/expReport.asp?ucode=AR000004&bgbn=A  \n6     Exists  /partner/expReport.asp?ucode=AU000019&bgbn=A  \n7     Exists  /partner/expReport.asp?ucode=AU000001&bgbn=A  \n8     Exists  /partner/expReport.asp?ucode=AU000002&bgbn=A  \n9     Exists  /partner/expReport.asp?ucode=AU000003&bgbn=A  ",
      "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>대학코드</th>\n      <th>국가</th>\n      <th>대학명</th>\n      <th>파견구분</th>\n      <th>협정형태</th>\n      <th>visitation</th>\n      <th>href</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>4</th>\n      <td>AR000004</td>\n      <td>ARGENTINA</td>\n      <td>Universidad del Salvador</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>Exists</td>\n      <td>/partner/expReport.asp?ucode=AR000004&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>AU000019</td>\n      <td>AUSTRALIA</td>\n      <td>Australian National University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>Exists</td>\n      <td>/partner/expReport.asp?ucode=AU000019&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>AU000001</td>\n      <td>AUSTRALIA</td>\n      <td>Bond University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>Exists</td>\n      <td>/partner/expReport.asp?ucode=AU000001&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>AU000002</td>\n      <td>AUSTRALIA</td>\n      <td>Curtin University</td>\n      <td>교환학생</td>\n      <td>교환대학ISEP</td>\n      <td>Exists</td>\n      <td>/partner/expReport.asp?ucode=AU000002&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>AU000003</td>\n      <td>AUSTRALIA</td>\n      <td>Curtin University of Technology</td>\n      <td>교환학생</td>\n      <td>NaN</td>\n      <td>Exists</td>\n      <td>/partner/expReport.asp?ucode=AU000003&amp;bgbn=A</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 21
    }
   ],
   "source": [
    "# sort out unvisited universities\n",
    "df_ever_been = df.loc[df['visitation'] == \"Exists\"]\n",
    "df_ever_been.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "(470, 7)"
     },
     "metadata": {},
     "execution_count": 22
    }
   ],
   "source": [
    "# number of visited universities in Yonsei history\n",
    "df_ever_been.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# wrangle university dataframe\n",
    "def make_univ_df():\n",
    "    # read university dataset csv file\n",
    "    df_univ = pd.read_csv(\"./data/univ_db_full.csv\", encoding=\"utf-8\")\n",
    "\n",
    "    # get university code\n",
    "    series_query = df_univ[\"href\"].str.split(\"=\", expand=True)[1]\n",
    "    series_univ_code = series_query.str.split(\"&\", expand=True)[0]\n",
    "    df_univ[\"대학코드\"] = series_univ_code\n",
    "    \n",
    "    # Making column values for BERT_SUM, RNN_SUM, STUDENT_NO\n",
    "    list_bert_sum = []\n",
    "    list_rnn_sum = []\n",
    "    list_student_no = []\n",
    "    \n",
    "    # iterrating over rows of university datframe\n",
    "    for index, row in df_univ.iterrows():\n",
    "\n",
    "        # if university is not ever visited, input NaN values\n",
    "        if row[\"visitation\"] == \"None\":\n",
    "            list_bert_sum.append(np.NaN)\n",
    "            list_rnn_sum.append(np.NaN)\n",
    "            list_student_no.append(np.NaN)\n",
    "\n",
    "        # if university is visited, input BERT_SCORE, RNN_SCORE's sum and input number of visitations\n",
    "        if row[\"visitation\"] == \"Exists\":\n",
    "            list_bert_sum.append(yield_BERT_SUM(row[\"대학코드\"]))\n",
    "            list_rnn_sum.append(yield_RNN_SUM(row[\"대학코드\"]))\n",
    "            list_student_no.append(yield_no_of_students(row[\"대학코드\"]))\n",
    "    \n",
    "    # Make Columns\n",
    "    df_univ[\"BERT_SUM\"] = list_bert_sum\n",
    "    df_univ[\"RNN_SUM\"] = list_rnn_sum\n",
    "    df_univ[\"NUM_VISITS\"] = list_student_no\n",
    "    \n",
    "    # Select and order columns\n",
    "    df = df_univ[[\"대학코드\", \"국가\", \"대학명\", \"파견구분\", \"협정형태\", \"NUM_VISITS\", \"BERT_SUM\", \"RNN_SUM\", \"href\"]]\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_univ_wrangled = make_univ_df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "(745, 9)\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "       대학코드           국가                              대학명  파견구분      협정형태  \\\n0  AF000001  AFGHANISTAN                 Kabul University  교환학생      교환대학   \n1  AR000001    ARGENTINA          Universidad Blas Pascal  교환학생      ISEP   \n2  AR000002    ARGENTINA  Universidad Catolica de Cordoba  교환학생      ISEP   \n3  AR000003    ARGENTINA           Universidad de Palermo  교환학생      ISEP   \n4  AR000004    ARGENTINA         Universidad del Salvador  교환학생      ISEP   \n5  AR000005    ARGENTINA       University of Buenos Aires  교환학생      교환대학   \n6  AU000019    AUSTRALIA   Australian National University  교환학생      교환대학   \n7  AU000001    AUSTRALIA                  Bond University  교환학생      교환대학   \n8  AU000002    AUSTRALIA                Curtin University  교환학생  교환대학ISEP   \n9  AU000003    AUSTRALIA  Curtin University of Technology  교환학생       NaN   \n\n   NUM_VISITS  BERT_SUM    RNN_SUM  \\\n0         NaN       NaN        NaN   \n1         NaN       NaN        NaN   \n2         NaN       NaN        NaN   \n3         NaN       NaN        NaN   \n4         1.0       0.0   0.399670   \n5         NaN       NaN        NaN   \n6        27.0      17.0  17.952732   \n7        19.0      16.0  13.359809   \n8         7.0       5.0   4.657787   \n9        16.0      14.0   9.696670   \n\n                                           href  \n0  /partner/expReport.asp?ucode=AF000001&bgbn=A  \n1  /partner/expReport.asp?ucode=AR000001&bgbn=A  \n2  /partner/expReport.asp?ucode=AR000002&bgbn=A  \n3  /partner/expReport.asp?ucode=AR000003&bgbn=A  \n4  /partner/expReport.asp?ucode=AR000004&bgbn=A  \n5  /partner/expReport.asp?ucode=AR000005&bgbn=A  \n6  /partner/expReport.asp?ucode=AU000019&bgbn=A  \n7  /partner/expReport.asp?ucode=AU000001&bgbn=A  \n8  /partner/expReport.asp?ucode=AU000002&bgbn=A  \n9  /partner/expReport.asp?ucode=AU000003&bgbn=A  ",
      "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>대학코드</th>\n      <th>국가</th>\n      <th>대학명</th>\n      <th>파견구분</th>\n      <th>협정형태</th>\n      <th>NUM_VISITS</th>\n      <th>BERT_SUM</th>\n      <th>RNN_SUM</th>\n      <th>href</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>AF000001</td>\n      <td>AFGHANISTAN</td>\n      <td>Kabul University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>/partner/expReport.asp?ucode=AF000001&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>AR000001</td>\n      <td>ARGENTINA</td>\n      <td>Universidad Blas Pascal</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>/partner/expReport.asp?ucode=AR000001&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>AR000002</td>\n      <td>ARGENTINA</td>\n      <td>Universidad Catolica de Cordoba</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>/partner/expReport.asp?ucode=AR000002&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>AR000003</td>\n      <td>ARGENTINA</td>\n      <td>Universidad de Palermo</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>/partner/expReport.asp?ucode=AR000003&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>AR000004</td>\n      <td>ARGENTINA</td>\n      <td>Universidad del Salvador</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>1.0</td>\n      <td>0.0</td>\n      <td>0.399670</td>\n      <td>/partner/expReport.asp?ucode=AR000004&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>AR000005</td>\n      <td>ARGENTINA</td>\n      <td>University of Buenos Aires</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>/partner/expReport.asp?ucode=AR000005&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>AU000019</td>\n      <td>AUSTRALIA</td>\n      <td>Australian National University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>27.0</td>\n      <td>17.0</td>\n      <td>17.952732</td>\n      <td>/partner/expReport.asp?ucode=AU000019&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>AU000001</td>\n      <td>AUSTRALIA</td>\n      <td>Bond University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>19.0</td>\n      <td>16.0</td>\n      <td>13.359809</td>\n      <td>/partner/expReport.asp?ucode=AU000001&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>AU000002</td>\n      <td>AUSTRALIA</td>\n      <td>Curtin University</td>\n      <td>교환학생</td>\n      <td>교환대학ISEP</td>\n      <td>7.0</td>\n      <td>5.0</td>\n      <td>4.657787</td>\n      <td>/partner/expReport.asp?ucode=AU000002&amp;bgbn=A</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>AU000003</td>\n      <td>AUSTRALIA</td>\n      <td>Curtin University of Technology</td>\n      <td>교환학생</td>\n      <td>NaN</td>\n      <td>16.0</td>\n      <td>14.0</td>\n      <td>9.696670</td>\n      <td>/partner/expReport.asp?ucode=AU000003&amp;bgbn=A</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 25
    }
   ],
   "source": [
    "print(df_univ_wrangled.shape)\n",
    "df_univ_wrangled.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "Index(['대학코드', '국가', '대학명', '파견구분', '협정형태', 'NUM_VISITS', 'BERT_SUM',\n       'RNN_SUM', 'href'],\n      dtype='object')"
     },
     "metadata": {},
     "execution_count": 26
    }
   ],
   "source": [
    "df_univ_wrangled.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "(745, 9)"
     },
     "metadata": {},
     "execution_count": 27
    }
   ],
   "source": [
    "df_univ_wrangled.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "(745, 9)"
     },
     "metadata": {},
     "execution_count": 28
    }
   ],
   "source": [
    "# check for duplicate rows\n",
    "df_univ_wrangled = df_univ_wrangled.drop_duplicates()\n",
    "df_univ_wrangled.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_univ_wrangled.rename(columns = {'href':'COLLEGE_LINK'}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_univ_wrangled[\"COLLEGE_LINK\"] = \"http:https://oia.yonsei.ac.kr\" + df_univ_wrangled[\"COLLEGE_LINK\"].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# GET BERT and RNN average\n",
    "df_univ_wrangled[\"BERT_AVG\"] = df_univ_wrangled[\"BERT_SUM\"] / df_univ_wrangled[\"NUM_VISITS\"]\n",
    "df_univ_wrangled[\"RNN_AVG\"] = df_univ_wrangled[\"RNN_SUM\"] / df_univ_wrangled[\"NUM_VISITS\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# GET BERT and RNN average\n",
    "df_univ_wrangled[\"BERT_PLUS_RNN_SCORE\"] = df_univ_wrangled[\"BERT_AVG\"]*0.7 + df_univ_wrangled[\"RNN_AVG\"]*0.3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reorder columns\n",
    "df_univ_wrangled = df_univ_wrangled[['대학코드', '국가', '대학명', '파견구분', '협정형태', 'NUM_VISITS', 'BERT_SUM', 'RNN_SUM', 'BERT_AVG', 'RNN_AVG','BERT_PLUS_RNN_SCORE', 'COLLEGE_LINK']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "       대학코드           국가                              대학명  파견구분      협정형태  \\\n0  AF000001  AFGHANISTAN                 Kabul University  교환학생      교환대학   \n1  AR000001    ARGENTINA          Universidad Blas Pascal  교환학생      ISEP   \n2  AR000002    ARGENTINA  Universidad Catolica de Cordoba  교환학생      ISEP   \n3  AR000003    ARGENTINA           Universidad de Palermo  교환학생      ISEP   \n4  AR000004    ARGENTINA         Universidad del Salvador  교환학생      ISEP   \n5  AR000005    ARGENTINA       University of Buenos Aires  교환학생      교환대학   \n6  AU000019    AUSTRALIA   Australian National University  교환학생      교환대학   \n7  AU000001    AUSTRALIA                  Bond University  교환학생      교환대학   \n8  AU000002    AUSTRALIA                Curtin University  교환학생  교환대학ISEP   \n9  AU000003    AUSTRALIA  Curtin University of Technology  교환학생       NaN   \n\n   NUM_VISITS  BERT_SUM    RNN_SUM  BERT_AVG   RNN_AVG  BERT_PLUS_RNN_SCORE  \\\n0         NaN       NaN        NaN       NaN       NaN                  NaN   \n1         NaN       NaN        NaN       NaN       NaN                  NaN   \n2         NaN       NaN        NaN       NaN       NaN                  NaN   \n3         NaN       NaN        NaN       NaN       NaN                  NaN   \n4         1.0       0.0   0.399670  0.000000  0.399670             0.119901   \n5         NaN       NaN        NaN       NaN       NaN                  NaN   \n6        27.0      17.0  17.952732  0.629630  0.664916             0.640216   \n7        19.0      16.0  13.359809  0.842105  0.703148             0.800418   \n8         7.0       5.0   4.657787  0.714286  0.665398             0.699619   \n9        16.0      14.0   9.696670  0.875000  0.606042             0.794313   \n\n                                        COLLEGE_LINK  \n0  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n1  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n2  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n3  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n4  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n5  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n6  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n7  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n8  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n9  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  ",
      "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>대학코드</th>\n      <th>국가</th>\n      <th>대학명</th>\n      <th>파견구분</th>\n      <th>협정형태</th>\n      <th>NUM_VISITS</th>\n      <th>BERT_SUM</th>\n      <th>RNN_SUM</th>\n      <th>BERT_AVG</th>\n      <th>RNN_AVG</th>\n      <th>BERT_PLUS_RNN_SCORE</th>\n      <th>COLLEGE_LINK</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>AF000001</td>\n      <td>AFGHANISTAN</td>\n      <td>Kabul University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>AR000001</td>\n      <td>ARGENTINA</td>\n      <td>Universidad Blas Pascal</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>AR000002</td>\n      <td>ARGENTINA</td>\n      <td>Universidad Catolica de Cordoba</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>AR000003</td>\n      <td>ARGENTINA</td>\n      <td>Universidad de Palermo</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>AR000004</td>\n      <td>ARGENTINA</td>\n      <td>Universidad del Salvador</td>\n      <td>교환학생</td>\n      <td>ISEP</td>\n      <td>1.0</td>\n      <td>0.0</td>\n      <td>0.399670</td>\n      <td>0.000000</td>\n      <td>0.399670</td>\n      <td>0.119901</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>AR000005</td>\n      <td>ARGENTINA</td>\n      <td>University of Buenos Aires</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>AU000019</td>\n      <td>AUSTRALIA</td>\n      <td>Australian National University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>27.0</td>\n      <td>17.0</td>\n      <td>17.952732</td>\n      <td>0.629630</td>\n      <td>0.664916</td>\n      <td>0.640216</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>AU000001</td>\n      <td>AUSTRALIA</td>\n      <td>Bond University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>19.0</td>\n      <td>16.0</td>\n      <td>13.359809</td>\n      <td>0.842105</td>\n      <td>0.703148</td>\n      <td>0.800418</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>AU000002</td>\n      <td>AUSTRALIA</td>\n      <td>Curtin University</td>\n      <td>교환학생</td>\n      <td>교환대학ISEP</td>\n      <td>7.0</td>\n      <td>5.0</td>\n      <td>4.657787</td>\n      <td>0.714286</td>\n      <td>0.665398</td>\n      <td>0.699619</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>AU000003</td>\n      <td>AUSTRALIA</td>\n      <td>Curtin University of Technology</td>\n      <td>교환학생</td>\n      <td>NaN</td>\n      <td>16.0</td>\n      <td>14.0</td>\n      <td>9.696670</td>\n      <td>0.875000</td>\n      <td>0.606042</td>\n      <td>0.794313</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 34
    }
   ],
   "source": [
    "df_univ_wrangled.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "(745, 12)\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "Index(['대학코드', '국가', '대학명', '파견구분', '협정형태', 'NUM_VISITS', 'BERT_SUM',\n       'RNN_SUM', 'BERT_AVG', 'RNN_AVG', 'BERT_PLUS_RNN_SCORE',\n       'COLLEGE_LINK'],\n      dtype='object')"
     },
     "metadata": {},
     "execution_count": 35
    }
   ],
   "source": [
    "print(df_univ_wrangled.shape)\n",
    "df_univ_wrangled.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# export wrangled df abstract concat dataframe\n",
    "df_univ_wrangled.to_csv(\"./data_wrangled/df_univ_wrangled.csv\",encoding=\"utf-8\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sort universities according to BERT and RNN average score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "(196, 12)\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "        대학코드         국가                              대학명  파견구분          협정형태  \\\n6   AU000019  AUSTRALIA   Australian National University  교환학생          교환대학   \n7   AU000001  AUSTRALIA                  Bond University  교환학생          교환대학   \n9   AU000003  AUSTRALIA  Curtin University of Technology  교환학생           NaN   \n10  AU000004  AUSTRALIA                Deakin University  교환학생  교환대학ISEPUSAC   \n12  AU000005  AUSTRALIA              La Trobe University  교환학생      교환대학ISEP   \n\n    NUM_VISITS  BERT_SUM    RNN_SUM  BERT_AVG   RNN_AVG  BERT_PLUS_RNN_SCORE  \\\n6         27.0      17.0  17.952732  0.629630  0.664916             0.640216   \n7         19.0      16.0  13.359809  0.842105  0.703148             0.800418   \n9         16.0      14.0   9.696670  0.875000  0.606042             0.794313   \n10        24.0      18.0  17.425120  0.750000  0.726047             0.742814   \n12        22.0      14.0  13.872905  0.636364  0.630587             0.634631   \n\n                                         COLLEGE_LINK  \n6   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n7   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n9   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n10  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n12  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  ",
      "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>대학코드</th>\n      <th>국가</th>\n      <th>대학명</th>\n      <th>파견구분</th>\n      <th>협정형태</th>\n      <th>NUM_VISITS</th>\n      <th>BERT_SUM</th>\n      <th>RNN_SUM</th>\n      <th>BERT_AVG</th>\n      <th>RNN_AVG</th>\n      <th>BERT_PLUS_RNN_SCORE</th>\n      <th>COLLEGE_LINK</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>6</th>\n      <td>AU000019</td>\n      <td>AUSTRALIA</td>\n      <td>Australian National University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>27.0</td>\n      <td>17.0</td>\n      <td>17.952732</td>\n      <td>0.629630</td>\n      <td>0.664916</td>\n      <td>0.640216</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>AU000001</td>\n      <td>AUSTRALIA</td>\n      <td>Bond University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>19.0</td>\n      <td>16.0</td>\n      <td>13.359809</td>\n      <td>0.842105</td>\n      <td>0.703148</td>\n      <td>0.800418</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>AU000003</td>\n      <td>AUSTRALIA</td>\n      <td>Curtin University of Technology</td>\n      <td>교환학생</td>\n      <td>NaN</td>\n      <td>16.0</td>\n      <td>14.0</td>\n      <td>9.696670</td>\n      <td>0.875000</td>\n      <td>0.606042</td>\n      <td>0.794313</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>AU000004</td>\n      <td>AUSTRALIA</td>\n      <td>Deakin University</td>\n      <td>교환학생</td>\n      <td>교환대학ISEPUSAC</td>\n      <td>24.0</td>\n      <td>18.0</td>\n      <td>17.425120</td>\n      <td>0.750000</td>\n      <td>0.726047</td>\n      <td>0.742814</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>AU000005</td>\n      <td>AUSTRALIA</td>\n      <td>La Trobe University</td>\n      <td>교환학생</td>\n      <td>교환대학ISEP</td>\n      <td>22.0</td>\n      <td>14.0</td>\n      <td>13.872905</td>\n      <td>0.636364</td>\n      <td>0.630587</td>\n      <td>0.634631</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 44
    }
   ],
   "source": [
    "# Looking at cases with enough number of visits\n",
    "NUMBER_OF_VISITS = 15\n",
    "df_enough_visits = df_univ_wrangled.loc[df_univ_wrangled['NUM_VISITS'] >= NUMBER_OF_VISITS]\n",
    "print(df_enough_visits.shape)\n",
    "df_enough_visits.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "    index      대학코드             국가  \\\n0     361  SE000003         SWEDEN   \n1     547  US000104  UNITED STATES   \n2      34  AT000009        AUSTRIA   \n3     539  US000096  UNITED STATES   \n4     168  DE000017        GERMANY   \n5     369  SE000011         SWEDEN   \n6     521  US000079  UNITED STATES   \n7     615  US000168  UNITED STATES   \n8     731  US000273  UNITED STATES   \n9     566  US000123  UNITED STATES   \n10    341  ES000001          SPAIN   \n11     27  AT000001        AUSTRIA   \n12    166  DE000003        GERMANY   \n13    480  US000022  UNITED STATES   \n14    120  FI000007        FINLAND   \n\n                                                 대학명  파견구분      협정형태  \\\n0                            Kristianstad University  교환학생      교환대학   \n1                                  Maryville College  교환학생      교환대학   \n2                               University of Vienna  교환학생      교환대학   \n3                                   Linfield College  교환학생      교환대학   \n4                     Humboldt-Universitat zu Berlin  교환학생      교환대학   \n5                           University of Gothenburg  교환학생      교환대학   \n6                     Hobart & William Smith College  교환학생      교환대학   \n7                                   St. Olaf College  교환학생      교환대학   \n8                                Westminster College  교환학생      교환대학   \n9     New York University - Stern School of Business  교환학생      교환대학   \n10                                             ESADE  교환학생      교환대학   \n11  FH Kufstein Tirol University of Applied Sciences  교환학생      교환대학   \n12                          Freie Universitat Berlin  교환학생      교환대학   \n13                                         CSU Chico  교환학생      교환대학   \n14                            University of Helsinki  교환학생  교환대학ISEP   \n\n    NUM_VISITS  BERT_SUM    RNN_SUM  BERT_AVG   RNN_AVG  BERT_PLUS_RNN_SCORE  \\\n0         49.0      47.0  39.341540  0.959184  0.802889             0.912295   \n1         28.0      27.0  21.112275  0.964286  0.754010             0.901203   \n2         20.0      20.0  13.028918  1.000000  0.651446             0.895434   \n3         51.0      50.0  35.077536  0.980392  0.687795             0.892613   \n4         28.0      27.0  19.999718  0.964286  0.714276             0.889283   \n5         28.0      27.0  19.829802  0.964286  0.708207             0.887462   \n6         28.0      26.0  21.590119  0.928571  0.771076             0.881323   \n7         20.0      19.0  14.099125  0.950000  0.704956             0.876487   \n8         23.0      21.0  17.890283  0.913043  0.777838             0.872482   \n9         18.0      17.0  12.651913  0.944444  0.702884             0.871976   \n10        24.0      23.0  16.047977  0.958333  0.668666             0.871433   \n11        40.0      36.0  31.848505  0.900000  0.796213             0.868864   \n12        49.0      45.0  36.713999  0.918367  0.749265             0.867637   \n13        21.0      19.0  16.284026  0.904762  0.775430             0.865962   \n14        30.0      28.0  20.996371  0.933333  0.699879             0.863297   \n\n                                         COLLEGE_LINK  \n0   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n1   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n2   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n3   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n4   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n5   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n6   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n7   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n8   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n9   http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n10  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n11  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n12  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n13  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n14  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  ",
      "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>index</th>\n      <th>대학코드</th>\n      <th>국가</th>\n      <th>대학명</th>\n      <th>파견구분</th>\n      <th>협정형태</th>\n      <th>NUM_VISITS</th>\n      <th>BERT_SUM</th>\n      <th>RNN_SUM</th>\n      <th>BERT_AVG</th>\n      <th>RNN_AVG</th>\n      <th>BERT_PLUS_RNN_SCORE</th>\n      <th>COLLEGE_LINK</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>361</td>\n      <td>SE000003</td>\n      <td>SWEDEN</td>\n      <td>Kristianstad University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>49.0</td>\n      <td>47.0</td>\n      <td>39.341540</td>\n      <td>0.959184</td>\n      <td>0.802889</td>\n      <td>0.912295</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>547</td>\n      <td>US000104</td>\n      <td>UNITED STATES</td>\n      <td>Maryville College</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>28.0</td>\n      <td>27.0</td>\n      <td>21.112275</td>\n      <td>0.964286</td>\n      <td>0.754010</td>\n      <td>0.901203</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>34</td>\n      <td>AT000009</td>\n      <td>AUSTRIA</td>\n      <td>University of Vienna</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>20.0</td>\n      <td>20.0</td>\n      <td>13.028918</td>\n      <td>1.000000</td>\n      <td>0.651446</td>\n      <td>0.895434</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>539</td>\n      <td>US000096</td>\n      <td>UNITED STATES</td>\n      <td>Linfield College</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>51.0</td>\n      <td>50.0</td>\n      <td>35.077536</td>\n      <td>0.980392</td>\n      <td>0.687795</td>\n      <td>0.892613</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>168</td>\n      <td>DE000017</td>\n      <td>GERMANY</td>\n      <td>Humboldt-Universitat zu Berlin</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>28.0</td>\n      <td>27.0</td>\n      <td>19.999718</td>\n      <td>0.964286</td>\n      <td>0.714276</td>\n      <td>0.889283</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>369</td>\n      <td>SE000011</td>\n      <td>SWEDEN</td>\n      <td>University of Gothenburg</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>28.0</td>\n      <td>27.0</td>\n      <td>19.829802</td>\n      <td>0.964286</td>\n      <td>0.708207</td>\n      <td>0.887462</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>521</td>\n      <td>US000079</td>\n      <td>UNITED STATES</td>\n      <td>Hobart &amp; William Smith College</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>28.0</td>\n      <td>26.0</td>\n      <td>21.590119</td>\n      <td>0.928571</td>\n      <td>0.771076</td>\n      <td>0.881323</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>615</td>\n      <td>US000168</td>\n      <td>UNITED STATES</td>\n      <td>St. Olaf College</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>20.0</td>\n      <td>19.0</td>\n      <td>14.099125</td>\n      <td>0.950000</td>\n      <td>0.704956</td>\n      <td>0.876487</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>731</td>\n      <td>US000273</td>\n      <td>UNITED STATES</td>\n      <td>Westminster College</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>23.0</td>\n      <td>21.0</td>\n      <td>17.890283</td>\n      <td>0.913043</td>\n      <td>0.777838</td>\n      <td>0.872482</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>566</td>\n      <td>US000123</td>\n      <td>UNITED STATES</td>\n      <td>New York University - Stern School of Business</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>18.0</td>\n      <td>17.0</td>\n      <td>12.651913</td>\n      <td>0.944444</td>\n      <td>0.702884</td>\n      <td>0.871976</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>341</td>\n      <td>ES000001</td>\n      <td>SPAIN</td>\n      <td>ESADE</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>24.0</td>\n      <td>23.0</td>\n      <td>16.047977</td>\n      <td>0.958333</td>\n      <td>0.668666</td>\n      <td>0.871433</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>27</td>\n      <td>AT000001</td>\n      <td>AUSTRIA</td>\n      <td>FH Kufstein Tirol University of Applied Sciences</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>40.0</td>\n      <td>36.0</td>\n      <td>31.848505</td>\n      <td>0.900000</td>\n      <td>0.796213</td>\n      <td>0.868864</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>166</td>\n      <td>DE000003</td>\n      <td>GERMANY</td>\n      <td>Freie Universitat Berlin</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>49.0</td>\n      <td>45.0</td>\n      <td>36.713999</td>\n      <td>0.918367</td>\n      <td>0.749265</td>\n      <td>0.867637</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>480</td>\n      <td>US000022</td>\n      <td>UNITED STATES</td>\n      <td>CSU Chico</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>21.0</td>\n      <td>19.0</td>\n      <td>16.284026</td>\n      <td>0.904762</td>\n      <td>0.775430</td>\n      <td>0.865962</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>14</th>\n      <td>120</td>\n      <td>FI000007</td>\n      <td>FINLAND</td>\n      <td>University of Helsinki</td>\n      <td>교환학생</td>\n      <td>교환대학ISEP</td>\n      <td>30.0</td>\n      <td>28.0</td>\n      <td>20.996371</td>\n      <td>0.933333</td>\n      <td>0.699879</td>\n      <td>0.863297</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 45
    }
   ],
   "source": [
    "df_enough_visits_sort = df_enough_visits.sort_values(by=[\"BERT_PLUS_RNN_SCORE\"], ascending=False)\n",
    "df_ranking = df_enough_visits_sort.reset_index()\n",
    "df_ranking.head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "     index      대학코드                  국가  \\\n280    220  IT000008               ITALY   \n281    486  US000021       UNITED STATES   \n282    435  GB000025      UNITED KINGDOM   \n283    334  RU000002  RUSSIAN FEDERATION   \n284    164  DE000001             GERMANY   \n285    404  GB000033      UNITED KINGDOM   \n286    187  CN000004           HONG KONG   \n287    126  FR000004              FRANCE   \n288    300  NL000013         NETHERLANDS   \n289    172  DE000008             GERMANY   \n290     94  CN000015               CHINA   \n291    257  JP000026               JAPAN   \n292     82  CN000007               CHINA   \n293    383  TW000005              TAIWAN   \n294    354  ES000017               SPAIN   \n\n                                                   대학명         파견구분  협정형태  \\\n280                                  Luiss Guido Carli         교환학생  교환대학   \n281                                    CSU Los Angeles         교환학생  교환대학   \n282                          University of Westminster  방문학생SAP(정규)   SAF   \n283                            Moscow State University         교환학생  교환대학   \n284                Eberhard Karls Universitat Tubingen         교환학생  교환대학   \n285                         SOAS, University of London         교환학생  교환대학   \n286                   Hong Kong Polytechnic University         교환학생  교환대학   \n287  Ecole Superieure des Sciences Economiques et C...         교환학생  교환대학   \n288                       Amsterdam University College         교환학생   NaN   \n289             Ludwig-Maximilians-Universitat Munchen         교환학생  교환대학   \n290                                Tsinghua University         교환학생  교환대학   \n291                                  Sophia University         교환학생  교환대학   \n292                                 Nanjing University         교환학생  교환대학   \n293  National Taiwan University, College of Management         교환학생  교환대학   \n294                  Universitat Autonoma de Barcelona         교환학생  교환대학   \n\n     NUM_VISITS  BERT_SUM    RNN_SUM  BERT_AVG   RNN_AVG  BERT_PLUS_RNN_SCORE  \\\n280        20.0      10.0  13.335463  0.500000  0.666773             0.550032   \n281         8.0       4.0   5.026329  0.500000  0.628291             0.538487   \n282         7.0       3.0   5.564678  0.428571  0.794954             0.538486   \n283        29.0      14.0  19.249699  0.482759  0.663783             0.537066   \n284        28.0      12.0  21.016637  0.428571  0.750594             0.525178   \n285         9.0       4.0   6.251262  0.444444  0.694585             0.519487   \n286        53.0      23.0  36.354918  0.433962  0.685942             0.509556   \n287         7.0       3.0   4.874636  0.428571  0.696377             0.508913   \n288        17.0       8.0  10.136701  0.470588  0.596277             0.508295   \n289        31.0      13.0  21.730792  0.419355  0.700993             0.503846   \n290         9.0       4.0   5.523812  0.444444  0.613757             0.495238   \n291         7.0       3.0   4.519462  0.428571  0.645637             0.493691   \n292        10.0       4.0   6.720541  0.400000  0.672054             0.481616   \n293         6.0       2.0   3.883967  0.333333  0.647328             0.427532   \n294         7.0       0.0   4.854875  0.000000  0.693554             0.208066   \n\n                                          COLLEGE_LINK  \n280  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n281  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n282  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n283  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n284  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n285  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n286  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n287  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n288  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n289  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n290  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n291  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n292  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n293  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  \n294  http:https://oia.yonsei.ac.kr/partner/expReport.asp?...  ",
      "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>index</th>\n      <th>대학코드</th>\n      <th>국가</th>\n      <th>대학명</th>\n      <th>파견구분</th>\n      <th>협정형태</th>\n      <th>NUM_VISITS</th>\n      <th>BERT_SUM</th>\n      <th>RNN_SUM</th>\n      <th>BERT_AVG</th>\n      <th>RNN_AVG</th>\n      <th>BERT_PLUS_RNN_SCORE</th>\n      <th>COLLEGE_LINK</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>280</th>\n      <td>220</td>\n      <td>IT000008</td>\n      <td>ITALY</td>\n      <td>Luiss Guido Carli</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>20.0</td>\n      <td>10.0</td>\n      <td>13.335463</td>\n      <td>0.500000</td>\n      <td>0.666773</td>\n      <td>0.550032</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>281</th>\n      <td>486</td>\n      <td>US000021</td>\n      <td>UNITED STATES</td>\n      <td>CSU Los Angeles</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>8.0</td>\n      <td>4.0</td>\n      <td>5.026329</td>\n      <td>0.500000</td>\n      <td>0.628291</td>\n      <td>0.538487</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>282</th>\n      <td>435</td>\n      <td>GB000025</td>\n      <td>UNITED KINGDOM</td>\n      <td>University of Westminster</td>\n      <td>방문학생SAP(정규)</td>\n      <td>SAF</td>\n      <td>7.0</td>\n      <td>3.0</td>\n      <td>5.564678</td>\n      <td>0.428571</td>\n      <td>0.794954</td>\n      <td>0.538486</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>283</th>\n      <td>334</td>\n      <td>RU000002</td>\n      <td>RUSSIAN FEDERATION</td>\n      <td>Moscow State University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>29.0</td>\n      <td>14.0</td>\n      <td>19.249699</td>\n      <td>0.482759</td>\n      <td>0.663783</td>\n      <td>0.537066</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>284</th>\n      <td>164</td>\n      <td>DE000001</td>\n      <td>GERMANY</td>\n      <td>Eberhard Karls Universitat Tubingen</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>28.0</td>\n      <td>12.0</td>\n      <td>21.016637</td>\n      <td>0.428571</td>\n      <td>0.750594</td>\n      <td>0.525178</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>285</th>\n      <td>404</td>\n      <td>GB000033</td>\n      <td>UNITED KINGDOM</td>\n      <td>SOAS, University of London</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>9.0</td>\n      <td>4.0</td>\n      <td>6.251262</td>\n      <td>0.444444</td>\n      <td>0.694585</td>\n      <td>0.519487</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>286</th>\n      <td>187</td>\n      <td>CN000004</td>\n      <td>HONG KONG</td>\n      <td>Hong Kong Polytechnic University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>53.0</td>\n      <td>23.0</td>\n      <td>36.354918</td>\n      <td>0.433962</td>\n      <td>0.685942</td>\n      <td>0.509556</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>287</th>\n      <td>126</td>\n      <td>FR000004</td>\n      <td>FRANCE</td>\n      <td>Ecole Superieure des Sciences Economiques et C...</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>7.0</td>\n      <td>3.0</td>\n      <td>4.874636</td>\n      <td>0.428571</td>\n      <td>0.696377</td>\n      <td>0.508913</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>288</th>\n      <td>300</td>\n      <td>NL000013</td>\n      <td>NETHERLANDS</td>\n      <td>Amsterdam University College</td>\n      <td>교환학생</td>\n      <td>NaN</td>\n      <td>17.0</td>\n      <td>8.0</td>\n      <td>10.136701</td>\n      <td>0.470588</td>\n      <td>0.596277</td>\n      <td>0.508295</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>289</th>\n      <td>172</td>\n      <td>DE000008</td>\n      <td>GERMANY</td>\n      <td>Ludwig-Maximilians-Universitat Munchen</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>31.0</td>\n      <td>13.0</td>\n      <td>21.730792</td>\n      <td>0.419355</td>\n      <td>0.700993</td>\n      <td>0.503846</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>290</th>\n      <td>94</td>\n      <td>CN000015</td>\n      <td>CHINA</td>\n      <td>Tsinghua University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>9.0</td>\n      <td>4.0</td>\n      <td>5.523812</td>\n      <td>0.444444</td>\n      <td>0.613757</td>\n      <td>0.495238</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>291</th>\n      <td>257</td>\n      <td>JP000026</td>\n      <td>JAPAN</td>\n      <td>Sophia University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>7.0</td>\n      <td>3.0</td>\n      <td>4.519462</td>\n      <td>0.428571</td>\n      <td>0.645637</td>\n      <td>0.493691</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>292</th>\n      <td>82</td>\n      <td>CN000007</td>\n      <td>CHINA</td>\n      <td>Nanjing University</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>10.0</td>\n      <td>4.0</td>\n      <td>6.720541</td>\n      <td>0.400000</td>\n      <td>0.672054</td>\n      <td>0.481616</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>293</th>\n      <td>383</td>\n      <td>TW000005</td>\n      <td>TAIWAN</td>\n      <td>National Taiwan University, College of Management</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>6.0</td>\n      <td>2.0</td>\n      <td>3.883967</td>\n      <td>0.333333</td>\n      <td>0.647328</td>\n      <td>0.427532</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n    <tr>\n      <th>294</th>\n      <td>354</td>\n      <td>ES000017</td>\n      <td>SPAIN</td>\n      <td>Universitat Autonoma de Barcelona</td>\n      <td>교환학생</td>\n      <td>교환대학</td>\n      <td>7.0</td>\n      <td>0.0</td>\n      <td>4.854875</td>\n      <td>0.000000</td>\n      <td>0.693554</td>\n      <td>0.208066</td>\n      <td>http:https://oia.yonsei.ac.kr/partner/expReport.asp?...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 39
    }
   ],
   "source": [
    "df_ranking.tail(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ranking.to_csv(\"./data_wrangled/df_univ_ranking.csv\",encoding=\"utf-8\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "                국가                                                대학명  \\\n0      SWITZERLAND                             University of Lausanne   \n1           MEXICO  Instituto Tecnologico y de Estudios Superiores...   \n2   UNITED KINGDOM                            University of Edinburgh   \n3           SWEDEN                            Kristianstad University   \n4           CANADA                               Dalhousie University   \n5    UNITED STATES                                  Maryville College   \n6            JAPAN                                Nagasaki University   \n7          AUSTRIA                               University of Vienna   \n8    UNITED STATES                                   Linfield College   \n9          GERMANY                     Humboldt-Universitat zu Berlin   \n10          FRANCE              Hautes Etudes Commercials (HEC Paris)   \n11          SWEDEN                           University of Gothenburg   \n12   UNITED STATES                                 University of Iowa   \n13   UNITED STATES                     Hobart & William Smith College   \n14           JAPAN                                University of Tokyo   \n\n    BERT_PLUS_RNN_SCORE  \n0              0.949716  \n1              0.942816  \n2              0.940312  \n3              0.912295  \n4              0.904432  \n5              0.901203  \n6              0.897993  \n7              0.895434  \n8              0.892613  \n9              0.889283  \n10             0.888180  \n11             0.887462  \n12             0.882376  \n13             0.881323  \n14             0.876563  ",
      "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>국가</th>\n      <th>대학명</th>\n      <th>BERT_PLUS_RNN_SCORE</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>SWITZERLAND</td>\n      <td>University of Lausanne</td>\n      <td>0.949716</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>MEXICO</td>\n      <td>Instituto Tecnologico y de Estudios Superiores...</td>\n      <td>0.942816</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>UNITED KINGDOM</td>\n      <td>University of Edinburgh</td>\n      <td>0.940312</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>SWEDEN</td>\n      <td>Kristianstad University</td>\n      <td>0.912295</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>CANADA</td>\n      <td>Dalhousie University</td>\n      <td>0.904432</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>UNITED STATES</td>\n      <td>Maryville College</td>\n      <td>0.901203</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>JAPAN</td>\n      <td>Nagasaki University</td>\n      <td>0.897993</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>AUSTRIA</td>\n      <td>University of Vienna</td>\n      <td>0.895434</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>UNITED STATES</td>\n      <td>Linfield College</td>\n      <td>0.892613</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>GERMANY</td>\n      <td>Humboldt-Universitat zu Berlin</td>\n      <td>0.889283</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>FRANCE</td>\n      <td>Hautes Etudes Commercials (HEC Paris)</td>\n      <td>0.888180</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>SWEDEN</td>\n      <td>University of Gothenburg</td>\n      <td>0.887462</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>UNITED STATES</td>\n      <td>University of Iowa</td>\n      <td>0.882376</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>UNITED STATES</td>\n      <td>Hobart &amp; William Smith College</td>\n      <td>0.881323</td>\n    </tr>\n    <tr>\n      <th>14</th>\n      <td>JAPAN</td>\n      <td>University of Tokyo</td>\n      <td>0.876563</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 42
    }
   ],
   "source": [
    "df_ranking[[\"국가\", \"대학명\", \"BERT_PLUS_RNN_SCORE\"]].head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "                     국가                                       대학명  \\\n181      CZECH REPUBLIC                        Charles University   \n182       UNITED STATES                 Oklahoma State University   \n183             GERMANY                      Pforzheim University   \n184           HONG KONG                   University of Hong Kong   \n185       UNITED STATES       University of California Santa Cruz   \n186               CHINA                         Peking University   \n187         NETHERLANDS  The Hague University of Applied Sciences   \n188               SPAIN              Public University of Navarre   \n189       UNITED STATES        University of California Riverside   \n190               ITALY                         Luiss Guido Carli   \n191  RUSSIAN FEDERATION                   Moscow State University   \n192             GERMANY       Eberhard Karls Universitat Tubingen   \n193           HONG KONG          Hong Kong Polytechnic University   \n194         NETHERLANDS              Amsterdam University College   \n195             GERMANY    Ludwig-Maximilians-Universitat Munchen   \n\n     BERT_PLUS_RNN_SCORE  \n181             0.604372  \n182             0.602584  \n183             0.602334  \n184             0.594541  \n185             0.588182  \n186             0.581289  \n187             0.581102  \n188             0.580937  \n189             0.559684  \n190             0.550032  \n191             0.537066  \n192             0.525178  \n193             0.509556  \n194             0.508295  \n195             0.503846  ",
      "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>국가</th>\n      <th>대학명</th>\n      <th>BERT_PLUS_RNN_SCORE</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>181</th>\n      <td>CZECH REPUBLIC</td>\n      <td>Charles University</td>\n      <td>0.604372</td>\n    </tr>\n    <tr>\n      <th>182</th>\n      <td>UNITED STATES</td>\n      <td>Oklahoma State University</td>\n      <td>0.602584</td>\n    </tr>\n    <tr>\n      <th>183</th>\n      <td>GERMANY</td>\n      <td>Pforzheim University</td>\n      <td>0.602334</td>\n    </tr>\n    <tr>\n      <th>184</th>\n      <td>HONG KONG</td>\n      <td>University of Hong Kong</td>\n      <td>0.594541</td>\n    </tr>\n    <tr>\n      <th>185</th>\n      <td>UNITED STATES</td>\n      <td>University of California Santa Cruz</td>\n      <td>0.588182</td>\n    </tr>\n    <tr>\n      <th>186</th>\n      <td>CHINA</td>\n      <td>Peking University</td>\n      <td>0.581289</td>\n    </tr>\n    <tr>\n      <th>187</th>\n      <td>NETHERLANDS</td>\n      <td>The Hague University of Applied Sciences</td>\n      <td>0.581102</td>\n    </tr>\n    <tr>\n      <th>188</th>\n      <td>SPAIN</td>\n      <td>Public University of Navarre</td>\n      <td>0.580937</td>\n    </tr>\n    <tr>\n      <th>189</th>\n      <td>UNITED STATES</td>\n      <td>University of California Riverside</td>\n      <td>0.559684</td>\n    </tr>\n    <tr>\n      <th>190</th>\n      <td>ITALY</td>\n      <td>Luiss Guido Carli</td>\n      <td>0.550032</td>\n    </tr>\n    <tr>\n      <th>191</th>\n      <td>RUSSIAN FEDERATION</td>\n      <td>Moscow State University</td>\n      <td>0.537066</td>\n    </tr>\n    <tr>\n      <th>192</th>\n      <td>GERMANY</td>\n      <td>Eberhard Karls Universitat Tubingen</td>\n      <td>0.525178</td>\n    </tr>\n    <tr>\n      <th>193</th>\n      <td>HONG KONG</td>\n      <td>Hong Kong Polytechnic University</td>\n      <td>0.509556</td>\n    </tr>\n    <tr>\n      <th>194</th>\n      <td>NETHERLANDS</td>\n      <td>Amsterdam University College</td>\n      <td>0.508295</td>\n    </tr>\n    <tr>\n      <th>195</th>\n      <td>GERMANY</td>\n      <td>Ludwig-Maximilians-Universitat Munchen</td>\n      <td>0.503846</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 46
    }
   ],
   "source": [
    "df_ranking[[\"국가\", \"대학명\", \"BERT_PLUS_RNN_SCORE\"]].tail(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ]
}