<jupyter_start><jupyter_text>Imports<jupyter_code>import gspread
import pandas as pd
import numpy as np
import datetime
from us_state_abbrev import abbrev_us_state
import re
from tqdm.notebook import tqdm

gc = gspread.oauth()<jupyter_output><empty_output><jupyter_text>Import data (1 team entry, 3 comparison datasets)<jupyter_code>team_entry_sheet = gc.open("Mountains-Midwest COVID-19 by County (TEAM ENTRY) 10-28-2020 to present").sheet1
nyt_comparison_sheet = gc.open("QA TEAM ONLY - COVID-19 by County Comparison Data (NYT)").sheet1

team_entry = pd.DataFrame(team_entry_sheet.get_all_records(head = 2))
team_entry = team_entry.replace(r'^\s*$', np.nan, regex=True)
nyt_comparison = pd.DataFrame(nyt_comparison_sheet.get_all_records())
nyt_comparison = nyt_comparison.replace(r'^\s*$', np.nan, regex=True)
JHU_USAF_Cases_comparison_sheets = gc.open("COVID-19 by County Comparison Data (JHU/USAF) ")

USAF_Cases_comparison_sheet = JHU_USAF_Cases_comparison_sheets.sheet1
USAF_Deaths_comparison_sheet = JHU_USAF_Cases_comparison_sheets.get_worksheet(1)
JHU_comparison_sheet = JHU_USAF_Cases_comparison_sheets.get_worksheet(2)
USAF_Cases_comparison = pd.DataFrame(USAF_Cases_comparison_sheet.get_all_records())
USAF_Deaths_comparison = pd.DataFrame(USAF_Deaths_comparison_sheet.get_all_records())
JHU_comparison = pd.DataFrame(JHU_comparison_sheet.get_all_records())

USAF_Cases_comparison = USAF_Cases_comparison.replace(r'^\s*$', np.nan, regex=True)
USAF_Deaths_comparison = USAF_Deaths_comparison.replace(r'^\s*$', np.nan, regex=True)
JHU_comparison = JHU_comparison.replace(r'^\s*$', np.nan, regex=True)
# with pd.option_context("display.max_rows", 1000):
#     display(team_entry[team_entry.state == 'TX'].loc[:, 'tstpos_101620':'pbmort_101620'])
#     display(team_entry.loc[team_entry.state == 'TX', 'tstpos_101620':'pbmort_101620'])
# with pd.option_context('display.max_rows', None, 'display.max_columns', None):  # more options can be specified also
#     print(team_entry.pbmort_092520)
# team_entry.pbmort_092520.values[:] = 0<jupyter_output><empty_output><jupyter_text>Functions needed<jupyter_code># given a data frame and state, find out the latest updated time, formated in US time format
def lastTimeUpdated(state):
    print(f'For {state}, last updated on:')
    
    # team entry
    yourstateDF = team_entry[team_entry.state == state]
    nototalDF = yourstateDF.filter(regex=f"\d$", axis=1).iloc[1:]
    first_col_empty = pd.isna(nototalDF).any().idxmax()
#     print(first_col_empty) # show which column has empty values
    nextdate_str = re.compile("\d.*").findall(first_col_empty)[0]
    nextdate = datetime.datetime.strptime(nextdate_str, '%m%d%y')
    te_date = nextdate - datetime.timedelta(days=1)
    print(f"Team entry: {te_date.strftime('%m/%d/%y')}")
    
    # nyt comparison dataset
    nyt_date_str = nyt_comparison.date.iloc[-1]
    try:
        nyt_date = datetime.datetime.strptime(nyt_date_str, '%m/%d/%y')
    except ValueError:
        nyt_date = datetime.datetime.strptime(nyt_date_str, '%Y-%m-%d')
    print(f"NYT comparison dataset: {nyt_date.strftime('%m/%d/%y')}")
    
    # USAF
    usaf_date_str = USAF_Cases_comparison.columns[-1]
    usaf_date = datetime.datetime.strptime(usaf_date_str, '%m/%d/%Y')
    print(f"USAF comparison dataset: {usaf_date.strftime('%m/%d/%y')}")
    
    # JHU
    jhu_date_str = JHU_comparison.columns[-1][1:-2]
    jhu_date = datetime.datetime.strptime(jhu_date_str, '%Y%m%d')
    print(f"JHU comparison dataset: {jhu_date.strftime('%m/%d/%y')} \n")
    
    earliest_date = min([te_date, nyt_date, usaf_date, jhu_date])
    print(f"Date you can work on: {earliest_date.strftime('%m/%d/%y')}")
    
    return earliest_date
