Pivot Text Values Instead of Numbers in pandas

Pivot tables are usually about sums and counts, but plenty of reports need the text itself arranged in a grid: a project status board with projects down the side and weeks across the top showing "On track", "At risk" or "Late"; a rota with employees by day showing shift codes; a matrix of approvers and documents showing who signed what. The data arrives long — one row per project per week — and needs to become wide. Excel's pivot tables cannot place text in the values area at all; pandas can, with pivot when each cell has exactly one value, and with pivot_table and a text-friendly aggregation when it might have several. This page covers both, and the error that tells you which case you are in. It belongs to Creating Pivot Tables from Excel Data.

Prerequisites

pip install pandas openpyxl xlsxwriter

status_updates.xlsx is a log of weekly status updates for three projects. The warehouse move started in week 38, so it has no week-37 entry, and the website relaunch was updated twice in week 39 — first "At risk", then "On track" after a fix — which is exactly the situation that breaks a naive pivot.

Step 1 — pivot works when each cell has one value

import pandas as pd

log = pd.read_excel("status_updates.xlsx")
first_two_weeks = log[log["Week"] != "W39"]
board = first_two_weeks.pivot(index="Project", columns="Week", values="Status")
print(board)

pivot reshapes without aggregating: each project/week combination becomes one cell holding the status text. The warehouse move has NaN for week 37, because there was no update — a gap that should be visible on a status board. pivot works for any data type because it never has to combine values.

Step 2 — The duplicate entries error

Add week 39, with its two updates for the website relaunch, and pivot refuses:

try:
    log.pivot(index="Project", columns="Week", values="Status")
except ValueError as exc:
    print("ValueError:", exc)
dupes = log[log.duplicated(["Project", "Week"], keep=False)]
print(dupes.to_string(index=False))

ValueError: Index contains duplicate entries, cannot reshape means at least one project/week cell would need to hold two values. The error is useful: it forces a decision about which value the cell should show. Listing the duplicated combinations, as the second part does, shows exactly where the conflict is — one cell, with two updates two days apart.

Step 3 — pivot_table with a text aggregation

pivot_table aggregates, and for text the useful aggregations are "first", "last" and joining:

last_status = log.pivot_table(index="Project", columns="Week", values="Status", aggfunc="last")
all_statuses = log.pivot_table(index="Project", columns="Week", values="Status",
                               aggfunc=lambda s: " → ".join(s))
print(last_status)
print(all_statuses)

aggfunc="last" keeps the last value in row order, which here happens to be the later update. Joining with an arrow shows the whole history in the cell — "At risk → On track" — which is often exactly what a status meeting wants to see. Note that pivot_table drops all-NaN columns and rows by default (dropna=True); pass dropna=False to keep a week in which nothing was reported. Counting non-numeric values per cell is also possible with aggfunc="count", which turns the grid into a "how many updates" table.

Step 4 — The latest value by timestamp, not by row order

"First" and "last" depend on the order rows happen to be in, which is not guaranteed — an export sorted by project name, or a merge, can reorder them. When there is a timestamp, use it: sort, keep the latest row per cell, then pivot:

latest = (log.sort_values("Updated")
             .drop_duplicates(["Project", "Week"], keep="last"))
board = latest.pivot(index="Project", columns="Week", values="Status")
print(board)
shuffled = log.sample(frac=1, random_state=1)
board_again = (shuffled.sort_values("Updated").drop_duplicates(["Project", "Week"], keep="last")
                       .pivot(index="Project", columns="Week", values="Status"))
print("same result after shuffling:", board.equals(board_again))

Sorting by Updated and keeping the last row per project/week leaves exactly one value per cell, so plain pivot works and the website relaunch shows "On track" for week 39. Shuffling the input and repeating gives the identical board, which aggfunc="last" on unsorted data would not guarantee. The keep-latest pattern is covered in general in Keep the Latest Record per Key with pandas.

Step 5 — Write the board with colours by value

A status board is read by colour. Write it with one conditional format per status:

order = ["Website relaunch", "ERP upgrade", "Warehouse move"]
board = board.reindex(order).fillna("—")
colours = {"On track": ("#D9F4F1", "#0B6157"), "At risk": ("#FDEFD8", "#7A4E06"), "Late": ("#FCE9E9", "#9C0006")}
with pd.ExcelWriter("status_board.xlsx", engine="xlsxwriter") as xw:
    board.to_excel(xw, sheet_name="Board")
    book, ws = xw.book, xw.sheets["Board"]
    for status, (bg, fg) in colours.items():
        fmt = book.add_format({"bg_color": bg, "font_color": fg, "bold": True})
        ws.conditional_format(1, 1, len(board), len(board.columns),
                              {"type": "cell", "criteria": "==", "value": f'"{status}"', "format": fmt})
    ws.set_column(0, 0, 18)
    ws.set_column(1, len(board.columns), 11)
