Lab 2: Introduction to Python (continued)

In Lab 1, we learned how to:

1. Install and import packages
2. Load CSV data into pandas
3. Do basic inspection with .head(), .info(), .describe()
4. Create, modify, and drop variables
5. Do simple subsetting and cleaning

In Lab 2, we will:

1. Load an Excel file with soda price data
2. Explore and clean the dataset
3. Practice a few small coding tasks during the lab

1. Setup and load the Excel data

First, we import the packages we need and load the dataset sodaprice.xlsx.

# make sure to have these packages installed, if not, run the following command in Anaconda Prompt terminal
# conda install -c conda-forge pandas numpy openpyxl matplotlib

import os
import pandas as pd
import numpy as np

# Read the Excel file (make sure sodaprice.xlsx is in the same folder as this notebook)
df = pd.read_excel("sodaprice.xlsx")

df.head()

	soda_price	propblack	income	county	New Jersey
0	1.12	0.171154	44534.0	18	1
1	1.06	0.171154	44534.0	18	1
2	1.06	0.047360	41164.0	12	1
3	1.12	0.052839	50366.0	10	1
4	1.12	0.034480	72287.0	10	1

# Basic info and summary statistics
df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 410 entries, 0 to 409
Data columns (total 5 columns):
 #   Column      Non-Null Count  Dtype  
---  ------      --------------  -----  
 0   soda_price  402 non-null    float64
 1   propblack   409 non-null    float64
 2   income      409 non-null    float64
 3   county      410 non-null    int64  
 4   New Jersey  410 non-null    int64  
dtypes: float64(3), int64(2)
memory usage: 16.1 KB

df.describe()

	soda_price	propblack	income	county	New Jersey
count	402.000000	409.000000	409.000000	410.000000	410.000000
mean	1.044876	0.113486	47053.784841	13.658537	0.807317
std	0.088687	0.182416	13179.286069	8.045439	0.394888
min	0.730000	0.000000	15919.000000	1.000000	0.000000
25%	0.980000	0.011649	37883.000000	6.000000	1.000000
50%	1.060000	0.041444	46272.000000	14.000000	1.000000
75%	1.085000	0.121059	54981.000000	20.000000	1.000000
max	1.490000	0.981658	136529.000000	29.000000	1.000000

2. Cleaning and renaming columns

The dataset contains variables such as:

• soda_price: price of soda
• propblack: proportion of Black residents
• income: income level
• county: county identifier
• New Jersey: indicator for being in New Jersey (1 = NJ, 0 = other)

We will first rename the New Jersey column to new_jersey, and then check for missing values.

# Rename the 'New Jersey' column to avoid spaces in the column name
df = df.rename(columns={"New Jersey": "new_jersey"})

# Check missing values
df.isna().sum()

soda_price    8
propblack     1
income        1
county        0
new_jersey    0
dtype: int64

Your Task 1: Proportion of missing values

Goal: Compute the proportion of missing values for each column.

Hint: Remember that in pandas, booleans True/False can be treated like 1/0, so .mean() on a boolean Series gives a proportion.

# TODO: Calculate the proportion of missing values in each column.
# Hint: use .isna() and .mean()

df.isna().mean()

soda_price    0.019512
propblack     0.002439
income        0.002439
county        0.000000
new_jersey    0.000000
dtype: float64

2.1 Simple missing data strategies

Two common simple strategies:

1. Drop rows with missing values
2. Fill missing values with a summary statistic (e.g., median)

We will create two versions of the data:

1. df_drop: all rows with any missing values removed
2. df_fill: missing income values filled with the median income

# Option 1: Drop rows with any missing values
df_drop = df.dropna()

df_drop.info()

<class 'pandas.core.frame.DataFrame'>
Index: 401 entries, 0 to 409
Data columns (total 5 columns):
 #   Column      Non-Null Count  Dtype  
---  ------      --------------  -----  
 0   soda_price  401 non-null    float64
 1   propblack   401 non-null    float64
 2   income      401 non-null    float64
 3   county      401 non-null    int64  
 4   new_jersey  401 non-null    int64  
dtypes: float64(3), int64(2)
memory usage: 18.8 KB

# Option 2: Fill missing income with the median
df_fill = df.copy()

median_income = df_fill["income"].median()
df_fill["income"] = df_fill["income"].fillna(median_income)

df_fill["income"].isna().sum()  # should be 0 after filling

np.int64(0)

Your Task 2: Fill missing propblack

Goal: Create a copy of the data and fill missing values in propblack with its median.

Complete the code below.

df2 = df.copy()

# TODO: Fill missing propblack with its median and store in a new column 'propblack_fill'

# Step 1: compute the median
median_propblack = df2["propblack"].median()

# Step 2: fill missing values
df2["propblack_fill"] = df2["propblack"].fillna(median_propblack)

# Check results
df2["propblack_fill"].isna().sum()

np.int64(0)

3. Creating new variables with pandas and NumPy

We will now create new variables using both pandas operations and NumPy functions.

Examples:

1. Convert income to thousands (income_k)
2. Standardize soda price (soda_z)
3. Create indicator variables using np.where

# Income in thousands
df["income_k"] = df["income"] / 1000

df[["income", "income_k"]].head()

	income	income_k
