1. one-drug-database-analysis
  2. notebooks

Analysing the impact of the One Drug Database on OpenPrescribing

February 2020

The BSA publish the Detailed Prescribing Information dataset monthly, which contains the number of items prescribed of each presentation for each practice. In 2020 they are changing the underlying database which produces this dataset. This is called the One Drug Database project, and these changes will affect the Detailed Prescribing Information dataset that we use.

We expect to be affected by two changes.

Change 1: BNF code changes

There will be a rationalisation of BNF codes. In particular:

BNF codes will change for products (drugs and appliances) where the pack size is currently shown as part of the naming convention.

1501 presentations are affected by changed BNF codes.

However, it looks like there has been a much larger rationalisation of BNF codes.

Changes include:

Presentations affected by changed BNF codes account for 1.3% of items and 1.4% of net_cost in November 2019.

1% of presentations account for 55% of items and 29% of net_cost, and 10% of presentations account for 89% of items and 82% of net_cost.

The 10 presentations most affected by changes in items are shown here, and the 10 presentations most affected by changes in net_cost are shown here.

Change 2: special container quantity changes

Some special container packs currently include the pack size in the drug name, and the quantity field represents the number of packs. Once the ODD changes are applied:

the quantity will be represented as the total quantity (for example, the number of mls or gms), instead of the number of special container packs.

Presentations affected by changed BNF codes account for 1.5% of items and 3.2% of net_cost in November 2019.

1% of presentations account for 44% of items and 31% of net_cost, and 10% of presentations account for 87% of items and 84% of net_cost.

The 10 presentations most affected by changes in items are shown here, and the 10 presentations most affected by changes in net_cost are shown here.

Housekeeping

In [1]:
# Imports from the standard library
import json
from collections import Counter

# Imports from third-party packages
import pandas as pd
from ebmdatalab import bq
In [2]:
# Ensure that DataFrames columns are not truncated
pd.set_option('display.max_colwidth', -1)

Total items and net_cost for November 2019

In [3]:
# Query the prescribing data in BigQuery for the total items and net_cost 
sql = """
SELECT
    SUM(items) AS items,
    SUM(net_cost_pence) / 100 AS net_cost
FROM public_draft.prescribing
WHERE month = '2019-11-01'
"""

df = bq.cached_read(sql, csv_path='../bq-cache/2019_11_items_and_spending.csv')
df
Out[3]:
items net_cost
0 93549790 7.585725e+08
In [4]:
# Save these off for later -- we'll want to see what proprtion the items and net_cost are affected by the changes
total_items, total_net_cost = df.iloc[0]

Change 1: BNF code changes

Affected presentations

In [5]:
# Load the data from the spreadsheet
bnf_mapping_raw = pd.read_excel('../data/MDR BNF to dm+d BNF Mapping.xlsx')
bnf_mapping_raw.head()
Out[5]:
Current BNF Code MDR: BNF Description New BNF Code dm+d: BNF Description BNF Code Changed (Y/N)
0 0101010C0AAAAAA Alum Hydrox_Cap 475mg 0101010C0AAAAAA Aluminium hydroxide 475mg capsules N
1 0101010C0AAACAC Alum Hydrox_Oral Susp S/F 0101010C0AAACAC Aluminium hydroxide oral suspension sugar free N
2 0101010C0AAAMAM Alum Hydrox_Oral Susp 475mg/5ml 0101010C0AAAMAM Aluminium hydroxide 475mg/5ml oral suspension N
3 0101010C0BBAAAA Alu-Cap_Cap 475mg 0101010C0BBAAAA Alu-Cap 475mg capsules N
4 0101010E0AAAEAE Hydrotalcite_Oral Susp 500mg/5ml S/F 0101010E0AAAEAE Hydrotalcite 500mg/5ml oral suspension sugar free N
In [6]:
# Massage the data a bit, changing column names...
bnf_mapping = bnf_mapping_raw.rename(columns={
    'Current BNF Code': 'old_code',
    'MDR: BNF Description': 'old_name',
    'dm+d: BNF Description': 'new_name',
    'New BNF Code': 'new_code',
    'BNF Code Changed (Y/N)': 'changed'
})
# ...dropping rows where the code hasn't changed...
bnf_mapping = bnf_mapping[bnf_mapping['changed'] == 'Y']
# ...dropping rows where the old_code is in chapter 19...
bnf_mapping = bnf_mapping[~bnf_mapping['old_code'].str.startswith('19')]
# ...dropping rows where the new_code is in chapter 19...
bnf_mapping = bnf_mapping[~bnf_mapping['new_code'].str.startswith('19')]
# ...and sorting by old_code and new_code
bnf_mapping = bnf_mapping.sort_values(['old_code', 'new_code'])

print(f"There are {len(bnf_mapping)} changed records")
display(bnf_mapping.head())

There are 1946 changed records
old_code old_name new_code new_name changed
56 0101010N0AAAAAA Antacid/Oxetacaine_Oral Susp S/F 0101010N0AAABAB Antacid and Oxetacaine oral suspension Y
81 0101012B0AAAUAU Sod Bicarb_Liq Spec 420mg/5ml 0101012B0AAABAB Sodium bicarbonate 420mg/5ml (1mol/ml) soln sugar free Y
97 0101012B0AABVBV Sod Bicarb_Oral Soln 50mg/5ml (Old) 0101012B0AABXBX Sodium bicarbonate 50mg/5ml oral solution Y
87 0101012B0AABWBW Sod Bicarb_Oral Soln 420mg/5ml 0101012B0AAAUAU Sodium bicarbonate 420mg/5ml (1mmol/ml) oral liquid Y
100 0101012B0BKAABY Boots_Gripe Mix S/F 0101012B0AABYBY Sodium bicarbonate 50mg/5ml oral solution sugar free Y

Distribution of numbers of new presentations that are mapped to

(Is there a better way to express this?)

There are 1501 distinct old BNF codes:

In [7]:
bnf_mapping['old_code'].nunique()
Out[7]:
1501

There are 1418 old BNF codes map to a single new BNF code, 37 old BNF codes map to 2 new BNF codes... and 1 maps to 112 new BNF codes:

In [8]:
old_code_counts = Counter(bnf_mapping['old_code'])
old_code_count_distribution = sorted(Counter(old_code_counts.values()).items())
rows = [
    [num_old_codes, num_new_codes_mapped_to]
    for num_new_codes_mapped_to, num_old_codes in old_code_count_distribution
]
pd.DataFrame(rows, columns=['num_old_codes', 'num_new_codes_mapped_to'])
Out[8]:
num_old_codes num_new_codes_mapped_to
0 1418 1
1 37 2
2 41 3
3 1 5
4 2 57
5 1 100
6 1 112

Old presentations mapping to 2 new codes

In [9]:
old_codes_mapping_to_2_new_codes = [code for code, count in old_code_counts.items() if count == 2]

with pd.option_context('display.max_rows', None):
    display(bnf_mapping[bnf_mapping["old_code"].isin(old_codes_mapping_to_2_new_codes)])
