0

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:

enter image description here

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
  • 317,841
  • 20
  • 164
  • 234
Clauric
  • 1,826
  • 8
  • 29
  • 48

1 Answers1

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
BENY
  • 317,841
  • 20
  • 164
  • 234
  • 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. – Clauric Jan 18 '22 at 16:57
  • @Clauric not recommend that ~ :-) , if you do like , you can check pivot https://stackoverflow.com/questions/47152691/how-can-i-pivot-a-dataframe, Q&A10 – BENY Jan 18 '22 at 17:06