{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"provenance":[],"authorship_tag":"ABX9TyNUxlFk8o1sQFzzJ4FoGbc3"},"kernelspec":{"name":"python3","display_name":"Python 3"},"language_info":{"name":"python"}},"cells":[{"cell_type":"code","execution_count":1,"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":90},"id":"e-n37xid8sKu","executionInfo":{"status":"ok","timestamp":1766425110902,"user_tz":300,"elapsed":9585,"user":{"displayName":"Natasha Holmes","userId":"01063502562103573622"}},"outputId":"1cc8e6d8-d45d-46ab-fc95-30e81cc159b8"},"outputs":[{"output_type":"display_data","data":{"text/plain":["<IPython.core.display.HTML object>"],"text/html":["\n","     <input type=\"file\" id=\"files-ef71d8ff-29b4-4b46-a09b-7a0421f1c588\" name=\"files[]\" multiple disabled\n","        style=\"border:none\" />\n","     <output id=\"result-ef71d8ff-29b4-4b46-a09b-7a0421f1c588\">\n","      Upload widget is only available when the cell has been executed in the\n","      current browser session. Please rerun this cell to enable.\n","      </output>\n","      <script>// Copyright 2017 Google LLC\n","//\n","// Licensed under the Apache License, Version 2.0 (the \"License\");\n","// you may not use this file except in compliance with the License.\n","// You may obtain a copy of the License at\n","//\n","//      http://www.apache.org/licenses/LICENSE-2.0\n","//\n","// Unless required by applicable law or agreed to in writing, software\n","// distributed under the License is distributed on an \"AS IS\" BASIS,\n","// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n","// See the License for the specific language governing permissions and\n","// limitations under the License.\n","\n","/**\n"," * @fileoverview Helpers for google.colab Python module.\n"," */\n","(function(scope) {\n","function span(text, styleAttributes = {}) {\n","  const element = document.createElement('span');\n","  element.textContent = text;\n","  for (const key of Object.keys(styleAttributes)) {\n","    element.style[key] = styleAttributes[key];\n","  }\n","  return element;\n","}\n","\n","// Max number of bytes which will be uploaded at a time.\n","const MAX_PAYLOAD_SIZE = 100 * 1024;\n","\n","function _uploadFiles(inputId, outputId) {\n","  const steps = uploadFilesStep(inputId, outputId);\n","  const outputElement = document.getElementById(outputId);\n","  // Cache steps on the outputElement to make it available for the next call\n","  // to uploadFilesContinue from Python.\n","  outputElement.steps = steps;\n","\n","  return _uploadFilesContinue(outputId);\n","}\n","\n","// This is roughly an async generator (not supported in the browser yet),\n","// where there are multiple asynchronous steps and the Python side is going\n","// to poll for completion of each step.\n","// This uses a Promise to block the python side on completion of each step,\n","// then passes the result of the previous step as the input to the next step.\n","function _uploadFilesContinue(outputId) {\n","  const outputElement = document.getElementById(outputId);\n","  const steps = outputElement.steps;\n","\n","  const next = steps.next(outputElement.lastPromiseValue);\n","  return Promise.resolve(next.value.promise).then((value) => {\n","    // Cache the last promise value to make it available to the next\n","    // step of the generator.\n","    outputElement.lastPromiseValue = value;\n","    return next.value.response;\n","  });\n","}\n","\n","/**\n"," * Generator function which is called between each async step of the upload\n"," * process.\n"," * @param {string} inputId Element ID of the input file picker element.\n"," * @param {string} outputId Element ID of the output display.\n"," * @return {!Iterable<!Object>} Iterable of next steps.\n"," */\n","function* uploadFilesStep(inputId, outputId) {\n","  const inputElement = document.getElementById(inputId);\n","  inputElement.disabled = false;\n","\n","  const outputElement = document.getElementById(outputId);\n","  outputElement.innerHTML = '';\n","\n","  const pickedPromise = new Promise((resolve) => {\n","    inputElement.addEventListener('change', (e) => {\n","      resolve(e.target.files);\n","    });\n","  });\n","\n","  const cancel = document.createElement('button');\n","  inputElement.parentElement.appendChild(cancel);\n","  cancel.textContent = 'Cancel upload';\n","  const cancelPromise = new Promise((resolve) => {\n","    cancel.onclick = () => {\n","      resolve(null);\n","    };\n","  });\n","\n","  // Wait for the user to pick the files.\n","  const files = yield {\n","    promise: Promise.race([pickedPromise, cancelPromise]),\n","    response: {\n","      action: 'starting',\n","    }\n","  };\n","\n","  cancel.remove();\n","\n","  // Disable the input element since further picks are not allowed.\n","  inputElement.disabled = true;\n","\n","  if (!files) {\n","    return {\n","      response: {\n","        action: 'complete',\n","      }\n","    };\n","  }\n","\n","  for (const file of files) {\n","    const li = document.createElement('li');\n","    li.append(span(file.name, {fontWeight: 'bold'}));\n","    li.append(span(\n","        `(${file.type || 'n/a'}) - ${file.size} bytes, ` +\n","        `last modified: ${\n","            file.lastModifiedDate ? file.lastModifiedDate.toLocaleDateString() :\n","                                    'n/a'} - `));\n","    const percent = span('0% done');\n","    li.appendChild(percent);\n","\n","    outputElement.appendChild(li);\n","\n","    const fileDataPromise = new Promise((resolve) => {\n","      const reader = new FileReader();\n","      reader.onload = (e) => {\n","        resolve(e.target.result);\n","      };\n","      reader.readAsArrayBuffer(file);\n","    });\n","    // Wait for the data to be ready.\n","    let fileData = yield {\n","      promise: fileDataPromise,\n","      response: {\n","        action: 'continue',\n","      }\n","    };\n","\n","    // Use a chunked sending to avoid message size limits. See b/62115660.\n","    let position = 0;\n","    do {\n","      const length = Math.min(fileData.byteLength - position, MAX_PAYLOAD_SIZE);\n","      const chunk = new Uint8Array(fileData, position, length);\n","      position += length;\n","\n","      const base64 = btoa(String.fromCharCode.apply(null, chunk));\n","      yield {\n","        response: {\n","          action: 'append',\n","          file: file.name,\n","          data: base64,\n","        },\n","      };\n","\n","      let percentDone = fileData.byteLength === 0 ?\n","          100 :\n","          Math.round((position / fileData.byteLength) * 100);\n","      percent.textContent = `${percentDone}% done`;\n","\n","    } while (position < fileData.byteLength);\n","  }\n","\n","  // All done.\n","  yield {\n","    response: {\n","      action: 'complete',\n","    }\n","  };\n","}\n","\n","scope.google = scope.google || {};\n","scope.google.colab = scope.google.colab || {};\n","scope.google.colab._files = {\n","  _uploadFiles,\n","  _uploadFilesContinue,\n","};\n","})(self);\n","</script> "]},"metadata":{}},{"output_type":"stream","name":"stdout","text":["Saving Phys 1110 FA25 Attendance_afterSept29.xlsx to Phys 1110 FA25 Attendance_afterSept29.xlsx\n"]}],"source":["import pandas as pd\n","import re\n","\n","# Upload your Excel file\n","from google.colab import files\n","uploaded = files.upload()\n"]},{"cell_type":"code","source":["###SALT\n","\n","# Replace with the uploaded file name\n","excel_file_path = list(uploaded.keys())[0]\n","\n","# Load the Excel file and get the sheet names\n","xls = pd.ExcelFile(excel_file_path)\n","sheet_names = xls.sheet_names\n","\n","# Filter the first 11 sheets that match the pattern \"SALT-4xx\"\n","salt_sheets = [name for name in sheet_names if re.match(r\"SALT-4\\d{2}\", name)][:11]\n","\n","# Initialize a list to store DataFrames\n","dfs = []\n","\n","# Loop through each selected sheet\n","for sheet in salt_sheets:\n","    # Read the sheet into a DataFrame\n","    df = pd.read_excel(xls, sheet_name=sheet)\n","\n","    # Select columns: 'SIS Login ID' and any column matching 'Lab xx Attendance'\n","    selected_columns = ['SIS Login ID'] + ['Lab Section'] + [col for col in df.columns if 'Attendance' in col]\n","\n","    # Append the filtered DataFrame to the list\n","    dfs.append(df[selected_columns])\n","\n","# Concatenate all DataFrames vertically\n","combined_df = pd.concat(dfs, ignore_index=True)\n","\n","# Save the result to a new CSV file\n","combined_df.to_csv('combined_attendance-salt.csv', index=False)\n","\n","# Download the CSV file\n","files.download('combined_attendance-salt.csv')"],"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":17},"id":"n2dgTZhf-A9T","executionInfo":{"status":"ok","timestamp":1766425117687,"user_tz":300,"elapsed":615,"user":{"displayName":"Natasha Holmes","userId":"01063502562103573622"}},"outputId":"4b632957-b217-403d-b5d9-f4b4cbb1743d"},"execution_count":2,"outputs":[{"output_type":"display_data","data":{"text/plain":["<IPython.core.display.Javascript object>"],"application/javascript":["\n","    async function download(id, filename, size) {\n","      if (!google.colab.kernel.accessAllowed) {\n","        return;\n","      }\n","      const div = document.createElement('div');\n","      const label = document.createElement('label');\n","      label.textContent = `Downloading \"${filename}\": `;\n","      div.appendChild(label);\n","      const progress = document.createElement('progress');\n","      progress.max = size;\n","      div.appendChild(progress);\n","      document.body.appendChild(div);\n","\n","      const buffers = [];\n","      let downloaded = 0;\n","\n","      const channel = await google.colab.kernel.comms.open(id);\n","      // Send a message to notify the kernel that we're ready.\n","      channel.send({})\n","\n","      for await (const message of channel.messages) {\n","        // Send a message to notify the kernel that we're ready.\n","        channel.send({})\n","        if (message.buffers) {\n","          for (const buffer of message.buffers) {\n","            buffers.push(buffer);\n","            downloaded += buffer.byteLength;\n","            progress.value = downloaded;\n","          }\n","        }\n","      }\n","      const blob = new Blob(buffers, {type: 'application/binary'});\n","      const a = document.createElement('a');\n","      a.href = window.URL.createObjectURL(blob);\n","      a.download = filename;\n","      div.appendChild(a);\n","      a.click();\n","      div.remove();\n","    }\n","  "]},"metadata":{}},{"output_type":"display_data","data":{"text/plain":["<IPython.core.display.Javascript object>"],"application/javascript":["download(\"download_e87cf00d-3d18-404d-93fd-01e6a79bf6b7\", \"combined_attendance-salt.csv\", 13583)"]},"metadata":{}}]},{"cell_type":"code","source":["###PEPPER\n","\n","# Replace with the uploaded file name\n","excel_file_path = list(uploaded.keys())[0]\n","\n","# Load the Excel file and get the sheet names\n","xls = pd.ExcelFile(excel_file_path)\n","sheet_names = xls.sheet_names\n","\n","# Filter the first 11 sheets that match the pattern \"PEPPER-4xx\"\n","salt_sheets = [name for name in sheet_names if re.match(r\"PEPPER-4\\d{2}\", name)][:9]\n","\n","# Initialize a list to store DataFrames\n","dfs = []\n","\n","# Loop through each selected sheet\n","for sheet in salt_sheets:\n","    # Read the sheet into a DataFrame\n","    df = pd.read_excel(xls, sheet_name=sheet)\n","\n","    # Select columns: 'SIS Login ID' and any column matching 'Lab xx Attendance'\n","    selected_columns = ['SIS Login ID'] + ['Lab Section'] + [col for col in df.columns if 'Attendance' in col]\n","\n","    # Append the filtered DataFrame to the list\n","    dfs.append(df[selected_columns])\n","\n","# Concatenate all DataFrames vertically\n","combined_df = pd.concat(dfs, ignore_index=True)\n","\n","# Save the result to a new CSV file\n","combined_df.to_csv('combined_attendance-pepper.csv', index=False)\n","\n","# Download the CSV file\n","files.download('combined_attendance-pepper.csv')"],"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":17},"id":"okhXrA9scFrf","executionInfo":{"status":"ok","timestamp":1766414993335,"user_tz":300,"elapsed":169,"user":{"displayName":"Natasha Holmes","userId":"01063502562103573622"}},"outputId":"ee8cc116-477b-43fe-8c75-1534842b3e2e"},"execution_count":3,"outputs":[{"output_type":"display_data","data":{"text/plain":["<IPython.core.display.Javascript object>"],"application/javascript":["\n","    async function download(id, filename, size) {\n","      if (!google.colab.kernel.accessAllowed) {\n","        return;\n","      }\n","      const div = document.createElement('div');\n","      const label = document.createElement('label');\n","      label.textContent = `Downloading \"${filename}\": `;\n","      div.appendChild(label);\n","      const progress = document.createElement('progress');\n","      progress.max = size;\n","      div.appendChild(progress);\n","      document.body.appendChild(div);\n","\n","      const buffers = [];\n","      let downloaded = 0;\n","\n","      const channel = await google.colab.kernel.comms.open(id);\n","      // Send a message to notify the kernel that we're ready.\n","      channel.send({})\n","\n","      for await (const message of channel.messages) {\n","        // Send a message to notify the kernel that we're ready.\n","        channel.send({})\n","        if (message.buffers) {\n","          for (const buffer of message.buffers) {\n","            buffers.push(buffer);\n","            downloaded += buffer.byteLength;\n","            progress.value = downloaded;\n","          }\n","        }\n","      }\n","      const blob = new Blob(buffers, {type: 'application/binary'});\n","      const a = document.createElement('a');\n","      a.href = window.URL.createObjectURL(blob);\n","      a.download = filename;\n","      div.appendChild(a);\n","      a.click();\n","      div.remove();\n","    }\n","  "]},"metadata":{}},{"output_type":"display_data","data":{"text/plain":["<IPython.core.display.Javascript object>"],"application/javascript":["download(\"download_33bfda99-7b7b-457b-8a81-e591e28149da\", \"combined_attendance-pepper.csv\", 13156)"]},"metadata":{}}]},{"cell_type":"code","source":[],"metadata":{"id":"whaKmeUScNZd"},"execution_count":null,"outputs":[]}]}