python - 如何使用 python pandas 将此 Json 转换为 CSV？

Question

尝试将这个大的 json 转换为 csv。这是示例 JSON

    [
    [{
            "User": "Phoebe",
            "text": "Oh my God, hes lost it. Hes totally lost it.",
            "sent": "non-neutral"
        },
        {
            "user": "Monica",
            "text": "What?",
            "sent": "surprise"
        }

    ],
    [{
            "user": "Joey",
            "text": "Hey Estelle, listen",
            "sent": "neutral"
        },
        {
            "user": "Estelle",
            "text": "Well! Well! Well! Joey Tribbiani! So you came back huh? They",
            "sent": "surprise"
        }
    ]
]


with open('/Users/dsg281/Downloads/EmotionLines/Friends/friends_dev.json') as data_file:    
        data = json.load(data_file) 

我正在尝试在 csv 中获取包含 "User"、"text"、"sent"

Answer 1

我认为需要(将 json 更改为有效后):

file.json

[
  [
    {
      "user": "Phoebe",
      "text": "Oh my God, hes lost it. Hes totally lost it.",
      "sent": "non-neutral"
    },
    {
      "user": "Monica",
      "text": "What?",
      "sent": "surprise"
    }

   ],
   [{
      "user": "Joey",
      "text": "Hey Estelle, listen",
      "sent": "neutral"
    },
    {
      "user": "Estelle",
      "text": "Well! Well! Well! Joey Tribbiani! So you came back huh? They",
      "sent": "surprise"
    }
  ]     
]

---

import json

with open('file.json') as data_file:    
    data = json.load(data_file)  

df = pd.concat([pd.DataFrame(x) for x in data], ignore_index=False)
print (df)
          sent                                               text     user
0  non-neutral       Oh my God, hes lost it. Hes totally lost it.   Phoebe
1     surprise                                              What?   Monica
0      neutral                                Hey Estelle, listen     Joey
1     surprise  Well! Well! Well! Joey Tribbiani! So you came ...  Estelle

然后:

df.to_csv(file, index=False)

编辑:

如果想使用非 pandas 的纯 python 解决方案，可以对 this solution 进行一些修改:

import json, csv

with open('file.json') as data_file:    
    data = json.load(data_file)  

f = csv.writer(open("test.csv", "w+"))
f.writerow(["user", "sent", "text"])

for y in data:
    for x in y: #added for read nested lists
        f.writerow([x["user"], x["sent"], x["text"]])