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)