Multiperiod hledger-Style Reports in beancount: Pivoting a Table

#!/usr/bin/env python 
# Export beancount raw data to Pandas dataframe 
from beancount.loader import load_file
from beancount.query.query import run_query
from beancount.query.numberify import numberify_results
import os
import pandas as pd
import numpy as np

# Create an Example beancount Journal File FileName = "example.beancount"
if not os.path.isfile(FileName):
    os.system("bean-example > {}".format(FileName))      

# Load an Example beancount Journal File entries, _, opts = load_file(FileName)
# Main currency currency = opts["operating_currency"][0]

# Executing a BQL Query cols, rows = run_query(entries, opts, 
                       "SELECT account, YEAR(date) AS year, SUM(convert(position, '{}', date)) AS amount\  WHERE account ~ 'Expenses'\  OR account ~ 'Income'\  GROUP BY account, year ORDER BY account, year".format(currency)
                      )
cols, rows = numberify_results(cols, rows)

# Converting Result Rows to a Pandas Dataframe df = pd.DataFrame(rows, columns=[k[0] for k in cols])
df.rename(columns={"account": "Account", "year":"Year", "amount ({})".format(currency): "Amount ({})".format(currency)}, inplace=True)
df = df.astype({"Account": str, "Year": int, "Amount ({})".format(currency): np.float})
print(df[["Account", "Year", "Amount ({})".format(currency)]].fillna(0))

# Pivoting a Table by Year df = df.pivot_table(index="Account", columns=[ 'Year']).fillna(0).reset_index()
print(df)

# Creating Multi-Level Accounts n_levels = df["Account"].str.count(":").max() + 1
cols = ["Account_L{}".format(k) for k in range(n_levels)]
df[cols] = df["Account"].str.split(':', n_levels - 1, expand=True)
df = df.fillna('').drop(columns="Account", level=0).set_index(cols)
print(df)

# Aggregation at Different Account Levels print(df.groupby(["Account_L0", "Account_L1"]).sum())

from matplotlib import pyplot as plt
font = {'weight' : 'normal',
        'size'   : 12}
plt.rc('font', **font)
plt.rcParams["figure.figsize"] = (15, 5)

df_L0 = df.groupby(["Account_L0"]).sum()

# Invert the sign of the "Income" account. Negative income is counter-intuitive. df_L0.loc["Income"] = -df_L0.loc["Income"]
df_L0 = df_L0.transpose().reset_index()
df_L0.columns.name = "Account"
df_L0.plot.bar(x="Year", y=["Income", "Expenses"], ylabel=df_L0["level_0"][0], rot=0)
plt.show()

import squarify
df_L1 = df.groupby(["Account_L0", "Account_L1"]).sum()
tot = df_L1.sum(axis=1).to_frame()

# Treemap Plot of Expenses data = tot.loc["Expenses"].sort_values(by=0, ascending=False)

values = data.values
labels = data.index

width = 1
height = 0.5
values_norm = squarify.normalize_sizes(values, width, height)
rects = squarify.squarify(values_norm, 0, 0, width, height)

fig = plt.figure(figsize=(10, 10))
fig.suptitle('Expenses', x=0.5, y=0.55, fontsize=16)
axes = [fig.add_axes([rect['x'], rect['y'], rect['dx'], rect['dy'], ]) for rect in rects]

for ax, txt, color in zip(axes, labels, plt.cm.Pastel1.colors):
    ax.text(0.5, 0.5, txt, horizontalalignment='center', verticalalignment='center')
    ax.set_yticks([])
    ax.set_xticks([])
    ax.set_facecolor(color)    
plt.show()