Skip to content

Datetime and Text Features

Datetime and text columns are among the richest sources of signal in real-world datasets — and among the most commonly left on the table. A raw timestamp fed into a model is nearly useless. The same timestamp decomposed into hour of day, day of week, and "is this a payday Friday?" can be the difference between a mediocre and a strong model. Text columns, treated as raw strings, carry nothing. Treated as TF-IDF vectors, they carry the full vocabulary of your customers' complaints, reviews, and descriptions.

Learning Objectives

By the end of this note you will be able to:

  • Extract useful components from datetime columns (year, month, day, hour, day_of_week, is_weekend)
  • Compute duration and recency features from reference dates
  • Apply cyclical encoding (sin/cos) for time-of-day and day-of-week features
  • Extract statistical text features (word count, character count, average word length)
  • Build a TF-IDF feature matrix using CountVectorizer and TfidfVectorizer

Datetime Features

Why Timestamps Are Not Numeric Features

A Unix timestamp (e.g., 1716192000) is technically a number, but feeding it directly into a model gives the model no way to learn that "3 AM orders behave differently from 3 PM orders" or that "Friday evenings have higher conversion rates." The timestamp increases monotonically — there is no way to extract cyclical patterns from it without decomposing it first.

Extracting Calendar Components

import pandas as pd
import numpy as np

# Realistic e-commerce order log
np.random.seed(42)
date_range = pd.date_range(start="2024-01-01", end="2025-12-31", freq="h")
orders = pd.DataFrame({
    "order_id": range(len(date_range)),
    "order_timestamp": date_range,
    "order_value": np.random.exponential(scale=1500, size=len(date_range))
})

# Convert to datetime if it is not already (always do this explicitly)
orders["order_timestamp"] = pd.to_datetime(orders["order_timestamp"])

# Extract calendar components
orders["order_year"]        = orders["order_timestamp"].dt.year
orders["order_month"]       = orders["order_timestamp"].dt.month          # 1–12
orders["order_day"]         = orders["order_timestamp"].dt.day            # 1–31
orders["order_hour"]        = orders["order_timestamp"].dt.hour           # 0–23
orders["order_day_of_week"] = orders["order_timestamp"].dt.dayofweek      # 0=Monday, 6=Sunday
orders["order_day_name"]    = orders["order_timestamp"].dt.day_name()
orders["order_month_name"]  = orders["order_timestamp"].dt.month_name()
orders["is_weekend"]        = orders["order_day_of_week"].isin([5, 6]).astype(int)
orders["is_month_start"]    = orders["order_timestamp"].dt.is_month_start.astype(int)
orders["is_month_end"]      = orders["order_timestamp"].dt.is_month_end.astype(int)
orders["quarter"]           = orders["order_timestamp"].dt.quarter

# Show a sample
print(orders[["order_timestamp", "order_month", "order_hour",
              "order_day_of_week", "is_weekend"]].head(6))
# Output:
#         order_timestamp  order_month  order_hour  order_day_of_week  is_weekend
# 0   2024-01-01 00:00:00            1           0                  0           0
# 1   2024-01-01 01:00:00            1           1                  0           0
# 2   2024-01-01 02:00:00            1           2                  0           0
# 3   2024-01-01 03:00:00            1           3                  0           0
# 4   2024-01-01 04:00:00            1           4                  0           0
# 5   2024-01-01 05:00:00            1           5                  0           0

Indian Holidays and Festival Flags

For Indian e-commerce or fintech datasets, features like is_diwali_week, is_dussehra, or is_republic_day can be powerful signals. Build a list of key dates and create binary flags. This is domain knowledge that no automated tool will discover for you.

Recency and Duration Features

The raw date is rarely what matters. What matters is how much time has elapsed — customer tenure, days since last purchase, days until contract renewal.

import pandas as pd
import numpy as np

np.random.seed(10)
n = 500
customers = pd.DataFrame({
    "customer_id": range(n),
    "signup_date": pd.to_datetime("2022-01-01") + pd.to_timedelta(
        np.random.randint(0, 900, size=n), unit="D"
    ),
    "last_purchase_date": pd.to_datetime("2023-01-01") + pd.to_timedelta(
        np.random.randint(0, 730, size=n), unit="D"
    ),
    "contract_end_date": pd.to_datetime("2025-01-01") + pd.to_timedelta(
        np.random.randint(-180, 365, size=n), unit="D"
    )
})

