Python pandas merge keyerror

Question

Consistently getting a keyerror when I try to merge two data frames. The code:

c = pd.merge(a, b, on='video_id', how='left')

Based on internet research I double checked the dtype and coerced both to int:

a = pd.read_csv(filename, index_col=False, dtype={'video_id': np.int64}, low_memory=False)
b = pd.read_csv(videoinfo, index_col=False, dtype={'video_id': np.int64})

Renamed the columns (to make sure they match):

a.columns.values[2] = "video_id"
b.columns.values[0] = "video_id"

Coerced to df:

c = pd.merge(pd.DataFrame(a), pd.DataFrame(b), on='video_id', how='left')

Out of ideas as to why I'm still getting the keyerror. And it's always "KeyError: 'video_id'"

Answer 1

You want to be careful not to use df.columns.values to rename columns. Doing so screws with the indexing on your column names.

If you know which column names you're replacing, you can try something like this:

a.rename(columns={'old_col_name':'video_id'}, inplace = True)
b.rename(columns={'old_col_name':'video_id'}, inplace = True)

If you don't know the column names ahead of time, you can try:

col_names_a = a.columns
col_names_a[index] = 'video_id'
a.columns = col_names_a

Keep in mind, you actually don't need to use the same column names on both dataframes. Pandas allows you to specify the individual names in each dataframe

pd.merge(a, b, left_on = 'a_col', right_on = 'b_col', how = 'left')

Answer 2

There was a leading space in one of the dfs in the column name, 'video_id ' instead of 'video_id'. Not sure why the initial rename didn't fix that but it's fixed.