Estimated reading time: 24 minutes
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
month revenue cogs opex
0 2017-01-01 1416.45 456.71 253.93
1 2017-02-01 1143.79 368.74 212.61
2 2017-03-01 1170.74 401.54 409.30
3 2017-04-01 1295.67 433.87 125.72
4 2017-05-01 1365.70 433.21 469.99
5 2017-06-01 1783.54 539.93 643.53
6 2017-07-01 753.44 233.71 184.28
7 2017-08-01 1164.51 376.59 395.08
8 2017-09-01 3684.36 1168.46 1426.04
9 2017-10-01 3900.38 1008.98 1269.18
10 2017-11-01 9767.07 3133.63 5052.31
11 2017-12-01 15303.12 4914.47 4681.09
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
customer invoice_ref issue_date due_date paid_date amount_due total_amount
0 Marsoftwares INV-001 2017-01-01 2017-02-05 2017-03-15 0 1400
1 Moonlimited INV-002 2017-01-04 2017-02-08 2017-02-13 0 1700
2 Cubrews INV-003 2017-01-13 2017-02-17 2017-03-21 0 1600
3 Honeydustries INV-004 2017-01-14 2017-02-18 2017-03-01 0 4700
4 Ironmobile INV-005 2017-01-18 2017-02-22 2017-02-25 0 200
5 Happypaw INV-006 2017-01-27 2017-03-03 2017-03-18 0 5200
6 Marsoftwares INV-007 2017-01-30 2017-03-06 2017-03-31 0 800
7 Dreamedia INV-008 2017-01-31 2017-03-07 2017-05-06 0 3800
8 Ridgeco INV-009 2017-02-08 2017-03-15 2017-06-09 0 2500
9 Cubrews INV-010 2017-02-11 2017-03-18 2017-05-18 0 5200
10 Honeydustries INV-011 2017-02-21 2017-03-28 2017-04-09 0 5400
11 Arcanetime INV-012 2017-02-22 2017-03-29 2017-04-04 0 1600
12 Zeuslife INV-013 2017-02-27 2017-04-03 2017-04-11 0 5400
13 Wavecast INV-014 2017-03-07 2017-04-11 2017-04-28 0 3600
14 Moonlimited INV-015 2017-03-15 2017-04-19 2017-04-23 0 400
15 Betagate INV-016 2017-03-18 2017-04-22 2017-04-30 0 2200
16 Happypaw INV-017 2017-03-28 2017-05-02 2017-05-18 0 3300
17 Wavecast INV-018 2017-03-31 2017-05-05 2017-05-24 0 1400
18 Moonlimited INV-019 2017-04-04 2017-05-09 2017-05-10 0 1400
19 Zeuslife INV-020 2017-04-12 2017-05-17 2017-05-31 0 4200
20 Ridgeco INV-021 2017-04-21 2017-05-26 2017-07-30 0 5600
21 Betagate INV-022 2017-04-22 2017-05-27 2017-06-05 0 1600
22 Marsoftwares INV-023 2017-04-25 2017-05-30 2017-06-20 0 3600
23 Honeydustries INV-024 2017-04-29 2017-06-03 2017-06-13 0 5700
24 Ironmobile INV-025 2017-04-30 2017-06-04 2017-06-07 0 3600
25 Ridgeco INV-026 2017-05-05 2017-06-09 2017-08-30 0 2700
26 Moonlimited INV-027 2017-05-07 2017-06-11 2017-06-14 0 2800
27 Dreamedia INV-028 2017-05-17 2017-06-21 2017-08-20 0 2400
28 Happypaw INV-029 2017-05-24 2017-06-28 2017-07-17 0 1800
29 Moonlimited INV-030 2017-05-26 2017-06-30 2017-07-02 0 4400
... ... ... ... ... ... ... ...
45 Ironmobile INV-046 2017-08-14 2017-09-18 2017-09-20 0 500
46 Honeydustries INV-047 2017-08-21 2017-09-25 2017-10-08 0 4300
47 Dreamedia INV-048 2017-08-24 2017-09-28 2017-12-22 0 4400
48 Cubrews INV-049 2017-08-31 2017-10-05 2017-12-05 0 700
49 Moonlimited INV-050 2017-09-05 2017-10-10 2017-10-15 0 3300
50 Happypaw INV-051 2017-09-13 2017-10-18 2017-11-04 0 1400
51 Zeuslife INV-052 2017-09-22 2017-10-27 2017-11-04 0 1800
52 Betagate INV-053 2017-10-02 2017-11-06 2017-11-14 0 5400
53 Marsoftwares INV-054 2017-10-09 2017-11-13 2017-12-22 0 2800
54 Ironmobile INV-055 2017-10-12 2017-11-16 2017-11-16 0 4400
55 Ridgeco INV-056 2017-10-13 2017-11-17 2018-01-06 0 800
56 Wavecast INV-057 2017-10-16 2017-11-20 2017-12-05 0 1800
57 Cubrews INV-058 2017-10-26 2017-11-30 2018-01-13 0 700
58 Arcanetime INV-059 2017-10-27 2017-12-01 2017-12-06 0 1600
59 Marsoftwares INV-060 2017-10-30 2017-12-04 2018-01-06 0 5400
60 Honeydustries INV-061 2017-10-31 2017-12-05 2017-12-15 0 1800
61 Happypaw INV-062 2017-11-05 2017-12-10 2017-12-26 0 5800
62 Betagate INV-063 2017-11-15 2017-12-20 2017-12-29 0 5400
63 Moonlimited INV-064 2017-11-19 2017-12-24 2017-12-28 0 3200
64 Cubrews INV-065 2017-11-21 2017-12-26 NaT 4800 4800
65 Honeydustries INV-066 2017-11-29 2018-01-03 NaT 5500 5500
66 Cubrews INV-067 2017-12-06 2018-01-10 NaT 2000 5600
67 Ridgeco INV-068 2017-12-14 2018-01-18 NaT 4500 4500
68 Marsoftwares INV-069 2017-12-21 2018-01-25 NaT 4600 4600
69 Dreamedia INV-070 2017-12-23 2018-01-27 NaT 4000 5500
70 Arcanetime INV-071 2017-12-28 2018-02-01 NaT 5300 5300
71 Zeuslife INV-072 2017-12-31 2018-02-04 NaT 3700 3700
72 Wavecast INV-073 2018-01-05 2018-02-09 NaT 5400 5400
73 Honeydustries INV-074 2018-01-08 2018-02-12 NaT 3400 3400
74 Happypaw INV-075 2018-01-15 2018-02-19 NaT 1800 1800

