{ "cells": [ { "cell_type": "code", "execution_count": 1, "id": "d7a8c4bb", "metadata": {}, "outputs": [], "source": [ "import cloudinary\n", "import cloudinary.uploader\n", "import cloudinary.api\n", "import json\n", "import pandas as pd\n", "from google.oauth2 import service_account\n", "import pandas_gbq\n", "\n", "config = cloudinary.config(\n", " cloud_name = \"###\",\n", " api_key = \"###\",\n", " api_secret = \"###\"\n", " )" ] }, { "cell_type": "code", "execution_count": 2, "id": "2c11cf20", "metadata": {}, "outputs": [], "source": [ "credentials = service_account.Credentials.from_service_account_file('data/credentials.json')" ] }, { "cell_type": "code", "execution_count": 3, "id": "521a5ac0", "metadata": {}, "outputs": [], "source": [ "# Update the in-memory credentials cache (added in pandas-gbq 0.7.0).\n", "pandas_gbq.context.credentials = credentials\n", "\n", "sql = \"\"\"\n", "SELECT Date, Ad_Creative_Image_URL, Ad_Creative_Video_Picture_URL, Ad_Name, Data_Source_Name\n", "FROM `idr-insights.all_cloudinary.all_cloudinary_view`\n", "WHERE DATE(Date) BETWEEN CURRENT_DATE()-14 and CURRENT_DATE()\n", "AND (Ad_Creative_Image_URL IS NOT NULL OR Ad_Creative_Video_Picture_URL IS NOT NULL);\n", "\"\"\"\n", "df = pandas_gbq.read_gbq(sql, project_id=\"idr-insights\", progress_bar_type=None)" ] }, { "cell_type": "code", "execution_count": 4, "id": "ee3ee547", "metadata": {}, "outputs": [], "source": [ "img_df = df.drop(['Ad_Creative_Video_Picture_URL'], axis=1)\n", "vid_df = df.drop(['Ad_Creative_Image_URL'], axis=1)" ] }, { "cell_type": "code", "execution_count": 5, "id": "702873a2", "metadata": {}, "outputs": [], "source": [ "img_df = img_df.dropna()\n", "vid_df = vid_df.dropna()" ] }, { "cell_type": "code", "execution_count": 6, "id": "ff0e38df", "metadata": {}, "outputs": [], "source": [ "img_df['Media_Type'] = \"Image\"\n", "vid_df['Media_Type'] = \"Video\"" ] }, { "cell_type": "code", "execution_count": 7, "id": "2019ce05", "metadata": {}, "outputs": [], "source": [ "df = pd.concat([img_df, vid_df.rename(columns={'Ad_Creative_Video_Picture_URL':'Ad_Creative_Image_URL'})], ignore_index=True)" ] }, { "cell_type": "code", "execution_count": 8, "id": "5ca7e485", "metadata": {}, "outputs": [], "source": [ "df = df.sort_values(['Date','Media_Type'], ascending=([False, True])).reset_index(drop=True)\n", "df = df.drop_duplicates(subset='Ad_Name', keep='first').reset_index(drop=True)" ] }, { "cell_type": "code", "execution_count": 9, "id": "187adede", "metadata": {}, "outputs": [], "source": [ "df.loc[df['Data_Source_Name'].str.contains('ASO'), 'Client'] = 'aso'\n", "df.loc[df['Data_Source_Name'].str.contains('Adaptive Sports'), 'Client'] = 'asc'\n", "df.loc[df['Data_Source_Name'].str.contains('BOG'), 'Client'] = 'bog'\n", "df.loc[df['Data_Source_Name'].str.contains('B&C'), 'Client'] = 'bcc'\n", "df.loc[df['Data_Source_Name'].str.contains('CHRISTUS'), 'Client'] = 'cts'\n", "df.loc[df['Data_Source_Name'].str.contains('Fidelity'), 'Client'] = 'fid'\n", "df.loc[df['Data_Source_Name'].str.contains('Folds'), 'Client'] = 'fhf'\n", "df.loc[df['Data_Source_Name'].str.contains('GOSAFE'), 'Client'] = 'gos'\n", "df.loc[df['Data_Source_Name'].str.contains('Idea Ranch'), 'Client'] = 'idr'\n", "df.loc[df['Data_Source_Name'].str.contains('MEAT'), 'Client'] = 'meat'\n", "df.loc[df['Data_Source_Name'].str.contains('Meprolight'), 'Client'] = 'mep'\n", "df.loc[df['Data_Source_Name'].str.contains('Moultrie'), 'Client'] = 'pob'\n", "df.loc[df['Data_Source_Name'].str.contains('REDARC'), 'Client'] = 'red'\n", "df.loc[df['Data_Source_Name'].str.contains('Splatrball'), 'Client'] = 'gam'\n", "df.loc[df['Data_Source_Name'].str.contains('UA - Fish'), 'Client'] = 'ua'\n", "df.loc[df['Data_Source_Name'].str.contains('UA - Freedom'), 'Client'] = 'ua'\n", "df.loc[df['Data_Source_Name'].str.contains('UA - Hunt'), 'Client'] = 'ua'\n", "df.loc[df['Data_Source_Name'].str.contains('Utica'), 'Client'] = 'usp'\n", "df.loc[df['Data_Source_Name'].str.contains('Bushnell - Standard'), 'Client'] = 'bsh'\n", "df.loc[df['Data_Source_Name'].str.contains('Bushnell Golf'), 'Client'] = 'bsh'\n", "df.loc[df['Data_Source_Name'].str.contains('Blackhawk'), 'Client'] = 'bsh'\n", "df.loc[df['Data_Source_Name'].str.contains('Hoppe'), 'Client'] = 'bsh'\n", "df.loc[df['Data_Source_Name'].str.contains('RCBS'), 'Client'] = 'bsh'\n", "df.loc[df['Data_Source_Name'].str.contains('Primos'), 'Client'] = 'bsh'\n", "df.loc[df['Data_Source_Name'].str.contains('Wild Sheep'), 'Client'] = 'wsf'" ] }, { "cell_type": "code", "execution_count": 10, "id": "241190bd", "metadata": {}, "outputs": [], "source": [ "df[\"Ad_Name\"] = df[\"Ad_Name\"].str.replace(\" \", '-', regex=True)\n", "df[\"Ad_Name\"] = df[\"Ad_Name\"].str.replace(\".\", '-', regex=True)\n", "df[\"Ad_Name\"] = df[\"Ad_Name\"].str.replace(\"_\", '-', regex=True)\n", "df[\"Ad_Name\"] = df[\"Ad_Name\"].str.replace(\"[^a-zA-Z0-9 -]\", '', regex=True)\n", "df[\"Ad_Name\"] = df[\"Ad_Name\"].str.replace(\"---\", '-', regex=True)\n", "df[\"Ad_Name\"] = df[\"Ad_Name\"].str.replace(\"--\", '-', regex=True)\n", "df[\"Ad_Name\"] = df[\"Ad_Name\"].str.replace(\"-$\", \"\", regex=True)" ] }, { "cell_type": "code", "execution_count": 11, "id": "58d0bf6b", "metadata": {}, "outputs": [], "source": [ "image_url = df['Ad_Creative_Image_URL'].to_list()\n", "ad_name = df['Ad_Name'].to_list()\n", "client = df['Client'].to_list()" ] }, { "cell_type": "code", "execution_count": 12, "id": "4caf9386", "metadata": {}, "outputs": [], "source": [ "for img, client, path in zip(image_url, client, ad_name):\n", " try:\n", " cloudinary.uploader.upload(\"%s\" % img, public_id=\"%s/\" % client + \"%s\" % path, unique_filename = False, overwrite = False)\n", " except:\n", " pass" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.7" }, "toc": { "base_numbering": 1, "nav_menu": {}, "number_sections": true, "sideBar": true, "skip_h1_title": false, "title_cell": "Table of Contents", "title_sidebar": "Contents", "toc_cell": false, "toc_position": {}, "toc_section_display": true, "toc_window_display": false }, "varInspector": { "cols": { "lenName": 16, "lenType": 16, "lenVar": 40 }, "kernels_config": { "python": { "delete_cmd_postfix": "", "delete_cmd_prefix": "del ", "library": "var_list.py", "varRefreshCmd": "print(var_dic_list())" }, "r": { "delete_cmd_postfix": ") ", "delete_cmd_prefix": "rm(", "library": "var_list.r", "varRefreshCmd": "cat(var_dic_list()) " } }, "types_to_exclude": [ "module", "function", "builtin_function_or_method", "instance", "_Feature" ], "window_display": false } }, "nbformat": 4, "nbformat_minor": 5 }