如何从Pandas数据框中创建JSON，其中列是关键 [英] How to create a json from pandas data frame where columns are the key

问题描述

我有一个数据框df

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

我正在寻找的json是：

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,
        }

我尝试了df.to_json但它不起作用

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

如何最有效地做到这一点方式使用pandas / python？

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

推荐答案

JSON文件在python中被视为字典，您指定的JSON文件具有重复的键，并且只能解析为字符串（并且不能使用python json库）。
以下代码：

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)

会产生以下JSON：

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

您可以稍后将其加载到python中。或者，此脚本将完全生成您想要的字符串：

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]')

，输出为：

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

编辑：固定括号

这篇关于如何从Pandas数据框中创建JSON，其中列是关键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！