Inverse of Pandas json_normalize

Question

I just discovered the json_normalize function which works great in taking a JSON object and giving me a pandas Dataframe. Now I want the reverse operation which takes that same Dataframe and gives me a json (or json-like dictionary which I can easily turn to json) with the same structure as the original json.

Here's an example: https://hackersandslackers.com/json-into-pandas-dataframes/.

They take a JSON object (or JSON-like python dictionary) and turn it into a dataframe, but I now want to take that dataframe and turn it back into a JSON-like dictionary (to later dump to json file).

Answer 1

I implemented it with a couple functions

def set_for_keys(my_dict, key_arr, val):
    """
    Set val at path in my_dict defined by the string (or serializable object) array key_arr
    """
    current = my_dict
    for i in range(len(key_arr)):
        key = key_arr[i]
        if key not in current:
            if i==len(key_arr)-1:
                current[key] = val
            else:
                current[key] = {}
        else:
            if type(current[key]) is not dict:
                print("Given dictionary is not compatible with key structure requested")
                raise ValueError("Dictionary key already occupied")

        current = current[key]

    return my_dict

def to_formatted_json(df, sep="."):
    result = []
    for _, row in df.iterrows():
        parsed_row = {}
        for idx, val in row.iteritems():
            keys = idx.split(sep)
            parsed_row = set_for_keys(parsed_row, keys, val)

        result.append(parsed_row)
    return result


#Where df was parsed from json-dict using json_normalize
to_formatted_json(df, sep=".")

Answer 2

df.to_json(path)

or

df.to_dict()

Answer 3

A simpler approach:
Uses only 1 function...

def df_to_formatted_json(df, sep="."):
    """
    The opposite of json_normalize
    """
    result = []
    for idx, row in df.iterrows():
        parsed_row = {}
        for col_label,v in row.items():
            keys = col_label.split(".")

            current = parsed_row
            for i, k in enumerate(keys):
                if i==len(keys)-1:
                    current[k] = v
                else:
                    if k not in current.keys():
                        current[k] = {}
                    current = current[k]
        # save
        result.append(parsed_row)
    return result

Answer 4

let me throw in my two cents

after backward converting you might need to drop empty columns from your generated jsons therefore, i checked if val != np.nan. but u cant directly do it, instead you need to check val == val or not, because np.nan != itself. my version:

def to_formatted_json(df, sep="."):
    result = []
    for _, row in df.iterrows():
        parsed_row = {}
        for idx, val in row.iteritems():
            if val == val:
                keys = idx.split(sep)
                parsed_row = set_for_keys(parsed_row, keys, val)

        result.append(parsed_row)
    return result

Answer 5

This is a solution which looks working to me. It is designed to work on a dataframe with one line, but it can be easily looped over large dataframes.

class JsonRecreate():
    
    def __init__(self, df):
        self.df = df

    def pandas_to_json(self):
        df = self.df
        # determine the number of nesting levels
        number_levels = np.max([len(i.split('.')) for i in df.columns])
        # put all the nesting levels in an a list
        levels = []
        for level_idx in np.arange(number_levels):
            levels.append(np.array([i.split('.')[level_idx] if len(i.split('.')) > level_idx else ''
                                    for i in df.columns.tolist()]))
        self.levels = levels
        return self.create_dict(upper_bound = self.levels[0].shape[0])

    def create_dict(self, level_idx = 0, lower_bound = 0, upper_bound = 100):
        ''' Function to create the dictionary starting from a pandas dataframe generated by json_normalize '''
        levels = self.levels
        dict_ = {}
        # current nesting level
        level = levels[level_idx]
        # loop over all the relevant elements of the level (relevant w.r.t. its parent)
        for key in [i for i in np.unique(level[lower_bound: upper_bound]) if i != '']:
            # find where a particular key occurs in the level
            correspondence = np.where(level[lower_bound: upper_bound] == key)[0] + lower_bound
            # check if the value(s) corresponding to the key appears once (multiple times)
            if correspondence.shape[0] == 1:
                # if the occurence is unique, append the value to the dictionary
                dict_[key] = self.df.values[0][correspondence[0]]
            else:
                # otherwhise, redefine the relevant bounds and call the function recursively
                lower_bound_, upper_bound_ = correspondence.min(), correspondence.max() + 1
                dict_[key] = self.create_dict(level_idx + 1, lower_bound_, upper_bound_)
        return dict_

I tested it with a simple dataframe such as:

df = pd.DataFrame({'a.b': [1], 'a.c.d': [2], 'a.c.e': [3], 'a.z.h1': [-1], 'a.z.h2': [-2], 'f': [4], 'g.h': [5], 'g.i.l': [6], 'g.i.m': [7], 'g.z.h1': [-3], 'g.z.h2': [-4]})

The order in the json is not exactly preserved in the resulting json, but it can be easily handled if needed.