Merging two data.frame objects while preserving the rows’ order

Merging two data.frame objects in R is very easily done by using the merge function. While being very powerful, the merge function does not (as of yet) offer to return a merged data.frame that preserved the original order of, one of the two merged, data.frame objects.
In this post I describe this problem, and offer some easy to use code to solve it.

Let us start with a simple example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
    x <- data.frame(
           ref = c( 'Ref1', 'Ref2' )
         , label = c( 'Label01', 'Label02' )
         )
    y <- data.frame(
          id = c( 'A1', 'C2', 'B3', 'D4' )
        , ref = c( 'Ref1', 'Ref2' , 'Ref3','Ref1' )
        , val = c( 1.11, 2.22, 3.33, 4.44 )
        )
 
#######################
# having a look at the two data.frame objects:
> x
   ref   label
1 Ref1 Label01
2 Ref2 Label02
> y
  id  ref  val
1 A1 Ref1 1.11
2 C2 Ref2 2.22
3 B3 Ref3 3.33
4 D4 Ref1 4.44

If we will now merge the two objects, we will find that the order of the rows is different then the original order of the “y” object. This is true whether we use “sort =T” or “sort=F”. You can notice that the original order was an ascending order of the “val” variable:

1
2
3
4
5
6
7
8
9
10
11
12
> merge( x, y, by='ref', all.y = T, sort= T)
   ref   label id  val
1 Ref1 Label01 A1 1.11
2 Ref1 Label01 D4 4.44
3 Ref2 Label02 C2 2.22
4 Ref3    <NA> B3 3.33
> merge( x, y, by='ref', all.y = T, sort=F )
   ref   label id  val
1 Ref1 Label01 A1 1.11
2 Ref1 Label01 D4 4.44
3 Ref2 Label02 C2 2.22
4 Ref3    <NA> B3 3.33

This is explained in the help page of ?merge:

The rows are by default lexicographically sorted on the common columns, but for ‘sort = FALSE’ are in an unspecified order.

Or put differently: sort=FALSE doesn’t preserve the order of any of the two entered data.frame objects (x or y); instead it gives us an
unspecified (potentially random) order.

However, it can so happen that we want to make sure the order of the resulting merged data.frame objects ARE ordered according to the order of one of the two original objects. In order to make sure of that, we could add an extra “id” (row index number) sequence on the dataframe we wish to sort on. Then, we can merge the two data.frame objects, sort by the sequence, and delete the sequence. (this was previously mentioned on the R-help mailing list by Bart Joosen).

Following is a function that implements this logic, followed by an example for its use:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
############## function:
	merge.with.order <- function(x,y, ..., sort = T, keep_order)
	{
		# this function works just like merge, only that it adds the option to return the merged data.frame ordered by x (1) or by y (2)
		add.id.column.to.data <- function(DATA)
		{
			data.frame(DATA, id... = seq_len(nrow(DATA)))
		}
		# add.id.column.to.data(data.frame(x = rnorm(5), x2 = rnorm(5)))
		order.by.id...and.remove.it <- function(DATA)
		{
			# gets in a data.frame with the "id..." column.  Orders by it and returns it
			if(!any(colnames(DATA)=="id...")) stop("The function order.by.id...and.remove.it only works with data.frame objects which includes the 'id...' order column")
 
			ss_r <- order(DATA$id...)
			ss_c <- colnames(DATA) != "id..."
			DATA[ss_r, ss_c]		
		}
 
		# tmp <- function(x) x==1; 1	# why we must check what to do if it is missing or not...
		# tmp()
 
		if(!missing(keep_order))
		{
			if(keep_order == 1) return(order.by.id...and.remove.it(merge(x=add.id.column.to.data(x),y=y,..., sort = FALSE)))
			if(keep_order == 2) return(order.by.id...and.remove.it(merge(x=x,y=add.id.column.to.data(y),..., sort = FALSE)))
			# if you didn't get "return" by now - issue a warning.
			warning("The function merge.with.order only accepts NULL/1/2 values for the keep_order variable")
		} else {return(merge(x=x,y=y,..., sort = sort))}
	}
 
######### example:
>     merge( x.labels, x.vals, by='ref', all.y = T, sort=F )
   ref   label id  val
1 Ref1 Label01 A1 1.11
2 Ref1 Label01 D4 4.44
3 Ref2 Label02 C2 2.22
4 Ref3    <NA> B3 3.33
>     merge.with.order( x.labels, x.vals, by='ref', all.y = T, sort=F ,keep_order = 1)
   ref   label id  val
