Reading JSON file into Pandas for analysis

Question

I am getting myself familiar with Classes/OOP in Python and I am practicing on a basic program for tracking finances. I have the program to the point where I add entries to a JSON file and save that file away. Now I want to read in the JSON file into a dataframe and perform some aggregates on it. That's where I am stuck. The following fails with:

json.decoder.JSONDecodeError: Extra data: line 7 column 2 (char 122)

The JSON file looks like this:

{
    "DATE": "2019-02-01 12:57:13.140724",
    "HSA": "600",
    "401K": "90",
    "ROTH": "900",
    "SAVINGS": "1000"
}{
    "DATE": "2019-02-01 12:57:26.995724",
    "HSA": "250",
    "401K": "90",
    "ROTH": "80",
    "SAVINGS": "900"
}

Any ideas?

import datetime
import json
import pandas as pd

class BankAccount:

    def __init__(self):
        self.accounts = ['HSA', '401K', 'ROTH', 'SAVINGS']
        self.records = {}
        self.now = datetime.datetime.now()



    def data_entry(self):
        for i in self.accounts:
            x = input('Enter the amount for {}:'.format(i))
            self.records['DATE'] = self.now
            self.records[i] = x


    def display(self):
        return self.records

    def savefile(self):
        with open('finance.json', 'a') as file:
            file.write(json.dumps(self.records, indent=4, sort_keys=True, default=str))
            file.close()

    def analyzedata(self):
        with open('finance.json', 'r') as f:
            obj = json.load(f)
            frame = pd.DataFrame(obj, columns=['401K', 'HSA', 'ROTH', 'SAVINGS', 'DATE'])
            print(frame)





s = BankAccount()
s.data_entry()
s.savefile()
s.analyzedata()

BTW feel free to offer any other suggestions as to why this is a bad way to do it, i.e. using a Dictionary or whatever it may be. Still learning. Thanks

No need to close a file if you're using a context manager to open it. Also you're appending a whole new json file on top of the last one every time you save this. try implementing a load method and performing this within __init__ if there is data in the file then fill records with that data before you alter it. then use 'w' as your file option when you save it. This will result in a correct json file format and a sort of persistence to the object. — Jab
– Jab, Commented Feb 1, 2019 at 21:09

Jab · Accepted Answer · 2019-02-01 22:40:46Z

1

JSON data is represented as one dict, not multiple dicts in a file. This said I suggest the JSON format be basically a dict that has a key 'data' that holds a list of record dicts. I also fixed a couple namimg conventions a small things I say to be easier to understand ontop of my comment.

from datetime import datetime
import json
import pandas as pd

class BankAccount:

    def __init__(self, filename='finance.json'):
        self.accounts = ['HSA', '401K', 'ROTH', 'SAVINGS']
        self.records = []
        self.filename = filename

        #load data upon initialization
        self.load_data()

    def load_data(self):
        with open(self.filename, 'r') as file:
            #you may want to do some error checking here
            data = json.load(file)
            self.records = data.get('data', [])

    def data_entry(self):

        #make a new record with current date
        record = {'DATE': datetime.now()}
        for account_name in self.accounts:
            account_data = int(input('Enter the amount for {}:'.format(account_name)))
            record[account_name] = account_data
        self.records.append(record)

        #You made a modification to the records
        #now save it to file
        self.save_data()

    def save_data(self):
        with open(self.filename, 'w') as file:
            #possibly some error checking here as seen fit
            file.write(json.dumps({'data': self.records}, default=str))

    def analyze_edata(self):
        with open(self.filename, 'r') as file:
            df = pd.DataFrame(self.records, columns=self.accounts+['DATE'])
            print(df)

s = BankAccount()
s.data_entry()
s.save_data()
s.analyze_data()

When Ran: *a couple times**

Enter the amount for HSA:250
Enter the amount for 401K:90
Enter the amount for ROTH:80
Enter the amount for SAVINGS:900
['HSA', '401K', 'ROTH', 'SAVINGS', 'DATE']
   HSA  401K  ROTH  SAVINGS                        DATE
0  600    90   900     1000  2019-02-01 22:05:06.110471
1  360   100   250      430  2019-02-01 22:06:10.649269
2  250    90    80      900  2019-02-01 22:07:04.176700

finance.json

{
    "data": [{
        "401K": 90,
        "SAVINGS": 1000,
        "ROTH": 900,
        "HSA": 600,
        "DATE": "2019-02-01 22:05:06.110471"
    }, {
        "401K": 100,
        "SAVINGS": 430,
        "ROTH": 250,
        "HSA": 360,
        "DATE": "2019-02-01 22:06:10.649269"
    }, {
        "401K": 90,
        "ROTH": 80,
        "SAVINGS": 900,
        "HSA": 250,
        "DATE": "2019-02-01 22:07:04.176700"
    }]
}

edited Feb 1, 2019 at 22:40

answered Feb 1, 2019 at 22:27

Jab

27.6k22 gold badges82 silver badges117 bronze badges

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

4 Comments

JD2775 Over a year ago

this is great, thank you for taking the time to write this out. Question though, when I start with an empty json file (finance.json) and I add the last 4 lines of my original code (s = BankAccount() etc....) and run it, it errors out with json decoder error 'Expecting value: line 1 column 1 (char 0) Any ideas why? Should I change how I am executing these? I dont see that part in your code so maybe I am doing it wrong. thank you again!

Jab Over a year ago

Copy my .json format or just put {} into the .json file and start over. If you need the old data try a json verifier on google for starts

JD2775 Over a year ago

adding {} to the empty json file did the trick. Thank you again, much appreciated!

Jab Over a year ago

Take note the comments I edited in that say you may want to do some error checking for valid JSON. That will allow you to handle when there is invalid JSON in there. You’d either have to parse and fix it or overwrite it with an empty dict or {'data': []}

Collectives™ on Stack Overflow

Reading JSON file into Pandas for analysis

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related