Pandas Group By

This notebook demonstrates the following features of Pandas:

Read selected columns (usecols)
Identify missing values (isnull)
Drop rows with missing values (dropna)
Split and summarize data by categories (grouopby)
Sort data by column values (sort_values)
Pandas visualization (bar chart, scatter plot)
Pandas visualization backend (matplotlib, plotly)

References:

Step 1 - Set Up Environment

1.1 - Install Plotly

Plotly is an interactive visualization library and can be utilized as the backend for Pandas. Pandas support multiple visualization backends. For example, matplotlib, seaborn, plotly, bokeh are all supported.

!pip install plotly

1.2 - Import pandas

import pandas as pd print(f"pd.__version__ = {pd.__version__}")

Step 2. Read Data

We will read data directly from the web link. Since the dataset has almost 2000 columns, we only read a selected list of columns of interest.

DATA_URL = "https://data.ed.gov/dataset/9dc70e6b-8426-4d71-b9d5-70ce6094a3f4/resource/823ac095-bdfc-41b0-b508-4e8fc3110082/download/most-recent-cohorts-all-data-elements-1.csv" COLUMN_LIST = ['UNITID','INSTNM', 'CONTROL','CITY', 'STABBR','ST_FIPS', 'ZIP', 'TUITIONFEE_IN','ADM_RATE','MD_EARN_WNE_P10'] df = pd.read_csv(DATA_URL, usecols=COLUMN_LIST) df.shape

df.head()

Step 3 - Cleanse Data

The data have many missing values and other quality issues and requires cleansing.

3.1 - Handle Missing Values

For simplicity, we simply drop all rows that contain missing values.

df[df.notnull()]

df.isnull().sum()

df2 = df.dropna() df2.shape

3.2 - Cleanse Median Earnings Column

# Find out how many colleges have "PrivacySuppressed" in median earnings df2[df2["MD_EARN_WNE_P10"] == "PrivacySuppressed"]

# Remove these colleges df2 = df2[df2["MD_EARN_WNE_P10"] != "PrivacySuppressed"] df2.shape

# Change median earnings column type from string to integer df2["MD_EARN_WNE_P10"] = df2["MD_EARN_WNE_P10"].astype(int) df2.info()

# Replace the Control Code with label for easier understanding control_dict = { 1:"Public", 2:"Private, Nonprofit", 3:"Proprietary" } df2.replace({"CONTROL": control_dict}, inplace=True) df2.sample(5)

Step 4 - Aggregate Data

# Find out how many unique states df2["STABBR"].nunique()

# List all states df2["STABBR"].unique()

# Use Named Aggregates to calculate number of colleges, mean tuition, # and mean earnings for each state. gb = df2[["STABBR","TUITIONFEE_IN","MD_EARN_WNE_P10"]].groupby('STABBR').agg( Number_Colleges=pd.NamedAgg("STABBR", "count"), Mean_Tuition=pd.NamedAgg("TUITIONFEE_IN", "mean"), Mean_Earnings=pd.NamedAgg("MD_EARN_WNE_P10", "mean") ) gb.head()

# Reset index so that State becomes a column gb = gb.reset_index() gb.head()

# Rename the column gb.rename(columns={"STABBR":"STATE"}, inplace=True) gb.sample()

Step 5 - Visualize Data

5.1 - Bar Charts

We want to rank the states based on their in-state tuition and their potential earnings (10 years after graduation)

# Rank total number of colleges from lowest to highest by state pd.options.plotting.backend = "matplotlib" gb.sort_values(by="Number_Colleges").plot.bar( x="STATE", y="Number_Colleges", figsize=(12,8) )

# Rank in-state tution from lowest to highest gb.sort_values(by="Mean_Tuition").plot.bar( x="STATE", y="Mean_Tuition", figsize=(12,8) )

# Rank potential earnings from lowest to highest gb.sort_values(by="Mean_Earnings").plot.bar( x="STATE", y="Mean_Earnings", figsize=(12,8) )

5.2 - Scatter Plot

We want to find out if there is a relationship between tuition and potential earnings.

# First, we use matplotlib as the plotting backend. import matplotlib.pyplot as plt pd.options.plotting.backend = "matplotlib" gb.plot.scatter(x="Mean_Tuition", y="Mean_Earnings", figsize=(10,8)) plt.show()

# Here, we switch the Pandas plotting backend from matplotlib to plotly so that # can explore interactively pd.options.plotting.backend = "plotly" gb.plot.scatter(x="Mean_Tuition", y="Mean_Earnings", hover_data=["STATE"])