Level 5 · How long until a return?

Subtracting dates

When you subtract one datetime column from another, pandas gives you a Timedelta column: a length of time, not a date.

r = (
    returns.merge(order_items[["order_item_id", "order_id"]], on="order_item_id")
    .merge(orders[["order_id", "order_date"]], on="order_id")
)
(r["returned_on"] - r["order_date"]).head(2)
# 0   7 days 12:16:24
# 1   5 days 12:47:48

The .dt accessor works on Timedeltas too: .dt.days gives the whole days, and .dt.total_seconds() the exact length in seconds (divide by 86,400 for fractional days).

Watch the time of day

returned_on is a date (midnight), but order_date is a timestamp with a time. The first return above belongs to an order placed at 11:43 on 2 January 2023 and came back on 10 January: “7 days 12:16” later. .dt.days drops the partial day and says 7, but on the calendar the return arrived 8 days after the order day. To count calendar days, set the order to midnight first with .dt.normalize():

order_day = r["order_date"].dt.normalize()
r["days"] = (r["returned_on"] - order_day).dt.days
r["days"].mean()   # 16.29 (15.29 without normalize)

A whole day of difference in the average from one missing method call: always check what kind of date each column holds.

Moving dates: Timedelta and DateOffset

To shift a date by a fixed length, add a pd.Timedelta:

order_day + pd.Timedelta(days=30)    # 30 days later
orders["order_date"] - pd.Timedelta(hours=6)

For calendar-aware moves, use pd.DateOffset: pd.DateOffset(months=1) moves 31 January to 28 or 29 February, and pd.offsets.MonthEnd(0) rolls a date forward to the end of its month. Timedelta is a fixed number of days; DateOffset follows the calendar. A “30-day window” is a Timedelta; “until the end of next month” is a DateOffset.

The return window per reason

Once each row has days and a True/False within_30, a named aggregation answers Angela. The mean of a boolean column is the share of True values:

#          reason  n_returns  avg_days  share_30d
#      wrong_item        255       7.9      1.000
#         damaged        477       8.0      1.000
#    changed_mind        698      22.7      0.711
#            late        243      22.9      0.741

Damaged and wrong items come back within about a week, all inside 30 days. Change-of-mind and late returns take three weeks on average, and roughly a quarter of them arrive after day 30. Overall, 84.2% of returns would fit the window, so the policy hits exactly the customers who changed their mind.

Common mistakes

• Subtracting a timestamp with a time of day and reading .dt.days as calendar days.
• Calling .days on the column instead of .dt.days (it works on one Timedelta, not on a Series).
• Adding a plain number to a date (order_day + 30), which raises a TypeError. Wrap it in pd.Timedelta(days=30).
• Using pd.DateOffset(months=1) when the rule really is a fixed number of days, or the reverse.