1 Ref1 Label01 A1 1.11
2 Ref1 Label01 D4 4.44
3 Ref2 Label02 C2 2.22
4 Ref3    <NA> B3 3.33
>     merge.with.order( x.labels, x.vals, by='ref', all.y = T, sort=F ,keep_order = 2) # yay - works as we wanted it to...
   ref   label id  val
1 Ref1 Label01 A1 1.11
3 Ref2 Label02 C2 2.22
4 Ref3    <NA> B3 3.33
2 Ref1 Label01 D4 4.44

Here is a description for how to use the keep_order parameter:

keep_order can accept the numbers 1 or 2, in which case it will make sure the resulting merged data.frame will be ordered according to the original order of rows of the data.frame entered to x (if keep_order=1) or to y (if keep_order=2). If keep_order is missing, merge will continue working as usual. If keep_order gets some input other then 1 or 2, it will issue a warning that it doesn’t accept these values, but will continue working as merge normally would. Notice that the parameter “sort” is practically overridden when using keep_order (with the value 1 or 2).

The same code can be used to modify the original merge.data.frame function in base R, so to allow the use of the keep_order, here is a link to the patched merge.data.frame function (on github). If you can think of any ways to improve the function (or happen to notice a bug) please let me know either on github or in the comments. (also saying that you found the function to be useful will be fun to know about :) )

Update: Thanks to KY’s comment, I noticed the ?join function in the {plyr} library. This function is similar to merge (with less features, yet faster), and also automatically keeps the order of the x (first) data.frame used for merging, as explained in the ?join help page:

Unlike merge, (join) preserves the order of x no matter what join type is used. If needed, rows from y will be added to the bottom. Join is often faster than merge, although it is somewhat less featureful – it currently offers no way to rename output or merge on different variables in the x and y data frames.

reshaping data using melt and cast

Aggregation and Restructuring data (from “R in Action”)

The followings introductory post is intended for new users of R.  It deals with the restructuring of data: what it is and how to perform it using base R functions and the {reshape} package.

This is a guest article by Dr. Robert I. Kabacoff, the founder of (one of) the first online R tutorials websites: Quick-R. Kabacoff has recently published the book ”R in Action“, providing a detailed walk-through for the R language based on various examples for illustrating R’s features (data manipulation, statistical methods, graphics, and so on…). The previous guest post by Kabacoff introduced data.frame objects in R.

For readers of this blog, there is a 38% discount off the “R in Action” book (as well as all other eBooks, pBooks and MEAPs at Manning publishing house), simply by using the code rblogg38 when reaching checkout.

Let us now talk about the Aggregation and Restructuring of data in R:

Aggregation and Restructuring

R provides a number of powerful methods for aggregating and reshaping data. When you aggregate data, you replace groups of observations with summary statistics based on those observations. When you reshape data, you alter the structure (rows and columns) determining how the data is organized. This article describes a variety of methods for accomplishing these tasks.

We’ll use the mtcars data frame that’s included with the base installation of R. This dataset, extracted from Motor Trend magazine (1974), describes the design and performance characteristics (number of cylinders, displacement, horsepower, mpg, and so on) for 34 automobiles. To learn more about the dataset, see help(mtcars).

Transpose

The transpose (reversing rows and columns) is perhaps the simplest method of reshaping a dataset. Use the t() function to transpose a matrix or a data frame. In the latter case, row names become variable (column) names. An example is presented in the next listing.

Listing 1 Transposing a dataset

1
2
3
4
5
6
7
8
9
10
11
12
13
14
> cars <- mtcars[1:5,1:4]
> cars
                  mpg  cyl disp  hp
Mazda RX4         21.0   6  160 110
Mazda RX4 Wag     21.0   6  160 110
Datsun 710        22.8   4  108 93
Hornet 4 Drive    21.4   6  258 110
Hornet Sportabout 18.7   8  360 175
> t(cars)
     Mazda RX4 Mazda RX4 Wag Datsun 710 Hornet 4 Drive Hornet Sportabout
mpg         21        21           22.8           21.4              18.7
cyl          6         6            4.0            6.0               8.0
disp       160       160          108.0          258.0             360.0
hp         110       110           93.0           110.0            175.0

Listing 1 uses a subset of the mtcars dataset in order to conserve space on the page. You’ll see a more flexible way of transposing data when we look at the reshape package later in this article.