# Reference point: the date the dataset was extracted (or model deployment date)
# IMPORTANT: this must match the reference date used in production
reference_date = pd.Timestamp("2025-01-01")

customers["customer_tenure_days"] = (
    reference_date - customers["signup_date"]
).dt.days

customers["days_since_last_purchase"] = (
    reference_date - customers["last_purchase_date"]
).dt.days

customers["days_until_contract_end"] = (
    customers["contract_end_date"] - reference_date
).dt.days  # negative = already expired

# Duration between signup and first purchase (if both are available)
customers["days_to_first_purchase"] = (
    customers["last_purchase_date"] - customers["signup_date"]
).dt.days

print(customers[["customer_tenure_days", "days_since_last_purchase",
                  "days_until_contract_end"]].describe().round(1))

Fix the Reference Date Before Training

If you compute days_since_last_purchase relative to today() (i.e., pd.Timestamp.now()), the feature value will be different every time you run the code. Your trained model encodes the expectation of a fixed reference date. Fix it to the date your training dataset was extracted and hard-code that date in your production pipeline.

Cyclical Encoding — Time of Day and Day of Week

Hour of day and day of week are cyclical: hour 23 is one hour away from hour 0, and Saturday is one day away from Sunday. If you feed these as raw integers (0–23, 0–6), a linear model sees hour 23 and hour 0 as far apart. Cyclical encoding uses sine and cosine to make the circular distance correct.

import numpy as np
import pandas as pd

hours = pd.DataFrame({"hour": range(24)})

# Encode hour as (sin, cos) pair — captures cyclical proximity
hours["hour_sin"] = np.sin(2 * np.pi * hours["hour"] / 24)
hours["hour_cos"] = np.cos(2 * np.pi * hours["hour"] / 24)

print(hours[["hour", "hour_sin", "hour_cos"]].iloc[[0, 6, 12, 18, 23]].round(3))
# Output:
#     hour  hour_sin  hour_cos
# 0      0     0.000     1.000
# 6      6     1.000     0.000
# 12    12     0.000    -1.000
# 18    18    -1.000     0.000
# 23    23    -0.259     0.966  ← close to hour 0 in (sin, cos) space

# Same for day of week (0=Monday through 6=Sunday)
days = pd.DataFrame({"day_of_week": range(7)})
days["dow_sin"] = np.sin(2 * np.pi * days["day_of_week"] / 7)
days["dow_cos"] = np.cos(2 * np.pi * days["day_of_week"] / 7)

print(days[["day_of_week", "dow_sin", "dow_cos"]].round(3))
# Output:
#    day_of_week  dow_sin  dow_cos
# 0            0    0.000    1.000
# 1            1    0.782    0.623
# 2            2    0.975   -0.223
# 3            3    0.434   -0.901
# 4            4   -0.434   -0.901
# 5            5   -0.975   -0.223
# 6            6   -0.782    0.623  ← Sunday, close to Monday (0) in (sin,cos) space

When Cyclical Encoding Matters

Cyclical encoding is most valuable for linear models and neural networks, where the model learns linear combinations of input features and cannot discover circular patterns on its own. Tree-based models (XGBoost, LightGBM) can theoretically discover cyclical relationships through multiple splits, but cyclical encoding still tends to help by making the pattern more accessible. Always add both the _sin and _cos columns — one alone does not capture the full cycle.

Text Features

Text data in tabular datasets comes in two flavours: short structured text (product names, city names, job titles) and long free text (reviews, support tickets, descriptions). They need different approaches.

Statistical Text Features

These are simple numeric summaries of the text content. They require no model training and carry genuine signal — a longer, more detailed review often indicates a more engaged customer.

import pandas as pd
import numpy as np

reviews = pd.DataFrame({
    "review_id": [1, 2, 3, 4, 5],
    "review_text": [
        "Great product, very happy with my purchase!",
        "Terrible quality. The item broke after two days. Will not buy again.",
        "OK",
        "Absolutely fantastic! Best purchase I have made this year. Fast shipping, excellent build quality, and the customer service team was incredibly helpful when I had a question.",
        "Mediocre at best. Expected more for the price."
    ],
    "rating": [5, 1, 3, 5, 2]
})