# given a state and a date (module datetime), output part of data frame you need to work on.
def partTeamEntry(state, date):
    yourstateDF = team_entry[team_entry.state == state]
    infoDF = yourstateDF.iloc[:, 0:5]
    covidDF = yourstateDF.filter(regex=f"{date.strftime('%m%d%y')}$", axis=1)
    
    return pd.concat([infoDF, covidDF], axis=1).iloc[1:,5:]
#     return infoDF
def partNYT(state, date):
    date_str = date.strftime('%m/%-d/%y')
#     date_str = date.strftime('%Y-%m-%d')
    return nyt_comparison[(nyt_comparison.date == date_str) & (nyt_comparison.state == abbrev_us_state[state])]
def partUSAFCases(state, date):
    date_str = date.strftime('%-m/%-d/%Y')
    return USAF_Cases_comparison[USAF_Cases_comparison.State == state].filter(items = [date_str])
def partUSAFDeaths(state, date):
    date_str = date.strftime('%-m/%-d/%Y')
    return USAF_Deaths_comparison[USAF_Deaths_comparison.State == state].filter(items = [date_str])
def partJHU(state, date):
    date_str = [f"x{date.strftime('%Y%m%d')}_c", f"x{date.strftime('%Y%m%d')}_m"]
    return JHU_comparison[JHU_comparison.state_ab == state].filter(items = date_str)
def letter_to_num(letter):
    return [ord(char) - 97 for char in letter.lower()][0]
def inputData(comp_xlsx, state, date):
    from openpyxl import load_workbook

    book = load_workbook(comp_xlsx)
    writer = pd.ExcelWriter(comp_xlsx, engine='openpyxl') 
    writer.book = book

    ## ExcelWriter for some reason uses writer.sheets to access the sheet.
    ## If you leave it empty it will not know that sheet Main is already there
    ## and will create a new sheet.

    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

    partTeamEntry(state,date).to_excel(writer, 
                                       startcol = letter_to_num('C'), 
                                       startrow = 3, 
                                       header=None, index=False,
                                       sheet_name=list(writer.sheets.keys())[1])
    partJHU(state,date).to_excel(writer, 
                                 startcol = letter_to_num('I'), 
                                 startrow = 3, 
                                 header=None, index=False,
                                 sheet_name=list(writer.sheets.keys())[1])
    partNYT(state,date).to_excel(writer, 
                                 startcol = letter_to_num('L'), 
                                 startrow = 3, 
                                 header=None, index=False,
                                 sheet_name=list(writer.sheets.keys())[1])
    partUSAFCases(state,date).to_excel(writer, 
                                       startcol = letter_to_num('V'), 
                                       startrow = 3, 
                                       header=None, index=False,
                                       sheet_name=list(writer.sheets.keys())[1])
    partUSAFDeaths(state,date).to_excel(writer, 
                                        startcol = 26, 
                                        startrow = 3, 
                                        header=None, index=False,
                                        sheet_name=list(writer.sheets.keys())[1])

    writer.save()<jupyter_output><empty_output><jupyter_text>Main Program<jupyter_code>today = datetime.date.today() 
print("Today's date is", today, '\n')

# state = input('Enter a state you working on: ').upper()
state = 'SD'
print('\n')

date = lastTimeUpdated(state)

# custom date
# last: 2020, 10 ,23
date = datetime.datetime(2020, 11, 25)
partNYT(state,date)
pre = './comparison sheets/'
if state == 'MN':
    filename = "Minnesota - Mountains - County Comparison (with probable)"
    inputData(f'{pre+filename}.xlsx', state, date)
if state == 'ND':
    filename = "North Dakota - Mountains - County Comparison (with probable)"
    inputData(f'{pre+filename}.xlsx', state, date)
if state == 'TX':
    filename = "Texas - Mountains - County Comparison (with probable)"
    inputData(f'{pre+filename}.xlsx', state, date)
if state == 'MT':
    filename = "Montana - Mountains - County Comparison (with probable)"
    inputData(f'{pre+filename}.xlsx', state, date)
if state == 'SD':
    filename = "South Dakota - Mountains - County Comparison (with probable)"
    inputData(f'{pre+filename}.xlsx', state, date)
from gspread.urls import SPREADSHEETS_API_V4_BASE_URL


