CODEWITHSUNDEEP
pythonpandasmerge
JJAN
JJAN

Reputation: 917

pandas merge_asof keys must be sorted error after sorting

I would like to merge a target column from one data frame to another. The merged data frame has many more keys and they are are close, but do not exactly match the original data frame. See an example below:

Original data frame (FDMA)

DMA 
130506  
130510  
130512  
130555  
130556

Merged data frame (ZC)

DMA        Distance (Miles)
1305060    303.87
1305061    305.35
1305062    278.80
1305065    299.94
1305067    291.83

pd.merge_asof(FDMA,ZC[['DMA','Distance (Miles)']],on='DMA')

This is what I am expecting

DMA     Distance (Miles)
130506  303.87
130510  291.83
130512  XXX
130555  XXX
130556  XXX

I have tried the above code and get a ValueError: right keys must be sorted error. I have sorted the values and reset the indexes, but still get the error. Any help is appreciated!

Upvotes: 12

Views: 27331

Answers (2)

DaveR
DaveR

Reputation: 2358

Also, when you have NA in your on column, pandas will trow the same error, so before merging you should use df.dropna() to solve that.

Upvotes: 4

Scott Boston
Scott Boston

Reputation: 153460

With the error code "ValueError: right keys must be sorted", the most effective solution is to add sort_values in the merge on the keys column:

pd.merge_asof(FDMA,ZC[['DMA','Distance (Miles)']].sort_values('DMA'),on='DMA')

This ensures that the dataframe is sorted on the join keys as required by pd.merge_asof.

Upvotes: 21

Related Questions