Split (Explode) String into Multiple columns and rows - Python

Question

Good afternoon, i am trying to split text in a column to a specfic format here is my table below

UserId  Application
1       Grey Blue::Black Orange;White:Green
2       Yellow Purple::Orange Grey;Blue Pink::Red

I would like it to read the following:

UserId     Application          Role
    1       Grey Blue           Black Orange
    1       White               Green
    2       Yellow Purple       Orange Grey 
    2       Blue Pink           Red

So far my code is

def unnesting(df, explode):
idx=df.index.repeat(df[explode[0]].str.len())
df1=pd.concat([pd.DataFrame({x:np.concatenate(df[x].values)} )for x in explode],axis=1)
df1.index=idx
return df1.join(df.drop(explode,1),how='left')

df['Application']=df.Roles.str.split(';|::|:').map(lambda x : x[0::2])

unnesting(df.drop('Roles',1),['Application'])

The following output code reads

UserId     Application          
        1       Grey Blue           
        1       White               
        2       Yellow Purple        
        2       Blue Pink

i do not know how to add the second column (role) in the code for the second split after ::

SpghttCd · Accepted Answer · 2019-07-16 22:49:40Z

1

Given this dataframe:

   UserId                                Application
0       1       Grey Blue::Black Orange;White::Green
1       2  Yellow Purple::Orange Grey;Blue Pink::Red

you could at least achieve the last two columns directly via

df.Application.str.split(';', expand=True).stack().str.split('::', expand=True).reset_index().drop(columns=['level_0', 'level_1'])

which results in

               0             1
0      Grey Blue  Black Orange
1          White         Green
2  Yellow Purple   Orange Grey
3      Blue Pink           Red

However, defining UserId as index before would also provide the proper UserId column:

result = df.set_index('UserId').Application.str.split(';', expand=True).stack().str.split('::', expand=True).reset_index().drop(columns=['level_1'])
result.columns = ['UserId', 'Application', 'Role']

   UserId    Application          Role
0       1      Grey Blue  Black Orange
1       1          White         Green
2       2  Yellow Purple   Orange Grey
3       2      Blue Pink           Red

edited Jul 16, 2019 at 22:49

answered Jul 16, 2019 at 20:35

SpghttCd

10.9k2 gold badges23 silver badges28 bronze badges

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

2 Comments

James Scott Over a year ago

thank you for responding! i dont know what but the set index of user idk is not owrking, any thoughts? the error is a keyerror: 'UserId' @spghttcd

SpghttCd Over a year ago

ok, that means your dataframe has an additional index and UserIdis a normal column - I thought UserId was your index. I'll edit...

Collectives™ on Stack Overflow

Split (Explode) String into Multiple columns and rows - Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related