Forum Discussion

joelb95's avatar
joelb95
Brass Contributor
Nov 04, 2024

Stacking row/columns of an array - some module formulas

I just wrote a few functions that should theoretically allow you to take an array, chunk it by rows or columns, and then stack them in the other dimensions, e.g. you have 10 rows by 3 columns and you want to stack them as a set of 5 rows by 6 columns.  You should be able to use this to completely flatten an array (e.g. take each column and place them one under the other).  It has optional parameters for separate fill values for rows and columns.

 

I welcome any comments, suggestions, or alternative approaches.

 

By way of contrast, if you had an array like:

 

ctrldateend_date
11/1/20211/1/2021
21/15/20211/15/2021
32/2/20212/2/2021

 

Excel's native TOCOL function would flatten it into one column like this:

=TOCOL(B4:D6) or =TOCOL(B4:D6,,FALSE)

ctrl
date
end_date
1
44197
44197
2
44211

44211

 

or this

=TOCOL(B4:D6,,TRUE)

 

ctrl
1
2
date
44197
44211
end_date
44197
44211

 

whereas mine flattens it like this:

 

=chunk.vStackChunksOfColumns(B4:D7,1)

ctrl
1
2
date
44197
44211
end_date
44197
44211

 

or this

=chunk.vStackChunksOfColumns(B4:D6,2)

ctrldate
144197
244211
end_date 
44197 
44211 

 

 

or this (which is effectively no change):

=chunk.vStackChunksOfColumns(B4:D6,3)

 

ctrldateend_date
14419744197
24421144211

 

 

 

hStackChunksOfRows =
lambda(
    target_array,
    rows_per_slice,
    [no_columns_to_adj_slice_to_df_NO_ADJUSTMENT],
    [fill_value_for_added_columns_df_DBLQTS],
    [no_rows_to_adj_array_to_df_ROWS_PER_SLICE_TIMES_NO_OF_SLICES],
    [fill_value_for_added_rows_df_DBLQTS],
    let(
        rows_in_array, rows(target_array),
        cols_in_array, columns(target_array),
        no_of_slices, roundup(rows_in_array/rows_per_slice,0),
        target_slice_width, 
            if(
                if(
                    isomitted(no_columns_to_adj_slice_to_df_NO_ADJUSTMENT),
                    FALSE,
                    no_columns_to_adj_slice_to_df_NO_ADJUSTMENT
                ),
                no_columns_to_adj_slice_to_df_NO_ADJUSTMENT,
                cols_in_array
            )
        ,
        target_array_height, 
            if(
                isomitted(no_rows_to_adj_array_to_df_ROWS_PER_SLICE_TIMES_NO_OF_SLICES),
                rows_per_slice * no_of_slices,
                no_rows_to_adj_array_to_df_ROWS_PER_SLICE_TIMES_NO_OF_SLICES
            ),
        fill_value_for_added_columns, if(isomitted(fill_value_for_added_columns_df_DBLQTS),"",fill_value_for_added_columns_df_DBLQTS),
        fill_value_for_added_rows, if(isomitted(fill_value_for_added_rows_df_DBLQTS),"",fill_value_for_added_rows_df_DBLQTS),
        adj_array, 
            normalizeArray(
                target_array,
                target_array_height,
                target_slice_width,
                FALSE,
                fill_value_for_added_rows,
                fill_value_for_added_columns
        ),
        stack_iter, sequence(no_of_slices-1),
        stack_func,
            lambda(
                acc,
                slice_counter,
                hstack(
                    acc,
                    take(drop(adj_array,(slice_counter)*rows_per_slice),rows_per_slice)
                )
        ),
        ifs(
            rows_in_array<=rows_per_slice,
            adj_array,
            rows_per_slice = 1,
            drop(reduce("",sequence(rows_in_array),lambda(acc, cur_row, hstack(acc, chooserows(adj_array,cur_row)))),,1),
            1,
            reduce(
                take(adj_array,rows_per_slice),
                stack_iter,
                stack_func
            )
        )
    )
);


