pandas - Modify column values based on dictionary values

Question

I have an CSV file containing a column "State" which contains US State names in full like: "New Jersey", "California", etc. I want to modify this column so that they contain abbreviations instead of the full name like "NJ", "CA"...

To do this, I already have a dictionary that maps the state name to its abbreviation

us_state_abbrev = {
'Alabama': 'AL', 'Alaska': 'AK', 'Arizona': 'AZ', 'Arkansas': 'AR', 'California': 'CA', 'Colorado': 'CO',
'Connecticut': 'CT', 'Delaware': 'DE', 'Florida': 'FL', 'Georgia': 'GA', 'Hawaii': 'HI', 'Idaho': 'ID',
'Illinois': 'IL', 'Indiana': 'IN', 'Iowa': 'IA', 'Kansas': 'KS', 'Kentucky': 'KY', 'Louisiana': 'LA',
'Maine': 'ME', 'Maryland': 'MD', 'Massachusetts': 'MA', 'Michigan': 'MI', 'Minnesota': 'MN', 'Mississippi': 'MS',
'Missouri': 'MO', 'Montana': 'MT', 'Nebraska': 'NE', 'Nevada': 'NV', 'New Hampshire': 'NH', 'New Jersey': 'NJ',
'New Mexico': 'NM', 'New York': 'NY', 'North Carolina': 'NC', 'North Dakota': 'ND', 'Ohio': 'OH', 'Oklahoma': 'OK',
'Oregon': 'OR', 'Pennsylvania': 'PA', 'Rhode Island': 'RI', 'South Carolina': 'SC', 'South Dakota': 'SD',
'Tennessee': 'TN', 'Texas': 'TX', 'Utah': 'UT', 'Vermont': 'VT', 'Virginia': 'VA', 'Washington': 'WA',
'West Virginia': 'WV', 'Wisconsin': 'WI', 'Wyoming': 'WY'}

How do I loop through the column in my CSV file AND the dictionary and replace the full state name with the abbreviation?

Here's the code I wrote but it doesn't work:

with open(emp_file, 'r', errors='ignore') as fileHandle:
reader = csv.reader(fileHandle)
for row in reader:
    for state, abbrev in us_state_abbrev.items():
        if row[4] == state:
            row[4] = abbrev

What am I doing wrong here? Please help.

map should work as suggested by @Tacratis, if no, i would recommend you to post a sample input v/s output dataframe. — anky
– anky, Commented Feb 8, 2019 at 5:57

Tacratis · Accepted Answer · 2019-02-08 06:06:37Z

4

import pandas as pd

df = pd.read_csv(emp_file)

then, assuming you know which column you want to edit:

df['State'] = df['State'].map(us_state_abbrev).fillna(df['State'])

Note: the last part deals with State entries not present in your dictionary

edited Feb 8, 2019 at 6:06

answered Feb 8, 2019 at 5:50

Tacratis

1,0551 gold badge7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tacratis Over a year ago

Perfect, I'll add it to the answer

Gerardo Jaramillo Over a year ago

What is the difference between the map method and using replace? same result different way?

Collectives™ on Stack Overflow

pandas - Modify column values based on dictionary values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related