I2 · Working with Data: NumPy, pandas, SQL and Visualisation

Preparing Text and Dates

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.

8 min Intermediate · Sublevel Beginner Jump to the activity

What you need: The earlier topics of this module.

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

Taskpandas
Trim and collapse spacess.str.strip().str.replace(r"\s+", " ", regex=True)
Standardise cases.str.lower() or s.str.title()
Map variants to one values.replace({"bombay": "mumbai", "bengaluru": "bangalore"})
Extract a patterns.str.extract(r"(\d{6})") for a six-digit pin code
Flag a keywords.str.contains("refund", case=False)
Split into partss.str.split(",", expand=True)
Text length as a features.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

  1. Trim, collapse spaces and standardise case in text columns.
  2. Map known variants to one spelling and normalise Unicode.
  3. Extract structured parts with regular expressions.
  4. Convert dates with an explicit format or dayfirst=True.
  5. Create date features and durations, using only information available at prediction time.

In code

text_dates.py
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

Question 1

1. What does df["d"].dt.dayofweek return?

Question 2

2. How do you get delivery time in days?

Question 3

3. Why normalise "Bombay" and "Mumbai" to one value?

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