SyntaxPilot
Courses/Data Analytics with Python/Lesson 12
Video coming soonThis lesson’s video is being made

Start with the notes below; the video appears here as soon as it is on YouTube.

Data Analytics with Python · Lesson 12 of 90

pandas 2: the DataFrame

DataFramerows and columnscreating from dict and list of dictsheadtailsampleshapeinfodescribedtypescolumnsindexset_indexreset_index

These are the notes for Data Analytics with Python, episode 12. In episode 11 we met the pandas Series: one column of values with labels called the index. Here we put many Series side by side and get the DataFrame, the table at the heart of all data analytics. We build DataFrames three ways (dict of lists, list of dicts, list of lists), take a first look at any dataset (head, tail, sample, shape, info, dtypes, columns, index, describe), learn why the object dtype is an alarm bell, and choose meaningful row labels with set_index / reset_index. Every term is defined the first time it appears and collected in the Glossary. Every program below was run for real (Python 3.13, pandas 3.0.5); outputs are unedited.

The problem: four facts a day, four separate lists

Every evening Ramesh, who runs a chai stall, writes one line in his notebook: the day, how many cups he sold, the price per cup, and the weather. Four facts about each day.

The obvious way in plain Python is one list per fact. Position 0 in every list is Monday, position 1 is Tuesday, and so on. Then he finds a wrong entry and deletes it from one list only:

days = ["Mon", "Tue", "Wed", "Thu"]
cups = [320, 280, 410, 300]
weather = ["sunny", "rain", "cold", "sunny"]
cups.remove(280)
for d, c, w in zip(days, cups, weather):
    print(d, c, w)
Mon 320 sunny
Tue 410 rain
Wed 300 cold

No error, but the answer is wrong. Tuesday now shows Wednesday's 410 cups, Wednesday shows Thursday's 300, and Thursday has disappeared (zip, from episode 6, stops at the shortest list). The lists fell out of step: nothing ties one day's facts together except their positions, and one delete shifted every position after it. A program that runs fine and gives a wrong answer is the worst kind of bug.

Separate Series are better, but still loose

A Series (episode 11) gives each value an index label, and arithmetic between Series matches labels, not positions (index alignment). drop(1) removes the value with label 1:

import pandas as pd
cups = pd.Series([320, 280, 410, 300])
price = pd.Series([10, 10, 12, 10])
weather = pd.Series(["sunny", "rain", "cold", "sunny"])
cups = cups.drop(1)
print(cups * price)
0    3200.0
1       NaN
2    4920.0
3    3000.0
dtype: float64

Label 1 has no cups any more, so the result there is NaN ("Not a Number"), pandas' mark for a missing value, instead of a wrong pairing. Safer, but we still have four loose variables to keep in sync. We want one object where each day's facts live together.

The DataFrame: rows, columns and an index

Real-life example: a school register.

School register pandas DataFrame
One line per student, with all that student's facts A row (also called a record or observation): all the facts about one thing, here one day
One column per subject, the same kind of value going down A column (also called a variable or feature): each column is a Series
Roll numbers in the margin The index: the labels of the rows

A DataFrame is a two-dimensional table made of rows, columns and an index. Every column is a Series, and all columns share the same index. Delete a row and the whole line goes, so the facts can never fall out of step.

Creating a DataFrame from a dict of lists

pd.DataFrame(...) builds a DataFrame. Give it a dict (episode 5): each key becomes a column name, and each list becomes that column's values.

import pandas as pd
sales = pd.DataFrame({
    "day": ["Mon", "Tue", "Wed", "Thu"],
    "cups": [320, 280, 410, 300],
    "price": [10, 10, 12, 10],
    "weather": ["sunny", "rain", "cold", "sunny"],
})
print(sales)
print(type(sales))
print(sales["cups"])
   day  cups  price weather
0  Mon   320     10   sunny
1  Tue   280     10    rain
2  Wed   410     12    cold
3  Thu   300     10   sunny
<class 'pandas.DataFrame'>
0    320
1    280
2    410
3    300
Name: cups, dtype: int64
  • The names along the top are the columns; the 0 1 2 3 down the left is the index pandas created automatically.
  • sales["cups"] (square brackets with a column name) returns one column, as a Series: the same index, Name: cups, dtype: int64. A DataFrame really is Series side by side. (Selecting several columns, loc and iloc get their own episode: episode 14.)

