0

I am trying to read from a text file into a pandas dataframe. The text file seems to be a 2D array of JSON, how could I read it?

[[{'metric_name':'CPU','category':'A','data':'9','time_stamp':'2019-03-28 13:15:31'}],[{'metric_name':'Disk','category':'B','data':'56','time_stamp':'2019-03-28 13:15:31'}]]

I expect to have the parameters "metric_name", "category", "data", "time_stamp" as headers

Pat
  • 1
  • 1

1 Answers1

0

Here is a solution :

import json
import pandas as pd

# load the file
raw_data = json.load(open('myfile.json'))

# raw_data contains a nested list, so convert it to a simple list :
data = [x[0] for x in raw_data]

# then create the dataframe
df = pd.DataFrame.from_records(data)

Here is the content of data. The nested list has been converted to a simple list (assuming that we have one record per array) :

[{"category": "VM1",
      "data": "9",
      "metric_name": "CPU",
      "time_stamp": "2019-03-28 13:15:31"},
     {"category": "VM1",
      "data": "9",
      "metric_name": "CPU",
      "time_stamp": "2019-03-28 13:15:31"}]
XavierBrt
  • 1,179
  • 8
  • 13
  • I am getting this error: TypeError: the JSON object must be str, bytes or bytearray, not TextIOWrapper – Pat Apr 02 '19 at 14:16
  • Try to replace `raw_data = json.load(open(file_path))` by `with open(file_path) as f: raw_data = json.loads(f.read())` – XavierBrt Apr 02 '19 at 14:49
  • JSONDecodeError: Expecting property name enclosed in double quotes – Pat Apr 02 '19 at 15:16
  • https://stackoverflow.com/questions/39491420/python-jsonexpecting-property-name-enclosed-in-double-quotes – XavierBrt Apr 02 '19 at 15:53