Last active
January 1, 2024 16:36
-
-
Save zldoty/6e7f921a065cc8bbc980b4aeb7d60268 to your computer and use it in GitHub Desktop.
Page-Performance-Slope.txt
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"nbformat": 4, | |
"nbformat_minor": 0, | |
"metadata": { | |
"colab": { | |
"provenance": [], | |
"include_colab_link": true | |
}, | |
"kernelspec": { | |
"name": "python3", | |
"display_name": "Python 3" | |
}, | |
"language_info": { | |
"name": "python" | |
} | |
}, | |
"cells": [ | |
{ | |
"cell_type": "markdown", | |
"metadata": { | |
"id": "view-in-github", | |
"colab_type": "text" | |
}, | |
"source": [ | |
"<a href=\"https://colab.research.google.com/gist/zldoty/6e7f921a065cc8bbc980b4aeb7d60268/performance-txt.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": null, | |
"metadata": { | |
"id": "WrL_sE19nljt" | |
}, | |
"outputs": [], | |
"source": [ | |
"# Step 1: Import necessary libraries\n", | |
"import pandas as pd\n", | |
"from google.colab import files" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"source": [ | |
"# Step 2: Upload the Excel file\n", | |
"uploaded = files.upload()" | |
], | |
"metadata": { | |
"colab": { | |
"base_uri": "https://localhost:8080/", | |
"height": 73 | |
}, | |
"id": "AV4onSZEnuUQ", | |
"outputId": "2c2f2a1e-c171-41e2-c7a1-be02fa5c0c43" | |
}, | |
"execution_count": null, | |
"outputs": [ | |
{ | |
"output_type": "display_data", | |
"data": { | |
"text/plain": [ | |
"<IPython.core.display.HTML object>" | |
], | |
"text/html": [ | |
"\n", | |
" <input type=\"file\" id=\"files-9ba2a4e0-f569-4cd8-a638-e22833638874\" name=\"files[]\" multiple disabled\n", | |
" style=\"border:none\" />\n", | |
" <output id=\"result-9ba2a4e0-f569-4cd8-a638-e22833638874\">\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 TLF-Input-Slope-Sample-20231222.xlsx to TLF-Input-Slope-Sample-20231222 (1).xlsx\n" | |
] | |
} | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"source": [ | |
"# Step 3: Read the uploaded Excel file into a DataFrame\n", | |
"file_name = list(uploaded.keys())[0]\n", | |
"df = pd.read_excel(file_name)" | |
], | |
"metadata": { | |
"id": "gSiP2wDTnvAD" | |
}, | |
"execution_count": null, | |
"outputs": [] | |
}, | |
{ | |
"cell_type": "code", | |
"source": [ | |
"# Step 4: Extract unique URLs from Column H\n", | |
"unique_urls = df['URL'].unique()\n", | |
"\n", | |
"# Step 5: Create a new DataFrame for the desired output\n", | |
"output_df = pd.DataFrame({'URL': unique_urls})" | |
], | |
"metadata": { | |
"id": "tIez3D0eoM3V" | |
}, | |
"execution_count": null, | |
"outputs": [] | |
}, | |
{ | |
"cell_type": "code", | |
"source": [ | |
"# Step 6: Iterate over unique months and create columns with counts\n", | |
"for month in df['Month of year'].unique():\n", | |
" month_data = df[df['Month of year'] == month]\n", | |
" month_count = month_data[month_data['Position'] <= 10].groupby('URL').size()\n", | |
"\n", | |
" # Convert numpy datetime64 to Python datetime object\n", | |
" month = pd.Timestamp(month).to_pydatetime()\n", | |
"\n", | |
" output_df[month.strftime('%b-%y')] = output_df['URL'].map(month_count).fillna(0)\n" | |
], | |
"metadata": { | |
"id": "otJ52gC_oNUs" | |
}, | |
"execution_count": null, | |
"outputs": [] | |
}, | |
{ | |
"cell_type": "code", | |
"source": [ | |
"# Step 7: Create a column for \"Performance Slope\"\n", | |
"monthly_columns = output_df.columns[1:] # Exclude 'URL' and the last column\n", | |
"output_df['Performance Slope'] = output_df[monthly_columns].apply(lambda row: row.diff().mean(), axis=1)" | |
], | |
"metadata": { | |
"id": "oow9jx-moNvo" | |
}, | |
"execution_count": null, | |
"outputs": [] | |
}, | |
{ | |
"cell_type": "code", | |
"source": [ | |
"# Step 8: Create a column for \"% Difference\"\n", | |
"output_df['% Difference'] = (output_df.iloc[:, -2] - output_df.iloc[:, 1]) / output_df.iloc[:, 1] * 100\n", | |
"\n", | |
"# Step 9: Export the final DataFrame to Excel\n", | |
"output_file_path = \"Output File.xlsx\"\n", | |
"output_df.to_excel(output_file_path, index=False)\n", | |
"\n", | |
"# Step 10: Download the output file\n", | |
"files.download(output_file_path)" | |
], | |
"metadata": { | |
"colab": { | |
"base_uri": "https://localhost:8080/", | |
"height": 17 | |
}, | |
"id": "2ZkDTGAcoOSJ", | |
"outputId": "b8b1d69b-2af3-4094-b20d-a7f178fa113d" | |
}, | |
"execution_count": null, | |
"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_989a75c0-6b2a-40a3-a714-d92816213a0a\", \"Output File.xlsx\", 24392)" | |
] | |
}, | |
"metadata": {} | |
} | |
] | |
} | |
] | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment