Converting a json file to a dataframe with rows and columns

[eyavuz21]

Thanks so much for your help. I have tried your code above - it gives me an empty dataframe.

When I try the following code:

import json
game_data = []

with open('/Users/macbook/Desktop/SavedSHQdatafullgame/All-gamedata-279-20230126-120924.json', 'r') as file:
    readfile = file.read()

json.loads(readfile)

json.loads(readfile) looks like this:

[{'meta': {'user_id': '178054',
     'level_id': 1,
     'duration': 16.6286621,
     'previous_attempts': 1,
     'early_termination': False,
     'platform': 'iOS',
     'app_version': '1.9.23',
     'map_view_duration': 7.3983},

readfile looks like this:

[{"meta":{"user_id":"178054","level_id":1,"duration":14.6162566999999992134462445392273366451263427734375,"previous_attempts":1,"early_termination":false,"platform":"ANDROID","app_version":"1.9.24","map_view_duration":15.2666930999999994611471265670843422412872314453125},"events":[{"time":0,

[eyavuz21]

That is not a json file. game-data is not valid json, and json syntax uses double quotes instead of single quotes. The file also looks like it is several individual json strings, not one data structure. Where did you get the file?

I'm going to guess that the file looks like this:

Output:
game-data
[{'meta': {'user_id': '178054', 'level_id': 1}}]
[{'meta': {'user_id': '178054', 'level_id': 1}}]
[{'meta': {'user_id': '178054', 'level_id': 2}}]

Using that format, I wrote a program that skips the first line. For each following line it replaces single quotes with double quotes and does a json.loads() to get a python list of dictionaries. Then, thinking there is only one dictionary in the list, it gets the first dictionary and gets the values associated with the "meta" key. That information is collected into a list. Finally the json information is normalized and loaded into a dataframe.

import json
import pandas as pd

game_data = []
with open('data.txt', 'r') as file:
    next(file)
    for line in file:
        line = line.replace("'", '"')
        game_data.append(json.loads(line)[0]['meta'])

df = pd.json_normalize(game_data)
print(df)

Output:
  user_id  level_id
0  178054         1
1  178054         1
2  178054         2

Thanks so much for your help. I have tried your code above - it gives me an empty dataframe.

When I try the following code:

import json
game_data = []

with open('/Users/macbook/Desktop/SavedSHQdatafullgame/All-gamedata-279-20230126-120924.json', 'r') as file:
    readfile = file.read()

json.loads(readfile)

json.loads(readfile) looks like this:

[{'meta': {'user_id': '178054',
     'level_id': 1,
     'duration': 16.6286621,
     'previous_attempts': 1,
     'early_termination': False,
     'platform': 'iOS',
     'app_version': '1.9.23',
     'map_view_duration': 7.3983},

readfile looks like this:

[{"meta":{"user_id":"178054","level_id":1,"duration":14.6162566999999992134462445392273366451263427734375,"previous_attempts":1,"early_termination":false,"platform":"ANDROID","app_version":"1.9.24","map_view_duration":15.2666930999999994611471265670843422412872314453125},"events":[{"time":0,

[snippsat]

Thanks so much for your help. I have tried your code above - it gives me an empty dataframe.

When I try the following code:

You mess the json file to a string when doing that,most use json.load(file) and not file.read()
You post complete raw json data,or working smample of it it's big.

Here a comlete example that help if you look at undertand how it works.
Use eg jsoncrack to see the structure better and that is vaild json file.
game.json

Output:
{
  "results": [
    {
      "leagues": [
        {
          "matches": [
            {
              "league": "Premier League",
              "teams": "Liverpool - Chelsea",
              "score": [
                "3:0"
              ]
            },
            {
              "league": "Premier League",
              "teams": "Man Utd - Arsenal",
              "score": [
                "2:1"
              ]
            }
          ]
        },
        {
          "matches": [
            {
              "league": "La Liga",
              "teams": "Atletico Madrid - Villareal",
              "score": [
                "0:2"
              ]
            }
          ]
        }
      ]
    }
  ]
}

I want this result of game.json as a DataFrame.

Output:
           league                teams  score
0  Premier League  Liverpool - Chelsea  [3:0]
1  Premier League    Man Utd - Arsenal  [2:1]

Then i would use json_normalize like this in code under.

import pandas as pd
import json

with open('game.json') as file:
    json_data = json.load(file)

df = pd.json_normalize(json_data['results'][0]['leagues'][0], record_path='matches')

Look at result.

>>> df
           league                teams  score
0  Premier League  Liverpool - Chelsea  [3:0]
1  Premier League    Man Utd - Arsenal  [2:1]

[eyavuz21]

Thanks so much for your help. I have tried your code above - it gives me an empty dataframe.

When I try the following code:

You mess the json file to a string when doing that,most use json.load(file) and not file.read()
You post complete raw json data,or working smample of it it's big.

Here a comlete example that help if you look at undertand how it works.
Use eg jsoncrack to see the structure better and that is vaild json file.
game.json

Output:
{
  "results": [
    {
      "leagues": [
        {
          "matches": [
            {
              "league": "Premier League",
              "teams": "Liverpool - Chelsea",
              "score": [
                "3:0"
              ]
            },
            {
              "league": "Premier League",
              "teams": "Man Utd - Arsenal",
              "score": [
                "2:1"
              ]
            }
          ]
        },
        {
          "matches": [
            {
              "league": "La Liga",
              "teams": "Atletico Madrid - Villareal",
              "score": [
                "0:2"
              ]
            }
          ]
        }
      ]
    }
  ]
}

I want this result of game.json as a DataFrame.

Output:
           league                teams  score
0  Premier League  Liverpool - Chelsea  [3:0]
1  Premier League    Man Utd - Arsenal  [2:1]

Then i would use json_normalize like this in code under.

import pandas as pd
import json

with open('game.json') as file:
    json_data = json.load(file)

df = pd.json_normalize(json_data['results'][0]['leagues'][0], record_path='matches')

Look at result.

>>> df
           league                teams  score
0  Premier League  Liverpool - Chelsea  [3:0]
1  Premier League    Man Utd - Arsenal  [2:1]

Thank you so much for your help. I have now managed to find a solution:

import json
import pandas as pd

newdataframe = pd.DataFrame(index=range(705),columns=range(9))

with open('/Users/macbook/Desktop/SavedSHQdatafullgame/All-gamedata-279-20230129-023739.json', 'r') as file:
    json_data = json.load(file)
 
for i in range(115,len(json_data['game-data'])): 
    relevantrow = pd.json_normalize(json_data['game-data'][i])
    j = i - 115 
    newdataframe.iloc[j,0] = relevantrow['player'][0]
    newdataframe.iloc[j,1] = relevantrow['meta.user_id'][0]
    newdataframe.iloc[j,2] = relevantrow['meta.level_id'][0]
    newdataframe.iloc[j,3] = relevantrow['meta.duration'][0]
    newdataframe.iloc[j,4] = relevantrow['meta.previous_attempts'][0]
    newdataframe.iloc[j,5] = relevantrow['meta.early_termination'][0]
    newdataframe.iloc[j,6] = relevantrow['meta.platform'][0]
    newdataframe.iloc[j,7] = relevantrow['meta.app_version'][0]
    newdataframe.iloc[j,8] = relevantrow['meta.map_view_duration'][0]
 
newdataframe = newdataframe.rename(columns={0: "Coordinates",1:"ID",2:"Level",3:"Duration",4:"Previousattempts?",5:"Earlytermination?",6:"Platform",7:"Appversion",8:"Durationofmapview"})
newdataframe.to_csv('/Users/macbook/Desktop/SavedSHQdatafullgame/fulldata.csv')

This results in 'newdataframe' looking like this:

Coordinates	ID	Level	Duration	Previousattempts?	Earlytermination?	Platform	Appversion	Durationofmapview
0	[{'x': 11, 'y': 12, 'r': 2}, {'x': 11, 'y': 12...	178054	1	16.628662	1	False	iOS	1.9.23	7.3983