Translating column-wise JSON data from Excel into a hierarchical structure with python

user_10166261 0
Viewed 109

Overall goal

My overall goal is to translate input from an Excel file into a text file (a Stata .do file) to perform a set of data harmonisation tasks that have roughly the same structure but require individual treatment due to the idiosyncratic nature of the data (national surveys). The harmonisation will be performed by different people and Excel ensures people use the same structure. My strategy is to read the Excel file in JSON structure, use this structure write to the do file.

Task I request help with

The problem I am trying to solve now is how to go from the column structure of the JSON as it reads from Excel to a hierarchical structure. The Excel file I would like users to have has a structure of Blocks and Tasks within the Blocks, with specific commands (of one or more lines) needed to carry out the tasks. The image below is an example of the Excel document:

Excel File Structure for user code

The ultimate goal is to produce a text file like this:

* A 

* A1    
Command for A1
        
* A2    
Command for A2 (1)
Command for A2 (2)
        
* B 

* B1    
Command for B1 (1)
Command for B1 (2)
Command for B1 (3)
        
*B2 
Command for B2 (1)
Command for B2 (2)

I use Python to read this in using the following code:

path = "Some Path/test.xlsx"
import pandas
import json

x = pandas.read_excel(path, sheet_name='Sheet')
json_str = x.to_json()

This yields, as said, a column structure of JSON, which looks like this:

JSON structure when simply reading Excel file

What I am struggling with is to translate this into the hierarchical structure the code naturally has. My desired output would be a JSON object of this form:

Desired JSON Structure

If there is a smarter way to achieve the overall goal, I am happy to learn of it, but I thought this translation is a necessary step to structure the information.

Here is the code to produce the JSON objects for reproducibility

# Column-wise object
'{"Block":{"0":"* A","1":null,"2":null,"3":null,"4":null,"5":null,"6":"* B","7":null,"8":null,"9":null,"10":null,"11":null},"Task":{"0":"* A1","1":null,"2":null,"3":"* A2","4":null,"5":null,"6":"* B1","7":null,"8":null,"9":null,"10":"*B2","11":null},"Code":{"0":"Command for A1","1":null,"2":null,"3":"Command for A2 (1)","4":"Command for A2 (2)","5":null,"6":"Command for B1 (1)","7":"Command for B1 (2)","8":"Command for B1 (3)","9":null,"10":"Command for B2 (1)","11":"Command for B2 (2)"}}'

# Hierarchical object
'{"* A": {"* A1": ["Command for A1"], "* A2": ["Command for A2 (1)", "Command for A2 (2)"] }, "* B" : {"* B1" : ["Command for B1 (1)", "Command for B1 (2)", "Command for B1 (3)"], "* B2" : ["Command for B2 (1)", "Command for B2 (2)"]}}'
pandas excel python json
1 Answers

You can do it like this:

import pandas as pd
import json
from collections import defaultdict

path = "Some Path/test.xlsx"
df = pd.read_excel(path,
                   engine='openpyxl',
                   # header=None, names=['Block', 'Task', 'Code']  # only if your file has no headers
                   )

df.dropna(inplace=True, axis=0, how='all')
df.fillna(method='ffill', inplace=True, axis=0)
df = df.set_index(['Block', 'Task'])

nested_dict = defaultdict(lambda : defaultdict(list))

for keys, value in df.Code.iteritems():
    nested_dict[keys[0]][keys[1]].append(value)

json_str = json.dumps(nested_dict, indent=4, sort_keys=True)
print(json_str)

Output:

{
    "* A": {
        "* A1": [
            "Command for A1"
        ],
        "* A2": [
            "Command for A2 (1)",
            "Command for A2 (2)"
        ]
    },
    "* B": {
        "* B1": [
            "Command for B1 (1)",
            "Command for B1 (2)",
            "Command for B1 (3)"
        ],
        "* B2": [
            "Command for B2 (1)",
            "Command for B2 (2)"
        ]
    }
}
user_14535080
Related