NI Open Data – Mining Prescription Data Part 2. – #opendata #spark #clojure


The Story So Far….

You can read part 1 here.

A few weeks ago I started on finding out which were the most popular items that a GP practice would prescribe. Once again I turned to Sparkling and Clojure to do the grunt work for me.

The Practice List

What I didn’t have at the time was the practice list. You can download that from the HSC Business Services Organisation.

I’m going to create another PairRDD with the practice number as the key and then the information as it’s value.

(def practice-fields [:pracno :partnershipno :practicename :address1 :address2 :address3 :postcode :telno :lcg])

(defn load-practices [sc filepath] 
  (->> (spark/text-file sc filepath)
       (spark/map #(->> (csv/read-csv %) first))
       (spark/filter #(not= (first %) "PracNo"))
       (spark/map #(zipmap practice-fields %))
       (spark/map-to-pair (fn [rec] 
                            (let [practice-id (:pracno rec)]
                              (spark/tuple practice-id rec))))))

It’s very similar to the original function I used to load the prescription CSV.

Joining The RDD’s

With two pair RDD’s I can safely perform the join.

(defn join-practice-prescriptions [practices prescriptions]
  (spark/join practices prescriptions))

This will give me a Pair RDD with a [key, [value1, value2]] form. I’ll need to tweak the function that works out the frequencies so it takes into account this new data structure.

(defn def-practice-prescription-freq [prescriptiondata]
  (->> prescriptiondata
       (spark/map-to-pair (s-de/key-val-val-fn (fn [k v pr] 
                                               (let [freqmap (map (fn [rec] (:vmp_nm rec)) pr)]
                                                 (spark/tuple (str (:practicename v) " " (:postcode v)) (apply list (take 10 (reverse (sort-by val (frequencies freqmap))))))))))))

The last thing to do it wrap up the process-data function to load in the practice list.

(defn process-data [sc filepath practicefile outputpath] 
  (let [prescription-rdd (load-prescription-data sc filepath)
        practice-rdd (load-practices sc practicefile)]
    (->> (join-practice-prescriptions practice-rdd prescription-rdd)
         (spark/coalesce 1)
         (spark/save-as-text-file outputpath))))

Testing From The REPL

To test these changes is fairly trivial from the REPL.

; CIDER 0.8.1 (package: 20141120.1746) (Java 1.7.0_40, Clojure 1.6.0, nREPL 0.2.10)
nipresciptions.core> (def c (-> (conf/spark-conf)
             (conf/master "local[3]")
             (conf/app-name "niprescriptions-sparkjob")))
  (def sc (spark/spark-context c))
nipresciptions.core> (def practice-file "/Users/Jason/work/data/Northern_Ireland_Practice_List_0107151.csv")
nipresciptions.core> (def prescription-path "/Users/Jason/work/data/niprescriptions")
nipresciptions.core> (process-data sc prescription-path practice-file "/Users/Jason/testoutput")

The Spark job will take a bit of time as there’s 347 practices and a lot of prescription data…..


Detail Data Prescription CSV’s:

Github Repo for this project:

Practice List:




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: