A Twitter-based WHO Model List of Essential Medicines Lookup Tool or How ICT Can Wrangle the "Massive Essential Drugs List" Without Breaking a Sweat

Inspired by a post on the Urban Health Updates blog, Kenya - 90% of Nairobi's poor get wrong medication, I wrote up a half-baked idea about a way to use SMS and a tool like FrontlineSMS to create a real-time lookup for medication information (indications, dosages, contraindications, etc.) and sent it off to an internal mailing list that is used to discuss Urban Health issues. There was quite a bit of interest & discussion but there was also one response (from a highly-regarded & very senior coworker) that mentioned that the "essential drugs list is massive." My response to that comment was that we could start small, maybe with just HIV/AIDS-related medications, and see how it worked. Well, never having been one to retreat in the face of a challenge -- especially one I think I can handle :P -- I decided to tackle turning the WHO Model List of Essential Medicines into a mobile-accessible database.

First, a few caveats:

• I'm a coder, not a clinician (I'm having Star Trek flashbacks :P).
• I don't have a test instance of FrontlineSMS running so I chose the next best thing: Twitter (which, as we all know, is accessible via SMS).
• The downloadable WHO Model List of Essential Medicines simply lists categorization and formulation information. There is the Essential Medicines Library (EMLib), but it's only available on the web and there doesn't appear to be an easy way to hook into it (e.g. there's no API and no obvious way to generate drug-specific URLs).

As an aside: WHO, if you're listening/reading, I'd be more than happy to work with you to turn EMLib into something much more useful. Just drop me a line. I work @ Jhpiego. I'm sure you can find me without much trouble :)

So, anyway, on to the good stuff -- the steps I took to actually make this work. Before I begin, if any real Perl coders come across this, please forgive my hack job :P

1. Download a copy of the WHO Model List of Essential Medicines PDF and save it as a text file. I tried XML & HTML, but it turns out it was easier to parse in TXT format. See #3 below.
   


2. Create a SQLite database with a single table to hold the medication name, categorization & formulation information. Here's the SQL:
   
   create table medications (id integer primary key autoincrement, name varchar(64) not null, formulation varchar(256) not null, category varhcar(64) not null, subcategory varchar(64) not null)


3. Write a Perl script to parse the TXT file and insert records into the database. Then run it! Here's the code:
   
   
#!/usr/bin/perl

use DBI;

my $dbargs = {AutoCommit => 1, PrintError=>1};
my $dbh = DBI->connect("dbi:SQLite2:dbname=path_to_sqlite_db","","",$dbargs) || die "$DBI::errstr\n";
my $sql = "insert into medications (name, formulation, category, subcategory) values (?, ?, ?, ?)";

my $meds = 0;
my $cat = "";
my $subcat = "";
my $txt = path_to_essential_medicines_txt_file;

open (TXT, "<$txt") || die "$!\n"; while (<TXT>) { chomp; s/^(\.\s+|\s+)//; tr/A-Z/a-z/; $cat = $_ if ($_ =~ /^\d+\.\s/); $cat =~ s/(^\d+\.\s|\s+$)//; $subcat = $_ if ($_ =~ /^\d+\.\d+\s/); $subcat =~ s/(^\d+\.\d+\s|\s+$)//; $meds = ($meds == 1 || ($meds == 0 && $_ =~ /^\d\./)) ? 1 : 0; my @med = split(/\s{2}/, $_); if ($meds && $#med == 1 && $_ !~ /^$/) { $sql =~ tr/A-Z/a-z/; $dbh->do($sql, undef, @med[0], @med[1], $cat, $subcat);
die "$DBI::errstr\n" if ($dbh->err);
print "@med[0]\n";
}
}

close (TXT) || die "$!\n";

$dbh->disconnect();



4. Create a Twitter account (I called it @LEssMed - List of Essential Medicines) that will receive queries as direct messages (DM) and respond with another DM containing the requested information.


5. Using Net::Twitter, write a Perl script that will download the latest DMs, turn them into a query that gets executed against the database, and post a DM (or multiple DMs if there's more than one record returned) back with formated results. Here's the code for this:
   
   
#!/usr/bin/perl

use DBI;
use Net::Twitter;
use strict;

my $id = "0";

open (ID, "<path_to_pid_type_file_of_last_dm") || die "$!\n";
while (<ID>) {
chomp;
$id = $_;
}
close (ID);

my $dbargs = {AutoCommit => 1, PrintError=>1};
my $dbh = DBI->connect("dbi:SQLite2:path_to_sqlite_db","","",$dbargs) || die "$DBI::errstr\n";
my $sth = $dbh->prepare("select name, formulation, category, subcategory from medications where name like ?") || die $dbh->errstr;

my $twit = Net::Twitter->new(username=>"twitter_username", password=>"twitter_password");
my $dms = $twit->direct_messages({since_id=>$id});
my $i = 0;
my $j = 0;

for my $dm (@{$dms}) {
if ($i == 0) {
open (ID, ">path_to_pid_type_file_of_last_dm") || die "$!\n";
print ID $$dm{id};
close (ID);
$i++;
}

$sth->execute($$dm{text} . "%");
while (my @data = $sth->fetchrow_array()) {
$twit->new_direct_message({user=>$$dm{sender_screen_name}, text=>"$data[0]: $data[2]. $data[3]. $data[1]"});
$j++;
}

$twit->new_direct_message({user=>$$dm{sender_screen_name}, text=>"No results for '$$dm{text}'"}) if ($j == 0);
}

$sth->finish;
$dbh->disconnect();

print "Processed $i dm(s)\n";



6. Write a quick autofollow script (very important!) so that reciprocal following can be set up automagically; otherwise, I'd have to manually monitor for new followers & follow back. And who wants to do that?! :P Here's this all-important Perl code:
   
   
#!/usr/bin/perl

use Net::Twitter;
use strict;

my $twit = Net::Twitter->new(username=>"twitter_username", password=>"twitter_password");
my $flws = $twit->followers_ids();
my $i = 0;

for my $flw (@{$flws}) {
my $name = ${$twit->show_user({id=>$flw})}{screen_name};
print "$name\n";
if (!($twit->relationship_exists("twitter_username", $name))) {
$twit->create_friend({id=>$flw});
}
$i++;
}

print "Processed $i follower(s)\n";



7. Finally, set up two cron jobs so the DM checks & autofollows get triggered on a regular basis. BTW, I had initially set the two scripts to run every minute but quickly ran out of API calls :P I throttled everything back to 2 minutes which seems to work for now. I'd have to tweak this even further if the service got really popular! Just to be consistent & thorough, here are the crontab entries:
   
   
*/2 * * * * path_to_DM_and_db_lookup_code
*/2 * * * * path_to_autofollow_code


So, after a few hours of coding, I had a fully-functional, Twitter-based lookup tool for the WHO Model List of Essential Medicines up & running. As I mentioned already, it's a bit of a hack-job, but it works. Honestly, if you know what you're doing, this isn't really all that impressive: kind of trivial, actually (yes, there's a little bit of horn-tooting there :P).

The beauty is, the same basic approach can be used to set up any type of automated lookup tool. This just goes to show how truly cross-cutting ICT is. And is but a small window onto the world of opportunities and potential applications for global health & ICT4D.