{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "
\n", "\n", "Created by [Nathan Kelber](http://nkelber.com) and Ted Lawless for [JSTOR Labs](https://labs.jstor.org/) under [Creative Commons CC BY License](https://creativecommons.org/licenses/by/4.0/)
\n", "For questions/comments/improvements, email nathan.kelber@ithaka.org.
\n", "___" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Pandas I**\n", "\n", "**Description:** This notebook describes how to:\n", "* Create a Pandas Series or DataFrame\n", "* Accessing data rows, columns, elements using `.loc` and `.iloc`\n", "* Creating filters using boolean operators\n", "* Changing data in rows, columns, and elements\n", "\n", "This is the first notebook in a series on learning to use Pandas. \n", "\n", "**Use Case:** For Learners (Detailed explanation, not ideal for researchers)\n", "\n", "**Difficulty:** Intermediate\n", "\n", "**Knowledge Required:** \n", "* Python Basics ([Start Python Basics I](./python-basics-1.ipynb))\n", "\n", "**Knowledge Recommended:** \n", "* [Working with Dataset Files](./working-with-dataset-files.ipynb)\n", "\n", "**Completion Time:** 75 minutes\n", "\n", "**Data Format:** CSV (.csv)\n", "\n", "**Libraries Used:** Pandas\n", "\n", "**Research Pipeline:** None\n", "___" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# When to use Pandas" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Pandas is a Python data analysis and manipulation library. When it comes to viewing and manipulating data, most people are familiar with commercial spreadsheet software, such as Microsoft Excel or Google Sheets. While spreadsheet software and Pandas can accomplish similar tasks, each has significant advantages depending on the use-case.\n", "\n", "**Advantages of Spreadsheet Software**\n", "* Point and click\n", "* Easier to learn\n", "* Great for small datasets (<10,000 rows)\n", "* Better for browsing data\n", "\n", "**Advantages of Pandas**\n", "* More powerful data manipulation with Python\n", "* Can work with large datasets (millions of rows)\n", "* Faster for complicated manipulations\n", "* Better for cleaning and/or pre-processing data\n", "* Can automate workflows in a larger data pipeline\n", "\n", "In short, spreadsheet software is better for browsing small datasets and making moderate adjustments. Pandas is better for automating data cleaning processes that require large or complex data manipulation.\n", "\n", "Pandas can interpret a wide variety of data sources, including Excel files, CSV files, and Python objects like lists and dictionaries. Pandas converts these into two fundamental objects: \n", "\n", "* Data Series- a single column of data\n", "* DataFrame- a table of data containing multiple columns and rows" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Pandas Series\n", "\n", "We can think of a Series as a single column of data. A DataFrame then is made by combining Series objects side-by-side into a table that has both height and width. Let's create a Series based on this data about the world's ten most-populated countries [according to Wikipedia](https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population).\n", "\n", "|Population (in millions)|\n", "|---|\n", "|1,404|\n", "|1,366|\n", "|330|\n", "|269|\n", "|220|\n", "|211|\n", "|206|\n", "|169|\n", "|146|\n", "|127|\n", "\n", "We can put the population data into a Series." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# import pandas, `as pd` allows us to shorten typing `pandas` to `pd` for each \n", "import pandas as pd" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Create a data series in Pandas\n", "worldpop = pd.Series([1404, 1366, 330, 269, 220, 211, 206, 169, 146, 127])\n", "\n", "# Give our series a name\n", "worldpop.name = 'World Population (In Millions)'\n", "print(worldpop)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Underneath the Series is a `dtype` which describes the way the data is stored in the Series. Here we see `int64`, denoting the data is a 64-bit integer.\n", "\n", "## `.iloc[]` Integer Location Selection\n", "\n", "To the left of each Series is an index number. This index number is very similar to a Python list index; it can help us reference a particular row for data retrieval. Also, like a Python list, the index to a Series begins with 0. We can retrieve individual elements in a Series using the `.iloc` attribute, which stands for \"integer location.\" " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Return the 4th element in our series\n", "worldpop.iloc[3]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Return a slice of elements in our series\n", "# This slice will not include element 4\n", "worldpop.iloc[2:4]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "By default, our Series has a numerical index like a Python list, but it would be much easier to use if our Series had names like a Python dictionary. We can \n", "\n", "It is cumbersome to remember the index number for each country, so we can instead give each row an index with names." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Rename the index to use names instead of numerical indexes\n", "worldpop.index = [\n", " 'China',\n", " 'India',\n", " 'United States',\n", " 'Indonesia',\n", " 'Pakistan',\n", " 'Brazil',\n", " 'Nigeria',\n", " 'Bangladesh',\n", " 'Russia',\n", " 'Mexico'\n", "]\n", "\n", "worldpop" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## `.loc[]` Location Selection\n", "Now we can also reference each element by its index name, very similar to how we can supply a key to a dictionary to get a value. We use the `.loc` attribute." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Return the series value for Nigeria\n", "worldpop.loc['Nigeria']" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Return a series value for Indonesia and Mexico\n", "worldpop.loc[['Indonesia', 'Mexico']]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Return a slice from Nigeria to Russia\n", "# This slice will include the final element!\n", "worldpop.loc['Nigeria':'Russia']" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "A Series is like an ordered dictionary. In fact, we can create a Series out of a list (where the index will automatically be numerical starting at 0) or a dictionary (where the keys are the index)." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Creating a Series from a dictionary\n", "# Based on most populous cities in the world according to Wikipedia\n", "\n", "worldcitiespop = pd.Series({\n", " 'Tokyo': 37,\n", " 'Delhi': 28,\n", " 'Shanghai': 25,\n", " 'São Paulo': 21,\n", " 'Mexico City': 21,\n", " 'Cairo': 20,\n", " 'Mumbai': 19,\n", " 'Beijing': 19,\n", " 'Dhaka': 19,\n", " 'Osaka': 19,\n", "}, name='World City Populations (In Millions)')\n", "\n", "#Return the series\n", "worldcitiespop" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Boolean Expressions\n", "\n", "We have seen already how we can select a particular value in a series by using an index name or number. We can also select particular values using Boolean expressions. An expression will evaluate to a Truth Table." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Which countries have populations greater than 200 million?\n", "worldpop > 200" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Instead of evaluating to a Truth Table, we can also evaluate to a smaller series." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Evaluate worldpop for `worldpop > 200`\n", "worldpop.loc[worldpop > 200]\n", "\n", "# If we wanted to save this to a new series variable\n", "#new_series = worldpop[worldpop > 200]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Pandas uses `|` to represent `or` operations. It uses `&` to represent `and` operations. We can also use `~` for negation.\n", "\n", "|Pandas Operator|Boolean|Requires|\n", "|---|---|---|\n", "|&|and|All required to `True`|\n", "|\\||or|If any are `True`|\n", "|~|not|The opposite|" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "worldpop.loc[(worldpop > 500) | (worldpop < 250)]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Modifying a Series\n", "\n", "We can use an initialization statement to change a value in our Series. " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Change the population of China to 1500\n", "worldpop.loc['China'] = 1500\n", "print(worldpop)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Change the population of several countries based on an expression\n", "worldpop.loc[worldpop < 300] = 25\n", "worldpop" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Summary of Pandas Series\n", "\n", "* A Series is a single column of data that may contain a Name and Index\n", "* Use `.iloc` to select a row by index number\n", "* Use `.loc` to select a row by index name\n", "* Use an initialization statement to change values\n", "* Boolean operators include & (and), | (or), ~ (negation) " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Pandas DataFrame\n", "\n", "If a Series is like a column of data, a DataFrame is like a table connecting multiple columns together. DataFrames can contain thousands or millions of rows and columns. When working with DataFrames, we are usually using a dataset that has been compiled by someone else. Often the data will be in the form of a CSV or Excel file. " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "\n", "# Create a DataFrame `df` from the CSV file 'sample2.csv'\n", "df = pd.read_csv('data/sample2.csv', index_col='Username')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Exploring DataFrame Contents\n", "Now that we have a DataFrame called `df`, we need to learn a little more about its contents. The first step is usually to explore the DataFrame's attributes. Attributes are properties of the dataset (not functions), so they do not have parentheses `()` after them. \n", "\n", "|Attribute|Reveals|\n", "|---|---|\n", "|.shape| The number of rows and columns|\n", "|.info| The shape plus the first and last 5 rows|\n", "|.columns| The name of each column|\n", "|.rows| The name of each row|" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Use `.shape` to find rows and columns in the DataFrame\n", "df.shape" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Use `.info` to find the shape plus the first and last five rows of the DataFrame\n", "df.info" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Use `.columns` to find the name of each column (if they are named)\n", "df.columns" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can use `.index` attribute to discover the name for each row in our DataFrame. We set the index column to `Username`, but `Identifier` would also make sense. If no column is chosen, a numeric index is created starting at 0." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Use `.index` to list the rows of our DataFrame\n", "df.index" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Preview with `.head()` and `.tail()`\n", "We can also use the `.head()` and `.tail` methods to get a preview of our DataFrame." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Use `.head()` to see the first five lines\n", "# Pass an integer into .head() to see a different number of lines\n", "df.head()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Use `.tail()` to see the last five lines\n", "# Pass an integer into .tail() to see a different number lines\n", "df.tail()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Display More Rows or Columns\n", "By default, Pandas limits the number of rows and columns to display. If desired, we can increase or decrease the number to display. If your DataFrame has limited number of rows or columns, you may wish to show all of them." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Show all columns\n", "# Set `None` to an integer to show a set number\n", "pd.set_option('display.max_columns', None)\n", "\n", "# Show all rows\n", "# Set `None` to an integer to show a set number\n", "# Be careful if your dataset is thousands of lines long!\n", "pd.set_option('display.max_rows', None)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Change Column Names\n", "If we wanted to change the column names, one option is to modify the original data file. We can also change the column names in the DataFrame." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Updating all column names at once\n", "df.columns = ['email', 'Identifier', 'First name', 'Last name']\n", "df" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Updating a single column name\n", "df.rename(columns={'email': 'Login email'}, inplace=True)\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Reset the Index\n", "\n", "When we created the dataframe, we used the `index_col` attribute to set the index column to the `Username` column.\n", "\n", "```df = pd.read_csv('data/sample2.csv', index_col='Username')```\n", "\n", "We could reset the index to a numerical index starting at 0 using the `.reset_index()` method." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Reset the Index for the DataFrame to integers\n", "# creating a new column\n", "# Passing a `inplace=True` makes the change immediately\n", "df.reset_index()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "For many operations that will alter a DataFrame, such as `.reset_index`, the changes will be previewed unless a `inplace=True` parameter is passed. This allows users to preview changes to the data before implementing them in a permanent fashion. Of course, you should always work on a copy of your data in case a manipulation goes awry. " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Confirm index has not been changed\n", "df" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Make the change to reset the index\n", "df.reset_index(inplace=True)\n", "# Print the index, now changed\n", "df" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "scrolled": false }, "outputs": [], "source": [ "# Change the index back to `Username`\n", "df.set_index('Username', inplace=True)\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Sorting the Index\n", "\n", "We can sort the index by using `sort_index()`." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Sort the DataFrame by ascending order\n", "df.sort_index()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Sort by descending order\n", "df.sort_index(ascending=False)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## `.loc[]` and `.iloc[]` Selection\n", "\n", "Like Series, DataFrames can use the `.iloc[]` and `.loc[]` methods for selection. To select a particular element, we need to supply a row *and* a column.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# View our DataFrame for reference\n", "df" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Return the value for the specified row and column\n", "df.iloc[6, 3]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Return the value for the specified row and column\n", "df.loc['booker12', 'First name']" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Select an entire row\n", "df.loc['redtree333', :]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Technically, we could also use: `df.loc['redtree333']` for the same result, but including the `, :` makes our row and column selections explicit, where the `:` is basically a slice that includes the whole column. Using a `:` is required if we want to select an entire column using `.loc[]` since the row selection comes before the column selection. " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Select an entire column\n", "df.loc[:, 'Login email']" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Of course, we can use the `:` to make a slice using `.loc[]` or `.loc`." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Slicing rows and columns using `.iloc`\n", "df.iloc[0:3, 1:4]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Note that `.iloc[]` slicing is not inclusive of the final value, similar to a Python list**. On the other hand, `.loc[]` slicing *is* inclusive. The reason for this difference is that it would make the code confusing since we would need to include whatever name is *after* the name we want to include." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "scrolled": true }, "outputs": [], "source": [ "# Slicing rows and columns using `.loc`\n", "df.loc['booker12':'french999', 'Login email':'First name']" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Boolean Expressions\n", "We can also use Boolean expressions to select based on the contents of the elements. We can use these expressions to create filters for selecting particular rows or columns.\n", "\n", "|Pandas Operator|Boolean|Requires|\n", "|---|---|---|\n", "|&|and|All required to `True`|\n", "|\\||or|If any are `True`|\n", "|~|not|The opposite|\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "scrolled": true }, "outputs": [], "source": [ "df" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Return a Truth Table for the `Identifier` column\n", "# Where the Identifier is more than 4000\n", "df.loc[:, 'Identifier'] > 4000" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Preview every row where the Identifier is more than 4000\n", "id_filter = (df.loc[:, 'Identifier'] > 4000)\n", "df.loc[id_filter, :]\n", "\n", "# Alternatively, the whole expression can be written out\n", "# But this can be a little more difficult to read\n", "# In this case, it is a good idea to include parentheses\n", "# To make clear the row filter is one expression\n", "#df.loc[(df.loc[:, 'Identifier'] > 4000), :]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Preview every row with Last name not \"Smith\"\n", "name_filter = df.loc[:, 'Last name'] == 'Smith'\n", "df.loc[name_filter, :]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Select the row with `First Name` of Jamie\n", "# And last name of `Smith`\n", "name_filter = (df.loc[:, 'Last name'] == 'Smith') & (df.loc[:, 'First name'] == 'Jamie')\n", "df.loc[name_filter, :]\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "scrolled": true }, "outputs": [], "source": [ "# Find every row with Last Name not `Smith`\n", "name_filter = (df.loc[:, 'Last name'] == 'Smith')\n", "df.loc[~name_filter, :]\n", "\n", "# Or alternatively\n", "#name_filter = (df.loc[:, 'Last name'] != 'Smith')\n", "#df.loc[name_filter, :]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Modifying a DataFrame\n", "\n", "A single element can be changed with an initialization statement." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Change a value using `.loc[]`\n", "df.loc['jenkins46', 'First name'] = 'Mark'\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can also use filters for more powerful manipulation." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Create a string filter that checks for email addresses containing\n", "# 'example.com'. For missing (na) elements, output `False` instead of NaN.\n", "email_filt = df['Login email'].str.contains('example.com', na=False)\n", "email_filt" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Re-Initialize `df` without the users with no email address\n", "df = df[email_filt]\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Dropping Rows Without Data\n", "There is also a `.dropna()` method specifically for dropping rows without data" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Recreate the DataFrame `df` from the CSV file 'sample2.csv'\n", "df = pd.read_csv('data/sample2.csv', index_col='Username')\n", "df # Confirm the NaN fields have returned" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Remove all rows without a `Login email` using `.dropna()`\n", "df = df.dropna(subset=['Login email'])\n", "df # Confirm the fields were dropped" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Summary of Pandas DataFrames\n", "\n", "* A DataFrame has multiple rows and columns\n", "* Use attributes along with `.head()` and `.tail()` to explore the DataFrame\n", "* Use `.iloc` and `.loc` to select an column, row, or element\n", "* Use `inplace=True` to confirm certain manipulations\n", "* Filters and Boolean Operators can be powerful selectors\n", "* Use an initialization statement to change one or many elements\n", "* Drop rows without data using the `.dropna()` method" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "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": null }, "toc": { "base_numbering": 1, "nav_menu": {}, "number_sections": true, "sideBar": true, "skip_h1_title": false, "title_cell": "Table of Contents", "title_sidebar": "Contents", "toc_cell": false, "toc_position": { "height": "calc(100% - 180px)", "left": "10px", "top": "150px", "width": "278px" }, "toc_section_display": true, "toc_window_display": true } }, "nbformat": 4, "nbformat_minor": 4 }