CODEWITHSUNDEEP
pythonarraysdataframefor-loopappend
user1486507
user1486507

Reputation: 709

convert json to dataframe in for loops in python

I'm trying to call the data using api and making a dataframe using for loops with returned json. I am able to create the first dataframe but my for loop only returns the first json -> dataframe. After a few days struggle, I decided to ask guidance from experts here..

import requests
import json
import pandas as pd

# create an Empty DataFrame object
df = pd.DataFrame()

# api header
headers = {"Accept": "application/json","Authorization": "api_secret"}

#email for loops
email_list = ["[email protected]", "[email protected]"]

#supposed to read 2 emails in the list and append each df but only reads the first one...# 

for i in email_list:

  querystring = {"where":i}  
  response = requests.request("GET", "https://example.com/api/2.0/export", headers=headers, params=querystring) 

  with open('test.jsonl', 'w') as writefile:
    writefile.write(response.text)    
  data = [json.loads(line) for line in open('test.jsonl', 'r')]
  FIELDS = ["event"]
  df = pd.json_normalize(data)[FIELDS]
  df = df.append(df)

I wonder if I need to change something in df append but I can't pinpoint where needs to be changed. thank you so much in advance!

Upvotes: 0

Views: 908

Answers (1)

njzk2
njzk2

Reputation: 39386

df = pd.json_normalize(data)[FIELDS]
df = df.append(df)

overwrites the dataframe each time instead, create a new one before appending:

df2 = pd.json_normalize(data)[FIELDS]
df = df.append(df2)

Upvotes: 1

Related Questions