<jupyter_start><jupyter_text>Data Migration - Program A<jupyter_code># Dependencies
import pandas as pd
import datetime as dt
from datetime import date
import warnings
import numpy as np
warnings.filterwarnings('ignore')<jupyter_output><empty_output><jupyter_text>Prepare Data Migration for Patent Family<jupyter_code># READING DATA for Patent from Program A
prg-A_patent_df = pd.read_excel("Program A_Sample Data for Patent.xlsx", encoding = "utf8", dtype=str)
prg-A_patent_df
# We want to validate values between Patent Family and Priority Application
# Patent Family Format: [Patent Holder]_[Priority Application]
# Example: 
# Patent Family: Disney_US2000-112233
# Priority Application: US2000-112233

# Extract separately patent holder and priority application in 'Patent Family' column
fam = prg-A_patent_df['Patent Family']
priority_app_fam = []

for f in fam:
    if "_" in f:
        priority_app_fam.append(f.split("_")[1])
    else:
        priority_app_fam.append("")
        
prg-A_patent_df["Priority App_from Family"] = priority_app_fam
prg-A_patent_df["Priority App_from Family"] = prg-A_patent_df["Priority App_from Family"].str.strip()

# Validate priority application we get from patent family is the same with values in 'Earliest Priority Application' column
priority_app = prg-A_patent_df['Earliest Priority Application']

prg-A_patent_df.loc[prg-A_patent_df['Priority App_from Family'] == priority_app, 'Validate_Priority App'] = 'YES'
prg-A_patent_df.loc[prg-A_patent_df['Priority App_from Family'] != priority_app, 'Validate_Priority App'] = 'NO'

diff_priority_app_index = prg-A_patent_df.index[prg-A_patent_df['Validate_Priority App'] == 'NO'].tolist()

for i in diff_priority_app_index:
    prg-A_patent_df['Earliest Priority Application'][i] = prg-A_patent_df['Priority App_from Family'][i]
    print(prg-A_patent_df.iloc[i])
# Validate each unique 'Earliest Priority Application' only has one unique 'Earliest Priority Date'
priority_check = prg-A_patent_df.pivot_table(values='Earliest Priority Date', index='Earliest Priority Application', aggfunc=pd.Series.nunique)
priority_check = priority_check.reset_index()
# priority_check
fix = list(priority_check.loc[priority_check['Earliest Priority Date'] > 1]['Earliest Priority Application'])
for app in fix:
    print(app)
    print(prg-A_patent_df.loc[prg-A_patent_df['Earliest Priority Application'] == app]['Earliest Priority Date'])
# Replace and update the 'Earliest Priority Date'
for i in range(0, len(prg-A_patent_df['Earliest Priority Application'])):
    if prg-A_patent_df['Earliest Priority Application'][i] == 'KR2007-0109729' and prg-A_patent_df['Earliest Priority Date'][i] == '2009-10-30 00:00:00':
        prg-A_patent_df['Earliest Priority Date'][i] = '2007-10-30 00:00:00'
    elif prg-A_patent_df['Earliest Priority Application'][i] == 'SE1999-03552' and prg-A_patent_df['Earliest Priority Date'][i] == '1999-01-10 00:00:00':
        prg-A_patent_df['Earliest Priority Date'][i] = '1999-10-01 00:00:00'
    elif prg-A_patent_df['Earliest Priority Application'][i] == 'SE2000-04163' and prg-A_patent_df['Earliest Priority Date'][i] == '2002-07-04 00:00:00':
        prg-A_patent_df['Earliest Priority Date'][i] = '2000-11-14 00:00:00'
    elif prg-A_patent_df['Earliest Priority Application'][i] == 'SE2000-04187' and prg-A_patent_df['Earliest Priority Date'][i] == '2001-11-15 00:00:00':
        prg-A_patent_df['Earliest Priority Date'][i] = '2000-11-15 00:00:00'
    elif prg-A_patent_df['Earliest Priority Application'][i] == 'US2009-213593' and prg-A_patent_df['Earliest Priority Date'][i] == '2009-12-23 00:00:00':
        prg-A_patent_df['Earliest Priority Date'][i] = '2009-06-23 00:00:00'
    elif prg-A_patent_df['Earliest Priority Application'][i] == 'US2009-145223' and pd.isnull(prg-A_patent_df['Earliest Priority Date'][i]) == True:
        prg-A_patent_df['Earliest Priority Date'][i] = '2009-01-16 00:00:00'

