Exact matching string with "==" operator between Str and a list of strings-CodePudding

I have this example df:

df6 = pd.DataFrame({
                   'answer1': ['Lo', 'New York', 'Toronto'],
                   'answer2': ['London', 'New', 'Paris'],
                   'answer3': ['CA', 'CA', 'CA'],
                   'correct': [['London'], ['New York'], ['Toronto']]
                   })

df6

gives:

    answer1   answer2     answer3     correct
0   Lo         London         CA    [London]
1   New York    New           CA    [New York]
2   Toronto    Paris          CA    [Toronto]

I am trying to get the column name (answer 1 or2 .. etc) that contains the text in the correct column in a new column called Answer by matching values in str format. The correct column has the data in a list type

I used the following code to do so:

cols = df6.filter(like='answer').columns

df6['Answer'] = df6[cols].apply(lambda s: ', '.join(cols[(m:=[str(s[col]) in str(df6.loc[s.name, 'correct']) for col in cols])]) , axis=1)

But I go inaccurate results:

    answer1    answer2     answer3     correct       Answer
0   Lo         London       CA         [London]      answer1, answer2
1   New York   New          CA         [New York]    answer1, answer2
2   Toronto    Paris        CA         [Toronto]     answer1

It should be:

    answer1    answer2     answer3     correct       Answer
0   Lo         London       CA         [London]      answer2
1   New York   New          CA         [New York]    answer1
2   Toronto    Paris        CA         [Toronto]     answer1

If I changed in to == the code will not work because the type of data is not comparable (str with list) and also I need to wrap list items in a str to avoid multiple data issues in my original df

I do not know how to achieve this?

CodePudding user response：

Strip correct of corner brackets , check existence in df and then conditionally copy over the columns

 df6['answer'] =df6.isin(df6['correct'].str[0].to_list()).agg(lambda s: s.index[s].values, axis=1)
df6



     answer1 answer2 answer3     correct     answer
0        Lo  London      CA    [London]  [answer2]
1  New York     New      CA  [New York]  [answer1]
2   Toronto   Paris      CA   [Toronto]  [answer1]

CodePudding user response：

I think you should look if the element in the answer column is in the list, not in the string, in the correct column:

df6['Answer'] = df6[cols].apply(lambda s: ', '.join(cols[(m:=[str(s[col]) in list(df6.loc[s.name, 'correct']) for col in cols])]) , axis=1)

Should work, as this is checking if the answerX element is in the correct list.