Pandas for GIS Tables: Groupby, Merge, Reshape, Classes and Dates

Part 7 selected and filtered rows. This part changes the shape of a table: summarise it, combine it with another, restructure it and classify its values. These are the same operations GIS software calls Statistics by Categories, Join Attributes by Field Value and Reclassify.

What is the idea?

Most analysis questions fit one of these patterns:

Question	pandas tool	GIS equivalent
How many cities and what total population per state?	`groupby`	Statistics by categories
Attach district names to a table of cities	`merge`	Join attributes by field value
Add the 2024 table below the 2023 table	`concat`	Merge vector layers
Turn year columns into a year field	`melt`	Transpose
Put values into classes	`cut`, `qcut`	Reclassify

1. The sample data

import pandas as pd

# A small table of cities. Each list is one column; the lists must be the same length.
cities = pd.DataFrame({
    "city": ["Bengaluru", "Mysuru", "Mangaluru", "Chennai", "Coimbatore",
             "Mumbai", "Pune", "Nagpur", "Hyderabad"],
    "state": ["Karnataka", "Karnataka", "Karnataka", "Tamil Nadu", "Tamil Nadu",
              "Maharashtra", "Maharashtra", "Maharashtra", "Telangana"],
    "population": [8443675, 920550, 484785, 4646732, 1050721,
                   12442373, 3124458, 2405665, 6809970],
    "area_km2": [741, 152, 132, 426, 246, 603, 331, 218, 650],
})
print(cities)

         city        state  population  area_km2
0   Bengaluru    Karnataka     8443675       741
1      Mysuru    Karnataka      920550       152
2   Mangaluru    Karnataka      484785       132
3     Chennai   Tamil Nadu     4646732       426
4  Coimbatore   Tamil Nadu     1050721       246
5      Mumbai  Maharashtra    12442373       603
6        Pune  Maharashtra     3124458       331
7      Nagpur  Maharashtra     2405665       218
8   Hyderabad    Telangana     6809970       650

A DataFrame can be made directly from a dictionary of lists, where each key is a column. This is the same structure as the list-of-dictionaries table from Part 2, with the roles turned around.

2. Groupby: summarise by group

groupby splits the table into groups, applies a calculation to each, and combines the results.

# groupby("state") puts the rows of each state in its own pile.
# ["population"].sum() then adds up the population inside each pile.
by_state = cities.groupby("state")["population"].sum()
print(by_state)

state
Karnataka       9849010
Maharashtra    17972496
Tamil Nadu      5697453
Telangana       6809970
Name: population, dtype: int64

Read it as: group by state, take the population column, add it up. Ask for several statistics at once with agg:

# agg lets you ask for several results at once.
# The pattern is:  new_column_name=("column to use", "what to calculate")
summary = cities.groupby("state").agg(
    n_cities=("city", "count"),            # how many cities in the state
    total_pop=("population", "sum"),       # their combined population
    mean_pop=("population", "mean"),       # the average city size
    largest=("population", "max"),         # the biggest city
).round(0)                                 # round everything to whole numbers

# Show the biggest states first
print(summary.sort_values("total_pop", ascending=False))

             n_cities  total_pop   mean_pop   largest
state                                                
Maharashtra         3   17972496  5990832.0  12442373
Karnataka           3    9849010  3283003.0   8443675
Telangana           1    6809970  6809970.0   6809970
Tamil Nadu          2    5697453  2848726.0   4646732

Each argument has the form new_name=("column", "function"). The group names (the state) become the index of the result. Use reset_index() to turn them back into a normal column, which is needed before merging or saving:

# After groupby the state names became the row labels (the index).
# reset_index() turns them back into a normal column, which is easier to merge and save.
summary = summary.reset_index()
print(summary.columns.tolist())

['state', 'n_cities', 'total_pop', 'mean_pop', 'largest']

size() counts rows per group, and value_counts() on a single column does the same job in one call.

3. Merge: join on a key

