<jupyter_start><jupyter_text>Littlefinger   -*Slyly telling you where your money goes*  Table of ContentsTODOReports:For both just US and UK and all transactions in GBP as it cost me:Master categories, table, graph:Income per monthExpenses per month  Boilerplate<jupyter_code>import pandas as pd
import numpy as np
from datetime import datetime, timedelta
from dateutil.parser import parse
import seaborn as sns
import matplotlib.pyplot as plt
from pandas.tseries.offsets import *

%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)<jupyter_output><empty_output><jupyter_text>Import data and set variables<jupyter_code>excel = pd.ExcelFile("C:\\Users\\aowd\OneDrive - Chevron\\Special Projects\\littlefinger\\Money1.2.xlsx")
uk = excel.parse('UK')
us = excel.parse('US')
funds = excel.parse('Investments')
prices = excel.parse('Funds')
forex = excel.parse('Forex')

now = datetime.now()
weekago = now - timedelta(7)
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
rows = ['Car', 'Entertainment', 'Fees', 'Food', 'Gift', 'Health', 'Home', 'Kids', 'NBC', 'Pets', 'Shopping', 'Travel']<jupyter_output><empty_output><jupyter_text>Preprocess Data<jupyter_code>def tidyxacts(df):
    """
    Fix formats of columns for transactions in dataframe df

    Parameters
    ----------
    df : Dataframe

    Returns
    -------
    Date formatted as Date
    Amounts as numeric
    Adds columns for year, month, quarter, week number
    Sets date as index
    """

    df['Date'] = pd.to_datetime(df['Date'])
    df['Inflow'] = pd.to_numeric(df['Inflow'])
    df['Outflow'] = pd.to_numeric(df['Outflow'])
    df['Net'] = pd.to_numeric(df['Net'])
    # df['Master Category'] = df['Master Category'].astype("category")
    # df['Sub Category'] = df['Sub Category'].astype("category")
    # df['Year'], df['Month'] = df['Date'].dt.year, df['Date'].dt.strftime('%b')
    df['Year'], df['Month'] = df['Date'].dt.year, df['Date'].dt.month
    df['Quarter'], df['Week'] = df['Date'].dt.quarter, df['Date'].dt.week
    df.set_index(['Date'], inplace=True)
   
def xact_type(df):
    """
    Adds transfer, expense, income category column to df  
    """
    def categorise(row):
        if row['Master Category'] == "Transfer":
            return 'Transfer'
        if row['Master Category'] == "Income":
            return 'Income'
        return 'Expense'

    df['Type'] = df.apply(lambda row: categorise(row), axis=1)

def summary_tables(df, currency, type):
    
    
tidyxacts(uk)
tidyxacts(us)
xact_type(uk)
xact_type(us)<jupyter_output><empty_output><jupyter_text>Create all transaction table in GBP<jupyter_code>forex.set_index(['Date'], inplace=True) # Set date to be index
forex = forex['Exchange Rate'] # Only keep exchange column
usex =  us.join(other=forex, how='outer')
usex['Exchange Rate'].fillna(method='ffill', inplace=True)
usex['Net GBP'] = usex['Net'] / usex['Exchange Rate']
usgbp = usex.drop(['Net', 'Exchange Rate'], axis=1)
usgbp.rename(columns={'Net GBP':'Net'}, inplace=True)
allgbp = pd.concat([uk, usgbp], axis=0)<jupyter_output><empty_output><jupyter_text>Results All Expenses in GBP<jupyter_code>summary = pd.pivot_table(allgbp['2017'].query('Type == ["Expense"]'), index=['Master Category'], columns=['Month'], values=['Net'], aggfunc=np.sum, fill_value=0)
summary.columns = months[:now.month] # Renames to months based off current month
summary = summary * -1 # We're just looking at expenses
summary['Total'] = summary.sum(axis=1) # adds total to categories
summary['Average'] = summary.iloc[:, :-2].mean(axis=1) # adds average column for categories, iloc to ignore total col, -2 to only have full months
summary.loc['Total']= summary.sum() # adds total row at bottom
summary['EOY'] = summary['Total'] + (summary['Average'] * (now.month - 1))
summary.style.format("£{:,.2f}")

