如何使用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更改为valid之后）：

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"
    }
  ]     
]

---

^{pr2}$

然后：

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

Answer 2

如果我们提供一个空列表的起始值[]，我们可以将这些列表与sum结合起来

pd.DataFrame(sum(json.load(open('file.json')), [])).to_csv('file.csv', index=False)

sent,text,user
non-neutral,"Oh my God, hes lost it. Hes totally lost it.",Phoebe
surprise,What?,Monica
neutral,"Hey Estelle, listen",Joey
surprise,Well! Well! Well! Joey Tribbiani! So you came back huh? They,Estelle