Closed richiecroker closed 6 years ago
Almost certainly caused by problems with the SNOMED mapping import.
There are a lot of Product
s with the BNF code in question:
# select count(*) from dmd_product where bnf_code = '190201000AABLBL';
┌───────┐
│ count │
├───────┤
│ 1424 │
└───────┘
(1 row)
Time: 30.053 ms
The problem's not with how we imported the data, but with the data itself: 2142 rows in the March spreadsheet have BNF code 190201000AABLBL
. They also all have Unspec Drug Code_
as in the columnMDR: Product Description
. (And they're the only rows with that value in that column.)
Compare this with the January data, where only two rows have that BNF code:
190201000AABLBL,Unspec Drug Code_,321894004,Amoxapine 100mg tablets,
190201000AABLBL,Unspec Drug Code_,,Exception Handler Unspecified Item,
Note the curious value of the last field (this is DMD: Product Description
): Exception Handler Unspecified Item
.
It's only 190201000AABLBL
that has a massive number of rows in the March spreadsheet, but there are over 100 BNF codes that appear in more than 100 rows (compared with 38 BNF codes that appear in more than 100 rows in the January spreadsheet).
@richiecroker, how did you find the problem with 190201000AABLBL
?
I'm going to reimport the January data, and we should follow up with NHS BSA to try to get better data for March.
Part of my confusion last week was because my spreadsheet program (Numbers) struggles with large spreadsheets, and can only export ~65k lines of a spreadsheet to a CSV file, while the January and March spreadsheets contain 1,048,575 and 142,521 records respectively. I've since exported the spreadsheets to CSV using Python, which has made analysis easier.
Having done so, I understand the cause of the problem.
The March spreadsheet contains a mapping from AMPP ID to BNF code. We want to use this to attach a BNF code to VMP records in the dmd_product
table. The dmd_product
table contains both AMPs and VMPs. We tried to do this for each row of the March spreadsheet with the following code:
sql = '''
SELECT vpid
FROM dmd_vmpp
INNER JOIN dmd_ampp
ON dmd_vmpp.vppid = dmd_ampp.vppid
WHERE appid = %s
'''
cursor.execute(sql, [appid])
row = cursor.fetchone()
if row is None:
continue
vpid = row[0]
sql = 'UPDATE dmd_product SET bnf_code = %s WHERE dmdid = %s'
cursor.execute(sql, [bnf_code, vpid])
That is, for each AMPP, we find the corresponding VMP ID, which we then use to find the row in the dmd_product
table to update. (In the dmd_product
table, dmdid
matches the VMP ID if the product is a VMP. There's more about the data model here.)
Let's look at VMP 408024009 ("Rosuvastatin 40mg tablets"):
# select vpid, nm from dmd_vmp where vpid = 408024009;
┌───────────┬───────────────────────────┐
│ vpid │ nm │
├───────────┼───────────────────────────┤
│ 408024009 │ Rosuvastatin 40mg tablets │
└───────────┴───────────────────────────┘
This has a single VMPP:
# select * from dmd_vmpp where vpid = 408024009;
┌──────────────────┬─────────┬─────────────────────────────────────┬──────────┬───────────┬────────┬───────────┬────────────┐
│ vppid │ invalid │ nm │ abbrevnm │ vpid │ qtyval │ qty_uomcd │ combpackcd │
├──────────────────┼─────────┼─────────────────────────────────────┼──────────┼───────────┼────────┼───────────┼────────────┤
│ 4171911000001100 │ ¤ │ Rosuvastatin 40mg tablets 28 tablet │ ¤ │ 408024009 │ 28 │ 428673006 │ ¤ │
└──────────────────┴─────────┴─────────────────────────────────────┴──────────┴───────────┴────────┴───────────┴────────────┘
There are 15 AMPPs for this VMPP:
select appid, nm from dmd_ampp where vppid = 4171911000001100;
┌───────────────────┬─────────────────────────────────────────────────────────────────────────────────────────────┐
│ appid │ nm │
├───────────────────┼─────────────────────────────────────────────────────────────────────────────────────────────┤
│ 35041811000001108 │ Rosuvastatin 40mg tablets (Sun Pharmaceuticals UK Ltd) 28 tablet 4 x 7 tablets │
│ 35040111000001107 │ Rosuvastatin 40mg tablets (Alliance Healthcare (Distribution) Ltd) 28 tablet 2 x 14 tablets │
│ 35036411000001101 │ Rosuvastatin 40mg tablets (Sandoz Ltd) 28 tablet 4 x 7 tablets │
│ 35035111000001100 │ Rosuvastatin 40mg tablets (Actavis UK Ltd) 28 tablet 2 x 14 tablets │
│ 35032411000001102 │ Rosuvastatin 40mg tablets (A A H Pharmaceuticals Ltd) 28 tablet 2 x 14 tablets │
│ 35028511000001103 │ Rosuvastatin 40mg tablets (Teva UK Ltd) 28 tablet 2 x 14 tablets │
│ 35028111000001107 │ Rosuvastatin 40mg tablets (Mylan) 28 tablet 2 x 14 tablets │
│ 35018811000001109 │ Rosuvastatin 40mg tablets (Zentiva) 28 tablet 4 x 7 tablets │
│ 34954711000001101 │ Rosuvastatin 40mg tablets (Aristo Pharma GmbH) 28 tablet 4 x 7 tablets │
│ 18229511000001105 │ Crestor 40mg tablets (Necessity Supplies Ltd) 28 tablet │
│ 17339711000001102 │ Crestor 40mg tablets (Mawdsley-Brooks & Company Ltd) 28 tablet │
│ 16155311000001108 │ Crestor 40mg tablets (Lexon (UK) Ltd) 28 tablet │
│ 13858611000001107 │ Crestor 40mg tablets (DE Pharmaceuticals) 28 tablet │
│ 10513911000001101 │ Crestor 40mg tablets (Waymade Healthcare Plc) 28 tablet │
│ 4172211000001102 │ Crestor 40mg tablets (AstraZeneca UK Ltd) 28 tablet 4 x 7 tablets │
└───────────────────┴─────────────────────────────────────────────────────────────────────────────────────────────┘
Of these, one of the "Rosuvastatin 40mg" records and all six "Crestor 40mg" records appear in the March spreadsheet. Additionally, there is an extra record with AMPP ID 4171911000001100, which does not appear in our dmd_ampp
table:
$ grep -E '(Rosuvastatin|Crestor) 40mg' march_snomed.csv
190201000AABLBL,Unspec Drug Code_,18229511000001105,Crestor 40mg (Necessity Supplies Ltd) 28 tablets
190201000AABLBL,Unspec Drug Code_,16155311000001108,Crestor 40mg (Lexon (UK) Ltd) 28 tablets
190201000AABLBL,Unspec Drug Code_,13858611000001107,Crestor 40mg (DE Pharmaceuticals) 28 tablets
190201000AABLBL,Unspec Drug Code_,10513911000001101,Crestor 40mg (Waymade Healthcare Plc) 28 tablets
0212000AAAAACAC,Rosuvastatin Calc_Tab 40mg,35018811000001109,Rosuvastatin 40mg (Zentiva) 28 tablets
0212000AABBACAC,Crestor_Tab 40mg,4172211000001102,Crestor 40mg (AstraZeneca UK Ltd) 28 tablets
190201000AABLBL,Unspec Drug Code_,17339711000001102,Crestor 40mg (Mawdsley-Brooks & Company Ltd) 28 tablets
0212000AAAAACAC,Rosuvastatin Calc_Tab 40mg,4171911000001100,Rosuvastatin 40mg 28 tablets
Two of the records in the spreadsheet have BNF code 0212000AAAAACAC, which I believe is correct. The others have BNF code 190201000AABLBL, which belongs to chapter 19 which doesn't exist.
When we iterate over the records in the spreadsheet, we set the BNF code in the dmd_product
table as described above. As such, we end up setting the BNF code to 190201000AABLBL, since the last spreadsheet record, with AMPP ID 4171911000001100, has no corresponding record in the dmd_product
table.
I'm pretty sure this indicates that the March spreadsheet contains bad data. I will do more work to identify which other drugs this affects.
The March spreadsheet does not contain anything like a straightforward mapping between VMPs and BNF codes. As such, I don't think we can use it as hoped.
(In contrast, the January spreadsheet, and the one we'd been sent privately last year, had an explicit mapping between BNF codes and VMP IDs.)
To expand on this:
So it's clear that one VMP might have multiple BNF codes. Ignoring the chapter 19 cases, how should we choose which BNF code to use?
One for @richiecroker and @sebbacon when they get back from holiday?
Peter to draft email to NHSBSA outlining problems with March data:
And add link to this issue to "problems getting data" document.
We need to do more work to understand VMPs that map to multiple BNF codes -- I've not found any examples where it is the SNOMED mapping that is obviously wrong.
For instance, AMPs 3929111000001102 (Suprefact 5.5mg/5.5ml solution for injection vials (Sanofi) 2 vial) and 3929511000001106 (Suprecur 5.5mg/5.5ml solution for injection vials (Sanofi) 2 vial) both map to VMPP 3928711000001105, which has VMP 327304007.
However, according to the SNOMED mapping, the first (Suprefact) has BNF code 0803042B0BBAAAA, and the second (Suprecur) has BNF code 0607020B0BBABAB. These names/codes are also present in the BNF data that we use, which suggests the confusion runs deeper than the SNOMED mapping.
Here's the code I used to find the example above: https://gist.github.com/inglesp/3bfc568ffc2695323eaebcc5157faddc
For the other two issues, here's a draft email for Seb to send to our contact at the NHSBSA.
Dear ...,
[...pleasantries...]
We believe that there are some errors with the data in the March 2018 spreadsheet of SNOMED - BNF mappings at https://www.nhsbsa.nhs.uk/prescription-data/understanding-our-data/bnf-snomed-mapping.
Specifically, there are 12,016 records with a BNF code in chapter 19 whose MDR product description field has value "Unspec Drug Code_", and in many cases we believe this is incorrect.
For instance, there are six records for different brands of "Crestor 40mg 28 tablets". However, only one of them ("Crestor 40mg (AstraZeneca UK Ltd) 28 tablets") has a correct MDR product description ("CrestorTab 40mg") and BNF code (0212000AABBACAC). The other five all have BNF code 190201000AABLBL, and "Unspec Drug Code" for their MDR product description.
Additionally, there are 4,247 records whose AMPP code does not appear in the latest dm+d dataset at https://isd.digital.nhs.uk/trud3/user/authenticated/group/0/pack/6/subpack/24/releases. Some examples of this are:
Please could you confirm that our understanding of the data is correct, and if so, whether you plan to reissue a corrected version of the data.
...
I've emailed 4 different people about this at NHS BSA today.
I suspect that, in the example that @inglesp used above, the BNF coding is actually correct. Suprecur and Suprefact are licensed for different conditions (Suprecur for ovulation, Suprefact for prostate cancer), which match the codes. - although the generic codes are more complicated...
TL;DR:
The March mapping contained:
The June mapping will be for both AMPPs and VMPPs. It remains to be seen if it continues to have things which should never have been exported.
Response from BSA:
When I have looked at this mapping file you are correct they are 12,016 records where the MDR product description field has either Unspec Drug Code or Unspec Drug Code_(Discount Not Deducted). This is not incorrect but we understand why it might have caused some confusion. The data is correct for our internal systems as we still need to have mapping in place and for example in the case of Crestor 40mg payment is made based on AstraZenca UK Ltd.
We will continue to include these records in the dataset as they do have individual snomed codes and are available in the dm+d Browser but to avoid any further confusion we will not show a BNF Code or an MDR product description.
In relation to the 4,247 records whose AMPP code does not appear in the latest dm+d dataset and the 3 examples that you have quoted. The link that you have supplied is in relation to TRUD and I do not have a log in to this data. I have asked ... one of our pharmacists to help explain this data as he has more knowledge around this dataset than I do
In response to a followup, they confirmed, regarding the Unspec Drug Code issue:
essentially these [chapter 19 items] are products with SNOMED codes but no active prescribing and therefore do not require an active/meaningful BNF Code
Regarding the AMPPs missing from the dataset, the pharmacist confirmed:
In the case of the Fybogel, for example, the original extract included mappings for two SNOMED AMPPs to the same BNF Code. In fact, one of those rows was for a dm+d entry known to be wrong, and therefore not marked "extractable", and should therefore not have appeared in the mapping data.
In the case of Galantamine, it's later been confirmed that the June release will keep this, and the column will be renamed VMPP / AMPP SNOMED
, i.e. it's going to include both. Which makes sense as this is what BNF codes are, i.e. a mixture of VMPP and AMPP. I'm not sure if we can tell from the number of characters in a code if it's VMPP or AMPP; AMPP has always been longer whenever I've looked, but I've never done so systematically.
In response to a followup, they confirmed, regarding the Unspec Drug Code issue: essentially these [chapter 19 items] are products with SNOMED codes but no active prescribing and therefore do not require an active/meaningful BNF Code
I may have misunderstood their response but the unspecified codes had the following prescribing assigned against it for last three months
My understanding was there was an aim to reduce to £0 the unspecified codes through the Pacific Programme.
Here's another thing I'm confused about:
The supplied mapping is BNF Code to VMPP/AMPP. But that doesn't make sense; prescribers and dispensers and the Drug Tariff work at the VMP or AMP level.
Example: paracetamol 500mg caplets are mapped to this AMPP, which is Teva's 32 caplet pack. Teva themselves do a 100 caplet pack, and there are many other AMPPs from other manufacturers and wholesalers. Why map to an AMPP for a generic which is prescribed at product (not pack) level?
Shouldn't generics all be mapped to VMPs, and brands to AMPs?
Response from NHSBSA:
Originally we produced this report due to the volume of FOI requests we were receiving for this information and proactively published this data on our website to reduce the burden of these requests.
The requirements for this report and the fields included were put together based on what people were originally asking for under FOI and subsequently from when we first published this information which is why the data is being displayed at VMPP and AMPP level including the snomed codes.
Generics are all mapped to VMPs and brands to AMPs and if this will help you we can add a further column of data to this report that also includes the snomed code at VMP and AMP level.
As you have pointed out prescribers do generally prescribe at the VMP or AMP level, however in terms of dispensing, reimbursement is based on the VMPP or the AMPP and indeed the Drug Tariff shows information at pack level.
Please let me know if this is something that you would like and I will include this in the next report which is due to be published by the end of September. We are happy to include any additional fields that our users require to enhance their use of this report.
With the June data we can map a single BNF code to each AMPP and VMPP, and for the majority of AMPs and VMPs.
There are some AMPs with multiple possible BNF codes:
number of BNF codes | number of AMPs |
---|---|
5 | 1 |
4 | 9 |
3 | 54 |
2 | 435 |
1 | 116120 |
(That is, there is 1 AMP that has 5 BNF codes, 9 AMPs with 4 BNF codes, and so on.)
There are also some VMPs with multiple possible BNF codes:
number of BNF codes | number of VMPs |
---|---|
4 | 1 |
3 | 4 |
2 | 23 |
1 | 3546 |
When I next get the chance I'll update this with details about the AMPs and VMPs with multiple possible BNF codes.
Where a single VMP has multiple BNF codes it appears to be because the BNF codes refer to product packs and not products. This looks like it's related to investigations in #940.
VMP ID | dm+d name | BNF code | BNF name |
---|---|---|---|
3412311000001102 | Absorbent cotton gauze type 13 light BP 1988 sterile 90cm | 20040200120 | Absorbent Cott Gz 90cm x 5m Ster |
20040200100 | Absorbent Cott Gz 90cm x 1m Ster | ||
20040200130 | Absorbent Cott Gz 90cm x 10m Ster | ||
20040200110 | Absorbent Cott Gz 90cm x 3m Ster | ||
3412511000001108 | Absorbent lint BPC | 20070000100 | Lint Absorbent 25g |
20070000110 | Lint Absorbent 100g | ||
20070000120 | Lint Absorbent 500g | ||
3411711000001105 | Absorbent cotton BP 1988 | 20010000103 | Absorbent Cott Wool BP 500g |
20010000102 | Absorbent Cott Wool BP 100g | ||
20010000101 | Absorbent Cott Wool BP 25g | ||
320141001 | Salbutamol 200micrograms/dose dry powder inhaler | 0301011R0AABYBY | Salbutamol_Pdr For Inh 200mcg (100 D) |
0301011R0AAAQAQ | Salbutamol_Pdr For Inh 200mcg (60 D) | ||
0301011R0AACACA | Salbutamol_Pdr For Inh 200mcg (200 D) | ||
4747111000001104 | Fludroxycortide 4micrograms/square cm tape 7.5cm | 1304000T0AACCCC | Fludroxycortide_Tape 7.5cm x 50cm |
1304000T0AACECE | Fludroxycortide_Tape 7.5cm x 20cm | ||
1304000T0AACDCD | Fludroxycortide_Tape 7.5cm x 200cm | ||
3374011000001105 | Elasticated tubular bandage BP 7.5cm size D | 20090000455 | Unspecified Stkntte 7.5cm x 0.5m Elasctd Tublr Band |
20090000456 | Unspecified Stkntte 7.5cm x 1m Elasctd Tublr Band | ||
3412411000001109 | Absorbent cotton hospital quality | 20010000201 | Absorbent Cott Wool HQ 100g |
20010000202 | Absorbent Cott Wool HQ 500g | ||
3373611000001101 | Elasticated tubular bandage BP 10cm size F | 20090000459 | Unspecified Stkntte 10cm x 0.5m Elasctd Tublr Band |
20090000460 | Unspecified Stkntte 10cm x 1m Elasctd Tublr Band | ||
18358011000001101 | Glyceryl trinitrate 400micrograms/dose pump sublingual spray | 0206010F0AACICI | Glyceryl Trinit_Sub P/Spy 400mcg (180D) |
0206010F0AACJCJ | Glyceryl Trinit_Sub P/Spy 400mcg (200D) | ||
331032009 | Budesonide 100micrograms/dose nasal spray | 1202010I0AAACAC | Budesonide_Aq Nsl Spy 100mcg (100D) |
1202010I0AAADAD | Budesonide_Aq Nsl Spy 100mcg (200D) | ||
408013007 | Mometasone 400micrograms/dose dry powder inhaler | 0302000R0AAADAD | Mometasone Fur_Pdr For Inh 400mcg (60 D) |
0302000R0AAACAC | Mometasone Fur_Pdr For Inh 400mcg (30 D) | ||
3374111000001106 | Elasticated tubular bandage BP 8.75cm size E | 20090000458 | Unspecified Stkntte 8.75cm x 1m Elasctd Tublr Band |
20090000457 | Unspecified Stkntte 8.75cm x 0.5m Elasctd Tublr Band | ||
330239000 | Ibuprofen 5% spray | 1003020P0AAAGAG | Ibuprofen_Spy 5% 35ml |
1003020P0AAADAD | Ibuprofen_Spy 5% 100ml | ||
3373711000001105 | Elasticated tubular bandage BP 12cm size G | 20090000461 | Unspecified Stkntte 12cm x 0.5m Elasctd Tublr Band |
20090000462 | Unspecified Stkntte 12cm x 1m Elasctd Tublr Band | ||
325914009 | Desmopressin 10micrograms/dose nasal spray | 0605020E0AAACAC | Desmopressin Acet_I/Nsl Spy 10mcg (50 D) |
0605020E0AAAQAQ | Desmopressin Acet_I/Nsl Spy 10mcg (60 D) | ||
22777211000001102 | Elasticated tubular bandage BP 4.5cm size A | 20090000550 | Unspecified Stkntte 4.5cm x 0.5m Elasctd Tublr Band |
20090000551 | Unspecified Stkntte 4.5cm x 1m Elasctd Tublr Band | ||
3373811000001102 | Elasticated tubular bandage BP 6.25cm size B | 20090000451 | Unspecified Stkntte 6.25cm x 0.5m Elasctd Tublr Band |
20090000452 | Unspecified Stkntte 6.25cm x 1m Elasctd Tublr Band | ||
322492007 | Buprenorphine 400microgram sublingual tablets sugar free | 0407020B0AAADAD | Buprenorphine_Tab Subling 400mcg S/F |
0410030A0AAACAC | Buprenorphine_Tab Subling 400mcg S/F | ||
320264009 | Formoterol 12micrograms/dose dry powder inhaler | 0301011E0AAACAC | Formoterol Fumar_Pdr For Inh 12mcg(60 D) |
0301011E0AAAEAE | Formoterol Fumar_Pdr For Inh 12mcg(120D) | ||
7289111000001100 | Lidocaine 2% and Chlorhexidine gel | 1502010J0AABQBQ | Lido HCl 2%/Chlorhex_Gel 6ml Pfs |
1502010J0AABRBR | Lido HCl 2%/Chlorhex_Gel 11ml Pfs | ||
3466511000001108 | Impermeable plastic adhesive tape BP 1988 2.5cm | 20100000220 | Imperm Plas 2.5cm x 3m Surg Adh Tape |
20100000230 | Imperm Plas 2.5cm x 5m Surg Adh Tape | ||
331018007 | Beclometasone 50micrograms/dose nasal spray | 1202010C0AAAAAA | Beclomet Diprop_Nsl Spy 50mcg (200 D) |
1202010C0AAADAD | Beclomet Diprop_Nsl Spy 50mcg (180 D) | ||
3373911000001107 | Elasticated tubular bandage BP 6.75cm size C | 20090000453 | Unspecified Stkntte 6.75cm x 0.5m Elasctd Tublr Band |
20090000454 | Unspecified Stkntte 6.75cm x 1m Elasctd Tublr Band | ||
408026006 | Mometasone 200micrograms/dose dry powder inhaler | 0302000R0AAABAB | Mometasone Fur_Pdr For Inh 200mcg (60 D) |
0302000R0AAAAAA | Mometasone Fur_Pdr For Inh 200mcg (30 D) | ||
9004411000001108 | Ciclesonide 160micrograms/dose inhaler CFC free | 0302000U0AAACAC | Ciclesonide_Inh 160mcg (60 D) CFF |
0302000U0AAABAB | Ciclesonide_Inh 160mcg (120 D) CFF | ||
22777111000001108 | Elasticated tubular bandage BP 17.5cm size J | 20090000552 | Unspecified Stkntte 17.5cm x 0.5m Elasctd Tublr Band |
20090000553 | Unspecified Stkntte 17.5cm x 1m Elasctd Tublr Band | ||
320962001 | Codeine 15mg/5ml linctus sugar free | 0309010C0AAABAB | Codeine Phos_Linct Diabetic 15mg/5ml |
0309010C0AAADAD | Codeine Phos_Linct 15mg/5ml S/F | ||
18357911000001103 | Glyceryl trinitrate 400micrograms/dose aerosol sublingual spray | 0206010F0AACHCH | Glyceryl Trinit_Sub A/Spy 400mcg (200D) |
0206010F0AACGCG | Glyceryl Trinit_Sub A/Spy 400mcg (180D) |
Eyeballing the AMP data suggests the same conclusion, that is, that where a single AMP has multiple BNF codes it appears to be because the BNF codes refer to product packs and not products.
A table of AMP data is attached. amps.txt
My conclusion to this is that when we have a choice of BNF codes for a product (ie a VMP or AMP, or equivalently, an entry in dmd_product
), it's Good Enough For Now to pick one arbitrarily since (a) only a small proportion of VMPs and AMPs have multiple BNF codes, and (b) the BNF codes all correspond to roughly the same thing. We're not going to have the Suprefact/Suprecur problem described above, which was presumably an error in the data.
@sebbacon do you agree?
So, I'm starting to get my head round this better (2 years after starting working with this data...):
quantity
field means. In cases where VMPPs get separate BNF Codes (see #940), quantity
nearly always means something like "1 pack of 50 doses" or "1 pack of 30 doses". In these cases, the prescriber is still likely to be interested in comparing doses, not packs.So, how we handle this depends on our audience and what we're trying to achieve.
For the DT viewer, then, the only outstanding problem is where there are multiple BNF codes that share a name - for example Ibuprofen 5% gel
will be shown to the user once (because we show them VMP names), but there are actually separate codes for 35ml and 100ml packs. This is a bug - just picking one is possibly Good Enough, but the correct fix is to add product pack information to the DMD Product names, whenever there are separate BNF Codes provided; OR for us to look up (in the tariff API call) all BNF codes which share the same VMP name and show them all on the chart.
But that's a different issue, I think, so picking the first maintains previous behaviour and fixing that can be a separate issue - I think!
More on Suprefact/Suprecur. (These are mostly notes for myself.)
We have two AMPPs which map to the same VMPP, but to very different BNF codes.
This is OK. I was previously confused because I was trying to link VMPs to BNF codes but this is obviously nonsense as BNF codes can be for branded drugs, which are AMPs.
For certain drugs (possibly where there has been a change in Category) the charts do not render:
Selecting:
gives:
(and the wrong URL for the code at the top)
Entering the correct code manually into the URL also doesn't work: