Pivot tables are a a growing staple for analysis in excel yet they remain limited to the functionality which Microsoft has chosen to include. Typical operations are the inclusion of filters, choice over rows, columns, and maths operations. In R this functionality is not always so straightforward. That is until you try sqldf(). The sqldf package implements sql (Structured Query Language) query functionality onto traditional data.frames. A simple example being:

sqldf("select * from irisTable")

This simple statement will request all columns (all indicated with *) from a table which you are basing your work on. Before we start we will create the sample data. We will use the Iris dataset and change column names to remove any full stops and spaces.

irisTable <- iris

names(irisTable) <- c("Sepal_length", "Sepal_Width", "Petal_length", "Petal_width", "Species")

However, what if we only want a selection of all columns? Further, we may wish to order our data by a particular column. This can be achieved by appending ‘order by desc or asc.

sqldf("select Species, Sepal_length from irisTable")

By specifying the column names as they are displayed in the data.frame we can select these specific columns. At this stage we have the basic functionality available in the standard Pivot Table tool. Moving on to aggregating our data we can use the ‘group by’ function as demonstrated below.

sqldf("select Species, count(*) from irisTable group by Species")

Having added the group by statement we are now bringing the real power of Pivot Tables to R. If we assume that Species is a list of row names we have brought together all of our records into a much smaller number of categories. Questions we may now want to ask are: What is the average Sepal Length of each Species? What is the Min, Max or average petal length? these function may be simply applied by wrapping the chosen variable with the function you wish to perform.

sqldf("select Species, avg(Sepal_length) from irisTable group by Species")

A full list of these functions can be found at W3 SQL. In addition to math operations a number of text based operations are available such as length() which determines the length of a string, UCASE() converts to upper-case and LCASE converts to lower case.

The next aspect of Pivot Tables are filters. Filters allow subsets of the data to be generated for example we want to study a specific district, or are looking for values between two points. You will notice that once we have used the ‘group by’ statement we use ‘having’ rather than ‘where’.

sqldf("select Species, Sepal_length from irisTable group by Species having Sepal_length between 5 and 6")

sqldf("select Species, Sepal_length from irisTable group by Species having Species='virginica'")

The above two example highlight the use of where with two different approached. The first example is looking for values between, and including 0 and 10. The second example is looking for all records relating to Berkshire. It should be noted that where clauses are not case sensitive.

One issue which many users experience in R is the generated field names created during joins, or maths functions. In its basic format, sqldf will produce similar column names however this can be rectified using the ‘as’ statement.

sqldf("select Species, Sepal_length as 'Avg Sepal Length' from irisTable group by Species having Species='virginica'")

Though brief, this tutorial is able to address all and more of the functionality found within Pivot Tables and yet leaves many features of sqldf unused. Additional functionality includes table join, SQLite backend databases and data recording.