# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

import pandas as pd
import re
import json
import numpy as np
from matplotlib.pyplot import figure, show
import jellyfish

# # Reading the data
#
# First we are going to read the data. 
#

authors = pd.read_csv('~/data/authors_1019.csv', encoding = "ISO-8859-1")
investigators = pd.read_csv('~/data/investigators_837.csv', encoding = "ISO-8859-1")

# # First Look 
# In this sections I will just take a first look at the data, check if anything is weird or not normal.

authors.head()

investigators.head()

# The cities and country seems to be in JSON format. Just incase we will use them later I would like to do some processing of them. Also I take notice of list format of Topic and co-authors but this can stay as is.

authors['cities'] = authors['cities'].apply(lambda x : json.loads(x))
authors['countries'] = authors['countries'].apply(lambda x : json.loads(x))
authors = pd.concat([authors, authors['cities'].apply(pd.Series)[0].apply(pd.Series)], axis = 1).drop('cities', axis = 1).rename(columns={"identifier": "city_id", "name": "city_name"})
authors = pd.concat([authors, authors['countries'].apply(pd.Series)[0].apply(pd.Series)], axis = 1).drop('countries', axis = 1).rename(columns={"identifier": "country_id", "name": "country_name"})

investigators['cities'] = investigators['cities'].apply(lambda x : json.loads(x))
investigators['countries'] = investigators['countries'].apply(lambda x : json.loads(x))
investigators = pd.concat([investigators, investigators['cities'].apply(pd.Series)[0].apply(pd.Series)], axis = 1).drop('cities', axis = 1).rename(columns={"identifier": "city_id", "name": "city_name"})
investigators = pd.concat([investigators, investigators['countries'].apply(pd.Series)[0].apply(pd.Series)], axis = 1).drop('countries', axis = 1).rename(columns={"identifier": "country_id", "name": "country_name"})

# Lets take a look at the data after we have processed it just to keep it in mind.

authors

investigators

# # Divide and conquer
# I have philosophy when it comes to problems in data science. KISS (Keep It Simple Stupid). With a cartisean product of the subset of the data that we have ground truth for, it would be possible to engineer some features from the strings and build a predictive model. However I think there are better ways to get the ball rolling. We will try a divide and conquer techique. We will try to develop unique IDs using information in the data.
#
# 1. Join data that has ORCIDs
# 2. Remove data with ORCIDs from the data
# 3. Find unique last names and join those from the two datasets
# 4. Concatenate orcid join and unique last name join
# 5. Filter out already joined data from the data
# 6. Find unique combination of the first letter from first name and last name
# 7. Join on this combination and concatenate with previous data.
# 8. Filter out already joined data from the data
# 9. Combine fist, middle, and last name into 1 column for remaining data
# 10. Do fuzzy join
# 11. Combine with previously joined data

investigators_with_orcid = investigators[investigators['orcid'].notnull()].rename(columns={'id':'id_authors'})[['id_authors', 'orcid']]
authors_with_orcid = authors[authors['orcid'].notnull()].rename(columns={'id':'id_investigators'})[['id_investigators', 'orcid']]

joined_on_orcid = pd.merge(investigators_with_orcid, authors_with_orcid, on='orcid')[['id_authors', 'id_investigators']]

# +
# finding authors that do not have orcid
authors_without_orcid = authors[authors['orcid'].isnull()]
investigators_without_orcid = investigators[investigators['orcid'].isnull()]
    
# finding authors with unique last names
authors_unique_last_name = authors_without_orcid.drop_duplicates(subset=['lastname'])
investigators_unique_last_name = investigators_without_orcid.drop_duplicates(subset=['lastname'])
# -

#unique last names count for authors
(authors_unique_last_name.lastname.value_counts().apply(pd.Series)[0] == 1).value_counts()


#unique last names count for investigators
(investigators_unique_last_name.lastname.value_counts().apply(pd.Series)[0] == 1).value_counts()


joined_on_unique_names = pd.merge(authors_unique_last_name, investigators_unique_last_name, on='lastname',
                                  suffixes=('_authors', '_investigators'))[['id_authors', 'id_investigators']]

#198 more joined! It means however there is not perfect overlap with the data
joined_on_unique_names.nunique()

concat_orcid_unique_names = pd.concat([joined_on_unique_names, joined_on_orcid])

# Removing authors that have already been sucessfully joined
authors_left = authors[~authors['id'].isin(concat_orcid_unique_names['id_authors'])]
investigators_left = investigators[~investigators['id'].isin(concat_orcid_unique_names['id_investigators'])]