Mistake: columns of different lengths

import pandas as pd
sales = pd.DataFrame({
    "day": ["Mon", "Tue", "Wed"],
    "cups": [320, 280, 410, 300],
})
ValueError: All arrays must be of the same length

Every column needs exactly one value per row. This error is pandas protecting you from the out-of-step bug above.

Mistake: wrong column name

import pandas as pd
sales = pd.DataFrame({"day": ["Mon", "Tue"],
                      "cups": [320, 280]})
print(sales["Cups"])
KeyError: 'Cups'

A key is the name you look something up by. Column names are case-sensitive: cups exists, Cups does not. Print df.columns to see the exact names.

Creating a DataFrame from a list of dicts

A dict of lists is column by column. Apps, websites and APIs usually send data row by row: one small dict per record (like one order slip per day). A list of such dicts is a list of dicts, and pd.DataFrame turns it into a table directly. Real records have gaps: here Tuesday has no price key.

import pandas as pd
orders = [
    {"day": "Mon", "cups": 320, "price": 10},
    {"day": "Tue", "cups": 280},
    {"day": "Wed", "cups": 410, "price": 12},
]
df = pd.DataFrame(orders)
print(df)
print(df.dtypes)
   day  cups  price
0  Mon   320   10.0
1  Tue   280    NaN
2  Wed   410   12.0
day          str
cups       int64
price    float64
dtype: object
  • Each dict became a row; each key became a column (pandas lines the dicts up by key, not by position).
  • The missing key became NaN. No error, no wrong pairing.
  • Surprise: price became 10.0, 12.0 and its dtype is float64. NaN is a special decimal (floating-point) value, so a whole-number column with a gap turns into decimals.

A third way: list of lists plus columns=

import pandas as pd
rows = [["Mon", 320], ["Tue", 280]]
df = pd.DataFrame(rows, columns=["day", "cups"])
print(df)
   day  cups
0  Mon   320
1  Tue   280

Each inner list is a row; columns= names the columns in order.

Input Thinks in Missing values Use when
dict of lists columns not allowed (lengths must match) you have each column ready
list of dicts rows (records) a missing key becomes NaN data arrives record by record (APIs, JSON)
list of lists + columns= rows must be written explicitly rows without names, e.g. from a CSV reader

First look 1: head, tail, sample, shape

Real-life example: previewing a book in a shop. You read the first page (head), peek at the last page (tail), and open a random page in the middle (sample). Printing ten thousand rows would flood the screen and tell you nothing.

A method is a function that belongs to an object, called with a dot and round brackets: week.head(). An attribute is a value stored on the object, read with a dot and no brackets: week.shape.

All examples below start from this full week:

import pandas as pd
week = pd.DataFrame({
    "day": ["Mon", "Tue", "Wed", "Thu",
            "Fri", "Sat", "Sun"],
    "cups": [320, 280, 410, 300, 350, 390, 450],
    "price": [10, 10, 12, 10, 10, 12, 12],
    "weather": ["sunny", "rain", "cold", "sunny",
                "sunny", "cold", "rain"],
})
print(week.head())
print(week.head(2))
   day  cups  price weather
0  Mon   320     10   sunny
1  Tue   280     10    rain
2  Wed   410     12    cold
3  Thu   300     10   sunny
4  Fri   350     10   sunny
   day  cups  price weather
0  Mon   320     10   sunny
1  Tue   280     10    rain

head() shows the first 5 rows by default; head(n) shows the first n.

print(week.tail(2))
print(week.sample(2, random_state=1))
print(week.shape)
   day  cups  price weather
5  Sat   390     12    cold
6  Sun   450     12    rain
   day  cups  price weather
6  Sun   450     12    rain
2  Wed   410     12    cold
(7, 4)
  • tail(n): the last n rows (default 5). Rows keep their index labels (5 and 6).
  • sample(n): n random rows. random_state=1 is a seed (episode 10): a starting number for the random generator so the "random" pick is the same every run, which makes your results reproducible (repeatable by anyone).
  • shape: (rows, columns), always rows first. len(week) gives just the number of rows (7).

