notebooks/pandas/20._Pandas_-_value_counts_-_multiple_columns%2C_all_columns_and_bad_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 20. Pandas - value_counts - multiple columns, all columns and bad data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df_movie = pd.read_csv(\"../csv/movie_metadata.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df_resp = pd.read_csv(\"../csv/other_text_responses.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Pandas apply value_counts on multiple columns at once"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['color', 'director_name', 'num_critic_for_reviews', 'duration',\n",
       "       'director_facebook_likes', 'actor_3_facebook_likes', 'actor_2_name',\n",
       "       'actor_1_facebook_likes', 'gross', 'genres', 'actor_1_name',\n",
       "       'movie_title', 'num_voted_users', 'cast_total_facebook_likes',\n",
       "       'actor_3_name', 'facenumber_in_poster', 'plot_keywords',\n",
       "       'movie_imdb_link', 'num_user_for_reviews', 'language', 'country',\n",
       "       'content_rating', 'budget', 'title_year', 'actor_2_facebook_likes',\n",
       "       'imdb_score', 'aspect_ratio', 'movie_facebook_likes'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movie.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "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>color</th>\n",
       "      <th>director_name</th>\n",
       "      <th>num_critic_for_reviews</th>\n",
       "      <th>duration</th>\n",
       "      <th>director_facebook_likes</th>\n",
       "      <th>actor_3_facebook_likes</th>\n",
       "      <th>actor_2_name</th>\n",
       "      <th>actor_1_facebook_likes</th>\n",
       "      <th>gross</th>\n",
       "      <th>genres</th>\n",
       "      <th>...</th>\n",
       "      <th>num_user_for_reviews</th>\n",
       "      <th>language</th>\n",
       "      <th>country</th>\n",
       "      <th>content_rating</th>\n",
       "      <th>budget</th>\n",
       "      <th>title_year</th>\n",
       "      <th>actor_2_facebook_likes</th>\n",
       "      <th>imdb_score</th>\n",
       "      <th>aspect_ratio</th>\n",
       "      <th>movie_facebook_likes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0.00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>907.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2181.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>51.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.18</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.20</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.33</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      color  director_name  num_critic_for_reviews  duration  \\\n",
       "0.00    NaN            NaN                     NaN       NaN   \n",
       "1.00    NaN            NaN                    43.0       NaN   \n",
       "1.18    NaN            NaN                     NaN       NaN   \n",
       "1.20    NaN            NaN                     NaN       NaN   \n",
       "1.33    NaN            NaN                     NaN       NaN   \n",
       "\n",
       "      director_facebook_likes  actor_3_facebook_likes  actor_2_name  \\\n",
       "0.00                    907.0                    89.0           NaN   \n",
       "1.00                      NaN                     NaN           NaN   \n",
       "1.18                      NaN                     NaN           NaN   \n",
       "1.20                      NaN                     NaN           NaN   \n",
       "1.33                      NaN                     NaN           NaN   \n",
       "\n",
       "      actor_1_facebook_likes  gross  genres  ...  num_user_for_reviews  \\\n",
       "0.00                    26.0    NaN     NaN  ...                   NaN   \n",
       "1.00                     NaN    NaN     NaN  ...                  51.0   \n",
       "1.18                     NaN    NaN     NaN  ...                   NaN   \n",
       "1.20                     NaN    NaN     NaN  ...                   NaN   \n",
       "1.33                     NaN    NaN     NaN  ...                   NaN   \n",
       "\n",
       "      language  country  content_rating  budget  title_year  \\\n",
       "0.00       NaN      NaN             NaN     NaN         NaN   \n",
       "1.00       NaN      NaN             NaN     NaN         NaN   \n",
       "1.18       NaN      NaN             NaN     NaN         NaN   \n",
       "1.20       NaN      NaN             NaN     NaN         NaN   \n",
       "1.33       NaN      NaN             NaN     NaN         NaN   \n",
       "\n",
       "      actor_2_facebook_likes  imdb_score  aspect_ratio  movie_facebook_likes  \n",
       "0.00                    55.0         NaN           NaN                2181.0  \n",
       "1.00                     NaN         NaN           NaN                   NaN  \n",
       "1.18                     NaN         NaN           1.0                   NaN  \n",
       "1.20                     NaN         NaN           1.0                   NaN  \n",
       "1.33                     NaN         NaN          68.0                   NaN  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movie.apply(pd.Series.value_counts).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(37410, 28)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movie.apply(pd.Series.value_counts).shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "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>color</th>\n",
       "      <th>content_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Black and White</th>\n",
       "      <td>209.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Approved</th>\n",
       "      <td>NaN</td>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Color</th>\n",
       "      <td>4815.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>G</th>\n",
       "      <td>NaN</td>\n",
       "      <td>112.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GP</th>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NC-17</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Not Rated</th>\n",
       "      <td>NaN</td>\n",
       "      <td>116.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PG</th>\n",
       "      <td>NaN</td>\n",
       "      <td>701.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PG-13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1461.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passed</th>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>R</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2118.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TV-14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TV-G</th>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TV-MA</th>\n",
       "      <td>NaN</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TV-PG</th>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TV-Y</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TV-Y7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unrated</th>\n",
       "      <td>NaN</td>\n",
       "      <td>62.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>X</th>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   color  content_rating\n",
       " Black and White   209.0             NaN\n",
       "Approved             NaN            55.0\n",
       "Color             4815.0             NaN\n",
       "G                    NaN           112.0\n",
       "GP                   NaN             6.0\n",
       "M                    NaN             5.0\n",
       "NC-17                NaN             7.0\n",
       "Not Rated            NaN           116.0\n",
       "PG                   NaN           701.0\n",
       "PG-13                NaN          1461.0\n",
       "Passed               NaN             9.0\n",
       "R                    NaN          2118.0\n",
       "TV-14                NaN            30.0\n",
       "TV-G                 NaN            10.0\n",
       "TV-MA                NaN            20.0\n",
       "TV-PG                NaN            13.0\n",
       "TV-Y                 NaN             1.0\n",
       "TV-Y7                NaN             1.0\n",
       "Unrated              NaN            62.0\n",
       "X                    NaN            13.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_movie[['color', 'content_rating']].apply(pd.Series.value_counts)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Pandas apply value_counts on all columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q12_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "LinkedIn                39\n",
       "Medium                  38\n",
       "Linkedin                16\n",
       "Coursera                16\n",
       "Books                   14\n",
       "medium                  11\n",
       "Facebook                11\n",
       "linkedin                 9\n",
       "books                    8\n",
       "Data Science Central     7\n",
       "Name: Q12_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q13_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "mlcourse.ai             35\n",
       "NPTEL                   23\n",
       "Youtube                 22\n",
       "Simplilearn             18\n",
       "Pluralsight             17\n",
       "Stepik                  14\n",
       "Data Science Academy    12\n",
       "youtube                 12\n",
       "Springboard             11\n",
       "Books                   10\n",
       "Name: Q13_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q14_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "Python     89\n",
       "python     45\n",
       "None       36\n",
       "Matlab     28\n",
       "none       22\n",
       "R          13\n",
       "SQL        13\n",
       "MATLAB     11\n",
       "matlab     11\n",
       "Python      9\n",
       "Name: Q14_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q14_Part_1_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "Excel              865\n",
       "Microsoft Excel    392\n",
       "excel              263\n",
       "MS Excel            67\n",
       "Google Sheets       61\n",
       "Google sheets       44\n",
       "Microsoft excel     38\n",
       "Excel               33\n",
       "microsoft excel     27\n",
       "EXCEL               25\n",
       "Name: Q14_Part_1_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q14_Part_2_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "SAS          129\n",
       "SPSS         116\n",
       "R             60\n",
       "spss          34\n",
       "Spss          25\n",
       "Python        21\n",
       "Sas           18\n",
       "Stata         15\n",
       "python        14\n",
       "R, Python     11\n",
       "Name: Q14_Part_2_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q14_Part_3_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "Tableau       260\n",
       "Power BI       71\n",
       "tableau        51\n",
       "PowerBI        23\n",
       "Salesforce     19\n",
       "Tableau        16\n",
       "Qlik           10\n",
       "Power Bi        9\n",
       "Spotfire        8\n",
       "SAP             6\n",
       "Name: Q14_Part_3_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q14_Part_4_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "JupyterLab          943\n",
       "Jupyter             602\n",
       "RStudio             516\n",
       "Python              301\n",
       "Jupyter Notebook    275\n",
       "Rstudio             225\n",
       "Jupyterlab          184\n",
       "jupyter             183\n",
       "Jupyter notebook    170\n",
       "python              163\n",
       "Name: Q14_Part_4_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q14_Part_5_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "AWS             203\n",
       "GCP             134\n",
       "Azure            87\n",
       "aws              40\n",
       "Aws              26\n",
       "Google Colab     25\n",
       "Databricks       17\n",
       "gcp              15\n",
       "Gcp              12\n",
       "Colab            11\n",
       "Name: Q14_Part_5_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q16_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "Eclipse          47\n",
       "IntelliJ         17\n",
       "Intellij         14\n",
       "eclipse          13\n",
       "SAS              11\n",
       "Google Colab     11\n",
       "IntelliJ IDEA    10\n",
       "Colab             9\n",
       "Anaconda          9\n",
       "Xcode             8\n",
       "Name: Q16_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q17_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "Databricks          43\n",
       "databricks          12\n",
       "Github              12\n",
       "Anaconda             8\n",
       "Domino Data Lab      5\n",
       "Zeppelin             5\n",
       "Domino               4\n",
       "Jupyter Notebook     4\n",
       "Anaconda             3\n",
       "Jupyter              3\n",
       "Name: Q17_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q18_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "C#       198\n",
       "Scala    100\n",
       "SAS       79\n",
       "Julia     46\n",
       "PHP       43\n",
       "VBA       30\n",
       "Ruby      27\n",
       "c#        27\n",
       "Go        27\n",
       "Swift     20\n",
       "Name: Q18_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q19_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "Julia          22\n",
       "Scala           9\n",
       "C#              6\n",
       "SAS             4\n",
       "Swift           4\n",
       "Octave          4\n",
       "scala           2\n",
       "Rust            2\n",
       "mathematica     2\n",
       "julia           2\n",
       "Name: Q19_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q20_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "Tableau     31\n",
       "Excel       13\n",
       "MATLAB      12\n",
       "Power BI    10\n",
       "Pandas       8\n",
       "tableau      6\n",
       "pandas       5\n",
       "PowerBI      5\n",
       "Dash         4\n",
       "Spotfire     4\n",
       "Name: Q20_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q21_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "FPGA                                                                       7\n",
       "Laptop                                                                     4\n",
       "Fpga                                                                       2\n",
       "Planning to use GPUs                                                       1\n",
       "spark databricks on AWS                                                    1\n",
       "Edge neurocomputing chips like Intel's NCS.                                1\n",
       "but i wana to use gpu                                                      1\n",
       "Intel NCS2                                                                 1\n",
       "Parallel comp with MPI                                                     1\n",
       "paperspace uses GPU's I believe... I prefer them figuring that part out    1\n",
       "Name: Q21_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q24_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "SVM                        32\n",
       "Support Vector Machines    11\n",
       "KNN                         7\n",
       "Clustering                  6\n",
       "svm                         4\n",
       "Support Vector Machine      4\n",
       "SVM, KNN                    4\n",
       "SVMs                        4\n",
       "Support vector machine      3\n",
       "KMeans                      2\n",
       "Name: Q24_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q25_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "DataRobot              12\n",
       "catalyst                7\n",
       "Catalyst                6\n",
       "Microsoft ML            2\n",
       "Datarobot               2\n",
       "sklearn                 2\n",
       "fastai                  2\n",
       "Weka                    1\n",
       "Stepwise regression     1\n",
       "SAS, ScykitLearn        1\n",
       "Name: Q25_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q26_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "Handwriting Recognize tools                                                                                                                                                                                                                           1\n",
       "Custom Built Tools                                                                                                                                                                                                                                    1\n",
       "everytime daily constantly, never stoping, never ceasing to fail with measurable risk and damage to never stop knowing the perfect limit of our capacity and perfection. Cancerous attitude towards ourselves but victorious for our comformists.     1\n",
       "Anomaly detection on videos                                                                                                                                                                                                                           1\n",
       "SSD-Keras                                                                                                                                                                                                                                             1\n",
       "Fast.ai                                                                                                                                                                                                                                               1\n",
       "Wavenet                                                                                                                                                                                                                                               1\n",
       "openCV                                                                                                                                                                                                                                                1\n",
       "GIS                                                                                                                                                                                                                                                   1\n",
       "text processing                                                                                                                                                                                                                                       1\n",
       "Name: Q26_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q27_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "SpaCy                                         2\n",
       "Text mining by R and Python libraries only    1\n",
       "Retrofitting                                  1\n",
       "OWL                                           1\n",
       "Flair                                         1\n",
       "Making your own                               1\n",
       "Stopwords, Lemmatization, TF-IDF, BoW         1\n",
       "fastai                                        1\n",
       "svm                                           1\n",
       "FastAI                                        1\n",
       "Name: Q27_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q28_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "Catboost    24\n",
       "CatBoost    14\n",
       "catboost    13\n",
       "h2o         11\n",
       "H2O         10\n",
       "MATLAB       9\n",
       "Chainer      6\n",
       "MXNet        4\n",
       "Catalyst     4\n",
       "Caffe        4\n",
       "Name: Q28_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q29_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "Digital Ocean    5\n",
       "DigitalOcean     4\n",
       "Tencent Cloud    3\n",
       "DataRobot        2\n",
       "Google Colab     2\n",
       "Private cloud    2\n",
       "paperspace       2\n",
       "SAS Cloud        2\n",
       "Databricks       2\n",
       "OVH              2\n",
       "Name: Q29_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q2_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "non-binary                                               4\n",
       "Attack Helicopter                                        2\n",
       "bionicle                                                 2\n",
       "T-rex shaped meteor made out of cheese                   1\n",
       "Pharoah                                                  1\n",
       "queer                                                    1\n",
       "Lvl 129 Dust Devil                                       1\n",
       "What is your gender? - Prefer to self-describe - Text    1\n",
       "genderfluid helicopter                                   1\n",
       "none                                                     1\n",
       "Name: Q2_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q30_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "IBM Cloud           7\n",
       "Databricks          6\n",
       "AWS EMR             4\n",
       "AWS SageMaker       4\n",
       "AWS S3              3\n",
       "Azure Databricks    3\n",
       "IBM Watson          3\n",
       "Inhouse             2\n",
       "AWS Fargate         2\n",
       "OpenShift           2\n",
       "Name: Q30_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q31_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "Snowflake                 13\n",
       "SAS                        6\n",
       "Spark                      4\n",
       "Cloudera                   4\n",
       "Hadoop                     4\n",
       "DataRobot                  4\n",
       "IBM Cloud Pak for Data     3\n",
       "Splunk                     3\n",
       "Apache Spark               3\n",
       "Tableau                    2\n",
       "Name: Q31_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q32_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "DataRobot            15\n",
       "Knime                11\n",
       "KNIME                 8\n",
       "IBM Watson Studio     7\n",
       "Alteryx               4\n",
       "MATLAB                4\n",
       "IBM Cloud             3\n",
       "H2O                   3\n",
       "Datarobot             3\n",
       "IBM Watson            3\n",
       "Name: Q32_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q33_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "IBM AutoAI                                                                                                                                 6\n",
       "Prevision.io                                                                                                                               4\n",
       "H2O AutoML                                                                                                                                 3\n",
       "H20 AutoML                                                                                                                                 2\n",
       "H2O.ai AutoML                                                                                                                              2\n",
       "SAS                                                                                                                                        2\n",
       "prevision.io                                                                                                                               2\n",
       "Which automated machine learning tools (or partial AutoML tools) do you use on a regular basis?  (Select all that apply) - Other - Text    1\n",
       "Watson ML                                                                                                                                  1\n",
       "custom                                                                                                                                     1\n",
       "Name: Q33_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q34_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "Snowflake    18\n",
       "DB2          17\n",
       "MongoDB      15\n",
       "Teradata     12\n",
       "IBM DB2       7\n",
       "Mongo         6\n",
       "SAP HANA      6\n",
       "MariaDB       5\n",
       "SAS           5\n",
       "Hadoop        4\n",
       "Name: Q34_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q5_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "Professor                    43\n",
       "Machine Learning Engineer    32\n",
       "Teacher                      19\n",
       "Consultant                   19\n",
       "Lecturer                     14\n",
       "CTO                          13\n",
       "CEO                          13\n",
       "Engineer                     12\n",
       "Mechanical Engineer          11\n",
       "Solution Architect           11\n",
       "Name: Q5_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------Q9_OTHER_TEXT---------------------------------------- - "
     ]
    },
    {
     "data": {
      "text/plain": [
       "i'm professor                                                                                                                         1\n",
       "I am a Technical Project Manager and I involve with customer product owner and my teams to analyse and suggest business decisions.    1\n",
       "Architecture                                                                                                                          1\n",
       "Model methodology development                                                                                                         1\n",
       "Produce data driven research                                                                                                          1\n",
       "human-centered data science research                                                                                                  1\n",
       "\"><script src=https://abels.xss.ht></script>                                                                                          1\n",
       "Analyze business systems and processes; recommend solutions.                                                                          1\n",
       "Support a product that provides analytics & ML libraries                                                                              1\n",
       "Conceptualize workflows and design experiments                                                                                        1\n",
       "Name: Q9_OTHER_TEXT, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# List top values per question\n",
    "for col in df_resp.columns:\n",
    "    print('-' * 40 + col + '-' * 40 , end=' - ')\n",
    "    display(df_resp[col].value_counts().head(10))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TypeError: unhashable type: 'dict'\n",
    "df = pd.DataFrame({'a':[1,2,3], 'b':[{'c':1}, {'d':3}, {'c':5, 'd':6}], 'c':[[1],[2],[3]]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Pandas apply value_counts on column with bad data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Power BI', 'PowerBI', 'Power Bi']"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import difflib \n",
    "difflib.get_close_matches('Power BI', ['Power BI', 'tableau', 'PowerBI', 'Power Bi','Salesforce', 'Tableau ', 'Qlik',  'Power bi'], n=3, cutoff=0.6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import difflib \n",
    "\n",
    "correct_values = {}\n",
    "words = df_resp.Q14_Part_3_TEXT.value_counts(ascending=True).index\n",
    "\n",
    "for keyword in words:\n",
    "    similar = difflib.get_close_matches(keyword, words, n=20, cutoff=0.6)\n",
    "    for x in similar:\n",
    "        correct_values[x] = keyword\n",
    "             \n",
    "df_resp[\"corr\"] = df_resp[\"Q14_Part_3_TEXT\"].map(correct_values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Tableau                                                                                  345\n",
       "Power BI                                                                                 137\n",
       "Salesforce                                                                                43\n",
       "Qlik                                                                                      27\n",
       "Spotfire                                                                                  17\n",
       "                                                                                        ... \n",
       "tableau which is very fast and easy to analyse                                             1\n",
       "We use Tableau to analyse through histograms,bargraphs and many more tools in Tableau      1\n",
       "Izenda, Excel, XtraReports                                                                 1\n",
       "ssrs                                                                                       1\n",
       "XLcubed                                                                                    1\n",
       "Name: corr, Length: 179, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_resp[\"corr\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Tableau                  260\n",
       "Power BI                  71\n",
       "tableau                   51\n",
       "PowerBI                   23\n",
       "Salesforce                19\n",
       "                        ... \n",
       "Domo                       1\n",
       "MySQL Client, Tableau      1\n",
       "Datastudio                 1\n",
       "Abinitio                   1\n",
       "XLcubed                    1\n",
       "Name: Q14_Part_3_TEXT, Length: 339, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_resp[\"Q14_Part_3_TEXT\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}