Replicating rows in pandas dataframe by column value and add a new column with repetition index

Question

My question is similar to one asked here. I have a dataframe and I want to repeat each row of the dataframe k number of times. Along with it, I also want to create a column with values 0 to k-1. So

import pandas as pd

df = pd.DataFrame(data={
  'id': ['A', 'B', 'C'],
  'n' : [  1,   2,   3],
  'v' : [ 10,  13,   8]
})

what_i_want = pd.DataFrame(data={
  'id': ['A', 'B', 'B', 'C', 'C', 'C'],
  'n' : [ 1, 2, 2, 3, 3, 3],
  'v' : [ 10,  13, 13, 8, 8, 8],
  'repeat_id': [0, 0, 1, 0, 1, 2]
})

Command below does half of the job. I am looking for pandas way of adding the repeat_id column.

df.loc[df.index.repeat(df.n)]

Your 'half of the job' was exactly what I was looking for!

user1718097
– user1718097

2018-11-16 10:58:05 +00:00
Commented Nov 16, 2018 at 10:58 — user1718097
– user1718097, Commented Nov 16, 2018 at 10:58

jezrael · Accepted Answer · 2018-05-11 11:22:14Z

4

Use GroupBy.cumcount and copy for avoid SettingWithCopyWarning:

If you modify values in df1 later you will find that the modifications do not propagate back to the original data (df), and that Pandas does warning.

df1 = df.loc[df.index.repeat(df.n)].copy()
df1['repeat_id'] = df1.groupby(level=0).cumcount()
df1 = df1.reset_index(drop=True)
print (df1)
  id  n   v  repeat_id
0  A  1  10          0
1  B  2  13          0
2  B  2  13          1
3  C  3   8          0
4  C  3   8          1
5  C  3   8          2

edited May 11, 2018 at 11:22

answered May 11, 2018 at 11:19

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

5 Comments

kampta Over a year ago

Thanks for a quick reply! Apparently I can accept an answer only after 10 minutes :D

jezrael Over a year ago

@kampta - Really nice question (inut, data, output data, what you try), unfortunately not very often in SO in these days...

FHTMitchell Over a year ago

What happens if you don't copy()? I'm struggling to see what the issue with doing that is.

jezrael Over a year ago

@FHTMitchell - I get

SettingWithCopyWarning:  A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead  See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

FHTMitchell Over a year ago

@jezrael Yeah I got that warning too. I continued anyway and nothing happened to df. Odd.

Collectives™ on Stack Overflow

Replicating rows in pandas dataframe by column value and add a new column with repetition index

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related