2-extract-and-clean-data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3ece4021",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c99d733d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('fortune_2000_in_2021.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc9ebbc3",
   "metadata": {},
   "source": [
    "# Cleaning the data (existing columns) - Checking column info"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bf94c23",
   "metadata": {},
   "source": [
    "- As you can see below, we only have 1999 non null rows in the 'Assets' column while there are 2000 non null columns elsewhere."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e94076b6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2000 entries, 0 to 1999\n",
      "Data columns (total 7 columns):\n",
      " #   Column        Non-Null Count  Dtype \n",
      "---  ------        --------------  ----- \n",
      " 0   Rank          2000 non-null   int64 \n",
      " 1   Name          2000 non-null   object\n",
      " 2   Country       2000 non-null   object\n",
      " 3   Sales         2000 non-null   object\n",
      " 4   Profit        2000 non-null   object\n",
      " 5   Assets        1999 non-null   object\n",
      " 6   Market Value  2000 non-null   object\n",
      "dtypes: int64(1), object(6)\n",
      "memory usage: 109.5+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "323d72f8",
   "metadata": {},
   "source": [
    "# Find and replace null values\n",
    "- Here we can see that the Bank of Greece has NaN under its assets column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9801240e",
   "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>Rank</th>\n",
       "      <th>Name</th>\n",
       "      <th>Country</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Assets</th>\n",
       "      <th>Market Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1932</th>\n",
       "      <td>1933</td>\n",
       "      <td>Bank of Greece</td>\n",
       "      <td>Greece</td>\n",
       "      <td>$1.3 B</td>\n",
       "      <td>$942.8 M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$366 M</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Rank            Name Country   Sales    Profit Assets Market Value\n",
       "1932  1933  Bank of Greece  Greece  $1.3 B  $942.8 M    NaN       $366 M"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['Assets'].isnull()]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2df61abb",
   "metadata": {},
   "source": [
    "- For further filtering and sorting, we are going to replace NaN with 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "84cfe930",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Rank                      1933\n",
       "Name            Bank of Greece\n",
       "Country                 Greece\n",
       "Sales                   $1.3 B\n",
       "Profit                $942.8 M\n",
       "Assets                       0\n",
       "Market Value            $366 M\n",
       "Name: 1932, dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Assets'] = df['Assets'].fillna(0)\n",
    "# Check row\n",
    "df.loc[1932]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "886734b8",
   "metadata": {},
   "source": [
    "# Inspect 'Country' column - check for spelling errors and check value counts as expected"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "55285f08",
   "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>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Argentina</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Austria</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bahrain</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bangladesh</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Belgium</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bermuda</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brazil</th>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canada</th>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chile</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>China</th>\n",
       "      <td>291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colombia</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cyprus</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Czech Republic</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Denmark</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Egypt</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Finland</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>France</th>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Germany</th>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Greece</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hong Kong</th>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hungary</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>India</th>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Indonesia</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ireland</th>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Israel</th>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Italy</th>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Japan</th>\n",
       "      <td>215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kazakhstan</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kenya</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kuwait</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Luxembourg</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Malaysia</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mexico</th>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Monaco</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Morocco</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Netherlands</th>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nigeria</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Norway</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oman</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Peru</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Philippines</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Poland</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Portugal</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Qatar</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Russia</th>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Saudi Arabia</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Singapore</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Africa</th>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Korea</th>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Spain</th>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sweden</th>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Switzerland</th>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Taiwan</th>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thailand</th>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Turkey</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United Arab Emirates</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United Kingdom</th>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United States</th>\n",
       "      <td>590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Venezuela</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vietnam</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Country\n",
       "Argentina                   2\n",
       "Australia                  31\n",
       "Austria                     9\n",
       "Bahrain                     1\n",
       "Bangladesh                  1\n",
       "Belgium                     9\n",
       "Bermuda                     6\n",
       "Brazil                     21\n",
       "Canada                     56\n",
       "Chile                       5\n",
       "China                     291\n",
       "Colombia                    3\n",
       "Cyprus                      1\n",
       "Czech Republic              1\n",
       "Denmark                    12\n",
       "Egypt                       1\n",
       "Finland                     9\n",
       "France                     53\n",
       "Germany                    54\n",
       "Greece                      5\n",
       "Hong Kong                  59\n",
       "Hungary                     2\n",
       "India                      50\n",
       "Indonesia                   6\n",
       "Ireland                    18\n",
       "Israel                     10\n",
       "Italy                      23\n",
       "Japan                     215\n",
       "Kazakhstan                  2\n",
       "Kenya                       1\n",
       "Kuwait                      3\n",
       "Luxembourg                  6\n",
       "Malaysia                    8\n",
       "Mexico                     10\n",
       "Monaco                      1\n",
       "Morocco                     3\n",
       "Netherlands                20\n",
       "Nigeria                     2\n",
       "Norway                      8\n",
       "Oman                        1\n",
       "Peru                        1\n",
       "Philippines                 6\n",
       "Poland                      7\n",
       "Portugal                    4\n",
       "Qatar                       6\n",
       "Russia                     24\n",
       "Saudi Arabia               13\n",
       "Singapore                   9\n",
       "South Africa               15\n",
       "South Korea                62\n",
       "Spain                      21\n",
       "Sweden                     32\n",
       "Switzerland                42\n",
       "Taiwan                     45\n",
       "Thailand                   14\n",
       "Turkey                      9\n",
       "United Arab Emirates        9\n",
       "United Kingdom             66\n",
       "United States             590\n",
       "Venezuela                   1\n",
       "Vietnam                     5"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Change max option to view all countries\n",
    "pd.set_option ('display.max_rows', 61)\n",
    "\n",
    "# Get unique countries by performing value counts\n",
    "countries = pd.DataFrame(df['Country'].value_counts())\n",
    "countries.sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "e532b871",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set max rows back to previous\n",
    "pd.set_option ('display.max_rows', 10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a6731b0",
   "metadata": {},
   "source": [
    "# Check all numerical columns have the same format\n",
    "### Checking if Sales, Profits, Assets and Market Value all contain $ and M or B\n",
    "\n",
    "- Before we convert rows to numerical formats, we want to check the existing rows all have the same format.\n",
    "- We want to check they all contain $ and M or B"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0ad0ab68",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2000"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Expecting a length of 2000 \n",
    "len(df[df['Sales'].str.contains('$', na=False) & (df['Sales'].str.contains('B|M',na=False))])                             "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "fc87593d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2000"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Expecting a length of 2000 \n",
    "len(df[df['Profit'].str.contains('$',na=False) & (df['Profit'].str.contains('B|M',na=False))])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "da8f9044",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1999"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Expecting a length of 2000 \n",
    "len(df[df['Assets'].str.contains('$',na=False) & (df['Assets'].str.contains('B|M',na=False))])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "80838c4d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2000"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Expecting a length of 2000 \n",
    "len(df[df['Market Value'].str.contains('$',na=False) & (df['Market Value'].str.contains('B|M',na=False))])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38bf093f",
   "metadata": {},
   "source": [
    "# Find and fix 1 row with Assets in incorrect format"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43f1b13b",
   "metadata": {},
   "source": [
    "- From above we only have a length of 1999 for Asset column strings containing $ and M or B.\n",
    "- From earlier we set the Bank of Greece 'Assets' to 0 (an integer)\n",
    "- Here we use na=False to get any NaN or non string items."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "57e2a4cb",
   "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>Rank</th>\n",
       "      <th>Name</th>\n",
       "      <th>Country</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Assets</th>\n",
       "      <th>Market Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1932</th>\n",
       "      <td>1933</td>\n",
       "      <td>Bank of Greece</td>\n",
       "      <td>Greece</td>\n",
       "      <td>$1.3 B</td>\n",
       "      <td>$942.8 M</td>\n",
       "      <td>0</td>\n",
       "      <td>$366 M</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Rank            Name Country   Sales    Profit Assets Market Value\n",
       "1932  1933  Bank of Greece  Greece  $1.3 B  $942.8 M      0       $366 M"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#df.loc[(df['Assets'].str.contains(\"M\")) & (~df['Assets'].str.contains(\"B\"))]\n",
    "df[df['Assets'].str.contains('$', na=False) == False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "10cae6f5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Rank                      1933\n",
       "Name            Bank of Greece\n",
       "Country                 Greece\n",
       "Sales                   $1.3 B\n",
       "Profit                $942.8 M\n",
       "Assets                    $0 M\n",
       "Market Value            $366 M\n",
       "Name: 1932, dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Here we will set the 'Assets' column to have the same string format as other cells.\n",
    "df.at[1932,'Assets'] = '$0 M'\n",
    "# Check row\n",
    "df.loc[1932]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28ea5e79",
   "metadata": {},
   "source": [
    "# Check for possible anomalies \n",
    "### Checking if profit is in billions but sales are in millions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd07afe7",
   "metadata": {},
   "source": [
    "- Although the data is from a practice dataset, in this situation we might want to double check with team members that the data below is correct."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "09f19061",
   "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>Rank</th>\n",
       "      <th>Name</th>\n",
       "      <th>Country</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Profit</th>\n",
       "      <th>Assets</th>\n",
       "      <th>Market Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>605</th>\n",
       "      <td>606</td>\n",
       "      <td>Porsche Automobil Holding</td>\n",
       "      <td>Germany</td>\n",
       "      <td>$122 M</td>\n",
       "      <td>$3 B</td>\n",
       "      <td>$44.5 B</td>\n",
       "      <td>$36.2 B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1037</th>\n",
       "      <td>1036</td>\n",
       "      <td>Segro</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>$554 M</td>\n",
       "      <td>$1.8 B</td>\n",
       "      <td>$17.3 B</td>\n",
       "      <td>$16.2 B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1074</th>\n",
       "      <td>1075</td>\n",
       "      <td>Kinnevik</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>$575 M</td>\n",
       "      <td>$4.4 B</td>\n",
       "      <td>$14.1 B</td>\n",
       "      <td>$15.5 B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1113</th>\n",
       "      <td>1114</td>\n",
       "      <td>Carnival</td>\n",
       "      <td>United States</td>\n",
       "      <td>$831 M</td>\n",
       "      <td>$-11.4 B</td>\n",
       "      <td>$57.2 B</td>\n",
       "      <td>$30.6 B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1384</th>\n",
       "      <td>1384</td>\n",
       "      <td>Sofina</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>$97 M</td>\n",
       "      <td>$1.6 B</td>\n",
       "      <td>$11.1 B</td>\n",
       "      <td>$12.4 B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1584</th>\n",
       "      <td>1585</td>\n",
       "      <td>Sparebanken Nord-Norge</td>\n",
       "      <td>Norway</td>\n",
       "      <td>$481 M</td>\n",
       "      <td>$2 B</td>\n",
       "      <td>$13.7 B</td>\n",
       "      <td>$1 B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1639</th>\n",
       "      <td>1640</td>\n",
       "      <td>RMB Holdings</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>$2 M</td>\n",
       "      <td>$2 B</td>\n",
       "      <td>$330 M</td>\n",
       "      <td>$191 M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1677</th>\n",
       "      <td>1678</td>\n",
       "      <td>Cannae Holdings</td>\n",
       "      <td>United States</td>\n",
       "      <td>$586 M</td>\n",
       "      <td>$1.8 B</td>\n",
       "      <td>$4.6 B</td>\n",
       "      <td>$3.8 B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1708</th>\n",
       "      <td>1708</td>\n",
       "      <td>Iluka Resources</td>\n",
       "      <td>Australia</td>\n",
       "      <td>$715 M</td>\n",
       "      <td>$1.7 B</td>\n",
       "      <td>$2 B</td>\n",
       "      <td>$2.4 B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1803</th>\n",
       "      <td>1804</td>\n",
       "      <td>China Conch Venture Holdings</td>\n",
       "      <td>China</td>\n",
       "      <td>$960 M</td>\n",
       "      <td>$1.1 B</td>\n",
       "      <td>$8.3 B</td>\n",
       "      <td>$8.7 B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1881</th>\n",
       "      <td>1882</td>\n",
       "      <td>Brilliance China Automotive Holdings</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>$485 M</td>\n",
       "      <td>$1.1 B</td>\n",
       "      <td>$7.6 B</td>\n",
       "      <td>$4.7 B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1885</th>\n",
       "      <td>1886</td>\n",
       "      <td>First Heartland Jusan Bank</td>\n",
       "      <td>Kazakhstan</td>\n",
       "      <td>$533 M</td>\n",
       "      <td>$1.1 B</td>\n",
       "      <td>$3.8 B</td>\n",
       "      <td>$323 M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1914</th>\n",
       "      <td>1915</td>\n",
       "      <td>BeiGene</td>\n",
       "      <td>China</td>\n",
       "      <td>$309 M</td>\n",
       "      <td>$-1.6 B</td>\n",
       "      <td>$5.6 B</td>\n",
       "      <td>$28.4 B</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Rank                                  Name         Country   Sales  \\\n",
       "605    606             Porsche Automobil Holding         Germany  $122 M   \n",
       "1037  1036                                 Segro  United Kingdom  $554 M   \n",
       "1074  1075                              Kinnevik          Sweden  $575 M   \n",
       "1113  1114                              Carnival   United States  $831 M   \n",
       "1384  1384                                Sofina         Belgium   $97 M   \n",
       "1584  1585                Sparebanken Nord-Norge          Norway  $481 M   \n",
       "1639  1640                          RMB Holdings    South Africa    $2 M   \n",
       "1677  1678                       Cannae Holdings   United States  $586 M   \n",
       "1708  1708                       Iluka Resources       Australia  $715 M   \n",
       "1803  1804          China Conch Venture Holdings           China  $960 M   \n",
       "1881  1882  Brilliance China Automotive Holdings       Hong Kong  $485 M   \n",
       "1885  1886            First Heartland Jusan Bank      Kazakhstan  $533 M   \n",
       "1914  1915                               BeiGene           China  $309 M   \n",
       "\n",
       "        Profit   Assets Market Value  \n",
       "605       $3 B  $44.5 B      $36.2 B  \n",
       "1037    $1.8 B  $17.3 B      $16.2 B  \n",
       "1074    $4.4 B  $14.1 B      $15.5 B  \n",
       "1113  $-11.4 B  $57.2 B      $30.6 B  \n",
       "1384    $1.6 B  $11.1 B      $12.4 B  \n",
       "1584      $2 B  $13.7 B         $1 B  \n",
       "1639      $2 B   $330 M       $191 M  \n",
       "1677    $1.8 B   $4.6 B       $3.8 B  \n",
       "1708    $1.7 B     $2 B       $2.4 B  \n",
       "1803    $1.1 B   $8.3 B       $8.7 B  \n",
       "1881    $1.1 B   $7.6 B       $4.7 B  \n",
       "1885    $1.1 B   $3.8 B       $323 M  \n",
       "1914   $-1.6 B   $5.6 B      $28.4 B  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Change option for displaying max rows\n",
    "pd.set_option ('display.max_rows', 100)\n",
    "# Find all rows where profits are in billions but sales are in millions\n",
    "df[df['Sales'].str.contains('M') & df['Profit'].str.contains('B')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2f931387",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Set the max rows option back again\n",
    "pd.set_option ('display.max_rows', 10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b223ee6",
   "metadata": {},
   "source": [
    "# Save the cleaned dataframe to a file\n",
    "- Here, we save dataframe to csv without the index \n",
    "- An index will be created when we re-open file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "8ca78d2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('fortune_2000_in_2021_cleaned.csv', index=False)"
   ]
  }
 ],
 "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}