{ "cells": [ { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "#import libraries required for analysis\n", "import pandas as pd\n", "import numpy as np\n", "from ebmdatalab import bq" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "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\n", " **kwargs)\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
monthbnf_namebnf_codequantityactual_cost
02011-04-01Intanza_Vac 15mcg/0.1ml Strain Pfs1404000H0BQABAI139911654.98
12011-04-01Inflexal V_Vac 0.5ml Pfs1404000H0BIAAAF1795.92
22011-04-01Influenza_Vac Inact 9mcg/0.1ml Pfs1404000H0AAAHAH216.72
32011-04-01Begrivac_Vac 0.5ml Pfs1404000H0BGAAAF11306086.22
42011-04-01Fluvirin_Vac 0.5ml Pfs1404000H0BBAAAF34173.81
\n", "
" ], "text/plain": [ " month bnf_name bnf_code \\\n", "0 2011-04-01 Intanza_Vac 15mcg/0.1ml Strain Pfs 1404000H0BQABAI \n", "1 2011-04-01 Inflexal V_Vac 0.5ml Pfs 1404000H0BIAAAF \n", "2 2011-04-01 Influenza_Vac Inact 9mcg/0.1ml Pfs 1404000H0AAAHAH \n", "3 2011-04-01 Begrivac_Vac 0.5ml Pfs 1404000H0BGAAAF \n", "4 2011-04-01 Fluvirin_Vac 0.5ml Pfs 1404000H0BBAAAF \n", "\n", " quantity actual_cost \n", "0 1399 11654.98 \n", "1 17 95.92 \n", "2 2 16.72 \n", "3 1130 6086.22 \n", "4 34 173.81 " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#obtain overall data for formulary prescribing at Guildford and Waveney CCG\n", "\n", "sql = \"\"\"\n", "select month, bnf_name, bnf_code, sum(quantity) as quantity, sum(actual_cost) as actual_cost\n", "from `hscic.normalised_prescribing_standard` \n", "where\n", "bnf_code like '1404000H0%'\n", "and month >='2011-04-01'\n", "group by month, bnf_name, bnf_code\n", "order by month\n", "\"\"\"\n", "flu_df = bq.cached_read(sql, csv_path='flu_df.csv')\n", "flu_df.head()" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [], "source": [ "grp_flu_df=flu_df.groupby('month').sum()\n", "grp_flu_df['actual_cost'] = grp_flu_df['actual_cost'].map('£{:,.0f}'.format)" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
quantityactual_cost
month
2011-04-0135819£213,769
2011-05-0119270£111,343
2011-06-0115716£89,109
2011-07-019090£53,687
2011-08-0110258£60,034
2011-09-01850455£5,005,017
2011-10-015827836£34,338,176
2011-11-012463149£14,504,938
2011-12-01845111£4,970,248
2012-01-01339537£1,994,701
2012-02-01148304£869,428
2012-03-0168750£401,236
2012-04-0133947£193,163
2012-05-0128119£168,794
2012-06-0123619£131,924
2012-07-015757£32,806
2012-08-012989£16,264
2012-09-01756942£4,552,599
2012-10-015647630£33,464,306
2012-11-012837006£16,698,334
2012-12-01927877£5,457,099
2013-01-01401230£2,343,758
2013-02-01167973£980,340
2013-03-0193941£547,317
2013-04-0138608£225,166
2013-05-0113406£78,281
2013-06-0120751£124,039
2013-07-015781£34,879
2013-08-015574£32,981
2013-09-01725934£4,379,893
.........
2016-07-0118186£111,040
2016-08-0121015£135,784
2016-09-012061808£12,665,664
2016-10-014752789£29,095,693
2016-11-011903130£11,621,805
2016-12-01796246£4,855,039
2017-01-01368551£2,253,340
2017-02-01210495£1,287,904
2017-03-01148903£897,161
2017-04-0140050£244,332
2017-05-0140878£254,693
2017-06-0126002£159,409
2017-07-0127915£167,995
2017-08-0123677£142,066
2017-09-012592842£16,560,997
2017-10-014627952£29,609,255
2017-11-011752636£11,197,910
2017-12-01708743£4,509,635
2018-01-01465403£2,985,500
2018-02-01220042£1,401,840
2018-03-01135733£863,361
2018-04-0157626£364,694
2018-05-0148693£303,773
2018-06-0118132£120,099
2018-07-0115073£94,847
2018-08-0110927£69,464
2018-09-011686491£14,290,031
2018-10-013661628£30,723,290
2018-11-012998311£25,629,831
2018-12-01973069£8,133,543
\n", "

93 rows × 2 columns

\n", "
" ], "text/plain": [ " quantity actual_cost\n", "month \n", "2011-04-01 35819 £213,769\n", "2011-05-01 19270 £111,343\n", "2011-06-01 15716 £89,109\n", "2011-07-01 9090 £53,687\n", "2011-08-01 10258 £60,034\n", "2011-09-01 850455 £5,005,017\n", "2011-10-01 5827836 £34,338,176\n", "2011-11-01 2463149 £14,504,938\n", "2011-12-01 845111 £4,970,248\n", "2012-01-01 339537 £1,994,701\n", "2012-02-01 148304 £869,428\n", "2012-03-01 68750 £401,236\n", "2012-04-01 33947 £193,163\n", "2012-05-01 28119 £168,794\n", "2012-06-01 23619 £131,924\n", "2012-07-01 5757 £32,806\n", "2012-08-01 2989 £16,264\n", "2012-09-01 756942 £4,552,599\n", "2012-10-01 5647630 £33,464,306\n", "2012-11-01 2837006 £16,698,334\n", "2012-12-01 927877 £5,457,099\n", "2013-01-01 401230 £2,343,758\n", "2013-02-01 167973 £980,340\n", "2013-03-01 93941 £547,317\n", "2013-04-01 38608 £225,166\n", "2013-05-01 13406 £78,281\n", "2013-06-01 20751 £124,039\n", "2013-07-01 5781 £34,879\n", "2013-08-01 5574 £32,981\n", "2013-09-01 725934 £4,379,893\n", "... ... ...\n", "2016-07-01 18186 £111,040\n", "2016-08-01 21015 £135,784\n", "2016-09-01 2061808 £12,665,664\n", "2016-10-01 4752789 £29,095,693\n", "2016-11-01 1903130 £11,621,805\n", "2016-12-01 796246 £4,855,039\n", "2017-01-01 368551 £2,253,340\n", "2017-02-01 210495 £1,287,904\n", "2017-03-01 148903 £897,161\n", "2017-04-01 40050 £244,332\n", "2017-05-01 40878 £254,693\n", "2017-06-01 26002 £159,409\n", "2017-07-01 27915 £167,995\n", "2017-08-01 23677 £142,066\n", "2017-09-01 2592842 £16,560,997\n", "2017-10-01 4627952 £29,609,255\n", "2017-11-01 1752636 £11,197,910\n", "2017-12-01 708743 £4,509,635\n", "2018-01-01 465403 £2,985,500\n", "2018-02-01 220042 £1,401,840\n", "2018-03-01 135733 £863,361\n", "2018-04-01 57626 £364,694\n", "2018-05-01 48693 £303,773\n", "2018-06-01 18132 £120,099\n", "2018-07-01 15073 £94,847\n", "2018-08-01 10927 £69,464\n", "2018-09-01 1686491 £14,290,031\n", "2018-10-01 3661628 £30,723,290\n", "2018-11-01 2998311 £25,629,831\n", "2018-12-01 973069 £8,133,543\n", "\n", "[93 rows x 2 columns]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "grp_flu_df.head(200)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.1" } }, "nbformat": 4, "nbformat_minor": 2 }