# Creating first letter last name combo
authors_left['first_letter_last_name'] = (authors_left.first_name.str[0] + '_' + authors_left.lastname).astype(str)
investigators_left['first_letter_last_name'] = (investigators_left.first_name.str[0] + '_' + investigators_left.lastname).astype(str)
authors_left_unique_combo = authors_left.drop_duplicates(subset=['first_letter_last_name'])
investigators_left_unique_combo = investigators_left.drop_duplicates(subset=['first_letter_last_name'])

joined_on_unique_names_combo = pd.merge(authors_left_unique_combo, investigators_left_unique_combo, on='first_letter_last_name',
                                  suffixes=('_authors', '_investigators'))[['id_authors', 'id_investigators']]

joined_on_unique_names_combo.shape

concat_all_before_fuzzy = pd.concat([concat_orcid_unique_names,joined_on_unique_names_combo])

# Removing authors that have already been sucessfully joined
authors_for_fuzzy_join = authors[~authors['id'].isin(concat_all_before_fuzzy['id_authors'])]
investigators_for_fuzzy_join = investigators[~investigators['id'].isin(concat_all_before_fuzzy['id_investigators'])]

authors_for_fuzzy_join.shape

# Replace middle name with neatural character
authors_for_fuzzy_join['middle_name'] = authors_for_fuzzy_join.middle_name.fillna('_')
investigators_for_fuzzy_join['middle_name'] = investigators_for_fuzzy_join.middle_name.fillna('_')

# Creating fuzzy join ID
authors_for_fuzzy_join['fuzzy_id'] = (authors_for_fuzzy_join.first_name + '_' + authors_for_fuzzy_join.middle_name + "_" + authors_for_fuzzy_join.lastname).astype(str)
investigators_for_fuzzy_join['fuzzy_id'] = (investigators_for_fuzzy_join.first_name + '_' + investigators_for_fuzzy_join.middle_name + "_" + investigators_for_fuzzy_join.lastname).astype(str)

# Renaming and selecting columns
authors_for_fuzzy_join = authors_for_fuzzy_join[['fuzzy_id', 'id']].rename(columns={'id':'id_authors'})
investigators_for_fuzzy_join = investigators_for_fuzzy_join[['fuzzy_id', 'id']].rename(columns={'id':'id_investigators'})


# get closest match algorithm based on jaro_winkler
def get_closest_match(x, list_strings):
    best_match = None
    highest_jw = 0
    for current_string in list_strings:
        current_score = jellyfish.jaro_winkler(x, current_string)
        
        if(current_score > highest_jw):
            highest_jw = current_score
            best_match = current_string
            
    return best_match


# # Evaluation

investigators_with_orcid = investigators[investigators['orcid'].notnull()].rename(columns={'id':'id_authors'})
authors_with_orcid = authors[authors['orcid'].notnull()].rename(columns={'id':'id_investigators'})

authors_with_orcid['fuzzy_id'] = (authors_with_orcid.first_name + '_' + authors_with_orcid.middle_name + "_" + authors_with_orcid.lastname).astype(str)
investigators_with_orcid['fuzzy_id'] = (investigators_with_orcid.first_name + '_' + investigators_with_orcid.middle_name + "_" + investigators_with_orcid.lastname).astype(str)

authors_with_orcid.fuzzy_id = authors_with_orcid.fuzzy_id.map(lambda x: get_closest_match(x, investigators_with_orcid.fuzzy_id))

evaluate_fuzz = pd.merge(authors_with_orcid, investigators_with_orcid, how='left', on='fuzzy_id', suffixes=('_authors', '_investigators'))

(evaluate_fuzz['orcid_authors'] == evaluate_fuzz['orcid_investigators']).sum()

authors_with_orcid.shape

#correct matches using fuzzy join
133/206

investigators_for_fuzzy_join.fuzzy_id = investigators_for_fuzzy_join.fuzzy_id.map(lambda x: get_closest_match(x, authors_for_fuzzy_join.fuzzy_id))


fuzzy = pd.merge(authors_for_fuzzy_join, investigators_for_fuzzy_join, on='fuzzy_id')


final_fuzz = fuzzy[['id_authors', 'id_investigators']]

concat_all = pd.concat([final_fuzz, concat_all_before_fuzzy])

concat_all.shape

# # Final Remarks
# Given the time constains I could not try everything that I would have liked. I would still investigate further the approached outside of ML further.I would test different distance calculations, as well as different variable combinations to make a fuzzy id out of.
#
# From the machine learning perspective I would have done a cartisean product of ground truth data, feature engineer label, plus feature engineer multiple features related to distance of strings. I would test out different string distance metrics. I would start with a base_line logistics regression, then move to more complex models from there. If I engineer to many distance features I may try to do a PCA.
#
# From a code perspecive, way to much duplicated code, everything needs to be put into functions. As well as proper unit testing for little pieces of the code.



StarcoderdataJupyterScriptsDedupFiltered