Pandas create dataframe from one row-CodePudding

Let us say i have some dataframe df and I want to create a new dataframe new_df with n rows each being the same as row idx from df. Is there a faster way compared to:

import pandas as pd

df = pd.DataFrame()
new_df = pd.DataFrame()


for i in range(n):
    new_df.loc[i] = df.iloc[idx]

thanks

CodePudding user response：

You can use repeat:

N = 5
new_df = df.loc[df.index.repeat(N)]
# or for a particular row idx
new_df = df.loc[df.loc[idx].index.repeat(N)]

Or, for a new index reset_index with drop=True:

new_df = df.loc[df.index.repeat(N)].reset_index(drop=True)
# or for a particular row idx
new_df = df.loc[df.loc[idx].index.repeat(N)].reset_index(drop=True)

NB. if you have many rows in the input and only want to repeat one or some. replace df.index.repeat(N) with df.loc[idx].index.repeat(N) of df.loc[['idx1', 'idx2', 'idx3']].index.repeat(N)

Example input:

df = pd.DataFrame([['A', 'B', 'C']])

Output:

   0  1  2
0  A  B  C
1  A  B  C
2  A  B  C
3  A  B  C
4  A  B  C

CodePudding user response：

Sample:

np.random.seed(100)
df = pd.DataFrame(np.random.randint(10, size=(5,5)), columns=list('ABCDE'))
print (df)
   A  B  C  D  E
0  8  8  3  7  7
1  0  4  2  5  2
2  2  2  1  0  8
3  4  0  9  6  2
4  4  1  5  3  4

You can create dictionary/list by row idx and call DataFrame constructor:

idx = 2
N = 10
df1 = pd.DataFrame(df.loc[idx].to_dict(), index=range(N))
df1 = pd.DataFrame([df.loc[idx].tolist()], index=range(N), columns=df.columns)
print (df1)
   A  B  C  D  E
0  2  2  1  0  8
1  2  2  1  0  8
2  2  2  1  0  8
3  2  2  1  0  8
4  2  2  1  0  8
5  2  2  1  0  8
6  2  2  1  0  8
7  2  2  1  0  8
8  2  2  1  0  8
9  2  2  1  0  8

Another solution with numpy.repeat and DataFrame.loc, for default index use DataFrame.reset_index with drop=True:

idx = 2
N = 10
df1 = df.loc[np.repeat(idx, N)].reset_index(drop=True)
print (df1)
   A  B  C  D  E
0  2  2  1  0  8
1  2  2  1  0  8
2  2  2  1  0  8
3  2  2  1  0  8
4  2  2  1  0  8
5  2  2  1  0  8
6  2  2  1  0  8
7  2  2  1  0  8
8  2  2  1  0  8
9  2  2  1  0  8

Performance comparison (with my data, bset test in your real data):

np.random.seed(100)
df = pd.DataFrame(np.random.randint(10, size=(5,5)), columns=list('ABCDE'))
print (df)

idx = 2
N = 10000

In [260]: %timeit pd.DataFrame([df.loc[idx].tolist()], index=range(N), columns=df.columns)
690 µs ± 44.6 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

In [261]: %timeit pd.DataFrame(df.loc[idx].to_dict(), index=range(N))
786 µs ± 106 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

In [262]: %timeit df.loc[np.repeat(idx, N)].reset_index(drop=True)
796 µs ± 26.3 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

@mozway solution
In [263]: %timeit df.loc[df.index.repeat(N)].reset_index(drop=True)
3.62 ms ± 178 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

@original solution
In [264]: %%timeit
     ...: nnew_df = pd.DataFrame(columns=df.columns)
     ...: for i in range(N):
     ...:     new_df.loc[i] = df.iloc[idx]
     ...:     
2.44 s ± 274 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)