Skip to content Skip to sidebar Skip to footer

How To Split Pandas String Column Into Different Rows?

Here is my issue. I have data like this: data = { 'name': ['Jack ;; Josh ;; John', 'Apple ;; Fruit ;; Pear'], 'grade': [11, 12], 'color':['black', 'blue'] } df = pd.D

Solution 1:

Use Series.str.split with reshape by DataFrame.stack and add orriginal another columns by DataFrame.join:

c = df.columns
s = (df.pop('name')
       .str.split(' ;; ', expand=True)
       .stack()
       .reset_index(level=1, drop=True)
       .rename('name'))
df = df.join(s).reset_index(drop=True).reindex(columns=c)
print (df)
    name  grade  color
0   Jack     11  black
1   Josh     11  black
2   John     11  black
3  Apple     12   blue
4  Fruit     12   blue
5   Pear     12   blue

Solution 2:

You have 2 challenges:

  1. split the name with ;; into a list AND have each item in the list as a column such that:

    df['name']=df.name.str.split(';;') 
    df_temp = df.name.apply(pd.Series)  
    df = pd.concat([df[:], df_temp[:]], axis=1) 
    df.drop('name', inplace=True, axis=1)
    

result:

    grade   color012011      black   Jack    Josh    John
112      blue    Apple   Fruit   Pear
  1. Melt the list to get desired result:

    df.melt(id_vars=["grade", "color"], 
        value_name="Name").sort_values('grade').drop('variable', axis=1)
    

desired result:

    grade   color   Name
011      black   Jack
211      black   Josh
411      black   John
112      blue    Apple
312      blue    Fruit
512      blue    Pear

Post a Comment for "How To Split Pandas String Column Into Different Rows?"