# Word count
reviews["word_count"] = reviews["review_text"].str.split().str.len()

# Character count (excluding spaces)
reviews["char_count"] = reviews["review_text"].str.replace(" ", "").str.len()

# Average word length
reviews["avg_word_length"] = reviews["review_text"].apply(
    lambda text: np.mean([len(w) for w in text.split()]) if text.split() else 0
)

# Sentence count (rough — split on . ! ?)
reviews["sentence_count"] = reviews["review_text"].str.count(r"[.!?]") + 1

# Exclamation marks — a proxy for emotional intensity
reviews["exclamation_count"] = reviews["review_text"].str.count(r"!")

# Uppercase word ratio — can indicate shouting / strong sentiment
reviews["uppercase_ratio"] = reviews["review_text"].apply(
    lambda text: sum(1 for w in text.split() if w.isupper()) / max(len(text.split()), 1)
)

print(reviews[["review_text", "word_count", "avg_word_length",
               "exclamation_count"]].to_string())
# Output:
#    review_text                                               word_count  avg_word_length  exclamation_count
# 0  Great product, very happy with my purchase!                        7             5.14                  1
# 1  Terrible quality. The item broke after two ...                    13             4.54                  0
# 2  OK                                                                 1             2.00                  0
# 3  Absolutely fantastic! Best purchase I have ...                    30             4.97                  1
# 4  Mediocre at best. Expected more for the price.                     8             4.75                  0

Statistical Features Are Interpretable and Fast

These features can be computed in microseconds and are directly interpretable to stakeholders. They often add meaningful signal to a model even when TF-IDF vectors are also included. Add them before computing expensive vectorised representations.

Bag-of-Words with CountVectorizer

CountVectorizer converts a collection of text documents to a matrix of token counts. Each row is a document, each column is a word, and each cell is the word's count in that document.

from sklearn.feature_extraction.text import CountVectorizer
import pandas as pd

product_descriptions = [
    "lightweight laptop with long battery life",
    "gaming laptop with high performance gpu",
    "budget laptop for students and office work",
    "ultra thin laptop with touchscreen display",
    "powerful laptop with dedicated graphics card"
]

cv = CountVectorizer(
    max_features=20,        # keep only the 20 most frequent tokens
    stop_words="english",   # remove "and", "with", "for", etc.
    min_df=2                # ignore tokens that appear in fewer than 2 documents
)

count_matrix = cv.fit_transform(product_descriptions)
vocab = cv.get_feature_names_out()
count_df = pd.DataFrame(count_matrix.toarray(), columns=vocab)

print(count_df)
# Output: sparse matrix showing word frequencies across documents
#    battery  budget  card  dedicated  display  gaming  gpu  graphics  high  laptop  ...
# 0        1       0     0          0        0       0    0         0     0       1  ...
# 1        0       0     0          0        0       1    1         0     1       1  ...
# 2        0       1     0          0        0       0    0         0     0       1  ...
# ...

TF-IDF with TfidfVectorizer

TF-IDF (Term Frequency — Inverse Document Frequency) weights each word by how often it appears in a document (TF) divided by how many documents it appears in (IDF). Common words that appear in every document get low weight. Rare, distinctive words get high weight.

It produces better features than raw counts for text classification because it downweights uninformative high-frequency words.

from sklearn.feature_extraction.text import TfidfVectorizer
import pandas as pd

support_tickets = [
    "payment failed credit card declined unable to complete purchase",
    "order not delivered tracking shows shipped but not received",
    "wrong item received incorrect product delivered to my address",
    "refund request for cancelled order payment not returned",
    "cannot login account password reset not working email not received",
    "delivery delayed expected three days ago still waiting",
    "credit card charge appeared twice duplicate payment issue",
    "product damaged on arrival packaging broken item not working"
]

tfidf = TfidfVectorizer(
    max_features=500,       # top 500 terms by TF-IDF score
    stop_words="english",
    ngram_range=(1, 2),     # include unigrams and bigrams ("not working", "credit card")
    min_df=2,               # ignore terms in fewer than 2 documents
    sublinear_tf=True       # apply log(1+tf) to dampen term frequency
)

tfidf_matrix = tfidf.fit_transform(support_tickets)

