Tag: Pandas

Generating a Date Column from Month, Day, and Year in Python Pandas

Generating a Date Column from Month, Day, and Year in Python Pandas

When working with real-world datasets, dates are often split across multiple columns—month, day, and year stored separately. Combining them into a proper datetime column enables time-based filtering, resampling, date arithmetic, and plotting. Python’s Pandas library provides several approaches, each suited to different data formats and performance requirements.

Using pd.to_datetime with a Dictionary

The most readable approach passes a dictionary mapping column names to date parts. Pandas’s to_datetime function accepts year, month, day keys and assembles them into datetime objects. This works directly on DataFrame columns without looping or apply functions. Missing or invalid dates (like February 30) produce NaT (Not a Time) values by default, which you can then handle with fillna or dropna.

import pandas as pd

df = pd.DataFrame({
    "year": [2024, 2024, 2024, 2024],
    "month": [1, 2, 3, 2],
    "day": [15, 28, 1, 30]
})

df["date"] = pd.to_datetime(df[["year", "month", "day"]])
print(df)
#    year  month  day       date
# 0  2024      1   15 2024-01-15
# 1  2024      2   28 2024-02-28
# 2  2024      3    1 2024-03-01
# 3  2024      2   30 2024-02-30  # NaT (invalid date)

# Drop invalid dates
df = df.dropna(subset=["date"])

String Concatenation Approach

An alternative method concatenates the columns into a date string and parses it. This is useful when you have additional columns like hour, minute, second, or timezone that you want to include. The f-string or .str.cat() approach creates a standard ISO format string (YYYY-MM-DD) that to_datetime parses efficiently. For large datasets (millions of rows), the dictionary method is faster because it avoids string creation overhead, but the string method offers more flexibility for non-standard date formats.

# String concatenation method
df["date_str"] = (df["year"].astype(str) + "-" +
                  df["month"].astype(str).str.zfill(2) + "-" +
                  df["day"].astype(str).str.zfill(2))
df["date"] = pd.to_datetime(df["date_str"])

# More concise: using assign and f-string
df = df.assign(date=pd.to_datetime(
    df["year"].astype(str) + "-" +
    df["month"].astype(str).str.zfill(2) + "-" +
    df["day"].astype(str).str.zfill(2)
))

Handling Different Column Names

Real datasets use varying column names. The dictionary approach handles this by renaming on the fly: pd.to_datetime(df[[“yr”, “mo”, “dy”]].rename(columns={“yr”:”year”,”mo”:”month”,”dy”:”day”})). For datasets with century prefixes (e.g., year column has values 23 instead of 2023), add 2000 before conversion. When month or day names are used instead of numbers (“January” instead of 1), use pd.to_datetime(df[“month”], format=”%B”) first to convert month names to numbers before combining.

# Rename columns to match expected names
cols = {"yr": "year", "mon": "month", "d": "day"}
df["date"] = pd.to_datetime(df[["yr", "mon", "d"]].rename(columns=cols))

# Handle 2-digit years
df["full_year"] = df["yr"] + 2000
df["date"] = pd.to_datetime(df[["full_year", "month", "day"]])

# For month names instead of numbers
df["month_num"] = pd.to_datetime(df["month_name"], format="%B").month

Performance Considerations

For small datasets (under 100K rows), all methods are fast enough. For millions of rows, the dictionary method (pd.to_datetime(df[[cols]])) is the fastest because it operates on integer columns directly without string conversion. Adding parsed dates as a DatetimeIndex enables efficient resampling (.resample()), time-based slicing (.loc[“2024-01″:]), and date-based aggregations (.groupby(pd.Grouper(freq=”ME”))). Once your data has a proper datetime column, you unlock the full Pandas time series toolkit—rolling windows, shifting, differencing, and timezone-aware operations.

Working with Time Series After Date Creation

Once you have a proper datetime column, set it as the DataFrame index with df.set_index(‘date’). This enables powerful time series operations: df.resample(‘M’).mean() computes monthly averages, df[‘2024′] selects all data from 2024, and df.rolling(7).mean() computes a 7-day moving average. For financial data, you can compute day-over-day changes with .diff(), year-over-year comparisons with .pct_change(periods=365), and cumulative sums with .cumsum(). Timezone-aware datetime columns (use tz=’UTC’ or tz=’Asia/Kolkata’ in to_datetime) handle daylight saving transitions correctly. Pandas also supports custom business calendars (pd.offsets.CustomBusinessDay) for financial data that excludes holidays and weekends. These operations form the foundation of time series analysis in Python, used across finance, IoT sensor data, web analytics, and scientific research.

df['date'] = pd.to_datetime(df[['year','month','day']])
df = df.set_index('date')
monthly = df.resample('ME').mean()  # Month-end frequency
weekly_rolling = df['value'].rolling(7, center=True).mean()
df['pct_change'] = df['value'].pct_change()

Handling Missing Date Components

Real datasets often have missing day or month values. If only year and month are known, set day to 1 as a convention. If month is missing but quarter is available, map quarter (Q1=month 1, Q2=4, Q3=7, Q4=10). The nullable integer type (pd.Int32Dtype()) allows integer columns to hold NA values that to_datetime can propagate as NaT. For datasets where dates span centuries (birth years from 1920-2020), ensure 2-digit years are parsed correctly by specifying the century cutoff with pd.to_datetime(col, format=’%m/%d/%y’, errors=’coerce’). Always validate the resulting dates by checking range: dates in the future or before the dataset’s expected timeframe indicate parsing errors. Visualizing the date distribution with df[‘date’].hist() quickly reveals outliers and gaps in the temporal coverage of your data.