Week 3 — Data Manipulation with pandas

Programming and Numerical Methods for Economics (ECNM10115) · The University of Edinburgh

Learning goals. After this notebook you can: build DataFrames; index and subset data (including with multiple conditions); create, rename, and modify variables; build dummy variables; and handle missing values properly.

pandas is the tool you will use on every empirical project for the rest of your career. Run everything, attempt each Your turn before revealing the solution — and keep the pandas cheat sheet open next to you.

import pandas as pd
import numpy as np
pd.options.display.float_format = '{:,.2f}'.format

Creating DataFrames

A DataFrame is a table: rows are observations, columns are variables. Two common ways to build one — from Series (columns) or from lists (rows):

# From Series — column by column
ids   = pd.Series([1, 2, 3, 4, 5])
incs  = pd.Series([3000, 1000, 1500, 4500, 2000])
names = pd.Series(['Jef', 'Mark', 'Claire', 'Laura', 'Amy'])
educ  = pd.Series(['primary', 'secondary', 'tertiary', 'secondary', 'secondary'])
year  = pd.Series([2021, 2022, 2022, 2022, 2021])
gen   = pd.Series(['male', 'male', 'female', 'female', 'female'])

df1 = pd.DataFrame({'id': ids, 'income': incs, 'name': names,
                    'education': educ, 'year': year, 'gender': gen})
df1

	id	income	name	education	year	gender
0	1	3000	Jef	primary	2021	male
1	2	1000	Mark	secondary	2022	male
2	3	1500	Claire	tertiary	2022	female
3	4	4500	Laura	secondary	2022	female
4	5	2000	Amy	secondary	2021	female

# From lists — row by row
list_data = [[1, 3000, 'Jef',    'primary',   2021, 'male'],
             [2, 1000, 'Mark',   'secondary', 2022, 'male'],
             [3, 1500, 'Claire', 'tertiary',  2022, 'female'],
             [4, 4500, 'Laura',  'secondary', 2022, 'female'],
             [5, 2000, 'Amy',    'secondary', 2021, 'female']]
var_names = ['id', 'income', 'name', 'education', 'year', 'gender']

df2 = pd.DataFrame(data=list_data, columns=var_names)
df2

	id	income	name	education	year	gender
0	1	3000	Jef	primary	2021	male
1	2	1000	Mark	secondary	2022	male
2	3	1500	Claire	tertiary	2022	female
3	4	4500	Laura	secondary	2022	female
4	5	2000	Amy	secondary	2021	female

Checking your directory

When you load data files, Python looks in the working directory — check where you are with:

import os
os.getcwd()

'/home/claude/site/courses/pnm/notebooks'

Indexing (subsetting) a DataFrame

Two indexers: .iloc selects by position (integer), .loc selects by label.

print(df2.iloc[0, 0])        # first row, first column
df2.iloc[0:2, 1:6]           # first 2 rows, 2nd to 6th column

	income	name	education	year	gender
0	3000	Jef	primary	2021	male
1	1000	Mark	secondary	2022	male

df2.loc[:, ['name']]                    # the name column
df2.loc[0, ['name', 'education']]       # name and education of the first observation

name             Jef
education    primary
Name: 0, dtype: str

Conditional subsetting

This is 80% of everyday empirical work: keep the rows that satisfy a condition.

# Select data for the year 2022
df2.loc[df2['year'] == 2022]

	id	income	name	education	year	gender
1	2	1000	Mark	secondary	2022	male
2	3	1500	Claire	tertiary	2022	female
3	4	4500	Laura	secondary	2022	female

# Selecting only *shows* the data — to keep it, assign it to a new DataFrame:
df2_22 = df2.loc[df2['year'] == 2022]

# Select 2022 data, but only the name and income columns:
df2.loc[df2['year'] == 2022, ['name', 'income']]

	name	income
1	Mark	1000
2	Claire	1500
3	Laura	4500

# Select above-median incomes
med_inc = df2['income'].median()
rich = df2.loc[df2['income'] > med_inc]
rich

	id	income	name	education	year	gender
0	1	3000	Jef	primary	2021	male
3	4	4500	Laura	secondary	2022	female

# Three equivalent ways to select the women in the data:
df_fem = df2.loc[df2['gender'] == 'female']
df_fem = df2.loc[df2['gender'] != 'male']       # not equal
df_fem = df2.loc[~(df2['gender'] == 'male')]    # ~ is the invert operator
df_fem

	id	income	name	education	year	gender
2	3	1500	Claire	tertiary	2022	female
3	4	4500	Laura	secondary	2022	female
4	5	2000	Amy	secondary	2021	female

Multiple conditions

Combine conditions with & (and) and | (or) — each condition in parentheses.

