VSTACK, HSTACK, TAKE and CHOOSECOLS Equivalents in pandas

Excel 365's array functions made it possible to reshape data without helper columns: VSTACK appends the monthly sheets into one list, CHOOSECOLS keeps the columns a report needs, TAKE shows the first or last few rows, and TOCOL flattens a grid into a single column. They read almost like pandas — and most of them map to a single pandas call. The exception is important: Excel stacks ranges by position, while pandas aligns by column name (for rows) and index (for columns), so the same-looking operation can line data up differently. This page maps each function and verifies the results against the spreadsheet itself. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install pandas numpy openpyxl

months.xlsx has a Jan and a Feb sheet with the same three fields — except that whoever built February put Orders before Sales. That small inconsistency is exactly where position-based and name-based stacking part ways. LibreOffice (soffice) evaluates the Excel formulas in Step 6; recent versions support these functions.

Step 1 — VSTACK: append rows

import pandas as pd

sheets = pd.read_excel("months.xlsx", sheet_name=None)
stacked = pd.concat(sheets, names=["Month", None]).reset_index(level=0)
print(stacked.reset_index(drop=True))

pd.concat aligns the frames by column name, so February's Sales lands under Sales even though it is the third column in that sheet. Passing the dictionary of sheets labels each row with its source sheet, which VSTACK cannot do. =VSTACK(Jan!A2:C3, Feb!A2:C4) stacks by position: February's orders would appear in the Sales column and its sales in Orders — a silent swap that the spreadsheet will happily total. When the sheets genuinely share a layout the results are identical; when they do not, pandas is right and Excel is not.

To reproduce VSTACK exactly — for example to match a workbook that already contains it — ignore the headers and stack the raw values:

by_position = pd.DataFrame(
    [row for df in sheets.values() for row in df.to_numpy().tolist()],
    columns=sheets["Jan"].columns,
)
print(by_position)

Step 2 — HSTACK: put ranges side by side

HSTACK places ranges next to each other by position; pd.concat(axis=1) aligns on the index:

left = sheets["Jan"][["Region", "Sales"]]
right = sheets["Feb"][["Region", "Sales"]]
print(pd.concat([left, right], axis=1, keys=["Jan", "Feb"]))

Both frames have a default index 0, 1, 2, so row 0 meets row 0 — position and index coincide, and the result matches HSTACK. The third February row has no January partner and gets NaN, where Excel pads the shorter range with #N/A. Note that North and North happen to align, but South sits next to East: neither tool matches keys. Side-by-side comparisons by region need a merge on the key instead, as in Merge Two Excel Files on a Common Column with Python.

Step 3 — TAKE and DROP

data = stacked.reset_index(drop=True)
print(data.head(2), data.tail(2), data.iloc[2:], data.iloc[:, :2], sep="\n\n")

TAKE(range, 2) is head(2) and TAKE(range, -2) is tail(2); DROP(range, 2) is iloc[2:] and DROP(range, -2) is iloc[:-2]. Both Excel functions also take a column count — TAKE(range, , 2) keeps the first two columns, iloc[:, :2]. Combined with a sort, TAKE is the usual way to show a top-N list; see Get the Top N Rows per Group in pandas for the grouped version.

Step 4 — CHOOSECOLS and CHOOSEROWS

Excel counts columns from 1 and counts negative numbers from the end; Python counts from 0. A tiny helper keeps the Excel numbering for anyone translating formulas line by line:

def choosecols(df, *cols):
    return df.iloc[:, [c - 1 if c > 0 else c for c in cols]]

def chooserows(df, *rows):
    return df.iloc[[r - 1 if r > 0 else r for r in rows]]

print(choosecols(data, 1, 3))      # CHOOSECOLS(range, 1, 3)
print(chooserows(data, 1, -1))     # CHOOSEROWS(range, 1, -1): first and last rows

In pandas code you would normally select by name — data[["Region", "Orders"]] — which survives column reordering; the numeric helper is for faithful translation and for files whose headers cannot be trusted.

Step 5 — TOCOL and WRAPROWS

TOCOL flattens a grid into one column, reading row by row unless told otherwise; WRAPROWS does the reverse, cutting a list into rows of a given width:

import numpy as np

grid = data[["Region", "Sales", "Orders"]]
print(grid.to_numpy().ravel().tolist())                 # TOCOL(range)
print(grid.to_numpy().ravel(order="F").tolist()[:5])     # TOCOL(range, , TRUE): by column

def wraprows(values, width, pad=None):
    values = list(values)
    values += [pad] * (-len(values) % width)
    return pd.DataFrame(np.array(values, dtype=object).reshape(-1, width))

print(wraprows(["A", "B", "C", "D", "E", "F", "G"], 3))

ravel() reads row by row like TOCOL's default; order="F" reads column by column like its scan_by_column option. wraprows pads the last row, as WRAPROWS does with its pad_with argument (without it Excel fills the gap with #N/A). These are handy for turning a label list into a printable grid or flattening a matrix-style sheet before analysis — though for data, melt is usually the better tool, see Unpivot a Wide Excel Sheet with pandas melt.

