PostgreSQL, Excel, R, and a Really Big Data Set!

April 19, 2012

(This article was first published on Data and Analysis with R, at Work, and kindly contributed to R-bloggers)

At work I’ve started to work with the biggest data set I’ve ever seen!  First, let me qualify my use of the term “Big Data”.  The number of rows in the resultant data set (after much transformation and manipulation in PostgreSQL and to a lesser extent in Excel) is only just 395,928.  This will certainly pale in comparison to truly BIG data sets, but for me it’s huge!  For now, the data I’m working with has 307 variables (but a big chunk of null values).  Just calculate: if there were no null values in this data set, the number of data points would be 395,928 * 307 = 121,549,896!!  Even if 60% of this table consisted of null values, that still leaves 48,619,958 data points.  Wow!

Working with so much data (it came from a database dump), I’ve noticed a few things:

1) PostgreSQL is awesome at handling it.  I really haven’t had to wait too long for each of my queries (create table queries, mainly) to execute.  The longest I had to wait was in fact the final query that I ran today that joined all of the transformed tables that I created to a biographical information table.  I don’t think it took much longer than a minute, at most.

In fact, what I found really helpful in PostgreSQL was recoding some of the qualitative variables using CASE … WHEN …. THEN clauses.  Those are super handy and are carried out with seemingly little difficulty.

2) Excel 2010 had LOTS of difficulty letting me manipulate my data set once I loaded it up. Granted, the file was a 176 megabyte csv file… there’s no shame at whining when you have the weight of the world on your shoulders!

One problem I had in excel was that, at one point, it stopped immediately redrawing the excel window every time I changed to a different ribbon.  For example, I would click on the “File” ribbon, after being on the “Home” ribbon, and I would still see my data set, and not all the “File” ribbon options.  Sizing the excel window down and dragging it out of view to the bottom of the screen and back seemed to help at times, and waving my mouse cursor where all the relevant buttons should be helped at other times.

Another problem was when I went to recode some variables in Excel into dummy variables, it took SO LONG to finish the job!  Granted, I used the standard “Find / Replace” window, which might not be the best option in Excel.  I’ve learned my lesson though.  I have to either recode in PostgreSQL, or do what I describe below, in R.

3) Although I haven’t done too much with the data set in R yet, it has handled it with pizzazz!  R loaded the csv file in just under 2 minutes (I was honestly expecting longer).  Once I had it in, I realized that for the variables in my data set where there were no zeros indicating the absence of something, I would need to include them somehow.  I didn’t actually want to modify the data set to include the zeros, as that would add to how much RAM R was using up (it was already using up 1.5 gigs of ram!).  So, I made a simple function that would put zeros into a vector just for the purpose of graphing or statistical testing.

In other words, if I’m doing a boxplot and I need my X variable to contain zeros where there are only NA values, I run the boxplot command, passing the X variable to the function so that the zeros get added temporarily and not permanently (e.g. boxplot(Y ~ zerofy(X)).  This should hopefully prevent me from using too much RAM.

I haven’t yet run any typical statistical tests on the data set, so I can’t attest to how quickly they work on it, but that will come very very soon.  So far, I’m quite impressed with the open source tools I use and their performance on this data!

To leave a comment for the author, please follow the link and comment on their blog: Data and Analysis with R, at Work. offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...

If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Tags: , , ,

Comments are closed.


Mango solutions

RStudio homepage

Zero Inflated Models and Generalized Linear Mixed Models with R

Quantide: statistical consulting and training


CRC R books series

Contact us if you wish to help support R-bloggers, and place your banner here.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)