Python Forum
extract tabe from nested dictionary, find generic approach,clever way - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: extract tabe from nested dictionary, find generic approach,clever way (/thread-29290.html)

extract tabe from nested dictionary, find generic approach,clever way - tonycat - Aug-27-2020

Hi, I extract information from dictionary, step by steps, in stupid way...

can anyone provide clever way, or generic way to solve the problem. or flatten the dictionary. I need to extract information and display in pandas dataframe


# data in dictionary , converted from json, get from api    
# code in juypter notebook format

cat = {'header': {'success': True, 'err_code': '0000', 'err_msg': 'No error found'},
 'result': {'datasize': 100,
  'records': [{'end_of_month': '2020-07',
    'ir_overnight': 0.05792,
    'ir_1w': 0.12714,
    'ir_1m': 0.25393,
    'ir_3m': 0.45119,
    'ir_6m': 0.6556,
    'ir_9m': None,
    'ir_12m': 0.96696},
   {'end_of_month': '2020-06',
    'ir_overnight': 0.18089,
    'ir_1w': 0.29268,
    'ir_1m': 0.44196,
    'ir_3m': 0.77786,
    'ir_6m': 1.02411,
    'ir_9m': None,
    'ir_12m': 1.31643},
  {'end_of_month': '2012-04',
    'ir_overnight': 0.09929,
    'ir_1w': 0.11,
    'ir_1m': 0.30321,
    'ir_3m': 0.40321,
    'ir_6m': 0.56179,
    'ir_9m': 0.61179,
    'ir_12m': 0.86179}]}}

# extract information from dictionary 
cream = cat.get("result")

# extract information from extracted dictionary  
cream_bb = cream.get("records")

# convert final dictionary to dataframe

cream_bb_df =  pd.DataFrame(cream_bb)
# display dataframe
cream_bb_df
The final display Big Grin Big Grin

end_of_month ir_overnight ir_1w ir_1m ir_3m ir_6m ir_9m ir_12m
0 2020-07 0.05792 0.12714 0.25393 0.45119 0.65560 NaN 0.96696
1 2020-06 0.18089 0.29268 0.44196 0.77786 1.02411 NaN 1.31643
2 2012-04 0.09929 0.11000 0.30321 0.40321 0.56179 0.61179 0.86179

RE: extract tabe from nested dictionary, find generic approach,clever way - Larz60+ - Aug-27-2020

create dictionary directly from json.

what is the api name, and what's being extracted

or as an alternative, post raw unaltered json data

RE: extract tabe from nested dictionary, find generic approach,clever way - tonycat - Aug-31-2020

Thank, I just focus on the json, and find solution 


cat1 = pd.DataFrame(dict([(k,pd.Series(v)) for k,v in cat['result'].items()]))

pd.json_normalize(json.loads(cat1.to_json(orient="records")))
take care nested dictionary is not easy for non-IT guy..…………..


Big Grin Big Grin

RE: extract tabe from nested dictionary, find generic approach,clever way - DeaD_EyE - Aug-31-2020

Without Pandas, it makes more fun.

from operator import itemgetter

# your cat definition

# for easier access
records = cat["result"]["records"]

# itemgetter which takes the column names
# this will guarantee that all following elements
# have the same column-order
columns = itemgetter(*records[0])


results = [columns(items) for items in records]


RE: extract tabe from nested dictionary, find generic approach,clever way - tonycat - Aug-31-2020

Thanks, I tried your code, very fast and robust.

And It is very easy to modify, or change to more generic, thank a lot!

Clap Clap Clap