Convert a CSV column to nested field in json

Question

I have a csv file with some as the columns in the format x;y;z. I am using pandas to read this data, do some pre-processing and convert to a list of json objects using to_json/to_dict methods of pandas. While converting these special columns, the json object for that column should be of the format {x: {y: {z: value}}}. There could be different columns like x:y:z and x:y:a and these 2 have to be merged together into a single object in the resultant record json in the format i.e., {x: {y: {z: value1, a: value2}}}

CSV:

Id,Name,X;Y;Z,X;Y;A,X;B;Z
101,Adam,1,2,3
102,John,4,5,6
103,Sara,7,8,9

Output:

[ 
   { 
      "Id":101,
      "Name":"Adam",
      "X":{ 
         "Y":{ 
            "Z":1,
            "A":2
         },
         "B":{ 
            "Z":3
         }
      }
   },
   { 
      "Id":102,
      "Name":"John",
      "X":{ 
         "Y":{ 
            "Z":4,
            "A":5
         },
         "B":{ 
            "Z":6
         }
      }
   },
   { 
      "Id":103,
      "Name":"Sara",
      "X":{ 
         "Y":{ 
            "Z":7,
            "A":8
         },
         "B":{ 
            "Z":9
         }
      }
   }
]

Possible duplicate of [pandas groupby to nested json](https://stackoverflow.com/questions/24374062/pandas-groupby-to-nested-json) — pissall, Oct 21 '19 at 11:00
@pissall I don't need to do a groupby as x;y;z is a single column and its value has to be converted to a nested json object — Vinay Nikhil, Oct 21 '19 at 11:06
you might get a better response if you actually show your data structure and your expected output — Chris Doyle, Oct 21 '19 at 11:08
thanks I will have a look but what have you tried so far and whats not working — Chris Doyle, Oct 21 '19 at 16:18

score 1 · Accepted Answer · answered Oct 21 '19 at 17:19

I found it easier to use pandas to dump the data as a dict then use a recursive function to iterate through the keys and where I encounter a key which contains a ; then i split the key by this deliminator and recursively create the nested dicts. When i reach the last element in the split key i update the key with the original value and the remove the original key from the dict.

import pandas as pd
from io import StringIO
import json

def split_key_to_nested_dict(original_dict, original_key, nested_dict, nested_keys):
    if nested_keys[0] not in nested_dict:
        nested_dict[nested_keys[0]] = {}
    if len(nested_keys) == 1:
        nested_dict[nested_keys[0]] = original_dict[original_key]
        del original_dict[original_key]
    else:
        split_key_to_nested_dict(original_dict, original_key, nested_dict[nested_keys[0]], nested_keys[1:])


csv_data = StringIO("""Id,Name,X;Y;Z,X;Y;A,X;B;Z
101,Adam,1,2,3
102,John,4,5,6
103,Sara,7,8,9""")
df = pd.DataFrame.from_csv(csv_data)
df.insert(0, df.index.name, df.index)
dict_data = df.to_dict('records')

for data in dict_data:
    keys = list(data.keys())
    for key in keys:
        if ';' in key:
            nested_keys = key.split(';')
            split_key_to_nested_dict(data, key, data, nested_keys)
print(json.dumps(dict_data))

OUTPUT

[{"Id": 101, "Name": "Adam", "X": {"Y": {"Z": 1, "A": 2}, "B": {"Z": 3}}}, {"Id": 102, "Name": "John", "X": {"Y": {"Z": 4, "A": 5}, "B": {"Z": 6}}}, {"Id": 103, "Name": "Sara", "X": {"Y": {"Z": 7, "A": 8}, "B": {"Z": 9}}}]

FORMATED OUTPUT

[
  {
    "Id": 101,
    "Name": "Adam",
    "X": {
      "Y": {
        "Z": 1,
        "A": 2
      },
      "B": {
        "Z": 3
      }
    }
  },
  {
    "Id": 102,
    "Name": "John",
    "X": {
      "Y": {
        "Z": 4,
        "A": 5
      },
      "B": {
        "Z": 6
      }
    }
  },
  {
    "Id": 103,
    "Name": "Sara",
    "X": {
      "Y": {
        "Z": 7,
        "A": 8
      },
      "B": {
        "Z": 9
      }
    }
  }
]

Convert a CSV column to nested field in json

1 Answers1