TEXTJOIN Equivalent in pandas (Join Values per Group)

Excel's TEXTJOIN(", ", TRUE, B2:B20) turns a range into one string with a delimiter between the values, and combined with FILTER it is how people build "all products this customer bought" in a single cell. It is also one of the operations pandas does most naturally: joining strings is plain Python, and doing it per group is one groupby. The details that make it work on real data are the ones TEXTJOIN's ignore_empty argument hints at — blanks, numbers, duplicates, ordering — and one Excel limit that long lists run into. This page, part of Excel Formula Equivalents in pandas, covers each.

Prerequisites

pip install pandas openpyxl

orders.xlsx has seven order lines for three customers. Northwind bought Widgets twice; Contoso has one line with no product; Fabrikam has one with an empty string.

Step 1 — Join a whole column

For a single list, TEXTJOIN(", ", TRUE, range) is Python's str.join over the non-empty values:

import pandas as pd

orders = pd.read_excel("orders.xlsx", keep_default_na=False, na_values=[""])
products = orders["product"].dropna()
print(", ".join(products))

dropna does the job of ignore_empty=TRUE. It is not optional in pandas: a missing value is NaN, which is a float, and str.join raises TypeError the moment it meets one. Reading with na_values=[""] makes the empty-string cell missing too, so both kinds of blank are skipped the same way.

Step 2 — Join per group

The everyday version — one row per customer with everything they bought — is a groupby with ", ".join as the aggregation:

per_customer = (orders.dropna(subset=["product"])
                      .groupby("customer", sort=False)["product"]
                      .agg(", ".join)
                      .reset_index(name="products"))
print(per_customer.to_string(index=False))

sort=False keeps customers in the order they first appear, like a UNIQUE list in the sheet. Northwind's value lists Widgets twice, because it bought them twice — correct if the column is meant to show every line, redundant if it is meant to show which products. Step 4 handles that. Count Distinct Values per Group in pandas gives the count that often sits next to such a list.

Step 3 — Numbers must become text first

Joining the order numbers fails, because str.join only accepts strings:

try:
    orders.groupby("customer")["order_no"].agg(", ".join)
except TypeError as exc:
    print("TypeError:", exc)

order_lists = orders.groupby("customer", sort=False)["order_no"].agg(lambda s: ", ".join(s.astype(str)))
print(order_lists.to_dict())

Convert inside the aggregation with astype(str), or format the numbers first when their appearance matters — s.map("{:,.2f}".format) for amounts, s.dt.strftime("%d %b") for dates. TEXTJOIN converts numbers silently using their displayed format; pandas makes you choose, which avoids surprises like amounts joined with fifteen decimal places. Order numbers are a good case for plain astype(str): they are identifiers, and any thousands separator would make them harder to search for in another system.

Step 4 — Unique, sorted lists

"Which products has each customer bought?" wants each product once, usually in alphabetical order — TEXTJOIN(", ", TRUE, SORT(UNIQUE(FILTER(...)))) in Excel. In pandas, do both inside the aggregation function:

def join_unique(values, sep=", "):
    seen = values.dropna().loc[lambda s: s.str.strip() != ""]
    return sep.join(sorted(seen.unique(), key=str.lower))

bought = orders.groupby("customer", sort=False)["product"].agg(join_unique).reset_index(name="products")
print(bought.to_string(index=False))

Northwind's list is now Gadgets, Widgets. Sorting with key=str.lower ignores case, the way Excel's SORT does; plain sorted would put capitalised names first. If the order of first purchase matters more than the alphabet, drop the sorted and keep unique()'s order of appearance.

Show counts, or put each value on its own line

Two variations make joined lists more useful in reports. The first adds a count to each item — "Widgets (2), Gadgets (1)" — which TEXTJOIN cannot do without helper columns. value_counts inside the aggregation produces it directly, most frequent first:

def join_counts(values, sep=", "):
    counts = values.dropna().loc[lambda s: s.str.strip() != ""].value_counts()
    return sep.join(f"{item} ({n})" for item, n in counts.items())

print(orders.groupby("customer", sort=False)["product"].agg(join_counts).to_dict())

The second puts each value on its own line inside one cell — TEXTJOIN(CHAR(10), TRUE, ...) in Excel. Join with "\n" and switch on wrapping for the cell, or the line breaks will not show:

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active
ws["A1"] = "\n".join(sorted(orders["product"].dropna().loc[lambda s: s != ""].unique()))
ws["A1"].alignment = Alignment(wrap_text=True, vertical="top")
ws.row_dimensions[1].height = 45
wb.save("lines_in_cell.xlsx")
print(repr(ws["A1"].value))

