4myportfolio_cldfreportbuilder.ipynb

4myportfolio_cldfreportbuilder.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyPoDqEwPrKKqOGAD3X9nZ45",
      "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/UmbreLu/a0fb6cca3f342ad79a40df79e2e08620/4myportfolio_cldfreportbuilder.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install xlsxwriter"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "R5Ubp3AnIw-M",
        "outputId": "4a793868-6632-4b6c-ffef-3c252eddf0c3"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting xlsxwriter\n",
            "  Downloading XlsxWriter-3.1.2-py3-none-any.whl (153 kB)\n",
            "\u001b[?25l     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m0.0/153.0 kB\u001b[0m \u001b[31m?\u001b[0m eta \u001b[36m-:--:--\u001b[0m\r\u001b[2K     \u001b[91m━━━━━━━━\u001b[0m\u001b[90m╺\u001b[0m\u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m30.7/153.0 kB\u001b[0m \u001b[31m1.1 MB/s\u001b[0m eta \u001b[36m0:00:01\u001b[0m\r\u001b[2K     \u001b[91m━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m\u001b[90m╺\u001b[0m\u001b[90m━━━━━━━━━━━━━━━\u001b[0m \u001b[32m92.2/153.0 kB\u001b[0m \u001b[31m1.4 MB/s\u001b[0m eta \u001b[36m0:00:01\u001b[0m\r\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m153.0/153.0 kB\u001b[0m \u001b[31m1.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hInstalling collected packages: xlsxwriter\n",
            "Successfully installed xlsxwriter-3.1.2\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ljnO73xeoNxg"
      },
      "outputs": [],
      "source": [
        "import requests\n",
        "import pandas as pd\n",
        "import json\n",
        "from datetime import date"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "url = \"###########################################\""
      ],
      "metadata": {
        "id": "gSO6mDaa8AJO"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df = pd.read_json(url)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cr5NxLySoVSp",
        "outputId": "1bbadec7-0cf6-4c13-9308-2c0f0bdb9bba"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "<ipython-input-4-fcbc1ffd57af>:1: UserWarning: Parsing dates in DD/MM/YYYY format when dayfirst=False (the default) was specified. This may lead to inconsistently parsed dates! Specify a format to ensure consistent parsing.\n",
            "  df = pd.read_json(url)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df.columns"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "WWBR9Z1Do2Jp",
        "outputId": "99d4861d-fdec-4a7a-bed0-a907a7b1658d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Index(['campaign_id', 'campaign_name', 'campaign_start_datetime',\n",
              "       'campaign_end_datetime', 'placement_id', 'placement_purchase_qty',\n",
              "       'placement_name', 'account_id', 'account_name', 'team_id', 'team_name',\n",
              "       'format_name', 'format_size_width', 'format_size_height', 'site_id',\n",
              "       'site_name', 'site_website', 'site_mediakit_url', 'channel_descr',\n",
              "       'channel_purchased_quantity', 'purchase_type_format', 'creative_id',\n",
              "       'creative_active', 'creative_redirect_url', 'creative_delivery_order',\n",
              "       'creative_delivery_limit', 'creative_dates', 'creative_has_file',\n",
              "       'creative_deleted_at', 'group_name', 'blocklists', 'unique_users',\n",
              "       'impressions', 'unique_impressions', 'views', 'unique_views', 'clicks',\n",
              "       'unique_clicks', 'viewables', 'errors', 'starts', 'firstquartiles',\n",
              "       'midpoints', 'thirdquartiles', 'completes', 'safeframes', 'pixels',\n",
              "       'viewables_starts', 'viewables_firstquartiles', 'viewables_midpoints',\n",
              "       'viewables_thirdquartiles', 'viewables_completes', 'viewability', 'ctr',\n",
              "       'datetime'],\n",
              "      dtype='object')"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df[['campaign_name', 'datetime','group_name', 'site_name','channel_purchased_quantity', 'impressions', 'viewables', 'views','clicks', 'viewability']]"
      ],
      "metadata": {
        "id": "SMbZNnDMo2mI"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df['group_name'].unique()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BpCnPM_Rvh26",
        "outputId": "e8d7bf1d-4824-40ba-d6d2-c5fe5f82a88a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array(['MOTIVO 1', 'MOTIVO 2'], dtype=object)"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "def creative_start_date(dataframe):\n",
        "    return dataframe[['site_name', 'datetime', 'impressions']][dataframe['impressions'] > 10].groupby('site_name').agg({'datetime': 'min'})"
      ],
      "metadata": {
        "id": "iDcmyaowvn1Y"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "creative_start_date(df[df['group_name'] == 'MOTIVO 1'])"
      ],
      "metadata": {
        "id": "stt7tjVhxooa"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "section = df[['campaign_name', 'site_name','channel_purchased_quantity', 'impressions', 'viewables','clicks']]\n",
        "base = section.groupby('site_name').agg({'channel_purchased_quantity': 'max', 'impressions': 'sum', 'viewables': 'sum','clicks': 'sum'}).copy(deep=True)"
      ],
      "metadata": {
        "id": "hvvtNVMkx2on"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "viewabilities = base[['viewables', 'impressions']]\n",
        "viewability = []\n",
        "for _, row in viewabilities.iterrows():\n",
        "    if row[1] != 0:\n",
        "        viewability.append(row[0]/ row[1])\n",
        "    else:\n",
        "        viewability.append('No viewables')\n",
        "base['viewability'] = viewability"
      ],
      "metadata": {
        "id": "ZCAxdu0u93Lp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "base['Taxa de Entrega'] = [(row[1] / row[0]) for index, row in base[['channel_purchased_quantity', 'impressions'] if row[0] else None].iterrows()]"
      ],
      "metadata": {
        "id": "oLP-m-88D1RU"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "base['MOTIVO 1'] = creative_start_date(df[df['group_name'] == 'MOTIVO 1'])\n",
        "base['MOTIVO 2'] = creative_start_date(df[df['group_name'] == 'MOTIVO 2'])\n",
        "base['MOTIVO 1'].fillna('Nunca', inplace=True)\n",
        "base['MOTIVO 2'].fillna('Nunca', inplace=True)"
      ],
      "metadata": {
        "id": "4kqorW36GLET"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "base"
      ],
      "metadata": {
        "id": "lmgJvkscGLph"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "dfs = {'monitoramentoCLDF': base.reset_index().rename({'channel_purchased_quantity': 'contratado'}, axis=1)}"
      ],
      "metadata": {
        "id": "t11X7nxfJLBJ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "writer = pd.ExcelWriter('monitoramentoCLDF_' + date.today().strftime('%d-%m-%Y') + '.xlsx')\n",
        "for sheetname, df in dfs.items():  # loop through `dict` of dataframes\n",
        "    df.to_excel(writer, sheet_name=sheetname, index=False)  # send df to writer\n",
        "    worksheet = writer.sheets[sheetname]  # pull worksheet object\n",
        "    for idx, col in enumerate(df):  # loop through all columns\n",
        "        series = df[col]\n",
        "        max_len = max((\n",
        "            series.astype(str).map(len).max(),  # len of largest item\n",
        "            len(str(series.name))  # len of column name/header\n",
        "            )) + 1  # adding a little extra space\n",
        "        worksheet.set_column(idx, idx, max_len)  # set column width\n",
        "writer.save()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "T1f-rI4DI_0L",
        "outputId": "5e612968-f2d6-4cf6-f33e-bfe344b96757"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "<ipython-input-17-e9ddb8facd18>:12: FutureWarning: save is not part of the public API, usage can give unexpected results and will be removed in a future version\n",
            "  writer.save()\n"
          ]
        }
      ]
    }
  ]
}