Step 6 — Check against the spreadsheet

import subprocess
from openpyxl import load_workbook
from openpyxl.worksheet.formula import ArrayFormula

wb = load_workbook("months.xlsx")
out = wb.create_sheet("Out")
out["A1"] = ArrayFormula("A1:C5", "=_xlfn.VSTACK(Jan!A2:C3,Feb!A2:C4)")
out["E1"] = ArrayFormula("E1:F5", "=_xlfn.CHOOSECOLS(A1:C5,1,3)")
out["H1"] = ArrayFormula("H1:J2", "=_xlfn.TAKE(A1:C5,-2)")
out["A8"] = ArrayFormula("A8:A22", "=_xlfn.TOCOL(A1:C5)")
wb.save("months_formulas.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "recalc", "months_formulas.xlsx"],
               check=True, capture_output=True)
ws = load_workbook("recalc/months_formulas.xlsx", data_only=True)["Out"]
vstack = [[c.value for c in row] for row in ws["A1:C5"]]
print("VSTACK rows:", vstack[2:4])
print("matches position-based stack:", vstack == by_position.to_numpy().tolist())
print("TAKE -2:", [[c.value for c in row] for row in ws["H1:J2"]])
print("TOCOL first 6:", [ws[f"A{i}"].value for i in range(8, 14)])

The recalculated VSTACK rows for February are ['North', 3, 7] and ['East', 4, 4] — orders in the Sales column — and the whole block equals the position-based frame from Step 1, not the name-aligned concat. TAKE and TOCOL match tail and ravel applied to that position-based frame — the last row is West, 5, 1 there, while the name-aligned frame correctly shows West's sales as 1 and orders as 5. The array formulas are written as ArrayFormula objects with _xlfn. names because these functions are recent additions; the reasons are explained in Write Legacy Array Formulas (Ctrl+Shift+Enter) with openpyxl.

Performance notes

All of these operations are cheap in pandas: concat of a few dozen monthly sheets with tens of thousands of rows each takes milliseconds, and iloc selections and ravel are views or single copies. The cost in a real job is reading the sheets, not reshaping them. In Excel, by contrast, a VSTACK over many large ranges recalculates on every change to any of them; moving that consolidation into Python and writing the result as values keeps big workbooks responsive. When stacking many files, collect the frames in a list and call concat once — appending inside a loop copies the growing frame every time and turns a linear job into a quadratic one.

Common pitfalls

• Assuming concat stacks by position. It aligns by column name; columns in a different order are matched, not swapped — usually what you want, but not what VSTACK does.
• Misspelled headers. Sales and Sales are different names to pandas and become two half-empty columns; standardise names first.
• Off-by-one indexes. Excel's CHOOSECOLS(range, 1) is iloc[:, 0].
• Expecting key matching from HSTACK or axis=1 concat. Both line up rows by position or index; use merge for keys.
• #N/A versus NaN. Excel pads ragged stacks with #N/A; pandas with NaN, which fillna can replace.
• Duplicate index labels after concat. Each source keeps its own 0, 1, 2… index; pass ignore_index=True or call reset_index(drop=True) before selecting rows by label.

Conclusion

VSTACK and HSTACK are pd.concat along rows and columns, TAKE and DROP are head, tail and iloc, CHOOSECOLS and CHOOSEROWS are iloc with Excel's 1-based positions converted, and TOCOL and WRAPROWS are NumPy ravel and reshape. The difference to remember is alignment: Excel stacks by position, pandas by column name and index — which makes pandas safer when sheets disagree on column order, and means you must stack raw values when you need to match an existing VSTACK exactly.

Frequently asked questions

What is the pandas equivalent of Excel VSTACK? pd.concat(df1, df2) stacks DataFrames vertically. Unlike VSTACK, it matches columns by name rather than position, so sheets with the same columns in a different order are combined correctly.

How do I do CHOOSECOLS in pandas? Select by name with df[["Region", "Orders"]], or by position with df.iloc:, 0, 2. Excel's positions start at 1, so subtract one; negative positions count from the end in both.

What replaces Excel TAKE and DROP in pandas? TAKE(range, n) is df.head(n) and TAKE(range, -n) is df.tail(n). DROP(range, n) is df.ilocn: and DROP(range, -n) is df.iloc:-n.

How do I flatten a range into one column like TOCOL? Use df.to_numpy().ravel() to read row by row, which matches TOCOL's default, or ravel(order="F") to read column by column. For data with headers, melt is usually more useful.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions in pandas.
• Concatenate Excel Sheets with Different Columns — stacking when the columns do not match.
• UNIQUE, SORT and FILTER Equivalents in pandas — the other dynamic array functions.
• Read All Sheets from an Excel File into DataFrames — loading the sheets to stack.