compare pandas dataframes with different lengths

Question

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.

Answer 1

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
Out[17]: 
  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
Out[19]: 
   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
Out[21]: 
   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!