Compare multiple string columns return different column

Question

I have a dataframe with four string columns

col1 col2 col3 col4
A.    A.   A.    B. 
A.    A.   A.    A.
A.    A.   B.    B.

Would like the response like this.

col1 col2 col3 col4 Changed
A.    A.   A.    B.    B
A.    A.   A.    A.    
A.    A.   B.    B.    B

lst = col1,col2,col3,col4
def compare(**kwargs):
  if all(col1 for i in my_list):
     return col1
  elif all(col2 for i in my_list):
     return col2
  elif all(col3 for i in (col3,col4):
     return col3
  elif col4 == col4 :
     return col2
  

df['changed'] = df.apply(lambda x: compare(x), axis = 1)

Bit stuck in getting this right if anyone can help?

For the 2 cases, what is the expected output for Changed column? — Corralien
– Corralien, Commented Sep 16, 2021 at 20:04

Corralien · Accepted Answer · 2021-09-16 20:29:13Z

1

Take the last column if not all values are identical:

df['Changed'] = df['col4'].mask(df.eq(df['col1'], axis=0).all(1), '')

Output:

  col1 col2 col3 col4 Changed
0   A.   A.   A.   B.      B.
1   A.   A.   A.   A.
2   A.   A.   B.   B.      B.

edited Sep 16, 2021 at 20:29

Corralien

121k8 gold badges44 silver badges69 bronze badges

answered Sep 16, 2021 at 20:12

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

mozway Over a year ago

Thanks @Ben.T, I just realized it ;)

David Draper Over a year ago

This issue with this it doesn't show NAN when they are the same

mozway Over a year ago

@David yes it does, remove the '' in the where.

David Draper Over a year ago

Hmm thanks but for some reason thats not working

mozway Over a year ago

Can you update your question with the exact expected output? How is it not working? Do you use a different dataset?

|

Corralien · Accepted Answer · 2021-09-16 20:25:20Z

0

Use mask to select row where there are some changes. I slightly modify your dataframe to take into account special cases.

mask = df.ne(df.shift(fill_value=df['col1'], axis=1))
df['Changed'] = df.mask(mask.sum(1) <= 0)[mask].ffill(axis=1)['col4']

>>> df
  col1 col2 col3 col4 Changed
0   A.   A.   A.   B.      B.
1   A.   A.   A.   A.     NaN
2   A.   A.   B.   B.      B.
3   A.   A.   B.   C.      C.
4   A.   A.   B.   A.      A.

answered Sep 16, 2021 at 20:25

Corralien

121k8 gold badges44 silver badges69 bronze badges

1 Comment

Corralien Over a year ago

@DavidDraper. Can you give it a try to my answer, please? Does it work?

Collectives™ on Stack Overflow

Compare multiple string columns return different column

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related