使用Pandas Series.map()匹配医院邮编时遇NaN及KeyError问题求助

Hi there! Let's break down what's going wrong with your hospital postcode matching and fix it step by step—since you're just getting started with pandas, I'll keep this clear and actionable.

1. Why Did Your First map() Return All NaNs? #

When you use Series.map() with another Series, pandas uses index values to match, not the actual content of the Series.

Your code:

spine['Trust'].map(postcodes_all['Trust_title'])

This tries to take each value from spine['Trust'] and look it up as an index in postcodes_all['Trust_title']. But postcodes_all['Trust_title'] uses the default numeric index (0, 1, 2...), so none of your hospital names match those numeric indices—hence all NaNs.

2. What's Causing the KeyError? #

Look at how you loaded your postcode data:

postcodes_all = pd.read_csv('all_all.csv', index_col = 'Trust')

By setting index_col='Trust', you turned the Trust column into the index of the DataFrame. That means the Trust column no longer exists as a regular column in postcodes_all—so when you try to access postcodes_all['Trust'], pandas can't find it and throws a KeyError.

Plus, you were trying to map the Trust column to itself, which isn't what you want—you need to map to the postcode column!

3. Correct Ways to Match Hospital Names to Postcodes #

Method 1: Use pd.merge() (Most Intuitive for Beginners) #

Merge is designed exactly for this kind of table matching. It's harder to mess up than map():

import pandas as pd

# Load your data (keep only the columns you need)
spine = pd.read_csv('~/Dropbox/Work/NNAP/Spine/Kate_W/kate_spine2.csv', usecols=['Trust'])
postcodes_all = pd.read_csv('all_all.csv', usecols=['Trust_title', 'postcode'])

# Merge the two tables on matching hospital names
spine_with_postcode = pd.merge(
    spine,
    postcodes_all,
    left_on='Trust',  # Column name in spine to match
    right_on='Trust_title',  # Column name in postcodes_all to match
    how='left'  # Keep all rows from spine, even if no postcode matches
)

Now spine_with_postcode will have your original Trust column plus a postcode column. Any rows with NaN in postcode are names that didn't match—we'll fix that next.

Method 2: Use map() Correctly (With a Dictionary) #

If you prefer using map(), convert your postcode data into a dictionary where keys are hospital names and values are postcodes:

# Create a name-to-postcode dictionary
postcode_lookup = postcodes_all.set_index('Trust_title')['postcode'].to_dict()

# Map the dictionary to spine's Trust column
spine['postcode'] = spine['Trust'].map(postcode_lookup)

4. Troubleshooting Unmatched Names (NaN Postcodes) #

Even if names look the same, small differences can break matching. Try these fixes:

• Standardize case: Convert all names to uppercase (or lowercase) to avoid case sensitivity:

  # Clean up whitespace and case
  spine['Trust_clean'] = spine['Trust'].str.upper().str.strip()
  postcodes_all['Trust_title_clean'] = postcodes_all['Trust_title'].str.upper().str.strip()
  
  # Merge using cleaned columns
  spine_with_postcode = pd.merge(
      spine,
      postcodes_all,
      left_on='Trust_clean',
      right_on='Trust_title_clean',
      how='left'
  )
  

• Check for extra spaces: Use str.strip() to remove leading/trailing spaces (like in the code above).
• Look for abbreviations: Some names might use "NHS FT" instead of "NHS FOUNDATION TRUST"—you'll need to manually adjust these or use a fuzzy matching library like fuzzywuzzy if you have lots of them.

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