Add a column from one pandas dataframe to another when both are indexed differently

Question

I'm new to python. I have two pandas dataframes that are indexed differently. I want to copy a column from one to another. Dataframe 1: Holds the id and class that each image belongs to

 ID index class 0 10472 10472 0 1 7655 7655 0 2 6197 6197 0 3 9741 9741 0 4 9169 9169 0

Dataframe 2: Holds the id of the image in index and the image data in data columns

 data index 5882 [[[255, 255, 255, 0], [255, 255, 255, 0], [255... 360 [[[0, 0, 0, 0], [0, 0, 0, 0], [0, 0, 0, 0], [0... 1906 [[[255, 255, 255, 0], [255, 255, 255, 0], [255... 3598 [[[255, 255, 255, 0], [232, 232, 247, 25], [34... 231 [[[255, 255, 255, 0], [234, 234, 234, 0], [57,...

I want to iterate through the dataframe1 and pick up the image id and look up the dataframe 2 for the matching id in the index and copy the 'data' column over to dataframe1. How could i do this (in a performance optimal way)?

jezrael · Accepted Answer · 2018-05-29 12:38:18Z

2

First for match data need same types, so if get different:

print (df1['index'].dtype) int64 print (df2.index.dtype) object

there are 2 possible solutions - convert index to integers by:

df2.index = df2.index.astype(int)

Or column to strings:

df1['index'] = df1['index'].astype(str)

Then use map by column data in df2:

df1['data'] = df1['index'].map(df2['data'])

Or if need add multiple columns from df2 (e.g. in real data) use join:

df1 = df1.join(df2, on=['index'])

edited May 29, 2018 at 12:38

answered May 29, 2018 at 12:28

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

6 Comments

redwolf_cr7 Over a year ago

I tried your first suggestion, but it returns 'NaN' for all values:

jezrael Over a year ago

What is print (df1.dtypes) and print (df2.index) ? Need same types, integers or strings

redwolf_cr7 Over a year ago

This is what is returned (in that order): ID int64 index int64 class int64 data object dtype: object Index([u'0', u'1', u'10', u'100', u'1000', u'10000', u'10001', u'10002', u'10003', u'10004', ... u'9990', u'9991', u'9992', u'9993', u'9994', u'9995', u'9996', u'9997', u'9998', u'9999'], dtype='object', name=u'index', length=12533)

redwolf_cr7 Over a year ago

Sorry about the formatting, but it looks like the df2 index is of type u'index'

jezrael Over a year ago

@Praveen - Added to answer also :) Thank you.

|

Collectives™ on Stack Overflow

Add a column from one pandas dataframe to another when both are indexed differently

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related