How to format a pd DataFrame reading a JSON file with 1 line without splitting into multiple rows?

user_5654627 0
Viewed 116

I have data in JSON file that is split up into 2 rows when I read it into a pandas DataFrame because 2 keys have 2 instead of 1 values.

The JSON file contains:

{'userId': 'e79dc0d35a30d1b3c6215bb54f163f247ca8f1cd5b988d3496ac7fa2f4dfa06d',
 'attemptTime': '2020-10-21T16:51:12',
 'ipAddress': 'c739bc11e8cb4b56ec984b5939e0606f795176fc2d844ad8f1360a185c57d5cb',
 'location': 'EXTERNAL',
 'score': '8,00',
 'subScores': ['5,00', '3,00'],
 'words': 330,
 'subWords': [132, 198],
 'doNotReview': False}

The output of pd.read_json(f'{qs_path}/metadata.json') is

enter image description here

Because subScores and subWords are a list of 2 values the data is being split into 2 rows. How do I format it where I keep each list in one cell and end up with just one row?

pandas dataframe python json
2 Answers

You can use json module with pd.json_normalize:

import json

df = pd.json_normalize(json.load(open("your_data.json", "rb")))
print(df)

Prints:

                                              userId          attemptTime                                          ipAddress  location score     subScores  words    subWords  doNotReview
0  e79dc0d35a30d1b3c6215bb54f163f247ca8f1cd5b988d...  2020-10-21T16:51:12  c739bc11e8cb4b56ec984b5939e0606f795176fc2d844a...  EXTERNAL  8,00  [5,00, 3,00]    330  [132, 198]        False

Or: read json as series and convert it to dataframe:

df = pd.read_json("your_data.json", typ="series").to_frame().T
print(df)
user_100359850

You can try agg().

import pandas as pd


data = {'userId': 'e79dc0d35a30d1b3c6215bb54f163f247ca8f1cd5b988d3496ac7fa2f4dfa06d',
        'attemptTime': '2020-10-21T16:51:12',
        'ipAddress': 'c739bc11e8cb4b56ec984b5939e0606f795176fc2d844ad8f1360a185c57d5cb',
        'location': 'EXTERNAL',
        'score': '8,00',
        'subScores': ['5,00', '3,00'],
        'words': 330,
        'subWords': [132, 198],
        'doNotReview': False
        }


df = pd.DataFrame.from_dict(data)

df.columns

d = {col:'first' for col in df.columns}
d['subScores'] = list
d['subWords'] = list

df_ = df.groupby('userId').agg(d).reset_index(drop=True)

print(df_)

                                                             userId  \
0  e79dc0d35a30d1b3c6215bb54f163f247ca8f1cd5b988d3496ac7fa2f4dfa06d   

           attemptTime  \
0  2020-10-21T16:51:12   

                                                          ipAddress  location  \
0  c739bc11e8cb4b56ec984b5939e0606f795176fc2d844ad8f1360a185c57d5cb  EXTERNAL   

  score     subScores  words    subWords  doNotReview  
0  8,00  [5,00, 3,00]    330  [132, 198]        False
user_103151630
Related