Forum Discussion
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:
ctrl | date | end_date |
1 | 1/1/2021 | 1/1/2021 |
2 | 1/15/2021 | 1/15/2021 |
3 | 2/2/2021 | 2/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)
ctrl | date |
1 | 44197 |
2 | 44211 |
end_date | |
44197 | |
44211 |
or this (which is effectively no change):
=chunk.vStackChunksOfColumns(B4:D6,3)
ctrl | date | end_date |
1 | 44197 | 44197 |
2 | 44211 | 44211 |
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
- CaspianMillerIron Contributor
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.
- UrielGroveIron 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:
- Chunk an array by rows or columns.
- Stack chunks in the other dimension.
- Flatten an array.
- Handle optional fill values for rows and columns.