How to parse Json when the key is unknown using Python?

Question

This is a sample of my Json:

text = {"rates":{
   "AT":{
     "country_name":"Austria",
     "standard_rate":20,
     "reduced_rates":{
       "food":10,
       "books":10
     }
  }
}}

Now the "AT" is the country code.. It's not fixed. It can also be GB, IT etc...

I want to parse this Json and get from it columns as follow:

rates.AT   rates.AT.country_name   rates.AT.reducted_rates.food
  AT           Austria                  10

Can be also renamed to:

code        country_name               food
  AT           Austria                  10

Say for example that in another run I have:

text = {"rates":{
   "IT":{
     "country_name":"Italy",
     "standard_rate":20,
     "reduced_rates":{
       "food":13,
       "books":11
     }
  }
}}

Then It needs to be:

rates.IT   rates.IT.country_name   rates.IT.reducted_rates.food
  IT           Italy                     13

Can be also renamed to:

 code        country_name               food
  IT           Italy                     13

How can I do this?

EDIT:

If possible using @GPhilo answer I would prefer to get the data as Pandas dataframe. Something like?

df = pd.DataFrame()
for k,item in dic['rates'].items(): # use iteritems() if you're on Python 2
    line = '{};{};{}'.format(k, item['country_name'], item['reduced_rates']['food'])
    df = df.append(line, ignore_index=True)

This doesn't work because line isn't the proper way to do this.

I understand that the key each time is unknown, but do you have a collections of keys? For example, "IT" for Italy, "US" for USA, "RU" for Russia etc are known to you? — Jacob Fuchs
– Jacob Fuchs, Commented Jan 22, 2019 at 10:24
What do you mean by time? The sample of data is what I get from the Json I just need to convert it to insert to columns in my DB — Luis
– Luis, Commented Jan 22, 2019 at 10:27
@karansthr print (json.loads(text)) gives TypeError: the JSON object must be str, not 'dict' converting to string doesn't work either — Luis
– Luis, Commented Jan 22, 2019 at 10:29

GPhilo · Accepted Answer · 2019-01-22 12:27:49Z

4

Once parsed, json objects are python dicts, so just loop over the key/value pair at the level you need and print the information:

import json

dic = json.loads('''
{"rates":{
   "AT":{
     "country_name":"Austria",
     "standard_rate":20,
     "reduced_rates":{
       "food":10,
       "books":10
     }
  }
}}
''')

for k,item in dic['rates'].items(): # use iteritems() if you're on Python 2
    print('{};{};{}'.format(k, item['country_name'], item['reduced_rates']['food']))

Format the output as needed, the three values you need are the three in the format call in the code above. Running the sample returns:

AT;Austria;10

Edit: Amended the answer

Once parsed, json objects are python dicts: print(dic.__class__) returns <class 'dict'>.

Update

In reply to the edit in the question, instead of appending the formatted string, just append the values:

df = pd.Dataframe(columns=['code', 'country_name', 'food'])
[...]
df = df.append([k, item['country_name'], item['reduced_rates']['food']], ignore_index=True)

edited Jan 22, 2019 at 12:27

answered Jan 22, 2019 at 10:28

GPhilo

19.3k9 gold badges70 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Luis Over a year ago

Is it possible to do json.dumps instead of print?

GPhilo Over a year ago

The print is just to show the three values you need to use. You can use k, item['country_name'], item['reduced_rates']['food'] in any way you want, possibly manipulating them or formatting in a different way. If you update your answer with an example of how you want the output to be I can update the answer

Luis Over a year ago

TypeError: append() got an unexpected keyword argument 'columns'

GPhilo Over a year ago

sorry, the columns went in the Dataframe definition

Luis Over a year ago

I tried that it doesn't preduce the correct result all the code, country_name and food columns contains NaN and there is a 0 column with all the values

|

Shirkan · Accepted Answer · 2019-01-22 10:29:58Z

3

you should use dict.keys() to get a list of all keys of a dictionary, and then you can iterate it and do whatever you need with it. For example:

for k in text.keys():
   #do something with text[k] or k itself

consider also use dict.items() to get pairs of key, value:

for k, v in text.items():
    #do something with k and v, where text[k] is v

that's good for python 3, in python 2 you should use dict.iteritems()

answered Jan 22, 2019 at 10:29

Shirkan

1,0171 gold badge10 silver badges14 bronze badges

Collectives™ on Stack Overflow

How to parse Json when the key is unknown using Python?

2 Answers 2

Edit: Amended the answer

Update

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Edit: Amended the answer

Update

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related