Methods of Grouping and Summarizing in R Language

The operation of grouping and summarizing includes grouping one or more certain fields of two-dimensional structured data and then summarizing fields of each group. The following will introduce methods of grouping and summarizing in R language through an example. In order to make the example more typical, we’ll set two fields to be grouped and two summarizing operations.

Case description:

Please group data frame orders according to CLIENT and SELLERID, and then summate field AMOUNT and seek its maximum value respectively in each group.

Note: orders contains records of sales orders. Its source can be a database or a file. Such as orders<-read.table("sales.txt",sep="\t", header=TRUE).The first rows of data are as follows: 

Method 1: aggregate function

Code:

result1<-aggregate(orders$AMOUNT, orders[,c("SELLERID","CLIENT")],sum)

result2<-aggregate(orders$AMOUNT, orders[,c("SELLERID","CLIENT")],max)

result<-cbind(result1,result2$x)

Part of the computed result:

Code interpretation:

1.The name aggregate implies that it is a function specializing in grouping and summarizing. Both its input parameters and computed result are data frame and its usage is relatively simple.

2. aggregate function cannot perform multiple summarizing operations on grouped data, thus two lines of code are required to realize the operations of seeking sum and maxrespectively, then their results are combined using cbind. Obviously, the code is not satisfactory in performance and usability.

3. aggregate function has a strange requirement about the order of the fields to be grouped, that is, the fields must be in reversed order. In view of this, the code for grouping CLIENT first and then SELLERID should only be written as orders[,c("SELLERID","CLIENT")]. The code written according to the normal way of thinking will be incorrect instead.

4. Not only the code is written in an unnatural way, but the computed result is weird too by putting filed SELLERID before CLIENT. In reality, the code should be improved in order to make the computed result conform to the business logic.

Summary:aggregate function manages to perform the task after a fashion. But it is not good in performance and usability because the way of coding, computed result and business logic are inconsistent with each other.

Method 2: split+lapply function

Code:

sp<-split(orders,orders[,c("SELLERID","CLIENT")],drop=TRUE)

result1<-lapply(sp,FUN=function(x) sum(x$AMOUNT))

result2<-lapply(sp,FUN=function(x) max(x$AMOUNT))

result<-cbind(result1,result2)

Part of the computed result:

Code interpretation:

1. The role of split function is to group the data frame according to specified fields. No further computation is involved. lapply function can perform the same operation on data of each group. By working with each other, split and lapply can fulfill the task.

2.  Because the grouped data can be reused, this operation performs better than that using aggregate function.

3. As lapply function doesn't support multiple statistical approaches, two lines of code are required too to realize the operations of seeking sum and max respectively, and then use cbind to combine the results. What’s more, this operation requires an extra split function, so instead of enhancing the usability, it reduces it.

4. The grouping order is still unnatural and the code has to be written reversely as orders[,c("SELLERID","CLIENT")].

5.  The computed result needs a lot of modification which brings great inconvenience. It can be seen that the first column of the computed result is, in fact, the "SELLERID.CLIENT". The column needs to be split into two columns whose orders should be exchanged.

Summary:This operation improves some performance but the usability is obviously poor with inconsistency in the aspects of way of coding, business logic and the computed result.

lapply belongs to the family of apply function. Similar functions include sapply and tapply, whose usages differ on parameters. For example:

sp<-split(orders,orders[,c("SELLERID","CLIENT")],drop=TRUE)

result1<-sapply(sp,simplify=FALSE,FUN=function(x) sum(x$AMOUNT))

result2<-sapply(sp,simplify=FALSE,FUN=function(x) max(x$AMOUNT))

result<-cbind(result1,result2)

tapply specializes in data frame, which, by rights, is the most suitable one for fulfilling this task. But it isn't in fact. It applies only to the situation where a single field is required to be grouped. When it is used to group two fields together, the result will be two-dimensional matrix. This requires users to make further complicated processing. For example, the computed result of the line of code tapply(orders$AMOUNT, orders[,c("SELLERID","CLIENT")],function(x) sum(x))is as follows:

Third-party library functions

There are various disadvantages when using R's built-in functions to group and summarize. In response to the problem, we may consider using the third-party library functions, such as reshape, stack, etc. The stability and computational efficiency of these library functions is generally not as good as those of the built-in functions, and the information for their use is not many. Therefore it is difficult for them to fulfil the task. Here we won't go into any example about their use.

Third-party languages

Python, esProc and Perl can also be employed to fulfil this task. All of them can perform grouping and summarizing as well as structured data computing as R language can. We’ll briefly introduce solutions of esProc and python.

esProc

esProc can fulfil this task by simply using groups function. Its syntax is concise and easy to understand, as well as in line with the natural way of thinking. The code is as follows: result=orders.groups(CLIENT,SELLERID;sum(Amount),max(Amount))

