Concatinating the dataframe in pandas so that the indexes are placed one after the other

Question

I have two dataframes created using pandas, I want to concatenate them so that the indexes of the two come one after the other. For example,

df1:

    x    y
a   10   30
b   20   40

df2:

    x    y
a1  11   31
b1  21   41

required output:

    x    y
a   10   30
a1  11   31
b   20   40
b1  21   41

Should the indexes always alternate? What if there is a different amount in each df? — matszwecja, Jan 10 '23 at 10:37
Does this answer your question? [Pandas - Interleave / Zip two DataFrames by row](https://stackoverflow.com/questions/45565311/pandas-interleave-zip-two-dataframes-by-row) — Abdul Niyas P M, Jan 10 '23 at 10:40
I will try with the zip_logest() method once. See if that works. — programmer_04_03, Jan 10 '23 at 10:46

score 2 · Answer 1 · answered Jan 10 '23 at 10:38

2

You need to concatenate the two dataframes and then sort the indeces:

pd.concat([df1, df2]).sort_index()

Output:

answered Jan 10 '23 at 10:38

ali bakhtiari

1,051
4
23

ok. sort_index() after concat() works. The output is just how I wanted. Thank you. – programmer_04_03 Jan 10 '23 at 10:48
Glad it helped. You can also accept answers if you find them helpful. Good luck! – ali bakhtiari Jan 10 '23 at 10:49
Note that this only works if you have specific indices in both input dataframes, for arbitrary keys this won't. – mozway Jan 10 '23 at 10:53

mozway · Answer 2 · 2023-01-10T12:53:12.333

1

You can assign a counter ("order"), concat, and use a stable sorting to reorder in an alternating way with the sort='stable' parameter of sort_values:

out = (pd
   .concat([df1.assign(order=range(len(df1))),
            df2.assign(order=range(len(df2)))])
   .sort_values(by='order', kind='stable')
   .drop(columns='order')
)

NB. this works independently of the length of the two dataframes and the original order of the indices.

Output:

Intermediate after concat:

     x   y  order
a   10  30      0
b   20  40      1
a1  11  31      0
b1  21  41      1

edited Jan 10 '23 at 12:53

answered Jan 10 '23 at 10:39

mozway

194,879
13
39
75

That's really intuitive. +1. Shouldn't `df2.assign(order=range(len(df1)))` be `df2.assign(order=range(len(df2)))`? – ali bakhtiari Jan 10 '23 at 11:02

Concatinating the dataframe in pandas so that the indexes are placed one after the other

2 Answers2