CODEWITHSUNDEEP
pythonjsonpandas
JD2775
JD2775

Reputation: 3801

Reading JSON file into Pandas for analysis

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

Upvotes: 1

Views: 172

Answers (1)

Jab
Jab

Reputation: 27515

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"
    }]
}

Upvotes: 1

Related Questions