old_code old_name new_code new_name changed
269 0102000L0AAAJAJ Glycopyrronium Brom_Liq Spec 500mcg/5ml 0102000L0AABHBH Glycopyrronium bromide 500micrograms/5ml oral solution Y
271 0102000L0AAAJAJ Glycopyrronium Brom_Liq Spec 500mcg/5ml 0102000L0AABIBI Glycopyrronium bromide 500micrograms/5ml oral suspension Y
1504 0204000H0AAAUAU Bisoprolol Fumar_OralSoln1.25mg/5ml(Old) 0204000H0AABIBI Bisoprolol 1.25mg/5ml oral suspension Y
1506 0204000H0AAAUAU Bisoprolol Fumar_OralSoln1.25mg/5ml(Old) 0204000H0AABJBJ Bisoprolol 1.25mg/5ml oral solution Y
1913 0205051L0AAAFAF Lisinopril_Liq Spec 20mg/5ml 0205051L0AAAYAY Lisinopril 20mg/5ml oral solution Y
1915 0205051L0AAAFAF Lisinopril_Liq Spec 20mg/5ml 0205051L0AAAZAZ Lisinopril 20mg/5ml oral suspension Y
2184 0206010F0AAAAAA Glyceryl Trinit_Subling Spy 400mcg(200D) 0206010F0AACGCG Glyceryl trinitrate 400micrograms/dose aerosol SL spy Y
2188 0206010F0AAAAAA Glyceryl Trinit_Subling Spy 400mcg(200D) 0206010F0AACICI Glyceryl trinitrate 400micrograms/dose pump sublingual spray Y
4108 0401010ADAABUBU Melatonin_Liq Spec 10mg/5ml 0401010ADAACDCD Melatonin 10mg/5ml oral solution Y
4110 0401010ADAABUBU Melatonin_Liq Spec 10mg/5ml 0401010ADAACECE Melatonin 10mg/5ml oral suspension Y
5561 0407010H0AAAAAA Paracet_Cap 500mg 0407010H0B3AKAA Boots Paracetamol 500mg capsules Y
5585 0407010H0AAAAAA Paracet_Cap 500mg 0407010H0BUAAAA Numark Paracetamol 500mg capsules Y
7631 0410030E0AAARAR Naltrexone HCl_Oral Soln 5mg/5ml (Old) 0410030E0AAATAT Naltrexone 5mg/5ml oral suspension Y
7635 0410030E0AAARAR Naltrexone HCl_Oral Soln 5mg/5ml (Old) 0410030E0AAAVAV Naltrexone 5mg/5ml oral solution Y
10719 070405000BBADA0 Invicorp-2_Inj Soln 1ml Amp 0704050ADAAAAAA Phentolamine 2mg/0.35ml / Aviptadil 25microg/0.35ml inj amp Y
10722 070405000BBADA0 Invicorp-2_Inj Soln 1ml Amp 0704050ADBBABAA Invicorp 2 solution for injection Y
12160 090401000BBIEA0 Maxijul Super Solb_Pdr Sach 132g 090401000AAANAN Generic Maxijul Super Soluble powder Y
12193 090401000BBIEA0 Maxijul Super Solb_Pdr Sach 132g 090401000BBCJAN Maxijul Super Soluble powder Y
12385 090401000BBMRA0 PKU Lophlex LQ_20 Liq (Berry) 090401000BBRPA0 PKU Lophlex LQ 20 liquid (6 flavours) Y
12426 090401000BBMRA0 PKU Lophlex LQ_20 Liq (Berry) 090401000BBTDA0 PKU Lophlex LQ 20 liquid citrus Y
12380 090401000BBNNA0 PKU Lophlex LQ_10 Liq (4 Flav) 090401000BBRLA0 PKU Lophlex LQ 10 liquid (5 flavours) Y
12425 090401000BBNNA0 PKU Lophlex LQ_10 Liq (4 Flav) 090401000BBTBA0 PKU Lophlex LQ 10 liquid citrus Y
12670 0904010U0AAAAAA G/F /W/F_Bread 0904010A0AABFBF Gluten free biscuits Y
12922 0904010U0AAAAAA G/F /W/F_Bread 0904010H0AAAAAA Gluten free bread Y
13273 090402000BBKLA0 Peptamen_Dripac-Flex Liq (Unflav) 090402000AAASAS Generic Peptamen liquid Y
13339 090402000BBKLA0 Peptamen_Dripac-Flex Liq (Unflav) 090402000BBIGA0 Peptamen liquid unflavoured Y
14106 090602400BBCGA0 Solgar_Vit B6 Cap 100mg (OLD) 0906024N0AABEBE Pyridoxine 100mg capsules Y
14138 090602400BBCGA0 Solgar_Vit B6 Cap 100mg (OLD) 0906024N0CKAABE Solgar Vitamin B6 100mg capsules Y
14122 0906024N0AABLBL Pyridox HCl_Liq Spec 100mg/5ml 0906024N0AACXCX Pyridoxine 100mg/5ml oral solution Y
14124 0906024N0AABLBL Pyridox HCl_Liq Spec 100mg/5ml 0906024N0AACYCY Pyridoxine 100mg/5ml oral suspension Y
14860 0906027G0AAABAB Vit B Co Strong_Tab 090607000AABBBB Multivitamin tablets Y
15497 0906027G0AAABAB Vit B Co Strong_Tab 091200000AAFTFT Multinutrient tablets Y
14394 0906040G0AAAUAU Colecal_Oral Susp 15,000u/5ml 0906040G0AADEDE Colecalciferol 5,000units/ml oral solution Y
14409 0906040G0AAAUAU Colecal_Oral Susp 15,000u/5ml 0906040G0AADSDS Colecalciferol 15,000units/5ml oral solution Y
16272 1001030U0AABTBT Methotrexate_Oral Soln 10mg/5ml 1001030U0AAAHAH Methotrexate 10mg/5ml oral liquid Y
16309 1001030U0AABTBT Methotrexate_Oral Soln 10mg/5ml 1001030U0AACFCF Methotrexate 2mg/ml oral solution sugar free Y
16485 1002010Q0AAAIAI Pyridostig Brom_Liq Spec 60mg/5ml 1002010Q0AABFBF Pyridostigmine bromide 60mg/5ml oral solution Y
16491 1002010Q0AAAIAI Pyridostig Brom_Liq Spec 60mg/5ml 1002010Q0AABIBI Pyridostigmine bromide 60mg/5ml oral suspension Y
17083 110801000BLAQA0 Blephaclean_Eye Lid Wipe (Drug) 110801000AAABAB Generic Lid-Care wipes Y
29069 110801000BLAQA0 Blephaclean_Eye Lid Wipe (Drug) 21300000850 Blephaclean wipes Y
19493 1313010S0BBAAA0 Zipzoc_Zn Ox Impregnated Medic Stkn(Old) 1313010S0AAAAAA Zinc oxide impregnated medicated stockings Y
19494 1313010S0BBAAA0 Zipzoc_Zn Ox Impregnated Medic Stkn(Old) 1313010S0BBABAA Zipzoc stockings Y
21503 20030300026 L-Mesitran Oint 50g Wound Dress H/Base Top Applic 20030300025 Mesitran ointment dressing Y
21505 20030300026 L-Mesitran Oint 50g Wound Dress H/Base Top Applic 20030300028 L-Mesitran ointment dressing Y
24709 20200000175 DermaSilk Brief Adult (Fle) XSml-Sml Silk Garment 20200000333 DermaSilk briefs female adult medium-large Y
24711 20200000175 DermaSilk Brief Adult (Fle) XSml-Sml Silk Garment 20200000334 DermaSilk briefs female adult extra large-XX large Y
25599 21020001000 Hollister InstantCath Protect Nelaton 8 (25-Pack) Cath 21020001002 InstantCath Protect catheter 14Ch Y
25601 21020001000 Hollister InstantCath Protect Nelaton 8 (25-Pack) Cath 21020001003 InstantCath Protect catheter 18Ch Y
17845 21220000118 Flexitol 10% Urea Crm 500g 1302010U0AAAFAF Urea 10% cream Y
26396 21220000118 Flexitol 10% Urea Crm 500g 21220000117 Flexitol 10% Urea cream Y
17727 21220000234 Cetraben Crm 150g 130201000AACFCF White soft paraffin 13.2% /Liquid paraffin light 10.5% cream Y
26454 21220000234 Cetraben Crm 150g 21220000233 Cetraben cream Y
17728 21220000235 Cetraben Crm 500g 130201000AACFCF White soft paraffin 13.2% /Liquid paraffin light 10.5% cream Y
26455 21220000235 Cetraben Crm 500g 21220000233 Cetraben cream Y
17729 21220000236 Cetraben Crm 1050g 130201000AACFCF White soft paraffin 13.2% /Liquid paraffin light 10.5% cream Y
26456 21220000236 Cetraben Crm 1050g 21220000233 Cetraben cream Y
17718 21220000252 Isomol Gel 500g 130201000AACACA Isopropyl myristate 15% / Liquid paraffin 15% gel Y
26476 21220000252 Isomol Gel 500g 21220000251 isomol gel Y
17720 21220000261 MyriBase Gel 500g 130201000AACACA Isopropyl myristate 15% / Liquid paraffin 15% gel Y
26486 21220000261 MyriBase Gel 500g 21220000260 MyriBase gel Y
17721 21220000263 AproDerm Gel 500g 130201000AACACA Isopropyl myristate 15% / Liquid paraffin 15% gel Y
26489 21220000263 AproDerm Gel 500g 21220000262 AproDerm gel Y
17891 21220000268 AquaDerm Aq Crm 130201100AAAMAM Aqueous cream Y
26491 21220000268 AquaDerm Aq Crm 21220000267 AquaDerm Aqueous cream Y
17705 21220000272 AquaDerm Liq Par 50%/WSP Oint 500g 130201000AAA6A6 White soft paraffin 50% / Liquid paraffin 50% ointment Y
26495 21220000272 AquaDerm Liq Par 50%/WSP Oint 500g 21220000271 AquaDerm Liquid Paraffin 50% in White Soft Paraffin ointment Y
17847 21220000279 Aquadrate Crm 10% 100g (App) 1302010U0AAAFAF Urea 10% cream Y
26504 21220000279 Aquadrate Crm 10% 100g (App) 21220000278 Aquadrate 10% cream Y
17849 21220000281 Hydromol Intensive Crm 10% 100g (App) 1302010U0AAAFAF Urea 10% cream Y
26507 21220000281 Hydromol Intensive Crm 10% 100g (App) 21220000280 Hydromol Intensive 10% cream Y
31640 23300263031 AMI_Suportx Ostomy/Hernia Supp/Belt 15cm+Hole Sml (75-90cm) 23300263864 Suportx ostomy belt with hole cutting, small 15cm black Y
31645 23300263031 AMI_Suportx Ostomy/Hernia Supp/Belt 15cm+Hole Sml (75-90cm) 23300263870 Suportx ostomy belt with hole cutting, small 15cm skin Y
31644 23300263036 AMI_Suportx Ostomy/Hernia Supp Belt 15cm + Hole (Bespoke) 23300263869 Suportx ostomy belt with hole cutting, made to measure 15cm black Y
31651 23300263036 AMI_Suportx Ostomy/Hernia Supp Belt 15cm + Hole (Bespoke) 23300263875 Suportx ostomy belt with hole cutting, made to measure 15cm skin Y

There are several patterns here that we can pick out:

Old "Liq Spec" presentation disaggregated into "oral solution" and "oral suspension" presentations

In [10]:
bnf_mapping[bnf_mapping["old_code"] == "0102000L0AAAJAJ"]
Out[10]:
old_code old_name new_code new_name changed
269 0102000L0AAAJAJ Glycopyrronium Brom_Liq Spec 500mcg/5ml 0102000L0AABHBH Glycopyrronium bromide 500micrograms/5ml oral solution Y
271 0102000L0AAAJAJ Glycopyrronium Brom_Liq Spec 500mcg/5ml 0102000L0AABIBI Glycopyrronium bromide 500micrograms/5ml oral suspension Y

Old brandend presentation disaggregated into branded and generic presentations

In [11]:
bnf_mapping[bnf_mapping["old_code"] == "070405000BBADA0"]
Out[11]:
old_code old_name new_code new_name changed
10719 070405000BBADA0 Invicorp-2_Inj Soln 1ml Amp 0704050ADAAAAAA Phentolamine 2mg/0.35ml / Aviptadil 25microg/0.35ml inj amp Y
10722 070405000BBADA0 Invicorp-2_Inj Soln 1ml Amp 0704050ADBBABAA Invicorp 2 solution for injection Y

Old presentation disaggregated into more accurately named presentations

In [12]:
bnf_mapping[bnf_mapping["old_code"] == "0906027G0AAABAB"]
Out[12]:
old_code old_name new_code new_name changed
14860 0906027G0AAABAB Vit B Co Strong_Tab 090607000AABBBB Multivitamin tablets Y
15497 0906027G0AAABAB Vit B Co Strong_Tab 091200000AAFTFT Multinutrient tablets Y

Branded appliances into branded presentation and generic presentation in chapter 13

In [13]:
bnf_mapping[bnf_mapping["old_code"] == "21220000234"]
Out[13]:
old_code old_name new_code new_name changed
17727 21220000234 Cetraben Crm 150g 130201000AACFCF White soft paraffin 13.2% /Liquid paraffin light 10.5% cream Y
26454 21220000234 Cetraben Crm 150g 21220000233 Cetraben cream Y

Old presentation mapping to 112 new codes