def insert_note(worksheet, label, note):
    """
    Insert note ito the google worksheet for a certain cell.
    
    Compatible with gspread.
    """
    spreadsheet_id = worksheet.spreadsheet.id
    worksheet_id = worksheet.id

    row, col = tuple(label)      # [0, 0] is A1

    url = f"{SPREADSHEETS_API_V4_BASE_URL}/{spreadsheet_id}:batchUpdate"
    payload = {
        "requests": [
            {
                "updateCells": {
                    "range": {
                        "sheetId": worksheet_id,
                        "startRowIndex": row,
                        "endRowIndex": row + 1,
                        "startColumnIndex": col,
                        "endColumnIndex": col + 1
                    },
                    "rows": [
                        {
                            "values": [
                                {
                                    "note": note
                                }
                            ]
                        }
                    ],
                    "fields": "note"
                }
            }
        ]
    }
    worksheet.spreadsheet.client.request("post", url, json=payload)<jupyter_output><empty_output><jupyter_text>Save as csv here!<jupyter_code>read_comp_again = pd.read_csv(f'{pre+filename}.csv', header=1)

read_comp_again = read_comp_again.dropna(how='all')

part_read_again = read_comp_again.loc[:,['County', 'State', 'Unnamed: 21', 'Unnamed: 22']]

part_read_again
# change the order as team entry
part_read_again_wo_total = part_read_again.loc[1:,:]
unknown_row = part_read_again_wo_total.iloc[0,:]
part_read_again_wo_total

part_read_again_wo_total = part_read_again_wo_total.shift(-1)
part_read_again_wo_total.iloc[-1] = unknown_row.squeeze()
part_read_again_wo_total

part_reference = pd.concat([pd.DataFrame(part_read_again.loc[0,:]).transpose(), part_read_again_wo_total])
part_reference.columns = ['County', 'State', 'Case Comments', 'Death Comments']

part_reference
case_comments = part_reference.loc[:, 'Case Comments'].to_list()
death_comments = part_reference.loc[:, 'Death Comments'].to_list()
start_col_idx = [ i for i, col in enumerate(team_entry.columns) if col.endswith(date.strftime('%m%d%y')) ][::2]

if state == 'MT':
    start_row_idx = 159 # MT
    end_row_idx = 216 # MT
elif state == 'SD':
    start_row_idx = 402 # SD
    end_row_idx = 469 # SD

start_row_idx -= 1
end_row_idx -= 1
start_col_idx
from xlsxwriter.utility import xl_cell_to_rowcol
xl_cell_to_rowcol('CH159')
# Case
for i, ridx in enumerate(tqdm(range(start_row_idx, end_row_idx+1))):
    if i == 0:
        continue
    if pd.isna(case_comments[i]):
        continue
    insert_note(team_entry_sheet, (ridx, start_col_idx[0]), case_comments[i])
#         insert_note(team_entry, (ridx, cidx))
# Death
for i, ridx in enumerate(tqdm(range(start_row_idx, end_row_idx+1))):
    if i == 0:
        continue
    if pd.isna(death_comments[i]):
        continue
    insert_note(team_entry_sheet, (ridx, start_col_idx[1]), death_comments[i])
print(date)
print('Case Comment for STATE TOTALS: ', case_comments[0])
print('Death Comment for STATE TOTALS: ', death_comments[0])<jupyter_output>2020-11-25 00:00:00
Case Comment for STATE TOTALS:  JHU=76142; NYT=76142; USAF=76142 in STATE TOTALS
Death Comment for STATE TOTALS:  JHU=849; NYT=849; USAF=849 in STATE TOTALS<jupyter_text>Testing<jupyter_code>test_sheet = gc.open("test").sheet1
test = pd.DataFrame(test_sheet.get_all_records())
insert_comment(test_sheet, (1,2), 'Hello comment')
def insert_comment(service, file_id, content):
    """
    Insert a new document-level comment.
      Args:
        service: Drive API service instance.
        file_id: ID of the file to insert comment for.
        content: Text content of the comment.
      Returns:
        The inserted comment if successful, None otherwise.
    """
    new_comment = {
        'content': content
    }
    try:
        return service.comments().insert(fileId=file_id, body=new_comment).execute()
    except:
        print('An error occurred: %s')
        return None<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1259216778633459, 'fraction_numerical': 0.01581189578895625, 'mean_word_length': 2.9983622666229937, 'pattern_counts': {'":': 12, '<': 24, '<?xml version=': 0, '>': 24, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 21, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '39405', 'license': 'MIT', 'n_tokens_mistral': 4329, 'n_tokens_neox': 4028, 'n_words': 783, 'path': 'Main.ipynb', 'repo_name': 'Anthonyive/BroadStreet-QA-Automation', 'size': '12206'}