First look 2: info()

The first-look checklist every analyst runs on new data: look at rows (head/tail/sample) → shape → info() → describe().

week.info()
<class 'pandas.DataFrame'>
RangeIndex: 7 entries, 0 to 6
Data columns (total 4 columns):
 #   Column   Non-Null Count  Dtype
---  ------   --------------  -----
 0   day      7 non-null      str  
 1   cups     7 non-null      int64
 2   price    7 non-null      int64
 3   weather  7 non-null      str  
dtypes: int64(2), str(2)
memory usage: 356.0 bytes
  • info() prints its own report, so you don't wrap it in print.
  • RangeIndex: 7 entries, 0 to 6: the index is the automatic numbers 0 to 6.
  • Non-Null Count: null means missing, so non-null means filled in. 7 non-null out of 7 entries = nothing missing. If a column said 5 non-null, two values would be missing. This is how you spot gaps in big data without scrolling.
  • Dtype: the data type of each column. int64 = whole numbers, float64 = decimal numbers, str = text.
  • memory usage: how much computer memory (RAM) the table uses.

First look 3: dtypes, columns, index

print(week.dtypes)
print(week.columns)
print(week.index)
day          str
cups       int64
price      int64
weather      str
dtype: object
Index(['day', 'cups', 'price', 'weather'], dtype='str')
RangeIndex(start=0, stop=7, step=1)
  • dtypes: each column's type, as a small Series (its own last line dtype: object just describes that little Series).
  • columns: the column names, as an Index object (pandas' container for labels).
  • index: the row labels. A RangeIndex is the default index 0, 1, 2, ... stored compactly as start, stop and step instead of listing every number.

First look 4: describe()

print(week.describe())
             cups      price
count    7.000000   7.000000
mean   357.142857  10.857143
std     62.105900   1.069045
min    280.000000  10.000000
25%    310.000000  10.000000
50%    350.000000  10.000000
75%    400.000000  12.000000
max    450.000000  12.000000
Row Meaning Here (cups)
count how many values are filled in (not missing) 7
mean the average about 357
std standard deviation (episode 9): how far values typically sit from the mean about 62
min / max smallest / largest 280 / 450
25% the 25th percentile: a quarter of the values are at or below it 310
50% the median: the middle value when sorted 350
75% the 75th percentile 400

A percentile is the value below which that percent of the data falls. By default describe() summarises number columns only; text columns are skipped. With include="all" it also describes text columns (unique = number of distinct values, top = the most common value, freq = how often it appears):

print(week.describe(include="all"))
        day        cups      price weather
count     7    7.000000   7.000000       7
unique    7         NaN        NaN       3
top     Mon         NaN        NaN   sunny
freq      1         NaN        NaN       3
mean    NaN  357.142857  10.857143     NaN
std     NaN   62.105900   1.069045     NaN
min     NaN  280.000000  10.000000     NaN
25%     NaN  310.000000  10.000000     NaN
50%     NaN  350.000000  10.000000     NaN
75%     NaN  400.000000  12.000000     NaN
max     NaN  450.000000  12.000000     NaN

Reading it like an analyst: Ramesh sells about 357 cups a day, give or take 62; his worst day was 280 and his best 450; half the days sold 350 or fewer; the price is 10 on most days and 12 on a few.

object dtype vs str dtype

Real-life example: a tray holding only cups is quick to count (a number column: int64/float64); a box of only name slips is all words (a text column: str); a bag of anything (cups, slips and coins mixed) is the object dtype.

  • In pandas 3 (the version used here), text columns get the dedicated str dtype, as the outputs above show. Older pandas versions showed text columns as object, so many tutorials and old code still say object for text. (Source: the pandas 3.0.0 release notes, 21 January 2026: "Starting with pandas 3.0, a dedicated string data type is enabled by default".)
  • object means the column holds general Python objects: possibly a mix of numbers, text and anything else. In a column that should be numbers, object is an alarm bell: something non-numeric is hiding inside.
import pandas as pd
df = pd.DataFrame({
    "day": ["Mon", "Tue", "Wed"],
    "cups": [320, "n/a", 410],
})
print(df.dtypes)
print(df["cups"].sum())
day        str
cups    object
dtype: object
TypeError: unsupported operand type(s) for +: 'int' and 'str'

Someone typed "n/a" into the cups column, so it became object, and adding a number to a word fails. Always check dtypes (or info()) before doing maths. Converting such columns (to_numeric, astype) is covered in the cleaning episodes (episode 20).

set_index and reset_index

Real-life example: phone contacts. "Contact #3" tells you nothing; "Amma" does. The default index 0, 1, 2 is like numbered contacts. set_index("day") takes the day column and makes it the index, so rows are labelled Mon, Tue, Wed. reset_index() does the reverse: the index goes back to being an ordinary column and the numbers 0, 1, 2 return.

import pandas as pd
week = pd.DataFrame({
    "day": ["Mon", "Tue", "Wed", "Thu"],
    "cups": [320, 280, 410, 300],
    "weather": ["sunny", "rain", "cold", "sunny"],
})
byday = week.set_index("day")
print(byday)
print(byday.index)
print(byday.loc["Wed"])
print(byday.reset_index())
     cups weather
day              
Mon   320   sunny
Tue   280    rain
Wed   410    cold
Thu   300   sunny
Index(['Mon', 'Tue', 'Wed', 'Thu'], dtype='str', name='day')
cups        410
weather    cold
Name: Wed, dtype: object
   day  cups weather
0  Mon   320   sunny
1  Tue   280    rain
2  Wed   410    cold
3  Thu   300   sunny
  • After set_index, day is no longer a column; it is the index (the extra header line shows the index's name, day).
  • byday.loc["Wed"]: loc (short for locate) finds a row by its index label. The row comes back as a Series. Full details in episode 14.
  • reset_index() gives back a 0, 1, 2, 3 index with day as a column again.

Mistake: not saving the result

import pandas as pd
week = pd.DataFrame({
    "day": ["Mon", "Tue", "Wed", "Thu"],
    "cups": [320, 280, 410, 300],
})
week.set_index("day")
print(week.loc["Wed"])
KeyError: 'Wed'

set_index does not change week; it returns a new DataFrame, which we threw away. Most pandas methods work this way. Fix: week = week.set_index("day").

Mini project: a first-look report on snack orders

Five order slips as a list of dicts. Order 103 has no amount.

import pandas as pd
orders = [
    {"id": 101, "item": "chai", "qty": 2, "amt": 20},
    {"id": 102, "item": "samosa", "qty": 3, "amt": 45},
    {"id": 103, "item": "chai", "qty": 1},
    {"id": 104, "item": "bun", "qty": 2, "amt": 30},
    {"id": 105, "item": "chai", "qty": 4, "amt": 40},
]
df = pd.DataFrame(orders).set_index("id")
print(df.shape)
print(df.head(3))
print(df.dtypes)
(5, 3)
       item  qty   amt
id                    
101    chai    2  20.0
102  samosa    3  45.0
103    chai    1   NaN
item        str
qty       int64
amt     float64
dtype: object

Second look:

df = pd.DataFrame(orders).set_index("id")
print(df.describe())
print(df["item"].value_counts())
            qty        amt
count  5.000000   4.000000
mean   2.400000  33.750000
std    1.140175  11.086779
min    1.000000  20.000000
25%    2.000000  27.500000
50%    2.000000  35.000000
75%    3.000000  41.250000
max    4.000000  45.000000
item
chai      3
samosa    1
bun       1
Name: count, dtype: int64

Findings: 5 orders and 3 columns (id became the index); count is 5 for qty but 4 for amt, so one amount is missing (found without scrolling); amt is float64 because of that gap; the average order is ₹33.75; chai is the best seller (3 of 5 orders, via value_counts from episode 11).

Common mistakes

Code Real error Why Fix
Deleting from one of several lists, then zip (no error: wrong pairings) Lists are tied only by position Keep the data in one DataFrame
pd.DataFrame({"day": [3 items], "cups": [4 items]}) ValueError: All arrays must be of the same length Every column needs one value per row Make all lists the same length
sales["Cups"] KeyError: 'Cups' Column names are case-sensitive sales["cups"]; check df.columns
df["cups"].sum() with "n/a" inside TypeError: unsupported operand type(s) for +: 'int' and 'str' The column is object: numbers and text mixed Check dtypes; clean the column first
week.set_index("day") then week.loc["Wed"] KeyError: 'Wed' set_index returns a new DataFrame week = week.set_index("day")
Expecting ints after a missing key (no error: 10.0, float64) NaN is a float, so the column becomes float Expected; handle missing values (episode 19)
week.shape() TypeError: 'tuple' object is not callable shape is an attribute, not a method week.shape

Practice

1. Build a DataFrame of three fruits with columns name, kg and price (apple 2 kg ₹180, banana 6 kg ₹40, mango 3 kg ₹120). Print it, its shape, and the highest price.

Solution
import pandas as pd
fruit = pd.DataFrame({
    "name": ["apple", "banana", "mango"],
    "kg": [2, 6, 3],
    "price": [180, 40, 120],
})
print(fruit)
print(fruit.shape)
print(fruit["price"].max())
     name  kg  price
0   apple   2    180
1  banana   6     40
2   mango   3    120
(3, 3)
180

2. A column of step counts has one missing day: [5000, None, 8000]. What dtype will it have, and what will info() report?

Solution
import pandas as pd
df = pd.DataFrame({"steps": [5000, None, 8000]})
print(df.dtypes)
df.info()
steps    float64
dtype: object
<class 'pandas.DataFrame'>
RangeIndex: 3 entries, 0 to 2
Data columns (total 1 columns):
 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   steps   2 non-null      float64
dtypes: float64(1)
memory usage: 156.0 bytes

None becomes NaN, so the column is float64, and info() shows 2 non-null out of 3 entries.

3. After byday = week.set_index("day") on a 3-row table with columns day and cups, what are byday.shape and byday.reset_index().shape? How do you get Tuesday's cups?

Solution
import pandas as pd
week = pd.DataFrame({
    "day": ["Mon", "Tue", "Wed"],
    "cups": [320, 280, 410],
})
byday = week.set_index("day")
print(byday.shape)
print(byday.reset_index().shape)
print(byday.loc["Tue", "cups"])
(3, 1)
(3, 2)
280

The index is not counted as a column, so byday has 1 column; reset_index turns day back into a column (2 columns).

4. What dtypes does this table have, and what does describe() show when there are no number columns?

Solution
import pandas as pd
df = pd.DataFrame({"code": ["A1", "B2"], "mixed": [1, "two"]})
print(df.dtypes)
print(df.describe())
code        str
mixed    object
dtype: object
       code  mixed
count     2      2
unique    2      2
top      A1      1
freq      1      1

All-text code is str; mixed holds a number and a word, so it is object. With no number columns, describe() falls back to count, unique, top and freq.

Quick quiz

df = pd.DataFrame([{"a": 1, "b": 2},
                   {"a": 3}])
print(df.shape)
print(df)

A. (2, 2), and b is 2.0 then NaN B. (2, 1), column b is dropped C. ValueError: lengths differ D. (2, 2), and b is 2 then 0

Answer

A.

(2, 2)
   a    b
0  1  2.0
1  3  NaN

Two dicts = two rows; keys a and b = two columns. The missing b becomes NaN (not 0, not an error), and because of that gap b becomes float64.

Interview questions

  1. What is a DataFrame? A 2-D labelled table in pandas: rows (records), columns (each a Series), and an index of row labels shared by all columns.
  2. How is a DataFrame related to a Series? Each column is a Series; df["col"] returns one. A DataFrame is a collection of Series that share one index.
  3. Ways to create a DataFrame? From a dict of lists (column-wise), a list of dicts (row-wise, missing keys become NaN), a list of lists with columns=, a NumPy 2-D array, or by reading a file (read_csv, next episode).
  4. Why did an integer column become float64? It has a missing value: NaN is a floating-point value, so the column is upcast to float. (Nullable integer types such as Int64 avoid this; episode 20.)
  5. What does info() tell you that describe() doesn't? Every column's dtype, the non-null count per column (so missing values), the index type and memory usage. describe() gives summary statistics.
  6. What does describe() return by default? count, mean, std, min, 25%, 50% (median), 75%, max for numeric columns only; include="all" adds unique, top and freq for text columns.
  7. What does the object dtype mean, and why is it a warning sign? The column holds arbitrary Python objects, often mixed types; a numeric column showing object usually contains stray text like "n/a". In pandas 3 pure text columns use the str dtype instead.
  8. Does df.set_index("col") change df? No, it returns a new DataFrame; assign it back (df = df.set_index("col")).
  9. head() vs sample()? head() shows the first rows (which may all be similar, e.g. sorted by date); sample() shows random rows from anywhere; use random_state to make it reproducible.

Glossary

Term Meaning Example
pandas Python's main library for working with tables of data import pandas as pd
Series One labelled column of values (episode 11) pd.Series([320, 280])
DataFrame A 2-D table of rows, columns and an index pd.DataFrame({...})
Row / record / observation One line of the table: all facts about one thing Monday's sales
Column / variable / feature One named field going down; each is a Series cups
Index The labels of the rows 0, 1, 2 or Mon, Tue
Index alignment Matching values by label, not position, in operations cups * price
NaN "Not a Number": pandas' mark for a missing value the gap after a missing key
Missing value / null A value that isn't there Tuesday's price
Non-null Filled in, not missing 7 non-null
Out of step Separate lists whose positions no longer match cups.remove(280)
Dict of lists A dict whose values are lists: one list per column {"day": [...], "cups": [...]}
List of dicts A list with one dict per row (record) [{"day": "Mon", "cups": 320}, ...]
List of lists One inner list per row, names given with columns= [["Mon", 320], ["Tue", 280]]
Key The name used to look something up "cups" in df["cups"]
KeyError Error when a key (name or label) doesn't exist KeyError: 'Cups'
Case-sensitive Capital and small letters count as different Cups ≠ cups
Method A function that belongs to an object, called with () week.head()
Attribute A value stored on an object, no () week.shape
head First rows (default 5) week.head(2)
tail Last rows (default 5) week.tail(2)
sample Random rows week.sample(2, random_state=1)
Seed / random_state A starting number that makes random picks repeatable random_state=1
Reproducible Gives the same result every time anyone runs it a seeded sample
shape (rows, columns) (7, 4)
info Report of index, columns, non-null counts, dtypes, memory week.info()
dtype (data type) The kind of values a column holds int64, float64, str
dtypes Each column's dtype week.dtypes
int64 Whole numbers 320
float64 Decimal (floating-point) numbers 10.0
str dtype pandas 3's dtype for text columns day str
object dtype General Python objects, often mixed types [320, "n/a", 410]
columns The column names (an Index) Index(['day', 'cups', ...])
RangeIndex The default index 0, 1, 2, ... stored as start/stop/step RangeIndex(start=0, stop=7, step=1)
describe Summary statistics of number columns week.describe()
count Number of non-missing values count 4
mean Average 357.14
Standard deviation (std) How far values typically sit from the mean 62.1
Percentile The value below which that percent of data falls 25% = 310
Median The middle value when sorted (50th percentile) 350
unique / top / freq Distinct values / most common value / its count (text columns) top sunny, freq 3
Memory usage How much RAM the table takes 356.0 bytes
set_index Make a column the index (returns a new DataFrame) week.set_index("day")
reset_index Turn the index back into a column, restore 0, 1, 2 byday.reset_index()
loc Find rows by index label (episode 14) byday.loc["Wed"]
value_counts Count how often each value appears (episode 11) chai 3

Summary

  • Separate lists fall out of step silently; a DataFrame keeps each record on one row, each column is a Series, and the index labels the rows.
  • Create one from a dict of lists (lengths must match), a list of dicts (missing key → NaN, int column → float64), or a list of lists with columns=.
  • First look: head / tail / sample → shape → info() (non-null counts, dtypes, memory) → describe() (count, mean, std, min, 25/50/75%, max).
  • dtypes, columns, index (a RangeIndex by default) describe the table's structure.
  • Text is str in pandas 3; object in a number column means dirty data and breaks maths.
  • set_index gives rows meaningful labels, reset_index undoes it, and both return a new DataFrame: save the result.
  • Next episode: pandas 3: reading and writing CSV, Excel, JSON and Parquet.