Let's be real... every data professional eventually meets that one dataset that looks like a simple task until you actually start cleaning it. It’s always the same moment. You scroll a little deeper and suddenly you’re looking at four different ways of formatting a phone number, punctuation that seems to move around on its own, and strings that look identical until you realize they aren’t identical at all. It’s the kind of dataset that makes you squint at the screen and think, “Someone definitely typed this at 2 a.m.” I’ve been there many times, and each time I’m reminded how unpredictable real-world data can be. But the moment you bring regex into the picture, things shift. It feels like you suddenly have a pair of glasses that lets you see the hidden structure beneath the chaos. You stop feeling overwhelmed by messy text and start seeing it as a puzzle you finally have the right tools to solve.
What you will learn: In this edition, we’re exploring how advanced regex can help you make sense of unpredictable text fields that show up in real projects. By the time you get through it, you’ll have a clearer way of spotting patterns that other people miss, expressing those patterns in a structured way, and shaping unstructured data into something that finally behaves. You’ll also get a feel for how this kind of thinking changes the way you approach cleaning work overall, because once regex clicks, you start seeing text differently.
Read Time: 8 minutes
Source: Sahir Maharaj (https://sahirmaharaj.com)
Regex has this reputation of looking complicated, but once you look closer, it’s really just a pattern language. It’s a way of describing relationships in text. The trouble is that most people only see the symbols and never get to the part where it actually starts making sense. But once you see that regex is simply a structured way to communicate what you already know about your data, things become much easier. You stop seeing it as a technical barrier and start seeing it as a creative tool. One thing I’ve noticed over the years is that regex teaches you to notice the small things. You start catching tiny inconsistencies that usually slip past your eyes. A space that looks harmless but breaks grouping logic.
A special character that shows up once but causes downstream issues. The moment regex exposes these things, it changes how you work. You’re no longer troubleshooting blind. You have clarity. And that clarity is powerful. It shifts you from reacting to problems to actually understanding them. When you’ve cleaned enough text fields, you realize that most “random” errors aren’t random at all. They come from habits, systems, timing, human shortcuts, or copy-paste quirks. Regex helps you uncover those patterns. You begin thinking less about fixing individual cases and more about fixing the underlying cause. I’ve seen this happen often when mentoring junior data professionals.
Source: Sahir Maharaj (https://sahirmaharaj.com)
Once someone understands that regex is not about memorizing 20 symbols, but about observing how text behaves, they suddenly relax. Their confidence grows. They begin approaching messy text with curiosity rather than dread. That’s the moment regex shifts from something they avoid into something they rely on. When this foundation clicks, cleaning text becomes far more manageable. You start feeling less like you’re at war with your dataset and more like you’re in control. That is a good place to transition into how this thinking applies when you’re handling real-world data, because that is where regex truly starts proving its value.
import pandas as pd
import numpy as np
import re
import matplotlib.pyplot as plt
np.random.seed(42)
names = [
"John Doe", "john doe", "JOHN DOE", "John-Doe",
"Jane Smith", "jane_smith", "JANE smith",
"Ali Khan", "ali-khan", "ALI KHAN",
"Sahir Maharaj", "sahir maharaj", "sahir_maharaj"
]
noise = ["!", "@", "#", "$", "%", "&", "*", "??", " ", "-", "_"]
rows = []
for n in names:
for _ in range(10):
noisy = n
for _ in range(np.random.randint(1,4)):
noisy = noisy + np.random.choice(noise)
rows.append(noisy)
df = pd.DataFrame({"raw_text": rows})
df["clean_step_1"] = df["raw_text"].str.lower()
df["clean_step_2"] = df["clean_step_1"].apply(lambda x: re.sub(r"[^a-z\s]", "", x))
df["clean_step_3"] = df["clean_step_2"].apply(lambda x: re.sub(r"\s+", " ", x).strip())
df["token_count"] = df["clean_step_3"].apply(lambda x: len(x.split(" ")))
df["char_count"] = df["clean_step_3"].apply(len)
plt.figure(figsize=(10,5))
plt.hist(df["char_count"], bins=20)
plt.title("Distribution of Cleaned Name Lengths")
plt.xlabel("Length")
plt.ylabel("Frequency")
plt.tight_layout()
plt.show()
plt.figure(figsize=(10,5))
plt.boxplot(df["token_count"])
plt.title("Token Count Spread After Regex Cleaning")
plt.ylabel("Token Count")
plt.tight_layout()
plt.show()
df.sample(15)
Real-world data is naturally chaotic. No matter how much structure an organization intends to enforce, text-based fields always find a way to become messy. You get entries typed by different people, different systems, and different rules, and somehow all of them land on your desk. Once you’ve worked with enough datasets, you start recognizing that every messy column has a personality of its own. And regex helps you understand that personality instead of fighting it. When you look closely, you’ll notice that messy text is rarely random. It follows patterns. Some formats appear more often than others. Some characters appear consistently in certain positions. Parts of a string may shift around, but not completely.
When I work on client projects, I often spot these patterns long before I write any code. Regex simply gives me the structure to formalize what I’ve already observed. This is where you begin to see the efficiency of regex compared to manual cleaning. Without regex, you end up writing endless if-statements, manual replacements, and case-by-case patches. That works for a while but never holds up at scale. Regex allows you to generalize. Instead of solving one data error five hundred times, you solve it once in a way that handles every reasonable variation. It’s cleaner, faster, and far more durable. Over time, this mindset becomes second nature. You start predicting what might be wrong based on experience.
Source: Sahir Maharaj (https://sahirmaharaj.com)
Like, if a field was typed manually, there are probably extra spaces. If it came from a web form, there might be invisible characters. If it came from an older system, the formatting probably changed mid-year. You start reading the data like a story that tells you how it was created. And regex is how you translate that story into structure. By the time you get comfortable with this, messy datasets stop feeling overwhelming. They become interesting challenges you know how to decode. That sets the stage for the next part of the journey, which is learning how advanced regex techniques give you even more control and precision.
import pandas as pd
import numpy as np
import re
import matplotlib.pyplot as plt
np.random.seed(123)
product_base = ["A100", "B2500", "C-900", "X50", "D-777", "Z4500"]
def inject_variation(code):
variations = [
lambda x: x.lower(),
lambda x: x.replace("-", ""),
lambda x: x + np.random.choice(["*", "!", "@", "#", "%"]),
lambda x: x[:1].lower() + x[1:],
lambda x: x.replace("0", "O")
]
for _ in range(np.random.randint(1,4)):
code = np.random.choice(variations)(code)
return code
raw_codes = []
for c in product_base:
for _ in range(25):
raw_codes.append(inject_variation(c))
df = pd.DataFrame({"raw_code": raw_codes})
pattern = r"[a-zA-Z]-?\d{2,5}"
df["matched"] = df["raw_code"].apply(lambda x: re.findall(pattern, x))
df["cleaned_numeric_part"] = df["matched"].apply(lambda m: int(re.sub(r"[^0-9]", "", m[0])) if len(m) > 0 else np.nan)
freqs = df["cleaned_numeric_part"].value_counts()
plt.figure(figsize=(10,5))
plt.bar(freqs.index.astype(str), freqs.values)
plt.title("Frequency of Extracted Numeric Product Codes")
plt.xlabel("Product Numeric Part")
plt.ylabel("Frequency")
plt.tight_layout()
plt.show()
plt.figure(figsize=(10,5))
plt.scatter(df.index, df["cleaned_numeric_part"])
plt.title("Scatter Plot of Extracted Numeric Codes Over Index")
plt.xlabel("Row Index")
plt.ylabel("Numeric Code")
plt.tight_layout()
plt.show()
df.head(20)
This is the point where regex begins moving from useful to powerful. When you start exploring things like lookaheads, lookbehinds, boundaries, and grouping logic, you unlock a new level of precision. These concepts aren’t complicated once you break them down. They are simply ways to add context to your pattern. Instead of just matching characters, you get to describe rules and conditions that must also be true. In real projects, these ideas show their value quickly. I’ve had situations where I needed to match something only if it appeared before a certain keyword, or extract part of a string while ignoring another part that always appeared nearby. In moments like that, advanced regex becomes the cleanest and most efficient solution. It lets you target exactly what you need without breaking the surrounding structure.
Source: Sahir Maharaj (https://sahirmaharaj.com)
The great part is that the more you work with advanced regex, the more intuitive it becomes. You stop thinking about it as a complicated skill. You think about it the same way you think about cleaning any dataset. You’re looking for logic. You’re describing relationships. You’re making decisions about what should match and what shouldn’t. And the syntax becomes a natural extension of thought rather than a hurdle. This also helps you become better at explaining data problems to others. Advanced regex forces you to articulate structure clearly, and that skill transfers everywhere.
import pandas as pd
import numpy as np
import re
import matplotlib.pyplot as plt
np.random.seed(7)
logs = []
users = ["alice", "john", "sahir", "karen", "tumi", "mika"]
actions = ["login", "logout", "purchase", "fail", "timeout"]
devices = ["mobile", "desktop", "tablet"]
for _ in range(300):
u = np.random.choice(users)
a = np.random.choice(actions)
d = np.random.choice(devices)
amt = np.random.randint(50, 500)
entry = f"user={u}; action={a}; device={d}; amount={amt}"
if np.random.rand() < 0.25:
entry = entry.replace("=", " = ").replace(";", " ; ")
logs.append(entry)
df = pd.DataFrame({"log_entry": logs})
pattern_user = r"(?<=user=)[a-zA-Z]+"
pattern_action = r"(?<=action=)[a-zA-Z]+"
pattern_amount = r"(?<=amount=)\d+"
df["user"] = df["log_entry"].apply(lambda x: re.findall(pattern_user, x)[0] if re.findall(pattern_user, x) else None)
df["action"] = df["log_entry"].apply(lambda x: re.findall(pattern_action, x)[0] if re.findall(pattern_action, x) else None)
df["amount"] = df["log_entry"].apply(lambda x: int(re.findall(pattern_amount, x)[0]) if re.findall(pattern_amount, x) else None)
action_counts = df["action"].value_counts()
plt.figure(figsize=(10,5))
plt.bar(action_counts.index, action_counts.values)
plt.title("Action Frequency in Log Data")
plt.xlabel("Action")
plt.ylabel("Count")
plt.tight_layout()
plt.show()
plt.figure(figsize=(10,5))
for user in users:
subset = df[df["user"] == user]["amount"].reset_index(drop=True)
plt.plot(subset, label=user)
plt.title("Amount Trends Per User")
plt.xlabel("Occurrence Index")
plt.ylabel("Amount")
plt.legend()
plt.tight_layout()
plt.show()
df.sample(20)
Whether you’re documenting a process, teaching a junior analyst, or reviewing someone’s work, you become much better at describing patterns objectively. It’s one of those quiet skills that improves your overall technical communication. By the time you understand advanced regex, you’re not just matching patterns anymore. You’re understanding text at a deeper level. You’re shaping it with intention. And messy datasets become far less stressful because you know you have the tools to bring structure wherever it’s missing. It’s a comfortable, confident place to be, and a good point to wrap up the journey.
Source: Sahir Maharaj (https://sahirmaharaj.com)
Now, if you are wondering where to begin... start small. Pick one messy column and play with a few simple patterns. Try cleaning spacing. Try extracting something predictable. Try identifying a pattern you normally would not catch by eye. These little experiments build confidence faster than you expect, and before long, you will start recognizing repeating structures everywhere. And once that happens, regex becomes less of a tool and more of an instinct. Though, I think what makes all of this even more exciting is how naturally regex fits into existing Fabric workflows. The moment you try it inside Fabric, even for something small, you start realizing how much time you have been spending manually cleaning text or writing one-off transformations. So let this be your nudge to give it a try. Start with one column, one expression, one small win. After that, the improvements stack quickly, and you’ll wonder how you ever worked without it!
Thanks for taking the time to read my post! I’d love to hear what you think and connect with you 🙂
Super User