Newer
Older
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"[pandas](http://pandas.pydata.org) provides high-level data structures and functions designed to make working with structured or tabular data fast, easy and expressive. The primary objects in pandas that we will be using are the `DataFrame`, a tabular, column-oriented data structure with both row and column labels, and the `Series`, a one-dimensional labeled array object.\n",
"\n",
"pandas blends the high-performance, array-computing ideas of NumPy with the flexible data manipulation capabilities of spreadsheets and relational databases. It provides sophisticated indexing functionality to make it easy to reshape, slice and perform aggregations.\n",
"While pandas adopts many coding idioms from NumPy, the most significant difference is that pandas is designed for working with tabular or heterogeneous data. NumPy, by contrast, is best suited for working with homogeneous numerical array data.\n",
"- [Data Structures](#structures)\n",
" - [Series](#series)\n",
" - [DataFrame](#dataframe)\n",
"- [Essential Functionality](#ess_func)\n",
" - [Removing Entries](#removing)\n",
" - [Indexing](#indexing)\n",
" - [Arithmetic Operations](#arithmetic)\n",
"- [Summarizing and Computing Descriptive Statistics](#sums)\n",
"- [Loading and storing data](#loading)\n",
"- [Data Cleaning and preperation](#cleaning)\n",
" - [Handling missing data](#missing)\n",
" - [Data transformation](#transformation)\n",
"\n",
"The common pandas import statment is shown below:"
"metadata": {},
"outputs": [],
"source": [
"# Common pandas import statement\n",
"import pandas as pd"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Data Structures <a name=\"structures\"></a>\n",
"## Series <a name=\"series\"></a>\n",
"A Series is a one-dimensional array-like object containing a sequence of values and an associated array of data labels called its index.\n",
"\n",
"The easiest way to make a Series is from an array of data:"
]
},
{
"cell_type": "code",
"metadata": {},
"outputs": [],
"source": [
"data = pd.Series([4, 7, -5, 3])"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now try printing out data"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The string representation of a Series displayed interactively shows the index on the left and the values on the right. Because we didn't specify an index, the default one is simply integers 0 through N-1.\n",
"It is possible to get only the index or only the values of a Serioes with:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"You can specify custom indices when intialising the Series"
"cell_type": "code",
"execution_count": null,
"data2 = pd.Series([4, 7, -5, 3], index=[\"a\", \"b\", \"c\", \"d\"])"
"execution_count": null,
"metadata": {},
"outputs": [],
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Another way to think about Series is as a fixed-length ordered dictionary. Furthermore, you can actually define a Series in a similar manner to a dictionary"
"cities = {\"Glasgow\" : 599650, \"Edinburgh\" : 464990, \"Aberdeen\" : 196670, \"Dundee\" : 147710}\n",
"data3 = pd.Series(cities)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"data3"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"A DataFrame represents a rectangular table of data and contains an ordered collection of columns, each of which can be a different value type. The DataFrame has both row and column index and can be thought of as a dict of Series all sharing the same index.\n",
"\n",
"The most common way to create a DataFrame is with dicts:"
"data = {\"cities\" : [\"Glasgow\", \"Edinburgh\", \"Aberdeen\", \"Dundee\"],\n",
" \"population\" : [599650, 464990, 196670, 147710],\n",
" \"year\" : [2011, 2013, 2013, 2013]}\n",
"frame = pd.DataFrame(data)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Try printing it out"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"frame"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Jupyter Notebooks prints it out in a nice table but the basic version of this is also just as readable!\n",
"\n",
"Additionally you can also specify the order of columns during initialisation"
]
},
{
"cell_type": "code",
"frame2 = pd.DataFrame(data, columns=[\"year\", \"cities\", \"population\"])\n",
"frame2"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You can retrieve a particular column from a DataFrame with\n",
"```python\n",
"frame[\"cities\"]\n",
"```\n",
"The result is going to be a Series."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"It is also possible to add and modify the columns of a DataFrame"
"execution_count": null,
"metadata": {},
"outputs": [],
"frame2[\"size\"] = [175, 264, 65.1, 60] # in km^2\n",
"frame2"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Another common way of creating DataFrames is from a nested dict of dicts:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"data2 = {\"Glasgow\": {2011: 599650},\n",
" \"Edinburgh\": {2013:464990},\n",
"frame3 = pd.DataFrame(data2)\n",
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Here is a table of different ways of initialising a DataFrame for your reference\n",
"\n",
"| Type | Notes |\n",
"| --- | --- |\n",
"| 2D ndarray | A matrix of data; passing optional row and column labels |\n",
"| dict of arrays, lists, or tuples | Each sequence becomes a column in the DataFrame; all sequences must be the same length |\n",
"| dict of Series | Each value becomes a column; indexes from each Series are unioned together to<br>form the result's row index if not explicit index is passed |\n",
"| dict of dicts | Each inner dict becomes a column; keys are unioned to form the row<br>index as in the \"dict of Series\" case |\n",
"| List of dicts or Series | Each item becomes a row in the DataFrame; union of dict keys or<br>Series indices becomes the DataFrame's column labels |\n",
"| List of lists or tuples | Treated as the \"2D ndarray\" case |"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Essential Functionality <a name=\"ess_func\"></a>\n",
"In this section, we will go through the fundamental mechanics of interacting with the data contained in a Series or DaraFrame."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Removing columns/indices <a name=\"removing\"></a>\n",
"Similar to above, it is easy to remove entries. This is done with the `drop()` method and can be applied to both columns and indices:"
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# define new DataFrame\n",
"data = np.reshape(np.arange(9), (3,3))\n",
"df = pd.DataFrame(data, index=[\"a\", \"b\", \"c\"],\n",
" columns=[\"Edinburgh\", \"Glasgow\", \"Aberdeen\"])\n",
"\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df.drop(\"b\") # remove row (index)"
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# You can also drop from a column\n",
"df.drop([\"Aberdeen\", \"Edinburgh\"], axis=\"columns\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Note that the original data frame is unchanged: `df.drop()` gives us a new data frame with the desired data dropped, and leaves the original data intact. We can ask `.drop()` to operate directly on the original data frame by setting the argument `inplace=True`."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Indexing <a name=\"indexing\"></a>\n",
"Indexing in pandas works simiarly to numpy, but we have to use `.iloc` instead of just normal indexing"
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"data = np.reshape(np.arange(9), (3,3))\n",
"df = pd.DataFrame(data, index=[\"a\", \"b\", \"c\"],\n",
" columns=[\"Edinburgh\", \"Glasgow\", \"Aberdeen\"])\n",
"\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df[2] # this won't work"
"execution_count": null,
"metadata": {},
"outputs": [],
"df.iloc[2] # this works and return a series"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You can also slice a dataframe as usual:"
"execution_count": null,
"metadata": {},
"outputs": [],
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Finally, you can index into many dimensions as seen in NumPy:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df.iloc[2,0]"
]
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Exercise 1\n",
"A dataset of random numbers is created below. Index the 47th row and the 22nd column. You should get the number **4621**.\n",
"\n",
"*Note: Remember that Python uses 0-based indexing*"
"execution_count": null,
"metadata": {},
"outputs": [],
"df = pd.DataFrame(np.reshape(np.arange(10000), (100,100)))\n"
"### Exercise 2\n",
"Using the same DataFrame from the previous exercise, obtain all rows starting from row 85 to 97."
"execution_count": null,
"metadata": {},
"outputs": [],
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Arithmetic <a name=\"arithmetic\"></a>\n",
"When you are performing arithmetic operations between two objects, if any index pairs are not the same, the respective index in the result will be the union of the index pair. Let's have a look"
"execution_count": null,
"metadata": {},
"outputs": [],
"df1 = pd.DataFrame(np.arange(12).reshape((3,4)),\n",
" columns=list(\"abcd\"))\n",
"df1"
"cell_type": "code",
"execution_count": null,
"df2 = pd.DataFrame(np.arange(12).reshape((3,4)),\n",
" columns=list(\"cdef\"))\n",
"df2"
"execution_count": null,
"metadata": {},
"outputs": [],
"# adding the two\n",
"df1+df2"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Notice how where we don't have matching values from `df1` and `df2` the output of the addition operation is `NaN` since there are no two numbers to add.\n",
"\n",
"Well, we can \"fix\" that by filling in the `NaN` values. This effectively tells pandas where there are no two values to add, assume that the missing value is just zero."
"execution_count": null,
"metadata": {},
"outputs": [],
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Here's a list of all arithmetic operations within panda. They are similar to NumPy!\n",
"| Operator | Method | Description |\n",
"| -- | -- | -- |\n",
"| + | add | Addition |\n",
"| - | sub | Subtraction |\n",
"| / | div | Division |\n",
"| // | floordiv | Floor division |\n",
"| * | mul | Multiplication |\n",
"| ** | pow | Exponentiation |\n"
"### Exercise 3\n",
"Create a (3,3) DataFrame and square all elements in it."
"execution_count": null,
"metadata": {},
"outputs": [],
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Summarizing and computing descriptive stats <a name=\"sums\"></a>\n",
"`pandas` is equipped with common mathematical and statistical methods. Most of which fall into the category of reductions or summary statistics. These are methods that extract a single value from a list of values. For example, you can extract the sum of a `Series` object like this:"
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df = pd.DataFrame(np.arange(20).reshape(5,4),\n",
" columns=[\"a\", \"b\", \"c\", \"d\"])\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df.sum()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Notice how that created the sum of each column?\n",
"\n",
"Well you can actually make that the other way around by adding an extra option to `sum()`"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df.sum(axis=\"columns\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"A similar method also exists for obtaining the mean of data:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df.mean()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Finally, the mother of the methods we discussed here is `describe()` "
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df.describe()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Here are some of the summary methods:\n",
"\n",
"| Method | Description |\n",
"| -- | -- |\n",
"| count | Return number of non-NA values |\n",
"| describe | Set of summary statistics |\n",
"| min, max | Minimum, maximum values |\n",
"| argmin, argmax | Index locations at which the minimum or maximum value is obtained | \n",
"| sum | Sum of values |\n",
"| mean | Mean of values |\n",
"| median | Arithmetic median of values |\n",
"| std | Sample standard deviation of values\n",
"| value_counts() | Counts the number of occurrences of each unique element in a column |"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Exercise 4\n",
"\n",
"A random DataFrame is created below. Find it's mean and standard deviation, then normalise it column-wise according to the formula:\n",
"\n",
"$$ Y = \\frac{X - \\mu}{\\sigma} $$\n",
"\n",
"Where X is your dataset, $\\mu$ is the mean and $\\sigma$ is the standard deviation.\n",
"\n"
]
},
{
"cell_type": "code",
"metadata": {},
"outputs": [],
"source": [
"df = pd.DataFrame(np.random.uniform(0, 10, (100, 100)))\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Accessing data is a necessary first step for data science.\n",
"\n",
"Data usually fall into these categories:\n",
"- text files\n",
"- binary files (more efficient space-wise)\n",
"- web data\n",
"\n",
"## Text formats <a name=\"text\"></a>\n",
"The most common format in this category is by far `.csv`. This is an easy to read file format which is usually visualised like a spreadsheet. The data itself is usually separated with a `,` which is called the **delimiter**.\n",
"\n",
"Here is an example of a `.csv` file:\n",
"\n",
"```\n",
"Sell,List,Living,Rooms,Beds,Baths,Age,Acres,Taxes\n",
"142, 160, 28, 10, 5, 3, 60, 0.28, 3167\n",
"175, 180, 18, 8, 4, 1, 12, 0.43, 4033\n",
"129, 132, 13, 6, 3, 1, 41, 0.33, 1471\n",
"138, 140, 17, 7, 3, 1, 22, 0.46, 3204\n",
"232, 240, 25, 8, 4, 3, 5, 2.05, 3613\n",
"135, 140, 18, 7, 4, 3, 9, 0.57, 3028\n",
"150, 160, 20, 8, 4, 3, 18, 4.00, 3131\n",
"207, 225, 22, 8, 4, 2, 16, 2.22, 5158\n",
"271, 285, 30, 10, 5, 2, 30, 0.53, 5702\n",
" 89, 90, 10, 5, 3, 1, 43, 0.30, 2054\n",
" ```\n",
"\n",
"It detailed home sale statistics. The first line is called the header, and you can imagine that it is the name of the columns of a spreadsheet.\n",
"\n",
"Let's now see how we can load this data and analyse it. The file is located in the folder `data` and is called `homes.csv`. We can read it like this:"
]
},
{
"cell_type": "code",
"metadata": {},
"outputs": [],
"source": [
"homes = pd.read_csv(\"data/homes.csv\")\n",
"homes"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Easy right?"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Find the mean selling price of the homes in `data/homes.csv`"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The `read_csv` function has a lot of optional arguments (more than 50). It's impossible to memorise all of them - it's usually best just to look up the particular functionality when you need it. \n",
"\n",
"You can search `pandas read_csv` online and find all of the documentation.\n",
"\n",
"There are also many other functions that can read textual data. Here are some of them:\n",
"\n",
"| Function | Description\n",
"| -- | -- |\n",
"| read_csv | Load delimited data from a file, URL, or file-like object. The default delimiter is a comma `,` |\n",
"| read_table | Load delimited data from a file, URL, or file-like object. The default delimiter is tab `\\t` |\n",
"| read_clipboard | Reads the last object you have copied (Ctrl-C) |\n",
"| read_excel | Read tabular data from Excel XLS or XLSX file |\n",
"| read_hdf | Read HDF5 file written by pandas |\n",
"| read_html | Read all tables found in the given HTML document |\n",
"| read_json | Read data from a JSON string representation |\n",
"| read_sql | Read the results of a SQL query |\n",
"\n",
"*Note: there are also other loading functions which are not touched upon here*"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"There is a dataset `data/yob2012.txt` which lists the number of newborns registered in 2018 with their names and sex. Open the dataset in pandas **as a csv**, explore it and derive the ratio between male and female newborns.\n",
"\n",
"*Note: The file doesn't contain a header so you will need to add your own column names with*\n",
"```python\n",
"pd.read_csv(\"...\", names=[\"Some\", \"Fun\", \"Columns\"]\n",
"```\n"
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Data Cleaning <a name=\"cleaning\"></a>\n",
"While doing data analysis and modeling, a significant amount of time is spent on data preparation: loading, cleaning, transforming and rearranging. Such tasks are often reported to take **up to 80%** or more of a data analyst's time. Often the way the data is stored in files isn't in the correct format and needs to be modified. Researchers usually do this on an ad-hoc basis using programming languages like Python.\n",
"In this chapter, we will discuss tools for handling missing data, duplicate data, string manipulation, and some other analytical data transformations.\n",
"Missing data occurs commonly in many data analysis applications. One of the goals of pandas is to make working with missing data as painless as possible.\n",
"In pandas, missing numeric data is represented by `NaN` (Not a Number) and can easily be handled:"
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"string_data = pd.Series(['orange', 'tomato', np.nan, 'avocado'])\n",
"string_data"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"string_data.isnull()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Furthermore, the pandas `NaN` is functionally equlevant to the standard Python type `NoneType` which can be defined with `x = None`."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"string_data[0] = None\n",
"string_data"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"string_data.isnull()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Here are some other methods which you can find useful:\n",
" \n",
"| dropna | Filter axis labels based on whether the values of each label have missing data|\n",
"| fillna | Fill in missing data with some value |\n",
"| isnull | Return boolean values indicating which values are missing |\n",
"| notnull | Negation of isnull |\n",
"\n",
"Just like `.drop()`, these methods all return a new object, leaving the original unchanged (this behaviour can be overridden using the argument `inplace=True`).\n",
"\n",
"Remove the missing data below using the appropriate method"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"data = pd.Series([1, None, 3, 4, None, 6])\n",
"data"
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"`dropna()` by default removes any row/column that has a missing value. What if we want to remove only rows in which all of the data is missing though?"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"data = pd.DataFrame([[1., 6.5, 3.], [1., None, None],\n",
" [None, None, None], [None, 6.5, 3.]])\n",
"data"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"data.dropna()"
]
},
{
"cell_type": "code",
"source": [
"data.dropna(how=\"all\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"That's fine if we want to remove missing data, what if we want to fill in missing data? Do you know of a way? Try to fill in all of the missing values from the data below with **0s**"
]
},
{
"cell_type": "code",
"metadata": {},
"outputs": [],
"source": [
"data = pd.DataFrame([[1., 6.5, 3.], [2., None, None],\n",
" [None, None, None], [None, 1.5, 9.]])\n",
"data"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Data Transformation <a name=\"transformation\"></a>\n",
"### Removing duplicates\n",
"Duplicate data can be a serious issue, luckily pandas offers a simple way to remove duplicates"
]
},
{
"cell_type": "code",
"source": [
"data = pd.DataFrame([1, 2, 3, 4, 3, 2, 1])\n",
"data"
]
},
{
"cell_type": "code",
"source": [
"data.drop_duplicates()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You can also select which rows to keep"
]
},
{
"cell_type": "code",
"source": [
"data.drop_duplicates(keep=\"last\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You've already seen how you can fill in missing data with `fillna()`. That is actually a special case of more general value replacement. That is done via the `replace()` method.\n",
"\n",
"Let's consider an example where the dataset given to us had `-999` as sentinel values for missing data instead of `NaN`."
]
},
{
"cell_type": "code",