Python - How to split cell in a column to a new row based of a delmimeter

numpy, pandas, python

Solution

Blend of Speed and Elegance

def pir(df, c):
    colc = df[c].str.split('\.|;|#')
    clst = colc.values.tolist()
    lens = [len(l) for l in clst]

    cdf = pd.DataFrame({c: np.concatenate(clst)}, df.index.repeat(lens))
    return df.drop(c, 1).join(cdf).reset_index(drop=True)

Forget Elegance, Give me Speed!

def pir2(df, c):
    colc = df[c].str.split('\.|;|#')
    clst = colc.values.tolist()
    lens = [len(l) for l in clst]
    j = df.columns.get_loc(c)
    v = df.values
    n, m = v.shape
    r = np.arange(n).repeat(lens)
    return pd.DataFrame(
        np.column_stack([v[r, 0:j], np.concatenate(clst), v[r, j+1:]]),
        columns=df.columns
    )

pir(df, 'COL_C')
# pir2(df, 'COL_C')

   COL_A  COL_B  COL_C
0  Hello  World     Hi
1  Hello  World    123
2  Hello  World   move
3    New   line    Can
4    New   line      I
5    New   line  parse
6    New   line   this
7    New   line   data

Timing

%timeit pir(df, 'COL_C')
1000 loops, best of 3: 1.42 ms per loop

%timeit pir2(df, 'COL_C')
1000 loops, best of 3: 278 µs per loop

%timeit split_list_in_cols_to_rows(df.assign(COL_C=df.COL_C.str.split(r'[.,;#]')), lst_cols='COL_C')
100 loops, best of 3: 4.16 ms per loop

%%timeit 
COL_C2 = df.COL_C.str.split('\.|;|#').apply(pd.Series).stack()
df.drop('COL_C', 1).join(pd.Series(index=COL_C2.index.droplevel(1), data=COL_C2.values, name='COL_C')).reset_index(drop=True)
100 loops, best of 3: 2.81 ms per loop

Setup

from io import StringIO
import pandas as pd

txt = """COL_A | COL_B | COL_C
Hello | World | Hi#123;move
New   | line  | Can.I#parse;this.data """

df = pd.read_csv(StringIO(txt), sep='\s*\|\s*', engine='python')

Problem

Relatively new and trying to split some data with python from a CSV file. I am trying to parse this data and split it into a new row if a specific delimiter appears. Those delimiters are '.' ';' and '#'. There are also no spaces in COL_C. In addition, it wouldn't matter the order of the delimiters, if we find one of them, automatically create the new line. Here is the example data `COL_A | COL_B |COL_C` `--------------------` `Hello | World | Hi.Can;You#Help` the output i'm trying to get would be: `COL_A | COL_B | COL_C` `----------------------` `Hello | World | Hi` `Hello | World | Can` `Hello | World | You` `Hello | World | Help` example 2: `COL_A | COL_B | COL_C` `----------------------` `Hello | World | Hi#123;move` `New | line | Can.I#parse;this.data` the output i'm trying to get would be: `COL_A | COL_B | COL_C` `----------------------` `Hello | World | Hi` `Hello | World | 123` `Hello | World | move` `New | Line | Can` `New | Line | I` `New | Line | parse` `New | Line | this` `New | Line | data` If this data set had another row without Hello World and had world hello in the first two columns, i would like to display that with the corresponding third column's data parsed out into new rows. thanks!

Original source