0

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!

6
  • start by fixing your indentation, please, as it is it can't run Commented May 4, 2021 at 17:26
  • did you check that: a/ your file contains the expected content b/ your data array contains the expected content? Commented May 4, 2021 at 17:27
  • what do you think this does df = pd.json_normalize(data)[FIELDS] df = df.append(df)? Especially the second line? Commented May 4, 2021 at 17:28
  • hi @njzk2! so sorry but I'm really new with python. 1) can you point out what portion is wrong with indentation? As for 3) my intention was to pick the specific field in [FIELDS] and make a dataframe and keep appending it using df.append in the for loop. 2) yes there is contents! but it's not appending even though i see two specific json outputs. Commented May 6, 2021 at 7:50
  • Just make sure the indentation here matches what you are running. Also df = pd.json_normalize(data)[FIELDS] creates a new dataframe. then df = df.append(df) appends it to itself. Commented May 6, 2021 at 20:32

1 Answer 1

1
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)
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.