Menu
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!
211
Both DataFrames must be sorted by the key. For each row in the left DataFrame: A “backward” search selects the last row in the right DataFrame whose ‘on’ key is less than or equal to the left’s key.
merge_asof () function Perform an asof merge. This is similar to a left-join except that we match on nearest key rather than equal keys. Both DataFrames must be sorted by the key.
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. Thanks for contributing an answer to Stack Overflow!
merge_asof() function. Perform an asof merge. This is similar to a left-join except that we match on nearest key rather than equal keys. Both DataFrames must be sorted by the key. For each row in the left DataFrame: A “backward” search selects the last row in the right DataFrame whose 'on' key is less than or equal to the left’s key.
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.
124
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
