#import libraries required for analysis
import pandas as pd
import numpy as np
from ebmdatalab import bq
#obtain overall data for formulary prescribing at Guildford and Waveney CCG
sql = """
select month, bnf_name, bnf_code, sum(quantity) as quantity, sum(actual_cost) as actual_cost
from `hscic.normalised_prescribing_standard`
where
bnf_code like '1404000H0%'
and month >='2011-04-01'
group by month, bnf_name, bnf_code
order by month
"""
flu_df = bq.cached_read(sql, csv_path='flu_df.csv')
flu_df.head()
C:\Users\racro\Anaconda3\lib\site-packages\pandas\io\gbq.py:108: FutureWarning: verbose is deprecated and will be removed in a future version. Set logging level in order to vary verbosity **kwargs)
month | bnf_name | bnf_code | quantity | actual_cost | |
---|---|---|---|---|---|
0 | 2011-04-01 | Intanza_Vac 15mcg/0.1ml Strain Pfs | 1404000H0BQABAI | 1399 | 11654.98 |
1 | 2011-04-01 | Inflexal V_Vac 0.5ml Pfs | 1404000H0BIAAAF | 17 | 95.92 |
2 | 2011-04-01 | Influenza_Vac Inact 9mcg/0.1ml Pfs | 1404000H0AAAHAH | 2 | 16.72 |
3 | 2011-04-01 | Begrivac_Vac 0.5ml Pfs | 1404000H0BGAAAF | 1130 | 6086.22 |
4 | 2011-04-01 | Fluvirin_Vac 0.5ml Pfs | 1404000H0BBAAAF | 34 | 173.81 |
grp_flu_df=flu_df.groupby('month').sum()
grp_flu_df['actual_cost'] = grp_flu_df['actual_cost'].map('£{:,.0f}'.format)
grp_flu_df.head(200)
quantity | actual_cost | |
---|---|---|
month | ||
2011-04-01 | 35819 | £213,769 |
2011-05-01 | 19270 | £111,343 |
2011-06-01 | 15716 | £89,109 |
2011-07-01 | 9090 | £53,687 |
2011-08-01 | 10258 | £60,034 |
2011-09-01 | 850455 | £5,005,017 |
2011-10-01 | 5827836 | £34,338,176 |
2011-11-01 | 2463149 | £14,504,938 |
2011-12-01 | 845111 | £4,970,248 |
2012-01-01 | 339537 | £1,994,701 |
2012-02-01 | 148304 | £869,428 |
2012-03-01 | 68750 | £401,236 |
2012-04-01 | 33947 | £193,163 |
2012-05-01 | 28119 | £168,794 |
2012-06-01 | 23619 | £131,924 |
2012-07-01 | 5757 | £32,806 |
2012-08-01 | 2989 | £16,264 |
2012-09-01 | 756942 | £4,552,599 |
2012-10-01 | 5647630 | £33,464,306 |
2012-11-01 | 2837006 | £16,698,334 |
2012-12-01 | 927877 | £5,457,099 |
2013-01-01 | 401230 | £2,343,758 |
2013-02-01 | 167973 | £980,340 |
2013-03-01 | 93941 | £547,317 |
2013-04-01 | 38608 | £225,166 |
2013-05-01 | 13406 | £78,281 |
2013-06-01 | 20751 | £124,039 |
2013-07-01 | 5781 | £34,879 |
2013-08-01 | 5574 | £32,981 |
2013-09-01 | 725934 | £4,379,893 |
... | ... | ... |
2016-07-01 | 18186 | £111,040 |
2016-08-01 | 21015 | £135,784 |
2016-09-01 | 2061808 | £12,665,664 |
2016-10-01 | 4752789 | £29,095,693 |
2016-11-01 | 1903130 | £11,621,805 |
2016-12-01 | 796246 | £4,855,039 |
2017-01-01 | 368551 | £2,253,340 |
2017-02-01 | 210495 | £1,287,904 |
2017-03-01 | 148903 | £897,161 |
2017-04-01 | 40050 | £244,332 |
2017-05-01 | 40878 | £254,693 |
2017-06-01 | 26002 | £159,409 |
2017-07-01 | 27915 | £167,995 |
2017-08-01 | 23677 | £142,066 |
2017-09-01 | 2592842 | £16,560,997 |
2017-10-01 | 4627952 | £29,609,255 |
2017-11-01 | 1752636 | £11,197,910 |
2017-12-01 | 708743 | £4,509,635 |
2018-01-01 | 465403 | £2,985,500 |
2018-02-01 | 220042 | £1,401,840 |
2018-03-01 | 135733 | £863,361 |
2018-04-01 | 57626 | £364,694 |
2018-05-01 | 48693 | £303,773 |
2018-06-01 | 18132 | £120,099 |
2018-07-01 | 15073 | £94,847 |
2018-08-01 | 10927 | £69,464 |
2018-09-01 | 1686491 | £14,290,031 |
2018-10-01 | 3661628 | £30,723,290 |
2018-11-01 | 2998311 | £25,629,831 |
2018-12-01 | 973069 | £8,133,543 |
93 rows × 2 columns