print(board)

Each status gets a fill and text colour; "—" marks weeks with no update. The criteria value must include the quotation marks ('"On track"') because it is compared as a formula expression, and a bare word would be read as a name. Projects are reindexed into a business order rather than alphabetical, as described in Sort Pivot Rows in a Custom Order with pandas.

Step 6 — Summarise the board per project

A status board raises the next question at once: how many weeks has each project spent in each state, and is it getting better? Both come from the same grid:

weeks = board.replace("—", pd.NA)
summary = weeks.apply(lambda row: row.value_counts(), axis=1).fillna(0).astype(int)
rank = {"Late": 0, "At risk": 1, "On track": 2}
latest_two = weeks.apply(lambda row: row.dropna().tail(2).map(rank).tolist(), axis=1)
summary["Trend"] = latest_two.map(lambda r: "improving" if len(r) == 2 and r[1] > r[0]
                                  else "worsening" if len(r) == 2 and r[1] < r[0] else "steady")
print(summary)

Counting values along each row gives weeks per status; comparing the last two reported statuses on a simple scale gives a trend. The website relaunch improved from At risk to On track, the ERP upgrade from Late to At risk, and the warehouse move is steady. A trend column next to the board answers the first question in most status meetings before anyone asks it.

Step 7 — When Excel's own pivot table is not enough

People often try this in Excel first and discover that a pivot table's values area only summarises — it shows counts of text, never the text. The workarounds in Excel (Power Pivot measures with CONCATENATEX, or a grid of XLOOKUP formulas against the log) work but are fragile when weeks are added. Generating the board in pandas each week and writing it as values, as above, is usually simpler; when the workbook must stay live, write one lookup formula per cell instead, as in Write Lookup Formulas (XLOOKUP, VLOOKUP, INDEX/MATCH) with openpyxl.

Performance notes

Pivoting text costs the same as pivoting numbers; the slow part is a Python lambda used as aggfunc, which runs once per cell. For a board of a few hundred projects by fifty weeks that is instant. For millions of cells, prefer the sort-and-drop-duplicates approach, which uses only vectorised operations, and join text with a group-by on the long data (groupby([...])["Status"].agg(" → ".join)) before a final unstack, which is considerably faster than a lambda inside pivot_table.

Common pitfalls

• Using pivot on duplicated keys. It raises "Index contains duplicate entries"; decide which value wins.
• Relying on row order. first and last follow the current order; sort by a timestamp first.
• Numeric aggregations on text. aggfunc="sum" concatenates strings without separators or fails; use a join.
• Disappearing empty weeks. pivot_table drops all-empty columns unless dropna=False.
• Unquoted criteria in conditional formats. Text values must be wrapped in quotes inside the criteria.

Conclusion

Pivot text with pivot when every row/column combination has one value, and treat its "Index contains duplicate entries" error as a prompt to decide which value wins. Use pivot_table with "first", "last" or a join to combine several values, but prefer sorting by a timestamp and keeping the latest row, which does not depend on row order. Keep empty cells visible, put rows in a business order, and colour the cells by value when writing the board to Excel.

Frequently asked questions

How do I pivot text values in pandas? Use df.pivot(index=..., columns=..., values="Status") when each combination appears once. If combinations repeat, use pivot_table with a text aggregation such as aggfunc="last" or aggfunc=", ".join.

What does "Index contains duplicate entries, cannot reshape" mean? At least one combination of the index and columns appears in more than one row, so pivot cannot put a single value in that cell. Remove the duplicates, for example by keeping the latest row, or use pivot_table with an aggregation.

How do I show the latest status in each cell of a pivot? Sort the data by its timestamp, keep the last row per combination with drop_duplicates(keep="last"), then pivot. This is independent of the original row order.

Can an Excel pivot table show text in the values area? No; Excel's pivot table summarises values, so text appears only as a count. Build the grid in pandas and write it as values, or use lookup formulas against the source data.

Related

• Up one level: Creating Pivot Tables from Excel Data — reshaping and summarising.
• Create a Pivot Table from Excel with pandas — numeric pivots.
• Unpivot a Wide Excel Sheet with pandas melt — the reverse reshape.
• TEXTJOIN Equivalent in pandas — joining text values in groups.