Apply dictionary look-up function to compare pandas dataframe columns

user_10718568 0
Viewed 220

I'd like to apply a function to two columns (A and B) of a pandas dataframe that tests if each of their values match the same result in a dictionary. I'd like it to return the result to a third column.

I've tried the code below and close variants but I keep getting errors and I think there's something fundemental that I'm not understanding about the data structure. Can anyone explain where am I going wrong? I can imagine cumbersome alternative ways to do this but I'm sure there must be an elegant solution.

def do_they_match(A1,A2):
    if A1 in dictionary and A2 in dictionary and dictionary[A1] == dictionary[A2]:
        return 1
    else:
        return 0

df['match'] = df.apply(lambda x: do_they_match(x['A'],x['B']))
## also tried ## 
df = df.assign(link=lambda x: do_they_match(x['A'],x['B']))

For context, the errors I get are IndexError: ('A', 'occurred at index A') or TypeError: 'Series' objects are mutable, thus they cannot be hashed for the alternative code on the last line.The values in both dataframe columns and in the dictionary are all strings.

Thanks for the help!

pandas apply mapping dictionary python
1 Answers

You got the error because you are trying to apply the function passing parameters of data from the same row but with your syntax, the x in lambda x refers to a column. Thus, the code x['A'] is actually trying to take A as a row index for the current column x being processed. Each of the column in the dataframe will take turn as the column to be processed in this apply statement.

You have to make use of the axis= parameter of .apply() to instruct Pandas to take row-wise operations by passing axis=1.

Official document has explanation of the axis parameter:

axis {0 or ‘index’, 1 or ‘columns’}, default 0 Axis along which the function is applied:

0 or ‘index’: apply function to each column.

1 or ‘columns’: apply function to each row.

The default value is axis=0 to apply function to each column.

To get through the error, you can add axis=1 to your code:

df['match'] = df.apply(lambda x: do_they_match(x['A'], x['B']), axis=1)

Better solution is that you don't need to define the custom function and probably you can map the dictionary values by using the Pandas function .map() such as follows:

df['match'] = (df['A'].map(dictionary) == df['B'].map(dictionary)).astype(int)

We use astype() to convert the boolean result to integer 0 (for False) and 1 (for True)

For example, we have the following dataframe and dictionary:

df = pd.DataFrame({'A': ['x1', 'x2', 'x3'], 'B': ['y1', 'y2', 'y3']})

    A   B
0  x1  y1
1  x2  y2
2  x3  y3


dictionary = {'x1': 'apple', 'y1': 'orange', 'x2': 'banana', 'y2': 'banana', 'x3': 'peach'}

When we apply the code, we get:

df['match'] = (df['A'].map(dictionary) == df['B'].map(dictionary)).astype(int)

print(df)

    A   B  match
0  x1  y1      0
1  x2  y2      1
2  x3  y3      0
user_150706970
Related