<jupyter_start><jupyter_text>Parse Json File Dynamically Author: <NAME>  Description: Program to dynamically parse JSON dataset. Creates profiling, flatten dataset with column header.  Date: 2020-03-11  Remove CRLF<jupyter_code>def clean_string(string_value):
    if string_value == None:
        return None
    else:
        return string_value.replace("\r","").replace("\n","").replace("\t","").strip()<jupyter_output><empty_output><jupyter_text>**Create Directory**<jupyter_code>def create_dir(dirname):
    if not os.path.exists(dirname):
        os.makedirs(dirname)<jupyter_output><empty_output><jupyter_text>**Directory HouseKeeping**<jupyter_code>import os
from datetime import datetime

file_name = 'style_format.json'
incoming_dataset = 'source_dataset'
base_dataset = 'processed_dataset'
profile_metadata = 'profiled_metadata'
processing_dir = file_name.split('.')[0]
now = datetime.now().strftime("%Y%m%d%H%M%S")
today = now[0:8]
tmp_dir = 'tmp'
current_dir = os.path.join(base_dataset, processing_dir,today)
profile_dir = os.path.join(profile_metadata, processing_dir, today)
tmp_dir = os.path.join(tmp_dir, 'tmp' + '_' + today, processing_dir)

# create directory
create_dir(incoming_dataset)
create_dir(current_dir)
create_dir(profile_dir)
create_dir(tmp_dir)

# output file format
file_suffix = 'tsv'
field_delimiter = '\t'
record_delimiter = '\n'<jupyter_output><empty_output><jupyter_text>**Move dataset to source directory**<jupyter_code># uncomment if your are testing and json file exists in current directory.
#import shutil
#shutil.move(file_name,incoming_dataset)<jupyter_output><empty_output><jupyter_text>**Load JSON File**<jupyter_code>import json

with open(os.path.join(incoming_dataset,file_name)) as json_file:
    data_file = json.load(json_file)<jupyter_output><empty_output><jupyter_text>Recursively parse nested object<jupyter_code>element_list = list()
parent_attr = list()
_parent_name = set()
column_name_delimiter = '_'
def recursive_dict(dict_name):
    if isinstance(dict_name, dict):
        for index,(k,v) in enumerate(dict_name.items()):
            if not isinstance(v, dict) and not isinstance(v, list):
                if len(parent_attr) != 0:
                    parent_name = column_name_delimiter.join(parent_attr) + column_name_delimiter + k             
                else:
                    parent_name = k                    
                element_tuple = (parent_name,v)
                element_list.append(element_tuple)
            if isinstance(v, list):
                for i in range(len(v)):
                    parent_attr.append(k)
                    recursive_dict(v[i])
                    parent_attr.pop()
            if isinstance(v, dict):
                parent_attr.append(k)
                _parent_name.add(k)
                #print("Nested dictionary found. Total elements:",len(v))
                recursive_dict(v)
                parent_attr.pop()
    return element_list<jupyter_output><empty_output><jupyter_text>Create tsv file from original dataset for all nested object<jupyter_code>dataset_file = file_name.replace('json','txt')
is_dict = isinstance(data_file, dict)
dataset = list()
if (is_dict):
     for k,v in data_file.items():
            if isinstance(v, list):
                for i in range(len(v)):
                    data = recursive_dict(v[i])
                    dataset.append(data[:])
                    # Writing to file is consuming lot of memory so this is disabled
                    #with open(os.path.join(tmp_dir, dataset_file), 'a+') as datafile:
                        #datafile.writelines(str(data) + "\n")
                    #for tup in range(len(data)):
                        #with open(os.path.join(tmp_dir,'flattened_data.tsv'), 'a+') as realdata:
                            #data_value = clean_string(data[tup][1])
                            #realdata.writelines(data_value + '\t')
                            #if tup == len(data) - 1:
                                #realdata.writelines("\n")
                    element_list.clear()
            else:
                data = recursive_dict(data_file)
                dataset.append(data[:])
                element_list.clear()
                break<jupyter_output><empty_output><jupyter_text>Create data profiling to store metadata about file and all the records<jupyter_code>from collections import OrderedDict
from datetime import datetime
import getpass
import hashlib
profile_report_name = file_name.split('.')[0] + now + '_profiling.' + file_name.split('.')[1]
def profile_dataset(data_element):
    data_element_profile = OrderedDict()
    metadata_data = OrderedDict()
    attribute_data = OrderedDict()
    dataset_properties = list()
    metadata_type = ['created_by','created_date', 'email', 'total_data_element']
    attribute_type = ['record_id', 'total_element', 'attribute_name', 'attribute_hash']
    date_format = "%Y-%m-%d %H:%M:%S"
    email_address = '<EMAIL>'
    metadata_data[metadata_type[0]] = getpass.getuser()
    metadata_data[metadata_type[1]] = datetime.now().strftime(date_format)
    metadata_data[metadata_type[2]] = email_address
    metadata_data[metadata_type[3]] = len(data_element)
    data_element_profile['metadata'] = metadata_data
    for i in range(len(data_element)):
        attribute_name = ''        
        for j in range(len(data_element[i])):
            attribute_name += data_element[i][j][0] + ',' 
        attribute_data[attribute_type[0]] = i + 1
        attribute_data[attribute_type[1]] = len(data_element[i])
        attribute_data[attribute_type[2]] = attribute_name[0:len(attribute_name)-1]
        attribute_data[attribute_type[3]] = hashlib.sha1(attribute_data[attribute_type[2]].lower().encode()).hexdigest()
        dataset_properties.append(attribute_data.copy())
        data_element_profile['data_attributes'] = dataset_properties
    profile_report = json.dumps(data_element_profile, separators=(',', ': '), indent = 4)
    with open(os.path.join(profile_dir, profile_report_name), 'a+') as profiling:
        profiling.writelines(profile_report)
    print("Data profiling report for {} is located at {} directory with filename {}".format(file_name,profile_dir,profile_report_name))