The computed result, syntax and business logic are highly consistent with each other in esProc. Some of the computed results are as follows:

Python(pandas)

If python's built-in functions are used to deal with this task, the code will be rather complicated. Here pandas, the third-party function library, comes to help. pandas will first perform grouping operation using groupby function, then summarize using agg function. Its code, which is simpler than R languagebut not as good as esProc,is written like this: result=orders.groupby(['CLIENT','SELLERID']).agg({'AMOUNT':[sum,max]}).

Pandas' computed result and syntax are highly consistent with the business logic. Part of the computed result is as follows:

The step-by-step computational mode of grouping first then summarizing adopted by pandas is not always bad. Such as, it can increase performance in situations where grouping result is reused. esProc can also perform the step-by-step operation, the equivalent code is group(CLIENT,SELLERID).new(CLIENT,SELLERID,sum(AMOUNT),max(AMOUNT)).

How to Compute Moving Average in R Language

A moving average is used to smooth out a time series. Computing moving average is a typical case of ordered data computing. Its basic computing method is to create a subset composed of N consecutive members of a time series, compute the average of the set and shift the subset forward one by one. The following example teaches you how to compute moving average in R language.

Case description:

Data frame sales has two fields: salesDate and Amount of this date. Requirement: compute the moving average in three days. Computing steps include seeking sales amount average of the previous day, the current day and the next day, and shift forward along the dates. A part of the source data is as follows:

Code:

  filter(sales$Amount/3, rep(1, 3))

Computed result:

Code interpretation:

filter function can be used in R language to compute moving average, which produces concise code. This method is quite convenient.

Despite the convenience of the filter function, it is difficult to understand for beginners. For example, sales$Amount/3 means dividing the current value of field Amount by three,but when it is used in filter function, it may mean adding the three consecutive values together, then divide the sum by three. [1,1,1] is the value of expression rep(1,3), which is used here to specify the range of data fetching. In addition, because neither the name nor the parameters of filter function contain the words "average" and "moving", even many developers of R language don't know its use for computing moving average.

In fact, filter function is a universal linear filter. Its use is more than computing moving average. Its complete function reference is filter(x, filter, method = c("convolution", "recursive"),sides = 2, circular = FALSE, init).

Any modification of the requirement will make the code more difficult to understand. For example, the code for computing moving average of the current day and the previous two days cannot be written as filter(sales$Amount/3, rep(0,2)), it has to befilter(sales$Amount/3, rep(1,3), sides = 1).

Summary:

R language can compute moving average, but its code is rather elusive.

Third-party solutions

We can also use Python, esProc and Perl to handle this case. As R language, all of these languages can perform data statistics and analysis and compute moving average. The following introduces solutions of Python and esProc briefly.

Python(pandas)

Pandas is Python's third-party library function. It is powerful in processing structured data with basic data type imitating R's dataframe. At present the latest version is 0.14. Its code for handling this case is as follows:

   pandas.stats.moments.rolling_mean(sales["Amount"], 3)

The name of rolling_mean function is clear, even a developer without experience with pandas can understand it easily. The function’s usage is simple too. Its first parameter is the sequence being computed and the second parameter is N, which is the number of days in seeking moving average.

esProc

esProc is good at expressing business logic freely with agile syntax. Its expressions for relative position can solve computational problems of ordering data easily. The code is as follows:

   sales.(Amount{-1,1}.avg())

{-1,1} in the code represents a relative interval, that is, the three days of the previous day, the current day and the next day. It can be seen that moving average can be worked out clearly and flexibly by using a relative interval. If it is required, for example, to compute the moving average of the current day and the previous two days, we just need to change the interval to {-2,0}in esProc.

A relative interval is a set. esProc can also express an element of relative position. For example, it can compute sales growth rate with (Amount -Amount[-1]) conveniently. In contrast, the code in R language and Python is difficult to understand. 

A Method of Grouping and Summarizing Data of Big Text Files in R Language

It is common to use R language to group and summarize data of files.Sometimes we may find ourselves processing comparatively big files which have smaller computed result and bigger source data. We cannot load them wholly to the memory when we need to compute them. The only solutions could be batch importing and computing as well as result merging. We’ll use an example in the following to illustrate the way of R language to group and summarize data from big text files.

Here is a file, sales.txt, of 1G size, which contains a great number of records of sales orders. We want to group field CLIENT and summarize field AMOUNT. "\t"is used in the file as the column separator. The first rows of data are as follows:

