-
Notifications
You must be signed in to change notification settings - Fork 11
/
Copy pathfill_missing_data.py
128 lines (94 loc) · 3.71 KB
/
fill_missing_data.py
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
# coding: utf-8
# In[1]:
import pandas as pd
import numpy as np
from datetime import datetime
# In[2]:
hh_df = pd.read_csv('home_ac/processed_hhdata_86_2.csv')
# print(hh_df.shape)
# hh_df.head(15)
hh_df.drop_duplicates(subset ="localhour", keep = False, inplace = True)
print(hh_df.shape)
# In[3]:
hh_df['hour_index']=0
#hh_df.iloc[-50]
# In[4]:
used = ['localhour', 'use', 'temperature', 'cloud_cover','GH', 'is_weekday','month','hour','AC','DC','hour_index']
datarow= []
# In[5]:
hour_index=0#hour index
hour_value=0
missing_count=0
start_time= pd.to_datetime(hh_df['localhour'].iloc[0][:-3])
for index, row in hh_df.iterrows():
row.localhour=row.localhour[:-3]
#print(row.localhour)
difference=(pd.to_datetime(row.localhour)-pd.to_datetime(hh_df['localhour'].iloc[0][:-3])).total_seconds()/3600
#print("index is difference",difference)
if difference!=hour_index:
gap = difference-hour_index
missing_count += gap
#fill in the missing hours
for i in range(int(gap)):
print("\n---------------------------------------")
print("missing data for hour index:",hour_index+i)
#row.hour=(hour_index+i)%24
temprow=None
#print("this is lastrow",lastrow)
temprow=lastrow
#print("this is temprow",temprow)
temprow.hour_index=hour_index+i
#print("this is hour of lastrow",lastrow.hour)
#temprow.hour = (hour_index+i)%24
current_time = start_time+pd.Timedelta(hour_index+i,unit='h')
temprow.localhour = current_time
temprow.hour = current_time.hour
temprow.month = current_time.month
temprow.is_weekday = int(datetime.strptime(str(current_time), "%Y-%m-%d %H:%M:%S").weekday() < 5)
print("The inserted row is \n",temprow)
#datarow.append(row[used])
datarow.append(temprow[used])
temprow=None
#hour=None
#print(datarow)
hour_index = difference
hour_index +=1
row.hour_index=difference
#hour_value = row.hour
#print(row[used])
#print("reach here")
lastrow = row[used]
datarow.append(row[used])
print("total missing hours",missing_count)
#------------------------------------------testing----------------------------
# hour_index=0 #hour index
# missing_count=0
# for index, row in hh_df.iterrows():
# #print(row.localhour)
# #row.month = float(pd.to_datetime(row.localhour[:-3]).month)
# #row.day = float(pd.to_datetime(row.localhour[:-3]).day)
# #data_hour = float(pd.to_datetime(row.localhour).hour-6)%24
# data_hour = float(pd.to_datetime(row.localhour[:-3]).hour)
# #print(data_hour)
# if data_hour != hour_index%24:
# print("we are missing hours for",row.localhour)
# missing_count += 1
# hour_index +=1
# hour_index += 1
# print("In total missing hours", missing_count)
# for index, row in hh_df.iterrows():
# #row.month = float(pd.to_datetime(row.localhour[:-3]).month)
# #row.day = float(pd.to_datetime(row.localhour[:-3]).day)
# print("------------")
# print(row.localhour)
# print(float(pd.to_datetime(row.localhour).hour-6)%24)
# print(float(pd.to_datetime(row.localhour[:-3]).hour))
# # print(pd.to_datetime(row.localhour))
# # print(pd.to_datetime(row.localhour).tz_localize('UTC'))
# # print(pd.to_datetime(row.localhour).tz_localize('UTC').tz_convert('US/Central'))
# # print(pd.to_datetime(row.localhour[:-3]).tz_localize('US/Central'))
# # print(pd.to_datetime(row.localhour)-pd.Timedelta('06:00:00'))
# In[6]:
df = pd.DataFrame(data=datarow, columns=used)
print(df.head())
df.to_csv('datanew/afterfix6.csv')