Attribute data often arrives in separate tables. A join matches rows using a shared column, the key.

# A second table: one row per state, with its capital. Kerala has no city in our list.
states = pd.DataFrame({
    "state": ["Karnataka", "Tamil Nadu", "Maharashtra", "Kerala"],
    "capital": ["Bengaluru", "Chennai", "Mumbai", "Thiruvananthapuram"],
})

# merge = join two tables on a shared column (like a VLOOKUP).
# how="left" keeps every city, and fills "capital" where the state matches.
joined = cities.merge(states, on="state", how="left")
print(joined[["city", "state", "capital"]])     # Telangana has no match, so its capital is empty (NaN)

         city        state    capital
0   Bengaluru    Karnataka  Bengaluru
1      Mysuru    Karnataka  Bengaluru
2   Mangaluru    Karnataka  Bengaluru
3     Chennai   Tamil Nadu    Chennai
4  Coimbatore   Tamil Nadu    Chennai
5      Mumbai  Maharashtra     Mumbai
6        Pune  Maharashtra     Mumbai
7      Nagpur  Maharashtra     Mumbai
8   Hyderabad    Telangana        NaN

on="state" names the key. The how argument decides which rows survive:

`how`	Keeps
`inner`	only keys found in both tables
`left`	every row of the left table, with blanks where there is no match
`right`	every row of the right table
`outer`	every row of both

Hyderabad's state, Telangana, is not in the states table, so its capital is missing (NaN) after a left join. Kerala has no city, so it disappears in a left join. Compare:

# The same merge four ways. Compare how many rows each keeps:
#   inner = only rows that match in both tables
#   left  = every row from the left table (cities)
#   right = every row from the right table (states)
#   outer = every row from both
for how in ["inner", "left", "right", "outer"]:
    print(how, len(cities.merge(states, on="state", how=how)))

inner 8
left 9
right 9
outer 10

Always check what failed to match, because unmatched keys are the most common join problem. A trick is indicator=True:

# indicator=True adds a "_merge" column that says where each row came from:
# "both", "left_only" or "right_only". Filtering out "both" shows the rows that did not match.
check = cities.merge(states, on="state", how="outer", indicator=True)
print(check[check["_merge"] != "both"][["city", "state", "_merge"]])

        city      state      _merge
3        NaN     Kerala  right_only
9  Hyderabad  Telangana   left_only

Spelling and capital letters in the key must agree exactly. "karnataka" does not match "Karnataka", so clean the key first (Part 9).

4. Concat: stack tables

merge adds columns by key. concat stacks tables that share the same columns, for example one file per year.

# Take the first 3 cities and label them with a year (assign adds a column)
t2023 = cities[["city", "population"]].head(3).assign(year=2023)

# Make a pretend 2024 copy where the population grew by 2%
t2024 = t2023.assign(year=2024, population=t2023["population"] * 1.02)

# concat stacks tables on top of each other. ignore_index=True renumbers the rows 0, 1, 2...
stacked = pd.concat([t2023, t2024], ignore_index=True)
print(stacked)

        city  population  year
0  Bengaluru   8443675.0  2023
1     Mysuru    920550.0  2023
2  Mangaluru    484785.0  2023
3  Bengaluru   8612548.5  2024
4     Mysuru    938961.0  2024
5  Mangaluru    494480.7  2024

assign adds or replaces columns on a copy. ignore_index=True renumbers the rows. Without it the stacked table repeats index 0, 1, 2, which causes confusing results later. Use axis=1 to place tables side by side instead.

5. Reshape: wide and long

A wide table has one column per year. A long table has one row per city and year. Plotting and grouping usually want long data.

# A "wide" table: one column per year
wide = pd.DataFrame({
    "city": ["Bengaluru", "Mysuru", "Chennai"],
    "pop_2022": [8.5, 0.93, 4.7],
    "pop_2023": [8.7, 0.95, 4.8],
    "pop_2024": [8.9, 0.97, 4.9],
})

