Issue
I have been struggling to sort some data I am getting from an API. I am still fairly new to python and attempting to do this in pandas.
(The data I am getting is from here: https://www.jao.eu/page-api/market-data).
I have tried to normalize the data for the results column, which seems to be a nested dictionary and the result does not quite look like how it should.
My current code looks like:
import pandas as pd
import requests
import json
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)
r
This yeilds a long dictionary like output which I suspect is correct. When I try to put this into a dataframe, I get the below:
Output table as an example in excel
The problem is I am trying to also get the data for what is in column V titled 'results', which is in a list as it represents several hours as individual data points for one day. Essentially each row of the output table has several nested rows of data in results column I need to get in my data frame.
The nested dictionary looks like:
[{'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4301.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B05-------', 'productHour': '04:00-05:00', 'auctionPrice': 10.24, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 767.0, 'requestedCapacity': 4221.0, 'allocatedCapacity': 767.0, 'productIdentification': 'B13-------', 'productHour': '12:00-13:00', 'auctionPrice': 3.04, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4146.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B14-------', 'productHour': '13:00-14:00', 'auctionPrice': 5.53, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4146.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B17-------', 'productHour': '16:00-17:00', 'auctionPrice': 3.87, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4294.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B18-------', 'productHour': '17:00-18:00', 'auctionPrice': 20.94, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4216.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B22-------', 'productHour': '21:00-22:00', 'auctionPrice': 4.95, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4129.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B19-------', 'productHour': '18:00-19:00', 'auctionPrice': 53.16, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4226.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B06-------', 'productHour': '05:00-06:00', 'auctionPrice': 8.55, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 1094.0, 'requestedCapacity': 4779.0, 'allocatedCapacity': 1094.0, 'productIdentification': 'B09-------', 'productHour': '08:00-09:00', 'auctionPrice': 1.12, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4679.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B03-------', 'productHour': '02:00-03:00', 'auctionPrice': 17.24, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4126.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B07-------', 'productHour': '06:00-07:00', 'auctionPrice': 3.94, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 1094.0, 'requestedCapacity': 4803.0, 'allocatedCapacity': 1094.0, 'productIdentification': 'B10-------', 'productHour': '09:00-10:00', 'auctionPrice': 1.2, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 767.0, 'requestedCapacity': 4241.0, 'allocatedCapacity': 767.0, 'productIdentification': 'B12-------', 'productHour': '11:00-12:00', 'auctionPrice': 3.1, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4629.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B02-------', 'productHour': '01:00-02:00', 'auctionPrice': 14.73, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4679.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B04-------', 'productHour': '03:00-04:00', 'auctionPrice': 15.14, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4216.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B23-------', 'productHour': '22:00-23:00', 'auctionPrice': 5.63, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4629.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B01-------', 'productHour': '00:00-01:00', 'auctionPrice': 12.1, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4279.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B20-------', 'productHour': '19:00-20:00', 'auctionPrice': 28.33, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4306.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B21-------', 'productHour': '20:00-21:00', 'auctionPrice': 11.55, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4222.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B08-------', 'productHour': '07:00-08:00', 'auctionPrice': 1.81, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4166.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B11-------', 'productHour': '10:00-11:00', 'auctionPrice': 2.25, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4146.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B15-------', 'productHour': '14:00-15:00', 'auctionPrice': 7.46, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4246.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B24-------', 'productHour': '23:00-24:00', 'auctionPrice': 5.0, 'additionalMessage': None}, {'comment': None, 'corridorCode': 'IF1-FR-GB', 'offeredCapacity': 667.0, 'requestedCapacity': 4146.0, 'allocatedCapacity': 667.0, 'productIdentification': 'B16-------', 'productHour': '15:00-16:00', 'auctionPrice': 5.89, 'additionalMessage': None}]
I tried to normalize this and then concat and append to the original df but I think either my indexing is wrong of I am not doing it correctly but I do not seem to get the desired result.
I would appreciate any help from the community!
Solution
use:
df=df.explode('results')
df=df.join(pd.json_normalize(df.pop('results')).add_suffix('_new'))
Answered By - Clegane
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.