How to create a json from pandas data frame where columns are the key

Question

I have a data frame df

df:
col1    col2  col3
 1        2     3
 4        5     6
 7        8     9

The json I am looking for is:

 {
            "col1": 1,
            "col1": 4,
            "col1": 7,
        },
        {
            "col2": 2,
            "col2": 5,
            "col2": 8
        },
        {
            "col3": 3,
            "col3": 6,
            "col3": 9,
        }

I have tries df.to_json but its not working

df.to_json(orients=records)
it gives this output
'[{"col1":1,"col2":2,"col3":3},{"col1":4,"col2":5,"col3":6}, 
 {"col1":7,"col2":8,"col3":9}]

This is not the output i was looking for

How to do it in most effective way using pandas/python ?

@jezrael I have tried it but I am not getting expected json I am looking for — Kallol
– Kallol, Commented Jun 14, 2019 at 6:29

Rotem Tal · Accepted Answer · 2019-06-14 07:56:51Z

JSON files are treated as dicts in python, the JSON file you specified has duplicate keys and could only be parsed as a string (and not using the python json library). The following code:

import json
from io import StringIO

df = pd.DataFrame(np.arange(1,10).reshape((3,3)), columns=['col1','col2','col3'])
io = StringIO()
df.to_json(io, orient='columns')
parsed = json.loads(io.getvalue())
with open("pretty.json", '+w') as of:
    json.dump(parsed, of, indent=4)

will produce the following JSON:

{
    "col1": {
        "0": 1,
        "1": 4,
        "2": 7
    },
    "col2": {
        "0": 2,
        "1": 5,
        "2": 8
    },
    "col3": {
        "0": 3,
        "1": 6,
        "2": 9
    }
}

which you could later load to python. alternatively, this script will produce exatcly the string you want:

with open("exact.json", "w+") as of:
    of.write('[\n\t{\n' + '\t},\n\t{\n'.join(["".join(["\t\t\"%s\": %s,\n"%(c, df[c][i]) for i in df.index]) for c in df.columns])+'\t}\n]')

and the output would be:

[
    {
        "col1": 1,
        "col1": 4,
        "col1": 7,
    },
    {
        "col2": 2,
        "col2": 5,
        "col2": 8,
    },
    {
        "col3": 3,
        "col3": 6,
        "col3": 9,
    }
]

edit: fixed brackets

manojlds · Accepted Answer · 2019-06-14 06:44:06Z

1

You need to do

df.to_json('file.json', orient='records')

Note that this will give you a array of objects:

[
        {
            "col1": 1,
            "col1": 4,
            "col1": 7
        },
        {
            "col2": 2,
            "col2": 5,
            "col2": 8
        },
        {
            "col3": 3,
            "col3": 6,
            "col3": 9
        }
]

You can also do

df.to_json('file.json', orient='records', lines=True)

if you want output like:

{"col1":1,"col1":4,"col1":7},
{"col2":2,"col2":5,"col2":8},
{"col3":3,"col3":6,"col3":9}

To prettify output:

pip install jq
cat file.json | jq '.' > new_file.json

edited Jun 14, 2019 at 6:44

answered Jun 14, 2019 at 6:30

manojlds

304k66 gold badges483 silver badges426 bronze badges

5 Comments

Kallol Over a year ago

I am getting the output '[{"col1":1,"col2":2,"col3":3},{"col1":4,"col2":5,"col3":6},{"col1":7,"col2":8,"col3":9}]'

manojlds Over a year ago

@Kallol - try with lines=True. And then you can prettify your json with other tools like jq or your text editor like VSCode

Kallol Over a year ago

using lines=True I am getting the same output '{"col1":1,"col2":2,"col3":3}\n{"col1":4,"col2":5,"col3":6}\n{"col1":7,"col2":8,"col3":9}'

manojlds Over a year ago

It's not the same output. But anyway, if you want pretty output, use the jq cli tool

Kallol Over a year ago

including 'file.json' gives not output, how to find 'file.json'

hd1 · Accepted Answer · 2019-06-14 06:29:47Z

0

This sort of JSON, whilst valid, is not recommended, and strongly so, as during deserialization, you'll lose all but the last element of your JSON array.

answered Jun 14, 2019 at 6:29

hd1

34.9k5 gold badges83 silver badges95 bronze badges

Collectives™ on Stack Overflow

How to create a json from pandas data frame where columns are the key

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related