1

I have a dataframe with multiple slimier columns to marge

ID   col0    col1   col2   col3   col4   col5
1    jack    in A   A jf    w/n    y/h    56
2    sam     z/n    b/w      A     A      93
3    john    e/e    jg      b/d    A      33
4    Adam    jj     b/b     b/d    NaN    15

What I want now is to merge the column with A to be like this

ID   col0    col1   col2   col3   col4      A         col5
1    jack    in A   A jf    w/n    y/h  in A - A jf    56
2    sam     z/n    b/w     A n     A     A n - A      93
3    john    e/e    jg      b/d    A        A          33
4    Adam    jj     b/b     b/d    NaN     NaN         15

I tried the first solution in here Is there a python way to merge multiple cells with condition yet the result ended up missing info:

ID   col0    col1   col2   col3   col4      A         col5
1    jack    in A   A jf    w/n    y/h  in A - A jf    56
2    sam     z/n    b/w     A n     A      NaN         93
3    john    e/e    jg      b/d     A       A          33
4    Adam    jj     b/b     b/d    NaN     NaN         15

Can any one figure what is not not working with this line

s = df.filter(regex=r'col[1-4]').stack()
s = s[s.str.contains('A')].groupby(level=0).agg(' - '.join)
df['A'] = s
SMO
  • 149
  • 8

1 Answers1

0

Let's try this,

(
    df.filter(regex=r'col[1-4]').fillna("").
        apply(lambda x: " - ".join([v for v in x if "A" in v]), axis=1)
)
sushanth
  • 8,275
  • 3
  • 17
  • 28
  • still the same problem my original dataframe has 35 column to be searched for the key word. what is happening is that is locate some columns with the key word and miss others – SMO Jul 20 '20 at 06:59