In [6]:
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
import numpy as np
%matplotlib inline
%load_ext autoreload
%autoreload 2
pd.set_option('float_format', '{:.2f}'.format)
plt.style.use('seaborn-colorblind')
cm = sns.light_palette("green", as_cmap=True)
In [2]:
# Run bean-query to generate table
!bean-query -q ~/finance/money.beancount "SELECT year, month, root(account, 2) as Account, sum(number) as Total, currency WHERE account ~ 'Expenses' OR account ~ 'Liabilities:UK:HSBC:Mortgage' OR account ~ 'Income' GROUP BY year, month, Account, currency ORDER BY year, month, currency, Account FLATTEN" > monthly.txt
In [29]:
# Process Data
df = pd.read_csv("monthly.txt", delim_whitespace=True)
df['Type'], df['Category'] = df['account'].str.split(':', 1).str
df = df.drop(df.index[0])
df = df.drop(['account'], axis=1)
df = df.rename(columns={'year':'Year','mo':'Month','total':'Net','cur':'Currency',})
df = df[['Year', 'Month', 'Type', 'Category', 'Net', 'Currency']]
df['Net'] = pd.to_numeric(df['Net'])
df['Net'] = df['Net'] * -1 # Make it normal
#Fix mortgage category
df.loc[df['Type'] == "Liabilities", ['Category']] = "Home"
df['Type'] = df['Type'].replace(['Liabilities'], 'Expenses')
df.head()
Out[29]:
In [32]:
# Summary Table
#exp_summary = pd.pivot_table(df.query('Type == ["Expense"]'), index=['Master Category'], columns=['Month'], values=['Net'], aggfunc=np.sum, fill_value=0)
summary = pd.pivot_table(df.query('Year == ["2017"]'), index=['Type','Category','Currency'], columns=['Month'], values=['Net'], aggfunc=np.sum, fill_value=0)
#summary['Total'] = summary.sum(axis=1) # adds total to categories
#summary.loc['Total']= summary.sum() # adds total row at bottom
summary
Out[32]: