organise .json data to pandas dataframe/excel-CodePudding

I had some progression in my webscraping stats from https://liiga.fi/tilastot/joukkueet.

Now i have .json file with is total mess. How i can organise data to panda dataframe or excel, that it would look like normal stats table?

>    {'id': 1, 'season': 2022, 'start': '2021-09-09T15:30:00Z',
> 'homeTeam': {'teamId': '624554857:lukko', 'teamPlaceholder': None,
> 'goals': 3, 'timeOut': None, 'powerplayInstances': 1,
> 'powerplayGoals': 0, 'shortHandedInstances': 5, 'shortHandedGoals':
> 0}, 'awayTeam': {'teamId': '651304385:tps', 'teamPlaceholder': None,
> 'goals': 5, 'timeOut': None, 'powerplayInstances': 5,
> 'powerplayGoals': 2, 'shortHandedInstances': 1, 'shortHandedGoals':
> 0}, 'finishedType': 'ENDED_DURING_REGULAR_GAME_TIME', 'started': True,
> 'ended': True, 'gameTime': 3600, 'cacheUpdateDate':
> '2021-11-09T15:52:49.446332Z'}, {'id': 5, 'season': 2022, 'start':
> '2021-09-10T15:30:00Z', 'homeTeam': {'teamId': '933686567:saipa',
> 'teamPlaceholder': None, 'goals': 1, 'timeOut': None,
> 'powerplayInstances': 5, 'powerplayGoals': 1, 'shortHandedInstances':
> 5, 'shortHandedGoals': 0}, 'awayTeam': {'teamId': '461765763:kookoo',
> 'teamPlaceholder': None, 'goals': 0, 'timeOut': None,
> 'powerplayInstances': 5, 'powerplayGoals': 0, 'shortHandedInstances':
> 5, 'shortHandedGoals': 0}, 'finishedType':
> 'ENDED_DURING_REGULAR_GAME_TIME', 'started': True, 'ended': True,
> 'gameTime': 3600, 'cacheUpdateDate': '2021-11-09T16:16:08.429208Z'},
> {'id': 4, 'season': 2022, 'start': '2021-09-10T15:30:00Z', 'homeTeam':
> {'teamId': '219244634:jyp', 'teamPlaceholder': None, 'goals': 2,
> 'timeOut': None, 'powerplayInstances': 2, 'powerplayGoals': 0,
> 'shortHandedInstances': 2, 'shortHandedGoals': 0}, 'awayTeam':
> {'teamId': '168761288:hifk', 'teamPlaceholder': None, 'goals': 1,
> 'timeOut': None, 'powerplayInstances': 2, 'powerplayGoals': 0,
> 'shortHandedInstances': 2, 'shortHandedGoals': 0}, 'finishedType':
> 'ENDED_DURING_EXTENDED_GAME_TIME', 'started': True, 'ended': True,
> 'gameTime': 3651, 'cacheUpdateDate': '2021-11-09T15:30:26.273845Z'},
> {'id': 3, 'season': 2022, 'start': '2021-09-10T15:30:00Z', 'homeTeam':
> {'teamId': '292293444:jukurit', 'teamPlaceholder': None, 'goals': 2,
> 'timeOut': None, 'powerplayInstances': 2, 'powerplayGoals': 0,
> 'shortHandedInstances': 4, 'shortHandedGoals': 0}, 'awayTeam':
> {'teamId': '859884935:kalpa', 'teamPlaceholder': None, 'goals': 1,
> 'timeOut': None, 'powerplayInstances': 4, 'powerplayGoals': 0,
> 'shortHandedInstances': 2, 'shortHandedGoals': 0}, 'finishedType':
> 'ENDED_DURING_WINNING_SHOT_COMPETITION', 'started': True, 'ended':
> True, 'gameTime': 3900, 'cacheUpdateDate':
> '2021-11-09T15:55:43.977639Z'}, {'id': 2, 'season': 2022, 'start':
> '2021-09-10T15:30:00Z', 'homeTeam': {'teamId': '55786244:hpk',
> 'teamPlaceholder': None, 'goals': 1, 'timeOut': None,
> 'powerplayInstances': 3, 'powerplayGoals': 1, 'shortHandedInstances':
> 4, 'shortHandedGoals': 0}, 'awayTeam': {'teamId': '362185137:tappara',
> 'teamPlaceholder': None, 'goals': 3, 'timeOut': None,
> 'powerplayInstances': 4, 'powerplayGoals': 0, 'shortHandedInstances':
> 3, 'shortHandedGoals': 1}, 'finishedType':
> 'ENDED_DURING_REGULAR_GAME_TIME', 'started': True, 'ended': True,
> 'gameTime': 3600, 'cacheUpdateDate': '2021-11-09T16:03:08.907945Z'},
> {'id': 6, 'season': 2022, 'start': '2021-09-10T15:30:00Z', 'homeTeam':
> {'teamId': '679171680:ässät', 'teamPlaceholder': None, 'goals': 4,
> 'timeOut': None, 'powerplayInstances': 5, 'powerplayGoals': 3,
> 'shortHandedInstances': 2, 'shortHandedGoals': 0}, 'awayTeam':
> {'teamId': '626537494:sport', 'teamPlaceholder': None, 'goals': 1,
> 'timeOut': None, 'powerplayInstances': 2, 'powerplayGoals': 0,
> 'shortHandedInstances': 5, 'shortHandedGoals': 0}, 'finishedType':
> 'ENDED_DURING_REGULAR_GAME_TIME', 'started': True, 'ended': True,
> 'gameTime': 3600, 'cacheUpdateDate': '2021-11-09T15:48:03.008279Z'},
> {'id': 7, 'season': 2022, 'start': '2021-09-11T13:00:00Z', 'homeTeam':
> {'teamId': '495643563:kärpät', 'teamPlaceholder': None, 'goals': 6,
> 'timeOut': None, 'powerplayInstances': 6, 'powerplayGoals': 1,
> 'shortHandedInstances': 3, 'shortHandedGoals': 0}, 'awayTeam':
> {'teamId': '219244634:jyp', 'teamPlaceholder': None, 'goals': 2,
> 'timeOut': None, 'powerplayInstances': 3, 'powerplayGoals': 0,
> 'shortHandedInstances': 6, 'shortHandedGoals': 0}, 'finishedType':
> 'ENDED_DURING_REGULAR_GAME_TIME', 'started': True, 'ended': True,
> 'gameTime': 3600, 'cacheUpdateDate': '2021-11-09T16:14:33.224637Z'},
> {'id': 11, 'season': 2022, 'start': '2021-09-11T14:00:00Z',
> 'homeTeam': {'teamId': '651304385:tps', 'teamPlaceholder': None,
> 'goals': 2, 'timeOut': None, 'powerplayInstances': 1,
> 'powerplayGoals': 0, 'shortHandedInstances': 3, 'shortHandedGoals':
> 0}, 'awayTeam': {'teamId': '951626834:ilves', 'teamPlaceholder': None,
> 'goals': 3, 'timeOut': None, 'powerplayInstances': 3,
> 'powerplayGoals': 1, 'shortHandedInstances': 1, 'shortHandedGoals':
> 0}, 'finishedType': 'ENDED_DURING_REGULAR_GAME_TIME', 'started': True,
> 'ended': True, 'gameTime': 3600, 'cacheUpdateDate':
> '2021-11-09T16:10:12.498916Z'}, {'id': 10, 'season': 2022, 'start':
> '2021-09-11T14:00:00Z', 'homeTeam': {'teamId': '875886777:pelicans',
> 'teamPlaceholder': None, 'goals': 3, 'timeOut': None,
> 'powerplayInstances': 3, 'powerplayGoals': 1, 'shortHandedInstances':
> 5, 'shortHandedGoals': 0}, 'awayTeam': {'teamId': '168761288:hifk',
> 'teamPlaceholder': None, 'goals': 6, 'timeOut': None,
> 'powerplayInstances': 5, 'powerplayGoals': 1, 'shortHandedInstances':
> 3, 'shortHandedGoals': 0}, 'finishedType':
> 'ENDED_DURING_REGULAR_GAME_TIME', 'started': True, 'ended': True,
> 'gameTime': 3600, 'cacheUpdateDate': '2021-11-09T15:56:12.855089Z'},
> {'id': 9, 'season': 2022, 'start': '2021-09-11T14:00:00Z', 'homeTeam':
> {'teamId': '461765763:kookoo', 'teamPlaceholder': None, 'goals': 3,
> 'timeOut': None, 'powerplayInstances': 5, 'powerplayGoals': 1,
> 'shortHandedInstances': 2, 'shortHandedGoals': 0}, 'awayTeam':
> {'teamId': '933686567:saipa', 'teamPlaceholder': None, 'goals': 1,
> 'timeOut': None, 'powerplayInstances': 2, 'powerplayGoals': 0,
> 'shortHandedInstances': 5, 'shortHandedGoals': 0}, 'finishedType':
> 'ENDED_DURING_REGULAR_GAME_TIME', 'started': True, 'ended': True,
> 'gameTime': 3600, 'cacheUpdateDate': '2021-11-09T16:17:59.306028Z'},
> {'id': 8, 'season': 2022, 'start': '2021-09-11T14:00:00Z', 'homeTeam':
> {'teamId': '859884935:kalpa', 'teamPlaceholder': None, 'goals': 0,
> 'timeOut': None, 'powerplayInstances': 1, 'powerplayGoals': 0,
> 'shortHandedInstances': 1, 'shortHandedGoals': 0}, 'awayTeam':
> {'teamId': '292293444:jukurit', 'teamPlaceholder': None, 'goals': 3,
> 'timeOut': None, 'powerplayInstances': 1, 'powerplayGoals': 0,
> 'shortHandedInstances': 1, 'shortHandedGoals': 0}, 'finishedType':
> 'ENDED_DURING_REGULAR_GAME_TIME', 'started': True, 'ended': True,
> 'gameTime': 3600, 'cacheUpdateDate': '2021-11-09T16:15:27.449045Z'},
> {'id': 13, 'season': 2022, 'start': '2021-09-11T15:30:00Z',
> 'homeTeam': {'teamId': '626537494:sport', 'teamPlaceholder': None,
> 'goals': 3, 'timeOut': None, 'powerplayInstances': 2,
> 'powerplayGoals': 0, 'shortHandedInstances': 3, 'shortHandedGoals':
> 0}, 'awayTeam': {'teamId': '362185137:tappara', 'teamPlaceholder':
> None, 'goals': 2, 'timeOut': None, 'powerplayInstances': 3,
> 'powerplayGoals': 2, 'shortHandedInstances': 2, 'shortHandedGoals':
> 0}, 'finishedType': 'ENDED_DURING_REGULAR_GAME_TIME', 'started': True,
> 'ended': True, 'gameTime': 3600, 'cacheUpdateDate':
> '2021-11-09T16:01:44.356985Z'}, {'id': 12, 'season': 2022, 'start':
> '2021-09-11T15:30:00Z', 'homeTeam': {'teamId': '624554857:lukko',
> 'teamPlaceholder': None, 'goals': 5, 'timeOut': None,
> 'powerplayInstances': 3, 'powerplayGoals': 2, 'shortHandedInstances':
> 3, 'shortHandedGoals': 0}, 'awayTeam': {'teamId': '679171680:ässät',
> 'teamPlaceholder': None, 'goals': 1, 'timeOut': None,
> 'powerplayInstances': 3, 'powerplayGoals': 1, 'shortHandedInstances':
> 3, 'shortHandedGoals': 0}, 'finishedType':
> 'ENDED_DURING_REGULAR_GAME_TIME', 'started': True, 'ended': True,
> 'gameTime': 3600, 'cacheUpdateDate': '2021-11-09T16:08:36.538621Z'},
> {'id': 16, 'season': 2022, 'start': '2021-09-15T15:30:00Z',
> 'homeTeam': {'teamId': '219244634:jyp', 'teamPlaceholder': None,
> 'goals': 1, 'timeOut': None, 'powerplayInstances': 2,
> 'powerplayGoals': 0, 'shortHandedInstances': 4, 'shortHandedGoals':
> 0}, 'awayTeam': {'teamId': '651304385:tps', 'teamPlaceholder': None,
> 'goals': 2, 'timeOut': None, 'powerplayInstances': 4,
> 'powerplayGoals': 0, 'shortHandedInstances': 2, 'shortHandedGoals':
> 0}, 'finishedType': 'ENDED_DURING_REGULAR_GAME_TIME', 'started': True,
> 'ended': True, 'gameTime': 3600, 'cacheUpdateDate':
> '2021-11-09T16:09:07.505231Z'}, {'id': 15, 'season': 2022, 'start':
> '2021-09-15T15:30:00Z', 'homeTeam': {'teamId': '292293444:jukurit',
> 'teamPlaceholder': None, 'goals': 2, 'timeOut': None,
> 'powerplayInstances': 2, 'powerplayGoals': 1, 'shortHandedInstances':
> 6, 'shortHandedGoals': 0}, 'awayTeam': {'teamId':
> '875886777:pelicans', 'teamPlaceholder': None, 'goals': 6, 'timeOut':
> None, 'powerplayInstances': 6, 'powerplayGoals': 3,
> 'shortHandedInstances': 2, 'shortHandedGoals': 0}, 'finishedType':
> 'ENDED_DURING_REGULAR_GAME_TIME', 'started': True, 'ended': True,
> 'gameTime': 3600, 'cacheUpdateDate': '2021-11-09T16:03:44.272707Z'},
> {'id': 14, 'season': 2022, 'start': '2021-09-15T15:30:00Z',
> 'homeTeam': {'teamId': '168761288:hifk', 'teamPlaceholder': None,
> 'goals': 2, 'timeOut': None, 'powerplayInstances': 5,
> 'powerplayGoals': 1, 'shortHandedInstances': 2, 'shortHandedGoals':
> 0}, 'awayTeam': {'teamId': '624554857:lukko', 'teamPlaceholder': None,
> 'goals': 3, 'timeOut': None, 'powerplayInstances': 2,
> 'powerplayGoals': 1, 'shortHandedInstances': 5, 'shortHandedGoals':
> 0}, 'finishedType': 'ENDED_DURING_EXTENDED_GAME_TIME', 'started':
> True, 'ended': True, 'gameTime': 3650, 'cacheUpdateDate':
> '2021-11-09T15:18:11.207884Z'}, {'id': 18, 'season': 2022, 'start':
> '2021-09-15T15:30:00Z', 'homeTeam': {'teamId': '679171680:ässät',
> 'teamPlaceholder': None, 'goals': 5, 'timeOut': None,
> 'powerplayInstances': 3, 'powerplayGoals': 1, 'shortHandedInstances':
> 4, 'shortHandedGoals': 0}, 'awayTeam': {'teamId': '859884935:kalpa',
> 'teamPlaceholder': None, 'goals': 4, 'timeOut': None,
> 'powerplayInstances': 4, 'powerplayGoals': 1, 'shortHandedInstances':
> 3, 'shortHandedGoals': 0}, 'finishedType':
> 'ENDED_DURING_EXTENDED_GAME_TIME', 'started': True, 'ended': True,
> 'gameTime': 3791, 'cacheUpdateDate': '2021-11-09T16:08:28.328099Z'},
> {'id': 17, 'season': 2022, 'start': '2021-09-15T15:30:00Z',
> 'homeTeam': {'teamId': '495643563:kärpät', 'teamPlaceholder': None,
> 'goals': 1, 'timeOut': None, 'powerplayInstances': 2,
> 'powerplayGoals': 0, 'shortHandedInstances': 4, 'shortHandedGoals':
> 0}, 'awayTeam': {'teamId': '55786244:hpk', 'teamPlaceholder': None,
> 'goals': 2, 'timeOut': None, 'powerplayInstances': 4,
> 'powerplayGoals': 0, 'shortHandedInstances': 2, 'shortHandedGoals':
> 0}, 'finishedType': 'ENDED_DURING_EXTENDED_GAME_TIME', 'started':
> True, 'ended': True, 'gameTime': 3679, 'cacheUpdateDate':
> '2021-11-09T15:43:33.855781Z'}, {'id': 19, 'season': 2022, 'start':
> '2021-09-16T15:30:00Z', 'homeTeam': {'teamId': '362185137:tappara',
> 'teamPlaceholder': None, 'goals': 2, 'timeOut': None,
> 'powerplayInstances': 5, 'powerplayGoals': 0, 'shortHandedInstances':
> 3, 'shortHandedGoals': 0}, 'awayTeam': {'teamId': '951626834:ilves',
> 'teamPlaceholder': None, 'goals': 3, 'timeOut': None,
> 'powerplayInstances': 3, 'powerplayGoals': 1, 'shortHandedInstances':
> 5, 'shortHandedGoals': 0}, 'finishedType':
> 'ENDED_DURING_REGULAR_GAME_TIME', 'started': True, 'ended': True,
> 'gameTime': 3600, 'cacheUpdateDate': '2021-11-09T16:00:11.888989Z'},
> {'id': 21, 'season': 2022, 'start': '2021-09-17T15:30:00Z',
> 'homeTeam': {'teamId': '461765763:kookoo', 'teamPlaceholder': None,
> 'goals': 1, 'timeOut': None, 'powerplayInstances': 5,
> 'powerplayGoals': 0, 'shortHandedInstances': 8, 'shortHandedGoals':
> 0}, 'awayTeam': {'teamId': '219244634:jyp', 'teamPlaceholder': None,
> 'goals': 2, 'timeOut': None, 'powerplayInstances': 8,
> 'powerplayGoals': 2, 'shortHandedInstances': 5, 'shortHandedGoals':
> 0}, 'finishedType': 'ENDED_DURING_EXTENDED_GAME_TIME', 'started':
> True, 'ended': True, 'gameTime': 3648, 'cacheUpdateDate':
> '2021-11-09T16:02:06.371005Z'}, {'id': 20, 'season': 2022, 'start':
> '2021-09-17T15:30:00Z', 'homeTeam': {'teamId': '55786244:hpk',
> 'teamPlaceholder': None, 'goals': 2, 'timeOut': None,
> 'powerplayInstances': 1, 'powerplayGoals': 0, 'shortHandedInstances':
> 2, 'shortHandedGoals': 0}, 'awayTeam': {'teamId': '679171680:ässät',
> 'teamPlaceholder': None, 'goals': 1, 'timeOut': None,
> 'powerplayInstances': 2, 'powerplayGoals': 0, 'shortHandedInstances':
> 1, 'shortHandedGoals': 0}, 'finishedType':
> 'ENDED_DURING_EXTENDED_GAME_TIME', 'started': True, 'ended': True,
> 'gameTime': 3672, 'cacheUpdateDate': '2021-11-09T15:47:19.435124Z'},
> {'id': 24, 'season': 2022, 'start': '2021-09-17T15:30:00Z',
> 'homeTeam': {'teamId': '626537494:sport', 'teamPlaceholder': None,

that's part of the data. So far just used requests:

import requests

url = "https://liiga.fi/api/v1/games?tournament=all&season=2021" r = requests.get(url)

print(r.json())

CodePudding user response：

Only get this:

id  season  ...              cacheUpdateDate buyTicketsUrl
0       1    2022  ...  2021-11-09T16:54:56.243678Z           NaN
1       4    2022  ...  2021-11-09T16:30:45.387198Z           NaN
2       3    2022  ...  2021-11-09T16:57:55.660414Z           NaN
3       2    2022  ...  2021-11-09T17:05:34.763264Z           NaN
4       6    2022  ...  2021-11-09T16:52:37.081451Z           NaN
..    ...     ...  ...                          ...           ...
518  6909    2022  ...  2021-11-09T17:26:48.886193Z           NaN
519  6898    2022  ...  2021-11-09T16:54:08.527658Z           NaN
520  6902    2022  ...  2021-11-09T16:30:00.998782Z           NaN
521  6910    2022  ...  2021-11-09T17:05:19.950635Z           NaN
522  6906    2022  ...  2021-11-09T16:39:19.910866Z           NaN

[523 rows x 11 columns]

CodePudding user response：

You could use Pandas DataFrames as:

import requests
import json
import pandas as pd

url = "https://liiga.fi/api/v1/games?tournament=all&season=2021" 
r = requests.get(url)
r = r.json()

df = pd.DataFrame(r)

print(df)

Since you want to reproduce the standings table, you still need to analyse the raw data that you have. To help you analyse the data, you could create separate the homeTeam and awayTeam columns into separate DataFrames as

homeTeam_list = [ homeTeam for homeTeam in df['homeTeam']]
awayTeam_list = [ awayTeam for awayTeam in df['awayTeam']]

df_homeTeam = pd.DataFrame(homeTeam_list)
df_awayTeam = pd.DataFrame(awayTeam_list)

print(df_awayTeam)

In this way you can make loops to calculate the scores for each game, who won, the table score and so on.