cat.py

# apply 'precision' and 'value' column to get 'amount' 
def update_total(row):
    amount = row['value'] / pow(10, row['precision'])
    return amount

raw1 = pandas.read_json('cs.json', convert_dates=['booking'])
raw1 = raw1.dropna(axis=1, how='all')
flat1 = pandas.concat([raw1.drop(['amount'], axis=1), raw1['amount'].apply(pandas.Series)], axis=1)
flat1 = pandas.concat([flat1.drop(['partnerAccount'], axis=1), flat1['partnerAccount'].apply(pandas.Series)], axis=1)
flat1 = flat1.dropna(axis=1, how='all')
flat1 = flat1[['booking', 'partnerName', 'value', 'precision', 'currency']]
flat1['booking'] = pandas.to_datetime(flat1['booking'], utc=True)
flat1['amount'] = flat1.apply(update_total, axis=1)
flat1 = flat1.drop(columns=['value', 'precision'])

raw2 = pandas.read_json('cs2.json', convert_dates=['booking'])
raw2 = raw2.dropna(axis=1, how='all')
flat2 = pandas.concat([raw2.drop(['amount'], axis=1), raw2['amount'].apply(pandas.Series)], axis=1)
flat2 = pandas.concat([flat2.drop(['partnerAccount'], axis=1), flat2['partnerAccount'].apply(pandas.Series)], axis=1)
flat2 = flat2.dropna(axis=1, how='all')
flat2 = flat2[['booking', 'partnerName', 'value', 'precision', 'currency']]
flat2['booking'] = pandas.to_datetime(flat2['booking'], utc=True)
flat2['amount'] = flat2.apply(update_total, axis=1)
flat2 = flat2.drop(columns=['value', 'precision'])



cat = pandas.concat([flat2, flat1], sort=False)
cat = cat.drop_duplicates(keep="first")



CURRENT_ACCOUNT_BALANCE = 1000.00 # CZK
# calculate growing total value
current_total = CURRENT_ACCOUNT_BALANCE
total = []
for transact in [i for i in cat['amount']]:
    current_total -= transact
    total.append(current_total)
cat['total'] = total



cat = cat.set_index('booking')



cat.plot()
cat.head(50)