import pandas as pd
import plotly.graph_objs as go
from plotly.offline import init_notebook_mode, iplot

init_notebook_mode(connected=True)

pnl = pd.read_excel('data/pnl.xlsx')

pnl

pnl['expenses'] = pnl['cogs'] + pnl['opex']
pnl['net_profit'] = pnl['revenue'] - pnl['expenses']

data = [
    go.Bar(
        x=pnl['month'], 
        y=pnl['revenue'], 
        name='Revenue',
        marker={'color': '#3FC1C9'}
    ),    
    go.Bar(
        x=pnl['month'], 
        y=pnl['expenses'], 
        name='Expenses',
        marker={'color': '#95E1D3'}    
    ),
    go.Scatter(
        x=pnl['month'], 
        y=pnl['net_profit'],
        name='Net Profit',        
        marker={'color': '#393E46'}    
    )
]

layout = go.Layout(
    title='Profit and Loss - 2017', 
    xaxis={'title': 'Month'}, 
    yaxis={'title': 'Amount (£)'}
)

fig = go.Figure(data=data, layout=layout)

iplot(fig)

date_columns = ['issue_date', 'due_date', 'paid_date']

ar = pd.read_csv('data/debtors.csv', parse_dates=date_columns)

ar

totals = ar.groupby(['customer'], as_index=False).sum()

totals

data = [
    go.Pie(
        labels=totals['customer'], 
        values=totals['total_amount'],
        hoverinfo='label+percent', 
        textinfo='value',
        hole=0.4
    )
]

iplot(data)

ar['paid_amount'] = ar['total_amount'] - ar['amount_due']

ar

ar['due_month'] = ar['due_date'].map(lambda x: x.strftime('%Y-%m'))

ar

ar_monthly = ar.groupby(['due_month'], as_index=False).sum()

ar_monthly

data = [
    go.Bar(
        x=ar_monthly['due_month'],
        y=ar_monthly['paid_amount'],
        name='Paid Amount'
    ),
    go.Bar(
        x=ar_monthly['due_month'],
        y=ar_monthly['amount_due'],
        name='Unpaid Amount'
    )
]
    
layout = go.Layout(barmode='stack')

fig = go.Figure(data=data, layout=layout)

iplot(fig)

import datetime
import numpy as np

today = datetime.datetime(2018, 1, 1)

def due_by(row):
    due_days = max(0, (row['due_date'] - today).days)
    week_due = int(due_days / 7)
    return '< {} weeks'.format(week_due + 1)

ar['due_by'] = ar.apply(due_by, axis=1)

outstanding = ar[ar['amount_due'] > 0]

outstanding 

aged_debtors = pd.pivot_table(
    outstanding, 
    values='amount_due', 
    index=['customer'], 
    columns=['due_by'], 
    aggfunc=np.sum
).fillna(0)

aged_debtors.reset_index(inplace=True)

aged_debtors

total_due = outstanding.groupby('customer', as_index=False).sum()[['customer', 'amount_due']]

total_due

total_due.merge(aged_debtors, on='customer', how='left')