data_analysis_tom.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "data analysis tom",
      "provenance": [],
      "toc_visible": true,
      "authorship_tag": "ABX9TyNBANIg0hIeVSVdvLkjar52",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Kunstenpunt/perform_europe/blob/main/data_analysis_tom.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FZu9h_JWfFqp"
      },
      "source": [
        "# Perform Europe Facebook Event Data Analysis\n",
        "In this analysis, we start from the step 2 survey that was performed among more than 2000 respondents. In this survey, the respondents indicated their online presence. We gathered from this indication if these respondents also establish themselves on social media, c.q. Facebook, and if they use the Facebook Events module to promote their work.\n",
        "\n",
        "Using the Facebook Events module is a straightforward way of creating structured data in an international standard (schema.org) that offers linked data. By doing so, the Event is not only widely shareable inside Facebook through the social functions of that platform. Moreover, because of the usage of a de facto standard (schema.org), the structured metadata of such events is also picked up in more general Knowledge Graphs, such as the one by Google. By consequence, the Event will also occur in the \"events nearby\" or \"events coming up\" module that Google offers when searching for cities, venues or performing arts organisations.\n",
        "\n",
        "The implicit nudge of Facebook to generate structured metadata thus has a multiplication effect that increases the visibility of performing arts significantly.\n",
        "\n",
        "The following analysis not only checks which of the respondents of this survey make use of the digital advantages of this approach. This is an interesting insight in the adoption of digital tools in the performing arts sector. In addition, we will also use the available structured metadata to investigate the core question of this survey: what are the characteristics of cross-border mobility in performing arts?\n",
        "\n",
        "To make this analysis, we make use of the data science tool Colaboratory, a Google implemention of Python notebooks, widely used in data science."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rUuGrZg-goEw"
      },
      "source": [
        "## Preparing the data\n",
        "First some technical steps to get the data., starting with loading libraries and mounting the google drive."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "WZu1gbXWZjIR",
        "outputId": "d4db5815-62b6-427f-8ca0-53128a251897"
      },
      "source": [
        "from google.colab import drive\n",
        "from pandas import read_excel, isna, DataFrame, merge, offsets, concat, isnull\n",
        "from re import sub\n",
        "drive.mount(\"/content/gdrive\")"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Drive already mounted at /content/gdrive; to attempt to forcibly remount, call drive.mount(\"/content/gdrive\", force_remount=True).\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2kd_3w1bg7UZ"
      },
      "source": [
        "Now we read in the data, both from the survey as from the scraped structured metadata from Facebook, thanks to their adherence to the schema.org standard."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jCpxv6YddY6K"
      },
      "source": [
        "facebook_data_file = \"/content/gdrive/Shareddrives/Perform Europe/Task 1. Mapping/STEP 2 (Feb-May2021)/FACEBOOK EVENTS ANALYSIS/data.xlsx\"\n",
        "pe_survey_data_file = \"/content/gdrive/Shareddrives/Perform Europe/Task 1. Mapping/STEP 2 (Feb-May2021)/FACEBOOK EVENTS ANALYSIS/performeurope.xlsx\"\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RENsCHWBearx"
      },
      "source": [
        "pe_survey_data = read_excel(pe_survey_data_file)\n",
        "facebook_data = read_excel(facebook_data_file)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QSMw5cU6hHve"
      },
      "source": [
        "Let's prepare the data. From a first exercise, we distinguished the following data points that we need to collect from the available data:\n",
        "\n",
        "- Perform Europe Survey\n",
        " - respondent_id (CHECK)\n",
        " - in which country is the respondent based\n",
        "   - available values in Q2 (CHECK)\n",
        "   - clustered into macroregions (CHECK)\n",
        " - primary core activity\n",
        "   - production (artistic creation, production, artist mangement), presentation (question 4) (CHECK)\n",
        " - how long is the respondent active in the sector\n",
        "   - less than 5 years, between 5 - 10 years , more than 10 years (question 8) (CHECK)\n",
        " - what is the main discipline of the respondent\n",
        "   - theatre or music theatre, dance, circus and/or street arts, performance, multidisciplinary (question 3) (CHECK)\n",
        " - is the respondent a freelancer or organisation\n",
        "   - freelancer, organisation (question 6) (CHECK)\n",
        " - urban/rural focus of the performing arts organisation\n",
        "   - urban, rural (question 7) (CHECK)\n",
        " - amount of employees\n",
        "   - less than 5, between 5 - 15, between 15 - 30, more than 30 (question 10) (CHECK)\n",
        " - legal status\n",
        "   - for-profit, non-profit, public institution, social enterprise (question 11) (CHECK)\n",
        " - how is the respondent funded \n",
        "   - public, private, mixed, self, crowd (question 12) (CHECK)\n",
        " - where is the work of the respondent already shown\n",
        "   - available values in Q 16 (CHECK)\n",
        "   - clustered by macroregions (CHECK)\n",
        "- Facebook\n",
        " - datum (CHECK)\n",
        " - respondent_id (CHECK) \n",
        " - event_id (CHECK)\n",
        " - location name (CHECK)\n",
        " - location city \n",
        " - location country (CHECK)\n",
        " - location macroregion (CHECK)\n",
        "- External data\n",
        " - amount of inhabitants per country\n",
        " - amount of inhabitants per macroregion"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jCLAOqc-2AY_"
      },
      "source": [
        "### Rural/urban\n",
        "Is the working focus of the respondent urban or rural?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cbLJ-dBM3y2H",
        "outputId": "a71125c2-7a48-4085-b6a1-5c5d33e03a3b"
      },
      "source": [
        "areas = [column for column in pe_survey_data.columns if column.startswith(\"7.\")]\n",
        "print(areas)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "[\"7.1. {{tooltip 'An urban area' 'Urban areas cover cities, towns and suburbs'}}\", \"7.2. {{tooltip 'A rural area' 'Rural areas are all areas outside the urban cluster'}}\"]\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "yHoiAHGO39Ld",
        "outputId": "00a44bba-4fe2-4235-c3a4-f0e54af3a819"
      },
      "source": [
        "area_records = []\n",
        "for row in pe_survey_data.iterrows():\n",
        "  respondent_id = row[1][\"Respondent\"]\n",
        "  values = []\n",
        "  for area in areas:\n",
        "    if not isna(row[1][area]):\n",
        "      area_clean = \"urban\" if area.startswith(\"7.1\") else \"rural\"\n",
        "      values.append(area_clean)\n",
        "  record = {\n",
        "      'respondent_id': respondent_id,\n",
        "      'area': sorted(values),\n",
        "      'area_str': \", \".join(sorted(values))\n",
        "  }\n",
        "  area_records.append(record)\n",
        "area_df = DataFrame.from_records(area_records)\n",
        "area_df.set_index(\"respondent_id\", inplace=True)\n",
        "print(area_df.head())"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "                  area area_str\n",
            "respondent_id                  \n",
            "57             [urban]    urban\n",
            "58             [urban]    urban\n",
            "59                  []         \n",
            "60                  []         \n",
            "61             [urban]    urban\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7Af_fyaH2OM2"
      },
      "source": [
        "### Core activity (production/presentation)\n",
        "What is the core activity of the respondent?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "AaymlD0x2WFH",
        "outputId": "96d118b4-32bc-4af2-b9e6-dd11184ebb7e"
      },
      "source": [
        "core_activities = pe_survey_data['4. Which is your primary core activity within the performing arts field?'].unique()\n",
        "print(core_activities)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "['Artist management, promotion and representation' 'Artistic creation' nan\n",
            " 'Production' 'Presentation and programming (might include co-production)']\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Dv5xc3W42pYv"
      },
      "source": [
        "Let's map this:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4vDNERm02sGm"
      },
      "source": [
        "activity_mapping = {\n",
        "    'Artist management, promotion and representation': 'production',\n",
        "    'Artistic creation': 'production',\n",
        "    'Production': 'production',\n",
        "    'Presentation and programming (might include co-production)': 'presentation'\n",
        " }"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_60L8XzF25z2",
        "outputId": "3ce7fabd-f5dd-4629-fd1c-f8c01bb7c03f"
      },
      "source": [
        "prod_pres_records = []\n",
        "for row in pe_survey_data.iterrows():\n",
        "  respondent_id = row[1][\"Respondent\"]\n",
        "  core_activity = row[1]['4. Which is your primary core activity within the performing arts field?']\n",
        "  value = activity_mapping[core_activity] if not isna(core_activity) else None\n",
        "  record = {\n",
        "      'respondent_id': respondent_id,\n",
        "      'core_activity': value\n",
        "  }\n",
        "  prod_pres_records.append(record)\n",
        "core_activity_df = DataFrame.from_records(prod_pres_records)\n",
        "core_activity_df.set_index(\"respondent_id\", inplace=True)\n",
        "print(core_activity_df.head())"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "              core_activity\n",
            "respondent_id              \n",
            "57               production\n",
            "58               production\n",
            "59                     None\n",
            "60                     None\n",
            "61               production\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ie7kanFHqaCh"
      },
      "source": [
        "### Countries\n",
        "Let's get the country information and the information about the macroregions."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "kByOPapzrnCQ",
        "outputId": "762a6478-a9ac-425d-890a-aae2a79b981a"
      },
      "source": [
        "based_in_countries = [column for column in pe_survey_data.columns if column.startswith(\"2.\")]\n",
        "print(based_in_countries)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "['2.1. Albania', '2.2. Armenia', '2.3. Austria', '2.4. Belgium', '2.5. Bosnia and Herzegovina', '2.6. Bulgaria', '2.7. Croatia', '2.8. Republic of Cyprus', '2.9. Czech Republic', '2.10. Denmark', '2.11. Estonia', '2.12. Finland', '2.13. France', '2.14. Georgia', '2.15. Germany', '2.16. Greece', '2.17. Hungary', '2.18. Iceland', '2.19. Ireland', '2.20. Italy', '2.21. Kosovo', '2.22. Latvia', '2.23. Lithuania', '2.24. Luxembourg', '2.25. Malta', '2.26. Moldova', '2.27. Montenegro', '2.28. Netherlands', '2.29. North Macedonia', '2.30. Norway', '2.31. Poland', '2.32. Portugal', '2.33. Republic of Serbia', '2.34. Romania', '2.35. Slovakia', '2.36. Slovenia', '2.37. Spain', '2.38. Sweden', '2.39. Tunisia', '2.40. Ukraine', '2.41. United Kingdom']\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "04QstjdsrrH-"
      },
      "source": [
        "For each country, we need a macroregion."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LSTX4Iu2rvXO"
      },
      "source": [
        "macroregions_mapping = {\n",
        "     'Albania': 'Balkans', \n",
        "     'Armenia': 'Eastern Partnership + Tunisia', \n",
        "     'Austria': 'Western Europe', \n",
        "     'Belgium': 'Western Europe', \n",
        "     'Bosnia and Herzegovina': 'Balkans', \n",
        "     'Bulgaria': 'Eastern Europe', \n",
        "     'Croatia': 'Eastern Europe', \n",
        "     'Republic of Cyprus': 'Southern Europe', \n",
        "     'Czech Republic': 'Eastern Europe', \n",
        "     'Denmark': 'Northern Europe', \n",
        "     'Estonia': 'Northern Europe', \n",
        "     'Finland': 'Northern Europe', \n",
        "     'France': 'Western Europe', \n",
        "     'Georgia': 'Eastern Partnership + Tunisia', \n",
        "     'Germany': 'Western Europe', \n",
        "     'Greece': 'Southern Europe', \n",
        "     'Hungary': 'Eastern Europe', \n",
        "     'Iceland': 'Northern Europe', \n",
        "     'Ireland': 'Western Europe', \n",
        "     'Italy': 'Southern Europe', \n",
        "     'Kosovo': 'Balkans',\n",
        "     'Latvia': 'Northern Europe', \n",
        "     'Lithuania': 'Northern Europe', \n",
        "     'Luxembourg': 'Western Europe', \n",
        "     'Malta': 'Southern Europe', \n",
        "     'Moldova': 'Eastern Partnership + Tunisia', \n",
        "     'Montenegro': 'Balkans', \n",
        "     'Netherlands': 'Western Europe', \n",
        "     'North Macedonia': 'Balkans', \n",
        "     'Norway': 'Northern Europe', \n",
        "     'Poland': 'Eastern Europe', \n",
        "     'Portugal': 'Southern Europe', \n",
        "     'Republic of Serbia': 'Balkans', \n",
        "     'Romania': 'Eastern Europe', \n",
        "     'Slovakia': 'Eastern Europe', \n",
        "     'Slovenia': 'Eastern Europe', \n",
        "     'Spain': 'Southern Europe', \n",
        "     'Sweden': 'Northern Europe', \n",
        "     'Tunisia': 'Eastern Partnership + Tunisia', \n",
        "     'Ukraine': 'Eastern Partnership + Tunisia', \n",
        "     'United Kingdom': 'Western Europe',\n",
        "     'Countries outside Creative Europe': 'Countries outside Creative Europe'\n",
        "     }"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RstZXeUB65an"
      },
      "source": [
        "And we also take the market size in function of the population (per million)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "v6rtj6lx3X9L"
      },
      "source": [
        "market_size_mapping = {\n",
        "     'Albania': 2.8, \n",
        "     'Armenia': 2.9, \n",
        "     'Austria': 8.9, \n",
        "     'Belgium': 11, \n",
        "     'Bosnia and Herzegovina': 3, \n",
        "     'Bulgaria': 7, \n",
        "     'Croatia': 4, \n",
        "     'Republic of Cyprus': 1, \n",
        "     'Czech Republic': 11, \n",
        "     'Denmark': 6, \n",
        "     'Estonia': 1.3, \n",
        "     'Finland': 5.5, \n",
        "     'France': 67, \n",
        "     'Georgia': 3.7, \n",
        "     'Germany': 83, \n",
        "     'Greece': 10.7, \n",
        "     'Hungary': 9.7, \n",
        "     'Iceland': 1, \n",
        "     'Ireland': 4.9, \n",
        "     'Italy': 60, \n",
        "     'Kosovo': 1.9,\n",
        "     'Latvia': 1.9, \n",
        "     'Lithuania': 2.8, \n",
        "     'Luxembourg': 1, \n",
        "     'Malta': 1, \n",
        "     'Moldova': 2.6, \n",
        "     'Montenegro': 1, \n",
        "     'Netherlands': 17.2, \n",
        "     'North Macedonia': 2, \n",
        "     'Norway': 5.3, \n",
        "     'Poland': 38, \n",
        "     'Portugal': 10, \n",
        "     'Republic of Serbia': 6.9, \n",
        "     'Romania': 19.4, \n",
        "     'Slovakia': 5.4, \n",
        "     'Slovenia': 2, \n",
        "     'Spain': 47, \n",
        "     'Sweden': 10.2, \n",
        "     'Tunisia': 11.7, \n",
        "     'Ukraine': 44.4, \n",
        "     'United Kingdom': 66.6\n",
        "     }"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "78VNuSRon9Kq",
        "outputId": "90f89738-0cd2-422f-840c-bde62548ab70"
      },
      "source": [
        "based_in_records = []\n",
        "for row in pe_survey_data.iterrows():\n",
        "  respondent_id = row[1][\"Respondent\"]\n",
        "  based_in = []\n",
        "  based_in_macroregion = set()\n",
        "  market_size = set()\n",
        "  for country in based_in_countries:\n",
        "    if not isna(row[1][country]):\n",
        "      country_clean = sub(r\"\\d\\.\\d+?\\.\\s\", \"\", country)\n",
        "      based_in.append(country_clean)\n",
        "      based_in_macroregion.add(macroregions_mapping[country_clean])\n",
        "      market_size.add(\"large internal market\" if market_size_mapping[country_clean] > 30 else \"small internal market\")\n",
        "  record = {\n",
        "      'respondent_id': respondent_id,\n",
        "      'based_in_country': sorted(based_in),\n",
        "      'based_in_country_str': \", \".join(sorted(based_in)),\n",
        "      'based_in_macroregion': sorted(based_in_macroregion),\n",
        "      'based_in_macroregion_str': \", \".join(sorted(based_in_macroregion)),\n",
        "      'market_size': sorted(market_size),\n",
        "      'market_size_str': \", \".join(sorted(market_size))\n",
        "  }\n",
        "  based_in_records.append(record)\n",
        "based_in_df = DataFrame.from_records(based_in_records)\n",
        "based_in_df.set_index(\"respondent_id\", inplace=True)\n",
        "print(based_in_df.head())"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "              based_in_country  ...        market_size_str\n",
            "respondent_id                   ...                       \n",
            "57                   [Finland]  ...  small internal market\n",
            "58                  [Slovenia]  ...  small internal market\n",
            "59                 [Lithuania]  ...  small internal market\n",
            "60                    [Greece]  ...  small internal market\n",
            "61                    [France]  ...  large internal market\n",
            "\n",
            "[5 rows x 6 columns]\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "A3XGGjFZyh80",
        "outputId": "1271fa5d-7fa7-4558-da51-dba954d9359c"
      },
      "source": [
        "shown_in_countries = [column for column in pe_survey_data.columns if column.startswith(\"16.\")]\n",
        "shown_in_records = []\n",
        "for row in pe_survey_data.iterrows():\n",
        "  respondent_id = row[1][\"Respondent\"]\n",
        "  shown_in = []\n",
        "  shown_in_macroregion = set()\n",
        "  for country in shown_in_countries:\n",
        "    if not isna(row[1][country]):\n",
        "      country_clean = sub(r\"\\d\\d\\.\\d+?\\.\\s\", \"\", country)\n",
        "      shown_in.append(country_clean)\n",
        "      if not country_clean == \"Countries outside Creative Europe\":\n",
        "        shown_in_macroregion.add(macroregions_mapping[country_clean])\n",
        "      else:\n",
        "        shown_in_macroregion.add(\"Countries outside Creative Europe\")\n",
        "  record = {\n",
        "      'respondent_id': respondent_id,\n",
        "      'shown_in_country': sorted(shown_in),\n",
        "      'shown_in_country_str': \", \".join(sorted(shown_in)),\n",
        "      'shown_in_macroregion': sorted(shown_in_macroregion),\n",
        "      'shown_in_macroregion_str': \", \".join(sorted(shown_in_macroregion))\n",
        "  }\n",
        "  shown_in_records.append(record)\n",
        "shown_in_df = DataFrame.from_records(shown_in_records)\n",
        "shown_in_df.set_index(\"respondent_id\", inplace=True)\n",
        "print(shown_in_df.head())"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "                                                shown_in_country  ...                           shown_in_macroregion_str\n",
            "respondent_id                                                     ...                                                   \n",
            "57             [Austria, Countries outside Creative Europe, F...  ...  Countries outside Creative Europe, Eastern Eur...\n",
            "58             [Austria, Bosnia and Herzegovina, Croatia, Hun...  ...  Balkans, Eastern Europe, Southern Europe, West...\n",
            "59                                                            []  ...                                                   \n",
            "60                                                            []  ...                                                   \n",
            "61                                                            []  ...                                                   \n",
            "\n",
            "[5 rows x 4 columns]\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LsJ5Pzqx0FfB"
      },
      "source": [
        "### Discipline\n",
        "Via question 3, we can detect which are the disciplines of the respondent."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "OVWV6Rxq0cbh",
        "outputId": "1849c9d7-c9c6-48a0-d049-cb10472f31ce"
      },
      "source": [
        "disciplines = [column for column in pe_survey_data.columns if column.startswith(\"3.\")]\n",
        "print(disciplines)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "['3.1. Theatre or music theatre', '3.2. Dance', '3.3. Circus and/or street arts', \"3.4. {{tooltip 'Performance' 'Performance must be only considered as a subcategory within the performing arts field (not visual arts or literature)'}}\", '3.5. Multidisciplinary within the performing arts (music excluded)']\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "l1u_0Xnq0oOw"
      },
      "source": [
        "Let's bring this together in a variable."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "z0XnMfGs0ldQ",
        "outputId": "11710741-8450-4ce9-b354-0a96325aa2c7"
      },
      "source": [
        "discipline_records = []\n",
        "for row in pe_survey_data.iterrows():\n",
        "  respondent_id = row[1][\"Respondent\"]\n",
        "  values = []\n",
        "  for discipline in disciplines:\n",
        "    if not isna(row[1][discipline]):\n",
        "      discipline_clean = sub(r\"\\d\\.\\d+?\\.\\s\", \"\", discipline)\n",
        "      if discipline_clean == \"{{tooltip 'Performance' 'Performance must be only considered as a subcategory within the performing arts field (not visual arts or literature)'}}\":\n",
        "        discipline_clean = \"Performance\"\n",
        "      values.append(discipline_clean)\n",
        "  record = {\n",
        "      'respondent_id': respondent_id,\n",
        "      'disciplines': sorted(values),\n",
        "      'disciplines_str': \", \".join(sorted(values))\n",
        "  }\n",
        "  discipline_records.append(record)\n",
        "discipline_df = DataFrame.from_records(discipline_records)\n",
        "discipline_df.set_index(\"respondent_id\", inplace=True)\n",
        "print(discipline_df.head())"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "                                                     disciplines                                    disciplines_str\n",
            "respondent_id                                                                                                      \n",
            "57                                                       [Dance]                                              Dance\n",
            "58                                                       [Dance]                                              Dance\n",
            "59             [Multidisciplinary within the performing arts ...  Multidisciplinary within the performing arts (...\n",
            "60             [Multidisciplinary within the performing arts ...  Multidisciplinary within the performing arts (...\n",
            "61             [Multidisciplinary within the performing arts ...  Multidisciplinary within the performing arts (...\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HvCXWXKf4kmZ"
      },
      "source": [
        "### Facebook macroregion"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GeLAa4_l4jm2",
        "outputId": "5e562c2e-d444-41ed-c28a-fe60fdaec064"
      },
      "source": [
        "!pip install pycountry\n",
        "import pycountry\n",
        "fb_countries = facebook_data['location_address_country'].unique()\n",
        "print(fb_countries)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Requirement already satisfied: pycountry in /usr/local/lib/python3.7/dist-packages (20.7.3)\n",
            "['DK' nan 'IE' 'SE' 'FR' 'NL' 'IT' 'GR' 'CY' 'FI' 'AT' 'LV' 'DE' 'BE' 'US'\n",
            " 'ES' 'HR' 'CH' 'RS' 'NO' 'PT' 'HU' 'PL' 'CA' 'GB' 'AR' 'MX' 'IL' 'TN'\n",
            " 'CZ' 'SK' 'SI' 'LU' 'UA' 'RO' 'MU' 'AU' 'GE' 'IS' 'LT' 'EG' 'MK' 'BG'\n",
            " 'FO' 'MT' 'LB' 'EE' 'XK' 'TW' 'BO' 'TR' 'CL' 'RU' 'JP' 'SG' 'TH' 'BR'\n",
            " 'ID']\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "87Es6HHA5B9x"
      },
      "source": [
        "fb_macroregions_mapping = {\n",
        "     'Albania': 'Balkans', \n",
        "     'Armenia': 'Eastern Partnership + Tunisia', \n",
        "     'Austria': 'Western Europe', \n",
        "     'Belgium': 'Western Europe', \n",
        "     'Bosnia and Herzegovina': 'Balkans', \n",
        "     'Bulgaria': 'Eastern Europe', \n",
        "     'Croatia': 'Eastern Europe', \n",
        "     'Cyprus': 'Southern Europe', \n",
        "     'Czech Republic': 'Eastern Europe', \n",
        "     'Czechia': 'Eastern Europe', \n",
        "     'Denmark': 'Northern Europe', \n",
        "     'Estonia': 'Northern Europe', \n",
        "     'Finland': 'Northern Europe', \n",
        "     'France': 'Western Europe', \n",
        "     'Georgia': 'Eastern Partnership + Tunisia', \n",
        "     'Germany': 'Western Europe', \n",
        "     'Greece': 'Southern Europe', \n",
        "     'Hungary': 'Eastern Europe', \n",
        "     'Iceland': 'Northern Europe', \n",
        "     'Ireland': 'Western Europe', \n",
        "     'Italy': 'Southern Europe', \n",
        "     'Kosovo': 'Balkans',\n",
        "     'Latvia': 'Northern Europe', \n",
        "     'Lithuania': 'Northern Europe', \n",
        "     'Luxembourg': 'Western Europe', \n",
        "     'Malta': 'Southern Europe', \n",
        "     'Moldova': 'Eastern Partnership + Tunisia', \n",
        "     'Montenegro': 'Balkans', \n",
        "     'Netherlands': 'Western Europe', \n",
        "     'North Macedonia': 'Balkans', \n",
        "     'Norway': 'Northern Europe', \n",
        "     'Poland': 'Eastern Europe', \n",
        "     'Portugal': 'Southern Europe', \n",
        "     'Serbia': 'Balkans', \n",
        "     'Romania': 'Eastern Europe', \n",
        "     'Slovakia': 'Eastern Europe', \n",
        "     'Slovenia': 'Eastern Europe', \n",
        "     'Spain': 'Southern Europe', \n",
        "     'Sweden': 'Northern Europe', \n",
        "     'Tunisia': 'Eastern Partnership + Tunisia', \n",
        "     'Ukraine': 'Eastern Partnership + Tunisia', \n",
        "     'United Kingdom': 'Western Europe',\n",
        "     'United States': 'Countries outside Creative Europe',\n",
        "     'Switzerland': 'Countries outside Creative Europe',\n",
        "     'Canada': 'Countries outside Creative Europe',\n",
        "     'Argentina': 'Countries outside Creative Europe',\n",
        "     'Mexico': 'Countries outside Creative Europe',\n",
        "     'Israel': 'Countries outside Creative Europe',\n",
        "     'Lebanon': 'Countries outside Creative Europe',\n",
        "     'Taiwan, Province of China': 'Countries outside Creative Europe',\n",
        "     'Bolivia, Plurinational State of': 'Countries outside Creative Europe',\n",
        "     'Turkey': 'Countries outside Creative Europe',\n",
        "     'Chile': 'Countries outside Creative Europe',\n",
        "     'Australia': 'Countries outside Creative Europe',\n",
        "     'Russian Federation': 'Countries outside Creative Europe',\n",
        "     'Japan': 'Countries outside Creative Europe',\n",
        "     'Singapore': 'Countries outside Creative Europe',\n",
        "     'Thailand': 'Countries outside Creative Europe',\n",
        "     'Brazil': 'Countries outside Creative Europe',\n",
        "     'Indonesia': 'Countries outside Creative Europe',\n",
        "     'Mauritius': 'Countries outside Creative Europe',\n",
        "     'Egypt': 'Countries outside Creative Europe',\n",
        "     'Faroe Islands': 'Countries outside Creative Europe',\n",
        "     'Kosovo': 'Balkans',\n",
        "     }"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "dNlDhdIV5svG",
        "outputId": "c8dde076-c4dd-4326-a53e-3d7c45568927"
      },
      "source": [
        "shown_in_records_fb = []\n",
        "for row in facebook_data.iterrows():\n",
        "  platform_id = row[1][\"platform_id\"]\n",
        "  country = row[1]['location_address_country']\n",
        "  if not isna(country):\n",
        "    if country == \"XK\":\n",
        "      country_clean = \"Kosovo\"\n",
        "    else:\n",
        "      country_clean = pycountry.countries.get(alpha_2=country)\n",
        "      country_clean = country_clean.name\n",
        "    record = {\n",
        "        'platform_id': platform_id,\n",
        "        'fb_shown_in_country': country_clean,\n",
        "        'fb_shown_in_macroregion': fb_macroregions_mapping[country_clean]\n",
        "    }\n",
        "  shown_in_records_fb.append(record)\n",
        "shown_in_fb_df = DataFrame.from_records(shown_in_records_fb)\n",
        "shown_in_fb_df.set_index(\"platform_id\", inplace=True)\n",
        "print(shown_in_fb_df.head())"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "                 fb_shown_in_country fb_shown_in_macroregion\n",
            "platform_id                                                 \n",
            "312118346518788              Denmark         Northern Europe\n",
            "1602125709937098             Denmark         Northern Europe\n",
            "650277199170858              Denmark         Northern Europe\n",
            "650277199170858              Denmark         Northern Europe\n",
            "350994285633781              Denmark         Northern Europe\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cGiGZfFn87Bb"
      },
      "source": [
        "### Combining the data\n",
        "Now that we have all variables prepared, we can make an integrated dataset. Let's first add the macroregions to facebook."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "c2oxJe-qOJoN",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "8140184f-222e-453a-faba-80b638fe231b"
      },
      "source": [
        "facebook_data.set_index(\"platform_id\", inplace=True)\n",
        "facebook_data_with_macroregions = facebook_data.join(shown_in_fb_df).reset_index()\n",
        "facebook_data_with_macroregions.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<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>platform_id</th>\n",
              "      <th>Unnamed: 0</th>\n",
              "      <th>platform</th>\n",
              "      <th>name</th>\n",
              "      <th>date</th>\n",
              "      <th>until_date</th>\n",
              "      <th>location_name</th>\n",
              "      <th>location_address_street</th>\n",
              "      <th>location_address_zip</th>\n",
              "      <th>location_address_city</th>\n",
              "      <th>location_address_country</th>\n",
              "      <th>description</th>\n",
              "      <th>respondent_id</th>\n",
              "      <th>date_parsed</th>\n",
              "      <th>fb_shown_in_country</th>\n",
              "      <th>fb_shown_in_macroregion</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>100148020514218</td>\n",
              "      <td>18926</td>\n",
              "      <td>facebook</td>\n",
              "      <td>Autoctonos / Présentation publique Ayelen Parolin</td>\n",
              "      <td>2017-03-09</td>\n",
              "      <td>2017-03-09</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Nous sommes tous des autochtones, et tous des ...</td>\n",
              "      <td>916</td>\n",
              "      <td>2017-03-09</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>100186340734716</td>\n",
              "      <td>19473</td>\n",
              "      <td>facebook</td>\n",
              "      <td>Alleen - Sara De Roo &amp; Fikry El Azzouzi / tg STAN</td>\n",
              "      <td>2017-10-17</td>\n",
              "      <td>2017-10-18</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Selectie @[396205347229383:274:Het TheaterFest...</td>\n",
              "      <td>948</td>\n",
              "      <td>2017-10-17</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>100280486986476</td>\n",
              "      <td>5664</td>\n",
              "      <td>facebook</td>\n",
              "      <td>Mapped Productions - NOVA INSULA</td>\n",
              "      <td>2015-07-01</td>\n",
              "      <td>2015-07-04</td>\n",
              "      <td>Mole Vanvitelliana, Ancona</td>\n",
              "      <td>Banchina Giovanni da Chio, 60121 Ancona</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Ancona, Marche, Italië</td>\n",
              "      <td>IT</td>\n",
              "      <td>Oggi alle ore 19, si inaugura Nova Insula. Si ...</td>\n",
              "      <td>1659</td>\n",
              "      <td>2015-07-01</td>\n",
              "      <td>Italy</td>\n",
              "      <td>Southern Europe</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>100548043729175</td>\n",
              "      <td>13043</td>\n",
              "      <td>facebook</td>\n",
              "      <td>Remek hang a futkosásban az Átrium Film-Színhá...</td>\n",
              "      <td>2016-09-07</td>\n",
              "      <td>2016-09-07</td>\n",
              "      <td>Átrium</td>\n",
              "      <td>Margit körút 55.</td>\n",
              "      <td>H-1024</td>\n",
              "      <td>Boedapest, Hongarije</td>\n",
              "      <td>HU</td>\n",
              "      <td>Szeptember 7-én - szerdán - 19.00-kor az idei ...</td>\n",
              "      <td>555</td>\n",
              "      <td>2016-09-07</td>\n",
              "      <td>Hungary</td>\n",
              "      <td>Eastern Europe</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>100590720764575</td>\n",
              "      <td>17151</td>\n",
              "      <td>facebook</td>\n",
              "      <td>Scena madre* - Maison de la musique de Nanterre</td>\n",
              "      <td>2018-02-16</td>\n",
              "      <td>2018-02-16</td>\n",
              "      <td>Maison de la musique de Nanterre</td>\n",
              "      <td>8 rue des Anciennes-Mairies</td>\n",
              "      <td>92000</td>\n",
              "      <td>Nanterre, Frankrijk</td>\n",
              "      <td>FR</td>\n",
              "      <td>SCENA MADRE*\\n——————\\n\\nIntrigue, suspense et ...</td>\n",
              "      <td>850</td>\n",
              "      <td>2018-02-16</td>\n",
              "      <td>France</td>\n",
              "      <td>Western Europe</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       platform_id  Unnamed: 0  ... fb_shown_in_country fb_shown_in_macroregion\n",
              "0  100148020514218       18926  ...                 NaN                     NaN\n",
              "1  100186340734716       19473  ...                 NaN                     NaN\n",
              "2  100280486986476        5664  ...               Italy         Southern Europe\n",
              "3  100548043729175       13043  ...             Hungary          Eastern Europe\n",
              "4  100590720764575       17151  ...              France          Western Europe\n",
              "\n",
              "[5 rows x 16 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 329
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pMVGSmx9SDjf"
      },
      "source": [
        "And then we add the variables to the survey data."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yfp69lzvSHW1",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "5825c9ee-8178-4abb-b886-611afe4217ea"
      },
      "source": [
        "full_data = area_df.join([core_activity_df, based_in_df, shown_in_df, discipline_df])\n",
        "full_data.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<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>area</th>\n",
              "      <th>area_str</th>\n",
              "      <th>core_activity</th>\n",
              "      <th>based_in_country</th>\n",
              "      <th>based_in_country_str</th>\n",
              "      <th>based_in_macroregion</th>\n",
              "      <th>based_in_macroregion_str</th>\n",
              "      <th>market_size</th>\n",
              "      <th>market_size_str</th>\n",
              "      <th>shown_in_country</th>\n",
              "      <th>shown_in_country_str</th>\n",
              "      <th>shown_in_macroregion</th>\n",
              "      <th>shown_in_macroregion_str</th>\n",
              "      <th>disciplines</th>\n",
              "      <th>disciplines_str</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>respondent_id</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></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>57</th>\n",
              "      <td>[urban]</td>\n",
              "      <td>urban</td>\n",
              "      <td>production</td>\n",
              "      <td>[Finland]</td>\n",
              "      <td>Finland</td>\n",
              "      <td>[Northern Europe]</td>\n",
              "      <td>Northern Europe</td>\n",
              "      <td>[small internal market]</td>\n",
              "      <td>small internal market</td>\n",
              "      <td>[Austria, Countries outside Creative Europe, F...</td>\n",
              "      <td>Austria, Countries outside Creative Europe, Fi...</td>\n",
              "      <td>[Countries outside Creative Europe, Eastern Eu...</td>\n",
              "      <td>Countries outside Creative Europe, Eastern Eur...</td>\n",
              "      <td>[Dance]</td>\n",
              "      <td>Dance</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>58</th>\n",
              "      <td>[urban]</td>\n",
              "      <td>urban</td>\n",
              "      <td>production</td>\n",
              "      <td>[Slovenia]</td>\n",
              "      <td>Slovenia</td>\n",
              "      <td>[Eastern Europe]</td>\n",
              "      <td>Eastern Europe</td>\n",
              "      <td>[small internal market]</td>\n",
              "      <td>small internal market</td>\n",
              "      <td>[Austria, Bosnia and Herzegovina, Croatia, Hun...</td>\n",
              "      <td>Austria, Bosnia and Herzegovina, Croatia, Hung...</td>\n",
              "      <td>[Balkans, Eastern Europe, Southern Europe, Wes...</td>\n",
              "      <td>Balkans, Eastern Europe, Southern Europe, West...</td>\n",
              "      <td>[Dance]</td>\n",
              "      <td>Dance</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>59</th>\n",
              "      <td>[]</td>\n",
              "      <td></td>\n",
              "      <td>None</td>\n",
              "      <td>[Lithuania]</td>\n",
              "      <td>Lithuania</td>\n",
              "      <td>[Northern Europe]</td>\n",
              "      <td>Northern Europe</td>\n",
              "      <td>[small internal market]</td>\n",
              "      <td>small internal market</td>\n",
              "      <td>[]</td>\n",
              "      <td></td>\n",
              "      <td>[]</td>\n",
              "      <td></td>\n",
              "      <td>[Multidisciplinary within the performing arts ...</td>\n",
              "      <td>Multidisciplinary within the performing arts (...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>60</th>\n",
              "      <td>[]</td>\n",
              "      <td></td>\n",
              "      <td>None</td>\n",
              "      <td>[Greece]</td>\n",
              "      <td>Greece</td>\n",
              "      <td>[Southern Europe]</td>\n",
              "      <td>Southern Europe</td>\n",
              "      <td>[small internal market]</td>\n",
              "      <td>small internal market</td>\n",
              "      <td>[]</td>\n",
              "      <td></td>\n",
              "      <td>[]</td>\n",
              "      <td></td>\n",
              "      <td>[Multidisciplinary within the performing arts ...</td>\n",
              "      <td>Multidisciplinary within the performing arts (...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>61</th>\n",
              "      <td>[urban]</td>\n",
              "      <td>urban</td>\n",
              "      <td>production</td>\n",
              "      <td>[France]</td>\n",
              "      <td>France</td>\n",
              "      <td>[Western Europe]</td>\n",
              "      <td>Western Europe</td>\n",
              "      <td>[large internal market]</td>\n",
              "      <td>large internal market</td>\n",
              "      <td>[]</td>\n",
              "      <td></td>\n",
              "      <td>[]</td>\n",
              "      <td></td>\n",
              "      <td>[Multidisciplinary within the performing arts ...</td>\n",
              "      <td>Multidisciplinary within the performing arts (...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                  area  ...                                    disciplines_str\n",
              "respondent_id           ...                                                   \n",
              "57             [urban]  ...                                              Dance\n",
              "58             [urban]  ...                                              Dance\n",
              "59                  []  ...  Multidisciplinary within the performing arts (...\n",
              "60                  []  ...  Multidisciplinary within the performing arts (...\n",
              "61             [urban]  ...  Multidisciplinary within the performing arts (...\n",
              "\n",
              "[5 rows x 15 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 330
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mgO5fIEen9jY"
      },
      "source": [
        "## Analysis questions\n",
        "We have a number of questions for this data.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dDfXhiW8dnso"
      },
      "source": [
        "### Country distribution"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "HPI9J4kmK5V3",
        "outputId": "9407a617-1e27-4c81-8baf-3a1e8b048a73"
      },
      "source": [
        "full_data.explode(\"based_in_country\").reset_index().groupby(\"based_in_country\").agg({\"respondent_id\": \"nunique\"})[\"respondent_id\"].sort_values(ascending=False)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "based_in_country\n",
              "Spain                     190\n",
              "Germany                   189\n",
              "France                    175\n",
              "Italy                     167\n",
              "Portugal                  137\n",
              "United Kingdom            133\n",
              "Greece                    102\n",
              "Belgium                    93\n",
              "Sweden                     76\n",
              "Iceland                    70\n",
              "Netherlands                66\n",
              "Ireland                    62\n",
              "Denmark                    59\n",
              "Norway                     54\n",
              "Poland                     49\n",
              "Finland                    47\n",
              "Bulgaria                   37\n",
              "Czech Republic             33\n",
              "Lithuania                  31\n",
              "Slovenia                   31\n",
              "Republic of Cyprus         30\n",
              "Slovakia                   30\n",
              "Luxembourg                 28\n",
              "Croatia                    27\n",
              "Republic of Serbia         26\n",
              "Romania                    24\n",
              "Hungary                    24\n",
              "Georgia                    23\n",
              "Latvia                     23\n",
              "Austria                    19\n",
              "Tunisia                    13\n",
              "Kosovo                     11\n",
              "Ukraine                    11\n",
              "North Macedonia            11\n",
              "Moldova                    10\n",
              "Estonia                     9\n",
              "Malta                       8\n",
              "Albania                     6\n",
              "Bosnia and Herzegovina      3\n",
              "Armenia                     3\n",
              "Montenegro                  1\n",
              "Name: respondent_id, dtype: int64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 331
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GKVv7zzUpRZo"
      },
      "source": [
        "### How many of the respondents use Facebook Events to announce activities?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2YPzQN3ZpRGY",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "b66d1576-16b7-483b-d55f-d6f115760481"
      },
      "source": [
        "fb_event_users = len(facebook_data[\"respondent_id\"].value_counts())\n",
        "fb_event_users"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "270"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 332
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "thlKTBRS9Ydl",
        "outputId": "a79e7192-e4a7-4c22-9f1b-edb1e52797aa"
      },
      "source": [
        "fb_event_users / len(full_data.index)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0.13392857142857142"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 333
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "dfCndfXAGdj4",
        "outputId": "7c7c09a5-b59c-4734-a0eb-0eb57bc57c28"
      },
      "source": [
        "len(facebook_data_with_macroregions[-isna(facebook_data_with_macroregions[\"fb_shown_in_country\"])][\"respondent_id\"].value_counts())"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "253"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 334
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8iIy27MFIz8X",
        "outputId": "89f98f62-a43a-4345-d4a0-950457504c7b"
      },
      "source": [
        "len(facebook_data_with_macroregions[-isna(facebook_data_with_macroregions[\"fb_shown_in_country\"])].value_counts())"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "13056"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 335
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Oo98l8iMoX-j"
      },
      "source": [
        "### How many respondents of facebook events per discipline?\n",
        "\n",
        "And how many respondents were there per discipline?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LpbzKzyQw6Mj",
        "outputId": "c9ba83d4-f7db-4cb9-8c7c-ca5ae06fe119"
      },
      "source": [
        "full_data.explode(\"disciplines\").reset_index().groupby(\"disciplines\").agg({\"respondent_id\": \"nunique\"})"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<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>respondent_id</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>disciplines</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Circus and/or street arts</th>\n",
              "      <td>394</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Dance</th>\n",
              "      <td>765</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Multidisciplinary within the performing arts (music excluded)</th>\n",
              "      <td>693</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Performance</th>\n",
              "      <td>756</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Theatre or music theatre</th>\n",
              "      <td>843</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                                    respondent_id\n",
              "disciplines                                                      \n",
              "Circus and/or street arts                                     394\n",
              "Dance                                                         765\n",
              "Multidisciplinary within the performing arts (m...            693\n",
              "Performance                                                   756\n",
              "Theatre or music theatre                                      843"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 336
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zwk31FFYxG7g"
      },
      "source": [
        "And in the facebook data?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ZeE3V0nhodWr",
        "outputId": "78e2e017-3004-4ec2-f18c-7e68af27b784"
      },
      "source": [
        "fb_and_survey = full_data.reset_index().merge(facebook_data_with_macroregions, how=\"right\", left_on=\"respondent_id\", right_on=\"respondent_id\")\n",
        "fb_and_survey.explode(\"disciplines\")[-isna(fb_and_survey[\"platform_id\"])].groupby(\"disciplines\").agg({\"respondent_id\": \"nunique\"})"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:2: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
            "  \n"
          ],
          "name": "stderr"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<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>respondent_id</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>disciplines</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Circus and/or street arts</th>\n",
              "      <td>85</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Dance</th>\n",
              "      <td>127</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Multidisciplinary within the performing arts (music excluded)</th>\n",
              "      <td>122</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Performance</th>\n",
              "      <td>120</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Theatre or music theatre</th>\n",
              "      <td>131</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                                    respondent_id\n",
              "disciplines                                                      \n",
              "Circus and/or street arts                                      85\n",
              "Dance                                                         127\n",
              "Multidisciplinary within the performing arts (m...            122\n",
              "Performance                                                   120\n",
              "Theatre or music theatre                                      131"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 337
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "21Jr0dyCrkxT"
      },
      "source": [
        "### How many respondents of facebook events per producer/presenter type?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "uZf1yZ2hxpDX",
        "outputId": "a240d8cc-1011-4609-8a58-ea223b3df0fa"
      },
      "source": [
        "full_data.reset_index().groupby(\"core_activity\").agg({\"respondent_id\": \"nunique\"})"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<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>respondent_id</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>core_activity</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>presentation</th>\n",
              "      <td>287</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>production</th>\n",
              "      <td>1447</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "               respondent_id\n",
              "core_activity               \n",
              "presentation             287\n",
              "production              1447"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 338
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "WMTYcYIhrplE",
        "outputId": "315fe89f-0234-45e3-c502-10efaeff1668"
      },
      "source": [
        "fb_and_survey.groupby(\"core_activity\").agg({\"respondent_id\": \"nunique\"})"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<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>respondent_id</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>core_activity</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>presentation</th>\n",
              "      <td>83</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>production</th>\n",
              "      <td>187</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "               respondent_id\n",
              "core_activity               \n",
              "presentation              83\n",
              "production               187"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 339
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Or-MD3rvsebL"
      },
      "source": [
        "### How many respondents of facebook events per country and per macroregion?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "V7Xv5lGfytPQ",
        "outputId": "f299ca72-4811-4a0a-cf36-e161b927383b"
      },
      "source": [
        "full_data.explode(\"based_in_country\").reset_index().groupby(\"based_in_country\").agg({\"respondent_id\": \"nunique\"}).sort_values(by=\"respondent_id\", ascending=False)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<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>respondent_id</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>based_in_country</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Spain</th>\n",
              "      <td>190</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Germany</th>\n",
              "      <td>189</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>France</th>\n",
              "      <td>175</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Italy</th>\n",
              "      <td>167</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Portugal</th>\n",
              "      <td>137</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>United Kingdom</th>\n",
              "      <td>133</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Greece</th>\n",
              "      <td>102</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Belgium</th>\n",
              "      <td>93</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Sweden</th>\n",
              "      <td>76</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Iceland</th>\n",
              "      <td>70</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Netherlands</th>\n",
              "      <td>66</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Ireland</th>\n",
              "      <td>62</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Denmark</th>\n",
              "      <td>59</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Norway</th>\n",
              "      <td>54</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Poland</th>\n",
              "      <td>49</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Finland</th>\n",
              "      <td>47</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Bulgaria</th>\n",
              "      <td>37</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Czech Republic</th>\n",
              "      <td>33</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Slovenia</th>\n",
              "      <td>31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Lithuania</th>\n",
              "      <td>31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Slovakia</th>\n",
              "      <td>30</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Republic of Cyprus</th>\n",
              "      <td>30</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Luxembourg</th>\n",
              "      <td>28</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Croatia</th>\n",
              "      <td>27</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Republic of Serbia</th>\n",
              "      <td>26</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Hungary</th>\n",
              "      <td>24</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Romania</th>\n",
              "      <td>24</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Latvia</th>\n",
              "      <td>23</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Georgia</th>\n",
              "      <td>23</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Austria</th>\n",
              "      <td>19</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Tunisia</th>\n",
              "      <td>13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>North Macedonia</th>\n",
              "      <td>11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Kosovo</th>\n",
              "      <td>11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Ukraine</th>\n",
              "      <td>11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Moldova</th>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Estonia</th>\n",
              "      <td>9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Malta</th>\n",
              "      <td>8</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Albania</th>\n",
              "      <td>6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Armenia</th>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Bosnia and Herzegovina</th>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Montenegro</th>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                        respondent_id\n",
              "based_in_country                     \n",
              "Spain                             190\n",
              "Germany                           189\n",
              "France                            175\n",
              "Italy                             167\n",
              "Portugal                          137\n",
              "United Kingdom                    133\n",
              "Greece                            102\n",
              "Belgium                            93\n",
              "Sweden                             76\n",
              "Iceland                            70\n",
              "Netherlands                        66\n",
              "Ireland                            62\n",
              "Denmark                            59\n",
              "Norway                             54\n",
              "Poland                             49\n",
              "Finland                            47\n",
              "Bulgaria                           37\n",
              "Czech Republic                     33\n",
              "Slovenia                           31\n",
              "Lithuania                          31\n",
              "Slovakia                           30\n",
              "Republic of Cyprus                 30\n",
              "Luxembourg                         28\n",
              "Croatia                            27\n",
              "Republic of Serbia                 26\n",
              "Hungary                            24\n",
              "Romania                            24\n",
              "Latvia                             23\n",
              "Georgia                            23\n",
              "Austria                            19\n",
              "Tunisia                            13\n",
              "North Macedonia                    11\n",
              "Kosovo                             11\n",
              "Ukraine                            11\n",
              "Moldova                            10\n",
              "Estonia                             9\n",
              "Malta                               8\n",
              "Albania                             6\n",
              "Armenia                             3\n",
              "Bosnia and Herzegovina              3\n",
              "Montenegro                          1"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 340
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_qugw-2osiIA",
        "outputId": "cee8b511-d9fb-4481-a989-ac59019bde17"
      },
      "source": [
        "fb_and_survey.explode(\"based_in_country\").groupby(\"based_in_country\").agg({\"respondent_id\": \"nunique\"}).sort_values(by=\"respondent_id\", ascending=False)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<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>respondent_id</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>based_in_country</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Spain</th>\n",
              "      <td>33</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Italy</th>\n",
              "      <td>31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>France</th>\n",
              "      <td>27</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>United Kingdom</th>\n",
              "      <td>22</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Portugal</th>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Germany</th>\n",
              "      <td>19</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Denmark</th>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Belgium</th>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Greece</th>\n",
              "      <td>14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Netherlands</th>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Norway</th>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Ireland</th>\n",
              "      <td>9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Hungary</th>\n",
              "      <td>7</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Sweden</th>\n",
              "      <td>6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Republic of Cyprus</th>\n",
              "      <td>6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Lithuania</th>\n",
              "      <td>6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Luxembourg</th>\n",
              "      <td>6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Latvia</th>\n",
              "      <td>6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Finland</th>\n",
              "      <td>6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Austria</th>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Slovakia</th>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Republic of Serbia</th>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Poland</th>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Czech Republic</th>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Iceland</th>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Slovenia</th>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Croatia</th>\n",
              "      <td>4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Romania</th>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Bulgaria</th>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Ukraine</th>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Georgia</th>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Estonia</th>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Malta</th>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Tunisia</th>\n",
              "      <td>2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>North Macedonia</th>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Kosovo</th>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Albania</th>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                    respondent_id\n",
              "based_in_country                 \n",
              "Spain                          33\n",
              "Italy                          31\n",
              "France                         27\n",
              "United Kingdom                 22\n",
              "Portugal                       20\n",
              "Germany                        19\n",
              "Denmark                        14\n",
              "Belgium                        14\n",
              "Greece                         14\n",
              "Netherlands                    12\n",
              "Norway                         10\n",
              "Ireland                         9\n",
              "Hungary                         7\n",
              "Sweden                          6\n",
              "Republic of Cyprus              6\n",
              "Lithuania                       6\n",
              "Luxembourg                      6\n",
              "Latvia                          6\n",
              "Finland                         6\n",
              "Austria                         5\n",
              "Slovakia                        5\n",
              "Republic of Serbia              5\n",
              "Poland                          5\n",
              "Czech Republic                  5\n",
              "Iceland                         4\n",
              "Slovenia                        4\n",
              "Croatia                         4\n",
              "Romania                         3\n",
              "Bulgaria                        3\n",
              "Ukraine                         3\n",
              "Georgia                         2\n",
              "Estonia                         2\n",
              "Malta                           2\n",
              "Tunisia                         2\n",
              "North Macedonia                 1\n",
              "Kosovo                          1\n",
              "Albania                         1"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 341
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6Yn3dARctuc7",
        "outputId": "717b55b9-ba9b-4017-8da0-87b3a4be2aec"
      },
      "source": [
        "fb_and_survey.explode(\"based_in_macroregion\").groupby(\"based_in_macroregion\").agg({\"respondent_id\": \"nunique\"}).sort_values(by=\"respondent_id\", ascending=False)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<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>respondent_id</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>based_in_macroregion</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Western Europe</th>\n",
              "      <td>102</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Southern Europe</th>\n",
              "      <td>100</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Northern Europe</th>\n",
              "      <td>46</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Eastern Europe</th>\n",
              "      <td>28</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Balkans</th>\n",
              "      <td>7</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Eastern Partnership + Tunisia</th>\n",
              "      <td>7</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                               respondent_id\n",
              "based_in_macroregion                        \n",
              "Western Europe                           102\n",
              "Southern Europe                          100\n",
              "Northern Europe                           46\n",
              "Eastern Europe                            28\n",
              "Balkans                                    7\n",
              "Eastern Partnership + Tunisia              7"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 342
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Yw_n6N9Z4uVK"
      },
      "source": [
        "## Q16 data\n",
        "Based on the answers to q16 in the survey, we get an idea of how many respondents are present in which countries and macroregions. For this, we focus on respondents with core activitye \"producer\"."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ikrAFL0d4sEi"
      },
      "source": [
        "fd = full_data.reset_index().explode(\"based_in_country\")\n",
        "fd[\"based_in_macroregion\"] = fd[\"based_in_country\"].map(macroregions_mapping)\n",
        "fd = fd.explode(\"shown_in_country\")\n",
        "fd = fd[-isnull(fd[\"shown_in_country\"])]\n",
        "fd[\"shown_in_macroregion\"] = fd[\"shown_in_country\"].map(macroregions_mapping)\n",
        "fd[\"cross_border_other_macroregion\"] = (fd[\"based_in_country_str\"] != fd[\"shown_in_country\"]) & (fd[\"based_in_macroregion_str\"] != fd[\"shown_in_macroregion\"])\n",
        "fd[\"cross_border_same_macroregion\"] = (fd[\"based_in_country_str\"] != fd[\"shown_in_country\"]) & (fd[\"based_in_macroregion_str\"] == fd[\"shown_in_macroregion\"])\n",
        "fd[\"within_border\"] = (fd[\"based_in_country_str\"] == fd[\"shown_in_country\"])\n",
        "fd = fd[fd[\"core_activity\"] == \"production\"]\n",
        "q16 = fd[[\"respondent_id\", \"based_in_country\", \"based_in_macroregion\", \"shown_in_country\", \"shown_in_macroregion\", \"within_border\", \"cross_border_same_macroregion\", \"cross_border_other_macroregion\", \"core_activity\"]].drop_duplicates()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tA0y1Gu5_Skt"
      },
      "source": [
        "### Sankey data\n",
        "\n",
        "A first analysis is to simply make a \"flow\" of producers that are based in a certain macroregion and indicate that they are also present in another macroregion"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 137
        },
        "id": "D3KMNLnj-65Y",
        "outputId": "b0e5525b-9958-4dc0-f7f9-997926757d9f"
      },
      "source": [
        "q16.groupby([\"based_in_macroregion\", \"shown_in_macroregion\"]).agg({\"respondent_id\": \"nunique\"}).to_csv()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            },
            "text/plain": [
              "'based_in_macroregion,shown_in_macroregion,respondent_id\\nBalkans,Balkans,37\\nBalkans,Countries outside Creative Europe,14\\nBalkans,Eastern Europe,25\\nBalkans,Eastern Partnership + Tunisia,3\\nBalkans,Northern Europe,8\\nBalkans,Southern Europe,24\\nBalkans,Western Europe,26\\nEastern Europe,Balkans,51\\nEastern Europe,Countries outside Creative Europe,64\\nEastern Europe,Eastern Europe,133\\nEastern Europe,Eastern Partnership + Tunisia,25\\nEastern Europe,Northern Europe,59\\nEastern Europe,Southern Europe,79\\nEastern Europe,Western Europe,122\\nEastern Partnership + Tunisia,Balkans,1\\nEastern Partnership + Tunisia,Countries outside Creative Europe,11\\nEastern Partnership + Tunisia,Eastern Europe,14\\nEastern Partnership + Tunisia,Eastern Partnership + Tunisia,23\\nEastern Partnership + Tunisia,Northern Europe,8\\nEastern Partnership + Tunisia,Southern Europe,7\\nEastern Partnership + Tunisia,Western Europe,19\\nNorthern Europe,Balkans,22\\nNorthern Europe,Countries outside Creative Europe,125\\nNorthern Europe,Eastern Europe,109\\nNorthern Europe,Eastern Partnership + Tunisia,22\\nNorthern Europe,Northern Europe,209\\nNorthern Europe,Southern Europe,110\\nNorthern Europe,Western Europe,179\\nSouthern Europe,Balkans,41\\nSouthern Europe,Countries outside Creative Europe,173\\nSouthern Europe,Eastern Europe,163\\nSouthern Europe,Eastern Partnership + Tunisia,26\\nSouthern Europe,Northern Europe,134\\nSouthern Europe,Southern Europe,291\\nSouthern Europe,Western Europe,273\\nWestern Europe,Balkans,50\\nWestern Europe,Countries outside Creative Europe,261\\nWestern Europe,Eastern Europe,220\\nWestern Europe,Eastern Partnership + Tunisia,41\\nWestern Europe,Northern Europe,219\\nWestern Europe,Southern Europe,269\\nWestern Europe,Western Europe,402\\n'"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 344
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kNYLCOMGARy3"
      },
      "source": [
        "### Perspective Audience\n",
        "\n",
        "From the perspective of an audience, we can ask how many of the producing respondents that this audience can be exposed in the macroregion of the audience."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 269
        },
        "id": "-8tlQqS6ATum",
        "outputId": "637e6ad5-5327-4f07-b050-9406858adae7"
      },
      "source": [
        "perspective_audience_cbsm = q16.groupby([\"shown_in_macroregion\", \"cross_border_same_macroregion\"]).respondent_id.nunique()\n",
        "perspective_audience_cbom = q16.groupby([\"shown_in_macroregion\", \"cross_border_other_macroregion\"]).agg({\"respondent_id\": \"nunique\"})\n",
        "perspective_audience_wb = q16.groupby([\"shown_in_macroregion\", \"within_border\"]).agg({\"respondent_id\": \"nunique\"})\n",
        "amount_of_producers_shown = q16.groupby([\"shown_in_macroregion\"]).agg({\"respondent_id\": \"nunique\"})\n",
        "amount_of_producers_based = q16.groupby([\"based_in_macroregion\"]).agg({\"respondent_id\": \"nunique\"})\n",
        "# remove the unnecessary False data\n",
        "perspective_audience_cbsm = perspective_audience_cbsm[perspective_audience_cbsm.index.get_level_values(\"cross_border_same_macroregion\") == True].droplevel(1)\n",
        "perspective_audience_cbom = perspective_audience_cbom[perspective_audience_cbom.index.get_level_values(\"cross_border_other_macroregion\") == True].droplevel(1)\n",
        "perspective_audience_wb = perspective_audience_wb[perspective_audience_wb.index.get_level_values(\"within_border\") == True].droplevel(1)\n",
        "# join\n",
        "perspective_audience = concat([perspective_audience_wb, perspective_audience_cbsm, perspective_audience_cbom, amount_of_producers_shown, amount_of_producers_based], axis=1, join=\"inner\")\n",
        "perspective_audience.columns = [\"within_border\", \"cross_border_same_macroregion\", \"cross_border_other_macroregion\", \"amount of producers shown in this region\", \"amount of producers based in this region\"]\n",
        "perspective_audience"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<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>within_border</th>\n",
              "      <th>cross_border_same_macroregion</th>\n",
              "      <th>cross_border_other_macroregion</th>\n",
              "      <th>amount of producers shown in this region</th>\n",
              "      <th>amount of producers based in this region</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Balkans</th>\n",
              "      <td>25</td>\n",
              "      <td>25</td>\n",
              "      <td>148</td>\n",
              "      <td>177</td>\n",
              "      <td>38</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Eastern Europe</th>\n",
              "      <td>80</td>\n",
              "      <td>104</td>\n",
              "      <td>485</td>\n",
              "      <td>601</td>\n",
              "      <td>155</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Eastern Partnership + Tunisia</th>\n",
              "      <td>15</td>\n",
              "      <td>15</td>\n",
              "      <td>110</td>\n",
              "      <td>130</td>\n",
              "      <td>31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Northern Europe</th>\n",
              "      <td>123</td>\n",
              "      <td>156</td>\n",
              "      <td>393</td>\n",
              "      <td>567</td>\n",
              "      <td>224</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Southern Europe</th>\n",
              "      <td>173</td>\n",
              "      <td>196</td>\n",
              "      <td>460</td>\n",
              "      <td>698</td>\n",
              "      <td>335</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Western Europe</th>\n",
              "      <td>227</td>\n",
              "      <td>303</td>\n",
              "      <td>594</td>\n",
              "      <td>920</td>\n",
              "      <td>430</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                               within_border  ...  amount of producers based in this region\n",
              "Balkans                                   25  ...                                        38\n",
              "Eastern Europe                            80  ...                                       155\n",
              "Eastern Partnership + Tunisia             15  ...                                        31\n",
              "Northern Europe                          123  ...                                       224\n",
              "Southern Europe                          173  ...                                       335\n",
              "Western Europe                           227  ...                                       430\n",
              "\n",
              "[6 rows x 5 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 345
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mdYIMAUSGKmF"
      },
      "source": [
        "### Perspective producer"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 266
        },
        "id": "FlUSOCUTGTxC",
        "outputId": "741aa110-6151-4ed6-c029-8330e5a22a92"
      },
      "source": [
        "perspective_respondent_cbsm = q16.groupby([\"based_in_macroregion\", \"cross_border_same_macroregion\"]).agg({\"respondent_id\": \"nunique\"})\n",
        "perspective_respondent_cbom = q16.groupby([\"based_in_macroregion\", \"cross_border_other_macroregion\"]).agg({\"respondent_id\": \"nunique\"})\n",
        "perspective_respondent_wb = q16.groupby([\"based_in_macroregion\", \"within_border\"]).agg({\"respondent_id\": \"nunique\"})\n",
        "amount_of_producers = q16.groupby([\"based_in_macroregion\"]).agg({\"respondent_id\": \"nunique\"})\n",
        "# remove the unnecessary False data\n",
        "perspective_respondent_cbsm = perspective_respondent_cbsm[perspective_respondent_cbsm.index.get_level_values(\"cross_border_same_macroregion\") == True].droplevel(1)\n",
        "perspective_respondent_cbom = perspective_respondent_cbom[perspective_respondent_cbom.index.get_level_values(\"cross_border_other_macroregion\") == True].droplevel(1)\n",
        "perspective_respondent_wb = perspective_respondent_wb[perspective_respondent_wb.index.get_level_values(\"within_border\") == True].droplevel(1)\n",
        "# join\n",
        "perspective_respondent = concat([perspective_respondent_wb, perspective_respondent_cbsm, perspective_respondent_cbom, amount_of_producers], axis=1, join=\"inner\")\n",
        "perspective_respondent.columns = [\"within_border\", \"cross_border_same_macroregion\", \"cross_border_other_macroregion\", \"amount of producers based in macroregion\"]\n",
        "perspective_respondent"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<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>within_border</th>\n",
              "      <th>cross_border_same_macroregion</th>\n",
              "      <th>cross_border_other_macroregion</th>\n",
              "      <th>amount of producers based in macroregion</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>based_in_macroregion</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Balkans</th>\n",
              "      <td>25</td>\n",
              "      <td>25</td>\n",
              "      <td>34</td>\n",
              "      <td>38</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Eastern Europe</th>\n",
              "      <td>80</td>\n",
              "      <td>104</td>\n",
              "      <td>143</td>\n",
              "      <td>155</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Eastern Partnership + Tunisia</th>\n",
              "      <td>15</td>\n",
              "      <td>15</td>\n",
              "      <td>26</td>\n",
              "      <td>31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Northern Europe</th>\n",
              "      <td>123</td>\n",
              "      <td>156</td>\n",
              "      <td>201</td>\n",
              "      <td>224</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Southern Europe</th>\n",
              "      <td>173</td>\n",
              "      <td>196</td>\n",
              "      <td>316</td>\n",
              "      <td>335</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Western Europe</th>\n",
              "      <td>227</td>\n",
              "      <td>303</td>\n",
              "      <td>395</td>\n",
              "      <td>430</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                               within_border  ...  amount of producers based in macroregion\n",
              "based_in_macroregion                          ...                                          \n",
              "Balkans                                   25  ...                                        38\n",
              "Eastern Europe                            80  ...                                       155\n",
              "Eastern Partnership + Tunisia             15  ...                                        31\n",
              "Northern Europe                          123  ...                                       224\n",
              "Southern Europe                          173  ...                                       335\n",
              "Western Europe                           227  ...                                       430\n",
              "\n",
              "[6 rows x 4 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 346
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HtLEpXTrQIBv"
      },
      "source": [
        "## Facebook data\n",
        "\n",
        "we can reproduce the analyses above based on facebook data, rather than on data from the amount of respondents."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 439
        },
        "id": "bRcXzV41QN5J",
        "outputId": "45ee577f-e44c-4e90-bc88-ecd191e7d781"
      },
      "source": [
        "fd = fb_and_survey.reset_index().explode(\"based_in_country\")\n",
        "fd[\"based_in_macroregion\"] = fd[\"based_in_country\"].map(macroregions_mapping)\n",
        "fd = fd[fd[\"core_activity\"] == \"production\"] # only take producers\n",
        "fd = fd[-isna(fd[\"fb_shown_in_country\"])] # ignore eventdata without country information\n",
        "fd[\"cross_border_other_macroregion\"] = (fd[\"based_in_country\"] != fd[\"fb_shown_in_country\"]) & (fd[\"based_in_macroregion\"] != fd[\"fb_shown_in_macroregion\"])\n",
        "fd[\"cross_border_same_macroregion\"] = (fd[\"based_in_country\"] != fd[\"fb_shown_in_country\"]) & (fd[\"based_in_macroregion\"] == fd[\"fb_shown_in_macroregion\"])\n",
        "fd[\"within_border\"] = (fd[\"based_in_country\"] == fd[\"fb_shown_in_country\"])\n",
        "fd = fd[fd[\"core_activity\"] == \"production\"]\n",
        "fb = fd[[\"respondent_id\", \"platform_id\", \"based_in_country\", \"based_in_macroregion\", \"fb_shown_in_country\", \"fb_shown_in_macroregion\", \"within_border\", \"cross_border_same_macroregion\", \"cross_border_other_macroregion\", \"core_activity\"]].drop_duplicates()\n",
        "fb"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<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>respondent_id</th>\n",
              "      <th>platform_id</th>\n",
              "      <th>based_in_country</th>\n",
              "      <th>based_in_macroregion</th>\n",
              "      <th>fb_shown_in_country</th>\n",
              "      <th>fb_shown_in_macroregion</th>\n",
              "      <th>within_border</th>\n",
              "      <th>cross_border_same_macroregion</th>\n",
              "      <th>cross_border_other_macroregion</th>\n",
              "      <th>core_activity</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>294</th>\n",
              "      <td>948</td>\n",
              "      <td>121767468503339</td>\n",
              "      <td>Belgium</td>\n",
              "      <td>Western Europe</td>\n",
              "      <td>United Kingdom</td>\n",
              "      <td>Western Europe</td>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>production</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>295</th>\n",
              "      <td>948</td>\n",
              "      <td>126190487930717</td>\n",
              "      <td>Belgium</td>\n",
              "      <td>Western Europe</td>\n",
              "      <td>Belgium</td>\n",
              "      <td>Western Europe</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>production</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>301</th>\n",
              "      <td>948</td>\n",
              "      <td>134699050804032</td>\n",
              "      <td>Belgium</td>\n",
              "      <td>Western Europe</td>\n",
              "      <td>Belgium</td>\n",
              "      <td>Western Europe</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>production</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>303</th>\n",
              "      <td>948</td>\n",
              "      <td>137664490252614</td>\n",
              "      <td>Belgium</td>\n",
              "      <td>Western Europe</td>\n",
              "      <td>Belgium</td>\n",
              "      <td>Western Europe</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>production</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>305</th>\n",
              "      <td>948</td>\n",
              "      <td>140128050050025</td>\n",
              "      <td>Belgium</td>\n",
              "      <td>Western Europe</td>\n",
              "      <td>Belgium</td>\n",
              "      <td>Western Europe</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>production</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28745</th>\n",
              "      <td>2055</td>\n",
              "      <td>2250871828257839</td>\n",
              "      <td>Czech Republic</td>\n",
              "      <td>Eastern Europe</td>\n",
              "      <td>Germany</td>\n",
              "      <td>Western Europe</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "      <td>production</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28750</th>\n",
              "      <td>1620</td>\n",
              "      <td>2308805572761077</td>\n",
              "      <td>Croatia</td>\n",
              "      <td>Eastern Europe</td>\n",
              "      <td>Croatia</td>\n",
              "      <td>Eastern Europe</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>production</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28757</th>\n",
              "      <td>1620</td>\n",
              "      <td>2322746724696012</td>\n",
              "      <td>Croatia</td>\n",
              "      <td>Eastern Europe</td>\n",
              "      <td>Croatia</td>\n",
              "      <td>Eastern Europe</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>production</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28758</th>\n",
              "      <td>1111</td>\n",
              "      <td>2834129026616155</td>\n",
              "      <td>Spain</td>\n",
              "      <td>Southern Europe</td>\n",
              "      <td>Spain</td>\n",
              "      <td>Southern Europe</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>production</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28761</th>\n",
              "      <td>541</td>\n",
              "      <td>2834129026616155</td>\n",
              "      <td>Spain</td>\n",
              "      <td>Southern Europe</td>\n",
              "      <td>Spain</td>\n",
              "      <td>Southern Europe</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>production</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>6502 rows × 10 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "       respondent_id  ...  core_activity\n",
              "294              948  ...     production\n",
              "295              948  ...     production\n",
              "301              948  ...     production\n",
              "303              948  ...     production\n",
              "305              948  ...     production\n",
              "...              ...  ...            ...\n",
              "28745           2055  ...     production\n",
              "28750           1620  ...     production\n",
              "28757           1620  ...     production\n",
              "28758           1111  ...     production\n",
              "28761            541  ...     production\n",
              "\n",
              "[6502 rows x 10 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 347
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QmGpztPpRKDJ"
      },
      "source": [
        "### Sankey data\n",
        "First with all data"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 137
        },
        "id": "ST5XzQpTRL35",
        "outputId": "e5d7ac6d-bea1-4663-f981-8ee9e3f6db0a"
      },
      "source": [
        "fb.groupby([\"based_in_macroregion\", \"fb_shown_in_macroregion\"]).agg({\"platform_id\": \"nunique\"}).to_csv()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            },
            "text/plain": [
              "'based_in_macroregion,fb_shown_in_macroregion,platform_id\\nBalkans,Balkans,166\\nBalkans,Countries outside Creative Europe,3\\nBalkans,Eastern Europe,4\\nEastern Europe,Balkans,1\\nEastern Europe,Eastern Europe,1230\\nEastern Europe,Eastern Partnership + Tunisia,1\\nEastern Europe,Northern Europe,2\\nEastern Europe,Southern Europe,1\\nEastern Europe,Western Europe,19\\nEastern Partnership + Tunisia,Eastern Europe,1\\nEastern Partnership + Tunisia,Eastern Partnership + Tunisia,372\\nNorthern Europe,Balkans,1\\nNorthern Europe,Countries outside Creative Europe,3\\nNorthern Europe,Eastern Europe,4\\nNorthern Europe,Northern Europe,912\\nNorthern Europe,Southern Europe,3\\nNorthern Europe,Western Europe,21\\nSouthern Europe,Balkans,1\\nSouthern Europe,Countries outside Creative Europe,28\\nSouthern Europe,Eastern Europe,7\\nSouthern Europe,Northern Europe,15\\nSouthern Europe,Southern Europe,1215\\nSouthern Europe,Western Europe,93\\nWestern Europe,Balkans,1\\nWestern Europe,Countries outside Creative Europe,68\\nWestern Europe,Eastern Europe,12\\nWestern Europe,Eastern Partnership + Tunisia,1\\nWestern Europe,Northern Europe,42\\nWestern Europe,Southern Europe,26\\nWestern Europe,Western Europe,1865\\n'"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 348
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7VvPB0sxUuJO"
      },
      "source": [
        "What if we only take cross-border data?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 137
        },
        "id": "p8YsXOr2UwKv",
        "outputId": "eaa8203f-22fc-4f83-d945-ecd4a6853e97"
      },
      "source": [
        "fb[fb[\"within_border\"] == False].groupby([\"based_in_macroregion\", \"fb_shown_in_macroregion\"]).agg({\"platform_id\": \"nunique\"}).to_csv()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            },
            "text/plain": [
              "'based_in_macroregion,fb_shown_in_macroregion,platform_id\\nBalkans,Balkans,60\\nBalkans,Countries outside Creative Europe,3\\nBalkans,Eastern Europe,4\\nEastern Europe,Balkans,1\\nEastern Europe,Eastern Europe,158\\nEastern Europe,Eastern Partnership + Tunisia,1\\nEastern Europe,Northern Europe,2\\nEastern Europe,Southern Europe,1\\nEastern Europe,Western Europe,19\\nEastern Partnership + Tunisia,Eastern Europe,1\\nNorthern Europe,Balkans,1\\nNorthern Europe,Countries outside Creative Europe,3\\nNorthern Europe,Eastern Europe,4\\nNorthern Europe,Northern Europe,58\\nNorthern Europe,Southern Europe,3\\nNorthern Europe,Western Europe,21\\nSouthern Europe,Balkans,1\\nSouthern Europe,Countries outside Creative Europe,28\\nSouthern Europe,Eastern Europe,7\\nSouthern Europe,Northern Europe,15\\nSouthern Europe,Southern Europe,80\\nSouthern Europe,Western Europe,93\\nWestern Europe,Balkans,1\\nWestern Europe,Countries outside Creative Europe,68\\nWestern Europe,Eastern Europe,12\\nWestern Europe,Eastern Partnership + Tunisia,1\\nWestern Europe,Northern Europe,42\\nWestern Europe,Southern Europe,26\\nWestern Europe,Western Europe,88\\n'"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 349
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rqqXP_DzapTK"
      },
      "source": [
        "### Perspective audience"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 235
        },
        "id": "d4g_vR8rarA5",
        "outputId": "a1e53090-e0a8-4bc8-d923-50444c165953"
      },
      "source": [
        "perspective_audience_cbsm = fb.groupby([\"fb_shown_in_macroregion\", \"cross_border_same_macroregion\"]).agg({\"platform_id\": \"nunique\"})\n",
        "perspective_audience_cbom = fb.groupby([\"fb_shown_in_macroregion\", \"cross_border_other_macroregion\"]).agg({\"platform_id\": \"nunique\"})\n",
        "perspective_audience_wb = fb.groupby([\"fb_shown_in_macroregion\", \"within_border\"]).agg({\"platform_id\": \"nunique\"})\n",
        "amount_of_events_shown = fb.groupby([\"fb_shown_in_macroregion\"]).agg({\"platform_id\": \"nunique\"})\n",
        "# remove the unnecessary False data\n",
        "perspective_audience_cbsm = perspective_audience_cbsm[perspective_audience_cbsm.index.get_level_values(\"cross_border_same_macroregion\") == True].droplevel(1)\n",
        "perspective_audience_cbom = perspective_audience_cbom[perspective_audience_cbom.index.get_level_values(\"cross_border_other_macroregion\") == True].droplevel(1)\n",
        "perspective_audience_wb = perspective_audience_wb[perspective_audience_wb.index.get_level_values(\"within_border\") == True].droplevel(1)\n",
        "# join\n",
        "perspective_audience = concat([perspective_audience_wb, perspective_audience_cbsm, perspective_audience_cbom, amount_of_events_shown], axis=1, join=\"inner\")\n",
        "perspective_audience.columns = [\"within_border\", \"cross_border_same_macroregion\", \"cross_border_other_macroregion\", \"amount of events shown in this region\"]\n",
        "perspective_audience"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<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>within_border</th>\n",
              "      <th>cross_border_same_macroregion</th>\n",
              "      <th>cross_border_other_macroregion</th>\n",
              "      <th>amount of events shown in this region</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>fb_shown_in_macroregion</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Balkans</th>\n",
              "      <td>106</td>\n",
              "      <td>60</td>\n",
              "      <td>2</td>\n",
              "      <td>168</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Eastern Europe</th>\n",
              "      <td>1073</td>\n",
              "      <td>158</td>\n",
              "      <td>26</td>\n",
              "      <td>1250</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Northern Europe</th>\n",
              "      <td>896</td>\n",
              "      <td>58</td>\n",
              "      <td>46</td>\n",
              "      <td>933</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Southern Europe</th>\n",
              "      <td>1135</td>\n",
              "      <td>80</td>\n",
              "      <td>28</td>\n",
              "      <td>1231</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Western Europe</th>\n",
              "      <td>1789</td>\n",
              "      <td>88</td>\n",
              "      <td>132</td>\n",
              "      <td>1941</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                         within_border  ...  amount of events shown in this region\n",
              "fb_shown_in_macroregion                 ...                                       \n",
              "Balkans                            106  ...                                    168\n",
              "Eastern Europe                    1073  ...                                   1250\n",
              "Northern Europe                    896  ...                                    933\n",
              "Southern Europe                   1135  ...                                   1231\n",
              "Western Europe                    1789  ...                                   1941\n",
              "\n",
              "[5 rows x 4 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 350
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_XntGxfhcHyB"
      },
      "source": [
        "### Perspective producer"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 235
        },
        "id": "JvRtlxD7cKC5",
        "outputId": "5fbd4242-4e67-4b01-ccdc-90ef1ee50f7f"
      },
      "source": [
        "perspective_respondent_cbsm = fb.groupby([\"based_in_macroregion\", \"cross_border_same_macroregion\"]).agg({\"platform_id\": \"nunique\"})\n",
        "perspective_respondent_cbom = fb.groupby([\"based_in_macroregion\", \"cross_border_other_macroregion\"]).agg({\"platform_id\": \"nunique\"})\n",
        "perspective_respondent_wb = fb.groupby([\"based_in_macroregion\", \"within_border\"]).agg({\"platform_id\": \"nunique\"})\n",
        "amount_of_events = fb.groupby([\"based_in_macroregion\"]).agg({\"platform_id\": \"nunique\"})\n",
        "# remove the unnecessary False data\n",
        "perspective_respondent_cbsm = perspective_respondent_cbsm[perspective_respondent_cbsm.index.get_level_values(\"cross_border_same_macroregion\") == True].droplevel(1)\n",
        "perspective_respondent_cbom = perspective_respondent_cbom[perspective_respondent_cbom.index.get_level_values(\"cross_border_other_macroregion\") == True].droplevel(1)\n",
        "perspective_respondent_wb = perspective_respondent_wb[perspective_respondent_wb.index.get_level_values(\"within_border\") == True].droplevel(1)\n",
        "# join\n",
        "perspective_respondent = concat([perspective_respondent_wb, perspective_respondent_cbsm, perspective_respondent_cbom, amount_of_events], axis=1, join=\"inner\")\n",
        "perspective_respondent.columns = [\"within_border\", \"cross_border_same_macroregion\", \"cross_border_other_macroregion\", \"events_by_producers\"]\n",
        "perspective_respondent"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<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>within_border</th>\n",
              "      <th>cross_border_same_macroregion</th>\n",
              "      <th>cross_border_other_macroregion</th>\n",
              "      <th>events_by_producers</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>based_in_macroregion</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Balkans</th>\n",
              "      <td>106</td>\n",
              "      <td>60</td>\n",
              "      <td>7</td>\n",
              "      <td>173</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Eastern Europe</th>\n",
              "      <td>1073</td>\n",
              "      <td>158</td>\n",
              "      <td>24</td>\n",
              "      <td>1254</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Northern Europe</th>\n",
              "      <td>896</td>\n",
              "      <td>58</td>\n",
              "      <td>32</td>\n",
              "      <td>944</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Southern Europe</th>\n",
              "      <td>1135</td>\n",
              "      <td>80</td>\n",
              "      <td>144</td>\n",
              "      <td>1359</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Western Europe</th>\n",
              "      <td>1789</td>\n",
              "      <td>88</td>\n",
              "      <td>150</td>\n",
              "      <td>2015</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                      within_border  ...  events_by_producers\n",
              "based_in_macroregion                 ...                     \n",
              "Balkans                         106  ...                  173\n",
              "Eastern Europe                 1073  ...                 1254\n",
              "Northern Europe                 896  ...                  944\n",
              "Southern Europe                1135  ...                 1359\n",
              "Western Europe                 1789  ...                 2015\n",
              "\n",
              "[5 rows x 4 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 352
        }
      ]
    }
  ]
}