Split nested json into correct rows using Pandas

Question

I have the following json file being received from an API:

{'get': 'fixtures/statistics', 'parameters': {'fixture': '65'}, 'errors': [], 'results': 2, 'paging': {'current': 1, 'total': 1}, 'response': [{'team': {'id': 33, 'name': 'Manchester United'} 'statistics': [{'type': 'Shots on Goal', 'value': 6}, {'type': 'Shots off Goal', 'value': 1}, {'type': 'Total Shots', 'value': 8}, {'type': 'Blocked Shots', 'value': 1}]}, {'team': {'id': 46, 'name': 'Leicester'} 'statistics': [{'type': 'Shots on Goal', 'value': 4}, {'type': 'Shots off Goal', 'value': 3}, {'type': 'Total Shots', 'value': 13}, {'type': 'Blocked Shots', 'value': 6}]}]}

I am trying to get the data from the statistics section into the same rows as the team information.

When I run:

results_df = pd.json_normalize(results_json, record_path=["response"])

I get:

However, when I run

results_data = pd.json_normalize(results_json, record_path = ["response", "statistics"])

I get:

|   | type           | value |
|---|----------------|-------|
| 0 | Shots on Goal  | 6     |
| 1 | Shots off Goal | 1     |
| 2 | Total Shots    | 8     |
| 3 | Blocked Shots  | 1     |
| 4 | Shots on Goal  | 4     |
| 5 | Shots off Goal | 3     |
| 6 | Total Shots    | 13    |
| 7 | Blocked Shots  | 6     |

In the above, rows 0 - 3 would correspond with team.id = 33, while rows 4 - 7 correspond with team.id - 46.

Is there any way to get the data from the statistics section of the json into the correct row for each response?

BENY · Accepted Answer · 2022-01-18 16:40:39Z

1

We can modify your current output with explode

s = pd.json_normalize(result_json, record_path=["response"]).explode('statistics').reset_index(drop=True)
s = s.join(pd.DataFrame(s.pop('statistics').tolist()))
s
Out[112]: 
   team.id          team.name            type  value
0       33  Manchester United   Shots on Goal      6
1       33  Manchester United  Shots off Goal      1
2       33  Manchester United     Total Shots      8
3       33  Manchester United   Blocked Shots      1
4       46          Leicester   Shots on Goal      4
5       46          Leicester  Shots off Goal      3
6       46          Leicester     Total Shots     13
7       46          Leicester   Blocked Shots      6

answered Jan 18, 2022 at 16:40

BENY

324k22 gold badges176 silver badges250 bronze badges

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

2 Comments

Clauric Over a year ago

Thank you for that. Is it possible to get all the statistics on the same row as the team.id, so that there would only be 2 rows of data returned, 1 for each team.

BENY Over a year ago

@Clauric not recommend that ~ :-) , if you do like , you can check pivot stackoverflow.com/questions/47152691/…, Q&A10

Collectives™ on Stack Overflow

Split nested json into correct rows using Pandas

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related