2

I am trying to convert a CSV file to nested JSON, here's my CSV with first row as columns.

CLID,District, attribute,value
C001,Tebuslik, Name,Philip
C001,Tebuslik,Age,34
C002,Hontenlo,Name,Jane
C002,Hontenlo,Age,23

My desired output is a nested json where the values of the Age key are numeric and not strings.

[
    {
        "CLID": "C001",
        "District": "Tebuslik",
        "attributes": [
            {
                "attribute": "Name",
                "value": "Philip"
            },
            {
                "attribute": "Age",
                "value": 34
            }
        ]
    },
    {
        "CLID": "C002",
        "District": "Hontenlo",
        "attributes": [
            {
                "attribute": "Name",
                "value": "Jane"
            },
            {
                "attribute": "Age",
                "value": 23
            }
        ]
    }
]

In my CSV ,all keys share the same column (Attribute) and the value could be of string or numeric format depending on the attribute.

Here's my python script that half-works:

from csv import DictReader
from itertools import groupby
from pprint import pprint
import json

with open('teis.csv') as csvfile:
    r = DictReader(csvfile, skipinitialspace=True)
    data = [dict(d) for d in r]

    groups = []
    uniquekeys = []

    for k, g in groupby(data, lambda r: (r['CLID'], r['District'])):
        groups.append({
            "CLID": k[0],
            "District": k[1],
            "attributes": [{k:v for k, v in d.items() if k not in ['CLID','District']} for d in list(g)]
        })
        uniquekeys.append(k)

print(json.dumps(groups, indent = 4) + '\n}')

However, below is the output i get with quoted numeric age values;

[
    {
        "CLID": "C001",
        "District": "Tebuslik",
        "attributes": [
            {
                "attribute": "Name",
                "value": "Philip"
            },
            {
                "attribute": "Age",
                "value": "34"
            }
        ]
    },
    {
        "CLID": "C002",
        "District": "Hontenlo",
        "attributes": [
            {
                "attribute": "Name",
                "value": "Jane"
            },
            {
                "attribute": "Age",
                "value": "23"
            }
        ]
    }
]
Aleu
  • 73
  • 6

1 Answers1

2

Use str.isdigit to check the string and then use int.

Ex:

from csv import DictReader
from itertools import groupby
from pprint import pprint
import json

with open(filename) as csvfile:
    r = DictReader(csvfile, skipinitialspace=True)
    data = [dict(d) for d in r]

    groups = []
    uniquekeys = []

    for k, g in groupby(data, lambda r: (r['CLID'], r['District'])):
        groups.append({
            "CLID": k[0],
            "District": k[1],
            "attributes": [{k:int(v) if v.isdigit() else v for k, v in d.items() if k not in ['CLID','District']} for d in list(g)]  #Update
        })
        uniquekeys.append(k)

print(json.dumps(groups, indent = 4) + '\n}')
Rakesh
  • 81,458
  • 17
  • 76
  • 113
  • 1
    You have saved my day. I didn't know that i'd use such a smart string method in that for block. I guess I can also check for date values too if i expand my CSV. – Aleu Nov 26 '19 at 08:39
  • How can I handle floating point values so that they don't have quotes? – Aleu Nov 26 '19 at 13:26
  • You could call `float()` and catch the error with `try/except`. @Aleu – Barmar Nov 26 '19 at 16:45
  • 1
    @Aleu See https://stackoverflow.com/questions/10261141/determine-type-of-value-from-a-string-in-python – Barmar Nov 26 '19 at 16:49