fuzzy_matching_names.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re \n",
    "import pandas as pd  \n",
    "import numpy as np\n",
    "import ast"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The task here is to clean a dataset of thousands of names associated with ID numbers... \n",
    "***-- Many of these ID numbers are associated with more than one name;***\n",
    "\n",
    "***-- Many of those duplicate names are the same name spelt differently;***\n",
    "\n",
    "***-- some of them are different people erroneously associated with the same ID number.***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read-in Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in CSV data and parse-normalize dates \n",
    "parse_dates = ['Date of Birth']\n",
    "names = pd.read_csv('test_data/duplicates_apps.csv', encoding=\"utf8\",parse_dates=parse_dates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# delete obsolete unnamed column\n",
    "names.columns[0]\n",
    "del names[names.columns[0]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of duplicate rows deleted: 3\n"
     ]
    }
   ],
   "source": [
    "# Find and delete duplicate= rows accross all columns:\n",
    "#duplicateRowsDF = names[names.duplicated()]\n",
    "#print(\"Duplicate Rows except first occurrence based on all columns are :\")\n",
    "#print(duplicateRowsDF\n",
    "names_set = names.drop_duplicates()\n",
    "print(\"Number of duplicate rows deleted:\", len(names[names.duplicated()]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Input index column for referencing changes to origin dataframe\n",
    "Names_set=names_set.copy()\n",
    "Names_set['index'] = names_set.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert NaN values to empty strings and lowercase all name values\n",
    "Names_set['Employee Name']=Names_set['Employee Name'].replace(np.nan, '', regex=True)\n",
    "Names_set['Family Name']=Names_set['Family Name'].replace(np.nan, '', regex=True)\n",
    "Names_set['Employee Name']=Names_set['Employee Name'].str.lower()\n",
    "Names_set['Family Name']=Names_set['Family Name'].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Strip each values of leading or trailing whitespace and remove extra whitespaces\n",
    "Names_set['Employee Name']=Names_set['Employee Name'].str.strip()\n",
    "Names_set['Family Name']=Names_set['Family Name'].str.strip()\n",
    "\n",
    "Names_set['Employee Name'] = Names_set['Employee Name'].apply(lambda x: re.sub(r' +', ' ', x))\n",
    "Names_set['Family Name'] = Names_set['Family Name'].apply(lambda x: re.sub(r' +', ' ', x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "FullNames = Names_set.loc[:, ['Employee Name','Family Name']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prep text by converting NaN values to empty strings\n",
    "import numpy as np\n",
    "#EmployeeNames = EmployeeNames.replace(np.nan, '', regex=True)\n",
    "#FamilyNames = FamilyNames.replace(np.nan, '', regex=True)\n",
    "FullNames = FullNames.replace(np.nan, '', regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "FullNames['index'] = FullNames.index # Set Index for new dataframe\n",
    "FullNames[\"FullName\"] = FullNames[\"Employee Name\"] + ' ' + FullNames[\"Family Name\"] # Set new column of joined name parts\n",
    "FullNames[\"FullName\"]=FullNames[\"FullName\"].str.replace('.','')\n",
    "FullNames[\"FullName\"]=FullNames[\"FullName\"].str.strip()\n",
    "#EmployeeNames = Names_set.loc[:, ['Employee Name']]\n",
    "#FamilyNames = Names_set.loc[:, ['Family Name']]\n",
    "#EmployeeNames['index'] = EmployeeNames.index\n",
    "#FamilyNames['index'] = FamilyNames.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## N-Grams\n",
    "n-grams: sequences of N contiguous items, in this case characters.\n",
    "\n",
    "The terms in TF-IDF can be n-grams instead of words!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This function cleans a string then generates all n-grams in the string\n",
    "def ngrams(string, n=3):\n",
    "    string = re.sub(r'[,-./]',r' ', string) # Remove simple punctuation, could always add more here...\n",
    "    #string = re.sub(r'[,-./]|\\sYOURSTRING',r'', string) #Replace YOURSTRING with any letters that may cause bias and need to be replaced.\n",
    "    ngrams = zip(*[string[i:] for i in range(n)])\n",
    "    return [''.join(ngram) for ngram in ngrams]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TF-IDF\n",
    "Generates features from text by means of multiplying the frequency of a term in a document (TF)\n",
    "by the importance (IDF) of that term throughout the corpus. \n",
    "IDF weights less important words down and words that don’t occur frequently up.\n",
    "\n",
    "IDF(t) = log_e(Total number of documents / Number of documents with term t in it).\n",
    "\n",
    "TF-IDF is used to transform documents into numeric vectors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(53, 329)\n"
     ]
    }
   ],
   "source": [
    "# Here is the code to generate a matrix of TF-IDF values for each n-gram\n",
    "# https://scikit-learn.org/stable/modules/generated/sklearn.feature_extraction.text.TfidfVectorizer.html\n",
    "\n",
    "from sklearn.feature_extraction.text import TfidfVectorizer\n",
    "\n",
    "FullNamesList = list(set(list(FullNames['FullName'])))\n",
    "#EmployeeNamesList = list(EmployeeNames['Employee Name'])\n",
    "#EmployeeNamesList=list(set(EmployeeNamesList))\n",
    "#FamilyNamesList = list(FamilyNames['Family Name'])\n",
    "#FamilyNamesList=list(set(FamilyNamesList))\n",
    "\n",
    "vectorizer = TfidfVectorizer(min_df=1, analyzer=ngrams) #analyzer{‘word’, ‘char’, ‘char_wb’} or callable, default=’word’ so we feature should be made of word or character n-grams\n",
    "tf_idf_matrix = vectorizer.fit_transform(FullNamesList)\n",
    "#tf_idf_matrix = vectorizer.fit_transform(EmployeeNamesList)\n",
    "#tf_idf_matrix2 = vectorizer.fit_transform(FamilyNamesList)\n",
    "print(tf_idf_matrix.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Alternative option for Cosine similarity provided by \n",
    "https://github.com/ing-bank/sparse_dot_topn\n",
    "\n",
    "https://bergvca.github.io/2017/10/14/super-fast-string-matching.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "from scipy.sparse import csr_matrix\n",
    "import sparse_dot_topn.sparse_dot_topn as ct\n",
    "\n",
    "# Stores only the top N highest matches in each row, and only the similarities above an (optional) threshold.\n",
    "def awesome_cossim_top(A, B, ntop, lower_bound=0):\n",
    "    # force A and B as a CSR matrix.\n",
    "    # If they have already been CSR, there is no overhead\n",
    "    A = A.tocsr()\n",
    "    B = B.tocsr()\n",
    "    M, _ = A.shape\n",
    "    _, N = B.shape\n",
    " \n",
    "    idx_dtype = np.int32\n",
    " \n",
    "    nnz_max = M*ntop\n",
    " \n",
    "    indptr = np.zeros(M+1, dtype=idx_dtype)\n",
    "    indices = np.zeros(nnz_max, dtype=idx_dtype)\n",
    "    data = np.zeros(nnz_max, dtype=A.dtype)\n",
    "\n",
    "    ct.sparse_dot_topn(\n",
    "        M, N, np.asarray(A.indptr, dtype=idx_dtype),\n",
    "        np.asarray(A.indices, dtype=idx_dtype),\n",
    "        A.data,\n",
    "        np.asarray(B.indptr, dtype=idx_dtype),\n",
    "        np.asarray(B.indices, dtype=idx_dtype),\n",
    "        B.data,\n",
    "        ntop,\n",
    "        lower_bound,\n",
    "        indptr, indices, data)\n",
    "\n",
    "    return csr_matrix((data,indices,indptr),shape=(M,N))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**The following code runs the optimized cosine similarity function.**\n",
    "\n",
    "**It only stores the top n most similar items, and only items with a similarity above n.nn:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELFTIMED: 0.000997781753540039\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "t1 = time.time()\n",
    "FullNamesMatches = awesome_cossim_top(tf_idf_matrix, tf_idf_matrix.transpose(), 1000, 0.30)\n",
    "#EmployeeNamesMatches = awesome_cossim_top(tf_idf_matrix, tf_idf_matrix.transpose(), 1000, 0.20)\n",
    "#FamilyNamesMatches = awesome_cossim_top(tf_idf_matrix2, tf_idf_matrix2.transpose(), 1000, 0.20)\n",
    "\n",
    "#matches = cosine_similarity(tf_idf_matrix, tf_idf_matrix.transpose())\n",
    "t = time.time()-t1\n",
    "print(\"SELFTIMED:\", t)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Unpack the sparse matrix with added option to look at only the first n values:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_matches_df(sparse_matrix, name_vector, top=1000):\n",
    "    non_zeros = sparse_matrix.nonzero()\n",
    "    \n",
    "    sparserows = non_zeros[0]\n",
    "    sparsecols = non_zeros[1]\n",
    "    \n",
    "    if top:\n",
    "        nr_matches = top\n",
    "    else:\n",
    "        nr_matches = sparsecols.size\n",
    "    \n",
    "    left_side = np.empty([nr_matches], dtype=object)\n",
    "    right_side = np.empty([nr_matches], dtype=object)\n",
    "    similarity = np.zeros(nr_matches)\n",
    "    \n",
    "    for index in range(0, nr_matches):\n",
    "        left_side[index] = name_vector[sparserows[index]]\n",
    "        right_side[index] = name_vector[sparsecols[index]]\n",
    "        similarity[index] = sparse_matrix.data[index]\n",
    "    \n",
    "    return pd.DataFrame({'left_side': left_side,\n",
    "                          'right_side': right_side,\n",
    "                           'similarity': similarity})\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**View matches in dataframe matches_df**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "FullNamesMatchesDF = get_matches_df(FullNamesMatches, FullNamesList, top=len(FullNamesList)) # n in 'top=n' can only be within bounds of index on dataset\n",
    "FullNamesMatchesDF = FullNamesMatchesDF[FullNamesMatchesDF['similarity'] < 0.99999] # Remove all exact matches\n",
    "\n",
    "#EmployeeNamesMatchesDF = get_matches_df(EmployeeNamesMatches, EmployeeNamesList, top=len(EmployeeNamesList)) # n in 'top=n' can only be within bounds of index on dataset\n",
    "#EmployeeNamesMatchesDF = EmployeeNamesMatchesDF[EmployeeNamesMatchesDF['similarity'] < 0.99999] # Remove all exact matches\n",
    "#FamilyNamesMatchesDF = get_matches_df(FamilyNamesMatches, FamilyNamesList, top=len(FamilyNamesList)) # n in 'top=n' can only be within bounds of index on dataset\n",
    "#FamilyNamesMatchesDF = FamilyNamesMatchesDF[FamilyNamesMatchesDF['similarity'] < 0.99999] # Remove all exact matches\n",
    "#matches_df.sample(5) #will fail here if less results are found that samples requested "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(list(FullNamesMatchesDF['right_side']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Show full dataframe\n",
    "pd.set_option('max_rows', None)\n",
    "pd.set_option('max_colwidth', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#FullNamesMatchesDF.sort_values(['similarity'], ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Remove duplicated entities criss-crossing both columns**\n",
    "**by converting to frozenset and using pd.DataFrame.duplicated:**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_matches_df = FullNamesMatchesDF[~FullNamesMatchesDF[['left_side', 'right_side']].apply(frozenset, axis=1).duplicated()]\n",
    "#final_matches_df.sort_values(['similarity'], ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Use pivot to append duplicate rows in DataFrame to right hand side as new columns then do the same from the left hand side. Append each result as a dictionary to a list and then combine the two dictionaries to create a dictionary of similar values**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Instantiate list to hold dictionaries: \n",
    "results=[]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "#append duplicate rows in DataFrame to right hand side as new columns\n",
    "#final_matches_df = final_matches_df.loc[:, ['left_side','right_side']]\n",
    "result = (final_matches_df.assign(count=final_matches_df.groupby(\"left_side\").cumcount())\n",
    "            .pivot(index='left_side', columns='count'))\n",
    "\n",
    "result.columns = [\"_\".join(str(x) for x in i) for i in result.columns]\n",
    "#result.info()\n",
    "result['left_side'] = result.index \n",
    "result = result.replace(np.nan, '', regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "numbRightsideCols = len([c for c in result.columns if c.count('right_side')>0])\n",
    "for i in range(numbRightsideCols):\n",
    "    col1 = 'right_side_'+str(i)\n",
    "    similarity_col = 'similarity_'+str(i)\n",
    "    result[col1] = result[[col1,similarity_col]].apply(lambda row: tuple(row.values.astype(str)), axis=1)\n",
    "    del result[similarity_col] # Delete similarity column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "result2 = (final_matches_df.assign(count=final_matches_df.groupby(\"right_side\").cumcount())\n",
    "            .pivot(index='right_side', columns='count'))\n",
    "\n",
    "result2.columns = [\"_\".join(str(x) for x in i) for i in result2.columns]\n",
    "#result.info()\n",
    "result2['right_side'] = result2.index \n",
    "result2 = result2.replace(np.nan, '', regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['left_side_0', 'left_side_1', 'similarity_0', 'similarity_1',\n",
       "       'right_side'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "numbLeftsideCols = len([c for c in result2.columns if c.count('left_side')>0])\n",
    "for i in range(numbLeftsideCols):\n",
    "    col1 = 'left_side_'+str(i)\n",
    "    similarity_col = 'similarity_'+str(i)\n",
    "    result2[col1] = result2[[col1,similarity_col]].apply(lambda row: tuple(row.values.astype(str)), axis=1)\n",
    "    #result2[col1] = result2[col1].replace(\"('', '')\", '', regex=False)\n",
    "    del result2[similarity_col] # Delete similarity column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "#result.set_index('left_side').to_dict()\n",
    "left_result = result.set_index('left_side').T.to_dict('list')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "for k,v in left_result.items():\n",
    "    left_result[k] = [n for n in v if n != ('', '')]\n",
    "results.append(left_result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "right_result = result2.set_index('right_side').T.to_dict('list')\n",
    "for k,v in right_result.items():\n",
    "    right_result[k] = [n for n in v if n != ('', '')]\n",
    "results.append(right_result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "def merge_dictionary_list(dict_list):\n",
    "  return {\n",
    "    k: [d.get(k) for d in dict_list if k in d] # explanation A\n",
    "    for k in set().union(*dict_list) # explanation B\n",
    "  }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "complete_results = merge_dictionary_list(results)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Store All Associationed Names in Dictionary (term_similarity_table):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "import itertools\n",
    "term_similarity_table={}\n",
    "for k,v in complete_results.items():\n",
    "    #flattened_list = [i for sublist in v for i in sublist]\n",
    "    #complete_results[k]=flattened_list\n",
    "    merged = list(set(list(itertools.chain.from_iterable(v))))\n",
    "    term_similarity_table[k]=merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of duplicate rows deleted: 0\n",
      "54\n"
     ]
    }
   ],
   "source": [
    "# Find and delete duplicate rows accross all columns after lowercasing and stripping the content:\n",
    "Names_set = Names_set.drop_duplicates()\n",
    "print(\"Number of duplicate rows deleted:\", len(Names_set[Names_set.duplicated()]))\n",
    "\n",
    "# Select all duplicate rows based on multiple column names in list\n",
    "#duplicateAPPSid = names_set[names_set.duplicated(['APPS ID'])]\n",
    "duplicateAPPSid = pd.concat(row for _, row in Names_set.groupby(\"APPS ID\") if len(row) > 1)\n",
    "print(len(duplicateAPPSid))\n",
    "#duplicateAPPSid_duplBirths = pd.concat(row for _, row in Names_set.groupby(\"Date of Birth\") if len(row) > 1)\n",
    "#print(len(duplicateAPPSid_duplBirths))\n",
    "\n",
    "#duplicateAPPSid.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "duplicateAPPSid[\"FullName\"] = duplicateAPPSid[\"Employee Name\"] + ' ' + duplicateAPPSid[\"Family Name\"] # Set new column of joined name parts\n",
    "duplicateAPPSid[\"FullName\"]=duplicateAPPSid[\"FullName\"].str.replace('.','')\n",
    "duplicateAPPSid[\"FullName\"]=duplicateAPPSid[\"FullName\"].str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "duplicateAPPSid=duplicateAPPSid.sort_values(['APPS ID'], ascending=True)\n",
    "duplicateIDs = list(set([i for i in duplicateAPPSid['APPS ID']]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "def intersection(lst1, lst2):\n",
    "    lst3 = [value for value in lst1 if value in lst2]\n",
    "    return lst3\n",
    "sameIdBirthName = {}\n",
    "sameIdBirth = {}\n",
    "for i in duplicateIDs:\n",
    "    valuesList=[]\n",
    "    df=duplicateAPPSid.loc[duplicateAPPSid['APPS ID'] == i]\n",
    "    \n",
    "    if len(df[df.duplicated(['FullName'])])>0:\n",
    "        alikes=[]\n",
    "        df_name_match = pd.concat(row for _, row in df.groupby('FullName') if len(row) > 1)\n",
    "        for r in range(len(df_name_match)):\n",
    "            alikes.append(df_name_match['index'].iloc[r])\n",
    "        alikes=sorted(alikes)\n",
    "        sameIdBirthName[alikes[0]]=alikes[1:]\n",
    "        \n",
    "    elif len(df[df.duplicated(['Date of Birth'])])>0:\n",
    "        df_birthdays = pd.concat(row for _, row in df.groupby('Date of Birth') if len(row) > 1)\n",
    "        for r in range(len(df_birthdays)):\n",
    "            valuesList.append((df_birthdays['FullName'].iloc[r].lower().strip(),df_birthdays['index'].iloc[r]))\n",
    "        alikes2 = []\n",
    "        almostalikes=[]\n",
    "        for idx,v in enumerate(valuesList):\n",
    "            if v[0] in term_similarity_table.keys():\n",
    "                if len(intersection([n[0] for n in term_similarity_table[v[0]]],[t[0] for t in valuesList]))>0:\n",
    "                    alikes2.append(v[1])\n",
    "                else:\n",
    "                    almostalikes.append(v[1])\n",
    "        if alikes2!=[]:\n",
    "            alikes2 = sorted(alikes2)\n",
    "            sameIdBirthName[alikes2[0]]=alikes2[1:]\n",
    "        if almostalikes != []:\n",
    "            almostalikes = sorted(intersection(almostalikes,[t[1] for t in valuesList]))\n",
    "            sameIdBirth[almostalikes[0]]=almostalikes[1:]\n",
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "sameIdBirthNameDF = pd.DataFrame(columns=Names_set.columns)\n",
    "for idx,val in sameIdBirthName.items():\n",
    "    cnt=1\n",
    "    for rowidx in val:\n",
    "        newrow = 'Alternative Entry' + str(cnt)\n",
    "        #cond = Names_set.index = rowidx\n",
    "        rows = Names_set.loc[Names_set.index == rowidx, :]\n",
    "        sameIdBirthNameDF = sameIdBirthNameDF.append(rows, ignore_index=False)\n",
    "        sameIdBirthNameDF = sameIdBirthNameDF.replace(np.nan, '', regex=True)\n",
    "        Names_set.loc[idx, newrow] = str([ele for ele in sameIdBirthNameDF.loc[rowidx, :].values.tolist() if ele!=''])\n",
    "        Names_set.drop(rows.index, inplace=True)\n",
    "        cnt+=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "#sameIdBirthNameDF\n",
    "#sameIdBirthNameDF['index'].iloc[rowidx]=idx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "Names_set = Names_set.replace(np.nan, '', regex=True)\n",
    "#Names_set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# writing to Excel\n",
    "dataframe2Excel = pd.ExcelWriter('duplicates_apps_Cleaned.xlsx')\n",
    "  \n",
    "# write DataFrame to excel\n",
    "Names_set.to_excel(dataframe2Excel)\n",
    "  \n",
    "# save the excel\n",
    "dataframe2Excel.save()\n",
    "dataframe2Excel.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## LEFT OFF HERE 7/16/2021"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "AlternativesDF= sameIdBirthNameDF.reindex(list(range(0,sameIdBirthNameDF.index.max()+1)),fill_value='')\n",
    "Cleaned_Names_set = Names_set.reindex(list(range(0,Names_set.index.max()+1)),fill_value='')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(sameIdBirthNameDF)):\n",
    "    rowindex=sameIdBirthNameDF['index'].iloc[i]\n",
    "    Names_set.loc[rowIndex, 'Alternative Entry'] = list(row)\n",
    "    print(rowindex)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Names_set.iloc[14]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Cleaned_Names_set"
   ]
  },
  {
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}