Best ways to aggregate and analyze data

Question

Having just recently started teaching myself Machine Learning and Data Analysis I'm finding myself hitting a brick wall on the need for creating and querying large sets of data. I would like to take data I've been aggregating in my professional and personal life and analyze it but I'm uncertain of the best way to do the following:

1. How should I be storing this data? Excel? SQL? ??

2. What is a good way for a beginner to begin trying to analyze this data? I am a professional computer programmer so the complexity is not in writing programs but more or less specific to the domain of data analysis.

EDIT: Apologies for my vagueness, when you first start learning about something it's hard to know what you don't know, ya know? ;)

Having said that, my aim is to apply this to two main topics:

1. Software team metrics (think Agile velocity, quantifying risk, likelihood of a successfully completed iteration given x number of story points)

2. Machine learning (ex. system exceptions have occurred in a given set of modules what is the likelihood that a module will throw an exception in the field, how much will that cost, what can the data tell me about key modules to improve that will get me the best bang for my buck, predict what portion of the system the user will want to use next in order to start loading data, etc).

Answer 1

If you have large data sets - ones that make Excel or Notepad load slowly, then a database is a good way to go. Postgres is open-source and very well-made, and it's easy to connect with JMP, SPSS and other programs. You may want to sample in this case. You don't have to normalize the data in the database. Otherwise, CSV is sharing-friendly.

Consider Apache Hive if you have 100M+ rows.

In terms of analysis, here are some starting points:

Describe one variable:

• Histogram
• Summary statistics (mean, range, standard deviation, min, max, etc)
• Are there outliers? (greater than 1.5x inter-quartile range)
• What sort of distribution does it follow? (normal, etc)

Describe relationship between variables:

• Scatter Plot
• Correlation

• Outliers? check out Mahalanobis distance

• Mosaic plot for categorical

• Contingency table for categorical

Predict a real number (like price): regression

• OLS regression or machine learning regression techniques

• when the technique used to predict is understandable by humans, this is called modeling. For example, a neural network can make predictions, but is generally not understandable. You can use regression to find Key Performance Indicators too.

Predict class membership or probability of class membership (like passed/failed): classification

• logistic regression or machine learning techniques, such as SVM

Put observations into "natural" groups: clustering

• Generally one finds "similar" observations by calculating the distance between them.

Put attributes into "natural" groups: factoring

• And other matrix operations such as PCA, NMF

Quantifying Risk = Standard Deviation, or proportion of times that "bad things" happen x how bad they are

Likelihood of a successfully completed iteration given x number of story points = Logistic Regression

Good luck!

Answer 2

If you're looking at system faults, you might be interested in the following paper employing machine learning techniques for fault diagnosis at eBay. It may give you a sense of what kind of data to collect or how one team approached a specific problem in a similar domain.

• Fault Diagnosis Using Decision Trees

If you're just getting started, something like RapidMiner or Orange might be a good software system to start playing with your data pretty quickly. Both of them can access the data in a variety of formats (file csv, database, among others).

Answer 3

Your question is so broad that the answer is: it depends. Still, to give some more useful answer I'll indicate what I think are common in Research.

Storing of data is very often done in text files. When doing statistical analyses you mostly work with a collection of one type of vectors. This can be seen as a table and written in csv format. The reason thins are often stored in plain-text, is because simply every tool can read them and it is easy to transform them.

About analyzing, this is a bit harder to be specific. If it is 2 dimensional, make a scatterplot. If it is high-dimensional, do PCA and see where the first principal components exist of to discover important variables. If you have time data, plot it. This is all so general that to be useful you have to really indicate better what your data is.

Answer 4

The one thing ROOT is really good at is storing enourmous amounts of data. ROOT is a C++ library used in particle physics; it also comes with Ruby and Python bindings, so you could use packages in these languages (e.g. NumPy or Scipy) to analyze the data when you find that ROOT offers to few possibilities out-of-the-box.

The ROOT fileformat can store trees or tuples, and entries can be read sequentially, so you do not need to keep all data in memory at the same time. This allows to analyze petabytes of data, something you wouldn't want to try with Excel or R.

The ROOT I/O documentation can be reached from here.