Pandas: New column based on values of two other columns

Question

I have two columns one with OLD id number that (column b) and another column with the NEW id number (a). I want to find out what name (c) corresponded to the old id number given the information I have in a and c.

# Example dataset
print(df1)

# output
a  b  c
4  5  John
5  19 Joanna
1  4  Jenna
19 10 Jane
10 66 Johnna
16 16 JoJo

What I am trying to achieve:

print(df2)

# output
b  d 
5  Joanna
19 Jane
4  John
10 Johnna
66 na
16 JoJo

Answer 1

It's a self join.

df = pd.read_csv(io.StringIO("""a  b  c
4  5  John
5  19 Joanna
1  4  Jenna
19 10 Jane
10 66 Johnna
16 16 JoJo"""), sep="\s+")

df.merge(df, left_on="b", right_on="a").loc[:,["b_x","c_y"]].rename(columns={"b_x":"b","c_y":"c"})

	b	c
0	5	Joanna
1	19	Jane
2	4	John
3	10	Johnna
4	16	JoJo

Answer 2

df['d'] = df['b'].map(dict(df[['a', 'c']].to_numpy()))