I have a list =[a,b] and

Dataframe as below

enter image description here

If the text in the 'Text column' matches with List, then need to add additional column matchedItem

So output should be

enter image description here

Please suggest on this..How to get this output after the join (with exact match and partial match)..

also alternatively output in another dataframe(partial match) enter image description here

Code enter image description here

To resolve

I am trying something like this one

df["kpiName"] = getKPIName(df1.text.str) kp1=['a','b']

def getKPIName (str): return [kp1.index(x) for x in kp1 if str in x]

But this results in error TypeError: 'in ' requires string as left operand, not StringMethods

  • $\begingroup$ Which language (python, r, etc.)? Can you add a reproducible data sample (beyond just images which do nothing)? Have you tried anything before, can you share your existing code? $\endgroup$
    – Fnguyen
    Oct 10 '19 at 15:32
  • $\begingroup$ Thank You, I have tried using the join of dataframe and the list as below dfList = pd.concat([df1.assign(kp1=i) for i in kpi], ignore_index=True) print(dfList) #df2 = df1.loc[ $\endgroup$
    – jaiswati_b
    Oct 10 '19 at 15:38
  • $\begingroup$ Can you share the actual code and programming language? Because right now it is impossible for me to say why join did not work. $\endgroup$
    – Fnguyen
    Oct 10 '19 at 15:42
  • $\begingroup$ I am updating it in question $\endgroup$
    – jaiswati_b
    Oct 10 '19 at 16:58

The question is presented in a somewhat confusing fashion. However, I'm assuming OP has a dataframe setup something like this:

import pandas
data = { 'No': [ 1, 2, 3, 4 ], 'Text': [ 'a', 'ab', 'b', 'bd' ] }
df = pandas.DataFrame( data )

In addition to the df file, OP has a kpi list consisting of 'a' and 'b'. There is also a mask variable, which I am assuming was meant to catch all cases where the df.Text is equal to either 'a' or 'b'. This is where the first mistake is. The way OP wrote that line would match cases where the character a is in any part of the element. In other words 'a' in 'ab' is True. I suspect, OP wanted that statement to be False, which meant it needs to be written as 'a' == 'ab' which would be false. I fixed it in the lines below:

kpi = [ 'a', 'b' ]
mask = df['Text'].apply( lambda x: any( item for item in kpi if item.lower() == x ) )

Basically I changed the item.lower() in x to item.lower() == x. Next, OP indicated that there should be a new column called matchedItem, which duplicates the df.Text when there was a match and '----' when there was not a match. Given the mask variable that OP had written, this can be done simply as such:

df[ 'matchedItem' ] = '----'
df.matchedItem[ mask ] = df.Text[ mask ]

In other words, first I set '----' as the default value for all the elements in column matchedItem. Next, I copy over the elements from column Text only for the rows identified in the mask variable. This results in the following output for print( df ):

   No Text matchedItem
0   1    a           a
1   2   ab        ----
2   3    b           b
3   4   bd        ----

There's a number of things that are unclear on the asking of this question (e.g. did OP really want to set mismatches as '----'? Is No supposed to be an index or a separate column? etc.), but hopefully this answer will guide OP towards the desired result.

  • $\begingroup$ Hi Jason, thank you for response $\endgroup$
    – jaiswati_b
    Oct 10 '19 at 22:55
  • $\begingroup$ This works for the exact match df.matchedItem[ mask ] = df.Text[ mask ] but need this output in another dataframe with the partial match as well.. updated the question as above $\endgroup$
    – jaiswati_b
    Oct 10 '19 at 23:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.