I have the following data being fetched from MySQL:
data <- tibble::tribble(
~BNFNAME, ~CHEMSUB, ~month, ~ACTCOST, ~QUANTITY,
"RANITIDINE 150MG TABLETS", "DRUGS FOR PEPTIC ULCER AND GASTRO-OESOPHAGEAL REFLUX DISEASE", 201511, 365.44, 3822,
"KETOCONAZOLE 200MG TABLETS", "ANTIMYCOTICS FOR SYSTEMIC USE", 201511, 278.77, 1910,
"CALCIGARD (NIFEDIPINE) 20MG SUSTAINED RELEASE TABLETS", "CALCIUM CHANNEL BLOCKERS, SELECTIVE CALCIUM CHANNEL BLOCKERS WITH MAINLY VASCULAR EFFECTS", 201511, 7608.22, 76465,
"COARTEM 20MG; 120MG TABLETS", "ANTIMALARIALS", 201511, 11.29, 42,
"VARIPAN (PARACETAMOL) 500MG TABLETS", "OTHER ANALGESICS AND ANTIPYRETICS", 201511, 1035.09, 18171,
"DOMPERIDONE 10MG TABLETS", "PROPULSIVES", 201511, 261.58, 1313,
"AMARYL 4MG TABLETS", "BLOOD GLUCOSE LOWERING DRUGS, EXL. INSULINS", 201511, 1624.77, 1238,
"METFORMIN 500MG TABLETS", "BLOOD GLUCOSE LOWERING DRUGS, EXL. INSULINS", 201511, 7425.2, 39650,
"GLIBENCLAMIDE 5MG TABLETS", "BLOOD GLUCOSE LOWERING DRUGS, EXL. INSULINS", 201511, 638.41, 24160,
"CO-TRIMOXAZOLE 480MG TABLETS", "SULFONAMIDES AND TRIMETHOPRIM FOR SYSTEMIC USE", 201511, 823.12, 18670,
"CIPROFLOXACIN 250MG TABLETS", "QUINOLONE ANTIBACTERIALS FOR SYSTEMIC USE", 201511, 1736.54, 18600,
"EFAVIRENZ 600MG; LAMIVUDINE 300MG; TENOFOVIR 300MG TABLETS", "DIRECT ACTING ANTIVIRALS", 201511, 6603.22, 10049,
"DOXYCYCLINE 100MG CAPSULES", "TETRACYCLINES FOR SYSTEMIC USE", 201511, 305.21, 2595,
"GRISEOFULVIN 500MG TABLETS", "ANTIFUNGALS FOR SYSTEMIC USE", 201511, 281.6, 1015,
"AMLODIPINE 10MG TABLETS", "CALCIUM CHANNEL BLOCKERS, SELECTIVE CALCIUM CHANNEL BLOCKERS WITH MAINLY VASCULAR EFFECTS", 201511, 843.1, 3840,
"PREDNISOLONE 5MG TABLETS", "CORTICOSTEROIDS FOR SYTEMIC USE, PLAIN", 201511, 6.57, 89,
"SILVER SULFADIAZINE 1% CREAM", "CHEMOTHERAPEUTICS FOR TOPICAL USE", 201511, 87, 16,
"AMOXYCILLIN 250MG CAPSULES", "BETA-LACTAM ANTIBACTERIALS, PENICILLINS FOR SYSTEMIC USE", 201511, 1041.06, 7540,
"CETIRIZINE 10MG TABLETS", "ANTIHISTAMINES FOR SYSTEMIC USE", 201511, 870.03, 4667,
"HYDROCHLOROTHIAZIDE 25MG TABLETS", "DIURETICS, LOW-CEILING DIURETICS, THIAZIDES", 201511, 459.94, 8887,
"IBUPROFEN 400MG TABLETS", "ANTIINFLAMMATORY AND ANTIRHEUMATIC PRODUCTS, NON-STEROIDS", 201511, 731.78, 10130,
"ADCO INDOGEL (INDOMETHACIN) 1% 25G", "TOPICAL PRODUCTS FOR JOINT AND MUSCLULAR PAIN", 201511, 572, 90,
"EXFORGE (AMLODIPINE 10MG; VALSARTAN 160MG) TABLETS", "AGENTS ACTING ON THE RENIN-ANGIOTENSIN SYSTEM, ANGIOTENSIN II ANTAGONISTS, COMBINATIONS", 201511, 75, 70,
"DICLOFENAC SODIUM 75MG/3ML INJECTABLE", "ANTIINFLAMMATORY AND ANTIRHEUMATIC PRODUCTS, NON-STEROIDS", 201511, 165, 103,
"VARIPAN (PARACETAMOL) 500MG TABLETS", "OTHER ANALGESICS AND ANTIPYRETICS", 201601, 1091.04, 17536,
"LANSOPRAZOLE 30MG CAPSULES", "DRUGS FOR PEPTIC ULCER AND GASTRO-OESOPHAGEAL REFLUX DISEASE", 201601, 227.27, 1141,
"CIPROFLOXACIN 500MG TABLETS", "QUINOLONE ANTIBACTERIALS FOR SYSTEMIC USE", 201601, 1517.27, 9729,
"AMLODIPINE 5MG TABLETS", "CALCIUM CHANNEL BLOCKERS, SELECTIVE CALCIUM CHANNEL BLOCKERS WITH MAINLY VASCULAR EFFECTS", 201601, 910.17, 5505,
"EFAVIRENZ 600MG; EMTRICITABINE 200MG; TENOFOVIR 300MG TABLETS", "DIRECT ACTING ANTIVIRALS", 201601, 1448.67, 1080,
"PONSTAN 50MG/5ML SUSPENSION 100ML", "ANTIINFLAMMATORY AND ANTIRHEUMATIC PRODUCTS, NON-STEROIDS", 201601, 122.1, 33,
"PARACET (PARACETAMOL) 120MG/5ML SYRUP", "OTHER ANALGESICS AND ANTIPYRETICS", 201601, 1202.5, 370,
"TETRACYCLINE 1% EYE OINTMENT", "ANTIINFECTIVES FOR OPTHTHALMIC USE", 201601, 84.48, 44,
"ZINNAT 125MG/5ML SUSPENSION 100ML", "OTHER BETA-LACTAM ANTIBACTERIALS FOR SYSTEMIC USE", 201601, 629, 36,
"PREDNISOLONE 5MG TABLETS", "CORTICOSTEROIDS FOR SYTEMIC USE, PLAIN", 201601, 1081.22, 18460,
"AMITRIPTYLINE HYDROCHLORIDE 25MG TABLETS", "ANTIDEPRESSANTS", 201601, 368.07, 5368,
"AMLODIPINE 10MG TABLETS", "CALCIUM CHANNEL BLOCKERS, SELECTIVE CALCIUM CHANNEL BLOCKERS WITH MAINLY VASCULAR EFFECTS", 201601, 3655.62, 16079,
"ENALAPRIL MALEATE 10MG TABLETS", "AGENTS ACTING ON THE RENIN-ANGIOTENSIN SYSTEM, ACE INHIBITORS, PLAIN", 201601, 979.73, 9430,
"CO-AMOXICLAV 1 G TABLETS", "BETA-LACTAM ANTIBACTERIALS, PENICILLINS FOR SYSTEMIC USE", 201601, 2384.75, 2506,
"FLAGENTYL 500MG TABLETS", "AGENTS AGAINST AMOEBIASIS AND OTHER ANTIPROTOZOAL DISEASES", 201601, 183, 104,
"CIPROFLOXACIN 0.3% EYE DROPS", "ANTIINFECTIVES FOR OPTHTHALMIC USE", 201601, 152.75, 65,
"CANDID V (CLOTRIMAZOLE) 2% VAGINAL GEL", "ANTIINFECTIVES AND ANTISEPTICS, EXCL. COMBINATIONS WITH CORTICOSTEROIDS, GU SYSTEM", 201601, 491.84, 116,
"CO-TRIMOXAZOLE 480MG TABLETS", "SULFONAMIDES AND TRIMETHOPRIM FOR SYSTEMIC USE", 201601, 880.15, 19930,
"DICLOFENAC SODIUM 50MG TABLETS", "ANTIINFLAMMATORY AND ANTIRHEUMATIC PRODUCTS, NON-STEROIDS", 201601, 2566.06, 38126,
"ACYCLOVIR 5% TOPICAL CREAM", "CHEMOTHERAPEUTICS FOR TOPICAL USE", 201601, 187.5, 30,
"DICLOFENAC SODIUM 75MG/3ML INJECTABLE", "ANTIINFLAMMATORY AND ANTIRHEUMATIC PRODUCTS, NON-STEROIDS", 201601, 294.4, 225,
"GRISEOFULVIN 125MG/ML SUSPENSION", "ANTIFUNGALS FOR SYSTEMIC USE", 201601, 840, 129,
"AMOXYCILLIN 250MG CAPSULES", "BETA-LACTAM ANTIBACTERIALS, PENICILLINS FOR SYSTEMIC USE", 201601, 1271.9, 18308,
"LAMIVUDINE 150MG; NEVIRAPINE 200MG; TENOFOVIR 300MG TABLETS", "DIRECT ACTING ANTIVIRALS", 201601, 2339.84, 11520,
"HYDROCHLOROTHIAZIDE 25MG TABLETS", "DIURETICS, LOW-CEILING DIURETICS, THIAZIDES", 201601, 739.55, 14265,
"CEPHALEXIN 500MG CAPSULES", "OTHER BETA-LACTAM ANTIBACTERIALS FOR SYSTEMIC USE", 201601, 1038.79, 2090,
"BACTROBAN 2% TOPICAL OINTMENT", "ANTIBIOTICS FOR TOPICAL USE", 201601, 1327.1, 169,
"CONTIFLO OD (TAMSULOSIN) 0.4MG EXTENDED RELEASE CAPSULES", "DRUGS USED IN BENIGN PROSTATIC HYPERTROPHY", 201601, 366.98, 699,
"ERYTHROMYCIN STEARATE 250MG CAPSULES", "MACROLIDES, LINCOSAMIDES AND STREPTOGRAMINS FOR SYSTEMIC USE", 201601, 511.84, 3732,
"METRONIDAZOLE 400MG TABLETS", "AGENTS AGAINST AMOEBIASIS AND OTHER ANTIPROTOZOAL DISEASES", 201601, 649.17, 14078,
"EFAVIRENZ 600MG TABLETS", "DIRECT ACTING ANTIVIRALS", 201601, 2038.8, 10170,
"LAMIVUDINE 300MG; TENOFOVIR 300MG TABLETS", "DIRECT ACTING ANTIVIRALS", 201601, 2892.44, 9478,
"DOXYCYCLINE 100MG CAPSULES", "TETRACYCLINES FOR SYSTEMIC USE", 201601, 474.38, 3964,
"TENORIC (ATENOLOL 50MG; CHLORTHALIDONE 12.5MG) TABLETS", "BETA BLOCKING AGENTS AND OTHER DIURETICS", 201601, 2720, 9856,
"ALDACTONE 25MG TABLETS", "DIURETICS, POTASSIUM-SPARING AGENTS", 201601, 1448.11, 5342,
"VALPROATE SODIUM 200MG TABLETS", "ANTIEPILEPTICS", 201601, 1257.02, 6118,
"NORMAL SALINE 0.9% 200ML INJECTABLE", "IRRIGATION SOLUTIONS", 201601, 105.9, 39,
"FRUSEMIDE 40MG TABLETS", "DIURETICS, HIGH-CELING DIURETICS", 201601, 706.57, 17410,
"PRED FORTE 1% EYE DROPS", "ANTIINFLAMMATORY AGENTS FOR OPHTHALMIC USE", 201601, 449.88, 26,
"BESEMAX TABLETS", "OTHER ANALGESICS AND ANTIPYRETICS", 201601, 1499.29, 7940,
"METOCLOPRAMIDE 10MG/2ML INJECTABLE", "PROPULSIVES", 201601, 21.75, 17,
"CETIRIZINE 10MG TABLETS", "ANTIHISTAMINES FOR SYSTEMIC USE", 201601, 854.96, 4636,
"METHOTREXATE 2.5MG TABLETS", "IMMUNOSUPPRESSANTS", 201601, 118.55, 308,
"METFORMIN 500MG TABLETS", "BLOOD GLUCOSE LOWERING DRUGS, EXL. INSULINS", 201601, 8924.79, 47709,
"BAYER CARDIO (ASPIRIN) 100MG TABLETS", "ANTITHROMBOTIC AGENTS", 201601, 1193.75, 191,
"ATENOLOL 50MG TABLETS", "BETA BLOCKING AGENTS", 201601, 713.26, 14345,
"CEFACLOR 250MG/5ML SUSPENSION", "OTHER BETA-LACTAM ANTIBACTERIALS FOR SYSTEMIC USE", 201601, 230, 23,
"FLUCLOXACILLIN 125MG/5ML SUSPENSION", "BETA-LACTAM ANTIBACTERIALS, PENICILLINS FOR SYSTEMIC USE", 201601, 761.45, 117,
"HYOSCINE 10MG TABLETS", "BELLADONA AND DERIVATIVES, PLAIN", 201601, 417.41, 1049,
"METOCLOPRAMDIE 10MG TABLETS", "PROPULSIVES", 201601, 172.59, 1458,
"DIAZEPAM 5MG TABLETS", "ANXIOLYTICS", 201601, 108.61, 605,
"SODIUM CROMOGLYCATE 2% EYE DROPS", "DECONGESTANTS AND ANTIALLERGICS FOR OPHTHALMIC USE", 201601, 92.43, 13,
"ACETAZOLAMDE 250MG TABLETS", "ANTIGLAUCOMA PREPARATIONS AND MIOTICS", 201601, 37.75, 336,
"ACYCLOVIR 200MG TABLETS", "DIRECT ACTING ANTIVIRALS", 201601, 300.23, 2445,
"PYRIMON EYE DROPS", "ANTIINFLAMMATORY AGENTS AND ANTIINFECTIVES IN COMBINATION FOR OPHTHALMIC USE", 201601, 431, 155,
"OMEPRAZOLE 20MG CAPSULES", "DRUGS FOR PEPTIC ULCER AND GASTRO-OESOPHAGEAL REFLUX DISEASE", 201601, 2072.87, 17136,
"AMOXYCILLIN 125MG/5ML SUSPENSION 100ML", "BETA-LACTAM ANTIBACTERIALS, PENICILLINS FOR SYSTEMIC USE", 201601, 308.8, 176,
"RANITIDINE 150MG TABLETS", "DRUGS FOR PEPTIC ULCER AND GASTRO-OESOPHAGEAL REFLUX DISEASE", 201601, 239.97, 2567,
"GLIBENCLAMIDE 5MG TABLETS", "BLOOD GLUCOSE LOWERING DRUGS, EXL. INSULINS", 201601, 648.44, 24629,
"METHOCID (INDOMETHACIN) 25MG CAPSULES", "ANTIINFLAMMATORY AND ANTIRHEUMATIC PRODUCTS, NON-STEROIDS", 201601, 359.92, 6616,
"RAMIPRIL 5MG CAPSULES", "AGENTS ACTING ON THE RENIN-ANGIOTENSIN SYSTEM, ACE INHIBITORS, PLAIN", 201601, 549.77, 2011,
"LOPERAMIDE 2MG CAPSULES", "ANTIPROPULSIVES", 201601, 96.35, 47,
"GRISEOFULVIN 250MG TABLETS", "ANTIFUNGALS FOR SYSTEMIC USE", 201601, 30.3, 284,
"FLUCONAZOLE 200MG TABLETS", "ANTIMYCOTICS FOR SYSTEMIC USE", 201601, 606.76, 2156,
"CLOXACILLIN 250MG CAPSULES", "BETA-LACTAM ANTIBACTERIALS, PENICILLINS FOR SYSTEMIC USE", 201601, 982.6, 12660,
"CANDID (CLOTRIMAZOLE) 1% EAR DROPS", "ANTIINFECTIVES FOR OPTHTHALMIC USE", 201601, 10.83, 3,
"CUROX (ROXITHROMYCIN) 150MG TABLETS", "MACROLIDES, LINCOSAMIDES AND STREPTOGRAMINS FOR SYSTEMIC USE", 201601, 197.4, 480,
"NORMAL SALINE 0.9% 1L INJECTABLE", "IRRIGATION SOLUTIONS", 201601, 225, 64,
"MICONAZOLE 2% ORAL GEL 40G", "STOMATOLOGICAL PREPARATIONS", 201601, 214.6, 37,
"ENALAPRIL MALEATE 20MG TABLETS", "AGENTS ACTING ON THE RENIN-ANGIOTENSIN SYSTEM, ACE INHIBITORS, PLAIN", 201601, 1618.4, 11270,
"ASTHALIN 100MCG INHALER ", "ADRENERGICS, INHALANTS", 201601, 344.35, 97,
"CALCIGARD (NIFEDIPINE) 20MG SUSTAINED RELEASE TABLETS", "CALCIUM CHANNEL BLOCKERS, SELECTIVE CALCIUM CHANNEL BLOCKERS WITH MAINLY VASCULAR EFFECTS", 201601, 5607.98, 56527,
"PROTAPHANE PENSET", "INSULINS AND ANALOGUES", 201601, 693, 64,
"AMARYL 4MG TABLETS", "BLOOD GLUCOSE LOWERING DRUGS, EXL. INSULINS", 201601, 3240, 2460,
"IMOVANE 7.5MG TABLETS", "HYPNOTICS AND SEDATIVES", 201601, 85.25, 107
)
Created on 2019-09-05 by the reprex package (v0.3.0)
The data is being fetched from MySQL via the following code:
productdb <- dbGetQuery(con, "SELECT BNFNAME, CHEMSUB, month, sum(ACTCOST) AS ACTCOST, sum(QUANTITY) AS QUANTITY from pharmaMachineLearningPSPGS GROUP BY BNFNAME, CHEMSUB, month;")
I then need to further process the data as follows:
products <- (unique(productdb$BNFNAME))
productslower <- tolower(products)
productdb2 <- productdb[,.(SUMQ=sum(QUANTITY)), by=c("BNFNAME","CHEMSUB")][order(-SUMQ)]
I am getting the following error message:
Error in [.data.frame(productdb, , .(SUMQ = sum(QUANTITY)), by = c("BNFNAME", :
unused argument (by = c("BNFNAME", "CHEMSUB"))
Kindly assist with the error trouble shooting as i am failing to locate my error in the code.
Regards,
Chris