Unable to extract nested JSON and put into Pandas Data Frame

Question

I have a JSON file and am trying to extract a list of all teams. I can do it with one iteration, but when it's nested at more than one level, I am unable to do anything. In this scenario, I am trying to extract players into a data frame and write that to json. Code is below. Starting with JSON. Thanks!

{
"teams": [{
    "coach": "Cowher",
    "players": [{
        "player": "Simms",
        "number": 11
    }, {
        "player": "Bradshaw",
        "number": 12

    }, {
        "player": "Elway",
        "number": 7

    }]
}]
}

Here is my Python script.

response = urllib.urlopen(url)
data = json.loads(response.read())
df = items_data = pd.DataFrame**(data['teams']['players'])**

I know in the last line is where the error is and that this is a Series. How do I get all players. Once I get this I can write this to a csv. Any help will be greatly appreciated!

Wombatz · Accepted Answer · 2015-12-29 00:13:29Z

1

There is no real question here, but i assume you want to know why the error occurs.

data["teams"] is a list as you can see in line 2 of your input JSON (opening square bracket). So some_list["players"] does not make sense and raises TypeError: list indices must be integers or slices, not str because a list can only be indexed by integers.

You can either get all players of a specific team: data["teams"][n]["players"] where n denotes the n-th team.

Or you can get all players of all teams: [teams["players"] for team in data["teams"]]

EDIT:

If you want to get rid of the nesting you can use: [player for team in data["teams"] for player in team["players"]]

note: using a generator expression might be faster, depending on how pandas works.

edited Dec 29, 2015 at 0:13

answered Dec 28, 2015 at 23:34

Wombatz

5,4851 gold badge29 silver badges39 bronze badges

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

2 Comments

Kulwant Over a year ago

My question is how do you I get all players, so that I can write a list of players to a data-frame and eventually a CSV. I have no problem when it is nested only at one level, but cannot get at when it is nested two levels like the current example.

Kulwant Over a year ago

Thanks. Extremely helpful!

Collectives™ on Stack Overflow

Unable to extract nested JSON and put into Pandas Data Frame

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