The old presentation covered a huge range of sizes, colours, and materials. There is now one presentation for each.

In [14]:
bnf_code = old_code_counts.most_common(1)[0][0]
bnf_mapping[bnf_mapping["old_code"] == bnf_code].head(10)
Out[14]:
old_code old_name new_code new_name changed
31251 23300263001 AMI_Suportx Hernia Support Girdles Fle Low Waist (Sml-XXLge) 23300263578 Suportx female low waist ostomy girdle small black Y
31253 23300263001 AMI_Suportx Hernia Support Girdles Fle Low Waist (Sml-XXLge) 23300263579 Suportx female low waist ostomy girdle medium black Y
31255 23300263001 AMI_Suportx Hernia Support Girdles Fle Low Waist (Sml-XXLge) 23300263580 Suportx female low waist ostomy girdle large black Y
31257 23300263001 AMI_Suportx Hernia Support Girdles Fle Low Waist (Sml-XXLge) 23300263581 Suportx female low waist ostomy girdle extra large black Y
31259 23300263001 AMI_Suportx Hernia Support Girdles Fle Low Waist (Sml-XXLge) 23300263582 Suportx female low waist ostomy girdle XX large black Y
31261 23300263001 AMI_Suportx Hernia Support Girdles Fle Low Waist (Sml-XXLge) 23300263583 Suportx female low waist ostomy girdle small skin Y
31263 23300263001 AMI_Suportx Hernia Support Girdles Fle Low Waist (Sml-XXLge) 23300263584 Suportx female low waist ostomy girdle medium skin Y
31265 23300263001 AMI_Suportx Hernia Support Girdles Fle Low Waist (Sml-XXLge) 23300263585 Suportx female low waist ostomy girdle large skin Y
31267 23300263001 AMI_Suportx Hernia Support Girdles Fle Low Waist (Sml-XXLge) 23300263586 Suportx female low waist ostomy girdle extra large skin Y
31269 23300263001 AMI_Suportx Hernia Support Girdles Fle Low Waist (Sml-XXLge) 23300263587 Suportx female low waist ostomy girdle XX large skin Y

Distribution of numbers of old presentations that are mapped from

(Again, is there a better way to express this?)

There are 1476 distinct new BNF codes:

In [15]:
bnf_mapping['new_code'].nunique()
Out[15]:
1476

There are 1270 new BNF codes that are mapped to from a single old BNF code, 132 new BNF codes that are mapped to from 2 old BNF codes... and 1 that is mapped to from 13 old BNF codes.

In [16]:
new_code_counts = Counter(bnf_mapping['new_code'])
new_code_count_distribution = sorted(Counter(new_code_counts.values()).items())
rows = [
    [num_new_codes, num_old_codes_mapped_from]
    for num_old_codes_mapped_from, num_new_codes in new_code_count_distribution
]
pd.DataFrame(rows, columns=['num_new_codes', 'num_old_codes_mapped_from'])
Out[16]:
num_new_codes num_old_codes_mapped_from
0 1270 1
1 132 2
2 30 3
3 14 4
4 12 5
5 4 6
6 6 7
7 2 8
8 1 13
9 1 14
10 1 19
11 1 23
12 1 24
13 1 31

New presentations mapped to from 2 new codes

In [17]:
new_codes_mapping_to_2_new_codes = [code for code, count in new_code_counts.items() if count == 2]

with pd.option_context('display.max_rows', None):
    display(bnf_mapping[bnf_mapping["new_code"].isin(new_codes_mapping_to_2_new_codes)].sort_values(['new_code', 'old_code']))
