### here we import various libraries needed
import pandas as pd
import numpy as np
from ebmdatalab import bq
from ebmdatalab import charts
from ebmdatalab import maps
sql='''SELECT
presc.month,
pct,
ccg.org_type,
ccg.name,
presc.bnf_code,
presc.bnf_name,
quantity,
SUM(items) AS totalitems,
SUM(actual_cost) AS totalcost
FROM
ebmdatalab.hscic.normalised_prescribing_standard AS presc
JOIN
hscic.ccgs AS ccg
ON
presc.pct = ccg.code
LEFT JOIN
ebmdatalab.dmd.product AS dmd
ON
presc.bnf_code=dmd.bnf_code
WHERE
dmd.is_blacklisted = true
GROUP BY
month,
pct,
ccg.org_type,
ccg.name,
presc.bnf_code,
presc.bnf_name,
quantity'''
df_blacklist = bq.cached_read(sql, csv_path='df_blacklist.csv', use_cache=True) # add `use_cache=False` to override
df_blacklist['month'] = df_blacklist['month'].astype('datetime64[ns]')
df_blacklist.head()
month | pct | org_type | name | bnf_code | bnf_name | totalitems | totalcost | |
---|---|---|---|---|---|---|---|---|
0 | 2016-10-01 | 08L | CCG | NHS LEWISHAM CCG | 1310020N0BBABAB | Daktarin_Pdr 2% | 1 | 4.79000 |
1 | 2017-06-01 | 08N | CCG | NHS REDBRIDGE CCG | 0904010J0BLADAK | Doves Farm_G/F Wte Plain Flour | 3 | 49.46364 |
2 | 2013-05-01 | 02F | CCG | NHS WEST CHESHIRE CCG | 0904010ACBIACAC | Juvela_G/F W/FSpecial Flakes Red Berries | 1 | 6.32000 |
3 | 2015-09-01 | 02M | CCG | NHS FYLDE AND WYRE CCG | 090401080BPABBD | Barkat_G/F W/F Buckwheat Penne | 3 | 24.42000 |
4 | 2010-11-01 | 99A | CCG | NHS LIVERPOOL CCG | 090401080BFACBD | Orgran_G/F W/F Buckwheat Spiral | 3 | 24.60000 |
## ensuring the format is consistent for pounds and pence
pd.set_option('display.float_format', lambda x: '%.2f' % x)
### grouping the total cost by product
totalcost = df_blacklist.groupby(['bnf_name'])['totalcost'].sum()
totalcost.sort_values(ascending = False)
bnf_name Juvela_G/F Pizza Base 4886593.34 Glutafin_G/F W/F Spiral 3047528.63 Glutafin_G/F W/F Pizza Base 2573821.48 Juvela_G/F W/F Fusilli 2425069.24 Glutafin_G/F W/F Cracker 1574435.75 Glutafin_G/F W/F Spaghetti 1535744.97 Glutafin_G/F W/F Macaroni Penne 1336839.56 Juvela_G/F W/F Spaghetti 1234030.76 Glutafin_G/F W/F Crisp Bread 1017253.61 Glutafin_G/F W/F Diges Bisc 978959.28 Juvela_G/F Crispbread 965679.22 Glutafin_G/F W/F Pasta Shell 907448.15 Glutafin_G/F W/F H/Fbre Cracker 721688.97 Juvela_G/F W/F Fibre Flakes 679469.29 Juvela_G/F Tea Bisc 671812.88 Glutafin_G/F W/F Shortbread Bisc 612724.72 Juvela_G/F W/F Fibre Penne 601119.25 Juvela_G/F W/F Pure Oats 577397.08 Juvela_G/F Pizza Base 558852.71 Juvela_G/F Diges Bisc 547950.69 Daktarin_Crm 2% 530140.10 Glutafin_G/F W/F Bisc (Plain) 491963.14 Juvela_G/F W/F Fusilli 454565.69 Glutafin_G/F W/F Tea Bisc 453858.41 Juvela_G/F W/F Macaroni 414232.60 Juvela_G/F W/F Tagliatelle 403846.55 Nairn's_G/F W/F Oat Porridge 399310.12 Glutafin_G/F W/F Lasagne 394791.97 Glutafin_G/F W/F Tagliatelle Nest 390618.46 Glutafin_G/F W/F Mini Cracker 381421.57 ... Innovative Soln_PureG/FW/FRespectrumFlou 3.42 Vita-E_Crm 50u/g 3.41 Barkat_G/F W/F Ginger Cookie 3.38 Lamb_Natural Vit E Cap 400u 3.37 Relcofen_Tab 400mg 3.01 Olbas_Oil 2.98 Innovative Soln_Pure G/F W/F Baking Pdr 2.97 Orgran_G/F W/F Dinosaur W/Fruit Cookie 2.75 Orgran_G/F W/F Essent Fibre Rotondo Bisc 2.65 Covonia_Original Bronchial Balsam Soln 2.55 Barkat_G/F W/F Organ Muesli 2.43 Boots_Natur Senna Lax Tab 7.5mg 2.32 Fefol_Span Cap 2.32 Radian-B_Muscle Rub 2.31 Benerva_Tab 50mg 2.30 Ultra_G/F W/F Plain Cracker 2.29 Fenox_Nsl Dps 0.5% 2.28 Orgran_G/F W/F Toasted Corn Dippers 2.19 Tropium_Cap 10mg 2.17 Vicks_Inh 2.13 P.R._Heat A/Spy 150ml 1.91 Glutafin_G/F W/F Cust Crm Bisc 1.66 Ultra_G/F W/F Penne 1.58 DS_G/F W/F Pretzels 1.53 Snufflebabe_Vapour Rub 1.32 Disprin_Tab 300mg 1.07 Galpharm_Cold&Flu DecongestSpy 0.05%15ml 0.86 Mrs Crimble's_G/F W/F Cheese Bites Orig 0.79 Mrs Crimble's_G/F W/F Coconut Macaroons 0.70 Mrs Crimble's_G/F W/F MiniCheeseCrackers 0.42 Name: totalcost, Length: 745, dtype: float64
### grouping the total cost by CCG
totalcost = df_blacklist.groupby(['name'])['totalcost'].sum()
totalcost.sort_values(ascending = False)
name NHS BIRMINGHAM AND SOLIHULL CCG 1308535.63 NHS BRISTOL, NORTH SOMERSET AND SOUTH GLOUCESTERSHIRE CCG 1084260.04 NHS NORTHERN, EASTERN AND WESTERN DEVON CCG 1067852.87 NHS LEEDS CCG 947556.60 NHS DORSET CCG 945462.17 NHS SHEFFIELD CCG 787281.81 NHS SOUTHERN DERBYSHIRE CCG 665237.17 NHS WILTSHIRE CCG 644684.86 NHS WEST HAMPSHIRE CCG 620101.54 NHS EAST AND NORTH HERTFORDSHIRE CCG 604911.98 NHS COASTAL WEST SUSSEX CCG 583021.39 NHS GLOUCESTERSHIRE CCG 551635.54 NHS SOMERSET CCG 548373.81 NHS LIVERPOOL CCG 518927.41 NHS COVENTRY AND RUGBY CCG 488760.91 NHS HERTS VALLEYS CCG 485797.78 NHS SANDWELL AND WEST BIRMINGHAM CCG 458163.72 NHS NEWCASTLE GATESHEAD CCG 449393.93 NHS NORTH DERBYSHIRE CCG 447296.07 NHS CAMBRIDGESHIRE AND PETERBOROUGH CCG 438329.63 NHS WAKEFIELD CCG 385842.19 NHS WEST LEICESTERSHIRE CCG 379722.97 NHS MANCHESTER CCG 375703.79 NHS KERNOW CCG 364323.52 NHS BUCKINGHAMSHIRE CCG 359732.94 NHS WIGAN BOROUGH CCG 337555.22 NHS EAST LEICESTERSHIRE AND RUTLAND CCG 336851.58 NHS WOLVERHAMPTON CCG 330405.92 NHS BRADFORD DISTRICTS CCG 330269.96 NHS SWINDON CCG 320707.47 ... Northamptonshire Teaching 12.89 Tameside and Glossop 8.27 Manchester Teaching 8.21 Heart of Birmingham Teaching 6.78 Stockport 6.67 NORTH MIDLANDS COMMISSIONING HUB 5.94 Bristol 5.15 Wirral 5.13 South East Essex 5.13 Doncaster 3.55 Derby City 3.42 Middlesbrough 3.42 West Essex 3.42 Camden 3.41 Lewisham 3.40 Bradford and Airedale Teaching 3.39 Bedfordshire 3.39 SOUTH WEST COMMISSIONING HUB 1.86 Central and Eastern Cheshire 1.71 Trafford 1.71 Sheffield 1.71 Derbyshire County 1.71 Warwickshire 1.71 Hastings and Rother 1.71 Surrey 1.71 Leicester City 1.71 Mid Essex 1.71 Warrington 1.71 Worcestershire 1.71 Lincolnshire Teaching 1.64 Name: totalcost, Length: 255, dtype: float64
### see impact of NCSO in 2018
bl2018 = df_blacklist.loc[(df_blacklist["month"]>="2018-01-01") & (df_blacklist["month"]<="2018-12-01")]
bl2018
month | pct | org_type | name | bnf_code | bnf_name | totalitems | totalcost | |
---|---|---|---|---|---|---|---|---|
25 | 2018-07-01 | 06A | CCG | NHS WOLVERHAMPTON CCG | 0904010U0BIADAA | Barkat_G/F W/F Wte Rice Pizza Crust | 1 | 9.48 |
35 | 2018-03-01 | 14L | CCG | NHS MANCHESTER CCG | 0904010ACBKABAC | Glutafin_G/F W/F Fibre Flakes | 3 | 54.36 |
93 | 2018-06-01 | 09G | CCG | NHS COASTAL WEST SUSSEX CCG | 090401080BPAJBD | Barkat_G/F W/F Lasagne | 1 | 2.78 |
95 | 2018-11-01 | 01F | CCG | NHS HALTON CCG | 0904010Q0BJAAAQ | BiAlimenta_G/F Pasta Tubetti | 1 | 11.20 |
107 | 2018-01-01 | 05A | CCG | NHS COVENTRY AND RUGBY CCG | 0904010A0BKAEBF | Barkat_G/F W/F Crispbread | 1 | 5.82 |
119 | 2018-01-01 | 06K | CCG | NHS EAST AND NORTH HERTFORDSHIRE CCG | 090401080BFAMBD | Orgran_G/F W/F Rice & Corn Lasagne | 1 | 2.91 |
131 | 2018-09-01 | 15F | CCG | NHS LEEDS CCG | 090401070BGADAI | Innovative Soln_PureG/F W/F TapiocaFlour | 1 | 6.75 |
141 | 2018-09-01 | 08G | CCG | NHS HILLINGDON CCG | 0904010ACBKABAC | Glutafin_G/F W/F Fibre Flakes | 2 | 7.01 |
144 | 2018-05-01 | 07G | CCG | NHS THURROCK CCG | 0904010U0BIACAA | Barkat_G/F W/F Brown Rice Pizza Crust | 1 | 18.95 |
154 | 2018-05-01 | 07P | CCG | NHS BRENT CCG | 0904010Q0BJADAQ | BiAlimenta_G/F Pasta Penne | 3 | 73.75 |
172 | 2018-05-01 | 15F | CCG | NHS LEEDS CCG | 090401080BLAGBD | Juvela_G/F W/F Fibre Linguine | 3 | 15.49 |
196 | 2018-08-01 | 04N | CCG | NHS RUSHCLIFFE CCG | 090401070BGADAI | Innovative Soln_PureG/F W/F TapiocaFlour | 1 | 2.26 |
204 | 2018-01-01 | 02E | CCG | NHS WARRINGTON CCG | 090401070BGAFAI | Innovative Soln_PureG/FW/FBrownTeffFlour | 1 | 4.56 |
205 | 2018-08-01 | 07P | CCG | NHS BRENT CCG | 0904010A0BGAYBF | Orgran_G/F W/F Toasted Corn Crispibread | 1 | 9.26 |
216 | 2018-03-01 | 04K | CCG | NHS NOTTINGHAM CITY CCG | 090401080BFBABD | Orgran_G/F W/F Rice&Corn SpaghettiNoodle | 1 | 4.50 |
240 | 2018-10-01 | 07Y | CCG | NHS HOUNSLOW CCG | 090401070BGAGAI | Innovative Soln_PureG/F W/F WteTeffFlour | 1 | 4.75 |
262 | 2018-02-01 | 15F | CCG | NHS LEEDS CCG | 0904010A0BKAEBF | Barkat_G/F W/F Crispbread | 5 | 41.65 |
266 | 2018-09-01 | 08C | CCG | NHS HAMMERSMITH AND FULHAM CCG | 090401080BFAHBD | Orgran_G/F W/F Rice & Millet Spiral | 1 | 13.48 |
294 | 2018-10-01 | 99K | CCG | NHS HIGH WEALD LEWES HAVENS CCG | 090401070BGADAI | Innovative Soln_PureG/F W/F TapiocaFlour | 2 | 4.52 |
295 | 2018-11-01 | 08K | CCG | NHS LAMBETH CCG | 090401080BLAGBD | Juvela_G/F W/F Fibre Linguine | 2 | 59.62 |
359 | 2018-09-01 | 08E | CCG | NHS HARROW CCG | 1310020N0BBABAB | Daktarin_Pdr 2% | 1 | 8.22 |
393 | 2018-10-01 | 99H | CCG | NHS SURREY DOWNS CCG | 0904010A0BKAEBF | Barkat_G/F W/F Crispbread | 1 | 11.65 |
423 | 2018-02-01 | 09A | CCG | NHS CENTRAL LONDON (WESTMINSTER) CCG | 090401080BPAIBD | Barkat_G/F W/F Pasta Shells | 1 | 26.62 |
426 | 2018-02-01 | 07W | CCG | NHS EALING CCG | 090401080BPAGBD | Barkat_G/F W/F Tagliatelle | 1 | 5.56 |
429 | 2018-04-01 | 99A | CCG | NHS LIVERPOOL CCG | 090401080BPAJBD | Barkat_G/F W/F Lasagne | 2 | 31.08 |
443 | 2018-04-01 | 07J | CCG | NHS WEST NORFOLK CCG | 090401080BPAKBD | Barkat_G/F W/F Potato Pasta Gnocchi | 1 | 2.78 |
458 | 2018-05-01 | 02T | CCG | NHS CALDERDALE CCG | 090401080BPAKBD | Barkat_G/F W/F Potato Pasta Gnocchi | 1 | 2.78 |
477 | 2018-07-01 | 01T | CCG | NHS SOUTH SEFTON CCG | 0904010ACBKABAC | Glutafin_G/F W/F Fibre Flakes | 1 | 13.99 |
483 | 2018-06-01 | 05H | CCG | NHS WARWICKSHIRE NORTH CCG | 090401080BPABBD | Barkat_G/F W/F Buckwheat Penne | 1 | 2.78 |
506 | 2018-08-01 | 09A | CCG | NHS CENTRAL LONDON (WESTMINSTER) CCG | 090401080BPAIBD | Barkat_G/F W/F Pasta Shells | 1 | 26.64 |
... | ... | ... | ... | ... | ... | ... | ... | ... |
897236 | 2018-05-01 | 01Y | CCG | NHS TAMESIDE AND GLOSSOP CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 11 | 20.41 |
897238 | 2018-11-01 | 04E | CCG | NHS MANSFIELD AND ASHFIELD CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 6 | 10.21 |
897267 | 2018-07-01 | 05T | CCG | NHS SOUTH WORCESTERSHIRE CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 5 | 8.46 |
897294 | 2018-08-01 | 02P | CCG | NHS BARNSLEY CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 5 | 8.51 |
897308 | 2018-03-01 | 07M | CCG | NHS BARNET CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 16 | 32.29 |
897314 | 2018-06-01 | 05R | CCG | NHS SOUTH WARWICKSHIRE CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 9 | 22.04 |
897327 | 2018-10-01 | 01G | CCG | NHS SALFORD CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 59 | 120.65 |
897329 | 2018-09-01 | 07J | CCG | NHS WEST NORFOLK CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 10 | 20.34 |
897331 | 2018-12-01 | 07M | CCG | NHS BARNET CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 15 | 33.97 |
897332 | 2018-04-01 | 00C | CCG | NHS DARLINGTON CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 3 | 5.10 |
897333 | 2018-06-01 | 08Y | CCG | NHS WEST LONDON CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 8 | 23.74 |
897338 | 2018-01-01 | 05C | CCG | NHS DUDLEY CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 34 | 71.26 |
897340 | 2018-11-01 | 04V | CCG | NHS WEST LEICESTERSHIRE CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 7 | 15.23 |
897350 | 2018-03-01 | 09N | CCG | NHS GUILDFORD AND WAVERLEY CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 9 | 17.00 |
897355 | 2018-09-01 | 05T | CCG | NHS SOUTH WORCESTERSHIRE CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 1 | 1.69 |
897361 | 2018-05-01 | 05L | CCG | NHS SANDWELL AND WEST BIRMINGHAM CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 39 | 81.56 |
897363 | 2018-08-01 | 14L | CCG | NHS MANCHESTER CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 57 | 117.34 |
897369 | 2018-07-01 | 99F | CCG | NHS CASTLE POINT AND ROCHFORD CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 9 | 18.69 |
897408 | 2018-10-01 | 03V | CCG | NHS CORBY CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 1 | 1.70 |
897411 | 2018-04-01 | 09H | CCG | NHS CRAWLEY CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 5 | 8.50 |
897416 | 2018-10-01 | 11J | CCG | NHS DORSET CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 17 | 33.96 |
897436 | 2018-11-01 | 04G | CCG | NHS NENE CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 17 | 30.58 |
897441 | 2018-05-01 | 10V | CCG | NHS SOUTH EASTERN HAMPSHIRE CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 10 | 18.70 |
897447 | 2018-10-01 | 02Y | CCG | NHS EAST RIDING OF YORKSHIRE CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 15 | 30.56 |
897450 | 2018-02-01 | 15F | CCG | NHS LEEDS CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 8 | 15.28 |
897456 | 2018-04-01 | 04K | CCG | NHS NOTTINGHAM CITY CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 26 | 61.09 |
897458 | 2018-04-01 | 02X | CCG | NHS DONCASTER CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 9 | 16.99 |
897469 | 2018-10-01 | 14Y | CCG | NHS BUCKINGHAMSHIRE CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 33 | 64.58 |
897470 | 2018-04-01 | 09E | CCG | NHS CANTERBURY AND COASTAL CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 5 | 15.25 |
897482 | 2018-05-01 | 04Y | CCG | NHS CANNOCK CHASE CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 15 | 33.97 |
49804 rows × 8 columns
bl2018.sum()
totalitems 244708.00 totalcost 1798801.89 dtype: float64
### see impact since change of gf regs
bl_post_gfregs = df_blacklist.loc[(df_blacklist["month"]>="2018-12-01")]
bl_post_gfregs
month | pct | org_type | name | bnf_code | bnf_name | totalitems | totalcost | |
---|---|---|---|---|---|---|---|---|
2601 | 2018-12-01 | 15F | CCG | NHS LEEDS CCG | 090401070BGAGAI | Innovative Soln_PureG/F W/F WteTeffFlour | 1 | 4.75 |
3485 | 2018-12-01 | 02P | CCG | NHS BARNSLEY CCG | 1310020N0BBABAB | Daktarin_Pdr 2% | 1 | 2.75 |
4624 | 2019-01-01 | 07K | CCG | NHS WEST SUFFOLK CCG | 090401080BFAMBD | Orgran_G/F W/F Rice & Corn Lasagne | 1 | 8.72 |
5319 | 2019-01-01 | 05G | CCG | NHS NORTH STAFFORDSHIRE CCG | 0901020N0BBAAAB | Neo-Cytamen_'1000' Inj 1mg/ml 1ml Amp | 1 | 2.32 |
6232 | 2018-12-01 | 00D | CCG | NHS DURHAM DALES, EASINGTON AND SEDGEFIELD CCG | 090607000BBCJA0 | Orovite_High-Pot B & C Vit Tab | 1 | 2.08 |
6770 | 2018-12-01 | 04J | CCG | NHS NORTH DERBYSHIRE CCG | 0901020N0BBAAAB | Neo-Cytamen_'1000' Inj 1mg/ml 1ml Amp | 1 | 11.59 |
7005 | 2018-12-01 | 08N | CCG | NHS REDBRIDGE CCG | 0901020N0BBAAAB | Neo-Cytamen_'1000' Inj 1mg/ml 1ml Amp | 2 | 4.64 |
8415 | 2019-02-01 | 08X | CCG | NHS WANDSWORTH CCG | 0407010H0BFAGAQ | Panadol ActiFast Solb_Tab 500mg | 2 | 32.19 |
9755 | 2019-01-01 | 08R | CCG | NHS MERTON CCG | 0407010H0BFAGAQ | Panadol ActiFast Solb_Tab 500mg | 1 | 5.21 |
11002 | 2018-12-01 | 02R | CCG | NHS BRADFORD DISTRICTS CCG | 0906050P0BCABAB | Vita-E_Gels 200u | 1 | 4.74 |
11495 | 2019-02-01 | 09F | CCG | NHS EASTBOURNE, HAILSHAM AND SEAFORD CCG | 0407010H0BFAGAQ | Panadol ActiFast Solb_Tab 500mg | 1 | 26.01 |
11528 | 2019-02-01 | NI3 | Unknown | NaN | 0401020E0BBABAD | Librium_Cap 5mg | 1 | 2.71 |
12525 | 2018-12-01 | 09X | CCG | NHS HORSHAM AND MID SUSSEX CCG | 0304010D0BBACAA | Clarityn_Allergy Tab 10mg | 1 | 8.44 |
13464 | 2019-01-01 | 06H | CCG | NHS CAMBRIDGESHIRE AND PETERBOROUGH CCG | 0901020N0BBAAAB | Neo-Cytamen_'1000' Inj 1mg/ml 1ml Amp | 6 | 13.92 |
14038 | 2019-01-01 | 11A | CCG | NHS WEST HAMPSHIRE CCG | 091102000BBHWBA | Lamb_Co-Enzyme Q10 Cap 100mg | 1 | 3.69 |
15597 | 2018-12-01 | 02A | CCG | NHS TRAFFORD CCG | 0901020N0BBAAAB | Neo-Cytamen_'1000' Inj 1mg/ml 1ml Amp | 1 | 48.69 |
17889 | 2018-12-01 | 05R | CCG | NHS SOUTH WARWICKSHIRE CCG | 0901020N0BBAAAB | Neo-Cytamen_'1000' Inj 1mg/ml 1ml Amp | 9 | 20.88 |
19821 | 2018-12-01 | 14Y | CCG | NHS BUCKINGHAMSHIRE CCG | 0901020N0BBAAAB | Neo-Cytamen_'1000' Inj 1mg/ml 1ml Amp | 2 | 4.64 |
20751 | 2019-01-01 | 04Y | CCG | NHS CANNOCK CHASE CCG | 0901020N0BBAAAB | Neo-Cytamen_'1000' Inj 1mg/ml 1ml Amp | 6 | 13.92 |
21131 | 2019-01-01 | 03V | CCG | NHS CORBY CCG | 0106020M0BBAEAC | Senokot_Tab 7.5mg | 2 | 1.15 |
23193 | 2019-02-01 | 01K | CCG | NHS MORECAMBE BAY CCG | 0407010H0BFAGAQ | Panadol ActiFast Solb_Tab 500mg | 1 | 6.61 |
23596 | 2019-01-01 | 08R | CCG | NHS MERTON CCG | 0901020N0BBAAAB | Neo-Cytamen_'1000' Inj 1mg/ml 1ml Amp | 3 | 6.96 |
27560 | 2018-12-01 | 08L | CCG | NHS LEWISHAM CCG | 0407010H0BFAGAQ | Panadol ActiFast Solb_Tab 500mg | 2 | 41.61 |
27581 | 2018-12-01 | 99A | CCG | NHS LIVERPOOL CCG | 0304010D0BBACAA | Clarityn_Allergy Tab 10mg | 1 | 6.32 |
28503 | 2018-12-01 | 07W | CCG | NHS EALING CCG | 0407010H0BEASDJ | Calpol_Infant Susp 120mg/5ml Sach 5ml | 1 | 2.26 |
29836 | 2019-02-01 | 02Y | CCG | NHS EAST RIDING OF YORKSHIRE CCG | 0304010N0BFACAG | Nytol_Tab 25mg | 1 | 2.59 |
30264 | 2019-01-01 | 11J | CCG | NHS DORSET CCG | 0407010H0BFAGAQ | Panadol ActiFast Solb_Tab 500mg | 1 | 10.94 |
31388 | 2019-02-01 | 15C | CCG | NHS BRISTOL, NORTH SOMERSET AND SOUTH GLOUCEST... | 0901020D0BBABAD | Cytamen_'1000' Inj 1000mcg/ml 1ml Amp | 3 | 8.08 |
32609 | 2018-12-01 | 10Q | CCG | NHS OXFORDSHIRE CCG | 0401020E0BBACAE | Librium_Cap 10mg | 1 | 1.94 |
32967 | 2019-02-01 | 99N | CCG | NHS WILTSHIRE CCG | 0901020D0BCAAAE | Cytacon_Tab 50mcg | 2 | 18.70 |
... | ... | ... | ... | ... | ... | ... | ... | ... |
896249 | 2018-12-01 | 08E | CCG | NHS HARROW CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 6 | 13.59 |
896286 | 2019-02-01 | 08G | CCG | NHS HILLINGDON CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 9 | 17.01 |
896290 | 2019-02-01 | 08E | CCG | NHS HARROW CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 4 | 6.81 |
896303 | 2019-01-01 | 02F | CCG | NHS WEST CHESHIRE CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 7 | 20.36 |
896339 | 2019-02-01 | 11X | CCG | NHS SOMERSET CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 5 | 10.18 |
896364 | 2018-12-01 | 15C | CCG | NHS BRISTOL, NORTH SOMERSET AND SOUTH GLOUCEST... | 1310020N0BBAAAA | Daktarin_Crm 2% | 25 | 50.99 |
896424 | 2019-01-01 | 07H | CCG | NHS WEST ESSEX CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 16 | 28.90 |
896596 | 2019-01-01 | RYW | Unknown | NaN | 1310020N0BBAAAA | Daktarin_Crm 2% | 1 | 1.70 |
896614 | 2018-12-01 | 00D | CCG | NHS DURHAM DALES, EASINGTON AND SEDGEFIELD CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 8 | 13.61 |
896689 | 2018-12-01 | 01K | CCG | NHS MORECAMBE BAY CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 5 | 8.50 |
896714 | 2019-01-01 | NQ7 | Unknown | NaN | 1310020N0BBAAAA | Daktarin_Crm 2% | 2 | 3.40 |
896789 | 2018-12-01 | 03V | CCG | NHS CORBY CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 1 | 1.70 |
896824 | 2019-02-01 | 08H | CCG | NHS ISLINGTON CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 3 | 6.80 |
896833 | 2018-12-01 | NKB | Unknown | NaN | 1310020N0BBAAAA | Daktarin_Crm 2% | 1 | 1.70 |
896939 | 2018-12-01 | 09N | CCG | NHS GUILDFORD AND WAVERLEY CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 8 | 13.61 |
896941 | 2018-12-01 | 10V | CCG | NHS SOUTH EASTERN HAMPSHIRE CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 6 | 10.21 |
897055 | 2019-02-01 | 01V | CCG | NHS SOUTHPORT AND FORMBY CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 6 | 13.60 |
897183 | 2018-12-01 | 11E | CCG | NHS BATH AND NORTH EAST SOMERSET CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 1 | 3.39 |
897220 | 2019-02-01 | 07H | CCG | NHS WEST ESSEX CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 9 | 20.38 |
897227 | 2019-01-01 | 06N | CCG | NHS HERTS VALLEYS CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 35 | 69.68 |
897242 | 2019-02-01 | RFR | Unknown | NaN | 1310020N0BBAAAA | Daktarin_Crm 2% | 1 | 1.70 |
897261 | 2019-02-01 | 02Y | CCG | NHS EAST RIDING OF YORKSHIRE CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 6 | 11.89 |
897268 | 2019-01-01 | 08E | CCG | NHS HARROW CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 6 | 13.59 |
897276 | 2019-01-01 | 08Q | CCG | NHS SOUTHWARK CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 10 | 27.15 |
897311 | 2019-02-01 | 99N | CCG | NHS WILTSHIRE CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 3 | 8.48 |
897331 | 2018-12-01 | 07M | CCG | NHS BARNET CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 15 | 33.97 |
897372 | 2019-01-01 | 08K | CCG | NHS LAMBETH CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 9 | 17.00 |
897403 | 2019-02-01 | 09H | CCG | NHS CRAWLEY CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 3 | 5.11 |
897442 | 2019-01-01 | 05D | CCG | NHS EAST STAFFORDSHIRE CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 13 | 25.50 |
897486 | 2019-01-01 | 06Q | CCG | NHS MID ESSEX CCG | 1310020N0BBAAAA | Daktarin_Crm 2% | 11 | 20.36 |
1056 rows × 8 columns
bl_post_gfregs.groupby(['bnf_name'])['totalcost'].sum().sort_values(ascending = False)
bnf_name Daktarin_Crm 2% 14613.75 Panadol ActiFast Solb_Tab 500mg 1026.49 Neo-Cytamen_'1000' Inj 1mg/ml 1ml Amp 723.12 Juvela_G/F W/F Fusilli 294.75 Glutafin_G/F W/F Spiral 150.14 Vita-E_Gels 200u 141.55 Finasteride_Tab 1mg 117.48 Regaine For Men_Ex Strgh Foam Aero 5% 108.42 Orgran_G/F Self Raising Flour 95.09 Clarityn_Allergy Tab 10mg 94.69 Juvela_G/F W/F Spaghetti 87.12 Cobalin-H_Inj 1mg/ml 1ml Amp 81.25 Glutafin_G/F W/F Pizza Base 79.26 Glutafin_G/F Fibre Fusilli 68.86 Juvela_G/F Pizza Base 65.23 BiAlimenta_G/F Potato Pasta Gnocchi 63.63 Juvela_G/F W/F Cornflakes 58.12 Dermacolor_Fixier A/Spy 150ml 57.38 Juvela_G/F W/F Fibre Penne 55.29 Juvela_G/F W/F Fibre Flakes 54.29 Cytacon_Tab 50mcg 52.08 Orgran_G/F All Purpose Plain Flour 51.84 Juvela_G/F W/F Pure Oats 49.20 Senokot_Tab 7.5mg 40.74 Juvela_G/F Cracker 38.70 Glutafin_G/F W/F Cracker 38.61 Glutafin_G/F W/F Macaroni Penne 37.55 Glutafin_G/F W/F Spaghetti 31.29 Glutafin_G/F W/F Pasta Shell 31.28 Juvela_G/F W/F Crispy Rice Cereal 30.60 ... HealthAid_Vit E Cap Natur 1,000u 11.13 Nytol_Tab 25mg 9.93 Orgran_G/F W/F Rice & Corn Lasagne 8.72 Barkat_G/F W/F Porridge 8.64 Barkat_G/F W/F Buckwheat Penne 8.32 Glutafin_G/F W/F Mini Cracker 8.25 Lamb_Co-Enzyme Q10 Cap 100mg 7.37 Wellfoods_G/F W/F Flour Alternative 7.25 Juvela_G/F W/F Lasagne 6.86 Librium_Cap 10mg 6.09 Piriton Allergy_Tab 4mg 5.74 Barkat_G/F W/F Spiral 5.57 Barkat_G/F W/F Spaghetti 5.57 Orovite_High-Pot B & C Vit Tab 5.57 Earex_Ear Dps 5.45 Juvela_G/F W/F Flakes 5.18 Rizopia_G/F W/F Brown Rice Pasta Penne 5.06 Rizopia_G/F W/F Brown Rice Pasta Spaghet 5.06 Innovative Soln_PureG/F W/F WteTeffFlour 4.75 Innovative Soln_PureG/FW/FBrownTeffFlour 4.73 Glutafin_G/F W/F Fibre Flakes 4.67 Glutafin_G/F W/F Tea Bisc 3.89 Vicks_Sinex Micromist Nsl Spy 3.72 Juvela_G/F Savoury Bisc 3.56 Juvela_G/F Tea Bisc 2.84 Librium_Cap 5mg 2.71 Juvela_G/F Sweet Bisc 2.69 Calpol_Infant Susp 120mg/5ml Sach 5ml 2.26 Anthisan_Crm 2% 2.21 Innovative Soln_PureG/F W/F WteRiceFlour 1.67 Name: totalcost, Length: 79, dtype: float64