My question is similar to this, but not quite. I have two data frames:
A B
p1 1
p1 2
p3 3
p2 4
or
df1 = pd.DataFrame([('p1', 1), ('p1', 2), ('p3', 3), ('p2', 4)], columns=['A', 'B'])
and
X Y
p1 aaa
p2 bbb
p3 ccc
p4 ddd
p5 eee
p6 fff
p7 ggg
or
df2 = pd.DataFrame([('p1', 'aaa'), ('p2', 'bbb'), ('p3', 'ccc'), ('p4', 'ddd'), ('p5', 'eee'), ('p6', 'fff'), ('p7', 'ggg')], columns=['X', 'Y'])
I want to add a new column Y to df1 by looking up its value of column A in column X and return value in column Y. How would I do this nicely? In this case, the expected output for a new df1_new is:
A B Y
p1 1 aaa
p1 2 aaa
p3 3 ccc
p2 4 bbb
