Data frame string matching from one to another and replace with original value

Question

i have two data frame , A and B . In A Data frame two column value and filed . and in B data frame have also value and filed column. i want to match the 'value' column of B to A of'Filed' column, replace the Filed of A to value of B.

A=

 Value                     Filed        

valid username            username
valid username           input_txtuserid
valid username           name
Password                 input_txtpassword
Password                 txtPassword
Login                     input_submit_log_in
Login                     loginid
LOG IN                    SIGNIN

B=

 Value                     Filed        

input_txtuserid           "JOHN"
input_txtpassword          "78945"
input_submit_log_in        "Sucessfully"
City                       "London"
PLACE                      "4-A avenue Street"
PHONE                      789456

I want my data frame to look like this:

C=

 Value                     Filed        

valid username            "JOHN"
Password                   "78945"
Login                       "Sucessfully"
City                       "London"
PLACE                      "4-A avenue Street"
PHONE                      789456

Answer 1

I would make a custom function to embed the logic and then apply it to the DataFrame by row.

import pandas as pd

A = pd.DataFrame({
    'Value':['valid username','valid username','valid username'],
    'Filed':['username', 'input_txtuserid', 'name']
})

B = pd.DataFrame({
    'Value':['input_txtuserid'],
    'Filed':['JOHN']
})


#dictionary mapping A.Filed and A.Value
_map = dict(zip(A.Filed.values, A.Value.values))



#Function embedding the logic
#It uses _map to determine the correct "filed" string
def get_correct_value(row, _map=_map):
    new_value = _map[row.Value]
    filed = row.Filed
    return new_value, filed



#Make a new df from the result of B.apply(function)
C = B.apply(get_correct_value, axis=1, result_type='expand')
C.columns = ['Value','Filed']
print(C)

#            Value Filed
#0  valid username  JOHN