pythonpandasapplymelt

how to divide text after symbol into rows in pandas


im trying to divide text from each symbol from one long text.

Column 1
C9 ban Pearl; LEV ban Fracture; C9 pick Lotus; LEV pick Haven; C9 ban Split; LEV ban Ascent; Bind remains

and i need to tranform this long text into this table

Team pb_phase Map
C9 ban Pearl
LEV ban Fracture
C9 pick Lotus
LEV pick Haven
C9 ban Split
LEV ban Ascent

im expecting to divide each symbol to each row and then divide text from each row into 3 different column (team,pb_phase, and map)


Solution

  • Use Series.str.split with DataFrame.explode, agins split and set new columns names by DataFrame.set_axis:

    out = (df['Column 1'].str.split(';\s*')
                        .explode()
                        .str.split(expand=True)
                        .set_axis(['Team','pb_phase','Map'], axis=1)
                        .reset_index(drop=True))
    

    Or add expand=True for first split and use DataFrame.stack:

    out = (df['Column 1'].str.split(';\s*', expand=True)
                         .stack()
                        .str.split(expand=True)
                        .set_axis(['Team','pb_phase','Map'], axis=1)
                        .reset_index(drop=True))
    

    Or use list comprehension for lists and pass to Dataframe constructor:

    out = pd.DataFrame([y.split() for x in df['Column 1'] for y in x.split('; ')],
                       columns=['Team','pb_phase','Map'])
    

    print (out)
       Team pb_phase       Map
    0    C9      ban     Pearl
    1   LEV      ban  Fracture
    2    C9     pick     Lotus
    3   LEV     pick     Haven
    4    C9      ban     Split
    5   LEV      ban    Ascent
    6  Bind  remains      None