75 rows × 7 columns

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

totals
customer amount_due total_amount
0 Arcanetime 5300 12400
1 Betagate 0 14600
2 Cubrews 6800 23900
3 Dreamedia 4000 16100
4 Happypaw 1800 20700
5 Honeydustries 8900 31600
6 Ironmobile 0 8900
7 Marsoftwares 4600 26800
8 Moonlimited 0 19400
9 Ridgeco 4500 18800
10 Wavecast 5400 14000
11 Zeuslife 3700 15700
data = [
    go.Pie(
        labels=totals['customer'], 
        values=totals['total_amount'],
        hoverinfo='label+percent', 
        textinfo='value',
        hole=0.4
    )
]

iplot(data)

Part 4

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

ar
customer invoice_ref issue_date due_date paid_date amount_due total_amount paid_amount
0 Marsoftwares INV-001 2017-01-01 2017-02-05 2017-03-15 0 1400 1400
1 Moonlimited INV-002 2017-01-04 2017-02-08 2017-02-13 0 1700 1700
2 Cubrews INV-003 2017-01-13 2017-02-17 2017-03-21 0 1600 1600
3 Honeydustries INV-004 2017-01-14 2017-02-18 2017-03-01 0 4700 4700
4 Ironmobile INV-005 2017-01-18 2017-02-22 2017-02-25 0 200 200
5 Happypaw INV-006 2017-01-27 2017-03-03 2017-03-18 0 5200 5200
6 Marsoftwares INV-007 2017-01-30 2017-03-06 2017-03-31 0 800 800
7 Dreamedia INV-008 2017-01-31 2017-03-07 2017-05-06 0 3800 3800
8 Ridgeco INV-009 2017-02-08 2017-03-15 2017-06-09 0 2500 2500
9 Cubrews INV-010 2017-02-11 2017-03-18 2017-05-18 0 5200 5200
10 Honeydustries INV-011 2017-02-21 2017-03-28 2017-04-09 0 5400 5400
11 Arcanetime INV-012 2017-02-22 2017-03-29 2017-04-04 0 1600 1600
12 Zeuslife INV-013 2017-02-27 2017-04-03 2017-04-11 0 5400 5400
13 Wavecast INV-014 2017-03-07 2017-04-11 2017-04-28 0 3600 3600
14 Moonlimited INV-015 2017-03-15 2017-04-19 2017-04-23 0 400 400
15 Betagate INV-016 2017-03-18 2017-04-22 2017-04-30 0 2200 2200
16 Happypaw INV-017 2017-03-28 2017-05-02 2017-05-18 0 3300 3300
17 Wavecast INV-018 2017-03-31 2017-05-05 2017-05-24 0 1400 1400
18 Moonlimited INV-019 2017-04-04 2017-05-09 2017-05-10 0 1400 1400
19 Zeuslife INV-020 2017-04-12 2017-05-17 2017-05-31 0 4200 4200
20 Ridgeco INV-021 2017-04-21 2017-05-26 2017-07-30 0 5600 5600
21 Betagate INV-022 2017-04-22 2017-05-27 2017-06-05 0 1600 1600
22 Marsoftwares INV-023 2017-04-25 2017-05-30 2017-06-20 0 3600 3600
23 Honeydustries INV-024 2017-04-29 2017-06-03 2017-06-13 0 5700 5700
24 Ironmobile INV-025 2017-04-30 2017-06-04 2017-06-07 0 3600 3600
25 Ridgeco INV-026 2017-05-05 2017-06-09 2017-08-30 0 2700 2700
26 Moonlimited INV-027 2017-05-07 2017-06-11 2017-06-14 0 2800 2800
27 Dreamedia INV-028 2017-05-17 2017-06-21 2017-08-20 0 2400 2400
28 Happypaw INV-029 2017-05-24 2017-06-28 2017-07-17 0 1800 1800
29 Moonlimited INV-030 2017-05-26 2017-06-30 2017-07-02 0 4400 4400
... ... ... ... ... ... ... ... ...
45 Ironmobile INV-046 2017-08-14 2017-09-18 2017-09-20 0 500 500
46 Honeydustries INV-047 2017-08-21 2017-09-25 2017-10-08 0 4300 4300
47 Dreamedia INV-048 2017-08-24 2017-09-28 2017-12-22 0 4400 4400
48 Cubrews INV-049 2017-08-31 2017-10-05 2017-12-05 0 700 700
49 Moonlimited INV-050 2017-09-05 2017-10-10 2017-10-15 0 3300 3300
50 Happypaw INV-051 2017-09-13 2017-10-18 2017-11-04 0 1400 1400
51 Zeuslife INV-052 2017-09-22 2017-10-27 2017-11-04 0 1800 1800
52 Betagate INV-053 2017-10-02 2017-11-06 2017-11-14 0 5400 5400
53 Marsoftwares INV-054 2017-10-09 2017-11-13 2017-12-22 0 2800 2800
54 Ironmobile INV-055 2017-10-12 2017-11-16 2017-11-16 0 4400 4400
55 Ridgeco INV-056 2017-10-13 2017-11-17 2018-01-06 0 800 800
56 Wavecast INV-057 2017-10-16 2017-11-20 2017-12-05 0 1800 1800
57 Cubrews INV-058 2017-10-26 2017-11-30 2018-01-13 0 700 700
58 Arcanetime INV-059 2017-10-27 2017-12-01 2017-12-06 0 1600 1600
59 Marsoftwares INV-060 2017-10-30 2017-12-04 2018-01-06 0 5400 5400
60 Honeydustries INV-061 2017-10-31 2017-12-05 2017-12-15 0 1800 1800
61 Happypaw INV-062 2017-11-05 2017-12-10 2017-12-26 0 5800 5800
62 Betagate INV-063 2017-11-15 2017-12-20 2017-12-29 0 5400 5400
63 Moonlimited INV-064 2017-11-19 2017-12-24 2017-12-28 0 3200 3200
64 Cubrews INV-065 2017-11-21 2017-12-26 NaT 4800 4800 0
65 Honeydustries INV-066 2017-11-29 2018-01-03 NaT 5500 5500 0
66 Cubrews INV-067 2017-12-06 2018-01-10 NaT 2000 5600 3600
67 Ridgeco INV-068 2017-12-14 2018-01-18 NaT 4500 4500 0
68 Marsoftwares INV-069 2017-12-21 2018-01-25 NaT 4600 4600 0
69 Dreamedia INV-070 2017-12-23 2018-01-27 NaT 4000 5500 1500
70 Arcanetime INV-071 2017-12-28 2018-02-01 NaT 5300 5300 0
71 Zeuslife INV-072 2017-12-31 2018-02-04 NaT 3700 3700 0
72 Wavecast INV-073 2018-01-05 2018-02-09 NaT 5400 5400 0
73 Honeydustries INV-074 2018-01-08 2018-02-12 NaT 3400 3400 0
74 Happypaw INV-075 2018-01-15 2018-02-19 NaT 1800 1800 0

