Duplicating n rows in a DataFrame using 2nd level index?

user_4442753 0
Viewed 59

I have a pandas DataFrame that for instance is looking like this.

df
            Values
Timestamp
2020-02-01       A
2020-02-02       B
2020-02-03       C

I would like (to ease processing to be done afterward) to keep a window of n row and duplicate it for each timestamp, and creating a 2nd level index with local int index.

With n=2, this would give:

df_new
                                Values
Timestamp   2nd_level_index
2020-02-01                0        NaN
                          1          A
2020-02-02                0          A
                          1          B
2020-03-03                0          B
                          1          C

Is there any kind of pandas built-in function that would help me do that? A rolling window with fixed size (n) seems to be the start, but then how do I duplicate the window and store it for each row using a 2nd level index?

Thanks in advance for any help! Bests,

EDIT 04/05

Taking propose code, and changing a bit the output format, I adapted it for a 2-column DataFrame.

I ended up with following code.

import pandas as pd
import numpy as np
from random import seed, randint

def transpose_n_rows(df: pd.DataFrame, n_rows: int) -> pd.DataFrame:

    array = np.concatenate((np.full((len(df.columns),n_rows-1), np.nan), df.transpose()), axis=1)

    shape = array.shape[:-1] + (array.shape[-1] - n_rows + 1, n_rows)
    strides = array.strides + (array.strides[-1],)
    array = np.lib.stride_tricks.as_strided(array, shape=shape, strides=strides)

    midx = pd.MultiIndex.from_product([df.columns, range(n_rows)], names=['Data','Position'])
    transposed = pd.DataFrame(np.concatenate(array, axis=1), index=df.index, columns=midx)

    return transposed

n = 4
start = '2020-01-01 00:00+00:00'
end = '2020-01-01 12:00+00:00'

pr2h = pd.period_range(start=start, end=end, freq='2h')
seed(1)
values1 = [randint(0,10) for ts in pr2h]
values2 = [randint(20,30) for ts in pr2h]
df2h = pd.DataFrame({'Values1' : values1, 'Values2': values2}, index=pr2h)

df2h_new = transpose_n_rows(df2h, n)

Which gives.

In [29]:df2h
Out[29]: 
                  Values1  Values2
2020-01-01 00:00        2       27
2020-01-01 02:00        9       30
2020-01-01 04:00        1       26
2020-01-01 06:00        4       23
2020-01-01 08:00        1       21
2020-01-01 10:00        7       27
2020-01-01 12:00        7       20

In [30]:df2h_new
Out[30]: 
Data             Values1                Values2                  
Position               0    1    2    3       0     1     2     3
2020-01-01 00:00     NaN  NaN  NaN  2.0     NaN   NaN   NaN  27.0
2020-01-01 02:00     NaN  NaN  2.0  9.0     NaN   NaN  27.0  30.0
2020-01-01 04:00     NaN  2.0  9.0  1.0     NaN  27.0  30.0  26.0
2020-01-01 06:00     2.0  9.0  1.0  4.0    27.0  30.0  26.0  23.0
2020-01-01 08:00     9.0  1.0  4.0  1.0    30.0  26.0  23.0  21.0
2020-01-01 10:00     1.0  4.0  1.0  7.0    26.0  23.0  21.0  27.0
2020-01-01 12:00     4.0  1.0  7.0  7.0    23.0  21.0  27.0  20.0

However, I am calling this function transpose_n_rows in a for loop for a significant number of DataFrames. This first use makes me a bit afraid with performance issues.

I could read that one should avoid multiple calls to np.concatenate or pd.concat, and here, I have 2 of them for a use that maybe can be bypassed?

Please, is there any advice to get rid of them if this is possible?

I thank you in advance for any help! Bests,

rolling-computation pandas python
1 Answers

I think there is not built-in method in pandas.

Possible solution with strides for generate rolling 2d array:

n = 2
#added Nones for first values of 2d array
x = np.concatenate([[None] * (n-1), df['Values']])

def rolling_window(a, window):
    shape = a.shape[:-1] + (a.shape[-1] - window + 1, window)
    strides = a.strides + (a.strides[-1],)
    return np.lib.stride_tricks.as_strided(a, shape=shape, strides=strides)
a = rolling_window(x, n)
print (a)
[[None 'A']
 ['A' 'B']
 ['B' 'C']]

And then create MultiIndex by MultiIndex.from_product and flatten values of array by numpy.ravel:

mux = pd.MultiIndex.from_product([df.index, range(n)], names=('times','level1'))
df = pd.DataFrame({'Values': np.ravel(a)}, index=mux)
print (df)
                  Values
times      level1       
2020-02-01 0        None
           1           A
2020-02-02 0           A
           1           B
2020-02-03 0           B
           1           C

If values are numbers add missing values:

print (df)
            Values
Timestamp         
2020-02-01       1
2020-02-02       2
2020-02-03       3

n = 2
x = np.concatenate([[np.nan] * (n-1), df['Values']])

def rolling_window(a, window):
    shape = a.shape[:-1] + (a.shape[-1] - window + 1, window)
    strides = a.strides + (a.strides[-1],)
    return np.lib.stride_tricks.as_strided(a, shape=shape, strides=strides)
a = rolling_window(x, n)
print (a)
[[nan  1.]
 [ 1.  2.]
 [ 2.  3.]]

mux = pd.MultiIndex.from_product([df.index, range(n)], names=('times','level1'))
df = pd.DataFrame({'Values': np.ravel(a)}, index=mux)
print (df)

                   Values
times      level1        
2020-02-01 0          NaN
           1          1.0
2020-02-02 0          1.0
           1          2.0
2020-02-03 0          2.0
           1          3.0
user_29010020
Related