按Datetime_x列时间间隔统计Pandas DataFrame中compound列值出现频率的问题求助

Got it, let's fix this up—your initial groupby + count approach didn't work because you weren't properly binning the datetime column into your target time intervals. Since Datetime_x isn't the DataFrame index, we need to use pd.Grouper to handle the time-based grouping correctly. Here's a step-by-step solution:

Step 1: Ensure Your Datetime Column is Properly Formatted #

First, double-check that Datetime_x is a datetime type (not a string). If it's not, convert it first:

df['Datetime_x'] = pd.to_datetime(df['Datetime_x'])

Step 2: Group by Time Interval + Compound Value #

Use pd.Grouper to define your desired time granularity (like hourly, daily, weekly) alongside the compound column. Then use size() to count occurrences per group:

# Replace 'H' with your target interval (e.g., 'D' for day, '15T' for 15 minutes)
frequency_results = df.groupby(
    [pd.Grouper(key='Datetime_x', freq='H'), 'compound']
).size().reset_index(name='frequency')

Let's break this down:

• pd.Grouper(key='Datetime_x', freq='H'): Bins all rows in Datetime_x into hourly intervals (adjust freq to match your required hierarchy level).
• Grouping with 'compound' ensures we count how many times each unique compound value appears within each time bin.
• size() gives the raw count per group, and reset_index() converts the grouped index back into regular columns for readability.

Step 3 (Optional): Calculate Relative Frequencies #

If you want percentage-based frequencies (instead of raw counts) per time interval, add these lines:

# Calculate total occurrences per time interval
interval_totals = frequency_results.groupby('Datetime_x')['frequency'].sum().reset_index(name='total')

# Merge totals back and compute percentage
frequency_results = frequency_results.merge(interval_totals, on='Datetime_x')
frequency_results['frequency_pct'] = (frequency_results['frequency'] / frequency_results['total']) * 100

Common Time Interval Codes for freq #

Pick the code that matches your required hierarchy:

• 'T': Minutes
• 'H': Hours
• 'D': Days
• 'W-MON': Weeks (starting on Monday; replace MON with other days if needed)
• 'M': Month-end
• 'Q': Quarter-end
• Custom intervals like '30T' (30 minutes) or '2H' (2 hours)

Why Your Original Approach Failed #

When you tried df.groupby('Datetime_x')['compound'].count(), Pandas grouped by every unique timestamp in Datetime_x—not the aggregated intervals you wanted. pd.Grouper solves this by explicitly binning the datetime values into the time chunks you specify.

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