Trying to extract data from JSON URL into Pandas

Question

I am trying to extract data from a JSON URL into pandas but this file has multiple "layers" of lists and dictionaries which i just cannot seem to navigate.

import json
from urllib.request import urlopen

with urlopen('https://statdata.pgatour.com/r/010/2020/player_stats.json') as response:
    source = response.read()

data = json.loads(source)

for item in data['tournament']['players']:
    pid = item['pid']
    statId = item['stats']['statId']
    name = item['stats']['name']
    tValue = item['stats']['tValue']
    print(pid, statId, name, tValue)

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-84-eadd8bdb34cb> in <module>
      1 for item in data['tournament']['players']:
      2     player_id = item['pid']
----> 3     stat_id = item['stats']['statId']
      4     stat_name = item['stats']['name']
      5     stat_value = item['stats']['tValue']

TypeError: list indices must be integers or slices, not str

The output i am trying to get to is like :-

pink spikyhairman · Accepted Answer · 2020-05-05 14:25:30Z

1

As the previous answer suggests, stats is a list of stat items. This will show you what happens, and aslo catch any other problems:

import json
from urllib.request import urlopen

with urlopen('https://statdata.pgatour.com/r/010/2020/player_stats.json') as response:
    source = response.read()

data = json.loads(source)

for item in data['tournament']['players']:
    try:
        pid = item['pid']
        stats = item['stats']
        for stat in stats:
            statId = stat['statId']
            name = stat['name']
            tValue = stat['tValue']
            print(pid, statId, name, tValue)
     except Exception as e:
        print(e)
        print(item)
        break

answered May 5, 2020 at 14:25

pink spikyhairman

2,5751 gold badge19 silver badges13 bronze badges

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

3 Comments

rug Over a year ago

Thank you pink spikyhairman - one extra question - how could i extract the "tournamentNumber":"010" and add it in first column -- print(tournamentNumber, pid, statId, name, tValue) ?

pink spikyhairman Over a year ago

There is only one tournament in the data so print (data['tournament']['tournamentNumber']) before the for loop

rug Over a year ago

thanks both answers grab the data correctly, how do i get the data into a dataframe ?

estherwn · Accepted Answer · 2020-05-05 14:20:18Z

1

You are missing a layer.

To simplify the data, we are trying to access:

"stats": [{
    "statId":"106",
    "name":"Eagles",
    "tValue":"0",
}]

The data of 'stats' starts with [{. This is a dictionary within an array.

I think this should work:

for item in data['tournament']['players']:
    pid = item['pid']
    for stat in item['stats']:
        statId = stat['statId']
        name = stat['name']
        tValue = stat['tValue']
        print(pid, statId, name, tValue)

To read more on dictionaries: https://realpython.com/iterate-through-dictionary-python/

answered May 5, 2020 at 14:20

estherwn

1866 bronze badges

Collectives™ on Stack Overflow

Trying to extract data from JSON URL into Pandas

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related