R's solution:

	con <- file("E: \\sales.txt", "r")
	result=read.table(con,nrows=100000,sep="\t",header=TRUE)
	result<-aggregate(result[,4],list(result[,2]),sum)
	while(nrow(databatch<-read.table(con,header=FALSE,nrows=100000,sep="\t",col.names=c("ORDERID", "Group.1","SELLERID","x","ORDERDATE")))!=0) {
	databatch<-databatch[,c(2,4)]
	result<-rbind(result,databatch)
	result<-aggregate(result[,2],list(result[,1]),sum)
	}
	close(con)

Part of the computed result:

Code interpretation:

The 1^stline: Open the file handle.

The 2^nd ~ 3^rdline: Import the first batch of 100,000 rows of data, group and summarize them and save the result in result.

The 4^th ~ 8^thline: Import data by loop, with 100,000 rows of data per batch, and store them in the variable databatch. Then get the second and fourth field, i.e. "CLIENT" and "AMOUNT", merge databatch into result, and execute grouping operation.

It can be seen that, at a certain moment, only databatch,which includes 100,000 rows of data, and result, the summarizing result, have memory usage. Usually, the size of the latter is small and will not result in a memory overflow.

The 11^thline: Close the file handle.

Matters needing attention:

Data frame. Because the data frame of R language cannot directly perform the computing of big files, loop statement is necessary to help to do the job in this occasion. The steps are: import a batch of data and merge them into the data frame result; group and summarize result and then import the next batch of data. You can see that this part of code of loop statement is a little complicated.

Column name. As the first row of data is the column name, header=TRUE can be used in the first batch of data to directly set the column name. But the subsequent data hasn't column names and header=FALSE should be used to import data. The default column names are V1, V2 and so forth when header=FALSE is used. But the default column names are Group.1 and x after grouping and summarizing are executed, and col.names is needed to change the column names in order to maintain structure consistency both before and after grouping and summarizing and set the stage for the subsequent merging. The code about column names is worth our notice because it is easy to get wrong.

Alternative solutions:

Python, esProc and Perl can also perform the same operation. They can execute the grouping and summarizing of data from big text files and the subsequent structured data computing as R language does. We'll briefly introduce the coding methods used by esProc and Python.

esProc can process data in batches automatically, which requires no manual control from the programmers by loop statement and produces quite simple code:

Cursor is a data type used for structured data computing in esProc. Its usage is similar to that of the data frame, but it is better at processing big files and performing complicated computations. What's more, @t option in the code indicates that the first line of the file is the column name. So it is convenient to use the column name directly in subsequent computation.

Python's code structure, which also requires manual loop control, is similar to that of R language. But Python itself hasn't the structured data type, like data frame or cursor, so its code is executed in a lower level:

	from itertools import groupby
	from operator import itemgetter
	result = []
	myfile = open("E:\\sales.txt",'r')
	BUFSIZE = 10240000
	myfile.readline()
	lines = myfile.readlines(BUFSIZE)
	value=0
	while lines:
	for line in lines:
	record=line.split('\t')
	result.append([record[1],float(record[3])])
	result=sorted(result,key=lambda x:(x[0]))                #the sorting before grouping is executed
	batch=[]
	for key, items in groupby(result, itemgetter(0)):    # group using groupBy function
	value=0
	for subItem in items:value+=subItem[1]
	batch.append([key,value])                 # finally, merger the summarizing results into a two-dimensional array
	result=batch
	lines = myfile.readlines(BUFSIZE)
	myfile.close()

Except for the above two-dimensional array, Python can execute the operation with the third-party packages. For example, pandas has the structured data object similar to the data frame. pandas simplifies the code in a similar way as R language. But it lacks sufficient ability to perform big file computing, thus loop statement is still needed while programming. 

Methods of Accessing Excel Files by R language

There are many ways for R language to access Excel files, but each has its weaknesses. For example, xlsx package has complicated code and supports only Excel2007; RODBC has too many restrictions, is difficult to understand and unstable and goes wrong strangely. Though the method of saving the file in csv format is relatively common and stable, it operates inconveniently and lacks ability to process multiple files with program. Another method is to extract xml, but the complicated steps and code forbid us to use it.It's also not ideal to transform the file with a clipboard because part of the operation need to be done manually and we'd rather save the file in csv format.

However, all these problems can be avoided if we access Excel files using gdata package and meanwhile, write to Excel with WriteXLS. Both of the two packages support Excel2003 and Excel2007, operate stably, have easy and intuitive code and require no manual work. The following example is used to illustrate the method of accessing Excel with the two function packages.

Target:

There are multiple Excel files of same structure in the directory ordersData. Among these files containing sales order over the years, some are in the format of Excel2007, others are in the format of Excel2003. Please load them, compute the total sales amount of each client and write the result to result.xlsx. The following is some of the data of 2011.xlsx:

Code:

library(gdata)                                     

