基于另一列填充DataFrame空值的技术实现咨询

Hey there! Let's sort out that missing data problem you're dealing with. You want to fill in the empty State and Country fields for San Diego, San Francisco, and Sacramento using the same values as Los Angeles—here's a clean, reliable way to do it with pandas:

Step 1: Grab the reference values from Los Angeles #

First, we'll pull the valid State and Country values from Los Angeles since that's our template:

# Get the State and Country from Los Angeles (assuming it's present in the DataFrame)
ca_state = df.loc[df['City'] == 'Los Angeles', 'State'].iloc[0]
ca_country = df.loc[df['City'] == 'Los Angeles', 'Country'].iloc[0]

This code uses loc to find the row where City is "Los Angeles", then extracts the first (and only, in your case) value for State and Country.

Step 2: Fill the missing values for target cities #

Next, we'll target the cities in your CA_cities list and fill their missing State/Country fields. We'll use boolean indexing to only update rows where the value is actually missing, so we don't accidentally overwrite any existing data:

CA_cities = ['San Diego', 'Los Angeles', 'San Francisco', 'Sacramento']

# Fill missing State for CA cities
df.loc[df['City'].isin(CA_cities) & df['State'].isna(), 'State'] = ca_state

# Fill missing Country for CA cities
df.loc[df['City'].isin(CA_cities) & df['Country'].isna(), 'Country'] = ca_country

Full Working Example #

If you want to test this end-to-end, here's a complete snippet with your sample data:

import pandas as pd

# Your sample data
data = {
    'City': ['Chicago', 'Boston', 'San Diego', 'Los Angeles', 'San Francisco', 'Sacramento', 'Vancouver'],
    'State': ['IL', 'MA', None, 'CA', None, None, 'BC'],
    'Country': ['United States', 'United States', None, 'United States', None, None, 'Canada']
}

df = pd.DataFrame(data)

# Define your CA cities list
CA_cities = ['San Diego', 'Los Angeles', 'San Francisco', 'Sacramento']

# Get reference values from Los Angeles
ca_state = df.loc[df['City'] == 'Los Angeles', 'State'].iloc[0]
ca_country = df.loc[df['City'] == 'Los Angeles', 'Country'].iloc[0]

# Fill missing values
df.loc[df['City'].isin(CA_cities) & df['State'].isna(), 'State'] = ca_state
df.loc[df['City'].isin(CA_cities) & df['Country'].isna(), 'Country'] = ca_country

# Print the result
print(df)

Output #

Running this will give you the filled DataFrame:

City State        Country
0        Chicago    IL  United States
1         Boston    MA  United States
2      San Diego    CA  United States
3    Los Angeles    CA  United States
4  San Francisco    CA  United States
5     Sacramento    CA  United States
6      Vancouver    BC         Canada

Quick Notes #

• If there are multiple rows for Los Angeles, using .iloc[0] still works as long as all its State/Country values are the same. For extra safety, you could use .unique()[0] to grab the unique valid value.
• The & operator ensures we only update rows where both the city is in our list and the field is missing—this prevents overwriting any existing data you might have.

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