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'"

You should post a sample of your original data to reproduce your problem. — Fabio Lamanna
– Fabio Lamanna, Commented Dec 11, 2015 at 15:34

Amy D · Accepted Answer · 2015-12-11 16:12:22Z

26

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')

answered Dec 11, 2015 at 16:12

Amy D

5812 gold badges7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jthorpe · Accepted Answer · 2021-11-16 14:06:42Z

4

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.

edited Nov 16, 2021 at 14:06

Jthorpe

10.3k2 gold badges55 silver badges75 bronze badges

answered Dec 11, 2015 at 16:43

Sara

2571 gold badge2 silver badges9 bronze badges

Comments

Sastivel Loganathan · Accepted Answer · 2019-10-29 16:56:01Z

3

Sending the left_on and Right_on parameters as arrays worked for me.

c = pd.merge(pd.DataFrame(a), pd.DataFrame(b), left_on=['video_id'], 
             right_on= ['video_id'], how='left')

answered Oct 29, 2019 at 16:56

Sastivel Loganathan

312 bronze badges

Collectives™ on Stack Overflow

Python pandas merge keyerror

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related