Data Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#imports\n",
    "\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import os\n",
    "\n",
    "#regex\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a dataframe from csv file\n",
    "\n",
    "cwd = os.getcwd()\n",
    "\n",
    "df = pd.read_csv(cwd+\"/BA_reviews.csv\", index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>reviews</th>\n",
       "      <th>stars</th>\n",
       "      <th>date</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Not Verified |  Happy with the flight crew. Ha...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>24th September 2024</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>✅ Trip Verified |   Horrible service from boar...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>22nd September 2024</td>\n",
       "      <td>Morocco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Not Verified |  My wife and I are very disappo...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13th September 2024</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Not Verified |  We flew BA between Heathrow an...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13th September 2024</td>\n",
       "      <td>Australia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Not Verified |  Absolutely disgusted with BA. ...</td>\n",
       "      <td>8.0</td>\n",
       "      <td>13th September 2024</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             reviews  stars  \\\n",
       "0  Not Verified |  Happy with the flight crew. Ha...    5.0   \n",
       "1  ✅ Trip Verified |   Horrible service from boar...   10.0   \n",
       "2  Not Verified |  My wife and I are very disappo...    1.0   \n",
       "3  Not Verified |  We flew BA between Heathrow an...    1.0   \n",
       "4  Not Verified |  Absolutely disgusted with BA. ...    8.0   \n",
       "\n",
       "                  date         country  \n",
       "0  24th September 2024   United States  \n",
       "1  22nd September 2024         Morocco  \n",
       "2  13th September 2024   United States  \n",
       "3  13th September 2024       Australia  \n",
       "4  13th September 2024  United Kingdom  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['verified'] = df.reviews.str.contains(\"Trip Verified\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       False\n",
       "1        True\n",
       "2       False\n",
       "3       False\n",
       "4       False\n",
       "        ...  \n",
       "3495    False\n",
       "3496    False\n",
       "3497    False\n",
       "3498    False\n",
       "3499    False\n",
       "Name: verified, Length: 3500, dtype: bool"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['verified']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "import spacy\n",
    "\n",
    "nlp = spacy.load(\"en_core_web_sm\")\n",
    "\n",
    "stop_words = spacy.lang.en.stop_words.STOP_WORDS\n",
    "\n",
    "reviews_data = df.reviews.str.replace(r\"✅ Trip Verified \\|\", \"\", regex=True).str.strip()\n",
    "\n",
    "corpus = []\n",
    "\n",
    "for rev in reviews_data:\n",
    "    rev = re.sub(r'[^a-zA-Z]', ' ', rev)  \n",
    "    rev = rev.lower()  \n",
    "    doc = nlp(rev)  \n",
    "    tokens = [token.lemma_ for token in doc if token.text not in stop_words and not token.is_punct]\n",
    "    cleaned_review = \" \".join(tokens)  \n",
    "    corpus.append(cleaned_review)  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['corpus'] = corpus"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>reviews</th>\n",
       "      <th>stars</th>\n",
       "      <th>date</th>\n",
       "      <th>country</th>\n",
       "      <th>verified</th>\n",
       "      <th>corpus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Not Verified |  Happy with the flight crew. Ha...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>24th September 2024</td>\n",
       "      <td>United States</td>\n",
       "      <td>False</td>\n",
       "      <td>verify     happy flight crew   hadn t plane   ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>✅ Trip Verified |   Horrible service from boar...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>22nd September 2024</td>\n",
       "      <td>Morocco</td>\n",
       "      <td>True</td>\n",
       "      <td>horrible service boarding landing   fly london...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Not Verified |  My wife and I are very disappo...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13th September 2024</td>\n",
       "      <td>United States</td>\n",
       "      <td>False</td>\n",
       "      <td>verify     wife disappointed fly british airwa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Not Verified |  We flew BA between Heathrow an...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13th September 2024</td>\n",
       "      <td>Australia</td>\n",
       "      <td>False</td>\n",
       "      <td>verify     fly ba heathrow berlin way connecti...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Not Verified |  Absolutely disgusted with BA. ...</td>\n",
       "      <td>8.0</td>\n",
       "      <td>13th September 2024</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>False</td>\n",
       "      <td>verify     absolutely disgusted ba   flight ca...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             reviews  stars  \\\n",
       "0  Not Verified |  Happy with the flight crew. Ha...    5.0   \n",
       "1  ✅ Trip Verified |   Horrible service from boar...   10.0   \n",
       "2  Not Verified |  My wife and I are very disappo...    1.0   \n",
       "3  Not Verified |  We flew BA between Heathrow an...    1.0   \n",
       "4  Not Verified |  Absolutely disgusted with BA. ...    8.0   \n",
       "\n",
       "                  date         country  verified  \\\n",
       "0  24th September 2024   United States     False   \n",
       "1  22nd September 2024         Morocco      True   \n",
       "2  13th September 2024   United States     False   \n",
       "3  13th September 2024       Australia     False   \n",
       "4  13th September 2024  United Kingdom     False   \n",
       "\n",
       "                                              corpus  \n",
       "0  verify     happy flight crew   hadn t plane   ...  \n",
       "1  horrible service boarding landing   fly london...  \n",
       "2  verify     wife disappointed fly british airwa...  \n",
       "3  verify     fly ba heathrow berlin way connecti...  \n",
       "4  verify     absolutely disgusted ba   flight ca...  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "reviews      object\n",
       "stars       float64\n",
       "date         object\n",
       "country      object\n",
       "verified       bool\n",
       "corpus       object\n",
       "dtype: object"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.date = pd.to_datetime(df.date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    24th September 2024\n",
       "1    22nd September 2024\n",
       "2    13th September 2024\n",
       "3    13th September 2024\n",
       "4    13th September 2024\n",
       "Name: date, dtype: object"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.date.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 5., 10.,  1.,  8.,  4.,  2.,  9.,  3.,  6.,  7., nan])"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df.stars.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "stars\n",
       "1.0     868\n",
       "2.0     407\n",
       "3.0     398\n",
       "8.0     341\n",
       "10.0    286\n",
       "7.0     272\n",
       "9.0     270\n",
       "5.0     246\n",
       "4.0     236\n",
       "6.0     173\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.stars.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df.drop(df[df.stars == \"None\"].index, axis=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 5., 10.,  1.,  8.,  4.,  2.,  9.,  3.,  6.,  7., nan])"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df.stars.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "reviews  stars  date   country  verified  corpus\n",
       "False    False  False  False    False     False     3496\n",
       "         True   False  False    False     False        3\n",
       "         False  False  True     False     False        1\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country\n",
       "False    3499\n",
       "True        1\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.country.isnull().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop the rows using index where the country value is null\n",
    "df.drop(df[df.country.isnull() == True].index, axis=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3499, 6)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>reviews</th>\n",
       "      <th>stars</th>\n",
       "      <th>date</th>\n",
       "      <th>country</th>\n",
       "      <th>verified</th>\n",
       "      <th>corpus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Not Verified |  Happy with the flight crew. Ha...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>24th September 2024</td>\n",
       "      <td>United States</td>\n",
       "      <td>False</td>\n",
       "      <td>verify     happy flight crew   hadn t plane   ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>✅ Trip Verified |   Horrible service from boar...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>22nd September 2024</td>\n",
       "      <td>Morocco</td>\n",
       "      <td>True</td>\n",
       "      <td>horrible service boarding landing   fly london...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Not Verified |  My wife and I are very disappo...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13th September 2024</td>\n",
       "      <td>United States</td>\n",
       "      <td>False</td>\n",
       "      <td>verify     wife disappointed fly british airwa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Not Verified |  We flew BA between Heathrow an...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13th September 2024</td>\n",
       "      <td>Australia</td>\n",
       "      <td>False</td>\n",
       "      <td>verify     fly ba heathrow berlin way connecti...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Not Verified |  Absolutely disgusted with BA. ...</td>\n",
       "      <td>8.0</td>\n",
       "      <td>13th September 2024</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>False</td>\n",
       "      <td>verify     absolutely disgusted ba   flight ca...</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3494</th>\n",
       "      <td>Travelled to Palma in new BA 'sardine' busines...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>28th October 2014</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>False</td>\n",
       "      <td>travel palma new ba   sardine   business class...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3495</th>\n",
       "      <td>We flew London Heathrow to Singapore business ...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>28th October 2014</td>\n",
       "      <td>Australia</td>\n",
       "      <td>False</td>\n",
       "      <td>fly london heathrow singapore business class t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3496</th>\n",
       "      <td>GIG-LHR B773 Newly refurbished cabin. Seats we...</td>\n",
       "      <td>9.0</td>\n",
       "      <td>28th October 2014</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>False</td>\n",
       "      <td>gig lhr b     newly refurbish cabin   seat goo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3497</th>\n",
       "      <td>LHR-DEL. RTN Club World. Excellent service on ...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>28th October 2014</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>False</td>\n",
       "      <td>lhr del   rtn club world   excellent service s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3498</th>\n",
       "      <td>Flew BA from Toronto to New Delhi via London i...</td>\n",
       "      <td>9.0</td>\n",
       "      <td>28th October 2014</td>\n",
       "      <td>Canada</td>\n",
       "      <td>False</td>\n",
       "      <td>fly ba toronto new delhi london premium econom...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3499 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                reviews  stars  \\\n",
       "0     Not Verified |  Happy with the flight crew. Ha...    5.0   \n",
       "1     ✅ Trip Verified |   Horrible service from boar...   10.0   \n",
       "2     Not Verified |  My wife and I are very disappo...    1.0   \n",
       "3     Not Verified |  We flew BA between Heathrow an...    1.0   \n",
       "4     Not Verified |  Absolutely disgusted with BA. ...    8.0   \n",
       "...                                                 ...    ...   \n",
       "3494  Travelled to Palma in new BA 'sardine' busines...    1.0   \n",
       "3495  We flew London Heathrow to Singapore business ...    3.0   \n",
       "3496  GIG-LHR B773 Newly refurbished cabin. Seats we...    9.0   \n",
       "3497  LHR-DEL. RTN Club World. Excellent service on ...    6.0   \n",
       "3498  Flew BA from Toronto to New Delhi via London i...    9.0   \n",
       "\n",
       "                     date         country  verified  \\\n",
       "0     24th September 2024   United States     False   \n",
       "1     22nd September 2024         Morocco      True   \n",
       "2     13th September 2024   United States     False   \n",
       "3     13th September 2024       Australia     False   \n",
       "4     13th September 2024  United Kingdom     False   \n",
       "...                   ...             ...       ...   \n",
       "3494    28th October 2014  United Kingdom     False   \n",
       "3495    28th October 2014       Australia     False   \n",
       "3496    28th October 2014         Denmark     False   \n",
       "3497    28th October 2014  United Kingdom     False   \n",
       "3498    28th October 2014          Canada     False   \n",
       "\n",
       "                                                 corpus  \n",
       "0     verify     happy flight crew   hadn t plane   ...  \n",
       "1     horrible service boarding landing   fly london...  \n",
       "2     verify     wife disappointed fly british airwa...  \n",
       "3     verify     fly ba heathrow berlin way connecti...  \n",
       "4     verify     absolutely disgusted ba   flight ca...  \n",
       "...                                                 ...  \n",
       "3494  travel palma new ba   sardine   business class...  \n",
       "3495  fly london heathrow singapore business class t...  \n",
       "3496  gig lhr b     newly refurbish cabin   seat goo...  \n",
       "3497  lhr del   rtn club world   excellent service s...  \n",
       "3498  fly ba toronto new delhi london premium econom...  \n",
       "\n",
       "[3499 rows x 6 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#resetting the index\n",
    "df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# export the cleaned data\n",
    "\n",
    "df.to_csv(cwd + \"/cleaned-BA-reviews.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}