CompaniesDataCleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "21a9b1c4",
   "metadata": {},
   "source": [
    "# Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9daa439f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d04c435a",
   "metadata": {},
   "source": [
    "# Files Loading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "634b935f",
   "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>Company</th>\n",
       "      <th>Company_Score</th>\n",
       "      <th>Reviews</th>\n",
       "      <th>Director</th>\n",
       "      <th>Director_Score</th>\n",
       "      <th>Company_Employee</th>\n",
       "      <th>Company_Revenue</th>\n",
       "      <th>Company_Sector_1</th>\n",
       "      <th>Company_URL_1</th>\n",
       "      <th>Company_Data_1</th>\n",
       "      <th>...</th>\n",
       "      <th>Company_Sector_2</th>\n",
       "      <th>Company_Sector_3</th>\n",
       "      <th>Null_column</th>\n",
       "      <th>Company_URL_2</th>\n",
       "      <th>Company_Data_3</th>\n",
       "      <th>Company_Data_4</th>\n",
       "      <th>Company_Sector_4</th>\n",
       "      <th>Company_URL_3</th>\n",
       "      <th>Company_Sector_5</th>\n",
       "      <th>Company_URL_4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BOK Financial</td>\n",
       "      <td>3.6</td>\n",
       "      <td>469 evaluaciones</td>\n",
       "      <td>Stacy Kymes</td>\n",
       "      <td>0.80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>Finanzas</td>\n",
       "      <td>https://www.bokfinancial.com/</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>De 1001 a 5000</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Live Nation</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1,174 evaluaciones</td>\n",
       "      <td>Michael Rapino</td>\n",
       "      <td>0.89</td>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Audiovisual y medios de comunicación</td>\n",
       "      <td>http://www.livenationentertainment.com/careers</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>más de 10 000</td>\n",
       "      <td>100 mil mdp a 200 mil mdp MXN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Amex</td>\n",
       "      <td>4.1</td>\n",
       "      <td>8,879 evaluaciones</td>\n",
       "      <td>Stephen J Squeri</td>\n",
       "      <td>0.85</td>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>Finanzas</td>\n",
       "      <td>https://www.americanexpress.com/</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>más de 10 000</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Staples</td>\n",
       "      <td>3.4</td>\n",
       "      <td>13,161 evaluaciones</td>\n",
       "      <td>Michael Motz</td>\n",
       "      <td>0.61</td>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>Ventas al mayoreo y al menudeo</td>\n",
       "      <td>https://www.staples.com/</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>más de 10 000</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M&amp;T Bank</td>\n",
       "      <td>3.5</td>\n",
       "      <td>2,474 evaluaciones</td>\n",
       "      <td>René Jones</td>\n",
       "      <td>0.78</td>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Finanzas</td>\n",
       "      <td>https://www.mtb.com/</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>más de 10 000</td>\n",
       "      <td>100 mil mdp a 200 mil mdp MXN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40554</th>\n",
       "      <td>24 Seven</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40555</th>\n",
       "      <td>Snyder Homes</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40556</th>\n",
       "      <td>Southern Teachers Agency</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40557</th>\n",
       "      <td>The Roman Catholic Diocese of Burlington</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40558</th>\n",
       "      <td>Phaxis</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>40559 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        Company  Company_Score  \\\n",
       "0                                 BOK Financial            3.6   \n",
       "1                                   Live Nation            4.1   \n",
       "2                                          Amex            4.1   \n",
       "3                                       Staples            3.4   \n",
       "4                                      M&T Bank            3.5   \n",
       "...                                         ...            ...   \n",
       "40554                                  24 Seven            NaN   \n",
       "40555                              Snyder Homes            NaN   \n",
       "40556                  Southern Teachers Agency            NaN   \n",
       "40557  The Roman Catholic Diocese of Burlington            NaN   \n",
       "40558                                    Phaxis            NaN   \n",
       "\n",
       "                   Reviews          Director  Director_Score Company_Employee  \\\n",
       "0         469 evaluaciones       Stacy Kymes            0.80              NaN   \n",
       "1       1,174 evaluaciones    Michael Rapino            0.89    más de 10 000   \n",
       "2       8,879 evaluaciones  Stephen J Squeri            0.85    más de 10 000   \n",
       "3      13,161 evaluaciones      Michael Motz            0.61    más de 10 000   \n",
       "4       2,474 evaluaciones        René Jones            0.78    más de 10 000   \n",
       "...                    ...               ...             ...              ...   \n",
       "40554                  NaN               NaN             NaN              NaN   \n",
       "40555                  NaN               NaN             NaN              NaN   \n",
       "40556                  NaN               NaN             NaN              NaN   \n",
       "40557                  NaN               NaN             NaN              NaN   \n",
       "40558                  NaN               NaN             NaN              NaN   \n",
       "\n",
       "                   Company_Revenue                      Company_Sector_1  \\\n",
       "0      más de $10 mil millones USD                              Finanzas   \n",
       "1                              NaN  Audiovisual y medios de comunicación   \n",
       "2      más de $10 mil millones USD                              Finanzas   \n",
       "3      más de $10 mil millones USD        Ventas al mayoreo y al menudeo   \n",
       "4                              NaN                              Finanzas   \n",
       "...                            ...                                   ...   \n",
       "40554                          NaN                                   NaN   \n",
       "40555                          NaN                                   NaN   \n",
       "40556                          NaN                                   NaN   \n",
       "40557                          NaN                                   NaN   \n",
       "40558                          NaN                                   NaN   \n",
       "\n",
       "                                        Company_URL_1 Company_Data_1  ...  \\\n",
       "0                       https://www.bokfinancial.com/            NaN  ...   \n",
       "1      http://www.livenationentertainment.com/careers            NaN  ...   \n",
       "2                    https://www.americanexpress.com/            NaN  ...   \n",
       "3                            https://www.staples.com/            NaN  ...   \n",
       "4                                https://www.mtb.com/            NaN  ...   \n",
       "...                                               ...            ...  ...   \n",
       "40554                                             NaN            NaN  ...   \n",
       "40555                                             NaN            NaN  ...   \n",
       "40556                                             NaN            NaN  ...   \n",
       "40557                                             NaN            NaN  ...   \n",
       "40558                                             NaN            NaN  ...   \n",
       "\n",
       "      Company_Sector_2 Company_Sector_3 Null_column  Company_URL_2  \\\n",
       "0                  NaN              NaN         NaN            NaN   \n",
       "1                  NaN              NaN         NaN            NaN   \n",
       "2                  NaN              NaN         NaN            NaN   \n",
       "3                  NaN              NaN         NaN            NaN   \n",
       "4                  NaN              NaN         NaN            NaN   \n",
       "...                ...              ...         ...            ...   \n",
       "40554              NaN              NaN         NaN            NaN   \n",
       "40555              NaN              NaN         NaN            NaN   \n",
       "40556              NaN              NaN         NaN            NaN   \n",
       "40557              NaN              NaN         NaN            NaN   \n",
       "40558              NaN              NaN         NaN            NaN   \n",
       "\n",
       "       Company_Data_3                 Company_Data_4 Company_Sector_4  \\\n",
       "0      De 1001 a 5000    más de $10 mil millones USD              NaN   \n",
       "1       más de 10 000  100 mil mdp a 200 mil mdp MXN              NaN   \n",
       "2       más de 10 000    más de $10 mil millones USD              NaN   \n",
       "3       más de 10 000    más de $10 mil millones USD              NaN   \n",
       "4       más de 10 000  100 mil mdp a 200 mil mdp MXN              NaN   \n",
       "...               ...                            ...              ...   \n",
       "40554             NaN                            NaN              NaN   \n",
       "40555             NaN                            NaN              NaN   \n",
       "40556             NaN                            NaN              NaN   \n",
       "40557             NaN                            NaN              NaN   \n",
       "40558             NaN                            NaN              NaN   \n",
       "\n",
       "      Company_URL_3 Company_Sector_5 Company_URL_4  \n",
       "0               NaN              NaN           NaN  \n",
       "1               NaN              NaN           NaN  \n",
       "2               NaN              NaN           NaN  \n",
       "3               NaN              NaN           NaN  \n",
       "4               NaN              NaN           NaN  \n",
       "...             ...              ...           ...  \n",
       "40554           NaN              NaN           NaN  \n",
       "40555           NaN              NaN           NaN  \n",
       "40556           NaN              NaN           NaN  \n",
       "40557           NaN              NaN           NaN  \n",
       "40558           NaN              NaN           NaN  \n",
       "\n",
       "[40559 rows x 21 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "headers = ['Company','Company_Score','Reviews','Director','Director_Score','Company_Employee','Company_Revenue','Company_Sector_1','Company_URL_1','Company_Data_1','Company_Data_2','Company_Sector_2','Company_Sector_3','Null_column','Company_URL_2','Company_Data_3','Company_Data_4','Company_Sector_4','Company_URL_3','Company_Sector_5','Company_URL_4']\n",
    "\n",
    "Companies = pd.read_excel('data_companies\\companies_extraction.xlsx', names = headers)\n",
    "Companies"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77ebeda5",
   "metadata": {},
   "source": [
    "# Data Cleaning\n",
    "\n",
    "Let's navigate in the Dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4f54de65",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 40559 entries, 0 to 40558\n",
      "Data columns (total 21 columns):\n",
      " #   Column            Non-Null Count  Dtype  \n",
      "---  ------            --------------  -----  \n",
      " 0   Company           40558 non-null  object \n",
      " 1   Company_Score     25982 non-null  float64\n",
      " 2   Reviews           25982 non-null  object \n",
      " 3   Director          7985 non-null   object \n",
      " 4   Director_Score    6202 non-null   float64\n",
      " 5   Company_Employee  2207 non-null   object \n",
      " 6   Company_Revenue   839 non-null    object \n",
      " 7   Company_Sector_1  9706 non-null   object \n",
      " 8   Company_URL_1     4993 non-null   object \n",
      " 9   Company_Data_1    5979 non-null   object \n",
      " 10  Company_Data_2    10336 non-null  object \n",
      " 11  Company_Sector_2  4065 non-null   object \n",
      " 12  Company_Sector_3  5504 non-null   object \n",
      " 13  Null_column       0 non-null      float64\n",
      " 14  Company_URL_2     5433 non-null   object \n",
      " 15  Company_Data_3    11483 non-null  object \n",
      " 16  Company_Data_4    5046 non-null   object \n",
      " 17  Company_Sector_4  6960 non-null   object \n",
      " 18  Company_URL_3     1708 non-null   object \n",
      " 19  Company_Sector_5  21640 non-null  object \n",
      " 20  Company_URL_4     3619 non-null   object \n",
      "dtypes: float64(3), object(18)\n",
      "memory usage: 6.5+ MB\n"
     ]
    }
   ],
   "source": [
    "Companies.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8173c5f4",
   "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>Company_Score</th>\n",
       "      <th>Director_Score</th>\n",
       "      <th>Null_column</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>25982.000000</td>\n",
       "      <td>6202.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>3.569983</td>\n",
       "      <td>0.716435</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.735675</td>\n",
       "      <td>0.125706</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.060000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>3.200000</td>\n",
       "      <td>0.640000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3.600000</td>\n",
       "      <td>0.730000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>4.000000</td>\n",
       "      <td>0.810000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>5.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Company_Score  Director_Score  Null_column\n",
       "count   25982.000000     6202.000000          0.0\n",
       "mean        3.569983        0.716435          NaN\n",
       "std         0.735675        0.125706          NaN\n",
       "min         1.000000        0.060000          NaN\n",
       "25%         3.200000        0.640000          NaN\n",
       "50%         3.600000        0.730000          NaN\n",
       "75%         4.000000        0.810000          NaN\n",
       "max         5.000000        1.000000          NaN"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bfea53eb",
   "metadata": {},
   "source": [
    "We see some facts:\n",
    "\n",
    "- Only three numerical columns. We can drop the Null_column, since we don't have values. Moreover, the column Reviews should be numerical also.\n",
    "- Some columns repeated. That's because of the extraction from Power Automate, it wasn't extracted as clean as it could. We need to merge the repeated columns into a single ones.\n",
    "- Columns \"Company_Data_x\", includes data of number of employees, or company revenue.\n",
    "\n",
    "Let's work on it!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "9df762b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "Companies = Companies.drop(columns=['Null_column'])\n",
    "Companies = Companies.dropna(how='all')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a348c89b",
   "metadata": {},
   "source": [
    "## Reviews\n",
    "\n",
    "We need to transform the column \"Reviews\" into a numerical data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6be28496",
   "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>Company</th>\n",
       "      <th>Company_Score</th>\n",
       "      <th>Reviews</th>\n",
       "      <th>Director</th>\n",
       "      <th>Director_Score</th>\n",
       "      <th>Company_Employee</th>\n",
       "      <th>Company_Revenue</th>\n",
       "      <th>Company_Sector_1</th>\n",
       "      <th>Company_URL_1</th>\n",
       "      <th>Company_Data_1</th>\n",
       "      <th>Company_Data_2</th>\n",
       "      <th>Company_Sector_2</th>\n",
       "      <th>Company_Sector_3</th>\n",
       "      <th>Company_URL_2</th>\n",
       "      <th>Company_Data_3</th>\n",
       "      <th>Company_Data_4</th>\n",
       "      <th>Company_Sector_4</th>\n",
       "      <th>Company_URL_3</th>\n",
       "      <th>Company_Sector_5</th>\n",
       "      <th>Company_URL_4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BOK Financial</td>\n",
       "      <td>3.6</td>\n",
       "      <td>469.0</td>\n",
       "      <td>Stacy Kymes</td>\n",
       "      <td>0.80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>Finanzas</td>\n",
       "      <td>https://www.bokfinancial.com/</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>De 1001 a 5000</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Live Nation</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1174.0</td>\n",
       "      <td>Michael Rapino</td>\n",
       "      <td>0.89</td>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Audiovisual y medios de comunicación</td>\n",
       "      <td>http://www.livenationentertainment.com/careers</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>100 mil mdp a 200 mil mdp MXN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Amex</td>\n",
       "      <td>4.1</td>\n",
       "      <td>8879.0</td>\n",
       "      <td>Stephen J Squeri</td>\n",
       "      <td>0.85</td>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>Finanzas</td>\n",
       "      <td>https://www.americanexpress.com/</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Staples</td>\n",
       "      <td>3.4</td>\n",
       "      <td>13161.0</td>\n",
       "      <td>Michael Motz</td>\n",
       "      <td>0.61</td>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>Ventas al mayoreo y al menudeo</td>\n",
       "      <td>https://www.staples.com/</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M&amp;T Bank</td>\n",
       "      <td>3.5</td>\n",
       "      <td>2474.0</td>\n",
       "      <td>René Jones</td>\n",
       "      <td>0.78</td>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Finanzas</td>\n",
       "      <td>https://www.mtb.com/</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>100 mil mdp a 200 mil mdp MXN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40554</th>\n",
       "      <td>24 Seven</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>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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40555</th>\n",
       "      <td>Snyder Homes</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>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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40556</th>\n",
       "      <td>Southern Teachers Agency</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>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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40557</th>\n",
       "      <td>The Roman Catholic Diocese of Burlington</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>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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40558</th>\n",
       "      <td>Phaxis</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>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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>40558 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        Company  Company_Score  Reviews  \\\n",
       "0                                 BOK Financial            3.6    469.0   \n",
       "1                                   Live Nation            4.1   1174.0   \n",
       "2                                          Amex            4.1   8879.0   \n",
       "3                                       Staples            3.4  13161.0   \n",
       "4                                      M&T Bank            3.5   2474.0   \n",
       "...                                         ...            ...      ...   \n",
       "40554                                  24 Seven            NaN      NaN   \n",
       "40555                              Snyder Homes            NaN      NaN   \n",
       "40556                  Southern Teachers Agency            NaN      NaN   \n",
       "40557  The Roman Catholic Diocese of Burlington            NaN      NaN   \n",
       "40558                                    Phaxis            NaN      NaN   \n",
       "\n",
       "               Director  Director_Score Company_Employee  \\\n",
       "0           Stacy Kymes            0.80              NaN   \n",
       "1        Michael Rapino            0.89    más de 10 000   \n",
       "2      Stephen J Squeri            0.85    más de 10 000   \n",
       "3          Michael Motz            0.61    más de 10 000   \n",
       "4            René Jones            0.78    más de 10 000   \n",
       "...                 ...             ...              ...   \n",
       "40554               NaN             NaN              NaN   \n",
       "40555               NaN             NaN              NaN   \n",
       "40556               NaN             NaN              NaN   \n",
       "40557               NaN             NaN              NaN   \n",
       "40558               NaN             NaN              NaN   \n",
       "\n",
       "                   Company_Revenue                      Company_Sector_1  \\\n",
       "0      más de $10 mil millones USD                              Finanzas   \n",
       "1                              NaN  Audiovisual y medios de comunicación   \n",
       "2      más de $10 mil millones USD                              Finanzas   \n",
       "3      más de $10 mil millones USD        Ventas al mayoreo y al menudeo   \n",
       "4                              NaN                              Finanzas   \n",
       "...                            ...                                   ...   \n",
       "40554                          NaN                                   NaN   \n",
       "40555                          NaN                                   NaN   \n",
       "40556                          NaN                                   NaN   \n",
       "40557                          NaN                                   NaN   \n",
       "40558                          NaN                                   NaN   \n",
       "\n",
       "                                        Company_URL_1 Company_Data_1  \\\n",
       "0                       https://www.bokfinancial.com/            NaN   \n",
       "1      http://www.livenationentertainment.com/careers            NaN   \n",
       "2                    https://www.americanexpress.com/            NaN   \n",
       "3                            https://www.staples.com/            NaN   \n",
       "4                                https://www.mtb.com/            NaN   \n",
       "...                                               ...            ...   \n",
       "40554                                             NaN            NaN   \n",
       "40555                                             NaN            NaN   \n",
       "40556                                             NaN            NaN   \n",
       "40557                                             NaN            NaN   \n",
       "40558                                             NaN            NaN   \n",
       "\n",
       "      Company_Data_2 Company_Sector_2 Company_Sector_3 Company_URL_2  \\\n",
       "0                NaN              NaN              NaN           NaN   \n",
       "1                NaN              NaN              NaN           NaN   \n",
       "2                NaN              NaN              NaN           NaN   \n",
       "3                NaN              NaN              NaN           NaN   \n",
       "4                NaN              NaN              NaN           NaN   \n",
       "...              ...              ...              ...           ...   \n",
       "40554            NaN              NaN              NaN           NaN   \n",
       "40555            NaN              NaN              NaN           NaN   \n",
       "40556            NaN              NaN              NaN           NaN   \n",
       "40557            NaN              NaN              NaN           NaN   \n",
       "40558            NaN              NaN              NaN           NaN   \n",
       "\n",
       "       Company_Data_3                 Company_Data_4 Company_Sector_4  \\\n",
       "0      De 1001 a 5000    más de $10 mil millones USD              NaN   \n",
       "1       más de 10 000  100 mil mdp a 200 mil mdp MXN              NaN   \n",
       "2       más de 10 000    más de $10 mil millones USD              NaN   \n",
       "3       más de 10 000    más de $10 mil millones USD              NaN   \n",
       "4       más de 10 000  100 mil mdp a 200 mil mdp MXN              NaN   \n",
       "...               ...                            ...              ...   \n",
       "40554             NaN                            NaN              NaN   \n",
       "40555             NaN                            NaN              NaN   \n",
       "40556             NaN                            NaN              NaN   \n",
       "40557             NaN                            NaN              NaN   \n",
       "40558             NaN                            NaN              NaN   \n",
       "\n",
       "      Company_URL_3 Company_Sector_5 Company_URL_4  \n",
       "0               NaN              NaN           NaN  \n",
       "1               NaN              NaN           NaN  \n",
       "2               NaN              NaN           NaN  \n",
       "3               NaN              NaN           NaN  \n",
       "4               NaN              NaN           NaN  \n",
       "...             ...              ...           ...  \n",
       "40554           NaN              NaN           NaN  \n",
       "40555           NaN              NaN           NaN  \n",
       "40556           NaN              NaN           NaN  \n",
       "40557           NaN              NaN           NaN  \n",
       "40558           NaN              NaN           NaN  \n",
       "\n",
       "[40558 rows x 20 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Reviews'] = Companies['Reviews'].fillna('0')\n",
    "Companies['Reviews'] = Companies['Reviews'].replace(regex=r'\\D', value='').astype(int)\n",
    "Companies['Reviews'] = Companies['Reviews'].replace(0, np.nan)\n",
    "\n",
    "Companies"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f25c8ab8",
   "metadata": {},
   "source": [
    "Nice!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f61da187",
   "metadata": {},
   "source": [
    "## Sector\n",
    "\n",
    "Let's try to merge the columns Sector:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "408c684d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Finanzas', 'Audiovisual y medios de comunicación', 'Finanzas',\n",
       "       'Ventas al mayoreo y al menudeo', 'Finanzas', 'Salud',\n",
       "       'Venta al por mayor', 'Electricidad y Servicios públicos', nan,\n",
       "       'Ventas al mayoreo y al menudeo',\n",
       "       'Fabricación de productos electrónicos',\n",
       "       'Energía, minería e infrastructura pública', 'Salud',\n",
       "       'Farmacéutica y biotecnología', 'Aeroespacial y defensa',\n",
       "       'Compañías de seguros', 'Ventas al mayoreo y al menudeo',\n",
       "       'Telecomunicaciones', 'Banca y Servicios de crédito', 'Seguros',\n",
       "       'Manufactura', 'Compañías de seguros', 'Salud',\n",
       "       'Transporte y logística', 'Tiendas de alimentos y de bebidas',\n",
       "       'Finanzas',\n",
       "       'Servicios de construcción, reparación y mantenimiento',\n",
       "       'Finanzas', 'Electricidad y Servicios públicos', 'Finanzas',\n",
       "       'Sitio web de Intone Networks',\n",
       "       'Audiovisual y medios de comunicación', 'Manufactura',\n",
       "       'Manufactura', 'Consultoría y servicios comerciales', 'Educación',\n",
       "       'Gestión de activos e Inversiones', 'Finanzas',\n",
       "       'Universidades y Escuelas profesionales',\n",
       "       'Restaurantes y servicios de comidas', nan,\n",
       "       'Restaurantes y servicios de comidas', 'Manufactura', 'Finanzas',\n",
       "       'Manufactura', 'Finanzas', 'Manufactura',\n",
       "       'Desarrollo de equipos informáticos', 'Finanzas',\n",
       "       'Tiendas de conveniencia'], dtype=object)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Company_Sector_1'].head(50).values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "21015287",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,\n",
       "       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,\n",
       "       nan, nan, nan, nan, 'Soporte informático', nan, nan, nan, nan, nan,\n",
       "       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,\n",
       "       nan], dtype=object)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Company_Sector_2'].head(50).values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a6a7f339",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([nan, nan, nan, nan, nan, nan, nan, nan, 'Educación', nan, nan, nan,\n",
       "       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,\n",
       "       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,\n",
       "       nan, nan, 'Educación', nan, nan, nan, nan, nan, nan, nan, nan, nan],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Company_Sector_3'].head(50).values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "c92415c3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([nan, nan, nan, ..., nan, nan, nan], dtype=object)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Company_Sector_4'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "4bc8caf5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([nan, nan, nan, ..., nan, nan, nan], dtype=object)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Company_Sector_5'].values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f5181ce",
   "metadata": {},
   "source": [
    "The values in the 2 and 3 columns looks good. However the values in column 1 looks worse. We need to set as null/empty all the values containing \"Sitio web\", because that isn't any sector. We need also to set as null the numeric values in columns 4 and 5, since they are not sector values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d1e7706f",
   "metadata": {},
   "outputs": [],
   "source": [
    "Companies['Company_Sector_1'] = Companies['Company_Sector_1'].fillna('')\n",
    "Companies.loc[Companies['Company_Sector_1'].str.contains('Sitio web'), 'Company_Sector_1'] = ''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "73794159",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Finanzas', 'Audiovisual y medios de comunicación', 'Finanzas',\n",
       "       'Ventas al mayoreo y al menudeo', 'Finanzas', 'Salud',\n",
       "       'Venta al por mayor', 'Electricidad y Servicios públicos', '',\n",
       "       'Ventas al mayoreo y al menudeo',\n",
       "       'Fabricación de productos electrónicos',\n",
       "       'Energía, minería e infrastructura pública', 'Salud',\n",
       "       'Farmacéutica y biotecnología', 'Aeroespacial y defensa',\n",
       "       'Compañías de seguros', 'Ventas al mayoreo y al menudeo',\n",
       "       'Telecomunicaciones', 'Banca y Servicios de crédito', 'Seguros',\n",
       "       'Manufactura', 'Compañías de seguros', 'Salud',\n",
       "       'Transporte y logística', 'Tiendas de alimentos y de bebidas',\n",
       "       'Finanzas',\n",
       "       'Servicios de construcción, reparación y mantenimiento',\n",
       "       'Finanzas', 'Electricidad y Servicios públicos', 'Finanzas', '',\n",
       "       'Audiovisual y medios de comunicación', 'Manufactura',\n",
       "       'Manufactura', 'Consultoría y servicios comerciales', 'Educación',\n",
       "       'Gestión de activos e Inversiones', 'Finanzas',\n",
       "       'Universidades y Escuelas profesionales',\n",
       "       'Restaurantes y servicios de comidas', '',\n",
       "       'Restaurantes y servicios de comidas', 'Manufactura', 'Finanzas',\n",
       "       'Manufactura', 'Finanzas', 'Manufactura',\n",
       "       'Desarrollo de equipos informáticos', 'Finanzas',\n",
       "       'Tiendas de conveniencia'], dtype=object)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Company_Sector_1'].head(50).values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "09088bc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "Companies.loc[Companies['Company_Sector_4'].apply(lambda x: isinstance(x, (int, float))), 'Company_Sector_4'] = None\n",
    "Companies.loc[Companies['Company_Sector_5'].apply(lambda x: isinstance(x, (int, float))), 'Company_Sector_5'] = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "a21d3874",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([None, None, None, ..., None, None, None], dtype=object)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Company_Sector_4'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "d9f3c02a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([None, None, None, ..., None, None, None], dtype=object)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Company_Sector_5'].values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a34022a5",
   "metadata": {},
   "source": [
    "Nice! Now let's merge the three columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "bb4d9e38",
   "metadata": {},
   "outputs": [],
   "source": [
    "Companies['Sector'] = Companies['Company_Sector_5'].fillna(Companies['Company_Sector_4']).fillna(Companies['Company_Sector_3']).fillna(Companies['Company_Sector_2']).fillna(Companies['Company_Sector_1'])\n",
    "Companies['Sector'] = Companies['Sector'].replace('', np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "ee574da7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n",
      "Int64Index: 40558 entries, 0 to 40558\n",
      "Series name: Sector\n",
      "Non-Null Count  Dtype \n",
      "--------------  ----- \n",
      "25073 non-null  object\n",
      "dtypes: object(1)\n",
      "memory usage: 633.7+ KB\n"
     ]
    }
   ],
   "source": [
    "Companies['Sector'].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "8b93f1dd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                                              Finanzas\n",
       "1                  Audiovisual y medios de comunicación\n",
       "2                                              Finanzas\n",
       "3                        Ventas al mayoreo y al menudeo\n",
       "4                                              Finanzas\n",
       "5                                                 Salud\n",
       "6                                    Venta al por mayor\n",
       "7                     Electricidad y Servicios públicos\n",
       "8                                             Educación\n",
       "9                        Ventas al mayoreo y al menudeo\n",
       "10                Fabricación de productos electrónicos\n",
       "11            Energía, minería e infrastructura pública\n",
       "12                                                Salud\n",
       "13                         Farmacéutica y biotecnología\n",
       "14                               Aeroespacial y defensa\n",
       "15                                 Compañías de seguros\n",
       "16                       Ventas al mayoreo y al menudeo\n",
       "17                                   Telecomunicaciones\n",
       "18                         Banca y Servicios de crédito\n",
       "19                                              Seguros\n",
       "20                                          Manufactura\n",
       "21                                 Compañías de seguros\n",
       "22                                                Salud\n",
       "23                               Transporte y logística\n",
       "24                    Tiendas de alimentos y de bebidas\n",
       "25                                             Finanzas\n",
       "26    Servicios de construcción, reparación y manten...\n",
       "27                                             Finanzas\n",
       "28                    Electricidad y Servicios públicos\n",
       "29                                             Finanzas\n",
       "Name: Sector, dtype: object"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Sector'].head(30)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8bb5ca6a",
   "metadata": {},
   "source": [
    "Nice! Now would be interesting, translate this into english, and group them somehow in a few groups. We create the dictionary sectors.xlsx in order to do that:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "16ce1b33",
   "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>Sector_spanish</th>\n",
       "      <th>Agrupación_spanish</th>\n",
       "      <th>Sector</th>\n",
       "      <th>Sector_Group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Finanzas</td>\n",
       "      <td>Finanzas</td>\n",
       "      <td>Finance</td>\n",
       "      <td>Finance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Audiovisual y medios de comunicación</td>\n",
       "      <td>Medios de comunicación</td>\n",
       "      <td>Audiovisual and Media Communications</td>\n",
       "      <td>Media Communications</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ventas al mayoreo y al menudeo</td>\n",
       "      <td>Ventas</td>\n",
       "      <td>Wholesale and Retail Sales</td>\n",
       "      <td>Sales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Salud</td>\n",
       "      <td>Salud</td>\n",
       "      <td>Healthcare</td>\n",
       "      <td>Health</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Venta al por mayor</td>\n",
       "      <td>Ventas</td>\n",
       "      <td>Wholesale Trade</td>\n",
       "      <td>Sales</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Sector_spanish      Agrupación_spanish  \\\n",
       "0                              Finanzas                Finanzas   \n",
       "1  Audiovisual y medios de comunicación  Medios de comunicación   \n",
       "2        Ventas al mayoreo y al menudeo                  Ventas   \n",
       "3                                 Salud                   Salud   \n",
       "4                    Venta al por mayor                  Ventas   \n",
       "\n",
       "                                 Sector          Sector_Group  \n",
       "0                               Finance               Finance  \n",
       "1  Audiovisual and Media Communications  Media Communications  \n",
       "2            Wholesale and Retail Sales                 Sales  \n",
       "3                            Healthcare                Health  \n",
       "4                       Wholesale Trade                 Sales  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sectors_df = pd.read_excel('master_data\\sectors.xlsx')\n",
    "\n",
    "sectors_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "22b16fdb",
   "metadata": {},
   "outputs": [],
   "source": [
    "sectors = sectors_df.set_index('Sector_spanish')['Sector'].to_dict()\n",
    "Companies['Sector'] = Companies['Sector'].map(sectors)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "28abb7c5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n",
      "Int64Index: 40558 entries, 0 to 40558\n",
      "Series name: Sector\n",
      "Non-Null Count  Dtype \n",
      "--------------  ----- \n",
      "24796 non-null  object\n",
      "dtypes: object(1)\n",
      "memory usage: 633.7+ KB\n"
     ]
    }
   ],
   "source": [
    "Companies['Sector'].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "0b8c538a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                                            Finance\n",
       "1               Audiovisual and Media Communications\n",
       "2                                            Finance\n",
       "3                         Wholesale and Retail Sales\n",
       "4                                            Finance\n",
       "5                                         Healthcare\n",
       "6                                    Wholesale Trade\n",
       "7                   Electricity and Public Utilities\n",
       "8                                          Education\n",
       "9                         Wholesale and Retail Sales\n",
       "10                  Electronic Product Manufacturing\n",
       "11         Energy, Mining, and Public Infrastructure\n",
       "12                                        Healthcare\n",
       "13                 Pharmaceuticals and Biotechnology\n",
       "14                             Aerospace and Defense\n",
       "15                               Insurance Companies\n",
       "16                        Wholesale and Retail Sales\n",
       "17                                Telecommunications\n",
       "18                       Banking and Credit Services\n",
       "19                                         Insurance\n",
       "20                                     Manufacturing\n",
       "21                               Insurance Companies\n",
       "22                                        Healthcare\n",
       "23                      Transportation and Logistics\n",
       "24                          Food and Beverage Stores\n",
       "25                                           Finance\n",
       "26    Construction, Repair, and Maintenance Services\n",
       "27                                           Finance\n",
       "28                  Electricity and Public Utilities\n",
       "29                                           Finance\n",
       "Name: Sector, dtype: object"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Sector'].head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "3c3398e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "sector_groups = sectors_df.set_index('Sector')['Sector_Group'].to_dict()\n",
    "Companies['Sector_Group'] = Companies['Sector'].map(sector_groups)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "63102400",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                               Finance\n",
       "1                  Media Communications\n",
       "2                               Finance\n",
       "3                                 Sales\n",
       "4                               Finance\n",
       "5                                Health\n",
       "6                                 Sales\n",
       "7                            Government\n",
       "8                             Education\n",
       "9                                 Sales\n",
       "10                Product Manufacturing\n",
       "11            Energy and Infrastructure\n",
       "12                               Health\n",
       "13    Pharmaceuticals and Biotechnology\n",
       "14                           Government\n",
       "15                            Insurance\n",
       "16                                Sales\n",
       "17               Information Technology\n",
       "18                              Finance\n",
       "19                            Insurance\n",
       "20                Product Manufacturing\n",
       "21                            Insurance\n",
       "22                               Health\n",
       "23         Transportation and Logistics\n",
       "24                    Food and Beverage\n",
       "25                              Finance\n",
       "26                         Construction\n",
       "27                              Finance\n",
       "28                           Government\n",
       "29                              Finance\n",
       "Name: Sector_Group, dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Sector_Group'].head(30)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88f6b8d3",
   "metadata": {},
   "source": [
    "Perfect!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "125b8294",
   "metadata": {},
   "source": [
    "## URL\n",
    "\n",
    "We must do the same with the URL columns, merge them into a single one:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "4d5f1942",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['https://www.bokfinancial.com/',\n",
       "       'http://www.livenationentertainment.com/careers',\n",
       "       'https://www.americanexpress.com/', 'https://www.staples.com/',\n",
       "       'https://www.mtb.com/', 'https://www.geisinger.org/',\n",
       "       'http://www.yamaha-motor.com/', 'http://careers.abb/', nan,\n",
       "       'http://www.publix.jobs/', 'https://www.intel.com/',\n",
       "       'http://www.halliburton.com/', 'http://www.exactsciences.com/',\n",
       "       'https://career.bayer.com/en/career',\n",
       "       'http://www.leidos.com/careers/', 'https://www.bluecrossnc.com/',\n",
       "       'https://corporate.dollartree.com/careers',\n",
       "       'http://corporate.comcast.com/', 'https://www.axosbank.com/',\n",
       "       'http://www.usablelife.com/', 'http://www.sherwin-williams.com/',\n",
       "       'https://www.arbella.com/', 'https://www.ashcompanies.com/',\n",
       "       'https://careers.southwestair.com/', 'http://www.unfi.com/',\n",
       "       'http://www.visa.com/', 'http://www.fluor.com/',\n",
       "       'https://jobs.discover.com/', 'https://www.srpnet.com/',\n",
       "       'http://www.citigroup.com/citi/', nan,\n",
       "       'http://thewaltdisneycompany.com/',\n",
       "       'http://www.conagrabrands.com/', 'http://www.whirlpoolcorp.com/',\n",
       "       'http://trustvip.com/', 'https://www.yale.edu/',\n",
       "       'https://www.capitalgroup.com/', 'http://www.lseg.com/',\n",
       "       'http://www.stanford.edu/', 'http://www.usfoods.com/', nan,\n",
       "       'https://www.labattfood.com/', 'http://www.colgatepalmolive.com/',\n",
       "       'https://search.jobs.barclays/',\n",
       "       'https://www.electroluxgroup.com/',\n",
       "       'http://www.westernalliancebank.com/', 'http://www.emerson.com/',\n",
       "       'http://www.reyrey.com/', 'https://www.globalpayments.com/',\n",
       "       'http://careers.7-eleven.com/careers/home'], dtype=object)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Company_URL_1'].head(50).values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "93d6df73",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([nan, nan, nan, nan, nan, nan, nan, nan, 'https://www.nova.edu/',\n",
       "       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,\n",
       "       nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan,\n",
       "       nan, nan, nan, nan, nan, 'http://www.artcenter.edu/', nan, nan,\n",
       "       nan, nan, nan, nan, nan, nan, nan], dtype=object)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Company_URL_2'].head(50).values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "6a57e5d6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([nan, nan, nan, ..., nan, nan, nan], dtype=object)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Company_URL_3'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "8af0df5b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([nan, nan, nan, ..., nan, nan, nan], dtype=object)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Company_URL_4'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "bd269fe9",
   "metadata": {},
   "outputs": [],
   "source": [
    "Companies['URL'] = Companies['Company_URL_4'].fillna(Companies['Company_URL_3']).fillna(Companies['Company_URL_2']).fillna(Companies['Company_URL_1'])\n",
    "Companies['URL'] = Companies['URL'].replace('', np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "293cc027",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n",
      "Int64Index: 40558 entries, 0 to 40558\n",
      "Series name: URL\n",
      "Non-Null Count  Dtype \n",
      "--------------  ----- \n",
      "15629 non-null  object\n",
      "dtypes: object(1)\n",
      "memory usage: 633.7+ KB\n"
     ]
    }
   ],
   "source": [
    "Companies['URL'].info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51c43756",
   "metadata": {},
   "source": [
    "Looks great!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da32bf48",
   "metadata": {},
   "source": [
    "## Revenue\n",
    "\n",
    "Now let's work on the revenue columns. We need to merge the Company_Revenue and Company_Data_x columns. The challenge here is that the columns \"Company_Data_1\", \"Company_Data_2\" and \"Company_Data_3\" has revenue values in some cases, but in other cases has employee values. Let's create the new column \"Revenue\". One possible approach, is to get the values including USD or MXN in these columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "ac77a8fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "Companies['Revenue'] = ''\n",
    "\n",
    "\n",
    "for index, row in Companies.iterrows():\n",
    "\n",
    "    if 'USD' in str(row['Company_Data_1']) or 'MXN' in str(row['Company_Data_1']):\n",
    "        Companies.at[index, 'Revenue'] = row['Company_Data_1']\n",
    "    elif 'USD' in str(row['Company_Data_2']) or 'MXN' in str(row['Company_Data_2']):\n",
    "        Companies.at[index, 'Revenue'] = row['Company_Data_2']\n",
    "    elif 'USD' in str(row['Company_Data_3']) or 'MXN' in str(row['Company_Data_3']):\n",
    "        Companies.at[index, 'Revenue'] = row['Company_Data_3']\n",
    "    elif 'USD' in str(row['Company_Data_4']) or 'MXN' in str(row['Company_Data_4']):\n",
    "        Companies.at[index, 'Revenue'] = row['Company_Data_4']    \n",
    "    elif 'USD' in str(row['Company_Revenue']) or 'MXN' in str(row['Company_Revenue']):\n",
    "        Companies.at[index, 'Revenue'] = row['Company_Revenue']\n",
    "        \n",
    "Companies['Revenue'] = Companies['Revenue'].replace('', np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "a0d26344",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['más de $10 mil millones USD', '100 mil mdp a 200 mil mdp MXN',\n",
       "       'más de $10 mil millones USD', 'más de $10 mil millones USD',\n",
       "       '100 mil mdp a 200 mil mdp MXN', '10 mil mdp a 20 mil mdp MXN',\n",
       "       '20 mil mdp a 100 mil mdp MXN', 'más de $10 mil millones USD',\n",
       "       '10 mil mdp a 20 mil mdp MXN', 'más de $10 mil millones USD',\n",
       "       'más de $10 mil millones USD', 'más de $10 mil millones USD',\n",
       "       '2 mil mdp a 10 mil mdp MXN', 'más de $10 mil millones USD',\n",
       "       'más de $10 mil millones USD', nan, 'más de $10 mil millones USD',\n",
       "       'más de $10 mil millones USD', nan, nan,\n",
       "       'más de $10 mil millones USD', nan, nan,\n",
       "       'más de $10 mil millones USD', 'más de $10 mil millones USD',\n",
       "       'más de $10 mil millones USD', 'más de $10 mil millones USD',\n",
       "       'más de $10 mil millones USD', nan, 'más de $10 mil millones USD',\n",
       "       '100 mdp a 500 mdp MXN', 'más de $10 mil millones USD',\n",
       "       'más de $10 mil millones USD', 'más de $10 mil millones USD',\n",
       "       '20 mdp a 100 mdp MXN', '20 mil mdp a 100 mil mdp MXN',\n",
       "       '20 mil mdp a 100 mil mdp MXN', '20 mil mdp a 100 mil mdp MXN',\n",
       "       'más de $10 mil millones USD', 'más de $10 mil millones USD',\n",
       "       '500 mdp a 2 mil mdp MXN', '20 mil mdp a 100 mil mdp MXN',\n",
       "       'más de $10 mil millones USD', 'más de $10 mil millones USD',\n",
       "       'más de $10 mil millones USD', '10 mil mdp a 20 mil mdp MXN',\n",
       "       '20 mil mdp a 100 mil mdp MXN', '20 mil mdp a 100 mil mdp MXN',\n",
       "       '100 mil mdp a 200 mil mdp MXN', 'más de $10 mil millones USD'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Revenue'].head(50).values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "52f19a50",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n",
      "Int64Index: 40558 entries, 0 to 40558\n",
      "Series name: Revenue\n",
      "Non-Null Count  Dtype \n",
      "--------------  ----- \n",
      "13314 non-null  object\n",
      "dtypes: object(1)\n",
      "memory usage: 1.6+ MB\n"
     ]
    }
   ],
   "source": [
    "Companies['Revenue'].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "d431d8da",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2 mil mdp a 10 mil mdp MXN       2464\n",
       "500 mdp a 2 mil mdp MXN          2345\n",
       "100 mdp a 500 mdp MXN            2202\n",
       "20 mil mdp a 100 mil mdp MXN     1758\n",
       "20 mdp a 100 mdp MXN             1138\n",
       "10 mil mdp a 20 mil mdp MXN       965\n",
       "más de $10 mil millones USD       891\n",
       "menos de $1 millón USD            800\n",
       "100 mil mdp a 200 mil mdp MXN     465\n",
       "$25M to $100M (USD)                73\n",
       "$5M to $25M (USD)                  56\n",
       "$100M to $500M (USD)               50\n",
       "$1B to $5B (USD)                   35\n",
       "$500M to $1B (USD)                 22\n",
       "less than $1M (USD)                19\n",
       "Name: Revenue, dtype: int64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies['Revenue'].value_counts().head(15)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e188b559",
   "metadata": {},
   "source": [
    "Nice! We see the values are weird, some in MXN and other in USD. The approach I'd like is to have here a categorical column, showing the size in revenue terms. Let's say, for example:\n",
    "\n",
    "- Very Small\n",
    "- Small\n",
    "- Medium\n",
    "- Big\n",
    "- Very big\n",
    "\n",
    "That's just an example. Let's create a dictionary in revenue.xlsx, and let's apply it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "efcfd3de",
   "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>Keyword</th>\n",
       "      <th>Revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>menos de $1 millón USD</td>\n",
       "      <td>XXXS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20 mdp a 100 mdp MXN</td>\n",
       "      <td>XXS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100 mdp a 500 mdp MXN</td>\n",
       "      <td>XS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>500 mdp a 2 mil mdp MXN</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2 mil mdp a 10 mil mdp MXN</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10 mil mdp a 20 mil mdp MXN</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20 mil mdp a 100 mil mdp MXN</td>\n",
       "      <td>XL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>100 mil mdp a 200 mil mdp MXN</td>\n",
       "      <td>XXL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>XXXL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Keyword Revenue\n",
       "0         menos de $1 millón USD    XXXS\n",
       "1           20 mdp a 100 mdp MXN     XXS\n",
       "2          100 mdp a 500 mdp MXN      XS\n",
       "3        500 mdp a 2 mil mdp MXN       S\n",
       "4     2 mil mdp a 10 mil mdp MXN       M\n",
       "5    10 mil mdp a 20 mil mdp MXN       L\n",
       "6   20 mil mdp a 100 mil mdp MXN      XL\n",
       "7  100 mil mdp a 200 mil mdp MXN     XXL\n",
       "8    más de $10 mil millones USD    XXXL"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "revenue_df = pd.read_excel('master_data/revenue.xlsx')\n",
    "\n",
    "revenue_df.head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "18e20aba",
   "metadata": {},
   "outputs": [],
   "source": [
    "revenue_dict = revenue_df.set_index('Keyword')['Revenue'].to_dict()\n",
    "\n",
    "def assign_revenue(row):\n",
    "    for col in ['Revenue']:\n",
    "        if isinstance(row[col], str):\n",
    "            for keyword, revenue in revenue_dict.items():\n",
    "                if keyword.lower() in row[col].lower():\n",
    "                    return revenue\n",
    "    return None\n",
    "\n",
    "Companies['Revenue'] = Companies.apply(assign_revenue, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "fb47ff34",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "M       2464\n",
      "S       2345\n",
      "XS      2202\n",
      "XL      1758\n",
      "XXS     1138\n",
      "L        965\n",
      "XXXL     891\n",
      "XXXS     800\n",
      "XXL      465\n",
      "Name: Revenue, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "revenue_counts = Companies['Revenue'].value_counts()\n",
    "\n",
    "print(revenue_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "71199d2e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n",
      "Int64Index: 40558 entries, 0 to 40558\n",
      "Series name: Revenue\n",
      "Non-Null Count  Dtype \n",
      "--------------  ----- \n",
      "13028 non-null  object\n",
      "dtypes: object(1)\n",
      "memory usage: 1.6+ MB\n"
     ]
    }
   ],
   "source": [
    "Companies['Revenue'].info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d52d7e7",
   "metadata": {},
   "source": [
    "Perfect!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57fd4a81",
   "metadata": {},
   "source": [
    "## Employee\n",
    "\n",
    "The next column is the Employee one. As we saw in Revenue column, here we have data mixed in the Company_Data_x columns, as well as we have data in Company_Employee column. The approach now is to get the values without the strings \"USD\" and \"MXN\", and containing numbers. Let's do it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "d1a24aa0",
   "metadata": {},
   "outputs": [],
   "source": [
    "def filtrar_empleados(df, columnas):\n",
    "    \n",
    "    def filtrar_valor(valor):\n",
    "        if pd.notnull(valor) and not any(keyword in str(valor) for keyword in ['USD', 'MXN']):\n",
    "            return valor\n",
    "        return None\n",
    "\n",
    "    \n",
    "    df['Employee'] = None\n",
    "\n",
    "    \n",
    "    for columna in columnas:\n",
    "        filtro = df[columna].apply(filtrar_valor)\n",
    "        df.loc[filtro.notnull(), 'Employee'] = df.loc[filtro.notnull(), columna]\n",
    "\n",
    "    return df\n",
    "\n",
    "Companies = filtrar_empleados(Companies, ['Company_Employee', 'Company_Data_1', 'Company_Data_2', 'Company_Data_3','Company_Data_4'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "021068e9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "De 1001 a 5000        3777\n",
      "51 a 200              3636\n",
      "201 a 500             2804\n",
      "11 a 50               2657\n",
      "más de 10 000         2182\n",
      "501 a 1.000           2156\n",
      "5.001 a 10.000        1161\n",
      "menos de 10            708\n",
      "201 to 500              96\n",
      "51 to 200               84\n",
      "1001 to 5,000           56\n",
      "11 to 50                53\n",
      "501 to 1,000            52\n",
      "more than 10,000        28\n",
      "Hidden by employer      26\n",
      "5,001 to 10,000         24\n",
      "less than 10            13\n",
      "De 1 a 50               12\n",
      "Name: Employee, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "employee_counts = Companies['Employee'].value_counts()\n",
    "\n",
    "print(employee_counts)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04e93a82",
   "metadata": {},
   "source": [
    "Nice! Now let's create a dictionary employees.xlsx and let's apply it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "e266f028",
   "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>Keyword</th>\n",
       "      <th>Employees</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>menos de 10</td>\n",
       "      <td>XXXS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>De 1 a 50</td>\n",
       "      <td>XXS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11 a 50</td>\n",
       "      <td>XS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>51 a 200</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201 a 500</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>501 a 1.000</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>De 1001 a 5000</td>\n",
       "      <td>XL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5.001 a 10.000</td>\n",
       "      <td>XXL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>XXXL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Keyword Employees\n",
       "0     menos de 10      XXXS\n",
       "1       De 1 a 50       XXS\n",
       "2         11 a 50        XS\n",
       "3        51 a 200         S\n",
       "4       201 a 500         M\n",
       "5     501 a 1.000         L\n",
       "6  De 1001 a 5000        XL\n",
       "7  5.001 a 10.000       XXL\n",
       "8   más de 10 000      XXXL"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employee_df = pd.read_excel('master_data/employees.xlsx')\n",
    "\n",
    "employee_df.head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "fc62e8e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "employee_dict = employee_df.set_index('Keyword')['Employees'].to_dict()\n",
    "\n",
    "def assign_employee(row):\n",
    "    for col in ['Employee']:\n",
    "        if isinstance(row[col], str):\n",
    "            for keyword, employees in employee_dict.items():\n",
    "                if keyword.lower() in row[col].lower():\n",
    "                    return employees\n",
    "    return None\n",
    "\n",
    "Companies['Employee'] = Companies.apply(assign_employee, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "626fb30c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "XL      3777\n",
      "S       3636\n",
      "M       2804\n",
      "XS      2657\n",
      "XXXL    2182\n",
      "L       2156\n",
      "XXL     1161\n",
      "XXXS     708\n",
      "XXS       12\n",
      "Name: Employee, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "employee_counts = Companies['Employee'].value_counts()\n",
    "\n",
    "print(employee_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "8b1d6dbc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n",
      "Int64Index: 40558 entries, 0 to 40558\n",
      "Series name: Employee\n",
      "Non-Null Count  Dtype \n",
      "--------------  ----- \n",
      "19093 non-null  object\n",
      "dtypes: object(1)\n",
      "memory usage: 1.6+ MB\n"
     ]
    }
   ],
   "source": [
    "Companies['Employee'].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "672f987b",
   "metadata": {
    "scrolled": false
   },
   "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>Company</th>\n",
       "      <th>Company_Score</th>\n",
       "      <th>Reviews</th>\n",
       "      <th>Director</th>\n",
       "      <th>Director_Score</th>\n",
       "      <th>Company_Employee</th>\n",
       "      <th>Company_Revenue</th>\n",
       "      <th>Company_Sector_1</th>\n",
       "      <th>Company_URL_1</th>\n",
       "      <th>Company_Data_1</th>\n",
       "      <th>...</th>\n",
       "      <th>Company_Data_4</th>\n",
       "      <th>Company_Sector_4</th>\n",
       "      <th>Company_URL_3</th>\n",
       "      <th>Company_Sector_5</th>\n",
       "      <th>Company_URL_4</th>\n",
       "      <th>Sector</th>\n",
       "      <th>Sector_Group</th>\n",
       "      <th>URL</th>\n",
       "      <th>Revenue</th>\n",
       "      <th>Employee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BOK Financial</td>\n",
       "      <td>3.6</td>\n",
       "      <td>469.0</td>\n",
       "      <td>Stacy Kymes</td>\n",
       "      <td>0.80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>Finanzas</td>\n",
       "      <td>https://www.bokfinancial.com/</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Finance</td>\n",
       "      <td>Finance</td>\n",
       "      <td>https://www.bokfinancial.com/</td>\n",
       "      <td>XXXL</td>\n",
       "      <td>XL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Live Nation</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1174.0</td>\n",
       "      <td>Michael Rapino</td>\n",
       "      <td>0.89</td>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Audiovisual y medios de comunicación</td>\n",
       "      <td>http://www.livenationentertainment.com/careers</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>100 mil mdp a 200 mil mdp MXN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Audiovisual and Media Communications</td>\n",
       "      <td>Media Communications</td>\n",
       "      <td>http://www.livenationentertainment.com/careers</td>\n",
       "      <td>XXL</td>\n",
       "      <td>XXXL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Amex</td>\n",
       "      <td>4.1</td>\n",
       "      <td>8879.0</td>\n",
       "      <td>Stephen J Squeri</td>\n",
       "      <td>0.85</td>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>Finanzas</td>\n",
       "      <td>https://www.americanexpress.com/</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Finance</td>\n",
       "      <td>Finance</td>\n",
       "      <td>https://www.americanexpress.com/</td>\n",
       "      <td>XXXL</td>\n",
       "      <td>XXXL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Staples</td>\n",
       "      <td>3.4</td>\n",
       "      <td>13161.0</td>\n",
       "      <td>Michael Motz</td>\n",
       "      <td>0.61</td>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>Ventas al mayoreo y al menudeo</td>\n",
       "      <td>https://www.staples.com/</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>más de $10 mil millones USD</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Wholesale and Retail Sales</td>\n",
       "      <td>Sales</td>\n",
       "      <td>https://www.staples.com/</td>\n",
       "      <td>XXXL</td>\n",
       "      <td>XXXL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M&amp;T Bank</td>\n",
       "      <td>3.5</td>\n",
       "      <td>2474.0</td>\n",
       "      <td>René Jones</td>\n",
       "      <td>0.78</td>\n",
       "      <td>más de 10 000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Finanzas</td>\n",
       "      <td>https://www.mtb.com/</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>100 mil mdp a 200 mil mdp MXN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Finance</td>\n",
       "      <td>Finance</td>\n",
       "      <td>https://www.mtb.com/</td>\n",
       "      <td>XXL</td>\n",
       "      <td>XXXL</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40554</th>\n",
       "      <td>24 Seven</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>...</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>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40555</th>\n",
       "      <td>Snyder Homes</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>...</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>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40556</th>\n",
       "      <td>Southern Teachers Agency</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>...</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>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40557</th>\n",
       "      <td>The Roman Catholic Diocese of Burlington</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>...</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>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40558</th>\n",
       "      <td>Phaxis</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>...</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>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>40558 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        Company  Company_Score  Reviews  \\\n",
       "0                                 BOK Financial            3.6    469.0   \n",
       "1                                   Live Nation            4.1   1174.0   \n",
       "2                                          Amex            4.1   8879.0   \n",
       "3                                       Staples            3.4  13161.0   \n",
       "4                                      M&T Bank            3.5   2474.0   \n",
       "...                                         ...            ...      ...   \n",
       "40554                                  24 Seven            NaN      NaN   \n",
       "40555                              Snyder Homes            NaN      NaN   \n",
       "40556                  Southern Teachers Agency            NaN      NaN   \n",
       "40557  The Roman Catholic Diocese of Burlington            NaN      NaN   \n",
       "40558                                    Phaxis            NaN      NaN   \n",
       "\n",
       "               Director  Director_Score Company_Employee  \\\n",
       "0           Stacy Kymes            0.80              NaN   \n",
       "1        Michael Rapino            0.89    más de 10 000   \n",
       "2      Stephen J Squeri            0.85    más de 10 000   \n",
       "3          Michael Motz            0.61    más de 10 000   \n",
       "4            René Jones            0.78    más de 10 000   \n",
       "...                 ...             ...              ...   \n",
       "40554               NaN             NaN              NaN   \n",
       "40555               NaN             NaN              NaN   \n",
       "40556               NaN             NaN              NaN   \n",
       "40557               NaN             NaN              NaN   \n",
       "40558               NaN             NaN              NaN   \n",
       "\n",
       "                   Company_Revenue                      Company_Sector_1  \\\n",
       "0      más de $10 mil millones USD                              Finanzas   \n",
       "1                              NaN  Audiovisual y medios de comunicación   \n",
       "2      más de $10 mil millones USD                              Finanzas   \n",
       "3      más de $10 mil millones USD        Ventas al mayoreo y al menudeo   \n",
       "4                              NaN                              Finanzas   \n",
       "...                            ...                                   ...   \n",
       "40554                          NaN                                         \n",
       "40555                          NaN                                         \n",
       "40556                          NaN                                         \n",
       "40557                          NaN                                         \n",
       "40558                          NaN                                         \n",
       "\n",
       "                                        Company_URL_1 Company_Data_1  ...  \\\n",
       "0                       https://www.bokfinancial.com/            NaN  ...   \n",
       "1      http://www.livenationentertainment.com/careers            NaN  ...   \n",
       "2                    https://www.americanexpress.com/            NaN  ...   \n",
       "3                            https://www.staples.com/            NaN  ...   \n",
       "4                                https://www.mtb.com/            NaN  ...   \n",
       "...                                               ...            ...  ...   \n",
       "40554                                             NaN            NaN  ...   \n",
       "40555                                             NaN            NaN  ...   \n",
       "40556                                             NaN            NaN  ...   \n",
       "40557                                             NaN            NaN  ...   \n",
       "40558                                             NaN            NaN  ...   \n",
       "\n",
       "                      Company_Data_4 Company_Sector_4 Company_URL_3  \\\n",
       "0        más de $10 mil millones USD             None           NaN   \n",
       "1      100 mil mdp a 200 mil mdp MXN             None           NaN   \n",
       "2        más de $10 mil millones USD             None           NaN   \n",
       "3        más de $10 mil millones USD             None           NaN   \n",
       "4      100 mil mdp a 200 mil mdp MXN             None           NaN   \n",
       "...                              ...              ...           ...   \n",
       "40554                            NaN              NaN           NaN   \n",
       "40555                            NaN              NaN           NaN   \n",
       "40556                            NaN              NaN           NaN   \n",
       "40557                            NaN              NaN           NaN   \n",
       "40558                            NaN              NaN           NaN   \n",
       "\n",
       "      Company_Sector_5 Company_URL_4                                Sector  \\\n",
       "0                 None           NaN                               Finance   \n",
       "1                 None           NaN  Audiovisual and Media Communications   \n",
       "2                 None           NaN                               Finance   \n",
       "3                 None           NaN            Wholesale and Retail Sales   \n",
       "4                 None           NaN                               Finance   \n",
       "...                ...           ...                                   ...   \n",
       "40554              NaN           NaN                                   NaN   \n",
       "40555              NaN           NaN                                   NaN   \n",
       "40556              NaN           NaN                                   NaN   \n",
       "40557              NaN           NaN                                   NaN   \n",
       "40558              NaN           NaN                                   NaN   \n",
       "\n",
       "               Sector_Group                                             URL  \\\n",
       "0                   Finance                   https://www.bokfinancial.com/   \n",
       "1      Media Communications  http://www.livenationentertainment.com/careers   \n",
       "2                   Finance                https://www.americanexpress.com/   \n",
       "3                     Sales                        https://www.staples.com/   \n",
       "4                   Finance                            https://www.mtb.com/   \n",
       "...                     ...                                             ...   \n",
       "40554                   NaN                                             NaN   \n",
       "40555                   NaN                                             NaN   \n",
       "40556                   NaN                                             NaN   \n",
       "40557                   NaN                                             NaN   \n",
       "40558                   NaN                                             NaN   \n",
       "\n",
       "      Revenue Employee  \n",
       "0        XXXL       XL  \n",
       "1         XXL     XXXL  \n",
       "2        XXXL     XXXL  \n",
       "3        XXXL     XXXL  \n",
       "4         XXL     XXXL  \n",
       "...       ...      ...  \n",
       "40554     NaN     None  \n",
       "40555     NaN     None  \n",
       "40556     NaN     None  \n",
       "40557     NaN     None  \n",
       "40558     NaN     None  \n",
       "\n",
       "[40558 rows x 25 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Companies"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8585e11",
   "metadata": {},
   "source": [
    "Looks great!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6a6adc2",
   "metadata": {},
   "source": [
    "# Generating files\n",
    "\n",
    "Now let's select the important columns, and generate them into a csv and xlsx file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "fbe1b225",
   "metadata": {},
   "outputs": [],
   "source": [
    "Companies_file = Companies[['Company','Sector','Sector_Group','Revenue','Employee','Company_Score','Reviews','Director','Director_Score','URL']]\n",
    "Companies_file.to_excel('companies.xlsx', index=False)\n",
    "Companies_file.to_csv('companies.csv', index=False, encoding=\"utf-16\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5777ecf4",
   "metadata": {},
   "source": [
    "# THANK YOU!!"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}