Converting JSON to CSV w/ Pandas Library

Question

I'm having trouble converting a JSON file to CSV in Python and I'm not sure what's going wrong. The conversion completes but it is not correct. I think there's an issue due to the formatting of the JSON file; however, it's a valid JSON.

Here's the content of my JSON file:

{
    "tags": [{
        "name": "ACDTestData",
        "results": [{
            "groups": [{
                "name": "type",
                "type": "number"
            }],
            "values": [
                [
                    1409154300000,
                    1.16003418,
                    3
                ],
                [
                    1409154240000,
                    0.024047852,
                    3
                ],
                [
                    1409153280000,
                    10.25598145,
                    3
                ],
                [
                    1409152200000,
                    10.73193359,
                    3
                ],
                [
                    1409151240000,
                    0.024047852,
                    3
                ],
                [
                    1409080200000,
                    14.34393311,
                    3
                ],
                [
                    1409039580000,
                    4.883850098,
                    3
                ],
                [
                    1408977480000,
                    5.520019531,
                    3
                ],
                [
                    1408977360000,
                    0.00793457,
                    3
                ],
                [
                    1408974300000,
                    2.695922852,
                    3
                ],
                [
                    1408968480000,
                    0.011962891,
                    3
                ],
                [
                    1408965720000,
                    0.427978516,
                    3
                ],
                [
                    1408965660000,
                    0.011962891,
                    3
                ]
            ]
        }]
    }]
}

Here's what I tried:

import pandas as pd
json_file = pd.read_json("QueryExportTest2.json")
json_file.to_csv()

Here's my output:

,tags\n0,"{u\'name\': u\'ACDTestData\', u\'results\': [{u\'values\': [[1409154300000L, 1.16003418, 3], [1409154240000L, 0.024047852, 3], [1409153280000L, 10.25598145, 3], [1409152200000L, 10.73193359, 3], [1409151240000L, 0.024047852, 3], [1409080200000L, 14.34393311, 3], [1409039580000L, 4.883850098, 3], [1408977480000L, 5.520019531, 3], [1408977360000L, 0.00793457, 3], [1408974300000L, 2.695922852, 3], [1408968480000L, 0.011962891000000002, 3], [1408965720000L, 0.42797851600000003, 3], [1408965660000L, 0.011962891000000002, 3]], u\'groups\': [{u\'type\': u\'number\', u\'name\': u\'type\'}]}]}"\n

This isn't right, because when I put it into a new Excel CSV doc instead of just printing it, the CSV is all in one cell.

If it helps, when I try this:

import json

with open('QueryExportTest2.json') as json_data:
d = json.load(json_data)
print(d)

I get this:

{u'tags': [{u'name': u'ACDTestData', u'results': [{u'values': [[1409154300000L, 1.16003418, 3], [1409154240000L, 0.024047852, 3], [1409153280000L, 10.25598145, 3], [1409152200000L, 10.73193359, 3], [1409151240000L, 0.024047852, 3], [1409080200000L, 14.34393311, 3], [1409039580000L, 4.883850098, 3], [1408977480000L, 5.520019531, 3], [1408977360000L, 0.00793457, 3], [1408974300000L, 2.695922852, 3], [1408968480000L, 0.011962891, 3], [1408965720000L, 0.427978516, 3], [1408965660000L, 0.011962891, 3]], u'groups': [{u'type': u'number', u'name': u'type'}]}]}]}

How can I convert this nested JSON to CSV properly?

Answer 1

Your json is a nested dict (with lists and other dictionaries). I guess that you are interested in the values section of the json. If my assumption is correct, since this is a single entry json, try the following

df = pd.DataFrame.from_dict(json_str['tags'][0]['results'][0]['values'])
df.columns = ['var1','var2', 'var3']
df.to_csv(filename)

If you will have more records you will have to iterate over the lists of values, namely you could append them.

all_results = json['tags'][0]['results']
for i in range(0, len(all_results))
    if i == 0:
        my_df = pd.DataFrame(all_results[i]['values'])
    else:
        my_df.append(pd.DataFrame(all_results[i]['values']))

Answer 2

What are you trying to get in the end? The problem here is that your json is nested, if you for example try doing something like this:

pandas.DataFrame.from_dict(jour_json['tags'])

you will get dataframe with two columns - name and result.