split and generate columns automatically

user_14114654 0
Viewed 74

df

fruit                want_split
apple    colour:red, like:yes, favourite:no
banana   colour:yellow, like:yes, favourite:no
pear     colour:green, like:yes, favourite:yes

Is there a way to split by the comma and then automatically generate column names, rather than manually? Expected Output:

fruit                want_split                  colour      like    favourite
apple    colour:red, like:yes, favourite:no     red        yes       no
banana   colour:yellow, like:yes, favourite:no  yellow     yes       no
pear     colour:green, like:yes, favourite:yes  green      yes       yes
pandas python-3.x python split
3 Answers

You can use pivot / unstack and join after str.split and explode:

u = df['want_split'].str.split(",").explode().str.split(":",expand=True)
out = df.join(u.set_index(0,append=True)[1].unstack())

print(out)

     type                             want_split  favourite  like  colour
0   apple     colour:red, like:yes, favourite:no         no   yes     red
1  banana  colour:yellow, like:yes, favourite:no         no   yes  yellow
2    pear  colour:green, like:yes, favourite:yes        yes   yes   green
user_98406370

You can use .str.extractall to extract capture group in the regex pattern as columns in dataframe.

c = ['colour','like','favourite']
df[c] = df['want_split'].str.extractall(r':([^,]+)')[0].unstack()

Alternatively you can try .str.findall:

df[c] = df['want_split'].str.findall(r':([^,]+)').tolist()

    fruit                             want_split  colour like favourite
0   apple     colour:red, like:yes, favourite:no     red  yes        no
1  banana  colour:yellow, like:yes, favourite:no  yellow  yes        no
2    pear  colour:green, like:yes, favourite:yes   green  yes       yes
user_128331660

Let us try split with dict , then create the additional column

out = df.join(pd.DataFrame(df.want_split.map(lambda s : dict(item.split(":") for item in s.split(", "))).tolist(),index=df.index))
    fruit                           want_split  colour like favourite
0   apple     colour:red,like:yes,favourite:no     red  yes        no
1  banana  colour:yellow,like:yes,favourite:no  yellow  yes        no
2    pear  colour:green,like:yes,favourite:yes   green  yes       yes
user_79645270
Related