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)
|
