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
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146 | def pipelineProcess(**context):
token = context.get("ti").xcom_pull(key="token")
# Contract Data
Data = getSheetData(token, "64e30f01da73cc0c91c644de")
# "Section and key date data"
Data2 = getSheetData(token, "64e3103f8a48730c87a906f0")
conn_string = getdrowPSQLConnectionString()
db = create_engine(conn_string)
conn = db.connect()
df = pd.DataFrame()
_df = pd.DataFrame()
with conn as conn:
for x in Data:
df_nested_list = json_normalize(x)
df2 = df_nested_list
df = df.append(df2)
df['starting date']=df['starting date'].apply(pd.to_datetime)
df['ori comp date']=df['ori comp date'].apply(pd.to_datetime)
df.columns = df.columns.str.replace(' ', '_').str.replace('.', '').str.replace('(', '_').str.replace(')', '').str.replace('%', 'percent').str.replace('/', '_')
if not(df.empty):
df.to_sql('dc202106_nec_section_of_work', con=conn, if_exists='replace', index= False)
for x in Data2:
df_nested_list = json_normalize(x)
df2=df_nested_list
_df = _df.append(df2)
_df['Starting Date']=_df['Starting Date'].apply(pd.to_datetime)
def preprocess_date_string(date_str):
try:
# Try to parse as ISO 8601 format first
dt = pd.to_datetime(date_str)
except ValueError:
# Remove the timezone name in parentheses if not ISO 8601
date_str = date_str.split(' (')[0]
# Define the date format without the timezone name
date_format = "%a %b %d %Y %H:%M:%S GMT%z"
dt = pd.to_datetime(date_str, format=date_format)
# Convert to UTC and then make timezone-naive
if dt.tzinfo is not None:
dt = dt.tz_convert('UTC').tz_localize(None)
return dt
# Apply preprocessing to each column
_df['Original completion dates'] = _df['Original completion dates'].apply(preprocess_date_string)
_df['Extended Completion Date'] = _df['Extended Completion Date'].apply(preprocess_date_string)
_df['Planned Completion Date(PCD)'] = _df['Planned Completion Date(PCD)'].apply(preprocess_date_string)
# date_format = "%a %b %d %Y %H:%M:%S GMT%z"
# _df['Original completion dates'] = pd.to_datetime(_df['Original completion dates'], format=date_format)
# _df['Extended Completion Date'] = pd.to_datetime(_df['Extended Completion Date'], format=date_format)
# _df['Planned Completion Date(PCD)'] = pd.to_datetime(_df['Planned Completion Date(PCD)'], format=date_format)
# _df['Original completion dates']=_df['Original completion dates'].apply(pd.to_datetime)
# _df['Extended Completion Date']=_df['Extended Completion Date'].apply(pd.to_datetime)
# _df['Planned Completion Date(PCD)']=_df['Planned Completion Date(PCD)'].apply(pd.to_datetime)
_df.columns = _df.columns.str.replace(' ', '_').str.replace('.', '').str.replace('(', '_').str.replace(')', '').str.replace('%', 'percent').str.replace('/', '_')
if not(_df.empty):
_df.to_sql('dc202106_nec_section_of_work_key_date', con=conn, if_exists='replace', index= False)
#"PWDD and Target Cost with Actual Monthly Total"
# Data = getSheetData(token, "6600f3cb1afd3fba7805c05d")
# df = pd.DataFrame.from_dict(Data)
# df['IP No.']=df['IP No.'].astype(str)
# numerics = df.select_dtypes(include="number").columns
# df=df.apply(pd.to_numeric, errors='ignore')
# df[numerics]=df[numerics].apply(lambda x: np.round(x, decimals=5))
# df = df.dropna(subset=['IP No.'])
# df = df.dropna(subset=['Month - Year'])
# df['Month - Year']=df['Month - Year'].apply(pd.to_datetime)
# df.columns = df.columns.str.replace(' ', '_').str.replace('.', '').str.replace('(', '_').str.replace(')', '').str.replace('%', 'percent').str.replace('/', '_')
# df = df.drop_duplicates(subset=['IP_No'], keep='first')
# db = create_engine(conn_string)
# conn = db.connect()
# with conn as conn:
# if not(df.empty):
# df.to_sql('dc202106_finance_data', con=conn, if_exists='replace')
# conn.close()
# # "Approved and Fo recast Price Bar Chart"
# data = getSheetData(token, "6600dd7b1afd3fba78d21cc1")
# df = pd.DataFrame.from_dict(data)
# numerics = df.select_dtypes(include="number").columns
# df=df.apply(pd.to_numeric, errors='ignore')
# df[numerics]=df[numerics].apply(lambda x: np.round(x, decimals=5))
# df['Month - Year']=df['Month - Year'].apply(pd.to_datetime)
# df.columns = df.columns.str.replace(' ', '_').str.replace('.', '_').str.replace('(', '_').str.replace(')', '').str.replace('%', 'percent')
# db = create_engine(conn_string)
# conn = db.connect()
# with conn as conn:
# if not(df.empty):
# df.to_sql('dc202106_finance_status_data', con=conn, if_exists='replace')
# conn.close()
# # EOT DATA
# # data = getSheetData(token, "64a68a0d9207b30c741cd79a")
# # df = pd.DataFrame.from_dict(data)
# # numerics = df.select_dtypes(include="number").columns
# # df=df.apply(pd.to_numeric, errors='ignore')
# # df[numerics]=df[numerics].apply(lambda x: np.round(x, decimals=5))
# # df['Month - Year']=df['Month - Year'].apply(pd.to_datetime)
# # df.columns = df.columns.str.replace(' ', '_').str.replace('.', '_').str.replace('(', '_').str.replace(')', '').str.replace('%', 'percent')
# # db = create_engine(conn_string)
# # conn = db.connect()
# # with conn as conn:
# # df.to_sql('c5_eot_data', con=conn, if_exists='replace')
# # conn.close()
# # "Programme Data"
# ##### Comment below for future use
data= getSheetData(token, "64e310138a48730c87a90451")
if data:
df = pd.DataFrame.from_dict(data)
def preprocess_date_string(date_str):
try:
# Try to parse as ISO 8601 format first
dt = pd.to_datetime(date_str)
except ValueError:
# Remove the timezone name in parentheses if not ISO 8601
date_str = date_str.split(' (')[0]
# Define the date format without the timezone name
date_format = "%a %b %d %Y %H:%M:%S GMT%z"
dt = pd.to_datetime(date_str, format=date_format)
# Convert to UTC and then make timezone-naive
if dt is not None and dt.tzinfo is not None:
dt = dt.tz_convert('UTC').tz_localize(None)
return dt
# Apply preprocessing to each column
df['Submission Date'] = df['Submission Date'].apply(preprocess_date_string)
df['Acceptance Date'] = df['Acceptance Date'].apply(preprocess_date_string)
# date_format = "%a %b %d %Y %H:%M:%S GMT%z"
# df['Submission Date'] = pd.to_datetime(df['Submission Date'], format=date_format)
# df['Acceptance Date'] = pd.to_datetime(df['Acceptance Date'], format=date_format)
# df=df.drop('Attachment', axis=1)
df.columns = df.columns.str.replace(' ', '_').str.replace('.', '_').str.replace('(', '_').str.replace(')', '').str.replace('%', 'percent')
db = create_engine(conn_string)
conn = db.connect()
with conn as conn:
df.to_sql('dc202106_nec_programme_data', con=conn, if_exists='replace')
conn.close()
|