Replicating rows in a pandas data frame by a column value

Posted on

Question :

Replicating rows in a pandas data frame by a column value

I want to replicate rows in a Pandas Dataframe. Each row should be repeated n times, where n is a field of each row.

import pandas as pd

what_i_have = 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'],
  'v' : [ 10,  13,  13,   8,   8,   8]
})

Is this possible?

Answer #1:

You could use np.repeat to get the repeated indices and then use that to index into the frame:

>>> df2 = df.loc[np.repeat(df.index.values,df.n)]
>>> df2
  id  n   v
0  A  1  10
1  B  2  13
1  B  2  13
2  C  3   8
2  C  3   8
2  C  3   8

After which there’s only a bit of cleaning up to do:

>>> df2 = df2.drop("n",axis=1).reset_index(drop=True)
>>> df2
  id   v
0  A  10
1  B  13
2  B  13
3  C   8
4  C   8
5  C   8

Note that if you might have duplicate indices to worry about, you could use .iloc instead:

In [86]: df.iloc[np.repeat(np.arange(len(df)), df["n"])].drop("n", axis=1).reset_index(drop=True)
Out[86]: 
  id   v
0  A  10
1  B  13
2  B  13
3  C   8
4  C   8
5  C   8

which uses the positions, and not the index labels.

Answered By: DSM

Answer #2:

You could use set_index and repeat

In [1057]: df.set_index(['id'])['v'].repeat(df['n']).reset_index()
Out[1057]:
  id   v
0  A  10
1  B  13
2  B  13
3  C   8
4  C   8
5  C   8

Details

In [1058]: df
Out[1058]:
  id  n   v
0  A  1  10
1  B  2  13
2  C  3   8
Answered By: Zero

Answer #3:

Not the best solution, but I want to share this: you could also use pandas.reindex() and .repeat():

df.reindex(df.index.repeat(df.n)).drop('n', axis=1)

Output:


   id   v
0   A   10
1   B   13
1   B   13
2   C   8
2   C   8
2   C   8

You can further append .reset_index(drop=True) to reset the .index.

Answered By: Chenglong Ma

Leave a Reply

Your email address will not be published. Required fields are marked *