# Expense per month Heatmap
# Ignoring housing and total rows - tend to mask the rest
summary.style.background_gradient(cmap=cm, subset=pd.IndexSlice[rows,summary.columns[:-2]], axis=1).format("£{:,.2f}").set_caption('Monthly Summary')<jupyter_output><empty_output><jupyter_text>Income<jupyter_code>income = pd.pivot_table(allgbp['2017'].query('Type == ["Income"]'), index=['Sub Category'], columns=['Month'], values=['Net'], aggfunc=np.sum, fill_value=0)
income.columns = months[:now.month] # Renames to months based off current month
income['Total'] = income.sum(axis=1) # adds total to categories
income['Average'] = income.iloc[:, :-2].mean(axis=1) # adds average column for categories, iloc to ignore total col, -2 to only have full months
income.loc['Total']= income.sum() # adds total row at bottom
income['EOY'] = income['Total'] + (income['Average'] * (now.month - 1))
income.style.format("£{:,.2f}")
# Savings Rate
1- summary.at['Total', 'EOY']/income.at['Total', 'EOY']<jupyter_output><empty_output><jupyter_text>USA Income<jupyter_code>usi = pd.pivot_table(us['2017'].query('Type == ["Income"]'), index=['Sub Category'], columns=['Month'], values=['Net'], aggfunc=np.sum, fill_value=0)
usi.columns = months[:now.month] # Renames to months based off current month
usi['Total'] = usi.sum(axis=1) # adds total to categories
usi['Average'] = usi.iloc[:, :-2].mean(axis=1) # adds average column for categories, iloc to ignore total col, -2 to only have full months
usi.loc['Total']= usi.sum() # adds total row at bottom
usi['EOY'] = usi['Total'] + (usi['Average'] * (now.month - 1))
usi.style.format("${:,.2f}")<jupyter_output><empty_output><jupyter_text>Expenses<jupyter_code>use = pd.pivot_table(us['2017'].query('Type == ["Expense"]'), index=['Master Category'], columns=['Month'], values=['Net'], aggfunc=np.sum, fill_value=0)
use.columns = months[:now.month] # Renames to months based off current month
use = use * -1
use['Total'] = use.sum(axis=1) # adds total to categories
use['Average'] = use.iloc[:, :-2].mean(axis=1) # adds average column for categories, iloc to ignore total col, -2 to only have full months
use.loc['Total']= use.sum() # adds total row at bottom
use['EOY'] = use['Total'] + (use['Average'] * (now.month - 1))
use.style.format("${:,.2f}")<jupyter_output><empty_output><jupyter_text>UK Income<jupyter_code>uki = pd.pivot_table(uk['2017'].query('Type == ["Income"]'), index=['Sub Category'], columns=['Month'], values=['Net'], aggfunc=np.sum, fill_value=0)
#uki.columns = months[:now.month] # Renames to months based off current month
uki.columns = months[:now.month - 1]
uki['Total'] = uki.sum(axis=1) # adds total to categories
uki['Average'] = uki.iloc[:, :-2].mean(axis=1) # adds average column for categories, iloc to ignore total col, -2 to only have full months
uki.loc['Total']= uki.sum() # adds total row at bottom
uki['EOY'] = uki['Total'] + (uki['Average'] * (now.month - 1))
uki.style.format("£{:,.2f}")<jupyter_output><empty_output><jupyter_text>Expenses<jupyter_code>uke = pd.pivot_table(uk['2017'].query('Type == ["Expense"]'), index=['Master Category'], columns=['Month'], values=['Net'], aggfunc=np.sum, fill_value=0)
uke.columns = months[:now.month] # Renames to months based off current month
uke = uke * -1
uke['Total'] = uke.sum(axis=1) # adds total to categories
uke['Average'] = uke.iloc[:, :-2].mean(axis=1) # adds average column for categories, iloc to ignore total col, -2 to only have full months
uke.loc['Total']= uke.sum() # adds total row at bottom
uke['EOY'] = uke['Total'] + (uke['Average'] * (now.month - 1))
uke.style.format("£{:,.2f}")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18556023145712783, 'fraction_numerical': 0.010783798001052078, 'mean_word_length': 5.556034482758621, 'pattern_counts': {'":': 2, '<': 41, '<?xml version=': 0, '>': 41, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '636756', 'license': 'MIT', 'n_tokens_mistral': 2703, 'n_tokens_neox': 2580, 'n_words': 641, 'path': 'littlefinger - notebook.ipynb', 'repo_name': 'alexjj/littlefinger', 'size': '7604'}