如何用Python统计连续购买日期的streak天数区间频次

Hey there! Let's break down how to solve your problem of finding consecutive date streaks and counting them in your desired ranges. I'll walk through each step with corrected code and explanations.

Step 1: Fix Initial Data Setup #

First, let's correct your initial code—there are a couple of syntax issues (missing quotes, incorrect DataFrame creation, and inconsistent variable names):

import pandas as pd

# Your sample date data (fixed missing quotes on the 4th entry)
sample_dates = [
    '09-08-16 0:00',
    '22-08-16 0:00',
    '23-08-16 0:00',
    '28-08-16 0:00',
    '29-08-16 0:00',
    '30-08-16 0:00',
    '31-08-16 0:00'
]

# Create DataFrame and parse dates correctly (specify format to avoid parsing errors)
df = pd.DataFrame({'CreatedDate': sample_dates})
df['CreatedDate'] = pd.to_datetime(df['CreatedDate'], format='%d-%m-%y')  # Format: day-month-year
df['DAY'] = df['CreatedDate'].dt.day

Step 2: Calculate Consecutive Date Streaks #

To identify streaks of consecutive dates, we need to:

1. Sort the dates (critical—otherwise consecutive dates might not be adjacent in the data)
2. Calculate gaps between adjacent dates
3. Group dates into streaks where gaps are exactly 1 day

# Sort dates to ensure consecutive entries are next to each other
df = df.sort_values('CreatedDate').reset_index(drop=True)

# Calculate the number of days between each date and the previous one
df['date_gap'] = df['CreatedDate'].diff().dt.days

# Assign a unique ID to each streak: increment the ID whenever the gap isn't 1 day
df['streak_id'] = (df['date_gap'] != 1).cumsum()

# Calculate the length of each streak
streak_lengths = df.groupby('streak_id')['CreatedDate'].count().reset_index(name='streak_length')

For your sample data, this will give us streak lengths: [1, 2, 4] (the single 9th, the pair 22-23, and the 4-day run 28-31).

Step 3: Count Streaks in Your Desired Ranges #

Now we'll bin the streak lengths into your specified ranges and count how many streaks fall into each:

# Define your range bins and labels
bins = [0, 3, 7, 15, float('inf')]
range_labels = ['1-3', '4-7', '8-15', '>=16']

# Bin the streak lengths into the ranges
streak_lengths['streak_range'] = pd.cut(
    streak_lengths['streak_length'],
    bins=bins,
    labels=range_labels,
    right=False  # Makes ranges like [1,3] instead of (0,3]
)

# Count streaks per range, including ranges with 0 streaks
final_count = streak_lengths['streak_range'].value_counts()
final_count = final_count.reindex(range_labels).fillna(0).astype(int).reset_index(name='Count')
final_count.columns = ['Streak', 'Count']

# Print the result
print(final_count)

This will output exactly your expected result:

Streak  Count
0      1-3      3
1      4-7      1
2     8-15      0
3     >=16      0

Key Notes for Your Full Dataset #

• Make sure your full 50-date dataset is parsed correctly (adjust the format parameter in pd.to_datetime if your date structure is different)
• The sorting step is non-negotiable—if dates are out of order, consecutive streaks won't be detected
• If you have multiple purchases on the same day, deduplicate dates first with df = df.drop_duplicates('CreatedDate') before calculating streaks

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