Definition
Customer complaints arrive as free text in Hindi, English and Hinglish, and the order dates arrive in three different formats. Before any model can use them, they need careful preparation.
Preparing text means cleaning and standardising strings, trimming spaces, fixing case, removing or replacing unwanted characters and extracting useful parts, often with pandas' .str methods and regular expressions. Preparing dates means converting strings to datetime values with pd.to_datetime, then extracting features such as year, month, day of week and the time between events with the .dt accessor.
In simple words
Text and dates are like ingredients that come unwashed and unpeeled. You wash the text, removing extra spaces and odd symbols, and peel the dates into parts such as month and weekday, so the model can actually use them.
Overview
- Text and dates usually arrive as messy strings that must be cleaned and converted.
- pandas' .str accessor applies string methods to a whole column at once.
- pd.to_datetime converts text to dates; the .dt accessor extracts parts such as year, month and weekday.
- Indian data often uses day-first dates (15/08/2024), so dayfirst=True or an explicit format is needed.
- Date arithmetic gives durations: (end − start).dt.days.
- Clean text and date features are the raw material for NLP and time-series models.
Why it matters
Many valuable signals hide in text and time: the words in a review, the city in an address, whether an order came on a weekend or during a festival season. Preparing them well often improves a model more than changing the algorithm.
Key terms
- .str accessor
- pandas methods that apply string operations to a whole column, such as s.str.lower().
- Regular expression
- A pattern language for finding and replacing text, such as \d+ for one or more digits.
- Normalisation
- Making equivalent text identical: same case, spacing and spelling.
- datetime64
- pandas' date-and-time type, which supports arithmetic and the .dt accessor.
- Timedelta
- A duration, such as the difference between two dates.
- Time zone
- The local offset from UTC; India uses IST, UTC+5:30.
Types
Preparing text and dates involves several types of transformation. Each produces cleaner columns or new features.
1. Text normalisation
Making text consistent: case, spaces and punctuation.
- How it works: .str.lower(), .str.strip(), .str.replace(r"\s+", " ", regex=True) and removing punctuation give one standard form.
- Example: " New Delhi " and "new delhi" both become "new delhi".
- When to use it: Use it before grouping, matching or counting words. Keep a copy of the original column, so that cleaning mistakes can be spotted and undone.
2. Text extraction and splitting
Pulling parts out of text with split or regular expressions.
- How it works: .str.split(",", expand=True) makes columns; .str.extract(r"(\d{6})") pulls a six-digit PIN code; .str.len() counts characters.
- Example: Splitting "Pune, Maharashtra" into city and state columns.
- When to use it: Use it to turn free-text fields into structured columns. Check a sample of results by eye, because real text always contains a few rows that break the pattern.
3. Date parsing
Converting text to proper datetime values.
- How it works: pd.to_datetime(df["date"], format="%d/%m/%Y") is fast and unambiguous; errors="coerce" turns bad dates into NaT.
- Example: "05/08/2024" read as 5 August, not 8 May.
- When to use it: Use it on every date column; never sort or compare dates stored as text. Then count the NaT values, which show how many dates could not be read and need checking.
4. Date-part features
New columns extracted from a date: year, month, day, weekday, hour, quarter and is_weekend.
- How it works: df["date"].dt.month, .dt.dayofweek and .dt.hour pull out parts; comparing with a holiday list adds is_holiday.
- Example: Delivery orders showing a Sunday-evening peak once weekday and hour are extracted.
- When to use it: Use them to reveal daily, weekly and seasonal patterns for models.
5. Durations and time-based resampling
Differences between dates, and summaries over regular time periods.
- How it works: Subtracting dates gives timedeltas (.dt.days); with a datetime index, resample("W").sum() gives weekly totals and rolling(7).mean() a 7-day average.
- Example: Days since a customer's last purchase; weekly dengue cases from daily reports.
- When to use it: Use them for recency features and for turning event logs into time series.
Explained in detail
Cleaning text columns
| Task | pandas |
|---|---|
| Trim and collapse spaces | s.str.strip().str.replace(r"\s+", " ", regex=True) |
| Standardise case | s.str.lower() or s.str.title() |
| Map variants to one value | s.replace({"bombay": "mumbai", "bengaluru": "bangalore"}) |
| Extract a pattern | s.str.extract(r"(\d{6})") for a six-digit pin code |
| Flag a keyword | s.str.contains("refund", case=False) |
| Split into parts | s.str.split(",", expand=True) |
| Text length as a feature | s.str.len() |
Indian data adds particular challenges. The same city may appear as Bombay or Mumbai, Bengaluru or Bangalore. Names may be written in Devanagari in one row and in Roman script in another. Hinglish mixes both languages in one sentence, with variable spellings such as "bahut" and "bohot". Unicode normalisation, unicodedata.normalize("NFC", text), makes visually identical characters identical in memory. For machine learning on text, these cleaned columns become the input to tokenisers and vectorisers covered in the NLP module.
Working with dates
Convert with pd.to_datetime(s, dayfirst=True) for dates such as 15/08/2024, or pass format="%d-%m-%Y" when the format is known, which is faster and avoids ambiguity: 03/04/2024 is 3 April in India but March 4 in the United States. Once converted, the .dt accessor exposes year, month, day, dayofweek, hour and more. Subtracting dates gives a Timedelta, so (df["delivered"] - df["ordered"]).dt.days gives delivery time in days. For time series, set the date as the index and use resample("W").sum() to total by week, or rolling(7).mean() for a seven-day moving average.
Useful date features
- Day of week and weekend flag: shopping, traffic and hospital visits differ on weekends.
- Month and season: monsoon months, exam months, festival periods such as Diwali.
- Time since an event: days since last purchase, customer tenure in months.
- Hour of day, converted to local time: store timestamps in UTC and convert with tz_convert("Asia/Kolkata") for display.
- Never use future information: a feature such as "total purchases this year" computed at year-end leaks into a prediction made in March.
Text and dates also carry privacy risks. Free-text complaints may contain phone numbers, account details or health information, and exact birth dates combined with pin codes can identify individuals. Before analysing or sharing such columns, remove or mask personal details, replace exact dates with ages or months where that is enough for the task, and keep the raw text in a secure place. Good preparation makes data both more useful and safer to work with.
How it works
- Trim, collapse spaces and standardise case in text columns.
- Map known variants to one spelling and normalise Unicode.
- Extract structured parts with regular expressions.
- Convert dates with an explicit format or dayfirst=True.
- Create date features and durations, using only information available at prediction time.
In code
import pandas as pd
df = pd.DataFrame({
"city": [" Mumbai", "bombay ", "Bengaluru", "bangalore", "Pune"],
"address": ["Flat 2, Andheri 400053", "Dadar 400014", "Indiranagar 560038", "HSR 560102", "Kothrud 411038"],
"ordered": ["03/04/2024", "15/08/2024", "01/11/2024", "25/12/2024", "31/12/2024"],
"delivered": ["06/04/2024", "17/08/2024", "05/11/2024", "27/12/2024", "02/01/2025"],
})
df["city"] = df["city"].str.strip().str.lower().replace({"bombay": "mumbai", "bengaluru": "bangalore"})
df["pincode"] = df["address"].str.extract(r"(\d{6})")
for col in ["ordered", "delivered"]:
df[col] = pd.to_datetime(df[col], format="%d/%m/%Y")
df["weekday"] = df["ordered"].dt.day_name()
df["is_weekend"] = df["ordered"].dt.dayofweek >= 5
df["delivery_days"] = (df["delivered"] - df["ordered"]).dt.days
print(df[["city", "pincode", "ordered", "weekday", "is_weekend", "delivery_days"]])
print(df["city"].value_counts().to_dict())Output
city pincode ordered weekday is_weekend delivery_days
0 mumbai 400053 2024-04-03 Wednesday False 3
1 mumbai 400014 2024-08-15 Thursday False 2
2 bangalore 560038 2024-11-01 Friday False 4
3 bangalore 560102 2024-12-25 Wednesday False 2
4 pune 411038 2024-12-31 Tuesday False 2
{'mumbai': 2, 'bangalore': 2, 'pune': 1}Try it yourself
The festival-week delays
A delivery company wants to know when orders are slowest.
Match the task to the tool
Which pandas tool does it?
- Remove spaces at both ends
- Pull a six-digit pin code from an address
- Get the day of the week
- Total sales by week
Common mistakes
- Parsing dates without stating the day-first format.
- Leaving spelling variants that split one category into several.
- Building date features that use information from the future.
Check yourself
Summary
- Clean text with .str methods and regular expressions; normalise case, spacing, spelling and Unicode.
- Convert dates with an explicit format, then extract features with .dt and compute durations.
- Watch day-first formats, time zones and future information.
Finished reading?
Sources: pandas: Working with text data · pandas: Time series / date functionality