How to repeat/expand pandas data frame?

user_10934417 0
Viewed 134

I would like to expand/repeat # of rows based on the length of sequences. For example:

import pandas as pd 
df = pd.DataFrame({"id":[11,12], "seq":['A,B,C', 'A,B,C,D'], "y":[1, 5]})
print(df)

   id      seq  y
0  11    A,B,C  1
1  12  A,B,C,D  5

I would this df to be

   id      seq     y
0  11      A       1
1  11      A,B     1
2  11      A,B,C   1
3  12      A       5
4  12      A,B     5
5  12      A,B,C   5
6  12      A,B,C,D 5

Any suggestions? Thanks

pandas dataframe numpy python
3 Answers

You could collect the odd-length prefixes of each seq string in a list comprehension and then explode the dataframe on those lists:

df['seq'] = df.seq.apply(lambda s: [s[:i] for i in range(1, len(s)+1, 2)])
df = df.explode('seq')
df.reset_index(drop=True)

    id  seq      y
0   11  A        1
1   11  A,B      1
2   11  A,B,C    1
3   12  A        5
4   12  A,B      5
5   12  A,B,C    5
6   12  A,B,C,D  5
user_130141720

First, splitting and exploding to get new rows:

df['seq1'] = df['seq'].str.split(',')
df = df.explode('seq1')

Then in groups, for each sequence, do a cumulative appending of the values. Then split and join them while stripping the leading and trailing commas that appear due to that split.

df['l'] = df.groupby('id')['seq1'].apply(
    lambda s: s.cumsum().str.split('').str.join(',').str.strip(','))

Thus,

>>> df
   id      seq  y seq1        l
0  11    A,B,C  1    A        A
0  11    A,B,C  1    B      A,B
0  11    A,B,C  1    C    A,B,C
1  12  A,B,C,D  5    A        A
1  12  A,B,C,D  5    B      A,B
1  12  A,B,C,D  5    C    A,B,C
1  12  A,B,C,D  5    D  A,B,C,D
user_27410910

Another option would be to use groupby expanding after explode:

df['seq'] = df['seq'].str.split(',')
df = df.explode('seq')
df['seq'] = [','.join(ewv) for ewv in df.groupby(level=0).expanding()['seq']]
df = df.reset_index(drop=True)

df:

   id      seq  y
0  11        A  1
1  11      A,B  1
2  11    A,B,C  1
3  12        A  5
4  12      A,B  5
5  12    A,B,C  5
6  12  A,B,C,D  5
user_154978880
Related