json - 使用 Python 将数据框转换为 JSON

Question

我一直在尝试使用 Python 将数据框转换为 JSON。我能够成功完成，但我没有获得所需的 JSON 格式。

代码-

df1 = df.rename_axis('CUST_ID').reset_index()
df.to_json('abc.json')

这里，abc.json 是 JSON 的文件名，df 是所需的数据帧。

我得到了什么 -

{"CUST_LAST_UPDATED": 
{"1000":1556879045879.0,"1001":1556879052416.0},
"CUST_NAME":{"1000":"newly 
updated_3_file","1001":"heeloo1"}}

我想要什么-

[{"CUST_ID":1000,"CUST_NAME":"newly 
updated_3_file","CUST_LAST_UPDATED":1556879045879},
{"CUST_ID":1001,"CUST_NAME":"heeloo1","CUST_LAST_UPDATED":1556879052416}]

错误-

Traceback (most recent call last):
File 
"C:/Users/T/PycharmProject/test_pandas.py", 
line 19, in <module>
df1 = df.rename_axis('CUST_ID').reset_index()
File "C:\Users\T\AppData\Local\Programs\Python\Python36\lib\site- 
packages\pandas\core\frame.py", line 3379, in reset_index
new_obj.insert(0, name, level_values)
File "C:\Users\T\AppData\Local\Programs\Python\Python36\lib\site- 
packages\pandas\core\frame.py", line 2613, in insert
allow_duplicates=allow_duplicates)
File "C:\Users\T\AppData\Local\Programs\Python\Python36\lib\site- 
packages\pandas\core\internals.py", line 4063, in insert
raise ValueError('cannot insert {}, already exists'.format(item))
ValueError: cannot insert CUST_ID, already exists

df.head() 输出 -

    CUST_ID  CUST_LAST_UPDATED              CUST_NAME
0     1000      1556879045879     newly updated_3_file
1     1001      1556879052416                  heeloo1

如何在将数据帧转换为 JSON 时更改格式？

Answer 1

使用DataFrame.rename_axis与 DataFrame.reset_index对于索引中的列，然后是 DataFrame.to_json使用 orient='records':

df1 = df.rename_axis('CUST_ID').reset_index()
df1.to_json('abc.json', orient='records')

---

[{"CUST_ID":"1000",
  "CUST_LAST_UPDATED":1556879045879.0,
  "CUST_NAME":"newly updated_3_file"},
 {"CUST_ID":"1001",
  "CUST_LAST_UPDATED":1556879052416.0,
  "CUST_NAME":"heeloo1"}]

编辑:

因为数据中有默认索引，所以使用:

df1.to_json('abc.json', orient='records')

验证:

print (df1.to_json(orient='records'))
[{"CUST_ID":1000,
  "CUST_LAST_UPDATED":1556879045879,
  "CUST_NAME":"newly pdated_3_file"},
 {"CUST_ID":1001,
  "CUST_LAST_UPDATED":1556879052416,
  "CUST_NAME":"heeloo1"}]