# melt turns it "long": one row per city and year.
# id_vars is the column to keep as it is; the other columns are folded into two new ones.
long = wide.melt(id_vars="city", var_name="year", value_name="population_m")

# The year column holds text like "pop_2022". Remove "pop_" and turn what is left into a number.
long["year"] = long["year"].str.replace("pop_", "").astype(int)
print(long.sort_values(["city", "year"]))

        city  year  population_m
0  Bengaluru  2022          8.50
3  Bengaluru  2023          8.70
6  Bengaluru  2024          8.90
2    Chennai  2022          4.70
5    Chennai  2023          4.80
8    Chennai  2024          4.90
1     Mysuru  2022          0.93
4     Mysuru  2023          0.95
7     Mysuru  2024          0.97

melt keeps the id_vars and stacks every other column into two new ones, a name column and a value column. The population values are in millions and are illustrative numbers for practice. Turn long data back into wide data with pivot_table:

# pivot_table does the reverse of melt: it spreads the years back out into columns
back = long.pivot_table(index="city", columns="year", values="population_m")
print(back)

year       2022  2023  2024
city                       
Bengaluru  8.50  8.70  8.90
Chennai    4.70  4.80  4.90
Mysuru     0.93  0.95  0.97

pivot_table also handles duplicates by aggregating them (the mean by default), while the simpler pivot stops with an error if a city and year pair appears twice. pd.crosstab(a, b) counts how often each pair of categories occurs together.

6. Classify numbers: cut and qcut

cut makes classes with boundaries you choose. qcut makes classes with equal numbers of rows.

# pd.cut sorts numbers into bins you choose.
# bins=[0, 1 million, 5 million, infinity] makes three groups, so there are three labels.
cities["size_class"] = pd.cut(
    cities["population"],
    bins=[0, 1_000_000, 5_000_000, float("inf")],
    labels=["small", "large", "metro"],
)
print(cities[["city", "population", "size_class"]])
print(cities["size_class"].value_counts())      # how many cities fall in each group

         city  population size_class
0   Bengaluru     8443675      metro
1      Mysuru      920550      small
2   Mangaluru      484785      small
3     Chennai     4646732      large
4  Coimbatore     1050721      large
5      Mumbai    12442373      metro
6        Pune     3124458      large
7      Nagpur     2405665      large
8   Hyderabad     6809970      metro
size_class
large    4
metro    3
small    2
Name: count, dtype: int64

Bins are written as boundaries, so three classes need four numbers. Each class includes its upper limit by default, so a city of exactly 1,000,000 goes to small. Use fixed bins when the boundaries have a meaning, such as a legal definition or a map legend.

# Step 1: people per km2
cities["density"] = (cities["population"] / cities["area_km2"]).round(0)

# Step 2: pd.qcut makes groups with (about) the same number of cities in each.
# 4 means quartiles: Q1 is the least dense quarter, Q4 the densest.
cities["density_quartile"] = pd.qcut(cities["density"], 4, labels=["Q1", "Q2", "Q3", "Q4"])
print(cities[["city", "density", "density_quartile"]].sort_values("density"))

         city  density density_quartile
2   Mangaluru   3673.0               Q1
4  Coimbatore   4271.0               Q1
1      Mysuru   6056.0               Q1
6        Pune   9439.0               Q2
8   Hyderabad  10477.0               Q2
3     Chennai  10908.0               Q3
7      Nagpur  11035.0               Q3
0   Bengaluru  11395.0               Q4
5      Mumbai  20634.0               Q4

Quantile classes always fill evenly, which makes comparison easy, but the boundaries are chosen by the data and can be hard to explain. This is the same choice you make in a QGIS graduated style between equal interval and quantile modes.

7. Dirty numbers, dates and missing values

Data from files and forms often has numbers stored as text. to_numeric with errors="coerce" turns anything it cannot read into NaN instead of failing.

