for_merging_LinkedIn_wos.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:100% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from tqdm import tqdm\n",
    "from tqdm import tnrange, tqdm_notebook\n",
    "from time import sleep\n",
    "import difflib\n",
    "import scipy\n",
    "import operator\n",
    "import difflib\n",
    "from IPython.core.display import display,HTML\n",
    "try:\n",
    "    import cPickle as pickle     #it is faster than pickle!\n",
    "except:\n",
    "    import pickle\n",
    "    \n",
    "import unicodedata\n",
    "    \n",
    " \n",
    "    \n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))  # to make the notebook use the entire width of the browser"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\n",
    "################\n",
    "\n",
    "def convert_unicode_to_string(old_cadena):\n",
    "    \n",
    "    \n",
    "    try:\n",
    "        \n",
    "        new_cadena=unicodedata.normalize('NFKD', old_cadena).encode('ascii','ignore')\n",
    "#         print old_cadena,type(old_cadena)\n",
    "#         print new_cadena,type(new_cadena)\n",
    "#         raw_input()\n",
    "        return new_cadena\n",
    "    except TypeError:  # if it is a string already\n",
    "#         print type(old_cadena)\n",
    "#         print old_cadena\n",
    "#         raw_input()\n",
    "        return old_cadena\n",
    "\n",
    "\n",
    "\n",
    "############################\n",
    "\n",
    "def find_closest_match(cadena, lista):\n",
    "     #difflib.get_close_matches(a,b,3)   # this one gets you as many close matches as you want, in descending order of matching\n",
    "        \n",
    "#     print type(cadena), type(lista)\n",
    "#     raw_input()\n",
    "     ###   difflib.get_close_print \"wos univ:\", df_disamb_wos_test.University.unique()matches?   #### to read the manual of sth\n",
    "    score=0.\n",
    "    match=None\n",
    "    \n",
    "    a=cadena\n",
    "    dict_index_score={}\n",
    "    for i in range(len(lista)):\n",
    "    \n",
    "        b=lista[i]\n",
    "        score = difflib.SequenceMatcher(None,a, b).ratio()  \n",
    "        dict_index_score[i]=score\n",
    "\n",
    "  \n",
    "    sorted_dict = sorted(dict_index_score.items(), key=lambda x: x[1],reverse=True) # SORT DICT BY VALUE, IN DESCENDING ORDER     print \"sorted dictsorted_dict\",sorted_dict ## [(3, 0.9285714285714286), (6, 0.7586206896551724), (0, 0.6896551724137931), (5, 0.37037037037037035), (2, 0.35714285714285715), (4, 0.35714285714285715), (1, 0.23076923076923078)]\n",
    "\n",
    "    match=lista[sorted_dict[0][0]]\n",
    "    index=sorted_dict[0][0]\n",
    "    score=sorted_dict[0][1]\n",
    "\n",
    "#     print \"\\nbest matching for \", cadena,\"is:\", match, \",   score:\"    , score\n",
    "#     raw_input()\n",
    "    return match, score\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "done reading wos file\n"
     ]
    }
   ],
   "source": [
    "\n",
    "number_rows=1000000#\"All\" # or 1000000\n",
    "\n",
    "\n",
    "#path_cluster='/home/julia/Dropbox_collaborations/Data/WoS_data/Disambiguated_authors/'\n",
    "path_redbox='/home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/WoS_data/Disambiguated_authors/'\n",
    "\n",
    "string_num_rows=\"\"\n",
    "if  number_rows==\"All\":\n",
    "\n",
    "    df_disamb_wos_test=pd.read_csv(path_redbox+'wos_author_disambiguated_2000-2015_USA_processed.tsv', sep=\"\\t\")\n",
    "\n",
    "else:\n",
    "    string_num_rows=\"_\"+str(number_rows)\n",
    "    df_disamb_wos_test=pd.read_csv(path_redbox+'wos_author_disambiguated_2000-2015_USA_processed.tsv', sep=\"\\t\",nrows= number_rows)\n",
    "\n",
    "\n",
    "print \"done reading wos file\"\n",
    "\n",
    "df_disamb_wos_test['full_name'] = df_disamb_wos_test.full_name.apply(convert_unicode_to_string)\n",
    "df_disamb_wos_test['firstname'] = df_disamb_wos_test.firstname.apply(convert_unicode_to_string)\n",
    "df_disamb_wos_test['middle'] = df_disamb_wos_test.middle.apply(convert_unicode_to_string)\n",
    "df_disamb_wos_test['lastname'] = df_disamb_wos_test.lastname.apply(convert_unicode_to_string)\n",
    "\n",
    "\n",
    "df_disamb_wos_test['University'] = df_disamb_wos_test.apply(lambda row: row.University.replace(\", \",\",\").replace(\"[\",\"\").replace(\"]\",\"\").split(\",\"), axis=1)\n",
    "\n",
    "# example:  [University Michigan, University Michigan Hlth Syst]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "done reading linkedin file\n",
      "WoS:  (100000, 15)\n",
      "LinkedIn:  (83072, 144)\n"
     ]
    }
   ],
   "source": [
    "path_linkedin_redbox='/home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/Vinu_University_Sheets/Improved_3Feb_and_more_emails_27Feb/'\n",
    "\n",
    "#path_linkedin_redbox='/home/julia/Dropbox_collaborations/Data/Vinu_University_Sheets/Improved_3Feb_and_more_emails_27Feb/All_linkedIn.pickle'\n",
    "\n",
    "df_linkedin_test=pd.read_pickle(path_linkedin_redbox+'All_linkedIn.pickle')\n",
    "\n",
    "\n",
    "print \"done reading linkedin file\"\n",
    "\n",
    "df_linkedin_test = df_linkedin_test.rename(columns={'Department(s)': 'Department', 'School/college': 'School_college'})\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "df_linkedin_test['full_name'] = df_linkedin_test.full_name.apply(convert_unicode_to_string)\n",
    "df_linkedin_test['firstname'] = df_linkedin_test.firstname.apply(convert_unicode_to_string)\n",
    "df_linkedin_test['middle'] = df_linkedin_test.middle.apply(convert_unicode_to_string)\n",
    "df_linkedin_test['lastname'] = df_linkedin_test.lastname.apply(convert_unicode_to_string)\n",
    "df_linkedin_test['dirty_firstname'] = df_linkedin_test.dirty_firstname.apply(convert_unicode_to_string)\n",
    "df_linkedin_test['dirty_lastname'] = df_linkedin_test.dirty_lastname.apply(convert_unicode_to_string)\n",
    "\n",
    "df_linkedin_test['University'] = df_linkedin_test.University.apply(convert_unicode_to_string)\n",
    "df_linkedin_test['Department'] = df_linkedin_test.Department.apply(convert_unicode_to_string)\n",
    "df_linkedin_test['School_college'] = df_linkedin_test.School_college.apply(convert_unicode_to_string)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "print \"WoS: \",df_disamb_wos_test.shape\n",
    "print \"LinkedIn: \",df_linkedin_test.shape\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "entering a loop of 83072 iters (linkedin rows)......\n",
      "\n",
      "\n",
      "done with the big loop\n",
      "\n",
      "# of linkedin names with wos ambiguity: 20\n"
     ]
    }
   ],
   "source": [
    "\n",
    "threshold  = 0.9  # to accept a matching of two university names\n",
    "\n",
    "\n",
    "# given two dataframes, i go over all names in one (linkedin)   and find  matches from the other\n",
    "\n",
    "master_dict_linkedin_index_wos_index={}\n",
    "cont=0\n",
    "print \"entering a loop of\",len(df_linkedin_test), \"iters (linkedin rows)......\\n\"\n",
    "\n",
    "\n",
    "cont_ambig=0\n",
    "# for row in tqdm_nofull_nametebook(df_linkedin_test.head(5000).iterrows()):       \n",
    "#for row in df_linkedin_test.head(1000).iterrows():       \n",
    "lista_linkedin_idx=[]    \n",
    "for row in tqdm_notebook(df_linkedin_test.head(500).iterrows()):       \n",
    "    cont +=1\n",
    "\n",
    "    linkedin_index=row[0]\n",
    "\n",
    "\n",
    "#     if linkedin_index ==100 or linkedin_index ==1000 or   linkedin_index ==5000 or linkedin_index ==10000 or linkedin_index == 50000 :\n",
    "#         print linkedin_index\n",
    "\n",
    "    lista_linkedin_idx.append(linkedin_index)\n",
    "\n",
    "    master_dict_linkedin_index_wos_index[linkedin_index]=[]\n",
    "    matching_dict_univ_linkedin_univ_wos={}     \n",
    "\n",
    "\n",
    "    univ_linkedin=row[1].University    \n",
    "    lastname_linkedin=row[1].lastname    \n",
    "    full_name_linkedin=row[1].full_name\n",
    "\n",
    "\n",
    "    lista_posibles_wos_index=[] \n",
    "\n",
    "\n",
    "    try:   # if the whole row is not just NANs    \n",
    "\n",
    "    #LINKEDIN:   dirty_firstname\tdirty_lastname\tfull_name\tfirstname\tmiddle\tlastname\n",
    "    #WOS:      firstname\tfirstname_initial\tmiddle\tlastname\tlist_author_names\tUniversity\tlist_years\tfull_name\n",
    "\n",
    "\n",
    "         first_name_linkedin=row[1].firstname\n",
    "\n",
    "\n",
    "         perfect_selection=df_disamb_wos_test[df_disamb_wos_test.full_name == full_name_linkedin]\n",
    "         if len( perfect_selection ) ==1:\n",
    "        #print \"perfect match on full name for linkedin_idx\", linkedin_index,\"  and wos_idx:\", perfect_selection.index\n",
    "        #raw_input()\n",
    "            select_df_wos_one_lastname = perfect_selection\n",
    "\n",
    "         else:                      \n",
    "\n",
    "         ### for a given last name, i only look at the wos rows with the same lastname\n",
    "            pre_select_df_wos_one_lastname=df_disamb_wos_test[df_disamb_wos_test.lastname == lastname_linkedin]\n",
    "\n",
    "            if len(pre_select_df_wos_one_lastname)>0:        \n",
    "                select_df_wos_one_lastname = pre_select_df_wos_one_lastname[pre_select_df_wos_one_lastname.firstname ==  first_name_linkedin]\n",
    "\n",
    "            # the selected df keeps the same indices from de original df !!!!!           \n",
    "            #print \"linkedin:\", linkedin_index, first_name_linkedin, lastname_linkedin, univ_linkedin\n",
    "\n",
    "\n",
    "            else:\n",
    "                select_df_wos_one_lastname = pre_select_df_wos_one_lastname\n",
    "\n",
    "\n",
    "#             print \"size pre-selection:\", len(pre_select_df_wos_one_lastname),  \"   size selection:\", len(select_df_wos_one_lastname)\n",
    "\n",
    "#             if len(select_df_wos_one_lastname)>0:\n",
    "#                 raw_input()\n",
    "\n",
    "\n",
    "         for fila in  select_df_wos_one_lastname.head(5000).iterrows():  # one row per author\n",
    "\n",
    "            list_univ_wos=fila[1].University                           \n",
    "            wos_index=fila[0]\n",
    "\n",
    "\n",
    "\n",
    "#             print type(univ_linkedin), univ_linkedin\n",
    "#             print type(list_univ_wos), list_univ_wos\n",
    "#             raw_input()\n",
    "\n",
    "\n",
    "            match, score= find_closest_match(univ_linkedin, list_univ_wos)\n",
    "\n",
    "#             print univ_linkedin, list_univ_wos\n",
    "#             print score\n",
    "#             raw_input()\n",
    "\n",
    "\n",
    "            if score > threshold:  #if the result of the matching is worth considering\n",
    "                matching_dict_univ_linkedin_univ_wos[univ_linkedin]=(match,  wos_index, score)\n",
    "                master_dict_linkedin_index_wos_index[linkedin_index].append(wos_index)   \n",
    "\n",
    "\n",
    "\n",
    "                lista_posibles_wos_index.append(wos_index)   \n",
    "\n",
    "\n",
    "         if len(lista_posibles_wos_index)>1:\n",
    "                cont_ambig +=1\n",
    "    except TypeError:   # for the few empty rows (all Nan)\n",
    "            pass\n",
    "\n",
    "\n",
    "print \"done with the big loop\\n\"\n",
    "\n",
    "\n",
    "print \"# of linkedin names with wos ambiguity:\", cont_ambig\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "written: /home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/Merged_LinkedIn_WoS/master_dict_linkedin_index_wos_index_10000.pickle\n"
     ]
    }
   ],
   "source": [
    "#path_merge_win='C:\\\\Users\\\\julietta\\\\Work\\\\Dropbox_studies\\\\Data\\\\Merged_LinkedIn_WoS\\\\'\n",
    "path_merge_redbox='/home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/Merged_LinkedIn_WoS/'\n",
    "\n",
    "#path_merge_cluster='/home/julia/Dropbox_collaborations/Data/Merged_LinkedIn_WoS/'\n",
    "\n",
    "\n",
    "filename_pickle=path_merge_redbox+\"master_dict_linkedin_index_wos_index\"+string_num_rows+\".pickle\"    \n",
    "pickle.dump(master_dict_linkedin_index_wos_index, open(filename_pickle, 'wb'))\n",
    "print \"written:\",filename_pickle\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "linkedin_idx  wos_idx\n",
      "3 [914060]\n",
      "16 [631495]\n",
      "18 [519471]\n",
      "20 [559062]\n",
      "32 [743657]\n",
      "35 [353345]\n",
      "37 [89692, 91471, 227853, 268561, 269130, 272844, 303116, 542503, 576526, 632393, 660344, 693071, 696693, 735784, 935781]\n",
      "48 [513475]\n",
      "62 [21556]\n",
      "68 [782901]\n",
      "71 [502140, 922102]\n",
      "73 [21324, 349069, 487923]\n",
      "83 [913763]\n",
      "110 [361404]\n",
      "138 [274132]\n",
      "139 [514664]\n",
      "142 [191527]\n",
      "154 [595574]\n",
      "164 [210794]\n",
      "169 [953960]\n",
      "170 [590300, 915668]\n",
      "173 [642725]\n",
      "177 [437708, 932650]\n",
      "181 [718576]\n",
      "187 [336693]\n",
      "189 [83815, 560374, 753608, 962360]\n",
      "192 [448069, 859270]\n",
      "194 [544037]\n",
      "195 [467512]\n",
      "197 [16311]\n",
      "198 [959006]\n",
      "199 [26550]\n",
      "203 [706701]\n",
      "205 [766693]\n",
      "206 [804198]\n",
      "207 [206469]\n",
      "218 [423210]\n",
      "222 [821019]\n",
      "229 [108143]\n",
      "236 [401751]\n",
      "241 [310393]\n",
      "250 [582856]\n",
      "251 [65334, 797847]\n",
      "253 [837611]\n",
      "255 [772470]\n",
      "258 [675716]\n",
      "261 [406941]\n",
      "265 [904178]\n",
      "267 [555625, 792249, 813528]\n",
      "279 [401970, 789011, 967621]\n",
      "289 [113609]\n",
      "291 [598573]\n",
      "292 [413129]\n",
      "302 [705889, 974440]\n",
      "331 [842668]\n",
      "334 [573032]\n",
      "341 [409453]\n",
      "344 [792760]\n",
      "345 [966471]\n",
      "355 [833213, 990177]\n",
      "358 [86778]\n",
      "366 [622208]\n",
      "377 [407048]\n",
      "384 [685730]\n",
      "386 [625788, 912287]\n",
      "388 [29043]\n",
      "389 [873952]\n",
      "392 [959165]\n",
      "397 [102068]\n",
      "400 [518472]\n",
      "405 [638076]\n",
      "407 [288280, 350424, 366634, 395385, 549779, 617949, 637578, 868492]\n",
      "428 [581557]\n",
      "429 [232384, 913765]\n",
      "432 [438434]\n",
      "437 [273802]\n",
      "441 [712213]\n",
      "443 [124175]\n",
      "446 [638895]\n",
      "448 [455000]\n",
      "452 [552261, 735749]\n",
      "458 [830123, 946979]\n",
      "460 [409745]\n",
      "465 [951713]\n",
      "480 [463028]\n",
      "481 [620912]\n",
      "483 [58469]\n",
      "486 [627876]\n",
      "487 [65734, 809239]\n",
      "489 [13256, 185046, 384064, 623783, 681015]\n",
      "492 [420945, 674002]\n",
      "numb linkedin names:  500\n",
      "  numb. linkedin authors found:  91\n",
      "      numb. linkedin authors found but with ambiguity:  20\n",
      "  numb. linkedin authors NOT found:  409\n",
      "\n",
      "size list linkedin idx 500   unique: 500\n",
      "size master dict: 500\n",
      "tot # names on linkedin 500\n",
      "# linkedin lastnmes NOT found on wos: 409   unique: 392\n",
      "# linkedin lastnames found on wos: 91   unique: 88\n",
      "# wos lastnames: 1000000   unique: 238049\n",
      "\n",
      "overlap between all wos lastnames and linkedin lastnames not found on wos: 302\n"
     ]
    }
   ],
   "source": [
    "\n",
    "print \"linkedin_idx  wos_idx\"\n",
    "cont_found=0\n",
    "cont_ambiguedad=0\n",
    "cont_no_match=0\n",
    "for llave in master_dict_linkedin_index_wos_index:\n",
    "    if len(master_dict_linkedin_index_wos_index[llave]) >0:\n",
    "        print llave, master_dict_linkedin_index_wos_index[llave]\n",
    "        cont_found +=1\n",
    "        if len(master_dict_linkedin_index_wos_index[llave]) >1:\n",
    "            cont_ambiguedad +=1 \n",
    "    else:\n",
    "        cont_no_match +=1\n",
    "\n",
    "print \"numb linkedin names: \",len(master_dict_linkedin_index_wos_index)\n",
    "print \"  numb. linkedin authors found: \", cont_found\n",
    "print \"      numb. linkedin authors found but with ambiguity: \", cont_ambiguedad\n",
    "print \"  numb. linkedin authors NOT found: \", cont_no_match\n",
    "print \n",
    "print \"size list linkedin idx\",len(lista_linkedin_idx), \"  unique:\", len(set(lista_linkedin_idx))\n",
    "print \"size master dict:\",len(master_dict_linkedin_index_wos_index)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "list_not_found_linkedin_lastnames=[]\n",
    "list_found_linkedin_lastnames=[]\n",
    "for linkedin_idx in master_dict_linkedin_index_wos_index:\n",
    "#print linkedin_idx, pickled_master_dict_linkedin_index_wos_index[linkedin_idx]\n",
    "    if len(master_dict_linkedin_index_wos_index[linkedin_idx]) == 0:\n",
    "        list_not_found_linkedin_lastnames.append(df_linkedin_test.iloc[linkedin_idx].lastname)\n",
    "    else:\n",
    "        list_found_linkedin_lastnames.append(df_linkedin_test.iloc[linkedin_idx].lastname)\n",
    "\n",
    "\n",
    "print \"tot # names on linkedin\", len(master_dict_linkedin_index_wos_index)   # 81444\n",
    "print \"# linkedin lastnmes NOT found on wos:\",len(list_not_found_linkedin_lastnames), \"  unique:\",len(set(list_not_found_linkedin_lastnames))  #    48840   unique: 29070  \n",
    "print \"# linkedin lastnames found on wos:\",len(list_found_linkedin_lastnames), \"  unique:\",len(set(list_found_linkedin_lastnames))   #  32604   unique: 18840 \n",
    "\n",
    "\n",
    "\n",
    "\n",
    "list_lastnames_in_wos=list(df_disamb_wos_test.lastname.values)\n",
    "\n",
    "\n",
    "\n",
    "print \"# wos lastnames:\",len(list_lastnames_in_wos), \"  unique:\",len(set(list_lastnames_in_wos))\n",
    "\n",
    "\n",
    "\n",
    "print \"\\noverlap between all wos lastnames and linkedin lastnames not found on wos:\", len(list( set(list_lastnames_in_wos)  &   set(list_not_found_linkedin_lastnames)   ))   #  20104    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "LINKEDIN idx: 37    David Williams  ||  Professor  ||  Harvard University  ||  African and African American Studies\n",
      "\n",
      "  ---wos_idx: 89692 WOS_disamb_ID: 26325864\n",
      "   david williams\n",
      "   ['University Hospital Gasthuisberg', 'Institute Mental Hlth', 'St George Hospital University', 'Suny Stony Brook', 'Hospital Fernand Widal', 'Ausl Bologna', 'University Ibadan', 'Cundinamarca University', 'Sant Joan De Deu Ssm', 'University Michigan', 'University Tokyo', 'University Otago', 'Saldarriaga Concha Fdn', 'Harvard University', 'Mexican Institute Psychiat', 'Institute Municipal Invest Med', 'Minist Hlth', 'University Groningen', 'University Leipzig', 'Grp Hlth Cooperat Puget Sound']\n",
      "   [Wellington School Med & Hlth Sci, Med Center, Institute Social Res, Department Mental Hlth, Department Psychiat, Center Hlth Studies, School Med, Univ College Hospital, Grad School Med]\n",
      "   1 publ\n",
      "   2007\n",
      "\n",
      "  ---wos_idx: 91471 WOS_disamb_ID: 26289699\n",
      "   david r williams\n",
      "   ['Harvard University']\n",
      "   [School Med]\n",
      "   1 publ\n",
      "   2008\n",
      "\n",
      "  ---wos_idx: 227853 WOS_disamb_ID: 26274759\n",
      "   david r williams\n",
      "   ['Harvard University']\n",
      "   [Center Developing Child, Department African & African Amer Studies]\n",
      "   1 publ\n",
      "   2012\n",
      "\n",
      "  ---wos_idx: 268561 WOS_disamb_ID: 26329984\n",
      "   david l williams\n",
      "   ['E Tennessee State University', 'Harvard University']\n",
      "   [School Med]\n",
      "   1 publ\n",
      "   2007\n",
      "\n",
      "  ---wos_idx: 269130 WOS_disamb_ID: 26322889\n",
      "   david a williams\n",
      "   ['Boston Childrens Hospital', 'Harvard University']\n",
      "   [School Med]\n",
      "   1 publ\n",
      "   2014\n",
      "\n",
      "  ---wos_idx: 272844 WOS_disamb_ID: 26326754\n",
      "   david williams\n",
      "   ['Sanford Burnham Med Res Institute', 'Sanford Consortium Regenerat Med', 'National Institute Biol Stand & Controls', 'Massachusetts Institute of Technology', 'Stanford University', 'Nih', 'University Oxford', 'Astar', 'Dankook University', 'Tap Biosyst', 'Broad Institute Massachusetts Institute of Technology & Harvard', 'Harvard University', 'Stanford Ucsf Fda', 'University Loughborough', 'Ucl', 'Consulting Adv Biol Ltd', 'Scripps Res Institute', 'Riken', 'University of California San Diego']\n",
      "   [Center Behav Med, Department Nanobiomed Sci, Wolfson School Mech & Mfg Engn, Center Regenerat Med, Department Physiol Chem, Department Biochem Engn, Bioproc Technol Institute, Center Adv Sustainable Med Innovat, Nuffield Department Orthopaed Rheumatol & Musculoskelet, Biomat & Tissue Engn Lab, Center Mhra, Brigham & Womens Hospital, Said Business School, Institute Med Engn & Sci, Stem Cell Institute, School Med, Department Dent Biomat, Wcu Res Center, Center Dev Biol, Department Stem Cell & Regenerat Biol]\n",
      "   1 publ\n",
      "   2015\n",
      "\n",
      "  ---wos_idx: 303116 WOS_disamb_ID: 26314809\n",
      "   david r williams\n",
      "   ['Harvard University']\n",
      "   [School Publ Hlth]\n",
      "   1 publ\n",
      "   2015\n",
      "\n",
      "  ---wos_idx: 542503 WOS_disamb_ID: 26287529\n",
      "   david w williams\n",
      "   ['Eli Lilly & Co', 'University Utah', 'Indiana University', 'Stanford University', 'Nyu', 'University Penn', 'University Washington', 'Harvard University']\n",
      "   [School Med, Hlth Sci Center, Lilly Res Labs, Center Anxiety & Depress, Department Psychiat]\n",
      "   3 publ\n",
      "   2007|2009|2010\n",
      "\n",
      "  ---wos_idx: 576526 WOS_disamb_ID: 26338579\n",
      "   david r williams\n",
      "   ['Harvard University']\n",
      "   [School Publ Hlth]\n",
      "   1 publ\n",
      "   2010\n",
      "\n",
      "  ---wos_idx: 632393 WOS_disamb_ID: 26296844\n",
      "   david a williams\n",
      "   ['Harvard University']\n",
      "   [School Med]\n",
      "   1 publ\n",
      "   2012\n",
      "\n",
      "  ---wos_idx: 660344 WOS_disamb_ID: 26296884\n",
      "   david williams\n",
      "   ['Harvard University']\n",
      "   nan\n",
      "   1 publ\n",
      "   2012\n",
      "\n",
      "  ---wos_idx: 693071 WOS_disamb_ID: 26306809\n",
      "   david a williams\n",
      "   ['Dana Farber Canc Institute', 'Childrens Hospital', 'Harvard University', 'Harvard Stem Cell Institute']\n",
      "   [Department Pediat Oncol, School Med]\n",
      "   1 publ\n",
      "   2012\n",
      "\n",
      "  ---wos_idx: 696693 WOS_disamb_ID: 26296384\n",
      "   david r williams\n",
      "   ['Harvard University']\n",
      "   [School Publ Hlth]\n",
      "   1 publ\n",
      "   2009\n",
      "\n",
      "  ---wos_idx: 735784 WOS_disamb_ID: 26290894\n",
      "   david a williams\n",
      "   ['Harvard University']\n",
      "   [School Med]\n",
      "   1 publ\n",
      "   2010\n",
      "\n",
      "  ---wos_idx: 935781 WOS_disamb_ID: 26276954\n",
      "   david r williams\n",
      "   ['Harvard University']\n",
      "   [School Publ Hlth]\n",
      "   1 publ\n",
      "   2013\n"
     ]
    }
   ],
   "source": [
    "linkedin_idx =37\n",
    "\n",
    "#if len(master_dict_linkedin_index_wos_index[linkedin_idx])>1:        \n",
    "print \"\\n\\nLINKEDIN idx:\",linkedin_idx, \"  \",df_linkedin_test.iloc[linkedin_idx].full_name.title() ,\" || \", df_linkedin_test.iloc[linkedin_idx].Current_Title,\" || \", df_linkedin_test.iloc[linkedin_idx].University,\" || \", df_linkedin_test.iloc[linkedin_idx].Department\n",
    "\n",
    "\n",
    "\n",
    "for wos_indx in master_dict_linkedin_index_wos_index[linkedin_idx]:\n",
    "\n",
    "    print \"\\n  ---wos_idx:\", wos_indx, \"WOS_disamb_ID:\", df_disamb_wos_test.iloc[wos_indx].author_id\n",
    "    print \"  \", df_disamb_wos_test.iloc[wos_indx].full_name\n",
    "    print \"  \",df_disamb_wos_test.iloc[wos_indx].University\n",
    "    print \"  \", df_disamb_wos_test.iloc[wos_indx].Department\n",
    "    print \"  \",df_disamb_wos_test.iloc[wos_indx].total_pubs , \"publ\"\n",
    "    print \"  \",df_disamb_wos_test.iloc[wos_indx].year\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dirty_firstname                                                                 lin\n",
      "dirty_lastname                                                                   xu\n",
      "full_name                                                                    lin xu\n",
      "firstname                                                                       lin\n",
      "middle                                                                             \n",
      "lastname                                                                         xu\n",
      "Current_Title                                      Partner at Zuoyu Capital 左驭资本合伙人\n",
      "Email                                                        lin_xu@hms.harvard.edu\n",
      "University                                                       Harvard University\n",
      "School_college                                    Harvard School of Dental Medicine\n",
      "Department                                                    Developmental Biology\n",
      "Url                                           http://hsdm.harvard.edu/people/lin-xu\n",
      "CV_Url                                                                          NaN\n",
      "Linkedin_Url                             https://www.linkedin.com/in/linxuvariarts/\n",
      "Starting_Year_Current_Position                                                 2006\n",
      "Institution_Bachelors_Degree                                      Peking University\n",
      "Field_Bachelors_Degree            BA Field Of Study Guanghua School of Business ...\n",
      "Years_Bachelors_Degree                                                    1995-1999\n",
      "Institution_Masters                                             Stanford University\n",
      "Field_Masters                     Masters Field Of Study Center for East Asian S...\n",
      "Years_Masters                                                             1999-2001\n",
      "Institution_Phd                                                                 NaN\n",
      "Field_Phd                                                                       NaN\n",
      "Years_Phd                                                                       NaN\n",
      "Previous_Title_0                                           Founder & Senior Advisor\n",
      "Previous_Institution_0                                              VariArts Travel\n",
      "Years_0                                                                2006-present\n",
      "Department_0                                                                    NaN\n",
      "Previous_Title_1                                                                NaN\n",
      "Previous_Institution_1                                                          NaN\n",
      "                                                        ...                        \n",
      "Years_22                                                                        NaN\n",
      "Department_22                                                                   NaN\n",
      "Previous_Title_23                                                               NaN\n",
      "Previous_Institution_23                                                         NaN\n",
      "Years_23                                                                        NaN\n",
      "Department_23                                                                   NaN\n",
      "Previous_Title_24                                                               NaN\n",
      "Previous_Institution_24                                                         NaN\n",
      "Years_24                                                                        NaN\n",
      "Department_24                                                                   NaN\n",
      "Previous_Title_25                                                               NaN\n",
      "Previous_Institution_25                                                         NaN\n",
      "Years_25                                                                        NaN\n",
      "Department_25                                                                   NaN\n",
      "Previous_Title_26                                                               NaN\n",
      "Previous_Institution_26                                                         NaN\n",
      "Years_26                                                                        NaN\n",
      "Department_26                                                                   NaN\n",
      "Previous_Title_27                                                               NaN\n",
      "Previous_Institution_27                                                         NaN\n",
      "Years_27                                                                        NaN\n",
      "Department_27                                                                   NaN\n",
      "Previous_Title_28                                                               NaN\n",
      "Previous_Institution_28                                                         NaN\n",
      "Years_28                                                                        NaN\n",
      "Department_28                                                                   NaN\n",
      "Previous_Title_29                                                               NaN\n",
      "Previous_Institution_29                                                         NaN\n",
      "Years_29                                                                        NaN\n",
      "Department_29                                                                   NaN\n",
      "Name: 2125, dtype: object\n",
      "written: /home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/Merged_LinkedIn_WoS/checking_matcht_LinkedIn_WoS_1000000_ONE_name_0.9.dat\n"
     ]
    }
   ],
   "source": [
    "\n",
    "#filename_output='/home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/Merged_LinkedIn_WoS/checking_fuzzy_matcht_LinkedIn_WoS_with_repetitions.dat'\n",
    "filename_output_redbox='/home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/Merged_LinkedIn_WoS/checking_matcht_LinkedIn_WoS'+string_num_rows+'_ONE_name_0.9.dat'\n",
    "\n",
    "output=open(filename_output_redbox,'wt')\n",
    "\n",
    "for linkedin_idx in master_dict_linkedin_index_wos_index:\n",
    "\n",
    "   \n",
    "    if len(master_dict_linkedin_index_wos_index[linkedin_idx])==1:        \n",
    "        try:\n",
    "            print >> output, \"\\n\\nLINKEDIN idx:\",linkedin_idx, \"  \",df_linkedin_test.iloc[linkedin_idx].full_name.title() ,\" || \", df_linkedin_test.iloc[linkedin_idx].Current_Title,\" || \", df_linkedin_test.iloc[linkedin_idx].University,\" || \", df_linkedin_test.iloc[linkedin_idx].Department\n",
    "        \n",
    "            print >> output ,\"\"\n",
    "\n",
    "\n",
    "            for wos_indx in master_dict_linkedin_index_wos_index[linkedin_idx]:\n",
    "   \n",
    "                print >> output , \"  ---wos_idx:\", wos_indx\n",
    "                print >> output ,\"  \", df_disamb_wos_test.iloc[wos_indx].full_name\n",
    "                print >> output ,\"  \",df_disamb_wos_test.iloc[wos_indx].University\n",
    "                print >> output ,\"  \", df_disamb_wos_test.iloc[wos_indx].Department\n",
    "                print >> output ,\"  \",df_disamb_wos_test.iloc[wos_indx].total_pubs , \"publ\"\n",
    "                print >> output ,\"  \",df_disamb_wos_test.iloc[wos_indx].year\n",
    "            \n",
    "        except UnicodeEncodeError:\n",
    "            print df_linkedin_test.iloc[linkedin_idx]\n",
    "            \n",
    "            \n",
    "            \n",
    "            \n",
    "output.close()\n",
    "print \"written:\",filename_output_redbox\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "wos_with_new_column=df_disamb_wos_test.copy()\n",
    "linkedin_with_new_column=df_linkedin_test.copy()\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "wos_with_new_column=wos_with_new_column.assign(merging_idx=np.nan)\n",
    "\n",
    "linkedin_with_new_column=linkedin_with_new_column.assign(merging_idx=np.nan)\n",
    "\n",
    "\n",
    "\n",
    "# wos_with_new_column.head()\n",
    "# linkedin_with_new_column.head()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cont_merging_idx=0\n",
    "for linkedin_idx in master_dict_linkedin_index_wos_index:\n",
    "\n",
    "    try:\n",
    "        wos_idx=master_dict_linkedin_index_wos_index[linkedin_idx][0] # OJO!!! for now i only consider one of the potentialy multiple wos_idx associated to a given linkedin idx\n",
    "        wos_with_new_column.set_value(wos_idx, 'merging_idx',int(cont_merging_idx) )\n",
    "# df.set_value('C', 'x', 10)     where:   index=['A','B','C']  and columns=['x','y']\n",
    "        linkedin_with_new_column.set_value(linkedin_idx, 'merging_idx', int(cont_merging_idx) )\n",
    "\n",
    "\n",
    "\n",
    "    except IndexError:\n",
    "        linkedin_with_new_column.set_value(linkedin_idx, 'merging_idx', 999999999 )\n",
    "\n",
    "\n",
    "#         print linkedin_idx, pickled_master_dict_linkedin_index_wos_index[linkedin_idx]\n",
    "#         raw_input()\n",
    "\n",
    "\n",
    "    cont_merging_idx +=1\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "linkedin_with_new_column=linkedin_with_new_column.rename(columns = {'firstname':'firstname_linkedin'})\n",
    "linkedin_with_new_column=linkedin_with_new_column.rename(columns = {'lastname':'lastname_linkedin'})\n",
    "linkedin_with_new_column=linkedin_with_new_column.rename(columns = {'full_namename':'full_name_linkedin'})\n",
    "linkedin_with_new_column=linkedin_with_new_column.rename(columns = {'middle':'middle_linkedin'})\n",
    "\n",
    "linkedin_with_new_column=linkedin_with_new_column.rename(columns = {'University':'University_linkedin'})\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "df_merged = pd.merge(linkedin_with_new_column, wos_with_new_column, how='left',on='merging_idx')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "csv done: /home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/Merged_LinkedIn_WoS/Merged_linkedin_wos_10000.tsv\n",
      "pickle done: /home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/Merged_LinkedIn_WoS/Merged_linkedin_wos_10000.pickle\n"
     ]
    },
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-39-7d4e2d0245c1>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     23\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     24\u001b[0m \u001b[0;31m# Convert the dataframe to an XlsxWriter Excel object.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 25\u001b[0;31m \u001b[0mdf_merged\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_excel\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mwriter\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msheet_name\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'Sheet1'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     26\u001b[0m \u001b[0;31m# Close the Pandas Excel writer and output the Excel file.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     27\u001b[0m \u001b[0mwriter\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msave\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/juliaponcela/anaconda2/lib/python2.7/site-packages/pandas/core/frame.pyc\u001b[0m in \u001b[0;36mto_excel\u001b[0;34m(self, excel_writer, sheet_name, na_rep, float_format, columns, header, index, index_label, startrow, startcol, engine, merge_cells, encoding, inf_rep, verbose)\u001b[0m\n\u001b[1;32m   1423\u001b[0m         \u001b[0mformatted_cells\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mformatter\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_formatted_cells\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1424\u001b[0m         excel_writer.write_cells(formatted_cells, sheet_name,\n\u001b[0;32m-> 1425\u001b[0;31m                                  startrow=startrow, startcol=startcol)\n\u001b[0m\u001b[1;32m   1426\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mneed_save\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1427\u001b[0m             \u001b[0mexcel_writer\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msave\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/juliaponcela/anaconda2/lib/python2.7/site-packages/pandas/io/excel.pyc\u001b[0m in \u001b[0;36mwrite_cells\u001b[0;34m(self, cells, sheet_name, startrow, startcol)\u001b[0m\n\u001b[1;32m   1461\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1462\u001b[0m             \u001b[0mnum_format_str\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1463\u001b[0;31m             \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcell\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mval\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1464\u001b[0m                 \u001b[0mnum_format_str\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdatetime_format\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1465\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcell\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mval\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdate\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "\n",
    "#path_merged_cluster='/home/julia/Dropbox_collaborations/Data/Merged_LinkedIn_WoS/'\n",
    "\n",
    "path_merged='/home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/Merged_LinkedIn_WoS/'\n",
    "\n",
    "\n",
    "merged_file_name=path_merged+\"Merged_linkedin_wos\"+string_num_rows+\".tsv\"    \n",
    "\n",
    "df_merged.to_csv(merged_file_name, sep='\\t', encoding='utf-8')#, columns = list_headers)\n",
    "print \"csv done:\", merged_file_name\n",
    "\n",
    "\n",
    "df_merged.to_pickle(merged_file_name.split(\".tsv\")[0]+\".pickle\")\n",
    "print \"pickle done:\" ,merged_file_name.split(\".tsv\")[0]+\".pickle\"\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "writer = pd.ExcelWriter(path_merged+\"Merged_linkedin_wos\"+string_num_rows+\".xlsx\", engine='xlsxwriter',options={'strings_to_urls': False})\n",
    "\n",
    "# Convert the dataframe to an XlsxWriter Excel object.\n",
    "df_merged.to_excel(writer, sheet_name='Sheet1')\n",
    "# Close the Pandas Excel writer and output the Excel file.\n",
    "writer.save()\n",
    "\n",
    "print \"xlsx done:\", path_merged+\"Merged_linkedin_wos\"+string_num_rows+\".xlsx\"\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "print \"Number of unique idx in merged df:\",len(df_merged.merging_idx.unique())\n"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}