75 rows × 8 columns

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

ar
customer invoice_ref issue_date due_date paid_date amount_due total_amount paid_amount due_month
0 Marsoftwares INV-001 2017-01-01 2017-02-05 2017-03-15 0 1400 1400 2017-02
1 Moonlimited INV-002 2017-01-04 2017-02-08 2017-02-13 0 1700 1700 2017-02
2 Cubrews INV-003 2017-01-13 2017-02-17 2017-03-21 0 1600 1600 2017-02
3 Honeydustries INV-004 2017-01-14 2017-02-18 2017-03-01 0 4700 4700 2017-02
4 Ironmobile INV-005 2017-01-18 2017-02-22 2017-02-25 0 200 200 2017-02
5 Happypaw INV-006 2017-01-27 2017-03-03 2017-03-18 0 5200 5200 2017-03
6 Marsoftwares INV-007 2017-01-30 2017-03-06 2017-03-31 0 800 800 2017-03
7 Dreamedia INV-008 2017-01-31 2017-03-07 2017-05-06 0 3800 3800 2017-03
8 Ridgeco INV-009 2017-02-08 2017-03-15 2017-06-09 0 2500 2500 2017-03
9 Cubrews INV-010 2017-02-11 2017-03-18 2017-05-18 0 5200 5200 2017-03
10 Honeydustries INV-011 2017-02-21 2017-03-28 2017-04-09 0 5400 5400 2017-03
11 Arcanetime INV-012 2017-02-22 2017-03-29 2017-04-04 0 1600 1600 2017-03
12 Zeuslife INV-013 2017-02-27 2017-04-03 2017-04-11 0 5400 5400 2017-04
13 Wavecast INV-014 2017-03-07 2017-04-11 2017-04-28 0 3600 3600 2017-04
14 Moonlimited INV-015 2017-03-15 2017-04-19 2017-04-23 0 400 400 2017-04
15 Betagate INV-016 2017-03-18 2017-04-22 2017-04-30 0 2200 2200 2017-04
16 Happypaw INV-017 2017-03-28 2017-05-02 2017-05-18 0 3300 3300 2017-05
17 Wavecast INV-018 2017-03-31 2017-05-05 2017-05-24 0 1400 1400 2017-05
18 Moonlimited INV-019 2017-04-04 2017-05-09 2017-05-10 0 1400 1400 2017-05
19 Zeuslife INV-020 2017-04-12 2017-05-17 2017-05-31 0 4200 4200 2017-05
20 Ridgeco INV-021 2017-04-21 2017-05-26 2017-07-30 0 5600 5600 2017-05
21 Betagate INV-022 2017-04-22 2017-05-27 2017-06-05 0 1600 1600 2017-05
22 Marsoftwares INV-023 2017-04-25 2017-05-30 2017-06-20 0 3600 3600 2017-05
23 Honeydustries INV-024 2017-04-29 2017-06-03 2017-06-13 0 5700 5700 2017-06
24 Ironmobile INV-025 2017-04-30 2017-06-04 2017-06-07 0 3600 3600 2017-06
25 Ridgeco INV-026 2017-05-05 2017-06-09 2017-08-30 0 2700 2700 2017-06
26 Moonlimited INV-027 2017-05-07 2017-06-11 2017-06-14 0 2800 2800 2017-06
27 Dreamedia INV-028 2017-05-17 2017-06-21 2017-08-20 0 2400 2400 2017-06
28 Happypaw INV-029 2017-05-24 2017-06-28 2017-07-17 0 1800 1800 2017-06
29 Moonlimited INV-030 2017-05-26 2017-06-30 2017-07-02 0 4400 4400 2017-06
... ... ... ... ... ... ... ... ... ...
45 Ironmobile INV-046 2017-08-14 2017-09-18 2017-09-20 0 500 500 2017-09
46 Honeydustries INV-047 2017-08-21 2017-09-25 2017-10-08 0 4300 4300 2017-09
47 Dreamedia INV-048 2017-08-24 2017-09-28 2017-12-22 0 4400 4400 2017-09
48 Cubrews INV-049 2017-08-31 2017-10-05 2017-12-05 0 700 700 2017-10
49 Moonlimited INV-050 2017-09-05 2017-10-10 2017-10-15 0 3300 3300 2017-10
50 Happypaw INV-051 2017-09-13 2017-10-18 2017-11-04 0 1400 1400 2017-10
51 Zeuslife INV-052 2017-09-22 2017-10-27 2017-11-04 0 1800 1800 2017-10
52 Betagate INV-053 2017-10-02 2017-11-06 2017-11-14 0 5400 5400 2017-11
53 Marsoftwares INV-054 2017-10-09 2017-11-13 2017-12-22 0 2800 2800 2017-11
54 Ironmobile INV-055 2017-10-12 2017-11-16 2017-11-16 0 4400 4400 2017-11
55 Ridgeco INV-056 2017-10-13 2017-11-17 2018-01-06 0 800 800 2017-11
56 Wavecast INV-057 2017-10-16 2017-11-20 2017-12-05 0 1800 1800 2017-11
57 Cubrews INV-058 2017-10-26 2017-11-30 2018-01-13 0 700 700 2017-11
58 Arcanetime INV-059 2017-10-27 2017-12-01 2017-12-06 0 1600 1600 2017-12
59 Marsoftwares INV-060 2017-10-30 2017-12-04 2018-01-06 0 5400 5400 2017-12
60 Honeydustries INV-061 2017-10-31 2017-12-05 2017-12-15 0 1800 1800 2017-12
61 Happypaw INV-062 2017-11-05 2017-12-10 2017-12-26 0 5800 5800 2017-12
62 Betagate INV-063 2017-11-15 2017-12-20 2017-12-29 0 5400 5400 2017-12
63 Moonlimited INV-064 2017-11-19 2017-12-24 2017-12-28 0 3200 3200 2017-12
64 Cubrews INV-065 2017-11-21 2017-12-26 NaT 4800 4800 0 2017-12
65 Honeydustries INV-066 2017-11-29 2018-01-03 NaT 5500 5500 0 2018-01
66 Cubrews INV-067 2017-12-06 2018-01-10 NaT 2000 5600 3600 2018-01
67 Ridgeco INV-068 2017-12-14 2018-01-18 NaT 4500 4500 0 2018-01
68 Marsoftwares INV-069 2017-12-21 2018-01-25 NaT 4600 4600 0 2018-01
69 Dreamedia INV-070 2017-12-23 2018-01-27 NaT 4000 5500 1500 2018-01
70 Arcanetime INV-071 2017-12-28 2018-02-01 NaT 5300 5300 0 2018-02
71 Zeuslife INV-072 2017-12-31 2018-02-04 NaT 3700 3700 0 2018-02
72 Wavecast INV-073 2018-01-05 2018-02-09 NaT 5400 5400 0 2018-02
73 Honeydustries INV-074 2018-01-08 2018-02-12 NaT 3400 3400 0 2018-02
74 Happypaw INV-075 2018-01-15 2018-02-19 NaT 1800 1800 0 2018-02

