1

I am trying merge specific strings in a pandas df. The df below is just an example. The values in my df will differ but the basic rules will apply. I basically want to merge all rows until there's a 4 letter string.

Whilst the 4 letter string in this df is always Excl, my df will contain numerous 4 letter strings.

import pandas as pd

d = ({
    'A' : ['Include','Inclu','Incl','Inc'],
    'B' : ['Excl','de','ude','l'],           
    'C' : ['X','Excl','Excl','ude'],
    'D' : ['','Y','ABC','Excl'],
    })

df = pd.DataFrame(data=d)

Out:

         A     B     C     D
0  Include  Excl     X      
1    Inclu    de  Excl     Y
2     Incl   ude  Excl   ABC
3      Inc     l   ude  Excl

Intended Output:

         A     B     C     D
0  Include  Excl     X      
1  Include        Excl     Y 
2  Include        Excl   ABC
3  Include              Excl

So row 0 stays the same as col B has 4 letters. Row 1 merges Col A,B as Col C 4 letters. Row 2 stays the same as above. Row 3 merges Col A,B,C as Col D has 4 letters.

I have tried to do this manually by merging all columns and then go back and removing unwanted values.

df["Com"] = df["A"].map(str) + df["B"]  + df["C"]

But I would have to manually go through each row and remove different lengths of letters.

The above df is just an example. The central similarity is I need to merge everything before the 4 letter string.

Improve this question
9
  • what is your expected output? Commented Jul 25, 2018 at 4:41
  • It's in there @aydow. Under Intended Output Commented Jul 25, 2018 at 4:44
  • not able to understand your output,like what happend to the data in Column D Commented Jul 25, 2018 at 4:49
  • Updated @experiment Commented Jul 25, 2018 at 5:33
  • Row 2, column A also has four letters; what decides that it is the four letter string in column C that decides what is merged? Commented Jul 25, 2018 at 6:36

3 Answers 3

Reset to default
1

You could do something like

mask = (df.iloc[:, 1:].applymap(len) == 4).cumsum(1) == 0
df.A = df.A + df.iloc[:, 1:][mask].apply(lambda x: x.str.cat(), 1)
df.iloc[:, 1:] = df.iloc[:, 1:][~mask].fillna('')
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks @fuglede. I needed something that used any 4 letter strings. Not specifically Excl
1

try this,

Sorry for the clumsy solution, I'll try to improve the performance ,

temp=df.eq('Excl').shift(-1,axis=1)
df['end']= temp.apply(lambda x:x.argmax(),axis=1)
res=df.apply(lambda x:x.loc[:x['end']].sum(),axis=1)
mask=temp.replace(False,np.NaN).fillna(method='ffill').fillna(False).astype(bool)
del df['end']
df[:]=np.where(mask,'',df)
df['A']=res
print df

Output:

         A     B     C     D
0  Include  Excl     X      
1  Include        Excl     Y
2  Include        Excl   ABC
3  Include              Excl

Improved solution:

res= df.apply(lambda x:x.loc[:x.eq('Excl').shift(-1).argmax()].sum(),axis=1)
mask=df.eq('Excl').shift(-1,axis=1).replace(False,np.NaN).fillna(method='ffill').fillna(False).astype(bool)
df[:]=np.where(mask,'',df)
df['A']=res

More simplified solution:

t=df.eq('Excl').shift(-1,axis=1)
res= df.apply(lambda x:x.loc[:x.eq('Excl').shift(-1).argmax()].sum(),axis=1)
df[:]=np.where(t.fillna(0).astype(int).cumsum() >= 1,'',df)
df['A']=res
Improve this answer

3 Comments

Thanks @Mohamed Thasin ah but i'm getting an error. TypeError: ("reduction operation 'argmax' not allowed for this dtype", 'occurred at index 0')
sorry to hear that, but it works without a problem for me. It works on your sample data.
@Maxibon - check with the more simplified version
0

I am giving you a rough approach, Here, we are finding the location of the 'Excl' and merging the column values up it so as to obtain our desired output.

ls=[]
for i in range(len(df)):
    end=(df.loc[i,:].index[(df.loc[i,:]=='Excl')][0])
    ls.append(''.join(df.loc[i,:end].replace({'Excl':''}).values))
df['A']=ls
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.