python - how to lookup and find a value in a specific column in column precedent and put its value in a new column in Pandas

Question

I want to do something like Vlookup in pandas, I have a two column data frame, need to check if 2nd column values(B) are valid in 1st column(A), if yes related row and 2nd column value to be inserted in a new column named C, below is sample table:

original data frame is:

A      B

a      -
b      a
c      a
d      b
e      d

preferred data frame will be:

A      B      C

a      -      N/A
b      a      -
c      a      -
d      b      a
e      d      b

actually I am beginner in python but in excel this could be easily done by a Vlookup between Column A and B and the result would be reverted in Column C.

below is the code I wrote but it is not complete and does not work:

import pandas as pd
excel_file ='D:TestTest.xlsx'
data=pd.read_excel(excel_file, sheet_name= 0)
df=pd.DataFrame(data,columns=['A','B'])
lr = df.index.values.astype(int)[-1]
for j in range(0,2):
for i in range(1,lr):
C = []
row=0
for i in df.iloc[:,1]:
    df["C"]=df.iloc[:,0].str.match(i)
    if i == "-":
    C[row]=C.append(i)
    row+=1
elif df.at[i,['Index']]:
    idx = next(iter(df[df['Index'] == True].index), 'no match')
    df.at[i,"C"]=df.iloc[idx,1]



print(df)

Answer 1

You could fill C using np.where and then map C using a dictionary of A and B

import pandas as pd
import numpy as np
df = pd.DataFrame({'A': {0: 'a', 1: 'b', 2: 'c', 3: 'd', 4: 'e'},
 'B': {0: '-', 1: 'a', 2: 'a', 3: 'b', 4: 'd'}})

df['C'] = np.where(df['B'].isin(df['A'].values), df['B'], np.nan)
df['C'] = df['C'].map(dict(zip(df.A.values, df.B.values)))