Convert csv to JSON tree structure and change name?

Question

Thank you to Hatt for the explanation and the code. It works, although I am unable to change the string name for a meaningful name from column headers.

Can anyone suggest how to achieve that?

Data in csv file

conversion_month    channel sub_channel campaign    Id  cost    kpi
2017-08 DISPLAY Retargeting Summer_Campaign 200278217   2.286261    0.1
2017-08 DISPLAY Retargeting Summer_Campaign 200278218   3.627064    2.5
2017-08 DISPLAY Retargeting Summer_Campaign 200278219   2.768436    0.001
2017-08 DISPLAY Retargeting August Campaign 200278220   5.653297    0.35
2017-09 DISPLAY Prospecting Test Campaign   200278221   4.11847 1.5
2017-08 DISPLAY Prospecting August Campaign 200278222   3.393972    0.26
2017-09 DISPLAY Prospecting Test Campaign   200278223   3.975332    4.2
2017-08 DISPLAY Prospecting August Campaign 200278224   4.131035    0.3

Code used:

import csv
from collections import defaultdict

def ctree():
    return defaultdict(ctree)

def build_leaf(name, leaf):
    res = {"name":name}
    # add children node if the leaf actually has any children
    if len(leaf.keys()) > 0:
        res["children"] = [build_leaf(k, v) for k, v in leaf.items()]
    return res

def main():
    tree = ctree()
    with open('file.csv') as csvfile:
        reader = csv.reader(csvfile)
        for rid, row in enumerate(reader):
            if rid == 0:       
                continue
            leaf = tree[row[0]]
            for cid in range(1, (len(row)-2)):
                leaf = leaf[row[cid]]
            for cid in range((len(row)-1), len(row)):
                leaf = (leaf[row[cid-1]],leaf[row[cid]])
    # building a custom tree structure
    res = []
    for name, leaf in tree.items():
        res.append(build_leaf(name, leaf))

    # printing results into the terminal
    import json
    print(json.dumps(res, indent=2))

main()

It gives the tree, but I would like to change the string "name" for meaningful name such as "month", "channel", ..."id", etc. The names are in the first row of the csv file.

[
  {
    "name": "2017-08",
    "children": [
      {
        "name": "DISPLAY",
        "children": [
          {
            "name": "Retargeting",
            "children": [
              {
                "name": "Summer_Campaign",
                "children": [
                  {
                    "name": "200278217",
                    "children": [
                      {
                        "name": "2.286261"
                      },
                      {
                        "name": "0.1"
                      }
                    ]

Thank you for any suggestions in advance.

My question is related to this post: https://stackoverflow.com/questions/43757965/convert-csv-to-json-tree-structure — Agata Plewa, Jun 05 '18 at 02:51
Hello, and welcome to SO. Please remember to provide a [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) in your question so we can reproduce the problem. — Derek Pollard, Jun 05 '18 at 02:56

score 0 · Answer 1 · answered Jun 06 '18 at 08:39

Use next(reader) to first extract the header row from the CSV file. A level counter can be used to indicate which column is currently being dealt with so the corresponding column header can be extracted from the header:

import csv
from collections import defaultdict

def ctree():
    return defaultdict(ctree)

def build_leaf(name, leaf, level, header):
    res = {header[level] : name}
    # add children node if the leaf actually has any children
    if len(leaf.keys()) > 0:
        res["children"] = [build_leaf(k, v, level+1, header) for k, v in leaf.items()]
    return res

def main():
    tree = ctree()
    with open('file.csv') as csvfile:
        reader = csv.reader(csvfile)
        header = next(reader)

        for row in reader:
            leaf = tree[row[0]]
            for cid in range(1, (len(row)-2)):
                leaf = leaf[row[cid]]
            for cid in range((len(row)-1), len(row)):
                leaf = (leaf[row[cid-1]],leaf[row[cid]])

    # building a custom tree structure
    res = []
    for name, leaf in tree.items():
        res.append(build_leaf(name, leaf, 0, header))

    # printing results into the terminal
    import json
    print(json.dumps(res, indent=2))

main()

This would give you:

[
  {
    "conversion_month": "2017-08",
    "children": [
      {
        "channel": "DISPLAY",
        "children": [
          {
            "sub_channel": "Retargeting",
            "children": [
              {
                "campaign": "Summer_Campaign",
                "children": [
                  {
                    "Id": "200278217",
                    "children": [
                      {
                        "cost": "2.286261"
                      },

Convert csv to JSON tree structure and change name?

1 Answers1