Concise way to concatenate consecutive rows in pandas

user_1900169 0
Viewed 77

I would like to take a dataframe and concatenate consecutive rows for comparison.

e.g. Take

xyt = pd.DataFrame(np.concatenate((np.random.randn(3,2), np.arange(3).reshape((3, 1))), axis=1), columns=['x','y','t'])

Which looks something like:

    x           y           t
0   1.237007    -1.035837   0.0
1   -1.782458   1.042942    1.0
2   0.063130    0.355014    2.0

And make:

    a                               b
    x           y           t       x           y           t
0   1.237007    -1.035837   0.0     -1.782458   1.042942    1.0
1   -1.782458   1.042942    1.0     0.063130    0.355014    2.0

The best I could come up with was:

pd.DataFrame(
    [np.append(x,y) for (x, y) in zip(xyt.values, xyt[1:].values)],
    columns=pd.MultiIndex.from_product([('a', 'b'), xyt.columns]))

Is there a better way?

pandas
2 Answers

Let's try concat on axis=1 with the shifted frame:

import pandas as pd

xyt = pd.DataFrame({'x': {0: 1.237007, 1: -1.782458, 2: 0.06313},
                    'y': {0: -1.035837, 1: 1.042942, 2: 0.355014},
                    't': {0: 0.0, 1: 1.0, 2: 2.0}})

merged = pd.concat((xyt, xyt.shift(-1)), axis=1, keys=('a', 'b')).iloc[:-1]
print(merged)

merged:

          a                        b               
          x         y    t         x         y    t
0  1.237007 -1.035837  0.0 -1.782458  1.042942  1.0
1 -1.782458  1.042942  1.0  0.063130  0.355014  2.0
user_154978880

You can use pd.concat:

# Generate random data
n = 10
x, y = np.random.randn(2, n)
t = np.arange(n)

xyt = pd.DataFrame({
    'x': x, 'y': y, 't': t
})

# The call
pd.concat([xyt, xyt.shift(-1)], axis=1, keys=['a','b'])

# Result
          a                      b               
          x         y  t         x         y    t
0  1.180544  1.707380  0 -0.227370  0.734225  1.0
1 -0.227370  0.734225  1  0.271997 -1.039424  2.0
2  0.271997 -1.039424  2 -0.729960 -1.081224  3.0
3 -0.729960 -1.081224  3  0.185301  0.530126  4.0
4  0.185301  0.530126  4 -0.175333 -0.126157  5.0
5 -0.175333 -0.126157  5 -0.634870  0.068683  6.0
6 -0.634870  0.068683  6  0.350867  0.361564  7.0
7  0.350867  0.361564  7  0.090678 -0.269504  8.0
8  0.090678 -0.269504  8  0.177076 -0.976640  9.0
9  0.177076 -0.976640  9       NaN       NaN  NaN
user_25389390
Related