SQLite for Data Analysis

Before I started using a Mac as my main computer, I would often find myself using Microsoft Access to do some data analysis for a one-off project. Now that I’m using a Mac, I needed to find a tool to do what I used to do with Microsoft Access. In my search I came across SQLite. SQLite is an excellent lightweight database that is extremely fast and a great tool for a data analyst. SQLite is also built into the Mac OS X operating system. The built-in version of SQLite requires use of the command line terminal. Best of all SQLite is in the public domain so it is free to use as you wish.

DB Browser for SQLite

Luckily I came across a GUI front end called DB Browser for SQLite that works cross-platform and is open source. You still need to be proficient in SQL, but what data analyst isn’t? Here is a screen shot of what the application looks like. With SQLite there is no need to install a server, so there is less administrative maintenance. Everything with SQLite happens on the client and the database is all stored in one file for easy transport and sharing.

DB Browser for SQLite

Data Analysis with SQLite

Using SQLite I can easily create a small (and it seems to be able to handle some pretty large datasets) analytic database by importing a few csv files of source data. I can then write some SQL statements and turn them into views for use in my analysis. The analysis is easily output to a csv file so that I could display and format the results in excel for further analysis. Using SQLite is much easier and faster than excel and creating a bunch of vlookups and subtotals to try to aggregate a bunch of data. For a more in-depth guide to SQLite I suggest you buy The Definitive Guide to SQLite. Please leave a comment if you have any experience using SQLite for data analysis and how you used it.

Leave a Reply

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

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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.