How to join data from two API in Python

So, far I have only used get/post data from one API at a time. I am not sure how can I join data from two API. Similar like SQL, is there a some sort of Join tables to achieve same with the API?

I would like to display staff photos with other staff details in html template.

API 1
https://test.com/api/Staff/GetStaffDetails    
StaffId, Firstname, Lastname, Email

API 2
https://test.com/api/Staff/GetStaffPhotos    
Id (this matches with the StaffId), Photo

Staff Details

def StaffDetails(request, *, context):
    url = "https://test.com/api/Staff/GetStaffDetails"
    headers = {
        "X-DOMAIN": "test",
        "X-TOKEN": "123456789qwertyuiop",
        "User-Agent": "",
    }

    response = requests.post(url, headers=headers)
    data = response.json()
    random_data = random.sample(data, min(5, len(data)))
    context = {"data": random_data}
    return render(request, "StaffDetails.html", context)

StaffDetails.html
  <table border="1">
    <tr>
      <td>STAFFID</td>
      <td>FIRSTNAME</td>
      <td>LASTNAME</td>      
      <td>EMAIL</td>      
    </tr>
  
      {% for row in data %}
    <tr>
      <td>{{ row.StaffId }}</td>
      <td>{{ row.Firstname }}</td>
      <td>{{ row.Lastname }}</td>     
      <td>{{ row.Email }}</td>  
    </tr>
    {% endfor %}  
  </table>

Staff Photo

def StaffPhoto(request, *, context):
    url = "https://test.com/api/Staff/GetStaffPhotos"
    headers = {
        "X-DOMAIN": "test",
        "X-TOKEN": "123456789qwertyuiop",
        "User-Agent": "",
    }

    response = requests.post(url, headers=headers)
    data = response.json()
    random_data = random.sample(data, min(5, len(data)))
    context = {"data": random_data}
    return render(request, "StaffPhoto.html", context)
    
StaffPhoto.html
  <table border="1">
    <tr>
      <td>ID</td>
      <td>PHOTO</td>      
    </tr>
  
      {% for row in data %}
    <tr>
      <td>{{ row.Id }}</td>
      <td><img src="data:image/jpeg;base64,{{ row.Photo }}" width="100px"></td>  
    </tr>
    {% endfor %}  
  </table>

Thank you.

Solution

It seems you have images as list of dictionares

[{'Id': number1, 'Photo': image1}, {'Id': number2, 'Photo': image2}, ...]

but if you convert it to dictionary

{number1:image1, number2:image2}

using

images = {item['Id']:item['Photo'] for item in data_images}

then you don't need to join it because you can directly use row['StaffId'] to get image from this dictionary.

for item in data_staff:
    _id = item['StaffId']
    print(_id, item['Firstname'], images[_id])

Minimal working example:

data_staff = [
    {'StaffId': 1, 'Firstname': 'A', 'Lastname':'A', 'Email': 'A.A@gmail.com'},
    {'StaffId': 2, 'Firstname': 'B', 'Lastname':'B', 'Email': 'B.B@gmail.com'},
    {'StaffId': 3, 'Firstname': 'C', 'Lastname':'C', 'Email': 'C.C@gmail.com'},
    {'StaffId': 4, 'Firstname': 'D', 'Lastname':'D', 'Email': 'D.D@gmail.com'},
]

data_images = [
    {'Id': 1, 'Photo': 'image_A_A'},
    {'Id': 2, 'Photo': 'image_B_B'},
    {'Id': 3, 'Photo': 'image_C_C'},
    {'Id': 4, 'Photo': 'image_D_D'},
]

images = {item['Id']:item['Photo'] for item in data_images}

for item in data_staff:
    _id = item['StaffId']
    print(_id, item['Firstname'], images[_id])

Result:

1 A image_A_A
2 B image_B_B
3 C image_C_C
4 D image_D_D

If you need to convert to one table then you can convert to pandas.DataFrame

import pandas as pd

df_staff = pd.DataFrame(data_staff)
df_images = pd.DataFrame(data_images)

and merge using left_on="StaffId", right_on="Id"

df = pd.merge(df_staff, df_images, left_on='StaffId', right_on='Id')

later it may need to drop Id and keep only StaffId

df = df.drop(columns='Id')

It may need to convert to list of dictionares - to use in Django

data = df.to_dict(orient='records')

Minimal working example:

data_staff = [
    {'StaffId': 1, 'Firstname': 'A', 'Lastname':'A', 'Email': 'A.A@gmail.com'},
    {'StaffId': 2, 'Firstname': 'B', 'Lastname':'B', 'Email': 'B.B@gmail.com'},
    {'StaffId': 3, 'Firstname': 'C', 'Lastname':'C', 'Email': 'C.C@gmail.com'},
    {'StaffId': 4, 'Firstname': 'D', 'Lastname':'D', 'Email': 'D.D@gmail.com'},
]

data_images = [
    {'Id': 1, 'Photo': 'image_A_A'},
    {'Id': 2, 'Photo': 'image_B_B'},
    {'Id': 3, 'Photo': 'image_C_C'},
    {'Id': 4, 'Photo': 'image_D_D'},
]

import pandas as pd

df_staff = pd.DataFrame(data_staff)
print(df_staff)

df_images = pd.DataFrame(data_images)
print(df_images)

#df_images.rename(columns={'Id': 'StaffId'})

df = pd.merge(df_staff, df_images, left_on='StaffId', right_on='Id')
df = df.drop(columns='Id')
print(df)

data = df.to_dict(orient='records')
print(data)

Result:

   StaffId Firstname Lastname          Email
0        1         A        A  A.A@gmail.com
1        2         B        B  B.B@gmail.com
2        3         C        C  C.C@gmail.com
3        4         D        D  D.D@gmail.com

   Id      Photo
0   1  image_A_A
1   2  image_B_B
2   3  image_C_C
3   4  image_D_D

   StaffId Firstname Lastname          Email      Photo
0        1         A        A  A.A@gmail.com  image_A_A
1        2         B        B  B.B@gmail.com  image_B_B
2        3         C        C  C.C@gmail.com  image_C_C
3        4         D        D  D.D@gmail.com  image_D_D

[
{'StaffId': 1, 'Firstname': 'A', 'Lastname': 'A', 'Email': 'A.A@gmail.com', 'Photo': 'image_A_A'}, 
{'StaffId': 2, 'Firstname': 'B', 'Lastname': 'B', 'Email': 'B.B@gmail.com', 'Photo': 'image_B_B'}, 
{'StaffId': 3, 'Firstname': 'C', 'Lastname': 'C', 'Email': 'C.C@gmail.com', 'Photo': 'image_C_C'}, 
{'StaffId': 4, 'Firstname': 'D', 'Lastname': 'D', 'Email': 'D.D@gmail.com', 'Photo': 'image_D_D'}
]