Aggregating data

It’s relatively easy to collapse data in R using one or more by variables and a defined function. The format is

1
aggregate(x, by, FUN)

where x is the data object to be collapsed, by is a list of variables that will be crossed to form the new observations, and FUN is the scalar function used to calculate summary statistics that will make up the new observation values.

As an example, we’ll aggregate the mtcars data by number of cylinders and gears, returning means on each of the numeric variables (see the next listing).

Listing 2 Aggregating data

1
2
3
4
5
6
7
8
9
10
11
12
13
> options(digits=3)
> attach(mtcars)
> aggdata <-aggregate(mtcars, by=list(cyl,gear), FUN=mean, na.rm=TRUE)
> aggdata
  Group.1 Group.2  mpg cyl disp  hp drat   wt qsec  vs   am gear carb
1       4       3 21.5   4  120  97 3.70 2.46 20.0 1.0 0.00    3 1.00
2       6       3 19.8   6  242 108 2.92 3.34 19.8 1.0 0.00    3 1.00
3       8       3 15.1   8  358 194 3.12 4.10 17.1 0.0 0.00    3 3.08
4       4       4 26.9   4  103  76 4.11 2.38 19.6 1.0 0.75    4 1.50
5       6       4 19.8   6  164 116 3.91 3.09 17.7 0.5 0.50    4 4.00
6       4       5 28.2   4  108 102 4.10 1.83 16.8 0.5 1.00    5 2.00
7       6       5 19.7   6  145 175 3.62 2.77 15.5 0.0 1.00    5 6.00
8       8       5 15.4   8  326 300 3.88 3.37 14.6 0.0 1.00    5 6.00

In these results, Group.1 represents the number of cylinders (4, 6, or 8) and Group.2 represents the number of gears (3, 4, or 5). For example, cars with 4 cylinders and 3 gears have a mean of 21.5 miles per gallon (mpg).

When you’re using the aggregate() function , the by variables must be in a list (even if there’s only one). You can declare a custom name for the groups from within the list, for instance, using by=list(Group.cyl=cyl, Group.gears=gear).

The function specified can be any built-in or user-provided function. This gives the aggregate command a great deal of power. But when it comes to power, nothing beats the reshape package.

The reshape package

The reshape package is a tremendously versatile approach to both restructuring and aggregating datasets. Because of this versatility, it can be a bit challenging to learn.

We’ll go through the process slowly and use a small dataset so that it’s clear what’s happening. Because reshape isn’t included in the standard installation of R, you’ll need to install it one time, using install.packages(“reshape”).

Basically, you’ll “melt” data so that each row is a unique ID-variable combination. Then you’ll “cast” the melted data into any shape you desire. During the cast, you can aggregate the data with any function you wish. The dataset you’ll be working with is shown in table 1.

Table 1 The original dataset (mydata)

ID

Time

X1

X2

1156
1235
2161
2224

 

In this dataset, the measurements are the values in the last two columns (5, 6, 3, 5, 6, 1, 2, and 4). Each measurement is uniquely identified by a combination of ID variables (in this case ID, Time, and whether the measurement is on X1 or X2). For example, the measured value 5 in the first row is uniquely identified by knowing that it’s from observation (ID) 1, at Time 1, and on variable X1.

Melting

When you melt a dataset, you restructure it into a format where each measured variable is in its own row, along with the ID variables needed to uniquely identify it. If you melt the data from table 1, using the following code

1
2
library(reshape)
md <- melt(mydata, id=(c("id", "time")))

You end up with the structure shown in table 2.

Table 2 The melted dataset

ID

Time

Variable

Value

11X15
12X13
21X16
22X12
11X26
12X25
21X21
22X24

 

Note that you must specify the variables needed to uniquely identify each measurement (ID and Time) and that the variable indicating the measurement variable names (X1 or X2) is created for you automatically.

Now that you have your data in a melted form, you can recast it into any shape, using the cast() function.

Casting

The cast() function starts with melted data and reshapes it using a formula that you provide and an (optional) function used to aggregate the data. The format is

1
newdata <- cast(md, formula, FUN)

Where md is the melted data, formula describes the desired end result, and FUN is the (optional) aggregating function. The formula takes the form

1
rowvar1 + rowvar2 + …  ~  colvar1 + colvar2 +

In this formula, rowvar1 + rowvar2 + … define the set of crossed variables that define the rows, and colvar1 + colvar2 + … define the set of crossed variables that define the columns. See the examples in figure 1. (click to enlarge the image)

