Pandas Datetime Exercise: Accommodation Stay & Rent Calculation

In property management and accommodation billing, rental fees depend on check-in timestamps, elapsed duration, and departmental discount allowances. In this exercise, process an accommodation roster (rent-calculation.xlsx), audit missing student IDs, calculate elapsed stay days relative to a base billing date, allocate free days using conditional np.where(), and flag overdue stays.

Dataset Background & Download 🔝

Company-provided accommodations house interns across multiple departments. Each intern has a check-in date (ck_date) and departmental free days policy (Marketing: 50 days; Production: 65 days; Planning: 45 days). Download the practice Excel workbook below:

Download rent-calculation.xlsx ↓

Billing & Duration Practice Problems 🔝

1. Display the column names and initial records of the DataFrame.
2. List each department with its corresponding count of registered students.
3. Identify and display records where the student id is missing (isnull()).
4. Calculate elapsed days (diff_days) from the check-in date relative to the base billing date of June 1, 2020.
5. Filter for students who have stayed longer than 200 days.
6. Create an allowed column assigning departmental free days (50 for 'mktg', 65 for 'production', 45 for 'planning') using np.where().
7. Identify all students whose stay exceeds their allowed free days.

Step-by-Step Solutions & Code Blocks 🔝

import pandas as pd
import numpy as np

# 1. Load Excel file
df_rent = pd.read_excel("download/rent-calculation.xlsx")
print("Columns:", df_rent.columns.tolist())

# 2. Department counts
print("\nStudents by Dept:\n", df_rent["dept"].value_counts())

# 3. Missing IDs
missing_ids = df_rent[df_rent["id"].isnull()]
print(f"\nMissing ID Records: {len(missing_ids)}")
assert len(missing_ids) == 2

# 4. Elapsed days relative to June 1, 2020
df_rent["ck_date"] = pd.to_datetime(df_rent["ck_date"])
base_date = pd.to_datetime("2020-06-01")
df_rent["diff_days"] = (base_date - df_rent["ck_date"]).dt.days

# 5. Over 200 days stay
over_200 = df_rent[df_rent["diff_days"] > 200]
print(f"\nStayed > 200 days: {len(over_200)} students")

# 6. Allocate free days policy
df_rent["allowed"] = np.where(
    df_rent["dept"] == "mktg", 50,
    np.where(df_rent["dept"] == "production", 65,
    np.where(df_rent["dept"] == "planning", 45, np.nan))
)

# 7. Identify chargeable overstay students
billable = df_rent[df_rent["diff_days"] > df_rent["allowed"]]
print(f"\nBillable Overstay Students: {len(billable)}")
print(billable[["name", "dept", "diff_days", "allowed"]])

assert len(billable) > 0

Nested np.where() Conditional Logic 🔝

Using np.where() provides vector-optimized conditional assignment without slow row-by-row lambda functions:

df_rent["allowed"] = np.where(
    df_rent["dept"] == "mktg", 50,
    np.where(df_rent["dept"] == "production", 65,
    np.where(df_rent["dept"] == "planning", 45, np.nan))
)

Datetime Exercise Series Navigation 🔝

• Exercise 3-1: Time-series maintenance logs, date queries, and intervals.
• Exercise 3-2: Datetime basics, birthday arithmetic, and format conversions.
• Exercise 3-3: Webmaster external backlink crawl date analysis.
• Exercise 3-4 (Current Page): Student accommodation check-in durations and rent calculation.

Practice in Google Colab 🔝

Open in Google Colab View on GitHub
Run all check-in duration calculations, nested np.where allocations, and billable stay filters interactively in Google Colab.

Further Reading & Related Tutorials 🔝

• Pandas Date and Time Hub — Master pillar for all Pandas datetime operations.
• to_datetime() — Converting date columns into Timestamps.
• NumPy where() — Conditional array element assignment.
• isnull() — Detecting and auditing missing data.
• Pandas Section Home — Master index of all Pandas tutorials, pillars, and guides.

Reference: Pandas Time Series documentation.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: