finding patterns of two column values of a df with a dict and adding a new column based on that

user_11913986 0
Viewed 29

I have a df:

MSG_TYPE    MSG_TYPE_1
0   MT740   MT747
1   MT747   MT742
2   MT742   MT799
4   MT740   MT742
5   MT742   MT742

I have a dict as d:

[{'MSG_TYPE': 'MT740', 'MSG_TYPE_1': 'MT747', 'RELATIONSHIP': 'AMENDMENT_TO_AUTHORISISATION'}, {'MSG_TYPE': 'MT740', 'MSG_TYPE_1': 'MT742', 'RELATIONSHIP': 'REIMBURSEMENT_CLAIM_FOR'}, {'MSG_TYPE': 'MT740', 'MSG_TYPE_1': 'MT799', 'RELATIONSHIP': 'ADDITIONAL_INFORMATION'}, {'MSG_TYPE': 'MT742', 'MSG_TYPE_1': 'MT799', 'RELATIONSHIP': 'ADDITIONAL_INFORMATION'}, {'MSG_TYPE': 'MT742', 'MSG_TYPE_1': 'MT742', 'RELATIONSHIP': 'REIMBURSEMENT_CLAIM_FOR'}, {'MSG_TYPE': 'MT742', 'MSG_TYPE_1': 'MT740', 'RELATIONSHIP': 'REIMBURSEMENT_CLAIM_FOR'}, {'MSG_TYPE': 'MT742', 'MSG_TYPE_1': 'MT747', 'RELATIONSHIP': 'AMENDMENT_TO_AUTHORISISATION'}

I want to match the pattern of all the rown of the df with the dict d and if the patterns of a particular row is same as one of the key value pair entry and append a new column df['RELATIONSHIP'] with of the same entries of the match at the dict and if no match then NAN.

I have written my code accordingly:

for item in df.iterrows():
    for value in d:
        if item[1]==value['MSG_TYPE'] & item[2]==value['MSG_TYPE_1']:
            df['RELATIONSHIP']=value['RELATIONSHIP']

Which gives me an error:

IndexError: tuple index out of range

Is there a better pythonic way of doing this, as the dict key names are same as df column names.

pandas dataframe dictionary python
1 Answers

Try with merge:

df.merge(pd.DataFrame(d), on=['MSG_TYPE','MSG_TYPE_1'], how='left')

Output:

  MSG_TYPE MSG_TYPE_1                  RELATIONSHIP
0    MT740      MT747  AMENDMENT_TO_AUTHORISISATION
1    MT747      MT742                           NaN
2    MT742      MT799        ADDITIONAL_INFORMATION
3    MT740      MT742       REIMBURSEMENT_CLAIM_FOR
4    MT742      MT742       REIMBURSEMENT_CLAIM_FOR
user_42384080
Related