Read a JSON file using pandas

Question

I'm trying to convert a JSON file into a data frame and save it to a CSV file at the end. Well, I am able to do it using Jupyter or Colab, but when I tried on my local python compiler I get many errors.

Here is my code that works on Colab

import pandas as pd
import datetime

# reading json file
df = pd.read_json(path_or_buf=CONFIG_PROPERTIES)

# normalizing json file
df_items_normalized = pd.json_normalize(data=df.orders, sep='_', record_path='items', meta=['error', 'file','order_id'])

# define parameters to save in csv
today = datetime.datetime.today().strftime('%Y_%m_%d')
path = "/output/pedidos_weedu_" + today + ".csv"

# saving to csv
df_items_normalized.to_csv(path, index=False)

And here is my code when I tried through Pycharm

import pandas as pd
import datetime
import json
import os

CWD = os.getcwd()
JSON_CONFIG_FILE_PATH = '%s/%s' % (CWD, '12-11-2021.json')

CONFIG_PROPERTIES = {}

try:
    with open(JSON_CONFIG_FILE_PATH) as data_file:
        CONFIG_PROPERTIES = json.load(data_file)
except IOError as e:
    print(e)
    print('IOError: Unable to open config.json.')
    exit(1)

print(CONFIG_PROPERTIES)

# reading json file
df = pd.read_json(path_or_buf=CONFIG_PROPERTIES)

# normalizing json file
df_items_normalized = pd.json_normalize(data=df.orders, sep='_', record_path='items', meta=['error', 'file','order_id'])

# define parameters to save in csv
today = datetime.datetime.today().strftime('%Y_%m_%d')
path = "/output/pedidos_weedu_" + today + ".csv"

# saving to csv
df_items_normalized.to_csv(path, index=False)

Here is the JSON file I'm working on

What are the errors? Please paste the full traceback.

jkr
– jkr

2021-11-17 22:10:31 +00:00
Commented Nov 17, 2021 at 22:10 — jkr
– jkr, Commented Nov 17, 2021 at 22:10

Arash · Accepted Answer · 2021-11-17 22:46:58Z

-1

I have changed your code a little:

import pandas as pd
import datetime
import json
import os
from pandas.io.json import json_normalize as jn

CWD = os.getcwd()
JSON_CONFIG_FILE_PATH = "%s/%s" % (CWD, "12-11-2021.json")

print(JSON_CONFIG_FILE_PATH)
CONFIG_PROPERTIES = {}

try:
    with open(JSON_CONFIG_FILE_PATH) as data_file:
        CONFIG_PROPERTIES = json.load(data_file)
except IOError as e:
    print(e)
    print("IOError: Unable to open config.json.")
    exit(1)
print(CONFIG_PROPERTIES)

df = pd.read_json(path_or_buf=JSON_CONFIG_FILE_PATH)
df_items_normalized = jn(
    data=df.orders, sep="_", record_path="items", meta=["error", "file", "order_id"]
)

# define parameters to save in csv
today = datetime.datetime.today().strftime("%Y_%m_%d")
path = "./" + today + ".csv"

# saving to csv
df_items_normalized.to_csv(path, index=False)

this line from pandas.io.json import json_normalize as jn from this answer.

I really do not understant why this answer got a negative point!

edited Nov 17, 2021 at 22:46

answered Nov 17, 2021 at 22:00

Arash

6521 gold badge7 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kleiton Marques Dos Reis Over a year ago

It worked just fine.

Collectives™ on Stack Overflow

Read a JSON file using pandas

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related