1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127

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)