Merge Row With Next Row In Dataframe Pandas

I have a dataframe in pandas which contains multiple columns. I want to merge every row with the next row. Example: input dataframe: A B C a1 a2 a3 b1 b2 b3 c1 c1 c3 d1

Solution 1:

Use shift with join, concat or assign, for new columns names add_suffix is useful:

df1 = df.add_suffix('1').join(df.shift(-1).add_suffix('2'))

---

df1 = pd.concat([df.add_suffix('1'), df.shift(-1).add_suffix('2')], axis=1)

---

df1 = df.add_suffix('1').assign(**df.shift(-1).add_suffix('2'))


print (df1)
   A1  B1  C1   A2   B2   C2
0  a1  a2  a3   b1   b2   b3
1  b1  b2  b3   c1   c1   c3
2  c1  c1  c3   d1   d2   d3
3  d1  d2  d3  NaN  NaN  NaN

Solution 2:

You could use

In [204]: pd.concat([df.add_suffix(1), df[1:].reset_index(drop=True).add_suffix(2)],
                    axis=1)
Out[204]:
   A1  B1  C1   A2   B2   C2
0  a1  a2  a3   b1   b2   b3
1  b1  b2  b3   c1   c1   c3
2  c1  c1  c3   d1   d2   d3
3  d1  d2  d3  NaN  NaN  NaN

And, extend it to generic use

In [206]: N =3# Say 3 more times

In [207]: pd.concat([df.add_suffix(1)]+[df[x+1:].reset_index(drop=True).add_suffix(x+2)for x inrange(N)], axis=1)
Out[207]:
   A1  B1  C1   A2   B2   C2   A3   B3   C3   A4   B4   C4
0  a1  a2  a3   b1   b2   b3   c1   c1   c3   d1   d2   d3
1  b1  b2  b3   c1   c1   c3   d1   d2   d3  NaNNaNNaN2  c1  c1  c3   d1   d2   d3  NaNNaNNaNNaNNaNNaN3  d1  d2  d3  NaNNaNNaNNaNNaNNaNNaNNaNNaN