How can I replicate rows in Pandas?

My pandas dataframe looks like this:

   Person  ID   ZipCode   Gender
0  12345   882  38182     Female
1  32917   271  88172     Male
2  18273   552  90291     Female

I want to replicate every row 3 times like:

   Person  ID   ZipCode   Gender
0  12345   882  38182     Female
0  12345   882  38182     Female
0  12345   882  38182     Female
1  32917   271  88172     Male
1  32917   271  88172     Male
1  32917   271  88172     Male
2  18273   552  90291     Female
2  18273   552  90291     Female
2  18273   552  90291     Female

And of course, reset the index so it is:

0
1
2
...

I tried solutions such as:

pd.concat([df[:5]]*3, ignore_index=True)

And:

df.reindex(np.repeat(df.index.values, df['ID']), method='ffill')

But none of them worked.

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

Use np.repeat:

Version 1:

Try using np.repeat:

newdf = pd.DataFrame(np.repeat(df.values, 3, axis=0))
newdf.columns = df.columns
print(newdf)

The above code will output:

  Person   ID ZipCode  Gender
0  12345  882   38182  Female
1  12345  882   38182  Female
2  12345  882   38182  Female
3  32917  271   88172    Male
4  32917  271   88172    Male
5  32917  271   88172    Male
6  18273  552   90291  Female
7  18273  552   90291  Female
8  18273  552   90291  Female

np.repeat repeats the values of df, 3 times.

Then we add the columns with assigning new_df.columns = df.columns.

Version 2:

You could also assign the column names in the first line, like below:

newdf = pd.DataFrame(np.repeat(df.values, 3, axis=0), columns=df.columns)
print(newdf)

The above code will also output:

  Person   ID ZipCode  Gender
0  12345  882   38182  Female
1  12345  882   38182  Female
2  12345  882   38182  Female
3  32917  271   88172    Male
4  32917  271   88172    Male
5  32917  271   88172    Male
6  18273  552   90291  Female
7  18273  552   90291  Female
8  18273  552   90291  Female

Solution 2

These will repeat the indices and preserve the columns as op demonstrated

iloc version 1

df.iloc[np.arange(len(df)).repeat(3)]

iloc version 2

df.iloc[np.arange(len(df) * 3) // 3]

Solution 3

Using concat:

pd.concat([df]*3).sort_index()
Out[129]: 
   Person   ID  ZipCode  Gender
0   12345  882    38182  Female
0   12345  882    38182  Female
0   12345  882    38182  Female
1   32917  271    88172    Male
1   32917  271    88172    Male
1   32917  271    88172    Male
2   18273  552    90291  Female
2   18273  552    90291  Female
2   18273  552    90291  Female

Solution 4

You can do it like this.

def do_things(df, n_times):
    ndf = df.append(pd.DataFrame({'name' : np.repeat(df.name.values, n_times) }))
    ndf = ndf.sort_values(by='name')
    ndf = ndf.reset_index(drop=True)
    return ndf

if __name__ == '__main__':
    df = pd.DataFrame({'name' : ['Peter', 'Quill', 'Jackson']}) 
    n_times = 3
    print do_things(df, n_times)

And with explanation…

import pandas as pd
import numpy as np

n_times = 3
df = pd.DataFrame({'name' : ['Peter', 'Quill', 'Jackson']})
#       name
# 0    Peter
# 1    Quill
# 2  Jackson

#   Duplicating data.
df = df.append(pd.DataFrame({'name' : np.repeat(df.name.values, n_times) }))
#       name
# 0    Peter
# 1    Quill
# 2  Jackson
# 0    Peter
# 1    Peter
# 2    Peter
# 3    Quill
# 4    Quill
# 5    Quill
# 6  Jackson
# 7  Jackson
# 8  Jackson

#   The DataFrame is sorted by 'name' column.
df = df.sort_values(by=['name'])
#       name
# 2  Jackson
# 6  Jackson
# 7  Jackson
# 8  Jackson
# 0    Peter
# 0    Peter
# 1    Peter
# 2    Peter
# 1    Quill
# 3    Quill
# 4    Quill
# 5    Quill

#   Reseting the index.
#   You can play with drop=True and drop=False, as parameter of `reset_index()`
df = df.reset_index()
#     index     name
# 0       2  Jackson
# 1       6  Jackson
# 2       7  Jackson
# 3       8  Jackson
# 4       0    Peter
# 5       0    Peter
# 6       1    Peter
# 7       2    Peter
# 8       1    Quill
# 9       3    Quill
# 10      4    Quill
# 11      5    Quill

Solution 5

You can try the following code:

df = df.iloc[df.index.repeat(3),:].reset_index()

df.index.repeat(3) will create a list where each index value will be repeated 3 times and df.iloc[df.index.repeat(3),:] will help generate a dataframe with the rows as exactly returned by this list.

Solution 6

I’m not sure why this was never proposed, but you can easily use df.index.repeat in conjection with .loc:

new_df = df.loc[df.index.repeat(3)]

Output:

>>> new_df
   Person   ID  ZipCode  Gender
0   12345  882    38182  Female
0   12345  882    38182  Female
0   12345  882    38182  Female
1   32917  271    88172    Male
1   32917  271    88172    Male
1   32917  271    88172    Male
2   18273  552    90291  Female
2   18273  552    90291  Female
2   18273  552    90291  Female

Solution 7

If you need to index your repeats (e.g. for a multi-index) and also base the number of repeats on a value in a column, you can do this:

someDF["RepeatIndex"] = someDF["RepeatBasis"].fillna(value=0).apply(lambda x: list(range(int(x))) if x > 0 else [])
superDF = someDF.explode("RepeatIndex").dropna(subset="RepeatIndex")

This gives a DataFrame in which each record is repeated however many times is indicated in the "RepeatBasis" column. The DataFrame also gets a "RepeatIndex" column, which you can combine with the existing index to make into a multi-index, preserving index uniqueness.

If anyone’s wondering why you’d want to do such a thing, in my case it’s when I get data in which frequencies have already been summarized and for whatever reason, I need to work with singular observations. (think of reverse-engineering a histogram)

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply