The idea
A DataFrame is a table that lives in Python's memory. It has rows going down, columns going across, and a label on each row called the index. Harbor & Pine's orders, order_items and customers are all DataFrames, loaded for you before the first level of the pandas chapters.
Figure 1
The parts of a DataFrame: index, columns, values
| index | order_id | status | channel | shipping_fee |
|---|---|---|---|---|
| 0 | 1001 | completed | web | 0.00 |
| 1 | 1002 | completed | web | 0.00 |
| 2 | 1003 | completed | marketplace | 4.99 |
| 3 | 1004 | completed | marketplace | 4.99 |
| index | channel |
|---|---|
| 0 | web |
| 1 | web |
| 2 | marketplace |
| 3 | marketplace |
- This is the start of the
ordersDataFrame. It looks like a table because it is one: rows going down, columns going across. - The first column is not data. It is the index: a label for each row, here the numbers 0, 1, 2, 3. pandas adds it for you and uses it to keep track of rows.
- Each other column has a name and holds one kind of value from top to bottom.
shipping_feeis all numbers,channelis all text. - Pick one column with
orders['channel']and you get a Series: a single column that carries the same index along.
The part to hold on to is how the columns work. A DataFrame is not a grid of independent cells, like a spreadsheet. It is a set of columns, and each column is a typed array: one name, one kind of value, one value per row. Everything you will do in pandas is an operation on whole columns: add two of them, compare one with a number, group by one, sum another.
Why it exists
Plain Python gives you lists. A list of 30,000 prices works, but to double them you write a loop, and a loop that runs through 30,000 items in Python is slow and noisy. To keep a second list of quantities lined up with the prices, you have to remember that position 4,711 in one list means position 4,711 in the other. Nothing enforces it.
A DataFrame fixes both problems. The columns are stored side by side and always have the same length, so row 4,711 is the same row in every column. And the operations work on the whole column at once, in fast compiled code, so a question about 30,000 order lines takes a blink instead of a coffee.
How it works under the hood
Rows, columns, index
orders.shape gives (12000, 7): 12,000 rows and 7 columns. The index is separate from the 7 columns. By default it is the numbers 0 to 11999, and its job is to label rows so pandas can line things up. When you filter or sort, rows keep their original labels, which is why a result can start at row 3,418 and not at 0. reset_index() renumbers them when you want that.
Typed columns
Figure 2
Every column has one dtype
| column | dtype | example |
|---|---|---|
| order_id | int64 | 1001 |
| status | object | completed |
| shipping_fee | float64 | 4.99 |
| order_date | object | 2023-01-01 20:51:19 |
orders.info()ororders.dtypeslists the dtype of each column: the kind of value it holds.order_idisint64, whole numbers.shipping_feeisfloat64, numbers with decimals. Both support arithmetic.statusisobject, which for now means text. You can compare it with'completed'but you cannot add it up.order_dateis alsoobject: the file stores dates as text, so pandas does not treat them as dates yet. A wrong dtype is the usual reason a calculation fails or sorts oddly.
A column holds one dtype, and the dtype decides what you can do. Numbers can be added and averaged. Text can be matched and cut up. Dates can be subtracted. If a number arrives as text ("$1,249.00" in a supplier file, say), no arithmetic works until you convert it. That is why the pandas chapters start with info(): it shows each column's dtype and how many values are not missing. A column like coupon_code has gaps, and pandas shows those as NaN.
Vectorised operations
An operation on a column is applied to every value, row by row, and gives back a column.
Figure 3
One expression on a column, no loop
| quantity | unit_price | discount_pct |
|---|---|---|
| 9 | 31.99 | 0.0 |
| 3 | 11.99 | 0.0 |
| 4 | 11.99 | 0.0 |
| 1 | 149.00 | 0.0 |
| line_total |
|---|
| 287.91 |
| 35.97 |
| 47.96 |
| 149.00 |
- Four order lines from
order_items. We want the money for each line: quantity times price. order_items['quantity'] * order_items['unit_price']multiplies the two columns row by row: the first with the first, the second with the second, and so on.- The answer is a whole new column with one value per row. There was no
for, no counter and no first row handled specially.
Three things follow. The result has the same length and the same index as the inputs, so it lines up with the table it came from. You can store it as a new column: order_items['line_total'] = order_items['quantity'] * order_items['unit_price']. And it is fast, because pandas hands the whole column to compiled code instead of running Python once per row. The same goes for a comparison: orders['shipping_fee'] > 0 gives a column of True and False, one per order. That is the basis of filtering.
A DataFrame, a SQL table and a spreadsheet
All three hold rows and columns, and they differ in how you work with them.
- Spreadsheet. Every cell is its own thing, and a formula is written once per cell. The types are loose: one cell can hold a number and the next one text. You see the data and edit it by hand.
- SQL table. Columns are typed and rows have no order unless you ask for one. The data lives in a database; you send a query and receive a result. A change is a statement such as
UPDATE. - DataFrame. Columns are typed like SQL, but the table sits in your session's memory, and it has an index that keeps row order. You run one operation after another on it, and each step returns a new DataFrame or Series that you can name and keep.
The same job in each: "total shipping fee per channel" is a SUM formula per group in a spreadsheet, a GROUP BY in SQL, and orders.groupby('channel')['shipping_fee'].sum() in pandas. The reading is the same; only the vocabulary changes.
Common misconceptions
"I need a loop to do something to every row"
You almost never do. Loops are the habit from plain Python, and they work on a DataFrame, but they are slow and long. orders['shipping_fee'] * 2 is one line and doubles all 12,000 fees. Reach for a loop only when a row's result truly depends on the one before it. If you catch yourself writing for i in range(len(df)), look for the column operation first.
"The index is just the first column"
The index is not one of the 7 columns of orders. It labels rows and does not count in shape. Because it travels with the data, a filtered result keeps the old labels: the third row of the result may carry the label 2,041. Use .iloc for "the third row" and .loc for "the row labelled 2,041", which chapter 5 covers.
"A DataFrame behaves like a spreadsheet: each cell is separate"
A cell in pandas is never edited in isolation by a formula that sits in it. Operations act on columns, and a column has one dtype. That is why one stray text value such as "n/a" in a price column turns the whole column into object, and then sum() cannot add it. Fix the value or convert the column, and the arithmetic comes back.
Check yourself
One question. Answering it ticks this lesson off on the map; it never locks a level.
Not quite. Multiplying does not collapse a column. Only an aggregation like sum() returns one number; * 2 keeps one value per row.Try another answer.
Right. The operation runs on every value of the column and returns a new Series with the same index. orders itself is untouched.
Not quite. Operations return new results. The original column changes only when you assign the result back, as in orders['shipping_fee'] = ....Try another answer.
Not quite. Looping is exactly what vectorised operations remove. A float column can be multiplied as a whole.Try another answer.
Lesson done: it is ticked on the map.