How to read JSON data in TXT file into Pandas

Question

I have a

".txt"

file which has JSON data in it. I want to read this file in python and convert it into a dataframe.

The Data in this text file looks like this:

{
"_id" : "116b244599862fd2200",
"met_id" : [
    612019,
    621295,
    725,
    622169,
    640014,
    250,
    350,
    640015,
    613689,
    650423
],
"id" : "104",
"name" : "Energy",
"label" : "Peer Group",
"display_type" : "Risky Peer Group",
"processed_time" : ISODate("2019-04-18T11:17:05Z")
}

I tried reading it using the

pd.read_json

function but it always shows me an error. I am quite new to JSON, how can I use this Text file and load it in Python?

It's the `ISODate` that's causing issues, `pandas` won't have any idea what that is — C.Nivs, Apr 18 '19 at 18:45
_it always shows me an error_ It would help if you showed us that error. — John Gordon, Apr 18 '19 at 18:51
`"processed_time" : ISODate("2019-04-18T11:17:05Z")` is not JSON. — John Gordon, Apr 18 '19 at 18:52

Wang Liang · Answer 1 · 2021-09-10T14:19:09.750

3

Please check this link

Also, "processed_time" : ISODate("2019-04-18T11:17:05Z") is not JSON format.
We can check that in https://jsonlint.com/

I added python code.

import pandas as pd
import json

with open('rr.txt') as f:
    string = f.read()
    
    # Remove 'ISODate(', ')'       For correct, we can use regex
    string = string.replace('ISODate(', '')
    string = string.replace(')', '')

    jsonData = json.loads(string)
    print (pd.DataFrame(jsonData))

edited Sep 10 '21 at 14:19

answered Apr 18 '19 at 18:50

Wang Liang

4,244
6
22
45

It gives me error, JSONDecodeError: Extra data: line 21 column 1 (char 381) – Kshitij Yadav Apr 22 '19 at 13:26

How to read JSON data in TXT file into Pandas

1 Answers1