# Women in the top 50% of income
df_femrich = df2.loc[(df2['gender'] == 'female') & (df2['income'] > med_inc)]
print(df_femrich)

# Primary education OR income below 2000
df_educ12 = df2.loc[(df2['education'] == 'primary') | (df2['income'] < 2000)]
df_educ12

   id  income   name  education  year  gender
3   4    4500  Laura  secondary  2022  female

	id	income	name	education	year	gender
0	1	3000	Jef	primary	2021	male
1	2	1000	Mark	secondary	2022	male
2	3	1500	Claire	tertiary	2022	female

Your turn 3.1 — Using df2: select all individuals observed in 2022 with income at most 2000, showing only their name and education. Predict first: who should appear?

# Your attempt here:

💡 Click to reveal the solution — but try it yourself first!

df2.loc[(df2['year'] == 2022) & (df2['income'] <= 2000), ['name', 'education']]

Mark (secondary) and Claire (tertiary).

Series vs DataFrames — a notation clarification

df['var'] gives a Series (one column); df[['var']] (double brackets) gives a DataFrame. Two or more variables always need double brackets.

print(type(df2['income']))     # Series
print(type(df2[['income']]))   # DataFrame

# A Series has methods that depend on its type:
print(df2['income'].mean())        # numeric methods for a numeric column
print(df2['name'].str.upper())     # string methods for a text column

<class 'pandas.Series'>
<class 'pandas.DataFrame'>
2400.0
0       JEF
1      MARK
2    CLAIRE
3     LAURA
4       AMY
Name: name, dtype: str

Useful Series methods

df2['education'].value_counts()

education
secondary    3
primary      1
tertiary     1
Name: count, dtype: int64

Some data tricks: renaming, adding, and modifying variables

# Renaming (add inplace=True to make it stick)
df2.rename(columns={'education': 'educ'})

# Adding variables
df2['country'] = 'UK'
df2['age'] = [27, 40, 53, 29, 34]

# Modifying: operations apply element-wise to the whole column
pound_euro = 1.14
df2['income_eur'] = df2['income'] * pound_euro
df2['log_income'] = np.log(df2['income'])
df2

	id	income	name	education	year	gender	country	age	income_eur	log_income
0	1	3000	Jef	primary	2021	male	UK	27	3,420.00	8.01
1	2	1000	Mark	secondary	2022	male	UK	40	1,140.00	6.91
2	3	1500	Claire	tertiary	2022	female	UK	53	1,710.00	7.31
3	4	4500	Laura	secondary	2022	female	UK	29	5,130.00	8.41
4	5	2000	Amy	secondary	2021	female	UK	34	2,280.00	7.60

# Modify only where a condition holds, with .loc[condition, column]
df2.loc[df2['name'] == 'Amy', 'income'] = 1800

# A classic pattern: build a variable case by case
df2['below_30'] = np.nan
df2.loc[df2['age'] >= 30, 'below_30'] = 0
df2.loc[df2['age'] < 30, 'below_30'] = 1
df2

	id	income	name	education	year	gender	country	age	income_eur	log_income	below_30
0	1	3000	Jef	primary	2021	male	UK	27	3,420.00	8.01	1.00
1	2	1000	Mark	secondary	2022	male	UK	40	1,140.00	6.91	0.00
2	3	1500	Claire	tertiary	2022	female	UK	53	1,710.00	7.31	0.00
3	4	4500	Laura	secondary	2022	female	UK	29	5,130.00	8.41	1.00
4	5	1800	Amy	secondary	2021	female	UK	34	2,280.00	7.60	0.00

# Replacing values (here: any infinities to NaN)
df2['income'] = df2['income'].replace([-np.inf, np.inf], np.nan)

# Dropping observations or columns — these return a NEW DataFrame
df2.drop(index=0)             # drop the first row
df2.drop(columns='below_30')  # drop a column

# To keep the change, assign it:
# df2 = df2.drop(columns='below_30')

	id	income	name	education	year	gender	country	age	income_eur	log_income
0	1	3000	Jef	primary	2021	male	UK	27	3,420.00	8.01
1	2	1000	Mark	secondary	2022	male	UK	40	1,140.00	6.91
2	3	1500	Claire	tertiary	2022	female	UK	53	1,710.00	7.31
3	4	4500	Laura	secondary	2022	female	UK	29	5,130.00	8.41
4	5	1800	Amy	secondary	2021	female	UK	34	2,280.00	7.60

Creating dummies

Dummy (0/1) variables are the bread and butter of empirical economics.

df2['female'] = 1 * (df2['gender'] == 'female')   # 1* converts True/False to 1/0
print(df2[['name', 'gender', 'female']])

# One dummy per category, in one line:
dummies_ed = pd.get_dummies(df2['educ'] if 'educ' in df2 else df2['education'])
dummies_ed

     name  gender  female