# Re-checking each unique priority application only has one unique priority date
priority_check_2 = prg-A_patent_df.pivot_table(values='Earliest Priority Date', index='Earliest Priority Application', aggfunc=pd.Series.nunique)
priority_check_2 = priority_check_2.reset_index()
check = list(priority_check_2.loc[priority_check_2['Earliest Priority Date'] > 1]['Earliest Priority Application'])
check
prg-A_patent_df.loc[prg-A_patent_df['Earliest Priority Date'].isnull() == True]['Patent Family'].unique()
# Checking the all family information of child patents are the same as the family information of parent patents
# Separately extract and filter parent/CEP patents and child/FC patents
CEP_patent_df = prg-A_patent_df.loc[prg-A_patent_df['Parent Patent Number'].isnull() == True]
FC_patent_df = prg-A_patent_df.loc[prg-A_patent_df['Parent Patent Number'].isnull() == False]

CEP_patent_filtered_df = CEP_patent_df[['Patent: Matter Number','Patent Number_Clean','Primary Patent Country',
                                       'Earliest Priority Application', 'Earliest Priority Date']]
FC_patent_filtered_df = FC_patent_df[['Patent: Matter Number','Patent Number_Clean','Primary Patent Country',
                                      'Earliest Priority Application', 'Earliest Priority Date',
                                      'Parent Patent No_Clean','Parent Patent']]
CEP_patent_filtered_df.columns = ['Matter Number_Parent','CEP_Patent Number_Clean','CEP_Primary Patent Country',
                                  'CEP_Earliest Priority Application', 'CEP_Earliest Priority Date']
FC_patent_filtered_df.columns = ['Matter Number','FC_Patent Number_Clean','FC_Primary Patent Country',
                                 'FC_Earliest Priority Application', 'FC_Earliest Priority Date',
                                 'FC_Parent Patent No_Clean','Matter Number_Parent']

# Merge/Join both dataframes
FC_PP_validation = pd.merge(FC_patent_filtered_df, CEP_patent_filtered_df, on='Matter Number_Parent', how='left')
print("Vouched Patent: {}".format(FC_patent_filtered_df.shape))
print("Certified Patent: {}".format(CEP_patent_filtered_df.shape))
print("Merged Data: {}".format(FC_PP_validation.shape))

# Validate the family information between child and parent patents
FC_priority_app = FC_PP_validation['FC_Earliest Priority Application']
FC_priority_date = FC_PP_validation['FC_Earliest Priority Date']
FC_PP = FC_PP_validation['FC_Parent Patent No_Clean']

CEP_priority_app = FC_PP_validation['CEP_Earliest Priority Application']
CEP_priority_date = FC_PP_validation['CEP_Earliest Priority Date']
CEP_patent_no = FC_PP_validation['CEP_Patent Number_Clean']

FC_PP_validation.loc[FC_PP == CEP_patent_no, 'Validate_FC-PP_Parent Patent No'] = 'YES'
FC_PP_validation.loc[FC_PP != CEP_patent_no, 'Validate_FC-PP_Parent Patent No'] = 'NO'

FC_PP_validation.loc[FC_priority_app == CEP_priority_app, 'Validate_FC-PP_Priority App'] = 'YES'
FC_PP_validation.loc[FC_priority_app != CEP_priority_app, 'Validate_FC-PP_Priority App'] = 'NO'

FC_PP_validation.loc[FC_priority_date == CEP_priority_date, 'Validate_FC-PP_Priority Date'] = 'YES'
FC_PP_validation.loc[FC_priority_date != CEP_priority_date, 'Validate_FC-PP_Priority Date'] = 'NO'

diff_PP_no = FC_PP_validation.loc[FC_PP_validation['Validate_FC-PP_Parent Patent No'] == 'NO']
diff_priority_app = FC_PP_validation.loc[FC_PP_validation['Validate_FC-PP_Priority App'] == 'NO']
diff_priority_date = FC_PP_validation.loc[FC_PP_validation['Validate_FC-PP_Priority Date'] == 'NO']

print(diff_PP_no)
print(diff_priority_app)
print(diff_priority_date)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11796637097841917, 'fraction_numerical': 0.03204024890771879, 'mean_word_length': 5.750670241286863, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '263390', 'license': 'MIT', 'n_tokens_mistral': 2802, 'n_tokens_neox': 2565, 'n_words': 534, 'path': '.ipynb_checkpoints/data-migration-program-A-checkpoint.ipynb', 'repo_name': 'mhandipranata/data-cleaning-and-translation', 'size': '7553'}