Search code examples
pythonpandasdataframeconcatenationalternate

Concatenate dataframes alternating rows with Pandas

I have two dataframes df1 and df2 that are defined like so:

df1          df2
Out[69]:     Out[70]:
   A  B         A  B
0  2  a      0  5  q
1  1  s      1  6  w
2  3  d      2  3  e
3  4  f      3  1  r

My goal is to concatenate the dataframes by alternating the rows so that the resulting dataframe is like this:

dff
Out[71]: 
   A  B
0  2  a <--- belongs to df1
0  5  q <--- belongs to df2
1  1  s <--- belongs to df1
1  6  w <--- belongs to df2
2  3  d <--- belongs to df1
2  3  e <--- belongs to df2
3  4  f <--- belongs to df1
3  1  r <--- belongs to df2

As you can see the first row of dff corresponds to the first row of df1 and the second row of dff is the first row of df2. The pattern repeats until the end.

I tried to reach my goal by using the following lines of code:

import pandas as pd

df1 = pd.DataFrame({'A':[2,1,3,4], 'B':['a','s','d','f']})
df2 = pd.DataFrame({'A':[5,6,3,1], 'B':['q','w','e','r']})

dfff = pd.DataFrame()
for i in range(0,4):
    dfx = pd.concat([df1.iloc[i].T, df2.iloc[i].T])
    dfff = pd.concat([dfff, dfx])

However this approach doesn't work because df1.iloc[i] and df2.iloc[i] are automatically reshaped into columns instead of rows and I cannot revert the process (even by using .T).

Question: Can you please suggest me a nice and elegant way to reach my goal?

Optional: Can you also provide an explanation about how to convert a column back to row?

Solution

  • I'm unable to comment on the accepted answer, but note that the sort operation in unstable by default, so you must choose a stable sorting algorithm.

    pd.concat([df1, df2]).sort_index(kind='merge')