How to convert several rows into 1 row in a pandas data frame in a rolling way and fast?

user_6140832 0
Viewed 351

I am looking for a faster and more elegant way to solve the following problem:

Given a pandas data frame, I want to combine the current row and the previous k (prev_len) rows into a new row (of a new data frame). I want to do this for every valid old row, i.e. every row that has k previous rows. That is, every new row will consist of prev_len + 1 old rows that were horizontally appended next to each other. Hence, the resulting data frame will have prev_len fewer rows than the old data frame and its number of columns will be prev_len + 1 * number_of_columns_in_old_data_frame. Please see the example below with prev_len=2. Thanks a lot in advance!

Given data frame:

    x1  x2         y
0  166   9 -2.426679
1  192   6 -0.428913
2  198   1  1.265936
3  117   0 -0.866740
4  183   1 -0.678886

Desired data frame:

   00_x1  00_x2      00_y  01_x1  01_x2      01_y  02_x1  02_x2      02_y
0  166.0    9.0 -2.426679  192.0    6.0 -0.428913  198.0    1.0  1.265936
1  192.0    6.0 -0.428913  198.0    1.0  1.265936  117.0    0.0 -0.866740
2  198.0    1.0  1.265936  117.0    0.0 -0.866740  183.0    1.0 -0.678886

My solution:

import numpy as np
import pandas as pd
import random 

# given data ----------------------------------------------------------
np.random.seed(seed=123)
df = pd.DataFrame({'x1': np.random.randint(100, 200, 5), 
                       'x2': np.random.randint(0,10,5), 
                       'y': np.random.randn(5)})
print(df)

# desired data  -------------------------------------------------------
prev_len = 2

lag = []

for i in range(prev_len + 1):
    lag += [i] * len(df.columns.to_list())

col = df.columns.to_list() * (prev_len + 1)
colnames = ["{:02}_{}".format(lag_, col_) for lag_, col_ in zip(lag, col)]

df_new = pd.DataFrame(columns = colnames)

for i_new, i_old in zip(range(df.shape[0] - prev_len), range(prev_len, df.shape[0])):

    obs = pd.Series()

    print(i_old)

    for j in range(i_old - 2, i_old + 1):

        obs = obs.append(df.iloc[j, :])

    df_new.loc[i_new] = obs.to_list()

print(df_new)
pandas python-3.x python
3 Answers

Edit: As suggested by @user3483203 I generalize it 

prev_len = 2
key_cols = range(prev_len+1)
df_new = pd.concat([df.shift(-i) for i in key_cols], axis=1, keys=map(str, key_cols)).dropna()
df_new.columns = df_new.columns.map('_'.join)

Original:
For your requrement of prev_len = 2, I think pd.concat, shift, and dropna are sufficient

df_new = pd.concat([df, df.shift(-1), df.shift(-2)], axis=1, keys=['0', '1', '2']).dropna()
df_new.columns = df_new.columns.map('_'.join)


Out[556]:
   0_x1  0_x2       0_y   1_x1  1_x2       1_y   2_x1  2_x2       2_y
0   166     9 -2.426679  192.0   6.0 -0.428913  198.0   1.0  1.265936
1   192     6 -0.428913  198.0   1.0  1.265936  117.0   0.0 -0.866740
2   198     1  1.265936  117.0   0.0 -0.866740  183.0   1.0 -0.678886
user_101892140

I'd use skimage.util.view_as_windows and follow up with a reshape. In a general sense, you want your window_shape's first axis size to be one more than whatever your k is so it includes the current row plus k previous rows.

from skimage.util import view_as_windows

k = 2
x, y = df.shape
u = df.values

w = view_as_windows(u, window_shape=(k+1, y)).reshape(-1, y*(k+1))

res = pd.DataFrame(
    w, columns=[f'{i:02}_{col}' for i in range(k+1) for col in df.columns]
)


   00_x1  00_x2      00_y  01_x1  01_x2      01_y  02_x1  02_x2      02_y
0  166.0    9.0 -2.426679  192.0    6.0 -0.428913  198.0    1.0  1.265936
1  192.0    6.0 -0.428913  198.0    1.0  1.265936  117.0    0.0 -0.866740
2  198.0    1.0  1.265936  117.0    0.0 -0.866740  183.0    1.0 -0.678886
user_34832030

For max flexibility, I like iterating through pandas objects. This approach can have mixed performance until optimized, so I encourage you to tinker until it matches a speed suitable for performance.

Initialize data:

import pandas as pd

data = {"index":[0,1,2,3,4],
       "x1":[166,192,198,117,183],
       "x2":[9,6,1,0,1],
       "y":[-2.426679,-0.428913,1.265936,-0.866740, -0.678886]}

df = pd.DataFrame(data)
df.set_index('index', inplace=True)

Iterate and build new df:


lookahead = 2

records = []

for idx in df.index[:-lookahead]:
  # Create an empty record
  rec = {}
  # Lookahead + 1
  for i in range(0,lookahead+1):
    # Get the values
    x1, x2, y = df.iloc[idx+i,: ]
    # Cycle through, then add
    for k, v in zip(['x1','x2','y'],[x1,x2,y]):
      rec[f"{i:02d}_{k}"] = v
  # Append
  records.append(rec)

# Write your df
df_end = pd.DataFrame(records)

# yields:
    00_x1   00_x2   00_y    01_x1   01_x2   01_y    02_x1   02_x2   02_y
0   166.0   9.0 -2.426679   192.0   6.0 -0.428913   198.0   1.0 1.265936
1   192.0   6.0 -0.428913   198.0   1.0 1.265936    117.0   0.0 -0.866740
2   198.0   1.0 1.265936    117.0   0.0 -0.866740   183.0   1.0 -0.678886
user_105219590
Related