This notebook is associated with the following accompanied reading:
By the end of this module you should be able to:
The pandas library is similar to numpy in that it contains a core object type, the DataFrame, and a set of functions for computing over axes of this object. Generally, DataFrames are easier to learn and understand than numpy arrays because they have labels, and so it can be easier to keep track of what you're doing, and because you can combine different datatypes (e.g., ints, floats, strings) together in a single DataFrame, whereas in numpy you usually work with just one dtype at a time.
One way to think about Pandas DataFrames is that they are essentially a pretty wrapper around one or more numpy arrays, which put labels on the rows and columns, and allow you to index using row and column names instead of just indices. But Pandas does more than that as well, and provides many very useful functions for working with data in a table.
If you have used the R programming language before then Pandas may feel familiar, it is similar to the data frame object in R, and is intended for holding data for doing statistics operations. Your reading provides many examples of interesting uses for Pandas. Again, this notebook is primarily meant to test your knowledge after finishing the reading.
Numpy and Pandas are often used together.
import pandas as pd
import numpy as np
You can create a Pandas DataFrame from scratch in a variety of ways. I find that the easiest way is to use a Python dictionary to enter key/value pairs to the pd.DataFrame() function. Remember from our earlier notebook that you can create a dictionary using curly brackets. Here we create a DataFrame by providing it a dictionary where the key is a string (this will become the column name) and the value is a numpy array of randomly generated values (this will become the data). Each key/pair in the dictionary is written on a separate line to make it easier to read, but they are all surrounded by a single set of curly brackets, and so interpreted as a single dictionary. It is important that all of the data we enter is of the same length, since this will become the column data of the table. But, as you can see, it is OK that the data in each column is a different datatype, here we combine floats, strings, and ints. Pandas is also nice because it returns the table data in the output cell as a nicely formatted HTML table. Scroll your cursor over the table and you can see that cells light up as you highlight them. It's nice.
# create an array with randomly generated values
df = pd.DataFrame({
"column1": np.random.normal(0, 1, 10),
"column2": np.random.choice(list("ACGT"), 10),
"column3": np.random.randint(0, 5, 10),
})
# return the dataframe
df
You can index DataFrames by their column or row names (row names are called the index in pandas), or by using numeric indices like in numpy. The four options below accomplish the same thing, they select all data in column1.
# select columns by name in a dictionary-like way
df["column1"]
# select column by name in a simple object-like way
df.column1
# select column by name using fancy indexing
df.loc[:, "column1"]
# select column by index using fancy indexing
df.iloc[:, 0]
The first dictionary-like way is nice since if you're familiar with dictionary objects then you can substitute dataframes in for these objects easily.
The second object-like mode is fast to type and can be selected using tab-completion, so it's easy to view all the column choices in an object without having to print it; however in this mode you cannot select column names with odd characters in them like spaces since the column name is not wrapped in quotes. This syntax is not ideal.
The third way of indexing, with .loc[] can combine selecting by both row and column names into a single call, so this very powerful and is generally the preferred method.
The final way of indexing with .iloc[] is a nice alternative, you just need to keep track that you are indexing the col or row that you intended by knowing their index.
Pandas has several functions for loading data from a variety of formats, but the most commonly used ones are .read_csv() or read_table(). You can provide a number of arguments to each function to tell it details of the file, such as what the delimiter is (e.g., tabs, spaces, commas), whether to skip the first N rows, whether to use the first row and column names, etc. You can use this function to load data files from a file path location on your hard disk, or, if you're connected to the internet, you can even just provide it a url of the location of a datafile that is publicly hosted online.
We will practice by loading a GFF file, which we have seen before. We can load the yeast genome GFF file directly from the NCBI FTP site where it is hosted. For a quick reminder of the GFF file format visit here. We provide the argument comment="#" below so it will skip the first few lines of the file that are not data but just comments, and we will enter in the column names by hand because it is easier than trying to parse them from the file in this case.
The DataFrame object is saved as the variable gff, which we then show the first few lines of with the function of the dataframe object called .head(). This should sound familiar. Remember when we were learning bash scripting there was a function called head to print the first few lines of a file. Pandas is implementing that same idea as a function of dataframes. We want to just peek at the first few lines to make sure the data were loaded/parsed correctly.
# url to the GFF file on the NCBI FTP site
url = "https://ftp.ncbi.nlm.nih.gov/genomes/refseq/fungi/Saccharomyces_cerevisiae/reference/GCF_000146045.2_R64/GCF_000146045.2_R64_genomic.gff.gz"
# read csv function with arguments to parse the file
gff = pd.read_csv(
url,
sep="\t",
header=None,
comment="#",
names=("seqname", "source", "feature", "start",
"end", "score", "strand", "frame", "attribute"),
)
# return the dataframe
gff.head()
Let's repeat the exercise we implemented in our first session of trying to select rows and columns from a table of data that fit some filtering criterion that we're interested in. Here, we will try to select all of the features in the GFF that are telomeres so we can extract their start and stop positions. This is much easier and nicer looking in Pandas than it is by using bash scripting.
Here we will also introduce the concept of a boolean mask. A convenient way to filter or subselect part of a data set is to use a mask. You can create a boolean array to act as a mask for an entire column or row by asking a question with a True or False answer and broadcasting it across the row or column. For example, below we ask whether the feature is a telomere, which returns a True or False for every element in the 'feature' column.
# create a boolean mask of the feature column asking 'is it a telomere?'
mask = gff.feature == "telomere"
# show the first 10 values
mask.head(10)
We can now apply the mask as an index or with .loc or .iloc to the gff dataframe and it will select all rows that have True, and not the ones that have False. The index (row name) on the right is retained from the entire dataframe, thus you can see that by selecting only telomeres we get element 1, 432, 434, 2257, 2259, etc.
# use this mask to select only rows that are telomeres
telomeres = gff[mask]
# return the first 10 value
telomeres.head()
# or, we could have written the above code more concisely as a single line:
gff.loc[gff.feature == "telomere"].head()
gff.loc[gff.feature == "gene"].shape[0]
As before, much of the data of the GFF file is in the final column called attributes. We can parse this information as with any type of string, by splitting on the delimiter in the text which is a semicolon. Let's try extracting the first ten genes and see what kind of information is in their attributes.
In the code cells below I am just returning the filtered dataframe rather than saving it to variable so you can easily see the result of the operation. As you can see it is easy to explore the effect of our operations by filtering in various ways and calling the .head() function to show a small part of the result.
# select only gene rows
gff[gff.feature == "gene"].head(5)
# select attributes of those rows
gff[gff.feature == "gene"]["attribute"].head(5)
DataFrames are iterable, as are their columns or rows. Below we iterate over the elements in the 'attribute' column of the rows where feature=='gene'. The iteration is done using a list-comprehension. We can do something which each selected element as it is created during the for-loop that is happening within the list-comprehension. Here, because the selected elements are strings, we perform a string operation, .split(), to split the string every time a semi-colon appears. The result is a list of lists of the items in the string that were split.
That sounds pretty complicated right? We operated on a dataframe column full of strings and got in return a list of lists of strings! This is why it is important to understand the object types in Python, since it allows you to understand how and why this was possible.
# split the string objects of those attribute rows
[i.split(";") for i in gff[gff.feature == "gene"]["attribute"].head(5)]
The example above selected the 'attribute' element for a subset of rows and then parsed it into a list. Now we can go further by selecting elements out of those lists of results. For example, we can select the gene names of the rows where feature=='gene'.
# save 'attributes' as a list for the first ten genes, like we did above.
attrs_lists = [i.split(";") for i in gff[gff.feature == "gene"].attribute.head(10)]
# iterate over each list in the list of lists
for attrs_list in attrs_lists:
# iterate over each string in the list
for attr in attrs_list:
# if the first four string elements spell Name
if attr[:4] == "Name":
print(attr)
# save 'attributes' as a list for the first ten genes, like we did above.
attrs_lists = [i.split(";") for i in gff[gff.feature == "gene"].attribute.head(20)]
# iterate over each list in the list of lists
for attrs_list in attrs_lists:
# iterate over each string in the list
for attr in attrs_list:
# if the first four string elements spell Name
if "gene_biotype" in attr:
print(attr)