75 rows × 9 columns

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

ar_monthly
due_month amount_due total_amount paid_amount
0 2017-02 0 9600 9600
1 2017-03 0 24500 24500
2 2017-04 0 11600 11600
3 2017-05 0 21100 21100
4 2017-06 0 23400 23400
5 2017-07 0 11200 11200
6 2017-08 0 9900 9900
7 2017-09 0 15200 15200
8 2017-10 0 7200 7200
9 2017-11 0 15900 15900
10 2017-12 4800 28000 23200
11 2018-01 20600 25700 5100
12 2018-02 19600 19600 0
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 
customer invoice_ref issue_date due_date paid_date amount_due total_amount paid_amount due_month due_by
64 Cubrews INV-065 2017-11-21 2017-12-26 NaT 4800 4800 0 2017-12 < 1 weeks
65 Honeydustries INV-066 2017-11-29 2018-01-03 NaT 5500 5500 0 2018-01 < 1 weeks
66 Cubrews INV-067 2017-12-06 2018-01-10 NaT 2000 5600 3600 2018-01 < 2 weeks
67 Ridgeco INV-068 2017-12-14 2018-01-18 NaT 4500 4500 0 2018-01 < 3 weeks
68 Marsoftwares INV-069 2017-12-21 2018-01-25 NaT 4600 4600 0 2018-01 < 4 weeks
69 Dreamedia INV-070 2017-12-23 2018-01-27 NaT 4000 5500 1500 2018-01 < 4 weeks
70 Arcanetime INV-071 2017-12-28 2018-02-01 NaT 5300 5300 0 2018-02 < 5 weeks
71 Zeuslife INV-072 2017-12-31 2018-02-04 NaT 3700 3700 0 2018-02 < 5 weeks
72 Wavecast INV-073 2018-01-05 2018-02-09 NaT 5400 5400 0 2018-02 < 6 weeks
73 Honeydustries INV-074 2018-01-08 2018-02-12 NaT 3400 3400 0 2018-02 < 7 weeks
74 Happypaw INV-075 2018-01-15 2018-02-19 NaT 1800 1800 0 2018-02 < 8 weeks
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
due_by customer < 1 weeks < 2 weeks < 3 weeks < 4 weeks < 5 weeks < 6 weeks < 7 weeks < 8 weeks
0 Arcanetime 0.0 0.0 0.0 0.0 5300.0 0.0 0.0 0.0
1 Cubrews 4800.0 2000.0 0.0 0.0 0.0 0.0 0.0 0.0
2 Dreamedia 0.0 0.0 0.0 4000.0 0.0 0.0 0.0 0.0
3 Happypaw 0.0 0.0 0.0 0.0 0.0 0.0 0.0 1800.0
4 Honeydustries 5500.0 0.0 0.0 0.0 0.0 0.0 3400.0 0.0
5 Marsoftwares 0.0 0.0 0.0 4600.0 0.0 0.0 0.0 0.0
6 Ridgeco 0.0 0.0 4500.0 0.0 0.0 0.0 0.0 0.0
7 Wavecast 0.0 0.0 0.0 0.0 0.0 5400.0 0.0 0.0
8 Zeuslife 0.0 0.0 0.0 0.0 3700.0 0.0 0.0 0.0
total_due = outstanding.groupby('customer', as_index=False).sum()[['customer', 'amount_due']]

