{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Tutorial 3: Introduction to Data Analysis\n", "\n", "In this tutorial you will further learn on how to use Pandas, one of the most important python packages to be used within data sciences." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "
\n", "Note: This tutorial is heavily based upon the work of others\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "
\n", "Important: This tutorial is not part of your final grade. You simply have to pass it by answering the questions.\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Important before we start\n", "
\n", "Make sure that you save this file before you continue, else you will lose everything. To do so, go to Bestand/File and click on Een kopie opslaan in Drive/Save a Copy on Drive!\n", "\n", "Now, rename the file into Week1_Tutorial3.ipynb. You can do so by clicking on the name in the top of this screen." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Learning Objectives\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "- Inspect a dataframe with `df.head()`, `df.tail()`, `df.info()`, `df.describe()`.\n", "- Obtain dataframe summaries with `df.info()` and `df.describe()`.\n", "- Manipulate how a dataframe displays in Jupyter by modifying Pandas configuration options such as `pd.set_option(\"display.max_rows\", n)`.\n", "- Rename columns of a dataframe using the `df.rename()` function or by accessing the `df.columns` attribute.\n", "- Modify the index name and index values of a dataframe using `.set_index()`, `.reset_index()` , `df.index.name`, `.index`.\n", "- Use `df.melt()` and `df.pivot()` to reshape dataframes, specifically to make tidy dataframes.\n", "- Combine dataframes using `df.merge()` and `pd.concat()` and know when to use these different methods.\n", "- Apply functions to a dataframe `df.apply()` and `df.applymap()`\n", "- Perform grouping and aggregating operations using `df.groupby()` and `df.agg()`.\n", "- Perform aggregating methods on grouped or ungrouped objects such as finding the minimum, maximum and sum of values in a dataframe using `df.agg()`.\n", "- Remove or fill missing values in a dataframe with `df.dropna()` and `df.fillna()`." ] }, { "cell_type": "markdown", "metadata": { "toc": true }, "source": [ "

Tutorial Outline

\n", "
\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Importing packages\n", "Let's start with importing the two packages that we will use in this Tutorial.\n", "\n", "[**NumPy**](https://www.labri.fr/perso/nrougier/from-python-to-numpy/) stands for \"Numerical Python\" and it is the standard Python library used for working with arrays (i.e., vectors & matrices), linear algerba, and other numerical computations. NumPy is written in C, making NumPy arrays faster and more memory efficient than Python lists or arrays\n", "\n", "[**Pandas**](https://pypi.org/project/pandas/) is most popular Python library for tabular data structures. You can think of Pandas as an extremely powerful version of Excel (but free and with a lot more features!) " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import numpy as np\n", "import pandas as pd" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 1. DataFrame Characteristics\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Last chapter we looked at how we can create dataframes. Let's now look at some helpful ways we can view our dataframe." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Head/Tail" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The `.head()` and `.tail()` methods allow you to view the top/bottom *n* (default 5) rows of a dataframe. Let's load in the cycling data set from last chapter and try them out:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df = pd.read_csv('https://github.com/ElcoK/BigData_AED/raw/main/week1/cycling_data.csv')\n", "df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The default return value is 5 rows, but we can pass in any number we like. For example, let's take a look at the top 10 rows:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.head(10)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Or the bottom 5 rows:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.tail()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### DataFrame Summaries" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Three very helpful attributes/functions for getting high-level summaries of your dataframe are:\n", "- `.shape`\n", "- `.info()`\n", "- `.describe()`" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`.shape` is just like the ndarray attribute we've seen previously. It gives the shape (rows, cols) of your dataframe:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.shape" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`.info()` prints information about the dataframe itself, such as dtypes, memory usages, non-null values, etc:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.info()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`.describe()` provides summary statistics of the values within a dataframe:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.describe()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "By default, `.describe()` only print summaries of numeric features. We can force it to give summaries on all features using the argument `include='all'` (although they may not make sense!):" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.describe(include='all')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Displaying DataFrames" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Displaying your dataframes effectively can be an important part of your workflow. If a dataframe has more than 60 rows, Pandas will only display the first 5 and last 5 rows:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "pd.DataFrame(np.random.rand(100))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "For dataframes of less than 60 rows, Pandas will print the whole dataframe:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "I find the 60 row threshold to be a little too much, I prefer something more like 20. You can change the setting using `pd.set_option(\"display.max_rows\", 20)` so that anything with more than 20 rows will be summarised by the first and last 5 rows as before:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "pd.set_option(\"display.max_rows\", 20)\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "There are also other display options you can change, such as how many columns are shown, how numbers are formatted, etc. See the [official documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/options.html#options-and-settings) for more.\n", "\n", "One display option I will point out is that Pandas allows you to style your tables, for example by highlighting negative values, or adding conditional colour maps to your dataframe. Below I'll style values based on their value ranging from negative (purple) to postive (yellow) but you can see the [styling documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/style.html#Styling) for more examples." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "test = pd.DataFrame(np.random.randn(5, 5),\n", " index = [f\"row_{_}\" for _ in range(5)],\n", " columns = [f\"feature_{_}\" for _ in range(5)])\n", "test.style.background_gradient(cmap='plasma')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Views vs Copies" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "In previous chapters we've discussed views (\"looking\" at a part of an existing object) and copies (making a new copy of the object in memory). These things get a little abstract with Pandas and \"...it’s very hard to predict whether it will return a view or a copy\" (that's a quote straight [from a dedicated section in the Pandas documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy)).\n", "\n", "Basically, it depends on the operation you are trying to perform, your dataframe's structure and the memory layout of the underlying array. But don't worry, let me tell you all you need to know. Firstly, the most common warning you'll encounter in Pandas is the `SettingWithCopy`, Pandas raises it as a warning that you might not be doing what you think you're doing. Let's see an example. You may recall there is one outlier `Time` in our dataframe:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df[df['Time'] > 4000]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Imagine we wanted to change this to `2000`. You'd probably do the following:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df[df['Time'] > 4000]['Time'] = 2000" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ah, there's that warning. Did our dataframe get changed?" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df[df['Time'] > 4000]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "No it didn't, even though you probably thought it did. What happened above is that `df[df['Time'] > 4000]` was executed first and returned a copy of the dataframe, we can confirm by using `id()`:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "print(f\"The id of the original dataframe is: {id(df)}\")\n", "print(f\" The id of the indexed dataframe is: {id(df[df['Time'] > 4000])}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We then tried to set a value on this new object by appending `['Time'] = 2000`. Pandas is warning us that we are doing that operation on a copy of the original dataframe, which is probably not what we want. To fix this, you need to index in a single go, using `.loc[]` for example:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.loc[df['Time'] > 4000, 'Time'] = 2000" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "No error this time! And let's confirm the change:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df[df['Time'] > 4000]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The second thing you need to know is that if you're ever in doubt about whether something is a view or a copy, you can just use the `.copy()` method to force a copy of a dataframe. Just like this:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df2 = df[df['Time'] > 4000].copy()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "That way, your guaranteed a copy that you can modify as you wish." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 2. Basic DataFrame Manipulations\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Renaming Columns" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can rename columns two ways:\n", "1. Using `.rename()` (to selectively change column names)\n", "2. By setting the `.columns` attribute (to change all column names at once)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let's give it a go:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.rename(columns={\"Date\": \"Datetime\",\n", " \"Comments\": \"Notes\"})\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Wait? What happened? Nothing changed? In the code above we did actually rename columns of our dataframe but we didn't modify the dataframe inplace, we made a copy of it. There are generally two options for making permanent dataframe changes:\n", "- 1. Use the argument `inplace=True`, e.g., `df.rename(..., inplace=True)`, available in most functions/methods\n", "- 2. Re-assign, e.g., `df = df.rename(...)`\n", "The Pandas team recommends **Method 2 (re-assign)**, for a [few reasons](https://www.youtube.com/watch?v=hK6o_TDXXN8&t=700) (mostly to do with how memory is allocated under the hood)." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df = df.rename(columns={\"Date\": \"Datetime\",\n", " \"Comments\": \"Notes\"})\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "If you wish to change all of the columns of a dataframe, you can do so by setting the `.columns` attribute:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.columns = [f\"Column {_}\" for _ in range(1, 7)]\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Changing the Index" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can change the index labels of a dataframe in 3 main ways:\n", "1. `.set_index()` to make one of the columns of the dataframe the index\n", "2. Directly modify `df.index.name` to change the index name\n", "3. `.reset_index()` to move the current index as a column and to reset the index with integer labels starting from 0\n", "4. Directly modify the `.index()` attribute" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Below I will set the index as `Column 1` and rename the index to \"New Index\":" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df = df.set_index(\"Column 1\")\n", "df.index.name = \"New Index\"\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "I can send the index back to a column and have a default integer index using `.reset_index()`:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df = df.reset_index()\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Like with column names, we can also modify the index directly, but I can't remember ever doing this, usually I'll use `.set_index()`:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.index" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.index = range(100, 133, 1)\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Adding/Removing Columns" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "There are two main ways to add/remove columns of a dataframe:\n", "1. Use `[]` to add columns\n", "2. Use `.drop()` to drop columns\n", "\n", "Let's re-read in a fresh copy of the cycling dataset." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df = pd.read_csv('https://raw.githubusercontent.com/ElcoK/BigData_AED/main/week1/cycling_data.csv')\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can add a new column to a dataframe by simply using `[]` with a new column name and value(s):" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df['Rider'] = 'Tom Beuzen'\n", "df['Avg Speed'] = df['Distance'] * 1000 / df['Time'] # avg. speed in m/s\n", "df" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df = df.drop(columns=['Rider', 'Avg Speed'])\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Adding/Removing Rows" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You won't often be adding rows to a dataframe manually (you'll usually add rows through concatenating/joining - that's coming up next). You can add/remove rows of a dataframe in two ways:\n", "1. Use `.append()` to add rows\n", "2. Use `.drop()` to drop rows" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let's add a new row to the bottom of this dataframe:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "another_row = pd.DataFrame([[\"12 Oct 2019, 00:10:57\", \"Morning Ride\", \"Ride\",\n", " 2331, 12.67, \"Washed and oiled bike last night\"]],\n", " columns = df.columns,\n", " index = [33])\n", "df = df.append(another_row)\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can drop all rows above index 30 using `.drop()`:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.drop(index=range(30, 34))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 3. DataFrame Reshaping\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[Tidy data](https://vita.had.co.nz/papers/tidy-data.pdf) is about \"linking the structure of a dataset with its semantics (its meaning)\". It is defined by:\n", "1. Each variable forms a column\n", "2. Each observation forms a row\n", "3. Each type of observational unit forms a table\n", "\n", "Often you'll need to reshape a dataframe to make it tidy (or for some other purpose).\n", " \n", "\n", "\n", "Source: [r4ds](https://r4ds.had.co.nz/tidy-data.html#fig:tidy-structure)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Melt and Pivot" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Pandas `.melt()`, `.pivot()` and `.pivot_table()` can help reshape dataframes\n", "- `.melt()`: make wide data long.\n", "- `.pivot()`: make long data width.\n", "- `.pivot_table()`: same as `.pivot()` but can handle multiple indexes.\n", " \n", "\n", "\n", "\n", "Source: [Garrick Aden-Buie's GitHub](https://github.com/gadenbuie/tidyexplain#spread-and-gather)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The below data shows how many courses different instructors taught across different years. If the question you want to answer is something like: \"Does the number of courses taught vary depending on year?\" then the below would probably not be considered tidy because there are multiple observations of courses taught in a year per row (i.e., there is data for 2018, 2019 and 2020 in a single row):" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame({\"Name\": [\"Tom\", \"Mike\", \"Tiffany\", \"Varada\", \"Joel\"],\n", " \"2018\": [1, 3, 4, 5, 3],\n", " \"2019\": [2, 4, 3, 2, 1],\n", " \"2020\": [5, 2, 4, 4, 3]})\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let's make it tidy with `.melt()`. `.melt()` takes a few arguments, most important is the `id_vars` which indicated which column should be the \"identifier\"." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df_melt = df.melt(id_vars=\"Name\",\n", " var_name=\"Year\",\n", " value_name=\"Courses\")\n", "df_melt" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The `value_vars` argument allows us to select which specific variables we want to \"melt\" (if you don't specify `value_vars`, all non-identifier columns will be used). For example, below I'm omitting the `2018` column:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.melt(id_vars=\"Name\",\n", " value_vars=[\"2019\", \"2020\"],\n", " var_name=\"Year\",\n", " value_name=\"Courses\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Sometimes, you want to make long data wide, which we can do with `.pivot()`. When using `.pivot()` we need to specify the `index` to pivot on, and the `columns` that will be used to make the new columns of the wider dataframe:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df_pivot = df_melt.pivot(index=\"Name\",\n", " columns=\"Year\",\n", " values=\"Courses\")\n", "df_pivot" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You'll notice that Pandas set our specified `index` as the index of the new dataframe and preserved the label of the columns. We can easily remove these names and reset the index to make our dataframe look like it originally did:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df_pivot = df_pivot.reset_index()\n", "df_pivot.columns.name = None\n", "df_pivot" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`.pivot()` will often get you what you want, but it won't work if you want to:\n", "- Use multiple indexes (next chapter), or\n", "- Have duplicate index/column labels\n", "\n", "In these cases you'll have to use `.pivot_table()`. I won't focus on it too much here because I'd rather you learn about `pivot()` first." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame({\"Name\": [\"Tom\", \"Tom\", \"Mike\", \"Mike\"],\n", " \"Department\": [\"CS\", \"STATS\", \"CS\", \"STATS\"],\n", " \"2018\": [1, 2, 3, 1],\n", " \"2019\": [2, 3, 4, 2],\n", " \"2020\": [5, 1, 2, 2]}).melt(id_vars=[\"Name\", \"Department\"], var_name=\"Year\", value_name=\"Courses\")\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "In the above case, we have duplicates in `Name`, so `pivot()` won't work. It will throw us a `ValueError: Index contains duplicate entries, cannot reshape`:" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [ "raises-exception" ] }, "outputs": [], "source": [ "df.pivot(index=\"Name\",\n", " columns=\"Year\",\n", " values=\"Courses\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "In such a case, we'd use `.pivot_table()`. It will apply an aggregation function to our duplicates, in this case, we'll `sum()` them up:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.pivot_table(index=\"Name\", columns='Year', values='Courses', aggfunc='sum')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "If we wanted to keep the numbers per department, we could specify both `Name` and `Department` as multiple indexes:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.pivot_table(index=[\"Name\", \"Department\"], columns='Year', values='Courses')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The result above is a mutlti-index or \"hierarchically indexed\" dataframe (more on those next chapter). If you ever have a need to use it, you can read more about `pivot_table()` in the [documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/reshaping.html#pivot-tables)." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 4. Working with Multiple DataFrames\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Often you'll work with multiple dataframes that you want to stick together or merge. `df.merge()` and `df.concat()` are all you need to know for combining dataframes. The Pandas [documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html) is very helpful for these functions, but they are pretty easy to grasp.\n", "\n", "
\n", "Note: The example joins shown in this section are inspired by Chapter 15 of Jenny Bryan's STAT 545 materials.\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Sticking DataFrames Together with `pd.concat()`" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can use `pd.concat()` to stick dataframes together:\n", "- Vertically: if they have the same **columns**, OR\n", "- Horizontally: if they have the same **rows**" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df1 = pd.DataFrame({'A': [1, 3, 5],\n", " 'B': [2, 4, 6]})\n", "df2 = pd.DataFrame({'A': [7, 9, 11],\n", " 'B': [8, 10, 12]})" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df1" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df2" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "pd.concat((df1, df2), axis=0) # axis=0 specifies a vertical stick, i.e., on the columns" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Notice that the indexes were simply joined together? This may or may not be what you want. To reset the index, you can specify the argument `ignore_index=True`:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "pd.concat((df1, df2), axis=0, ignore_index=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Use `axis=1` to stick together horizontally:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "pd.concat((df1, df2), axis=1, ignore_index=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You are not limited to just two dataframes, you can concatenate as many as you want:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "pd.concat((df1, df2, df1, df2), axis=0, ignore_index=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Joining DataFrames with `pd.merge()`" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`pd.merge()` gives you the ability to \"join\" dataframes using different rules (just like with SQL if you're familiar with it). You can use `df.merge()` to join dataframes based on shared `key` columns. Methods include:\n", "- \"inner join\"\n", "- \"outer join\"\n", "- \"left join\"\n", "- \"right join\"\n", "\n", "See this great [cheat sheet](https://pandas.pydata.org/pandas-docs/stable/getting_started/comparison/comparison_with_sql.html#compare-with-sql-join) and [these great animations](https://github.com/gadenbuie/tidyexplain) for more insights." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df1 = pd.DataFrame({\"name\": ['Magneto', 'Storm', 'Mystique', 'Batman', 'Joker', 'Catwoman', 'Hellboy'],\n", " 'alignment': ['bad', 'good', 'bad', 'good', 'bad', 'bad', 'good'],\n", " 'gender': ['male', 'female', 'female', 'male', 'male', 'female', 'male'],\n", " 'publisher': ['Marvel', 'Marvel', 'Marvel', 'DC', 'DC', 'DC', 'Dark Horse Comics']})\n", "df2 = pd.DataFrame({'publisher': ['DC', 'Marvel', 'Image'],\n", " 'year_founded': [1934, 1939, 1992]})" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "An \"inner\" join will return all rows of `df1` where matching values for \"publisher\" are found in `df2`:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "pd.merge(df1, df2, how=\"inner\", on=\"publisher\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "An \"outer\" join will return all rows of `df1` and `df2`, placing NaNs where information is unavailable:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "pd.merge(df1, df2, how=\"outer\", on=\"publisher\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Return all rows from `df1` and all columns of `df1` and `df2`, populated where matches occur:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "pd.merge(df1, df2, how=\"left\", on=\"publisher\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "pd.merge(df1, df2, how=\"right\", on=\"publisher\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "There are many ways to specify the `key` to join dataframes on, you can join on index values, different, column names, etc. Another helpful argument is the `indicator` argument which will add a column to the result telling you where matches were found in the dataframes:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "pd.merge(df1, df2, how=\"outer\", on=\"publisher\", indicator=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "By the way, you can use `pd.concat()` to do a simple \"inner\" or \"outer\" join on multiple datadrames at once. It's less flexible than merge, but can be useful sometimes." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 5. More DataFrame Operations\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Applying Custom Functions" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "There will be times when you want to apply a function that is not built-in to Pandas. For this, we also have methods:\n", "- `df.apply()`, applies a function column-wise or row-wise across a dataframe (the function must be able to accept/return an array)\n", "- `df.applymap()`, applies a function element-wise (for functions that accept/return single values at a time)\n", "- `series.apply()`/`series.map()`, same as above but for Pandas series" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "For example, say you want to use a numpy function on a column in your dataframe:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df = pd.read_csv('https://raw.githubusercontent.com/ElcoK/BigData_AED/main/week1/cycling_data.csv')\n", "df[['Time', 'Distance']].apply(np.sin)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Or you may want to apply your own custom function:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def seconds_to_hours(x):\n", " return x / 3600\n", "\n", "df[['Time']].apply(seconds_to_hours)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This may have been better as a lambda function..." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df[['Time']].apply(lambda x: x / 3600)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can even use functions that require additional arguments. Just specify the arguments in `.apply()`:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def convert_seconds(x, to=\"hours\"):\n", " if to == \"hours\":\n", " return x / 3600\n", " elif to == \"minutes\":\n", " return x / 60\n", "\n", "df[['Time']].apply(convert_seconds, to=\"minutes\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Some functions only accept/return a scalar:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "int(3.141)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [ "raises-exception" ] }, "outputs": [], "source": [ "float([3.141, 10.345])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "For these, we need `.applymap()`:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df[['Time']].applymap(int)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "However, there are often \"vectorized\" versions of common functions like this already available, which are much faster. In the case above, we can use `.astype()` to change the dtype of a whole column quickly:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "time_applymap = %timeit -q -o -r 3 df[['Time']].applymap(float)\n", "time_builtin = %timeit -q -o -r 3 df[['Time']].astype(float)\n", "print(f\"'astype' is {time_applymap.average / time_builtin.average:.2f} faster than 'applymap'!\")" ] }, { "cell_type": "markdown", "metadata": { "tags": [ "raises-exception" ] }, "source": [ "### Grouping" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Often we are interested in examining specific groups in our data. `df.groupby()` allows us to group our data based on a variable(s)." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df = pd.read_csv('https://raw.githubusercontent.com/ElcoK/BigData_AED/main/week1/cycling_data.csv')\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let's group this dataframe on the column `Name`:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dfg = df.groupby(by='Name')\n", "dfg" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "What is a `DataFrameGroupBy` object? It contains information about the groups of the dataframe:\n", "\n", "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The groupby object is really just a dictionary of index-mappings, which we could look at if we wanted to:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dfg.groups" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can also access a group using the `.get_group()` method:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dfg.get_group('Afternoon Ride')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The usual thing to do however, is to apply aggregate functions to the groupby object:\n", "\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dfg.mean()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can apply multiple functions using `.aggregate()`:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dfg.aggregate(['mean', 'sum', 'count'])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "And even apply different functions to different columns:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def num_range(x):\n", " return x.max() - x.min()\n", "\n", "dfg.aggregate({\"Time\": ['max', 'min', 'mean', num_range], \n", " \"Distance\": ['sum']})" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "By the way, you can use aggregate for non-grouped dataframes too. This is pretty much what `df.describe` does under-the-hood:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.agg(['mean', 'min', 'count', num_range])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Dealing with Missing Values" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Missing values are typically denoted with `NaN`. We can use `df.isnull()` to find missing values in a dataframe. It returns a boolean for each element in the dataframe:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.isnull()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "But it's usually more helpful to get this information by row or by column using the `.any()` or `.info()` method:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.info()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df[df.isnull().any(axis=1)]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "When you have missing values, we usually either drop them or impute them.You can drop missing values with `df.dropna()`:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.dropna()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Or you can impute (\"fill\") them using `.fillna()`. This method has various options for filling, you can use a fixed value, the mean of the column, the previous non-nan value, etc:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df = pd.DataFrame([[np.nan, 2, np.nan, 0],\n", " [3, 4, np.nan, 1],\n", " [np.nan, np.nan, np.nan, 5],\n", " [np.nan, 3, np.nan, 4]],\n", " columns=list('ABCD'))\n", "df" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.fillna(0) # fill with 0" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.fillna(df.mean()) # fill with the mean" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.fillna(method='bfill') # backward (upwards) fill from non-nan values" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.fillna(method='ffill') # forward (downward) fill from non-nan values" ] } ], "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.10.6" }, "vscode": { "interpreter": { "hash": "9aa67eedecbb1544f55b95cced32a93bf08cf46b6b214074d43890cbd05ea300" } }, "widgets": { "application/vnd.jupyter.widget-state+json": { "state": {}, "version_major": 2, "version_minor": 0 } } }, "nbformat": 4, "nbformat_minor": 4 }