Nested Dictionaries/Arrays - Display specific columns within part of pandas dataframe-CodePudding

I have the following data set: data set

Here is the Documentation on the Polygon http request: https://polygon.io/docs/options/get_v3_snapshot_options__underlyingasset It is sending back dictionaries of dictionaries which is the source of my issue.

    break_even_price    day details greeks  last_quote  open_interest   underlying_asset
0   30  {'change': 0, 'change_percent': 0, 'close': 96...   {'contract_type': 'call', 'exercise_style': 'a...   {}  {'ask': 0, 'ask_size': 0, 'bid': 0, 'bid_size'...   2799    {'change_to_break_even': -100, 'last_updated':...
1   35  {'change': 0, 'change_percent': 0, 'close': 94...   {'contract_type': 'call', 'exercise_style': 'a...   {}  {'ask': 0, 'ask_size': 0, 'bid': 0, 'bid_size'...   642 {'change_to_break_even': -95.2, 'last_updated'...
2   40  {'change': 0, 'change_percent': 0, 'close': 89...   {'contract_type': 'call', 'exercise_style': 'a...   {}  {'ask': 0, 'ask_size': 0, 'bid': 0, 'bid_size'...   1007    {'change_to_break_even': -90.2, 'last_updated'...
3   50  {'change': 0, 'change_percent': 0, 'close': 79...   {'contract_type': 'call', 'exercise_style': 'a...   {}  {'ask': 0, 'ask_size': 0, 'bid': 0, 'bid_size'...   3915    {'change_to_break_even': -80.2, 'last_updated'...
4   55  {'change': 0, 'change_percent': 0, 'close': 75...   {'contract_type': 'call', 'exercise_style': 'a...   {}  {'ask': 0, 'ask_size': 0, 'bid': 0, 'bid_size'...   814 {'change_to_break_even': -75.2, 'last_updated'...
5   60  {'change': 0, 'change_percent': 0, 'close': 69...   {'contract_type': 'call', 'exercise_style': 'a...   {}  {'ask': 0, 'ask_size': 0, 'bid': 0, 'bid_size'...   2994    {'change_to_break_even': -70.2, 'last_updated'...
6   65  {'change': 0, 'change_percent': 0, 'close': 64...   {'contract_type': 'call', 'exercise_style': 'a...   {}  {'ask': 0, 'ask_size': 0, 'bid': 0, 'bid_size'...   1456    {'change_to_break_even': -65.2, 'last_updated'...
7   70  {'change': 0, 'change_percent': 0, 'close': 58...   {'contract_type': 'call', 'exercise_style': 'a...   {}  {'ask': 0, 'ask_size': 0, 'bid': 0, 'bid_size'...   3414    {'change_to_break_even': -60.2, 'last_updated'...
8   75  {'change': 0, 'change_percent': 0, 'close': 55...   {'contract_type': 'call', 'exercise_style': 'a...   {}  {'ask': 0, 'ask_size': 0, 'bid': 0, 'bid_size'...   1981    {'change_to_break_even': -55.2, 'last_updated'...
9   80  {'change': 0, 'change_percent': 0, 'close': 49...   {'contract_type': 'call', 'exercise_style': 'a...   {}  {'ask': 0, 'ask_size': 0, 'bid': 0, 'bid_size'...   4676    {'change_to_break_even': -50.2, 'last_updated'...

Underlined in red on the picture (or text data set above) is the data I would like to create a dataframe on; I would like to have a table with 'close' as column and then n rows with the close values from the 'day' dictionary.

I'm using jupyter notebook and can't find a way to formulate properly the code in python; any ideas?

so far here is my code:

import requests, pandas as pd
optionchain_url = 'https://api.polygon.io/v3/snapshot/options/AAPL?expiration_date=2023-01-20&apiKey=9z9LDShn----Q9sQSj'
optionchain = requests.get(optionchain_url).json()
df = pd.DataFrame(optionchain['results'])
closedf = pd.DataFrame(df, columns='close')

Last request throws an error:

TypeError: Index(...) must be called with a collection of some kind, 'close' was passed

In theory, I would like to do the following:

df = pd.DataFrame(optionchain['results']['day'], columns='close')

But ofcourse that is not the right syntax ^^

(replace apikey with your own if you want to pull http request)

Many Thanks!

CodePudding user response：

You can first parse the column day in df to get the value of the key close:

df = pd.DataFrame(optionchain['results'])
df['close'] = [i['close'] for i in df['day']]

Then create a new dataframe using this column:

closedf = df[['close']]