An attribute table is a table of rows (features) and columns (fields). pandas is the Python library for tables. GeoPandas, used from Part 10, is pandas with a geometry column added, so everything here carries over directly.
What is pandas?
pandas gives you two objects. A DataFrame is a whole table. A Series is one column of it. You import the library with the standard short name:
# pandas is the tool for tables of data. "as pd" gives it a short name.
import pandas as pd
Install it with pip install pandas if it is not available. QGIS's Python already has it.
Why it matters
In Part 3 you looped over rows one by one. With pandas, one line works on a whole column at once: df["population"] / df["area_km2"] divides every row. The code is shorter and runs faster.
1. Create the sample data and read it back
The data is a small table of Indian cities. The populations are rounded approximations from the 2011 census, meant for practice. Two values are missing on purpose, as they often are in real data.
import pandas as pd
from pathlib import Path
# Step 1: some CSV text. In real life this would be a file you downloaded.
# Look closely: Lucknow has no population and Patna has no area. Missing data is normal.
csv_text = """city,state,population,area_km2,lat,lon
Bengaluru,Karnataka,8443675,741,12.9716,77.5946
Mysuru,Karnataka,920550,152,12.2958,76.6394
Mangaluru,Karnataka,484785,132,12.9141,74.8560
Hubballi,Karnataka,943857,191,15.3647,75.1240
Chennai,Tamil Nadu,4646732,426,13.0827,80.2707
Coimbatore,Tamil Nadu,1050721,246,11.0168,76.9558
Madurai,Tamil Nadu,1017865,148,9.9252,78.1198
Hyderabad,Telangana,6809970,650,17.3850,78.4867
Mumbai,Maharashtra,12442373,603,19.0760,72.8777
Pune,Maharashtra,3124458,331,18.5204,73.8567
Nagpur,Maharashtra,2405665,218,21.1458,79.0882
Delhi,Delhi,11034555,1484,28.6139,77.2090
Kolkata,West Bengal,4496694,185,22.5726,88.3639
Jaipur,Rajasthan,3046163,467,26.9124,75.7873
Lucknow,Uttar Pradesh,,631,26.8467,80.9462
Patna,Bihar,1684222,,25.5941,85.1376
"""
# Step 2: save the text as a file called cities.csv
Path("cities.csv").write_text(csv_text)
# Step 3: read the file into a DataFrame. A DataFrame is a table: rows and named columns.
df = pd.read_csv("cities.csv")
print(df.head()) # head() shows the first 5 rows
city state population area_km2 lat lon
0 Bengaluru Karnataka 8443675.0 741.0 12.9716 77.5946
1 Mysuru Karnataka 920550.0 152.0 12.2958 76.6394
2 Mangaluru Karnataka 484785.0 132.0 12.9141 74.8560
3 Hubballi Karnataka 943857.0 191.0 15.3647 75.1240
4 Chennai Tamil Nadu 4646732.0 426.0 13.0827 80.2707
Path(...).write_text saves the text as a file, so the example works anywhere. With your own data you would only write pd.read_csv("your_file.csv"). read_csv returns a DataFrame, and head() shows the first five rows.
2. Inspect the table
Look at a table before you analyse it.
print(df.shape) # (number of rows, number of columns)
print(list(df.columns)) # the column names
print(df.dtypes) # the kind of data in each column (int, float, text="object" or "str")
(16, 6)
['city', 'state', 'population', 'area_km2', 'lat', 'lon']
city str
state str
population float64
area_km2 float64
lat float64
lon float64
dtype: object
shape is (rows, columns). columns lists the field names. dtypes shows the type of each column: int64 for whole numbers, float64 for decimals, and str (older pandas versions print object) for text. Lucknow's missing population forced that column to float64, because a missing value is stored as NaN (not a number), and NaN is a decimal.
df.info() # a one-screen summary: columns, how many values are filled in, types
<class 'pandas.DataFrame'>
RangeIndex: 16 entries, 0 to 15
Data columns (total 6 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 city 16 non-null str
1 state 16 non-null str
2 population 15 non-null float64
3 area_km2 15 non-null float64
4 lat 16 non-null float64
5 lon 16 non-null float64
dtypes: float64(4), str(2)
memory usage: 1.1 KB
info() lists each column with its count of non-missing values. The population and area columns have 15 of 16, so one value is missing in each.
print(df.describe().round(1)) # count, mean, min, max and quartiles for each numeric column
population area_km2 lat lon
count 15.0 15.0 16.0 16.0
mean 4170152.3 440.3 18.4 78.2
std 3845068.6 356.3 6.3 4.0
min 484785.0 132.0 9.9 72.9
25% 1034293.0 188.0 13.0 75.6
50% 3046163.0 331.0 18.0 77.4
75% 5728351.0 617.0 23.3 79.4
max 12442373.0 1484.0 28.6 88.4
describe() gives count, mean, standard deviation, minimum, quartiles and maximum for every number column. Use it to spot impossible values, such as a negative area or a latitude above 90.
3. Missing values
# isna() marks empty cells as True. sum() then counts the True values in each column.
print(df.isna().sum())
# Show the rows where population is empty: pick the rows with [condition], then the columns with [[...]]
print(df[df["population"].isna()][["city", "population"]])
city 0
state 0
population 1
area_km2 1
lat 0
lon 0
dtype: int64
city population
14 Lucknow NaN
isna() gives True where a value is missing, and sum() counts the True values per column. The second line is a filter, explained shortly. Decide what to do with each missing value: fill it from another source, drop the row, or leave it and let calculations skip it.
# Option 1: fill the missing area with the middle (median) value of the column
df["area_km2"] = df["area_km2"].fillna(df["area_km2"].median())
# Option 2: drop the rows that have no population. We save the result in a NEW table (no_pop)
# so that df stays unchanged.
no_pop = df.dropna(subset=["population"])
print(len(df), len(no_pop)) # 16 rows, then 15 rows
16 15
fillna replaces missing values (here with the median area), and dropna(subset=[...]) removes rows that are missing a value in the named columns. Filling is a judgement call: a median is a rough guess, so note it whenever you do it.
4. Select columns and rows
Square brackets with a name pick a column. Two brackets with a list pick several.
names = df["city"] # one pair of brackets and one name = one column (a Series)
small = df[["city", "state", "population"]] # two pairs of brackets and a list = several columns (a DataFrame)
print(type(names).__name__, type(small).__name__)
print(small.head(3))
Series DataFrame
city state population
0 Bengaluru Karnataka 8443675.0
1 Mysuru Karnataka 920550.0
2 Mangaluru Karnataka 484785.0
One name gives a Series and a list gives a DataFrame. Rows are selected by label or position with loc and iloc:
# loc picks by LABEL (row number and column name); iloc picks by POSITION
print(df.loc[0, "city"]) # row 0, column "city"
print(df.iloc[2]) # the whole third row
print(df.loc[1:3, ["city", "population"]]) # rows 1 to 3 (loc includes the end), two columns
Bengaluru
city Mangaluru
state Karnataka
population 484785.0
area_km2 132.0
lat 12.9141
lon 74.856
Name: 2, dtype: object
city population
1 Mysuru 920550.0
2 Mangaluru 484785.0
3 Hubballi 943857.0
loc uses labels, and with a default index the labels are 0, 1, 2 and so on. iloc uses positions. Notice that loc[1:3] includes row 3, unlike a Python list slice.
5. Filter rows
Put a condition inside the brackets and you keep only the rows where it is true.
# Filtering: df["population"] > 3_000_000 gives True/False for every row.
# Putting that inside df[...] keeps only the True rows.
big = df[df["population"] > 3_000_000]
print(big[["city", "population"]])
city population
0 Bengaluru 8443675.0
4 Chennai 4646732.0
7 Hyderabad 6809970.0
8 Mumbai 12442373.0
9 Pune 3124458.0
11 Delhi 11034555.0
12 Kolkata 4496694.0
13 Jaipur 3046163.0
The condition df["population"] > 3_000_000 produces a True or False value for every row, and the outer brackets keep the True ones. Combine conditions with & (and), | (or) and ~ (not). Each condition needs its own round brackets.
# Several conditions: put each in ( ) and join with & (and) or | (or). Do not use the words and / or here.
south_big = df[(df["state"] == "Karnataka") & (df["population"] > 900_000)]
print(south_big[["city", "population"]])
# isin() matches any value in a list
south = df[df["state"].isin(["Karnataka", "Tamil Nadu"])]
print(len(south))
# between() checks a range, including both ends
band = df[df["lat"].between(12, 14)]
print(band["city"].tolist())
# query() writes the same kind of filter as a piece of text
print(df.query("state == 'Maharashtra' and population > 3000000")["city"].tolist())
city population
0 Bengaluru 8443675.0
1 Mysuru 920550.0
3 Hubballi 943857.0
7
['Bengaluru', 'Mysuru', 'Mangaluru', 'Chennai']
['Mumbai', 'Pune']
isin tests membership in a list, between tests a range, and query lets you write the condition as a string. Do not use the Python words and and or between conditions on columns. Use & and |.
6. Sort and take the top rows
# Sort from biggest to smallest and keep the first 5 rows
top = df.sort_values("population", ascending=False).head(5)
print(top[["city", "population"]])
# nlargest does the same in one step: the 3 biggest areas
print(df.nlargest(3, "area_km2")[["city", "area_km2"]])
city population
8 Mumbai 12442373.0
11 Delhi 11034555.0
0 Bengaluru 8443675.0
7 Hyderabad 6809970.0
4 Chennai 4646732.0
city area_km2
11 Delhi 1484.0
0 Bengaluru 741.0
7 Hyderabad 650.0
sort_values orders by a column, and ascending=False puts the largest first. nlargest is a shortcut for the same idea. Missing values go to the end of a sort.
7. Add and change columns
Whole-column arithmetic creates a new field in one line:
# A new column is made by assigning to a name that does not exist yet. The maths runs on every row at once.
df["density"] = (df["population"] / df["area_km2"]).round(0)
print(df[["city", "density"]].sort_values("density", ascending=False).head(3))
print(df["state"].nunique()) # how many different states
print(df["state"].value_counts()) # how many cities in each state
city density
12 Kolkata 24306.0
8 Mumbai 20634.0
0 Bengaluru 11395.0
9
state
Karnataka 4
Tamil Nadu 3
Maharashtra 3
Telangana 1
Delhi 1
West Bengal 1
Rajasthan 1
Uttar Pradesh 1
Bihar 1
Name: count, dtype: int64
nunique counts distinct values and value_counts counts the rows per value. The density of Lucknow is missing because its population is missing, and pandas carries missing values through calculations without an error. to_csv saves your result:
# Save the table, with the new column, as a CSV. index=False leaves out the row numbers.
df.to_csv("cities_with_density.csv", index=False)
print(Path("cities_with_density.csv").exists()) # True means the file was created
True
index=False stops pandas from writing its row numbers as an extra column.
Run this part as a notebook
The code on this page is also a Jupyter notebook. It has the theory in text cells, then the code, then guided practice, an independent assignment and hidden solutions, so you can try each task before you look. Download the Part 7 notebook, open it in JupyterLab and run the cells from top to bottom. Part 1 shows how to install and start Jupyter.
Practice
- Find the cities with a population under 1 million and a latitude above 15.
- List the five cities with the lowest density.
- Count how many cities each state has.
- Fill the missing population with 0 in a copy of the table and compare the average with the original. Why is 0 a poor choice?
Common mistakes
- Using
andandorinstead of&and|in a filter. - Forgetting the round brackets around each condition.
- Filtering with
df[df["x"] > 5]["y"] = 1. This changes a copy. Usedf.loc[df["x"] > 5, "y"] = 1. - Treating
NaNas zero. Missing is not the same as none. - Forgetting
index=Falsewhen saving a CSV. - Expecting
sort_valuesto changedf. It returns a new table unless you store it.