Extracting-Scientific-output.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " # Extracting scientific output, if DOIs are provided"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a code to collect publication information of researchers, if digital object information (DOIs) is available."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is a need for automation of the publication information of the researchers. Once this\n",
    "information is available, the information can be automatically linked to the relevant researcher. Furthermore, this information can be used for a visualization for finding conflicts of interest with other researchers."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A researcher's publication information will be processed via a free (cross-ref) API, provided DOIs of the publications are available. The json format of Cross-ref API has been edited with python scripts. Excel output file is made of list of authors and number of citations."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Shortcomings The format of output file is not yet as it should be. Two wishes that haven't been fulfilled yet\n",
    "to be:\n",
    "ˆ columns 'family name' and 'given name' side by side and\n",
    "ˆ split year-month column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                            DOIs\n",
      "0   10.1016/j.petrol.2015.12.001\n",
      "1      10.1007/s11242-016-0632-z\n",
      "2  10.1016/j.jallcom.2008.11.002\n",
      "3   10.5771/0943-7444-2019-5-380\n"
     ]
    }
   ],
   "source": [
    "#Importing various modules required for analysis\n",
    "#You excel should have a single column of DOI's. The header of the column should be 'DOIs'\n",
    "import os\n",
    "os.getcwd()\n",
    "import tkinter as tk\n",
    "from tkinter import filedialog\n",
    "import pandas as pd\n",
    "#Importing the dois with Tkinter module\n",
    "root= tk.Tk()\n",
    "\n",
    "canvas1 = tk.Canvas(root, width = 300, height = 300, bg = 'lightsteelblue')\n",
    "canvas1.pack()\n",
    "\n",
    "def getExcel ():\n",
    "    global df\n",
    "    \n",
    "    import_file_path = filedialog.askopenfilename()\n",
    "    df = pd.read_excel (import_file_path)\n",
    "    print (df)\n",
    "    \n",
    "browseButton_Excel = tk.Button(text='Import Excel File', command=getExcel, bg='green', fg='white', font=('helvetica', 12, 'bold'))\n",
    "canvas1.create_window(150, 150, window=browseButton_Excel)\n",
    "\n",
    "root.mainloop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# If crossrefapi is not yet installed, then uncomment the next line and run the cell.\n",
    "#!pip install crossrefapi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#conversion of excel of dois to list format. \n",
    "doi=df['DOIs'].to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Let's start by looking briefly at\"works\". The route refers to items identified by a DOI in the index. These can be articles, books, components, etc.\n",
    "from crossref.restful import Works\n",
    "works = Works()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#extraction of author information\n",
    "def get_author_info():\n",
    "    authors_info=record['author']\n",
    "    for authors in authors_info:\n",
    "        familyname=authors['family']\n",
    "        givenname=authors['given']\n",
    "        familynames.append(familyname)\n",
    "        givennames.append(givenname)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#extraction of family names and given names\n",
    "familynamess=[]\n",
    "givennamess=[]\n",
    "for i in doi:\n",
    "    record=works.doi(i)\n",
    "    familynames=[]\n",
    "    givennames=[]\n",
    "    get_author_info()\n",
    "    familynamess.append(familynames)\n",
    "    givennamess.append(givennames)\n",
    "df_famm=pd.DataFrame(familynamess)\n",
    "df_given=pd.DataFrame(givennamess)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#joining family and given names dataframes\n",
    "df_author=pd.concat([df_famm,df_given], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#list of authors, and other description\n",
    "title=[]\n",
    "journal=[]\n",
    "\n",
    "volume=[]\n",
    "issue=[]\n",
    "page=[]\n",
    "cit=[]\n",
    "reference=[]\n",
    "# Using for loop \n",
    "for i in doi: \n",
    "    record = works.doi(i)\n",
    "    title.append(record['title'])\n",
    "    journal.append(record['container-title'])\n",
    "    volume.append(record['volume'])\n",
    "    issue.append(record['member'])\n",
    "    page.append(record['page'])\n",
    "    cit.append(record['is-referenced-by-count'])\n",
    "    reference.append(record['references-count'])\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#extraction of publication dates\n",
    "pub_date=[]\n",
    "for i in doi: \n",
    "    record = works.doi(i)\n",
    "    newrow=record['published-print']\n",
    "    pub_date.append(newrow)\n",
    "df_date= pd.DataFrame(pub_date)\n",
    "df_date.rename(columns={'date-parts': 'Date_list'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "Date_list=df_date['Date_list'].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "Date_list=[str(item) for item in Date_list]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_date['year-month']=Date_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#delete the dataframe because it it not needed anymore\n",
    "del df_date['Date_list']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#writing various dataframes for the information\n",
    "df_title=pd.DataFrame(title, columns=['title'])\n",
    "df_journal=pd.DataFrame(journal, columns=['journal'])\n",
    "df_volume=pd.DataFrame(volume, columns=['volume'])\n",
    "df_issue=pd.DataFrame(issue, columns=['issue'])\n",
    "df_page=pd.DataFrame(page, columns=['pages'])\n",
    "df_cit=pd.DataFrame(cit, columns=['citation'])\n",
    "df_ref=pd.DataFrame(reference, columns=['references'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DOIs</th>\n",
       "      <th>title</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>...</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>journal</th>\n",
       "      <th>year-month</th>\n",
       "      <th>volume</th>\n",
       "      <th>issue</th>\n",
       "      <th>pages</th>\n",
       "      <th>citation</th>\n",
       "      <th>references</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.1016/j.petrol.2015.12.001</td>\n",
       "      <td>Determination of the most significant variable...</td>\n",
       "      <td>Thorat</td>\n",
       "      <td>Bruining</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Rahul</td>\n",
       "      <td>Hans</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Journal of Petroleum Science and Engineering</td>\n",
       "      <td>[[2016, 5]]</td>\n",
       "      <td>141</td>\n",
       "      <td>78</td>\n",
       "      <td>144-156</td>\n",
       "      <td>2</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.1007/s11242-016-0632-z</td>\n",
       "      <td>Foam Flow Experiments. I. Estimation of the Bu...</td>\n",
       "      <td>Thorat</td>\n",
       "      <td>Bruining</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Rahul</td>\n",
       "      <td>Hans</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Transport in Porous Media</td>\n",
       "      <td>[[2016, 3]]</td>\n",
       "      <td>112</td>\n",
       "      <td>297</td>\n",
       "      <td>53-76</td>\n",
       "      <td>7</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.1016/j.jallcom.2008.11.002</td>\n",
       "      <td>On the transformation behaviour of NiTi partic...</td>\n",
       "      <td>Thorat</td>\n",
       "      <td>Risanti</td>\n",
       "      <td>San Martín</td>\n",
       "      <td>Garces</td>\n",
       "      <td>Rivera Díaz del Castillo</td>\n",
       "      <td>van der Zwaag</td>\n",
       "      <td>R.R.</td>\n",
       "      <td>D.D.</td>\n",
       "      <td>...</td>\n",
       "      <td>G.</td>\n",
       "      <td>P.E.J.</td>\n",
       "      <td>S.</td>\n",
       "      <td>Journal of Alloys and Compounds</td>\n",
       "      <td>[[2009, 5]]</td>\n",
       "      <td>477</td>\n",
       "      <td>78</td>\n",
       "      <td>307-315</td>\n",
       "      <td>23</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.5771/0943-7444-2019-5-380</td>\n",
       "      <td>Brief Communication: The Need for a National-L...</td>\n",
       "      <td>Thorat</td>\n",
       "      <td>Brakel</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Rahul</td>\n",
       "      <td>Reinout van</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>KNOWLEDGE ORGANIZATION</td>\n",
       "      <td>[[2019]]</td>\n",
       "      <td>46</td>\n",
       "      <td>3821</td>\n",
       "      <td>380-386</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            DOIs  \\\n",
       "0   10.1016/j.petrol.2015.12.001   \n",
       "1      10.1007/s11242-016-0632-z   \n",
       "2  10.1016/j.jallcom.2008.11.002   \n",
       "3   10.5771/0943-7444-2019-5-380   \n",
       "\n",
       "                                               title       0         1  \\\n",
       "0  Determination of the most significant variable...  Thorat  Bruining   \n",
       "1  Foam Flow Experiments. I. Estimation of the Bu...  Thorat  Bruining   \n",
       "2  On the transformation behaviour of NiTi partic...  Thorat   Risanti   \n",
       "3  Brief Communication: The Need for a National-L...  Thorat    Brakel   \n",
       "\n",
       "            2       3                         4              5      0  \\\n",
       "0        None    None                      None           None  Rahul   \n",
       "1        None    None                      None           None  Rahul   \n",
       "2  San Martín  Garces  Rivera Díaz del Castillo  van der Zwaag   R.R.   \n",
       "3        None    None                      None           None  Rahul   \n",
       "\n",
       "             1  ...     3       4     5  \\\n",
       "0         Hans  ...  None    None  None   \n",
       "1         Hans  ...  None    None  None   \n",
       "2         D.D.  ...    G.  P.E.J.    S.   \n",
       "3  Reinout van  ...  None    None  None   \n",
       "\n",
       "                                        journal   year-month volume issue  \\\n",
       "0  Journal of Petroleum Science and Engineering  [[2016, 5]]    141    78   \n",
       "1                     Transport in Porous Media  [[2016, 3]]    112   297   \n",
       "2               Journal of Alloys and Compounds  [[2009, 5]]    477    78   \n",
       "3                        KNOWLEDGE ORGANIZATION     [[2019]]     46  3821   \n",
       "\n",
       "     pages citation  references  \n",
       "0  144-156        2          79  \n",
       "1    53-76        7          43  \n",
       "2  307-315       23          64  \n",
       "3  380-386        0           0  \n",
       "\n",
       "[4 rows x 21 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#joining all the dataframes with the information\n",
    "df_all=pd.concat([df, df_title, df_author, df_journal, df_date, df_volume,df_issue, df_page, df_cit,df_ref], axis=1)\n",
    "df_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Give export file name 20200625 DOIs ouput file.xlsx\n"
     ]
    }
   ],
   "source": [
    "#Printing the output\n",
    "#The openpyxl.utils.dataframe.dataframe_to_rows() function provides a simple way to work with Pandas Dataframes:\n",
    "\n",
    "from openpyxl.utils.dataframe import dataframe_to_rows\n",
    "from openpyxl import Workbook\n",
    "\n",
    "\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "#ws = wb.create_sheet(\"Samenvatting\", 0) \n",
    "#ws.title='Trial'\n",
    "\n",
    "for r in dataframe_to_rows(df_all, index=False, header=True):\n",
    "    ws.append(r)\n",
    "\n",
    "    #saving the excel workbook\n",
    "Exportfile_name=input('Give export file name ',)# write the name of the file with extension .xlsx\n",
    "wb.save(Exportfile_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}