def main():
"""Go Main Go"""
config = util.get_config()
spr_client = util.get_spreadsheet_client(config)
drive = util.get_driveclient(config)
# Fake last conditional to make it easy to reprocess one site...
res = (drive.files().list(q=("title contains 'Soil Texture Data'"),
maxResults=999).execute())
HEADERS = [
"uniqueid",
"plotid",
"depth",
"tillage",
"rotation",
"soil6",
"nitrogen",
"drainage",
"rep",
"subsample",
"landscape",
"notes",
"herbicide",
"sampledate",
]
sz = len(res["items"])
for i, item in enumerate(res["items"]):
if item["mimeType"] != "application/vnd.google-apps.spreadsheet":
continue
spreadsheet = util.Spreadsheet(spr_client, item["id"])
spreadsheet.get_worksheets()
for year in spreadsheet.worksheets:
print('%3i/%3i sheet "%s" for "%s"' %
(i + 1, sz, year, item["title"]))
lf = spreadsheet.worksheets[year].get_list_feed()
for rownum, entry in enumerate(lf.entry):
dirty = False
data = entry.to_dict()
for key in ["soil13", "soil14"]:
if key not in data:
continue
value = data[key]
if rownum == 1 and value == "%":
print("updating % to g/kg")
entry.set_value(key, "g/kg")
dirty = True
continue
if rownum >= 2:
try:
newvalue = float(value) * 10.0
except Exception:
continue
print("%s updating %s to %s" % (key, value, newvalue))
entry.set_value(key, "%.4f" % (newvalue, ))
dirty = True
if dirty:
util.exponential_backoff(spr_client.update, entry)
def main():
"""Go Main Go"""
config = util.get_config()
spr_client = util.get_spreadsheet_client(config)
drive = util.get_driveclient(config)
# Fake last conditional to make it easy to reprocess one site...
res = drive.files().list(q=("title contains 'Soil Texture Data'"),
maxResults=999).execute()
HEADERS = ['uniqueid', 'plotid', 'depth', 'tillage', 'rotation', 'soil6',
'nitrogen', 'drainage', 'rep', 'subsample', 'landscape',
'notes', 'herbicide', 'sampledate']
sz = len(res['items'])
for i, item in enumerate(res['items']):
if item['mimeType'] != 'application/vnd.google-apps.spreadsheet':
continue
spreadsheet = util.Spreadsheet(spr_client, item['id'])
spreadsheet.get_worksheets()
for year in spreadsheet.worksheets:
print('%3i/%3i sheet "%s" for "%s"' % (i + 1, sz, year,
item['title']))
lf = spreadsheet.worksheets[year].get_list_feed()
for rownum, entry in enumerate(lf.entry):
dirty = False
data = entry.to_dict()
for key in ['soil13', 'soil14']:
if key not in data:
continue
value = data[key]
if rownum == 1 and value == '%':
print("updating % to g/kg")
entry.set_value(key, 'g/kg')
dirty = True
continue
if rownum >= 2:
try:
newvalue = float(value) * 10.
except Exception as _exp:
continue
print("%s updating %s to %s" % (key, value, newvalue))
entry.set_value(key, "%.4f" % (newvalue, ))
dirty = True
if dirty:
util.exponential_backoff(spr_client.update, entry)
def main():
"""Go Main"""
pgconn = get_dbconn('td')
pcursor = pgconn.cursor()
config = util.get_config()
sheets = util.get_sheetsclient(config, "cscap")
f = sheets.spreadsheets().get(
spreadsheetId=config['td']['manstore'], includeGridData=True
)
j = util.exponential_backoff(f.execute)
translate = {'date': 'valid'}
for sheet in j['sheets']:
table = TABLENAMES[TABS.index(sheet['properties']['title'])]
pcursor.execute("""DELETE from """ + table)
deleted = pcursor.rowcount
added = 0
for grid in sheet['data']:
cols = [a['formattedValue'] for a in grid['rowData'][0]['values']]
for row in grid['rowData'][2:]:
vals = [a.get('formattedValue') for a in row['values']]
data = dict(zip(cols, vals))
dbvals = []
dbcols = []
for key in data.keys():
if key.startswith('gio'):
continue
val = data[key]
if key in ['date', 'biomassdate1', 'biomassdate2',
'outletdate']:
val = (
val
if val not in ['unknown', 'N/A', 'n/a', 'TBD']
else None
)
dbvals.append(val)
dbcols.append(translate.get(key, key.replace("_", "")))
sql = """
INSERT into %s(%s) VALUES (%s)
""" % (table, ",".join(dbcols), ','.join(["%s"]*len(dbcols)))
try:
pcursor.execute(sql, dbvals)
except Exception as exp:
print("[TD] harvest_management traceback")
print(exp)
for col, val in zip(cols, vals):
print(" |%s| -> |%s|" % (col, val))
return
added += 1
print(("[TD] harvest_management %16s added:%4s deleted:%4s"
) % (table, added, deleted))
pcursor.close()
pgconn.commit()
pgconn.close()
def main():
"""Go Main Go"""
mydb = psycopg2.connect('dbname=nwa')
mcursor = mydb.cursor()
mcursor.execute("""
DELETE from lsrs where date(valid) = '2019-03-28'
""")
print('Deleted %s rows' % (mcursor.rowcount,))
# Get me a client, stat
config = util.get_config()
sheets = util.get_sheetsclient(config, "cscap")
f = sheets.spreadsheets().get(
spreadsheetId=SHEET, includeGridData=True
)
j = util.exponential_backoff(f.execute)
inserts = 0
grid = j['sheets'][0]['data'][0]
cols = [a['formattedValue'] for a in grid['rowData'][0]['values']]
print(cols)
for row in grid['rowData'][1:]:
vals = [a.get('formattedValue') for a in row['values']]
data = dict(zip(cols, vals))
if data['Workshop UTC'] is None:
continue
ts = convtime(data['Workshop UTC'])
ts = ts.replace(tzinfo=pytz.UTC)
if data['Workshop Reveal UTC'] is None:
revealts = ts
else:
revealts = convtime(data['Workshop Reveal UTC'])
revealts = revealts.replace(tzinfo=pytz.UTC)
if ts != revealts:
print((" Entry has reveal delta of %s minutes"
) % ((revealts - ts).total_seconds() / 60.,))
geo = 'SRID=4326;POINT(%s %s)' % (data['LON'], data['LAT'])
sql = """
INSERT into lsrs (valid, display_valid, type, magnitude, city, source,
remark, typetext, geom, wfo) values (%s, %s, %s, %s, %s, %s,
%s, %s, %s, 'DMX')"""
args = (ts, revealts,
LKP[data['Type']],
0 if data['Magnitude'] == 'None' else data['Magnitude'],
data['Workshop City'], data['source'], data['Remark'],
data['Type'], geo)
mcursor.execute(sql, args)
inserts += 1
mcursor.close()
mydb.commit()
print("Inserted %s new entries!" % (inserts,))
print("ALERT: Consider running assign_lsr_wfo.py to get WFO right in DB")
def build_xref():
f = sheets.spreadsheets().get(
spreadsheetId="1PKK-vWuOryYFOSYSgt4TosrjIDX_F-opHOvrEo5q-i4",
includeGridData=True,
)
j = util.exponential_backoff(f.execute)
sheet = j["sheets"][0]
griddata = sheet["data"][0]
d = dict()
for rowdata in griddata["rowData"]:
c1 = rowdata["values"][0].get("formattedValue", "n/a")
c2 = rowdata["values"][1].get("formattedValue", "n/a")
d[c1.strip()] = c2.strip()
return d
def main():
"""Go!"""
config = util.get_config()
sheets = util.get_sheetsclient(config, "cscap")
drive = util.get_driveclient(config)
res = (drive.files().list(
q=("title contains 'Soil Bulk Density' or "
"title contains 'Soil Nitrate Data' or "
"title contains 'Soil Texture Data' or "
"title contains 'Agronomic Data'"),
maxResults=999,
).execute())
results = []
for item in tqdm(res["items"]):
if item["mimeType"] != "application/vnd.google-apps.spreadsheet":
continue
title = item["title"]
f = sheets.spreadsheets().get(spreadsheetId=item["id"],
includeGridData=True)
j = util.exponential_backoff(f.execute)
for sheet in j["sheets"]:
sheet_title = sheet["properties"]["title"]
for griddata in sheet["data"]:
startcol = griddata.get("startColumn", 1)
startrow = griddata.get("startRow", 1)
header = []
for row, rowdata in enumerate(griddata["rowData"]):
if "values" not in rowdata: # empty sheet
continue
for col, celldata in enumerate(rowdata["values"]):
if row == 0:
header.append(celldata.get("formattedValue",
"n/a"))
if celldata.get("note") is not None:
results.append({
"title": title,
"header": header[col],
"sheet_title": sheet_title,
"row": row + startrow + 1,
"col": col + startcol + 1,
"note": celldata["note"],
})
df = pd.DataFrame(results)
df.to_csv("notes.csv", sep="|")
def has_or_create_sheet(drive, colfolder, title):
""" Create or find this sheet!"""
res = util.exponential_backoff(drive.files().list(
q=("mimeType = 'application/vnd.google-apps.spreadsheet' "
"and title = '%s' and '%s' in parents"
) % (title, colfolder)).execute)
if len(res['items']) == 0:
body = {'title': title,
'mimeType': 'application/vnd.google-apps.spreadsheet',
'parents': [{'id': colfolder}]
}
print("Creating Tile Flow Sheet: %s in %s" % (title, colfolder))
res = drive.files().insert(body=body).execute()
return res['id']
return res['items'][0]['id']
def has_or_create_sheet(drive, colfolder, title):
""" Create or find this sheet!"""
res = util.exponential_backoff(drive.files().list(
q=("mimeType = 'application/vnd.google-apps.spreadsheet' "
"and title = '%s' and '%s' in parents") %
(title, colfolder)).execute)
if len(res["items"]) == 0:
body = {
"title": title,
"mimeType": "application/vnd.google-apps.spreadsheet",
"parents": [{
"id": colfolder
}],
}
print("Creating Sheet: %s in %s" % (title, colfolder))
res = drive.files().insert(body=body).execute()
return res["id"]
return res["items"][0]["id"]
def googlesheet(siteid, sheetkey):
"""Harvest a google sheet, please"""
rows = []
config = util.get_config()
sheets = util.get_sheetsclient(config, "td")
f = sheets.spreadsheets().get(spreadsheetId=sheetkey, includeGridData=True)
j = util.exponential_backoff(f.execute)
for sheet in j['sheets']:
# sheet_title = sheet['properties']['title']
for griddata in sheet['data']:
for row, rowdata in enumerate(griddata['rowData']):
if 'values' not in rowdata: # empty sheet
continue
if row == 1: # skip units
continue
if row == 0:
header = []
for col, celldata in enumerate(rowdata['values']):
header.append(celldata['formattedValue'])
continue
data = {}
for col, celldata in enumerate(rowdata['values']):
data[header[col]] = fmt(celldata.get('formattedValue'))
rows.append(data)
df = pd.DataFrame(rows)
print("googlesheet has columns: %s" % (repr(df.columns.values),))
newcols = {}
for k in df.columns:
newcols[k] = XREF.get(k, k)
df.rename(columns=newcols, inplace=True)
df['valid'] = pd.to_datetime(df['valid'], errors='raise',
format='%m/%d/%y %H:%M')
df['valid'] = df['valid'] + datetime.timedelta(hours=TZREF[siteid])
# do some conversions
print("ALERT: doing windspeed unit conv")
df['windspeed_mps'] = speed(df['windspeed_mps'].values, 'KMH').value('MPS')
print("ALERT: doing windgustunit conv")
df['windgust_mps'] = speed(df['windgust_mps'].values, 'KMH').value('MPS')
return df
ssclient, spreadsheet, str(yr), 10, len(row1)
)
worksheet.get_cell_feed()
if worksheet.cols != len(row1):
print(
"len mismatch worksheet.cols: %s, new: %s"
% (worksheet.cols, len(row1))
)
for colnum in range(len(row1)):
if colnum >= worksheet.cols:
print("Expanding %s[%s] by one column" % (title, yr))
worksheet.expand_cols(1)
cell = worksheet.get_cell_entry(1, colnum + 1)
if cell.cell.input_value != row1[colnum]:
cell.cell.input_value = row1[colnum]
util.exponential_backoff(ssclient.update, cell)
cell = worksheet.get_cell_entry(2, colnum + 1)
if cell.cell.input_value != row2[colnum]:
cell.cell.input_value = row2[colnum]
util.exponential_backoff(ssclient.update, cell)
delcols = []
for colnum in range(len(row1), worksheet.cols):
delcols.append(worksheet.get_cell_value(1, colnum + 1))
if len(delcols) > 0:
for delcol in delcols:
print(" deleting column %s %s" % (yr, delcol))
worksheet.del_column(delcol)
if "sheet1" in spreadsheet.worksheets:
sheet = ssclient.get_worksheet(spreadkey, "od6")
ssclient.delete(sheet)
def main():
"""Do Main Things"""
sprclient = util.get_spreadsheet_client(util.get_config())
spreadsheet = util.Spreadsheet(sprclient, BACKEND)
spreadsheet.get_worksheets()
sheet = spreadsheet.worksheets["Field Operations"]
for entry in sheet.get_list_feed().entry:
row = entry.to_dict()
if row["operation"] != "fertilizer_synthetic":
continue
if (row["productrate"] is None or row["productrate"] == "-1.0"
or row["productrate"] == ""):
option = "B"
# Option B, values are in lbs per acre
phosphoruse = 0.0
potassium = 0.0
nitrogen = float2(row["nitrogen"]) * KGHA_LBA
if row["phosphorus"] is not None and float2(row["phosphorus"]) > 0:
phosphoruse = float2(row["phosphorus"]) * KGHA_LBA
if row["phosphate"] is not None and float2(row["phosphate"]) > 0:
phosphoruse = float2(row["phosphate"]) * KGHA_LBA * 0.437
if row["potassium"] is not None and float2(row["potassium"]) > 0:
potassium = float2(row["potassium"]) * KGHA_LBA
if row["potash"] is not None and float2(row["potash"]) > 0:
potassium = float(row["potash"]) * KGHA_LBA * 0.830
sulfur = float2(row["sulfur"]) * KGHA_LBA
zinc = float2(row["zinc"]) * KGHA_LBA
magnesium = float2(row["magnesium"]) * KGHA_LBA
calcium = float2(row["calcium"]) * KGHA_LBA
iron = float2(row["iron"]) * KGHA_LBA
else:
option = "A"
# Option A, straight percentages
prate = float2(row["productrate"])
nitrogen = prate * float2(row["nitrogen"]) / 100.0
phosphoruse = 0.0
potassium = 0.0
if row["phosphorus"] is not None and float2(row["phosphorus"]) > 0:
phosphoruse = prate * float2(row["phosphorus"]) / 100.0
if row["phosphate"] is not None and float2(row["phosphate"]) > 0:
phosphoruse = prate * float2(row["phosphate"]) / 100.0 * 0.437
if row["potassium"] is not None and float2(row["potassium"]) > 0:
potassium = prate * float2(row["potassium"]) / 100.0
if row["potash"] is not None and float2(row["potash"]) > 0:
potassium = prate * float(row["potash"]) / 100.0 * 0.830
sulfur = prate * float2(row["sulfur"]) / 100.0
zinc = prate * float2(row["zinc"]) / 100.0
magnesium = prate * float2(row["magnesium"]) / 100.0
calcium = prate * float2(row["calcium"]) / 100.0
iron = prate * float2(row["iron"]) / 100.0
print(("Option: %s\n nitrogen: Old: %s -> New: %s") %
(option, row["nitrogenelem"], nitrogen))
entry.set_value("nitrogenelem", "%.2f" % (nitrogen, ))
entry.set_value("phosphoruselem", "%.2f" % (phosphoruse, ))
entry.set_value("potassiumelem", "%.2f" % (potassium, ))
entry.set_value("sulfurelem", "%.2f" % (sulfur, ))
entry.set_value("zincelem", "%.2f" % (zinc, ))
entry.set_value("magnesiumelem", "%.2f" % (magnesium, ))
entry.set_value("calciumelem", "%.2f" % (calcium, ))
entry.set_value("ironelem", "%.2f" % (iron, ))
# if option == 'B':
util.exponential_backoff(sprclient.update, entry)
# Fake last conditional to make it easy to reprocess one site...
res = drive.files().list(q=("(title contains 'Soil Bulk Density' or "
"title contains 'Soil Nitrate Data' or "
"title contains 'Soil Texture Data' or "
"title contains 'Agronomic Data' or "
"title contains 'Plot Identifiers') and "
"title contains 'HICKS'"),
maxResults=999).execute()
sz = len(res['items'])
for i, item in enumerate(res['items']):
if item['mimeType'] != 'application/vnd.google-apps.spreadsheet':
continue
spreadsheet = util.Spreadsheet(spr_client, item['id'])
spreadsheet.get_worksheets()
for year in spreadsheet.worksheets:
print('%3i/%3i sheet "%s" for "%s"' % (i + 1, sz, year, item['title']))
lf = spreadsheet.worksheets[year].get_list_feed()
for entry in lf.entry:
dirty = False
data = entry.to_dict()
if data.get('uniqueid') is None:
continue
value = data['uniqueid']
newvalue = value.replace("swroc", 'hicks')
if newvalue != value:
entry.set_value('uniqueid', newvalue)
print(' "%s" -> "%s"' % (value, newvalue))
util.exponential_backoff(spr_client.update, entry)
continue
val = sheet.get_cell_value(myrow, col)
years[site] = util.translate_years(val)
# Now we dance
NOT_DONE = ['BATH_A', 'TIDE_NEW', 'TIDE_OLD']
for site in sites:
if site not in NOT_DONE:
continue
if years[site] is None or len(years[site]) == 0:
continue
print("---------- Processing %s -----------" % (site, ))
# Compute the base folder
foldername = "%s - %s" % (site, pis[site])
res = util.exponential_backoff(drive.files().list(
q=("mimeType = 'application/vnd.google-apps.folder' and title = '%s'"
" and '%s' in parents"
) % (foldername, config['td']['syncfolder'])).execute)
if 'items' not in res:
print("Folder Fail: %s" % (foldername, ))
continue
colfolder = res['items'][0]['id']
title = "%s Water Table Depth" % (site, )
spreadkey = has_or_create_sheet(drive, colfolder, title)
spreadsheet = util.Spreadsheet(ssclient, spreadkey)
row1 = ['Date']
row2 = ['MM/DD/YYYY']
for p in sites[site]:
for v, u in zip(['WAT4 Water Table Depth', ],
['(cm)', ]):
row1.append("%s %s" % (p, v))
row2.append(u)
c2 = rowdata["values"][1].get("formattedValue", "n/a")
d[c1.strip()] = c2.strip()
return d
xref = build_xref()
rows = []
for item in res["items"]:
if item["mimeType"] != "application/vnd.google-apps.spreadsheet":
continue
print("===> %s" % (item["title"], ))
site = item["title"].split()[0]
f = sheets.spreadsheets().get(spreadsheetId=item["id"],
includeGridData=True)
j = util.exponential_backoff(f.execute)
for sheet in j["sheets"]:
sheet_title = sheet["properties"]["title"]
print(" |-> %s" % (sheet_title, ))
for griddata in sheet["data"]:
row1 = griddata["rowData"][0]
row2 = griddata["rowData"][1]
for c1, c2 in zip(row1["values"], row2["values"]):
v1 = c1.get("formattedValue", "n/a").strip()
v2 = c2.get("formattedValue", "n/a").strip()
if v1 not in IGNORE and xref.get(v1, "") != v2:
print(("%s -> '%s'\n"
" should be '%s'") % (v1, v2, xref.get(v1)))
rows.append(
dict(
uniqueid=site,
continue
d[plotid][str(year)] = {
"corn": (opt.find("C") > -1),
"soy": (opt.find("S") > -1),
"wheat": (opt.find("W") > -1),
"popcorn": (opt.find("P") > -1),
"sugarbeets": (opt.find("B") > -1),
}
NOT_DONE = ["AUGLA", "HENRY"]
for site in sites:
if site not in NOT_DONE:
continue
foldername = "%s - %s" % (site, pis[site])
print("Searching for foldername |%s|" % (foldername, ))
res = util.exponential_backoff(drive.files().list(
q=("mimeType = 'application/vnd.google-apps.folder' and title = '%s'"
" and '%s' in parents") %
(foldername, config["td"]["syncfolder"])).execute)
if res is None or "items" not in res:
print("Folder Fail: %s" % (foldername, ))
continue
colfolder = res["items"][0]["id"]
title = "%s Crop Yield Data" % (site, )
spreadkey = has_or_create_sheet(drive, colfolder, title)
spreadsheet = util.Spreadsheet(spr_client, spreadkey)
years = []
for plotid in sites[site]:
for year in sites[site][plotid]:
if year not in years:
years.append(year)
years.sort()
if len(years) == 0:
"LND3": "[3] Toe slope",
}
col = "landscape"
sz = len(res["items"])
for i, item in enumerate(res["items"]):
if item["mimeType"] != "application/vnd.google-apps.spreadsheet":
continue
spreadsheet = util.Spreadsheet(spr_client, item["id"])
spreadsheet.get_worksheets()
for year in spreadsheet.worksheets:
uniqueid = item["title"].split()[0]
print('%3i/%3i sheet "%s" for "%s"' % (i + 1, sz, year, item["title"]))
lf = spreadsheet.worksheets[year].get_list_feed()
for rownum, entry in enumerate(lf.entry):
dirty = False
data = entry.to_dict()
if data["plotid"] is None or col not in data:
continue
key = "%s::%s" % (uniqueid, data["plotid"])
old = data[col]
new = LKP.get(df.at[key, col], df.at[key, col])
if old is None and new is None:
continue
if new is None:
new = ""
if old is None or old[:3] != new[:3]:
print("col: %s old: %s new: %s" % (col, old, new))
entry.set_value(col, new)
util.exponential_backoff(spr_client.update, entry)
