Duplicating each row in a dataframe with counts

user_8318127 0
Viewed 159

For each row in a dataframe, I wish to create duplicates of it with an additional column to identify each duplicate.

E.g Original dataframe is

A | A 

B | B

I wish to make make duplicate of each row with an additional column to identify it. Resulting in:

A | A | 1

A | A | 2

B | B | 1

B | B | 2
pandas dataframe duplicates python
4 Answers

You can use df.reindex followed by a groupby on df.index.

df = df.reindex(df.index.repeat(2))
df['count'] = df.groupby(level=0).cumcount() + 1
df = df.reset_index(drop=True)

df
   a  b  count
0  A  A      1
1  A  A      2
2  B  B      1
3  B  B      2

Similarly, using reindex and assign with np.tile:

df = df.reindex(df.index.repeat(2))\
       .assign(count=np.tile(df.index, 2) + 1)\
       .reset_index(drop=True)

df

   a  b  count
0  A  A      1
1  A  A      2
2  B  B      1
3  B  B      2
user_49090870

Use Index.repeat with loc, for count groupby with cumcount:

df = pd.DataFrame({'a': ['A', 'B'], 'b': ['A', 'B']})
print (df)
   a  b
0  A  A
1  B  B

df = df.loc[df.index.repeat(2)]
df['new'] = df.groupby(level=0).cumcount() + 1
df = df.reset_index(drop=True)
print (df)
   a  b  new
0  A  A    1
1  A  A    2
2  B  B    1
3  B  B    2

Or:

df = df.loc[df.index.repeat(2)]
df['new'] = np.tile(range(int(len(df.index)/2)), 2) + 1
df = df.reset_index(drop=True)
print (df)
   a  b  new
0  A  A    1
1  A  A    2
2  B  B    1
3  B  B    2
user_29010020

Setup
Borrowed from @jezrael 

df = pd.DataFrame({'a': ['A', 'B'], 'b': ['A', 'B']})

   a  b
0  A  A
1  B  B

Solution 1
Create a pd.MultiIndex with pd.MultiIndex.from_product
Then use pd.DataFrame.reindex 

idx = pd.MultiIndex.from_product(
    [df.index, [1, 2]],
    names=[df.index.name, 'New']
)

df.reindex(idx, level=0).reset_index('New')

   New  a  b
0    1  A  A
0    2  A  A
1    1  B  B
1    2  B  B

Solution 2
This uses the same loc and reindex concept used by @cᴏʟᴅsᴘᴇᴇᴅ and @jezrael, but simplifies the final answer by using list and int multiplication rather than np.tile. 

df.loc[df.index.repeat(2)].assign(New=[1, 2] * len(df))

   a  b  New
0  A  A    1
0  A  A    2
1  B  B    1
1  B  B    2
user_23366540

Use pd.concat() to repeat, and then groupby with cumcount() to count:

In [24]: df = pd.DataFrame({'col1': ['A', 'B'], 'col2': ['A', 'B']})

In [25]: df
Out[25]: 
  col1 col2
0    A    A
1    B    B

In [26]: df_repeat = pd.concat([df]*3).sort_index()

In [27]: df_repeat
Out[27]: 
  col1 col2
0    A    A
0    A    A
0    A    A
1    B    B
1    B    B
1    B    B

In [28]: df_repeat["count"] = df_repeat.groupby(level=0).cumcount() + 1

In [29]: df_repeat  # df_repeat.reset_index(drop=True); if index reset required.
Out[29]: 
  col1 col2  count
0    A    A      1
0    A    A      2
0    A    A      3
1    B    B      1
1    B    B      2
1    B    B      3
user_54249180
Related