I have two dataframes of different lengths and I need to add a column to the first one with filtered values, e.g.

df1 = pd.DataFrame({'Object':['cup', 'brick', 'board', 'stone'], 'id':[2, 8, 9, 6]})
df1 = pd.DataFrame({'Thing':['cup', 'board'], 'color':['blue', 'grey']})

and I want to create

df = pd.DataFrame({'Thing':['cup', 'board'], 'color':['blue', 'grey'], 'id':[2, 9]})

All methods I tried to use complained about different lengths.


You can accomplish your task by using the merge operation in pandas as follows:

In [16]: df1 = pd.DataFrame({'Object':['cup', 'brick', 'board', 'stone'], 'id':[2, 8, 9, 6]})

In [17]: df1
  Object  id
0    cup   2
1  brick   8
2  board   9
3  stone   6

In [18]: df2 = pd.DataFrame({'Thing':['cup', 'board'], 'color':['blue', 'grey']})

In [19]: df2
   Thing color
0    cup  blue
1  board  grey

In [20]: df = df2.merge(df1, left_on='Thing',right_on='Object', how='inner')

In [21]: df
   Thing color Object  id
0    cup  blue    cup   2
1  board  grey  board   9

and then drop the column (df.drop('Object', inplace=True)) that you don't need. For more details look at the official documentation here. Also, check out this to see how you can use merge and join operations in pandas to do all kinds of dataframe manipulations!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.