# Messy values stored as text: a comma in a number, "n/a", two date styles, and a blank
dirty = pd.DataFrame({
    "site": ["A", "B", "C", "D"],
    "elevation": ["920", "1,050", "n/a", "845"],
    "visited": ["2025-03-15", "15/03/2025", "2025-04-02", ""],
})

# Step 1: remove the comma, then convert to a number.
# errors="coerce" means "if it cannot be converted (like n/a), leave an empty value instead of crashing"
dirty["elevation_m"] = pd.to_numeric(dirty["elevation"].str.replace(",", ""), errors="coerce")

# Step 2: convert text to dates. The format says what a good date looks like (year-month-day).
# Rows in another style, or blank, become empty (NaT) instead of guessing wrongly.
dirty["visit_date"] = pd.to_datetime(dirty["visited"], format="%Y-%m-%d", errors="coerce")
print(dirty[["site", "elevation_m", "visit_date"]])

  site  elevation_m visit_date
0    A        920.0 2025-03-15
1    B       1050.0        NaT
2    C          NaN 2025-04-02
3    D        845.0        NaT

The comma is removed first, so "1,050" becomes 1050. "n/a" cannot be converted and becomes NaN. The date 15/03/2025 does not match the format, so it also becomes NaT (not a time). That is a feature: the problem rows are marked instead of crashing the script, and you can list them with dirty[dirty["visit_date"].isna()]. Parse dates in the format you expect, because a guessed day and month order can silently swap them.

Once a column holds real dates, the .dt accessor gives its parts:

# A series of 4 dates, 16 days apart (this is the Landsat revisit gap)
dates = pd.Series(pd.date_range("2025-03-15", periods=4, freq="16D"))

# .dt gives access to parts of a date. strftime writes it in the format you choose:
# %d = day, %b = short month name, %Y = year
print(dates.dt.strftime("%d %b %Y").tolist())
print(dates.dt.month.tolist())

# bdate_range counts working days only (it skips Saturday and Sunday)
print(pd.bdate_range("2025-03-14", periods=4).strftime("%a %d").tolist())

['15 Mar 2025', '31 Mar 2025', '16 Apr 2025', '02 May 2025']
[3, 3, 4, 5]
['Fri 14', 'Mon 17', 'Tue 18', 'Wed 19']

date_range makes a sequence of dates, here 16 days apart like a Landsat revisit. bdate_range skips weekends.

8. Category columns

Models and some tools need categories as numbers. get_dummies makes one 0 or 1 column per category, and factorize gives each category a code.

roads = pd.Series(["NH", "SH", "NH", "MDR", "SH"], name="road_class")

# get_dummies makes one 0/1 column per category (a "one-hot" table)
print(pd.get_dummies(roads, dtype=int))

# factorize gives each category a number code instead. labels says which number means what.
codes, labels = pd.factorize(roads)
print(codes.tolist(), labels.tolist())

   MDR  NH  SH
0    0   1   0
1    0   0   1
2    0   1   0
3    1   0   0
4    0   0   1
[0, 1, 0, 2, 1] ['NH', 'SH', 'MDR']

Use dummies when categories have no order, and codes when you just need an identifier.

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 8 notebook, open it in JupyterLab and run the cells from top to bottom. Part 1 shows how to install and start Jupyter.

Practice

Group the cities by state and find the state with the highest average density.
Merge the cities table with a small table of your own that adds a region column. Use how="left" and check for unmatched states.
Melt a wide table of three years of rainfall for four stations, then pivot it back.
Classify the density into low, medium and high with cut, then compare with qcut.

Common mistakes

Joining on keys that differ in spelling, spaces or capital letters.
Using inner when you wanted to keep unmatched rows, and losing them without noticing.
Merging without checking that the key is unique, which can multiply rows.
Forgetting reset_index() after groupby.
Concatenating without ignore_index=True and then selecting by index.
Using qcut when the class limits need a real-world meaning.

Key takeaways

Next: Pandas text cleaning.