How to convert csv with multiple values in a cell into nested json in python

Question

I have a csv file with multiple values in one cell like in this format:

ID, Name,      Role,               Task,                       Responsibility
123, Stephen, "1. Give, 2. Take", "1.1. DO, 1.2. AB, 2.1. DF", "1.1.1. FG, 1.1.2. GH, 1.2.1. SG, 2.1.1. DF, 2.1.2. JK"

I added some white space for readability. I need to convert this csv file into nested json format like:

{
    "Name" : "Stephen",
    "123": {
        "1": {
            "Role": "Give",
            "1.1": {
                "Task": "DO",
                "1.1.1": {
                    "Responsibility": "FG"
                },
                "1.1.2": {
                    "Responsibility": "GH"
                }
            },
            "1.2": {
                "Task": "AB",
                "1.2.1": {
                    "Responsibility": "SG"
                }
            }
        },
        "2": {
            "Role": "Take",
            "2.1": {
                "Task": "DF",
                "2.1.1": {
                    "Responsibility": "DF"
                },
                "2.1.2": {
                    "Responsibility": "JK"
                }
            }
        }
    }
}

and the numbers go like this 1, 1.1, 1.2.1, 2.2, 2.3, 2.3.1. I need a to detect such cells (or such type of columns) and convert it into the key:value pair like above.

So far, I can simply convert it into json like. {"ID" : "123", "Task" : {"1.1":"DO", "1.2": "AB"}}... and so on. I don't know how to convert one part of value (like 1, 1.1) in a cell into a key and another part to a nested pair of key value pair. For ex: 1.1 = key and another part "DO" as nested { "Task" : "DO" } and also 1.1.1 comes under 1.1 nested key:value pair like "1.1" : {"Task" :"DO", "1.1.1": "Responsibility" : "FG"} — user12460726
– user12460726, Commented Jan 30, 2020 at 4:34
What the source of csv file? The structure looks over-complicated, and at a glance map this csv to expected json - insane thing. Better to start simplifying data you recieve if possible. @Ajax1234 have written good code very fast (respect), but it's not very clear. Just for copy-and-paste to your project and pray you will never have to change it (or have to explain its work to professor). — mrEvgenX
– mrEvgenX, Commented Jan 30, 2020 at 4:52

Ajax1234 · Accepted Answer · 2020-01-30 04:42:38Z

You can use recursion with itertools.groupby:

from itertools import groupby as gb
def to_dict(data):
   d = [(a, list(b)) for a,b in gb(sorted(data, key=lambda x:x[0][0]), key=lambda x:x[0][0])]
   return {b[0][1]:{**b[0][-1], **to_dict([[j, k, l] for [_, *j], k, l in b if j])} for a,b in d}

import re, json
s = """
ID, Name,      Role,               Task,                       Responsibility
123, Stephen, "1. Give, 2. Take", "1.1. DO, 1.2. AB, 2.1. DF", "1.1.1. FG, 1.1.2. GH, 1.2.1. SG, 2.1.1. DF, 2.1.2. JK"
"""
#below: parse desired values from data and format header
[h1, h2, *h], [_id, n, *_data] = [re.findall('(?<=")[^"]+|\w+', i) for i in filter(None, s.split('\n'))]
#transform numerical paths as lists
data = [[b.split('. ') for b in i.split(', ')] for i in _data if i != ', ']
#associate original file headers to the transformed data
formed = [l for a, b in zip(h, data) for l in [[c.split('.'), c, {a:d}] for c, d in b]]
print(json.dumps({h2:n, h1:to_dict(formed)}, indent=4))

Output:

{
  "Name": "Stephen",
  "ID": {
    "1": {
        "Role": "Give",
        "1.1": {
            "Task": "DO",
            "1.1.1": {
                "Responsibility": "FG"
            },
            "1.1.2": {
                "Responsibility": "GH"
            }
        },
        "1.2": {
            "Task": "AB",
            "1.2.1": {
                "Responsibility": "SG"
            }
        }
    },
    "2": {
        "Role": "Take",
        "2.1": {
            "Task": "DF",
            "2.1.1": {
                "Responsibility": "DF"
            },
            "2.1.2": {
                "Responsibility": "JK"
            }
        }
      }
   }
}

Collectives™ on Stack Overflow

How to convert csv with multiple values in a cell into nested json in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related