Figure 1 Reshaping data with the melt() and cast() functions

Because the formulas on the right side (d, e, and f) don’t include a function, the data is reshaped. In contrast, the examples on the left side (a, b, and c) specify the mean as an aggregating function. Thus the data are not only reshaped but aggregated as well. For example, (a) gives the means on X1 and X2 averaged over time for each observation. Example (b) gives the mean scores of X1 and X2 at Time 1 and Time 2, averaged over observations. In (c) you have the mean score for each observation at Time 1 and Time 2, averaged over X1 and X2.

As you can see, the flexibility provided by the melt() and cast() functions is amazing. There are many times when you’ll have to reshape or aggregate your data prior to analysis. For example, you’ll typically need to place your data in what’s called long format resembling table 2 when analyzing repeated measures data (data where multiple measures are recorded for each observation).

Summary

Chapter 5 of R in Action reviews many of the dozens of mathematical, statistical, and probability functions that are useful for manipulating data. In this article, we have briefly explored several ways of aggregating and restructuring data.

 

This article first appeared as chapter 5.6 from the “R in action book, and is published with permission from Manning publishing house.  Other books in this serious which you might be interested in are (see the beginning of this post for a discount code):

Top 20 R posts of 2011 (and some R-bloggers statistics)

R-bloggers.com is now two years young. The site is an (unofficial) online R journal written by bloggers who agreed to contribute their R articles to the site.
In this post I wish to celebrate R-bloggers’ second birthmounth by sharing with you:

  1. Links to the top 20 posts of 2011
  2. Statistics on “how well” R-bloggers did this year
  3. An invitation for sponsors/supporters to help keep the site alive

1. Top 24 R posts of 2011

R-bloggers’ success is largely owed to the content submitted by the R bloggers themselves.  The R community currently has almost 300 active R bloggers (links to the blogs are clearly visible in the right navigation bar on the R-bloggers homepage).  In the past year, these bloggers wrote over 2800 posts about R.

Here is a list of the top visited posts on the site in 2011:

  1. How much of r is written in r
  2. Cpu and gpu trends over time
  3. Select operations on r data frames
  4. Getting started with sweave r latex eclipse statet texlipse
  5. Delete rows from r data frame
  6. Amanda cox on how the new york times graphics department uses r
  7. Hipster programming languages
  8. Opendata r google easy maps
  9. New r generated video has stackoverflow posting behavior changed over time
  10. SNA visualising an email box with r
  11. 100 prisoners 100 lines of code
  12. Google ai challenge languages used by the best programmers
  13. Basics on markov chain for parents
  14. Top 10 algorithms in data mining
  15. A million random digits review of reviews
  16. Character occurrence in passwords
  17. Setting graph margins in r using the par function and lots of cow milk
  18. The new r compiler package in r 2 13 0 some first experiments
  19. Tutorial principal components analysis pca in r
  20. Making guis using c and r with the help of r net

2. Statistics – how well did R-bloggers do this year

There are several matrices one can consider when evaluating the success of a website.  I’ll present a few of them here and will begin by talking about the visitors to the site.

This year, the site was visited by over 665,000 “Unique Visitors.”  There was a total of over 1.4 million visits and over 2.8 million page-views.  People have surfed the site from over 200 countries, with the greatest number of visitors coming from the United States (~40%) and then followed by the United Kingdom (6.9%), Germany (6.6%), Canada (4.7%), France (3.3%), and other countries.

The site has received between 15,000 to 45,000 visits a week in the past few months, and I suspect this number will remain stable in the next few months (unless something very interesting will happen).

I believe this number will stay constant thanks to visitors’ loyalty: 55% of the site’s visits came from returning users.

Another indicator of reader loyalty is the number of subscribers to R-bloggers as counted by feedburner, which includes both RSS readers and e-mail subscribers.  The range of subscribers is estimated to be between 5600 to 5900.

Thus, I am very happy to see that R-bloggers continues to succeed in offering a real service to the global R users community.

3. Invitation to sponsor/advertise on R-bloggers

This year I was sadly accused by google adsense of click fraud (which I did not do, but have no way of proving my innocence).  Therefor, I am no longer able to use google adsense to sustain R-bloggers high monthly bills, and I turned to rely on direct  sponsoring of R-bloggers.

If you are interested in sponsoring/placing-ads/supporting R-bloggers, then you are welcome to contact me.

Happy new year!
Yours,
Tal Galili