Create merged df based on the url list [pandas]

Question

I was able to extract the data from url_query url, but additionally, I would like to get the data from the urls_list created based on the query['ids'] column from dataframe. Please see below the current logic:

    url = 'https://instancename.some-platform.com/api/now/table/data?display_value=true&'
    team = 'query=group_name=123456789'
    url_query = url+team

    dataframe: query
         [ids]
    0    aaabbb1cccdddeee4ffggghhhhh5iijj
    1    aa1bbb2cccdddeee5ffggghhhhh6iijj

    issue_list = []
    for issue in query['ids']:
        issue_list.append(f'https://instancename.some-platform.com/api/now/table/data?display_value=true&?display_value=true&query=group_name&sys_id={issue}')

    response = requests.get(url_query, headers=headers,auth=auth, proxies=proxies)

data = response.json()

def api_response(k):
   dct = dict(
   event_id= k['number'],
   created_time = k[‘created’],
   status = k[‘status’],
   created_by = k[‘raised_by’],
   short_desc = k[‘short_description’],
   group = k[‘team’]
    )
    return dct

raw_data = []
for p in data['result']:
    rec = api_response(k)
    raw_data.append(rec)

df = pd.DataFrame.from_records(raw_data)

df:

The url_query response extracts what I need, but the key is that I would like to add to the existing one 'df' add the data from the issue_list = []. I don't know how to put the issue_list = [] to the response. I've tried to add issue_list to the response = requests.get(issue_list, headers=headers,auth=auth, proxies=proxies) statement, but I've got invalid schema error.

Answer 1

You can create list of DataFrames with query q instead url_query and last join together by concat:

dfs = []
for issue in query['ids']:
    q = f'https://instancename.some-platform.com/api/now/table/data?display_value=true&?display_value=true&query=group_name&sys_id={issue}'

    response = requests.get(q, headers=headers,auth=auth, proxies=proxies)

    data = response.json()
    raw_data = [api_response(k) for p in data['result']]
    df = pd.DataFrame.from_records(raw_data)
    dfs.append(df)
    
df = pd.concat(dfs, ignore_index=True)