In [1]:
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
In [2]:
f = pd.read_excel("Modified_Spend_Data.xlsx", skiprows = 6)
f = f.astype({'Supplier Parent Code': str,'Supplier Child Code' : str,'Month':str,'Year':str,'Spend':int})
f['Parent_Child'] = f[['Supplier Parent Code', 'Supplier Child Code']].apply(lambda x: '_'.join(x), axis=1)
f.head()
Out[2]:
Taxonomy Number Month Year Region Supplier Parent Code Supplier Child Code Spend Parent_Child
0 10.00.00.00 2016 / 01 2016 Region 1 L-1386722 C-S422 3073 L-1386722_C-S422
1 10.00.00.00 2016 / 01 2016 Region 1 L-1386722 CS306 1084 L-1386722_CS306
2 10.00.00.00 2016 / 01 2016 Region 1 L-1386720 10004661 1945 L-1386720_10004661
3 10.00.00.00 2016 / 01 2016 Region 1 L-1386720 C-S238 7692 L-1386720_C-S238
4 10.00.00.00 2016 / 01 2016 Region 1 L-1386722 C-S393 246 L-1386722_C-S393

Aggregation by month

In [3]:
m = f.groupby(['Parent_Child','Month']).agg({'Spend': 'sum'}).reset_index()
m.head(20)
Out[3]:
Parent_Child Month Spend
0 L-1386720_10004661 2016 / 01 1945
1 L-1386720_10004661 2016 / 02 5964
2 L-1386720_10004661 2016 / 03 2228
3 L-1386720_10004661 2016 / 04 1981
4 L-1386720_10004661 2016 / 05 20108
5 L-1386720_10004661 2016 / 06 9874
6 L-1386720_10004661 2016 / 07 2243
7 L-1386720_10004661 2016 / 08 6512
8 L-1386720_C-S238 2016 / 01 7692
9 L-1386720_C-S238 2016 / 02 7617
10 L-1386720_C-S238 2016 / 03 7612
11 L-1386720_C-S238 2016 / 04 7611
12 L-1386720_C-S238 2016 / 05 7611
13 L-1386720_C-S238 2016 / 06 7610
14 L-1386720_C-S238 2016 / 07 7608
15 L-1386720_C-S238 2016 / 08 5851
16 L-1386720_C-S309 2016 / 01 2361
17 L-1386720_C-S309 2016 / 02 2375
18 L-1386720_C-S309 2016 / 03 2371
19 L-1386720_C-S309 2016 / 04 2368
In [4]:
vendor = 'L-1386720_C-S238'  ##### change vendor name here

k = m.loc[m['Parent_Child'] == vendor] 
k.plot(x = 'Month',y ='Spend',figsize = (12,8),title = 'Result for vendor ' + vendor)
plt.xticks(np.arange(len(k)), k['Month'], rotation=90)
plt.show()

Aggregation by year

In [5]:
y = f.groupby(['Parent_Child','Year']).agg({'Spend': 'sum'}).reset_index()
y
Out[5]:
Parent_Child Year Spend
0 L-1386720_10004661 2016 50855
1 L-1386720_C-S238 2016 59212
2 L-1386720_C-S309 2016 18965
3 L-1386720_C70946 2016 4948
4 L-1386720_CS155 2016 46099
5 L-1386720_w 2016 8024
6 L-1386722_10054023 2016 15800
7 L-1386722_C-S393 2016 17472
8 L-1386722_C-S422 2016 8194
9 L-1386722_C75164 2016 11908
10 L-1386722_C90752 2016 4439
11 L-1386722_CS306 2016 21528
12 USA_BOS1287212046199_28721 2016 292
13 USA_BOS1287212046203_28721 2016 4366
14 USA_BOS1287212046205_28721 2016 2549219
15 USA_BOS1369982046254_36999 2016 438793
16 USA_BOS188082046195_8809 2016 4176520
In [6]:
ven = 'L-1386720_10004661'   ##### change vendor name here

j = y.loc[y['Parent_Child'] == ven] 
j.plot(x = 'Year',y ='Spend',kind = 'bar',figsize = (12,8),title = 'Result for vendor ' + ven)
plt.xticks(np.arange(len(j)), j['Year'], rotation=90)
plt.show()

Vendor Stats for a year

In [7]:
v = f.groupby(['Parent_Child','Year']).agg({'Spend': 'sum'})
v = v.sort_values(by=['Spend'], ascending = False).reset_index()
v
Out[7]:
Parent_Child Year Spend
0 USA_BOS188082046195_8809 2016 4176520
1 USA_BOS1287212046205_28721 2016 2549219
2 USA_BOS1369982046254_36999 2016 438793
3 L-1386720_C-S238 2016 59212
4 L-1386720_10004661 2016 50855
5 L-1386720_CS155 2016 46099
6 L-1386722_CS306 2016 21528
7 L-1386720_C-S309 2016 18965
8 L-1386722_C-S393 2016 17472
9 L-1386722_10054023 2016 15800
10 L-1386722_C75164 2016 11908
11 L-1386722_C-S422 2016 8194
12 L-1386720_w 2016 8024
13 L-1386720_C70946 2016 4948
14 L-1386722_C90752 2016 4439
15 USA_BOS1287212046203_28721 2016 4366
16 USA_BOS1287212046199_28721 2016 292
In [10]:
year = '2016'   ##### change year here

i = v.loc[v['Year'] == year] 
i = i.head(15)  #### change 10 to 25 here
i.plot(x = 'Parent_Child',y ='Spend',kind = 'bar',figsize = (10,8),title = 'Spend on top 15 Vendors for year ' + year)
plt.xticks(np.arange(len(i)), i['Parent_Child'], rotation=90)
plt.xlabel("Supplier in Parent_Child format")
plt.ylabel("Spend in Dollars ")
plt.tight_layout()
plt.savefig('Spend on top 15 Vendors for year ' + year+'.png')
plt.show()