old_code old_name new_code new_name changed
130 0101021B0BEALAG Gaviscon Ex Strgh_Tab Chble 500mg (Lem) 0101021B0AAAGAG Generic Gaviscon 500mg chewable tablets sugar free Y
131 0101021B0BEAMAG Gaviscon Ex Strgh_Tab Chble 500mg(P/Mint 0101021B0AAAGAG Generic Gaviscon 500mg chewable tablets sugar free Y
224 0102000ACAAAPAP Atrop Sulf_Oral Susp 500mcg/5ml 0102000ACAABABA Atropine 500micrograms/5ml oral solution Y
226 0102000ACAABBBB Atrop Sulf_Oral Soln 500mcg/5ml (Old) 0102000ACAABABA Atropine 500micrograms/5ml oral solution Y
248 0102000L0AAAWAW Glycopyrronium Brom_Oral Soln 1mg/5ml 0102000L0AAADAD Glycopyrronium bromide 1mg/5ml oral liquid Y
249 0102000L0AAAXAX Glycopyrronium Brom_Oral Susp 1mg/5ml 0102000L0AAADAD Glycopyrronium bromide 1mg/5ml oral liquid Y
254 0102000L0AAAZAZ Glycopyrronium Brom_Oral Soln 2mg/5ml 0102000L0AAAIAI Glycopyrronium bromide 2mg/5ml oral liquid Y
255 0102000L0AABABA Glycopyrronium Brom_Oral Susp 2mg/5ml 0102000L0AAAIAI Glycopyrronium bromide 2mg/5ml oral liquid Y
721 0106010E0AAAHAH Ispag Husk_Gran Eff Sach 3.5g Orange S/F 0106010E0AAADAD Ispaghula husk 3.5g efferv gran sach gluten free sugar free Y
722 0106010E0AAANAN Ispag Husk_Gran Eff Sach 3.5g Lem S/F 0106010E0AAADAD Ispaghula husk 3.5g efferv gran sach gluten free sugar free Y
864 0106040M0AAACAC Macrogol_Co Oral Pdr Sach 6.9g (Plain) 0106040M0AAABAB Macrogol compound half-strength oral pdr sach NPF sugar free Y
865 0106040M0AAAGAG Macrogol_Co Oral Pdr Sach 6.9g (Choc)S/F 0106040M0AAABAB Macrogol compound half-strength oral pdr sach NPF sugar free Y
1222 0202020L0AADJDJ Furosemide_Oral Soln 5mg/5ml 0202020L0AACACA Furosemide 5mg/5ml oral liquid Y
1223 0202020L0AADKDK Furosemide_Oral Susp 5mg/5ml 0202020L0AACACA Furosemide 5mg/5ml oral liquid Y
1818 0205051F0AADVDV Captopril_Oral Soln 5mg/5ml 0205051F0AABNBN Captopril 5mg/5ml oral liquid Y
1819 0205051F0AADWDW Captopril_Oral Susp 5mg/5ml 0205051F0AABNBN Captopril 5mg/5ml oral liquid Y
1824 0205051F0AADXDX Captopril_Oral Soln 25mg/5ml 0205051F0AABWBW Captopril 25mg/5ml oral liquid Y
1825 0205051F0AADYDY Captopril_Oral Susp 25mg/5ml 0205051F0AABWBW Captopril 25mg/5ml oral liquid Y
1903 0205051L0AAAUAU Lisinopril_Oral Soln 5mg/5ml 0205051L0AAAGAG Lisinopril 5mg/5ml oral liquid Y
1904 0205051L0AAAVAV Lisinopril_Oral Susp 5mg/5ml 0205051L0AAAGAG Lisinopril 5mg/5ml oral liquid Y
1907 0205051L0AAAWAW Lisinopril_Oral Soln 2.5mg/5ml 0205051L0AAAIAI Lisinopril 2.5mg/5ml oral liquid Y
1908 0205051L0AAAXAX Lisinopril_Oral Susp 2.5mg/5ml 0205051L0AAAIAI Lisinopril 2.5mg/5ml oral liquid Y
1961 0205051R0AAAXAX Ramipril_Oral Soln 5mg/5ml 0205051R0AAAEAE Ramipril 5mg/5ml oral liquid Y
1962 0205051R0AAAYAY Ramipril_Oral Susp 5mg/5ml 0205051R0AAAEAE Ramipril 5mg/5ml oral liquid Y
2197 0206010F0BFADCI Nitrolingual_P/Spy 400mcg (180 D) 0206010F0BFABCJ Nitrolingual 400micrograms/dose pump sublingual spray Y
2198 0206010F0BFAECK Nitrolingual_P/Spy 400mcg (75 D) 0206010F0BFABCJ Nitrolingual 400micrograms/dose pump sublingual spray Y
2305 0206020A0AAAQAQ Amlodipine_Oral Soln 5mg/5ml 0206020A0AAACAC Amlodipine 5mg/5ml oral liquid Y
2306 0206020A0AAARAR Amlodipine_Oral Susp 5mg/5ml 0206020A0AAACAC Amlodipine 5mg/5ml oral liquid Y
2308 0206020A0AAAPAP Amlodipine_Oral Susp 10mg/5ml 0206020A0AAADAD Amlodipine 10mg/5ml oral liquid Y
2309 0206020A0AAASAS Amlodipine_Oral Soln 10mg/5ml 0206020A0AAADAD Amlodipine 10mg/5ml oral liquid Y
2875 0209000L0AAANAN Dipyridamole_Oral Soln 200mg/5ml 0209000L0AAAZAZ Dipyridamole 200mg/5ml oral suspension Y
2876 0209000L0AAAXAX Dipyridamole_Oral Susp 200mg/5ml (Old) 0209000L0AAAZAZ Dipyridamole 200mg/5ml oral suspension Y
3133 0301011R0AABYBY Salbutamol_Pdr For Inh 200mcg (100 D) 0301011R0AAAQAQ Salbutamol 200micrograms/dose dry powder inhaler Y
3134 0301011R0AACACA Salbutamol_Pdr For Inh 200mcg (200 D) 0301011R0AAAQAQ Salbutamol 200micrograms/dose dry powder inhaler Y
3774 0304030C0AAALAL Adren_Inj 1/1000 1ml Pfs (21 Gauge) 0304030C0AAAYAY Adrenaline (base) 1mg/1ml (1 in 1,000) inj pfs Y
3775 0304030C0AAAWAW Adren_Inj 1/1000 1ml Pfs (25 Gauge) 0304030C0AAAYAY Adrenaline (base) 1mg/1ml (1 in 1,000) inj pfs Y
4138 0401010ADAABXBX Melatonin_Oral Susp 5mg/5ml (Old) 0401010ADAADEDE Melatonin 5mg/5ml oral suspension Y
4139 0401010ADAADBDB Melatonin_Oral Susp 5mg/5ml 0401010ADAADEDE Melatonin 5mg/5ml oral suspension Y
4162 0401010B0AABUBU Chloral Hydrate_Oral Soln 200mg/5ml BP 0401010B0AABGBG Chloral hydrate 200mg/5ml oral liquid Y
4163 0401010B0AABVBV Chloral Hydrate_Oral Susp 200mg/5ml 0401010B0AABGBG Chloral hydrate 200mg/5ml oral liquid Y
4567 0402010J0AABLBL Haloperidol_Oral Soln 1mg/5ml 0402010J0AAA7A7 Haloperidol 1mg/5ml oral liquid Y
4568 0402010J0AABMBM Haloperidol_Oral Susp 1mg/5ml 0402010J0AAA7A7 Haloperidol 1mg/5ml oral liquid Y
4783 0403010B0AABHBH Amitriptyline HCl_Oral Soln 10mg/5ml 0403010B0AAA6A6 Amitriptyline 10mg/5ml oral liquid Y
4784 0403010B0AABIBI Amitriptyline HCl_Oral Susp 10mg/5ml 0403010B0AAA6A6 Amitriptyline 10mg/5ml oral liquid Y
5127 0404000L0AABKBK Dexamfet Sulf_Oral Soln 5mg/5ml 0404000L0AAAMAM Dexamfetamine 5mg/5ml oral liquid Y
5128 0404000L0AABLBL Dexamfet Sulf_Oral Susp 5mg/5ml 0404000L0AAAMAM Dexamfetamine 5mg/5ml oral liquid Y
5214 0404000R0AAADAD Modafinil_Oral Soln 100mg/5ml 0404000R0AAAGAG Modafinil 100mg/5ml oral suspension Y
5215 0404000R0AAAEAE Modafinil_Oral Susp 100mg/5ml (Old) 0404000R0AAAGAG Modafinil 100mg/5ml oral suspension Y
5871 0407020A0AABIBI Fentanyl_Nsl Spy 50mcg (20D) 0407020A0AABHBH Fentanyl 50micrograms/dose nasal spray Y
5872 0407020A0AABSBS Fentanyl_Nsl Spy 50mcg/0.1ml Ud (6D) 0407020A0AABHBH Fentanyl 50micrograms/dose nasal spray Y
5878 0407020A0AABMBM Fentanyl_Nsl Spy 200mcg (20D) 0407020A0AABLBL Fentanyl 200micrograms/dose nasal spray Y
5879 0407020A0AABQBQ Fentanyl_Nsl Spy 200mcg/0.1ml Ud (6D) 0407020A0AABLBL Fentanyl 200micrograms/dose nasal spray Y
5946 0407020A0BNAEBL Instanyl_Nsl Spy 200mcg (10D) 0407020A0BNAGBQ Instanyl 200micrograms/dose nasal spray Y
5947 0407020A0BNAFBM Instanyl_Nsl Spy 200mcg (20D) 0407020A0BNAGBQ Instanyl 200micrograms/dose nasal spray Y
5949 0407020A0BNACBJ Instanyl_Nsl Spy 100mcg (10D) 0407020A0BNAHBR Instanyl 100micrograms/dose nasal spray Y
5950 0407020A0BNADBK Instanyl_Nsl Spy 100mcg (20D) 0407020A0BNAHBR Instanyl 100micrograms/dose nasal spray Y
5952 0407020A0BNAABH Instanyl_Nsl Spy 50mcg (10D) 0407020A0BNAIBS Instanyl 50micrograms/dose nasal spray Y
5953 0407020A0BNABBI Instanyl_Nsl Spy 50mcg (20D) 0407020A0BNAIBS Instanyl 50micrograms/dose nasal spray Y
6501 0407042F0AAATAT Clonidine HCl_Oral Soln 50mcg/5ml (DT) 0407042F0AAAFAF Clonidine 50micrograms/5ml oral liquid Y
6502 0407042F0AAAUAU Clonidine HCl_Oral Susp 50mcg/5ml (DT) 0407042F0AAAFAF Clonidine 50micrograms/5ml oral liquid Y
6568 040801050AAAZAZ Topiramate_Oral Susp 100mg/5ml 040801050AACCCC Topiramate 100mg/5ml oral solution Y
6569 040801050AACACA Topiramate_Oral Soln 100mg/5ml (DT Old) 040801050AACCCC Topiramate 100mg/5ml oral solution Y
6917 0408010H0BBAHAC Lamictal_Tab 25mg (Monotherapy) 0408010H0BBACAC Lamictal 25mg tablets Y
6918 0408010H0BBAIAC Lamictal_Tab 25mg (Valpr) 0408010H0BBACAC Lamictal 25mg tablets Y
6939 0408010N0AAEUEU Phenobarb_Oral Soln 50mg/5ml 0408010N0AACLCL Phenobarbital 50mg/5ml oral liquid Y
6940 0408010N0AAEVEV Phenobarb_Oral Susp 50mg/5ml 0408010N0AACLCL Phenobarbital 50mg/5ml oral liquid Y
6995 0408010U0AAAAAA Primidone_Oral Susp 250mg/5ml (Old) 0408010U0AABCBC Primidone 250mg/5ml oral suspension Y
6996 0408010U0AAAMAM Primidone_Oral Susp 250mg/5ml (Spec) 0408010U0AABCBC Primidone 250mg/5ml oral suspension Y
7350 0409020C0AAAMAM Trihexyphenidyl HCl_Oral Soln 2mg/5ml 0409020C0AAALAL Trihexyphenidyl 2mg/5ml oral liquid Y
7351 0409020C0AAANAN Trihexyphenidyl HCl_Oral Susp 2mg/5ml 0409020C0AAALAL Trihexyphenidyl 2mg/5ml oral liquid Y
7454 0410020B0AABVBV Nicotine_Loz 1.5mg S/F (Cherry) 0410020B0AABPBP Nicotine 1.5mg lozenges sugar free Y
7455 0410020B0AACBCB Nicotine_Loz 1.5mg S/F (Orange) 0410020B0AABPBP Nicotine 1.5mg lozenges sugar free Y
8891 0601012D0AAAVAV Gppe Ins_Humulin M3 Humaject 3ml Pf Pen 0601012D0AABZBZ Insulin isoph biphas hum 30/70 100u/ml inj 3ml pf dispos dev Y
8892 0601012D0AABABA Ins Bip Isophane_30/70 100u/ml 3ml Cart 0601012D0AABZBZ Insulin isoph biphas hum 30/70 100u/ml inj 3ml pf dispos dev Y
10318 0702020H0AAABAB Econazole Nit_Pess 150mg + Applic 0702020H0AAAFAF Econazole 150mg pessaries Y
10319 0702020H0AAAEAE Econazole Nit_Pess 150mg + Applic 0702020H0AAAFAF Econazole 150mg pessaries Y
10610 0704020J0AAAIAI Oxybutynin HCl_Oral Soln 2.5mg/5ml (Old) 0704020J0AAAZAZ Oxybutynin 2.5mg/5ml oral solution Y
10611 0704020J0AAAKAK Oxybutynin HCl_Oral Susp 2.5mg/5ml 0704020J0AAAZAZ Oxybutynin 2.5mg/5ml oral solution Y
11831 0902012L0AABRBR Sod Chlor_Liq Spec 292.5mg/5ml 0902012L0AADDDD Sodium chloride 292.5mg/5ml (1mmol/ml) oral solution Y
11832 0902012L0AADCDC Sod Chlor_Oral Soln 292.5mg/5ml S/F 0902012L0AADDDD Sodium chloride 292.5mg/5ml (1mmol/ml) oral solution Y
11836 0902012L0AABCBC Sod Chlor_Liq Spec 1.46g/5ml 0902012L0AADHDH Sodium chloride 1.46g/5ml (5mmol/ml) oral solution Y
11837 0902012L0AADEDE Sod Chlor_Oral Soln 1.46g/5ml (DT Old) 0902012L0AADHDH Sodium chloride 1.46g/5ml (5mmol/ml) oral solution Y
11966 0902021S0AADBDB Glucose/Sod Chlor_I/V Inf5%/0.9%[email protected] 0902021S0AABDBD Sodium chloride 0.9% / Glucose 5% infusion 500ml bags Y
11967 0902021S0BDAIBD Steriflex_No3 Gluc5%/SodChlor.9%Inf500ml 0902021S0AABDBD Sodium chloride 0.9% / Glucose 5% infusion 500ml bags Y
12163 090401000BBJGA0 Calogen_Emuls (Sbery) 090401000AABABA Generic Calogen emulsion Y
12164 090401000BBLKA0 Calogen_Emuls (Banana) 090401000AABABA Generic Calogen emulsion Y
12169 090401000BBJFA0 Neocate Advance_Pdr Sach 100g (Unflav) 090401000AACHCH Neocate Advance powder sachets Y
12170 090401000BBLDA0 Neocate Advance_Pdr Sach 50g (Flav) 090401000AACHCH Neocate Advance powder sachets Y
12669 0904010I0AABJBJ G/F_Bisc 0904010A0AABFBF Gluten free biscuits Y
12670 0904010U0AAAAAA G/F /W/F_Bread 0904010A0AABFBF Gluten free biscuits Y
12867 090401060AAACAC G/F /L/P_Meals 0904010AQAAAAAA Low protein pasta Y
12869 0904010T0AAADAD G/F /L/P_Pasta 0904010AQAAAAAA Low protein pasta Y
13272 090402000BBCEA0 Peptamen_Liq Diet (Vanilla) 090402000AAASAS Generic Peptamen liquid Y
13273 090402000BBKLA0 Peptamen_Dripac-Flex Liq (Unflav) 090402000AAASAS Generic Peptamen liquid Y
13274 090402000BBDMA0 Seravit_Paed Mix Unflav 090402000AAATAT Generic Seravit Paediatric powder Y
13275 090402000BBDNA0 Seravit_Paed Mix P/Apple 090402000AAATAT Generic Seravit Paediatric powder Y
13334 090402000BBMJA0 Optifibre_Pdr Sach 8g 090402000BBHWA0 Optifibre powder Y
13335 090402000BBSKA0 Optifibre_Pdr Sach 10g 090402000BBHWA0 Optifibre powder Y
13601 090402000BBTFA0 Pro-Cal_Shot S/Pack 750ml 090402000BBWUA0 Pro-Cal shot starter pack Y
13602 090402000BBVZA0 Pro-Cal_Shot S/Pack 240ml 090402000BBWUA0 Pro-Cal shot starter pack Y
13804 0905013G0AABXBX Mag Glycerophos_Oral Soln97.2mg/5ml(Old) 0905013G0AADCDC Mag glycerophos (mag 97.2mg/5ml (4mmol/5ml)) soln Y
13806 0905013G0AADIDI Mag Glycerophos_Oral Soln 97.2mg/5ml 0905013G0AADCDC Mag glycerophos (mag 97.2mg/5ml (4mmol/5ml)) soln Y
13810 0905013G0AACVCV Mag Glycerophos_OralSoln121.25mg/5ml Old 0905013G0AADFDF Mag glycerophos (mag 121.25mg/5ml (5mmol/5ml)) soln Y
13812 0905013G0AADGDG Mag Glycerophos_OralSoln121.25mg/5mlSpec 0905013G0AADFDF Mag glycerophos (mag 121.25mg/5ml (5mmol/5ml)) soln Y
13935 090504100BBBRBB Lamb_Zn (Cit) Cap 50mg (8283) 090504100AABBBB Zinc citrate 50mg capsules Y
13936 090504100BBHDBB Vega_Zn Cit V/Cap 50mg 090504100AABBBB Zinc citrate 50mg capsules Y
13964 090504700BBDGBC Solgar_Selenium Tab 200mcg 090504700AABCBC Selenium 200microgram tablets Y
13965 090504700BBFDBC HealthAid_Selenium Tab 200mcg 090504700AABCBC Selenium 200microgram tablets Y
13967 090504700BBDNBJ Solgar_Selenium Tab 100mcg 090504700AABJBJ Selenium 100microgram tablets Y
13968 090504700BBDXA0 Cytoplan_Selenium Tab 100mcg 090504700AABJBJ Selenium 100microgram tablets Y
14279 0906031C0CHABBI BioCare_Vit C Cap 500mg 0906031C0AABIBI Ascorbic acid 500mg capsules Y
14280 0906031C0CJAABI Solgar_Vit C V/Cap 500mg 0906031C0AABIBI Ascorbic acid 500mg capsules Y
14368 0906040G0AACACA Colecal & Calc_Tab Chble 400u/1.5g (Lem) 0906040G0AABYBY Colecalciferol 400unit / Calcium carbonate 1.5g chewable tab Y
14369 0906040G0AACECE Colecal & Calc_Tab Chble 400u/1.5g 0906040G0AABYBY Colecalciferol 400unit / Calcium carbonate 1.5g chewable tab Y
14409 0906040G0AAAUAU Colecal_Oral Susp 15,000u/5ml 0906040G0AADSDS Colecalciferol 15,000units/5ml oral solution Y
14410 0906040G0AACMCM Colecal_Oral Soln 15,000u/5ml (Old) 0906040G0AADSDS Colecalciferol 15,000units/5ml oral solution Y
14754 0906050P0CPADA9 Natures Aid_Natur Vit ESoftgelsCap1,000u 0906050P0AAA9A9 Vitamin E 1,000unit capsules Y
14755 0906050P0CWAFA9 Solgar_Vit E Cap 1,000u 0906050P0AAA9A9 Vitamin E 1,000unit capsules Y
14765 0906050P0CWAAAF Solgar_Vit E Mixed Soft Gel 400u (268mg) 0906050P0AAAFAF Vitamin E 400unit capsules Y
14766 0906050P0CWABAF Solgar_Vit E Dry V/Cap 400u (268mg) 0906050P0AAAFAF Vitamin E 400unit capsules Y
15247 091101000BBPXFF Quest_L-Glutamine Cap 500mg 091101000AAFFFF Glutamine 500mg capsules Y
15248 091101000BBQPFF Solgar_L-Glutamine V/Cap 500mg 091101000AAFFFF Glutamine 500mg capsules Y
15255 091101000BBCHFP Lamb_L-Phenylalanine Cap 500mg (8321) 091101000AAFPFP L-Phenylalanine 500mg capsules Y
15256 091101000BBPLFP Quest_L-Phenylalanine Cap 500mg 091101000AAFPFP L-Phenylalanine 500mg capsules Y
15431 091200000BETEEE NatraHealth_Glucosam+Chond Cap 400/100mg 091200000AAEEEE Glucosamine sulfate 400mg / Chondroitin sulfate 100mg caps Y
15432 091200000BEWHEE BR Pharm_Glucosamine + Chondroitin Cap 091200000AAEEEE Glucosamine sulfate 400mg / Chondroitin sulfate 100mg caps Y
15452 091200000BEIPFE HealthAid_MSM Tab 1g 091200000AAFEFE Dimethyl sulfone 1g / Ascorbic acid 60mg tablets Y
15453 091200000BFSAFE H/Nature_MSM Tab 1000mg 091200000AAFEFE Dimethyl sulfone 1g / Ascorbic acid 60mg tablets Y
16272 1001030U0AABTBT Methotrexate_Oral Soln 10mg/5ml 1001030U0AAAHAH Methotrexate 10mg/5ml oral liquid Y
16273 1001030U0AABUBU Methotrexate_Oral Susp 10mg/5ml 1001030U0AAAHAH Methotrexate 10mg/5ml oral liquid Y
16416 1001050A0BFAGAB Natures Aid_Veget Glucosam HCl Tab 750mg 1001050A0AAABAB Glucosamine hydrochloride 750mg tablets Y
16417 1001050A0BKAAAB Health+Plus_Glucosamine HCl Tab 750mg 1001050A0AAABAB Glucosamine hydrochloride 750mg tablets Y
16784 1104010I0AAACAC Dexameth_Eye Dps 0.1% P/F (Old) 1104010I0AAAUAU Dexamethasone 0.1% eye drops preservative free Y
16785 1104010I0AAASAS Dexameth_Eye Dps 0.1% P/F (DT Old) 1104010I0AAAUAU Dexamethasone 0.1% eye drops preservative free Y
17039 1106000X0AAAAAA Piloc HCl_Eye Dps 0.5% (DT Old) 1106000X0AABPBP Pilocarpine hydrochloride 0.5% eye drops Y
17040 1106000X0AABGBG Piloc HCl_Eye Dps 0.5% P/F 1106000X0AABPBP Pilocarpine hydrochloride 0.5% eye drops Y
17133 1108010C0AAAEAE Acetylcy_Eye Dps 10% 1108010C0AABABA Acetylcysteine 10% eye drops preservative free Y
17134 1108010C0AAAPAP Acetylcy_Eye Dps 10% P/F (DT Old) 1108010C0AABABA Acetylcysteine 10% eye drops preservative free Y
17168 21300000191 Sai-Meds Hypromellose 0.5% Eye Dps 10ml 1108010F0AAABAB Hypromellose 0.5% eye drops Y
17169 21300000309 Teardew 0.5% Hypromellose Eye Dps 10ml 1108010F0AAABAB Hypromellose 0.5% eye drops Y
17175 21300000119 Tear-Lac Hypromellose Eye Dps 0.3% P/F 1108010F0AAANAN Hypromellose 0.3% eye drops preservative free (drug) Y
17176 21300000159 PF Drops Hypromellose 0.3% Eye Dps 10ml P/F 1108010F0AAANAN Hypromellose 0.3% eye drops preservative free (drug) Y
17198 1108010K0AACECE Sod Chlor_Eye Dps 5% P/F 1108010K0AAA6A6 Sodium chloride 5% eye drops preservative free Y
17199 21300000133 PF Drops Sod Chlor 5% Eye Dps 10ml P/F 1108010K0AAA6A6 Sodium chloride 5% eye drops preservative free Y
17346 1202010C0AAACAC Beclomet Diprop_Aq Nsl Spy 50mcg (100 D) 1202010C0AAAAAA Beclometasone 50micrograms/dose nasal spray Y
17347 1202010C0AAADAD Beclomet Diprop_Nsl Spy 50mcg (180 D) 1202010C0AAAAAA Beclometasone 50micrograms/dose nasal spray Y
17360 1202010I0AAADAD Budesonide_Aq Nsl Spy 100mcg (200D) 1202010I0AAACAC Budesonide 100micrograms/dose nasal spray Y
17361 1202010I0AAAGAG Budesonide_Aq Nsl Spy 100mcg (150D) 1202010I0AAACAC Budesonide 100micrograms/dose nasal spray Y
17520 1203030A0AAAAAA Dichlor Alc/Amylmet_Loz 1.2mg/600mcg 1203030A0AAAEAE Amylmetacresol 600microgram lozenges Y
17521 1203030A0BJAAAA Throaties_A-Bact Pastil(R/Currant&R/Hip) 1203030A0AAAEAE Amylmetacresol 600microgram lozenges Y
17621 1203040E0AAACAC Chlorhex Glucon_Mthwsh (Mint) 0.2% 1203040E0AAABAB Chlorhexidine gluconate 0.2% mouthwash Y
17622 1203040E0AAAGAG Chlorhex Glucon_Mthwsh (Aniseed) 0.2% 1203040E0AAABAB Chlorhexidine gluconate 0.2% mouthwash Y
17748 130201000AACPCP Liq Paraf 50%/Emulsif 50%_Oint (Old) 130201000AADMDM Emulsifying ointment 50% / Liquid paraffin 50% ointment Y
17750 1302010F0AAAPAP Emulsif Oint 50%/WSP 50%_Oint 130201000AADMDM Emulsifying ointment 50% / Liquid paraffin 50% ointment Y
17827 21220000133 Ovelle Emulsif Oint 500g (App) 1302010F0AAADAD Emulsifying ointment Y
17828 21220000137 EmulsifEss Oint 500g 1302010F0AAADAD Emulsifying ointment Y
17890 21220000267 AquaDerm Aq Crm 100g 130201100AAAMAM Aqueous cream Y
17891 21220000268 AquaDerm Aq Crm 130201100AAAMAM Aqueous cream Y
17943 130202000BBAZA0 Derma Shield_Skin Prote Mousse 150ml 130202000BBBAA0 Derma Shield skin protector mousse Y
17945 130202000BBBGA0 Derma Shield_Skin Prote Mousse 500ml 130202000BBBAA0 Derma Shield skin protector mousse Y
18226 1304000H0AACUCU Clobet/Oxytet/Nystatin_Crm0.05%/3%(Spec) 1304000H0AACACA Clobet 0.05%/Oxytetracycline 3% /Nystatin 100,000units/g crm Y
18227 1304000H0AACVCV Clobet/Oxytet/Nystatin_Crm 0.05%/3% (DT) 1304000H0AACACA Clobet 0.05%/Oxytetracycline 3% /Nystatin 100,000units/g crm Y
18302 1304000T0BBAHCD Haelan_Tape 7.5cm x 200cm 1304000T0BBAGCC Haelan 4micrograms/square cm tape 7.5cm Y
18303 1304000T0BBAICE Haelan_Tape 7.5cm x 20cm 1304000T0BBAGCC Haelan 4micrograms/square cm tape 7.5cm Y
18661 1305020S0AABQBQ Salic Acid 5%/WSP_Oint 1305020S0AACZCZ Salicylic acid 5% ointment in a base Y
18662 1305020S0AACBCB Salic Acid 5%/Emulsif_Oint 1305020S0AACZCZ Salicylic acid 5% ointment in a base Y
18873 1305020S0AABRBR Salic Acid 20%/WSP_Oint 1307000M0AABZBZ Salicylic acid 20% ointment Y
18874 1305020S0AACFCF Salic Acid 20%/Emulsif_Oint 1307000M0AABZBZ Salicylic acid 20% ointment Y
19134 1310020A0AAACAC Amorolfine HCl_Nail Lacquer Kit 5% 3ml 1310020A0AAAAAA Amorolfine 5% medicated nail lacquer Y
19135 1310020A0AAADAD Amorolfine HCl_Nail Lacquer Kit 5% 1310020A0AAAAAA Amorolfine 5% medicated nail lacquer Y
19541 140400030AAAEAE Hepat A_Vac 320u/ml 0.5ml Pfs 140400030AAADAD Hepatitis A vaccine (inactivated, adsorbed) inj 0.5ml pfs Y
19542 140400030AAAFAF Hepat A_Vac 50u/ml 0.5ml Pfs 140400030AAADAD Hepatitis A vaccine (inactivated, adsorbed) inj 0.5ml pfs Y
19612 1404000F0BMAAAX Repevax_Vac 0.5ml Pfs 1404000F0AAAXAX dTAP/IPV inj 0.5ml pfs Y
19613 1404000F0BPAAAX Boostrix-IPV_Inj 0.5ml Pfs 1404000F0AAAXAX dTAP/IPV inj 0.5ml pfs Y
19616 1404000F0BNAAA0 Infanrix-IPV_Vac 0.5ml Pfs 1404000F0AABBBB DTaP/IPV vacc (ads) inj 0.5ml pfs Y
19617 1404000F0BNABBB Infanrix-IPV + Hib_Vac C/Pk Pfs + Pdr Vl 1404000F0AABBBB DTaP/IPV vacc (ads) inj 0.5ml pfs Y
19891 1502010J0AABLBL Lido HCl_Antis Gel 2% (Liq) 1502010J0AABQBQ Lidocaine 2% and Chlorhexidine gel Y
19893 1502010J0AABRBR Lido HCl 2%/Chlorhex_Gel 11ml Pfs 1502010J0AABQBQ Lidocaine 2% and Chlorhexidine gel Y
20585 20010000102 Absorbent Cott Wool BP 100g 20010000101 Absorbent cotton BP 1988 Y
20586 20010000103 Absorbent Cott Wool BP 500g 20010000101 Absorbent cotton BP 1988 Y
21659 20030500010 IntraSite Gel 15g Wound Dress H/Gel Ster 20030500005 IntraSite Gel dressing Y
21660 20030500015 IntraSite Gel 25g Wound Dress H/Gel Ster 20030500005 IntraSite Gel dressing Y
21680 20030500101 Iodoflex Paste 10g Wound Dress H/Gel Ster 20030500100 Iodoflex paste dressing Y
21681 20030500102 Iodoflex Paste 17g Wound Dress H/Gel Ster 20030500100 Iodoflex paste dressing Y
21687 20030500121 Cutimed Gel 15g Wound Dress H/Gel Ster 20030500120 Cutimed Gel dressing Y
21688 20030500122 Cutimed Gel 25g Wound Dress H/Gel Ster 20030500120 Cutimed Gel dressing Y
22592 20031400061 Alhydran Crm 100ml Wound Dress 20031400060 Alhydran cream Y
22593 20031400062 Alhydran Crm 250ml Wound Dress 20031400060 Alhydran cream Y
23116 20031900046 Silgen Ag Spy 10ml Wound Dress Silver Ctd Barrier 20031900045 Silgen Ag spray Y
23117 20031900047 Silgen Ag Spy 25ml Wound Dress Silver Ctd Barrier 20031900045 Silgen Ag spray Y
23790 20070000110 Lint Absorbent 100g 20070000100 Absorbent lint BPC Y
23791 20070000120 Lint Absorbent 500g 20070000100 Absorbent lint BPC Y
23853 20090000480 Comfifast 5cm x 3m (Green) Stkntte Elasctd Viscose Tublr 20090000479 Comfifast stockinette 5cm Y
23854 20090000481 Comfifast 5cm x 5m (Green) Stkntte Elasctd Viscose Tublr 20090000479 Comfifast stockinette 5cm Y
23856 20090000483 Comfifast 7.5cm x 3m (Blue) Stkntte Elasctd Viscose Tublr 20090000482 Comfifast stockinette 7.5cm Y
23857 20090000484 Comfifast 7.5cm x 5m (Blue) Stkntte Elasctd Viscose Tublr 20090000482 Comfifast stockinette 7.5cm Y
23859 20090000486 Comfifast 10.75cm x 3m (Yellow)Stkntte Elasctd Viscose Tublr 20090000485 Comfifast stockinette 10.75cm Y
23860 20090000487 Comfifast 10.75cm x 5m (Yellow)Stkntte Elasctd Viscose Tublr 20090000485 Comfifast stockinette 10.75cm Y
23865 20090000492 Acti-Fast 2-Way Stch 5cm x 3m(Green) Stkntte Elasctd V/Tublr 20090000491 Acti-Fast 2-way stretch stockinette 5cm Y
23866 20090000493 Acti-Fast 2-Way Stch 5cm x 5m(Green) Stkntte Elasctd V/Tublr 20090000491 Acti-Fast 2-way stretch stockinette 5cm Y
23868 20090000495 Acti-Fast 2-Way Stch 7.5cmx3m (Blue) Stkntte Elasctd V/Tublr 20090000494 Acti-Fast 2-way stretch stockinette 7.5cm Y
23869 20090000496 Acti-Fast 2-Way Stch 7.5cmx5m (Blue) Stkntte Elasctd V/Tublr 20090000494 Acti-Fast 2-way stretch stockinette 7.5cm Y
23871 20090000498 Acti-Fast 2-Way Stch 10.75cmx3m(Yell)Stkntte Elasctd V/Tublr 20090000497 Acti-Fast 2-way stretch stockinette 10.75cm Y
23872 20090000499 Acti-Fast 2-Way Stch 10.75cmx5m(Yell)Stkntte Elasctd V/Tublr 20090000497 Acti-Fast 2-way stretch stockinette 10.75cm Y
23874 20090000502 Tubifast 2-Way Stch 5cm x 5m (Green) Stkntte Elasctd Viscose 20090000510 Tubifast 2-way stretch stockinette 5cm Y
23875 20090000506 Tubifast 2-Way Stch 5cm x 3m (Green) Stkntte Elasctd Viscose 20090000510 Tubifast 2-way stretch stockinette 5cm Y
23877 20090000503 Tubifast 2-Way Stch 7.5cm x 5m (Blue) Stkntte ElasctdViscose 20090000520 Tubifast 2-way stretch stockinette 7.5cm Y
23878 20090000507 Tubifast 2-Way Stch 7.5cm x 3m (Blue) Stkntte ElasctdViscose 20090000520 Tubifast 2-way stretch stockinette 7.5cm Y
23880 20090000504 Tubifast 2-Way Stch 10.75cm x 5m YellowStkntteElasctdViscose 20090000530 Tubifast 2-way stretch stockinette 10.75cm Y
23881 20090000508 Tubifast 2-Way Stch 10.75cm x 3m YellowStkntteElasctdViscose 20090000530 Tubifast 2-way stretch stockinette 10.75cm Y
23931 20090000806 Coverflex 5cm x 3m (Green) Stkntte Elasctd Viscose Tublr 20090000805 Coverflex stockinette 5cm Y
23932 20090000807 Coverflex 5cm x 5m (Green) Stkntte Elasctd Viscose Tublr 20090000805 Coverflex stockinette 5cm Y
23934 20090000811 Coverflex 7.5cm x 3m (Blue) Stkntte Elasctd Viscose Tublr 20090000810 Coverflex stockinette 7.5cm Y
23935 20090000812 Coverflex 7.5cm x 5m (Blue) Stkntte Elasctd Viscose Tublr 20090000810 Coverflex stockinette 7.5cm Y
23937 20090000816 Coverflex 10.75cm x 3m (Yellow)Stkntte Elasctd Viscose Tublr 20090000815 Coverflex stockinette 10.75cm Y
23938 20090000817 Coverflex 10.75cm x 5m (Yellow)Stkntte Elasctd Viscose Tublr 20090000815 Coverflex stockinette 10.75cm Y
23972 20090000906 Easifast 5cm x 3m (Green) Stkntte Elasctd Viscose Tublr 20090000905 Easifast stockinette 5cm Y
23973 20090000907 Easifast 5cm x 5m (Green) Stkntte Elasctd Viscose Tublr 20090000905 Easifast stockinette 5cm Y
23975 20090000911 Easifast 7.5cm x 3m (Blue) Stkntte Elasctd Viscose Tublr 20090000910 Easifast stockinette 7.5cm Y
23976 20090000912 Easifast 7.5cm x 5m (Blue) Stkntte Elasctd Viscose Tublr 20090000910 Easifast stockinette 7.5cm Y
23978 20090000916 Easifast 10.75cm x 3m(Yellow) Stkntte Elasctd Viscose Tublr 20090000915 Easifast stockinette 10.75cm Y
23979 20090000917 Easifast 10.75cm x 5m(Yellow) Stkntte Elasctd Viscose Tublr 20090000915 Easifast stockinette 10.75cm Y
23989 20090000937 CliniFast 5cm x 3m (Green) Stkntte Elasctd Viscose Tublr 20090000936 CliniFast stockinette 5cm Y
23990 20090000938 CliniFast 5cm x 5m (Green) Stkntte Elasctd Viscose Tublr 20090000936 CliniFast stockinette 5cm Y
23992 20090000940 CliniFast 7.5cm x 3m (Blue) Stkntte Elasctd Viscose Tublr 20090000939 CliniFast stockinette 7.5cm Y
23993 20090000941 CliniFast 7.5cm x 5m (Blue) Stkntte Elasctd Viscose Tublr 20090000939 CliniFast stockinette 7.5cm Y
23995 20090000946 CliniFast 10.75cm x 3m (Yellow) Stkntte Elasctd ViscoseTublr 20090000945 CliniFast stockinette 10.75cm Y
23996 20090000947 CliniFast 10.75cm x 5m (Yellow) Stkntte Elasctd ViscoseTublr 20090000945 CliniFast stockinette 10.75cm Y
24069 20090001026 Comfifast MultiStretch 2-way Stch 5cmx3m (Green) Stkntte 20090001025 Comfifast MultiStretch 2-way stretch stockinette 5cm Y
24070 20090001027 Comfifast MultiStretch 2-way Stch 5cmx5m (Green) Stkntte 20090001025 Comfifast MultiStretch 2-way stretch stockinette 5cm Y
24072 20090001031 Comfifast MultiStretch 2-way Stch 7.5cmx3m (Blue) Stkntte 20090001030 Comfifast MultiStretch 2-way stretch stockinette 7.5cm Y
24073 20090001032 Comfifast MultiStretch 2-way Stch 7.5cmx5m (Blue) Stkntte 20090001030 Comfifast MultiStretch 2-way stretch stockinette 7.5cm Y
24075 20090001036 Comfifast MultiStretch 2-way Stch 10.75cmx3m (Yell) Stkntte 20090001035 Comfifast MultiStretch 2-way stretch stockinette 10.75cm Y
24076 20090001037 Comfifast MultiStretch 2-way Stch 10.75cmx5m (Yell) Stkntte 20090001035 Comfifast MultiStretch 2-way stretch stockinette 10.75cm Y
24177 20090001172 Fortuna Fast 5cm x 3m (Green) Stkntte Elasctd Viscose Tublr 20090001171 Fortuna Fast stockinette 5cm Y
24178 20090001173 Fortuna Fast 5cm x 5m (Green) Stkntte Elasctd Viscose Tublr 20090001171 Fortuna Fast stockinette 5cm Y
24180 20090001175 Fortuna Fast 7.5cm x 3m (Blue) Stkntte Elasctd Viscose Tublr 20090001174 Fortuna Fast stockinette 7.5cm Y
24181 20090001176 Fortuna Fast 7.5cm x 5m (Blue) Stkntte Elasctd Viscose Tublr 20090001174 Fortuna Fast stockinette 7.5cm Y
24183 20090001178 Fortuna Fast 10.75cmx3m (Yellow)Stkntte Elasctd ViscoseTublr 20090001177 Fortuna Fast stockinette 10.75cm Y
24184 20090001179 Fortuna Fast 10.75cmx5m (Yellow)Stkntte Elasctd ViscoseTublr 20090001177 Fortuna Fast stockinette 10.75cm Y
24248 20090001249 Flexifast 7.5cm x 3m (Blue) Stkntte Elasctd Viscose Tublr 20090001248 Flexifast stockinette 7.5cm Y
24249 20090001250 Flexifast 7.5cm x 5m (Blue) Stkntte Elasctd Viscose Tublr 20090001248 Flexifast stockinette 7.5cm Y
24251 20090001252 Flexifast 10.75cm x 3m (Yellow)Stkntte Elasctd Viscose Tublr 20090001251 Flexifast stockinette 10.75cm Y
24252 20090001253 Flexifast 10.75cm x 5m (Yellow)Stkntte Elasctd Viscose Tublr 20090001251 Flexifast stockinette 10.75cm Y
26107 21140000013 OptiLube Ster Syrg Lubricant Gel 21140000015 OptiLube sterile lubricating jelly Y
26109 21140000016 OptiLube Ster Lubri Jelly 21140000015 OptiLube sterile lubricating jelly Y
26349 21200000170 Jobst FarrowWrap 4000 Sml Compress System 21200000169 Jobst FarrowWrap 4000 compression system Y
26350 21200000171 Jobst FarrowWrap 4000 Med Compress System 21200000169 Jobst FarrowWrap 4000 compression system Y
26473 21220000249 Elave Intensive Crm 125g 21220000248 Elave Intensive cream Y
26474 21220000250 Elave Intensive Crm 500g 21220000248 Elave Intensive cream Y
26486 21220000261 MyriBase Gel 500g 21220000260 MyriBase gel Y
26487 21220000310 MyriBase Gel 500ml 21220000260 MyriBase gel Y
26502 1302010U0BBAAAF Aquadrate_Crm 10% (Drug) 21220000278 Aquadrate 10% cream Y
26504 21220000279 Aquadrate Crm 10% 100g (App) 21220000278 Aquadrate 10% cream Y
26505 1302010U0BMAAAF Hydromol Intensive_Crm 10% (Drug) 21220000280 Hydromol Intensive 10% cream Y
26507 21220000281 Hydromol Intensive Crm 10% 100g (App) 21220000280 Hydromol Intensive 10% cream Y
26535 130201000BBAFA4 Diprobase_Crm 21220000311 Diprobase cream Y
26537 21220000313 Diprobase Crm 50g (App) 21220000311 Diprobase cream Y
30542 23150101506 Hollister_Adapt No-Sting Medical Adh Remover A/Spy 50ml 23150101504 Adapt No-Sting medical adhesive remover aerosol Y
30543 23150101507 Hollister_Adapt No-Sting Medical Adh Remover A/Spy 100ml 23150101504 Adapt No-Sting medical adhesive remover aerosol Y
34130 23452704503 Loxley_Day-Drop 30ml 23452704502 Day-drop deodorant Y
34131 23452704504 Loxley_Day-Drop 7.5ml 23452704502 Day-drop deodorant Y
35956 23800108014 Hollister_Adapt Paste 14g 23800108009 Adapt paste Y
35957 23800108015 Hollister_Adapt Paste 60g 23800108009 Adapt paste Y

Again, there are some patterns we can pick out:

An old version of a presentation is combined with the current version of the same

In [18]:
bnf_mapping[bnf_mapping["new_code"] == "0102000ACAABABA"]
Out[18]:
old_code old_name new_code new_name changed
224 0102000ACAAAPAP Atrop Sulf_Oral Susp 500mcg/5ml 0102000ACAABABA Atropine 500micrograms/5ml oral solution Y
226 0102000ACAABBBB Atrop Sulf_Oral Soln 500mcg/5ml (Old) 0102000ACAABABA Atropine 500micrograms/5ml oral solution Y

An "Oral Soln" presentation is combined with an "Oral Susp" presentation

In [19]:
bnf_mapping[bnf_mapping["new_code"] == "0102000L0AAADAD"]
Out[19]:
old_code old_name new_code new_name changed
248 0102000L0AAAWAW Glycopyrronium Brom_Oral Soln 1mg/5ml 0102000L0AAADAD Glycopyrronium bromide 1mg/5ml oral liquid Y
249 0102000L0AAAXAX Glycopyrronium Brom_Oral Susp 1mg/5ml 0102000L0AAADAD Glycopyrronium bromide 1mg/5ml oral liquid Y

Presentations of different flavour are combined

In [20]:
bnf_mapping[bnf_mapping["new_code"] == "0106010E0AAADAD"]
Out[20]:
old_code old_name new_code new_name changed
721 0106010E0AAAHAH Ispag Husk_Gran Eff Sach 3.5g Orange S/F 0106010E0AAADAD Ispaghula husk 3.5g efferv gran sach gluten free sugar free Y
722 0106010E0AAANAN Ispag Husk_Gran Eff Sach 3.5g Lem S/F 0106010E0AAADAD Ispaghula husk 3.5g efferv gran sach gluten free sugar free Y

Presentations of different dose size are combined

In [21]:
bnf_mapping[bnf_mapping["new_code"] == "0206010F0BFABCJ"]
Out[21]:
old_code old_name new_code new_name changed
2197 0206010F0BFADCI Nitrolingual_P/Spy 400mcg (180 D) 0206010F0BFABCJ Nitrolingual 400micrograms/dose pump sublingual spray Y
2198 0206010F0BFAECK Nitrolingual_P/Spy 400mcg (75 D) 0206010F0BFABCJ Nitrolingual 400micrograms/dose pump sublingual spray Y

Branded presentations are combined into a generic presentation

In [22]:
bnf_mapping[bnf_mapping["new_code"] == "090504700AABCBC"]
Out[22]:
old_code old_name new_code new_name changed
13964 090504700BBDGBC Solgar_Selenium Tab 200mcg 090504700AABCBC Selenium 200microgram tablets Y
13965 090504700BBFDBC HealthAid_Selenium Tab 200mcg 090504700AABCBC Selenium 200microgram tablets Y

Total items and net_cost for each presentation affected by change 1 for November 2019

In [23]:
joined_bnf_codes = ", ".join("'{}'".format(bnf_code) for bnf_code in bnf_mapping["old_code"])

sql = """
SELECT
    p.bnf_code,
    p.name,
    SUM(rx.items) AS items,
    SUM(rx.net_cost_pence) / 100 AS net_cost
FROM public_draft.prescribing rx
INNER JOIN public_draft.presentation p
    ON rx.bnf_code = p.bnf_code
WHERE rx.month = '2019-11-01'
  AND p.bnf_code IN ({})
GROUP BY p.bnf_code, p.name
""".format(joined_bnf_codes)

df1 = bq.cached_read(sql, csv_path='../bq-cache/2019_11_items_and_spending1.csv')
df1.set_index('bnf_code', inplace=True)
df1.head()
Out[23]:
name items net_cost
bnf_code
090402000BBZGA0 Aymes Shake_Pdr Sach 57g (Choc) 2100 35708.40
21220000227 Zerolatum Bath Add 2277 11883.99
21220000233 Cetraben Crm 50g 3703 25562.71
1202010M0AAACAC Fluticasone Prop_Nsl Spy 50mcg (150 D) 33827 273936.93
20090000941 CliniFast 7.5cm x 5m (Blue) Stkntte Elasctd Viscose Tublr 3097 23247.00
In [24]:
items1, net_cost1 = df1['items'], df1['net_cost']

Percentage of items and net_cost that are affected by change 1

In [25]:
items1.sum() / total_items * 100
Out[25]:
1.3122145971679895
In [26]:
net_cost1.sum() / total_net_cost * 100
Out[26]:
1.4312732104979142

Distribution of presentations affected by change 1, by items

In [27]:
# Find the proprtion of presentations accounted for by the top 1%, 5%, 10%, 20% of prescribing, by items
for percentile in [0.01, 0.05, 0.1, 0.2]:
    print(percentile, items1[items1 > items1.quantile(1 - percentile)].sum() / items1.sum())

0.01 0.5469690625575322
0.05 0.7838533562946104
0.1 0.8878014685876371
0.2 0.963701577257257
In [28]:
ax = items1.sort_values().cumsum().reset_index().plot()
ax.set_ylim(0);

Top 10 presentations affected by change 1, by items

In [29]:
df1.sort_values(['items'], ascending=False).head(10)
Out[29]:
name items net_cost
bnf_code
0906026M0AAAGAG Thiamine HCl_Tab 100mg 216610 546200.82
0906027G0AAABAB Vit B Co Strong_Tab 143969 471005.47
21220000235 Cetraben Crm 500g 94487 636215.76
21220000242 Epimax Crm 500g 72203 192340.70
0407010H0AAAAAA Paracet_Cap 500mg 61869 207848.16
21300000702 Clinitas Carbomer Eye Gel 48480 102789.14
1202010M0AAACAC Fluticasone Prop_Nsl Spy 50mcg (150 D) 33827 273936.93
23803108006 3m Health Care_Cavilon Durable Barrier Crm 92g 31552 251428.39
21220000230 Zerodouble Gel 24672 137567.10
0501011P0AAAFAF Phenoxymethylpenicillin_Soln 250mg/5ml 21354 223958.71

Distribution of presentations affected by change 1, by net_cost

In [30]:
# Find the proprtion of presentations accounted for by the top 1%, 5%, 10%, 20% of prescribing, by net_cost
for percentile in [0.01, 0.05, 0.1, 0.2]:
    print(percentile, net_cost1[net_cost1 > net_cost1.quantile(1 - percentile)].sum() / net_cost1.sum())

0.01 0.29625967968164973
0.05 0.6613395896681702
0.1 0.8151351449011885
0.2 0.9391461893678298
In [31]:
ax = net_cost1.sort_values().cumsum().reset_index().plot()
ax.set_ylim(0);

Top 10 presentations affected by change 1, by net_cost

In [32]:
df1.sort_values(['net_cost'], ascending=False).head(10)
Out[32]:
name items net_cost
bnf_code
090402000BBLMA0 Nutrison Pack_Energy M/Fibre 2082 666942.32
21220000235 Cetraben Crm 500g 94487 636215.76
0906026M0AAAGAG Thiamine HCl_Tab 100mg 216610 546200.82
0906027G0AAABAB Vit B Co Strong_Tab 143969 471005.47
090402000BBGYA0 Nutrison Pack_Energy 1236 343100.29
1202020L0AACCCC Sod Chlor_Inh Soln 0.9% 2.5ml Ud Amp 8081 279162.33
1202010M0AAACAC Fluticasone Prop_Nsl Spy 50mcg (150 D) 33827 273936.93
090402000BBGXA0 Nutrison Pack_Conc Liq 690 271235.45
23803108006 3m Health Care_Cavilon Durable Barrier Crm 92g 31552 251428.39
1202010Y0BBAAAA Dymista_Nsl Spy 50mcg/137mcg (120 D) 15323 247796.40

Change 2: special container quantity changes

Affected presentations

In [33]:
quantity_change = pd.read_excel('../data/Special Container size mismatch between MDR and dm+d latest.xlsx')
print(f"There are {len(quantity_change)} changed records")
display(quantity_change.head())

There are 1822 changed records
BNF Code dm+d: BNF Description MDR Quantity dm+d: Quantity dm+d: PACK Information
0 0106050B0BEAAA0 Moviprep oral powder sachets 1.0 4.0 4 sachets
1 0106050B0BEABA0 Moviprep Orange oral powder sachets 1.0 4.0 4 sachets
2 0107020J0AAAJAJ Lidocaine 1% / Hydrocortisone 0.2% spray 1.0 30.0 30 mls
3 0107020J0BHAAAJ Perinal spray 1.0 30.0 30 mls
4 0107020J0BJAAAJ Germoloids HC spray 1.0 30.0 30 mls

Total items and net_cost for each presentation affected by change 2 for November 2019

In [34]:
joined_bnf_codes = ", ".join("'{}'".format(bnf_code) for bnf_code in quantity_change['BNF Code'])

sql = """
SELECT
    p.bnf_code,
    p.name,
    SUM(rx.items) AS items,
    SUM(rx.net_cost_pence) / 100 AS net_cost
FROM public_draft.prescribing rx
INNER JOIN public_draft.presentation p
    ON rx.bnf_code = p.bnf_code
WHERE rx.month = '2019-11-01'
  AND p.bnf_code IN ({})
GROUP BY p.bnf_code, p.name
""".format(joined_bnf_codes)

df2 = bq.cached_read(sql, csv_path='../bq-cache/2019_11_items_and_spending2.csv')
df2.set_index('bnf_code', inplace=True)
df2.head()
Out[34]:
name items net_cost
bnf_code
21300000108 Hyabak Sod Hyaluronate Eye Dps 10ml P/F 15528 162125.09
21300000401 Liquifilm 1.4% Polyvinyl Alcohol Eye Dps 15ml 11656 29085.10
21300000711 Xailin Night Paraf Eye Oint P/F 5g 43421 156743.40
23150901504 CliniMed_Appeel No Sting Medical Adh Remover A/Spy 50ml 6317 183511.12
1203010E0AAABAB Benzydamine HCl_Spy 0.15% 30ml S/F 21406 67182.84
In [35]:
items2, net_cost2 = df2['items'], df2['net_cost']

Percentage of items and net_cost that are affected by change 2

In [36]:
items2.sum() / total_items * 100
Out[36]:
1.4898494160168612
In [37]:
net_cost2.sum() / total_net_cost * 100
Out[37]:
3.2417807563555776

Distribution of presentations affected by change 2, by items

In [38]:
# Find the proprtion of presentations accounted for by the top 1%, 5%, 10%, 20% of prescribing, by items
for percentile in [0.01, 0.05, 0.1, 0.2]:
    print(percentile, items2[items2 > items2.quantile(1 - percentile)].sum() / items2.sum())

0.01 0.4380237215973298
0.05 0.7494982963240924
0.1 0.8655276301147049
0.2 0.9481320551518887
In [39]:
ax = items2.sort_values().cumsum().reset_index().plot()
ax.set_ylim(0);

Top 10 presentations affected by change 2, by items

In [40]:
df2.sort_values(['items'], ascending=False).head(10)
Out[40]:
name items net_cost
bnf_code
1108010F0AAAAAA Hypromellose_Eye Dps 0.3% 118151 156821.91
120101050BCAAAB Otomize_Ear Spy 5ml 112613 398207.52
21300000111 Hylo-Forte Sod Hyaluronate Eye Dps 0.2% P/F 10ml 82955 1033619.00
1106000ACAAAAAA Brinzolamide_Eye Dps 10mg/ml 73444 254184.56
21220000311 Diprobase Crm 500g (App) 43775 324351.84
21300000711 Xailin Night Paraf Eye Oint P/F 5g 43421 156743.40
23804708007 Smith & Nephew_Proshield Plus Skin Prote 115g 41716 536024.44
21300000104 Hylo-Tear Sod Hyaluronate Eye Dps 0.1% P/F 10ml 38514 417520.00
0301020S0BBAAAA Seebri_Breezhaler Inh Cap 55mcg + Dev 29304 921215.28
23803068001 Medicareplus_Medi Derma-S Barrier Crm 90g 26603 188496.00

Distribution of presentations affected by change 2, by net_cost

In [41]:
# Find the proprtion of presentations accounted for by the top 1%, 5%, 10%, 20% of prescribing, by net_cost
for percentile in [0.01, 0.05, 0.1, 0.2]:
    print(percentile, net_cost2[net_cost2 > net_cost2.quantile(1 - percentile)].sum() / net_cost2.sum())

0.01 0.3149874674118635
0.05 0.7015026780245146
0.1 0.8425765622116822
0.2 0.9406182197270274
In [42]:
ax = net_cost2.sort_values().cumsum().reset_index().plot()
ax.set_ylim(0);

Top 10 presentations affected by change 2, by net_cost

In [43]:
df2.sort_values(['net_cost'], ascending=False).head(10)
Out[43]:
name items net_cost
bnf_code
21300000111 Hylo-Forte Sod Hyaluronate Eye Dps 0.2% P/F 10ml 82955 1033619.00
21020001196 Coloplast SpeediCath Compact Male OneSize 12/18(30-Pack)Cath 4624 1014858.96
0301020S0BBAAAA Seebri_Breezhaler Inh Cap 55mcg + Dev 29304 921215.28
190700000BBCJA0 Resource_ThickenUp Clr Pdr 18710 854096.22
21020001190 Coloplast SpeediCath Compact Fle Size 8-14 (30-Pack) Cath 3473 787740.68
21020000880 Coloplast SpeediCath Male Size 10-14 (30-Pack) Cath 4260 780975.01
21020001408 LoFric Origo+Water Nelaton Male 40cm Size 10-18(30-Pack)Cath 3767 646156.02
21020001195 Coloplast SpeediCath Compact Plus Fle 10-14 (30-Pack) Cath 2755 610643.90
21020001254 LoFric Sense Nelaton Fle 15cm 8-14 (30-Pack) Cath Wellspect 2726 550377.45
0702010F0AAACAC Estriol_Crm 0.01% + Applic 19028 546255.35