Several years ago, companies weren't very diligent in digitizing their data, and therefore most commercial IT systems would generate data extracts with thousands of entries only. This has changed and we've witnessed more and more people running into big data issues. Not just geeks, but consultants, project managers, and auditors; individuals not trained as data scientists.

We were reminded of this during a recent engagement where we used Cloud Link to analyze significant swaths of the Swiss population. We combined, filtered and scored millions of text and numeric data entries, a task impossible for Excel to achieve.

Now don't get us wrong: we love Excel and use it a lot. However, if Excel is all you know and you have a few minutes to spare, then in this post we'd like to invite you to experiment something different: R.
We'll focus on a few basic but useful operations: opening a data file, doing some exploratory stats, filtering relevant entries, storing these entries into a separate smaller file.

Why should you join in? R is orders of magnitude faster than Excel when working with big data sets and chances are you'll run into this challenge sooner than you think. It's a good investment of your time.

Step 1: Download and install R with this public link (it's free, how great is that). If you want to use your work-computer but don't have installation rights then don't worry because you should still be OK. Most companies include R in their accepted applications so you can order it via your company's standard procurement process.

Step 2 (optional): Install R-Studio which provides a more intuitive programming environment than the R console does. You don't need to for this tutorial though.

Step 3: Create a folder on your Desktop called "R_Experiment" and store in it this example data file. It's a CSV (Comma Separated Values) file, one of the main standard formats by which large data sets are shared. Values are usually separated by commas or tabs, the latter being the case in this example. CSV's are human readable so you can open the file with any text editor (like Notepad) if you want to look at the 8 entries in our example.

Step 4: Great! All set to start. Launch R (or R-Studio) and type into the R console the "set working directory" command:
setwd("C:\Users\YourUserName\Desktop\R_Experiment")
With this command you are asking R to work from the folder you previously created. Note that your path will look different if you are on Mac or Linux.

Step 5: Now that you are in the right folder, you can read the file into R as so:
mydata <- read.csv("example.csv", sep="\t")
Variable "mydata" will hold the entire table of data from the CSV file, it's type is a data-frame. This is also a good place to get used to the "<-" notation in R, which you can simply imagine as "=". Finally the above command specifies the value-separator as tab ("\t" denotes a tab). You can change the separator to include any character you want, for example sep=","

Step 6: OK, so you've successfully loaded the contents from the CSV and stored them into variable "mydata". Let's quickly check by printing the first 2 rows:
head(mydata, 2)
Let's go a bit further and ask for some exploratory statistics:
summary(mydata)
Did you see that R automatically recognized which columns contain text versus numbers and applied the relevant statistics based on each case? Pretty cool.

Step 7: Now for the most complex command of this tutorial, we're going to filter all entries where the department is "IT":
filterdata <- mydata[mydata$dept == "IT", ]
Let's break it down.
First, consider the left side of the command "filterdata <- ". This simply means that, whatever happens on the right side, we're going to store it into a variable called "filterdata".
The rest of the command tells R to select data-frame "mydata" and filter out all rows where mydata$dept equals "IT". The $ sign is how you refer to a column within a data-frame, so "mydata$dept" means column "dept" from data-frame "mydata". Also the "," followed by nothing means that we don't want to apply a second filter on the columns: we want the full row returned where dept == "IT".
It's a bit much, we know, but you'll quickly get used to this notation. It's repetitive and there are tons of tutorials out there to help.

Step 8 (final): All that's left is to store the filtered data into a new file. Execute:
write.table(filterdata, file="filtered.csv", sep="\t", row.names = FALSE)
This command tells R to write the data-frame stored in variable "filterdata" into a file called "filtered.csv". This file will be stored to the working directory by default. Finally it requests the output file to use tabs as separators (annotated with sep="\t") and disables the row.names option, else R will start each row with a number which is not something we need.

Congrats, we're done! The powerful thing is that this approach will still work if you process a file with millions of entries. You can now open it, explore the data, filter out the relevant entries and store them in a separate file so that you can continue the analysis in a tool you are more familiar with, like Excel.