How to append dataframe with columns from next row?

Question

Here is an example dataframe.

import pandas as pd

index_labels = pd.date_range(start='1/1/2018', end='1/08/2018')
column_labels = pd.MultiIndex.from_product([['Function A', 'Function B'],
                                            ['Fund 1', 'Fund 1', 'Fund 3']])
df = pd.DataFrame(index=index_labels, columns=column_labels)
for i in range(len(df.index)):
    for j in range(len(df.columns)):
        if df.columns[j][0] == 'Function A':
            df.iloc[i, j] = i + 1 + j/10
        else:
            df.iloc[i, j] = i + 1 + j/100

df.head()

           Function A               Function B              
               Fund 1 Fund 1 Fund 3     Fund 1 Fund 1 Fund 3
2018-01-01        0.0    0.1    0.2       0.03   0.04   0.05
2018-01-02        1.0    1.1    1.2       1.03   1.04   1.05
2018-01-03        2.0    2.1    2.2       2.03   2.04   2.05
2018-01-04        3.0    3.1    3.2       3.03   3.04   3.05
2018-01-05        4.0    4.1    4.2       4.03   4.04   4.05

I would like a function to recursively append rows and modify the column level 1 name to include then number of shifts. for example fund id:number_shifts

For example, calling with 2 shifts df2 = column_shift(df, 2) should produce the following The function should also work with negative shifts

            Function A               Function B           Function A                     Function B                 Function A                    Function B              
            Fund 1 Fund 2 Fund 3    Fund 1 Fund 2 Fund 3  Fund 1:1 Fund 2:1 Fund 3:1     Fund 1:1 Fund 2:1 Fund 3:1 Fund 1:2 Fund 2:2 Fund 3:2    Fund 1:2 Fund 2:2 Fund 3:2
2018-01-01  0.0    0.1    0.2       0.03   0.04   0.05    1.0      1.1      1.2          1.03     1.04     1.05     2.0      2.1      2.2         2.03     2.04     2.05
2018-01-02  1.0    1.1    1.2       1.03   1.04   1.05    2.0      2.1      2.2          2.03     2.04     2.05     3.0      3.1      3.2         3.03     3.04     3.05
2018-01-03  2.0    2.1    2.2       2.03   2.04   2.05    3.0      3.1      3.2          3.03     3.04     3.05     4.0      4.1      4.2         4.03     4.04     4.05

Answer 1

This code may not be pretty, but it works... I attempted drop, join / merge / concat, but wasn't sucessful...

import pandas as pd


def lead_data(_df, _n_leads):

    # Create a list containing the modified column names
    orig_columns_list = _df.columns.tolist().copy()
    mod_cols_list = []
    for i_lead in range(1, _n_leads+1):
        for j_list, t in enumerate(orig_columns_list):
            mod_cols_list.append(t[:-1] + (t[-1] + ':' + str(i_lead),))

    # Create a blank dataframe
    _lead_df = pd.DataFrame(columns=pd.MultiIndex.from_tuples(mod_cols_list), index=_df.index.copy())

    # Populate the dataframe
    n_rows = len(_df.index)
    n_columns = len(_df.columns)
    for k in range(1, _n_leads+1):
        for j in range(n_columns):
            for i in range(k, n_rows):
                _lead_df.iloc[i-k, j+(k-1)*n_columns] = _df.iloc[i, j]

    return _lead_df


def lag_data(_df, _n_lags):

    # Create a list containing the modified column names
    orig_columns_list = _df.columns.tolist().copy()
    mod_cols_list = orig_columns_list.copy()
    for i_lag in range(1, _n_lags+1):
        for j_list, t in enumerate(orig_columns_list):
            mod_cols_list.append(t[:-1] + (t[-1] + ':' + str(-i_lag),))

    # Create a blank dataframe
    _lag_df = pd.DataFrame(columns=pd.MultiIndex.from_tuples(mod_cols_list), index=_df.index.copy())

    # Populate the dataframe
    # Original data
    n_rows = len(_df.index)
    n_columns = len(_df.columns)
    for j in range(n_columns):
        for i in range(n_rows):
            _lag_df.iloc[i, j] = _df.iloc[i, j]

    # Lagged data
    for k in range(1, _n_lags+1):
        for j in range(n_columns):
            for i in range(n_rows-k):
                _lag_df.iloc[i, j+k*n_columns] = _df.iloc[i+k, j]

    return _lag_df


