How to export API JSON data to CSV file with Python

Question

I faced with a problem. I've got some data in JSON format with API.

It looks like that:

Example:

{"ID":"938","ENTITY_ID":"CRM_DEAL","FIELD_NAME":"UF_CRM_111111111111","USER_TYPE_ID":"enumeration","LIST":\[{"ID":"700","SORT":"10","VALUE":"text1","DEF":"N"},{"ID":"948","SORT":"20","VALUE":"text2","DEF":"N"}\]}

I need to output data in CSV format like this:

ID, ENTITY_ID, FIELD_NAME

938, CRM_DEAL, UF_CRM_111111111111, 700, text1
938, CRM_DEAL, UF_CRM_111111111111, 948, text2

Help me please, how can I solve this problem?

I've tried to run this code:

import requests 
import pandas as pd 
r = requests.get("https://myrestAPImethod")

df = pd.DataFrame(r)

print(r.text) with open("test.csv", 'w+', encoding='UTF8', newline='') as f: 
f.write(r.text)

But I've got wrong CSV file.

I need this:

ID, ENTITY_ID, FIELD_NAME

938, CRM_DEAL, UF_CRM_111111111111, 700, text1
938, CRM_DEAL, UF_CRM_111111111111, 948, text2

But I,ve got text

{"ID":"938","ENTITY_ID":"CRM_DEAL","FIELD_NAME":"UF_CRM_111111111111","USER_TYPE_ID":"enumeration","LIST":\[{"ID":"700","SORT":"10","VALUE":"text1","DEF":"N"},{"ID":"948","SORT":"20","VALUE":"text2","DEF":"N"}\]}

in the first A1 cell (when I open CSV with Excel).

Answer 1

You converted it to a dataframe with pandas. So now just use pandas to write to file:

r = {"ID":"938","ENTITY_ID":"CRM_DEAL","FIELD_NAME":"UF_CRM_111111111111","USER_TYPE_ID":"enumeration","LIST":[{"ID":"700","SORT":"10","VALUE":"text1","DEF":"N"},{"ID":"948","SORT":"20","VALUE":"text2","DEF":"N"}]}


df = pd.DataFrame(r)
df.to_csv("test.csv", index=False)

To flatten it out a bit:

import pandas as pd

r = {"ID":"938","ENTITY_ID":"CRM_DEAL","FIELD_NAME":"UF_CRM_111111111111","USER_TYPE_ID":"enumeration","LIST":[{"ID":"700","SORT":"10","VALUE":"text1","DEF":"N"},{"ID":"948","SORT":"20","VALUE":"text2","DEF":"N"}]}

df1 = pd.DataFrame(r).iloc[:,:-2]
df2 = pd.json_normalize(r, record_path=['LIST'])

df = pd.concat([df1, df2], axis=1)
df.to_csv("test.csv", index=False)

Output:

print(df)
    ID ENTITY_ID           FIELD_NAME   ID SORT  VALUE DEF
0  938  CRM_DEAL  UF_CRM_111111111111  700   10  text1   N
1  938  CRM_DEAL  UF_CRM_111111111111  948   20  text2   N