<jupyter_start><jupyter_text>Follow these instructions to get a Google API Key https://developers.google.com/sheets/api/guides/authorizingAPIKey<jupyter_code>api_key = 'FIX ME: Use a valid Google API Key'
service = build('sheets', 'v4', developerKey=api_key)<jupyter_output><empty_output><jupyter_text>Get Data from Google Sheets API<jupyter_code># Call the Sheets API
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                            range=SAMPLE_RANGE_NAME).execute()
values = result.get('values', [])
df = pd.DataFrame(values[1:])
df = df[[0, 1, 2, 3]].copy()
df.columns = ['Reported', 'Tested', 'Type', 'Campus']
df.head()
#Remove timestamp from first column
df['Reported'] = pd.to_datetime(df['Reported']).dt.date
df.head()
staff_df = df[df['Type'] == 'Staff'].copy()
staff_count = staff_df.groupby('Reported').size().values
staff_df = staff_df.drop_duplicates(subset='Reported').assign(Staff_Count=staff_count)
staff_df
student_df = df[df['Type'] == 'Student'].copy()
student_count = student_df.groupby('Reported').size().values
student_df = student_df.drop_duplicates(subset='Reported').assign(Student_Count=student_count)
student_df
total_count = df.groupby('Reported').size().values
df = df.drop_duplicates(subset='Reported').assign(Count=total_count)
ndf = df[['Reported', 'Count']].copy()
ndf
plot = ndf.set_index('Reported').plot(kind='line', grid=True, figsize=(20, 10), xticks=ndf['Reported'])
fig = plot.get_figure()

merged_df = pd.merge(left=df, right=staff_df[['Reported', 'Staff_Count']], how='left', left_on='Reported',
                     right_on='Reported')
merged_df = pd.merge(left=merged_df, right=student_df[['Reported', 'Student_Count']], how='left', left_on='Reported',
                     right_on='Reported')

merged_df['SMA_5'] = merged_df[['Reported', 'Count']].rolling(window=5).mean()
merged_df['EMA_5'] = merged_df[['Reported', 'Count']].ewm(span=5).mean()

plot = merged_df.set_index('Reported').plot(kind='line', grid=True, figsize=(20, 10), xticks=ndf['Reported'],
                                            yticks=np.arange(min(merged_df['Count']), max(merged_df['Count']) + 1, 5.0))
plt.legend(['Total Count', 'Staff Count', 'Student Count', 'SMA_5', 'EMA_5'])
fig = plot.get_figure()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18221070811744386, 'fraction_numerical': 0.009930915371329879, 'mean_word_length': 5.978915662650603, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 9, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '93470', 'license': 'MIT', 'n_tokens_mistral': 838, 'n_tokens_neox': 805, 'n_words': 131, 'path': 'notebooks/lisdCovid19Dashboard.ipynb', 'repo_name': 'jlmayorga/lisdcovidanalysis', 'size': '2316'}