7_LanguageData.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/ipeirotis/mturk_demographics/blob/master/7_LanguageData.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZvXHTj1G2upY"
      },
      "source": [
        "### Load dataset"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd"
      ],
      "metadata": {
        "id": "V7onWVJ-xj1c"
      },
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Fetch the old data as well (Mar 2015 - Oct 2020)\n",
        "URL = 'https://github.com/ipeirotis/mturk_demographics/raw/master/dataset_14may2023_16mar2015.zip'\n",
        "df = pd.read_csv(URL, low_memory=False)\n",
        "\n",
        "# Convert all NaN values to None\n",
        "df = df.where(pd.notnull(df), None)\n",
        "\n",
        "df = df.drop([\"hitId\", \"surveyId\"], axis=\"columns\")\n",
        "\n",
        "rename_columns = {\n",
        "    \"workerId\": \"worker_id\",\n",
        "    \"householdIncome\": \"household_income\",\n",
        "    \"educationalLevel\": \"educational_level\",\n",
        "    \"householdSize\": \"household_size\",\n",
        "    \"maritalStatus\": \"marital_status\",\n",
        "    \"languagesSpoken\": \"languages_spoken\",\n",
        "    \"timeSpentOnMturk\": \"time_spent_on_mturk\" ,\n",
        "    \"weeklyIncomeFromMturk\": \"weekly_income_from_mturk\",\n",
        "    \"yearOfBirth\": \"year_of_birth\",\n",
        "    \"locationCity\": \"location_city\",\n",
        "    \"locationRegion\": \"location_region\",\n",
        "    \"locationCountry\": \"location_country\",\n",
        "    \"date\": \"hit_answered_date\",\n",
        "    \"hitCreationDate\": \"hit_creation_date\",\n",
        "    # \"post_to_completion_secs\"\n",
        "    }\n",
        "\n",
        "df = df.rename(rename_columns, axis = \"columns\")\n",
        "\n",
        "df['hit_answered_date'] = pd.to_datetime(df['hit_answered_date'])\n",
        "df['hit_creation_date'] = pd.to_datetime(df['hit_creation_date'])\n",
        "df[\"post_to_completion_secs\"] = (df['hit_answered_date'] - df['hit_creation_date']).dt.seconds"
      ],
      "metadata": {
        "id": "y4zmGDfUxEFl"
      },
      "execution_count": 20,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df"
      ],
      "metadata": {
        "id": "O8h_anzaztEV",
        "outputId": "ef007897-eafc-40e4-890d-592f7c73e4da",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 641
        }
      },
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                      hit_answered_date       worker_id  \\\n",
              "0      2023-05-14 22:37:21.205000+00:00  A2HIKRXLONHR0K   \n",
              "1      2023-05-14 21:38:39.797000+00:00  A2ZNBH0L55UFTN   \n",
              "2      2023-05-14 21:37:40.899000+00:00  A3VPVACAKPBRMF   \n",
              "3      2023-05-14 21:29:14.653000+00:00  A12MUUQKRCQCT7   \n",
              "4      2023-05-14 21:28:07.492000+00:00  A2NAONUAYM1I73   \n",
              "...                                 ...             ...   \n",
              "289665 2015-03-16 18:53:40.792000+00:00  A2DZW5E52SJ93H   \n",
              "289666 2015-03-16 18:53:33.341000+00:00  A164EJTEKF1ECH   \n",
              "289667 2015-03-16 18:53:19.840000+00:00  A1R3W25JDAUQ09   \n",
              "289668 2015-03-16 18:52:07.237000+00:00  A1UFD8IOZTACGH   \n",
              "289669 2015-03-16 18:52:03.956000+00:00  A2OLVF6P86QSQJ   \n",
              "\n",
              "               hit_creation_date                            ip  gender  \\\n",
              "0                            NaT  185.235.120.103, 169.254.1.1  female   \n",
              "1                            NaT  208.195.183.130, 169.254.1.1  female   \n",
              "2                            NaT     45.43.133.34, 169.254.1.1    male   \n",
              "3                            NaT  208.195.188.253, 169.254.1.1  female   \n",
              "4                            NaT  208.195.185.112, 169.254.1.1  female   \n",
              "...                          ...                           ...     ...   \n",
              "289665 2015-03-16 18:50:37+00:00                24.100.175.132    None   \n",
              "289666 2015-03-16 18:50:37+00:00                    14.96.33.7    None   \n",
              "289667 2015-03-16 18:50:37+00:00                 75.134.10.210    None   \n",
              "289668 2015-03-16 18:50:37+00:00                 108.30.59.148    None   \n",
              "289669 2015-03-16 18:50:37+00:00                 129.59.122.21    None   \n",
              "\n",
              "       household_size household_income marital_status  year_of_birth  \\\n",
              "0                   1  $25,000-$39,999        married         1970.0   \n",
              "1                   3  $10,000-$14,999        married         1985.0   \n",
              "2                  5+  $10,000-$14,999        married         1992.0   \n",
              "3                   4  $10,000-$14,999        married         1989.0   \n",
              "4                   4  $10,000-$14,999        married         1987.0   \n",
              "...               ...              ...            ...            ...   \n",
              "289665           None             None           None            NaN   \n",
              "289666           None             None           None            NaN   \n",
              "289667           None             None           None            NaN   \n",
              "289668           None             None           None            NaN   \n",
              "289669           None             None           None            NaN   \n",
              "\n",
              "                time_spent_on_mturk languages_spoken weekly_income_from_mturk  \\\n",
              "0              20-40 hours per week          English        $50-$100 per week   \n",
              "1                1-2 hours per week          English          $5-$10 per week   \n",
              "2       More than 40 hours per week          English           $1-$5 per week   \n",
              "3                4-8 hours per week          English           $1-$5 per week   \n",
              "4                4-8 hours per week          English           $1-$5 per week   \n",
              "...                             ...              ...                      ...   \n",
              "289665                         None             None                     None   \n",
              "289666                         None             None                     None   \n",
              "289667                         None             None                     None   \n",
              "289668                         None             None                     None   \n",
              "289669                         None             None                     None   \n",
              "\n",
              "              educational_level location_country location_region  \\\n",
              "0              Bachelors degree               US              ks   \n",
              "1          High School Graduate               US              ny   \n",
              "2              Bachelors degree               US               ?   \n",
              "3          High School Graduate               US              ny   \n",
              "4       Some college, no degree               US              ny   \n",
              "...                         ...              ...             ...   \n",
              "289665                     None               US              ky   \n",
              "289666                     None               IN              ka   \n",
              "289667                     None               US              ct   \n",
              "289668                     None               US              ny   \n",
              "289669                     None               US              tn   \n",
              "\n",
              "        location_city  post_to_completion_secs  \n",
              "0                   ?                      NaN  \n",
              "1            new york                      NaN  \n",
              "2                   ?                      NaN  \n",
              "3            new york                      NaN  \n",
              "4            new york                      NaN  \n",
              "...               ...                      ...  \n",
              "289665       somerset                    183.0  \n",
              "289666      bangalore                    176.0  \n",
              "289667  new fairfield                    162.0  \n",
              "289668       new york                     90.0  \n",
              "289669      nashville                     86.0  \n",
              "\n",
              "[289670 rows x 17 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-454bcf36-6a66-42e3-8951-121cb525fe4e\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>hit_answered_date</th>\n",
              "      <th>worker_id</th>\n",
              "      <th>hit_creation_date</th>\n",
              "      <th>ip</th>\n",
              "      <th>gender</th>\n",
              "      <th>household_size</th>\n",
              "      <th>household_income</th>\n",
              "      <th>marital_status</th>\n",
              "      <th>year_of_birth</th>\n",
              "      <th>time_spent_on_mturk</th>\n",
              "      <th>languages_spoken</th>\n",
              "      <th>weekly_income_from_mturk</th>\n",
              "      <th>educational_level</th>\n",
              "      <th>location_country</th>\n",
              "      <th>location_region</th>\n",
              "      <th>location_city</th>\n",
              "      <th>post_to_completion_secs</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2023-05-14 22:37:21.205000+00:00</td>\n",
              "      <td>A2HIKRXLONHR0K</td>\n",
              "      <td>NaT</td>\n",
              "      <td>185.235.120.103, 169.254.1.1</td>\n",
              "      <td>female</td>\n",
              "      <td>1</td>\n",
              "      <td>$25,000-$39,999</td>\n",
              "      <td>married</td>\n",
              "      <td>1970.0</td>\n",
              "      <td>20-40 hours per week</td>\n",
              "      <td>English</td>\n",
              "      <td>$50-$100 per week</td>\n",
              "      <td>Bachelors degree</td>\n",
              "      <td>US</td>\n",
              "      <td>ks</td>\n",
              "      <td>?</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2023-05-14 21:38:39.797000+00:00</td>\n",
              "      <td>A2ZNBH0L55UFTN</td>\n",
              "      <td>NaT</td>\n",
              "      <td>208.195.183.130, 169.254.1.1</td>\n",
              "      <td>female</td>\n",
              "      <td>3</td>\n",
              "      <td>$10,000-$14,999</td>\n",
              "      <td>married</td>\n",
              "      <td>1985.0</td>\n",
              "      <td>1-2 hours per week</td>\n",
              "      <td>English</td>\n",
              "      <td>$5-$10 per week</td>\n",
              "      <td>High School Graduate</td>\n",
              "      <td>US</td>\n",
              "      <td>ny</td>\n",
              "      <td>new york</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2023-05-14 21:37:40.899000+00:00</td>\n",
              "      <td>A3VPVACAKPBRMF</td>\n",
              "      <td>NaT</td>\n",
              "      <td>45.43.133.34, 169.254.1.1</td>\n",
              "      <td>male</td>\n",
              "      <td>5+</td>\n",
              "      <td>$10,000-$14,999</td>\n",
              "      <td>married</td>\n",
              "      <td>1992.0</td>\n",
              "      <td>More than 40 hours per week</td>\n",
              "      <td>English</td>\n",
              "      <td>$1-$5 per week</td>\n",
              "      <td>Bachelors degree</td>\n",
              "      <td>US</td>\n",
              "      <td>?</td>\n",
              "      <td>?</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2023-05-14 21:29:14.653000+00:00</td>\n",
              "      <td>A12MUUQKRCQCT7</td>\n",
              "      <td>NaT</td>\n",
              "      <td>208.195.188.253, 169.254.1.1</td>\n",
              "      <td>female</td>\n",
              "      <td>4</td>\n",
              "      <td>$10,000-$14,999</td>\n",
              "      <td>married</td>\n",
              "      <td>1989.0</td>\n",
              "      <td>4-8 hours per week</td>\n",
              "      <td>English</td>\n",
              "      <td>$1-$5 per week</td>\n",
              "      <td>High School Graduate</td>\n",
              "      <td>US</td>\n",
              "      <td>ny</td>\n",
              "      <td>new york</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2023-05-14 21:28:07.492000+00:00</td>\n",
              "      <td>A2NAONUAYM1I73</td>\n",
              "      <td>NaT</td>\n",
              "      <td>208.195.185.112, 169.254.1.1</td>\n",
              "      <td>female</td>\n",
              "      <td>4</td>\n",
              "      <td>$10,000-$14,999</td>\n",
              "      <td>married</td>\n",
              "      <td>1987.0</td>\n",
              "      <td>4-8 hours per week</td>\n",
              "      <td>English</td>\n",
              "      <td>$1-$5 per week</td>\n",
              "      <td>Some college, no degree</td>\n",
              "      <td>US</td>\n",
              "      <td>ny</td>\n",
              "      <td>new york</td>\n",
              "      <td>NaN</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",
              "      <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>289665</th>\n",
              "      <td>2015-03-16 18:53:40.792000+00:00</td>\n",
              "      <td>A2DZW5E52SJ93H</td>\n",
              "      <td>2015-03-16 18:50:37+00:00</td>\n",
              "      <td>24.100.175.132</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>US</td>\n",
              "      <td>ky</td>\n",
              "      <td>somerset</td>\n",
              "      <td>183.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>289666</th>\n",
              "      <td>2015-03-16 18:53:33.341000+00:00</td>\n",
              "      <td>A164EJTEKF1ECH</td>\n",
              "      <td>2015-03-16 18:50:37+00:00</td>\n",
              "      <td>14.96.33.7</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>IN</td>\n",
              "      <td>ka</td>\n",
              "      <td>bangalore</td>\n",
              "      <td>176.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>289667</th>\n",
              "      <td>2015-03-16 18:53:19.840000+00:00</td>\n",
              "      <td>A1R3W25JDAUQ09</td>\n",
              "      <td>2015-03-16 18:50:37+00:00</td>\n",
              "      <td>75.134.10.210</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>US</td>\n",
              "      <td>ct</td>\n",
              "      <td>new fairfield</td>\n",
              "      <td>162.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>289668</th>\n",
              "      <td>2015-03-16 18:52:07.237000+00:00</td>\n",
              "      <td>A1UFD8IOZTACGH</td>\n",
              "      <td>2015-03-16 18:50:37+00:00</td>\n",
              "      <td>108.30.59.148</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>US</td>\n",
              "      <td>ny</td>\n",
              "      <td>new york</td>\n",
              "      <td>90.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>289669</th>\n",
              "      <td>2015-03-16 18:52:03.956000+00:00</td>\n",
              "      <td>A2OLVF6P86QSQJ</td>\n",
              "      <td>2015-03-16 18:50:37+00:00</td>\n",
              "      <td>129.59.122.21</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>US</td>\n",
              "      <td>tn</td>\n",
              "      <td>nashville</td>\n",
              "      <td>86.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>289670 rows × 17 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-454bcf36-6a66-42e3-8951-121cb525fe4e')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-454bcf36-6a66-42e3-8951-121cb525fe4e button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-454bcf36-6a66-42e3-8951-121cb525fe4e');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df.dtypes"
      ],
      "metadata": {
        "id": "J_6CWNxfzgJ8",
        "outputId": "2f93313c-83af-4122-f031-752145db5b67",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "hit_answered_date           datetime64[ns, UTC]\n",
              "worker_id                                object\n",
              "hit_creation_date           datetime64[ns, UTC]\n",
              "ip                                       object\n",
              "gender                                   object\n",
              "household_size                           object\n",
              "household_income                         object\n",
              "marital_status                           object\n",
              "year_of_birth                           float64\n",
              "time_spent_on_mturk                      object\n",
              "languages_spoken                         object\n",
              "weekly_income_from_mturk                 object\n",
              "educational_level                        object\n",
              "location_country                         object\n",
              "location_region                          object\n",
              "location_city                            object\n",
              "post_to_completion_secs                 float64\n",
              "dtype: object"
            ]
          },
          "metadata": {},
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df.shape"
      ],
      "metadata": {
        "id": "gn1JKrEK4U8i",
        "outputId": "d7c2d654-f193-4322-d2c6-b252d93b0c9f",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(289670, 17)"
            ]
          },
          "metadata": {},
          "execution_count": 24
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 25,
      "metadata": {
        "id": "F7UcXyx32upi",
        "outputId": "0ca6fac7-a1c0-4821-a966-963bf9830af1",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array(['English', 'English', 'English', ..., None, None, None],\n",
              "      dtype=object)"
            ]
          },
          "metadata": {},
          "execution_count": 25
        }
      ],
      "source": [
        "df.languages_spoken.values"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "uZDGcb3V2upi"
      },
      "outputs": [],
      "source": [
        "lol = [entries.split(',') for entries in df.languages_spoken.values if entries is not None]\n",
        "s = set()\n",
        "for l in lol:\n",
        "    for m in l:\n",
        "        s.add(m)\n",
        "s"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 28,
      "metadata": {
        "id": "74feKaML2upj",
        "outputId": "28846a1d-0dde-425d-8788-e1e9a3d9b485",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "206423"
            ]
          },
          "metadata": {},
          "execution_count": 28
        }
      ],
      "source": [
        "people_with_language = len([v for v in df.languages_spoken.values if v is not None and v!=''])\n",
        "people_with_language"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 29,
      "metadata": {
        "id": "dXEx6g9o2upj",
        "outputId": "3bc0ea83-fef2-448c-ea4a-a1037bb9c1a8",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "US    216803\n",
              "IN     45349\n",
              "BR      4303\n",
              "CA      3771\n",
              "GB      3226\n",
              "       ...  \n",
              "MP         1\n",
              "TG         1\n",
              "MW         1\n",
              "XK         1\n",
              "BM         1\n",
              "Name: location_country, Length: 165, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 29
        }
      ],
      "source": [
        "df.location_country.value_counts()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 31,
      "metadata": {
        "id": "q0NV-BeF2upk",
        "outputId": "d43d8c93-cf70-45ea-cd9a-30bbc7c8f480",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "IN    13673\n",
              "US    13616\n",
              "BR     3048\n",
              "IT     1589\n",
              "CA      977\n",
              "      ...  \n",
              "ST        1\n",
              "CM        1\n",
              "LC        1\n",
              "AZ        1\n",
              "FM        1\n",
              "Name: location_country, Length: 117, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 31
        }
      ],
      "source": [
        "df [ ~pd.isnull(df.languages_spoken) & df.languages_spoken.str.contains(',') ].location_country.value_counts()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 33,
      "metadata": {
        "id": "rE-7powG2upk",
        "outputId": "a2618ac4-dcb7-49cd-a299-7de30c7578d7",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "16174"
            ]
          },
          "metadata": {},
          "execution_count": 33
        }
      ],
      "source": [
        "# bilingual and above\n",
        "len(df [ ~pd.isnull(df.languages_spoken) & df.languages_spoken.str.contains(',') ].worker_id.value_counts())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 34,
      "metadata": {
        "id": "ITuIWjkU2upl"
      },
      "outputs": [],
      "source": [
        "df['bilingual'] = df.languages_spoken.str.contains(',')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "J_XrONtF2upl"
      },
      "outputs": [],
      "source": [
        "pv_bilingual = df.pivot_table(\n",
        "    index = 'location_country',\n",
        "    columns='bilingual',\n",
        "    values = 'worker_id',\n",
        "    aggfunc='count'\n",
        ").fillna(0)\n",
        "\n",
        "pv_bilingual['perc_bilingual'] = pv_bilingual[True] / (pv_bilingual[True] + pv_bilingual[False])\n",
        "pv_bilingual['total'] = pv_bilingual[True]  + pv_bilingual[False]\n",
        "pv_bilingual.sort_values(['perc_bilingual','total'], ascending=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 38,
      "metadata": {
        "id": "D0SXO4O92upl",
        "outputId": "4c9f3fd9-ccc0-40d2-bbd8-f6ea4b808dbc",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "93040"
            ]
          },
          "metadata": {},
          "execution_count": 38
        }
      ],
      "source": [
        "# unique ids of workers that answered the language question\n",
        "len(df [ ~pd.isnull(df.languages_spoken)  ].worker_id.value_counts())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 40,
      "metadata": {
        "id": "woM9Oe2Q2upm",
        "outputId": "7b88d59f-09c6-4347-dfda-a5c0fd7495e5",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "US    153670\n",
              "IN     28736\n",
              "BR      3604\n",
              "CA      2817\n",
              "GB      2624\n",
              "       ...  \n",
              "TG         1\n",
              "HT         1\n",
              "LA         1\n",
              "AM         1\n",
              "FM         1\n",
              "Name: location_country, Length: 151, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 40
        }
      ],
      "source": [
        "df [  ~pd.isnull(df.languages_spoken) & df.languages_spoken.str.contains('English') ].location_country.value_counts()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "scrolled": true,
        "id": "GyLYFwd62upm"
      },
      "outputs": [],
      "source": [
        "df [  ~pd.isnull(df.languages_spoken) & df.languages_spoken.str.contains('Tamil') ].location_country.value_counts()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "XKDFqdGv2upm"
      },
      "outputs": [],
      "source": [
        "df [ ~pd.isnull(df.languages_spoken) & df.languages_spoken.str.contains('Spanish') ].location_country.value_counts()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "1HVybNlX2upn"
      },
      "outputs": [],
      "source": [
        "df [ ~pd.isnull(df.languages_spoken) & df.languages_spoken.str.contains('Hindi') ].location_country.value_counts()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "xtRtLszZ2upn"
      },
      "outputs": [],
      "source": [
        "df [ ~pd.isnull(df.languages_spoken) & df.languages_spoken.str.contains('French') ].location_country.value_counts()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "X8b25sB32upn"
      },
      "outputs": [],
      "source": [
        "df [ ~pd.isnull(df.languages_spoken) & df.languages_spoken.str.contains('Malayalam') ].location_country.value_counts()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "oqaSozNH2upn"
      },
      "outputs": [],
      "source": [
        "df [ ~pd.isnull(df.languages_spoken) & df.languages_spoken.str.contains('Tegulu') ].location_country.value_counts()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "u1QhEM8Q2upo"
      },
      "outputs": [],
      "source": [
        "df [ ~pd.isnull(df.languages_spoken) & df.languages_spoken.str.contains('German') ].location_country.value_counts()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "yZxqzlxr2upo"
      },
      "outputs": [],
      "source": [
        "(df [ ~pd.isnull(df.languages_spoken) & df.languages_spoken.str.contains('Chinese') ].location_country.value_counts() / df.location_country.value_counts()).sort_values(ascending=False)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "jbjMJ7892upo"
      },
      "outputs": [],
      "source": [
        "df [ ~pd.isnull(df.languages_spoken) & df.languages_spoken.str.contains('Macedonian') ].location_country.value_counts()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "dKv0wqxL2upo"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "hIESLCZF2upo"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "code",
      "execution_count": 41,
      "metadata": {
        "id": "SlLRSio_2upo"
      },
      "outputs": [],
      "source": [
        "languages = set([l for v in df.languages_spoken.values if v is not None\n",
        "                 for l in v.split(',') if l!=''])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 43,
      "metadata": {
        "id": "D4txAb4N2upp",
        "outputId": "3000f373-9566-4559-ad62-aaeadd0e103c",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "         lang  unique_workers\n",
            "79    English           91897\n",
            "107   Spanish            6301\n",
            "104     Tamil            3772\n",
            "57     French            2536\n",
            "12      Hindi            2504\n",
            "..        ...             ...\n",
            "83    Sesotho               1\n",
            "23    Quechua               1\n",
            "16     Kazakh               1\n",
            "7     Bhutani               1\n",
            "119  Setswana               1\n",
            "\n",
            "[120 rows x 2 columns]\n"
          ]
        }
      ],
      "source": [
        "\n",
        "result = []\n",
        "for language in languages:\n",
        "    people = len(set(df[ ~pd.isnull(df.languages_spoken) & df.languages_spoken.str.contains(language) ].worker_id.values))\n",
        "    result.append({\"lang\":language, \"unique_workers\": people})\n",
        "    \n",
        "\n",
        "df_cnt = pd.DataFrame(result).sort_values('unique_workers', ascending=False)    \n",
        "print (df_cnt)    \n",
        "# more than 10 people for the language\n",
        "\n",
        "#df2 = pd.DataFrame(result).sort_values('unique_workers', ascending=False)\n",
        "#enough = df2 [df2.unique_workers > 9]\n",
        "#len(enough)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 44,
      "metadata": {
        "id": "G1CESk_L2upp",
        "outputId": "cbfe9aec-dede-45fd-8adc-c95c8540834f",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "1        11\n",
              "2         8\n",
              "3        11\n",
              "4         4\n",
              "5         8\n",
              "         ..\n",
              "2504      1\n",
              "2536      1\n",
              "3772      1\n",
              "6301      1\n",
              "91897     1\n",
              "Name: unique_workers, Length: 68, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 44
        }
      ],
      "source": [
        "df_cnt.unique_workers.value_counts().sort_index()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 46,
      "metadata": {
        "id": "79HpWESU2upp",
        "outputId": "778f3fa3-62e5-4a97-d37d-35274243bf16",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "11868"
            ]
          },
          "metadata": {},
          "execution_count": 46
        }
      ],
      "source": [
        "len([v for v in df.languages_spoken.values if v is not None and 'Spanish' in v])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "-DpC61cS2upp"
      },
      "outputs": [],
      "source": [
        "1189 / 19268 * 170000"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 47,
      "metadata": {
        "id": "HMph3GbI2upp",
        "outputId": "2baee9b8-c00e-4d21-b6b0-1fd9754a5d95",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "29"
            ]
          },
          "metadata": {},
          "execution_count": 47
        }
      ],
      "source": [
        "def intersect(a, b):\n",
        "    \"\"\" return the intersection of two lists \"\"\"\n",
        "    return list(set(a) & set(b))\n",
        "\n",
        "my_language = ['English', 'Tamil', 'Spanish', 'Hindi', 'Malayalam', 'French', 'Telugu', 'Chinese', 'German', 'Kannada', 'Italian', 'Portuguese', 'Marathi', 'Arabic', 'Russian', 'Japanese', 'Gujarati', 'Urdu', 'Bengali', 'Punjabi', 'Korean', 'Tagalog', 'Romanian', 'Vietnamese', 'Greek', 'Polish', 'Dutch', 'Turkish', 'Hebrew', 'Swedish', 'Serbian', 'Nepali', 'Bulgarian', 'Macedonian', 'Oriya']\n",
        "pavlick_langauge = ['English', 'Tamil', 'Malayalam', 'Hindi', 'Spanish', 'Telugu', 'Chinese', 'Romanian', 'Portuguese', 'Arabic', 'Kannada', 'German', 'French', 'Polish', 'Urdu', 'Tagalog', 'Marathi', 'Russian', 'Italian', 'Bengali', 'Gujarati', 'Hebrew', 'Dutch', 'Turkish', 'Vietnamese', 'Macedonian', 'Cebuano', 'Swedish', 'Bulgarian', 'Swahili', 'Hungarian', 'Catalan', 'Thai', 'Lithuanian', 'Punjabi']\n",
        "\n",
        "intersect_language = intersect(my_language, pavlick_langauge)\n",
        "len(intersect_language)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 48,
      "metadata": {
        "id": "9vw_JL1s2upq"
      },
      "outputs": [],
      "source": [
        "from scipy.stats import spearmanr"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 49,
      "metadata": {
        "id": "zKAvt-Ia2upq",
        "outputId": "f5cbcc7f-e44c-435f-9cf6-e0d6f23ceac5",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "SignificanceResult(statistic=0.057703081232493, pvalue=0.7419647128381073)"
            ]
          },
          "metadata": {},
          "execution_count": 49
        }
      ],
      "source": [
        "spearmanr(my_language,pavlick_langauge)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "dJi6zH7i2upq"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "l7kVV_ko2upq"
      },
      "outputs": [],
      "source": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.5.2"
    },
    "colab": {
      "provenance": [],
      "include_colab_link": true
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}