Convert nested JSON to CSV in Python 2.7

Question

Have seen a lot of thread but unable to found the solution for mine. I want to convert one nested JSON to CSV in Python 2.7. The sample JSON file is as below:

sample.json # My JSON file that mainly contains a firewall rule

"rulebase": [
    {
        "from": 1, 
        "name": "test-policy", 
        "rulebase": [
            {
                "action": "6c488338-8eec-4103-ad21-cd461ac2c473", 
                "action-settings": {}, 
                "comments": "FYI", 
                "content": [
                    "97aeb369-9aea-11d5-bd16-0090272ccb30"
                ], 
                "content-direction": "any", 
                "content-negate": false, 
                "custom-fields": {
                    "field-1": "", 
                    "field-2": "", 
                    "field-3": ""
                }, 
                "destination": [
                    "97aeb369-9aea-11d5-bd16-0090272ccb30"
                ], 
                "destination-negate": false, 
                "domain": {
                    "domain-type": "domain", 
                    "name": "SMC User", 
                    "uid": "41e821a0-3720-11e3-aa6e-0800200c9fde"
                }, 
                "enabled": true, 
                "hits": {
                    "first-date": {
                        "iso-8601": "2016-09-04T22:21-0500", 
                        "posix": 1473045718000
                    }, 
                    "last-date": {
                        "iso-8601": "2018-03-19T03:37-0500", 
                        "posix": 1521448660000
                    }, 
                    "level": "low", 
                    "percentage": "0%", 
                    "value": 36737474
                }, 
                "install-on": [
                    "6c488338-8eec-4103-ad21-cd461ac2c476"
                ], 
                "meta-info": {
                    "creation-time": {
                        "iso-8601": "2016-09-15T12:42-0500", 
                        "posix": 1473961370382
                    }, 
                    "creator": "System", 
                    "last-modifier": "admin", 
                    "last-modify-time": {
                        "iso-8601": "2018-08-30T18:36-0500", 
                        "posix": 1535672186192
                    }, 
                    "lock": "unlocked", 
                    "validation-state": "ok"
                }, 
                "rule-number": 1, 
                "service": [
                    "ef245528-9a3d-11d6-9eaa-3e5a6fdd6a6a", 
                    "dff4f7ba-9a3d-11d6-91c1-3e5a6fdd5151", 
                    "24bee257-6b37-49bb-99aa-557d993a0e48", 
                    "97aeb45c-9aea-11d5-bd16-0090272ccb30", 
                    "97aeb471-9aea-11d5-bd16-0090272ccb30"
                ], 
                "service-negate": false, 
                "source": [
                    "697bb7e0-0dfe-4070-a21a-68858daae98c", 
                    "349fb05c-99b2-4fb2-aea6-7b447d0e661c"
                ], 
                "source-negate": true, 
                "time": [
                    "97aeb369-9aea-11d5-bd16-0090272ccb30"
                ], 
                "track": {
                    "accounting": false, 
                    "alert": "none", 
                    "per-connection": true, 
                    "per-session": false, 
                    "type": "598ead32-aa42-4615-90ed-f51a5928d41d"
                }, 
                "type": "access-rule", 
                "uid": "2da21174-0af8-4b5b-b02e-2957a24d70e1", 
                "vpn": [
                    "97aeb369-9aea-11d5-bd16-0090272ccb30"
                ]
            }, 
            {
                "action": "6c488338-8eec-4103-ad21-cd461ac2c472", 
                "action-settings": {
                    "enable-identity-captive-portal": false
                }, 
                "comments": "", 
                "content": [
                    "97aeb369-9aea-11d5-bd16-0090272ccb30"
                ], 
                "content-direction": "any", 
                "content-negate": false, 
                "custom-fields": {
                    "field-1": "", 
                    "field-2": "", 
                    "field-3": ""
                }, 
                "destination": [
                    "b17d4573-ad1a-4126-ae6d-c874ea919cda", 
                    "5b78417c-64ed-4566-9c76-e4e1af25a9ae", 
                    "acb8d280-2ec4-46b1-be9f-c676fa255fb5"
                ], 
                "destination-negate": false, 
                "domain": {
                    "domain-type": "domain", 
                    "name": "SMC User", 
                    "uid": "41e821a0-3720-11e3-aa6e-0800200c9fde"
                }, 
                "enabled": true, 
                "hits": {
                    "level": "zero", 
                    "percentage": "0%", 
                    "value": 0
                }, 
                "install-on": [
                    "6c488338-8eec-4103-ad21-cd461ac2c476"
                ], 
                "meta-info": {
                    "creation-time": {
                        "iso-8601": "2018-07-25T16:27-0500", 
                        "posix": 1532554044090
                    }, 
                    "creator": "admin", 
                    "last-modifier": "admin", 
                    "last-modify-time": {
                        "iso-8601": "2018-08-31T16:00-0500", 
                        "posix": 1535749228997
                    }, 
                    "lock": "unlocked", 
                    "validation-state": "ok"
                }, 
                "name": "[email protected]", 
                "rule-number": 2, 
                "service": [
                    "18ec9eaa-1657-4240-ab97-5f234623336b"
                ], 
                "service-negate": false, 
                "source": [
                    "293ef5ba-5235-464e-9247-bda26229a998", 
                    "b503873f-0c5f-4798-b87a-dd6ed4561b40"
                ], 
                "source-negate": false, 
                "time": [
                    "97aeb369-9aea-11d5-bd16-0090272ccb30"
                ], 
                "track": {
                    "accounting": false, 
                    "alert": "none", 
                    "per-connection": true, 
                    "per-session": false, 
                    "type": "598ead32-aa42-4615-90ed-f51a5928d41d"
                }, 
                "type": "access-rule", 
                "uid": "fcc5a2c8-3a78-4cc5-9fd3-e7bd59eb36ba", 
                "vpn": [
                    "97aeb369-9aea-11d5-bd16-0090272ccb30"
                ]
            }, 
            {
                "action": "6c488338-8eec-4103-ad21-cd461ac2c472", 
                "action-settings": {
                    "enable-identity-captive-portal": false
                }, 
                "comments": "FYI", 
                "content": [
                    "97aeb369-9aea-11d5-bd16-0090272ccb30"
                ], 
                "content-direction": "any", 
                "content-negate": false, 
                "custom-fields": {
                    "field-1": "", 
                    "field-2": "", 
                    "field-3": ""
                }, 
                "destination": [
                    "b17d4573-ad1a-4126-ae6d-c874ea919cda", 
                    "5b78417c-64ed-4566-9c76-e4e1af25a9ae", 
                    "acb8d280-2ec4-46b1-be9f-c676fa255fb5"
                ], 
                "destination-negate": false, 
                "domain": {
                    "domain-type": "domain", 
                    "name": "SMC User", 
                    "uid": "41e821a0-3720-11e3-aa6e-0800200c9fde"
                }, 
                "enabled": true, 
                "hits": {
                    "first-date": {
                        "iso-8601": "2018-03-14T14:55-0500", 
                        "posix": 1521057347000
                    }, 
                    "last-date": {
                        "iso-8601": "2018-03-19T03:58-0500", 
                        "posix": 1521449932000
                    }, 
                    "level": "low", 
                    "percentage": "0%", 
                    "value": 11801
                }, 
                "install-on": [
                    "6c488338-8eec-4103-ad21-cd461ac2c476"
                ], 
                "meta-info": {
                    "creation-time": {
                        "iso-8601": "2018-03-14T09:47-0500", 
                        "posix": 1521038846894
                    }, 
                    "creator": "System", 
                    "last-modifier": "admin", 
                    "last-modify-time": {
                        "iso-8601": "2018-08-31T16:17-0500", 
                        "posix": 1535750234317
                    }, 
                    "lock": "unlocked", 
                    "validation-state": "ok"
                }, 
                "name": "[email protected]", 
             }

From the above JSON file my requirement to redirect keys {uid, name, rule-number, comments, destination, source, hits.last-date}, etc. with their values to CSV basically.

By following the below code, I was able to generate the CSV but seems that is only parsing header, nothing else.

import json
import csv

def get_leaves(item, key=None):
    if isinstance(item, dict):
        leaves = []
        for i in item.keys():
            leaves.extend(get_leaves(item[i], i))
        return leaves
    elif isinstance(item, list):
        leaves = []
        for i in item:
            leaves.extend(get_leaves(i, key))
        return leaves
    else:
        return [(key, item)]

with open('sample.json') as f_input, open('output.csv', 'wb') as f_output:
csv_output = csv.writer(f_output)
write_header = True

for entry in json.load(f_input):
    leaf_entries = sorted(get_leaves(entry))

    if write_header:
        csv_output.writerow([k for k, v in leaf_entries])
        write_header = False

    csv_output.writerows([v for k, v in leaf_entries.items()])

Please guide me as I am very much new to Python scripting.

Update your JSON and code with a working example. I made an attempt to get it running, but not sure my output is what you are getting. Answers should be able to save your input and code as files and just run them with no changes. The JSON is not complete and the code gives indentation errors. Added expected and actual output helps as well. See the minimal reproducible example guidelines. — Mark Tolonen
– Mark Tolonen, Commented Sep 20, 2018 at 23:24

pythomatic · Accepted Answer · 2018-09-20 16:03:32Z

0

You're pretty much there. You're just calling csv_output.writerow() on the list you created with [v for k, v in leaf_entries]. You should instead call csv_output.writerows().

Information on these calls is available here: https://docs.python.org/3/library/csv.html#writer-objects

edited Sep 20, 2018 at 16:03

answered Sep 20, 2018 at 15:55

pythomatic

6575 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rio Over a year ago

By updating csv_output.writerow([v for k, v in leaf_entries]) the output redirects each letter to each cell in excel with no values.

pythomatic Over a year ago

Use leaf_entries.items() to for your dict from json

Rio Over a year ago

csv_output.writerows([v for k, v in leaf_entries.items()]) AttributeError: 'list' object has no attribute 'items' This is where it is stuck now.

Rio · Accepted Answer · 2018-09-21 15:21:51Z

0

Just figured it out. The below code properly working and generating valid csv data from my complex JSON file.

# Generate CSV from JSON
fw_access_layers_data = open('show-access-layers.json', 'r')
fw_access_layers_parsed = json.loads(fw_access_layers_data.read())
access_layers = fw_access_layers_parsed['access-layers']
fw_access_layers_csv = open('show-access-layers.csv', 'w')
csvwriter = csv.writer(fw_access_layers_csv)
count = 0
for access_layer in access_layers:
if count == 0:
    header = access_layer.keys()
    csvwriter.writerow(header)
    count += 1
csvwriter.writerow(access_layer.values())
fw_access_layers_csv.close()

Thanks for your help mates.

edited Sep 21, 2018 at 15:21

answered Sep 21, 2018 at 13:31

Rio

7032 gold badges8 silver badges32 bronze badges

Collectives™ on Stack Overflow

Convert nested JSON to CSV in Python 2.7

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related