Explode dataset based on a specific column in Python

Question

I wish to explode my dataset based on a specific column in Python.

Data

id  type    date    stat    energy
aa  ss      Q1 2022 3       10
aa  ss      Q2 2022 2       10
bb  uu      Q1 2022 1       15
bb  uu      Q2 2022 3       15
cc  ii      Q1 2022 0       0

Desired

id  type    date    stat    energy
aa  ss     Q1 2022  3       10
aa  ss     Q1 2022  3       10
aa  ss     Q1 2022  3       10
aa  ss     Q2 2022  2       10
aa  ss     Q2 2022  2       10
bb  uu     Q1 2022  1       15
bb  uu     Q2 2022  3       15
bb  uu     Q2 2022  3       15
bb  uu     Q2 2022  3       15
cc  ii     Q1 2022  0       0

Doing

df.explode(list['stat'])

Any suggestion is appreciated

Corralien · Accepted Answer · 2022-01-20 19:40:08Z

1

Use df.index.repeat:

repeats = np.where(df['stat'] == 0, 1, df['stat'])
# OR
repeats = df['stat'].clip(lower=1)

out = df.reindex(df.index.repeat(repeats)).reset_index(drop=True)
print(out)

# Output
   id type     date  stat  energy
0  aa   ss  Q1 2022     3      10
1  aa   ss  Q1 2022     3      10
2  aa   ss  Q1 2022     3      10
3  aa   ss  Q2 2022     2      10
4  aa   ss  Q2 2022     2      10
5  bb   uu  Q1 2022     1      15
6  bb   uu  Q2 2022     3      15
7  bb   uu  Q2 2022     3      15
8  bb   uu  Q2 2022     3      15
9  cc   ii  Q1 2022     0       0

edited Jan 20, 2022 at 19:40

answered Jan 20, 2022 at 19:35

Corralien

121k8 gold badges44 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lynn Over a year ago

sure - thank you the original soln works- was wondering what the edit changes? @corralien

Corralien Over a year ago

Because if you repeat a row zero time, the row disappears so I need to keep at least one instance (your row 'cc')

mosc9575 · Accepted Answer · 2022-01-20 19:52:59Z

1

Another solution could be

df['stat'] = [[x]*x if x > 0 else [x] for x in df['stat']]
new = df.explode('stat')

edited Jan 20, 2022 at 19:52

answered Jan 20, 2022 at 19:47

mosc9575

6,3922 gold badges12 silver badges36 bronze badges

Comments

wwnde · Accepted Answer · 2022-01-20 21:37:41Z

1

Faster and neater way to do it is to use np.repeat

m=df['stat'].ge(1)#Isolate rows to be duplicated
df1 = (pd.DataFrame(np.repeat(df[m].values,df.loc[m,'stat'], axis=0)#convert to numpy array and duplicate conditionally
                    , columns=df.columns)#Convert back to df
       .append(df[~m])#Reappend rows that had had zero dup required
      )
print(df1)

answered Jan 20, 2022 at 21:37

wwnde

26.7k6 gold badges22 silver badges38 bronze badges

Collectives™ on Stack Overflow

Explode dataset based on a specific column in Python

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related