How to convert this Json to CSV using python pandas?

Question

Trying to convert this big json to csv.Here json sample

    [
    [{
            "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) 

I am trying to get output in csvwith columns "User" , "text , " sent

Answer 1

It seems to me what is needed (after changing json to action):

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

And then:

df.to_csv(file, index=False)

EDIT:

If you want to use a pure python solution without pandas, you can change it a bit with 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

We can combine lists with sumif we provide the initial value of an empty list[]

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