Data wrangling

GEOG 30323: Data Analysis & Visualization

Dr. Kyle Walker

2026-09-29

Data wrangling

In real-world data analysis, your data will likely:

  • Have missing/possibly incorrect values
  • Be in a format unsuitable for data analysis
  • Be spread across multiple files, possibly of different types
  • Need re-shaping or summarization to draw meaningful conclusions

Fortunately, pandas can help you with all of this!

Subsetting

  • Frequently, you’ll have way more data than you need!
  • Datasets can be reduced in size by indexing and subsetting
  • Let’s read in the colleges dataset as a demo
import pandas as pd

full_url = 'https://raw.githubusercontent.com/walkerke/geog30323/main/data/scorecard.csv'
full_df = pd.read_csv(full_url)
full_df.shape
(6273, 25)

By column name

  • Let’s drop most of the columns in the dataset with .filter()
cols_to_keep = ['INSTNM', 'STABBR', 'GRAD_DEBT_MDN_SUPP']
debt_df = full_df.filter(cols_to_keep)
debt_df.columns = ['name', 'state', 'debt']
debt_df.head()
                                  name state   debt
0             Alabama A & M University    AL  31000
1  University of Alabama at Birmingham    AL  22300
2                   Amridge University    AL  32189
3  University of Alabama in Huntsville    AL  20705
4             Alabama State University    AL  31000

By row position

  • Data frames can be sliced like lists and strings
# Retain row 0 up until but not including row 10
debt_df[0:10]
                                  name state   debt
0             Alabama A & M University    AL  31000
1  University of Alabama at Birmingham    AL  22300
2                   Amridge University    AL  32189
3  University of Alabama in Huntsville    AL  20705
4             Alabama State University    AL  31000
5            The University of Alabama    AL  22750
6    Central Alabama Community College    AL   9766
7              Athens State University    AL  18051
8      Auburn University at Montgomery    AL  25000
9                    Auburn University    AL  21000

By row or column index

  • Selecting by row or column index available in the .loc[] method (note the brackets)
example1 = debt_df.set_index('name')
example1.loc['Amridge University':'Alabama State University']
                                    state   debt
name                                            
Amridge University                     AL  32189
University of Alabama in Huntsville    AL  20705
Alabama State University               AL  31000

By value

  • Often, you’ll want to keep rows that have a certain column value, or exclude rows based on that value
  • The data frame method .query() will “query” your dataset based on an expression
  • Expressions use conditional operators; can be combined with & (and) and | (or)

By value

debt_df1 = debt_df.query("debt != 'PS'")
debt_df1.head()
                                  name state   debt
0             Alabama A & M University    AL  31000
1  University of Alabama at Birmingham    AL  22300
2                   Amridge University    AL  32189
3  University of Alabama in Huntsville    AL  20705
4             Alabama State University    AL  31000

By value

tx_debt_df = debt_df.query('debt != "PS" & state == "TX" ')
tx_debt_df.head()

# Alternatively, use an index-based method

# tx_debt_df = debt_df.loc[(debt_df['debt'] != 'PS') & (debt_df['state'] == 'TX')]
                              name state   debt
2977  Abilene Christian University    TX  24250
2978       Alvin Community College    TX   4519
2979              Amarillo College    TX  15000
2982       Angelo State University    TX  20000
2983  Arlington Baptist University    TX  27000

By value

states = ['OK', 'NM', 'TX', 'LA']
# Use `in @states` to get values in the list
# @ operator allows for use of variables in the query
sw_debt_df = debt_df.query("debt != 'PS' & state in @states")

sw_debt_df.head()
                                                                                        name state   debt
91                                                        Pima Medical Institute-Albuquerque    NM   5500
1195                                           Central Louisiana Technical Community College    LA   7000
1196                                                                    Ayers Career College    LA   9500
1197  Baton Rouge General Medical Center School of Nursing & School of Radiologic Technology    LA  16750
1198                                                        Bossier Parish Community College    LA  17500

Creating new columns

  • New columns can be created based on specified values, or as derivatives of other columns, using mathematical operators or the .assign() method
  • Let’s demo with a simulated data frame:
import numpy as np
np.random.seed(1983)

df1 = pd.DataFrame({'col1': np.random.randint(1, 100, 10), 
                    'col2': np.random.randint(1, 100, 10), 
                    'col3': np.random.randint(1, 100, 10)})

Creating new columns

# With .assign()
df2 = df1.assign(col4 = df1.col1 + df1.col2)