vStackChunksOfColumns =
lambda(
    target_array,
    cols_per_slice,
    [no_rows_to_adj_slice_to_df_NO_ADJUSTMENT],
    [fill_value_for_added_rows_df_DBLQTS],
    [no_columns_to_adj_array_to_df_COLS_PER_SLICE_TIMES_NO_OF_SLICES],
    [fill_value_for_added_columns_df_DBLQTS],
    let(
        rows_in_array, rows(target_array),
        cols_in_array, columns(target_array),
        no_of_slices, roundup(cols_in_array/cols_per_slice,0),
        target_slice_height, 
            if(
                if(
                    isomitted(no_rows_to_adj_slice_to_df_NO_ADJUSTMENT),
                    FALSE,
                    no_rows_to_adj_slice_to_df_NO_ADJUSTMENT
                ),
                no_rows_to_adj_slice_to_df_NO_ADJUSTMENT,
                rows_in_array
            )
        ,
        target_array_width, 
            if(
                isomitted(no_columns_to_adj_array_to_df_COLS_PER_SLICE_TIMES_NO_OF_SLICES),
                cols_per_slice*no_of_slices,
                no_columns_to_adj_array_to_df_COLS_PER_SLICE_TIMES_NO_OF_SLICES
            ),
        fill_value_for_added_rows, if(isomitted(fill_value_for_added_rows_df_DBLQTS),"",fill_value_for_added_rows_df_DBLQTS),
        fill_value_for_added_columns, if(isomitted(fill_value_for_added_columns_df_DBLQTS),"",fill_value_for_added_columns_df_DBLQTS),
        adj_array, 
            normalizeArray(
                target_array,
                target_slice_height,
                target_array_width,
                TRUE,
                fill_value_for_added_rows,
                fill_value_for_added_columns
        ),
        stack_iter, sequence(,no_of_slices-1),
        stack_func,
            lambda(
                acc,
                slice_counter,
                vstack(
                    acc,
                    take(drop(adj_array,,(slice_counter)*cols_per_slice),,cols_per_slice)
                )
        ),
        result,
        ifs(
            cols_in_array<=cols_per_slice,
            adj_array,
            cols_per_slice = 1,
            drop(reduce("",sequence(,cols_in_array),lambda(acc, cur_col, vstack(acc, CHOOSECOLS(adj_array,cur_col)))),1),
            1,
            reduce(
                take(adj_array,,cols_per_slice),
                stack_iter,
                stack_func
            )
        ),
        result

    )
);


normalizeArray = 
LAMBDA(
    target_array,
    final_height,                   // Desired final number of rows
    final_width,                    // Desired final number of columns
    fill_rows_first,                // Should rows be filled first (default: TRUE)
    value_for_filling_rows,         // Value to fill additional rows (default: "")
    value_for_filling_columns,      // Value to fill additional columns (default: "")

    LET(
        // Get current array dimensions
        current_height, ROWS(target_array),
        current_width, COLUMNS(target_array),

        // Define row adjustment function (truncate or expand)
        row_func,
            IF(
                final_height <= current_height,
                LAMBDA(target_array_for_row_adjustment, TAKE(target_array_for_row_adjustment, final_height)), 
                LAMBDA(target_array_for_row_adjustment, EXPAND(target_array_for_row_adjustment, final_height, , value_for_filling_rows))
            ),

        // Define column adjustment function (truncate or expand)
        col_func,
            IF(
                final_width <= current_width,
                LAMBDA(target_array_for_col_adjustment, TAKE(target_array_for_col_adjustment, , final_width)), 
                LAMBDA(target_array_for_col_adjustment, EXPAND(target_array_for_col_adjustment, , final_width, value_for_filling_columns))
            ),

        // Apply adjustments
        adjusted_array,
            IF(
                fill_rows_first,
                row_func(col_func(target_array)),
                col_func(row_func(target_array))
            ),

        adjusted_array
    )
);

 

 

 

2 Replies

  • For users needing to reshape and manipulate data without creating custom functions, leveraging Power Query can be powerful. It has built-in functions for pivoting, unpivoting, and transforming data much like what you described.

  • UrielGrove's avatar
    UrielGrove
    Iron Contributor

    It sounds like you're working on a useful set of functions to manipulate arrays by chunking and stacking them in different dimensions. Below is a Python representation of how you might implement your desired functionality using NumPy, which is a powerful library for numerical computing.

    Let's create functions to accomplish the following tasks:

    1. Chunk an array by rows or columns.
    2. Stack chunks in the other dimension.
    3. Flatten an array.
    4. Handle optional fill values for rows and columns.

Resources