时间趋势分析：检测个体测试值平台期及计算持续天数求助

Hey there! Let's break down how to solve this problem—you've got longitudinal data tracking test scores over time for each individual, and you want to spot when scores stay flat (plateaus) and calculate how long those plateaus last in days. I'll walk you through two practical approaches using tools you're probably working with: Python's Pandas and SQL.

This approach is great if you prefer working with data frames and want flexibility for further analysis.

First, let's load and prep your data properly. We need to make sure dates are parsed correctly and records are ordered by individual and time:

import pandas as pd

# Replace this with your actual data loading (e.g., pd.read_csv())
data = pd.DataFrame({
    'id': [1,1,1,2,2,2,3,3,3],
    'time': ['01/01/2000','05/03/2001','12/08/2006','03/03/1999','04/05/2001','03/07/2003','04/08/2007','05/09/2007','06/09/2012'],
    'test': [40,42,44,34,34,36,40,44,48]
})

# Convert time column to datetime (critical for calculating day differences)
data['time'] = pd.to_datetime(data['time'], format='%d/%m/%Y')

# Sort by individual and time to ensure consecutive records are in order
data = data.sort_values(['id', 'time']).reset_index(drop=True)

Next, we'll flag plateau segments and calculate their duration:

# Get the previous test score and time for each individual
data['prev_test'] = data.groupby('id')['test'].shift(1)
data['prev_time'] = data.groupby('id')['time'].shift(1)

# Mark rows where current test equals the previous one (part of a plateau)
data['is_plateau'] = (data['test'] == data['prev_test'])

# Calculate days between current and previous time for plateau segments
data['plateau_duration_days'] = (data['time'] - data['prev_time']).dt.days

Finally, let's summarize the results per individual to see if they have a plateau, total plateau days, and the longest single plateau:

def summarize_individual_plateaus(group):
    has_plateau = group['is_plateau'].any()
    if has_plateau:
        total_days = group[group['is_plateau']]['plateau_duration_days'].sum()
        longest_days = group[group['is_plateau']]['plateau_duration_days'].max()
    else:
        total_days = 0
        longest_days = 0
    return pd.Series({
        'has_plateau': has_plateau,
        'total_plateau_days': total_days,
        'longest_plateau_days': longest_days
    })

# Generate the summary
plateau_summary = data.groupby('id').apply(summarize_individual_plateaus).reset_index()

print(plateau_summary)

Output Explanation #

For your sample data, this will return:

• ID 2 has a plateau of 763 days (from 1999-03-03 to 2001-04-05)
• IDs 1 and 3 have no plateaus since their test scores increase every time

If your data lives in a database, you can use window functions to do this directly in SQL. This is efficient for large datasets.

WITH ordered_records AS (
    -- Get previous test score and time for each individual
    SELECT
        id,
        time,
        test,
        LAG(test) OVER (PARTITION BY id ORDER BY time) AS prev_test,
        LAG(time) OVER (PARTITION BY id ORDER BY time) AS prev_time
    FROM your_table_name
),
plateau_segments AS (
    -- Filter to only plateau segments and calculate duration
    SELECT
        id,
        test,
        prev_time AS plateau_start,
        time AS plateau_end,
        (time - prev_time) AS plateau_duration_days
    FROM ordered_records
    WHERE test = prev_test
)
-- Final summary per individual
SELECT
    id,
    TRUE AS has_plateau,
    SUM(plateau_duration_days) AS total_plateau_days,
    MAX(plateau_duration_days) AS longest_plateau_days
FROM plateau_segments
GROUP BY id
UNION ALL
-- Include individuals with no plateaus
SELECT
    id,
    FALSE AS has_plateau,
    0 AS total_plateau_days,
    0 AS longest_plateau_days
FROM your_table_name
WHERE id NOT IN (SELECT DISTINCT id FROM plateau_segments)
GROUP BY id
ORDER BY id;

Key Notes #

• Date Format: Make sure your database's time column is stored as a date/datatype (not string) for accurate day calculations.
• Edge Cases: If an individual has only one record, they can't have a plateau—both methods handle this automatically.
• Multiple Consecutive Scores: If someone has 3+ consecutive identical scores (e.g., t1, t2, t3 all with test=X), the total duration will be (t2-t1)+(t3-t2) = t3-t1, which is the full length of the plateau.

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