[FIXED] How to loop through an API request for different parameters such as changing the month and header

Issue

I have the following API request that I then clean and sort the data:

    Base_URL = "https://api.jao.eu/OWSMP/getauctions?"

headers = {
    "AUTH_API_KEY": "06e690fb-697b-4ab2-9325-4268cbd14502"
}

params = {
    "horizon":"Daily",
    "corridor":"IF1-FR-GB",
    "fromdate":"2021-01-01"
}

data = "results"

r = requests.get(Base_URL, headers=headers, params=params, json=data)
j = r.json()
df = pd.DataFrame.from_dict(j)
df=df.explode('results')
    df=df.join(pd.json_normalize(df.pop('results')).add_suffix('_new'))
    df.drop(['ftroption','identification','horizonName','periodToBeSecuredStart','periodToBeSecuredStop','bidGateOpening','bidGateClosure','isBidGateOpen','atcGateOpening','atcGateClosure','marketPeriodStop','disputeSubmissionGateOpening','disputeSubmissionGateClosure','disputeProcessGateOpening','disputeProcessGateClosure','ltResaleGateOpening','ltResaleGateClosure','maintenances','xnRule','winningParties','operationalMessage','products','lastDataUpdate','cancelled','comment_new','corridorCode_new','productIdentification_new','additionalMessage_new'], axis=1, inplace=True)
    df

I then sort it by the date column, which is why it is important to be able to run it for every month, as I need to repeat this process and hopefully automate it in the future:

df['new'] = pd.to_datetime(df['marketPeriodStart']).dt.strftime('%d/%m/%Y')
df = df = df.sort_values(by='new', ascending=True)
df

As the API can only run in one month periods, I am trying to loop-through it to be able to change the "fromdate" param to every month. I can then change the "corridor" param and I would be able to repeat the above for-loop. Thank you!

Solution

Get all data:

import pandas as pd
import requests
Base_URL = "https://api.jao.eu/OWSMP/getauctions?"

headers = {
"AUTH_API_KEY": "api_key"
}

final_df=pd.DataFrame() #all data will store here.

#create dates like 2022-01-01, 2022-08-01...
year=['2021','2022']
month=list(range(1,13))
dates=[]
errors=[]
for i in year:
    for j in month:
        if i =='2022' and j in [11,12]:
            pass
        else:
            dates.append(i+ '-' + f'{j:02}' + '-01')

#dates are ready. let's request for each date and append data to final df. 
for i in dates:    
    params = {
    "horizon":"Daily",
    "corridor":"IF1-FR-GB",
    "fromdate":i
    }
    
    data = "results"
    
    
    r = requests.get(Base_URL, headers=headers, params=params, json=data)
    j = r.json()
    try:
       df = pd.DataFrame.from_dict(j)
       final_df=final_df.append(df)
    except:
       errors.append(j)

#now, let's do same process for final data.
final_df=final_df.explode('results')
final_df=final_df.join(pd.json_normalize(final_df.pop('results')).add_suffix('_new'))
final_df.drop(['ftroption','identification','horizonName','periodToBeSecuredStart','periodToBeSecuredStop','bidGateOpening','bidGateClosure','isBidGateOpen','atcGateOpening','atcGateClosure','marketPeriodStop','disputeSubmissionGateOpening','disputeSubmissionGateClosure','disputeProcessGateOpening','disputeProcessGateClosure','ltResaleGateOpening','ltResaleGateClosure','maintenances','xnRule','winningParties','operationalMessage','products','lastDataUpdate','cancelled','comment_new','corridorCode_new','productIdentification_new','additionalMessage_new'], axis=1, inplace=True)

After you get all the data, if you want to get it automatically every month, you should set it to run on the first day of every month (if you want a different day, you should change the day value in timedelta).

import pandas as pd
import requests
Base_URL = "https://api.jao.eu/OWSMP/getauctions?"

headers = {
"AUTH_API_KEY": "api_key"
}


from datetime import datetime,timedelta
now=(datetime.today() - timedelta(days=2)).strftime('%Y-%m-01')
params = {
"horizon":"Daily",
"corridor":"IF1-FR-GB",
"fromdate":now
}

data = "results"


r = requests.get(Base_URL, headers=headers, params=params, json=data)
j = r.json()
df = pd.DataFrame.from_dict(j)
df=df.append(df)
df=df.explode('results')
df=df.join(pd.json_normalize(df.pop('results')).add_suffix('_new'))
df.drop(['ftroption','identification','horizonName','periodToBeSecuredStart','periodToBeSecuredStop','bidGateOpening','bidGateClosure','isBidGateOpen','atcGateOpening','atcGateClosure','marketPeriodStop','disputeSubmissionGateOpening','disputeSubmissionGateClosure','disputeProcessGateOpening','disputeProcessGateClosure','ltResaleGateOpening','ltResaleGateClosure','maintenances','xnRule','winningParties','operationalMessage','products','lastDataUpdate','cancelled','comment_new','corridorCode_new','productIdentification_new','additionalMessage_new'], axis=1, inplace=True)

Answered By - Clegane