# With index-based labeling
df2['col5'] = df2['col3'] / df2['col4']

df2.head()
   col1  col2  col3  col4      col5
0    27    35    23    62  0.370968
1    47    88    15   135  0.111111
2    57    81    92   138  0.666667
3    75    32    58   107  0.542056
4    25    20    50    45  1.111111

dtype conversion

  • To do numerical analysis, our numeric data have to be stored as numbers!
  • To convert: use the .astype() method
sw_debt_num = sw_debt_df.assign(debtnum = sw_debt_df.debt.astype(float))

sw_debt_num.head()
                                                                                        name state   debt  debtnum
91                                                        Pima Medical Institute-Albuquerque    NM   5500   5500.0
1195                                           Central Louisiana Technical Community College    LA   7000   7000.0
1196                                                                    Ayers Career College    LA   9500   9500.0
1197  Baton Rouge General Medical Center School of Nursing & School of Radiologic Technology    LA  16750  16750.0
1198                                                        Bossier Parish Community College    LA  17500  17500.0

Missing data

  • Commonly, all of the data you need will not be found in your data set!
  • Possible solutions:
    • Delete all rows that have missing data
    • Fill in missing data with a specified value
    • Interpolate missing values

Missing data

  • .dropna() method: delete all rows (or columns) that have any missing values (NaN in pandas)
sw_debt_clean = sw_debt_num.dropna()

sw_debt_clean.head()
                                                                                        name state   debt  debtnum
91                                                        Pima Medical Institute-Albuquerque    NM   5500   5500.0
1195                                           Central Louisiana Technical Community College    LA   7000   7000.0
1196                                                                    Ayers Career College    LA   9500   9500.0
1197  Baton Rouge General Medical Center School of Nursing & School of Radiologic Technology    LA  16750  16750.0
1198                                                        Bossier Parish Community College    LA  17500  17500.0

Missing data

  • .fillna() method: fill in missing data with a specified value
sw_debt_fill = sw_debt_num.fillna(sw_debt_num.median(numeric_only = True))

sw_debt_fill.head()
                                                                                        name state   debt  debtnum
91                                                        Pima Medical Institute-Albuquerque    NM   5500   5500.0
1195                                           Central Louisiana Technical Community College    LA   7000   7000.0
1196                                                                    Ayers Career College    LA   9500   9500.0
1197  Baton Rouge General Medical Center School of Nursing & School of Radiologic Technology    LA  16750  16750.0
1198                                                        Bossier Parish Community College    LA  17500  17500.0

Method chaining

  • pandas data wrangling methods can be “chained” together to compute a data wrangling workflow all at once
cols_to_keep = ['INSTNM', 'STABBR', 'GRAD_DEBT_MDN_SUPP']
states = ['OK', 'NM', 'TX', 'LA']

sw_debt_clean = (full_df
  .filter(cols_to_keep)
  .set_axis(['name', 'state', 'debt'], axis = 'columns')
  .query("debt != 'PS' & state in @states")
  .assign(debtnum = lambda x: x.debt.astype(float))
  .dropna()
)

Group-wise data analysis

  • Thus far, we’ve focused on characteristics of data within a particular group
  • Common question: how do characteristics vary by group?
  • In pandas: .groupby() method!

Split-apply-combine

  • Wickham (2011): the “split-apply-combine” model of data analysis

Process:

  • Data are split by some characteristic into groups
  • We apply a function to each of the groups
  • The resultant data are combined back into a single dataset

.groupby() in pandas

sw_grouped = sw_debt_clean.groupby('state')

sw_grouped.debtnum.mean()

# Result

state
LA    15458.226190
NM    13245.437500
OK    15708.925926
TX    13707.255385

Grouped visualization in seaborn

import seaborn as sns
sns.set(style = "darkgrid")

sns.boxplot(x = 'state', y = 'debtnum', data = sw_debt_clean)

Grouped visualization in seaborn

  • Faceting or small multiples: breaking down a plot by a grouping variable into multiple plots
grid = sns.FacetGrid(data = sw_debt_clean, col = 'state', col_wrap = 2)
grid.map(sns.kdeplot, 'debtnum')

Merging data

  • Commonly, you’ll have data in two - or multiple! - datasets that you’ll want to combine into one
  • Simulated data:
np.random.seed(123456)

