Using character value to create new rows in Pandas

Question

I need to create new rows in a pandas dataframe based on a value that occurs in a specific column.

The pattern to create the Split is that there is a semi colon that indicates where I need to initiate a new row.

df

animal  cat;dog;cat
animal  dog
animal  fish
color   black;green
color   red

desired_df

animal  cat
animal  dog
animal  cat
animal  dog
animal  fish
color   black
color green
color   red

I have seen solutions that use pandas split to create new columns or rows using a given character or values in the df (such as here: and here: ), however, I have not seen a solution that does this with text values. I have also seen solutions (as well as one that I requested myself here) that is able to accurately fill in the null values in pandas. However, I need to combine these two techniques and it is not clear to me if this is feasible to do in a one-liner (or two).

https://stackoverflow.com/questions/43794429/python-how-to-split-cell-in-a-column-to-a-new-row-based-of-a-delmimeter Check the solutions there. — Pygirl, Jun 22 '18 at 10:59

score 1 · Accepted Answer · answered Jun 22 '18 at 10:34

In [200]: df
Out[200]:
     col1         col2
0  animal  cat;dog;cat
1  animal          dog
2  animal         fish
3   color  black;green
4   color          red

In [201]: (df.set_index('col1')
             .col2.str.split(';', expand=True)
             .stack()
             .reset_index(level=1, drop=True)
             .reset_index(name='col2'))
Out[201]:
     col1   col2
0  animal    cat
1  animal    dog
2  animal    cat
3  animal    dog
4  animal   fish
5   color  black
6   color  green
7   color    red

score 0 · Answer 2 · answered Jun 22 '18 at 12:55

Using numpy.repeat and itertools.chain:

import numpy as np
from itertools import chain

split = df['col2'].str.split(';')

res = pd.DataFrame({'col1': np.repeat(df['col1'], split.map(len)),
                    'col2': list(chain.from_iterable(split))})

print(res)

     col1   col2
0  animal    cat
0  animal    dog
0  animal    cat
1  animal    dog
2  animal   fish
3   color  black
3   color  green
4   color    red

Using character value to create new rows in Pandas

2 Answers2