How can these two dataframes be merged on a specific key?

Question

I have two dataframes, both with a column 'hotelCode' that is type string. I made sure to convert both columns to string beforehand.

The first dataframe, we'll call old_DF looks like so:

and the second dataframe new_DF looks like:

I have been trying to merge these unsuccessfully. I've tried

final_DF = new_DF.join(old_DF, on = 'hotelCode')

and get this error:

I've tried a variety of things: changing the index name, various merge/join/concat and just haven't been successful.

Ideally, I will have a new dataframe where you have columns [[hotelCode, oldDate, newDate]] under one roof.

I think you're having exactly the same problem found here: https://stackoverflow.com/questions/52373285/pandas-join-on-string-datatype. Does the `merge` solution work, though a `merge` will grow this DataFrame a lot. Do you need to merge on the index **and** hotelCode? or simply concat? — ALollz, Jan 15 '20 at 21:30
can you show the output of `old_df.hotelCode.head()` and `new_df.hotelCode.head()`? — Max Power, Jan 15 '20 at 21:30
you should ensure the columns have the same variable type before merging the dataframes. But I'm confused how a column with values like "ALFTBW" could possibly be an int64 type in one of your dataframes (hence my question above) — Max Power, Jan 15 '20 at 21:32

score 3 · Accepted Answer · edited Jan 15 '20 at 21:34

3

import pandas as pd

final_DF = pd.merge(old_DF, new_DF, on='hotelCode', how='outer')

edited Jan 15 '20 at 21:34

James

answered Jan 15 '20 at 21:31

High-Octane

1 Answers1