def generate_data():
    index_labels = pd.date_range(start='1/1/2018', end='1/05/2018')
    column_labels = pd.MultiIndex.from_product([['Function A', 'Function B'],
                                                ['Fund 1', 'Fund 1', 'Fund 3']])
    _df = pd.DataFrame(index=index_labels, columns=column_labels)
    for i in range(len(_df.index)):
        for j in range(len(_df.columns)):
            if _df.columns[j][0] == 'Function A':
                _df.iloc[i, j] = i + 1 + j / 10
            else:
                _df.iloc[i, j] = i + 1 + j / 100
    return _df


pd.set_option('display.max_columns', None)
df = generate_data()
df_lag = lag_data(df, 3)
df_lead = lead_data(df, 3)

df.head()
print(df_lag)
print(df_lead)

Output

           Function A               Function B               Function A  \
               Fund 1 Fund 1 Fund 3     Fund 1 Fund 1 Fund 3  Fund 1:-1   
2018-01-01        1.0    1.1    1.2       1.03   1.04   1.05        2.0   
2018-01-02        2.0    2.1    2.2       2.03   2.04   2.05        3.0   
2018-01-03        3.0    3.1    3.2       3.03   3.04   3.05        4.0   
2018-01-04        4.0    4.1    4.2       4.03   4.04   4.05        5.0   
2018-01-05        5.0    5.1    5.2       5.03   5.04   5.05        NaN   

                               Function B                     Function A  \
           Fund 1:-1 Fund 3:-1  Fund 1:-1 Fund 1:-1 Fund 3:-1  Fund 1:-2   
2018-01-01       2.1       2.2       2.03      2.04      2.05        3.0   
2018-01-02       3.1       3.2       3.03      3.04      3.05        4.0   
2018-01-03       4.1       4.2       4.03      4.04      4.05        5.0   
2018-01-04       5.1       5.2       5.03      5.04      5.05        NaN   
2018-01-05       NaN       NaN        NaN       NaN       NaN        NaN   

                               Function B                     Function A  \
           Fund 1:-2 Fund 3:-2  Fund 1:-2 Fund 1:-2 Fund 3:-2  Fund 1:-3   
2018-01-01       3.1       3.2       3.03      3.04      3.05        4.0   
2018-01-02       4.1       4.2       4.03      4.04      4.05        5.0   
2018-01-03       5.1       5.2       5.03      5.04      5.05        NaN   
2018-01-04       NaN       NaN        NaN       NaN       NaN        NaN   
2018-01-05       NaN       NaN        NaN       NaN       NaN        NaN   

                               Function B                      
           Fund 1:-3 Fund 3:-3  Fund 1:-3 Fund 1:-3 Fund 3:-3  
2018-01-01       4.1       4.2       4.03      4.04      4.05  
2018-01-02       5.1       5.2       5.03      5.04      5.05  
2018-01-03       NaN       NaN        NaN       NaN       NaN  
2018-01-04       NaN       NaN        NaN       NaN       NaN  
2018-01-05       NaN       NaN        NaN       NaN       NaN  

Here are the lead values

           Function A                   Function B                    \
             Fund 1:1 Fund 1:1 Fund 3:1   Fund 1:1 Fund 1:1 Fund 3:1   
2018-01-01        2.0      2.1      2.2       2.03     2.04     2.05   
2018-01-02        3.0      3.1      3.2       3.03     3.04     3.05   
2018-01-03        4.0      4.1      4.2       4.03     4.04     4.05   
2018-01-04        5.0      5.1      5.2       5.03     5.04     5.05   
2018-01-05        NaN      NaN      NaN        NaN      NaN      NaN   

           Function A                   Function B                    \
             Fund 1:2 Fund 1:2 Fund 3:2   Fund 1:2 Fund 1:2 Fund 3:2   
2018-01-01        3.0      3.1      3.2       3.03     3.04     3.05   
2018-01-02        4.0      4.1      4.2       4.03     4.04     4.05   
2018-01-03        5.0      5.1      5.2       5.03     5.04     5.05   
2018-01-04        NaN      NaN      NaN        NaN      NaN      NaN   
2018-01-05        NaN      NaN      NaN        NaN      NaN      NaN   

           Function A                   Function B                    
             Fund 1:3 Fund 1:3 Fund 3:3   Fund 1:3 Fund 1:3 Fund 3:3  
2018-01-01        4.0      4.1      4.2       4.03     4.04     4.05  
2018-01-02        5.0      5.1      5.2       5.03     5.04     5.05  
2018-01-03        NaN      NaN      NaN        NaN      NaN      NaN  
2018-01-04        NaN      NaN      NaN        NaN      NaN      NaN  
2018-01-05        NaN      NaN      NaN        NaN      NaN      NaN