library(WriteXLS)                       

setwd("E: /ordersData")                  

fileList<-dir()   

orders<-read.xls(fileList[1])                                         

for (file in fileList[2:length(fileList)]){                         

orders<-rbind(orders,read.xls(file))

}

result<-aggregate(orders[,4], orders[c(2)],sum)

WriteXLS("result","result.xlsx")                                 

Some of the data of result.xlsx are as follows:

Code interpretation:

1. The two lines of code library(gdata) and library(WriteXLS)aim to import two third-party function packages, which have read.xls function and WriteXLS function to read and write Excel respectively.

2. The line of code fileList<-dir()lists all the files in the directory. The following for statement read files by loop and merge data into the data frame orders. If there are other files in the directory, they should be removed using wildcard characters.

3. This line of code result<-aggregate(orders[,4], orders[c(2)],sum)executes grouping and summarizing, in which orders[,4]represents summarizing column (i.e. Amount) and orders[c(2)] represents grouping column (i.e. Client).

4. Both read.xls and WriteXLS support the data type data.frame though they come from different packages, therefore, they can coordinate rather well.Besides, read.xls function can automatically identify the format of both Excel2003 and Eexcel2007, and is quite convenient to use.

5. All the code is concise and easy to grasp for beginners.

Note for use:

1.  Versions

gdata and WriteXLS are not R language’s built-in library functions, they are the third-party packages needing download and installation. What’s more, both of them require the Perl environment, so it is particularly important to choose an appropriate version. Through our trials, we find that 2.15.0 version of R language gets along well with 2.13.3 version of gdata and 3.5.0 version of WriteXLS. But something may go wrong if they operate with the newest Perl version and an older 5.14.2 version is thus required. Otherwise the following error report will appear:

Error in xls2sep(xls, sheet, verbose = verbose, ..., method = method,  :

  Intermediate file 'C:\Users\Thim\AppData\Local\Temp\RtmpMHvLZS\file224060624738.csv' missing!

2. Performance

gdata and WriteXLS have no problem in accessing small files, but they perform badly in handling bigger files (maybe because of Perl). For example, it takes 8 to 10 minutes to read an Excel file of 8 columns and 200,000 rows. To achieve a better performance, we recommend xlsx function package. But, of course, Excel2003 will be of no use in this occasion. In fact, xlsx performs just slightly better than gdata does. Therefore, in order to truly improve performance, it is recommended that all Excel files be transferred into 2007 format and xml files in them be uncompressed and data be read through resolving these xml files.

Alternative methods:

For the problems of version conflicts and poor performance that R language has, we have alternative solutions like Python, esProc, Perl etc. As R language, they can also access Excel files and perform data computing. In the following, we’ll introduce briefly esProc and Python.

esProc integrates the function of accessing EXCEL into its installation package, so it is no need for it to download the extra third-party packages. It can access Excel2003, Excel2007, Excel 2010 and even the older versions. Its code is as follows:

esProc's performance is satisfactory. It takes only 20 to 30 seconds for it to read an Excel file of 8 columns and 200,000 rows.

Python has a rather excellent performance, except that it requires the third-party packages as R language does. Pandas should have been able to complete the task of accessing xls file easily, but its installation under windows failed (after all, xls files are mainly produced under windows). Finally, we succeeded in performing this operation by using packages of both xlrd and xlwt3. Unfortunately, the two packages support only Excel2003 and produce much more complicated code:

    import xlwt3

    import xlrd

    from itertools import groupby

    from operator import itemgetter

    import os

    dir="E:/ordersData/"

    fileList =os.listdir(dir)

    rowList = []

    for f in fileList:

    book=xlrd.open_workbook(dir+f)    #open read-only workbook by loop

    sheet=book.sheet_by_index(0)

    nrows = sheet.nrows

    ncols = sheet.ncols

    for i in range(1,nrows):

    row_data = sheet.row_values(i)

    rowList.append(row_data)      #all records are appended to rowList

    rowList=sorted(rowList,key=lambda x:(x[1]))          #sort the data before     grouping

    result=[]

    for key, items in groupby(rowList, itemgetter(1)): # group using groupby function

        value1=0

    forsubItem in items:value1+=subItem[3]

    result.append([key,value1])              #merge the summarized result into 2D array in the end

    wBook=xlwt3.Workbook()                   # create a new writable workbook

    wSheet=wBook.add_sheet("sheet 1")

    wSheet.write(0,0,"Client")

    wSheet.write(0,1,"Sum")

    for row in range(len(result)):         #write data to the file by loop

    wSheet.write(row+1,0,result[row][0])

    wSheet.write(row+1,1,result[row][1])

    wBook.save(dir+"result.xls")                     #save the file

It is a far more complicated method than R language.