Json to CSV using python and blender 2.74

Question

I have a project in which i have to convert a json file into a CSV file.

The Json sample :

{

  "P_Portfolio Group": {

  "depth": 1,

  "dataType": "PortfolioOverview",

  "levelId": "P_Portfolio Group",

  "path": [

     {

        "label": "Portfolio Group",

        "levelId": "P_Portfolio Group"

     }

  ],

  "label": "Portfolio Group",

  "header": [

     {

        "id": "Label",

        "label": "Security name",

        "type": "text",

        "contentType": "text"

     },

     {

        "id": "SecurityValue",

        "label": "MioCHF",

        "type": "text",

        "contentType": "number"

     },

     {

        "id": "SecurityValuePct",

        "label": "%",

        "type": "text",

        "contentType": "pct"

     }

  ],

  "data": [

     {

        "dataValues": [

           {

              "value": "Client1",

              "type": "text"

           },

           {

              "value": 2068.73,

              "type": "number"

           },

           {

              "value": 14.0584,

              "type": "pct"

           }

        ]

     },

     {

        "dataValues": [

           {

              "value": "Client2",

              "type": "text"

           },

           {

              "value": 1511.9,

              "type": "number"

           },

           {

              "value": 10.2744,

              "type": "pct"

           }

        ]

     },

     {

        "dataValues": [

           {

              "value": "Client3",

              "type": "text"

           },

           {

              "value": 1354.74,

              "type": "number"

           },

           {

              "value": 9.2064,

              "type": "pct"

           }

        ]

     },

     {

        "dataValues": [

           {

              "value": "Client4",

              "type": "text"

           },

           {

              "value": 1225.78,

              "type": "number"

           },

           {

              "value": 8.33,

              "type": "pct"

           }

        ]

     }


  ],

  "summary": [

     {

        "value": "Total",

        "type": "text"

     },

     {

        "value": 11954.07,

        "type": "number"

     },

     {

        "value": 81.236,

        "type": "pct"

     }

  ]

}

}

And i want o obtain something like:

Client1,2068.73,14.0584
Client2,1511.9,10.2744
Client3,871.15,5.92
Client4,11954.07,81.236

Can you please give me a hint.

import csv
import json

with open("C:\Users\SVC\Desktop\test.json") as file:
    x = json.load(file)

f = csv.writer(open("C:\Users\SVC\Desktop\test.csv", "wb+"))

for x in x:
    f.writerow(x["P_Portfolio Group"]["data"]["dataValues"]["value"])

but it doesn't work.

Can you please give me a hint.

Answer 1

Use the pandas library:

import pandas as pd
data = pd.read_csv("C:\Users\SVC\Desktop\test.json")
data.to_csv('test.csv')

done

Answer 2

import csv
import json

with open('C:\Users\SVC\Desktop\test.json') as json_file:
    portfolio_group = json.load(json_file)

with open('C:\Users\SVC\Desktop\test.csv', 'w') as csv_file:
    csv_obj = csv.writer(csv_file)
    for data in portfolio_group['P_Portfolio Group']['data']:
        csv_obj.writerow([d['value'] for d in data['dataValues']])

This results in the following C:\Users\SVC\Desktop\test.csv content:

Client1,2068.73,14.0584
Client2,1511.9,10.2744
Client3,1354.74,9.2064
Client4,1225.78,8.33