0     Jef    male       0
1    Mark    male       0
2  Claire  female       1
3   Laura  female       1
4     Amy  female       1

	primary	secondary	tertiary
0	True	False	False
1	False	True	False
2	False	False	True
3	False	True	False
4	False	True	False

Your turn 3.2 — Create a dummy high_earner equal to 1 for individuals with income strictly above the median (recompute the median first — incomes changed when Amy got a raise!). How many high earners are there?

# Your attempt here:

💡 Click to reveal the solution — but try it yourself first!

med = df2['income'].median()
df2['high_earner'] = 1 * (df2['income'] > med)
print(df2[['name', 'income', 'high_earner']])
print('High earners:', df2['high_earner'].sum())

Booleans sum as 1/0 — the same trick as in Week 2.

Working with missing values (NaNs)

Real data is full of holes. Let’s create a dataset with missing values (using np.nan or None):

list_data = [[np.nan, 3000,   None,     'primary',   np.nan, 'male'],
             [2,      1000,   'Mark',   'secondary', 2022,   'male'],
             [3,      1500,   'Claire', np.nan,      2022,   np.nan],
             [4,      np.nan, np.nan,   'secondary', 2022,   'female'],
             [5,      np.nan, 'Amy',    'secondary', np.nan, 'female']]

df2_nans = pd.DataFrame(data=list_data, columns=var_names)
df2_nans

	id	income	name	education	year	gender
0	NaN	3,000.00	NaN	primary	NaN	male
1	2.00	1,000.00	Mark	secondary	2,022.00	male
2	3.00	1,500.00	Claire	NaN	2,022.00	NaN
3	4.00	NaN	NaN	secondary	2,022.00	female
4	5.00	NaN	Amy	secondary	NaN	female

# Detect missing values
print(df2_nans.isnull())
print(df2_nans.isnull().sum())    # count NaNs per variable — always do this first!

      id  income   name  education   year  gender
0   True   False   True      False   True   False
1  False   False  False      False  False   False
2  False   False  False       True  False    True
3  False    True   True      False  False   False
4  False    True  False      False   True   False
id           1
income       2
name         2
education    1
year         2
gender       1
dtype: int64

# Filling missing values with .fillna()
df2_nans['education'].fillna('missing educ')
df2_nans['income'].fillna(df2_nans['income'].dropna().median())
df2_nans['income'].fillna(0)

0   3,000.00
1   1,000.00
2   1,500.00
3       0.00
4       0.00
Name: income, dtype: float64

Quick check ✋ — filling income NaNs with 0 versus with the median: when would each choice badly distort your analysis?

Answer

Filling with 0 drags the mean down and creates a fake mass of zero-income individuals; filling with the median hides genuine dispersion and can flatten inequality measures. The honest first step is always to ask why the value is missing.

# Dropping rows or columns with NaNs
df2_nans.dropna(axis=0)   # drops ALL rows containing any NaN — usually too aggressive
df2_nans.dropna(axis=1)   # drops all columns with NaNs


0
1
2
3
4

# Better: drop only when KEY variables are missing
df2_nans.dropna(axis=0, subset=['id'])

	id	income	name	education	year	gender
1	2.00	1,000.00	Mark	secondary	2,022.00	male
2	3.00	1,500.00	Claire	NaN	2,022.00	NaN
3	4.00	NaN	NaN	secondary	2,022.00	female
4	5.00	NaN	Amy	secondary	NaN	female

Your turn 3.3 — For df2_nans: (a) count missing values per variable, (b) drop rows where income is missing, (c) on the result, fill missing education with 'unknown'. How many rows survive?

# Your attempt here:

💡 Click to reveal the solution — but try it yourself first!

print(df2_nans.isnull().sum())                       # (a)
clean = df2_nans.dropna(axis=0, subset=['income'])   # (b)
clean = clean.copy()
clean['education'] = clean['education'].fillna('unknown')   # (c)
print(clean)
print('Rows surviving:', len(clean))

3 rows survive — the two with missing income are dropped.

Self-check quiz

Q1. df.iloc[0:2, :] — how many rows do you get?

Answer

2 — positions 0 and 1; like range, the end is excluded.

Q2. What’s the difference between df['income'] and df[['income']]?

Answer

Single brackets → Series; double brackets → DataFrame.

Q3. Why does df.drop(columns='x') not change df?

Answer

It returns a new DataFrame — assign it (df = df.drop(…)) or nothing sticks.

This week’s case study and lab

Case study: consumption, income, and wealth inequality in Uganda (UNPS panel data) — summary statistics, urban vs rural gaps, distributions, and lifecycle profiles. The dataset is distributed on Learn.
Lab 2: hands-on practice with the UNPS extract, preparing you for PS2.

Next week: from data to models.