m1 = pd.DataFrame({'type': ['a', 'b', 'c', 'd', 'e', 'f'], 
                  'ind1': np.random.randint(1, 100, 6), 
                  'ind2': np.random.randint(1, 100, 6)})

m2 = pd.DataFrame({'type': ['a', 'b', 'c', 'd', 'e', 'f'], 
                  'ind3': np.random.randint(1, 100, 6), 
                  'ind4': np.random.randint(1, 100, 6)})

The .merge() method in pandas

m3 = m1.merge(m2, on = 'type')
  type  ind1  ind2  ind3  ind4
0    a    66    33    13    35
1    b    50    88    76    15
2    c    57    37    21    71
3    d    44     9    48    43
4    e    44    75    51    67
5    f    92    11    87    48

Types of merges in pandas

  • Options for merging (the how parameter): 'inner' (default), 'left', 'right', and 'outer'
  • Simulated data:
m4 = pd.DataFrame({'type': ['d', 'e', 'f', 'g', 'h', 'i'], 
                  'ind5': np.random.randint(1, 100, 6), 
                  'ind6': np.random.randint(1, 100, 6)})

Inner merges

m5 = m1.merge(m4, on = 'type', how = 'inner')
  type  ind1  ind2  ind5  ind6
0    d    44     9    69    46
1    e    44    75    95    70
2    f    92    11    46    88

Left merges

m5 = m1.merge(m4, on = 'type', how = 'left')
  type  ind1  ind2  ind5  ind6
0    a    66    33   NaN   NaN
1    b    50    88   NaN   NaN
2    c    57    37   NaN   NaN
3    d    44     9  69.0  46.0
4    e    44    75  95.0  70.0
5    f    92    11  46.0  88.0

Right merges

m5 = m1.merge(m4, on = 'type', how = 'right')
  type  ind1  ind2  ind5  ind6
0    d  44.0   9.0    69    46
1    e  44.0  75.0    95    70
2    f  92.0  11.0    46    88
3    g   NaN   NaN    88    37
4    h   NaN   NaN    85    76
5    i   NaN   NaN    85    36

Outer merges

m5 = m1.merge(m4, on = 'type', how = 'outer')
  type  ind1  ind2  ind5  ind6
0    a  66.0  33.0   NaN   NaN
1    b  50.0  88.0   NaN   NaN
2    c  57.0  37.0   NaN   NaN
3    d  44.0   9.0  69.0  46.0
4    e  44.0  75.0  95.0  70.0
5    f  92.0  11.0  46.0  88.0
6    g   NaN   NaN  88.0  37.0
7    h   NaN   NaN  85.0  76.0
8    i   NaN   NaN  85.0  36.0

The “shape” of data

  • Long (“tidy”) data:
    • Each variable forms a column;
    • Each observation forms a row;
    • Each type of observational unit forms a table
  • Wide data: column headers represent values, not variable names

Example: World Bank data

  • Long format:
# In Colab, first run: !pip install pandas-datareader
from pandas_datareader import wb
countries = ['ZA', 'BR', 'US']
tfr = wb.download(indicator = 'SP.DYN.TFRT.IN', 
                    country = countries, start = 1960, 
                    end = 2024).reset_index()
tfr.head()
  country  year  SP.DYN.TFRT.IN
0  Brazil  2024           1.614
1  Brazil  2023           1.619
2  Brazil  2022           1.629
3  Brazil  2021           1.638
4  Brazil  2020           1.653

Long to wide

  • .pivot() method in pandas
tfr_wide = tfr.pivot(index = 'year', columns = 'country',
                    values = 'SP.DYN.TFRT.IN')

tfr_wide.head()
country  Brazil  South Africa  United States
year                                        
1960      6.051         6.105          3.654
1961      6.022         6.080          3.620
1962      5.984         6.046          3.461
1963      5.930         6.012          3.319
1964      5.818         5.955          3.190

Plotting “wide” data

tfr_wide.plot()

Wide to long

  • pd.melt() function in pandas
tfr_long = pd.melt(tfr_wide.reset_index(), id_vars = 'year', 
                   var_name = 'country', value_name = 'tfr')

tfr_long.head()
   year country    tfr
0  1960  Brazil  6.051
1  1961  Brazil  6.022
2  1962  Brazil  5.984
3  1963  Brazil  5.930
4  1964  Brazil  5.818

Plotting long-form data

tfr_long['year'] = tfr_long['year'].astype(int)
sns.lineplot(x = "year", y = "tfr",
            hue = "country", data = tfr_long)