notebooks/01 - Excel Files and CSV into Pandas.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Excel files and CSV into Python Pandas\n",
    "\n",
    "Loading data into Python from standard Excel files and comma-separated value (CSV) data is fundamental for many data scientists and analysts. \n",
    "\n",
    "Excel and CSV files are among the most common data storage formats. Python provides various tools to read, manipulate, and analyze this data. In this process, Excel files can be read using libraries like `pandas`, `xlrd`, and `openpyxl`. In contrast, CSV files can be imported using the built-in `csv` module or the `pandas` library. \n",
    "\n",
    "Understanding how to load data from these file formats is essential for data analysis and machine learning tasks in Python. This article will explore different methods to read data from Excel and CSV files and analyze some everyday use cases."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How To"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>housing_median_age</th>\n",
       "      <th>total_rooms</th>\n",
       "      <th>total_bedrooms</th>\n",
       "      <th>population</th>\n",
       "      <th>households</th>\n",
       "      <th>median_income</th>\n",
       "      <th>median_house_value</th>\n",
       "      <th>ocean_proximity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-122.23</td>\n",
       "      <td>37.88</td>\n",
       "      <td>41.0</td>\n",
       "      <td>880.0</td>\n",
       "      <td>129.0</td>\n",
       "      <td>322.0</td>\n",
       "      <td>126.0</td>\n",
       "      <td>3252-08-01 00:00:00</td>\n",
       "      <td>452600.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-122.22</td>\n",
       "      <td>37.86</td>\n",
       "      <td>21.0</td>\n",
       "      <td>7099.0</td>\n",
       "      <td>1106.0</td>\n",
       "      <td>2401.0</td>\n",
       "      <td>1138.0</td>\n",
       "      <td>3014-08-01 00:00:00</td>\n",
       "      <td>358500.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-122.24</td>\n",
       "      <td>37.85</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1467.0</td>\n",
       "      <td>190.0</td>\n",
       "      <td>496.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>2574-07-01 00:00:00</td>\n",
       "      <td>352100.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-122.25</td>\n",
       "      <td>37.85</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1274.0</td>\n",
       "      <td>235.0</td>\n",
       "      <td>558.0</td>\n",
       "      <td>219.0</td>\n",
       "      <td>6431-05-01 00:00:00</td>\n",
       "      <td>341300.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-122.25</td>\n",
       "      <td>37.85</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1627.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>565.0</td>\n",
       "      <td>259.0</td>\n",
       "      <td>8462-03-01 00:00:00</td>\n",
       "      <td>342200.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   longitude  latitude  housing_median_age  total_rooms  total_bedrooms  \\\n",
       "0    -122.23     37.88                41.0        880.0           129.0   \n",
       "1    -122.22     37.86                21.0       7099.0          1106.0   \n",
       "2    -122.24     37.85                52.0       1467.0           190.0   \n",
       "3    -122.25     37.85                52.0       1274.0           235.0   \n",
       "4    -122.25     37.85                52.0       1627.0           280.0   \n",
       "\n",
       "   population  households        median_income  median_house_value  \\\n",
       "0       322.0       126.0  3252-08-01 00:00:00            452600.0   \n",
       "1      2401.0      1138.0  3014-08-01 00:00:00            358500.0   \n",
       "2       496.0       177.0  2574-07-01 00:00:00            352100.0   \n",
       "3       558.0       219.0  6431-05-01 00:00:00            341300.0   \n",
       "4       565.0       259.0  8462-03-01 00:00:00            342200.0   \n",
       "\n",
       "  ocean_proximity  \n",
       "0        NEAR BAY  \n",
       "1        NEAR BAY  \n",
       "2        NEAR BAY  \n",
       "3        NEAR BAY  \n",
       "4        NEAR BAY  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel(\"data/housing.xlsx\", engine=\"openpyxl\")\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>housing_median_age</th>\n",
       "      <th>total_rooms</th>\n",
       "      <th>total_bedrooms</th>\n",
       "      <th>population</th>\n",
       "      <th>households</th>\n",
       "      <th>median_income</th>\n",
       "      <th>median_house_value</th>\n",
       "      <th>ocean_proximity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-122.23</td>\n",
       "      <td>37.88</td>\n",
       "      <td>41.0</td>\n",
       "      <td>880.0</td>\n",
       "      <td>129.0</td>\n",
       "      <td>322.0</td>\n",
       "      <td>126.0</td>\n",
       "      <td>8.3252</td>\n",
       "      <td>452600.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-122.22</td>\n",
       "      <td>37.86</td>\n",
       "      <td>21.0</td>\n",
       "      <td>7099.0</td>\n",
       "      <td>1106.0</td>\n",
       "      <td>2401.0</td>\n",
       "      <td>1138.0</td>\n",
       "      <td>8.3014</td>\n",
       "      <td>358500.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-122.24</td>\n",
       "      <td>37.85</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1467.0</td>\n",
       "      <td>190.0</td>\n",
       "      <td>496.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>7.2574</td>\n",
       "      <td>352100.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-122.25</td>\n",
       "      <td>37.85</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1274.0</td>\n",
       "      <td>235.0</td>\n",
       "      <td>558.0</td>\n",
       "      <td>219.0</td>\n",
       "      <td>5.6431</td>\n",
       "      <td>341300.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-122.25</td>\n",
       "      <td>37.85</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1627.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>565.0</td>\n",
       "      <td>259.0</td>\n",
       "      <td>3.8462</td>\n",
       "      <td>342200.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   longitude  latitude  housing_median_age  total_rooms  total_bedrooms  \\\n",
       "0    -122.23     37.88                41.0        880.0           129.0   \n",
       "1    -122.22     37.86                21.0       7099.0          1106.0   \n",
       "2    -122.24     37.85                52.0       1467.0           190.0   \n",
       "3    -122.25     37.85                52.0       1274.0           235.0   \n",
       "4    -122.25     37.85                52.0       1627.0           280.0   \n",
       "\n",
       "   population  households  median_income  median_house_value ocean_proximity  \n",
       "0       322.0       126.0         8.3252            452600.0        NEAR BAY  \n",
       "1      2401.0      1138.0         8.3014            358500.0        NEAR BAY  \n",
       "2       496.0       177.0         7.2574            352100.0        NEAR BAY  \n",
       "3       558.0       219.0         5.6431            341300.0        NEAR BAY  \n",
       "4       565.0       259.0         3.8462            342200.0        NEAR BAY  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv(\"data/housing.csv\").head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order</th>\n",
       "      <th>inv-ord</th>\n",
       "      <th>selection</th>\n",
       "      <th>CollTitle</th>\n",
       "      <th>CorrectedTitle</th>\n",
       "      <th>OrigRecommends</th>\n",
       "      <th>classfication</th>\n",
       "      <th>Recommends</th>\n",
       "      <th>class_WD</th>\n",
       "      <th>class_WD&amp;CS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>541</td>\n",
       "      <td>112</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Angular 2 versus React: There Will Be BloodIn ...</td>\n",
       "      <td>Angular 2 versus React: There Will Be Blood</td>\n",
       "      <td>2.5K</td>\n",
       "      <td>TECH</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18</td>\n",
       "      <td>635</td>\n",
       "      <td>1.0</td>\n",
       "      <td>The mind-blowing AI announcement from Google t...</td>\n",
       "      <td>The mind-blowing AI announcement from Google t...</td>\n",
       "      <td>2.4K</td>\n",
       "      <td>TECH</td>\n",
       "      <td>2400.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>650</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Things I Wish Someone Had Told Me When I Was L...</td>\n",
       "      <td>Things I Wish Someone Had Told Me When I Was L...</td>\n",
       "      <td>3.2K</td>\n",
       "      <td>MOT</td>\n",
       "      <td>3200.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>94</td>\n",
       "      <td>559</td>\n",
       "      <td>1.0</td>\n",
       "      <td>How to encrypt your entire life in less than a...</td>\n",
       "      <td>How to encrypt your entire life in less than a...</td>\n",
       "      <td>7.4K</td>\n",
       "      <td>SEC</td>\n",
       "      <td>7400.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>409</td>\n",
       "      <td>244</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Being A Developer After 40In freeCodeCampView ...</td>\n",
       "      <td>Being A Developer After 40</td>\n",
       "      <td>6K</td>\n",
       "      <td>MOT</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>647</th>\n",
       "      <td>636</td>\n",
       "      <td>17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>December 2014</td>\n",
       "      <td>December 2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>date</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>648</th>\n",
       "      <td>644</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>November 2014</td>\n",
       "      <td>November 2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>date</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>649</th>\n",
       "      <td>647</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>October 2014</td>\n",
       "      <td>October 2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>date</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650</th>\n",
       "      <td>649</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>November 2013</td>\n",
       "      <td>November 2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>date</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651</th>\n",
       "      <td>651</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>October 2013</td>\n",
       "      <td>October 2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>date</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>652 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     order  inv-ord  selection  \\\n",
       "0      541      112        0.0   \n",
       "1       18      635        1.0   \n",
       "2      650        3        0.0   \n",
       "3       94      559        1.0   \n",
       "4      409      244        0.0   \n",
       "..     ...      ...        ...   \n",
       "647    636       17        NaN   \n",
       "648    644        9        NaN   \n",
       "649    647        6        NaN   \n",
       "650    649        4        NaN   \n",
       "651    651        2        NaN   \n",
       "\n",
       "                                             CollTitle  \\\n",
       "0    Angular 2 versus React: There Will Be BloodIn ...   \n",
       "1    The mind-blowing AI announcement from Google t...   \n",
       "2    Things I Wish Someone Had Told Me When I Was L...   \n",
       "3    How to encrypt your entire life in less than a...   \n",
       "4    Being A Developer After 40In freeCodeCampView ...   \n",
       "..                                                 ...   \n",
       "647                                      December 2014   \n",
       "648                                      November 2014   \n",
       "649                                       October 2014   \n",
       "650                                      November 2013   \n",
       "651                                       October 2013   \n",
       "\n",
       "                                        CorrectedTitle OrigRecommends  \\\n",
       "0          Angular 2 versus React: There Will Be Blood           2.5K   \n",
       "1    The mind-blowing AI announcement from Google t...           2.4K   \n",
       "2    Things I Wish Someone Had Told Me When I Was L...           3.2K   \n",
       "3    How to encrypt your entire life in less than a...           7.4K   \n",
       "4                           Being A Developer After 40             6K   \n",
       "..                                                 ...            ...   \n",
       "647                                      December 2014            NaN   \n",
       "648                                      November 2014            NaN   \n",
       "649                                       October 2014            NaN   \n",
       "650                                      November 2013            NaN   \n",
       "651                                       October 2013            NaN   \n",
       "\n",
       "    classfication  Recommends  class_WD  class_WD&CS  \n",
       "0            TECH      2500.0       0.0          0.0  \n",
       "1            TECH      2400.0       0.0          0.0  \n",
       "2             MOT      3200.0       0.0          0.0  \n",
       "3             SEC      7400.0       0.0          0.0  \n",
       "4             MOT      6000.0       0.0          0.0  \n",
       "..            ...         ...       ...          ...  \n",
       "647          date         NaN       NaN          NaN  \n",
       "648          date         NaN       NaN          NaN  \n",
       "649          date         NaN       NaN          NaN  \n",
       "650          date         NaN       NaN          NaN  \n",
       "651          date         NaN       NaN          NaN  \n",
       "\n",
       "[652 rows x 10 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv(\"https://raw.githubusercontent.com/freeCodeCamp/open-data/master/medium-fCC-data/data/medium_titles%20-%20rawdata.tsv\", sep=\"\\t\", )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Change the data loading to contain the correct data types and explore the keywords for reading CSV files and Excel files using `Shift + Tab`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_csv(\"https://raw.githubusercontent.com/freeCodeCamp/open-data/master/medium-fCC-data/data/medium_titles%20-%20rawdata.tsv\", \n",
    "            sep=\"\\t\", )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Additional Resources"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- [Pandas Documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html)\n",
    "- [Chris Albon on Loading CSVs](https://chrisalbon.com/python/data_wrangling/pandas_dataframe_importing_csv/)\n",
    "- [Shane Lynn on `Read_CSV`](https://www.shanelynn.ie/python-pandas-read_csv-load-data-from-csv-files/)"
   ]
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}