0	44534.0	44.534
1	44534.0	44.534
2	41164.0	41.164
3	50366.0	50.366
4	72287.0	72.287

# Revised code: In previous version, when I extracted the values of soda price, we referred to the original data frame. 
# This created errors as this array contained missing values. So I revised it to df_drop data frame which was cleaned before.

# Z-score (standardize) soda_price using NumPy
price_array = df_drop["soda_price"].values  # NumPy array

price_mean = np.mean(price_array)
price_std = np.std(price_array)

df_drop["soda_z"] = (df_drop["soda_price"] - price_mean) / price_std

df_drop[["soda_price", "soda_z"]].head()

C:\Users\hou.582\AppData\Local\Temp\1\ipykernel_22716\2625558098.py:10: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df_drop["soda_z"] = (df_drop["soda_price"] - price_mean) / price_std

	soda_price	soda_z
0	1.12	0.847217
1	1.06	0.170681
2	1.06	0.170681
3	1.12	0.847217
4	1.12	0.847217

# You may see the warning above. It implies another way to pass values to data frame object.

# Z-score (standardize) soda_price using NumPy
price_array = df_drop["soda_price"].values  # NumPy array

price_mean = np.mean(price_array)
price_std = np.std(price_array)

# This is a different command implied by the warning message.
df_drop.loc[:,"soda_z"] = (df_drop["soda_price"] - price_mean) / price_std

df_drop[["soda_price", "soda_z"]].head()

	soda_price	soda_z
0	1.12	0.847217
1	1.06	0.170681
2	1.06	0.170681
3	1.12	0.847217
4	1.12	0.847217

df_drop.info()

<class 'pandas.core.frame.DataFrame'>
Index: 401 entries, 0 to 409
Data columns (total 6 columns):
 #   Column      Non-Null Count  Dtype  
---  ------      --------------  -----  
 0   soda_price  401 non-null    float64
 1   propblack   401 non-null    float64
 2   income      401 non-null    float64
 3   county      401 non-null    int64  
 4   new_jersey  401 non-null    int64  
 5   soda_z      401 non-null    float64
dtypes: float64(4), int64(2)
memory usage: 21.9 KB

Your Task 3: High income dummy with np.where

Goal: Create a new variable high_income equal to 1 if income is greater than the median income, and 0 otherwise.

Use np.where(condition, value_if_true, value_if_false).

# TODO: Create a high_income dummy using np.where

income_array = df["income"].values

income_median = np.median(income_array)

df["high_income"] = np.where(income_array > income_median,1,0)

df[["income", "high_income"]].head()

	income	high_income
0	44534.0	0
1	44534.0	0
2	41164.0	0
3	50366.0	0
4	72287.0	0

We can similarly use np.where to define a high price dummy. For example, let's define high_price = 1 if soda_price > 1.10.

df["high_price"] = np.where(df["soda_price"] > 1.10 , 1, 0)

df[["soda_price", "high_price"]].head()

	soda_price	high_price
0	1.12	1
1	1.06	0
2	1.06	0
3	1.12	1
4	1.12	1

4. Groupby and summary statistics

Now we explore how soda prices differ by groups, such as:

1. New Jersey vs. other states
2. High-income vs. low-income areas

# Mean soda price by New Jersey vs other
df.groupby("new_jersey")["soda_price"].mean()

new_jersey
0    0.974675
1    1.061508
Name: soda_price, dtype: float64

Your Task 4: Mean soda price by high_income

Goal: Compute the mean soda price for high-income vs. non-high-income areas.

Use .groupby() with the high_income dummy.

# TODO: compute mean soda_price by high_income

We can also compute multiple statistics at once using .agg().

df.groupby("new_jersey").agg(
    mean_price = ("soda_price", "mean"),
    std_price  = ("soda_price", "std"),
    n          = ("soda_price", "count")
)

	mean_price	std_price	n
new_jersey
0	0.974675	0.069483	77
1	1.061508	0.084579	325

Your Task 5: Two-way comparison

Goal: Compare mean soda prices across four groups defined by:

• new_jersey (0/1)
• high_income (0/1)

Use a two-variable groupby with a list: ["new_jersey", "high_income"].

# TODO: group by both new_jersey and high_income and compute mean soda_price

Question: Based on the group means above, which group seems to have the highest soda prices?

5. Visualizing the data with matplotlib

Examples of Histogram, Boxplot, and Scatter

import matplotlib.pyplot as plt

fig1 = plt.figure()
plt.hist(df["soda_price"].dropna(), bins=20)
plt.xlabel("Soda price")
plt.ylabel("Frequency")
plt.title("Histogram of soda prices")
plt.show()

fig2 = plt.figure()
plt.scatter(df["income"], df["soda_price"])
plt.xlabel("Income")
plt.ylabel("Soda price")
plt.title("Income vs. soda price")

Text(0.5, 1.0, 'Income vs. soda price')

fig3, axs = plt.subplots(1,2, figsize=(10,8))

axs[0].hist(df["soda_price"])
axs[0].set_title("Histogram of soda price")

axs[1].scatter(df["income"], df["soda_price"])
axs[1].set_title("Income vs soda price")

plt.show()

fig1.savefig("fig1.png")
fig1.savefig("fig1_updated.png", dpi=500)

fig2.savefig("fig2.pdf")
fig3.savefig("fig3.svg")