Extracting data with Python from a json with different levels of nested lists and dicitonaries

Question

I have the following API to extract data from: https://www.business-humanrights.org/en/api/internal/explore/?format=json&search=nike

I have extracted the API results using json (see below), but the structure of the API seems to be quite convoluted and I do not understand how to extract the information relevant for me and store it into a pandas dataframe. The information I am interested in are the values of the following keys

"translated_title" "backdate" "translated_abstract" "translated_url"

r = requests.get("https://www.business-humanrights.org/en/api/internal/explore/?format=json&search=nike")
    rjson = r.json()
    users_locs = [webPage for webPage in rjson['results']]
    users_locs

More generally, it would be great if I could be pointed out to the logic of how to extract data from lists in a dictionary in a list in a dictionary, etc. etc.

My expected output is a dataset at a news level where for each row I report the translated title, the translated abstract and the backdate. See the following sturcture:

df = pd.DataFrame([{"translated_title" : "Chine : La pression augmente contre Nike, Apple et d’autres à mesure que le boycott lié aux allégations de travail forcé s’intensifie", "translated_abstract":'..', "backdate": "2020-07-24"}, 
{"translated_title" : "..",  "translated_abstract":'..', "backdate": ".."}])

Thanks!

Hi sushanth, I added the expected outcome, thanks for your request of clarification — Filippo Sebastio
– Filippo Sebastio, Commented Aug 27, 2020 at 4:46

deadshot · Accepted Answer · 2020-08-27 04:48:41Z

2

Try this

keys = ["translated_title", "backdate", "translated_abstract", "translated_url"]

res = [{k: x[k] for k in keys if k in x} for x in users_locs]
df = pd.DataFrame(res)

answered Aug 27, 2020 at 4:48

deadshot

9,0774 gold badges23 silver badges40 bronze badges

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

2 Comments

deadshot Over a year ago

@FilippoSebastio for your info translated_abstract has empty strings for all the records

Filippo Sebastio Over a year ago

it may be just for this api request, thanks for the solution :)

Collectives™ on Stack Overflow

Extracting data with Python from a json with different levels of nested lists and dicitonaries

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related