An export shows one order per row, with values such as Red | Blue in a single tags column. To count how often each tag occurs, give each tag its own row and carry over the order ID.
In pandas, split the text with str.split(), then turn the resulting lists into rows with explode(). Trim spaces after splitting so both orders with the Blue tag use the same spelling.

Start with one row per order
| Order ID | Raw tags |
|---|---|
| A01 | Red | Blue |
| A02 | Blue||Green |
| A03 | Missing value |
| A04 | Green|Green |
Order A02 contains an empty field between two separators. Order A03 has no source value at all. Order A04 repeats a tag. Those cases need different decisions before you count anything.
Split the values and expand the lists
import pandas as pd
orders = pd.DataFrame({
"order_id": ["A01", "A02", "A03", "A04"],
"tags": ["Red | Blue", "Blue||Green", pd.NA, "Green|Green"],
})
missing_orders = orders.loc[orders["tags"].isna(), "order_id"].tolist()
expanded = (
orders.assign(tag=orders["tags"].str.split("|", regex=False))
.drop(columns="tags")
.explode("tag", ignore_index=True)
)
expanded["tag"] = expanded["tag"].astype("string").str.strip()
tag_rows = expanded.loc[
expanded["tag"].notna() & expanded["tag"].ne("")
].reset_index(drop=True)
print(tag_rows.to_string(index=False))
print("Orders with missing source tags:", ", ".join(missing_orders))
order_id tag
A01 Red
A01 Blue
A02 Blue
A02 Green
A04 Green
A04 Green
Orders with missing source tags: A03
With regex=False, the pipe is a literal character. Each item in a split list becomes one output row, and pandas repeats the order ID for each item. ignore_index=True gives the result a fresh row index. The last filter removes whitespace-only tags and missing values from the tag table. The original order table still includes A03, and missing_orders records it for follow-up.
Decide whether repeated tags should count twice
The result has six tag occurrences. If your report asks how many orders carry each tag, first use tag_rows.drop_duplicates(["order_id", "tag"]). That leaves five distinct order–tag pairs. Green occurs three times, across two orders.
If a tag can contain the separator itself, use a format that keeps each tag separate, such as an actual list from a JSON export. An unescaped pipe inside a tag is indistinguishable from a separator in this example. For data spread across dedicated columns, see how to reshape wide data into long rows with pandas.
The example was run with Python 3.11.3 and pandas 2.3.3.