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

Question

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!

Answer 1

Setup

df = pd.DataFrame({'COL_A': {0: 'Hello ', 1: 'New   '},
 'COL_B': {0: ' World ', 1: ' line  '},
 'COL_C': {0: ' Hi#123;move', 1: ' Can.I#parse;this.data '}})
Out[480]: 
    COL_A    COL_B                    COL_C
0  Hello    World               Hi#123;move
1  New      line     Can.I#parse;this.data 

Solution

#split COL_C by given delimeter and stack them up in a series
COL_C2 = df.COL_C.str.split('\.|;|#',expand=True).stack()
#join the new series (after setting a name and index) back to the dataframe
df.join(pd.Series(index=COL_C2.index.droplevel(1), data=COL_C2.values, name='COL_C2'))

Out[475]: 
    COL_A    COL_B                    COL_C COL_C2
0  Hello    World               Hi#123;move     Hi
0  Hello    World               Hi#123;move    123
0  Hello    World               Hi#123;move   move
1  New      line     Can.I#parse;this.data     Can
1  New      line     Can.I#parse;this.data       I
1  New      line     Can.I#parse;this.data   parse
1  New      line     Can.I#parse;this.data    this
1  New      line     Can.I#parse;this.data   data