Python: How to split each string into new row with some string concatenation

user_15987964 0
Viewed 277

This is my df which consists of 3 columns. I roughly know how to split strings into a new line using stack and unstack. However, I'm wondering how I can retain the "prefix" (which might not always be the same length) when splitting the string.

Edit: Currently I am working with the Pandas version 0.23.0 without the explode function.

Before:

Col1   Col2              Col3
1       QQ12345-01/02/03  x
2       QQ123456-01/02    y
3       QQ12345-01/02/03  z

After:

Col1   Col2              Col3
1      QQ12345-01        x
1      QQ12345-02        x
1      QQ12345-03        x
2      QQ123456-01       y
2      QQ123456-02       y
3      QQ12345-01        z
3      QQ12345-02        z
3      QQ12345-03        z

Currently, I can only manage to split by '/' this is my code below. I Appreciate any help on this.

column_list = df.loc[:,df.columns!='Col2'].columns.tolist()
df.set_index(column_list).stack().str.split('\',expand=True).stack().unstack(-2).reset_index(-1,drop=True).reset_index()
pandas python split
2 Answers

Edit: Currently I am working with the Pandas version 0.23.0 without the explode function.

Okay, let's try some string split/join and let's use melt, it was introduced in pandas version 0.20, so this solution should work for you.

result = (
            df[['Col1', 'Col3']].join(
                df['Col2'].str.split('-')
                    .apply(lambda x: ','.join(f'{x[0]}-{item}' for item in x[1].split('/')))
                    .str.split(',', expand=True))
                .melt(id_vars=['Col1', 'Col3'], value_name='value')
                .dropna()
                .rename(columns={'value': 'Col2'})
                .sort_values(by='Col3')
    )[['Col1','Col2', 'Col3']]

EXPLANATION:

Instead of splitting the string on /, split it on -, then join the first part to the second part (splitted by /), join all these items by , and finally call split on , with expand as True, It will add n columns for n values, then call melt which will bring all these n values in a single column, finally drop any null rows, and sort the values by Col3 just to match it to the expected output you have in the question.

OUTPUT:

   Col1         Col2 Col3
0     1   QQ12345-01    x
3     1   QQ12345-02    x
6     1   QQ12345-03    x
1     2  QQ123456-01    y
4     2  QQ123456-02    y
2     3   QQ12345-01    z
5     3   QQ12345-02    z
8     3   QQ12345-03    z
user_91363480

One possible solution is to transform Col2 and then merge back to df :

outcome = (df.Col2.str.split("-", expand = True)
             .set_axis(['col1', 'col2'], axis = 1)
             .assign(col2 = lambda df: df.col2.str.split("/"))
             .explode('col2')
             .agg("-".join, axis = 1)
            )

Now recombine with the original dataframe, (pandas will take care of the alignment via the index) :

 pd.concat([df.drop(columns='Col2'), outcome.rename('Col2')], axis = 1)


   Col1 Col3         Col2
0     1    x   QQ12345-01
0     1    x   QQ12345-02
0     1    x   QQ12345-03
1     2    y  QQ123456-01
1     2    y  QQ123456-02
2     3    z   QQ12345-01
2     3    z   QQ12345-02
2     3    z   QQ12345-03

Since your version does not support explode, another option is to do all the processing within plain python and recreate the dataframe. Helpful too, since we are processing strings, which are faster within vanilla python than Pandas (Pandas strings are not fixed width, and are based on python's string module):

Dump dataframe into numpy:

from itertools import product, chain

dump = df.to_numpy()
dump
array([[1, 'QQ12345-01/02/03', 'x'],
       [2, 'QQ123456-01/02', 'y'],
       [3, 'QQ12345-01/02/03', 'z']], dtype=object)

Build a series of extractions here:

step1 = [(first, second.split("-")[0],
         second.split("-")[-1].split("/"), 
        last) 
        for first, second, last in dump]

step2 = [(first, product([second], third), last) 
         for first, second, third, last in step1]

step3 = [(first, map("-".join, second), last) 
          for first, second, last in step2]

step4 = [product([first], second, [last]) 
         for first, second, last in step3]

step5 = chain.from_iterable(step4)

pd.DataFrame(step5, columns = df.columns)

   Col1         Col2 Col3
0     1   QQ12345-01    x
1     1   QQ12345-02    x
2     1   QQ12345-03    x
3     2  QQ123456-01    y
4     2  QQ123456-02    y
5     3   QQ12345-01    z
6     3   QQ12345-02    z
7     3   QQ12345-03    z

Note, however, that you lose your dtypes for Col1 and Col3; you can do an astype or convert_dtypes

user_71757130
Related