import pandas as pd
import os
def read_xlsx(file_path):
    """
    Read an Excel file and return a DataFrame.
    """
    if os.path.exists(file_path):
        df = pd.read_excel(file_path)
        return df
    else:
        raise FileNotFoundError(f"The file {file_path} does not exist.")
def first_processing(df, t_begin_col, t_end_col, data_col):
    """
    INPUT: pandas.DataFrame, t_begin_col, t_end_col, data_col
    OUTPUT: pandas.DataFrame
    """
    df[t_begin_col] = pd.to_datetime(df[t_begin_col])
    df[t_end_col] = pd.to_datetime(df[t_end_col])
    df = df[df[t_begin_col] <= df[t_end_col]].copy()
    global_begin = df[t_begin_col].min()
    global_end = df[t_end_col].max()
    full_range = pd.date_range(start=global_begin, end=global_end, freq='h')
    mapping = {}
    for _, row in df.iterrows():
        start = row[t_begin_col]
        end = row[t_end_col]
        value = row[data_col]
        if end - start == pd.Timedelta(hours=1):
            mapping[start] = value
    new_rows = []
    for ts in full_range:
        new_rows.append({t_begin_col: ts, data_col: mapping.get(ts, 0),"type": 1 if ts in mapping else 0})
    new_df = pd.DataFrame(new_rows)
    return new_df
def further_processing(df, t_begin_col, t_end_col, data_col, target_df, type_val):
    """
    INPUT: pandas.DataFrame, t_begin_col, t_end_col, data_col, target_df, type_val
    OUTPUT: pandas.DataFrame
    """
    interval = pd.Timedelta(hours=type_val)
    filtered_df = df[(df[t_end_col] - df[t_begin_col] == interval)].drop_duplicates(
        subset=[t_begin_col, t_end_col, data_col])
    for _, row in filtered_df.iterrows():
        current_time = row[t_begin_col]
        selected_value = row[data_col]
        period_start = current_time
        period_end = current_time + interval
        mask = (target_df[t_begin_col] >= period_start) & (target_df[t_begin_col] < period_end)
        sub_df = target_df.loc[mask]
        existing_nonzero_sum = sub_df[sub_df[data_col] != 0][data_col].sum()
        remain_value = selected_value - existing_nonzero_sum
        if remain_value > 0:
            empty_mask = sub_df[data_col] == 0
            empty_count = empty_mask.sum()
            if empty_count > 0:
                average_val = remain_value / empty_count
                target_df.loc[mask & empty_mask, data_col] = average_val
                target_df.loc[mask & empty_mask, "type"] = type_val
    return target_df
def prep_extract(df,prep0,time,excel_name):
    """
    INPUT: pandas.DataFrame, prep0,time,excel_name
    OUTPUT: Excel file with sequences of rainfall
    """
    results = []
    current_sequence = []
    current_type = None
    for index, row in df.iterrows():
        if row['prep'] > prep0:
            current_sequence.append(row)
        else:
            if len(current_sequence) >= time:
                results.append((current_sequence, current_type))
            current_sequence = []
    if len(current_sequence) >= time:
        results.append((current_sequence, current_type))
    with pd.ExcelWriter(excel_name) as writer:
        for i, (sequence, seq_type) in enumerate(results):
            sequence_df = pd.DataFrame(sequence)
            sequence_df.to_excel(writer, sheet_name=f"Seq_{i+1}", index=False)
if __name__ == "__main__":
    file_path = 'data.xlsx'
    df = pd.read_excel(file_path)
    t_begin_col = 'begin'
    t_end_col = 'end'
    data_col = 'prep'
    types=[2,3,4,5,6,7,8,9,10,11,12,13,14,15]
    hourly_df = first_processing(df, t_begin_col, t_end_col, data_col)
    for i in types:
        hourly_df = further_processing(df, t_begin_col, t_end_col, data_col, hourly_df, i)
    hourly_df.to_excel('hourly.xlsx', index=False)
    prep_extract(hourly_df,2,5,'prep_extract.xlsx')
    print(hourly_df)