Hopefully someone will provide a better answer, but in case no one does, this will definitely work, so…
Zeroth, I'm assuming you don't want to just end up sorted on loan
, but to preserve whatever original order was in x
, which may or may not have anything to do with the order of the loan
column. (Otherwise, the problem is easier, and less interesting.)
First, you're asking it to sort based on the join keys. As the docs explain, that's the default when you don't pass a sort
argument.
Second, if you don't sort based on the join keys, the rows will end up grouped together, such that two rows that merged from the same source row end up next to each other, which means you're still going to get a
, c
, b
.
You can work around this by getting the rows grouped together in the order they appear in the original x
by just merging again with x
(on either side, it doesn't really matter), or by reindexing based on x
if you prefer. Like this:
x.merge(x.merge(y, how='left', on='state', sort=False))
Alternatively, you can cram an x-index in there with reset_index
, then just sort on that, like this:
x.reset_index().merge(y, how='left', on='state', sort=False).sort('index')
Either way obviously seems a bit wasteful, and clumsy… so, as I said, hopefully there's a better answer that I'm just not seeing at the moment. But if not, that works.