[Python] JSON to Python dataframe: mapping values from another API

I have an API with student data like this, for every student id there will be a corresponding API link with mark details.

for example:

https://api.school.com/2020/students.json

{
"Students": [
{
"StudentName": "AAA",
"Sid": 1020,
"Saddress": "st.aaa",
"Sdob": "10-11-1999"
},
{
"StudentName": "BBB",
"Sid": 1021,
"Saddress": "st.bbb",
"Sdob": "11-11-1999"
},
{
"StudentName": "CCC",
"Sid": 1022,
"Saddress": "st.fff",
"Sdob": "05-12-1999"
},
{
"StudentName": "DDD",
"Sid": 1023,
"Saddress": "st.ddd",
"Sdob": "15-09-1999"
},
{
"StudentName": "EEE",
"Sid": 1024,
"Saddress": "st.eee",
"Sdob": "10-11-1999"
},
{
"StudentName": "FFF",
"Sid": 1025,
"Saddress": "st.ddd",
"Sdob": "20-11-1999"
},
{
"StudentName": "GGG",
"Sid": 1026,
"Saddress": "st.ggg",
"Sdob": "25-11-1999"
},
{
"StudentName": "JJJ",
"Sid": 1019,
"Saddress": "st.aaa",
"Sdob": "18-11-1999"
}
]
}


https://api.school.com/2020/mark.json?sid=1020

{
"marks": [
{
"English": 11,
"Math": 12,
"Art": 13,
"Science": 14,
"History": 15,
"Geography": 16,
"Physical Education": 17,
"Chemistry": 18,
"Physics": 19,
"Biology": 20
}
]
}


https://api.school.com/2020/mark.json?sid=1021

{
"marks": [
{
"English": 21,
"Math": 22,
"Art": 23,
"Science": 24,
"History": 25,
"Geography": 26,
"Physical Education": 27,
"Chemistry": 28,
"Physics": 29,
"Biology": 30
}
]
}


https://api.school.com/2020/mark.json?sid=1022

{
"marks": [
{
"English": 31,
"Math": 32,
"Art": 33,
"Science": 34,
"History": 35,
"Geography": 36,
"Physical Education": 37,
"Chemistry": 38,
"Physics": 39,
"Biology": 40
}
]
}


https://api.school.com/2020/mark.json?sid=1023

{
"marks": [
{
"English": 41,
"Math": 42,
"Art": 43,
"Science": 44,
"History": 45,
"Geography": 46,
"Physical Education": 47,
"Chemistry": 48,
"Physics": 49,
"Biology": 50
}
]
}


https://api.school.com/2020/mark.json?sid=1024

{
"marks": [
{
"English": 51,
"Math": 52,
"Art": 53,
"Science": 54,
"History": 55,
"Geography": 56,
"Physical Education": 57,
"Chemistry": 58,
"Physics": 59,
"Biology": 60
}
]
}


https://api.school.com/2020/mark.json?sid=1025

{
"marks": [
{
"English": 61,
"Math": 62,
"Art": 63,
"Science": 64,
"History": 65,
"Geography": 66,
"Physical Education": 67,
"Chemistry": 68,
"Physics": 69,
"Biology": 70
}
]
}


https://api.school.com/2020/mark.json?sid=1026

{
"marks": [
{
"English": 71,
"Math": 72,
"Art": 73,
"Science": 74,
"History": 75,
"Geography": 76,
"Physical Education": 77,
"Chemistry": 78,
"Physics": 79,
"Biology": 80
}
]
}


https://api.school.com/2020/mark.json?sid=1019

{
"marks": [
{
"English": 1,
"Math": 2,
"Art": 3,
"Science": 4,
"History": 5,
"Geography": 6,
"Physical Education": 7,
"Chemistry": 8,
"Physics": 9,
"Biology": 10
}
]
}


I need to get an output like this in a csv file

My code(not completed):

import requests
import json
import pandas as pd

def getdata(link):
try:
response = s.get(link)
except requests.exceptions.RequestException as e:
print('Request Exception Found!')
json_data = response.json()
return json_data

s = requests.session()
json_data = getdata('https://api.school.com/2020/students.json')

print(json_data)

def normalize(json_data,record_path):
temp = json.dumps(json_data)
ar = json.loads(temp)
df = pd.json_normalize(ar[record_path])
return df

Student_df = normalize(json_data, 'Students')

Student_ids = Student_df["Sid"].tolist()

print(Student_ids)

links = []

for i, row in Student_df.groupby('Sid').size().items():
link = "https://api.school.com/2020/mark.json?sid="+str(i)
links.append(link)

for x in links:
normalize(getdata(x),'marks')


is it possible to insert the StudentName and Sid along with the mark? when the program fetches the mark from each API, it should be able to map and add the StudentName and Sid.

if i saves each mark api data to a json file in the local machine, then i can use this code and it works perfectly

Continue reading...