abortion_ads_spending.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "## Methodology\n",
        "\n",
        "Data from Courier's Facebook ads were downloaded from [Meta's ad library](https://www.facebook.com/ads/library/?active_status=all&ad_type=political_and_issue_ads&country=US&q=abortion%2C%20dogwood&sort_data[direction]=desc&sort_data[mode]=relevancy_monthly_grouped&search_type=keyword_unordered&media_type=all). Ads sponsered by Courier are labeled as \"Paid by Courier Newsroom, Inc.\" To get ads featuring reproductive content, the data was filtered based on whether the ad message included the words \"abortion\" or \"reprodutive\" or \"Roe v. Wade.\" \n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "9VpYPz6VB-X2"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "YyWNF2TUv5lU"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "pd.options.display.max_colwidth = 100"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#import data \n",
        "df_abortion = pd.read_csv(\"fb_ads_abortion.csv\")\n",
        "df_reproctive = pd.read_csv(\"fb_ads_reproductive.csv\")\n",
        "df_wade = pd.read_csv(\"fb_ads_wade.csv\")"
      ],
      "metadata": {
        "id": "ZUvYphBNv8If"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#combine files and drop duplicates \n",
        "df = pd.concat([df_abortion, df_reproctive, df_wade]).drop_duplicates()"
      ],
      "metadata": {
        "id": "pGC7ZQOIw5Eb"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#clean file \n",
        "df['ad_creation_time'] = df['ad_creation_time'].apply(pd.to_datetime)\n",
        "\n",
        "df['spend_lower_bound'] = df['spend'].str.split(',').str[0]\n",
        "df['spend_upper_bound'] = df['spend'].str.split(',').str[1]\n",
        "\n",
        "df = df.dropna(subset=['spend_upper_bound'])\n",
        "df = df.dropna(subset=['spend_lower_bound'])"
      ],
      "metadata": {
        "id": "NI76ubv2iZTk"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Get estimated spending by averaging 'spend_upper_bound' and 'spend_lower_bound' columns "
      ],
      "metadata": {
        "id": "3w1zRrX6GiSB"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df['spend_upper_bound'] = df['spend_upper_bound'].str.extract('(\\d+)').astype(int)\n",
        "df['spend_lower_bound'] = df['spend_lower_bound'].str.extract('(\\d+)').astype(int)\n",
        "\n",
        "df['spend_lower_bound'] = pd.to_numeric(df['spend_lower_bound']).astype('Int64')\n",
        "df['spend_upper_bound'] = pd.to_numeric(df['spend_upper_bound']).astype('Int64')\n",
        "\n",
        "df['spent_average'] = (df['spend_upper_bound'] + df['spend_lower_bound'] ) /2"
      ],
      "metadata": {
        "id": "lK9gOTI8xZVZ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df.sort_values('ad_creation_time').tail(2)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 344
        },
        "id": "7ijBDeeF-aMr",
        "outputId": "5ea048f5-664a-4477-9629-abdb76e6920d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "       ad_archive_id          page_id        page_name ad_creation_time  \\\n",
              "42  1060416964634532  106487520883333  Cardinal & Pine       2022-10-28   \n",
              "48  1935684343453129  106487520883333  Cardinal & Pine       2022-10-28   \n",
              "\n",
              "   ad_delivery_start_time ad_delivery_stop_time                   byline  \\\n",
              "42             2022-10-28            2022-11-08  Courier Newsroom, Inc.    \n",
              "48             2022-10-28            2022-10-30  Courier Newsroom, Inc.    \n",
              "\n",
              "                                                                                     ad_creative_bodies  \\\n",
              "42  WATCH: The most important two races in North Carolina might be the least talked about.  \\n\\nNort...   \n",
              "48  In November, voters will have the chance to vote in TWO races that will determine control of the...   \n",
              "\n",
              "                                                                      ad_creative_link_titles  \\\n",
              "42  North Carolina's Supreme Court Races Are the Most Important Races No One Is Talking About   \n",
              "48                                                                                        NaN   \n",
              "\n",
              "   ad_creative_link_captions  ...                            spend currency  \\\n",
              "42                       NaN  ...  lower_bound: 0, upper_bound: 99      USD   \n",
              "48                       NaN  ...  lower_bound: 0, upper_bound: 99      USD   \n",
              "\n",
              "                                                                               demographic_distribution  \\\n",
              "42  {\"age\":\"45-54\",\"gender\":\"unknown\",\"percentage\":0.001255},{\"age\":\"45-54\",\"gender\":\"male\",\"percent...   \n",
              "48  {\"age\":\"25-34\",\"gender\":\"unknown\",\"percentage\":0.0016},{\"age\":\"35-44\",\"gender\":\"unknown\",\"percen...   \n",
              "\n",
              "                                                                                     delivery_by_region  \\\n",
              "42  {\"region\":\"Nevada\",\"percentage\":0.00021},{\"region\":\"Aruba\",\"percentage\":0.00021},{\"region\":\"Flor...   \n",
              "48  {\"region\":\"Georgia\",\"percentage\":0.000799},{\"region\":\"New Mexico\",\"percentage\":0.0004},{\"region\"...   \n",
              "\n",
              "   publisher_platforms              estimated_audience_size languages  \\\n",
              "42           instagram  lower_bound: 100, upper_bound: 1000        en   \n",
              "48           instagram  lower_bound: 100, upper_bound: 1000        en   \n",
              "\n",
              "   spend_lower_bound spend_upper_bound  spent_average  \n",
              "42                 0                99           49.5  \n",
              "48                 0                99           49.5  \n",
              "\n",
              "[2 rows x 22 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-d881082e-4484-460b-b23c-b9c905393c57\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>ad_archive_id</th>\n",
              "      <th>page_id</th>\n",
              "      <th>page_name</th>\n",
              "      <th>ad_creation_time</th>\n",
              "      <th>ad_delivery_start_time</th>\n",
              "      <th>ad_delivery_stop_time</th>\n",
              "      <th>byline</th>\n",
              "      <th>ad_creative_bodies</th>\n",
              "      <th>ad_creative_link_titles</th>\n",
              "      <th>ad_creative_link_captions</th>\n",
              "      <th>...</th>\n",
              "      <th>spend</th>\n",
              "      <th>currency</th>\n",
              "      <th>demographic_distribution</th>\n",
              "      <th>delivery_by_region</th>\n",
              "      <th>publisher_platforms</th>\n",
              "      <th>estimated_audience_size</th>\n",
              "      <th>languages</th>\n",
              "      <th>spend_lower_bound</th>\n",
              "      <th>spend_upper_bound</th>\n",
              "      <th>spent_average</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>42</th>\n",
              "      <td>1060416964634532</td>\n",
              "      <td>106487520883333</td>\n",
              "      <td>Cardinal &amp; Pine</td>\n",
              "      <td>2022-10-28</td>\n",
              "      <td>2022-10-28</td>\n",
              "      <td>2022-11-08</td>\n",
              "      <td>Courier Newsroom, Inc.</td>\n",
              "      <td>WATCH: The most important two races in North Carolina might be the least talked about.  \\n\\nNort...</td>\n",
              "      <td>North Carolina's Supreme Court Races Are the Most Important Races No One Is Talking About</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>lower_bound: 0, upper_bound: 99</td>\n",
              "      <td>USD</td>\n",
              "      <td>{\"age\":\"45-54\",\"gender\":\"unknown\",\"percentage\":0.001255},{\"age\":\"45-54\",\"gender\":\"male\",\"percent...</td>\n",
              "      <td>{\"region\":\"Nevada\",\"percentage\":0.00021},{\"region\":\"Aruba\",\"percentage\":0.00021},{\"region\":\"Flor...</td>\n",
              "      <td>instagram</td>\n",
              "      <td>lower_bound: 100, upper_bound: 1000</td>\n",
              "      <td>en</td>\n",
              "      <td>0</td>\n",
              "      <td>99</td>\n",
              "      <td>49.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>48</th>\n",
              "      <td>1935684343453129</td>\n",
              "      <td>106487520883333</td>\n",
              "      <td>Cardinal &amp; Pine</td>\n",
              "      <td>2022-10-28</td>\n",
              "      <td>2022-10-28</td>\n",
              "      <td>2022-10-30</td>\n",
              "      <td>Courier Newsroom, Inc.</td>\n",
              "      <td>In November, voters will have the chance to vote in TWO races that will determine control of the...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>lower_bound: 0, upper_bound: 99</td>\n",
              "      <td>USD</td>\n",
              "      <td>{\"age\":\"25-34\",\"gender\":\"unknown\",\"percentage\":0.0016},{\"age\":\"35-44\",\"gender\":\"unknown\",\"percen...</td>\n",
              "      <td>{\"region\":\"Georgia\",\"percentage\":0.000799},{\"region\":\"New Mexico\",\"percentage\":0.0004},{\"region\"...</td>\n",
              "      <td>instagram</td>\n",
              "      <td>lower_bound: 100, upper_bound: 1000</td>\n",
              "      <td>en</td>\n",
              "      <td>0</td>\n",
              "      <td>99</td>\n",
              "      <td>49.5</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>2 rows × 22 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d881082e-4484-460b-b23c-b9c905393c57')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-d881082e-4484-460b-b23c-b9c905393c57 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-d881082e-4484-460b-b23c-b9c905393c57');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Look at frequency of ads through time "
      ],
      "metadata": {
        "id": "LNNabSHsGoyY"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# frequency of ads grouped by year and month\n",
        "df['ad_creation_time'].groupby([df['ad_creation_time'].dt.year, \n",
        "                                df['ad_creation_time'].dt.month]).agg('count')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bTWmezZ3i474",
        "outputId": "b2c752df-e2e1-478d-c4aa-3226068c22fb"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "ad_creation_time  ad_creation_time\n",
              "2020              8                     2\n",
              "                  10                    1\n",
              "2021              9                     2\n",
              "2022              5                    13\n",
              "                  6                    32\n",
              "                  7                    17\n",
              "                  8                    22\n",
              "                  9                    83\n",
              "                  10                  106\n",
              "Name: ad_creation_time, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Same thing but per newsroom "
      ],
      "metadata": {
        "id": "B95DcZrnG2Pp"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df['ad_creation_time'].groupby([df['page_name'], \n",
        "                                df['ad_creation_time'].dt.year, \n",
        "                                df['ad_creation_time'].dt.month]).agg('count')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "B_6oZF9Vi_qn",
        "outputId": "ba983ab8-8da0-4bd6-ccf5-538e2d3ff739"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "page_name             ad_creation_time  ad_creation_time\n",
              "Cardinal & Pine       2020              10                   1\n",
              "                      2022              10                  24\n",
              "Courier               2022              6                   28\n",
              "Dogwood               2021              9                    2\n",
              "Iowa Starting Line    2022              5                    2\n",
              "                                        10                   7\n",
              "The 'Gander Newsroom  2022              5                    2\n",
              "                                        7                    2\n",
              "                                        8                   10\n",
              "                                        9                   48\n",
              "                                        10                  25\n",
              "The Americano         2022              8                    2\n",
              "                                        9                   10\n",
              "                                        10                   6\n",
              "The Copper Courier    2020              8                    2\n",
              "                      2022              5                    9\n",
              "                                        6                    4\n",
              "                                        7                   15\n",
              "                                        8                   10\n",
              "                                        9                    9\n",
              "                                        10                  11\n",
              "The Keystone          2022              10                  11\n",
              "UpNorthNews           2022              9                   16\n",
              "                                        10                  22\n",
              "Name: ad_creation_time, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 33
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Courier spent approximatemly 5447 on abortion related ads prior to 2022 and\n",
        "###823,913 during. (This is assuming that FB hasn't removed any FB ads from previous years)"
      ],
      "metadata": {
        "id": "0kumkGo5mdst"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_2022 = df.loc[(df['ad_creation_time'] > '2021-12-31')]\n",
        "df_2022['spent_average'].sum()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ABkJu9-Elm1A",
        "outputId": "e56dfce8-78c5-4eb5-ff27-0aa4713fb87a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "823913.5"
            ]
          },
          "metadata": {},
          "execution_count": 34
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_2021 = df.loc[(df['ad_creation_time'] < '2022-01-01')]\n",
        "df_2021['spent_average'].sum()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "E-QcM_RfmV6g",
        "outputId": "cd595d20-36ef-4a67-bd5d-73876f0d3b1f"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "5447.5"
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "###How much has each newsroom spent on facebook ads in total? "
      ],
      "metadata": {
        "id": "2KV-y7HXHB_9"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_2022.groupby([df_2022['page_name'], \n",
        "                 df_2022['ad_creation_time'].dt.year]).agg('sum')\\\n",
        "        .sort_values(by='spent_average', ascending=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 482
        },
        "id": "lH1IsOERYzlz",
        "outputId": "2f76bfcc-5c65-4310-e852-85d2b1a5609c"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                       index       ad_archive_id  \\\n",
              "page_name            ad_creation_time                              \n",
              "The 'Gander Newsroom 2022               7493  111521088851590656   \n",
              "UpNorthNews          2022               3206   52589444233395800   \n",
              "The Copper Courier   2022               6650   62462300902980680   \n",
              "The Americano        2022               2021   26383674775454148   \n",
              "Cardinal & Pine      2022               1112   38660034204485248   \n",
              "The Keystone         2022                617   17038257005066508   \n",
              "Iowa Starting Line   2022                749   16920964529768264   \n",
              "Courier              2022               5278   30461201889493056   \n",
              "\n",
              "                                                 page_id  spend_lower_bound  \\\n",
              "page_name            ad_creation_time                                         \n",
              "The 'Gander Newsroom 2022               9634988738476520             335100   \n",
              "UpNorthNews          2022               3896527774048246             172900   \n",
              "The Copper Courier   2022              19992045895134256              95200   \n",
              "The Americano        2022               1918088355151386              66000   \n",
              "Cardinal & Pine      2022               2555700501199992              23500   \n",
              "The Keystone         2022               1264681841926602              15200   \n",
              "Iowa Starting Line   2022              13582887534309150              13300   \n",
              "Courier              2022               3100984514979960               2300   \n",
              "\n",
              "                                       spend_upper_bound  spent_average  \\\n",
              "page_name            ad_creation_time                                     \n",
              "The 'Gander Newsroom 2022                         420513       377806.5   \n",
              "UpNorthNews          2022                         222962       197931.0   \n",
              "The Copper Courier   2022                         124542       109871.0   \n",
              "The Americano        2022                          85682        75841.0   \n",
              "Cardinal & Pine      2022                          29976        26738.0   \n",
              "The Keystone         2022                          18389        16794.5   \n",
              "Iowa Starting Line   2022                          16791        15045.5   \n",
              "Courier              2022                           5472         3886.0   \n",
              "\n",
              "                                       mid_impressions  \n",
              "page_name            ad_creation_time                   \n",
              "The 'Gander Newsroom 2022                   15422959.5  \n",
              "UpNorthNews          2022                    9289482.0  \n",
              "The Copper Courier   2022                    3834971.0  \n",
              "The Americano        2022                    4053991.5  \n",
              "Cardinal & Pine      2022                    2412988.0  \n",
              "The Keystone         2022                     456994.5  \n",
              "Iowa Starting Line   2022                     903495.5  \n",
              "Courier              2022                     115486.0  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-74273a43-c2ff-4045-9745-1bdfce8c9d1e\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th>index</th>\n",
              "      <th>ad_archive_id</th>\n",
              "      <th>page_id</th>\n",
              "      <th>spend_lower_bound</th>\n",
              "      <th>spend_upper_bound</th>\n",
              "      <th>spent_average</th>\n",
              "      <th>mid_impressions</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>page_name</th>\n",
              "      <th>ad_creation_time</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>The 'Gander Newsroom</th>\n",
              "      <th>2022</th>\n",
              "      <td>7493</td>\n",
              "      <td>111521088851590656</td>\n",
              "      <td>9634988738476520</td>\n",
              "      <td>335100</td>\n",
              "      <td>420513</td>\n",
              "      <td>377806.5</td>\n",
              "      <td>15422959.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>UpNorthNews</th>\n",
              "      <th>2022</th>\n",
              "      <td>3206</td>\n",
              "      <td>52589444233395800</td>\n",
              "      <td>3896527774048246</td>\n",
              "      <td>172900</td>\n",
              "      <td>222962</td>\n",
              "      <td>197931.0</td>\n",
              "      <td>9289482.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>The Copper Courier</th>\n",
              "      <th>2022</th>\n",
              "      <td>6650</td>\n",
              "      <td>62462300902980680</td>\n",
              "      <td>19992045895134256</td>\n",
              "      <td>95200</td>\n",
              "      <td>124542</td>\n",
              "      <td>109871.0</td>\n",
              "      <td>3834971.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>The Americano</th>\n",
              "      <th>2022</th>\n",
              "      <td>2021</td>\n",
              "      <td>26383674775454148</td>\n",
              "      <td>1918088355151386</td>\n",
              "      <td>66000</td>\n",
              "      <td>85682</td>\n",
              "      <td>75841.0</td>\n",
              "      <td>4053991.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Cardinal &amp; Pine</th>\n",
              "      <th>2022</th>\n",
              "      <td>1112</td>\n",
              "      <td>38660034204485248</td>\n",
              "      <td>2555700501199992</td>\n",
              "      <td>23500</td>\n",
              "      <td>29976</td>\n",
              "      <td>26738.0</td>\n",
              "      <td>2412988.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>The Keystone</th>\n",
              "      <th>2022</th>\n",
              "      <td>617</td>\n",
              "      <td>17038257005066508</td>\n",
              "      <td>1264681841926602</td>\n",
              "      <td>15200</td>\n",
              "      <td>18389</td>\n",
              "      <td>16794.5</td>\n",
              "      <td>456994.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Iowa Starting Line</th>\n",
              "      <th>2022</th>\n",
              "      <td>749</td>\n",
              "      <td>16920964529768264</td>\n",
              "      <td>13582887534309150</td>\n",
              "      <td>13300</td>\n",
              "      <td>16791</td>\n",
              "      <td>15045.5</td>\n",
              "      <td>903495.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Courier</th>\n",
              "      <th>2022</th>\n",
              "      <td>5278</td>\n",
              "      <td>30461201889493056</td>\n",
              "      <td>3100984514979960</td>\n",
              "      <td>2300</td>\n",
              "      <td>5472</td>\n",
              "      <td>3886.0</td>\n",
              "      <td>115486.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-74273a43-c2ff-4045-9745-1bdfce8c9d1e')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-74273a43-c2ff-4045-9745-1bdfce8c9d1e button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-74273a43-c2ff-4045-9745-1bdfce8c9d1e');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 36
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Now, see which demographic groups are most likely to see the ads by multiplying impressions with the percentages associated with each demographic group"
      ],
      "metadata": {
        "id": "xAmOBssPFhlT"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def get_mid_impressions(row):\n",
        "  impressions = row.impressions\n",
        "  split = impressions.split(\",\")\n",
        "  lower = split[0].replace('lower_bound: ', '').strip()\n",
        "  if len(split) < 2:\n",
        "    upper = lower\n",
        "  else:\n",
        "    upper = split[1].replace('upper_bound: ', '').strip()\n",
        "  avg = (int(lower) + int(upper))/2\n",
        "  return avg\n",
        "\n",
        "df['mid_impressions'] = df.apply(lambda x: get_mid_impressions(x), axis=1)\n",
        "\n"
      ],
      "metadata": {
        "id": "ckdgAv2uQJQj"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df.reset_index(inplace = True)"
      ],
      "metadata": {
        "id": "Udj4vwP9VZ-2"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "demographics = df[['demographic_distribution', 'mid_impressions']].dropna()\n",
        "demographics.head()"
      ],
      "metadata": {
        "id": "ZF0XoJcjV6ox",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "outputId": "f06dc2ff-2d47-4d9e-a933-2d9eefc6ea2a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                                                              demographic_distribution  \\\n",
              "0  {\"age\":\"18-24\",\"gender\":\"female\",\"percentage\":0.005105},{\"age\":\"45-54\",\"gender\":\"female\",\"percen...   \n",
              "1  {\"age\":\"13-17\",\"gender\":\"female\",\"percentage\":3.2e-5},{\"age\":\"18-24\",\"gender\":\"female\",\"percenta...   \n",
              "2  {\"age\":\"13-17\",\"gender\":\"female\",\"percentage\":4.8e-5},{\"age\":\"35-44\",\"gender\":\"female\",\"percenta...   \n",
              "3  {\"age\":\"13-17\",\"gender\":\"male\",\"percentage\":8.8e-5},{\"age\":\"35-44\",\"gender\":\"male\",\"percentage\":...   \n",
              "4  {\"age\":\"13-17\",\"gender\":\"unknown\",\"percentage\":1.5e-5},{\"age\":\"18-24\",\"gender\":\"female\",\"percent...   \n",
              "\n",
              "   mid_impressions  \n",
              "0          12499.5  \n",
              "1          94999.5  \n",
              "2          64999.5  \n",
              "3          12499.5  \n",
              "4         112499.5  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-e6ab46ca-91ab-47f4-8918-9d8606b60200\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>demographic_distribution</th>\n",
              "      <th>mid_impressions</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>{\"age\":\"18-24\",\"gender\":\"female\",\"percentage\":0.005105},{\"age\":\"45-54\",\"gender\":\"female\",\"percen...</td>\n",
              "      <td>12499.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>{\"age\":\"13-17\",\"gender\":\"female\",\"percentage\":3.2e-5},{\"age\":\"18-24\",\"gender\":\"female\",\"percenta...</td>\n",
              "      <td>94999.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>{\"age\":\"13-17\",\"gender\":\"female\",\"percentage\":4.8e-5},{\"age\":\"35-44\",\"gender\":\"female\",\"percenta...</td>\n",
              "      <td>64999.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>{\"age\":\"13-17\",\"gender\":\"male\",\"percentage\":8.8e-5},{\"age\":\"35-44\",\"gender\":\"male\",\"percentage\":...</td>\n",
              "      <td>12499.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>{\"age\":\"13-17\",\"gender\":\"unknown\",\"percentage\":1.5e-5},{\"age\":\"18-24\",\"gender\":\"female\",\"percent...</td>\n",
              "      <td>112499.5</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-e6ab46ca-91ab-47f4-8918-9d8606b60200')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-e6ab46ca-91ab-47f4-8918-9d8606b60200 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-e6ab46ca-91ab-47f4-8918-9d8606b60200');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 39
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from collections import defaultdict\n",
        "import json\n",
        "di = defaultdict(float)\n",
        "for d in demographics.index:\n",
        "  dd = demographics['demographic_distribution'][d]\n",
        "  imps = demographics['mid_impressions'][d]\n",
        "  if type(dd) == float or type(imps) == float:\n",
        "    print(d)\n",
        "    continue\n",
        "  dd = '[' + dd + ']'\n",
        "  j = json.loads(dd.strip())\n",
        "  for i in j:\n",
        "    age = i['age']\n",
        "    gender = i['gender']\n",
        "    pct = i['percentage']\n",
        "    impressions = pct * imps\n",
        "    di[age+'_'+gender] = di[age+'_'+gender] + impressions\n",
        "\n",
        "  "
      ],
      "metadata": {
        "id": "DVbN3lEiWky4"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###Results: Females in age group 25-34 are most likely to see reproductive ads "
      ],
      "metadata": {
        "id": "Ycnggk6sHI-l"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "sorted([(k,v) for k,v in di.items()], key=lambda x: -x[1])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LDP1M7-0ezFy",
        "outputId": "a8c4b901-ca79-4c40-efef-f73c322169e4"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('25-34_female', 8151267.262387003),\n",
              " ('35-44_female', 6239522.0439695),\n",
              " ('25-34_male', 4094883.4731110027),\n",
              " ('45-54_female', 3601491.8780920007),\n",
              " ('35-44_male', 2903668.420279503),\n",
              " ('18-24_female', 2735455.381654),\n",
              " ('55-64_female', 2543801.215689501),\n",
              " ('65+_female', 1893854.619913499),\n",
              " ('45-54_male', 1673142.6707075012),\n",
              " ('18-24_male', 1119530.7673995001),\n",
              " ('55-64_male', 1033032.6017014999),\n",
              " ('65+_male', 680134.7565944999),\n",
              " ('25-34_unknown', 73299.99318800002),\n",
              " ('35-44_unknown', 62262.96461800001),\n",
              " ('18-24_unknown', 32076.697532500006),\n",
              " ('45-54_unknown', 31117.017942499995),\n",
              " ('55-64_unknown', 20119.4717415),\n",
              " ('65+_unknown', 17456.473872999988),\n",
              " ('13-17_male', 665.1762450000001),\n",
              " ('13-17_female', 542.0470339999999),\n",
              " ('13-17_unknown', 47.814819)]"
            ]
          },
          "metadata": {},
          "execution_count": 27
        }
      ]
    }
  ]
}