如何筛选DataFrame中State与Country列均为NaN的行

First, let's clarify a small discrepancy: Your problem statement mentions wanting rows where both State and Country are NaN, but your expected output includes rows where State is not NaN (like NYC, 601009, etc.) but Country is NaN—excluding only rows where State is a valid US state code (NY, AZ) with Country NaN. I'll cover both scenarios below.

Scenario 1: Get rows where both State and Country are NaN #

If you strictly want rows where both columns have missing values, use boolean indexing with isna() and the AND operator (&):

import pandas as pd

# Create the sample DataFrame
data = {
    'City': ['A', 'B', 'C', 'D', 'E', 'F', 'G'],
    'State': ['NYC', pd.NA, 'NY', '601009', 'AZ', '000001', pd.NA],
    'Country': [pd.NA, pd.NA, pd.NA, pd.NA, pd.NA, pd.NA, pd.NA],
    'Name': [pd.NA, 'USA', pd.NA, pd.NA, pd.NA, pd.NA, pd.NA]
}
df = pd.DataFrame(data)

# Filter rows where both State and Country are NaN
result = df[(df['State'].isna()) & (df['Country'].isna())]
print(result)

This returns only row G:

City State Country Name
6    G  <NA>    <NA>  <NA>

Scenario 2: Get rows matching your expected output #

Based on your desired result, you want rows where Country is NaN and State is either NaN or not a valid US state code (excluding NY and AZ). Here's how to implement that:

# Define valid state codes present in your dataset
valid_states = ['NY', 'AZ']

# Filter rows where Country is NaN and State is not in the valid list
result = df[(df['Country'].isna()) & (~df['State'].isin(valid_states))]
print(result)

This produces your expected output:

City   State Country Name
0    A     NYC    <NA>  <NA>
3    D  601009    <NA>  <NA>
5    F  000001    <NA>  <NA>
6    G    <NA>    <NA>  <NA>

If you want to use a full list of US state codes instead of just the ones in your data, replace valid_states with the complete set of 2-letter US state abbreviations (e.g., ['AL', 'AK', 'AZ', ..., 'WY']).

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