Pandas DataFrames: Tables in Python
A spreadsheet you can ask questions
In this lesson
NumPy handles columns of numbers. Real data is a whole table of names, dates, categories and numbers together, and that is what pandas is for.
This is the library you will use most. If you take one thing from this unit, take the DataFrame.
Explain it like I’m 5
A DataFrame is a spreadsheet that Python can ask questions about — and unlike a spreadsheet, the questions are written down and repeatable.
The whole table as one object
A DataFrame holds the entire table: columns with names, rows with positions, and different types in different columns. Building one from a dictionary is the quickest way to start: each key becomes a column.
Printing it shows the table, and the leading numbers are the index: pandas' label for each row, starting at 0 unless you give it something better.
import pandas as pd
grades = pd.DataFrame({
"student": ["Ada", "Sam", "Rae", "Kit", "Joe"],
"subject": ["math", "math", "art", "art", "math"],
"score": [91, 78, 84, 66, 95],
})
print(grades)
print()
print("shape:", grades.shape)
student subject score 0 Ada math 91 1 Sam math 78 2 Rae art 84 3 Kit art 66 4 Joe math 95 shape: (5, 3)
Reading a real file, and looking before you leap
In practice the data comes from a file. pd.read_csv() handles the CSV parsing you did by hand in Unit 3, working out the column names from the header row.
And then .head(), which shows the first few rows. This is the habit to build: look at the table before calculating anything. It takes one line and catches wrong delimiters, missing headers, and shifted columns immediately.
import io
import pandas as pd
# Normally this is a filename: pd.read_csv("grades.csv")
# Here the CSV is written inline so the example runs anywhere.
CSV = """student,subject,score
Ada,math,91
Sam,math,78
Rae,art,84
Kit,art,66
Joe,math,95
"""
grades = pd.read_csv(io.StringIO(CSV))
print(grades.head(3))
student subject score 0 Ada math 91 1 Sam math 78 2 Rae art 84
Selecting columns and filtering rows
Two operations do most of the work. Selecting picks columns; filtering picks rows.
One column comes back as a Series, a single labeled column, which is essentially a NumPy array that remembers its index. Several columns come back as another DataFrame, and that needs double brackets, which is one of the two things everyone gets wrong at first.
Filtering uses the boolean indexing you met in NumPy: write a condition, and pandas keeps the rows where it was true.
import pandas as pd
grades = pd.DataFrame({
"student": ["Ada", "Sam", "Rae", "Kit", "Joe"],
"subject": ["math", "math", "art", "art", "math"],
"score": [91, 78, 84, 66, 95],
})
# One column is a Series.
print(type(grades["score"]).__name__)
print(grades["score"].mean())
print()
# Two or more columns is a DataFrame - note the double brackets.
print(grades[["student", "score"]].head(2))
print()
# Filtering: a condition gives True/False, and that picks the rows.
print(grades[grades["score"] >= 85])
Series 82.8 student score 0 Ada 91 1 Sam 78 student subject score 0 Ada math 91 4 Joe math 95
Summarizing by group
The question is rarely “what is the average score?” It is usually “what is the average score per subject?” That is groupby, and it is the point at which pandas starts saving you real work.
Read it as a sentence: group by subject, take the score column, give me the mean of each group.
import pandas as pd
grades = pd.DataFrame({
"student": ["Ada", "Sam", "Rae", "Kit", "Joe"],
"subject": ["math", "math", "art", "art", "math"],
"score": [91, 78, 84, 66, 95],
})
print(grades.groupby("subject")["score"].mean())
print()
print(grades["subject"].value_counts())
subject art 75.0 math 88.0 Name: score, dtype: float64 subject math 3 art 2 Name: count, dtype: int64
Find the students who scored above 80. Filter the table with a condition, then report how many there were and their average score rounded to one decimal place.
import pandas as pd
grades = pd.DataFrame({
"student": ["Ada", "Sam", "Rae", "Kit", "Joe"],
"subject": ["math", "math", "art", "art", "math"],
"score": [91, 78, 84, 66, 95],
})
# TODO: keep only the rows where score is above 80
passed = grades
print(passed)
print("how many:", len(passed))
print("their average:", round(passed["score"].mean(), 1))Write the condition first: grades["score"] > 80. Then put it inside the brackets: grades[grades["score"] > 80]. The index in the output will be 0, 2, 4, the original row numbers, not renumbered.
import pandas as pd
grades = pd.DataFrame({
"student": ["Ada", "Sam", "Rae", "Kit", "Joe"],
"subject": ["math", "math", "art", "art", "math"],
"score": [91, 78, 84, 66, 95],
})
passed = grades[grades["score"] > 80]
print(passed)
print("how many:", len(passed))
print("their average:", round(passed["score"].mean(), 1))Common mistake: Forgetting the second pair of brackets for several columns
One column needs single brackets, so two columns look like they should too.
Selecting several columns means passing a list: df[["a", "b"]]. The inner brackets are the list.
Common mistake: Confusing a Series with a DataFrame
They print similarly and share many method names.
One column is a Series; two or more is a DataFrame. Check with type(x).__name__ when a method is unexpectedly missing.
Common mistake: Using = instead of == inside a filter
The condition reads like plain English, where “is” is one word.
== compares, = assigns. df[df["subject"] == "art"].
Common mistake: Chaining conditions with and / or
Those are the Python keywords for combining truth values everywhere else.
Use & and |, and bracket each condition: df[(df["a"] > 1) & (df["b"] < 5)].
What does head() show?
It is the quickest way to check a table loaded the way you expected.
How do you select a single column called score?
Single brackets with the name as a string. The result is a Series.
What does df[df["score"] > 80] do?
The condition produces True/False per row, and the outer brackets keep the True ones.
Mini exercise (medium)
A small table of books with title, year, and rating. Show the title and year of every book published in 1980 or later, find the two highest-rated books by name, and print the average rating across all five rounded to two decimals.
Now you. Edit the starter code below, then Run it, everything happens in the browser.
import pandas as pd
books = pd.DataFrame({
"title": ["Dune", "Neuromancer", "Snow Crash", "Ubik", "Solaris"],
"year": [1965, 1984, 1992, 1969, 1961],
"rating": [4.6, 4.1, 4.3, 3.9, 4.4],
})
modern = books # TODO: only books from 1980 onwards
best = books # TODO: the two highest-rated books
print(modern[["title", "year"]])
print()
print("top two by rating:", list(best["title"]))
print("average rating:", round(books["rating"].mean(), 2))Filter with books[books["year"] >= 1980] then select two columns with double brackets. For the top two, books.sort_values("rating", ascending=False).head(2), and pull the names with list(best["title"]).
import pandas as pd
books = pd.DataFrame({
"title": ["Dune", "Neuromancer", "Snow Crash", "Ubik", "Solaris"],
"year": [1965, 1984, 1992, 1969, 1961],
"rating": [4.6, 4.1, 4.3, 3.9, 4.4],
})
modern = books[books["year"] >= 1980]
best = books.sort_values("rating", ascending=False).head(2)
print(modern[["title", "year"]])
print()
print("top two by rating:", list(best["title"]))
print("average rating:", round(books["rating"].mean(), 2))