How to change column values based on another column value?-CodePudding

I am trying to change the column values in my csv file that have state codes (state_cd) to state name (state_name). Here are my two csv files and my code.

states.csv

"state_name","state_cd"
"Alabama","AL"
"Alaska","AK"
"Arizona","AZ"
"Arkansas","AR"
"California","CA"
"Colorado","CO"
"Connecticut","CT"
"Delaware","DE"
"District of Columbia","DC"
"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"

schools.csv

"parent_category_nm","category_nm","place_name","address","city","state_cd"
"Schools","Elementary Schools","123 YOU N ME PRESCHOOL","809 W DETWEILLER DR STE A","PEORIA","IL"............................

Code.py

import pandas as pd

df1 = pd.read_csv("school.csv")

df2 = pd.read_csv("state.csv")

df3 = df1.merge(df2, left_on="state_cd", right_on="state_name", how="left")

I am not receiving any errors but the code also isn't doing anything. The expected output would be something like this with the state code changed to the state name.

"parent_category_nm","category_nm","place_name","address","city","state_cd"
"Schools","Elementary Schools","123 YOU N ME PRESCHOOL","809 W DETWEILLER DR STE A","PEORIA","Illinois"............................

Can anyone help with this?

CodePudding user response：

What you want to do is:

df3 = df1.merge(df2, on="state_cd", how="left")

This will add the state_name column to your school dataframe.

If you want to keep state_cd as the column with the complete names:

 df1['state_cd'] = df1.state_cd.map(df2.set_index('state_cd').state_name)

CodePudding user response：

df1['state'] = df1['state_cd'].merge(df2,how='left')