如何删除本科生或研究生人数存在缺失的院校全部4年数据行

Got it, let's tackle this problem step by step. You need to remove all 4 rows for a college if any of its 2013-2016 entries has missing undergrad or grad student numbers. Below are practical solutions using two widely used tools: Python's Pandas and SQL.

Solution 1: Using Python Pandas #

This is ideal if you're working with a CSV/Excel file and prefer a scripting approach.

1. Load and flag rows with missing values
   First, we'll mark any row where either undergrad or grad student count is missing.
2. Identify valid colleges
   Group by college name and check if the group has zero missing rows.
3. Filter to keep only valid colleges
   Keep all rows for colleges that have no missing data across the 4 years.

Here's the code:

import pandas as pd

# Load your dataset (replace with your file path)
df = pd.read_csv("college_enrollment.csv")

# Create a flag column: True if either count is missing
df['has_missing'] = df[['number of undergrad students', 'number of grad students']].isnull().any(axis=1)

# Get colleges with NO missing data (sum of flags is 0)
valid_colleges = df.groupby('college name')['has_missing'].sum() == 0

# Filter the original dataframe to keep only valid colleges
cleaned_df = df[df['college name'].isin(valid_colleges[valid_colleges].index)]

# Optional: Drop the temporary flag column
cleaned_df = cleaned_df.drop(columns='has_missing')

# Save the cleaned data if needed
cleaned_df.to_csv("cleaned_college_enrollment.csv", index=False)

Notes for Pandas: #

• If your "missing values" are empty strings ('') or placeholders like 'N/A', adjust the flag step to:

  df['has_missing'] = (df['number of undergrad students'].replace({'N/A': '', '': None}).isnull()) | 
                      (df['number of grad students'].replace({'N/A': '', '': None}).isnull())
  

Solution 2: Using SQL #

Perfect if your data is stored in a database (like PostgreSQL, MySQL, etc.). Let's assume your table is named college_enrollment with columns: year, college_name, undergrad_count, grad_count.

Method 1: Using NOT IN #

This is straightforward for most cases:

SELECT *
FROM college_enrollment
WHERE college_name NOT IN (
    -- Subquery to find colleges with ANY missing data
    SELECT DISTINCT college_name
    FROM college_enrollment
    WHERE undergrad_count IS NULL OR grad_count IS NULL
);

Method 2: Using LEFT JOIN (More Robust) #

If you're worried about edge cases (like NULL values in the subquery), this method is safer:

SELECT ce.*
FROM college_enrollment ce
LEFT JOIN (
    SELECT DISTINCT college_name
    FROM college_enrollment
    WHERE undergrad_count IS NULL OR grad_count IS NULL
) invalid_colleges ON ce.college_name = invalid_colleges.college_name
-- Keep only rows where no match was found (i.e., valid colleges)
WHERE invalid_colleges.college_name IS NULL;

Notes for SQL: #

• If missing values are stored as empty strings instead of NULL, update the where clause to:

  WHERE undergrad_count IS NULL OR undergrad_count = '' OR grad_count IS NULL OR grad_count = ''
  

内容的提问来源于stack exchange，提问作者salami22