Best way to create ladder like dataframe using python

[vivan]

I have a list of dataframes:

data1 = {'rec_number': [1, 2, ,3 ,4, 5, 6]}
data2 = {'rec_number': [1, 2, ,3 ,4, 5, 6, 7, 8]}
data3 = {'rec_number': [1, 2, ,3 ,4]}
df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)
df3 = pd.DataFrame(data3)
lst = [df1, df2, df3]

My list would have thousands of dataframes, each having different 'rec_number', I need to add additional columns to all the dataframes to create a "shift like" columns. (take df1 for example):

data1 = {'rec_number': [1, 2, ,3 ,4, 5, 6], 
'rec_number_2': [np.nan, 1, 2, 3, 4, 5], 
'rec_number_3': [np.nan, np.nan, 1, 2, 3, 4], 
'rec_number_4': [np.nan, np.nan, np.nan, 1, 2, 3], 
'rec_number_5': [np.nan, np.nan, np.nan, np.nan, 1, 2], 
'rec_number_6': [np.nan, np.nan, np.nan, np.nan, np.nan, 1]}

df1 = pd.DataFrame(data1)

What would be most efficient way to add those additional columns?

Thanks!

Gribouillis write Aug-05-2024, 05:45 AM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.

[pintailscratchy]

To efficiently add "shift-like" columns to each DataFrame in your list, you can use the following approach. This solution iterates through each DataFrame and applies the shifting logic to create the additional columns. Here’s how you can do it:

import pandas as pd
import numpy as np

def add_shift_columns(df, max_shifts=6):
    for i in range(1, max_shifts + 1):
        df[f'rec_number_{i + 1}'] = df['rec_number'].shift(i)
    return df

# List of DataFrames
data1 = {'rec_number': [1, 2, 3, 4, 5, 6]}
data2 = {'rec_number': [1, 2, 3, 4, 5, 6, 7, 8]}
data3 = {'rec_number': [1, 2, 3, 4]}
df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)
df3 = pd.DataFrame(data3)
lst = [df1, df2, df3]

# Apply the function to each DataFrame
max_shifts = 6  # Define the maximum number of shifts you want
lst = [add_shift_columns(df, max_shifts) for df in lst]

# Check the updated DataFrames
for df in lst:
    print(df)

This code defines a function add_shift_columns to add the shifted columns up to a specified number (max_shifts). Then, it applies this function to each DataFrame in your list. Adjust max_shifts as needed based on your requirements. This method is efficient and scalable for large numbers of DataFrames.

bitlife

[Nere1995]

Thanks for the solution! This method of adding "shift-like" columns works perfectly. I appreciate the explanation and the clean implementation using the shift function in pandas. It's a great way to efficiently add multiple shifted columns to each DataFrame. I’ll definitely apply this in my project, especially for handling larger datasets. The use of list comprehension to apply the function across DataFrames is a nice touch! LINK REMOVED

Gribouillis write Jan-12-2025, 06:04 PM:
Clickbait link removed. Please read What to NOT include in a post