merge_wos_linkedin_from_pickle_master_dict.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",
    "from tqdm import tqdm\n",
    "from tqdm import tnrange, tqdm_notebook\n",
    "from time import sleep\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": true
   },
   "outputs": [],
   "source": [
    "\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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2min 2s, sys: 7.69 s, total: 2min 10s\n",
      "Wall time: 6min 28s\n"
     ]
    }
   ],
   "source": [
    "\n",
    "path_wos_linux='/home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/WoS_data/Disambiguated_authors/'\n",
    "\n",
    "\n",
    "%time df_disamb_wos_test=pd.read_csv(path_wos_linux+'wos_author_disambiguated_2000-2015_USA_processed.tsv', sep=\"\\t\")#, nrows=1000000)\n",
    "\n",
    "#%time df_disamb_wos_test=pd.read_pickle(path_wos_linux+'wos_author_disambiguated_2000-2015_USA_processed.pickle')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\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",
    "\n",
    "\n",
    "\n",
    "df_disamb_wos_test = df_disamb_wos_test.rename(columns={'total_pubs': 'total_publications'})\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path_linkedin_linux=\"/home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/Vinu_University_Sheets/Improved_3Feb_and_more_emails_27Feb/All_linkedIn.pickle\"\n",
    "\n",
    "df_linkedin_test=pd.read_pickle(path_linkedin_linux)\n",
    "\n",
    "\n",
    "\n",
    "df_linkedin_test = df_linkedin_test.rename(columns={'Department(s)': 'Department', 'School/college': 'School_college'})\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print df_linkedin_test.shape, df_disamb_wos_test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "list_headers=[\"dirty_firstname\",\"dirty_lastname\",\"full_name\", \"firstname\",\"middle\",\"lastname\",\"Current_Title\",\"Email\",\"University\",\"School_college\",\"Department\",\"Url\",\"CV_Url\",\"Linkedin_Url\",\"Starting_Year_Current_Position\",\"Institution_Bachelors_Degree\",\"Field_Bachelors_Degree\",\"Years_Bachelors_Degree\",\"Institution_Masters\",\"Field_Masters\",\"Years_Masters\",\"Institution_Phd\",\"Field_Phd\",\"Years_Phd\",\"Previous_Title_0\",\"Previous_Institution_0\",\"Years_0\",\"Department_0\",\"Previous_Title_1\",\"Previous_Institution_1\",\"Years_1\",\"Department_1\",\"Previous_Title_2\",\"Previous_Institution_2\",\"Years_2\",\"Department_2\",\"Previous_Title_3\",\"Previous_Institution_3\",\"Years_3\",\"Department_3\",\"Previous_Title_4\",\"Previous_Institution_4\",\"Years_4\",\"Department_4\",\"Previous_Title_5\",\"Previous_Institution_5\",\"Years_5\",\"Department_5\",\"Previous_Title_6\",\"Previous_Institution_6\",\"Years_6\",\"Department_6\",\"Previous_Title_7\",\"Previous_Institution_7\",\"Years_7\",\"Department_7\",\"Previous_Title_8\",\"Previous_Institution_8\",\"Years_8\",\"Department_8\",\"Previous_Title_9\",\"Previous_Institution_9\",\"Years_9\",\"Department_9\",\"Previous_Title_10\",\"Previous_Institution_10\",\"Years_10\",\"Department_10\",\"Previous_Title_11\",\"Previous_Institution_11\",\"Years_11\",\"Department_11\",\"Previous_Title_12\",\"Previous_Institution_12\",\"Years_12\",\"Department_12\",\"Previous_Title_13\",\"Previous_Institution_13\",\"Years_13\",\"Department_13\",\"Previous_Title_14\",\"Previous_Institution_14\",\"Years_14\",\"Department_14\",\"Previous_Title_15\",\"Previous_Institution_15\",\"Years_15\",\"Department_15\",\"Previous_Title_16\",\"Previous_Institution_16\",\"Years_16\",\"Department_16\",\"Previous_Title_17\",\"Previous_Institution_17\",\"Years_17\",\"Department_17\",\"Previous_Title_18\",\"Previous_Institution_18\",\"Years_18\",\"Department_18\",\"Previous_Title_19\",\"Previous_Institution_19\",\"Years_19\",\"Department_19\",\"Previous_Title_20\",\"Previous_Institution_20\",\"Years_20\",\"Department_20\",\"Previous_Title_21\",\"Previous_Institution_21\",\"Years_21\",\"Department_21\",\"Previous_Title_22\",\"Previous_Institution_22\",\"Years_22\",\"Department_22\",\"Previous_Title_23\",\"Previous_Institution_23\",\"Years_23\",\"Department_23\",\"Previous_Title_24\",\"Previous_Institution_24\",\"Years_24\",\"Department_24\",\"Previous_Title_25\",\"Previous_Institution_25\",\"Years_25\",\"Department_25\",\"Previous_Title_26\",\"Previous_Institution_26\",\"Years_26\",\"Department_26\",\"Previous_Title_27\",\"Previous_Institution_27\",\"Years_27\",\"Department_27\",\"Previous_Title_28\",\"Previous_Institution_28\",\"Years_28\",\"Department_28\",\"Previous_Title_29\",\"Previous_Institution_29\",\"Years_29\",\"Department_29\"]\n",
    " \n",
    "df_linkedin_test = df_linkedin_test[list_headers]  \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\n",
    "new_file_name='/home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/'+\"All_linkedIn_processed.csv\"  ## OJO!!! ESTE CREA UNAS 300 LINEAS EXTRAS (POR SALTOS DE CARRO ERRONEOS): USAR MEJOR EL FORMATO XLSX\n",
    "df_linkedin_test.to_csv(new_file_name, sep='\\t', encoding='utf-8', columns = list_headers)\n",
    "\n",
    "print \"csv done\"\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "writer = pd.ExcelWriter('/home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/'+\"All_linkedIn_processed.xlsx\", engine='xlsxwriter',options={'strings_to_urls': False})\n",
    "# Convert the dataframe to an XlsxWriter Excel object.\n",
    "df_linkedin_test.to_excel(writer, sheet_name='Sheet1')\n",
    "## OJO! hay url muy largas que hacen que el sistema se cuelgue (solucion: no permitirle que las considere url, sino simplemente str)\n",
    "\n",
    "# Close the Pandas Excel writer and output the Excel file.\n",
    "writer.save()\n",
    "\n",
    "print \"xlsx done\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "path_merge_linux='/home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/Merged_LinkedIn_WoS/'\n",
    "\n",
    "\n",
    "string_fuzzy='NO'\n",
    "\n",
    "\n",
    "if string_fuzzy == \"YES\":\n",
    "# filename_pickle=path_merge_linux+\"master_dict_linkedin_index_wos_index.pickle\"\n",
    "    filename_pickle=path_merge_linux+\"master_dict_linkedin_index_wos_index_fuzzy_matching_name_and_univ_10000.pickle\"\n",
    "else:\n",
    "     filename_pickle=path_merge_linux+\"master_dict_linkedin_index_wos_index.pickle\"\n",
    "\n",
    "\n",
    "\n",
    "master_dict_linkedin_index_wos_index=pd.read_pickle(filename_pickle)\n",
    "\n",
    "\n",
    "list_wos_with_repetition=[]\n",
    "\n",
    "\n",
    "print len(master_dict_linkedin_index_wos_index)\n",
    "\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",
    "        \n",
    "            list_wos_with_repetition.extend(master_dict_linkedin_index_wos_index[llave])\n",
    "            \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",
    "   \n",
    "    \n",
    "print \"# wos index with repetitions:\"    , len(list_wos_with_repetition)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\n",
    "# df_wos_only_with_repetitions = df_disamb_wos_test.iloc[list_wos_with_repetition]\n",
    "# df_wos_only_with_repetitions.shape\n",
    "\n",
    "# ### rearrange columns' order  (and select the ones i want for writing out)\n",
    "\n",
    "# list_headers=[\"author_id\",\"year\",\"author_names\",\"total_pubs\",\"Department\",\"University\",\"affiliations\"]\n",
    "\n",
    "# df_wos_only_with_repetitions = df_wos_only_with_repetitions[list_headers]  \n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# path='/home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/Merged_LinkedIn_WoS/'\n",
    "\n",
    "# df_wos_only_with_repetitions.to_csv(path+\"df_wos_only_authors_with_repetitions.tsv\", sep='\\t', )\n",
    "# print \"written:\", path+\"df_wos_only_authors_with_repetitions.tsv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print \"linkedin  wos\"\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 \"# linkedin names: \",len(master_dict_linkedin_index_wos_index)\n",
    "print \"  # linkedin authors found: \", cont_found\n",
    "print \"      # linkedin authors found but with ambiguity: \", cont_ambiguedad\n",
    "print \"  # link.firstname_or_initialedin authors NOT found: \", cont_no_match\n",
    "print \"\\n\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print df_disamb_wos_test.iloc[5606]\n",
    "# print df_linkedin_test.iloc[83010]\n",
    "\n",
    "\n",
    "\n",
    "if string_fuzzy == \"YES\":\n",
    "    filename_output_redbox='/home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/Merged_LinkedIn_WoS/checking_fuzzy_matcht_LinkedIn_WoS_multiple_names.dat'\n",
    "\n",
    "else:\n",
    "#filename_output_cluster='/home/julia/Dropbox_collaborations/Data/Merged_LinkedIn_WoS/checking_matcht_LinkedIn_WoS_with_repetitions'+string_num_rows+'.dat'\n",
    "    filename_output_redbox='/home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/Merged_LinkedIn_WoS/checking_matcht_LinkedIn_WoS_multiple_names.dat'\n",
    "\n",
    "    \n",
    "    \n",
    "output=open(filename_output_redbox,'wt')\n",
    "\n",
    "for linkedin_idx in master_dict_linkedin_index_wos_index:\n",
    "   try:\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",
    "        print \"\"\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",
    "#         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_publications , \"publ\"\n",
    "#             print >> output ,\"  \",df_disamb_wos_test.iloc[wos_indx].year\n",
    "            \n",
    "            print  \"  ---wos_idx:\", wos_indx\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_publications , \"publ\"\n",
    "            print \"  \",df_disamb_wos_test.iloc[wos_indx].year\n",
    "            \n",
    "   except IndexError       : pass\n",
    "            \n",
    "output.close()\n",
    "print \"written:\",filename_output_cluster\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "linkedin_idx=33546\n",
    "\n",
    "\n",
    "print df_disamb_wos_test.iloc[5606]\n",
    "print df_linkedin_test.iloc[83010]\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "print \"LINKEDIN profile:\"\n",
    "print df_linkedin_test.iloc[linkedin_idx].full_name      \n",
    "print df_linkedin_test.iloc[linkedin_idx].Current_Title\n",
    "print df_linkedin_test.iloc[linkedin_idx].University      \n",
    "print df_linkedin_test.iloc[linkedin_idx].Department      \n",
    " \n",
    "    \n",
    "print\n",
    "print\n",
    "print \"different WoS profiles:\"\n",
    "for wos_indx in master_dict_linkedin_index_wos_index[linkedin_idx]:    \n",
    "    print wos_indx\n",
    "    wos_indx=int(wos_indx)\n",
    "    print \"   \", df_disamb_wos_test.iloc[wos_indx].author_names#, df_wos_only_with_repetitions.ix[wos_indx].author_names   # OJO!!! .iloc[]  looks for the row by the physical position, in relation to the actual length of the df, and .ix[]  by the index (and an index can be int or sth else)\n",
    "    print \"   \",df_disamb_wos_test.iloc[wos_indx].University#, df_wos_only_with_repetitions.ix[wos_indx].University\n",
    "    print \"   \",df_disamb_wos_test.iloc[wos_indx].Department#, df_wos_only_with_repetitions.ix[wos_indx].Department    \n",
    "    print \"   \",df_disamb_wos_test.iloc[wos_indx].total_publications #, \"publications\", df_wos_only_with_repetitions.ix[wos_indx].total_pubs , \"publications\"\n",
    "    print \"-----\"\n",
    "  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print df_disamb_wos_test.index\n",
    "print df_wos_only_with_repetitions.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for item in df_disamb_wos_test.columns:\n",
    "    print item"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "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",
    "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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "\n",
    "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",
    "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",
    "df_merged = pd.merge(linkedin_with_new_column, wos_with_new_column, how='left',on='merging_idx')\n",
    "\n",
    "\n",
    "\n",
    "path_merged='/home/juliaponcela/at_NICO/Dropbox_collaboration_patterns/Data/Merged_LinkedIn_WoS/'\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "merged_file_name=path_merged+\"Merged_linkedin_wos.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",
    "writer = pd.ExcelWriter(path_merged+\"Merged_linkedin_wos.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.xlsx\"\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "df_merged.to_pickle(merged_file_name.split(\".csv\")[0]+\".pickle\")\n",
    "print \"pickle done:\"  ,merged_file_name.split(\".csv\")[0]"
   ]
  }
 ],
 "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
}