Setting the row height explicitly makes every line visible whichever application opens the file; three lines of default-size text need roughly 45 points. A cell with dozens of lines is a sign the list belongs on its own sheet, one value per row.

Step 5 — Respect Excel's cell length limit

A cell in an Excel workbook holds at most 32,767 characters. A joined list for a large customer — every invoice number for ten years — can exceed it, and even well below the limit a cell with a thousand items is unreadable. Truncate with an explicit note:

def join_limited(values, limit=3, sep=", "):
    items = sorted(values.dropna().astype(str).unique(), key=str.lower)
    shown = sep.join(items[:limit])
    return shown if len(items) <= limit else f"{shown} … and {len(items) - limit} more"

many = pd.Series([f"INV-{n:04d}" for n in range(1, 8)])
print(join_limited(many))

"… and 4 more" tells the reader the list is incomplete, which a silent truncation does not. When the full list matters, write it as rows on a detail sheet and put a link or a count in the summary cell instead.

Step 6 — Split a joined cell back into rows

The reverse operation comes up just as often: a column that already holds "Widgets, Gadgets" needs one row per product. str.split makes lists and explode turns each list element into its own row:

back = bought.assign(product=bought["products"].str.split(", ")).explode("product")
back["product"] = back["product"].str.strip()
print(back[["customer", "product"]].to_string(index=False))

Four rows — Northwind's two products, Contoso's and Fabrikam's one each. Splitting on the exact delimiter used for joining makes the round trip clean; for lists typed by people, split on a pattern such as r"\s*[,;]\s*" to handle both commas and semicolons and stray spaces. Split a Column into Multiple Columns with pandas covers splitting into columns instead of rows.

Write the summary

Write the joined lists next to other per-customer figures, and widen the column and turn on wrapping so long lists stay readable:

from openpyxl import load_workbook
from openpyxl.styles import Alignment

summary = bought.merge(orders.groupby("customer").size().rename("lines").reset_index(), on="customer")
summary.to_excel("customers.xlsx", index=False)
wb = load_workbook("customers.xlsx")
ws = wb.active
ws.column_dimensions["B"].width = 40
for (cell,) in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    cell.alignment = Alignment(wrap_text=True, vertical="top")
wb.save("customers.xlsx")
print(pd.read_excel("customers.xlsx").to_string(index=False))

Wrap Text and Align Cells with openpyxl covers the alignment options.

Common pitfalls

• NaN in the values. str.join raises TypeError; drop missing values first.
• Numbers in the values. Convert to text, ideally with a deliberate format.
• Duplicates you did not want. Join unique() values when the list names things rather than lines.
• Very long cells. Stay well under 32,767 characters; summarise and link to details.
• Delimiters inside the values. If a product name contains a comma, a comma-joined list cannot be split back reliably; choose a delimiter that never appears in the data, such as " | ", when the text will be parsed again.
• Joining before filtering. Filter the rows first (a period, a status), then join — a list built from all rows and filtered afterwards still contains the excluded values.

Performance notes

Joining per group with a Python function is fast for thousands of groups and a few hundred thousand rows. For very large data, de-duplicate first — drop_duplicates(["customer", "product"]) — so each group joins only distinct values, and sort once before grouping instead of sorting inside every call. Both changes shrink the work per group without changing the result.

Conclusion

TEXTJOIN in pandas is ", ".join(...) over the non-missing values of a column, and per group it is groupby(...)[col].agg(...) with a join function. Drop missing values and empty strings first, convert numbers to text deliberately, de-duplicate and sort inside the aggregation when the list names things, cap long lists with an explicit "… and N more", and reverse the operation with str.split and explode.

Frequently asked questions

What is the pandas equivalent of TEXTJOIN? For a whole column, ", ".join(values) over the non-missing values. Per group, df.groupby("key")"col".agg(", ".join) after dropping missing values. Both put a delimiter between the values the way TEXTJOIN does.

How do I skip blanks like TEXTJOIN's ignore_empty? Remove missing values with dropna and empty strings with a filter before joining. str.join fails on NaN, which is a float, so missing values must go first in any case.

Why does join fail on my number column? str.join only accepts strings. Convert first with astype(str) — or format the numbers the way you want them to appear — and then join.

Is there a limit on how long the joined text can be? Excel cells hold at most 32,767 characters. For long lists, truncate with a note such as "… and 120 more", or write the values as rows on a separate sheet instead of cramming them into one cell.

Related

• Up one level: Excel Formula Equivalents in pandas — every function, translated.
• Excel Text Functions LEFT, RIGHT, MID and CONCAT in pandas — joining a few columns in one row.
• UNIQUE, SORT and FILTER Equivalents in pandas — the functions usually nested inside TEXTJOIN.
• Parse Records That Span Multiple Rows in Excel with pandas — combining continuation rows.