total_due
customer amount_due
0 Arcanetime 5300
1 Cubrews 6800
2 Dreamedia 4000
3 Happypaw 1800
4 Honeydustries 8900
5 Marsoftwares 4600
6 Ridgeco 4500
7 Wavecast 5400
8 Zeuslife 3700
total_due.merge(aged_debtors, on='customer', how='left')
customer amount_due < 1 weeks < 2 weeks < 3 weeks < 4 weeks < 5 weeks < 6 weeks < 7 weeks < 8 weeks
0 Arcanetime 5300 0.0 0.0 0.0 0.0 5300.0 0.0 0.0 0.0
1 Cubrews 6800 4800.0 2000.0 0.0 0.0 0.0 0.0 0.0 0.0
2 Dreamedia 4000 0.0 0.0 0.0 4000.0 0.0 0.0 0.0 0.0
3 Happypaw 1800 0.0 0.0 0.0 0.0 0.0 0.0 0.0 1800.0
4 Honeydustries 8900 5500.0 0.0 0.0 0.0 0.0 0.0 3400.0 0.0
5 Marsoftwares 4600 0.0 0.0 0.0 4600.0 0.0 0.0 0.0 0.0
6 Ridgeco 4500 0.0 0.0 4500.0 0.0 0.0 0.0 0.0 0.0
7 Wavecast 5400 0.0 0.0 0.0 0.0 0.0 5400.0 0.0 0.0
8 Zeuslife 3700 0.0 0.0 0.0 0.0 3700.0 0.0 0.0 0.0