data-import.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "95f0a171",
   "metadata": {},
   "source": [
    "(data-import)=\n",
    "# Data Import\n",
    "\n",
    "## Introduction\n",
    "\n",
    "In this chapter, we're going to introduce working with your own data. Here, you'll learn how to read plain-text rectangular files into Python. We'll only scratch the surface of data import, but many of the principles will translate to other forms of data. We'll finish with a few pointers to opening other types of data.\n",
    "\n",
    "### Prerequisites\n",
    "\n",
    "You will need to have the **pandas** package installed. You'll need to ensure you have **pandas** installed. To do this, and to import **pandas** into your session, run"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1cf01bda",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e29b7103",
   "metadata": {},
   "source": [
    "If this command fails, you don't have **pandas** installed. Open up the terminal in Visual Studio Code (Terminal -> New Terminal) and type in `conda install pandas`. Note that once **pandas** is installed, the convention is to import it into your Python session under the name `pd` by putting `import pandas as pd` at the top of your script."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7505f1aa",
   "metadata": {},
   "source": [
    "## Getting Started\n",
    "\n",
    "There are a huge range of input and output formats available in **pandas**: Stata (.dta), Excel (.xls, .xlsx), csv, tsv, big data formats (HDF5, parquet), JSON, SAS, SPSS, SQL, and more; there's a [full list](https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html) of formats available in the documentation.\n",
    "\n",
    "![From the pandas documentation](https://pandas.pydata.org/pandas-docs/stable/_images/02_io_readwrite.svg)\n",
    "\n",
    "While **pandas** has a huge number of ways to read data in and load it into your Python session, here we'll focus on the humble plain-text table file; for example csv (comma separated values) and tsv (tab separated values).\n",
    "\n",
    "### Reading data from a file\n",
    "\n",
    "All of the power needed to open plain-text table files is contained in a single function, `pd.read_csv()`. It takes numerous arguments but the two most important are the (unnamed) first one, which gives the path to the data, and `sep=` (a keyword argument) that tells **pandas** whether to expect values to be separated by commas or tabs or another character; however, if you leave this field blank, **pandas** will guess for you. To see the full set of arguments, run `help(pd.read_csv)`.\n",
    "\n",
    "Here is what a simple CSV file with a row for column names (also commonly referred to as the header row) and six rows of data looks like (using the terminal):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eca85c47",
   "metadata": {},
   "outputs": [],
   "source": [
    "! cat data/students.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7352c95",
   "metadata": {},
   "source": [
    "Note that this is a CSV file, so the values are separated by commas. Now let's load this into a **pandas** dataframe in Python:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "232fdfef",
   "metadata": {},
   "outputs": [],
   "source": [
    "students = pd.read_csv(\"data/students.csv\")\n",
    "students"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "870cf0b5",
   "metadata": {},
   "source": [
    "If you want to download this data to try it for yourself, head to [this link](https://github.com/aeturrell/python4DS/blob/main/data/students.csv) and then right-click on 'Raw' and select \"Save Link As...\". Save the data in a directory called 'data' with the name 'students.csv'. This directory should sit within your active Visual Studio Code folder. You can check which folder you're currently in by running the following code:\n",
    "\n",
    "```python\n",
    "import os\n",
    "\n",
    "os.getcwd()  # get current working directory (cwd)\n",
    "```\n",
    "\n",
    "Say this comes back with 'python4DS', then your downloaded data should be in 'python4DS/data/students.csv'."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f3979fc",
   "metadata": {},
   "source": [
    "The first argument to `read_csv()` was the path to the data, and **pandas** guessed that this file uses commas as the separator.\n",
    "\n",
    "The read CSV function automatically creates a new *index* (which is just the position of each row) and takes the top line of data as the *header* or *column names*. But you may wish to tweak this behaviour, in several ways.\n",
    "\n",
    "1.  Sometimes there are a few lines of metadata at the top of the file. You can use `skiprows=n` to skip the first `n` lines, eg `pd.read_csv(\"data/students.csv\", skiprows=2)`.\n",
    "\n",
    "2.  The data might not have column names. You can use `names = ` a list to tell `read_csv()` to use a different option for the column names. For example, `pd.read_csv(\"data/students.csv\", names=range(5))` would put the numbers 0 to 4 in as the column names.\n",
    "\n",
    "3. You may wish to change which column is used as the index. The default behaviour is to create an index, but for this data we see that there already is an ID column we could use. To do this, use the `index_col=` argument, for example `pd.read_csv(\"data/students.csv\", index_col=0)`.\n",
    "\n",
    "This is all you need to know to read \\~75% of CSV files that you'll encounter in practice. Reading tab separated files and fixed width files is done with the same function."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7994a793",
   "metadata": {},
   "source": [
    "### First Steps\n",
    "\n",
    "Let's take another look at the `students` data.\n",
    "\n",
    "Once you read data in, the first step usually involves transforming it in some way to make it easier to work with in the rest of your analysis. For example, the column names in the `students` file we read in are formatted in non-standard ways.\n",
    "\n",
    "You might consider renaming them one by one with `.rename()` or you might use a convenience function from another package to clean them and turn them all into snake case at once. We will make use of the **skimpy** package to do this. **skimpy** is a smaller package so isn't available to install via conda; instead, install it by running `pip install skimpy` in the terminal.\n",
    "\n",
    "From **skimpy**, we will use the `clean_columns()` function; this takes in a data frame and returns a data frame with variable names converted to snake case."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51969364",
   "metadata": {},
   "outputs": [],
   "source": [
    "from skimpy import clean_columns\n",
    "\n",
    "students = clean_columns(students)\n",
    "students"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69c8a260",
   "metadata": {},
   "source": [
    "Another common task after reading in data is to consider variable types. In the `favourite_food` column, there are a bunch of food items and then the value `NaN`, which has been read in as a floating point number rather than a missing string. We can solve this by casting that column to explicitly be composed of strings:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4dad370",
   "metadata": {},
   "outputs": [],
   "source": [
    "students[\"favourite_food\"] = students[\"favourite_food\"].astype(\"string\")\n",
    "students"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e85148b3",
   "metadata": {},
   "source": [
    "Similarly, `\"age\"` has mixed data types: string and integer! Let's map the 'five' into the number five."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3c31e4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "students[\"age\"] = students[\"age\"].replace(\"five\", 5)\n",
    "students[\"age\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "719f90cc",
   "metadata": {},
   "source": [
    "In a moment, we will turn this into a column of integers too."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5c2b01e",
   "metadata": {},
   "source": [
    "Another example where the data type is wrong is `meal_type`. This is a categorical variable with a known set of possible values. **pandas** has a special data type for these:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "678fdd2d",
   "metadata": {},
   "outputs": [],
   "source": [
    "students[\"meal_plan\"] = students[\"meal_plan\"].astype(\"category\")\n",
    "students[\"meal_plan\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e2a32a8",
   "metadata": {},
   "source": [
    "Note that the values in the `meal_type` variable has stayed exactly the same, but the type of variable has changed from the object to category.\n",
    "\n",
    "It is a bit tedious to have to go through columns one-by-one as single line assignments to apply type. An alternative is to pass a dictionary that maps column names into types, like follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f54108d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "students = students.astype({\"student_id\": \"int\", \"full_name\": \"string\", \"age\": \"int\"})\n",
    "students.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "388ad316",
   "metadata": {},
   "source": [
    "### Exercises\n",
    "\n",
    "1.  What function would you use to read a file where fields were separated with \"\\|\"?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fc7dbd6",
   "metadata": {},
   "source": [
    "## Reading data from multiple files"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37415655",
   "metadata": {},
   "source": [
    "Sometimes your data is split across multiple files instead of being contained in a single file. For example, you might have sales data for multiple months, with each month's data in a separate file: `01-sales.csv` for January, `02-sales.csv` for February, and `03-sales.csv` for March.\n",
    "\n",
    "With `pd.read_csv()` you can read these data in one-by-one and then stack them on top of each other in a single data frame using the `pd.concat()` function. This looks like:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b80b958b",
   "metadata": {},
   "outputs": [],
   "source": [
    "list_of_dataframes = [\n",
    "    pd.read_csv(x)\n",
    "    for x in [\"data/01-sales.csv\", \"data/02-sales.csv\", \"data/03-sales.csv\"]\n",
    "]\n",
    "sales_files = pd.concat(list_of_dataframes)\n",
    "sales_files"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea3904ca",
   "metadata": {},
   "source": [
    "If you have many files you want to read in, it can get cumbersome to write out their names as a list. Instead, you can use the **glob** package (which is built in to Python) to find the files for you by matching a pattern in the file names. Note that there may be other CSV files in the directory data/, so here we specified `\"*-sales.csv\"` to ensure we only get those files that include the word sales. Here, `\"*\"` plays the role of a wildcard: it represents any series of characters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a92056c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import glob\n",
    "\n",
    "list_of_csvs = glob.glob(\"data/*-sales.csv\")\n",
    "print(\"List of csvs is:\")\n",
    "print(list_of_csvs, \"\\n\")\n",
    "sales_files = pd.concat([pd.read_csv(x) for x in list_of_csvs])\n",
    "sales_files"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6f9e77d",
   "metadata": {},
   "source": [
    "## Writing to a file\n",
    "\n",
    "Just as the typical pattern for reading files is `pd.read_FILETYPE()`, where filetype can be, for example, CSV, all of the ways of writing **pandas** data frames to disk have the pattern `DATAFRAME.to_FILETYPE()`. So to write our sales data to a CSV file, the code will be `sales_files.to_csv(FILEPATH)`, where filepath is the location plus name of the file you want to write to.\n",
    "\n",
    "Let's see an example of writing data to file using our students data that we already did such good work on setting the data types for:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0bc97749",
   "metadata": {},
   "outputs": [],
   "source": [
    "students.to_csv(\"data/students-clean.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c92b016",
   "metadata": {},
   "source": [
    "Now let's read it back in and check the info on data types:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "542c5223",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_csv(\"data/students-clean.csv\").info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "164cd1c0",
   "metadata": {},
   "source": [
    "Notice anything? We lost a lot of the nice data type work we did! While **pandas** guessed that some columns are integers, we lost the string and categorical variables. The reason for this is that plain text files are not able to carry any contextual information (though **pandas** will guess some column data types).\n",
    "\n",
    "If you want to save data in a file and have it remember the data types, you'll need to use a different data format. For temporary storage, we recommend using the *feather* format as it is very fast and interoperable with other programming languages. Interoperability is a good reason to avoid language-specific file formats such as Stata's .dta, R's .rds, and Python's .pickle.\n",
    "\n",
    "Note that the feather format has an additional dependency in the form of a package called **pyarrow**. To install it, run `pip install pyarrow` in a terminal window.\n",
    "\n",
    "Here's an example of writing to a feather file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16c6ca1b",
   "metadata": {},
   "outputs": [],
   "source": [
    "students.to_feather(\"data/students-clean.feather\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2119dd53",
   "metadata": {},
   "source": [
    "Now let's re-open that feather file and take a look at the info attached to it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bfd5104f",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_feather(\"data/students-clean.feather\").info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f89f342f",
   "metadata": {},
   "source": [
    "Saving to this format preserved our data type information.\n",
    "\n",
    "### Reading and Writing Other Data Formats\n",
    "\n",
    "The image at the start of this chapter gives you a sense of what other formats are available but you can find a comprehensive list over at the [official **pandas** documentation on input and output](https://pandas.pydata.org/docs/user_guide/io.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "227f7c50",
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "os.remove(\"data/students-clean.csv\")\n",
    "os.remove(\"data/students-clean.feather\")"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "9d7534ecd9fbc7d385378f8400cf4d6cb9c6175408a574f1c99c5269f08771cc"
  },
  "jupytext": {
   "cell_metadata_filter": "-all",
   "encoding": "# -*- coding: utf-8 -*-",
   "formats": "md:myst",
   "main_language": "python"
  },
  "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.10.12"
  },
  "toc-showtags": true
 },
 "nbformat": 4,
 "nbformat_minor": 5
}