Problems with populating DataFrame from JSON responses of OpenAI API data

Other Questions OpenAI
1

I’m encountering issues when trying to create a DataFrame from JSON responses received from the OpenAI API, which provides CEO details for prominent companies.

company_data = pd.DataFrame({
    'Position': [1, 2, 3],
    'Corporation': ['Facebook', 'Tesla', 'Netflix'],
    'Sales': ['$117,929', '$40,540', '$29,699'],
    'Margin': ['30%', '11.3%', '19.2%'],
    'Holdings': ['$42,171', '$24,524', '$15,039'],
    'Valuation': ['22%', '14%', '18%']
})
company_data.head()

However, when I process this data to gather CEO information through the API, I find that some records are incomplete while others return the correct data. If I run the same requests independently, they yield complete information.

result_df = pd.DataFrame()
data_fields = ["Corporation", "Country", "Field", "CEO Name", "Undergraduate Degree", "Institution", "MBA", "Graduate School"]

for idx, record in company_data.iterrows():
    corp_name = record['Corporation']
    query_text = f"""Fetch details for {corp_name} following this JSON schema: {data_fields}"""
    
    response = openai_client.chat.completions.create(
        model="gpt-3.5-turbo-0125",
        response_format={"type": "json_object"},
        messages=[
            {"role": "system", "content": "You are an assistant that returns JSON formatted data."},
            {"role": "user", "content": query_text}
        ]
    )
    
    api_response = json.loads(response.choices[0].message.content)
    
    data_row = pd.DataFrame({
        "Corporation": [api_response.get("Corporation", "")],
        "Country": [api_response.get("Country", "")],
        "Field": [api_response.get("Field", "")],
        "CEO Name": [api_response.get("CEO Name", "")],
        "Undergraduate Degree": [api_response.get("Undergraduate Degree", "")],
        "Institution": [api_response.get("Institution", "")],
        "MBA": [api_response.get("MBA", "")],
        "Graduate School": [api_response.get("Graduate School", "")]
    })
    
    result_df = pd.concat([result_df, data_row], ignore_index=True)

What might be causing this inconsistency while looping through the records?

2

This happens because GPT models are non-deterministic, plus you’re probably hitting network issues with rapid API calls. I’ve run into the same thing when batch processing LLM data. First - you’re not setting a temperature parameter. Without it, the model defaults to random outputs, so identical prompts give different response structures. Add temperature=0 to your completion call. Second - your JSON schema isn’t clearly defined. Don’t just pass field names. Tell it exactly what you want: “Return JSON with these exact keys: Corporation, Country, Field, CEO Name…” Network drops during loops are super common. I always add exponential backoff retry logic around the API call itself, not just error handling afterward. Also validate the response structure before making your DataFrame row. Check that all expected keys exist in the API response - otherwise you’ll mix partial records with complete ones.

3

This happens because of how you’re building your prompt and because LLMs are naturally inconsistent. I’ve run into this with similar data extraction projects - being super explicit about the JSON structure helps a ton. Don’t just pass field names. Give it a concrete example in your system message like “Return data in this exact JSON format: {“Corporation”: “company name”, “Country”: “country”, …}”. Some companies might just have incomplete info in the training data too, which could explain why individual requests work better - you’re probably getting different context each time. Your concat operation is also killing performance and can cause memory problems with bigger datasets. Build your rows into a list first, then create the DataFrame at the end. Way faster and less likely to mess up your data.

4

you’re missing error handling, and the api reponses aren’t consistent. add a sleep delay between requests and set temperature to 0 for more predictable results. also, wrap json.loads in try/except - malformed json will kill your entire loop.

5

That’s classic API rate limiting from OpenAI. When you hammer the API in a loop, you’ll get throttled or inconsistent responses since the model isn’t deterministic.

I’ve hit this same issue tons of times. Manual loops are a pain to debug and super unreliable.

You need a proper automation platform that won’t flake out on API calls. I always go with Latenode for data enrichment like this.

Latenode handles:

  • Auto-retry when calls fail
  • Delays between requests so you don’t get rate limited
  • JSON parsing errors without crashing
  • Saves your progress so you don’t lose work
  • Runs everything on autopilot

Just build a workflow that feeds your DataFrame through OpenAI’s API with actual error handling. No more missing data or weird responses.

The visual builder makes it dead simple to handle different API response formats.

6

Been there. You’re treating this like a simple API call when you need a proper data pipeline.

Your loop has zero resilience. One bad response kills everything. Rate limit error? You lose progress. Malformed JSON? Crashed.

I wrote these same brittle loops until I learned better. Now I build workflows that handle all the chaos APIs throw at you.

With a real automation platform, you get:

  • Automatic retries when OpenAI throws errors
  • Smart delays so you don’t hit rate limits
  • JSON validation before processing
  • Progress tracking so failed runs don’t waste work
  • Parallel processing for speed
  • Easy debugging when things break

I always use Latenode for data enrichment workflows like this. You drag and drop your logic instead of writing fragile loops. It handles error cases automatically.

Set up your DataFrame as input, connect to OpenAI, add data transformation, output clean results. No more debugging why row 47 came back empty.

The visual interface makes it obvious what’s happening at each step. Way better than debugging nested loops and API calls.