sort = FALSE does't generate equivalent results with data.frame.merge

[DavorJ]

Here is sample/test code:

A <- data.frame(a=1:4)
AB <- data.frame(a=2:4, b=1)
merge(A,AB,all.x = TRUE,sort=FALSE)
  a  b
1 2  1
2 3  1
3 4  1
4 1 NA

A <- data.table::data.table(a=1:4)
AB <- data.table::data.table(a=2:4, b=1)
merge(A,AB,all.x = TRUE,sort=FALSE)
   a  b
1: 1 NA
2: 2  1
3: 3  1
4: 4  1

This can have profound effects. Merge depends on type of A and data.frame/data.table are interchangeable in many scenario's. Changing type of A can end up causing fireworks at unexpected places.

I don't know what the correct behavior should be, but I do think the results should be the same.

[HughParsonage]

base::merge says for sort=FALSE the rows are in 'an unspecified order'. So I think you'll just have to check the ordering if you need sort=FALSE.

[MichaelChirico]

certainly the data.table result looks more natural in this case... this isn't SQL so getting rows back in some unpredictable order is an odd behavior

…

On Jan 18, 2018 10:44 PM, "HughParsonage" ***@***.***> wrote: base::merge says for sort=FALSE the rows are in 'an unspecified order'. So I think you'll just have to check the ordering if you need sort=FALSE. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#2574 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AHQQdZj3OWTywN2yNgCPzKS81QeCi_zkks5tL1jhgaJpZM4RjAlq> .

[DavorJ]

@MichaelChirico, natural, yes, but when I read the help for all.x:

logical; if TRUE, then extra rows will be added to the output, one for each row in x that has no matching row in y. These rows will have 'NA's in those columns that are usually filled with values from y. The default is FALSE, so that only rows with data from both x and y are included in the output.

I read the 'added' as meaning 'added at the end', which is base::merge behavior.

It might be nitpicking, but it did cause me some headscratching.

[arunsrinivasan]

data.table's merge seems to give different orders for merge(A, AB, all.x=TRUE, sort=FALSE) vs merge(AB, A, all.y=TRUE, sort=FALSE). Seems like it should be fixed on data.table side -- less surprises in base R, I'd agree.

[jangorecki]

Looks to be related to #2594

[jangorecki]

@arunsrinivasan don't you think the current behavior is better? all.x=T means that we return all rows from LHS data, and at the same time we retain order of LHS data. IMO this is good, and this is how Y[X] behaves. If base::merge documents that sort=FALSE gives unspecified order, then there is nothing to be consistent about there.