Get intersection between 2 columns using Pandas

I came across a task wherein I had to work on ticket logs extracted as an excel file. Each ticket field was assigned a column. Ticket number, service name, summary and work log are some of the column names. The specific task is to check if engineer names appeared on the work log column. I was able to do this in excel by creating an array with the engineer names and created a new column with the formula: 

=SUMPRODUCT(--ISNUMBER(SEARCH(<array name>,<column>)))>0

It did the trick. It shows true if the name appeared else, it would yield false. 

However...I wanted to do more. I wanted to output which names appeared in the work log instead of just true or false. I got crazy in googling the correct formula, so I tried to use the Pandas library to find the intersection.

Here is a sample file that I used. I called it 'Book1.xlsx'. Column A containts the Worklog with random strings.

The names in this case would be "dog" and "cat". The task would be to check if "dog" and "cat" appeared in the "Worklog" column.

To do this, import pandas and initiate the names. In this case, I used  "uniqueNames" for "dog" and "cat". The converted the string to a list.

The file is opened and assigned to the variable df.

import pandas as pd uniqueNames = 'dog cat' #converted to list unik = uniqueNames.split() #open file df = pd.read_excel('Book1.xlsx')

Processing the columns:

''' create Intersection Y/N column. This script will check if the text in unik are in the Worklog column. This is the same as the excel formula mentioned earlier. ''' df['Intersection Y/N'] = df['Worklog'].str.contains('|'.join(unik)) #show output print(df)


Here is the output of df. This is the same as the output of the excel formula.

In [12]: df Out[12]: Worklog Intersection Y/N 0 rabbit\ndog\napples\nkeyboard True 1 TV\ncat\ndog\ngrapes True 2 screen\ndog True



Now....to do more.
#convert the Worklog rows to a list df['worklogSplit'] = df.Worklog.str.split() #insert the uniqueNames string as a new column of each row df.insert(1, 'uniqueN', uniqueNames) #convert the rows to a list df['uniqueNsplit'] = df.uniqueN.str.split() #use zip to iterate over the lists in each row df['intersect'] = [list(set(name).intersection(set(log))) for name,log in zip(df['uniqueNsplit'],df['worklogSplit'])]


And here is the output for for the final dataframe (finalDf)


finalDf = df[['Worklog','Intersection Y/N','intersect']] #show final output print(finalDf)

These are the required information:

1. Worklog contains the original strings.
2. Intersection Y/N performed the excel formula mentioned earlier.
3. intersect showed which texts appeared in the Worklog

In [8]: finalDf Out[8]: Worklog Intersection Y/N intersect 0 rabbit\ndog\napples\nkeyboard True [dog] 1 TV\ncat\ndog\ngrapes True [cat, dog] 2 screen\ndog True [dog]

Finally, the output may be saved to excel by using. I hope this helps the interwebs!



finalDf.to_excel('Samplefile.xlsx')