File size: 7,492 Bytes
a75db34 3384583 a75db34 3384583 a75db34 3384583 a75db34 3384583 a75db34 3384583 519e8a2 3384583 519e8a2 3384583 a75db34 3384583 a75db34 3384583 a75db34 3384583 a75db34 3384583 a75db34 3384583 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 |
import pandas as pd
import numpy as np
import re
# Expected JSON schema
expected_schema = {
'citizenshipCountry': '',
'countryResidency': '',
'applyingFrom': '',
'age': np.nan,
'gender': '',
'visaPurpose': '',
'activeVisa': "",
'visaList':[{'visaName': '','visaCountry': '','visaCategory': '','visaType': ''}],
'educationList': [{'level': '', 'degree': ''}],
'certification': '',
'certificationList': [{'name': '', 'description': ''}],
'admissionInUniversity': '',
'uniName': '',
'uniLocation': '',
'workList': [{'occupation': '', 'experience': np.nan, 'designation': ''}],
'entrepreneurBusinessExp': '',
'entrepreneurBusinessDesc': '',
'jobOffer': '',
'jobDescription': '',
'businessOwner': '',
'businessCurrentlyActive': '',
'businessType': '',
'langugesList': [{'language': '', 'language': ''}],
'proficiency': '',
'additionalLanguages': '',
'proficienyTestsList': [{'language': '', 'test': '', 'label': '', 'score': '', 'subTest': ''}],
'maritalStatus': '',
'relativeDetails': [{'relativeType': '', 'relativeVisa': '', 'relativeCountry': ''}],
'relatives': '',
'otherRelativeAbroad': '',
'otherRelativeType': '',
'otherRelativeVisa': '',
'otherRelativeLocation': '',
'criminalRecord': '',
'criminalRecordList': [],
'militaryService': '',
'militaryServiceType': '',
'militaryServiceCountry': '',
'immigrated': '',
'immigrationDesc': '',
'immigratedCountry': '',
'deportStatus': '',
'deportDesc': '',
'overStayed': '',
'appliedAsylum': '',
'asylumResult': '',
'asylumAttemptCountry': '',
'relatives': '',
'refugeeStatus': '',
'liquidAssets': '',
'totalInvestment': '',
'totalAssets': '',
'willingInvestment': '',
'medicalExamination': '',
'medicalDisorders': '',
'noncontagiousDisease': [],
'contagiousDisease': [],
'disablities': [],
'companyInvitation': '',
'invitationPurpose': '',
'invitationCountry': '',
'specializedSkill': '',
'skill': '',
'innovation': '',
'innovationDescription': '',
'sponsorAvailable': '',
'sponserShipvalue': '',
'sponserDes': '',
'anyAwards': '',
'awards': [{'name': '', 'description': ''}],
'otherAchivement': '',
'email': ''
}
# Flattens a list of dictionaries into a single dictionary with keys suffixed by an index
def flatten_list_of_dicts(list_of_dicts, key_map, max_count):
result = {}
for i in range(max_count):
if i < len(list_of_dicts):
if not isinstance(list_of_dicts[i], dict):
raise TypeError(f"Expected dict but got {type(list_of_dicts[i])} for item {i} in {list_of_dicts}")
for original_key, new_key in key_map.items():
result[f"{new_key}{i+1}"] = list_of_dicts[i].get(original_key, '')
else:
for new_key in key_map.values():
result[f"{new_key}{i+1}"] = ''
return result
# Concatenates list values into a single string separated by commas
def concatenate_list_values(values_list):
return ', '.join(values_list)
# Processes the proficiency tests list and returns a dictionary where the test names are the keys and the scores are the values
def process_proficiency_tests(proficiency_tests_list):
result = {}
for test_result in proficiency_tests_list:
if 'test' in test_result and 'score' in test_result:
# Clean up the test name by removing spaces, parentheses, and content inside parentheses, trailing underscores, and colons
clean_test_name = re.sub(r'\s+', '_', test_result['label'])
clean_test_name = re.sub(r'\(.*?\)', '', clean_test_name)
clean_test_name = clean_test_name.replace('(', '').replace(')', '').replace(':', '').replace('__', '_').strip('_')
# Use the cleaned test name as the key and the score as the value in the result dictionary
result[clean_test_name] = test_result['score']
return result
# Combines the medical conditions into a single string
def combine_medical_conditions(item):
combined_values = []
for condition_key in ['noncontagiousDisease', 'contagiousDisease', 'disablities']:
combined_values.extend(item.get(condition_key, []))
return concatenate_list_values(combined_values)
# Processes the relative details list including the Other relative info, and returns a concatenated string
def process_relative_details(item):
# Process the main relative details
relative_details = '; '.join([
f"{d.get('relativeType', '')}, {d.get('relativeVisa', '')}, {d.get('relativeCountry', '')}"
for d in item.get('relativeDetails', [])
]).strip('; ')
# Process additional relative details if all required fields are present
if item.get('otherRelativeType') and item.get('otherRelativeVisa') and item.get('otherRelativeLocation'):
additional_relative = f"{item['otherRelativeType']}, {item['otherRelativeVisa']}, {item['otherRelativeLocation']}"
relative_details += f"; {additional_relative}" if relative_details else additional_relative
return relative_details
def default_processing(value, default_value):
return value if value is not None else default_value
def clean_dataframe(df):
# Replace empty strings with NaN
df.replace("", np.nan, inplace=True)
# Replace missing values with 'N/A'
df.fillna('N/A', inplace=True)
return df
def convert_json_to_dataframe(json_data):
data_list = []
# Mapping special processing functions to keys
special_processing_map = {
'visaList': lambda v: flatten_list_of_dicts(v, {'visaName': 'visaName', 'visaCountry': 'visaCountry'}, 4),
'educationList': lambda v: flatten_list_of_dicts(v, {'level': 'eduLevel', 'degree': 'eduDegree'}, 4),
'workList': lambda v: flatten_list_of_dicts(v, {'occupation': 'occupation', 'experience': 'experience', 'designation': 'designation'}, 4),
'certificationList': lambda v: flatten_list_of_dicts(v, {'name': 'certName', 'description': 'certDesc'}, 4),
'langugesList': lambda v: flatten_list_of_dicts(v, {'language': 'language', 'level': 'languageLevel'}, 4),
'awards': lambda v: flatten_list_of_dicts(v, {'name': 'awardsName', 'description': 'awardsDesc'}, 4),
'proficienyTestsList': process_proficiency_tests,
'relativeDetails': process_relative_details,
'medicalConditions': combine_medical_conditions
}
for item in json_data:
processed_item = {}
for key in expected_schema:
value = item.get(key, expected_schema[key])
if key in special_processing_map:
# Process keys requiring full-item processing
if key in ['relativeDetails', 'medicalConditions']:
processed_item[key] = special_processing_map[key](item)
else:
# Process directly using the value
processed_item.update(special_processing_map[key](value))
elif key not in ['noncontagiousDisease', 'contagiousDisease', 'disablities']:
processed_item[key] = default_processing(value, expected_schema[key])
# Add combined medical conditions
processed_item['medicalConditions'] = combine_medical_conditions(item)
data_list.append(processed_item)
df = pd.DataFrame(data_list)
return clean_dataframe(df)
|