print(f"TF-IDF matrix shape: {tfidf_matrix.shape}")
# Output: TF-IDF matrix shape: (8, 27)  ← 8 documents, up to 500 terms (fewer here due to small corpus)

# Most important terms for document 0 (payment failed)
feature_names = tfidf.get_feature_names_out()
doc_0_scores = tfidf_matrix[0].toarray()[0]
top_terms = sorted(zip(feature_names, doc_0_scores), key=lambda x: x[1], reverse=True)[:5]
print("Top terms for ticket 0:", top_terms)
# Output: Top terms for ticket 0: [('payment failed', 0.52), ('declined', 0.43), ...]

TF-IDF in a Production Pipeline

TfidfVectorizer is a sklearn transformer — it has fit, transform, and fit_transform methods. Fit it on training documents only. Wrap it in a sklearn Pipeline (covered in 05-pipelines-and-leakage) so it transforms test and production data using only vocabulary learned from training.

TF-IDF Matrices Are Sparse and Wide

With max_features=5000, you get 5000 additional columns. This can be manageable, but combining TF-IDF features with a large tabular feature set requires care. Use scipy.sparse for memory efficiency and consider dimensionality reduction (TruncatedSVD / LSA) if the TF-IDF matrix is too large for your model.

Putting It Together — Full Datetime + Text Feature Example

import pandas as pd
import numpy as np
from sklearn.feature_extraction.text import TfidfVectorizer

np.random.seed(7)
n = 200

customer_support = pd.DataFrame({
    "ticket_id": range(n),
    "created_at": pd.date_range("2024-06-01", periods=n, freq="3h"),
    "resolved_at": pd.date_range("2024-06-01 02:00", periods=n, freq="3h") +
                   pd.to_timedelta(np.random.randint(1, 72, size=n), unit="h"),
    "description": np.random.choice(
        ["payment failed", "item not delivered", "refund request", "wrong product"],
        size=n
    ),
    "escalated": np.random.binomial(1, 0.2, size=n)
})

customer_support["created_at"] = pd.to_datetime(customer_support["created_at"])
customer_support["resolved_at"] = pd.to_datetime(customer_support["resolved_at"])

# Datetime features
customer_support["created_hour"]     = customer_support["created_at"].dt.hour
customer_support["created_dow"]      = customer_support["created_at"].dt.dayofweek
customer_support["is_weekend"]       = customer_support["created_dow"].isin([5, 6]).astype(int)
customer_support["resolution_hours"] = (
    customer_support["resolved_at"] - customer_support["created_at"]
).dt.total_seconds() / 3600

# Cyclical encoding for hour
customer_support["hour_sin"] = np.sin(2 * np.pi * customer_support["created_hour"] / 24)
customer_support["hour_cos"] = np.cos(2 * np.pi * customer_support["created_hour"] / 24)

# Text features — word count (description is short here, but the pattern applies to long text)
customer_support["desc_word_count"] = customer_support["description"].str.split().str.len()

print(customer_support[["created_hour", "hour_sin", "hour_cos",
                          "is_weekend", "resolution_hours",
                          "desc_word_count"]].head(6).round(3))

Key Takeaway

Datetime columns should never be fed raw to a model. Decompose them into calendar components, compute recency and duration features, and apply cyclical encoding for periodic variables. Text columns should be processed into at minimum word count and character count features; for richer signal, use TF-IDF. Both datetime and text transformers (like TfidfVectorizer) are sklearn-compatible and belong inside a Pipeline.

What's Next

You've covered datetime decomposition into hour, day-of-week, month, and year, computing recency and duration features, cyclical sine/cosine encoding for periodic variables, text statistical features (word count, sentence count, uppercase ratio), CountVectorizer bag-of-words, and TfidfVectorizer with n-gram support. Next up: 05-pipelines-and-leakage — where you'll assemble all these preprocessing steps into sklearn Pipelines and ColumnTransformers that prevent leakage structurally, so your entire feature engineering workflow is reproducible, testable, and safe to pass to cross-validation.

Optional Deep Dive

Read the pandas documentation on DatetimeLikeArrayMixin accessor methods at https://pandas.pydata.org/docs/reference/series.html#datetime-properties — it lists every .dt attribute available on datetime Series, including fiscal year, quarter, days in month, and business day flags that are commonly needed in financial and retail feature engineering.

Back: Categorical Features | Next: Pipelines and Leakage