Friday, January 12, 2018

Merge in R and preserve order of the input file

If you want to merge two data frames in R, the merge function will automatically sort alphabetically the resulting output data frame. Adding sort= might help but in that case the file is left unsorted so it might not always work.

> table.2<-read.table("test.2", head=TRUE,stringsAsFactors=FALSE)
> table.1<-read.table("test.1", head=TRUE,stringsAsFactors=FALSE)
> table.1
  SAMPLE   NUM
1      B  23.0
2      D  11.0
3      C 121.0
4      A   3.4
> table.2
  SAMPLE CODE
1      A    7
2      B   11
3      C   56
4      D   45



> merge(table.1, table.2, by.x="SAMPLE", by.y="SAMPLE")
  SAMPLE   NUM CODE
1      A   3.4    7
2      B  23.0   11
3      C 121.0   56
4      D  11.0   45

> merge(table.1, table.2, by.x="SAMPLE", by.y="SAMPLE", sort=F)
  SAMPLE   NUM CODE
1      B  23.0   11
2      D  11.0   45
3      C 121.0   56
4      A   3.4    7

In case sort=F doesn't work, create a new column in the data frame you wish to sort by, in this case data frame 1 and use for sorting after the merge.


> merger<-merge(table.1, table.2, by.x="SAMPLE", by.y="SAMPLE")

> merger[order(merger$order),]
  SAMPLE   NUM order CODE
2      B  23.0     1   11
4      D  11.0     2   45
3      C 121.0     3   56
1      A   3.4     4    7

No comments:

Post a Comment