Where to start building simple database to streamline analysis for community organization?

Question

I work for a research organization in India and have recently taken up with a program extending immunizations among poor rural communities. They're a fairly large organization but don't really have any IT infrastructure. Data reports on vaccine coverage, logistical questions, meeting attendance etc. come from hundreds of villages, go from pen-and-paper through several iterations of data entry and compilation, finally arriving each month at the central office as HUNDREDS of messy Excel sheets. The organization generally needs nothing more than simple totals and proportions from a large series of indicators, but doctors and high-level professionals are left spending days summing the sheets by hand, introducing lots of error and generally wasting a ton of time. I threw in some formulas and at least automated the process within single sheets, but the compilation and cross-referencing is still an issue.

There's not much to be done at the point of data collection...obviously it would be great to implement some system at the point of entry, but that would involve training hundreds of officials and local health workers; not practical at the moment.

My question: what can be done with the stack of excel sheets every month so we can analyze individually and also holistically? Is there any type of management app. or simple database we can build to upload and compile the data for easy analysis in R or even (gasp) excel? What kind of tools could I implement and then pass on to some relative technophobes? Can we house it all online?

I'm by no means a programmer but I'm an epidemiologist/stats analyst proficient in R and Google products and the general tools of a not-so-tech-averse millenial. I'd be into using this as an opportunity for learning some mySQL or similar, but need some guidance. Any ideas are appreciated...there has to be a better way!

Answer 1

Since you said that you have Microsoft Excel, I'm going to assume that you also have Access.

I am not a great fan of Access but I must say it sometimes is fit and fast for certain task.

As far as data entry is concerned: building a database with Access is very easy (they teach it at middle school children and to many not-that-tech people) and then using masks for data entry is even easier. Furthermore you can export/import data to/from Excel. You can also export .txt file, great for using read.table() in R.

By prebuilding databases and enabling users to enter pre-defined values you can try to standardize the data-entry process. Masks are sort of "forms" to be filled in order to add/search data from the database.

Sure this might not be that smooth at the beginning or even not entirely practical. If it is of any help, since you already know R, Python has a very similar syntax and many useful modules. Among these, Pandas (http://pandas.pydata.org/) is a GREAT Python module for data analysis:

1. Can read easily excel files (few lines of code)
2. Can easily work with missing values (replace them with mode,median,mean or interpolate, etc...)
3. Can easily export in .csv, .txt format, great for exporting data and load it into R later.
4. Can easily map categorical variables into numbers.
5. Great documentation.

The combination Python + pandas offers maximum flexibility while keeping a not so steep learning curve. If you know someone who works with Python you might want to ask him the same question you asked here.

Answer 2

As you have probably guessed, there is no silver bullet that will automatically solve all problems, and some manual work will always be needed to fix formatting errors, but some tools can help you.

I would recommend Pentaho for this.

Pentaho PDI (aka Spoon) can ingest your Excel sheets, and put them into a MySQL database. You can configure how columns/rows are hashed, filtered, transformed, organized by using boxes and arrows, even someone who does not know SQL can manage it (technophobes will have a hard time though):

You can combine several Excel files, existing data from MySQL, and other sources. You can set up this tool to perform your calculations and write to several MySQL table (the screenshot above shows only one "Write to Database" step, but you can have as many as you want).

Once the data is inside MySQL, other tools of the Pentaho suite let you explore the data interactively, or generate reports.

It is not a deploy-and-forget solution: Every month you will receive data containing new kinds of irregularities, and each time you (or your successors) will need to fix these manually or add more filtering.

Pentaho is free and open source.

I have used Pentaho to automatically generate meaningful reports from quantities of ill-formatted Excel files written by resellers of a major electronics-related company.

Answer 3

I would suggest replacing the use of excel, (which costs money), with a LibreOffice Base database.

You can then standardize the forms for the specific data to be gathered and specify things like validation rules, etc.

The big advantages:

• LibreOffice is free, gratis & open source
• Cross platform - your machines that gather the data can be running Windows x86 or 64 (Vista Minimum), Mac OS-X x86_64 (10.8 or newer required) or Linux
• Multi-language support: See here - over 100 currently supported
• Gives your users a full office suite into the bargain
• You can connect base to SQL and other servers to amalgamate the data
• Long term commitment to standard file formats
• Data can imported from/exported to Spreadsheets, (Calc), etc.
• Wizards to help with database & form creation.

Answer 4

Your own recommendations of R (which you're already proficient with it) and MySQL (which you want to learn) are both good solid choices.

(This task can be done with pretty much any language and database - I was asked almost the same question by someone whose proficiencies are TSQL and Microsoft SQL Server and wrote up the answer I discovered for her).

Loading an Excel worksheet into an R data-frame is pretty much R 101, and when you've extracted what you want from each worksheet and put it into another data-frame then writing your summary out to MySQL is not a lot harder.