计算入住与退房日期间的每月天数及Dataframe转换需求咨询

Hey there! Let's break down how to solve both your tasks using pandas—this is the ideal tool for handling date logic and reshaping DataFrames to your desired format.

1. Calculate Monthly Stay Days Between Check-In/Check-Out Dates #

First, we'll build a helper function that splits a stay into individual months and calculates the number of days spent in each. This handles edge cases like cross-month, cross-year stays, and same-month stays.

Key Notes: #

• Adjust the day count logic based on your business rules: some scenarios count check-out day as a stay day, others don't. I'll include both options below.

import pandas as pd
from dateutil.relativedelta import relativedelta

def calculate_monthly_stay(checkin_date, checkout_date):
    # Convert inputs to datetime objects (handles string inputs)
    checkin = pd.to_datetime(checkin_date)
    checkout = pd.to_datetime(checkout_date)
    
    monthly_stays = []
    current_month = checkin.replace(day=1)  # Start of the check-in month
    
    while current_month < checkout:
        # Get the first day of the next month
        next_month = current_month + relativedelta(months=1)
        
        # Define the actual stay window for this month
        stay_start = max(checkin, current_month)
        stay_end = min(checkout, next_month)
        
        # Calculate days: 
        # - Use .days if check-out day is NOT counted
        # - Use .days + 1 if check-out day IS counted
        stay_days = (stay_end - stay_start).days  # Default: check-out day excluded
        
        # Add to list
        monthly_stays.append({
            "month_year": current_month.strftime("%Y-%m"),
            "stay_days": stay_days
        })
        
        # Move to next month
        current_month = next_month
    
    return monthly_stays

2. Transform Input DataFrame to Desired Output #

Let's assume your input DataFrame has columns like id, checkin, and checkout. We'll apply our helper function to each row, then reshape the data into a clean, row-per-month format.

Example Input DataFrame: #

input_df = pd.DataFrame({
    "id": [101, 102, 103],
    "checkin": ["2023-10-28", "2023-12-20", "2024-02-15"],
    "checkout": ["2023-11-05", "2024-01-03", "2024-02-20"]
})

Code to Generate Output: #

# Apply the helper function to every row
input_df["monthly_stay_details"] = input_df.apply(
    lambda row: calculate_monthly_stay(row["checkin"], row["checkout"]),
    axis=1
)

# Explode the list of monthly stays into separate rows
output_df = input_df.explode("monthly_stay_details").reset_index(drop=True)

# Expand the dictionary into separate columns
output_df = pd.concat(
    [output_df.drop("monthly_stay_details", axis=1),
     output_df["monthly_stay_details"].apply(pd.Series)],
    axis=1
)

Example Output DataFrame: #

Customization Tips: #

• If you need a wide-format output (columns for each month), use pd.pivot_table to reshape the data.
• Add validation (e.g., ensure check-out date is after check-in date) to avoid errors.

内容的提问来源于stack exchange，提问作者Snehal Kakade