Python Forum
extract tabe from nested dictionary, find generic approach,clever way
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
extract tabe from nested dictionary, find generic approach,clever way
#1
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
Reply
#2
create dictionary directly from json.

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

or as an alternative, post raw unaltered json data
Reply
#3
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
Reply
#4
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]
Almost dead, but too lazy to die: https://sourceserver.info
All humans together. We don't need politicians!
Reply
#5
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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Thoughts on how to approach a project birdieman 13 13,820 Feb-28-2023, 11:42 AM
Last Post: get2sid
  What is the best approach to training a final model after cross validation? amjass12 0 1,848 Jul-21-2021, 10:15 AM
Last Post: amjass12
  Dynamic updating of a nested dictionary in multiprocessing pool Mil_K 1 14,564 Oct-16-2018, 09:15 AM
Last Post: MaklaTakan
  Alternative approach to iterate numerous linear regressions with xlsx data? john_538 0 2,468 Apr-07-2018, 10:15 PM
Last Post: john_538

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020