profile_dataset(dataset)<jupyter_output>Data profiling report for style_format.json is located at profiled_metadata/style_format/20210329 directory with filename style_format20210329180502_profiling.json<jupyter_text>Data Exploration<jupyter_code>import pandas as pd
pd.set_option('display.max_columns', None)   # set pd columns option

df_data = pd.DataFrame.from_records(dataset)
df_data.head(5)<jupyter_output><empty_output><jupyter_text>**Categorize and collect records having same hashcode and add header**<jupyter_code>data = json.load(open(os.path.join(profile_dir, profile_report_name)))
df_metadata = pd.DataFrame(data["data_attributes"])   # load data_attributes objects
all_records_attributes = df_metadata['attribute_hash']

# Add record_id and attribute_hash in original dataset
df_data['record_id'] = df_metadata['record_id']
df_data['attribute_hash'] = df_metadata['attribute_hash']
df_data['attribute_name'] = df_metadata['attribute_name']

# create a dictionary for grouped records having same attribute_hash key
df_grouped_record = {}
for i in range(len(df_data)):
    df_attr = all_records_attributes[i]
    # add dataframe to dictionary
    df_tmp = df_data[df_data['attribute_hash'] == df_attr].dropna(axis=1)  # filter and remove empty columns
    column_header = df_data['attribute_name'].tolist()[i] + ",record_id,attribute_hash"
    column_header = str(column_header)[:len(str(column_header))].split(',') 
    df_tmp.drop(columns=['attribute_name'], inplace=True)
    df_tmp.columns = column_header
    df_grouped_record[df_attr] = df_tmp<jupyter_output><empty_output><jupyter_text>**Processed Dataset**<jupyter_code>df_tmp.head(3)<jupyter_output><empty_output><jupyter_text>**Flatten tuples to retrieve values and store in file for each dictionary keys**<jupyter_code>for k,v in df_grouped_record.items():
    filename = os.path.join(tmp_dir, k + "_" + now + "." + file_suffix)
    file = open(filename, "w")
    cols = df_grouped_record[k].columns.tolist()
    clean_col = [c.strip() for c in cols]
    clean_col = str(clean_col).replace("'","").replace(", ",field_delimiter).replace("[","").replace("]","")
    file.write(clean_col)
    file.write(record_delimiter)
    for rec in df_grouped_record[k].itertuples(index=False):   # iteritems()
        record = list(rec)
        for i in range(len(record)):
            if i >=len(record)-2:
                value = str(record[i]) + field_delimiter
            else:
                value = clean_string(str(record[i][1])) + field_delimiter
            file.write(value)
        file.write(record_delimiter)
    file.close()<jupyter_output><empty_output><jupyter_text>**Glob multiple files**<jupyter_code>import glob
pd.set_option('display.max_columns', 200)
pd.set_option('display.max_rows', 500)

file_path = os.path.join(tmp_dir, '*.tsv')
files = glob.glob(file_path)<jupyter_output><empty_output><jupyter_text>**Read all file in pandas**<jupyter_code>dfAll = pd.concat(map(lambda file: pd.read_csv(file, sep='\t', header='infer', index_col=False), files), ignore_index=True)
dfAll.head(3)<jupyter_output><empty_output><jupyter_text>**Sort columns order based on highest columns attribute**<jupyter_code># get highest attribute_hash value
max_attribute_hash = df_metadata[df_metadata['total_element'] == df_metadata['total_element'].max()]['attribute_hash'].values[0]
ordered_column = df_metadata[df_metadata['attribute_hash'] == max_attribute_hash]['attribute_name'].values[0].split(",")
ordered_column += ['record_id', 'attribute_hash']
dfAll = dfAll[ordered_column]<jupyter_output><empty_output><jupyter_text>**Drop empty parent column**<jupyter_code>remove_cols = [col for col in dfAll.columns.tolist() if col in _parent_name]
dfAll = dfAll.drop(columns=remove_cols)<jupyter_output><empty_output><jupyter_text>**Output Final Dataset**<jupyter_code># Sort order by record_id asc
dfAll = dfAll.sort_values(by=['record_id'])
dfAll.to_csv(os.path.join(current_dir,"allfile.tsv"), sep='\t', index=False)<jupyter_output><empty_output><jupyter_text>**Clean tmp directory**<jupyter_code>import shutil
shutil.rmtree(tmp_dir)<jupyter_output><empty_output><jupyter_text>**@todo:**  * Sample all the records.  * Determine column data type check for elastic search usecases.     * Generate summary of the report.  * Add logging for all the transactions.     **Done**    * Optimization (Exclude write tmp files to reduce processing time) done<jupyter_code>dfAll.describe()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12373926036608143, 'fraction_numerical': 0.006257004109077325, 'mean_word_length': 4.231558378114314, 'pattern_counts': {'":': 0, '<': 78, '<?xml version=': 0, '>': 79, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '586121', 'license': 'MIT', 'n_tokens_mistral': 3461, 'n_tokens_neox': 3351, 'n_words': 632, 'path': 'JsonParser.ipynb', 'repo_name': 'kcmahesh/jsonparser', 'size': '10735'}