with open('data/orfs.csv') as f:
for line_number, line in enumerate(f):
print(line, end='')
if line_number == 4:
break60 Dataframes: a table you can plot
Your data finally gets a container that fits it: a table with named columns, that you can question and draw.
You have just come out of the ORF project in Chapter 58 with a program that walks through a bacterial genome and finds every open reading frame in it. Thousands of them. And then what? You can print them, which gives you a wall of text that nobody, including you, will ever read. You can collect them in a list of lists, which you know how to do, and then write a for-loop every single time you want to know something as ordinary as how long the average one is.
There is a better container for this, and it is the last new data type this course hands you. It is called a dataframe, and it is exactly what it sounds like: a table, with rows and columns, where the columns have names. It comes from a library called pandas. The name has nothing to do with the animal, sadly. It is short for panel data.
Nothing you already know stops being true here. Square brackets still look things up. Methods are still things you ask an object to do, in the “Hey table, do this to yourself!” sense from Chapter 20. A method call is still substituted by the value it returns, so you can keep asking questions of what comes back. A dataframe is just one more object with a lot of methods.
This chapter runs. Download the notebook and the table below, put them in the same folder, and run every cell as you read.
The table is just a text file
Before pandas gets its hands on it, let us look at what we actually have. It is a file, and you know how to open a file and read lines out of it from Chapter 31.
That is the whole mystery. The first line is a row of column names, every line after it is one open reading frame, and the fields are separated by commas. A file like this is called a CSV file, for comma-separated values, and it is the most boring and most useful file format in science.
You could turn this into something workable yourself. Read the lines, split(',') each one, throw the first line in a dictionary of column names, convert the numbers with int and float. Every piece of that you have already written at some point in this course. Doing it once teaches you something. Doing it every time you meet a table is a waste of a perfectly good life.
Note
The columns are orf (a name), strand (which of the two DNA strands the reading frame is on), frame (which of the three reading frames), start (where in the genome it begins), length (how many bases long it is), gc_content (the fraction of its bases that are G or C), and stop_codon (which of the three stop codons ended it). These are real numbers from the real E. coli O157:H7 Sakai genome, produced by the same kind of program you wrote in the project.
Handing the file to pandas
import pandas as pdThe as pd part gives the library a shorter name for the rest of the notebook, so you can write pd.read_csv instead of pandas.read_csv. You are allowed to pick any name you like. Do not. Everyone in the world writes pd, and code you read for the rest of your life will assume you did too.
Now the entire file, in one line:
orfs = pd.read_csv('data/orfs.csv')
orfs.head()pd.read_csv opened the file, worked out the column names from the first line, worked out by itself that start and length hold whole numbers while gc_content holds decimal numbers, and handed back one value. That value is now sitting in orfs.
Then we said orfs.head(). In the anthropomorphising voice: “Hey table, show me your head!” The head of a table is its first five rows. We ask for the head rather than the whole thing because the whole thing is seven thousand rows long and your notebook would not thank you.
What you just made is called a dataframe. Let us ask it about itself.
print(type(orfs))
print(orfs.shape)
print(list(orfs.columns))shape is the number of rows and the number of columns, as a tuple, in that order — you met tuples as the type Python reaches for when it wants to hand you two things at once. columns is the column names.
Notice that head() has parentheses and shape does not. That difference matters and it is not arbitrary. head is a method: something the table does when you ask it to, and like every function call it needs parentheses even when there is nothing to put inside them. shape is an attribute: something the table simply is, a value sitting on the object waiting to be read. Asking for orfs.shape() will fail, because a tuple is not something you can call.
Tip
When you get TypeError: 'tuple' object is not callable, you have put parentheses after something that was never a method. Take them off and try again.
Exercise 60-1
Decide what you think each of these gives you before you run it. orfs.head(3). Then orfs.tail(). Then orfs.tail(1). Write your prediction down, then run all three and see whether the machine agrees with you.
Pulling out a column
A table is a lot of things at once. Most questions are about one column.
orfs['length']Square brackets with a name inside them. You have seen this exact syntax before, in Chapter 22, where the thing inside the brackets was a key and what came back was the value stored under it. It is the same idea here: the column name is the key, and the column is what comes back.
Look at what got printed. On the left is the row number, on the right is the length of that ORF, and at the bottom Python tells you two things about the column as a whole: Name: length, because the column remembers what it is called, and dtype: int64, because the column remembers that everything in it is a whole number.
A single column has a name of its own. It is called a Series. A dataframe is a table; a Series is one column of it.
lengths = orfs['length']
print(type(lengths))
lengths.head()Do the substitution in your head, because it is the whole trick to reading pandas code. orfs['length'] is substituted by a Series. Once it is a Series, you can ask that Series to do anything a Series knows how to do — including head(), which is why orfs['length'].head() works without you ever needing a variable in between.
Asking a column a question
print(lengths.mean())
print(lengths.max())
print(lengths.min())So the average open reading frame in this genome is about eight hundred bases long, the longest is a monster of over fourteen thousand, and the shortest is three hundred, because the program that made the table threw away everything shorter than that.
You have written mean before, or near enough. A total, a counter, a for-loop adding things up, a division at the end. mean() is that loop, written once by somebody else, and correct. That is the entire argument for using a library.
The numbers come out with more decimals than anybody wants. round is an ordinary built-in function and it works here like it works anywhere:
print(round(lengths.mean(), 1))Exercise 60-2
The gc_content column holds the fraction of each ORF’s bases that are G or C. The E. coli genome as a whole sits at about 0.51. Predict, before you run anything, whether the mean GC content of the ORFs will come out above or below that, and why coding sequence might differ from the genome average. Then work out the mean, the smallest and the largest, and see whether your reasoning survived contact with the data.
When you want all of that at once, there is a method for it:
orfs['gc_content'].describe()count is how many values there are, mean and std describe the middle and the spread, min and max are the extremes, and the three lines with percentages are the quarter marks: a quarter of the ORFs have a GC content below the 25% figure, half of them are below the 50% figure, and so on. describe() is the first thing to reach for when you have a column you have never looked at.
Counting the categories
Not every column is a number. stop_codon holds one of three short strings, and the question you want to ask of a column like that is not what its average is but how often each value turns up.
You have done this before, by hand, in Section 30.0.0.2: make an empty dictionary, loop over the values, and add one to the count under each key. Here is that loop, again written once by somebody else:
orfs['stop_codon'].value_counts()And here the method earns its keep, because that result is biology. All three stop codons end a protein equally well as far as the ribosome is concerned, and yet TAA shows up here more than five times as often as TAG. That is a real and well-documented bias in E. coli, and you have just measured it in one line from a file on your own laptop.
Exercise 60-3
Ask the assistant which of the two DNA strands it expects most open reading frames in a bacterial genome to be found on, and to explain its reasoning. Write down its answer. Then run value_counts() on the strand column and see what the genome says. If the assistant hedged, decide whether the hedge was honest or evasive. Note the outcome in your logbook.
More than one column at a time
If one column name in the brackets gives you one column, a list of column names gives you several:
orfs[['length', 'gc_content']]Those double brackets are the single most confusing piece of punctuation in pandas, and they stop being confusing the moment you do the substitution properly. The outer brackets are the lookup, exactly as before. The inner brackets are a list, which is what you are handing to the lookup. Reduce the inside first: ['length', 'gc_content'] is a list of two strings, and orfs[ that list ] is a lookup of two columns.
Two columns is still a table, so what comes back is a dataframe, not a Series. And a dataframe has a mean() too:
orfs[['length', 'gc_content']].mean()One mean per column, handed back as a Series with the column names down the side.
NoteFAQ
Q: Can I not just write orfs.mean() and get the mean of everything?
A: Try it. It will complain, because orf, strand and stop_codon hold text, and pandas has no idea how to average a word. Ask for the columns that are numbers and it will do exactly what you asked. This is a well-behaved error message: read it rather than guessing.
Putting the table in order
orfs.sort_values('length', ascending=False).head()Two things are happening on that line and both are worth slowing down for.
ascending=False is a keyword argument, the same kind of thing as the end='' you have been passing to print. Without it you get the smallest first, which is the sensible default.
And then .head() is stuck on the end. This is called chaining, and it needs no new rules to understand — only the substitution habit you already have. orfs.sort_values('length', ascending=False) is a method call, and a method call is substituted by the value it returns, which here is a new table, sorted. Once it is a table, you can ask that table for its head. So the line reads: sort the table, then take the head of the result.
Notice the word new. Sorting did not rearrange orfs. Run orfs.head() again if you do not believe me. Almost everything in pandas works this way: you get a new value back and the original is left alone, which is a mercy, because it means no experiment you run on a table can quietly ruin it.
The top row of that sorted table is a real open reading frame over fourteen thousand bases long. Whether it is a real gene is another question entirely, and a much better one.
Exercise 60-4
Predict what orfs.sort_values('gc_content').head() will show you, in words, before you run it: which end of the GC range, and roughly what numbers. Then run it. Then work out how to see the other end of the same column without writing ascending=False — there is a method you already know that will do it.
Your own function, applied to a column
Everything so far has been you asking the library for things it already knew how to do. Here is the join between the library and you.
Read an ordinary function. Nothing new, nothing pandas-flavoured, just a function that takes one length and returns one word:
def size_class(length):
if length < 500:
return 'short'
if length < 1500:
return 'medium'
return 'long'
print(size_class(300))
print(size_class(1000))
print(size_class(5000))Now hand that function to a column, and the column will run it once for every value in it:
orfs['size'] = orfs['length'].apply(size_class)
orfs.head()There is a new column on the right-hand side of the table. Assigning to a column name that does not exist yet creates it, the same way assigning to a key that does not exist yet creates it in a dictionary.
Look very carefully at what got passed to apply. It is size_class, not size_class(). No parentheses. You are not calling your function and handing over its result; you are handing over the function itself, so that apply can call it seven thousand times on your behalf. A function is a value like any other, and this is the first time in this course that being a value has actually bought you something.
Important
apply(size_class()) is the mistake everyone makes once. It calls your function immediately, with no argument, which raises a TypeError about a missing argument before apply ever gets started. If you see that error, count your parentheses.
Exercise 60-5
Write a function gc_class(fraction) that returns 'low' for a GC content below 0.45, 'high' for one above 0.55, and 'medium' in between. Apply it to the gc_content column, put the result in a new column called gc_class, and count the classes with value_counts().
Then ask the assistant to write the same function for you and compare it to yours. Look in particular at what each version does with the exact values 0.45 and 0.55: do the two functions agree? If they do not, neither of you is necessarily wrong, but one of you has made a decision without noticing. That is the kind of thing that goes in the logbook.
From a table to a picture
Seven thousand rows of numbers do not fit in a human head. A picture of seven thousand rows does.
You met iplot briefly in Exploring a table with iplot. This is where it earns its place. You hand it the whole table and it gives you dropdown menus: what goes on the x-axis, what goes on the y-axis, what splits the colours, what to break into separate panels, and what kind of plot to draw.
from iplot_widget import iplot
iplot(orfs)Try these, in this order, pressing “Show plot” each time:
Set x to length and the plot type to hist. That is the distribution of ORF lengths, and the shape of it is the reason nobody trusts a short ORF: there are enormous numbers of tiny ones, and they are mostly noise, stop codons happening to fall a certain distance apart by chance.
Set x to gc_content and y to length, with the type scatter. Now you are asking whether longer reading frames have a different base composition from short ones. Look at it and decide for yourself what it shows.
Set x to stop_codon and the type to count. That is the value_counts() result you computed above, as a picture.
Set x to size and y to gc_content with the type box, and then set hue to strand. The size column only exists because you wrote size_class yourself a few cells ago, and it is now steering a figure.
Note
Under the plot there is a fold labelled “Show code for plot”. Open it. Inside is the seaborn code that would produce the figure you are looking at, written out in full.
That fold is the point of this whole section. The widget is a piece of software writing code for you. So is the assistant. In both cases the honest response is the same one: read what it wrote, work out what each argument is doing, and decide whether it says what you meant. The difference is that the widget can only ever produce a plot of the table you handed it, so it is a very safe place to practise reading generated code before you have to do it where the stakes are higher.
Exercise 60-6
Pick one of the four views above and get the widget to draw it. Copy the code out of the “Show code for plot” fold and keep it.
Now, in the browser, describe the same figure to the assistant in words — the columns, what is on each axis, what kind of plot — and ask it to write the seaborn code for you. Do not tell it what the widget produced.
Put the two versions side by side. Which arguments do they agree on? Where they differ, run both and look at the two figures. One of them may simply be a different taste in defaults, and one of them may be wrong. Deciding which is which is the skill. Logbook entry: what the assistant produced, how it differed, and how you knew which to keep.
What we have deliberately left out
This has been a small chapter about a very large library, and you should know the shape of what is missing, so you recognise it when you meet it.
You cannot yet pick out rows by a condition — every ORF longer than a thousand bases, say. You cannot yet group the table by one column and compute something per group, which is how you would get the mean length for each stop codon in one go. You cannot yet join two tables together on a shared column, and you have not met what pandas does when a value is missing. Every one of those is a normal thing to want and every one of them has a one-line answer.
What you can do is the part that comes first anyway, and that most people skip: get a real table into memory, find out what is in it, ask each column what it looks like, put your own function to work on it, and draw it. When you need one of the missing pieces, you now have somewhere to put the answer, whether you find it in the documentation or ask for it.
Exercise 60-7
Your logbook entry for this week. In three or four sentences, describe one thing you found in this table that you did not expect, how you found it, and what you would need to check before you believed it. The last part is the one that matters.