Tell me more ×
Cross Validated is a question and answer site for statisticians, data analysts, data miners and data visualization experts. It's 100% free, no registration required.
up vote 5 down vote favorite

I have a large data set which is in .dbf format right now and what I would like to do is be able to manipulate it easily in Excel and do something like subtotal and calculate stdev and ratios.

Details of the data set; This data set contains shopper information. It has 1.2 million rows and 20 columns where the rows are each a unique shopper and the columns hold their shopping data (what they bought).

I am using Office 2007 programs, I know Excel the best but was wondering what alternatives I could use to accomplish my goals (subtotal, calculate stdev, and ratio's).

share|improve this question
9  
As far as I know, Excel 2007 only allows you to have 1 million or so rows (A quick google search turns up a maximum of 1,048,576 rows in Excel 2007). I would say your best bet is to use a database program, like SQL or Access. There are simple database queries that will give you what you want (subtotals, sd's, etc). Also, I haven't really worked with it myself, but I am sure you can access datasets from Access using Excel. Honestly, though, I would just use R, and for what you're trying to calculate, it does not take long to learn some simple functions :) – ialm May 25 '11 at 21:49
Thank you for the suggestion about R, do you know any online resources that could get me up and running fairly quickly? I have some experience programming in Python. – Furlong May 25 '11 at 22:07
1  
@Furlong: You can download R for free here. There are many guides to get started with R available online. Here is one for beginners that I found on google. Since you have experience in a programming language, you should be fine with the idea of variable assignment. You should focus on reading in files in R, and learning how to use functions (like sum(), mean(), sd(), etc). – ialm May 25 '11 at 22:41
2  
If you already know Python this task is trivial and there is no need to download R. There is the dbfpy package for reading dbf files directly, xlrd for reading Excel files, and numpy has the summary statistics. – Josh Hemann May 26 '11 at 16:02
1  
@Josh, xlrd works only with xls, Excel 2007 uses xlsx. You can convert but it might get tedious. And probably xlwt will be more needed here to write to Excel files, not to read. – mpiktas May 26 '11 at 20:03
show 2 more comments

2 Answers

up vote 14 down vote

If you feel you may start more of such very large Excel type projects in the future, then you should consider installing and spending 10 hours learning the basics of R (free), which will let you do what you mention in your question, in a much more efficient manner than Excel.
R for Beginners PDF

You can ask questions about R on StackOverflow and here.

share|improve this answer
1  
"in a much more efficient manner than Excel", I guess :) – Leo May 25 '11 at 23:24
Good catch. I just edited that. – Dirk Eddelbuettel May 26 '11 at 0:46
2  
Questions about R can be asked here too :) – mpiktas May 26 '11 at 6:09
@mpiktas thanks, I made the appropriate edit. – B_Dev May 26 '11 at 17:34
up vote 9 down vote

Although I would always recommend to use R, you could nevertheless achieve what you want with python.

There is at least a package for reading dbf files.

Furthermore, scipy offers a great range of functions for statistical analysis. For example the library ScientifyPython probably contains the functions you need.

The best idea is to check scipy.org. There you will find what you want.

(But learning R is a great idea!!)

share|improve this answer
2  
+1 for giving someone direction in a language they already know. Note that numpy has all of the functionality the OP has described needing. – Josh Hemann May 26 '11 at 16:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.