Converting a json file to a dataframe with rows and columns

[deanhystad]

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