[Python] Pandas DataFrame not populating as expected from JSON data produced by OpenAI API

I'm trying to populate a Pandas DataFrame from JSON data (details of CEO of respective fortune 500 companies) returned by OpenAI API. Here's a sample of the data that I am working with.

sample_df = pd.DataFrame({
'Rank': [1, 2, 3],
'Company': ['Walmart', 'Amazon', 'State Grid'],
'Revenue': ['$648,125', '$574,785', '$545,947.5'],
'Profit': ['6%', '11.8%', '3%'],
'Assets': ['$15,511', '$30,425', '$9,204.3'],
'Market Value': ['32.8%', '-', '12.4%']
})
sample_df.head()


However, when I run a for loop to get some additional info via openai API, I get some empty information - as opposed to running this outside of the for loop. How do I make sure all info are populated properly?

{
"Company": "Walmart",
"Country of Origin": "United States",
"Industry": "Retail",
"CEO name": "",
"Bachelor Degree": "",
"University Attended for Bachelor Degree": "",
"MBA": "",
"University for MBA": ""
}
{
"Company": "Amazon",
"Country of Origin": "United States",
"Industry": "E-commerce, Cloud Computing, Artificial Intelligence, Consumer Electronics, Digital Streaming, and more",
"CEO name": "Andy Jassy",
"Bachelor Degree": "Degree in Computer Science and Electrical Engineering",
"University Attended for Bachelor Degree": "Harvard University",
"MBA": "Yes",
"University for MBA": "Harvard Business School"
}
{
"Company": "State Grid",
"Country of Origin": "China",
"Industry": "Energy",
"CEO name": "",
"Bachelor Degree": "",
"University Attended for Bachelor Degree": "",
"MBA": "",
"University for MBA": ""
}


Below is my code (Python):

df_json = pd.DataFrame()
json_format =["Company", "Country of Origin", "Industry", "CEO name", "Bachelor Degree", "University Attended for Bachelor Degree", "MBA", "University for MBA"]
for index, row in sample_df.iterrows():
# print(row['Company'])
company_name = row['Company']
user_prompt = f"""List out the following details of the {company_name} in JSON format where the keys are based on the format listed in the json_format.



'''
{json_format}

''
"""

completion = client.chat.completions.create(
model= "gpt-3.5-turbo-0125",
response_format = {"type": "json_object"},
messages=[
{"role": "system", "content": "You are a helpful assistant designed to output in JSON."},
{"role": "user", "content": user_prompt}
]
)
print(completion.choices[0].message.content)
json_data = completion.choices[0].message.content
json_data = json.loads(json_data)


new_row = pd.DataFrame({
"Company": [json_data.get("Company", "")],
"Country of Origin": [json_data.get("Country of Origin", "")],
"Industry": [json_data.get("Industry", "")],
"CEO name": [json_data.get("CEO name", "")],
"Bachelor Degree": [json_data.get("Bachelor Degree", "")],
"University Attended for Bachelor Degree": [json_data.get("University Attended for Bachelor Degree", "")],
"MBA": [json_data.get("MBA", "")],
"University for MBA": [json_data.get("University for MBA", "")]
})

df_json = pd.concat([df_json, new_row], ignore_index=True)
# print(completion.choices[0].message.content)

Continue reading...